14.性能优化-explain优化器决策结果

explain结果列说明：

explain 列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

case：

1. 普通查询中，每出现一个SELECT关键字，就会分配一个唯一的id值

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    8 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.03 sec)

2. 连接查询，一个select关键字后边的from子句中可以跟随多个表，所以在连接查询的执行计划中，每个表都会对应一条记录，但是这些记录的id值都是相同的

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | NULL                                  |
|  1 | SIMPLE      | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9954 |   100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
2 rows in set, 1 warning (0.01 sec)

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

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | s1    | NULL       | ALL   | idx_key3      | NULL     | NULL    | NULL | 9688 |   100.00 | Using where |
|  2 | SUBQUERY    | s2    | NULL       | index | idx_key1      | idx_key1 | 303     | NULL | 9954 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.02 sec)

4. 子查询重写变成连接查询,查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 WHERE common_field = 'a');
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref               | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+
|  1 | SIMPLE      | s2    | NULL       | ALL  | idx_key3      | NULL     | NULL    | NULL              | 9954 |    10.00 | Using where; Start temporary |
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | xiaohaizi.s2.key3 |    1 |   100.00 | End temporary                |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)

5. UNION子句的查询语句, 会多出一个临时表记录用来做去重逻辑

mysql> EXPLAIN SELECT * FROM s1  UNION SELECT * FROM s2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | s1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | NULL            |
|  2 | UNION        | s2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9954 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

6. UNION ALL就不需要为最终的结果集进行去重，所以没有临时表

mysql> EXPLAIN SELECT * FROM s1  UNION ALL SELECT * FROM s2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | NULL  |
|  2 | UNION       | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9954 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)

select_type枚举:

名称	描述
SIMPLE	查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型
PRIMARY	对于包含UNION、UNION ALL或者子查询的大查询，其中最左边的那个查询的select_type值就是PRIMARY
UNION	对于包含UNION、UNION ALL或者子查询的大查询，除了最左边的那个小查询以外，其余的小查询的select_type值就是UNION
UNION RESULT	UNION查询的去重工作中使用到的临时表的查询
SUBQUERY	如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY
DEPENDENT SUBQUERY	如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是相关子查询，则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY
DEPENDENT UNION	在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type的值就是DEPENDENT UNION
DERIVED	对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的select_type就是DERIVED
MATERIALIZED	当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的select_type属性就是MATERIALIZED
UNCACHEABLE SUBQUERY	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	The second or later select in a UN´ION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

type 枚举:

枚举值	描述
system	当表中只有一条记录并且是 MyISAM、Memory引擎
const	根据主键索引列或者唯一二级索引列与常量进行等值匹配的方法
eq_ref	在连接查询时，被驱动表通过主键或者唯一二级索引列进行等值匹配的方法
ref	普通二级索引列与常量进行等值匹配查询某个表时，可能是ref
fulltext	全文索引
ref_or_null	普通二级索引与常量进行等值匹配查询某个表，且该索引列的值也可以是NULL值
index_merge	使用索引合并的方式来执行查询
unique_subquery	查询优化器决定将IN子查询转换为EXISTS子查询，且子查询可以使用到主键进行等值匹配
index_subquery	同unique_subquery, 只不过子查询中的表时使用普通的索引进行等值匹配
range	使用索引获取某些范围区间的记录
index	使用覆盖索引, 但需要扫描全部的索引记录的方法
ALL	全表扫描

possible_keys和key:

possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，key列表示实际用到的索引有哪些 注意：possible_keys越多，查询优化器计算查询成本时就得花费更长时间

key_len：

• key_len: 表示当优化器决定使用某个索引执行查询时，该索引记录的最大字节长度，单位：字节
• 计算规则：
  1. 固定长度类型的索引列:
     • 实际占用的存储空间的最大长度就是该固定值
     • e.g. CAHR(M) ASCII字符集
     • 计算：key_len = M * 1
  2. 可变长度字符集、可变长类型的索引列：
     • 需要考虑 字符集、索引列字符个数、NUll值列表、变长字段长度列表
     • e.g. VARCHAR(100) utf8字符集
       1. 该列实际占用的最大存储空间： 100 × 3 = 300个字节
       2. NUll值列表：1字节（一般来说索引为NOT NULL, 则不会有这1字节开销）
       3. 变长字段长度列表：2字节
       4. 计算： key_len = 300 + 1 + 2
• 作用：
  • 判断当使用了联合索引列的情况下，具体用到了几列

ref：

枚举值	描述
const	常量等值匹配； e.g. … = 1
func	函数等值匹配； e.g. … = UPPER(s1.key1)
某个字段	字段等值匹配,会把库名也打印出； e.g. … = s1.key1

当使用索引列等值匹配的条件去执行查询时(访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery之一)，ref列展示的就是与索引列作等值匹配具体内容，比如只是一个常数CONST或者是某个列t.id

rows：

• 作用：查询优化器给出的预计需要扫描的行数，预估值非精确值
• 场景：
  1. 如果查询优化器决定使用全表扫描执行查询时，rows列就代表预计需要扫描的行数
  2. 如果查询优化器决定使用索引查询时，rows列就代表预计扫描的索引记录行数

filtered:

• 作用：评估连表查询时，驱动表的扇出情况。扇出值表示驱动表符合条件的记录数n，在被驱动表中还需要n次查询
• 扇出值 = Rows * filtered
• 使用说明：
  1. 单表查询：
     1. 全表扫描：filtered列就代表查询优化器预测在这rows条记录中，满足全部搜索条件的比例
     2. 索引：filtered列就代表查询优化器预测在这rows条记录中，满足除使用到对应索引的搜索条件外，满足剩余搜索条件的比例
  2. 连表查询： 含义：扇出值：n = rows * filtered。在对驱动表一次访问之后（需要扫描rows条记录），对被驱动表执行大约n次查询 ### Extra： |常见的枚举值|描述|是否互斥| |:—-|:—-| | Using index | 使用索引覆盖的特性，即无需回表 |是| | Using index condition | 使用索引下推的特性，减少回表次数。（使用索引扫描出部分条件的结果，然后将这写临时结果再进行剩余条件匹配，最后再回表） |是| |Using where | 1.全表扫描且条件匹配 2. 使用到索引搜索，且剩余的条件判断无法通过索引覆盖、索引下推进行优化，必须直接回表 |是| |Using filesort | 使用了文件排序。无法通过索引排序得到结果集，必须通过内存排序、磁盘排序才能得到结果|否| |Using temporary | 使用了临时表来排序、去重、分组。无法通过索引排序得到结果集，通过建立内部的临时表来执行|否| |Using join buffer (Block Nested Loop)|连接查询执行过程中，被驱动表``不能利用索引,则会分配基于块的嵌套循环算法的内存|是| |Using intersect(…) |使用Intersect索引合并的方式执行查询|是| |Using union(…) |使用Union索引合并的方式执行查询|是| |Using sort_union(…) |使用Sort-Union索引合并的方式执行查询|是|