MySQL｜explain 执行计划

2021/06/23 #MySQL [字体小·中·大]

为了知道 SQL 的效率执行的怎么样，经常使用 explain 查看 SQL 的具体执行过程，以加快 SQL 的执行效率。

explain 语句官方说明文档：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

数据库版本 MySQL 8.0

explain 使用示例

使用 explain 对 SQL 进行分析，会得到一个或多个结果。

explain 字段含义

id
- id 相同表示再同一组，同一组中的查询从上往下执行
- id 不同，可能存在子查询，值越大优先级越高，则先执行
select_type 用来查看查询的类型，是普通查询、联合查询或子查询

table 当前查询正在访问的表，表名或别名
partitions 分区信息
type 指明查询访问的类型。
possible_keys 显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
key 实际使用的索引，如果为 null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠
key_len 表示索引中使用的字节数，可以通过 key_len 计算查询中使用的索引长度，在不损失精度的情况下长度越短越好
ref 显示索引的哪一列被使用了，如果可能的话，是一个常数
rows 根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的 sql 找了多少数据，在完成目的的情况下越少越好
filtered 过滤比例，使用 distinct 关键字，会进行过滤，filterd =（过滤后的记录数/过滤前的记录数）* 100
Extra 包含额外的信息，指明当前查询使用索引的情况

type 字段

type 显示查询访问的类型，它有许多取值，在开发中应该尽量使查询类型处在ref，否则至少保持在range。

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system 只有一条记录的表（等于系统表）
const 通过主键查询出一条记录

1-- 通过主键查
2SELECT * FROM tbl_name WHERE primary_key=1;
3
4-- 通过联合主键查，须满足左匹配原则
5SELECT * FROM tbl_name
6  WHERE primary_key_part1=1 AND primary_key_part2=2;

eq_ref
- 查询使用的所有列覆盖到 PRIMARY KEY 或 UNIQUE 非空字段
- where 中索引列使用 = 进行比较

1SELECT * FROM ref_table,other_table
2  WHERE ref_table.key_column=other_table.column;
3
4SELECT * FROM ref_table,other_table
5  WHERE ref_table.key_column_part1=other_table.column
6  AND ref_table.key_column_part2=1;

ref 表示使用的是非主键和 unique 索引，一般是普通索引进行查询
fulltext 使用全文索引
ref_or_null 索引覆盖，普通索引做 IS NULL 判断（普通索引可以为 NULL）

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

index_merge 在查询过程中多个索引组合使用，仍然满足索引覆盖

 1SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;
 2
 3SELECT * FROM tbl_name
 4  WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;
 5
 6SELECT * FROM t1, t2
 7  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
 8  AND t2.key1 = t1.some_col;
 9
10SELECT * FROM t1, t2
11  WHERE t1.key1 = 1
12  AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

unique_subquery 该连接类型类似与 index_subquery,使用的是唯一索引
index_subquery 利用索引来关联子查询，不再扫描全表

1-- student表中的s_id为主键，score中的索引为普通索引
2select s_name from student WHERE s_id in (SELECT s_id FROM score);

range 表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了 index 的全索引扫描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN()

1-- 使用以下
2SELECT * FROM tbl_name
3  WHERE key_column BETWEEN 10 and 20;
4
5SELECT * FROM tbl_name
6  WHERE key_column IN (10,20,30);
7
8SELECT * FROM tbl_name
9  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

index 索引覆盖且对整个索引进行扫描
- 不满足左匹配原则
ALL 全表扫描
- 索引不覆盖

1SELECT * FROM table_name

总结

通过了解 explain 可以知道 SQL 的执行情况，如果查询类型（select_type), 索引使用情况（key、type、possible_key），查询的记录数（rows）等
知道了索引索引在什么时候被选择和使用对 SQL 优化和表设计有很多帮助

编写 SQL 总结：

尽量不适用子查询（subquery)，含有子查询的查询多生成一次查询，下面是一个例子，它们相同的功能

查询是尽量保持索引覆盖
不用时 select *, 如果使用，一定会全部扫描

表设计总结：

两个表相关联的字段，一般都要加索引
表中常查询的字段和条件字段一般加索引
经常房在一块查询的字段可以考虑组合索引（combination index）

（完）