MySQL执行计划【explain】，看这一篇就够啦！

阅读量：788 次

发布时间：2023-02-12

本文共 3744 字，大约阅读时间需要 12 分钟。

使用 explain 命令分析 SQL 查询执行计划

1. 什么是 explain 命令

explain 命令用于分析 SQL 查询的执行计划，帮助我们了解数据库如何处理查询请求。通过 explain 我们可以了解以下信息：

数据库如何加载表

查询的类型

可能使用的索引

实际使用的索引

需要读取的行数

2. explain 执行计划的字段解析

explain 提供了详细的执行计划信息，包含以下 12 个字段：

表示查询中执行 select 子句或操作表的顺序。id 越大，执行优先级越高，越先执行。

select_type

表示 select 查询的类型，包括：

SIMPLE：最简单的 select 查询

PRIMARY：外层 select（可能包含子查询）

SUBQUERY：子查询

DERIVED：驱动的子查询

UNION：使用 union 的查询

table

查询的表名。对于从子句中的子查询，可能显示为 <derivedN>，其中 N 是嵌套查询的 id。

partitions

匹配到的分区信息。对于非分区表，值为 NULL。

type

查询使用了何种类型，涉及 SQL 优化的重要指标。常见类型包括：

system：系统表，只有一行记录

const：使用主键或唯一索引

eq_ref：多表关联，使用主键或唯一索引作为关联条件

ref：使用索引但不是主键或唯一索引

ref_or_null：类似 ref，但还允许 NULL 值

index_merge：同时使用两个以上索引

range：范围索引扫描

index：使用索引进行 select 或 order by

all：未使用索引，全表扫描

possible_keys

此次查询中可能使用的索引列表，并非所有索引都会被实际使用。

key

实际使用的索引。

key_len

索引字段的长度。

ref

使用索引作为关联条件的字段。

rows

估算需要读取的行数，rows 越小，性能越好。

filtered

在过滤后剩下的记录数量比例。

extra

额外信息，用于说明查询优化策略。

3. 搭建测试环境

为了更好地理解 explain 的使用，我们需要搭建一个测试环境。以下是创建测试表的 SQL 语句：

CREATE TABLE `blog` (    `blog_id` int NOT NULL AUTO_INCREMENT COMMENT '唯一博文id--主键',    `blog_title` varchar(255) NOT NULL COMMENT '博文标题',    `blog_body` text NOT NULL COMMENT '博文内容',    `blog_time` datetime NOT NULL COMMENT '博文发布时间',    `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,    `blog_state` int NOT NULL COMMENT '博文状态--0 删除 1正常',    `user_id` int NOT NULL COMMENT '用户id',    PRIMARY KEY (`blog_id`),    ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8) CREATE TABLE `user` (    `user_id` int NOT NULL AUTO_INCREMENT COMMENT '用户唯一id--主键',    `user_name` varchar(30) NOT NULL COMMENT '用户名--不能重复',    `user_password` varchar(255) NOT NULL COMMENT '用户密码',    PRIMARY KEY (`user_id`),    KEY `name` (`user_name`),    ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8) CREATE TABLE `discuss` (    `discuss_id` int NOT NULL AUTO_INCREMENT COMMENT '评论唯一id',    `discuss_body` varchar(255) NOT NULL COMMENT '评论内容',    `discuss_time` datetime NOT NULL COMMENT '评论时间',    `user_id` int NOT NULL COMMENT '用户id',    `blog_id` int NOT NULL COMMENT '博文id',    PRIMARY KEY (`discuss_id`),    ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8)

4. id 的含义

id 表示查询的执行顺序，值越大越先执行。例如：

explain select discuss_body from discuss where blog_id = (    select blog_id from blog where user_id = (        select user_id from user where user_name = 'admin'    ));

三个嵌套查询中，最里层的子查询 id 最大，最先执行。

5. select_type 的含义

select_type 表示 select 查询的类型。常见类型包括：

SIMPLE：最简单的 select 查询，不包含子查询或集合操作。

PRIMARY：外层 select，可能包含子查询。

SUBQUERY：子查询中的第一个 select。

DERIVED：被驱动的子查询（子查询位于 from 子句）。

UNION：在 select 之后使用了 union。

6. table 的含义

table 列显示查询的表名。对于从子句中的子查询，可能显示为 <derivedN>，表示依赖 id 为 N 的查询。

7. partitions 的含义

partitions 列显示匹配到的分区信息。对于非分区表，值为 NULL。

8. type 的含义

type 列显示查询使用的类型，重要性在 SQL 优化中尤为突出。常见类型包括：

system：系统表，只有一行记录。

const：使用主键或唯一索引。

eq_ref：多表关联，使用主键或唯一索引作为关联条件。

ref：使用索引但不是主键或唯一索引。

ref_or_null：类似 ref，但还允许 NULL 值。

index_merge：同时使用两个以上索引。

range：范围索引扫描。

index：使用索引进行 select 或 order by。

all：未使用索引，全表扫描。

9. possible_keys 和 key 的含义

possible_keys：此次查询中可能使用的索引列表。

key：实际使用的索引。

10. rows 的含义

rows 列估算需要读取的行数。rows 越小，性能越好。

11. filtered 的含义

filtered 列显示过滤后剩下的记录数量比例。

12. extra 的含义

extra 列提供额外的信息，解释查询优化策略。

13. 使用 explain 命令的示例

以下是使用 explain 命令分析 SQL 查询的示例：

explain select discuss_body from discuss where blog_id = (    select blog_id from blog where user_id = (        select user_id from user where user_name = 'admin'    ));

14. 使用 explain extended

为了获取更多优化信息，可以使用 explain extended：

explain extended select discuss_body from discuss where blog_id = (    select blog_id from blog where user_id = (        select user_id from user where user_name = 'admin'    ));