跳至主要內容

执行计划分析

SunnyFan大约 15 分钟约 4549 字

执行计划分析

简介

执行计划（Execution Plan）是数据库优化器根据 SQL 语句、表统计信息和索引信息生成的查询执行策略。它决定了数据检索的路径、索引选择、表连接方式和排序策略。通过 EXPLAIN 命令可以查看执行计划，它是 SQL 性能调优的起点——不懂执行计划，优化就是盲猜。

为什么执行计划分析如此重要

在生产环境中，一个 SQL 语句的性能差异可能达到几个数量级。同样的查询，走索引可能 1 毫秒返回，走全表扫描可能需要 30 秒。执行计划分析能够：

  1. 快速定位瓶颈：一眼看出是全表扫描、索引扫描还是索引查找，是嵌套循环还是哈希连接
  2. 量化优化效果：优化前后对比 rows、filtered、实际耗时，效果可度量
  3. 提前预防问题：上线前对 SQL 做 EXPLAIN，把性能问题拦截在开发阶段
  4. 理解优化器行为：通过分析执行计划，理解优化器为什么选择了某种方案，而不是我们期望的方案

EXPLAIN 核心字段详解

MySQL EXPLAIN 输出字段

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
字段含义关注点
idSELECT 标识符，相同 id 表示同一 SELECT子查询时会有多个 id，从大到小执行
select_type查询类型SIMPLE(简单查询) > PRIMARY(最外层) > SUBQUERY(子查询) > DERIVED(派生表)
table访问的表名<derived2> 表示派生表，<union1,2> 表示联合结果
type访问类型（最重要！）从优到差：system > const > eq_ref > ref > range > index > ALL
possible_keys可能使用的索引NULL 表示没有候选索引
key实际使用的索引NULL 表示没有使用索引（全表扫描）
key_len使用索引的字节数越短越好，判断联合索引使用了几个列
rows预估扫描行数越少越好，注意这只是估算值
filtered过滤比例rows * filtered = 实际返回行数估算
Extra额外信息非常重要，见下文详解

type 字段详解（从最优到最差）

type说明示例
system表只有一行（系统表）几乎不会出现在业务表中
const主键/唯一索引等值查询，最多返回一行WHERE id = 100
eq_ref唯一索引等值关联，对于前表每一行，后表只匹配一行JOIN 时使用主键关联
ref非唯一索引等值查询，可能返回多行WHERE user_id = 100
range索引范围扫描WHERE id BETWEEN 1 AND 100
index全索引扫描，只扫描索引不回表SELECT id FROM orders
ALL全表扫描WHERE status = 'pending' 且 status 无索引

生产环境红线：type 为 ALL 的查询必须优化，除非是数据量很小的配置表。

Extra 字段详解

Extra 值含义是否需要优化
Using index覆盖索引，无需回表优秀
Using where在存储引擎层之后进行过滤正常
Using index condition索引下推（ICP），存储引擎层提前过滤优秀
Using temporary使用临时表处理查询通常需要优化
Using filesort额外排序操作（非索引排序）通常需要优化
Using join buffer使用连接缓冲区（嵌套循环无索引关联）需要优化
Select tables optimized away优化器直接返回结果（如 COUNT(*) 有索引）优秀
Impossible WHEREWHERE 条件恒为假检查 SQL 逻辑

实战示例

示例 1：基础 EXPLAIN 分析

-- 创建测试表
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    customer_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    total_amount DECIMAL(12, 2),
    created_at DATETIME NOT NULL,
    INDEX idx_status_created (status, created_at),
    INDEX idx_customer (customer_id)
) ENGINE=InnoDB;

-- 查看执行计划
EXPLAIN SELECT o.order_id, o.total_amount, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'shipped'
  AND o.created_at >= '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 50;

-- 分析要点：
-- 1. orders 表的 type 应该是 range（因为 idx_status_created 支持范围查询）
-- 2. key 应该是 idx_status_created
-- 3. customers 表的 type 应该是 eq_ref（通过主键关联）
-- 4. Extra 不应出现 Using filesort（因为索引本身就是按 created_at 排序的）
-- 5. Extra 不应出现 Using temporary

示例 2：MySQL 8.0 EXPLAIN ANALYZE 实际执行

-- 实际执行并返回真实耗时（MySQL 8.0.18+）
EXPLAIN ANALYZE
SELECT p.product_name, SUM(oi.quantity) AS total_sold
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
WHERE oi.created_at BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY p.product_name
ORDER BY total_sold DESC
LIMIT 20;

-- 输出示例与解读：
-- -> Limit: 20 row(s)  (actual time=0.45..0.45 rows=20 loops=1)
--   -> TopN sort: 20 row(s), Sort field: total_sold DESC
--     (actual time=0.3..0.4 rows=150 loops=1)
--       -> Stream grouping: p.product_name
--     (actual time=0.05..0.35 rows=5000 loops=1)
--       -> Nested loop inner join
--     (actual time=0.01..0.2 rows=5000 loops=1)
--         -> Index scan on oi (actual time=0.005..0.05 rows=10000 loops=1)
--         -> Single-row index lookup on p
--     (actual time=0.001..0.01 rows=1 loops=10000)

-- 关键指标解读：
-- actual time=X..Y：X 是第一行返回时间，Y 是最后一行返回时间
-- rows=N：实际处理的行数（不是估算！）
-- loops=N：该节点被循环执行的次数

EXPLAIN 与 EXPLAIN ANALYZE 的区别：

特性EXPLAINEXPLAIN ANALYZE
执行查询否，只生成计划是，实际执行
行数数据估算值实际值
耗时数据无真实耗时
DML 安全安全（不执行）危险：会实际执行 INSERT/UPDATE/DELETE
适用场景日常开发调优验证执行计划准确性

示例 3：PostgreSQL 执行计划分析

-- PostgreSQL 查看执行计划
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.order_id, c.name, sum(oi.amount)
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.created_at >= '2024-01-01'
GROUP BY o.order_id, c.name
HAVING sum(oi.amount) > 10000;

-- 输出关键指标解读：
-- Seq Scan → 全表扫描，需要加索引
-- Index Scan → 索引扫描，正常
-- Index Only Scan → 只读索引不回表（类似 MySQL 的覆盖索引）
-- Bitmap Heap Scan + Bitmap Index Scan → 位图扫描，适合多条件 OR 查询
-- Hash Join → 哈希连接，适合大表连接
-- Nested Loop → 嵌套循环，适合小表驱动大表
-- Merge Join → 归并连接，适合两端都有序的情况

-- BUFFERS 关键指标：
-- shared hit=N → 缓存命中次数，越高越好
-- shared read=N → 磁盘读取次数，越低越好
-- shared hit / (shared hit + shared read) = 缓存命中率

-- 判断统计信息是否准确：
-- 如果 actual rows 与 plan rows 差距超过 10 倍，说明统计信息过时
-- 解决方案：ANALYZE orders;

示例 4：通过 Hints 引导优化器

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

-- 强制忽略指定索引
SELECT * FROM orders IGNORE INDEX (idx_status_created)
WHERE status = 'pending';

-- 强制连接顺序（STRAIGHT_JOIN 让左侧表驱动右侧表）
SELECT STRAIGHT_JOIN o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'shipped';

-- MySQL 8.0 优化器 hint 语法（注释形式）
SELECT /*+ INDEX(o idx_status_created) */ *
FROM orders o
WHERE status = 'pending' AND created_at > '2024-06-01';

-- MySQL 8.0 更多 hint
SELECT /*+ JOIN_ORDER(o, c) */ *        -- 指定连接顺序
FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * -- 限制最大执行时间 1 秒
FROM orders WHERE status = 'pending';

SELECT /*+ SET_VAR(sort_buffer_size = 4M) */ *
FROM orders ORDER BY created_at DESC LIMIT 100;

示例 5：常见慢查询模式的 EXPLAIN 分析

-- 模式 1：索引失效之隐式类型转换
CREATE TABLE users (
    user_id VARCHAR(36) PRIMARY KEY,  -- UUID 字符串
    name VARCHAR(100),
    email VARCHAR(200),
    INDEX idx_email (email)
);

-- 错误：email 是 VARCHAR，传入数字导致隐式转换
EXPLAIN SELECT * FROM users WHERE email = 12345;
-- type: ALL（全表扫描！）

-- 正确：传入字符串类型
EXPLAIN SELECT * FROM users WHERE email = '12345';
-- type: ref（索引查找）

-- 模式 2：OR 条件导致索引失效
EXPLAIN SELECT * FROM orders
WHERE order_id = 1001 OR customer_id = 5001;
-- 如果 customer_id 有索引但 order_id 是主键，MySQL 可能选择全表扫描
-- 优化方案：使用 UNION ALL
SELECT * FROM orders WHERE order_id = 1001
UNION ALL
SELECT * FROM orders WHERE customer_id = 5001 AND order_id != 1001;

-- 模式 3：ORDER BY 和 WHERE 索引冲突
-- idx_status_created (status, created_at) 支持 WHERE status AND ORDER BY created_at
EXPLAIN SELECT * FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;
-- Extra: Backward index scan; Using index（正常）

-- 但如果 ORDER BY 的列不在索引中
EXPLAIN SELECT * FROM orders
WHERE status = 'paid'
ORDER BY total_amount DESC
LIMIT 50;
-- Extra: Using filesort; Using where（需要额外排序）

连接类型对比

MySQL 支持三种表连接算法，优化器会根据统计信息自动选择：

连接类型适用场景时间复杂度内存占用
Nested-Loop Join小表驱动大表，驱动表有索引O(N * M * logK)低
Block Nested-Loop Join关联列无索引O(N * M)中（join_buffer_size）
Hash Join大表连接大表（MySQL 8.0+）O(N + M)高（需要建哈希表）
-- MySQL 8.0 Hash Join 示例
SET SESSION optimizer_switch = 'block_nested_loop=off';  -- 禁用 BNL
EXPLAIN SELECT *
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.created_at > '2024-01-01';
-- type: hash join（大表连接）

-- 强制使用 hash join（MySQL 8.0+）
SELECT /*+ HASH_JOIN(o, oi) */ *
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id;

优点

SQL Server 执行计划

图形化执行计划基础

-- SQL Server 支持预估执行计划和实际执行计划
-- SSMS 中：Ctrl+L（预估）或 Ctrl+M（实际）

-- SET SHOWPLAN_TEXT（文本格式预估计划）
SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM Orders WHERE UserId = 100;
GO
SET SHOWPLAN_TEXT OFF;
GO

-- SET STATISTICS PROFILE（实际执行计划 + 详细信息）
SET STATISTICS PROFILE ON;
GO
SELECT o.OrderId, o.TotalAmount, u.UserName
FROM Orders o
INNER JOIN Users u ON o.UserId = u.Id
WHERE u.Status = 'active';
GO
SET STATISTICS PROFILE OFF;
GO

-- SET STATISTICS IO + TIME（I/O 和时间统计）
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT * FROM Orders WHERE UserId = 100;
GO
-- 输出：
-- Table 'Orders'. Scan count 1, logical reads 15, physical reads 0
-- CPU time = 0 ms,  elapsed time = 2 ms

-- 关键执行计划操作符：
-- Index Seek — 索引查找（好）
-- Index Scan — 索引扫描（看数据量）
-- Table Scan — 表扫描（通常差）
-- Nested Loops — 嵌套循环（适合小数据集）
-- Hash Match — 哈希匹配（适合大数据集）
-- Merge Join — 合并连接（适合有序数据）
-- Key Lookup — 键查找（回表，考虑覆盖索引）
-- Sort — 排序（看是否必要）
-- Compute Scalar — 计算标量
-- Filter — 过滤
-- Stream Aggregate — 流聚合
-- Hash Aggregate — 哈希聚合

执行计划中的成本估算

-- 查看查询成本
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
GO

-- 子查询成本对比
-- 方案 A：子查询
SELECT * FROM Orders
WHERE UserId IN (SELECT Id FROM Users WHERE Status = 'active');
-- 查看逻辑读和 CPU 时间

-- 方案 B：JOIN
SELECT o.* FROM Orders o
INNER JOIN Users u ON o.UserId = u.Id
WHERE u.Status = 'active';
-- 对比两者的逻辑读

-- 查看执行计划缓存
SELECT
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
    SUBSTRING(qt.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_worker_time DESC;

-- 查看执行计划重编译原因
SELECT
    DB_NAME(database_id) AS database_name,
    object_name(object_id) AS object_name,
    sql_text,
    plan_handle,
    reason
FROM sys.dm_exec_query_optimizer_replay_scenario;

-- 参数嗅探问题诊断
-- 同一 SQL 不同参数导致不同执行计划
SELECT
    qs.plan_handle,
    qs.execution_count,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.execution_count > 10;

执行计划强制与提示

-- 使用查询提示强制执行计划
-- 强制使用特定索引
SELECT * FROM Orders WITH (INDEX(idx_user_id))
WHERE UserId = 100;

-- 强制连接方式
SELECT * FROM Orders o
INNER HASH JOIN Users u ON o.UserId = u.Id  -- 强制 Hash Join
WHERE u.Status = 'active';

SELECT * FROM Orders o
INNER LOOP JOIN Users u ON o.UserId = u.Id  -- 强制 Nested Loop Join
WHERE u.Status = 'active';

SELECT * FROM Orders o
INNER MERGE JOIN Users u ON o.UserId = u.Id  -- 强制 Merge Join
WHERE u.Status = 'active';

-- RECOMPILE 提示（每次重新编译执行计划）
SELECT * FROM Orders WHERE UserId = @UserId
OPTION (RECOMPILE);
-- 适用于参数值差异大的查询

-- OPTIMIZE FOR 提示（针对特定参数优化）
SELECT * FROM Orders WHERE UserId = @UserId AND Status = @Status
OPTION (OPTIMIZE FOR (@UserId UNKNOWN, @Status = 'paid'));
-- 适合已知热点参数值的情况

-- MAXDOP 提示（限制并行度）
SELECT * FROM Orders o
JOIN OrderItems oi ON o.OrderId = oi.OrderId
JOIN Products p ON oi.ProductId = p.Id
OPTION (MAXDOP 2);

-- FAST N 提示（优化前 N 行的返回速度）
SELECT * FROM Orders
WHERE Status = 'paid'
ORDER BY CreatedAt DESC
OPTION (FAST 20);
-- 适合分页场景，优化前 20 行的返回速度

PostgreSQL 执行计划

EXPLAIN 详解

-- 基础 EXPLAIN
EXPLAIN SELECT * FROM products WHERE price > 1000;

-- EXPLAIN ANALYZE（实际执行 + 耗时统计）
EXPLAIN (ANALYZE, BUFFERS, COSTS, VERBOSE)
SELECT p.name, p.price, c.name AS category
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.price > 500
ORDER BY p.price DESC
LIMIT 20;

-- 输出解读：
-- Seq Scan on products — 顺序扫描（全表扫描）
-- Index Scan using idx_price on products — 索引扫描
-- Index Only Scan using idx_cover on products — 仅索引扫描
-- Bitmap Heap Scan on products — 位图扫描
-- Bitmap Index Scan on idx_price — 位图索引扫描
-- Nested Loop — 嵌套循环连接
-- Hash Join — 哈希连接
-- Merge Join — 合并连接
-- Sort — 排序
-- Limit — 限制
-- Aggregate — 聚合

-- 关键指标：
-- cost=0.00..15.30 — 预估代价（startup..total）
-- rows=100 — 预估行数
-- width=68 — 预估每行宽度（字节）
-- actual time=0.05..0.30 — 实际耗时（毫秒）
-- actual rows=95 — 实际返回行数
-- loops=1 — 执行次数
-- shared hit=15 — 缓冲池命中
-- shared read=3 — 磁盘读取

-- 查看自动 EXPLAIN（记录慢查询的执行计划）
-- postgresql.conf
-- auto_explain.log_min_duration = 1000  -- 记录超过 1 秒的查询
-- auto_explain.log_analyze = ON
-- auto_explain.log_buffers = ON
-- LOAD 'auto_explain';  -- 加载扩展

PostgreSQL 执行计划调优

-- 启用/禁用优化器开关
SET enable_seqscan = OFF;      -- 禁用顺序扫描
SET enable_indexscan = ON;     -- 启用索引扫描
SET enable_bitmapscan = ON;    -- 启用位图扫描
SET enable_hashjoin = ON;      -- 启用 Hash Join
SET enable_mergejoin = ON;     -- 启用 Merge Join
SET enable_nestloop = ON;      -- 启用 Nested Loop Join
SET enable_sort = ON;          -- 启用排序
SET enable_parallel_seqscan = ON; -- 启用并行顺序扫描

-- 并行查询配置
SET max_parallel_workers_per_gather = 4;  -- 每个查询最大并行 worker
SET min_parallel_table_scan_size = '8MB';
SET parallel_setup_cost = 1000;

-- 查看当前查询的并行度
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM large_table;
-- Gather Workers: 4（使用了 4 个并行 worker）

-- pg_stat_statements 扩展（查询统计）
CREATE EXTENSION pg_stat_statements;

-- 查看最耗时的查询
SELECT
    query,
    calls,
    total_exec_time / 1000 AS total_ms,
    mean_exec_time / 1000 AS avg_ms,
    rows,
    shared_blks_hit,
    shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 重置统计
SELECT pg_stat_statements_reset();

缺点

总结

执行计划分析是 SQL 调优的核心工具，必须掌握 type 字段、Extra 字段、连接类型三大解读维度。建议建立"所有上线 SQL 必须过 EXPLAIN"的流程规范，把性能问题拦截在上线前。

掌握执行计划分析的能力进阶路径：

  1. 初级：能看懂 type、key、rows、Extra，判断是否需要优化
  2. 中级：能分析连接算法选择、索引使用效率、预估与实际偏差
  3. 高级：能通过调整统计信息、索引设计和 SQL 写法引导优化器选择最优计划
  4. 专家：能理解优化器代价模型，分析 Plan Stability 问题

关键知识点

  • type 字段从好到差：const > eq_ref > ref > range > index > ALL，生产环境应避免 ALL
  • Extra 出现 Using filesort 或 Using temporary 通常需要优化
  • ANALYZE TABLE 更新统计信息可以让优化器做出更好的决策
  • 复杂查询可能产生多个执行计划片段（id 不同），从上到下、从内到外阅读
  • MySQL 8.0 的 EXPLAIN ANALYZE 提供真实执行数据，是验证优化效果的利器

项目落地视角

  • 建立 SQL 审查流水线：所有 DDL 和慢 SQL 上线前必须附带 EXPLAIN 结果
  • 设置 long_query_time 捕获慢查询，定期 EXPLAIN 分析优化
  • 使用 sys.schema_unused_indexes 找出未使用的索引及时清理
  • 对关键报表 SQL 维护 EXPLAIN 快照，执行计划变化时告警
  • 建立 SQL 性能基线，每次变更前后对比执行计划

常见误区

  • 只看 rows 不看 type：rows 少不代表不走全表扫描，小表全表扫描 rows 也少
  • 认为加了索引就一定走索引：优化器可能因为成本估算选择全表扫描，尤其在数据量小或选择性低时
  • 忽略统计信息的影响：大量数据变更后不跑 ANALYZE TABLE，执行计划可能不准确
  • 只优化单条 SQL 不看整体：有时多条低效 SQL 合并成一条更高效，或者通过冗余字段避免 JOIN
  • 过度依赖 Hint：Hint 只是临时方案，根本解决方式是优化索引和统计信息

进阶路线

  • 学习 MySQL 优化器代价模型：mysql.server_cost 和 mysql.engine_cost 表
  • 研究 PostgreSQL 的 pg_stat_statements 自动化慢查询分析
  • 了解执行计划缓存与参数嗅探（Parameter Sniffing）问题
  • 学习数据库内核的查询优化算法：CBO（代价优化）与 RBO（规则优化）
  • 研究 MySQL 8.0 的 Window Function 优化和递归 CTE 优化

适用场景

  • 慢查询优化：定位全表扫描、低效连接、缺失索引等瓶颈
  • SQL 上线审查：上线前检查执行计划，预防性能回退
  • 索引效果验证：添加索引后对比 EXPLAIN 确认是否生效
  • 性能回归测试：版本升级或数据变更后验证执行计划是否变化

落地建议

  • 建立慢查询看板：pt-query-digest 定期分析慢日志，TOP 10 必须有 EXPLAIN
  • 关键 SQL 做 Plan Baseline：MySQL 8.0 使用 optimizer_switch 固定好的执行计划
  • 每次大版本升级后重新审查核心 SQL 的执行计划
  • 建立团队 SQL 审查清单，将 EXPLAIN 分析作为 Code Review 的标准步骤

排错清单

  • 检查 type 是否为 ALL 或 index（全表扫描或全索引扫描）
  • Extra 中是否有 Using filesort 或 Using temporary
  • key 是否使用了预期的索引，rows 估算是否合理
  • 连接类型是否合理（小表驱动大表、是否有 Hash Join 优化空间）
  • filtered 比例是否过低（< 10% 说明过滤条件效率低）

复盘问题

  • 是否有 SQL 上线后执行计划突变导致性能下降的情况？
  • 统计信息更新策略是什么？是否在大量数据导入后手动 ANALYZE？
  • 最近优化的 TOP 5 慢查询，执行计划有什么共同特征？
  • 团队中是否每个人都具备基本的 EXPLAIN 分析能力？

延伸阅读

Copyright © 2026 SunnyFan