MySQL EXPLAIN 执行计划分析器

EXPLAIN 在线分析器

将 MySQL EXPLAIN 输出粘贴到下方（支持表格格式和 JSON 格式），点击"分析"即可获得可视化解读、自动问题检测与优化建议。

id — 查询标识符

id 列是查询中 SELECT 的序号。当查询包含子查询、UNION 或派生表时，会出现多行，每行对应一个 SELECT 操作。理解 id 的编号规则是读懂复杂 EXPLAIN 输出的第一步。

编号规则

• 相同 id：表示这些表在同一个 SELECT 中被 JOIN 访问。MySQL 按从上到下的顺序处理同 id 的行，上面的表是驱动表，下面的是被驱动表。
• 不同 id：id 越大优先级越高，越先被执行。子查询的 id 大于外层查询。
• id 为 NULL：表示这是一个 UNION 结果的临时表行（select_type 为 UNION RESULT）。

示例

-- 简单 JOIN：两行的 id 都是 1
EXPLAIN SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;
-- id=1, table=o  (驱动表)
-- id=1, table=c  (被驱动表)

-- 子查询：子查询 id=2 先执行
EXPLAIN SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status='active');
-- id=1, table=orders
-- id=2, table=customers

-- UNION: UNION RESULT 的 id 为 NULL
EXPLAIN SELECT id FROM t1 UNION SELECT id FROM t2;
-- id=1, table=t1
-- id=2, table=t2
-- id=NULL, table=<union1,2>

注意：MySQL 优化器可能会将子查询重写为 JOIN，此时原本不同的 id 可能合并为同一个 id。通过对比 EXPLAIN 与 SHOW WARNINGS 中的重写后 SQL 来确认。

select_type — SELECT 类型

select_type 标识当前行的 SELECT 在整个查询中扮演的角色。它是理解查询结构（嵌套关系、依赖性）的关键线索。

性能警示

最需要警惕的是 DEPENDENT SUBQUERY。它意味着子查询会为外层的每一行执行一次。当外层表有 100 万行时，子查询执行 100 万次。常见优化方式：将关联子查询改写为 JOIN，或使用 EXISTS 改写为半连接（MySQL 优化器会自动尝试）。

-- 危险：DEPENDENT SUBQUERY
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE region = orders.region  -- 引用了外层 orders.region
);

-- 优化：改写为 JOIN
SELECT DISTINCT o.* FROM orders o
JOIN customers c ON o.customer_id = c.id AND c.region = o.region;

table — 表名

标识当前行访问的表。除了实际表名外，还可能显示以下特殊值：

partitions — 分区裁剪

如果表使用了分区，此列显示查询实际访问的分区列表。如果显示 NULL，说明表未分区。分区裁剪是分区表性能优势的核心——查询只扫描必要的分区而非全部。

-- 按年分区的订单表
CREATE TABLE orders (
  id BIGINT, order_date DATE, ...
) PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
-- partitions: p2024   (仅扫描一个分区)

EXPLAIN SELECT * FROM orders WHERE amount > 100;
-- partitions: p2023,p2024,p2025,pmax   (所有分区——WHERE 条件与分区键无关)

诊断要点：如果你看到所有分区都被列出，说明分区裁剪没有生效。检查 WHERE 条件是否命中了分区键，或者分区函数表达式是否与 WHERE 条件兼容。

type — 访问类型（最重要的列）

type 列描述 MySQL 如何查找该表中的行。它直接决定了查询性能。从最优到最差排列如下（越往上越好）：

system

表只有一行（系统表）。这是 const 的特殊情况，极少在生产查询中出现。

const

通过主键或唯一索引的等值查找，最多返回一行。MySQL 在优化阶段就将其当作常量处理。

EXPLAIN SELECT * FROM users WHERE id = 42;
-- type: const, key: PRIMARY

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
-- type: const (前提：email 有唯一索引)

eq_ref

在 JOIN 中对被驱动表使用主键或唯一非空索引进行等值关联，每次关联最多匹配一行。这是 JOIN 的最佳访问类型。

EXPLAIN SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;
-- orders:   type=ALL  (驱动表全扫)
-- customers: type=eq_ref, key=PRIMARY  (完美：每次只读一行)

ref

使用非唯一索引进行等值查找，可能返回多行。常见于普通索引的等值条件、JOIN 中的非唯一关联。

EXPLAIN SELECT * FROM orders WHERE customer_id = 100;
-- type: ref, key: idx_customer_id  (customer_id 是普通索引)

EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- type: ref, key: idx_status

fulltext

使用全文索引（FULLTEXT）。仅在 MATCH ... AGAINST 查询中出现。

EXPLAIN SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('mysql performance');
-- type: fulltext, key: ft_idx

ref_or_null

类似 ref，但额外搜索包含 NULL 值的行。常见于 WHERE col = val OR col IS NULL。

EXPLAIN SELECT * FROM t WHERE key_col = 10 OR key_col IS NULL;
-- type: ref_or_null

index_merge

优化器使用多个索引，然后合并结果（交集或并集）。Extra 列会显示使用了哪种合并策略：Using intersect、Using union、Using sort_union。

EXPLAIN SELECT * FROM t WHERE col_a = 1 OR col_b = 2;
-- type: index_merge, key: idx_a,idx_b, Extra: Using union(idx_a,idx_b); Using where

优化建议：虽然 index_merge 比全表扫描好，但通常不如建一个复合索引。如果频繁出现此类型，考虑创建覆盖多个条件的复合索引。

unique_subquery

在 IN 子查询中使用主键或唯一索引查找，是 eq_ref 的子查询版本。MySQL 5.6+ 通常已被半连接优化替代。

index_subquery

类似 unique_subquery，但使用的是非唯一索引。

range

索引范围扫描。出现在使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE（前缀匹配）、IN() 时。扫描索引的一个或多个区间，而非全部。

EXPLAIN SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-03-31';
-- type: range, key: idx_created_at

EXPLAIN SELECT * FROM users WHERE age IN (25, 30, 35);
-- type: range, key: idx_age

性能评估：range 的性能取决于选择性。扫描 1% 的索引范围很快，扫描 80% 可能不如全索引扫描。

index

全索引扫描。类似 ALL，但扫描的是索引树而不是数据行。出现在两种情况：
1) 索引覆盖了查询所需的所有列（Extra 显示 Using index）——仅需读索引页，无需回表；
2) 按索引顺序读取所有行（避免排序）。

-- 覆盖索引：只读索引
EXPLAIN SELECT customer_id FROM orders;
-- type: index, key: idx_customer_id, Extra: Using index

-- 全索引扫描：按主键顺序读所有行
EXPLAIN SELECT * FROM orders ORDER BY id;
-- type: index, key: PRIMARY

警告：type=index 在大表上仍然很慢。虽然比 ALL 好一点（索引比数据小），但如果表有 1000 万行，扫描整个索引仍需大量 I/O。

ALL

全表扫描。读取表中每一行。除非表很小（几百行）或确实需要读取大部分行（如分析查询），否则 type=ALL 几乎总是需要优化的信号。

EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- type: ALL  (函数包裹导致索引失效)

-- 修复：改写条件为范围查询
EXPLAIN SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- type: range  (索引可用)

type=NULL（最优极端情况）

MySQL 在优化阶段就已确定结果，甚至无需访问表或索引。例如从空表查询、查询 MIN()/MAX() 在有索引的列上、WHERE 1=0 等。

EXPLAIN SELECT MIN(id) FROM users;
-- type: NULL, Extra: Select tables optimized away

possible_keys — 候选索引

possible_keys 列出优化器认为可能用于此查询的所有索引。它基于查询涉及的列和条件，检查哪些索引的定义与查询匹配。

• NULL：没有候选索引。通常意味着查询条件中的列没有索引，或使用了函数/表达式导致索引不可用。
• 多个索引：优化器列出了多个候选，最终选择（或不选择）其中一个记录在 key 列。

注意：possible_keys 不为空但 key 为 NULL 是常见的问题信号——优化器认为全表扫描比使用任何候选索引都便宜。原因可能是：(1) 表很小；(2) 索引选择性太低；(3) 需要回表获取太多列导致随机 I/O 过大。

key — 实际使用的索引

key 列显示优化器最终选择使用的索引。这是 EXPLAIN 中最直接的"答案"之一：你的索引到底用了没有？

• NULL：未使用任何索引（全表扫描）。
• PRIMARY：使用了主键索引。
• 索引名：使用了指定的二级索引。

有时优化器选择的索引并非最优。可以使用 FORCE INDEX(idx_name) 或 USE INDEX(idx_name) 提示优化器。但请先确认：

-- 强制使用特定索引
EXPLAIN SELECT * FROM orders FORCE INDEX(idx_created_at)
WHERE created_at > '2024-01-01' AND status = 'pending';

-- 忽略某个索引
EXPLAIN SELECT * FROM orders IGNORE INDEX(idx_status)
WHERE status = 'pending';

key_len — 索引使用字节数

key_len 显示 MySQL 实际使用的索引长度（字节数）。对于复合索引，通过 key_len 可以判断使用了索引的前几个列。这是诊断复合索引利用率的关键。

计算规则

实战解读

-- 复合索引: INDEX idx_abc (a INT NOT NULL, b VARCHAR(50) NOT NULL, c DATE NOT NULL)
-- 完整使用: key_len = 4 + (50*4+2) + 3 = 209
-- 只用 a:         key_len = 4
-- 用 a + b:         key_len = 4 + 202 = 206
-- 用 a + b + c:     key_len = 209

EXPLAIN SELECT * FROM t WHERE a = 1 AND b = 'hello';
-- key_len: 206  →  使用了 a + b 两列

最左前缀原则：复合索引 (a, b, c) 只有在查询条件包含 a 时才能使用。WHERE b=1 不能使用此索引。WHERE a=1 AND c=1 只能使用 a 列（key_len 仅为 4）。

ref — 与索引比较的值

ref 显示哪些列或常量与 key 列指示的索引进行比较。它帮助你理解关联条件的细节。

-- ref = const
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
-- ref: const

-- ref = db.table.column
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- orders row: ref: NULL (driving table)
-- users row:  ref: mydb.o.user_id

rows — 预估扫描行数

rows 是优化器根据索引统计信息（index statistics）估算的、为了找到目标行需要检查的行数。这是一个估算值，可能与实际值差距很大——特别是在统计信息过时时。

• 对于 JOIN 查询，总代价大致是所有行的乘积。如果三个表分别估算 100、200、50 行，总检查行数约为 100 × 200 × 50 = 1,000,000。
• 统计信息不准确时可以运行 ANALYZE TABLE tablename 来更新。

-- 统计信息过时的症状:
EXPLAIN SELECT * FROM orders WHERE status = 'shipped';
-- rows: 500000  (估算)
-- 实际只有 128 行——运行 ANALYZE TABLE orders 后重新 EXPLAIN

filtered — 行过滤比例

filtered 表示经过表条件（WHERE）过滤后，预估有多少百分比的行会传递给下一步（JOIN 的下一个表）。有效行数 = rows × filtered / 100。

• filtered = 100.00：所有检查的行都满足条件（没有额外过滤），或者 MySQL 没有足够信息来估算。
• filtered = 10.00：只有 10% 的行满足条件。如果 rows=10000，实际只有约 1000 行传递给下一步。

低 filtered 值（如 1%）配合高 rows 值是一个优化机会：说明大量行被读取后又被丢弃。考虑添加索引来减少需要检查的行数。

Extra — 附加信息（30+ 种值详解）

Extra 列包含 MySQL 执行查询的附加信息。它是诊断查询性能问题最丰富的信息来源。以下按性能影响分组说明。

正面信号（绿色）

Using index

查询所需的所有列都可以从索引中获取，无需回表读取数据行。这就是"覆盖索引"（Covering Index），是性能最佳的情况之一。

-- 覆盖索引示例: INDEX idx_cover (status, created_at)
EXPLAIN SELECT status, created_at FROM orders WHERE status = 'pending';
-- Extra: Using index  (无需回表)

Using index condition

索引条件下推（Index Condition Pushdown, ICP）。MySQL 5.6+ 引入。原本需要回表后在 Server 层过滤的条件，现在下推到存储引擎层在索引中过滤，减少回表次数。

-- INDEX idx_name_age (name, age)
EXPLAIN SELECT * FROM users WHERE name LIKE 'John%' AND age > 30;
-- Extra: Using index condition
-- ICP 在索引层过滤 age > 30，避免对不满足 age 条件的行回表

Select tables optimized away

查询在优化阶段就已完成，无需在执行阶段访问任何表。常见于 MIN()/MAX() 查询在有 B-tree 索引的列上。

Using index for group-by

MySQL 使用索引来处理 GROUP BY，无需创建临时表或额外排序。当 GROUP BY 列正好是索引的前缀时发生。

Using index for skip scan

MySQL 8.0.13+ 引入的 Skip Scan 优化。当复合索引 (a, b) 的前缀列 a 不在 WHERE 中但 b 在时，优化器可以跳过索引前缀进行扫描。

中性/信息性

Using where

Server 层对存储引擎返回的行进行额外过滤。几乎所有有 WHERE 条件的查询都会显示它。它本身不是问题，但如果配合 type=ALL 和高 rows，说明大量行被读取后又被丢弃。

Using MRR

多范围读取优化（Multi-Range Read）。MySQL 将随机磁盘访问转换为顺序访问，通过先按主键排序再回表来减少 I/O。

Using join buffer (Block Nested Loop / hash join)

被驱动表没有可用索引，MySQL 使用 Join Buffer 来减少对被驱动表的扫描次数。MySQL 8.0.18+ 在此基础上引入了 Hash Join。

Impossible WHERE

WHERE 条件逻辑上不可能为真（如 WHERE 1 = 0）。MySQL 直接返回空结果。

Impossible WHERE noticed after reading const tables

在读取 const 表后发现 WHERE 不可能为真。

Impossible HAVING

HAVING 条件不可能为真。

No matching min/max row

没有满足 MIN()/MAX() 查询条件的行。

Using union / Using intersect / Using sort_union

index_merge 使用的合并策略。union 用于 OR 条件，intersect 用于 AND 条件，sort_union 用于需要排序的 OR 条件。

Distinct

MySQL 在找到第一个匹配行后就停止搜索。

Start temporary / End temporary

半连接（semi-join）使用的 DuplicateWeedout 策略的标记。

FirstMatch(tbl_name)

半连接的 FirstMatch 策略——找到第一个匹配就返回。

LooseScan(m..n)

半连接的 LooseScan 策略。

Rematerialize

MySQL 8.0+ 中 Lateral Derived Table 的重新物化。

警告信号（黄色）

Using filesort

MySQL 需要额外的排序操作来满足 ORDER BY。这并不一定意味着使用了磁盘排序——名字有误导性——它可能在内存中完成。但它表示索引无法满足排序需求，需要额外的 CPU 和内存。当数据量大时可能溢出到磁盘临时文件。

-- 触发 filesort: INDEX idx_status (status)
EXPLAIN SELECT * FROM orders WHERE status='pending' ORDER BY created_at;
-- Extra: Using where; Using filesort

-- 消除 filesort: INDEX idx_status_created (status, created_at)
EXPLAIN SELECT * FROM orders WHERE status='pending' ORDER BY created_at;
-- Extra: Using where  (无 filesort)

Using temporary

MySQL 需要创建临时表来处理查询，通常是 GROUP BY、DISTINCT、UNION、子查询等操作。小结果集的临时表在内存中（MEMORY 引擎），大结果集会落盘（MyISAM 或 InnoDB 临时表）。

-- 触发临时表:
EXPLAIN SELECT DISTINCT status FROM orders ORDER BY created_at;
-- Extra: Using temporary; Using filesort

-- 优化：让 ORDER BY 和 DISTINCT/GROUP BY 使用同一个索引

Using join buffer (Block Nested Loop)

被驱动表缺少索引（或优化器选择不使用索引），MySQL 使用 Block Nested Loop 算法。这通常是性能问题的信号——应当为 JOIN 条件添加索引。

危险信号（红色）

Using temporary; Using filesort

两者同时出现是最严重的性能警告。表示需要创建临时表并对其排序。在大数据量上可能导致严重的性能问题。

Full scan on NULL key

子查询优化的回退策略——当优化器无法使用索引时，对子查询做全表扫描。

Range checked for each record

MySQL 无法确定使用哪个索引，对每一行都重新评估。性能通常很差。

Backward index scan

MySQL 8.0+ 报告的反向索引扫描。出现在 ORDER BY ... DESC 但索引是 ASC 排序时。MySQL 8.0 支持降序索引（CREATE INDEX ... (col DESC)）来优化此场景。

Zero limit

查询有 LIMIT 0，MySQL 直接返回空结果。

No matching rows after partition pruning

分区裁剪后没有匹配的分区。

Using where with pushed condition

NDB Cluster 特有——条件被下推到数据节点。

Plan isn't ready yet

MySQL 8.0+ 的 EXPLAIN FOR CONNECTION 中出现——目标连接的执行计划尚未就绪。

EXPLAIN ANALYZE（MySQL 8.0.18+）

EXPLAIN ANALYZE 是 MySQL 8.0.18 引入的最强大的执行计划工具。与普通 EXPLAIN 不同，它实际执行查询并记录每个操作的真实执行统计：实际行数、实际耗时、循环次数。

EXPLAIN ANALYZE SELECT o.*, c.name
FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending' AND o.total > 100
ORDER BY o.created_at DESC LIMIT 10;

/*
-> Limit: 10 row(s)  (cost=2.54 rows=10) (actual time=0.892..0.943 rows=10 loops=1)
    -> Nested loop inner join  (cost=2.54 rows=10) (actual time=0.889..0.938 rows=10 loops=1)
        -> Sort: o.created_at DESC, limit input to 10 row(s) per chunk
            -> Filter: ((o.status = 'pending') and (o.total > 100))  (cost=1.25 rows=10) (actual time=0.325..0.678 rows=42 loops=1)
                -> Table scan on o  (cost=1.25 rows=1000) (actual time=0.312..0.590 rows=1000 loops=1)
        -> Single-row index lookup on c using PRIMARY (id=o.customer_id)  (cost=0.13 rows=1) (actual time=0.024..0.024 rows=1 loops=10)
*/

如何读取输出

估算 vs 实际的差异分析

EXPLAIN ANALYZE 最大的价值在于发现估算与实际的差异：

• 估算行数 << 实际行数：统计信息过时。运行 ANALYZE TABLE。优化器可能选择了错误的 JOIN 顺序或索引。
• 估算行数 >> 实际行数：优化器高估了代价，可能导致它放弃使用某个索引。尝试 FORCE INDEX 看是否更快。
• 某层 actual time 特别高：这就是瓶颈所在。集中优化这一层。
• loops 值很高：该操作被反复执行。如果 loops=50000 且每次 actual time=0.01ms，总耗时仍为 500ms。

注意：EXPLAIN ANALYZE 会实际执行查询。对于写操作（INSERT/UPDATE/DELETE），它会真正修改数据。请在事务中使用并回滚，或仅对 SELECT 使用。

EXPLAIN FORMAT=JSON

JSON 格式提供比表格格式丰富得多的信息，包括代价详情、使用的列、附加条件等。在诊断复杂查询时特别有用。

EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE status = 'pending'\G

关键 JSON 字段

JSON 输出示例

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "12.75"
    },
    "table": {
      "table_name": "orders",
      "access_type": "ref",
      "possible_keys": ["idx_status", "idx_status_created"],
      "key": "idx_status_created",
      "used_key_parts": ["status"],
      "key_length": "42",
      "ref": ["const"],
      "rows_examined_per_scan": 50,
      "rows_produced_per_join": 50,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "7.75",
        "eval_cost": "5.00",
        "prefix_cost": "12.75",
        "data_read_per_join": "25K"
      },
      "used_columns": ["id", "customer_id", "status", "total", "created_at"],
      "attached_condition": "(`mydb`.`orders`.`status` = 'pending')"
    }
  }
}

用途：通过 used_columns 可以精确设计覆盖索引——让索引包含所有 used_columns 中的列，从而将 type 提升到 index（覆盖索引）。

EXPLAIN FORMAT=TREE（MySQL 8.0.16+）

TREE 格式展示基于迭代器（Iterator）的执行计划。与传统的行级 EXPLAIN 不同，它显示数据如何在各个操作之间流动，更接近数据库实际的执行方式。

EXPLAIN FORMAT=TREE SELECT o.*, c.name
FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending' ORDER BY o.created_at LIMIT 10\G

/*
-> Limit: 10 row(s)  (cost=45.30 rows=10)
    -> Nested loop inner join  (cost=45.30 rows=50)
        -> Sort: o.created_at, limit input to 10 row(s) per chunk  (cost=27.80 rows=50)
            -> Index lookup on o using idx_status (status='pending')  (cost=27.80 rows=50)
        -> Single-row index lookup on c using PRIMARY (id=o.customer_id)  (cost=0.25 rows=1)
*/

迭代器类型说明

TREE 格式的阅读方式是从最内层（最深缩进）到最外层。最内层是数据源（表扫描或索引查找），数据逐层向上流动，经过过滤、排序、连接、限制等操作最终返回给客户端。

key_len 计算器

输入你的索引列的数据类型和属性，计算预期的 key_len 值。通过与 EXPLAIN 输出的实际 key_len 对比，判断复合索引用了几列。

常见 key_len 速查

实战案例分析

案例 1：全表扫描 → 添加索引

SELECT * FROM orders WHERE customer_email = '[email protected]';

-- EXPLAIN:
+----+--------+--------+------+------+------+------+--------+-------+-------------+
| id | s_type | table  | type | p_keys| key | k_len| ref   | rows  | Extra       |
+----+--------+--------+------+------+------+------+--------+-------+-------------+
|  1 | SIMPLE | orders | ALL  | NULL | NULL | NULL | NULL   | 850000| Using where |
+----+--------+--------+------+------+------+------+--------+-------+-------------+
-- 问题：扫描 85 万行找一个邮箱，耗时 3.2 秒

ALTER TABLE orders ADD INDEX idx_customer_email (customer_email);

-- EXPLAIN 优化后:
+----+--------+--------+------+--------------------+--------------------+------+-------+------+-------+
| id | s_type | table  | type | possible_keys      | key                | k_len| ref   | rows | Extra |
+----+--------+--------+------+--------------------+--------------------+------+-------+------+-------+
|  1 | SIMPLE | orders | ref  | idx_customer_email | idx_customer_email | 1022 | const |    3 |       |
+----+--------+--------+------+--------------------+--------------------+------+-------+------+-------+
-- 优化后：扫描 3 行，耗时 0.001 秒

案例 2：JOIN 全表扫描 → 优化连接顺序

SELECT o.id, p.name, oi.quantity
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.created_at > '2024-01-01';

-- EXPLAIN:
-- id=1, table=o,  type=ALL, rows=850000  (无索引在 created_at)
-- id=1, table=oi, type=ALL, rows=3200000 (无索引在 order_id)
-- id=1, table=p,  type=eq_ref, key=PRIMARY, rows=1
-- 总检查行数: 850000 × 3200000 × 1 ≈ 2.7 万亿！

ALTER TABLE orders ADD INDEX idx_created_at (created_at);
ALTER TABLE order_items ADD INDEX idx_order_id (order_id);

-- EXPLAIN 优化后:
-- id=1, table=o,  type=range, key=idx_created_at, rows=42000
-- id=1, table=oi, type=ref, key=idx_order_id, rows=4
-- id=1, table=p,  type=eq_ref, key=PRIMARY, rows=1
-- 总检查行数: 42000 × 4 × 1 = 168,000

案例 3：关联子查询 → 改写为 JOIN

SELECT c.name, (
  SELECT COUNT(*) FROM orders WHERE customer_id = c.id
) AS order_count
FROM customers c WHERE c.status = 'active';

-- EXPLAIN:
-- id=1, select_type=PRIMARY,            table=c,      type=ref, rows=5000
-- id=2, select_type=DEPENDENT SUBQUERY, table=orders, type=ALL, rows=850000
-- 子查询执行 5000 次，每次扫描 85 万行！

-- 方案 A：先给 orders 加索引，再用 LEFT JOIN + GROUP BY
ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);

SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name;

-- EXPLAIN:
-- id=1, table=c, type=ref, key=idx_status, rows=5000
-- id=1, table=o, type=ref, key=idx_customer_id, rows=4
-- 总检查行数: 5000 × 4 = 20,000

案例 4：filesort → 添加覆盖排序的索引

SELECT id, status, created_at, total
FROM orders WHERE status = 'pending'
ORDER BY created_at DESC LIMIT 20;

-- EXPLAIN:
-- type=ref, key=idx_status, rows=42000, Extra: Using filesort
-- 问题：索引找到 42000 行后还需要全部排序，然后取前 20 行

-- 创建复合索引，让 WHERE + ORDER BY 都走索引
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

-- EXPLAIN:
-- type=ref, key=idx_status_created, rows=20, Extra: Backward index scan
-- MySQL 沿着索引反向扫描 (DESC)，直接取前 20 行，无需 filesort
-- 进一步优化：创建降序索引
ALTER TABLE orders ADD INDEX idx_status_created_desc (status, created_at DESC);

案例 5：派生表物化 → 使用 CTE 或 Lateral Join

SELECT c.name, recent.last_order_date, recent.total
FROM customers c
JOIN (
  SELECT customer_id, MAX(created_at) AS last_order_date, SUM(total) AS total
  FROM orders GROUP BY customer_id
) AS recent ON c.id = recent.customer_id
WHERE c.status = 'vip';

-- EXPLAIN:
-- id=1, table=c,            type=ref,  rows=100
-- id=1, table=<derived2>,   type=ref,  rows=10
-- id=2, select_type=DERIVED, table=orders, type=ALL, rows=850000
-- 问题：派生表物化了 85 万行的全表聚合结果

SELECT c.name, recent.last_order_date, recent.total
FROM customers c,
LATERAL (
  SELECT MAX(created_at) AS last_order_date, SUM(total) AS total
  FROM orders WHERE customer_id = c.id
) AS recent
WHERE c.status = 'vip';

-- 或者使用 CTE + 窗口函数（8.0+）:
WITH ranked AS (
  SELECT customer_id, created_at, total,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders
)
SELECT c.name, r.created_at AS last_order_date, r.total
FROM customers c JOIN ranked r ON c.id = r.customer_id AND r.rn = 1
WHERE c.status = 'vip';

EXPLAIN 常见反模式检测清单

以下是通过 EXPLAIN 输出可以检测到的常见性能反模式。每种反模式都包含检测条件和修复建议。

反模式 1：大表全表扫描

反模式 2：临时表 + 文件排序

反模式 3：估算行数远大于实际结果

反模式 4：possible_keys 有值但 key 为 NULL

反模式 5：DEPENDENT SUBQUERY 逐行执行

反模式 6：函数包裹索引列

反模式 7：隐式类型转换

反模式 8：JOIN 乘数爆炸

Optimizer Trace — 深入优化器决策过程

当 EXPLAIN 告诉你"优化器选择了什么"，但没有告诉你"为什么选择它"时，optimizer_trace 就是你需要的工具。它记录了优化器从接收 SQL 到生成最终执行计划的完整决策链路。

-- 开启 optimizer_trace
SET optimizer_trace = 'enabled=on';
SET optimizer_trace_max_mem_size = 1048576;  -- 1MB

-- 执行你要分析的查询
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 10;

-- 查看完整的优化器决策过程
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

-- 关闭 trace（避免性能开销）
SET optimizer_trace = 'enabled=off';

Trace 输出的三个阶段

1. join_preparation

SQL 解析与预处理阶段。在这里你可以看到：子查询是否被转换为半连接（semi-join）、视图/派生表是否被合并（merge）或物化（materialize）、等价条件传播（equality propagation）等优化转换。

2. join_optimization

这是最核心的阶段。包含：

• condition_processing：条件简化（常量折叠、移除冗余条件等）。
• table_dependencies：表之间的依赖关系。
• ref_optimizer_key_uses：候选的 ref 访问方式。
• rows_estimation：每个表的行数估算和索引选择。这里可以看到为什么某个索引被选择或放弃——代价比较的完整数字。
• considered_execution_plans：所有被考虑的 JOIN 顺序及其代价。
• attaching_conditions_to_tables：条件被分配到哪些表。

3. join_execution

执行阶段的信息（通常较少）。

实战：用 Trace 诊断索引选择问题

-- 查询：优化器有两个候选索引但选了"错误"的那个
-- 在 trace 的 rows_estimation 段中查找:

"rows_estimation": [
  {
    "table": "`orders`",
    "range_analysis": {
      "table_scan": {
        "rows": 850000,
        "cost": 87412.1          -- 全表扫描代价
      },
      "potential_range_indexes": [
        {
          "index": "idx_status",
          "usable": true,
          "key_parts": ["status"]
        },
        {
          "index": "idx_created_at",
          "usable": true,
          "key_parts": ["created_at"]
        }
      ],
      "analyzing_range_alternatives": {
        "range_scan_alternatives": [
          {
            "index": "idx_status",
            "ranges": ["pending <= status <= pending"],
            "rows": 425000,       -- 50% 的行
            "cost": 148750,       -- 比全表扫描还贵！
            "chosen": false,
            "cause": "cost"
          },
          {
            "index": "idx_created_at",
            "ranges": ["2024-01-01 <= created_at"],
            "rows": 42000,
            "cost": 50400,
            "chosen": true
          }
        ]
      }
    }
  }
]

通过 Trace 可以清楚地看到：idx_status 匹配了 50% 的行（选择性太低），代价 148,750 甚至高于全表扫描的 87,412。而 idx_created_at 只匹配 42,000 行，代价 50,400，因此被选中。

EXPLAIN FOR CONNECTION — 诊断正在运行的查询

当你在 SHOW PROCESSLIST 中看到一个运行了很长时间的查询时，可以用 EXPLAIN FOR CONNECTION 查看它的执行计划，而无需复制 SQL 重新执行。

-- 步骤 1：找到问题查询的连接 ID
SELECT id, time, state, LEFT(info, 100) AS query
FROM information_schema.PROCESSLIST
WHERE command = 'Query' AND time > 5
ORDER BY time DESC;

-- 步骤 2：获取执行计划
EXPLAIN FOR CONNECTION 12345;

-- JSON 格式获取更多信息
EXPLAIN FORMAT=JSON FOR CONNECTION 12345;

使用场景

• 线上诊断：生产环境中发现慢查询，不需要复制 SQL、不需要有目标表的权限。
• 准备好的语句：Prepared Statement 的 SQL 中参数已被绑定，EXPLAIN FOR CONNECTION 显示的是绑定后的实际执行计划。
• 长事务分析：结合 performance_schema.events_statements_current 获取更完整的上下文。

限制：(1) 需要 PROCESS 权限；(2) 如果目标查询已进入"Sending data"状态，计划可能不完整；(3) 极短查询可能在你执行 EXPLAIN 前就结束了。

直方图统计与 EXPLAIN 准确性（MySQL 8.0+）

MySQL 8.0 引入了列直方图（Column Histograms），显著改善了 filtered 列的估算准确性，特别是对于数据分布不均匀的列。

为什么需要直方图

传统的索引统计只记录列的基数（cardinality = 不同值的数量）。对于均匀分布的数据足够了，但真实数据往往严重不均匀。例如一个电商平台的订单状态：

-- 数据分布:
-- completed: 85% (850,000 行)
-- shipped:   10% (100,000)
-- pending:    4% (40,000)
-- cancelled:  1% (10,000)

-- 没有直方图时，优化器假设每个值均匀分布:
-- 每个状态的行数 = 1,000,000 / 4 = 250,000

-- 这导致 WHERE status = 'cancelled' 的估算严重偏高
-- 估算 250,000 行，实际 10,000 行

创建和使用直方图

-- 创建直方图
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;
ANALYZE TABLE orders UPDATE HISTOGRAM ON total WITH 256 BUCKETS;

-- 查看直方图信息
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, HISTOGRAM->>'$.histogram-type' AS type,
       JSON_LENGTH(HISTOGRAM->>'$.buckets') AS buckets
FROM information_schema.COLUMN_STATISTICS;

-- 删除直方图
ANALYZE TABLE orders DROP HISTOGRAM ON status;

注意：直方图独立于索引。即使列没有索引，直方图也能帮助优化器更准确地估算 filtered 值。两种直方图类型：

• Singleton：当不同值的数量 <= 桶数时使用。每个桶存储一个精确值和它的频率。
• Equi-height：当不同值的数量 > 桶数时使用。每个桶存储一个值范围和累计频率。

EXPLAIN 优化决策流程图

面对 EXPLAIN 输出，按以下流程快速定位问题：

步骤 1：检查 type 列

• type = ALL 且 rows > 1000？→ 立即优化。添加索引或改写查询。
• type = index 且没有 Using index？→ 不是覆盖索引，可能需要优化。
• type 是 ref/eq_ref/const/range？→ 通常可以接受，继续检查其他列。

步骤 2：检查 key 和 possible_keys

• possible_keys = NULL？→ 没有可用索引，需要创建。
• possible_keys 有值但 key = NULL？→ 有索引但未被使用，检查选择性和统计信息。
• 使用了错误的索引？→ 用 FORCE INDEX 测试，用 optimizer_trace 查看原因。

步骤 3：检查 rows 和 filtered

• rows 很大？→ 索引选择性可能不够，考虑更精确的复合索引。
• filtered 很低（< 20%）？→ 大量行被读取后丢弃，考虑将过滤条件加入索引。
• JOIN 中各表 rows 乘积很大？→ 检查 JOIN 条件的索引，考虑调整 JOIN 顺序。

步骤 4：检查 Extra

• Using filesort？→ 创建覆盖 WHERE + ORDER BY 的复合索引。
• Using temporary？→ 确保 GROUP BY/DISTINCT 列有索引，或与 ORDER BY 列一致。
• Using index？→ 覆盖索引，表现好。
• Using index condition？→ ICP 在工作，一般是好的信号。

步骤 5：检查 select_type

• DEPENDENT SUBQUERY？→ 最高优先级优化——改写为 JOIN。
• DERIVED 且 rows 很大？→ 考虑使用 CTE 或 Lateral Join（MySQL 8.0+）。

步骤 6：使用 EXPLAIN ANALYZE 验证

• 对比估算行数 vs 实际行数——差异大说明统计信息不准。
• 找到 actual time 最高的节点——那就是瓶颈。
• 注意 loops 值——高 loops × 每次时间 = 实际总耗时。

Performance Schema 与 EXPLAIN 配合使用

EXPLAIN 告诉你执行计划，但 Performance Schema 告诉你查询的真实运行情况。两者结合使用是诊断复杂性能问题的最佳实践。

查找最慢的查询模板

-- 按总执行时间排名的 Top 10 查询模板
SELECT DIGEST_TEXT,
       COUNT_STAR AS exec_count,
       ROUND(SUM_TIMER_WAIT/1e12, 2) AS total_sec,
       ROUND(AVG_TIMER_WAIT/1e12, 4) AS avg_sec,
       SUM_ROWS_EXAMINED,
       SUM_ROWS_SENT,
       ROUND(SUM_ROWS_EXAMINED/NULLIF(SUM_ROWS_SENT,0), 1) AS examine_to_send_ratio
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'your_db'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

关键指标解读

工作流示例

-- 1. 从 Performance Schema 找到检查/返回比最高的查询
SELECT DIGEST_TEXT, SUM_ROWS_EXAMINED, SUM_ROWS_SENT,
       ROUND(SUM_ROWS_EXAMINED/NULLIF(SUM_ROWS_SENT,0)) AS ratio
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'mydb' AND SUM_ROWS_SENT > 0
ORDER BY ratio DESC LIMIT 5;

-- 2. 找到问题查询后，用 EXPLAIN 分析执行计划
EXPLAIN FORMAT=JSON SELECT ...;

-- 3. 优化后再次检查 Performance Schema 确认改善
-- （注意：需要先 TRUNCATE 统计表以从零开始计数）
TRUNCATE performance_schema.events_statements_summary_by_digest;

EXPLAIN 揭示的索引设计错误

错误 1：复合索引列顺序不当

-- 索引：INDEX (created_at, status)
-- 查询：
EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND created_at > '2024-01-01';

-- EXPLAIN 显示 key_len 只覆盖了 created_at（范围条件后的列不能用于精确匹配）
-- 正确的索引顺序应该是 INDEX (status, created_at)：
-- 等值条件列在前，范围条件列在后

错误 2：冗余索引

-- 以下索引是冗余的:
INDEX idx_a (a)                  -- 被 idx_ab 覆盖
INDEX idx_ab (a, b)
INDEX idx_abc (a, b, c)          -- idx_ab 也被 idx_abc 覆盖

-- 检查冗余索引:
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'mydb';

错误 3：过度索引

每个索引都增加写操作的代价（INSERT/UPDATE/DELETE 需要维护所有索引）。通过 EXPLAIN 检查哪些索引从未出现在 key 列中——它们可能是无用索引。

-- 查找从未被使用的索引（MySQL 5.6+）:
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema = 'mydb'
ORDER BY object_name;

错误 4：忽略字符集对 key_len 的影响

同样是 VARCHAR(255)，在 latin1 下 key_len = 257（255+2），在 utf8mb4 下 key_len = 1022（255×4+2）。这直接影响：

• 每个索引页能存放的条目数（key_len 越大，条目越少，B-tree 越高）。
• 复合索引的最大列数和总长度限制（InnoDB 最大 3072 字节）。
• 内存中排序时每行占用的空间。

EXPLAIN 速查表

type 列速查

Extra 列速查

EXPLAIN 命令速查

-- 基本用法
EXPLAIN SELECT ...;

-- JSON 格式（更详细）
EXPLAIN FORMAT=JSON SELECT ...;

-- TREE 格式（迭代器视图，8.0.16+）
EXPLAIN FORMAT=TREE SELECT ...;

-- 实际执行并收集统计（8.0.18+）
EXPLAIN ANALYZE SELECT ...;

-- 分析正在运行的查询
EXPLAIN FOR CONNECTION <connection_id>;

-- 查看优化器重写后的 SQL
EXPLAIN SELECT ...; SHOW WARNINGS;

-- 更新表统计信息
ANALYZE TABLE table_name;

-- 创建直方图（8.0+）
ANALYZE TABLE t UPDATE HISTOGRAM ON col WITH 100 BUCKETS;

FAQ

EXPLAIN FORMAT=JSON SELECT ... your slow query ...;

-- 1. 找到目标连接的 ID
SHOW PROCESSLIST;
-- 或
SELECT * FROM performance_schema.processlist WHERE INFO IS NOT NULL;

-- 2. 查看其执行计划
EXPLAIN FOR CONNECTION 42;
EXPLAIN FORMAT=JSON FOR CONNECTION 42;