MySQL 系統優化實戰：從慢查詢分析到索引重構、分表與分庫策略

優化工具箱：常用診斷命令

案例實戰：TSK_TASK 表慢查詢優化

表結構

• ID：主鍵
• MON_TIME：有索引 TSK_TASK_KEY_MON_TIME
• STATUS_ID：外鍵關聯 SYS_HIER_INFO.ID，MySQL 自動建立索引 FK_task_status_id_TO_SYS_HIER_INFO

問題 SQL

SELECT * FROM TSK_TASK WHERE STATUS_ID = 1064 AND MON_TIME >= '2007-11-22' AND MON_TIME < '2007-11-23';
-- 掃描 88143 行，耗時 18 秒

EXPLAIN 分析

possible_keys: FK_task_status_id_TO_SYS_HIER_INFO, TSK_TASK_KEY_MON_TIME
key: FK_task_status_id_TO_SYS_HIER_INFO  -- 選擇了外鍵索引
rows: 276168

根因：Cardinality 誤導優化器

MySQL 傾向選擇 Cardinality 較小的索引（以為更精準），但實際導致大量回表掃描。調整 WHERE 字段順序無效，因優化器不看順序看統計信息。

優化方案

1. 單日任務量少：刪除外鍵索引 FK_...，強制走 MON_TIME 索引。
2. 單日任務量大：刪除兩個單列索引，建立聯合索引 (STATUS_ID, MON_TIME)。
3. 建議：大表慎用外鍵，避免自動索引干擾優化器選擇。

架構層面優化策略

1. 控制單表數據量

大表索引維護耗時長，影響業務。按時間劃分實時表與歷史表，後台定期遷移數據，保證遷移時間極短，防死鎖。

2. 數據分片策略

客戶量達規模時，單庫無法支撐高並發。按客戶哈希/範圍將數據分散到多個數據庫，分擔負載，提升整體吞吐。

總結

MySQL 優化是「工具診斷 → 索引重構 → 架構演進」的系統工程。慢查詢日誌是起點，EXPLAIN 是放大鏡，Cardinality 理解是關鍵，大表拆分是出路。