发表更新MySQL系列 / MySQL优化35 分钟读完 (大约5291个字)

MySQL系列(16)：理解执行计划-EXPLAIN

　　MySQL 优化器会根据 表、列、索引 和 WHERE 子句中的条件的详细信息，会考虑许多技术来更高效地执行 SQL 查询中涉及的查找。可以在不读取所有行的情况下执行对大表的查询，可以在不比较每个行组合的情况下执行涉及多个表的联接。

　　MySQL 优化器会选择最有效查询的操作集称为 查询执行计划，也称为 EXPLAIN 计划。MySQL EXPLAIN 官方文档，MySQL EXPLAIN 语句。

　　 我们需要理解 EXPLAIN 计划中的操作是否已达到预期，以及对那些低效的操作进行改进。

EXPLAIN优化查询

EXPLAIN 提供了有关 MySQL 如何执行语句的信息：

  • EXPLAIN 支持 SELECT、DELETE、INSERT、REPLACE 和 UPDATE 语句。
  • 当 EXPLAIN 与可解释的语句一起使用时，MySQL 将显示优化器提供的关于语句执行计划的信息。
    显示信息可看到 MySQL 解释了如何处理语句，包括如何有关联接表及表的顺序的信息。
  • 当 EXPLAIN 与 FOR CONNECTION connection_id 一起使用则不是可解释的语句时，它将显示已命名的联接中的语句的执行计划。
  • 对于 SELECT 语句，EXPLAIN 会生成可以使用 SHOW WARNINGS 显示的其他执行计划信息(扩展信息)。
  • EXPLAIN 对于检查涉及分区表的查询非常有用。
  • FORMAT 选项可用于选择输出格式。默认是 TRADITIONAL ，即以表格显示输出。JSON 格式以 JSON 格式显示信息。

在 EXPLAIN 的帮助下，可以看到应该给表创建索引的位置，以便通过使用索引查询找来更快地执行语句。还可以使用 EXPLAIN 来检查优化器是否以最佳顺序联接表。

还可以提示优化器直接使用 SELECT 语句中命名表的顺序来执行表联接，而不是优化器优化后的顺序(相当于放弃优化的顺序)，这样就需要使用 SELECT STRAIGHT_JOIN，而不是只用 SELECT 作为语句的开始。但是 STRAIGHT_JOIN 可能会阻止使用索引，因为它会禁用半联接转换。

优化器跟踪有时可以提供与 EXPLAIN 的信息互补的信息。但优化器跟踪格式和内容在不同版本之间可能可能存在差异不。

如果认为优化器使用索引遇到问题，可使用 ANALYZE TABLE 来更新可能影响优化程序所做选择的表统计信息，例如键的基数。

注意：EXPLAIN 还可用于获取有关表中列的信息。expalin tbl_name 与 describe tbl_name 和 show columns from tbl_name 同义。

EXPLAIN 输出格式

EXPLAIN 为 select 语句中使用的每个表返回一行信息，是按照 MySQL 在处理语句时读取的表的顺序输出。

MySQL 使用嵌套循环联接方式解析所有联接。意思是 MySQL 从每一个表中读取一行，然后在每二个表，第三个表中找到匹配的行，依次类推。处理完所有表后，MySQL将通过表列表(table list)输出所选列和回溯(反向输出)，直到找到有更多匹配行的表，从该表中读取下一行，并继续执行下一个表。

EXPLAIN 输出包括分区信息，对于 SELECT 语句，还会生成扩展信息，可以在使用 EXPLAIN 后紧跟着使用 SHOW WARNINGS 来输出显示。

EXPLAIN 输出字段

EXPLAIN 信息以表的形式输出，表中每行就是一个相关表的信息。输出的信息的列字段如下：

列名 JSON 列名 意义
id select_id select id
select_type None select 类型
table table_name 该行分析对应的表名
partitions partitio 匹配的分区
type access_type join 类型
possible_keys possible_keys 可能选择的索引
key key 实际选择的索引
key_len key_length 实际选择的索引的长度
ref ref 常数或列名。
指与索引列比较的列或常数
rows rows 估计会检查的行数
filtered filtered 按表条件过滤行数的百分比
Extra None 附加信息

EXPLAIN 字段解释

  1. id (JSON name: select_id)
    select id，查询中 select 的序列号。

    多行且 id 相同，执行顺序由上至下执行；如果 id 不同，id 的序号递增，id 值越大， 优先级越高，越先被执行；id 值如果为 NULL 则最后执行。通常子查询的 SQL 语句 id 就会不相同。

    如果该行引用了其它行的联合结果，则该值可以为空。此情况下，table 列会显示一个类似于 [union M,N] 的值，以表示该行是指 ID 值为 M 和 N 的行的并集。

  2. select_type (JSON name: none)
    DEPENDENT：通常表示使用了相关子查询。
    DEPENDENT SUBQUERY：依赖子查询，仅对来自外部上下文的变量的每组不同的值进行一次重新评估。
    UNCACHEABLE SUBQUERY：不可缓存子查询，将为外部上下文的每一行重新计算子查询。

注意：子查询的可缓存性不同于在查询缓存中缓存查询结果。子查询缓存发生在查询执行期间；而查询缓存仅用于在查询执行完成后存储结果。
select_type 查询类型有以下类型

select_type 值 JSON 名 意义
SIMPLE None 简单查询(未使用 union 子查询)
PRIMARY None 最外层的 SELECT，主查询
UNION None UNION 中的第二个或更后的语句
DEPENDENT UNION dependent (true) UNION 中的第二个或更后的语句，依赖于外部查询
UNION RESULT union_result UNION 的结果
SUBQUERY None 子查询中的第一个 SELECT
DEPENDENT SUBQUERY dependent (true) 子查询中的第一个 SELECT 依赖于外部查询
DERIVED None 派生表(中间临时表：在 FROM 的子句中)
MATERIALIZED materialized_from_subquery 物化子查询(？待细究)
UNCACHEABLE SUBQUERY cacheable (false) 无法缓存结果的子查询，必须为外部查询的每一行重新计算其结果
UNCACHEABLE UNION cacheable (false) 属于不可缓存子查询的联合中的第二个或更后的 select

  1. table(JSON name: table_name)
    输出该行所引用的表的名称，除了具体的表名外，还可能有以下值：

    • [union M, N] ：该行引用了 id 值为 M 和 N 的联合结果。
    • [derived N]：该行引用 id 值为 N 的派生表的结果。派生表可能来自 FROM 子句中的子查询。
    • [subquery N]：该行引用了 id 值为 N 的行的物化子查询的结果。

  2. partitions(JSON name: partitions)
    查询所匹配的记录所在的分区，没有分区则显 NULL。

  3. type (JSON name: access_type)
    对查询联接类型的描述，请查看 EXPLAIN join type 章节。

  4. possible_keys (JSON name: possible_keys)
    可能选择的索引，如果值为 NULL ，则没有可用索引。

  5. key (JSON name: key)
    实际使用的索引。
    可能不在 possible_keys 中，如果 possible_keys 索引都不适合。但查询选择的所有列都是其它索引的列(索引列不在条件中，但在选择中)，则可能发生种情况。其实这是覆盖索引的使用，所选择的列是索引列，但该列不做为条件来查询，索引扫描比扫描表数据行更有效。
    对于 InnoDB ，即使查询也选择了主键，辅助索引也可能覆盖所选列(直接从索引取列的值)，因为 InnoDB 将主键值与每个辅助索引是一起存储的。
    如果 key 为 NULL ，MySQL 找不到可以更高效执行查询的索引。
    也可强制 MySQL 使用或忽略 possible_keys 中的索引，在查询中使用 FORCE INDEX, USE INDEX, 或者 IGNORE INDEX。

  6. key_len(JSON name: key_length)
    实际使用的索引的长度。用以确定 MySQL 实际使用索引的长度。如果 key 为 NULL ，则 key_len 为 NULL。
    由于索引存储格式的原因，可以为空的列的索引长度比不为空的列大一倍。

  7. ref(JSON name: ref)
    显示与索引列进行比较的常量或列。
    如果为 func ，则使用的值是某个函数的结果。查看具体是哪个函数，可用 SHOW WARNING 显示输出优化后完整的 SQL。

  8. rows(JSON name: rows)
    MySQL 认为执行查询必须检查的行数。
    对于 InnoDB 表，该值并不精确，只是个估算值。

  9. filtered(JSON name: filtered)
    按条件过滤的表行的估算百分比。最大值为 100，表示没有进行过滤就可以直接找到具体的行(如主键或唯一索引)，值越小，表示过滤(查询)的行数越多。rows × filtered 显示将与下表联接的行数。

  10. Extra(JSON name: none)
    显示 MySQL 如何解析查询的其他信息。

EXPLAIN 联接类型

EXPLAIN 输出的 type 列描述表的联接方式。以下是从最佳类型到最差类型排序的联接类型：

  1. system
    该表只有一行(系统表)。这是 const join 的一个特殊情况。

  2. const
    最多只有一个匹配行(主键或唯一索引)，在一开始查询时就读取到，只读取一次，非常快，优化器的其余部分可以将此列中的值视为常量。

  3. eq_ref
    根据前一个查询计划结果表中的行，关联读取当前表中的一行。实际取的是上一个表条件结果行中的主键或唯一非空索引列做为条件来关联当前表，即上一个表查询的是常量值或非空唯一值。
    该类型是除了 system 和 const 类型之外最好的联接类型。
    当一个索引的所有部分都被联接使用并且该索引是主键或唯一非空索引(UNIQUE NOT NULL)时，会使用该类型。
    该类型可使用 = 运算符比较索引列。比较值可以是常量，也可以是使用在此表之前读取上一个表中的列作为表达式。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    //上一个表
    SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

    //常量比较
    SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

    mysql> explain select * from film,film_actor,actor where film.film_id = film_actor.film_id and film_actor.actor_id = actor.actor_id and film.film_id = 10;
    +----+-------------+------------+------------+--------+------------------------+----------------+---------+----------------------------+------+----------+-------+
    | id | select_type | table      | partitions | type   | possible_keys          | key            | key_len | ref                        | rows | filtered | Extra |
    +----+-------------+------------+------------+--------+------------------------+----------------+---------+----------------------------+------+----------+-------+
    |  1 | SIMPLE      | film       | NULL       | const  | PRIMARY                | PRIMARY        | 2       | const                      |    1 |      100 | NULL  |
    |  1 | SIMPLE      | film_actor | NULL       | ref    | PRIMARY,idx_fk_film_id | idx_fk_film_id | 2       | const                      |    8 |      100 | NULL  |
    |  1 | SIMPLE      | actor      | NULL       | eq_ref | PRIMARY                | PRIMARY        | 2       | sakila.film_actor.actor_id |    1 |      100 | NULL  |
    +----+-------------+------------+------------+--------+------------------------+----------------+---------+----------------------------+------+----------+-------+

  4. ref
    为前一个表中的行，在当前表中读取所有能匹配索引值的行。
    如果联接仅使用最左前缀索引，或者使用的不是主键或唯一索引(即联接无法基于索引选择单行)，则使用 ref 联连类型。如果所使用的索引只匹配几行，这是一种较好的联接类型。
    该类型可用于使用 = 或 <=> 运算行比较的索引列。

    1
    2
    3
    4
    5
    6
    7
    mysql> explain select * from film,film_actor where film.film_id = film_actor.film_id and film.film_id = 10;
    +----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
    | id | select_type | table      | partitions | type  | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
    +----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | film       | NULL       | const | PRIMARY        | PRIMARY        | 2       | const |    1 |      100 | NULL  |
    |  1 | SIMPLE      | film_actor | NULL       | ref   | idx_fk_film_id | idx_fk_film_id | 2       | const |    8 |      100 | NULL  |
    +----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+

  5. fulltext
    使用全文索引 FULLTEXT 执行联接。

  6. ref_or_null
    与 ref 类型相似，但对包含了 NULL 值的行进行额外搜索。此联接类型优化常用于解析子查询。如下语句：

    1
    2
    SELECT * FROM ref_table
      WHERE key_column=expr OR key_column IS NULL;

  7. index_merge
    此联接类型使用了索引合并优化。
    此情况下，输出行中的 key 列包含使用的索引列表(多个索引)，key_len 列包含所使用索引的最长键部分的列表。

  8. unique_subquery
    在一些 IN 子查询语句中，此类型替换 eq_ref 类型。示例如下：

    1
    value IN (SELECT primary_key FROM single_table WHERE some_expr)

    unique_subquery 只是一个索引查找函数，它可以完全替换子查询以提供效率。

  9. index_subquery
    此联接类型类似于 unique_subquery。取代了 IN 子查询，适用于以下非唯一索引的子查询。

    1
    value IN (SELECT key_column FROM single_table WHERE some_expr)

  10. range
    只检索给定范围内的行，使用索引选择行。key 列指示所使用的索引，key_len 包含使用 key(index) 的最长度，此类型的 ref 列为 NULL。
    当使用=、<>、>、>=、<、<、<=、is NULL、<=>、between、like 或 in() 运算符将键列与常量进行比较时，可以使用 range。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT * FROM tbl_name
      WHERE key_column = 10;

    SELECT * FROM tbl_name
      WHERE key_column BETWEEN 10 and 20;

    SELECT * FROM tbl_name
      WHERE key_column IN (10,20,30);

    SELECT * FROM tbl_name
      WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

  11. index
    index 联接类型与 ALL 类型相同，但只扫描索引树。有两种方式：

    • 如果查询的索引是覆盖索引（即查询的列和条件列都是索引列：根据索引取索引的值，包括条件索引值和其它列索引的值），则只扫描索引树。此情况下 Extra 列显示 Using index。只扫描索引通常比全表扫描快，因为索引的大小通常小于表数据。
    • 非覆盖索引，在 Extra 列中不会显示 Using index。[官译：读取索引来执行全表扫描，并按索引顺序查找数据行。很怀疑全表扫描(full table scan)这个词在这里的正确性]

    当查询仅使用属于单个索引的列时，MySQL 使用些联接类型。该类型更多是 Extra 中显示。

    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
    -- actor_id 列创建了主键索引，last_name 列创建了普通索引，first_name 列没有创建索引

    -- 第一种方式：覆盖索引
    mysql> EXPLAIN SELECT actor.`actor_id` FROM actor WHERE last_name = 'AKROYD';
    +----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+
    |  1 | SIMPLE      | actor | NULL       | ref  | idx_actor_last_name | idx_actor_last_name | 137     | const |    3 |      100 | Using index |
    +----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+
    1 row in set

    mysql> EXPLAIN SELECT actor.`actor_id`, actor.`last_name` FROM actor WHERE last_name = 'AKROYD';
    +----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+
    |  1 | SIMPLE      | actor | NULL       | ref  | idx_actor_last_name | idx_actor_last_name | 137     | const |    3 |      100 | Using index |
    +----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+
    1 row in set

    -- 第二种情况：根据索引，按索引顺序对条件筛选后的数据进行全扫描。
    mysql> EXPLAIN SELECT actor.`actor_id`, actor.`first_name` FROM actor WHERE last_name = 'AKROYD';
    +----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | actor | NULL       | ref  | idx_actor_last_name | idx_actor_last_name | 137     | const |    3 |      100 | NULL  |
    +----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
    1 row in set

  12. ALL
    对前一个表进行全表扫描，如果是第一个表，且 key 列不是 const ，通常是不好的，并且相比其它类型是最坏的。
    应尽可能避免 ALL 类型的查询，特别是在大数据量情况下，性能会非常差，可通过创建索引、改良SQL来优化查询。

EXPLAIN 扩展输出

对于 SELECT 语句，EXPLAIN 生成了额外的扩展信息，这些信息不随 EXPALIN 一起输出，但可以过 EXPLAIN 之后，使用 SHOW WARNINGS 语句来查看。

SHOW WARNINGS 输出中的 Message 值显示优化器如何限定 SELECT 语句中的表名和列名、SELECT 语句在优化和重写后的样子、以及有关优化过程的其它注意事项。

SHOW WARNINGS 仅在 EXPLAIN 之后使用，仅对 SELECT 有效。对于 DELETE、INSERT、REPLACE 和 UPDATE 语句，结果显示为空。

SHOW WARNINGS 显示的语句包含了特殊标记以提供有关查询重写和优化操作的信息，因此该语句不一定是有效的 SQL，也不打算执行。输出还可以包括 Message 值的行，这些信息还提供有关优化器所采取操作的其他非 SQL 注释。

特殊标记

以下列表描述了可以出现在SHOW WARNINGS显示的扩展输出中的特殊标记：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a` AS `a`,
         <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in
         ( <materialize> (/* select#2 */ select `test`.`t2`.`a`
         from `test`.`t2` where 1 having 1 ),
         <primary_index_lookup>(`test`.`t1`.`a` in
         <temporary table> on <auto_key>
         where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `t1.a
         IN (SELECT t2.a FROM t2)` from `test`.`t1`

  1. auto_key

    临时表的自增键。

  2. cache(expr)

    执行一次的表达式(如子查询值)，结果值保存在内存中供以后使用。如果结果包含多个值，可能会创建一个临时表来保存。

  3. exists(query fragment)

    子查询判断转换使用 EXISTS。

  4. in_optimizer(query fragment)

    一个对用户没有意义的内部优化器对象。

  5. index_lookup(query fragment)

    使用索引查找处理查询片段以查找符合条件的行。

  6. if(condition, expr1, expr2)

    if 条件为 true，执行 expr1，否则执行 expr2。

  7. is_not_null_test(expr)

    用于验证表达式的计算结果不为 NULL 的测试

  8. materialize(query fragment)

    使用子查询物化(?不易理解)。

  9. materialized-subquery.col_name

    对内部临时表中的 col_name 的引用，该列用以保存评估子查询的结果。

  10. primary_index_lookup(query fragment)

    使用主键查找处理查询片段以查找符合条件的行。

  11. ref_null_helper(expr)

    一个对用户没有意义的内部优化器对象。

  12. /* select#N */ select_stmt

    SELECT 与非扩展 EXPLAIN 输出中具有 id 值为 N 的行相关联。

  13. outer_tables semi join (inner_tables)

    半连接操作。inner_tables 显示未拦出的表。

  14. temporary table

    为缓存中间结果而创建的内部临时表。

当某些表是 const 或 system 类型时，涉及这些表中列的表达式由优化器提前计算，并且不是所显示SQL语句的一部分。

查询性能评估

在大多数情况下，可以通过计算磁盘指标使用来评估查询性能。
对于小表，通常可以在一个磁盘中找到一行（因为索引可能已缓存）。
对于大表，可以使用 B树索引 来估算查找行所需要的搜索次数，公式如下：

1
log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1

在 MySQL 中，索引块通常为 1024 字节，数据指针通常为 4字节。对于索引长度为 3 个字节（MEDIUMINT 大小）的 500000行表，该公式计算如下：

1
log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 seeks

这个索引需要大约 (500000 * 7 * 3) / 2 = 5.2 MB 的存储(假设典型的索引缓冲区填充率为 2/3，即索引缓冲区使用率 2/3，给定的数据大小需要占用更多的存储空间)，所以可能在内存中存有很多索引，只需一到两个调用来读取索引数据查找行。

但是、对于写入，需要四个 SEEK 请求来查找放置新索引值的位置，通常两个请求更新索引并写入行。

前面的讨论并不意味着应用的性能会随着 log N 的增加而慢慢变差。
只要操作系统或 MySQL服务器缓存了所有内容，表越大，速度就越慢。当数据变得太大而无法缓存时，搜索会变得更慢，直到应用程序仅被磁盘搜索约束（这会增大 log N）。
要避免这种情况，可以随着数据的增长增加 key（index）缓存的大小。对于 MyISAM 表，key 缓存大小由 key_buffer_size 系统变量控制。

查看连接执行计划

Explain for Connection 返回当前用于在给定连接中执行查询的解释信息，这些解释信息可能与运行 explain 语句的查询操作的结果并不相同，这种行为差异对于诊断更多的瞬态性能问题很有用。

1
EXPLAIN [options] FOR CONNECTION connection_id;

connection_id 是从 information_schema processlist 表或 show processlist 语句中获取的连接标识符。

如果指定的连接未执行语句，则结果为空。否则，仅当可以解释在连接中执行的语句时，EXPLAIN FOR CONNECTION 才适用，这包括 SELECT，DELETE，INSERT，REPLACE 和 UPDATE 操作。

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|             373 |
+-----------------+
1 row in set (0.00 sec)

mysql> EXPLAIN FOR CONNECTION 373;
ERROR 1889 (HY000): EXPLAIN FOR CONNECTION command is supported
only for SELECT/UPDATE/INSERT/DELETE/REPLACE

MySQL系列(16)：理解执行计划-EXPLAIN

http://blog.gxitsky.com/2019/03/10/MySQL-16-Optimizing-Query-Execution-Plan-Explain/

作者

光星

发布于

2019-03-10

更新于

2023-03-06

许可协议

#

喜欢这篇文章？打赏一下作者吧

评论

关注我
×