如何利用工具，迅猛定位低效SQL？ | 1分钟系列
source link: https://www.tuicool.com/articles/67vqmqF
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
《 两个工具分析SQL死锁 》
《 SQL空值带来的大坑 》
两个案例分析，展现了MySQL性能分析工具explain的强大。
详细叙述了explain结果中最重要的 type字段 （连接类型）的含义。
其实，explain结果中还有一个 Extra字段 ，对分析与优化SQL有很大的帮助，今天花1分钟简单和大家聊一聊。
数据准备 ：
create table user (
id int primary key ,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;
insert into user values(1, 'shenjian','no');
insert into user values(2, 'zhangsan','no');
insert into user values(3, ' lisi ', 'yes');
insert into user values(4, ' lisi ', 'no');
数据说明 ：
用户表：id 主键索引 ，name 普通索引 （非唯一），sex 无索引 ；
四行记录：其中name普通索引存在重复记录lisi；
实验目的 ：
通过构造各类SQL语句，对explain的Extra字段进行说明，启发式定位待优化低性能SQL语句。
一、【Using where】
实验语句 ：
explain select * from user where sex='no';
结果说明 ：
Extra为 Using where 说明， SQL使用了where条件过滤数据 。
需要注意的是：
（1）返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，对于这类SQL往往需要进行优化；
（2）使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type（连接类型）来综合判断；
画外音： join type在 《 同一个SQL语句，为啥性能差异咋就这么大呢？ 》 一文中有详细叙述，本文不再展开。
本例虽然Extra字段说明使用了where条件过滤，但type属性是ALL，表示需要扫描全部数据，仍有优化空间。
常见的优化方法为，在where过滤属性上添加索引。
画外音： 本例中，sex字段区分度不高，添加索引对性能提升有限。
二、【Using index】
实验语句 ：
explain select id,name from user where name='shenjian';
结果说明 ：
Extra为 Using index 说明， SQL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录 。
画外音： The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row.
这类SQL语句往往性能较好。
问题来了，什么样的列数据，会包含在索引树上呢？
三、【Using index condition】
实验语句：
explain select id,name,sex from user
where name='shenjian';
画外音： 该SQL语句与上一个SQL语句不同的地方在于，被查询的列，多了一个sex字段。
结果说明：
Extra为 Using index condition 说明， 确实命中了索引，但不是所有的列数据都在索引树上，还需要 访问实际的行记录 。
画外音： 聚集索引，普通索引的底层实现差异，详见《 1分钟了解MyISAM与InnoDB的索引差异 》。
这类SQL语句性能也较高，但不如 Using index 。
问题来了，如何优化为Using index呢？
四、【Using filesort】
实验语句：
explain select * from user order by sex;
结果说明：
Extra为 Using filesort 说明， 得到所需结果集，需要对所有记录进行文件排序 。
这类SQL语句性能极差 ，需要进行优化。
典型的，在一个没有建立索引的列上进行了 order by ，就会触发 filesort ，常见的优化方案是，在 order by 的列上添加索引，避免每次查询都全量排序。
五、【Using temporary】
实验语句：
explain select * from user group by name order by sex;
结果说明：
Extra为 Using temporary 说明， 需要建立临时表 (temporary table) 来暂存中间结果 。
这类SQL语句性能较低，往往也需要进行优化。
典型的， group by 和 order by 同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。
六、【Using join buffer (Block Nested Loop)】
实验语句：
explain select * from user where id in(select id from user where sex='no');
结果说明：
Extra为 Using join buffer (Block Nested Loop) 说明， 需要进行嵌套循环计算 。
画外音： 内层和外层的type均为ALL，rows均为4，需要循环进行4*4次计算。
这类SQL语句性能往往也较低，需要进行优化。
典型的，两个关联表join，关联字段均未建立索引，就会出现这种情况。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。
结尾 ：
explain是SQL优化中最常用的工具，搞定type和Extra，explain也就基本搞定了。
-
《 MySQL explain，type分析 》进行了常见type分析
-
本文进行了常见Extra分析
-
《两个工具分析SQL死锁》和《SQL空值带来的大坑》是两篇典型案例分析
-
《 MyISAM与InnoDB的索引差异 》是InnoDB和MyISAM索引差异分析
-
《 数据库索引，到底是什么做的？ 》是索引底层实现分析
以上几篇文章，强烈 建议 大家读透。
架构师之路 -分享技术思路
相关推荐 ：
《 写缓冲(change buffer)，这次彻底懂了！ 》
作业 ：
select id,name where XXX 是 Using index ；
select id,name,sex where XXX 是 Using index condition ；
后者如何优化为 Using index 呢 ？
希望大家有收获，帮忙 再看 哟。
Recommend
-
49
不良的领导风格会导致团队之间的脱节和冲突
-
48
程序员一定要掌握 Linux 操作系统嘛？ 回想下你用的 Google 搜索，淘宝购物，用 QQ、微信聊天的时候，其实这些软件和服务的背后，都是成千上万的 Linux 服务器在支撑。 对软件工程师来说，几乎一定会遇到 Linux 的应用场景，如果...
-
41
我： 你好我面试开发，我会Java、Python... 面试官：稍等，你对操作系统了解多少？Linux 环境会部署吗？都会哪些命令？ 我：？？？ 面试官：...好的等通知吧。
-
43
团建这样做，才能拉近团队的距离。
-
41
就在前期准备的过程，不断和同事、合作伙伴磨合的过程中，我发现了很多行业里、职场里的诟病，这些诟病最大的危害不仅能浪费你最宝贵的时间，还分分钟能带你跑偏，让你最后无所适从。本文给大家讲讲关于大家为什么会低效而存在的问题。 这段时间真的忙，忙新产品的...
-
35
本文首发于公众号「Python知识圈」，如需转载，请在公众号联系作者授权。 前言 上一篇文章整理了的公众号所有文章的导航链接，其实如果手动整理起来的话，是一件很费力的事情，因为公众号里添加文章的时候只能一篇篇的选...
-
27
Linux已经成为目前最火的操作系统之一，尽管现在的Linux用户很多，但很多使用Linux的同学发现，他们在Linux下的工作效率并不高，那么这是为什么...
-
4
00:04 财富管理公司：比特币仍然低效 以太坊更加高效 财富管理公司Bessemer Trust在其《季度投资前景》报告中，分别在作为价值存储、交易方式和分布式账本的三种情况下讨论了比特币。作为一种交易方式，该报告称，从...
-
3
把脉低效之痛，飞算科技为企业数智化转型插上翅膀-存储在线 当前位置：存储在线 > 新闻 > 正文 零代码开发...
-
0
祝融说。 高效的节点会逐步替代相对低效的节点，高效的网络会逐步替代相对低效的网络。 祝融说。...
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK