一、初识执行计划

获取sql执行计划的语法：explain yoursql
image.png

字段名 作用
id sql中的每一个select都有一个对应的id，对子查询来说，有多个select，就有多个id。
select_type sql执行计划对应的查询类型。如：
1.针对单表查询或者是多表连接查询的select_type是simple。
2.union语句针对前半部分sql的select_type为primary，针对后半部分sql的select_type为union，做去重时的select_type是union result。
3.sql中出现子查询时，外层的select_type为primary，内层的select_type一般为subquery
table sql要查询的表
partitions 分区表
type 针对该表查询时的查询方式。如：
1.聚簇索引：const
2.二级索引的查询：ref（ref、eq_ref、ref_or_null）、range
3.对二级索引的全表扫描：index
4.对聚簇索引的全表扫描：all
possible_keys 有哪些索引可以选择
key 实际选择的索引
key_len 索引的长度
ref 和上面选中key进行比较时，是等值匹配（const）还是其他的字段（库名.表名.列名）
rows 估算的可能会读取的数据条数
filtered 过滤比例，真实数据*过滤比例为预计将读取出来的数据
extra 额外的说明数据，如：
1.sql中有where条件时，Extra为：Using Where
2.sql中使用二级索引时，Extra为：Using Index
3.sql中有join语句多表关联时，Extra为：Using join buffer(Block Nested Loop)
4.sql操作产生临时表时，Extra为：Using temporary
5.sql需要根据某个字段排序，且内存不够时（不管是不是索引），Extra为：Using filesort

1.1 统计的行数为什么是估值？

总行数大概有10W行，但用explain去分析sql的时候，就会发现只得到了9.4W，为啥行数只是个近视值呢？
MySQL中数据的单位都是页，MySQL又采用了采样统计的方法，采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
我们数据是一直在变的，所以索引的统计信息也是会变的，会根据一个阈值，重新做统计。

1.2 索引一定会走到最优索引么？

MySQL索引可能走错。如果走A索引要扫描100行，B索引只要20行，但是他可能选择走A索引，你可能会想MySQL是不是有病啊，其实不是的。
一般走错都是因为优化器在选择的时候发现，走A索引没有额外的代价，比如走B索引并不能直接拿到我们的值，还需要回到主键索引才可以拿到，多了一次回表的过程，这个也是会被优化器考虑进去的。
他发现走A索引不需要回表，没有额外的开销，所有他选错了。
如果是上面的统计信息错了，那用analyze table tablename 就可以重新统计索引信息了，所以在实践中，如果你发现explain的结果预估的rows值跟实际情况差距比较大，可以采用这个方法来处理。
还有一个方法就是force index强制走正确的索引，或者优化SQL，最后实在不行，可以新建索引，或者删掉错误的索引。

二、常见的执行计划的type

2.1 const

2.1.1 查询聚簇索引

  1. explain select * from t1 where id = 5000;

image.png

2.1.2 查询唯一的二级索引

-- t3.x1 是unique key 
explain select * from t3 where x1 = 'qweqwe';

image.png

2.2 ref

2.2.1 对非唯一的二级索引进行检索

explain select * from t1 where x1 = 'ucshiuhdaiusd';

image.png

2.2.2 多个二级索引的等值匹配

explain select * from t1 where x1 = 'ucshiuhdaiusd' and x3 = 'qdasdsadas';

image.png

2.2.3 使用普通索引的做join操作，被join的表的查询type也是ref

explain select * from t1 inner join t2 on t1.x1 = t2.x1;

image.png

2.3 eq_ref

2.3.1 使用主键进行join，被join的表的查询type为eq_ref

explain select * from t1 inner join t2 on t1.id = t2.id;

image.png

2.4 eq_or_null

2.4.1 对普通二级索引进行检索，并且二级索引允许存在null的情况

explain select * from t1 where x1 = 'ucshiuhdaiusd' or x1 is null;

image.png

2.5 range

2.5.1 基于二级索引进行对一个范围进行检索

explain select * from t1 where id > 5000;

image.png

2.6 index

-- t3表中有3个索引，如下： # id:聚簇索引 # x1:唯一的二级索引 # x1_x3_x2:联合索引 
explain select * from t3 where  x2 = 'fdc1a9f7d94ece2b68b7d3e3be1b0f3b';

x2列没有单独的索引。但是sql的执行计划选择去联合索引树中扫全表，也不会去聚簇索引中全表扫描。
image.png

三、详解ref列

3.1 ref之: 库名.表名.列名

explain select * from t1 inner join t2 on t1.id = t2.id;

当执行第二个查询计划时，对主键ID进行等值匹配，且使用test.t1.id 来和ID进行等值匹配（而不是某个常量）image.png

3.2 ref之: const

explain select * from t1 where x1 = 'qwdasdas';

image.png

四、Extra 列

4.1 Using where

explain select * from t1 where x1 = 'ucshiuhdaiusd' and x3 = 'qdasdsadas';

4.2 Using Index

explain select  x1  from t1;

4.3 Using filesort

其中的x1是二级索引，x2是普通索引。
image.png

4.4 Using temporary—表示：使用了中间表

当你的sql中针对非索引的列进行 group by、distinct、union操作时，它都会通过一张中间表去完成指定操作。而如果对索引列进行group by、distinct、union时，会直接使用索引完成我们的操作。
Case1:

--  x1是二级索引
mysql> explain select distinct x1  from t1;
--  x2是普通列
mysql> explain select distinct x2  from t1;

image.png
Case2:

--  x1是索引列
explain select x1,count(*)  from t1 group by x1;
--  x2是普通列
explain select x2,count(*)  from t1 group by x2;

image.png

五、分析执行计划

5.1 分析联表SQL的执行计划image.png

SQL执行时，会先将驱动表t1中的数据以全表扫描的方式检索出来放在内存中，一共检索2行。然后在将t2表中的数据检索出来，和t1中的数据join在一起作为返回值。由于没有加任何where条件，这里还会存在一个笛卡尔积，也就是说结果中会有58条数据。

执行计划解析：

  • id：每一个select 关键字对应一个id，这条SQL中只有一个select，所以这两行执行计划的id都是1
  • select_type：均是simple 简单的查询方式。
  • type：ALL表示全表扫描
  • possible_keys：可能使用到的索引，null表示，没有任何索引
  • key：null表示实际上也没有使用到索引
  • ref：当你使用到索引时，索引列是等值匹配还是其他的连接方式，由于我们都没有索引，所以直接为null
  • rows：估算的扫描行数
  • filterd：过滤比例，实际数量*过滤比例 ≈ 本次查询返回的行数

5.2 分析子查询SQL的执行计划

image.png

执行计划解析：

id：有两个select 语言，所以执行计划的id有两个
select_type：第一行查询语句的查询类型是Primary，主查询。
第二条查询SQL的类型是Subquery，自查询。
对于主查询而言，where条件中有or x3 = 123 （x3我们创建了索引）说明他有可用的索引：t1_x3_index 。但是它最终并没有使用这个索引，它使用的全表扫描ALL的查询方式。所以对应的key_len（最长的索引长度为null）。预估全表扫描出9987条数据。主查询之所以有x3索引却不用，是因为MYSQL认为，使用这个索引和不用索引的层本查不多。
对于自查询来说，它的查询类型是自查询。它选择使用t2表的x1这个二级索引，最长的索引长度为515byte，预估扫描14948条数据。

5.3 分析union SQL的执行计划

 -- 联合t1、t2并对union的结果进行去重！ 
 explain select * from t1 union select * from t2;

image.png

执行计划解析：

第一行是对t1的查询，为主查询，因为sql中没有任何查询条件，所以Extra、possible_key、key都为NULL，并且预计全表扫描9987行数据。
第二行不再赘述。
第三行的查询类型为：Union result，针对表进行操作，Extra中的Using tmporary表明这是一个临时表，也就是说，结果集放到临时表中进行去重。

5.4 分析复杂SQL的执行计划

-- 查询x1列重复次数超过1次以上的x1列以及它的重复次数。
explain select * from (select x1,count(*) as cnt from t1 group by x1) as _t1 where cnt > 1;

image.png

执行计划解析：

id为2的执行计划，是内层针对t1表的查询，并且最终选择索引：t1_x1_index。注意它的select_type是Derived表示派生，意思是它的查询结果是会被物化成一个临时表给外层的sql使用。
id为1的为外层，是类型为primary的主查询，查询自查询生成的临时表 ，查询方式是全表扫描。

六、成本计算

SQL的执行是有成本的，比如读取磁盘的操作远比内存中的操作高的多。而执行计划的作用就是选择一个低成本执行方式去执行我们的SQL。下面看一种简单的计算SQL执行成本的方式：
image.png
通过如上面的命令可以得到mysql替我们维护的表的统计数据。其中的rows并不准确，为估算值。data_length/1024/16 为数据页的数量。
全表扫描的成本计算方法如下：SQL的执行成本 约为 数据页的数量1.0 + 数据行数0.2