MySQL执行计划详解：从看不懂到秒懂，一线DBA的实战笔记

俊才

发布于 2025-12-30 20:34:56

1010

文章被收录于专栏：数据库干货铺数据库干货铺

曾经线上某个产品在发布日上线查询接口突然慢了10倍。我打开监控一看，一个分页SQL响应时间从200ms飙到了3.5秒。同事说：“加个索引不就好了？” 我说：“先别急，我们看看执行计划。”

这已经不是第一次靠EXPLAIN救命了。今天，我想把这几年踩过的坑、总结的经验，毫无保留地分享给你——不是理论堆砌，而是真正能用在生产环境的实战指南。全文内容较长，建议收藏，以后慢慢看。

1. 为什么你必须会看执行计划？

很多开发者觉得：“SQL能跑就行，慢了就加索引。” 但现实是：

加了索引，查询反而更慢
索引用了，但只用了部分字段
明明有索引，MySQL却视而不见

其根本原因：你没看执行计划。

查看执行计划不是DBA的专利，它是每个写SQL的人的“X光机”——让你看清MySQL到底怎么执行你的查询。也能够透视数据库内部的工作机制，发现潜在的性能瓶颈。在高性能要求的系统中，理解执行计划是确保数据库高效运行的基本技能。

2. 如何获取执行计划

2.1 获取预估的执行计划

在MYSQL中可以使用EXPLAIN或DESC获取预估的执行计划，即只需在SQL语句前加上EXPLAIN（或DESC）关键字即可：

mysql> EXPLAIN SELECT * FROM customers WHERE name = '张三' AND age = 30;
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | ref  | idx_name_age_level | idx_name_age_level | 78      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> DESC SELECT * FROM customers WHERE name = '张三' AND age = 30;
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | ref  | idx_name_age_level | idx_name_age_level | 78      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

2.2 获取实际的执行计划

MySQL 8.0及以上版本还支持更详细的EXPLAIN ANALYZE，它会实际执行查询并返回详细的执行统计信息：

mysql> EXPLAIN ANALYZE  SELECT * FROM customers WHERE name = '张三' AND age = 30;
+------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                        |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on customers using idx_name_age_level (name='张三', age=30)  (cost=0.35 rows=1) (actual time=0.0158..0.0158 rows=0 loops=1)
   |
+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

注意： EXPLAIN ANALYZE在查看DML(INSERT,DELETE,UPDATE)时慎用，建议开启事务，查看后回滚，以免真实修改了数据。需要查看执行计划时可以考虑转为select方式进行查看执行计划。

2.3 获取不同格式的执行计划

可以指定不同的输出格式获取更详细的执行计划信息，例如传统表（默认），JSON格式，树状格式（MYSQL8.0+支持），例如：

传统表格格式（默认）

mysql> -- 传统表格格式（默认）
mysql> EXPLAIN SELECT * FROM products WHERE price > 1000;
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | products | NULL       | range | idx_price     | idx_price | 5       | NULL |  539 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

JSON格式（可以提供更详细信息）

mysql> EXPLAIN FORMAT=JSON SELECT * FROM products WHERE category_id = 3;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "82.60"
    },
    "table": {
      "table_name": "products",
      "access_type": "ref",
      "possible_keys": [
        "idx_category_price"
      ],
      "key": "idx_category_price",
      "used_key_parts": [
        "category_id"
      ],
      "key_length": "4",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 236,
      "rows_produced_per_join": 236,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "59.00",
        "eval_cost": "23.60",
        "prefix_cost": "82.60",
        "data_read_per_join": "40K"
      },
      "used_columns": [
        "id",
        "product_name",
        "category_id",
        "price",
        "stock",
        "create_time"
      ]
    }
  }
} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

树状格式（MySQL 8.0+支持）


mysql> EXPLAIN FORMAT=TREE SELECT * FROM orders WHERE order_date > '2025-01-01';
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (orders.order_date > TIMESTAMP'2025-01-01 00:00:00')  (cost=30221 rows=149460)
    -> Table scan on orders  (cost=30221 rows=298920)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3. 执行计划核心字段详解

以常用的表格格式的执行计划为例，我们来解释一下MYSQL执行计划中核心字段的意义和作用。

mysql> EXPLAIN SELECT * FROM products WHERE price > 1000;
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | products | NULL       | range | idx_price     | idx_price | 5       | NULL |  539 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

3.1 id - 查询标识符

id 是SELECT查询的序列号，体现查询的执行顺序，即按照如下顺序执行：

id相同：执行顺序从上到下
id不同：id值越大，优先级越高，越先执行
id有相同有不同：id值越大优先级越高，相同id中从上到下执行

3.2 select_type - 查询类型

select_type表示查询的类别，常见的有：

SIMPLE：简单SELECT查询（不包含子查询或UNION）

这是最理想的情况之一，通常性能较好。

mysql> EXPLAIN SELECT * FROM users WHERE user_id = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 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 matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)

PRIMARY：当查询中包含子查询时，最外层的 SELECT 被标记为 PRIMARY

mysql> explain SELECT * FROM (SELECT 1) AS t;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table      | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL           |
|  2 | DERIVED     | NULL       | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
2 rows in set, 1 warning (0.00 sec)

SUBQUERY：出现在 SELECT 或 WHERE 子句中的子查询（非关联子查询），且不在 FROM 中

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

注：此类子查询通常只执行一次，结果可以被缓存。

另外，如果子查询依赖外层变量（如 WHERE u.id = o.user_id），则不是 SUBQUERY，而是 DEPENDENT SUBQUERY。

DEPENDENT SUBQUERY：关联子查询(子查询中引用了外层查询的字段)，因此每行外层数据都要执行一次子查询，非常容易导致性能问题（N+1 查询）,应尽量避免。如果出现，建议用join方式改写。不过MYSQL8.0中很多情况下已经优化为MATERIALIZED。

mysql> EXPLAIN
    -> SELECT 
    ->     t1.name,
    ->     (SELECT t2.val 
    ->      FROM t2 
    ->      WHERE t2.t1_id = t1.id 
    ->      ORDER BY t2.val DESC 
    ->      LIMIT 1) AS max_val
    -> FROM t1;
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type        | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | PRIMARY            | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                        |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where; Using filesort |
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
2 rows in set, 2 warnings (0.00 sec)

还有一种更极端的UNCACHEABLE SUBQUERY，不过几乎很难出现，也不好复现，本文就不举例了。

DERIVED：FROM子句中的子查询（派生表）

例子同primary

mysql> explain SELECT * FROM (SELECT 1) AS t;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table      | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL           |
|  2 | DERIVED     | NULL       | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
2 rows in set, 1 warning (0.00 sec)

在MySQL8.0之前DERIVED表无法使用索引，性能较差；MySQL 8.0 引入了CTE（公共表表达式）和物化优化，有所改善。


mysql> EXPLAIN 
    -> SELECT name FROM customers WHERE age > 30
    -> UNION
    -> SELECT name FROM customers WHERE name like '赵%';
+----+--------------+------------+------------+-------+--------------------+--------------------+---------+------+-------+----------+--------------------------+
| id | select_type  | table      | partitions | type  | possible_keys      | key                | key_len | ref  | rows  | filtered | Extra                    |
+----+--------------+------------+------------+-------+--------------------+--------------------+---------+------+-------+----------+--------------------------+
|  1 | PRIMARY      | customers  | NULL       | index | idx_name_age_level | idx_name_age_level | 140     | NULL | 49989 |    33.33 | Using where; Using index |
|  2 | UNION        | customers  | NULL       | range | idx_name_age_level | idx_name_age_level | 74      | NULL |     1 |   100.00 | Using where; Using index |
|  3 | UNION RESULT | <union1,2> | NULL       | ALL   | NULL               | NULL               | NULL    | NULL |  NULL |     NULL | Using temporary          |
+----+--------------+------------+------------+-------+--------------------+--------------------+---------+------+-------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

UNION&UNION RESULT:

UNION: UNION 或UNION ALL出现在 UNION 或 UNION ALL 的第二条及以后的 SELECT 中

UNION RESULT: UNION 操作的最终结果合并阶段。MySQL 需要将多个 SELECT 的结果合并，并去重（UNION）或直接合并（UNION ALL）NION）或直接合并（UNION ALL）

MATERIALIZED：子查询被物化（Materialized）, 即先执行子查询，将结果存入临时表，后续当作普通表使用。触发条件通常是 IN 子查询或exists被优化器转换为半连接（semi-join）并物化

mysql> EXPLAIN 
    -> SELECT name FROM customers u 
    -> WHERE EXISTS (
    ->     SELECT 1 FROM orders o WHERE o.customer_id = u.id AND o.amount > 1000
    -> );
+----+--------------+-------------+------------+--------+---------------------------------------+---------------------+---------+-------------+--------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys                         | key                 | key_len | ref         | rows   | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------------------------------+---------------------+---------+-------------+--------+----------+-------------+
|  1 | SIMPLE       | u           | NULL       | index  | PRIMARY                               | idx_name_age_level  | 140     | NULL        |  49989 |   100.00 | Using index |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_distinct_key>                   | <auto_distinct_key> | 4       | testdb.u.id |      1 |   100.00 | NULL        |
|  2 | MATERIALIZED | o           | NULL       | ALL    | idx_customer_date,idx_customer_status | NULL                | NULL    | NULL        | 298920 |    33.33 | Using where |
+----+--------------+-------------+------------+--------+---------------------------------------+---------------------+---------+-------------+--------+----------+-------------+
3 rows in set, 2 warnings (0.00 sec)

select_type查询复杂度大致如下：

select_type	复杂度	优化建议
SIMPLE	低	无需特别处理
PRIMARY	低	看内部子查询类型
SUBQUERY	中	确保子查询高效
DEPENDENT SUBQUERY	高	改写为 JOIN
DERIVED	中	MySQL 8.0+ 影响较小
MATERIALIZED	低	优化器自动优化
UNION/UNION RESULT	中	考虑是否可用单条 SQL 替代

3.3 partitions表分区

partitions字段显示的是查询实际访问的表分区。如果表没有分区，这个字段就是NULL。

通过它，你可以快速判断查询是否有效命中了目标分区，尽量直接读取指定分区的数据，避免全分区扫描

mysql> EXPLAIN SELECT * FROM goods_partitions;
+----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table            | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | goods_partitions | p0,p1,p2   | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

3.4 type - 连接类型（性能关键指标）

type列表示MySQL在表中找到所需行的方式，是判断查询效率的最重要指标，直接决定了查询性能的高低。

下面按照性能从好到坏排序进行介绍：

system：表只有一行记录（myisam表），这是const类型的特例

mysql> EXPLAIN SELECT * FROM myisam_single_row;
+----+-------------+-------------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table             | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | myisam_single_row | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------------------+------------+--------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

const：通过主键或唯一索引一次就能找到一行数据，查询速度非常快

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

eq_ref：多表关联查询时，使用主键或唯一索引进行关联

mysql> EXPLAIN SELECT * FROM orders o, customers c WHERE o.customer_id = c.id;
+----+-------------+-------+------------+--------+---------------------------------------+---------+---------+----------------------+--------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys                         | key     | key_len | ref                  | rows   | filtered | Extra |
+----+-------------+-------+------------+--------+---------------------------------------+---------+---------+----------------------+--------+----------+-------+
|  1 | SIMPLE      | o     | NULL       | ALL    | idx_customer_date,idx_customer_status | NULL    | NULL    | NULL                 | 298920 |   100.00 | NULL  |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY                               | PRIMARY | 4       | testdb.o.customer_id |      1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------------------------------+---------+---------+----------------------+--------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

ref：使用非唯一索引扫描，返回匹配某个单独值的所有行

-- name字段有普通索引
mysql> EXPLAIN SELECT * FROM customers WHERE name = '张三';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | ref  | idx_name_age_level | idx_name_age_level | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

range：索引范围扫描，常见于BETWEEN、>、<、IN、like 等操作

-- name字段有普通索引
mysql> explain  SELECT * FROM customers WHERE name like '赵%';
+----+-------------+-----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | customers | NULL       | range | idx_name_age_level | idx_name_age_level | 74      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

index：全索引扫描（索引覆盖）,只遍历索引树,无需回表

mysql> EXPLAIN SELECT user_id FROM users ORDER BY user_id;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

ALL：全表扫描，性能最差，应尽量避免,主要是因缺少索引等引起

mysql> EXPLAIN  SELECT * FROM customers WHERE age > 20;
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 49989 |    33.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

从高到低，各个type的性能排序如下

type	说明	性能	案例
system	表只有一行	★★★★★	SELECT * FROM (SELECT 1) AS t;
const	通过主键/唯一索引精确查找	★★★★☆	SELECT * FROM user WHERE id = 1;
eq_ref	多表关联时使用主键/唯一索引	★★★★☆	SELECT * FROM user JOIN order ON user.id = order.user_id;
ref	普通索引等值匹配	★★★☆☆	SELECT * FROM user WHERE name = '小明';
range	范围扫描（>、<、BETWEEN）	★★☆☆☆	SELECT * FROM user WHERE age > 20;
index	全索引扫描（覆盖索引）	★★☆☆☆	SELECT id FROM user WHERE status = 'active';
ALL	全表扫描（最差）	★☆☆☆☆	SELECT * FROM user WHERE name LIKE '%小明%';

因此，在做SQL优化的时候，至少达到range级别，最好能达到ref及以上。

3.4 possible_keys和key

possible_keys：查询可能使用的索引
key：查询实际使用的索引

如果possible_keys有值而key为NULL，说明可能是因为SQL写法无法走索引（例如函数计算、隐式转换等）问题，或者全表扫描的代价比索引低。

mysql> explain select * from orders where customer_id<>1000 ;
+----+-------------+--------+------------+------+---------------------------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys                         | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+--------+------------+------+---------------------------------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | idx_customer_date,idx_customer_status | NULL | NULL    | NULL | 298920 |    87.99 | Using where |
+----+-------------+--------+------------+------+---------------------------------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

3.5 key_len

key_len表示查询实际使用的索引的长度（单位是字节）。它能帮忙精确测量出一次查询到底用了多少索引资源，尤其是在组合索引中，可以看出一次查询进行索引检索时用到了哪些字段。

例如：

#SQL1:
mysql> explain select * from customers where name='张三';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | ref  | idx_name_age_level | idx_name_age_level | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

#SQL2:
mysql> explain select * from customers where name='张三' and age>20;
+----+-------------+-----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | customers | NULL       | range | idx_name_age_level | idx_name_age_level | 78      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

#SQL3:
mysql> explain select * from customers where name='张三' and age>20 and vip_level='普通';
+----+-------------+-----------+------------+-------+----------------------------------+--------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys                    | key                | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+----------------------------------+--------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | customers | NULL       | range | idx_name_age_level,idx_vip_level | idx_name_age_level | 78      | NULL |    1 |    50.00 | Using index condition |
+----+-------------+-----------+------------+-------+----------------------------------+--------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

#SQL4:

mysql> explain select * from customers where name='张三' and age=20 and vip_level='普通';
+----+-------------+-----------+------------+------+----------------------------------+--------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys                    | key                | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+----------------------------------+--------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | ref  | idx_name_age_level,idx_vip_level | idx_name_age_level | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+----------------------------------+--------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

例子中的4个SQL，可以看出4个SQL都走了idx_name_age_level，但是key_len不同，这说明不同的查询走上索引查询字段是不一样的：

SQL1: 走了idx_name_age_level索引中的name进行检索
SQL2: 走了idx_name_age_level索引中的name后再通过查询age进行检索
SQL3: 走了idx_name_age_level索引中的name后再通过查询age进行检索，因为age是范围查询，因此后面即使vip_level在索引内也没有继续检索了，因此key_len与SQL2相同
SQL4: 走了idx_name_age_level索引中的name后再通过查询age 进行检索，因为此时age是等值查询，因此后面可以继续进行vip_level检索，因此key_len继续变大
不同数据类型和约束下的key_len基本计算方式如下：

数据类型	允许NULL	字符集	计算示例	大致key_len值
INT NOT NULL	否	-	4字节	4
INT DEFAULT NULL	是	-	4字节 + 1字节（NULL标记）	5
CHAR(10) NOT NULL	否	utf8mb4	10字符 × 4字节/字符	40
CHAR(10) DEFAULT NULL	是	utf8mb4	10字符 × 4字节/字符 + 1字节	41
VARCHAR(100) NOT NULL	否	utf8mb4	100字符 × 4字节/字符 + 2字节（变长开销）	402
VARCHAR(100) DEFAULT NULL	是	utf8mb4	100字符 × 4字节/字符 + 2字节 + 1字节	403

3.6 rows - 预估扫描行数

MySQL估计需要扫描的行数。这个值不一定准确（与统计信息也有关系），但可以作为性能判断的重要参考——值越小越好。

另外rows小不代表就很快，例如关联的时候，关联字段是主键时，rows显示很可能就是1，但是不代表整个查询就很快。

3.7 filtered

filtered字段预估了存储引擎返回的数据在经过服务器层所有剩余条件过滤后，最终满足查询条件的行数所占的百分比。这个值介于0到100之间，值越大（越接近100），意味着存储引擎有效地返回了数据，并且索引得到了很好的利用，服务器层不需要再做大量的额外筛选，性能也往往越好。

3.8 Extra - 额外信息（重要优化线索）

Extra字段包含查询的详细信息，上述案例基本也都包含，常见值及优化建议如下：

Extra	说明	问题严重性	优化建议
Using where	需要存储引擎返回数据后，在server层过滤	中	优化查询条件，减少过滤
Using filesort	无法用索引排序，需要额外排序	高	添加覆盖索引或调整排序字段
Using index	使用了覆盖索引（查询字段都在索引中）	低	优化查询，减少字段
Using temporary	需要临时表（如GROUP BY、DISTINCT）	高	优化查询逻辑，避免临时表
Impossible WHERE	WHERE条件永远为假	低	检查WHERE条件

4 . 典型的案例演示

4.1 准备几张演示表

-- 客户表(customers)
CREATE TABLE `customers` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(24) NOT NULL DEFAULT '' COMMENT '客户姓名',
    `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
    `vip_level` varchar(20) NOT NULL DEFAULT '' COMMENT 'VIP等级',
    `registration_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
    `last_login` timestamp NULL DEFAULT NULL COMMENT '最后登录时间',
    PRIMARY KEY (`id`),
    KEY `idx_name_age_level` (`name`,`age`,`vip_level`),
    KEY `idx_registration_time` (`registration_time`),
    KEY `idx_vip_level` (`vip_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客户信息表';
-- 商品表(products)
CREATE TABLE `products` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `product_name` varchar(50) NOT NULL COMMENT '商品名称',
    `category_id` int(11) NOT NULL COMMENT '分类ID',
    `price` decimal(10,2) NOT NULL COMMENT '价格',
    `stock` int(11) NOT NULL COMMENT '库存',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `idx_category_price` (`category_id`,`price`),
    KEY `idx_price` (`price`),
    KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品信息表';
-- 订单表(orders)
CREATE TABLE `orders` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `order_number` varchar(20) NOT NULL COMMENT '订单号',
    `customer_id` int(11) NOT NULL COMMENT '客户ID',
    `product_id` int(11) NOT NULL COMMENT '商品ID',
    `quantity` int(11) NOT NULL COMMENT '数量',
    `amount` decimal(10,2) NOT NULL COMMENT '金额',
    `order_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '订单日期',
    `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '订单状态',
    PRIMARY KEY (`id`),
    KEY `idx_customer_date` (`customer_id`,`order_date`),
    KEY `idx_order_date` (`order_date`),
    KEY `idx_customer_status` (`customer_id`,`status`),
    KEY `idx_product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';

测试数据大家可以自己造一些

4.2 案例1：最左前缀原则验证

MySQL索引遵循最左前缀原则，即查询必须从索引的最左列开始。

有效使用索引的查询（上面案例也演示过）：

mysql> -- 使用name索引（最左列）
mysql> EXPLAIN SELECT * FROM customers WHERE name = '张三';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | ref  | idx_name_age_level | idx_name_age_level | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> -- 使用name和age索引
mysql> EXPLAIN SELECT * FROM customers WHERE name = '张三' AND age = 30;
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | ref  | idx_name_age_level | idx_name_age_level | 78      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

索引失效的查询：

mysql> -- 缺少最左name列，索引失效（可能全表扫描）
mysql> EXPLAIN SELECT * FROM customers WHERE age = 30;
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 49989 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> -- 跳过了age列，只使用了部分索引
mysql> EXPLAIN SELECT * FROM customers WHERE name = '张三' AND vip_level = '黄金';
+----+-------------+-----------+------------+------+----------------------------------+--------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys                    | key                | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+----------------------------------+--------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | customers | NULL       | ref  | idx_name_age_level,idx_vip_level | idx_name_age_level | 74      | const |    1 |     5.00 | Using index condition |
+----+-------------+-----------+------------+------+----------------------------------+--------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

4.3 案例2：排序优化实战

查询VIP等级为'黄金'的客户并按注册时间降序排列：

mysql> EXPLAIN SELECT * FROM customers WHERE vip_level = '黄金' ORDER BY registration_time DESC;
+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+----------------+
|  1 | SIMPLE      | customers | NULL       | ref  | idx_vip_level | idx_vip_level | 62      | const | 1502 |   100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

Extra中出现"Using filesort"，表示需要额外的排序操作。

优化方案：

为vip_level和registration_time创建联合索引，且registration_time降序

ALTER TABLE customers ADD INDEX idx_vip_regtime (vip_level, registration_time DESC);

查询时确保使用覆盖索引

mysql> EXPLAIN SELECT id, name, vip_level, registration_time
    -> FROM customers
    -> WHERE vip_level = '黄金'
    -> ORDER BY registration_time DESC;
+----+-------------+-----------+------------+------+-------------------------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys                 | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | ref  | idx_vip_level,idx_vip_regtime | idx_vip_regtime | 62      | const | 1502 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-------------------------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

优化后，Extra应该显示"NULL"，表示已经没有在使用文件排序了。

4.4 案例3：覆盖索引的优势

覆盖索引是指查询的字段都包含在索引中，无需回表查询数据行。

需要回表的查询

mysql> EXPLAIN SELECT * FROM customers WHERE name = '张三';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | ref  | idx_name_age_level | idx_name_age_level | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

覆盖索引查询（只需查索引，无需回表）


mysql> EXPLAIN SELECT name, age, vip_level FROM customers WHERE name = '张三';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ref  | idx_name_age_level | idx_name_age_level | 74      | const |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

第二个查询的Extra会显示"Using index"，效率更高，因为不需要回表访问数据行。

4.4 索引失效的常见场景及解决方案

在索引列上使用函数或计算

-- 索引失效

mysql> EXPLAIN SELECT * FROM customers WHERE YEAR(registration_time) = 2024;
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 49989 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- 优化后（使用范围查询）

mysql> EXPLAIN SELECT * FROM customers WHERE registration_time BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59';
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | customers | NULL       | range | idx_registration_time | idx_registration_time | 4       | NULL | 1096 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

隐式类型转换

mysql> desc customers2 id ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(20) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

-- id是字符串类型，如果使用数字查询会导致索引失效

mysql> EXPLAIN SELECT * FROM customers2 WHERE id = 1001;
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | customers2 | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL | 53014 |    10.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 7 warnings (0.00 sec)

-- 应使用正确类型

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

LIKE模糊查询

-- 前缀模糊查询导致索引失效

mysql> EXPLAIN SELECT * FROM customers WHERE name LIKE '%三';
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 49989 |    11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- 后缀模糊查询可以使用索引


mysql> EXPLAIN SELECT * FROM customers WHERE name LIKE '张%';
+----+-------------+-----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | customers | NULL       | range | idx_name_age_level | idx_name_age_level | 74      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

-- 覆盖索引优化前缀模糊查询

mysql> EXPLAIN SELECT name, age FROM customers WHERE name LIKE '%三';
+----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+-------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key                | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | customers | NULL       | index | NULL          | idx_name_age_level | 140     | NULL | 49989 |    11.11 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)