MySQL|MySQL实战（用explain分析sql执行性能） MySQL|mysql

文章图片

如何分析慢sql？在工作中，我们用于捕捉性能问题最常用的就是打开慢查询日志，定位执行效率差的SQL，那么当我们定位到一个SQL以后还不算完事，我们还需要知道该SQL的执行计划，比如是全表扫描，还是索引扫描，这些都需要通过EXPLAIN去完成。EXPLAIN命令是查看优化器如何决定执行查询的主要方法。
需要注意的是，生成的QEP并不确定，它可能会根据很多因素发生改变。MySQL不会将一个QEP和某个给定查询绑定，QEP将由SQL语句每次执行时的实际情况确定，即便使用存储过程也是如此。尽管在存储过程中SQL语句都是预先解析过的，但QEP仍然会在每次调用存储过程的时候才被确定。（QEP：sql生成一个执行计划query Execution plan）
在正式介绍explain的使用之前，我们需要了解一下单表的访问方法有哪些？
单表的访问方法我们先建一个single_table表，方便演示后面的结果

CREATE TABLE single_table ( id INT NOT NULL AUTO_INCREMENT, key1 VARCHAR(100), key2 INT, key3 VARCHAR(100), key_part1 VARCHAR(100), key_part2 VARCHAR(100), key_part3 VARCHAR(100), common_field VARCHAR(100), PRIMARY KEY (id), KEY idx_key1 (key1), UNIQUE KEY idx_key2 (key2), KEY idx_key3 (key3), KEY idx_key_part(key_part1, key_part2, key_part3) ) Engine=InnoDB CHARSET=utf8;

const
通过主键或者唯一二级索引与常数值的等值比较来定位一条记录
例如如下语句

-- 通过主键和常数值进行比较 select * from single_table where id = 400; -- 通过唯一二级索引和常数值进行比较 select * from single_table where key2 = 100;

注意：如果主键或者唯一二级索引的索引列由多个列组成，则只有在索引列中的每一项都与常数进行等值比较时，这个const访问方法才有效（因为只有这样才能保证最多只有一条记录符合条件）

select * from single_table where key2 is null

当执行上述语句的时候，访问方法并不是const，因为唯一二级索引并不限制null值的数量，所以上述语句可能访问到多条记录。那它是什么访问方法？接着往下看
ref
将某个普通的二级索引与常数进行等值比较

select * from single_table where key1 = 'abc'

对于普通的二级索引来说，通过索引列进行等值比较后可能会匹配到多条连续的二级索引记录，而不像主键或者唯一二级索引那样最多只能匹配一条记录，所以ref访问方法比const差。
另外需要注意如下两种情况

不论是普通二级索引还是唯一二级索引，索引列对包含null值的数量并不限制，所以采用key is null 这种形式的搜索条件最多只能使用ref的访问方法，而不是const的访问方法
满足最左前缀原则的等值查询可能采用ref的访问方法

例如如下几条语句

select * from single_table where key_part1 = 'a'; select * from single_table where key_part1 = 'a' and key_part2 = 'b'; select * from single_table where key_part1 = 'a' and key_part2 = 'b' AND key_part3 = 'c';

如果索引列并不全是等值查询的时候，访问方法就不是ref了，为range

select * from single_table where key_part1 = 'a' AND key_part2 > 'b';

ref_or_null
同时找出某个二级索引列的值等于某个常数值的记录，并且把该列中值为null的记录也找出来

select * from single_table where key1 = 'abc' or key1 is null

range
使用索引执行查询时，对应的扫描区间为若干个单点扫描区间或者范围扫描

select * from single_table where key2 in (11, 12) or (key2 >= 30)

上面sql的扫描区间为[11, 11]，[12, 12]，以及[30,+∞)
扫描区间为(-∞, +∞)的访问方法不能称为range
index

select key_part1, key_part2, key_part3 from single_table where key_part2 = 'abc'

可以看到key_part2并不是联合索引最左边的列，所以无法使用ref的访问方法来执行这个语句。但是它有如下两个特点

查询的列为key_part1，key_part2，key_part3 。而索引idx_key_part中包含这3个列的列值
搜索条件只有key_part2列，而这个列也包含在idx_key_part中

此时我们可以直接遍历idx_key_part索引中的所有记录，判断key_part2的值，并返回key_part1，key_part2，key_part3的值，此时扫描区间为(-∞, +∞)
扫描全部二级索引记录比直接扫描全部的聚集索引记录的成本要小很多（因为聚集索引的叶子节点要存所有列以及隐藏列，而二级所以只需要存索引列的列值和主键值，所以树高有可能比较低），这种方法为index
另外当语句添加了order by 主键的时候访问方法也为index
所以当查询满足如下条件时，访问方法为index

扫描全部二级索引记录
添加了order by 主键的语句

all
全表扫描，即直接扫描全部的聚集索引记录

select * from single_table

explain的使用 explain用法很简单，只需要在执行的select语句前加上explain即可

explain select * from teacher

文章图片

类型	描述
id	在一个大的查询语句中，每个select关键字都对应一个唯一的id
select_type	select关键字对应的查询类型
table	表
type	针对单表的访问方法
possible_keys	针对表进行查询时有哪些可以潜在使用的索引
key	实际使用的索引
key_len	实际使用索引的长度
ref	表之间的引用
rows	估算出来的结果记录条数
Extra	额外的信息

下面具体分析一下每个列值的含义
table
我们先构造2个和single_table表一摸一样的表，命名为s1表和s2表，这2个表里各有10000条记录，除id列外其余列都插入随机值。
无论我们的查询有多复杂，里面包含了多少表，到最后也是对单个表进行访问。explain语句输出中的每一行都对应着某个单表的访问方法，table列为该表的表名

explain * from s1

id
建立如下3个表
course表

CREATE TABLE `course` ( `cid` int(3) NOT NULL, `cname` varchar(20) NOT NULL, `tid` int(3) NOT NULL, PRIMARY KEY (`cid`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

文章图片

teacher表

CREATE TABLE `teacher` ( `tid` int(3) NOT NULL, `tname` varchar(20) NOT NULL, `tcid` int(3) NOT NULL, PRIMARY KEY (`tid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

文章图片

teacher_card表

CREATE TABLE `teacher_card` ( `tcid` int(3) NOT NULL, `tcdesc` varchar(20) NOT NULL, PRIMARY KEY (`tcid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

文章图片

查询语句中的每个select关键字都会被分配唯一的id值。对于连接查询来说，一个select关键字后面的from子句可以跟多个表。所以连接查询的执行计划中，每个表都会对应一条记录，但这些记录的id值是相同的
我们来查询课程编号为2或者教师证编号为3的老师信息

SELECT t.* FROM course c, teacher t, teacher_card tc WHERE c.tid = t.tid AND t.tcid = tc.tcid AND ( c.cid = 2 OR t.tcid = 3 )

explain上述SQL后，如下所示

文章图片

id值相同，从上往下顺序执行。出现在前面的为驱动表，出现在后面的为被驱动表
查询教授SQL课程的老师的描述

SELECT tc.tcdesc FROM teacher_card tc WHERE tc.tcid = ( SELECT t.tcid FROM teacher t WHERE t.tid = ( SELECT c.tid FROM course c WHERE c.cname = "sql" ) );

这个SQL是先查询c表，再查询t表，最后查询tc表，执行explain看一下

文章图片

id值不同，id值越大，越优先执行。将上述SQL改为如下形式

SELECT tc.tcdesc FROM teacher t, teacher_card tc WHERE tc.tcid = t.tcid AND t.tid = ( SELECT c.tid FROM course c WHERE c.cname = "sql" )

文章图片

id值有相同，又有不同，id值越大越优先，id值相同，从上往下顺序执行
select_type

名称	描述
simple	查询中不包含union或子查询
primary	对于包含union union all 或者子查询的大查询来说，它是由几个子查询组成的，最左边查询的select type 值为primary
union	对于包含union或者union all的大查询来说，它是由几个小查询组成的，除了最左边的那个小查询以外，其余小查询的select type值为union
union result	mysql使用临时表来完成union的去重工作，针对该临时表的查询的select type为union result
subquery	包含子查询的查询语句不能够转为对应的半连接形式，并且该查询不是相关子查询，查询优化器决定采用该子查询物化的方案来执行该子查询时，该子查询的第一个select关键字对应的select_type为subquery
dependent subquery	包含子查询的查询语句不能够转为对应的半连接形式，并且该查询是相关子查询，该子查询的第一个select关键字对应的select_type为dependent subquery
dependent union	在包含union 或者 union all的大查询中，如果各个小查询都依赖外层查询的话，除了最左边的那个小查询外，其余的select type为dependent union
derived	采用物化的方式执行包含派生表的查询，该派生表对应的自查询的select_type就是derived
materialized	查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的select_type为materialized

SIMPLE：查询中不包含union或子查询

explain select * from teacher

文章图片

PRIMARY：对于包含union union all 或者子查询的大查询来说，它是由几个子查询组成的，最左边查询的select type 值为primary
UNION：对于包含union或者union all的大查询来说，它是由几个小查询组成的，除了最左边的那个小查询以外，其余小查询的select type值为union
UNION RESULT：mysql使用临时表来完成union的去重工作，针对该临时表的查询的select type为union result

SELECT * FROM course WHERE tid = 1 UNION SELECT * FROM course WHERE tid = 2

文章图片

SUBQUERY：包含子查询的查询语句不能够转为对应的半连接形式，并且该查询不是相关子查询，查询优化器决定采用该子查询物化的方案来执行该子查询时，该子查询的第一个select关键字对应的select_type为subquery
（不知道什么是半连接，子查询物化的小伙伴可以参考《MySQL是怎样运行的？》基于规则的优化这一章）

SELECT tc.tcdesc FROM teacher_card tc WHERE tc.tcid = ( SELECT t.tcid FROM teacher t WHERE t.tid = ( SELECT c.tid FROM course c WHERE c.cname = "sql" ) );

文章图片

DEPENDENT SUBQUERY：包含子查询的查询语句不能够转为对应的半连接形式，并且该查询是相关子查询，该子查询的第一个select关键字对应的select_type为dependent subquery

DEPENDENT UNION：在包含union 或者 union all的大查询中，如果各个小查询都依赖外层查询的话，除了最左边的那个小查询外，其余的select type为dependent union

DERIVED：在包含派生表的查询中（下面例子中cr为一个派生表），如果是以物化派生表的方式执行查询，则派生表对应的子查询的select_type就是DERIVED

SELECT cr.cname FROM ( SELECT * FROM course WHERE tid IN ( 1, 2 ) ) cr

文章图片

id为1的table表名为，表明该查询是针对将派生表物化之后的表进行查询的，派生表从id为2的执行过程中来
MATERIALIZED：查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的select_type为materialized

type
执行计划的一条记录代表着mysql对某个表执行查询时的访问方法，type表明了对表的访问方法是啥？
我们前面只介绍了InnoDB引擎中表访问的部分方法，完整的访问方法如下

名称	描述
system	表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，派生表为只有一条数据的子查询
const	根据主键或者唯一二级索引与常数进行等值匹配时
eq_ref	连接查询时，被驱动表是通过主键或者唯一二级索引列进行等值匹配的方式进行访问
ref	普通二级索引与常量值进行等值匹配
ref_or_null	普通二级索引进行等值匹配，索引列值可以为null时
fulltext	全文索引，跳过
index_merge	使用索引合并的方式对表进行查询
unique_subquery	包含in自查询的语句中，如果查询优化器决定将in子查询转换为exists自查询，而且子查询可以使用到主键进行等值匹配
index_subquery	index_subquery和unique_subquery类似，只不过访问子查询中的表时使用的是普通的索引
range	使用索引列获取范围区间的记录
index	对二级索引进行全索引扫描
all	对聚集索引进行全表扫描

常用的执行效率如下所示
const，system>eq_ref>ref>range>index>all
system：

表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory（表中只有一条数据且为InnoDB引擎时为all）
派生表为只有一条数据的子查询

SELECT a.tname FROM ( SELECT * FROM teacher t WHERE t.tid = 1 ) a

文章图片

const：根据主键或者唯一二级索引与常数进行等值匹配时

SELECT * FROM teacher t WHERE t.tid = 1

文章图片

eq_ref：唯一性索引，对于每个键的查询，返回匹配唯一行数据（有且只有一个，不能多，不能0），常见于唯一索引和主键索引，不是必须的有索引，我下面句的例子中teacher表的tcid字段就没有加任何索引，当然是我数据太简单的问题，才能在不建索引的情况下出现eq_ref

SELECT t.tcid FROM teacher t, teacher_card tc WHERE t.tcid = tc.tcid

文章图片

来看看此时表的数据
teacher表

文章图片

teacher_card表

文章图片

此时对于teacher表的tcid这个键，都会返回唯一条数据，所以type为eq_ref，假如说将teacher表增加一条数据（只是针对这个例子临时更改），teacher_card表不变，数据如下
teacher表

文章图片

SELECT t.tcid FROM teacher t, teacher_card tc WHERE t.tcid = tc.tcid

文章图片

可以看到type类型变为了ALL，因为对于teacher表，4这个键，返回了0条数据，不是每个键值都返回了一条数据
上面的例子teacher_card都保持了不变，是为了验证的严密性，如果给teacher_card表增加数据再执行eq_ref的2个例子，其实是没有影响的，因为eq_ref针对的是teacher的键都能返回唯一行数据
ref：非唯一性索引，对于每个索引键的查询，返回匹配所有行（0，多）
修改表为如下（只是针对这个例子临时更改），出现了一个同名的老师张三，并且在teacher表的name列加上普通索引，演示一下匹配行有多个的情况
teacher表

文章图片

teacher_card表

文章图片

SELECT * FROM teacher WHERE tname = "张三"

文章图片

range：检索指定范围的行，where后面是一个范围查询（between，>，<，>=，in有时候会失效，从而转为无索引ALL）

SELECT * FROM teacher WHERE tid < 3

文章图片

index：查询索引中全部数据
对teacher表的name字段建索引（只是针对这个例子临时更改）

SELECT tname FROM teacher

文章图片

对tname建立索引，当我们只查询tid时，它的值已经在B+树的叶子节点上了，不需要回表查询，从索引中就可以拿到，因为非聚集索引，叶子节点存放索引键值，以及该索引键值指向的主键
同时查tname，tcid

SELECT tname, tcid FROM teacher

文章图片

可以看到type为ALL，因为从tname索引中拿不到tcid的数据，只能通过全表扫描
现在我们想同时查询tname和tcid，不想通过回表，只想通过索引表拿到数据，应该怎么建索引呢？现在我们可以肯定的是，只对tname加索引，或者只对tcid加索引肯定是不行的，那么在tname和tcid上都分别加索引呢（只是针对这个例子临时更改）？

文章图片

执行如下sql

SELECT tname, tcid FROM teacher

文章图片

可以看到是ALL，针对这种情况我们得对tname和tcid建联合索引，因为只有联合索引才能拿到tname和tcid的值，还不用回表

文章图片

再次执行

SELECT tname, tcid FROM teacher

文章图片

key使用了联合索引
all：查询表中全部数据

SELECT * FROM teacher

文章图片

possilbe_keys
针对一个表进行查询时，有哪些潜在可以使用的索引。
比如你有2个索引key(x1,x2,x3)和key(x1,x2,x4)，此时在where语句中要根据x1和x2进行查询，很明显2个索引都能使用，那么使用哪个索引呢？
通过我们之前分享的成本优化方法，去估算使用2个索引进行查询的成本，看使用哪个索引的成本更低，就使用哪个索引，最终选择的索引就是key这个字段的值
key
实际使用到的索引
key_len
索引的长度，用于判断联合索引是否被完全使用
建立如下的表，其中name列和address列都建立了索引

CREATE TABLE `teacher` ( `id` int(10) NOT NULL, `name` char(20) NOT NULL, `address` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`), KEY `idx_addr` (`address`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

执行如下命令

explain select * from teacher where name = "张三"

文章图片

explain select * from teacher where address = "北京"

文章图片

问题来了，这些key_len是怎么算出来的呢？
key_len表示索引使用的字节数，根据这个值，就可以判断索引使用情况，特别是在组合索引的时候，判断所有的索引字段是否都被查询用到
字符串类型

文章图片

char和varchar跟字符编码也有密切的联系
latin1占用一个字节，gbk占用2个字节，utf8占用3个字节，utf8mb4占用4个字节（不同字符编码占用的存储空间不同）
字符类型-索引字段为char类型+不可为Null时
char(n)=n*(utf8mb4=4,utf8=3,gbk=2,latin1=1)
所以上面第一个列子（查询name=张三）的key_len为20*3=60
下文中为了描述方便，编码类型默认为utf8
字符类型-索引字段为char类型+允许为Null时
char(n)=n*3+1（允许null，是否为空的标记）
字符类型-索引字段为varchar类型+不可为Null时
varchar(n)=n*3+2（变长列，记录当前数据存了多少）
字符类型-索引字段为varchar类型+允许为Null时
varchar(n)=n*3+1（允许null）+2（变长列）
所以上面第二个例子（查询住址=北京）的key_len为100*3+1+2=303

文章图片

文章图片

datetime类型在5.6中字段长度是5个字节，datetime类型在5.5中字段长度是8个字节
整数/浮点数/时间类型的索引长度
Not Null=字段本身的长度
Null=字段本身的长度+1
ref
对索引列执行等值匹配查询时，也就是单表访问方法在const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery中时，ref列展示的就是与索引列进行等值匹配的值是啥？
先对teacher表的tname字段和course表的tid字段增加索引

SELECT c.cid, t.tname FROM course c, teacher t WHERE c.tid = t.tid AND t.tname = "张三"

文章图片

第一个为const表示常量，即张三，第二个为test2.t.tid，表示couse表引用的是test2库中的t（teacher）表的tid字段
rows
MySQL Query Optimizer通过系统收集到的统计信息估算出来的结果记录条数
filtered
查询方式查出来的数据再用上其他不在索引范围里的查询条件，又会过滤出百分之几的数据
Extra
Using index：当出现索引覆盖时。即所需要的数据，只需要在索引上即可全部获得，而不需要回表查询数据
Using where：如果我们不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现Using where信息。
Using index和Using where前面已经有例子，当出现索引覆盖时，会显示Using index，性能得到了提升，出现Using temporary和Using filesort说明性能损耗比较大
Using temporary：当MySQL某些操作中必须使用临时表时，在Extra信息中就会出现Using temporary。主要常见于GROUP BY和ORDER BY等操作中
Using filesort：排序的时候没办法用到索引，此时就会基于内存或者磁盘文件来排序，大部分时候得基于磁盘文件来排序
将所有数据写入一个临时的磁盘文件，基于排序算法在磁盘文件里完成排序
Using index condition:
对teacher表的name字段建索引（只是针对这个例子临时更改），

SELECT * FROM teacher WHERE tname = "张三" ORDER BY tname

文章图片

SELECT * FROM teacher WHERE tname = "张三" ORDER BY tcid

文章图片

Using join buffer
SQL调优的核心是避免出现全表扫描，尽量使每个步骤都能基于索引执行，避免扫描过多的数据
参考博客 【MySQL|MySQL实战（用explain分析sql执行性能）】[1]https://www.cnblogs.com/gomysql/p/3720123.html
[2]https://blog.csdn.net/lijn_huo/article/details/52442675
[3]http://blog.jobbole.com/100349/
慢查询日志
[4]https://mp.weixin.qq.com/s/_SWewX-8nFam20Wcg6No1Q