一、问题背景
某线上接口响应时间高达 50秒以上，频繁超时，严重影响用户体验。测试环境却运行流畅，引发了排查行动。
二、初步排查过程
✅ 1. 链路追踪识别慢接口
通过链路追踪系统（如 SkyWalking）快速定位到是某数据库查询拖慢了接口响应。
✅ 2. 日志 + SQL 拦截器打印完整 SQL
结合 MyBatis 的 SQL 拦截器，拿到完整参数拼接后的 SQL：
SELECT MAX(t.id)
FROM user_log t
WHERE t.deleted_at = '1970-01-01 00:00:00'
AND LENGTH(t.platform_id) > 0
AND t.platform_id != '0'
AND t.platform_type = 'MOBILE'
AND t.nickname LIKE CONCAT('%', 'abc', '%')
GROUP BY t.platform_id
LIMIT 20;
✅ 3. 执行计划分析（EXPLAIN）
发现生产环境错误命中了一个 deleted_at 开头的索引：
idx_deletedat_memberaccountid_updatedat
→ 扫描行数：2,126,736
→ filesort + 临时表
→ 总耗时：50秒
而测试环境使用了更合适的索引 idx_platformtype_deletedat_platformid_nickname，执行仅需数百毫秒。
三、问题根因
优化器在生产环境中误判了最优索引，导致全表扫描 + 临时表排序。
原因如下：
| 条件字段 | 测试环境匹配量级 | 生产环境匹配量级 |
|---|---|---|
deleted_at | 千级（约 5,000） | 百万级（约 2,100,000） |
platform_type | 十万级（约 700,000） | 百万级（约 1,250,000） |
💡推断结果：生产环境中 deleted_at 匹配数据过多，导致优化器错误判断为过滤性强字段，选择了 sub-optimal 的索引。
四、最终优化方案：组合两种策略
✅ 方案一：条件性使用 USE INDEX
MyBatis XML 中动态注入：
<if test="ro.platformType != null and ro.partialNickname != null and ro.talentPlatformIdList == null">
USE INDEX (idx_platformtype_deletedat_platformid_nickname)
</if>
作用：
• 避免在昵称列表查询、没有 platform_id 精确范围时优化器选错；
• 限制性注入，确保只在特定组合下才强制索引，避免一刀切。
✅ 方案二：范围优化替换函数过滤
替换原本的冗余判断逻辑：
-- 原来
AND LENGTH(t.platform_id) > 0 AND t.platform_id != '0'
-- 优化后
AND t.platform_id > '0'
优势：
- length和 != 会用不上索引，直接扫表，改为 > 可以使用索引进行查询
- 避免函数对字段的干扰，提升索引使用概率；
- 简洁高效，语义等效。
五、优化效果
| 环境 | 优化前 | 优化后 | 提升倍数 |
|---|---|---|---|
| 生产环境 | ~50 秒 | ~0.9 秒 | 55x |
六、后续优化
以下是在当前方案基础上，总结出的更进一步的可选优化方向，适合后续演进考虑，目前先不继续优化，因为下面的ROI太低。
- ✅ 使用全文索引优化模糊搜索
- 当前 LIKE '%xxx%' 查询无法使用 B-Tree 索引。
- 可使用 FULLTEXT 索引（适用于 InnoDB 中 VARCHAR 或 TEXT 字段），提高模糊搜索性能：
ALTER TABLE some_table ADD FULLTEXT INDEX idx_nickname_ft (nickname);
- 查询改写为：
SELECT ... FROM some_table
WHERE MATCH(nickname) AGAINST('+abc' IN BOOLEAN MODE);
- ✅ SQL 拆分处理 OR 条件
- 原查询中存在：
WHERE platform_id IN (...) OR home_link IN (...)
- MySQL 遇到 OR 时，通常无法同时利用两个索引，容易触发全表扫描。
- 推荐将 SQL 拆成两条子查询后 UNION ALL：
(SELECT ... FROM ... WHERE platform_id IN (...))
UNION ALL
(SELECT ... FROM ... WHERE home_link IN (...))
- ✅ 设计多组复合索引应对不同参数组合
- 针对不同查询场景（如是否含 platform_type、是否模糊搜索、是否有 notification_id），分别设计专用复合索引；
- 并用 MyBatis 条件 + USE INDEX 动态控制路径，确保始终走最优执行计划。
- ✅ 通过直方图提升优化器对数据分布的理解（MySQL 8+）
- 若 ANALYZE TABLE 仍不足以准确反映某字段基数分布，可考虑使用：
ANALYZE TABLE some_table UPDATE HISTOGRAM ON platform_type WITH 256 BUCKETS;
- 提升优化器对索引代价评估的准确性。
七、总结与启示
🎯 优化关键词：
• USE INDEX 条件控制
• 函数过滤改范围判断
• 分析数据分布差异
• 关注优化器行为
• 精准条件下强引导，避免全局强绑
🧠 经验总结：
| 建议 | 说明 |
|---|---|
| 优化前务必 EXPLAIN 对比 | 理解生产 vs 测试为何差异巨大 |
| 慎用函数包裹索引字段 | 函数过滤常导致索引失效 |
| 条件性 USE INDEX 而非全局固定 | 提高兼容性与鲁棒性 |
| 字段分布决定优化器行为 | 不同环境需分别调优 |
⸻
一次错误的索引选择，能将查询放大 50 倍耗时；一次精细的微调组合，就能让系统重获新生。
面对优化器，别“全信”，也别“硬刚”，用更细致的策略去协同它，才是高阶的 SQL 优化之道。
