MySQL执行计划与索引

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';

是关键字，可以模拟优化器执行SQL语句，分析你的查询语句或者是结构的性能瓶颈；

在select语句之前增加Explain，返回的是执行查询的执行计划信息，而并不是执行这条SQL；如果From中包含子查询，仍然会执行子查询，并将结果放入临时表；

如果执行Explain结果中没有partitions、Extra，请使用：Explain extended 【执行的查询语句】；

语法：Explain extended 【执行的查询语句】

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

相比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字段，如果查询时基于分区表，会显示查询访问的分区；

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

不要在索引列上做任何操作，例如：计算、函数、类型转换（自动或手动），这样会导致索引失效而转向全表扫描；因为索引是一个已经排好序的数据结构；如果加了运算及其他处理，那么就不能从索引中直接提取出来，从而导致全表扫描；

通俗说明，先将数据全部取出，再对数据进行函数或者运算，因此会导致全表扫描或者临时表的创建；

结合索引树的结构“**一个已经排好序的数据结构**”来分析；

如果‘%’在前，将无法定位到具体的位置，不能通过索引来进行查找；

如果‘%’在后，可以使用索引内容的前缀，可以通过索引来进行查找，在MySQL底层中是等值于常量查询的；

优化方式：可以使用覆盖索引的原理来进行优化，查询的结果尽量为联合索引的内容值，且尽量精简；

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

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

假设已有索引：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

table中已经创建了索引，且table表仅有id, t_name；在查询的时候possible_keys有显示，但是效率还是很低；

是因为并非走了索引，是在索引中提取了遍历了所有的叶子节点；

select version(), @@sql_mode;SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));