sql分析工具explain详解

溯游从之～

已于 2023-04-04 15:21:01 修改

阅读量6.5k

点赞数 2

分类专栏： mysql 文章标签： sql 数据库 mysql

于 2023-04-03 15:08:49 首次发布

原文链接：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

版权

mysql 专栏收录该内容

3 篇文章 1 订阅

订阅专栏

mysql5.7execution-plan文档
explain命令也叫查询执行计划，是根据表、列、索引和WHERE子句中条件的详细信息，MySQL优化器会考虑许多技术来高效执行SQL查询中涉及的查找。可以在不读取所有行的情况下执行对大表的查询；在不比较每个行组合的情况下执行涉及多个表的连接。

使用

使用起来非常简单 explain [yourSql]
explain可以与select、delete、insert、replace和update语句配合使用。因为用来分析select较多，所以接下来就以select来示例。

比如我们要获取以下sql的执行计划

explain
SELECT id, status, uuid, bill_status, plain_data
     , sender, receiver, vin, plan_no, shipment_no
     , base, pub_key_decoder, shipped_date, pub_key_encryptor, enc
     , plan_type, settlement_type, owner_flag
FROM bill_plan
WHERE 1 = 1
  AND sender = 'TestH2'
  AND settlement_type = 'INCOME'
  AND owner_flag = 1
  AND bill_status = 'ssnConfirmed'
  AND base IN ('1','2','3','4','5','6','7')
  AND receiver IN ('Test1','Test2')
ORDER BY id DESC
LIMIT 200;

执行后获取到以下内容
explain执行结构
可以看到输出了很多字段，各个字段的含义分别是：

列名	含义
id	SELECT标识符
select_type	SELECT类型
table	输出行的表
partitions	匹配的分区
type	连接类型
possible_keys	可能的索引可供选择
key	实际选择的索引
key_len	所选密钥的长度
ref	与索引相比的列
rows	要检查的行的估计
filtered	按表格条件过滤的行百分比
Extra	其他信息

字段含义解释

id

这个是sql的序号，没有特殊的含义，可不关注

select_type

select的类型，类型较多

select_type	含义
SIMPLE	简单SELECT（不使用UNION或子查询）
PRIMARY	最外层SELECT
UNION	第二个或稍后的SELECT语句UNION
DEPENDENT UNION	UNION中的第二个或稍后SELECT语句，取决于外部查询
UNION RESULT	UNION的结果
SUBQUERY	子查询中的第一个SELECT
DEPENDENT SUBQUERY	子查询中的第一个SELECT，取决于外部查询
DERIVED	派生表
MATERIALIZED	物质化子查询
UNCACHEABLE SUBQUERY	无法缓存结果的子查询，必须为外部查询的每一行重新评估
UNCACHEABLE UNION	第二个或稍后在UNION中选择属于不可缓存的子查询

table

查询表名

type

连接类型，我们如果要优化，type至少也要是range级别以上。性能从好到差如下：

类型	描述
system	该表只有一行（=系统表）。这是const连接类型的特例。
const	该表最多有一个匹配的行，在查询开始时读取。因为只有一行，所以该行中列的值可以被优化器的其余部分视为常量。const表非常快，因为它们只读一次。通过PRIMARY KEY或UNIQUE索引用=查询，例`SELECT * FROM tbl_name WHERE primary_key=1;` 或者 `SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;`
eq_ref	对于上一个表中的每行组合，都会从此表中读取一行。除了system和const类型外，这是最好的连接类型。当连接使用索引的所有部分，并且索引是PRIMARY KEY或UNIQUE NOT NULL索引时它使用的类型。`SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;`
ref	对于之前表中的每个行组合，都会从此表中读取所有具有匹配索引值的行。如果连接仅使用键的最左边前缀，或者如果键不是PRIMARY KEY或UNIQUE索引（换句话说，如果连接无法根据键值选择单行），则使用ref如果使用的键只匹配几行，这是一个很好的连接类型。
ref_or_null	这个连接类型类似于ref，但是MySQL会对包含NULL值的行进行额外搜索。这种连接类型优化最常用于解析子查询。在下面的例子中，MySQL可以使用ref_or_null连接来处理ref_table:`SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;`
index_merge	此连接类型表示使用了索引合并优化。在这种情况下，输出行中的key列包含所用索引的列表，key_len包含所用索引的最长键部分的列表
unique_subquery	此类型替换了以下形式的一些IN子查询的eq_ref：`value IN (SELECT primary_key FROM single_table WHERE some_expr)`
index_subquery	此连接类型类似于unique_subquery。它取代了IN子查询，但它适用于子查询中的非唯一索引
range	仅检索给定范围内的行，使用索引选择行。输出行中的key列指示使用哪个索引。key_len包含使用过的最长键部分。此类型的ref列为NULL。range当使用任何=、<>、>、>=、<=、IS NULL、<=>、BETWEEN、LIKE或IN()运算符将键列与常量进行比较时，可以使用：`SELECT * FROM tbl_name WHERE key_part1 = 10 AND key_part2 IN (10,20,30);`
index	当查询仅使用作为单个索引一部分的列时
ALL	全表扫描

possible_keys

possible_keys列显示MySQL可以选择使用的索引。请注意，此列与EXPLAIN输出中显示的表格顺序完全无关。这意味着possible_keys中的key与实际使用的不一致。

如果此列为NULL（或在JSON格式的输出中未定义），则没有使用索引或表没有索引。

key

MySQL实际决定使用的索引，不一定是possible_keys中列出的，因为MyISAM会通过运行ANALYZE TABLE来选择更好的索引。

key_len

key_len列表示MySQL决定使用的索引的长度。key_len的值使您能够确定MySQL实际使用的多部分索引的多少部分。如果key列显示NULL，则key_len列也显示NULL。

ref

ref列显示哪些列或常量与key列中命名的索引进行比较，以便从表中选择行。

如果值是func，则使用的值是某个函数的结果。要查看哪个功能，请使用EXPLAIN之后SHOW WARNINGS来查看扩展的EXPLAIN输出。该函数实际上可能是一个运算符，例如算术运算符。

rows

rows表示MySQL认为必须检查才能执行查询的行数。

filtered

filtered列表示按表条件过滤的表行的估计百分比。越高越好
最大值为100，这意味着没有发生行过滤。从100减少的值表示过滤量增加。rows显示检查的估计行数，×filteredrows显示与下表连接的行数。例如，如果rows是1000，filtered是50.00（50%），则与下表连接的行数为1000 × 50% = 500。