MySQL 慢查询根治指南：从 EXPLAIN 看懂到索引覆盖率优化的完整链路

MySQL 慢查询根治指南：从 EXPLAIN 看懂到索引覆盖率优化的完整链路

一、"加了索引为什么还慢"——索引失效的六种典型场景

数据库慢查询排查中最让人挫败的场景：表上索引明明存在，EXPLAIN却显示type: ALL（全表扫描）。索引失效不是偶发现象，而是查询写法、字段类型、数据分布三者共同作用下的系统性问题。

根据 MySQL 优化器的决策逻辑，索引失效的根因可以归纳为六类：隐式类型转换、左模糊匹配、函数包裹索引列、OR 条件未命中索引、联合索引的最左前缀不匹配、以及索引选择性过低导致优化器放弃索引。每一个失效场景都有明确的修复路径——关键是要读懂EXPLAIN输出的每一列信号。

二、EXPLAIN 输出解读：从执行计划中捕获性能信号

flowchart TD A["EXPLAIN SELECT ..."] --> B{type 字段} B -->|ALL| C["全表扫描——最差情况<br/>必须优化"] B -->|index| D["全索引扫描——略优于 ALL<br/>仍需关注行数"] B -->|range| E["索引范围扫描——可接受<br/>检查扫描行数与返回行数的比例"] B -->|ref| F["非唯一索引查找——良好<br/>单个索引值匹配多行"] B -->|eq_ref| G["唯一索引查找——优秀<br/>连接查询中每行精确匹配一行"] B -->|const| H["主键常量查找——最优<br/>O(1) 定位"] A --> I{Extra 字段} I -->|Using filesort| J["需排序且无法用索引——瓶颈信号"] I -->|Using temporary| K["需临时表——内存或磁盘代价"] I -->|Using index| L["覆盖索引——最优<br/>回表次数为零"] I -->|Using where| M["索引过滤——正常<br/>需关注 filtered% 值"]

EXPLAIN分析的关键维度矩阵：

三、索引优化的六种实战技巧

-- 技巧 1: 联合索引的最左前缀——where 条件必须从联合索引的首列开始 -- ❌ 索引 idx_user_status_time(user_id, status, create_time) 无法被以下查询使用 status 列 SELECT * FROM orders WHERE status = 'paid' AND create_time > '2025-01-01'; -- ✅ 调整索引顺序或补充单列索引 ALTER TABLE orders ADD INDEX idx_status_time(status, create_time); -- 技巧 2: 覆盖索引消除回表——SELECT 列全部包含在索引中 -- ❌ 需要回表读取 title, content 列 SELECT title, content FROM posts WHERE author_id = 100 ORDER BY create_time DESC LIMIT 20; -- ✅ 建立覆盖索引 (author_id, create_time, title, content) ALTER TABLE posts ADD INDEX idx_cover(author_id, create_time, title, content); -- Extra 显示 Using index —— 零回表 -- 技巧 3: 避免函数包裹索引列——优化器无法使用索引 -- ❌ 对索引列应用函数导致失效 SELECT * FROM users WHERE DATE(register_time) = '2025-01-15'; -- ✅ 改写为范围查询 SELECT * FROM users WHERE register_time >= '2025-01-15 00:00:00' AND register_time < '2025-01-16 00:00:00'; -- 技巧 4: 前缀索引——大索引列的空间效率与选择性权衡 -- 对 VARCHAR(768) 的电商 SKU 码建立前缀索引 ALTER TABLE products ADD INDEX idx_sku_prefix(sku(12)); -- 用以下查询评估前缀选择性：> 95% 即认为可接受 SELECT COUNT(DISTINCT LEFT(sku, 12)) / COUNT(*) FROM products; -- 技巧 5: JOIN 的驱动表选择——小表驱动大表 -- ❌ 大表驱动小表（orders 10M 行，users 10K 行） SELECT u.name, o.amount FROM orders o JOIN users u ON o.user_id = u.id; -- ✅ 小表驱动大表——优化器自动选择，但可用 STRAIGHT_JOIN 强制 SELECT u.name, o.amount FROM users u STRAIGHT_JOIN orders o ON u.id = o.user_id; -- 技巧 6: 分页深翻——OFFSET 的性能灾难 -- ❌ OFFSET 1000000 需要扫描并丢弃前 100 万行 SELECT * FROM articles ORDER BY id LIMIT 20 OFFSET 1000000; -- ✅ 基于游标的分页 SELECT * FROM articles WHERE id > 1000000 ORDER BY id LIMIT 20;

四、索引的成本：写入放大与维护开销

写入性能代价：每个二级索引在 INSERT/UPDATE/DELETE 时都需要同步维护 B-Tree 的插入和页分裂。3 个索引意味着写放大率达到 4 倍（1 主键 + 3 个二级索引）。在高写入速率（10K+ QPS）下，索引维护的 I/O 可能超过数据本身。

页分裂与空间碎片：B-Tree 的页分裂导致索引物理页利用率下降，特别是在 UUID 作为主键时（随机插入导致频繁页分裂）。使用自增主键可将页利用率从 50%~70% 提升至 90%+。

索引选择性评估：COUNT(DISTINCT col) / COUNT(*)低于 5%~10% 的列不建议单独建索引。例如gender（2 个值）的索引选择性为 0.01%——MySQL 优化器宁可全表扫描也不愿回表 50% 的行。

索引覆盖的维护成本：包含业务字段的宽覆盖索引（如包含 TEXT 列）在每次 UPDATE 覆盖列时触发索引页更新。覆盖索引的目标是消除 SELECT 的回表，但代价是增大了 UPDATE 的写入负担——必须在读写比中做取舍。

五、总结

MySQL 索引优化遵循三个核心原则：能用索引 != 用好索引（必须从 type、rows、extra 三个维度综合判断）；覆盖索引优于回表（索引包含 SELECT 全部列时最优）；索引不是越多越好（每个索引都增加写入成本）。

排查路径：先用EXPLAIN FORMAT=JSON获取成本模型信息，锁定 rows 与 filtered 不匹配的查询；再用pt-query-digest从慢查询日志中找到重复出现的高频慢 SQL；最后按索引失效模式逐项修复——注意不要一次添加多个索引后仅看效果，应每次变更一个索引并对比前后的EXPLAIN输出与执行时间。索引优化的本质是空间换时间的工程权衡，每一列是否建索引都应有数据支撑，而非经验判断。