昨晚刚处理完一个线上慢查询报警一个原本 3 毫秒的订单列表查询业务高峰时突然飙到 7 秒直接拖垮了接口。排查后发现是索引失效——看似简单的 SQL就因为一个隐式类型转换把复合索引完全废掉了。这类问题并不罕见。很多团队在开发阶段只关注“有没有索引”却忽略了 MySQL 优化器是否真的用了它。本文用一个真实业务场景订单列表查询串联起完整的排查流程从慢查询日志定位、EXPLAIN 判断方法到索引失效原因、复合索引设计再到上线后的验证与监控。每个环节都会给出可复现的 SQL 示例和关键参数说明希望能让你下次遇到慢查询时能更快地找到根因。从慢查询日志定位问题 SQL 与数据分布首先开启慢查询日志生产环境建议长期开启阈值设 1 秒-- 查看当前设置 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; -- 临时开启需 SUPER 权限 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 单位秒建议 0.1 秒用于测试 SET GLOBAL log_queries_not_using_indexes ON;线上出现报警后登录数据库执行mysqldumpslow分析mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log输出中可以看到类似下面的 SQL实际场景中的订单查询SELECT order_id, order_no, user_id, total_amount, status, create_time FROM orders WHERE pay_time 2024-01-01 AND status 1 AND user_id 12345 ORDER BY create_time DESC LIMIT 10;定位到 SQL 后不要急着加索引先看数据分布。执行SELECT COUNT(*) AS total, COUNT(DISTINCT user_id) AS users, COUNT(DISTINCT status) AS status_cnt, MAX(pay_time), MIN(pay_time) FROM orders;假设输出结果总记录 500 万用户数 100 万status 只有 2 个值0 未支付1 已支付pay_time 跨度两年。这几个数字已经透露出关键信息status 字段的区分度极低500 万条记录只分 2 个值单独对 status 建索引基本没用。而 user_id 区分度约 5 条/用户是很好的过滤条件。EXPLAIN 关键字段type、key、rows、filtered 的判断方法现在对这个慢 SQL 执行EXPLAIN看它是怎么执行的EXPLAIN SELECT order_id, order_no, user_id, total_amount, status, create_time FROM orders WHERE pay_time 2024-01-01 AND status 1 AND user_id 12345 ORDER BY create_time DESC LIMIT 10;输出简化关键列idselect_typetabletypepossible_keyskeyrowsfilteredExtra1SIMPLEordersrangeidx_user_status, idx_pay_time, idx_create_timeidx_pay_time15000010.00Using index condition; Using where; Using filesort逐一分析type range使用了索引的范围扫描比全表扫描好但比 ref 差。这里说明驱动索引是idx_pay_time索引范围条件pay_time 2024-01-01。key idx_pay_time实际用到的索引。rows 150000MySQL 估算需要扫描 15 万行。pay_time 条件过滤后依然有大量记录。filtered 10%表示经过status和user_id条件过滤后只剩 10% 的行实际返回 1.5 万行逻辑不简单filtered 是存储引擎层处理后的估算比例。Extra Using filesort排序使用了文件排序没有用到索引排序。问题判断虽然idx_pay_time被用上了但它不是最有效的访问路径。user_id和status两个条件无法在idx_pay_time中直接完成过滤导致需要回表 文件排序。15 万行的回表都还能接受但加上排序后性能急剧下降排序在内存或临时表中进行LIMIT 10 也无法提前停止因为排序需要全量数据。常见索引失效场景函数、隐式转换、范围查询与低选择性字段3.1 函数导致索引失效我们经常在 WHERE 条件中对索引字段使用函数比如-- 错误写法索引失效 SELECT * FROM orders WHERE DATE(pay_time) 2024-01-15; -- 正确写法用范围查询或虚拟列 SELECT * FROM orders WHERE pay_time 2024-01-15 00:00:00 AND pay_time 2024-01-16 00:00:00;如果一定要对日期做函数运算MySQL 8.0 支持函数索引Generated Column 索引但更推荐改造 SQL。3.2 隐式类型转换这是最常见也是最隐蔽的。我们订单表user_id是 VARCHAR(20)但代码中传了数字// 错误的传参 String sql SELECT * FROM orders WHERE user_id ?; preparedStatement.setLong(1, 12345L); // 触发隐式转换MySQL 在比较时会CAST(user_id AS SIGNED)导致索引失效。查看慢查询日志会发现 type 变为 ALL。解决方法很简单类型匹配。如果字段是字符串传入参数必须也是字符串或者在 ORM 层面上确保参数类型一致。MyBatis 可以通过Param配合jdbcType强制类型。3.3 范围查询 低选择性字段上面的例子已经展示了pay_time范围条件导致扫描大量行。低选择性字段如 status建索引通常弊大于利索引的大小并不小但扫描的比值很高50% 的行优化器经常选择全表扫描而不是使用索引。一个更极端的情况WHERE status IN (0,1)且 status 只有两个值索引大概率被跳过。如果业务必须筛选 status可以结合其他高选择性字段一起做复合索引让 status 放在最后。3.4 OR 条件-- 两个字段各自有索引但 OR 联合可能导致索引合并index merge效率低下 SELECT * FROM orders WHERE user_id 12345 OR status 1;MySQL 5.7 及之后版本可以走index_merge但通常不如改写为 UNION ALLSELECT * FROM orders WHERE user_id 12345 UNION ALL SELECT * FROM orders WHERE status 1 AND user_id ! 12345;复合索引设计最左前缀、排序分页与覆盖索引回到原始问题我们需要的 SQLWHERE pay_time 2024-01-01 AND status 1 AND user_id 12345 ORDER BY create_time DESC LIMIT 10;4.1 最左前缀原则复合索引(a, b, c)可以匹配a、(a,b)、(a,b,c)但跳过中间列会导致后列无法使用。对于我们的查询如果建(user_id, status, pay_time)那么user_id 12345可以走索引status 1也能走等值匹配连续的条件pay_time 2024-01-01只能做 range 过滤且之后的所有列都无法再用于等值过滤。但当前 SQL 中pay_time是范围条件放在最后才能让前面的等值条件充分利用索引。4.2 排序与分页优化如果ORDER BY create_time DESC希望使用索引排序那么索引必须包含排序字段且排序方向要与索引一致或者 MySQL 能反向扫描。最佳索引设计ALTER TABLE orders ADD INDEX idx_query (user_id, status, pay_time, create_time DESC);注意create_time DESC是从 MySQL 8.0 开始支持的降序索引之前版本只能 ASC排序逆向时可能 filesort。这个索引的效果先通过user_id 12345定位到某几个 Bucket精确匹配。status 1继续在桶内精确匹配。pay_time 2024-01-01在桶内进行范围扫描连续位置。由于create_time在索引中已经排序ORDER BY create_time DESC可以直接从索引后面往前读无需 filesort。再加上LIMIT 10只需要读取 10 行然后停止极大地减少了扫描量。4.3 覆盖索引——减少回表查询列是order_id, order_no, user_id, total_amount, status, create_time如果复合索引包含了这些列那么整个查询完全在索引中完成不需要回表。对于 InnoDB覆盖索引可以显著降低磁盘 I/O。-- 建立覆盖索引如果表字段不多 ALTER TABLE orders ADD INDEX idx_cover (user_id, status, pay_time, create_time DESC, order_id, order_no, total_amount);order_id是主键InnoDB 二级索引叶子节点默认包含主键值所以可以省略order_no和total_amount需要显式包含。生产环境中应权衡索引长度避免索引过大例如 VARCHAR 字段太长时可以只包含前几个字符或改用前缀索引。对于大字段可以只将频繁查询的列放入覆盖索引其他列回表。4.4 改进后的 EXPLAIN添加idx_cover后重新 EXPLAINtypekeyrowsfilteredExtrarefidx_cover1100.00Using index; Using index condition; Using where; Using index for group-by?type ref等值匹配rows 1估算扫描 1 行Extra中出现Using index表示覆盖索引。实际性能提升从 7 秒降到 0.5ms。上线验证回归压测、索引维护成本与监控指标5.1 回归压测索引变更后必须对所有查询进行回归测试防止其他 SQL 因索引变化而走错执行计划。推荐使用pt-query-digest对比前后慢查询分布。压测脚本可以用sysbench或自行编写模拟 100 并发循环执行线上核心 SQL。重点关注P99 延时是否下降目标 10msrows_examined平均值是否明显减少performance_schema 可查看5.2 索引维护成本写放大每个索引都会增加 INSERT/UPDATE/DELETE 的代价。一个宽索引比如 7 个字段比 2 个单列索引的写入开销大得多。对于写密集型表要平衡读与写。索引大小使用SHOW TABLE STATUS LIKE orders查看Data_length和Index_length。如果索引大小超过数据本身例如宽表 冗余索引建议清理无用索引。碎片在高并发写入下索引页可能会产生碎片。定期业务低峰执行OPTIMIZE TABLE orders或ALTER TABLE orders ENGINEInnoDB整理。5.3 监控指标MySQL 慢查询日志设置long_query_time 0.1100ms持续收集。performance_schema开启events_statements_summary_by_digest可以查看每个 SQL 模板的平均延时、执行次数、扫描行数。Prometheus mysqld_exporter监控mysql_global_status_innodb_rows_read和mysql_global_variables_handler_commit如果发现 rows_read 突增而 QPS 未变可能是索引失效。业务监控在 APM 中标记该接口的数据库调用耗时设置告警阈值如 200ms。总结索引失效排查与设计要点把整篇文章的精华提炼成一张检查清单方便你下次遇到慢查询时快速对照定位问题 SQL开启慢查询日志long_query_time1用mysqldumpslow或pt-query-digest找到耗时最长的查询。分析数据分布COUNT(DISTINCT)检查字段区分度低选择性字段如 status不要单独建索引。EXPLAIN 必看四要素type理想为ref或const、key是否用了预期索引、rows扫描行数是否合理、Extra警惕Using filesort和Using temporary。检查常见索引失效- 函数操作DATE()、LEFT()等→ 改用范围查询或函数索引。- 隐式类型转换字符串 vs 数字→ 保证参数类型与字段类型一致。- 范围查询放错位置 → 等值条件放左边范围条件放右边。- OR 条件 → 改写成 UNION ALL。设计复合索引- 最左前缀等值条件优先范围条件靠后。- 排序字段纳入索引MySQL 8.0 支持降序索引。- 考虑覆盖索引减少回表但注意索引长度。上线前验证回归压测所有 SQL关注 P99 延时和rows_examined。持续监控慢查询日志 performance_schema 业务 APM 告警形成闭环。索引失效不是玄学根源通常是WHERE 条件中对索引列使用了函数/隐式转换、范围查询放错了位置、排序字段没有纳入索引。排查时先看慢查询日志锁定 SQL然后用 EXPLAIN 盯着type、rows和Extra中的Using filesort或Using index condition复合索引设计遵循“最左前缀 等值在前范围在后 覆盖排序列”的原则同时注意低选择性字段的取舍。最后记得把索引变更当作一次完整的发布压测通过后再上线并持续监控一周。如果我的经验能帮你少踩一个坑这篇文章就没白写。