一、問題背景#
某線上接口響應時間高達 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 優化之道。