《Mysql是怎样运行的》读书笔记四

《Mysql是怎样运行的》读书笔记四

一、Explain

​ 一条查询语句在经过Mysql查询优化器的各种基于成本和规则的优化后会生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等。Mysql提供了EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划

EXPLAIN SELECT 1

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

以一个连接查询为例子

EXPLAIN SELECT * from tb1 s1 JOIN tb1 s2

>
最后可以看到这个连接查询的执行计划中有两条记录，记录中的table分别对应s1和s2

• id：查询语句中每出现一个 SELECT 关键字，设计 MySQL 的大叔就会为它分配一个唯一的 id 值。

sql的查询语句一般都以SELECT关键字开头，简单的查询语句只有一个SELECT,稍微复杂的比如连接查询也是只有一个SELECT，这俩中情况的id都是相同的

​ 而对于下面俩种情况，一条语句中有多个SELECT关键字

• 子查询

• 对于子查询，涉及多个SELECT关键字，，所以在包含子查询的查询语句的执行计划中，每个 SELECT 关键字都会对应一个唯一的 id 值

EXPLAIN SELECT * FROM single_table s1 WHERE key1 IN (SELECT key1 FROM single_table s2) OR key3 = 'a';
#OR key3 = 'a'导致不能 将子查询优化为半连接

• 还需要注意，查询优化器可能对 IN子查询进行优化，转换为连接查询

EXPLAIN SELECT * FROM single_table s1 WHERE key1 IN (SELECT key1 FROM single_table s2);

看到Extra的First Match（半连接查询的算法），说明这个子查询被优化，两个id都为1

• UNION语句

• 对于包含 UNION 子句的查询语句来说，每个 SELECT 关键字对应一个 id 值也是没错的

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

为什么执行计划会出现第三条记录，而且id为NULL？

​ UNION 子句是为了把 id 为 1 的查询和 id 为 2 的查询的结果集合并起来并去重，所以在内部创建了一个名为 <union1, 2> 的临时表（就是执行计划第三条记录的 table列的名称）， id 为 NULL 表明这个临时表是为了合并两个查询的结果集而创建的

• 如果是UNION ALL 呢？

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

• select_type

1. SIMPLE：查询语句中不包含 UNION 或者子查询的查询都算作是 SIMPLE 类型（连接查询也算SIMPLE查询）

2. PRIMARY、UNION、UNION RESULT

   • 对于包含UNION、UNION ALL或子查询的大查询，其中最左边的的查询的select_type为PRIMARY
   • 对于包含UNION、UNION ALL，除了最左边的查询、其他的小查询均为UNION
   • MySQL 选择使用临时表来完成 UNION 查询的去重工作，针对该临时表的查询的 select_type 就是 UNION RESULT

   

3. SUBQUERY

   • 如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个 SELECT 关键字代表的那个查询的 select_type 就是 SUBQUERY

     EXPLAIN SELECT * FROM single_table s1 WHERE key1 IN (SELECT key1 FROM single_table s2) OR key3 = 'a';
     

   

   可以看到，外层查询的 select_type 就是 PRIMARY ，子查询的 select_type 就是 SUBQUERY 。需要大家注意的是，由于select_type为SUBQUERY的子查询由于会被物化，所以只需要执行一遍。

4. DEPENDENT SUBQUERY

   • 如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是相关子查询，则该子查询的第一个 SELECT 关键字代表的那个查询的 select_type 就是 DEPENDENT SUBQUERY

   

   select_type为DEPENDENT SUBQUERY的查询可能会被执行多次。

5. DEPENDENT UNION

   • 在包含 UNION 或者 UNION ALL 的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的 select_type 的值就是 DEPENDENT UNION 。

   EXPLAIN SELECT * FROM single_table s1 WHERE key1 IN (SELECT key1 FROM single_table s2 WHERE key1 = 'a' UNION SELECT  key1 FROM single_table s1 WHERE key1 = 'b');
   

这个查询比较复杂啊，大查询里包含了一个子查询，子查询里又是由 UNION 连起来的两个小查询。从执行计划中可以看出来， SELECT key1 FROM s2 WHERE key1 = ‘a’ 这个小查询由于是子查询中第一个查询，所以它的 select_type 是 DEPENDENT SUBQUERY ，而 SELECT key1 FROM s1 WHERE key1 = ‘b’ 这个查询的select_type 就是 DEPENDENT UNION 。

6. DERIVED

   • 对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的 select_type 就是 DERIVED

    EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM single_table s1 GROUP BY key1) AS derived_s1 where c > 1;
   

   

   从执行计划中可以看出， id 为 2 的记录就代表子查询的执行方式，它的 select_type 是 DERIVED ，说明该子查询是以物化的方式执行的。 id 为 1 的记录代表外层查询，大家注意看它的 table 列显示的是 ，表示该查询是针对将派生表物化之后的表进行查询的。

7. MATERIALIZED

   • 当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的 select_type 属性就是 MATERIALIZED (当子查询的)

   EXPLAIN SELECT * FROM single_table s1 WHERE key1 IN (SELECT key1 FROM s2);
   

​ 执行计划的第三条记录的 id 值为 2 ，说明该条记录对应的是一个单表查询，从它的 select_type 值为MATERIALIZED 可以看出，查询优化器是要把子查询先转换成物化表。然后看执行计划的前两条记录的 id 值都为 1 ，说明这两条记录对应的表进行连接查询，需要注意的是第二条记录的 table 列的值 ，说明该表其实就是 id 为 2 对应的子查询执行之后产生的物化表，然后将 s1 和该物化表进行连接查询。

• type：代表着 MySQL 对某个表的执行查询时的访问方法，其中的 type 列就表明了这个访问方法

  所有的查询方法：system ， const ，eq_ref ， ref ， fulltext ， ref_or_null ， index_merge ， unique_subquery ， index_subquery ，range ， index ， ALL

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

  • fulltext：全文搜索，自行百度

  • index_merge：索引合并，包含Intersection 、 Union 、 Sort-Union

  • unique_subquery：unique_subquery 是针对在一些包含 IN 子查询的查询语

    句中，如果查询优化器决定将 IN 子查询转换为 EXISTS 子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的 type 列的值就是 unique_subquery ，

     EXPLAIN SELECT * FROM single_table s1 WHERE key2 IN (SELECT id FROM single_table s2 where s1.key1 = s2.
    key1) OR key3 = 'a';
    

    

  • index_subquery: index_subquery 与 unique_subquery 类似，只不过访问子查询中的表时使用的是普通的索引，

  • index:当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index

• possible_keys和key

  • possible_keys 列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些
  • key 列表示实际用到的索引有哪些

  EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';
  

  

​ possible_keys 列的值是 idx_key1,idx_key3 ，表示该查询可能使用到 idx_key1,idx_key3 两个索引，然后 key 列的值是 idx_key3 ，表示经过查询优化器计算使用不同索引的成本后，最后决定使用idx_key3 来执行查询比较划算

• key_len

**key_len 列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，**它是由这三个部分构成的：

1. ​ 对于固定长度类型的索引列来说，其实际占用的存储空间的最大长度就是该固定值。，比如某个索引列的类型是 VARCHAR(100) ，使用的字符集是 utf8 ，那么该列实际占用的最大存储空间就是 100 × 3 = 300 个字节。
2. 对于可以存储NULL值索引列，则key_len比不可以存储NULL值时多1字节
3. 对于变长字段，有多2个字节的空间来存储该变长列的实际长度

• ref

​ 当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const 、 eq_ref 、 ref 、 ref_or_null 、unique_subquery 、 index_subquery 其中之一时， ref 列展示的就是与索引列作等值匹配的的东西

 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';# 这里的ref为const
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;#这里的ref 则为s1.id

• rows

​ 如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的 rows 列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的 rows 列就代表预计扫描的索引记录行数

• filtered

某个表结果搜索条件过滤后剩余记录条数的百分比

关于Explain的Extra属性

extra用来说明一些额外信息的，我们可以通过这些额外信息来准确理解Mysql执行给定的查询语句

• No tables used：语句中没有FROM子句时将提示该额外信息

• Impossible WHERE: 查询语句的WHERE子句永远为False时将会提示该额外信息

• No matching min/max row：当查询列表处有 MIN 或者 MAX 聚集函数，但是并没有符合 WHERE 子句中的搜索条件的记录时，将会提示该额外信息

• Using index：当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在Extra 列将会提示该额外信息。

• Using index condition和索引下推：有些搜索条件虽然出现了索引列，但却不能使用到索引列

 SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

key1>'z’可以使用到索引，但key1 LIKE '%a’却无法使用到索引

1. 先根据 key1 > ‘z’ 这个条件，从二级索引 idx_key1 中获取到对应的二级索引记录。

2. 根据上一步骤得到的二级索引记录中的主键值进行回表，找到完整的用户记录再检测该记录是否符合key1 LIKE ‘%a’ 这个条件，将符合条件的记录加入到最后的结果集。

但是虽然 key1 LIKE ‘%a’ 不能组成范围区间参与 range 访问方法的执行，但这个条件毕竟只涉及到了key1 列，

所以 MySQL 把上边的步骤改进了一下：

（1）先根据 key1 > ‘z’ 这个条件，定位到二级索引 idx_key1 中对应的二级索引记录。

（2）对于指定的二级索引记录，先不着急回表，而是先检测一下该记录是否满足 key1 LIKE ‘%a’ 这个条件，如果这个条件不满足，则该二级索引记录压根儿就没必要回表。

（3）对于满足 key1 LIKE ‘%a’ 这个条件的二级索引记录执行回表操作。

我们说回表其实是一个随机io，比较耗时，所以上述修改虽然改进了一点点，但是可以省区好多回表操作的成本。Mysql把这个改进称之为 索引条件下推

• Using where：当我们使用全表扫描来执行某个表的查询，并且该语句的where子句中有针对该表的搜索条件时，Extra列中会提示上述额外信息

  ​ 当使用索引访问来执行对某个表的查询，并且该语句的 WHERE 子句中有除了该索引包含的列之外的其他搜索条件时，在 Extra 列中也会提示上述额外信息。

• Using join buffer(基于块的嵌套循环算法)

• Not exists：当我们使用左（外）连接时，如果 WHERE 子句中包含要求被驱动表的某个列等于 NULL 值的搜索条件，而且那个列又是不允许存储 NULL 值的（外连接会转化为内连接），那么在该表的执行计划的 Extra 列就会提示 Not exists 额外信息

• Using intersect(…) 、 Using union(…) 和 Using sort_union(…) 索引合并

• Zero Limit：当limit参数为0时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息

• using filesort：对结果集中的记录进行排序是可以使用到索引，无需在内存中进行排序运算，但是如果没用到索引，则需要进行文件排序

• Using temporary：在许多查询的执行过程中， MySQL 可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含 DISTINCT 、 GROUP BY 、 UNION 等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL 很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的 Extra 列将会显示 Using temporary 提示