mysql 中的查询计划及 sql 语句性能分析：explain
前期准备：准备相关数据表和测试数据
1、explain 的简介
mysql 中可以使用 explain 这个关键字来获取（查询）sql 语句的查询执行计划的。使用 explain 关键字，可以模拟 mysql 优化器执行的 sql 语句，从而知道 mysql 是如何处理 sql 语句的。通过 explain 可以分析查询语句或表结构的性能瓶颈。
2、explain 的作用
①、查看表的读取顺序
②、数据读取操作的操作类型
③、查看哪些索引可以使用
④、查看哪些索引被实际使用
⑤、查看表之间的引用
⑥、查看每张表有多少行被优化器执行
3、explain 的使用方法
explain sql 语句
示例：explain select * from employee;
4、explain 执行计划输出中的各个列的详解
4.1、id
描述：
select 查询的序列号
包含一组数字，该组数字表示查询中执行 select 子句或操作表的顺序
id 值的三种情况如下：
①、id 相同
示例如下：
分析的结果截图：
从上图中可以看到，id 列的值都是 1。那么该条 sql 语句的执行顺序是由上到下，也就是说 先查询的 c 表 然后查询 e 表 最后查询 d 表。
②、id 不同
示例如下：
分析的结果截图：
从上图中可以看到，id 列的值是 1、2、3。那么该条 sql 语句的执行顺序是*从大到小*（由*下到上*），也就是说 id 列的值是 3 的先执行 其次是 id 列的值是 2 最后是 id 列的值是 1 再执行。
注意：这里都是子查询，如果是子查询，id 的序号会递增，id 值越大优先级越高，优先被执行。
③、id 相同和不同，同时存在
示例如下：
分析的结果截图：
从上图中可以看到，id 列的值是 1、1、2。那么该条 sql 语句的执行顺序是怎样的呢？根据上面的①和②这里应该也能猜到了。该条 sql 语句的执行顺序是 先执行 id 列的值是 2 的，其次执行 id 列的值是 1 的（最上面那个 id 列的值是 1 的，也就是 table 列的值是 d），最后执行中间那个 id 列的值是 1 的。
注意：上图中有一个 select_type 列，其中 select_type 有一列的值是 derived，而 derived 表示 衍生出来的虚表。再次说明，id 值越大，优先级越高，越先执行。
id 列的值总结如下：
相同，顺序走（由上到下）。不同，看谁大，大的先执行。
4.2、select_type
描述：
查询类型，主要用于区别普通查询，联合查询，子查询等复杂查询。
select_type 列的值主要有以下 6 种情况：
①、SIMPLE：简单的 select 查询，查询中不包含子查询或者 UNION
②、PRIMARY：查询中若包含任何复杂的子查询，那么最外层的查询则被标记为 primary
示例截图如下：
③、SUBQUERY：在 select 或 where 中包含了子查询
④、DERIVED：在 from 列表中包含的子查询被标记为 derived(衍生)，把结果放在临时表当中。
示例截图如下：
⑤、UNION：若第二个 select 出现在 union 之后，则被标记为 union。若 union 包含在 from 子句的子查询中，外层 select 将被标记为 deriver。
⑥、UNION RESULT：从 union 表获取结果 select。两个 UNION 合并的结果集在最后。
示例截图如下：
4.3、table
描述：
显示当前查询的数据是关于哪张表的。
4.4、partitions
描述：
如果查询是基于分区表的话，会显示查询访问的分区。
4.5、type（重要）
描述：
表示访问某个表的类型。更专业一点的解释就是：type 代表着 mysql 对某个表的执行查询时的访问方法，其中 type 列的值就表明了这个访问方法是个啥。通过 type 可以知道 mysql 是做了全表扫描还是范围扫描等，从而知道当前的 sql 语句到底要不要去优化。
type 列的值一般最常用的有 7 种，按照最好到最差来排序 分别是：system、const、eq_ref、ref、range、index、ALL。
①、system：表中只有一条记录，并且该表使用的存储引擎的统计数据是精确的，比如 MyISAM、Memory，那么该表的 type 列的值就是 system。这是 const 类型的特例，平时不会出现，也不用奢求将 sql 优化到这种级别的。
②、const：表示通过索引（主键索引或唯一索引）一次就找到了那一条数据。这里和上面那个 system 的区别就是 system 表里面只能有一条数据，而 const 表示表中可能会有多条数据，但是 const 能直接从多条数据中直接定位到那一条数据（通过主键索引或唯一索引）。因为只匹配一行数据，所以 const 速度很快。
示例截图：
③、eq_ref：唯一性索引扫描。对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。还是云里雾里？没关系，上个示例截图一解释就好了。
示例截图如下：
截图 1：
截图 2：
employee 表中有五条数据，而 department 表中有对应的五条数据，其中 employee 的 id（主键索引）和 department 的 id（主键索引）是一 一对应的，所以这里就会出现 eq_ref，eq_ref 也就是这个意思。
注意：eq_ref 基本上很难在单表上出现，一般都是在多表的情况下才会出现 eq_ref。
④、ref：非唯一性索引扫描。大白话解释一下就是：出现该连接类型的条件是， 查找条件列使用了索引而且不是使用的主键索引和唯一索引（unique），使用的是普通索引。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使 使用索引快速查找到了第一条数据，仍然不能停止扫描，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。下面为了演示这种情形，给 employee 表中的 age 列添加一个普通的索引
然后，在 employee 表中根据 age 查找数据的时候，mysql 优化器便选择了 ref
示例截图如下：
⑤、range：指的是有范围的索引扫描，相对于 index 的全索引扫描，它有范围限制，因此要优于 index。关于 range 比较容易理解，需要记住的是出现了 range，则一定是基于索引的。一般就是在你的 where 语句中出现 between，and，<，>，or，in 等查询，那么 type 列的值就是 range
⑥、index：Full Index Scan。index 与 All 区别为 index 类型只遍历索引树，通常比 All 要快，因为索引文件通常比数据文件要小。all 和 index 都是读全表，但 index 是从索引中读取，all 是从硬盘当中读取。
示例截图如下：
⑦、ALL：将表中的所有数据进行了扫描（全表扫描），从硬盘当中读取数据。如果出现了 All 且数据量非常大，那么该条 sql 必须去做优化的。
示例截图：
末尾了，说下要求：一般来说，要保证 SQL 查询至少达到 range 级别，最好能达到 ref 级别。
4.6、possible_keys
描述：
表示这张表中可能会用到的索引（一个或多个），查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用到，可能自己创建了 4 个索引，在实际执行 sql 查询的时候，根据 mysql 内部的自动判断，只使用了 3 个。
4.7、key（重要）
描述：
mysql 在执行的时候实际使用到的索引，如果为 NULL，则没有使用索引。
****
其它说明：
查询中若使用了覆盖索引，则该索引仅出现在 key 列表中。
覆盖索引：查询的字段和建立的字段刚好吻合，这种我们称为覆盖索引。
possible_keys 与 key 关系：前者表示理论应该用到哪些索引，后者表示实际用到了哪些索引。
4.8、key_len
描述：
表示索引中使用的字节数，可通过该列计算查询中使用的索引长度 。下面为了演示这种情形，给 employee 表添加一个复合索引。
示例截图：
4.9、ref
描述：
索引是否被引入到，到底引用到了哪几个索引。
这里就不写加索引的语句了，直接上几张截图看吧
4.10、rows
描述：
根据表的统计信息及索引选用情况，大致估算出找到所需的记录所需要扫描（读取）的行数。表有多少行被优化器查询过。没有建立索引和建立索引之后 rows 所显示的数据肯定是不一样的。这里就不进行截图演示了。
4.11、filtered
描述：
满足查询的记录数量的比例，注意是百分比，不是具体记录数，值越大越好，filtered 列的值依赖统计信息，并不十分准确。对于单表查询来说，这个 filtered 列的值没什么意义，更关注在连接查询中对应的执行计划记录的 filtered 列的值。关于这里的多表 demo 也就先不演示了。
4.12、Extra
描述：
顾名思义，Extra 列是用来说明一些额外信息的，可以通过这些额外信息来更准确的理解 mysql 到底将如何执行给定的查询语句。mysql 提供的额外信息有好几十个，这里就不一个一个介绍了，只挑一些平时常见的或者比较重要的做下说明。
①、Using filesort：专业术语成为“文件排序”。说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行。mysql 无法利用索引完成排序操作称为"文件排序"，当你看到 using filesort 的时候，那么一定要优化该条 sql 语句。（得到所需结果集，需要对所有记录进行"文件排序" 出现这个 表示该条 SQL 语句性能较低，需要进行优化）
****
**关于 filesort 的更多详解：**filesort 并不是说通过磁盘文件进行排序，而只是告诉我们进行了一个排序操作。文件排序是通过相应的排序算法，将取得的数据在内存中进行排序。mysql 需要将数据在内存中进行排序，所使用的内存区域也就是我们通过 sort_buffer_size 系统变量所设置的排序区。这个排序区是每个 Thread 独享的，所以说可能在同一时刻 在 mysql 中可能存在多个 sort buffer 内存区域。
filesort 分两种：
双路排序：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在 sort buffer 中进行排序。排序后再吧查询字段依照行指针取出，共执行两次磁盘 io。
单路排序：是一次性取出满足条件行的所有字段，然后在 sort buffer 中进行排序。 执行一次磁盘 io。
在 mysql4.1 版本之前只有第一种排序算法 双路排序。第二种算法是从 mysql4.1 开始的改进算法，主要目的是为了减少第一次算法中需要两次访问表数据的 IO 操作，将两次变成了一次，但相应也会耗用更多的 sort buffer 空间。当然，mysql4.1 开始的以后所有版本同时也支持第一种算法。
典型说明：在一个没有建立索引的列上进行了 order by，就会触发 filesort，常见的优化方案是，在 order by 的列上添加索引，避免每次查询都全量排序。
Using filesort 示例截图：
②、Using temporary：在许多查询的执行过程中，mysql 可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含 DISTINCT、GROUP BY、UNION 等子句的查询过程中，如果不能有效利用索引来完成查询，mysql 很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的 Extra 列将会显示 using temporary 提示。当你看到 using temporary 的时候，那么一定要优化该条 sql 语句。（需要建立临时表(temporary table)来暂存中间结果，出现这个 表示该条 SQL 语句性能较低，通常情况下需要进行优化）
③、Useing index：表示相应的 select 中使用了覆盖索引，避免访问了表中的数据行，效率很好。如果同时出现了 Using where 表明索引被用来执行索引键值的查找。如果没有同时出现 Using where 表明索引 用来读取数据而非执行查找动作。（SQL 所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录，出现这个 表示该条 SQL 语句性能较好）
示例截图：
using index 示例截图如下：
using where using index 示例截图如下：
④、Using where：说明使用了 where 过滤（SQL 使用了 where 条件过滤数据 需要需要优化该条 SQL 语句 需要配合 explain 结果中的 type（连接类型）来综合判断）
⑤、Using join buffer(Block Nested Loop)：在连接查询执行过程中，当 sql 查询语句不能有效的利用索引加快访问速度，mysql 选择退而求其次，一般会为其分配一块名叫 join buffer 的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法。（需要进行嵌套循环计算 出现这个 表示该条 SQL 语句性能较低，需要进行优化）
打个比方：内层和外层的 type 均为 ALL，rows 均为 4，需要循环进行 4*4 次计算。
典型说明：两个关联表 join，关联字段均未建立索引，就会出现这种情况。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。
⑥、impossible where：where 子句中的值总是 false 获取不到任何数据。出现这种提示通常情况下说明你的 sql 语句有误，请看情况选择是否进行修改相应的 sql 语句。
示例截图如下：
⑦、Using index condition：确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。（出现这个 表示 该条 SQL 语句性能也较高，但不如 Using index）
版权声明: 本文为 InfoQ 作者【乌龟哥哥】的原创文章。
原文链接:【http://xie.infoq.cn/article/a5b27a9995f6b8430b10ff8f3】。文章转载请联系作者。
正在努力寻找offer的大四小菜鸟 2021.03.16 加入
擅长 Hbuilder、VS Code、MyEclipse、AppServ、PS 等软件的安装与卸载 精通 Html、CSS、JavaScript、jQuery、Java 等单词的拼写 熟悉 Windows、Linux、 等系统的开关机 看–时间过得多快，不说了，去搬砖了
评论