MySQL 执行计划详解 | 京东物流技术团队-每日运维

1. EXPLAIN详解

本文基于MySQL 8.0编写，理论支持MySQL 5.0及更高版本。

2. EXPLAIN使用

2.1 explain分析SQL的执行计划

{EXPLAIN | DESCRIBE | DESC}
  tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

{EXPLAIN | DESCRIBE | DESC} ANALYZE select_statement    

explain_type: {
    FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
  | TREE
}

explainable_stmt: {
    SELECT statement
  | TABLE statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

2.2 示例

EXPLAIN format = TRADITIONAL json SELECT tt.TicketNumber, tt.TimeIn,
        tt.ProjectReference, tt.EstimatedShipDate,
        tt.ActualShipDate, tt.ClientID,
        tt.ServiceCodes, tt.RepetitiveID,
        tt.CurrentProcess, tt.CurrentDPPerson,
        tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
        et_1.COUNTRY, do.CUSTNAME
FROM tt, et, et AS et_1, do
WHERE tt.SubmitTime IS NULL
  AND tt.ActualPC = et.EMPLOYID
  AND tt.AssignedPC = et_1.EMPLOYID
  AND tt.ClientID = do.CUSTNMBR;

2.3 结果输出展示

3. 结果解读

id：该语句的唯一标识。如果explain的结果包括多个id值，则数字越大越先执行；而对于相同id的行，则表示从上往下依次执行。
select_type：查询类型，有如下几种取值：

table：表示当前这一行正在访问哪张表，如果SQL定义了别名，则展示表的别名
**partitions：**当前查询匹配记录的分区。对于未分区的表，返回null
type：连接类型，有如下几种取值，性能从好到坏排序如下：

◦system：该表只有一行（相当于系统表），system是const类型的特例

◦const：针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可

◦eq_ref：当使用了索引的全部组成部分，并且索引是PRIMARY KEY或UNIQUE NOT NULL 才会使用该类型，性能仅次于system及const。

-- 多表关联查询，单行匹配
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;

◦ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的。

-- 根据索引（非主键，非唯一索引），匹配到多行
SELECT * FROM ref_table WHERE key_column=expr;

-- 多表关联查询，单个索引，多行匹配
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;

TIPS最左前缀原则

指的是索引按照最左优先的方式匹配索引。比如创建了一个组合索引(column1, column2, column3)，那么，如果查询条件是： WHERE column1 = 1、WHERE column1= 1 AND column2 = 2、WHERE column1= 1 AND column2 = 2 AND column3 = 3 都可以使用该索引； WHERE column1 = 2、WHERE column1 = 1 AND column3 = 3就无法匹配该索引。

◦fulltext：全文索引ref_or_null：该类型类似于ref，但是MySQL会额外搜索哪些行包含了NULL。这种类型常见于解析子查询

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

◦index_merge：此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引

◦unique_subquery：该类型和eq_ref类似，但是使用了IN查询，且子查询是主键或者唯一索引。例如：value IN (SELECT primary_key FROM single_table WHERE some_expr)

◦index_subquery：和unique_subquery类似，只是子查询使用的是非唯一索引。value IN (SELECT key_column FROM single_table WHERE some_expr)

◦range：范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常见的范围扫描是带有BETWEEN子句或WHERE子句里有>、>=、 EXPLAIN
SELECT t1.a, t1.a IN (SELECT t2.a FROM t2) FROM t1G
*************************** 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 WARNINGSG
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a` AS `a`,
(`test`.`t1`.`a`,`test`.`t1`.`a` in
( (/* select#2 */ select `test`.`t2`.`a`
from `test`.`t2` where 1 having 1 ),
(`test`.`t1`.`a` in
on
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 WARNING的结果并不一定是一个有效SQL，也不一定能够执行（因为里面包含了很多特殊标记）。特殊标记取值如下：

◦：自动生成的临时表key

◦(expr)：表达式（例如标量子查询）执行了一次，并且将值保存在了内存中以备以后使用。对于包括多个值的结果，可能会创建临时表，你将会看到的字样

◦(query fragment)：子查询被转换为 EXISTS

◦(query fragment)：这是一个内部优化器对象，对用户没有任何意义

◦(query fragment)：使用索引查找来处理查询片段，从而找到合格的行

◦(condition, expr1, expr2)：如果条件是true，则取expr1，否则取expr2

◦(expr)：验证表达式不为NULL的测试

◦(query fragment)：使用子查询实现

◦materialized-subquery.col_name：在内部物化临时表中对col_name的引用，以保存子查询的结果

◦(query fragment)：使用主键来处理查询片段，从而找到合格的行

◦(expr)：这是一个内部优化器对象，对用户没有任何意义

◦/* select#N */ select_stmt：SELECT与非扩展的EXPLAIN输出中id=N的那行关联

◦outer_tables semi join (inner_tables)：半连接操作。inner_tables展示未拉出的表。详见 “Optimizing Subqueries, Derived Tables, and View References with Semijoin Transformations”

◦：表示创建了内部临时表而缓存中间结果

当某些表是const或system类型时，这些表中的列所涉及的表达式将由优化器尽早评估，并且不属于所显示语句的一部分。但是，当使用FORMAT=JSON时，某些const表的访问将显示为ref。

5. 估计查询性能

多数情况下，你可以通过计算磁盘的搜索次数来估算查询性能。对于比较小的表，通常可以在一次磁盘搜索中找到行（因为索引可能已经被缓存了），而对于更大的表，你可以使用B-tree索引进行估算：你需要进行多少次查找才能找到行：log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1
在MySQL中，index_block_length通常是1024字节，数据指针一般是4字节。比方说，有一个500,000的表，key是3字节，那么根据计算公式 log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 次搜索。
该索引将需要500,000 * 7 * 3/2 = 5.2MB的存储空间（假设典型的索引缓存的填充率是2/3），因此你可以在内存中存放更多索引，可能只要一到两个调用就可以找到想要的行了。
但是，对于写操作，你需要四个搜索请求来查找在何处放置新的索引值，然后通常需要2次搜索来更新索引并写入行。
前面的讨论并不意味着你的应用性能会因为log N而缓慢下降。只要内容被OS或MySQL服务器缓存，随着表的变大，只会稍微变慢。在数据量变得太大而无法缓存后，将会变慢很多，直到你的应用程序受到磁盘搜索约束（按照log N增长）。为了避免这种情况，可以根据数据的增长而增加key的。对于MyISAM表，key的缓存大小由名为key_buffer_size的系统变量控制，详见Section 5.1.1, “Configuring the Server”

6. 参考文档

EXPLAIN Output Format
EXPLAIN Statement
Extended EXPLAIN Output Format
Estimating Query Performance
MySQL中explain执行计划中额外信息字段(Extra)详解
explain参数详解
最官方的 mysql explain type 字段解读
What does eq_ref and ref types mean in MySQL explain
面试官：不会看 Explain执行计划，简历敢写 SQL 优化？

作者：京东物流柳宏

来源：京东云开发者社区自猿其说Tech 转载请注明来源

MySQL 执行计划详解 | 京东物流技术团队

2.1 explain分析SQL的执行计划

2.2 示例

2.3 结果输出展示

相关文章

发布评论取消回复

穿过生命散发芬芳

高速电路开发中有哪些挑战？

电脑无法远程桌面连接，电脑无法远程桌面连接的原因

OB Cloud 云数据库在西南！深耕海量场景，构建现代数据架构

MySQL 执行计划详解 | 京东物流技术团队

2.1 explain分析SQL的执行计划

2.2 示例

2.3 结果输出展示

相关文章

发布评论 取消回复

穿过生命散发芬芳

高速电路开发中有哪些挑战？

电脑无法远程桌面连接，电脑无法远程桌面连接的原因

OB Cloud 云数据库在西南！深耕海量场景，构建现代数据架构

发布评论取消回复