理解MySQL查询计划

vonlinee

已于 2024-09-25 22:06:12 修改

阅读量911

点赞数 9

分类专栏： MySQL 文章标签： mysql

于 2024-09-23 00:40:58 首次发布

本文链接：https://blog.csdn.net/qq_40926260/article/details/142425217

版权

MySQL 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

环境准备

MySQL 8.0.26（linux版本）

本文用到的数据库表参见：
https://blog.csdn.net/qq_40926260/article/details/142445464

EXPLAIN

EXPLAIN 是 MySQL 中的一个非常有用的命令，用于获取 MySQL 如何执行 SELECT 语句的详细信息，它可以帮助分析查询的执行计划，从而识别出性能瓶颈所在，同时可帮助了解 MySQL 是如何解析 SQL 语句、如何连接表、以及如何选择索引的。

使用方法：在 SQL 查询语句前加上 EXPLAIN 关键字，比如explain select * from students;结果如下

mysql> explain select * from students;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

注意：

EXPLAIN 只能与 SELECT, DELETE, INSERT, REPLACE 和 UPDATE 这些sql语句一起
对于查询sql，EXPLAIN会生成额外的执行计划信息，可以通过SHOW WARNINGS进行查看

输出格式

EXPLAIN结果默认是以表格形式进行展示的，也可以选择以JSON形式展示

FORMAT 选项可用于选择输出格式。TRADITIONAL 以表格格式显示输出。如果没有 FORMAT 选项，则这是默认设置。JSON 格式以 JSON 格式显示信息。

mysql> explain format=JSON select * from students\G;
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.75"
    },
    "table": {
      "table_name": "students",
      "access_type": "ALL",
      "rows_examined_per_scan": 5,
      "rows_produced_per_join": 5,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.50",
        "prefix_cost": "0.75",
        "data_read_per_join": "10K"
      },
      "used_columns": [
        "student_id",
        "student_name",
        "gender",
        "birth_date",
        "class_id",
        "phone_number",
        "email",
        "address",
        "enrollment_year",
        "school_id"
      ]
    }
  }
}
1 row in set, 1 warning (0.00 sec)

表格形式和JSON形式看到的数据有重合的，也有各自独有的

列含义

EXPLAIN 会解释数据库如何执行该查询以及估计的成本。通过利用此内部 MySQL 工具，您可以观察到以下内容：

id：查询的 ID，该列始终包含一个数字，用于标识行所属的 SELECT。
select_type：MySQL 会将 SELECT 查询分为简单和主要（复杂）类型，如下表所述。
SIMPLE 查询不包含子查询或 UNION
PRIMARY（复杂）复杂类型可分为三大类：简单子查询、派生表（FROM 子句中的子查询）和 UNION。
DELETE：如果您正在解释 DELETE，则 select_type 将是 DELETE
table：查的哪个表
partitions：查询访问的分区
type：使用的 JOIN 类型
possible_keys：sql可能用到的索引
key：实际使用到的索引
key_len：MySQL 选择的索引的长度 — 当 MySQL 选择复合索引时，长度字段是确定该复合索引中有多少列正在使用的唯一方法。
ref：与索引相比的列
rows：查询访问的行数 — 在数据库实例内设计索引时，也请留意行列。此列显示 MySQL 访问了多少行以完成请求，这在设计索引时很有用。查询访问的行越少，查询速度就越快。
filtered：按指定条件过滤的行的百分比，此列显示满足表上某些条件（例如 WHERE 子句或连接条件）的行的百分比的悲观估计。如果将行列乘以此百分比，您将看到 MySQL 估计它将与查询计划中的先前表连接的行数。
Extra：与查询相关的任何额外信息

id

id字段值为select查询的序列号，表示查询中执行select子句或操作表的顺序，有以下3种情况：

id相同，执行顺序由上至下，顺序执行
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同和id不同的情况同时存在：先根据大小，大的优先，然后id相同的从上到下顺序执行

第一种：id相同，执行顺序由上到下，执行顺序也可以说是加载表的顺序

比如查询课程id为2的授课教师信息，结果如下：

mysql> EXPLAIN SELECT * from teachers t, courses c
    -> WHERE t.teacher_id = c.teacher_id
    -> AND c.course_id = 2;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | c     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | t     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

这里使用的是没有join关键字的连接查询，即没有指定是左连接还是右连接，查了2张表，teachers和courses表，而且注意我这里是teachers写在前，courses表写在后的

这种查询和sql语句中谁写在前，谁在后无关。比如我把条件变成t.teacher_id = 2，就变成先查询teachers表了

mysql> EXPLAIN SELECT * from teachers t, courses c
    ->   WHERE t.teacher_id = c.teacher_id
    ->  AND t.teacher_id = 2;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | c     | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    5 |    20.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

第二种：id值不同，id越大越优先查询

比如我们将上面的查询改为子查询的写法

mysql> EXPLAIN SELECT * from teachers
    -> WHERE teacher_id = (
    -> SELECT teacher_id FROM courses WHERE course_id = 2);
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | teachers | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
|  2 | SUBQUERY    | courses  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

当遇到嵌套子查询时，会先查内层，再查外层子，所以id=2的查询会先进行

第三种：id值有相同也有不同，结果是前两种方式的综合，id大的优先，剩下的如果id相同则从上往下依次执行

查询某个班级的班主任所教课程信息，采用子查询的方式

mysql> EXPLAIN SELECT c.* from courses c, teachers t
    -> WHERE c.teacher_id = t.teacher_id
    -> AND t.teacher_id = (SELECT head_teacher_id FROM classes c WHERE class_id = 1);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | t     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  1 | PRIMARY     | c     | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    5 |    20.00 | Using where |
|  2 | SUBQUERY    | c     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0.01 sec)

第四种：id的值存在为NULL的情况，当使用了联合查询时，此时table列的值表示<unionM,N>引用了id为M,N的查询中的记录行

mysql> EXPLAIN SELECT * FROM courses c WHERE c.course_id < 4
    -> UNION SELECT * FROM courses c WHERE c.course_id > 1;
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | c          | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where     |
|  2 | UNION        | c          | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   45 |   100.00 | Using where     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

如果使用UNION ALL，不存在临时表，使用UNION时会进行去重

mysql> EXPLAIN SELECT * FROM courses c WHERE c.course_id < 4
    -> UNION ALL SELECT * FROM courses c WHERE c.course_id > 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | c     | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
|  2 | UNION       | c     | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   45 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

select_type

参考：https://dev.mysql.com/doc/refman/8.4/en/explain-output.html#explain_select_type
select_type 表示每个查询的查询类型

simple：简单查询，查询不包含子查询或者union
primary：复杂查询中最外层的select，一般来说是外层
subquery：包含在select中的子查询（不在 from 子句中），非最外层
derived：包含在 form 子句中的子查询，MySQL 会将结果放在一个临时表中，也称为派生表
union：在union 中的第二个或之后的 select。
union result：使用 union 关键词后会生成一个临时表，对于这个临时表的 select 。
如果是JSON格式，这些信息将会存放到query_block属性中

table

表示查询到的行引用的是哪张表的数据，可能是别名，也可能是衍生表，具体有以下几种值：

<union M,N>：行引用了id为M，N的查询的结果
：该查询引用了id为N的查询结果衍生而来的结果，发生在FROM后面的子查询
：行是指id值为N的行的物化子查询的结果

partitions

分区类型，表示该查询的结果将会从那个分区进行匹配，当表未分区时，该值为NULL

type

这个字段表示join type，描述表是如何被联结的，相关的JSON字段是access_type
该字段常见的值依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL，一般要保证效率的话，要优化我们的语句至少使其达到range级别，如果可能的话做好优化到 ref，出现range一般用于范围查找

possible_keys

主要显示查询可能用到哪些索引来查找，只是可能会使用，并不代表一定会使用
常见值说明：

NULL: 没有相关索引，如果是 NULL 的话，可以考虑在 where 子句中创建一个适当的索引来提高查询性能，然后继续用 explain 查看其效果；也有可能出现 possible_keys 为 NULL，但是 key 有值，实际走了索引。
有列值：如果显示表中的某列，则表示可能会走这一列对应列值的索引；如果 possible_keys 有值，但是 key 显示 NULL，这种情况一般存在于表中数据量不大的情况，因为 MySQL 语句优化器认为索引对此查询的帮助不大，从而选择了全表查询。

注意：possible_keys的值与explain输出结果表中表的顺序是完全独立的，这表示possible_keys中的一些键在生成的表顺序中实际上可能不可用

key

key 表示MySQL实际采用哪个索引来优化对该表的查询。如果没有使用索引，则该列为NULL，如果想强制MySQL使用或忽略possible_keys列中的索引，可以在查询中使用force index或ignore index

如果MySQL决定使用一个possible_keys中的索引来查找行，则该索引将作为key字段的值列出
有可能key列的值列出来possible_keys列中不存在的一个索引

key_len

显示了 MySQL 索引所使用的字节数，用于判断复合索引是否被完全使用，通过这个数值可以计算具体使用了索引中的哪些列（主要用于联合索引的优化）。
注意索引最大长度是 768 字节，当字符串过长时，MySQL 会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。
key_len 的计算规则如下：

字符串

常见的是 char(n) 和 varchar(n)，从 MySQL 5.0.3 之后，n 均表示字符数，而不是字节数，如果是 UTF-8，一个数字或字母占1个字节，一个汉字占3个字节。
char(n) 非汉字长度为 n，如果存放汉字长度为 3n 字节
varchar(n) 非汉字长度为 n+2，如果存放汉字长度为 3n+2 字节；因为 varchar 是可变长字符串，需要 2 字节来存储字符串长度

数值类型

tinyint 长度为 1 字节
smallint 长度为 2 字节
int 长度为 4 字节
bigint 长度为 8 字节

时间类型

date 长度为 3 字节
timestamp 长度为 4 字节
datetime 长度为 8 字节

NULL

如果字段允许设置为 NULL，则需要 1 字节来记录是否为 NULL； Not NULL 的列则不需要。

ref

显示了在使用 key 列中实际的索引时，表查找时所用到的列名和常量；常见的为 const 常量或索引关联查询的字段（列）名。

key 列是实际使用的 index 。但 index 可能建立在数据表的若干列上。ref 列列出具体哪些列或常数被使用了。

rows

rows的值可以粗略地告诉您MySQL必须检查多少行才能执行该查询，并不是真正的结果集中的记录行数，仅供参考

course_id是主键，主键进行等值匹配只会匹配一行

mysql> EXPLAIN SELECT * FROM courses c WHERE course_id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | c     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

filtered

表示被过滤了的数据占检查的数据的百分比，这个值是一个理论估计值，最大值为100，意味着没有对行进行过滤。MySQL.5.7 后，默认 explain 直接显示 partitions 和 filtered 的信息。

rows列表示估计要检查多少行，rows × filtered 显示了剩下的数据行数。例如，如果 rows 为 1000，filtered 为 50.00（50%），则剩下的数据行数为 1000 × 50% = 500。

举个例子，此时courses中有5行数据，course_name值都不同

mysql> EXPLAIN SELECT * FROM courses c WHERE course_name = 's';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

使用course_name进行等值匹配，不管能不能匹配成功，最多只能过滤一行，而表中有5行数据，因此filtered = 1 / 5 = 20%

如果像下面这样就变成40了，因为根据2个值来进行匹配，理论上是可以对2行数据进行过滤的

mysql> EXPLAIN SELECT * FROM courses c WHERE course_name IN ('s', 's');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    40.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Extra

这一列展示的是额外的信息，存在很多值，且在不同的场景下以及不同版本的 MySQL 所表示的意思也不同，只能是表示大概的意思并且仅做优化参考。包含不适合在其他列中显示但十分重要的额外信息

EXPLAIN扩展信息

https://dev.mysql.com/doc/refman/8.4/en/explain-extended.html
EXPLAIN 语句会生成额外的扩展信息，这些信息 EXPLAIN 的结果里看不到，但可以通过在 EXPLAIN 后执行 SHOW WARNINGS 语句来查看。扩展信息适用于 SELECT、DELETE、INSERT、REPLACE 和 UPDATE 语句。

举个例子

其实前面很多sql执行时，结果都会显示有警告信息的，不过可能没注意。例如下面这条sql：
EXPLAIN SELECT * from teachers
WHERE teacher_id = (
SELECT teacher_id FROM courses WHERE course_id = 2);
在这里插入图片描述
可以通过SHOW WARNINGS来查看

mysql> SHOW WARNINGS\G;
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select '2' AS `teacher_id`,'Bob Smith' AS 
`teacher_name`,'234-567-8901' AS `phone_number`,'bob.smith@riverside.edu' AS `email`,'Associate Professor' AS `title`,'Physics' AS `field_of_expertise`,'2' AS `school_id` from `student_management`.`teachers` where true
1 row in set (0.00 sec)

ERROR:
No query specified

EXPLAIN ANALYZE

在 MySQL 8.0.18 中，引入了 EXPLAIN ANALYZE，这是在常规 EXPLAIN 查询计划检查工具之上构建的一个新概念。除了常规 EXPLAIN 将打印的查询计划和估计成本之外，EXPLAIN ANALYZE 还会打印执行计划中各个迭代器的实际成本。

从MySQL 8.0.18开始，EXPLAIN ANALYZE是理解查询执行不可或缺的工具，因为它通过测量查询执行计划的每一步来分解响应时间的查询执行阶段。

使用方式和EXPLAIN类似，只不过是在sql前加上EXPLAIN ANALYZE

mysql> EXPLAIN ANALYZE SELECT * FROM courses;
+-----------------------------------------------------------------------------------------+
| EXPLAIN                                                                                 |
+-----------------------------------------------------------------------------------------+
| -> Table scan on courses  (cost=0.75 rows=5) (actual time=0.035..0.049 rows=5 loops=1)
 |
+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

输出结果只有1列，下面结合几个案例来说明

CREATE TABLE payments (
	id BIGINT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(255),
	amount decimal(5, 2)
);

往表里插入1234567条随机数据

DELIMITER $$  
  
CREATE PROCEDURE populate_payments(IN number_of_inserts INT)  
BEGIN  
    DECLARE i INT DEFAULT 0;  
    DECLARE customer_name VARCHAR(10) DEFAULT '';  
    DECLARE amount DECIMAL(5, 2) DEFAULT 0.00;  
  
    WHILE i < number_of_inserts DO  
        -- 生成随机名字（这里简化处理，实际使用时可能需要更复杂的逻辑）  
        SET customer_name = CONCAT(SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', RAND()*26+1, 1),  
                              SUBSTRING('abcdefghijklmnopqrstuvwxyz', RAND()*26+1, 8));  
          
        -- 生成随机金额  
        SET amount = ROUND(RAND() * 999.99, 2);  
          
        -- 插入数据  
        INSERT INTO payments (name, amount) VALUES (customer_name, amount );  
        -- 增加计数器  
        SET i = i + 1;  
    END WHILE;  
END$$  
  
DELIMITER ;

CALL populate_payments(1234567);

全表扫描

mysql> explain select * from payments where name = 'AA';
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | payments | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1222194 |    10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN ANALYZE输出的结果只有EXPLAIN这一列
在这里插入图片描述

mysql> explain analyze select * from payments where name = 'AA'\G;
*************************** 1. row ***************************
EXPLAIN: -> Filter: (payments.`name` = 'AA')  (cost=122900.40 rows=122219) (actual time=643.864..643.864 rows=0 loops=1)
    -> Table scan on payments  (cost=122900.40 rows=1222194) (actual time=0.053..452.192 rows=1234567 loops=1)

1 row in set (0.65 sec)

索引扫描

CREATE INDEX idx_payment_name ON payments (name);
数据量大的情况下，加索引的sql执行时间也比较长

由于是随机生成的数据
SELECT *
FROM
( SELECT NAME, COUNT(*) count FROM payments GROUP BY name ) A
ORDER BY
A.count DESC

以name='vUUqa’为例：

mysql> explain select * from payments where name = 'vUUqa';
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | payments | NULL       | ref  | idx_payment_name | idx_payment_name | 402     | const |   13 |   100.00 | NULL  |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

可以看到，没有进行过滤，rows=13，因为表中name='vUUqa’数据只有13行

mysql> explain analyze select * from payments where name = 'vUUqa';
+------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on payments using idx_payment_name (name='vUUqa')  (cost=4.55 rows=13) (actual time=0.112..0.118 rows=13 loops=1)
 |
+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

覆盖索引扫描

覆盖索引扫描（也可以称为仅索引扫描）比索引扫描性能更高。
当查询可以直接从索引中检索所有所需数据而无需访问实际表数据时，就会发生这种情况。在这种情况下，索引覆盖查询所需的所有列。
它避免了磁盘读取并减少了 I/O，使得在索引中完全存在所需数据的查询中效率很高。

mysql> explain select id, name from payments where name = 'vUUqa';
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | payments | NULL       | ref  | idx_payment_name | idx_payment_name | 402     | const |   13 |   100.00 | Using index |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

和select * from payments where name = 'vUUqa';的唯一区别是Extra中出现了Using index

使用覆盖索引不用再查询主键索引

mysql> explain analyze select id, name from payments where name = 'vUUqa';
+------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on payments using idx_payment_name (name='vUUqa')  (cost=2.87 rows=13) (actual time=0.029..0.035 rows=13 loops=1)
 |
+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

索引范围扫描

当查询需要根据索引列中的一系列值检索一系列行时，就会发生这种情况
通常发生在查询执行时涉及范围运算符（如 >、<、BETWEEN）的条件
根据数据分布和查询成本估算等因素，查询优化器可能会决定不使用索引范围扫描，即使它在列上找到索引。

mysql> select count(*) from payments where id > 986032 and id < 996032;
+----------+
| count(*) |
+----------+
|     9999 |
+----------+
1 row in set (0.00 sec)

mysql> explain select * from payments where id > 986032 and id < 996032;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | payments | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 19104 |   100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

首先使用主键扫描 19026 行，然后筛选出符合条件的 9999 行。

mysql> explain analyze select * from payments where id > 986032 and id < 996032\G;
*************************** 1. row ***************************
EXPLAIN: -> Filter: ((payments.id > 986032) and (payments.id < 996032))  (cost=3825.33 rows=19104) (actual time=0.051..6.613 rows=9999 loops=1)
    -> Index range scan on payments using PRIMARY  (cost=3825.33 rows=19104) (actual time=0.048..4.442 rows=9999 loops=1)

1 row in set (0.01 sec)

ERROR:
No query specified

主键索引扫描

当查询使用主索引获取行时，就会发生这种情况。在 MySQL 中，主键索引通常作为聚簇索引实现。
聚簇索引扫描通常很高效，因为它按照聚簇索引的顺序获取行，从而减少了额外磁盘寻道（I/O）的需要

mysql> explain select * from payments order by id limit 100;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | payments | NULL       | index | NULL          | PRIMARY | 4       | NULL |  100 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

全索引扫描

如果查询使用索引但需要扫描索引中的所有数据以获取所需的结果，就会发生这种情况。
它可能比全表扫描更快，因为它对较小的数据集进行操作，仅仅扫描索引，不全表扫描
但是，如果索引很大，它仍会产生大量 I/O。如果索引不包含所有必需的列，则需要额外的 I/O 来获取表数据。

mysql> explain select id, name from payments where name like '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+---------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | payments | NULL       | index | NULL          | idx_payment_name | 402     | NULL | 1222194 |    11.11 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+---------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

它几乎扫描了idx_payment_name索引中的所有 1222194 行，然后使用 where 条件应用过滤器来获取匹配的 4951 行。

mysql> explain analyze select id, name from payments where name like '%aa%'\G;
*************************** 1. row ***************************
EXPLAIN: -> Filter: (payments.`name` like '%aa%')  (cost=122900.40 rows=135786) (actual time=0.867..682.876 rows=4951 loops=1)
    -> Index scan on payments using idx_payment_name  (cost=122900.40 rows=1222194) (actual time=0.054..390.285 rows=1234567 loops=1)

1 row in set (0.68 sec)

ERROR:
No query specified

全表扫描 VS 索引

全表扫描是性能最差的扫描方法。
当查询需要检查表中的每一行以找到所需的结果时，就会发生这种情况。
全表扫描占用大量资源，尤其是对于大型表，应避免使用。

mysql> explain select * from payments where name like '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | payments | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1222194 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

查询优化器忽略了name字段上的索引并决定扫描整个表

mysql> explain analyze select * from payments where name like '%aa%'\G;
*************************** 1. row ***************************
EXPLAIN: -> Filter: (payments.`name` like '%aa%')  (cost=122900.40 rows=135786) (actual time=0.071..754.604 rows=4951 loops=1)
    -> Table scan on payments  (cost=122900.40 rows=1222194) (actual time=0.058..460.905 rows=1234567 loops=1)

1 row in set (0.75 sec)

优化器决定进行全表扫描而不是使用索引 idx_payment_name ，因为如果使用索引，则会产生额外的开销（I/O）来获取过滤记录的所有表数据

Using Filesort

Using Filesort表示 MySQL 需要执行外部排序操作才能完成查询。它通常出现在现有索引无法满足的带有 ORDER BY 子句的查询中。

mysql> explain select * from payments order by amount limit 100;
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+----------------+
|  1 | SIMPLE      | payments | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1222194 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

首先扫描整表数据，然后使用外部文件排序按amount字段进行排序以获取所需的 100 行。

mysql> explain analyze select * from payments order by amount limit 100\G;
*************************** 1. row ***************************
EXPLAIN: -> Limit: 100 row(s)  (cost=122900.40 rows=100) (actual time=812.102..812.127 rows=100 loops=1)
    -> Sort: payments.amount, limit input to 100 row(s) per chunk  (cost=122900.40 rows=1222194) (actual time=812.099..812.111 rows=100 loops=1)
        -> Table scan on payments  (cost=122900.40 rows=1222194) (actual time=0.054..457.320 rows=1234567 loops=1)

1 row in set (0.81 sec)

总结

注意：这些只是 MySQL 可能使用的不同查询计划元素的几个示例。特定查询生成的实际计划取决于各种因素，例如表大小、数据分布等

参考

https://blog.jcole.us/2013/01/07/the-physical-structure-of-innodb-index-pages/
https://optimizdba.com/guide-to-mastering-the-mysql-query-execution-plan/
https://medium.com/@mohammad.roshandelpoor/understanding-the-different-types-of-indexes-in-mysql-7ef2bcd0cc7f