MySQL 索引优化实战：从 EXPLAIN 到查询提速的完整指南

小小卒子2026-10-20数据库⏱ 4min

读完往下滑，给作者一点支持 🌱

MySQL 索引优化实战：从 EXPLAIN 到查询提速的完整指南

索引是关系型数据库性能的基石。同样一条 SQL，在索引缺失与索引合理的情况下，查询耗时可能相差几个数量级。本文从执行计划入手，系统梳理 MySQL 索引的设计原则、失效场景与调优方法论。

一、先看懂 EXPLAIN

任何索引优化都从读懂执行计划开始。EXPLAIN SELECT ... 输出每一行代表一个执行步骤，核心列的含义如下：

| 列名 | 含义 | 需要警惕的值 | |------|------|--------------| | type | 访问类型，从好到差依次为 | system > const > eq_ref > ref > range > index > ALL | | key | 实际使用的索引 | NULL 表示没走索引 | | rows | 预估扫描行数 | 与实际行数偏差过大说明统计信息陈旧 | | Extra | 附加信息 | Using filesort / Using temporary 是需要优化的信号 |

最需要记住的一条经验：type 为 ALL（全表扫描）或 index（扫描整个索引）时，先停下来分析。只要命中 range 及以上，SQL 通常还有救。

二、索引失效的十个高频场景

2.1 对索引列做函数运算

-- 反例：对 created_at 取函数，索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2026-10-01';

-- 正解：改成范围查询，走索引
SELECT * FROM orders
WHERE created_at >= '2026-10-01 00:00:00'
  AND created_at < '2026-10-02 00:00:00';

2.2 隐式类型转换

-- 反例：phone 是 varchar，传入数字导致隐式转换，索引失效
SELECT * FROM users WHERE phone = 13800138000;

-- 正解：加引号，保持类型一致
SELECT * FROM users WHERE phone = '13800138000';

2.3 前导模糊查询

-- '%xx' 开头无法利用索引，但 'xx%' 可以
SELECT * FROM users WHERE name LIKE '%小明%';  -- 索引失效
SELECT * FROM users WHERE name LIKE '小明%';    -- 走 range

2.4 联合索引违反最左前缀

联合索引 (a, b, c) 遵循最左前缀原则：WHERE b=1 AND c=2 用不上，WHERE a=1 AND c=2 只能用到 a。

2.5 索引列参与计算或比较

-- 反例
SELECT * FROM products WHERE price * 0.9 < 100;
-- 正解：把计算移到常量侧
SELECT * FROM products WHERE price < 100 / 0.9;

三、索引设计五原则

区分度优先：选择性 = 去重值数 / 总行数，低于 20% 的列单独建索引意义不大。
联合索引按"等值在前、排序在后"排列：WHERE a=? AND b>? ORDER BY c，索引设计为 (a, b, c) 可同时满足过滤与排序。
覆盖索引是查询提速神器：(a, b, c) 索引让 SELECT a,b,c 直接从索引树返回，避免回表。
控制索引数量：单表索引控制在 5 个以内，每个索引都要考虑写入成本。
字符串前缀索引：大字段用 INDEX idx_name(name(20))，用长度换空间。

四、order by / group by 的索引优化

-- 反例：产生 Using filesort
SELECT id, title FROM articles WHERE category_id = 5 ORDER BY created_at DESC;

-- 正解：联合索引 (category_id, created_at)，排序免 filesort
ALTER TABLE articles ADD INDEX idx_cat_time (category_id, created_at);

GROUP BY 同理，本质是先排序再分组。给 group by 列建联合索引能显著提升性能。

五、分页深翻页的优化

LIMIT 100000, 20 需要扫描 100020 行再丢弃前 10 万行，这是经典性能陷阱：

-- 反例：深翻页
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 方案一：延迟关联（先取主键，再回表）
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) t
ON o.id = t.id;

-- 方案二：游标/键集分页（推荐）
SELECT * FROM orders
WHERE id > :last_seen_id
ORDER BY id LIMIT 20;

六、count 的优化思路

COUNT(*) 和 COUNT(1) 在 MySQL 8 中性能无差异，别纠结；
大表精确 count 无法避免全表扫描（InnoDB 不缓存行数），业务上可用缓存计数、抽样估算替代；
条件 count 走覆盖索引：SELECT COUNT(*) FROM t WHERE status=1，若 (status) 有索引则走 Index Only Scan。