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

开发运维 2023-08-15 穿过生命散发芬芳手机阅读

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

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 结果输出展示

结果解读
- 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)