MySQL查询性能慢时索引失效的排查与优化实践-EW帮帮网

MySQL查询性能慢时索引失效的排查与优化实践

在高并发和大数据量的生产环境中，MySQL的查询性能至关重要。本文围绕“索引失效”这一常见问题展开，结合真实业务场景，从问题现象、定位过程、根因分析、优化改进到预防监控，带你深入排查并彻底解决索引失效引发的性能瓶颈。

一、问题现象描述

响应时间突增：某关键查询的平均响应时间由 < 50ms 突然飙升至 500ms~2s。
连接数激增：慢查询堆积导致数据库连接数持续上升，甚至出现连接超时。
CPU/IO突然飙高：结合监控，发现 MySQL 进程的 CPU 利用率或 IO 等待明显提升。
业务链路阻塞：依赖该查询的请求出现排队，业务整体吞吐下降。

这些都是典型的索引失效引起的性能下降现象。

二、问题定位过程

1. 开启慢查询日志

在 my.cnf 中配置：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1    # 记录超过1秒的查询
log_queries_not_using_indexes = 1   # 记录未使用索引的查询

重启后，复现业务，收集慢查询日志。

2. 使用 `EXPLAIN` 分析执行计划

EXPLAIN FORMAT=JSON
SELECT *
FROM orders
WHERE user_id = 123 AND status = 'PENDING';

通过输出，重点关注：

"type" 字段：ALL/NOSCAN 表示全表扫描或索引失效。
"key" 字段：显示实际使用的索引；NULL 表示未使用索引。
"rows"：扫描行数巨大时往往意味着全表扫描。

3. 监控视图查询

-- 当前正在执行的查询及其状态
SELECT * FROM information_schema.PROCESSLIST
WHERE COMMAND = 'Query';

-- 索引统计信息
SHOW INDEX FROM orders;

通过上述步骤，可以快速定位哪些 SQL 未走索引或全表扫描。

三、根因分析与解决

场景1：范围查询导致索引失效

SELECT * FROM orders
WHERE user_id = 123
  AND created_at > '2023-01-01';

如果在 (user_id, created_at) 的联合索引上，MySQL 可以使用前缀索引；但

WHERE created_at > '2023-01-01'
  AND user_id = 123;

顺序颠倒可能导致只命中 created_at 单列索引，或在某些版本下索引失效。

解决：保证 WHERE 中字段顺序与索引列顺序一致；必要时拆分查询。

场景2：前缀模糊匹配

WHERE username LIKE '%john%'

以上写法无法利用 B-tree 索引。

解决：使用倒排索引（如 Elasticsearch），或避免前缀通配符，改为 john%。

场景3：函数/隐式类型转换

WHERE DATE(created_at) = '2023-07-10'

DATE() 会对 created_at 列做全表函数扫描。

解决：使用范围查询：

WHERE created_at >= '2023-07-10 00:00:00'
  AND created_at < '2023-07-11 00:00:00'

或为 DATE(created_at) 创建函数索引（MySQL 8.0+）。

场景4：列顺序与索引不匹配

对于复合索引 (a,b,c)，查询只使用了 (c,b) 的顺序，会导致索引失效。

解决：根据实际查询场景拆分或重建索引，保证常用查询字段顺序一致。

场景5：数据倾斜与索引选择不当

当 status 取值极度不均衡（如 99% 为 'DONE'），WHERE status='DONE' 虽有索引，但效果不显著。执行计划可能选择全表扫描。

解决：考虑字段基数，避免为高度倾斜字段单独建立索引，或使用覆盖索引（覆盖查询所需字段）。

四、优化改进措施

合理拆分索引与覆盖索引

对于频繁查询字段，创建覆盖索引，例如：

CREATE INDEX idx_user_status ON orders(user_id, status, created_at);

EXPLAIN 时看到 Using index condition 则说明走了覆盖索引，无需回表。

建立监控告警

结合 pt-query-digest 定期分析慢查询日志。
利用 PMM（Percona Monitoring and Management）监控索引使用率和查询吞吐。

定期整理/重建索引

大表可使用在线 DDL：

ALTER TABLE orders
  DROP INDEX idx_old,
  ADD INDEX idx_new(user_id, status, created_at)
  LOCK=NONE;

避免索引碎片。

查询参数化和预编译

使用 PreparedStatement 避免 SQL 拼接导致执行计划不命中缓存。

归档与分表分库

对历史冷数据做归档操作，减小单表大小。
对业务热点分库分表，进一步提升查询性能。

五、预防措施与监控

建立 SQL 规范审查机制

新增或改动 SQL 前进行 EXPLAIN 审核。

自动化测试

在 CI/CD 流程中加入慢查询联调检测，对索引失效提前报警。

定期培训与分享

建立经验分享白皮书，宣贯索引原理与查询优化。

健康检查脚本

周期执行脚本，统计未使用的索引、低效索引和高瓶颈 SQL。

通过以上系统化的索引失效排查与优化方案，能够帮助后端开发者在生产环境中快速发现性能瓶颈，精准定位根因并实施改进，最终保障 MySQL 查询的高效可靠。

MySQL查询性能慢时索引失效的排查与优化实践