史上最实用的MySQL调优策略（二）

1.Explain执行计划

1.1.什么是执行计划

有了慢查询语句后，就要对语句进行分析。一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划，我们需要搞懂EPLATNEXPLAIN的各个输出项都是干嘛使的，从而可以有针对性的提升我们查询语句的性能。

通过使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析查询语句或是表结构的性能瓶颈，总的来说通过EXPLAIN我们可以：

l 表的读取顺序

l 数据读取操作的操作类型

l 哪些索引可以使用

l 哪些索引被实际使用

l 表之间的引用

l 每张表有多少行被优化器查询

1.2.执行计划的语法

执行计划的语法其实非常简单： 在SQL查询的前面加上EXPLAIN关键字就行。比如：EXPLAIN select * from table1

重点的就是EXPLAIN后面你要分析的SQL语句

除了以SELECT开头的查询语句，其余的DELETE、INSERT、REPLACE以及UPOATE语句前边都可以加上EXPLAIN，用来查看这些语句的执行计划，不过我们这里对SELECT语句更感兴趣，所以后边只会以SELECT语句为例来描述EsxPLAIN语句的用法。

1.3.执行计划详解

为了让大家先有一个感性的认识，我们把EXPLAIN语句输出的各个列的作用先大致罗列一下:

explain select * from order_exp;

id ： **在一个大的查询语句中每个SELECT关键字都对应一个唯一的id**

select_type ： SELECT**关键字对应的那个查询的类型**

table ：表名

partitions ：匹配的分区信息

type ：针对单表的访问方法

possible_keys ：可能用到的索引

key ：实际上使用的索引

key_len ：实际使用到的索引长度

ref ：当使用索引列等值查询时，与索引列进行等值匹配的对象信息

rows ：预估的需要读取的记录条数

filtered ：某个表经过搜索条件过滤后剩余记录条数的百分比

Extra ：—些额外的信息

id

我们知道我们写的查询语句一般都以SELECT关键字开头，比较简单的查询语句里只有一个SELECT关键字，

稍微复杂一点的连接查询中也只有一个SELECT关键字，比如:

SELECT *FROM s1
INNER J0IN s2 ON s1.id = s2.id
WHERE s1.order_status = 0 ;

但是下边两种情况下在一条查询语句中会出现多个SELECT关键字:

1、查询中包含子查询的情况

比如下边这个查询语句中就包含2个SELECT关键字:

SELECT* FROM s1 WHERE id IN ( SELECT * FROM s2);

2、查询中包含UNION语句的情况

比如下边这个查询语句中也包含2个SELECT关键字:

SELECT * FROM s1
UNION SELECT * FROM s2 ;

查询语句中每出现一个SELECT关键字，MySQL就会为它分配一个唯一的id值。这个id值就是EXPLAIN语句的第一个列。

单SELECT关键字

比如下边这个查询中只有一个SELECT关键字，所以EXPLAIN的结果中也就只有一条id列为1的记录∶

EXPLAIN SELECT * FROM s1 WHERE order_no = 'a';

连接查询

对于连接查询来说，一个SELEOT关键字后边的FROM子句中可以跟随多个表，所以在连接查询的执行计划中，每个表都会对应一条记录，但是这些记录的id值都是相同的，比如:

EXPLAIN SELECT * FROM s1 WHERE order_no = 'a';

可以看到，上述连接查询中参与连接的s1和s2表分别对应一条记录，但是这两条记录对应的id值都是1。这里需要大家记住的是，在连接查询的执行计划中，每个表都会对应一条记录，这些记录的id列的值是相同的。

包含子查询

对于包含子查询的查询语句来说，就可能涉及多个SELECT关键字，所以在包含子查询的查询语句的执行计划中，每个SELECT关键字都会对应一个唯一的id值，比如这样:

EXPLAIN
SELECT * FROM s1 WHERE id IN (SELECT id FROM s2) OR order_no = 'a';

但是这里大家需要特别注意，查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。所以如果我们想知道查询优化器对某个包含子查询的语句是否进行了重写，直接查看执行计划就好了，比如说:

EXPLAIN
SELECT * FROM s1 WHERE id IN (SELECT id FROM s2 WHERE order_no = 'a');

可以看到，虽然我们的查询语句是一个子查询，但是执行计划中s1和s2表对应的记录的id值全部是1，这就表明了查询优化器将子查询转换为了连接查询,

包含UNION子句

对于包含UNION子句的查询语句来说，每个SELECT关键字对应一个id值也是没错的，不过还是有点儿特别的东西，比方说下边这个查询:

EXPLAIN
SELECT * FROM s1 UNION SELECT * FROM s2;

这个语句的执行计划的第三条记录为什么这样？UNION 子句会把多个查询的结果集合并起来并对结果集中的记录进行去重，怎么去重呢? MySQL使用的是内部的临时表。正如上边的查询计划中所示，UNION 子句是为了把id为1的查询和id为2的查询的结果集合并起来并去重，所以在内部创建了一个名为<union1，2>的临时表（就是执行计划第三条记录的table列的名称)，id为NULL表明这个临时表是为了合并两个查询的结果集而创建的。

跟UNION 对比起来，UNION ALL就不需要为最终的结果集进行去重，它只是单纯的把多个查询的结果集中的记录合并成一个并返回给用户，所以也就不需要使用临时表。所以在包含UNION ALL子句的查询的执行计划中，就没有那个id为NULL的记录，如下所示:

EXPLAIN
SELECT * FROM s1 UNION ALL SELECT * FROM s2;

table

不论我们的查询语句有多复杂，里边包含了多少个表，到最后也是需要对每个表进行单表访问的，MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名。

可以看见，只涉及对s1表的单表查询，所以EXPLAIN输出中只有一条记录，其中的table列的值是s1，而连接查询的执行计划中有两条记录，这两条记录的table列分别是s1和s2.

partitions

和分区表有关，一般情况下我们的查询语句的执行计划的partitions列的值都是NULL。

type

我们前边说过执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法/访问类型，其中的type列就表明了这个访问方法/访问类型是个什么东西，是较为重要的一个指标，结果值从最好到最坏依次是：

出现比较多的是system>const>eq_ref>ref>range>index>ALL

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

system

当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system。

explain select * from test_myisam;

当然，如果改成使用InnoDB存储引擎，试试看执行计划的type列的值是什么。

const

就是当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const。因为只匹配一行数据，所以很快。

例如将主键置于where列表中

EXPLAIN
SELECT * FROM s1 WHERE id = 716;

B+树叶子节点中的记录是按照索引列排序的，对于的聚簇索引来说，它对应的B+树叶子节点中的记录就是按照id列排序的。B+树矮胖，所以这样根据主键值定位一条记录的速度很快。类似的，我们根据唯一二级索引列来定位一条记录的速度也很快的，比如下边这个查询：

SELECT * FROM
order_exp WHERE insert_time=’’ and order_status=’’ and expire_time=’’ ;

这个查询的执行分两步，第一步先从u_idx_day_status对应的B+树索引中根据索引列与常数的等值比较条件定位到一条二级索引记录，然后再根据该记录的id值到聚簇索引中获取到完整的用户记录。

MySQL把这种通过主键或者唯一二级索引列来定位一条记录的访问方法定义为：const，意思是常数级别的，代价是可以忽略不计的。

不过这种const访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效，如果主键或者唯一二级索引是由多个列构成的话，组成索引的每一个列都是与常数进行等值比较时，这个const访问方法才有效。

对于唯一二级索引来说，查询该列为NULL值的情况比较特殊，因为唯一二级索引列并不限制 NULL 值的数量，所以上述语句可能访问到多条记录，也就是说is null不可以使用const访问方法来执行。

eq_ref

在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的〈如