MySQL执行计划与索引

·  阅读 402
MySQL官方文档:[http://dev.mysql.com/doc/refman/5.7/en/explain-output.html](http://dev.mysql.com/doc/refman/5.7/en/explain-output.html)
关闭MySQL5.7新特性:对衍生表的合并优化,语句如下:
set session optimizer_switch='derived_merge=off';

一、Explain

是关键字,可以模拟优化器执行SQL语句,分析你的查询语句或者是结构的性能瓶颈;
在select语句之前增加Explain,返回的是执行查询的执行计划信息,而并不是执行这条SQL;如果From中包含子查询,仍然会执行子查询,并将结果放入临时表;
如果执行Explain结果中没有partitions、Extra,请使用:Explain extended 【执行的查询语句】;

二、Explain变种执行

2.1)Explain extended

语法:Explain extended 【执行的查询语句】
会额外显示一些查询优化信息;结合Show warnings可以得到优化后的查询语句(MySQL的自我优化);从而看出优化器优化了什么;额外还有filtered列,是一个半分比的值,rows * filtered/100可以估算出将要和Explain中前一个表进行的连接数(前一个表是指explain中ID值比当前ID值小的表);

2.2)Explain partitions

相比explain多了一个partitions字段,如果查询是基于分区表的话,此处会显示访问的分区;

三、结果说明

**id列:**
是Select的序列号,有几个select就有几个ID,并且ID的顺序是按Select出现的顺序依次增长,ID越大执行级别越高;
**select_type列:**
标识对应的是简单还是复杂的查询;
simple:表示简单查询,查询不包括union;
primary:表示复杂查询中最外层的select,也被成为衍生表是基于某个查询生成的,需要看Table列中最后的ID;
subquery:表示在select中的子查询(不在from子句中);
derived:表示包含在from子句中的子查询,MySQL会将结果存放在一个临时表中,也称为派生表;
**table列:**
表示正在访问的那个表,当From子句中有子查询时,此列是格式,表示当前查询依赖id=N的查询,于是先执行id=N的查询;
当有union时,UNION RESULT的此列值为,1和2表示参与union的select行Id;
**partitions列:**
显示分区信息;
**type列:**
表示关联类型或访问类型,即MySQL决定如何查找表中的行,查找数据行记录的大概范围;
从最优到最差分别为:system>const>eq_ref>ref>range>index>all
一般来说最低达到range级别,最好达到ref
NULL表示MySQL能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引;
**possible——keys列:**
显示查询可能使用哪些索引来查找;
**key_len列:**
显示在索引里使用的字节数,通过这个值可以算出具体使用了索引中的哪些列;
  • 字符串
char(n):n字节长度;
varchar(n):utf-8长度为3n+2字节,加的2个字节是用来存储字符串的长度;
  • 数值
tinyint:1字节;
smallint:2字节;
int:4字节;
bigint:8字节;
  • 时间
date:3字节;
timestamp:4字节;
datetime:8字节;
  • 如果字段允许Null,需要多出1个字节来记录是否为Null;
**索引的最大长度是768字节,当内容超过这个容量的时候,mysql或做一个类似左前缀索引的处理,将前半部分的内容提取出来做索引;**
**key列:**
显示实际采用哪个索引来优化对该表的访问,如果没有使用索引,则该列是NULL;
**ref列:**
显示在key列记录中的索引,表查找值所用到的列或常量;
**rows列:**
显示估计要读取并检测的行数,这里不是结果集里的行数;
**Extra列:**
**信息内部受版本等各个方面的影响,是不确定的;**
额外信息,常见的总要值如下:
1)Using index:使用了覆盖索引,**覆盖索引并不是一种索引,是一种查询方式。代表查询结果字段,在索引树中全部包含了,可以直接从索引树中获取数据,不需要先进行获取ID,再进行回表操作获取其他数据了;**
2)Using where:使用了Where语句来处理结果,查询的列未被索引覆盖,说明没有走索引,一般这个情况是需要优化的;
3)Using index condition:查询的列不完全被索引覆盖,where条件中是一个前导列的范围,即:条件是在索引范围内的;
4)Using temporary:需要创建临时表来处理查询,出现这种情况一般是需要进行优化的,通常情况下是可以优化成“Using index”的,加上一个字段索引,变成字段覆盖索引即可,覆盖索引是依赖索引树来进行的,例如:"select distinct name from user"这条语句,如果name字段没有在同一个所索引树中创建,那么就会出现“Using temporary”因为在查询的时候会将所有结果放到临时表中进行处理筛选的,如果结果在索引树中,在查询的时候就会做筛选处理,因为索引树“**是一个排好序的数据结构**”;
5)Using filesort:将使用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘完成排序,这种情况下一般是需要考虑使用索引优化的;
6)select tables optimized away:使用某些聚合函数Max、min等来访问存在的索引的某个字段;
  • explain extended
会在Explain的基础上额外提供一些查询优化的信息。通过Show warnings命令可以得到优化后的查询语句,从而得出优化器优化了什么
  • explain partitions
多了partitions字段,如果查询时基于分区表,会显示查询访问的分区;

四、注意点

4.1)最左前缀法则

在语句编写过程中,只要使用了联合索引中的字段,可以不遵守最左前缀法则,因为MySQL底层会进行一些简单的优化,为你调整查询语句的顺序,但是既然知道了索引的顺序,这点事情还是自己做比较好;

4.2)不在索引列上做任何运算操作

不要在索引列上做任何操作,例如:计算、函数、类型转换(自动或手动),这样会导致索引失效而转向全表扫描;因为索引是一个已经排好序的数据结构;如果加了运算及其他处理,那么就不能从索引中直接提取出来,从而导致全表扫描;
通俗说明,先将数据全部取出,再对数据进行函数或者运算,因此会导致全表扫描或者临时表的创建;

4.3)Like

结合索引树的结构“**一个已经排好序的数据结构**”来分析;
如果‘%’在前,将无法定位到具体的位置,不能通过索引来进行查找;
如果‘%’在后,可以使用索引内容的前缀,可以通过索引来进行查找,在MySQL底层中是等值于常量查询的;
优化方式:可以使用覆盖索引的原理来进行优化,查询的结果尽量为联合索引的内容值,且尽量精简;

4.4)加了索引也不一定会执行

MySQL在做执行SQL的时候会进行一些列的评估,如果评估出来的成本值来进行选择,当全表扫描的成功比索引扫描的成本低或相差无几,那么MySQL会直接全表扫描,不会进行索引扫描;

4.5)多个索引条件中出现了范围,后续不会在走索引

在索引字段索引查询的时候,由于索引树是一个已经排好序的数据结构,一旦遇到范围查找,后一个字段是不能判断是否是有序的,因此不会将范围查找后的数据进行范围查找,正常情况下,是将满足范围查询及之前的条件全部都拿出来,然后在这个结果集中进行进行筛选;

五、索引使用总结

假设已有索引:index(a,b,c);
Where语句
索引是否被使用
where a=3
是,使用了a
where a=3 and b=5
是,使用了a,b
where a=3 and b=5 and c=4
是,使用了a,b,c
where b=3、where b=3 and c=4、where c=4
没有
where a=3 and c=5
使用到了a,但是c不可以,因为b中间断了
where a=3 and b>4 and c=5
使用到了a与b,c不能用在范围之后,b断了
where a=3 and b like 'kk%' and c=4
是,使用到了a,b,c
where a=3 and b like '%kk' and c=4
是,只用到了a
where a=3 and b like '%kk%' and c=4
是,只用到了a
where a=3 and b like 'k%kk%' and c=4
是,使用到了a,b,c

五、常见问题

5.1)假设语句:select * from table

table中已经创建了索引,且table表仅有id, t_name;在查询的时候possible_keys有显示,但是效率还是很低;
是因为并非走了索引,是在索引中提取了遍历了所有的叶子节点;

5.1)ONLY_FULL_GROUP_BY报错

select version(), @@sql_mode;SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
分类:
后端
标签: