Mysql查询优化

什么是Explain

Explain被称为执行计划，在语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，模拟MySQL优化器来执行SQL语句，执行查询时，会返回执行计划的信息，并不执行这条SQL。（注意，如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中）。
Explain可以用来分析SQL语句和表结构的性能瓶颈。通过explain的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。

Explain命令扩展

explain extended
在explain的基础上提供一些额外的查询信息，在explian extended执行以后，通过show warnings命令可以得到优化后的查询语句，可以看出优化器做了哪些工作，还可以通过某些数据估算表连接的行数。
explain partitions
用于分析使用了分区的表，会显示出可能用到的分区。

两点重要提示

1. Explain结果是基于数据表中现有数据的。
2. Explain结果与MySQL版本有很大的关系，不同版本的优化器的优化策略不同。

explain主要结果列说明

id列

id列是一个有顺序的编号，是查询的顺序号，有几个 select 就显示几行。id的顺序是按 select 出现的顺序增长的。id列的值越大执行优先级越高越先执行，id列的值相同则从上往下执行，id列的值为NULL最后执行。

select_type列

select_type列的值标明查询的类型：

1. simple：表明当前行对应的select是简单查询，不包含子查询和union
2. primary：表明当前行对应的select是复杂查询中最外层的 select
3. subquery：表明当前行对应的select是包含在 select 中的子查询（不在 from 子句中）
4. derived：表明当前行对应的select是包含在 from 子句中的子查询。
5. union：表明当前行对应的select是在 union 中的第二个和随后的 select
6. union result：表明当前行对应的select是从 union 临时表检索结果的 select

table列

table列的结果表明当前行对应的select正在访问哪个表。当查询的子句中有子查询时，table列是 格式，表示当前的select依赖 id=N结果行对应的查询，要先执行 id序号=N 的查询。当存在 union 时，UNION RESULT 的 table 列的值为<unionN1,N2>，N1和N2表示参与 union 的select 行的id序号。

type列

type列的结果表明当前行对应的select的关联类型或访问类型，也就是优化器决定怎么查找数据表中的行，以及查找数据行记录的大概范围。该列的取值优化程度的优劣，从最优到最差依次为：null>system> const > eq_ref > ref > range > index > ALL。一般来说，要保证查询达到range级别，最好达到ref。

1. range：出现在 in(),between ,> ,<, >= 等操作符中。使用一个索引来查询给定范围的行。
2. index：扫描全表索引（index是从索引中读取的,所有字段都有索引，而all是从硬盘中读取），比ALL要快。

possible_keys

1. possible_keys 表示 MySQL 在查询时, 能够使用到的索引. 注意, 即使有些索引在 possible_keys 中出现, 但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引, 由 key 字段决定.
2. 如果possible_keys列的结果是null，则没有相关的索引。这时，可以通过优化where子句，增加恰当的索引来提升查询性能。

key

此字段是 MySQL 在当前查询时所真正使用到的索引.。如果没有使用索引，则该列是 null。

key_len

这一列表明了在索引里使用的字节数，通过这个值可以大致估算出具体使用了联合索引中的前几个列。

key_len计算规则这里不再赘述，不同的数据类型所占的字节数是不一致的。

ref

这一列表明了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名，如user.user_id

rows

显示此查询一共扫描了多少行. 这个是一个估计值。跟实际的数据行数大部分情况是不一致的。

Extra

额外信息,这一列的取值对优化SQL非常有参考意义。常见的重要取值如下：

1. using index：所有被查询的字段都是索引列(称为覆盖索引),并且where条件是索引的前导列，出现这样的结果，是性能高的表现。
2. using where：被查询的列未被索引覆盖，where条件也并非索引的前导列，表示 MySQL 执行器从存储引擎接收到查询数据,再进行“后过滤”（Post-filter）。所谓“后过滤”，就是先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃。
3. using where Using index：被查询的列被索引覆盖，并且where条件是索引列之一但是不是索引的前导列，也就是没有办法直接通过索引来查询到符合条件的数据
4. null：被查询的列没有被索引覆盖，但where条件是索引的前导列，此时用到了索引，但是部分列未被索引覆盖，必须通过“回表查询”来实现，不是纯粹地用到了索引，也不是完全没用到索引
5. using index condition：与using where类似，查询的列不完全被索引覆盖，where条件中是一个前导列的范围；这种情况未能通过示例显现，可能跟MySQL版本有关系。
6. using temporary：这表明需要通过创建临时表来处理查询。出现这种情况一般是要进行优化的，用索引来优化。创建临时表的情况：distinct，group by，orderby，子查询等
7. usingfilesort：在使用order by的情况下出现，mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据连接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下要考虑使用索引来优化的。

优化思路

1. 首先看 type列的结果，如果有类型是 all 时，表示预计会进行全表扫描（fulltable scan）。通常全表扫描的代价是比较大的，建议创建适当的索引，通过索引检索避免全表扫描。
2. 再来看下 Extra 列的结果，如果有出现 Using temporary 或者 Using filesort 则要多加关注：

• Using temporary，表示需要创建临时表以满足需求，通常是因为GROUP BY的列没有索引，或者GROUP BY和ORDER BY的列不一样，也需要创建临时表，建议添加适当的索引。
• Using filesort，表示无法利用索引完成排序，也有可能是因为多表连接时，排序字段不是驱动表中的字段，因此也没办法利用索引完成排序，建议添加适当的索引。
• Using where，通常是因为全表扫描或全索引扫描时（type 列显示为 ALL 或index），又加上了WHERE条件，建议添加适当的索引。

实战

1. 当数据库字段a类型为字符串，参数为数字时，索引失效,但group by a时索引又会生效；当数据库字段类型为数字，参数为字符类型时，索引不失效
2. 索引查出来的数据超过表的30%的数据，走全表（不绝对，它考虑的因素更多，比如表大小、行数量、IO块大小。）（当select的字段覆盖了索引字段一定会走索引）

参考博文
https://juejin.cn/post/6898356430802255879
https://segmentfault.com/a/1190000008131735