用了索引还是很慢，可能是什么原因？

#中间件#面试八股

用了索引还是很慢，可能是什么原因？

结论：「有索引」不等于「走了索引」，更不等于「走得快」。慢的原因通常落在四层：索引没被用上、用错了索引、用上了但回表/排序代价高、瓶颈根本不在 SQL（锁、连接、网络、硬件）。

一、四层归因

层次	典型现象	根因
没走索引	`type=ALL`，`key=NULL`	函数、隐式转换、前导模糊、`OR` 混用、区分度太低
走错索引	`key` 不是预期的那条	统计信息过期、优化器成本估算偏差
走了但慢	`rows` 很小但耗时长	回表次数多、`filesort`、`Using temporary`、深分页
不在 SQL	单条很快、整体很慢	锁等待、连接池耗尽、网络往返、磁盘 IO 饱和

二、诊断路径

SQL 慢
 ├─ EXPLAIN：type=ALL 或 key=NULL？
 │    └─ 是 → 检查表达式、类型、字符集、模糊匹配、OR
 ├─ rows 很小但 P99 高？
 │    ├─ SHOW PROCESSLIST 看到大量 Waiting for lock → 锁竞争
 │    ├─ Extra 有 Using filesort / temporary → 排序与临时表
 │    └─ Handler_read% 暴涨 → 回表随机 IO
 └─ 单条 SQL 快、接口慢？
      └─ 连接池、网络 RTT、N+1 查询、结果集过大

三、最常见的六个坑

1. 隐式类型转换：字符串列与数字比较，索引失效。

-- phone 是 VARCHAR，右边是数字 → 逐行转数字比较，索引失效
SELECT * FROM t_user WHERE phone = 13800000000;

-- 正确：类型对齐
SELECT * FROM t_user WHERE phone = '13800000000';

2. 索引列上做运算或函数：

-- 失效
SELECT * FROM t_order WHERE DATE(ctime) = '2026-11-03';
-- 改成范围
SELECT * FROM t_order WHERE ctime >= '2026-11-03 00:00:00'
                         AND ctime <  '2026-11-04 00:00:00';

3. 优化器选错索引：统计信息过期或数据分布倾斜。

ANALYZE TABLE t_order;                    -- 刷新统计信息
SHOW INDEX FROM t_order;                  -- 看 Cardinality
EXPLAIN SELECT ... ;                      -- 再确认 key

-- 应急手段（需评审，别长期用）
SELECT * FROM t_order FORCE INDEX (idx_user_ctime) WHERE ...;

4. 排序与临时表：ORDER BY 的列顺序与索引不一致，或 GROUP BY 无法利用索引。

ALTER TABLE t_order ADD KEY idx_user_ctime (user_id, ctime);
-- 索引顺序与 ORDER BY 对齐后可去掉 filesort
SELECT id, ctime FROM t_order WHERE user_id = 42 ORDER BY ctime DESC LIMIT 20;

5. 回表太多：返回行数大时，每行一次随机页访问。

-- 只看索引能否覆盖
EXPLAIN SELECT user_id, ctime FROM t_order WHERE user_id = 42;
-- Extra: Using index 表示不回表

6. 锁等待与热点行：

SELECT * FROM performance_schema.data_lock_waits;
SELECT trx_id, trx_state, trx_query FROM information_schema.innodb_trx;
-- 看到大量 wait 时，先解决事务边界，而不是加索引

四、容易被忽略的非 SQL 因素

因素	表现	排查
连接池过小	接口 RT 高但 DB 空闲	看 `Threads_running` 与池等待队列
结果集过大	网络传输占大头	`rows_sent` 与返回字节数
N+1 查询	一次请求几十条小 SQL	APM 里按 SQL 条数聚合
磁盘 IO 饱和	`iowait` 高、随机读多	`iostat`、`innodb_buffer_pool_reads`
buffer pool 太小	命中率低、频繁读盘	`innodb_buffer_pool_size` 与命中率
统计信息陈旧	计划突变	`ANALYZE TABLE`、监控执行计划变化

五、一个完整的排查动作

-- 1. 确认计划
EXPLAIN ANALYZE SELECT id, title FROM article
WHERE author = 'ruilin' ORDER BY ctime DESC LIMIT 20;

-- 2. 确认索引与区分度
SHOW INDEX FROM article;
SELECT COUNT(DISTINCT author) / COUNT(*) AS sel_author FROM article;

-- 3. 确认扫描量与实际读取
FLUSH STATUS;
SELECT id, title FROM article WHERE author = 'ruilin' ORDER BY ctime DESC LIMIT 20;
SHOW SESSION STATUS LIKE 'Handler_read%';

-- 4. 需要时刷新统计
ANALYZE TABLE article;

常见追问

为什么加了索引反而更慢？ 优化器可能选了一条选择性差的索引，或者多维护一棵 B+Tree 增加了写入成本；也可能是回表次数变多。
EXPLAIN 显示走了索引，为什么还是慢？ rows 是估算值，真实扫描量可能高一个数量级；用 EXPLAIN ANALYZE 看实际行数与耗时。
FORCE INDEX 能长期用吗？ 不建议，数据分布变化后它会变成新的坑；优先修统计信息或改索引设计。
区分度多低算低？ 单列区分度（不同值/总行数）低于 5% 通常不适合单独建索引，考虑联合索引或放到组合的后续列。
ORDER BY 有索引为什么还有 filesort？ 排序方向与索引不一致（8.0 之前），或 WHERE 与 ORDER BY 用的不是同一条索引。
主从延迟会让查询变慢吗？ 不会让单条 SQL 变慢，但读写分离后读到旧数据、重试与补偿逻辑会拉高整体耗时。

小结

「用了索引还慢」的答案要分层：先证明它真的走了索引（EXPLAIN），再证明它走得划算（rows、rows_examined/rows_sent、Handler_read%），最后排除锁与资源瓶颈。多数事故不是缺索引，而是索引设计与查询写法不匹配。

笔记加载中…