慢查询治理的五个步骤与陷阱：从 EXPLAIN 到索引与改写

现象与背景：一次慢查询治理的实战

在我们团队接手的某日志审计系统中，核心表 t_audit_log 规模达到了 4800 万行、单表 62GB、InnoDB、MySQL 8.0.32，部署在 16C64G、NVMe 本地盘的服务器上。业务方反馈该表的某高频查询 P99 超过 4.6s，严重影响系统性能。

原理与根因分析

这条查询 SQL 的关键问题是 索引选择不当 和 SQL 写法。通过 EXPLAIN 分析发现，查询实际走的是全表扫描，而 EXPLAIN ANALYZE 进一步确认了其真实耗时与扫描行数。我们通过以下步骤定位问题：

1. 定位慢 SQL：使用 EXPLAIN 和慢日志分析，确认 SQL 的执行计划和实际扫描行数。
2. 判断问题根源：发现该查询中存在对索引列的函数运算和隐式类型转换，导致索引失效。
3. 分析索引选择性：评估索引列的选择性，发现 status 字段的基数很低（只有 0/1），加索引意义不大。

具体做法：索引优化与 SQL 改写

我们采用了以下措施进行优化：

1. 索引优化

• 加索引：在 (user_id, status, create_time) 上加联合索引，提高查询效率。
• 覆盖索引：确保查询所需的列都包含在索引中，避免回表。

2. SQL 改写

• 避免函数运算：将 WHERE DATE(create_time) = '2025-01-01' 改为 create_time >= '2025-01-01' AND create_time < '2025-01-02'。
• 避免隐式类型转换：将 phone 字段的类型调整为与查询条件匹配。

踩过的坑或反直觉之处

1. 覆盖索引的代价：虽然覆盖索引可以减少回表，但会增加索引的大小，影响缓冲池效率。
2. 索引顺序的影响：联合索引的顺序对查询性能有显著影响，需要合理设计。
3. 统计信息过期：如果统计信息过期，会导致 EXPLAIN 的估算值与实际值偏差较大，需定期更新统计信息。

结论与可复用清单

1. 定位慢 SQL：使用 EXPLAIN 和慢日志分析，确认 SQL 的执行计划和实际扫描行数。
2. 判断问题根源：检查 SQL 写法，避免函数运算和隐式类型转换。
3. 分析索引选择性：评估索引列的选择性，选择合适的索引。
4. 优化索引和 SQL：合理设计联合索引，避免覆盖索引的副作用。
5. 监控与维护：定期更新统计信息，监控查询性能变化。

通过以上步骤，我们成功将该查询的 P99 从 4.6s 降低到 720ms，显著提升了系统性能。