MySQL 索引优化与执行计划分析：从全表扫描到精确命中

Dicky-_-zhang

1140人浏览 · 2026-06-11 10:30:51

Dicky-_-zhang · 2026-06-11 10:30:51 发布

MySQL 索引优化与执行计划分析：从全表扫描到精确命中

cover

一、索引失效的"隐形杀手"：查询慢但不知道为什么

MySQL 索引失效是后端开发中最常见也最隐蔽的性能问题。一条查询明明在 WHERE 条件上建了索引，EXPLAIN 却显示 type=ALL（全表扫描）。常见的失效场景包括：WHERE 条件中对索引列使用函数、隐式类型转换、OR 条件中包含非索引列、LIKE 前缀通配符、联合索引未遵循最左前缀原则。

更隐蔽的是"索引选择错误"：MySQL 优化器基于统计信息选择索引，当统计信息过期或数据分布倾斜时，优化器可能选择次优索引，导致查询性能远低于预期。理解 EXPLAIN 执行计划的每个字段含义，是诊断索引问题的基本功。

二、EXPLAIN 执行计划深度解读

EXPLAIN 的输出包含多个关键字段，每个字段都承载着查询优化的线索。

flowchart TD
    A[EXPLAIN 输出] --> B[type：访问类型]
    A --> C[key：使用索引]
    A --> D[rows：预估扫描行数]
    A --> E[Extra：额外信息]
    A --> F[possible_keys：候选索引]

    B --> B1[system > const > eq_ref > ref]
    B --> B2[range > index > ALL]
    B1 --> G[高效访问]
    B2 --> H[低效访问，需优化]

    E --> E1[Using index：覆盖索引]
    E --> E2[Using filesort：额外排序]
    E --> E3[Using temporary：临时表]
    E --> E4[Using where：回表过滤]

    E2 --> I[性能风险点]
    E3 --> I

type 字段从最优到最差依次为：system → const → eq_ref → ref → range → index → ALL。生产环境中，至少应达到 ref 级别，ALL 级别必须优化。key 字段显示实际使用的索引，如果为 NULL 说明没有使用索引。rows 字段是优化器预估的扫描行数，与实际行数的偏差反映了统计信息的准确性。

三、索引优化实战

3.1 联合索引的最左前缀原则

-- 创建联合索引
ALTER TABLE orders ADD INDEX idx_user_status_created
    (user_id, status, created_at);

-- 命中索引：遵循最左前缀
SELECT * FROM orders WHERE user_id = 1001;
SELECT * FROM orders WHERE user_id = 1001 AND status = 'PAID';
SELECT * FROM orders WHERE user_id = 1001 AND status = 'PAID'
    AND created_at > '2024-01-01';

-- 未命中索引：跳过最左列
SELECT * FROM orders WHERE status = 'PAID';
SELECT * FROM orders WHERE created_at > '2024-01-01';

-- 部分命中：跳过中间列（只命中 user_id）
SELECT * FROM orders WHERE user_id = 1001
    AND created_at > '2024-01-01';

-- 索引列顺序选择原则：
-- 1. 等值查询列在前，范围查询列在后
-- 2. 选择性高的列在前（区分度大）
-- 3. 排序/分组列考虑放在最后

3.2 覆盖索引消除回表

-- 回表查询：需要回到主键索引获取完整行
SELECT * FROM orders WHERE user_id = 1001 AND status = 'PAID';

-- 覆盖索引：查询列全部包含在索引中，无需回表
SELECT user_id, status, created_at
FROM orders WHERE user_id = 1001 AND status = 'PAID';

-- EXPLAIN 对比
-- 回表查询：Extra = Using where
-- 覆盖索引：Extra = Using index（性能提升显著）

-- 实际优化案例：订单列表查询
-- 原始查询（回表）
SELECT id, order_no, amount, status, created_at
FROM orders
WHERE user_id = 1001 AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

-- 优化方案：创建覆盖索引
ALTER TABLE orders ADD INDEX idx_user_status_created_cover
    (user_id, status, created_at, order_no, amount);

-- 优化后查询（覆盖索引 + 避免回表 + 索引排序）
SELECT id, order_no, amount, status, created_at
FROM orders
WHERE user_id = 1001 AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;
-- Extra: Using index

3.3 索引失效场景与修复

-- 场景 1：对索引列使用函数
-- 失效
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- 修复：改为范围查询
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 场景 2：隐式类型转换
-- 失效（user_id 是 INT，传入字符串）
SELECT * FROM orders WHERE user_id = '1001';
-- 修复：确保类型一致
SELECT * FROM orders WHERE user_id = 1001;

-- 场景 3：LIKE 前缀通配符
-- 失效
SELECT * FROM orders WHERE order_no LIKE '%ORD-2024%';
-- 修复：使用前缀匹配或全文索引
SELECT * FROM orders WHERE order_no LIKE 'ORD-2024%';

-- 场景 4：OR 条件包含非索引列
-- 失效（status 无索引）
SELECT * FROM orders
WHERE user_id = 1001 OR status = 'PAID';
-- 修复：为 OR 两侧都建索引，或改写为 UNION
SELECT * FROM orders WHERE user_id = 1001
UNION
SELECT * FROM orders WHERE status = 'PAID';

-- 场景 5：索引统计信息过期导致选择错误
-- 强制更新统计信息
ANALYZE TABLE orders;

-- 强制使用指定索引
SELECT * FROM orders FORCE INDEX(idx_user_status_created)
WHERE user_id = 1001 AND status = 'PAID';

3.4 执行计划分析工具

-- MySQL 8.0+ 使用 EXPLAIN ANALYZE 获取实际执行统计
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 1001 AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

-- 输出包含实际执行时间，比预估 rows 更准确
-- 示例输出：
-- -> Limit: 20 row(s)  (cost=0.35 rows=20) (actual time=0.12..0.15 rows=20 loops=1)
--   -> Index scan on orders using idx_user_status_created  (cost=0.35 rows=20) (actual time=0.12..0.14 rows=20 loops=1)

-- 查看索引使用统计
SELECT
    index_name,
    rows_read,
    selectivity
FROM sys.schema_index_statistics
WHERE table_schema = 'mydb'
ORDER BY rows_read DESC;

-- 查看未使用的索引（定期清理）
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';