【MySQL】十六、揭秘MySQL性能优化：执行计划深度解析！

本文主要介绍MySQL数据库的必须要掌握的执行计划的相关知识

以MySQL单表查询为例，看看执行计划包含哪些内容

• 假设有一个SQL select * from table where id = x, 或者select * from table where name = x的语句，直接就可以通过聚簇索引或者二级索引+聚簇索引回源，轻松查到你要的数据，这种根据索引直接可以快速查找数据的过程，在执行计划里称之为const，意思就是性能超高的常量级的。

  所以你以后再执行计划里看到const的时候，就知道他就是直接通过索引定位到数据，速度极快，这就是const的意思。

  但是这里有一个要点，你的二级索引必须是唯一索引，才是属于const方式的，也就是说你必须建立unique key唯一索引，保证一个二级索引的值都是唯一的，才可以。

• 那么如果你是一个普通的二级索引呢？就是个普通的KEY索引，这个时候如果你写一个select * from table where name=x的语句，name是个普通二级索引，不是唯一索引，那么此时这种查询速度也是很快的，他在执行计划里叫做ref。

  如果你是包含多个列的普通索引的话，那么必须是从索引最左侧开始连续多个列都是等值比较才可以是属于ref方式，就是类似于select * from table where name=x and age = x and xx=xx，然后索引可能是个KEY(name,age,xx)。

  然后一个例外，就是如果你用name IS NULL这种语法的话，即使name是主键或者唯一索引，还是只能走ref方式，但是如果你是针对一个二级索引同时比较了一个值还是限定了IS NULL，类似于select * from table where name = x or name IS NULL，那么此时在执行计划里就叫做ref_or_null。

  说白了，就是在二级索引里搜你要的值以及是NULL的值，然后再回源去聚簇索引里查罢了，因为同时有索引等值比较和NULL值查询，就叫做ref_or_null了。

  所以，假设你以后再分析执行计划的时候看到了const，意思是通过主键或者唯一索引的访问，速度超高。

  如果你看到了ref，就是用了普通的索引，或者用主键/唯一索引搞了一个IS NULL/ IS NOT NULL。

• 比如写一个SQL是select * from table where age >= x and age <=x，假设age就是一个普通索引，此时必然利用索引来进行范围筛选，一旦利用索引做了范围筛选，那么这种方式就是range。

• 假设我们有一个表，里面完整的字段联合索引是KEY(x1,x2,x3)，现在我们写一个SQL语句是select x1,x2,x3 from table where x2=xxx。这个SQL是没办法直接从联合索引的索引树的根节点开始二分查找，快速一层一层跳转的，那么他会怎么执行呢？其实这个SQL里要查的几个字段，就是联合索引里的几个字段。针对这种SQL，在实际查询的时候，就会直接遍历KEY(x1,x2,x3)这个联合索引的索引树的叶子节点，聚簇索引的叶子节点放的是完整的数据页，里面包含完整的一行一行的数据，联合索引的叶子节点放的是页，但是页里每一行就x1、x2、x3和主键的值。

  所以此时针对这个SQL，会直接遍历KEY(x1,x2,x3)索引树的叶子节点的那些页，一个接一个的遍历，然后找到x2=xxx的那个数据，就把里面的x1,x2,x3三个字段的值直接提取出来就可以了，这个遍历二级索引的过程，要比遍历聚簇索引快多了，毕竟二级索引叶子节点就包含几个字段的值，比聚簇索引叶子节点小多了，所以速度也快。

  也就是说，此时只要遍历一个KEY(x1,x2,x3)索引就可以了，不需要回源到聚簇索引去，针对这种只要遍历二级索引就可以拿到你想要的数据，而不需要回源到聚簇索引的访问方式，就叫做index访问方式。

重温写出各种SQL语句的时候会用什么执行计划

目前已经了解了const、ref、range、index几种执行计划里的访问方式；const、ref和range本质都是基于索引查询，只要你索引查出来的数据量不是特别大，一般性能都极为高效；index稍微次一点，需要遍历某个二级索引，但是因为二级索引比较小，所以遍历性能也还可以。

最差的一种就是all了，all的意思就是直接全表扫描，扫描你的聚簇索引的所有叶子节点，也就是一个表里一行一行数据去扫描，如果一个表就几百条数据还好，如果有几万条，或者几十万，几百万条数据，那全表扫描就基本得跪了

假设有一个SQL语句 select * from table where x1=xx and x2>=xx，这个SQL语句要查一个表，用了x1和x2两个字段，此时有人可能会说了，要是对x1和x2建立一个联合索引，那不就直接可以通过索引去扫描了? 但是万一要是你建的索引是2个呢？比如(x1,x3)和(x2,x4)，你建了两个联合索引，此时你这个SQL只能选择其中一个索引去用，此时会选择哪个呢？这里MySQL负责生成执行计划的查询优化器，一般会选择在索引里扫描行数比较少的那个条件。

比如说x1=xx，在索引里只要做等值比较，扫描数据比较少，那么可能就会挑x1的索引，做一个索引树的查找，在执行计划里，其实就是一个ref的方式，找到几条数据之后，接着做一个回表，回到聚簇索引里去查出每条数据的完整数据，接着加载到内存里，根据每条数据的x2字段的值做筛选。

MySQL是如何基于各种规则去优化执行计划的

有的时候MySQL可能会觉得你写的SQL一点都不好，直接按你的SQL生成的执行计划效率还是不够高，会自动帮你改改。

首先，要是MySQL觉得你的SQL里有很多括号，那么无关紧要的括号他会给你删除了，其次比如你有类似于i=5 and j > i 这样的SQL，就会改写为i=5 and j > 5，做一个常量替换。

还有比如x=y and y = k and k = 3 这样的SQL，都会给你优化成x=3 and y=3 and k = 3，本质也是做个常量替换，或者是类似于什么 b = b and a = a 这种一看就是乱写的SQL，是没有意义的，直接给你删了。

还有一些比较有意思的改写，比如下面的SQL语句：

select * from t1 join t2 on t1.x1 = t2.x1 and t1.id=1

这个SQL明显是针对t1表的id主键进行了查询，同时还要跟t2表进行关联，其实这个SQL语句就可能在执行前就先查询t1表的id=1的数据，然后直接做一个替换，把SQL替换为：

select t1表中id=1的那行数据的各个字段的常量值，t2.* from t1 join t2 on t1表里x1字段的常量值=t2,x1

上面的SQL就是直接把t1相关的子弹都替换成了提前查出来的id=1那行数据的字段常量值了。

如果有类似下面的SQL语句：

select * from t1 where x1 = (select x1 from t2 where id = xxx)

这就是一个典型的子查询，也就是说上面的SQL语句在执行的时候，其实会被拆分为两个步骤，第一步骤先执行子查询，也就是：select x1 from t2 where id = xxx，直接根据主键定位出一条数据的x1字段的值。接着再执行select * from t1 where x1=子查询的结果值。

另外一种子查询就是：select * from t1 where x1 = (select x1 from t2 where t1.x2 = t2.x2)

这种时候，你会发现子查询里的where条件依赖于t1表的字段值，所以这种查询就会效率很低下，他需要遍历t1表里每一条数据，对每一条数据取出x2字段的值，放到子查询里去执行，找出t2表的某条数据的x1字段的值，再放到外层去判断，是否符合跟t1表的x1字段匹配。

另外一种子查询就是：select * from t1 where x1 in (select x2 from t2 where x3 = xxx)

这个SQL语句就是典型的一个子查询运用，子查询查一波结果，然后判断t1表哪些数据的x1值在这个结果集里。这个SQL的执行计划会被优化为，先执行子查询，也就是select x2 from t2 where x3=xxx 这条SQL语句，把查出来的数据都写入一个临时表里，也可以叫做物化表，意思就是说把这个中间结果集进行物化。

这个物化表可能会基于memory存储引擎来通过内存存放，如果结果集太大，则可能采用普通的B+树聚簇索引的方式放在磁盘里，但是无论如何，这个物化表都会建立索引。

此时，假设t1表的数据量是10万条，而物化表的数据量只有500条，那么此时完全可以改成全表扫描物化表，对每个数据值都到t1表里根据x1这个字段的索引进行查找，查找物化表的这个值是否在t1表的x1索引树里，如果在的话，那么就符合条件了。

透彻研究通过explain命令得到的SQL执行计划

我们现在应该都知道，每条SQL语句mysql都会经过成本和规则的优化，对这个SQL选择对应的一些访问方法和顺序，包括做一些特殊的改写确保执行效率是最优的，然后优化过后，就会得到一个执行计划。

所谓的执行计划，落实到底层，无非就是先访问哪个表，用哪个索引还是全表扫描，拿到数据之后如何去聚簇索引回表，是否要基于临时磁盘文件做分组聚合或者排序，其实这个计划到最后就是这点东西。

平时我们只要用类似于: explain select * from table，这种SQL前面加一个explain命令，就可以轻松拿到这个SQL语句的执行计划。

1、当你执行explain命令之后，拿到的执行计划可能是类似下面这样的东西：

id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra
---------------------------------------------------------------------------------------
1 |SIMPLE     |NULL  |NULL     |NULL|NULL         |NULL|NULL  |NULL|NULL|NULL    |No tables used|

第一行中的id、select_type、table、partitions、type之类的东西就是所谓的执行计划里包含的东西，a值来说，如果是一个简单的单表查询，可能这里就只有一条数据，也就是代表了他是打算如何访问这一个表而已。

但是如果你的SQL语句极为的复杂，可能这里会有很多条数据，因为一个复杂的SQL语句的执行是要拆分为很多步骤的，比如先访问表A，接着搞一个排序，然后来一个分组聚合，再访问表B，接着搞一个连接，类似这样子。

接下来就看一下这个所谓的执行计划里包含的各个字段都是什么意思：

• 首先id这个东西，就是说每个SELECT都会对应一个id，其实就是一个复杂的SQL里可能会有很多个SELECT，也可能会包含多条执行计划，每一条执行计划都会有一个唯一的id。
• select_type：说的就是这一条执行计划对应的查询是个什么查询类型
• table：就是查询要查哪个表
• partitions：是表分区的概念
• type：这个比较关键，针对当前这个表的访问方法，比如说const、ref、range、index、all之类的，分别代表了使用聚簇索引、二级索引、全表扫描之类的访问方式。
• pssible_keys：这个也很关键，他是跟type结合起来的，意思就是说你type确定访问方式了，那么到底有哪些索引是可供选择，可以使用的，都会放在这里。
• key：就是在possible_keys里实际选择的那个索引。
• key_len：就是索引的长度
• ref：就是使用某个字段的索引进行等值匹配搜索的时候，跟索引列进行等值匹配的那个目标值的一些信息
• rows：预估通过索引或者别的方式访问这个表的时候，大概可能会读取多少条数据
• filtered：就是经过搜索条件过滤之后的声誉数据的百分比
• extra：一些额外信息，不太重要。

2、接下来先看第一条非常简单的SQL：

explain select * from t1

就这么一个简单的SQL语句，假设他这个表里面大概有一些条数据，此时执行计划看起来如下图所示：

首先，id是1，这个不用管他，select_type是SIMPLE，这个表示查询类型。table是aa，type是all，就是全表扫描，因为你没有加任何where条件，只能全表扫描了。

如果了解底层访问方式就会知道，这里直接会扫描表的聚簇索引的叶子节点，按照顺序扫描过去拿到表里全部数据。

rows是71，说明全表扫描会扫描这个表里的71条数据，说明这个表里就有71条数据，此时你全表扫描会全部扫描出来；filtered是100%，这个也很简单，你没有任何where过滤条件，所以直接筛选出来的数据就是表里数据的100%占比。

3、接着再看一个SQL语句的执行计划：

explain select * from t1 join t2

这是一个典型的多表关联语句，这种关联语句，实际上会选择一个表先查询出数据，接着遍历每一条数据去另外一个表里查询可以关联在一起的数据，然后关联起来，此时他的执行计划如下图：

因为是一个多表关联的执行计划，首先很明显，他的执行计划分为了两条，也就是会访问两个表，第一个表就是t2，明显是用ALL方式全表扫描他了，而且扫出了5条数据。

接着对第二个表的访问，也就是t1表，同样是全表扫描，因为他这种多表关联方式，基本上是笛卡尔积的效果，t2表的数据都会去t1表全表扫描所有71条数据，跟t1表的每一条数据都会做一个关联，而且extra里说了是Nested Loop，也就是嵌套循环的访问方式。

另外，这两条执行计划的id都是1，实际上一般来说，在执行计划里，一个select会对应一个id，因为这两条执行计划对应的是一个select语句，所以他俩的id都是1。

如果你要是有一个子查询，有另外一个select，那么另外一个select子查询对应的执行计划的id可能就是2了。

4、假设有下面这个SQL语句：

EXPLAIN SELECT * FROM t1 WHERE x1 IN (SELECT x1 FROM t2 ) OR x3='xxx'

这个SQL就稍稍有一点点的复杂了，因为主SELECT语句的WHERE筛选条件是依赖于一个子查询的，而且除此之外还有一个自己的where筛选条件，那么他的执行计划长什么样子呢？

首先第一条执行计划的id是1，第二条执行计划的id是2，因为这个SQL里有两个select，主查询select的执行计划的id就是1，子查询select的执行计划的id就是2

其次，第一条执行计划里，select_type是PRIMARY, 不是SIMPLE了，说明第一个执行计划的查询类型是主查询的意思，type是all，走的是全表扫描。

接着第二条执行计划，他的select_type是SUBQUERY，也就是子查询，子查询针对t2这个表，当然子查询本身就是一个全表扫描，但是对主查询而言，回事用x1 in 这个筛选条件，他这个type是index，说明使用了二级索引的方式。

5、接着我们来看另外一个union的SQL语句：

explain select * from t1 union select * from t2

这是一个典型的union语句，他的执行计划如下图：

第一条和第二条很好理解，两个select语句对应两个id，分别从t1和t2表里进行了全表扫描，第三条执行计划是什么呢？其实union语句默认的作用是把两个结果集合并起来进行去重，所以第三条执行计划干的是去重的活。所以上面他的table是<union 1,2>，这就是一个临时表的表名，而且你看他的extra里，有一个using temporaty，也就是使用临时表的意思，他就是把结果集放到临时表里进行去重的。