MySQL Explain

Explain

输出中的行,以MySQL实际执行的查询部分顺序出现,而这个顺序,不总是与其在原始SQL中的一致

1
2
3
4
5
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t_store | range | PRIMARY       | PRIMARY | 8       | NULL |    9 | Using where |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+

ID列

这一列包含一个编号,标识SELECT所属的行. 如果在语句中没有子查询,或者联合查询,那么只会有唯一的SELECT,否则内层的SELECT语句一般会顺序编号,对应其在原始语句中的位置.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

mysql> EXPLAIN SELECT *
        FROM (SELECT id
              FROM t_store) AS ts
        WHERE ts.id < 10;

+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |  350 | Using where |
|  2 | DERIVED     | t_store    | index | NULL          | PRIMARY | 8       | NULL |  350 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+

select_type列

这一列显示了对应的行是简单还是复杂SELECT

SIMPLE
- 意味着查询不包括子查询和UNION,如果查询有任何复杂的子部分,则最外层部分标记为PRIMARY.
SUBQUERY
- 包含在SELECT列表中的子查询中的SELECT(不在FROM子句中)
DERIVED
- 表示包含在FROM子句的子查询中的SELECT,MySQL会递归执行,并将结果放在一个临时表中. 服务器内部称为"派生表".
UNION
- 在UNION中的第二个和随后的SELECT被标记为UNION.
UNION RESULT
- 用来从UNION的匿名临时表检索结果的SELECT 被标记位UNION RESULT

table列

这一列显示了对应行正在访问哪个表,可以在这一列中从上往下,观察MySQL的关联优化器,为查询选择的关联顺序.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
mysql> EXPLAIN SELECT
          s.store_name,
          ts.style_name
        FROM t_store AS s INNER  JOIN t_style_store tss ON s.id = tss.store_id
          INNER JOIN t_style AS ts ON tss.style_id = ts.id;

+----+-------------+-------+--------+-----------------------------+-----------------------------+---------+-------------------------+------+-------------+
| id | select_type | table | type   | possible_keys               | key                         | key_len | ref                     | rows | Extra       |
+----+-------------+-------+--------+-----------------------------+-----------------------------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | tss   | index  | idx_style_store_sid_styleid | idx_style_store_sid_styleid | 16      | NULL                    |  145 | Using index |
|  1 | SIMPLE      | ts    | eq_ref | PRIMARY                     | PRIMARY                     | 8       | big_screen.tss.style_id |    1 | Using where |
|  1 | SIMPLE      | s     | eq_ref | PRIMARY                     | PRIMARY                     | 8       | big_screen.tss.store_id |    1 | Using where |
+----+-------------+-------+--------+-----------------------------+-----------------------------+---------+-------------------------+------+-------------+

type列

MySQL 决定如何查找表中的行,下面是最重要的访问方法,依次从最差到最优

ALL
- 全表扫描,通常意味着MySQL需要扫描整张表,从头到尾去找到需要的行. (例外: 查询中使用了LIMIT,或者在Extra中显示Using distinct / not exists)
index
- 这个和全表扫描一样, 只是MySQL扫描表时,按照索引次序进行,而不是行.
- 主要优点是避免了排序.
- 最大的缺点是要承担按索引次序读取整个表的开销. 这通常意味着若是按随机次序访问行,开销将非常大.
- 如果在Extra列中看到了Using index,说明MySQL正在使用覆盖索引,他只扫描索引的数据,而不是按索引次序扫描每一行. 他比按索引次序全表扫描的开销要小得多.
range
- 范围扫描是一个有限制的索引扫描,它开始于索引里的某一点,返回匹配这个值域的行.
- 这比全索引扫描好一点,因为不用遍历全部索引.
- 范围扫描是带有 BETWEEN 或者在WHERE 中带有>的查询.
- 当MySQL使用索引去查找一系列值时,例如IN(),OR(),也会显示范围扫描, 但是这其实是相当不同的访问类型. 性能上有巨大差异. (范围条件)
- 此类扫描的开销和索引类型相当.

什么是范围条件 从EXPLAIN 的输出很难区分MySQL是要查询范围值,还是查询列表值. EXPLAIN使用相同的词range来描述这两种情况.例如:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
mysql> EXPLAIN SELECT id from t_store WHERE id  45;
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t_store | range | PRIMARY       | PRIMARY | 8       | NULL |  304 | Using where; Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+

mysql> EXPLAIN SELECT id from t_store WHERE id in (10,20,30,40);

+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t_store | range | PRIMARY       | PRIMARY | 8       | NULL |    4 | Using where; Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+

从EXPLAIN的结果是无法区分这两者的. 但是可以从值的范围和多个等于条件来得出不同. 第二个查询就是多个等值条件查询. 这两种访问的效率是不同的. 对应范围条件查询,MySQL无法使用范围后面的其他索引了,但是对于"多个等值条件查询",则没有这个限制

REF
- 这是一种索引访问(索引查找) ,返回所有匹配某个单个值的行,然而,MySQL会找到多个符合条件的行. 因此,他是查找和扫描的混合体.
- 此类索引只有使用非唯一性索引或者唯一索引的非唯一性前缀才会发生.
- 叫做ref的原因是因为索引要跟某个参考值相比较

例如:

t_store表中store_name字段有一个非唯一性索引

1
2
3
4
5
6
7
      mysql> EXPLAIN SELECT * FROM  t_store WHERE store_name = 'test';
      
      +----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-----------------------+
| id | select_type | table   | type | possible_keys      | key                | key_len | ref   | rows | Extra                 |
+----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t_store | ref  | t_store_name_index | t_store_name_index | 514     | const |    1 | Using index condition |
+----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-----------------------+

t_store 表中 shop_id, map_area_id 组成唯一性索引

1
2
3
4
5
6
7
mysql> EXPLAIN SELECT * FROM  t_store WHERE shop_id = 'test';

+----+-------------+---------+------+----------------------+----------------------+---------+-------+------+-----------------------+
| id | select_type | table   | type | possible_keys        | key                  | key_len | ref   | rows | Extra                 |
+----+-------------+---------+------+----------------------+----------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t_store | ref  | t_store_shopid_index | t_store_shopid_index | 130     | const |    1 | Using index condition |
+----+-------------+---------+------+----------------------+----------------------+---------+-------+------+-----------------------+

eq_ref
- 使用这种索引查找,MySQL知道最多只返回一条符合条件的记录
- 可以在MySQL使用主键或者唯一索引查找时看到
- MySQL对于这类访问类型的优化做的很好,因为它知道无需估计匹配行的范围或在找到匹配行后再继续查找.

例如: t_store表中 id 是主键,t_style_store表中,store_id 是唯一索引.代表t_store中的店铺主键.

1
2
3
4
5
6
7
8
mysql> EXPLAIN SELECT * FROM  t_store as t LEFT JOIN  t_style_store as tss on t.id = tss.store_id;

+----+-------------+-------+--------+-------------------------------------------------+---------------------+---------+-----------------+------+-------------+
| id | select_type | table | type   | possible_keys                                   | key                 | key_len | ref             | rows | Extra       |
+----+-------------+-------+--------+-------------------------------------------------+---------------------+---------+-----------------+------+-------------+
|  1 | SIMPLE      | t     | ALL    | NULL                                            | NULL                | NULL    | NULL            |  350 | NULL        |
|  1 | SIMPLE      | tss   | eq_ref | t_style_store_index,idx_style_store_sid_styleid | t_style_store_index | 8       | big_screen.t.id |    1 | Using where |
+----+-------------+-------+--------+-------------------------------------------------+---------------------+---------+-----------------+------+-------------+

const,system
- MySQL 能对查询的某部分进行优化,并将其转换为一个常量时
- 如果你通过将某一行的主键放入WHERE子句里的方式来获取此行的主键,MySQL就能将其转换为一个常量.

例如:t_store 表中,id是主键

1
2
3
4
5
6
mysql> EXPLAIN SELECT * FROM  t_store WHERE id = 10;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t_store | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL  |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+

NULL
- 意味着MySQL能在优化阶段分解查询语句,在执行阶段甚至用不着在访问表或者索引.

例如:

1
2
3
4
5
6
mysql> EXPLAIN SELECT min(id) FROM t_store;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

possible_keys列

这一列显示查询可以使用那些索引, 这是基于查询访问的列表和使用的比价操作符来判断的. 这个列表是在优化过程的早期创建的. 因此有些罗列出来的索引可能对于后续优化过程是没有用的.

Key列

显示MySQL决定采用哪个索引来优化对该表的访问.

key_len列

显示MySQL在索引里使用的字节数. 当我们计算列的使用情况时,务必把字符列中字符集也考虑进去.

ref 列

显示之前的表在key列记录的索引中查找值所用的列和常量.

rows列

这一列显示MySQL估计为了找到所需行而要读取的行数. 这个数字是内嵌套循环关联计划里的循环数目.也就是说它不是MySQL认为它最终要从表里读取出来的行数,而是MySQL为了找到符合查询的每一点上标准的那些行必须要读取的行的平均数.

这个估算可能很不精确,例如下面这个,不是一定要读取350行.

1
2
3
4
5
6
mysql> EXPLAIN  SELECT  * FROM  t_store LIMIT 1;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t_store | ALL  | NULL          | NULL | NULL    | NULL |  350 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+

通过把所有的rows列的值相乘,可以粗略的估算出整个查询会检索的行数. 下面这个大约会扫描13行.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
mysql> EXPLAIN SELECT
       s.store_name
        FROM t_store AS s INNER JOIN t_style_store tss ON s.id = tss.store_id
          INNER JOIN t_category_store AS tcs ON tcs.store_id = s.id;
+----+-------------+-------+--------+-------------------------------------------------+----------------------------+---------+-------------------------+------+--------------------------+
| id | select_type | table | type   | possible_keys                                   | key                        | key_len | ref                     | rows | Extra                    |
+----+-------------+-------+--------+-------------------------------------------------+----------------------------+---------+-------------------------+------+--------------------------+
|  1 | SIMPLE      | tss   | index  | t_style_store_index,idx_style_store_sid_styleid | t_style_store_index        | 8       | NULL                    |   13 | Using index              |
|  1 | SIMPLE      | s     | eq_ref | PRIMARY                                         | PRIMARY                    | 8       | big_screen.tss.store_id |    1 | Using where              |
|  1 | SIMPLE      | tcs   | ref    | idx_category_store_sid_cid                      | idx_category_store_sid_cid | 8       | big_screen.s.id         |    1 | Using where; Using index |
+----+-------------+-------+--------+-------------------------------------------------+----------------------------+---------+-------------------------+------+--------------------------+

需要注意的是: 这个数字是MySQL认为他要检查的行数,而不是结果集里面的行数. MySQL可能不必真的读取所有他估计到的行.

filtered列

在使用 EXPLAIN EXTENDED 时出现. 它显示的是针对表里符合某个条件的记录数的百分比所做的一个悲观估算. 如果你把rows列和这个百分比相乘,则可以看到MySQL估算它将和查询计划里前一个表关联的行数.

1
2
3
4
5
6
mysql> EXPLAIN EXTENDED  SELECT * FROM t_action_record WHERE create_time < '2019-01-01 11:11:11';
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_action_record | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4737 |    33.33 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+

使用了全表扫描和where子句来过滤输出行. 他知道使用where子句可以从结果里过滤掉多少条记录,范围访问的成本是可以估算出来的.

Extra列

这列包含的是不适合在其他列显示的额外信息.

Using index
- 此值表示MySQL将使用覆盖索引. 以避免回表操作.
Using where
- 这意味着MySQL服务器将在存储引擎检索行后再进行过滤.
Using temporary
- MySQL需要使用临时表来存储结果集，常见于排序和分组查询
Using filesort
- 意味着MySQL会对结果使用一个外部索引排序,而不是按索引次序表里读取行.
Using index condition
- 在MySQL 5.6版本后加入的新特性（Index Condition Pushdown）;会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行.

Index Condition Pushdown

ICP（index condition pushdown）是MySQL利用索引（二级索引）元组和筛字段在索引中的WHERE条件从表中提取数据记录的一种优化操作。ICP的思想是：存储引擎在访问索引的时候检查筛选字段在索引中的WHERE条件（pushed index condition，推送的索引条件），如果索引元组中的数据不满足推送的索引条件，那么就过滤掉该条数据记录。ICP（优化器）尽可能的把index condition的处理从Server层下推到Storage Engine层。Storage Engine使用索引过过滤不相关的数据，仅返回符合Index Condition条件的数据给Server层。也是说数据过滤尽可能在Storage Engine层进行，而不是返回所有数据给Server层，然后后再根据WHERE条件进行过滤.

ICP开启时的执行计划含有 Using index condition 标示，表示优化器使用了ICP对数据访问进行优化。

当关闭ICP时,Index仅仅是data access的一种访问方式，存储引擎通过索引回表获取的数据会传递到MySQL Server 层进行WHERE条件过滤。
当打开ICP时,如果部分WHERE条件能使用索引中的字段,MySQL Server 会把这部分下推到引擎层,可以利用Index过滤的WHERE条件在存储引擎层进行数据过滤,而非将所有通过Index Access的结果传递到MySQL Server层进行WHERE过滤.

优化效果 :ICP能减少引擎层访问基表的次数和MySQL Server 访问存储引擎的次数,减少io次数，提高查询语句性能

ICP限制:

当SQL需要全表访问时，ICP的优化策略可用于range, ref, eq_ref, ref_or_null类型的访问数据方法。
支持InnoDB和MyISAM表。
ICP只能用于二级索引，不能用于主索引。
并非全部WHERE条件都可以用ICP筛选，如果WHERE条件的字段不在索引列中，还是要读取整表的记录到Server端做WHERE过滤。
ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。
MySQL 5.6版本的不支持分表的ICP功能，5.7版本的开始支持。
当SQL使用覆盖索引时，不支持ICP优化方法。

示例:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.18-log |
+------------+
1 row in set (0.01 sec)
 
mysql> set optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)
 
mysql> EXPLAIN EXTENDED  SELECT * FROM t_action_record WHERE create_time  < '2019-01-01 11:11:11' AND mall_id = '1682';

+----+-------------+-----------------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys          | key                    | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t_action_record | NULL       | ref  | t_action_record__index | t_action_record__index | 130     | const |    1 |    33.33 | Using where |
+----+-------------+-----------------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
1 row in set (0.01 sec)
 
mysql> set optimizer_switch='index_condition_pushdown=on';
Query OK, 0 rows affected (0.00 sec)
 
mysql> EXPLAIN EXTENDED  SELECT * FROM t_action_record WHERE create_time  < '2019-01-01 11:11:11' AND mall_id = '1682';

+----+-------------+-----------------+------------+------+------------------------+------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table           | partitions | type | possible_keys          | key                    | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------------+------------+------+------------------------+------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_action_record | NULL       | ref  | t_action_record__index | t_action_record__index | 130     | const |    1 |    33.33 | Using index condition |
+----+-------------+-----------------+------------+------+------------------------+------------------------+---------+-------+------+----------+-----------------------+

Explain#

ID列#

select_type列#

table列#

type列#

possible_keys列#

Key列#

key_len列#

ref 列#

rows列#

filtered列#

Extra列#

Index Condition Pushdown#