MySQL 慢查询排查实录：一次优化器误判引发的性能危机

一、问题背景

某线上接口响应时间高达 50秒以上，频繁超时，严重影响用户体验。测试环境却运行流畅，引发了排查行动。

二、初步排查过程

✅ 1. 链路追踪识别慢接口

通过链路追踪系统（如 SkyWalking）快速定位到是某数据库查询拖慢了接口响应。

✅ 2. 日志 + SQL 拦截器打印完整 SQL

结合 MyBatis 的 SQL 拦截器，拿到完整参数拼接后的 SQL：

1
SELECT MAX(t.id)
2
FROM user_log t
3
WHERE t.deleted_at = '1970-01-01 00:00:00'
4
  AND LENGTH(t.platform_id) > 0
5
  AND t.platform_id != '0'
6
  AND t.platform_type = 'MOBILE'
7
  AND t.nickname LIKE CONCAT('%', 'abc', '%')
8
GROUP BY t.platform_id
9
LIMIT 20;

✅ 3. 执行计划分析（EXPLAIN）

发现生产环境错误命中了一个 deleted_at 开头的索引：

1
idx_deletedat_memberaccountid_updatedat
2
→ 扫描行数：2,126,736
3
→ filesort + 临时表
4
→ 总耗时：50秒

而测试环境使用了更合适的索引 idx_platformtype_deletedat_platformid_nickname，执行仅需数百毫秒。

三、问题根因

优化器在生产环境中误判了最优索引，导致全表扫描 + 临时表排序。

原因如下：

💡推断结果：生产环境中 deleted_at 匹配数据过多，导致优化器错误判断为过滤性强字段，选择了 sub-optimal 的索引。

四、最终优化方案：组合两种策略

✅ 方案一：条件性使用 USE INDEX

MyBatis XML 中动态注入：

1
<if test="ro.platformType != null and ro.partialNickname != null and ro.talentPlatformIdList == null">
2
    USE INDEX (idx_platformtype_deletedat_platformid_nickname)
3
</if>

作用： • 避免在昵称列表查询、没有 platform_id 精确范围时优化器选错； • 限制性注入，确保只在特定组合下才强制索引，避免一刀切。

✅ 方案二：范围优化替换函数过滤

替换原本的冗余判断逻辑：

— 原来

1
AND LENGTH(t.platform_id) > 0 AND t.platform_id != '0'

— 优化后

1
AND t.platform_id > '0'

优势：

• length和 != 会用不上索引，直接扫表，改为 > 可以使用索引进行查询
• 避免函数对字段的干扰，提升索引使用概率；
• 简洁高效，语义等效。

五、优化效果

六、后续优化

以下是在当前方案基础上，总结出的更进一步的可选优化方向，适合后续演进考虑，目前先不继续优化，因为下面的ROI太低。

1. ✅ 使用全文索引优化模糊搜索

• 当前 LIKE ‘%xxx%’ 查询无法使用 B-Tree 索引。
• 可使用 FULLTEXT 索引（适用于 InnoDB 中 VARCHAR 或 TEXT 字段），提高模糊搜索性能：

1
ALTER TABLE some_table ADD FULLTEXT INDEX idx_nickname_ft (nickname);

• 查询改写为：

1
SELECT ... FROM some_table
2
WHERE MATCH(nickname) AGAINST('+abc' IN BOOLEAN MODE);

2. ✅ SQL 拆分处理 OR 条件

• 原查询中存在：

1
WHERE platform_id IN (...) OR home_link IN (...)

• MySQL 遇到 OR 时，通常无法同时利用两个索引，容易触发全表扫描。
• 推荐将 SQL 拆成两条子查询后 UNION ALL：

1
(SELECT ... FROM ... WHERE platform_id IN (...))
2
UNION ALL
3
(SELECT ... FROM ... WHERE home_link IN (...))

3. ✅ 设计多组复合索引应对不同参数组合

• 针对不同查询场景（如是否含 platform_type、是否模糊搜索、是否有 notification_id），分别设计专用复合索引；
• 并用 MyBatis 条件 + USE INDEX 动态控制路径，确保始终走最优执行计划。

4. ✅ 通过直方图提升优化器对数据分布的理解（MySQL 8+）

• 若 ANALYZE TABLE 仍不足以准确反映某字段基数分布，可考虑使用：

1
ANALYZE TABLE some_table UPDATE HISTOGRAM ON platform_type WITH 256 BUCKETS;

• 提升优化器对索引代价评估的准确性。

七、总结与启示

🎯 优化关键词： • USE INDEX 条件控制 • 函数过滤改范围判断 • 分析数据分布差异 • 关注优化器行为 • 精准条件下强引导，避免全局强绑

🧠 经验总结：

⸻

一次错误的索引选择，能将查询放大 50 倍耗时；一次精细的微调组合，就能让系统重获新生。

面对优化器，别“全信”，也别“硬刚”，用更细致的策略去协同它，才是高阶的 SQL 优化之道。