[大数据]【Mysql性能优化系列】查询执行计划EXPLAIN使用详解

前言

人生病了需要医生通过各种医疗工具分析病情，那么SQL“生病了”该怎么办呢？本文将通过执行计划EXPLAIN了解SQL的“身体状况”。

本文以MySQL5.7为例。

EXPLAIN简介

当EXPLAIN与可解释语句一起使用时，MySQL 显示来自优化器的有关语句执行计划的信息。。

EXPLAIN可以与 SELECT, DELETE, INSERT, REPLACE, 和 UPDATE语句一起使用。

EXPLAIN输出格式

EXPLAINSELECT为语句中使用的每个表返回一行信息 。它按照 MySQL 在处理语句时读取它们的顺序列出输出中的表。MySQL使用嵌套循环连接方法解析所有连接。

也可以使用MySQL Workbench 具有的 Visual Explain 功能，可提供 EXPLAIN输出的可视化表示.

输出列说明

EXPLAIN SELECT * FROM TEST 

id（JSON 名称 select_id：）

代表sql中查询语句的序列号，序列号越大则执行的优先级越高，序号一样谁在前谁先执行。id为null则最后执行。

select_type（JSON 名称：无）

SELECT的类型，可以是下表中显示的任何一种。

table（JSON 名称 table_name：）

输出行所引用的表的名称。也可以是以下值之一：

<unionM,N>：该行是指具有 和 id值 的行的M并 集N。

< derivedN >：该行是指值为 的行的派生表id结果 N。例如，派生表可能来自 FROM子句中的子查询。

< subqueryN>：该行指的是id 值为的行的具体化子查询的结果N。

partitions（JSON 名称 partitions：）

查询将匹配记录的分区。该值适用NULL于非分区表。

type（JSON 名称 access_type：）

连接类型。

possible_keys（JSON 名称 possible_keys：）

这个字段显示的是sql在查询时可能使用到的索引，但是不一定真的使用，只是一种可能。

如果此列是NULL（或在 JSON 格式的输出中未定义），则没有相关索引。在这种情况下，您可以通过检查WHERE 子句来检查它是否引用了适合索引的某些列或列，从而提高查询的性能。如果是这样，请创建一个适当的索引并 EXPLAIN再次检查查询。

查看表有哪些索引

SHOW INDEX FROM tbl_name

key（JSON 名称key：）

该key列指示 MySQL 实际决定使用的键（索引）。如果 MySQL 决定使用其中一个possible_keys索引来查找行，则该索引被列为键值。

要强制 MySQL 使用或忽略列中列出的索引 ，请在查询中possible_keys使用 FORCE INDEX、USE INDEX或IGNORE INDEX。

key_len（JSON 名称 key_length：）

用到的索引字段的长度，通过这个字段可以显示具体使用到了索引字段中的哪些列（主要针对联合索引）：计算公式如下

字符串
char(n)：n字节长度

     varchar(n)：如果是utf-8，则长度 3n + 2 字节，加的2字节用来存储字符串长度

数值类型
tinyint：1字节

     smallint：2字节  

     int：4字节  

     bigint：8字节

时间类型
date：3字节

       timestamp：4字节  

       datetime：8字节

如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

ref（JSON 名称ref：）

表示那些列或常量被用于查找索引列上的值

如果值为func，则使用的值是某个函数的结果。

rows（JSON 名称 rows：）

表示在查询过程中检索了多少列 但是并不一定就是返回这么多列数据。

对于InnoDB表格，这个数字是一个估计值，可能并不总是准确的。

filtered（JSON 名称 filtered：）

该filtered列指示按表条件过滤的表行的估计百分比。最大值为 100，这意味着没有过滤行。从 100 开始减小的值表示过滤量增加。 rows显示检查的估计行数，rows× filtered显示与下表连接的行数。例如，如果 rows是 1000 并且 filtered是 50.00 (50%)，则要与下表连接的行数是 1000 × 50% = 500。

Extra（JSON 名称：无）

展示一些额外信息。

连接类型type

输出列描述type了 EXPLAIN表是如何连接的。
以下列表描述了连接类型，按从最佳到最差的顺序排列：

system > const > eq_ref > ref > range > index > all;

一般来说我们优化到range就可以了 最好到ref。

详细如下，按从最佳到最差的顺序排列：

system

该表只有一行（= 系统表）。这是连接类型const 的一个特例 。

const

该表最多有一个匹配行，在查询开始时读取。因为只有一行，所以这一行中列的值可以被优化器的其余部分视为常量。const表非常快，因为它们只被读取一次。

eq_ref

在进行连接查询时，例如left join 时，如果是使用主键索引或者唯一索引连接查询，结果返回一条数据，则type的值为一般为eq_ref。

ref

相比较eq_ref,不使用主键索引或者唯一索引，使用的是普通索引或者唯一索引的部分前缀，索引与一个值进行比较后可能获取到多个符合条件的行，不在是唯一的行。

fulltext

连接是使用FULLTEXT 索引执行的。

ref_or_null

这种连接类型类似于 ref，但另外 MySQL 会额外搜索包含NULL值的行。这种连接类型优化最常用于解析子查询。

index_merge

此连接类型表明使用了索引合并优化。在这种情况下，key输出行中的列包含所用索引的列表，并 key_len包含所用索引的最长键部分的列表。

unique_subquery

此类型替换 以下形式eq_ref的某些 IN子查询：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery只是一个索引查找功能，完全替代子查询以提高效率。

index_subquery

此连接类型类似于 unique_subquery. 它替换IN子查询，但它适用于以下形式的子查询中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

range

通常使用范围查找，例如between，in，<,>,>=等使用索引进行范围检索。

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);

index

index连接类型与ALL相同 ，只是扫描了索引树。这有两种方式：

• 如果索引是查询的覆盖索引并且可以用于满足表中所需的所有数据，则仅扫描索引树。在这种情况下，该Extra列 显示Using index。
• 使用从索引中读取以按索引顺序查找数据行来执行全表扫描。Extra不显示 Uses index。

当查询仅使用属于单个索引的列时，MySQL 可以使用此连接类型。

ALL

这是一种效率最低的type，需要扫描主键索引树的叶子节点，获取数据是表中其他列的数据，即全表扫描。

扩展 EXPLAIN 输出格式

对于SELECT语句， EXPLAIN语句会产生额外的（“扩展”）信息，这些信息不是 EXPLAIN输出的一部分，但可以通过在.SHOW WARNINGS 后面发出语句来查看EXPLAIN。SHOW WARNINGS输出中的 Message值显示优化器如何限定 SELECT语句中的表名和列名，SELECT应用重写和优化规则后的样子，以及可能有关优化过程的其他注释。

扩展 EXPLAIN输出的示例：

mysql> EXPLAIN
       SELECT t1.a, t1.a IN (SELECT t2.a FROM t2) FROM t1\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: t2
         type: index
possible_keys: a
          key: a
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

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 row in set (0.00 sec)

SHOW WARNINGS扩展输出中的特殊标记：

< auto_key>

为临时表自动生成的键。

< cache>(expr)

表达式（例如标量子查询）执行一次，结果值保存在内存中供以后使用。对于包含多个值的结果，可能会创建一个临时表，可能会看到它 < temporary> table>。

< exists>(query fragment)

将子查询谓词转换为 EXISTS谓词，并对子查询进行转换，以便它可以与 EXISTS谓词一起使用。

<in_optimizer>(query fragment)

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

<index_lookup>(query fragment)

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

< if>(condition, expr1, expr2)

如果条件为真，则计算为 expr1，否则 为expr2。

<is_not_null_test>(expr)

验证表达式不计算为 的测试 NULL。

< materialize>(query fragment)

使用子查询实现。

materialized-subquery.col_name

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

<primary_index_lookup>(query fragment)

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

<ref_null_helper>(expr)

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

/ select#N / select_stmt

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

outer_tables semi join (inner_tables)

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

< temporary table>

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

当某些表属于const 或者system类型时，涉及这些表中的列的表达式由优化器提前求值，而不是显示语句的一部分。但是，使用 时FORMAT=JSON，某些 const表访问会显示为ref使用 const 值的访问。

获取连接中的执行计划信息

获取在连接中执行的可解释语句的执行计划：

EXPLAIN [options] FOR CONNECTION connection_id;

EXPLAIN FOR CONNECTION返回EXPLAIN当前用于在给定连接中执行查询的信息。

connection_id是从 INFORMATION_SCHEMA PROCESSLIST表或 SHOW PROCESSLIST语句中获得的连接标识符。如果有PROCESS权限，可以指定任何连接的标识符。否则，只能为自己的连接指定标识符。

如果命名连接没有执行语句，则结果为空。否则，EXPLAIN FOR CONNECTION 仅当在命名连接中执行的语句是可解释的时才适用。这包括 SELECT、 DELETE、 INSERT、 REPLACE和 UPDATE。（但是， EXPLAIN FOR CONNECTION不适用于准备好的语句，即使是那些类型的准备好的语句。）

如果命名连接正在执行一个可解释的语句，那么输出就是您 EXPLAIN在语句本身上使用所获得的结果。

如果命名连接正在执行无法解释的语句，则会发生错误。例如:

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

Com_explain_otherstatus 变量表示执行的 语句EXPLAIN FOR CONNECTION数。

查询性能估算

在大多数情况下，可以通过计算磁盘寻道次数来估计查询性能。对于小型表，通常可以在一次磁盘查找中找到一行（因为索引可能已缓存）。对于更大的表，可以使用 B-tree 索引估计，需要这么多次查找才能找到一行：

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

在 MySQL 中，索引块通常为 1,024 字节，数据指针通常为 4 字节。对于一个 500,000 行的表，键值长度为 3 个字节（大小为 MEDIUMINT），公式表示 log(500,000)/log(1024/3*2/(3+4)) + 1= 4seeks。

该索引需要大约 500,000 * 7 * 3/2 = 5.2MB 的存储空间（假设典型的索引缓冲区填充率为 2/3），可能在内存中有很多索引，因此只需要一两次调用读取数据以查找行。

对于写入，需要四个查找请求来查找放置新索引值的位置，需要两次查找来更新索引并写入行。

点赞 收藏 关注