AI 助理
备案控制台
开发者社区 数据库 文章 正文

一文读懂 MySQL Explain 执行计划

2023-09-07 1224
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： 一文读懂 MySQL Explain 执行计划

一、前言

上周老周的一个好朋友让我出一篇教你读懂 SQL 执行计划，和我另一位读者反馈的面试题如何排查慢 SQL 的强相关，索性先出一篇一文读懂 MySQL Explain 执行计划。Explain 执行计划你一定得会看，不然你简历上就别去写什么你会 SQL 调优，不然面试官会觉得，Explain 执行计划你都不会看，那你还 SQL 调啥优啊？SQL 调你吧？？？开个小玩笑，玩笑归玩笑，重要是真的重要！！！

二、Explain 执行计划是什么？

什么是执行计划？简而言之，就是 SQL 在数据库中执行时的表现情况，通常用于 SQL 性能分析、优化和加锁分析等场景，执行过程会在 MySQL 查询过程中由解析器，预处理器和查询优化器共同生成。在 MySQL 中使用 explain 关键字来查看。

2.1 执行计划有什么用？

它可以用来分析 SQL 语句和表结构的性能瓶颈

  • 关联查询的执行顺序
  • 查询操作的操作类型
  • 哪些索引可以被命中
  • 哪些索引实际被命中
  • 每张表有多少记录参与查询
  • …

2.2 MySQL 执行过程

如上图所示，MySQL 数据库由 Server 层和 Engine 层组成：

  • Server 层有 SQL 分析器、SQL优化器、SQL 执行器，用于负责 SQL 语句的具体执行过程；
  • Engine 层负责存储具体的数据，如最常使用的 MyISAM、InnoDB 存储引擎，还有用于在内存中存储临时结果集的 TempTable 引擎。

SQL 优化器会分析所有可能的执行计划，选择成本最低的执行，这种优化器称之为：CBO（Cost-based Optimizer，基于成本的优化器）。

而在 MySQL 中，一条 SQL 的计算成本计算如下所示：

Cost  = Server Cost + Engine Cost

= CPU Cost + IO Cost

其中，CPU Cost 表示计算的开销，比如索引键值的比较、记录值的比较、结果集的排序等这些操作都在 Server 层完成；

IO Cost 表示引擎层 IO 的开销，MySQL 8.0 可以通过区分一张表的数据是否在内存中，分别计算读取内存 IO 开销以及读取磁盘 IO 的开销。

数据库 mysql 下的表 server_cost、engine_cost 则记录了对于各种成本的计算，如：

表 server_cost 记录了 Server 层优化器各种操作的成本，这里面包括了所有 CPU Cost，其具体含义如下：

  • disk_temptable_create_cost：创建磁盘临时表的成本，默认为 20。
  • disk_temptable_row_cost：磁盘临时表中每条记录的成本，默认为 0.5。
  • key_compare_cost：索引键值比较的成本，默认为 0.05，成本最小。
  • memory_temptable_create_cost：创建内存临时表的成本：默认为 1。
  • memory_temptable_row_cost：内存临时表中每条记录的成本，默认为 0.1。
  • row_evaluate_cost：记录间的比较成本，默认为 0.1。

可以看到， MySQL 优化器认为如果一条 SQL 需要创建基于磁盘的临时表，则这时的成本是最大的，其成本是基于内存临时表的 20 倍。而索引键值的比较、记录之间的比较，其实开销是非常低的，但如果要比较的记录数非常多，则成本会变得非常大。

而表 engine_cost 记录了存储引擎层各种操作的成本，这里包含了所有的 IO Cost，具体含义如下：

  • io_block_read_cost：从磁盘读取一个页的成本，默认值为 1。
  • memory_block_read_cost：从内存读取一个页的成本，默认值为 0.25。

也就是说， MySQL 优化器认为从磁盘读取的开销是内存开销的 4 倍。

三、Explain 执行计划详解

我们先来准备以下 SQL 脚本：

CREATE TABLE `user` (
    `id` INT (11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR (20) DEFAULT NULL COMMENT "用户名",
    PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = "用户表";
CREATE TABLE `user_robot_relate` (
    `id` INT (11) NOT NULL AUTO_INCREMENT,
    `user_id` INT (11) NOT NULL COMMENT "用户id",
    `robot_id` INT (11) NOT NULL COMMENT "机器人id",
    PRIMARY KEY (`id`), 
    KEY `idx_user_id` (`user_id`), 
    KEY `idx_robot_id` (`robot_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = "用户与机器人表";
CREATE TABLE `robot` (
    `id` INT (11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR ( 20 ) DEFAULT NULL COMMENT "机器人名",
    PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = "机器人表";
INSERT INTO user VALUES (1, 'riemann');
INSERT INTO user VALUES (2, 'andy');
INSERT INTO user_robot_relate VALUES (1, 1, 1);
INSERT INTO user_robot_relate VALUES (2, 1, 2);
INSERT INTO user_robot_relate VALUES (3, 2, 3);
INSERT INTO robot VALUES (1, '小白鲸');
INSERT INTO robot VALUES (2, '扫地机');
INSERT INTO robot VALUES (3, '扫拖一体机');

我们创建三张表 user、user_robot_relate、robot，表之间的关系 user.id = user_robot_relate.user_id AND user_robot_relate.robot_id = robot.id。

先来看下我的 MySQL 版本，是 5.7.37 的。

接着我们看一下执行计划有哪些字段，先看个整体的，让大家有个大概的认识后，我们再逐一去详解分析。

explain 执行后输出的结果集包含 12 列，分别是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered 和 Extra，下面对这些字段进行解释。

  • id：Query Optimizer 所选定的执行计划中查询的序列号
  • select_type：显示本行是简单或复杂 select。如果查询有任何复杂的子查询，则最外层标记为PRIMARY、DERIVED.、UNION、UNION RESUIT 等。
  • table：显示这一步所访问的数据库中的表的名称
  • partitions：查询时匹配到的分区信息，对于非分区表值为 NULL，当查询的是分区表时，partitions 显示分区表命中的分区情况。
  • type：数据访问、读取操作类型（ALL、index、range、ref、eq_ref、const、system）等
  • possible_keys：该查询可以利用的索引，如果没有任何索引可以使用，就会显示成 null，这一
    项内容对于优化时候索引的调整非常重要。
  • key：MySQL Query Optimizer 从 possible_keys 中所选择使用的索引
  • key_len：被选中使用索引的索引键长度
  • ref：列出是通过常量（const），还是某个表的某个字段（如果是 join）来过滤（通过 key）
    的。
  • rows：MySQL Query Optimizer 通过系统收集到的统计信息估算出来的结果集记录条数
  • filtered：表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。
  • Extra：查询中每一步实现的额外细节信息，如 Using filesort、index 等。

3.1 id

看到三条记录的 id 都相同，可以理解成这三个表为一组，具有同样的优先级，执行顺序由上而下，具体顺序由优化器决定。

3.1.1 id 相同

mysql> EXPLAIN SELECT * FROM user u WHERE u.id = (SELECT ur.user_id FROM user_robot_relate ur WHERE ur.robot_id = (SELECT r.id FROM robot r WHERE r.name = '扫地机'));
+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys            | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | ur    | NULL       | ALL  | idx_user_id,idx_robot_id | NULL | NULL    | NULL |    3 |   100.00 | NULL                                               |
|  1 | SIMPLE      | u     | NULL       | ALL  | PRIMARY                  | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | r     | NULL       | ALL  | PRIMARY                  | NULL | NULL    | NULL |    3 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+----------------------------------------------------+

3.1.2 id 不同

如果我们的 SQL 中存在子查询，那么 id 的序号会递增，id 值越大优先级越高，越先被执行。当三个表依次嵌套，发现最里层的子查询 id 最大，最先执行。

mysql> EXPLAIN SELECT * FROM user u WHERE u.id = (SELECT ur.user_id FROM user_robot_relate ur WHERE ur.robot_id = (SELECT r.id FROM robot r WHERE r.name = '扫地机'));
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | u     | NULL       | const | PRIMARY       | PRIMARY      | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | ur    | NULL       | ref   | idx_robot_id  | idx_robot_id | 4       | const |    1 |   100.00 | Using where |
|  3 | SUBQUERY    | r     | NULL       | ALL   | NULL          | NULL         | NULL    | NULL  |    3 |    33.33 | Using where |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+

3.1.3 以上两种同时存在

将上边的 SQL 稍微修改一下，增加一个子查询，发现 id 的以上两种同时存在。相同 id 划分为一组，这样就有三个组，同组的从上往下顺序执行，不同组 id 值越大，优先级越高，越先执行。

mysql> EXPLAIN SELECT * FROM user u WHERE u.id = (SELECT ur.user_id FROM user_robot_relate ur WHERE ur.robot_id = (SELECT r.id FROM robot r WHERE r.name = '扫地机')) AND u.id IN (SELECT u.id FROM user u WHERE u.name = 'riemann');
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | u     | NULL       | const | PRIMARY       | PRIMARY      | 4       | const |    1 |   100.00 | NULL        |
|  1 | PRIMARY     | u     | NULL       | const | PRIMARY       | PRIMARY      | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | ur    | NULL       | ref   | idx_robot_id  | idx_robot_id | 4       | const |    1 |   100.00 | Using where |
|  3 | SUBQUERY    | r     | NULL       | ALL   | NULL          | NULL         | NULL    | NULL  |    3 |    33.33 | Using where |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+

3.2 select_type

select_type：表示 select 查询的类型，主要是用于区分各种复杂的查询，例如：普通查询、联合查询、子查询等。

  • SIMPLE：表示最简单的 select 查询语句，也就是在查询中不包含子查询或者 union 交并差集等操作。
  • PRIMARY：当查询语句中包含任何复杂的子部分，最外层查询则被标记为 PRIMARY。
  • SUBQUERY：当 select 或 where 列表中包含了子查询，该子查询被标记为 SUBQUERY。
  • DERIVED：表示包含在 from 子句中的子查询的 select，在我们的 from 列表中包含的子查询会被标记为 derived。
  • UNION：如果 union 后边又出现的 select 语句，则会被标记为 union；若 union 包含在 from 子句的子查询中，外层 select 将被标记为 derived。
  • UNION RESULT：代表从 union 的临时表中读取数据，而 table 列的表示用第一个和第四个 select 的结果进行 union 操作。
mysql> EXPLAIN SELECT t.user_id, (SELECT u.id FROM user u) o FROM (SELECT ur.user_id, ur.robot_id FROM user_robot_relate ur WHERE ur.id = 2) t UNION (SELECT r.id, r.name FROM robot r);
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | ur         | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL            |
|  2 | SUBQUERY     | u          | NULL       | index | NULL          | PRIMARY | 4       | NULL  |    2 |   100.00 | Using index     |
|  4 | UNION        | r          | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    3 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,4> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+

3.3 table

查询的表名，并不一定是真实存在的表，有别名显示别名，也可能为临时表，例如上边的 DERIVED、等。

3.4 partitions

查询时匹配到的分区信息，对于非分区表值为 NULL，当查询的是分区表时，partitions 显示分区表命中的分区情况。

3.5 type

type：查询使用了何种类型，它在 SQL优化中是一个非常重要的指标，以下性能从好到坏依次是：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

  • system：当表仅有一行记录时（系统表），数据量很少，往往不需要进行磁盘 IO，速度非常快。
  • const：表示查询时命中 primary key 主键或者 unique 唯一索引，或者被连接的部分是一个常量（const）值。这类扫描效率极高，返回数据量少，速度非常快。
mysql> EXPLAIN SELECT * FROM robot WHERE id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | robot | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  • eq_ref：查询时命中主键 primary key 或者 unique key 索引， type 就是 eq_ref。
mysql> EXPLAIN SELECT u.name FROM user u, user_robot_relate ur WHERE u.id = ur.id; 
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
|  1 | SIMPLE      | u     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL      |    2 |   100.00 | NULL        |
|  1 | SIMPLE      | ur    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.u.id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
  • ref：区别于 eq_ref，ref 表示使用非唯一性索引，会找到很多个符合条件的行。
mysql> EXPLAIN SELECT id FROM user_robot_relate WHERE user_id = 2; 
+----+-------------+-------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table             | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user_robot_relate | NULL       | ref  | idx_user_id   | idx_user_id | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
  • ref_or_null：这种连接类型类似于 ref，区别在于 MySQL 会额外搜索包含 NULL 值的行。
-- 为了模拟这个场景，我又新增了一个 user_test 表。
mysql> CREATE TABLE `user_test` (
    -> `id` INT (11) NOT NULL AUTO_INCREMENT,
    -> `name` VARCHAR (20) DEFAULT NULL COMMENT "用户名",
    -> PRIMARY KEY (`id`), 
    -> KEY `idx_name` (`name`)
    -> ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = "用户测试表";
Query OK, 0 rows affected (0.01 sec)
mysql> EXPLAIN SELECT id FROM user_test WHERE name = 'riemann' OR name IS NULL; 
+----+-------------+-----------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
| id | select_type | table     | partitions | type        | possible_keys | key      | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | user_test | NULL       | ref_or_null | idx_name      | idx_name | 83      | const |    2 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
  • index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。
-- 下边示例中同时使用到主键 id 和字段 user_id 的索引。
mysql> EXPLAIN SELECT * FROM user_robot_relate WHERE id > 1 AND user_id = 2; 
+----+-------------+-------------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
| id | select_type | table             | partitions | type        | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra                                             |
+----+-------------+-------------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
|  1 | SIMPLE      | user_robot_relate | NULL       | index_merge | PRIMARY,idx_user_id | idx_user_id,PRIMARY | 8,4     | NULL |    1 |   100.00 | Using intersect(idx_user_id,PRIMARY); Using where |
+----+-------------+-------------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
  • unique_subquery：替换下面的 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：使用索引选择行，仅检索给定范围内的行。简单点说就是针对一个有索引的字段，给定范围检索数据。在 where 语句中使用 bettween…and、<、>、<=、in 等条件查询 type 都是 range。
-- user_robot_relate 表中 id 为唯一主键，name 普通字段未建索引。
mysql> EXPLAIN SELECT * FROM user_robot_relate WHERE id BETWEEN 2 AND 3;
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table             | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_robot_relate | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  • 从结果中看到只有对设置了索引的字段，做范围检索 type 才是 range。
mysql> EXPLAIN SELECT * FROM user WHERE name BETWEEN 2 AND 3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  • index：Index 与 ALL 其实都是读全表，区别在于 index 是遍历索引树读取，而 ALL 是从硬盘中读取。
-- id 为主键，不带 where 条件全表查询，type 结果为 index。
mysql> EXPLAIN SELECT id FROM robot;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | robot | NULL       | index | NULL          | PRIMARY | 4       | NULL |    3 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  • ALL：将遍历全表以找到匹配的行，性能最差。
mysql> EXPLAIN SELECT * FROM robot;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | robot | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

3.6 possible_keys

possible_keys：表示在 MySQL 中通过哪些索引，能让我们在表中找到想要的记录，一旦查询涉及到的某个字段上存在索引，则索引将被列出，但这个索引并不一定会是最终查询数据时所被用到的索引。具体请参考上边的例子。

3.7 key

key：区别于 possible_keys，key 是查询中实际使用到的索引，若没有使用索引，显示为 NULL。具体请参考上边的例子。

当 type 为 index_merge 时，可能会显示多个索引。

3.8 key_len

key_len：表示查询用到的索引长度（字节数），原则上长度越短越好 。

  • 单列索引，那么需要将整个索引长度算进去；
  • 多列索引，不是所有列都能用到，需要计算查询中实际用到的列。

注：key_len 只计算 where 条件中用到的索引长度，而排序和分组即便是用到了索引，也不会计算到 key_len 中。

3.9 ref

列出是通过常量（const），还是某个表的某个字段（如果是 join）来过滤（通过 key）的。

3.10 rows

rows：以表的统计信息和索引使用情况，估算要找到我们所需的记录，需要读取的行数。

这是评估 SQL 性能的一个比较重要的数据，MySQL 需要扫描的行数，很直观的显示 SQL 性能的好坏，一般情况下 rows 值越小越好。

3.11 filtered

filtered 这个是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。

在 MySQL.5.7 版本以前想要显示 filtered 需要使用 explain extended 命令。MySQL.5.7 后，默认 explain 直接显示 partitions 和 filtered 的信息。

3.12 Extra

Extra ：不适合在其他列中显示的信息，Explain 中的很多额外的信息会在 Extra 字段显示。

3.12.1 Using index

Using index：我们在相应的 select 操作中使用了覆盖索引，通俗一点讲就是查询的列被索引覆盖，使用到覆盖索引查询速度会非常快，SQL 优化中理想的状态。

什么又是覆盖索引?

一条 SQL 只需要通过索引就可以返回，我们所需要查询的数据（一个或几个字段），而不必通过二级索引，查到主键之后再通过主键查询整行数据（SELECT * ）。

id 为 user 表的主键

mysql> EXPLAIN SELECT id FROM user; 
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | PRIMARY | 4       | NULL |    2 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

注意：想要使用到覆盖索引，我们在 select 时只取出需要的字段，不可 SELECT *，而且该字段建了索引。

mysql> EXPLAIN SELECT * FROM user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

3.12.2 Using where

Using where：查询时未找到可用的索引，进而通过 where 条件过滤获取所需数据，但要注意的是并不是所有带 where 语句的查询都会显示 Using where。

下边示例 name 并未用到索引，type 为 ALL，即 MySQL 通过全表扫描后再按 where 条件筛选数据。

mysql> EXPLAIN SELECT name FROM user WHERE name = 'riemann';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

3.12.3 Using temporary

Using temporary：表示查询后结果需要使用临时表来存储，一般在排序或者分组查询时用到。

mysql> EXPLAIN SELECT name FROM user WHERE id IN (1, 2) GROUP BY name;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                        |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+

3.12.4 Using filesort

Using filesort：表示无法利用索引完成的排序操作，也就是 ORDER BY 的字段没有索引，通常这样的 SQL 都是需要优化的。

mysql> EXPLAIN SELECT id FROM user ORDER BY name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

如果 ORDER BY 字段有索引就会用到覆盖索引，相比执行速度快很多。

mysql> EXPLAIN SELECT id FROM user ORDER BY id;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | PRIMARY | 4       | NULL |    2 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

3.12.5 Using join buffer

Using join buffer：在我们联表查询的时候，如果表的连接条件没有用到索引，需要有一个连接缓冲区来存储中间结果。

mysql> EXPLAIN SELECT u.name FROM user u, user_test t WHERE u.name = t.name;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | index | idx_name      | idx_name | 83      | NULL |    1 |   100.00 | Using index                                        |
|  1 | SIMPLE      | u     | NULL       | ALL   | NULL          | NULL     | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+

3.12.6 Impossible where

Impossible where：表示在我们用不太正确的 where 语句，导致没有符合条件的行。

mysql> EXPLAIN SELECT name FROM user WHERE 1=2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+

3.12.7 No tables used

No tables used：我们的查询语句中没有 FROM 子句，或者有 FROM DUAL 子句。

mysql> EXPLAIN SELECT now();
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+



欢迎大家关注我的公众号【老周聊架构】，Java后端主流技术栈的原理、源码分析、架构以及各种互联网高并发、高性能、高可用的解决方案。

文章标签：
云数据库 RDS MySQL 版
SQL
关系型数据库
索引
MySQL
存储
关键词：
云数据库 RDS MySQL 版执行计划
云数据库 RDS MySQL 版explain
云数据库 RDS MySQL 版explain执行计划
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
老周聊架构
目录
相关文章
潇洒洒
|
11天前
|
缓存 关系型数据库 MySQL
MySQL执行计划选择策略：揭秘查询优化的艺术
【10月更文挑战第15天】 在数据库性能优化中，选择最优的执行计划是提升查询效率的关键。MySQL作为一个强大的关系型数据库管理系统，提供了复杂的查询优化器来生成执行计划。本文将深入探讨如何选择合适的执行计划，以及为什么某些计划更优。
潇洒洒
29 2
最好zzz
|
11天前
|
缓存 关系型数据库 MySQL
MySQL执行计划深度解析：如何做出最优选择
【10月更文挑战第23天】 在数据库查询性能优化中，执行计划的选择至关重要。MySQL通过查询优化器来生成执行计划，但有时不同的执行计划会导致性能差异。理解如何选择合适的执行计划，以及为什么某些计划更优，对于数据库管理员和开发者来说是一项必备技能。
最好zzz
26 2
游客r43put2rqnbp4
|
11天前
|
SQL 关系型数据库 MySQL
美团面试：Mysql如何选择最优 执行计划，为什么？
在40岁老架构师尼恩的读者交流群中，近期有小伙伴面试美团时遇到了关于MySQL执行计划的面试题：“MySQL如何选择最优执行计划，为什么？”由于缺乏系统化的准备，小伙伴未能给出满意的答案，面试失败。为此，尼恩为大家系统化地梳理了MySQL执行计划的相关知识，帮助大家提升技术水平，展示“技术肌肉”，让面试官“爱到不能自已”。相关内容已收录进《尼恩Java面试宝典PDF》V175版本，供大家参考学习。
游客r43put2rqnbp4
39 1
猿java
|
1月前
|
SQL 关系型数据库 MySQL
MySQL EXPLAIN该如何分析？
本文将详细介绍MySQL中`EXPLAIN`关键字的工作原理及结果字段解析，帮助优化查询性能。`EXPLAIN`可显示查询SQL的执行计划，其结果包括`id`、`select_type`、`table`等字段。通过具体示例和优化建议，帮助你理解和应用`EXPLAIN`，提升数据库查询效率。
猿java
61 0
2012_126com-16008
|
2月前
|
关系型数据库 MySQL
The EXPLAIN statement provides information about how MySQL executes statements
EXPLAIN Output Format
2012_126com-16008
34 6
古明地盆
|
2月前
|
SQL 存储 关系型数据库
深入 MySQL 的执行计划与性能优化
深入 MySQL 的执行计划与性能优化
古明地盆
37 0
jianz123
|
4月前
|
SQL 关系型数据库 MySQL
mysql性能调优：EXPLAIN命令21
【7月更文挑战第21天】掌握SQL性能调优：深入解析EXPLAIN命令的神奇用法！
jianz123
58 1
六月暴雪飞梨花
|
4月前
|
SQL 缓存 关系型数据库
MySQL｜浅谈explain的使用
【7月更文挑战第11天】
六月暴雪飞梨花
83 1
三分钟热度的鱼
|
4月前
|
SQL 存储 数据库
MySQL设计规约问题之性能分析工具如Sql explain、show profile和mysqlsla在数据库性能优化中有什么作用
MySQL设计规约问题之性能分析工具如Sql explain、show profile和mysqlsla在数据库性能优化中有什么作用
三分钟热度的鱼
53 2
三分钟热度的鱼
|
4月前
|
SQL 索引 关系型数据库
MySQL设计规约问题之为什么推荐使用EXPLAIN来检查SQL查询
MySQL设计规约问题之为什么推荐使用EXPLAIN来检查SQL查询
三分钟热度的鱼
32 0

热门文章

最新文章

  • 1
    测试开启MySQL performance_schema后对性能的影响
  • 2
    MySQL调优之服务器参数优化实践
  • 3
    Mysql5注射技巧总结
  • 4
    mysql左关联不走索引的一个问题的排查过程及思考
  • 5
    应用MySQL MGR
  • 6
    mysql修改用户密码
  • 7
    如何修改WAMP中mysql默认空密码
  • 8
    MySQL-5.5.33主从复制
  • 9
    mysql的总结6--视图的学习
  • 10
    删除MySQL重复数据
  • 1
    MySQL上亿数据查询优化：实践与技巧
    275
  • 2
    如何理解Mysql的索引及他们的原理--------二叉查找树和平衡二叉树和B树和B+树
    46
  • 3
    MySQL：union all与union区别详解
    85
  • 4
    学习mysql中使用inner join，left join 等
    47
  • 5
    【最佳实践】MySQL数据库迁移到PXC集群
    85
  • 6
    MySQL PXC集群配置IPv6
    45
  • 7
    【实操记录】MySQL主从配置
    131
  • 8
    【实操记录】MySQL二进制安装包部署
    176
  • 9
    mysql模糊查询指定根据第几个字符来匹配
    225
  • 10
    mysql使用 CONCAT(字段,字段) 函数拼接
    59

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布
  • 阿里云企业级自治数据库 RDS 详解
  • 阿里云MySQL云数据库产品体系介绍

    • 相关实验场景

    更多
  • 灵活弹性的PolarDB Serverless确保数据业务持续在线
  • 云端MySQL的安装及应用
  • 体验PolarDB MySQL多主集群
  • 基于ECS+RDS的在线留言系统
  • RDS MySQL的SQL问题诊断与调优
  • RDS MySQL Serverless资源与负载自适应

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    DataWorks售前咨询