mysql explain解读

1. explain工具价绍

翻译：解释的意思，在mysql中，使用explain关键字可以模拟优化器的执行sql语句，从而知道mysql是如何处理你的sql语句，从分析你的sql语句或者表结构问题引起的sql性能瓶颈

2. 用法

mysql > expalin sql语句

3. 执行结果

4. 查询字段解读

4.1 id

mysql查询的序列号，包含一组数字，表示查询中执行select 子句或者操作表的顺序

三种情况：

1. id相同，执行顺序由上到下
   
   mysql内部对关联表查询的执行的顺序分别是t1，t3，t2，而并不是我们写sql语句的顺序t1，t2，t3

2. id不同，如果是子查询，id的序号会递增，id的值越高执行的优先级越高，越先被执行
   
   观察id的顺序是1，2，3，越高越先被执行，实际上的执行顺序也就是先执行最内子查询t3，紧接着是t1，继而t2

3. id相同,不同,同时存在
   
   这种情况稍微复杂一点，观察id顺序1，1，2,首先被执行的肯定是2，对应的是子查询的t3表，接着执行顺序是由上至下，但是它的table是<derived2>这表示，因为查询出来生成一张临时表s1，这个临时表其实是t3的衍生，而且这个<derived2>中的2就是对应id=2的操作t3表的衍生，最后id = 1就是再执行t2

4.2 select type

1. SIMPLE：简单的SQL查询，不包含子查询或者UNION语句，类似于4.1 id情况1图
2. PRIMARY：查询中SQL语句包含负责子查询，最外层的查询则被标记为PRIMIARY，类似于4.1 id情况2图，id = 1的情况
3. SUBQUERY：在select或者where中包含子查询类似于4.1 id情况2图，id = 2，3的情况
4. DERIVED：from中的子查询被标记为DERIVED（衍生）MySQL会递归这些子查询，把它们放入结果集，类似于4.3 id情况2图，id = 2的情况
5. UNION：出现在union语句后半部分
6. UNION RESULT：UNION连接上下select语句的合并查询结果集

4.3 table

MySQL语句执行的表名

4.4 type

指的是访问类型，也是衡量SQL性能的一个非常重要的指标。

常见的主要类型有：

1. ALL：遍历全表以找到匹配的行，通过不加索引，例如：

2. index：遍历索引树，以找到匹配的行，与ALL的相同点就是都是遍历全表,不同点是index是遍历的是索引，例如：id是表的主键索引
   

3. range：在范围内进行扫描，一般不用全表扫描，常出现在使用where条件后面加between,大于小于，例如

4. ref 非唯一性索引扫描或只满足唯一性索引的前缀，返回满足条件的所有行，例如：t1表只匹配到了索引的前缀

5. eq_ref 唯一性索引扫描，对于每一个唯一索引，表中只有一条记录与之匹配，常见于主键或唯一索引，例如：t1此时只通过唯一键索引id只匹配到一条于t2表进行连接

6. const,system system：表中只有一条数据，类似于系统表，是const的特例；const：表示索引一次就找到了，如将逐渐或唯一索引置于where后面，因为只匹配一行数据，所以很快，例如：
   性能从最好到最差依次是：

   const，system > eq_ref > ref > range > index > all

   一般来说，得保证查询至少得达到range级别，最好能达到ref级别

4.5 possible_keys

字面的意思翻译过来就是理论上可能用到的索引，可能是多个，也可能是一个。

也有可能存在理论上没有使用到，实际上用到了；发生在复合索引，理论上没有用到，实际上用到了，这要复合我们的复合索引顺序和select查询的字段要全部匹配，顺序 + 字段 ！

理论上使用到了，实际上没有用到，这就是一些索引失效的情况了。

4.6 key

实际上使用到的索引。

4.7 key_len

索引的字节数，可通过该列计算得出使用的索引长度，在不损失精度的情况下，长度越短越好，但是key_len 显示的值为索引字段的最大可能长度，并非实际使用的长度，即key_len是根据表定义计算得来的，不是通过表内检索出来的

通过观察查询同一张表，查询相同字段，建议查询进度越低越好，这样key_len长度就越小，性能越佳！

4.8 ref

用于匹配那些列可以用于查询索引列上的值，可以是一个字段，也可以是一个常量。

t1上面建立了索引 idx_col1_col2，t2上面没有建立索引，所以当查询t2时，possible_key，key，key_len，ref都为空，但是因为t1上存在索引，并且被充分使用到了key_len = 26，此时使用到了t2的col1列和一个常量去匹配索引了，所以ref的值为shared.t2.col2，const

4.9 rows

检索表所需要读取的行数，越少性能越优。

t1上面是只存在主键索引的，所以第一个查询，在查询t1的是的type走的是eq_ref，并且ref为t2.id，t2由于也只存在主键索引，但是它并没有被用到，而且col1上面也没有索引，所以ref上面没有const，所以ref和key都为空。

在设置完t2的复合索引后，t2可以走索引了，其命中的是新创建的索引，ref取的是常量，rows也有原先的640下降到142。

5.0 Extra

不适合在其他列展示，但是十分重要的信息。

1. Using filesort
   使用了文件内排序，并不是走的索引排序，这种情况下，需要耗费额外的性能来完成文件排序，也是可以优化的地方。
   

2. Using tempoary
   使用了临时表，需要耗费创建临时表的时间，属于必须要进行优化的地方了。
   

3. Using index
   使用的是覆盖索引，用于检索索引上的数据，没有在索引上进行键值的查找
   
   覆盖索引：指的也是索引覆盖，select的字段在索引中能找到，不必去读取数据行，MySQL可以直接利用索引来返回select列表的字段！

需要注意的是，如果使用覆盖索引，一定不能直接使用select * ，只取出所需要的列，因为所有字段一起索引会导致索引文件过大，查询性能下降。

4. Using where
   使用到了索引上的键值查找，一般是在where后面跟索引字段进行查找。可以见3的例子。
5. Using join buffer：使用到了join关键字
6. impossible where：where返回的都是false，例如查询身份证号即等于1又等于2，这种条件是不存在的。
7. select tables optimized away：在没有group by子句的情况下，基于索引优化max / min操作或者对于myisam存储引擎count（*）操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
8. distinct
   优化了distinct的操作，在找到第一匹配的元组后即停止找同样值的操作。

一个例子分析一下sql如何执行

观察id，发现执行顺序应该是4,3,2,1

①：首先被执行的是第四行，由于是被union连接，所以select_type是union，table是t2表

②：执行的是第二行，from后面的嵌套查询，由于在from后，所以select_type是derived，table对应的就是t1表

③：执行的是第一行，是一个子查询，table对应的就是t3表，而且也用到了主键索引，因此type为index

④：执行的是最外层查询，因为有内存嵌套子查询，所以最外层为select_type是primary，它的table是id等于3的衍生表derived3

⑤：最后id = null是一个所有结果集，union的结果集的一个返回，所以select_type是union resutl，并且table是id = 1和id = 4的一个结果

还有一点的是，这东西要在实践中真正实践才能掌握，需要多实践！

视频学习：

https://www.bilibili.com/video/BV1KW411u7vy?p=29