写在前面

每条SQL语句，mysql都会经过成本和规则的优化，对这个SQL选择对应的一些访问方法和顺序，包括做一些特殊的改写确保执行效率是最优的，然后优化过后，就会得到一个执行计划。
所谓的执行计划，落实到底层，无非就是先访问哪个表，用哪个索引还是全表扫描，拿到据之后如何去聚簇索引回表，是否要基于临时磁盘文件做分组聚合或者排序。

1、通过explain命令得到的SQL执行计划（1）

explain拿到这个SQL语句的执行计划:
explain select * from table

如果是一个简单的单表查询，可能这里就只有一条数据，代表他打算如何访问这一个表。
但是SQL语句极为复杂，可能这里会有很多条数据，因为一个复杂的SQL语句的执行是要拆分为很多步骤的，比如先访问表A，接着搞一个排序，然后来一个分组聚合，再访问表B，接着搞一个连接。
**1、id：**每个SELECT都会对应一个id，就是一个复杂的SQL里可能会有很多个SELECT，也可能会包含多条执行计划，每一条执行计划都会有一个唯一的id。
**2、select_type：**就是这一条执行计划对应的查询是个什么查询类型。
**3、table：**表名，意思是要查询哪个表。
**4、partitions：**是表分区的概念。
**5、type：**针对当前这个表的访问方法，比如说const、ref、range、index、all之类的，分别代表了使用聚簇索引、二级索引、全表扫描之类的访问方式。
**6、possible_keys：**跟type结合起来的，意思就是说你type确定访问方式了，那么到底有哪些索引是可供选择，可以使用的呢，这都会放这里。
**7、key：**就是在possible_keys里实际选择的那个索引。
**8、key_len：**就是索引的长度。
**9、ref：**就是使用某个字段的索引进行等值匹配搜索的时候，跟索引列进行等值匹配的那个目标值的一些信息。
10、rows：是预估通过索引或者别的方式访问这个表的时候，大概可能会读取多少条数。
**11、filtered：**就是经过搜索条件过滤之后的剩余数据的百分比。
**12、extra：**是一些额外的信息。

2、通过explain命令得到的SQL执行计划（2）

不同的SQL语句的执行计划长什么样子：

1、单表查询：
explain select /* from t1

select_type是SIMPLE表示查询类型是很普通的且简单。表名就是t1，all就是全表扫描，这没办法，你完全没加任何where条件，那当然只能是全表扫描了！这里直接会扫描表的聚簇索引的叶子节点，按顺序扫描过去拿到表里全部数据。rows是3457，这说明全表扫描会扫描这个表的3457条数据，说明这个表里就有3457条数据，此时你全表扫描会全部扫描出来。filtered是100%，你没有任何where过滤条件，所以直接筛选出来的数据就是表里数据的100%占比。

2、多表查询：
explain select /* from t1 join t2

这是一个典型的多表关联语句，实际上会选择一个表先查询出来数据，接着遍历每一条数据去另外一个表里查询可以关联在一起的数据，然后关联起来。

一个多表关联的执行计划。首先执行计划分为了两条，也就是会访问两个表，先看他如何访问第一个表的，针对第一个表就是t1，明显是先用ALL方式全表扫描他了，而且扫出了3457条数据。接着对第二个表的访问，也就是t2表，同样是全表扫描，因为他这种多表关联方式，基本上是笛卡尔积的效果，t1表的每条数据都会去t2表全表扫描所有4568条数据，跟t2表的每一条数据都会做一个关联，

extra里说了是Nested Loop，也就是嵌套循环的访问方式。
上面两条执行计划的id都是1，实际上一般来说，在执行计划里，一个SELECT会对应一个id，因为这两条执行计划对应的是一个SELECT语句，所以他们俩的id都是1，是一样。
如果有一个子查询，有另外一个SELECT，那么另外一个SELECT子查询对应的执行计划的id就可能是2了。

3、通过explain命令得到的SQL执行计划（3）

**3、一个包含子查询的SQL语句的执行计划：*EXPLAIN SELECT / FROM t1 WHERE x1 IN (SELECT x1 FROM t2) OR x3 = 'xxxx';

该SQL 主SELECT语句的WHERE筛选条件是依赖于一个子查询的，且除此之外还有一个自己的WHERE筛选条件。

首先，第一条执行计划的id是1，第二条执行计划的id是2，因为SQL里有两个SELECT，主查询SELECT的执行计划的id就是1，子查询SELECT的执行计划的id就是2。
其次，第一条执行计划里，select_type是PRIMARY，不是SIMPLE了，主查询的意思。
对主查询而言，他有一个where条件是x3='xxx'，所以他的possible_keys里包含了index_x3，就是x3字段的索引，但是他的key实际是NULL，而且type是ALL，所以说他最后没选择用x3字段的索引，而是选择了全表扫描。这是为什么呢？其实很简单，可能他通过成本分析发现，使用x3字段的索引扫描xxx这个值，几乎就跟全表扫描差不多，可能x3这个字段的值几乎都是xxx，所以最后就选择还不如直接全表扫描呢。
接着，第二条执行计划，他的select_type是SUBQUERY，也就是子查询，子查询针对的是t2这个表，当然子查询本身就是一个全表查询，但是对主查询而言，会使用x1 in这个筛选条件
type是index，说明使用了扫描index_x1这个x1字段的二级索引的方式，直接扫描x1字段的二级索引，来跟子查询的结果集做比对。

4、Union查询
EXPLAIN SELECT /* FROM t1 UNION SELECT /* FROM t2

union字句默认的作用是把两个结果集合并起来还会进行去重，所以第三条执行计划就是去重。

table是<union 1,2>，这就是一个临时表的表名。
extra里有一个using temporary，也就是使用临时表的意思，他就是把结果集放到临时表里进行去重的。
当然，如果你用的是union all，那么就不会进行去重了。

4、通过explain命令得到的SQL执行计划（4）

id：一个SELECT子句对应一个id，如果有多个SELECT就会对应多个id。但有时候一个SELECT字句涉及多个表，所以会对应多条执行计划，此时可能多条执行计划的id一样。
select_type：SIMPLE、primary和subquery的。
一般如果单表查询或者是多表连接查询，其实他们的select_type都是SIMPLE。
如果是union语句的话，就类似于select /* from t1 union select /* from t2，那么会对应两条执行计划，第一条执行计划是针对t1表的，select_type是PRIMARY，第二条执行计划是针对t2表的，select_type是UNION，这就是在出现union语句的时候，他们就不一样了。
在使用union语句的时候，会有第三条执行计划，意思是针对两个查询的结果依托一个临时表进行去重，第三条执行计划的select_type就是union_result。
如果SQL里有子查询的话，类似select /* from t1 where x1 in (select x1from t2) or x3='xxx'，此时其实会有两条执行计划，第一条执行计划的select_type是PRIMARY，第二条执行计划的select_type是SUBQUERY。

5、复杂SQL查询
EXPLAIN SELECT /* FROM t1 WHERE x1 IN (SELECT x1 FROM t2 WHERE x1 = 'xxx' UNION SELECT x1 FROM t1 WHERE x1 = 'xxx');

他有一个外层查询，还有一个内层子查询，子查询里还有两个SELECT语句进行union操作

第一个执行计划一看就是针对t1表查询的那个外层循环，select_type就是PRIMARY，因为这里涉及到了子查询，所以外层查询的select_type一定是PRIMARY了。
第二个执行计划是子查询里针对t2表的查询语句，他的select_type是dependent subquery。
第三个执行计划是子查询里针对t1表的另外一个查询语句，select_type是dependent union，因为第三个执行计划是在执行union后的查询。
第四个执行计划select_type是union result，因为在执行子查询里两个结果集的合并以及去重

6、更复杂SQL查询
explain select /* from (select x1, count(/*) as cnt from t1 group by x1) as _t1 where cnt > 10;

FROM子句后跟了一个子查询，在子查询里是根据x1字段进行分组然后进行count聚合操作，也就是统计出来x1这个字段每个值的个数，然后在外层则是针对这个内层查询的结果集进行查询通过where条件来进行过滤。

子查询里的那个语句的执行计划，select_type是derived，意思是针对子查询执行后的结果集会物化为一个内部临时表，然后外层查询是针对这个临时的物化表执行的。
这里执行分组聚合时，是使用的index_x1这个索引来进行的，type是index，意思就是直接扫描偶了index_x1这个索引树的所有叶子节点，把x1相同值的个数都统计出来就可以了。

然后外层查询是第一个执行计划，select_type是PRIMARY，针对的table是，就是一个子查询结果集物化形成的临时表，他是直接针对这个物化临时表进行了全表扫描根据where条件进行筛选的。

5、type取值

type的取值

const：
select /* fromt1 where id=110

直接根据主键进行等值匹配查询，那执行计划里的type就会是const，意思就是极为快速，性能几乎是线性的。因为主键值是不会重复的，这个唯一值匹配，在一个索引树里跳转查询，基本上几次磁盘IO就可以定位到了。
EXPLAIN select /* from t1 inner join t2 on t1.id = t2.id

这里是通过两个表的id进行关联查询的。

针对t1表是一个全表扫描，这个是必然的，因为关联的时候会先查询一个驱动表，这里就是t1，他没什么where筛选条件，自然只能是全表扫描查出来所有的数据了。
针对t2表的查询type是eq_ref，而且使用了PRIMARY主键。针对t1表全表扫描获取到的每条数据，都会去t2表里基于主键进行等值匹配，此时会在t2表的聚簇索引里根据主键值进行快速查找，所以在连接查询时，针对被驱动表如果基于主键进行等值匹配，那么他的查询方式就是eq_ref了。
如果正常基于某个二级索引进行等值匹配，type就会是ref。
如果基于二级索引查询的时候允许值为null，那么查询方式就会是ref_or_null。
针对单表查询可能会基于多个索引提取数据后进行合并，此时查询方式是index_merge。
range的话就是基于二级索引进行范围查询。
查询方式是index的时候是直接扫描二级索引的叶子节点，也就是扫描二级索引里的每条数据
all的话就是全表扫描，也就是对聚簇索引的叶子节点扫描每条数据。

6、possible_keys/ref取值

possible_keys****的取值

possible_keys：就是在针对一个表进行查询的时候有哪些潜在可以使用的索引。
比如你有两个索引，一个是KEY(x1, x2, x3)，一个是KEY(x1, x2, x4)，此时要是在where条件里要根据x1和x2两个字段进行查询，那么此时明显是上述两个索引都可以使用的，那么到底要使用哪个呢？
此时就需要通过我们之前讲解的成本优化方法，去估算使用两个索引进行查询的成本，看使用哪个索引的成本更低，那么就选择用那个索引，最终选择的索引，就是执行计划里的key这个字段的值了。
key_len：就是当你在key里选择使用某个索引之后，那个索引里的最大值的长度是多少，这个就是给你一个参考，大概知道那个索引里的值最大能有多长。

ref的取值：

ref ：当查询方式是索引等值匹配时，比如const、ref、eq_ref、ref_or_null这些方式时，此时执行计划的ref字段告诉你的就是：你跟索引列等值匹配的是什么？是等值匹配一个常量值？还是等值匹配另外一个字段的值？
EXPLAIN SELECT /* FROM t1 WHERE x1 = 'xxx'

针对t1表的查询，type是ref方式的，也就是说基于普通的二级索引进行等值匹配，然后possible_keys只有一个就是index_x1，针对x1字段建立的一个索引，而实际使用的索引也是index_x1，毕竟就他一个是可以用的。
key_len是589，意思就是说index_x1这个索引里的x1字段最大值的长度也就是589个字节，其实这个不算是太大，不过基本可以肯定这个x1字段是存储字符串的，因为是一个不规律的长度。

ref字段，它的意思是说，既然你是针对某个二级索引进行等值匹配的，那么跟index_x1
索引进行等值匹配的是什么？是一个常量或者是别的字段？这里的ref的值是const，意思就是说，是使用一个常量值跟index_x1索引里的值进行等值匹配的。EXPLAIN SELECT /* FROM t1 INNER JOIN t2 ON t1.id = t2.id;

此时执行计划里的ref肯定不是const，因为你跟t1表的id字段等值匹配的是另外一个表的id字段，此时 ref的值就是那个字段的名称了。

针对t1表作为驱动表执行一个全表扫描，接着针对t1表里每条数据都会去t2表根据t2表的主键执行等值匹配，所以第二个执行计划的type是eq_ref，意思就是被驱动表基于主键进行等值匹配，而且使用的索引是PRIMARY，就是使用了t2表的主键。

ref：到底是谁跟t2表的聚簇索引里的主键值进行等值匹配呢？是常量值吗？不是，是test_db这个库下的t1表的id字段，这里跟t2表的主键进行等值匹配的是t1表的主键id字段。
rows：就是说你使用指定的查询方式，会查出来多少条数据，
filtered：在查询方式查出来的这波数据里，再用上其他的不在索引范围里的查询条件，又会过滤出来百分之几的数据。
EXPLAIN SELECT /* FROM t1 WHERE x1 > 'xxx' AND x2 = 'xxx'

他只有一个x1字段建了索引，x2字段是没有索引的。

针对t1表的查询方式是range，也就是基于索引进行范围查询，用的索引是index_x1，也就是x1字段的索引，然后基于x1>'xxx'这个条件通过index_x1索引查询出来的数据大概是1987条，接着会针对这1987条数据再基于where条件里的其他条件，也就是x2='xxx'进行过滤。
这个filtered是13.00，意思是估算基于x2='xxx'条件过滤后的数据大概是13%，也就是说最终查出来的数据大概是1987 /* 13% = 258条左右。

7、extra取值

extra的取值：

EXPLAIN SELECT x1 FROM t1 WHERE x1 = 'xxx'

首先他是访问了t1表，使用的是ref访问方法，也就是基于二级索引去查找，找的是index_x1这个索引，这个索引的最大数据长度是456字节，查找的目标是一个const代表的常量值，通过索引可以查出来25条数据，经过其他条件筛选过后，最终剩下数据是100%。

**extra的信息：*Using index是说这次查询，仅仅涉及到了一个二级索引，不需要回表，因为他仅仅是查出来了x1这个字段，直接从index_x1索引里查就行了。如果没有回表操作，仅仅在二级索引里执行，那么extra里会告诉是Using index。
SELECT / FROM t1 WHERE x1 > 'xxx' AND x1 LIKE '%xxx'

此时他会先在二级索引index_x1里查找，查找出来的结果还会额外的跟x1 LIKE '%xxx'条件做比对，如果满足条件的才会被筛选出来，这种情况下，extra显示的是Using index condition。

8、Using where/join buffer

Using where

一般是见于你直接针对一个表扫描，没用到索引，然后where里好几个条件，就会告诉你Using where，或者是你用了索引去查找，但是除了索引之外，还需要用其他的字段进行筛选，也会告诉你Using where。
1、没用索引：

EXPLAIN SELECT /* FROM t1 WHERE x2 = 'xxx'

这里的x2是没有建立索引的，所以此时他的执行计划

针对t1表进行查询，用的是全表扫描方式，没有使用任何索引，然后全表扫描，扫出来的是4578条数据，这个时候大家注意看extra里显示了Using where，意思就是说，他对每条数据都用了WHERE x2 = 'xxx'去进行筛选。最终filtered告诉了你，过滤出来了15%的数据，大概就是说，从这个表里筛选出来了686条数据，就这个意思。

2、一个条件使用索引
如果你的where条件里有一个条件是针对索引列查询的，有一个列是普通列的筛选EXPLAIN SELECT /* FROM t1 WHERE x1 = 'xxx' AND x2 = 'xxx'

针对t1表去查询，先通过ref方式直接在index_x1索引里查找，是跟const代表的常量值去查找，然后查出来250条数据，接着再用Using where代表的方式，去使用AND x2 = 'xxx'条件进行筛选，筛选后的数据比例是18%，最终所以查出来的数据大概应该是45条。

在多表关联的时候，有的时候你的关联条件并不是索引，此时就会用一种叫做join buffer的内存技术来提升关联的性能。EXPLAIN select /* from t1 inner join t2 on t1.x2 = t2.x2

他们的连接条件x2是没有索引的，此时一起看看他的执行计划

因为要执行join，那么肯定是先得查询t1表的数据，此时是对t1表直接全表查询，查出来4578条数据，接着似乎很明确了，就是对每条数据的x2字段的值，跑到t2表里去查对应的数据，进行关联。但是此时因为 t2表也没法根据索引来查，也是属于全表扫描，所以每次都得对t2表全表扫描一下，根据extra提示的Using where，就是根据t1表每条数据的x2字段的值去t2表查找对应的数据了，然后此时会用join buffer技术，在内存里做一些特殊优化，减少t2表的全表扫描次数。

9、Using filesore/Using tempory

**Using filesort：*有时我们在SQL语句里进行排序时，如果排序字段是有索引的，那么其实是直接可以从索引里按照排序顺序去查找数据的。EXPLAIN SELECT / FROM t1 ORDER BY x1 LIMIT 10

这就是典型的一个排序后再分页的语句，他的执行计划如下

这个SQL语句，他是用了index方式访问的，意思就是说直接扫描了二级索引，而且实际使用的索引也是index_x1，本质上来说，他就是在index_x1索引里，按照顺序找你LIMIT 10要求的
10条数据罢了。所以大家看到返回的数据是10条，也没别的过滤条件了，所以filtered是100%，也就是10条数据都返回了。

如果我们排序的时候是没法用到索引的，此时就会基于内存或者磁盘文件来排序，大部分时候得都基于磁盘文件来排序。
EXPLAIN SELECT /* FROM t1 ORDER BY x2 LIMIT 10

x2字段是没有索引的，此时执行计划如下

他基于x2字段来排序，是没法直接根据有序的索引去找数据的，只能把所有数据写入一个临时的磁盘文件，基于排序算法在磁盘文件里按照x2字段的值完成排序，然后再按照LIMIT 10的要求取出来头10条数据。把表全数据放磁盘文件排序的做法真的是相当的糟糕，性能其实会极差的。
如果我们用group by、union、distinct之类的语法的时候，万一你要是没法直接利用索引来进行分组聚合，那么他会直接基于临时表来完成，也会有大量的磁盘操作，性能其实也是极低的。EXPLAIN SELECT x2, COUNT(/*) AS amount FROM t1 GROUP BY x2

这里的x2是没有索引的，所以此时的执行计划如下

这个SQL里只能对全表数据放到临时表里做大量的磁盘文件操作，然后才能完成对x2字段的不同的值去分组，分组完了以后对不同x2值的分组去做聚合操作，这个过程也是相当的耗时的，性能是极低的。
**记住：**在SQL调优的时候，核心就是分析执行计划里哪些地方出现了全表扫描，或者扫描数据过大，尽可能通过合理优化索引保证执行计划每个步骤都可以基于索引执行，避免扫描过多的数据。

Mysql基础(十六)：explain命令SQL执行计划