MySQL 查詢優化實戰：索引設計、SQL 改寫與架構級加速策略

為什麼查詢優化至關重要？

數據庫是信息系統核心，SELECT 語句佔比最高且代價最大。百萬級數據全表掃描耗時數分鐘至數小時，優秀的查詢計劃可將耗時壓縮至秒級。優化器雖智能，但SQL 編寫質量是優化上限——糟糕的 SQL 無法靠優化器變高效。

優化器工作流程

1. 詞法/語法解析
2. 查詢優化器：代數優化 + 存取路徑優化（基於成本模型）
3. 預編譯生成執行計劃
4. 執行並返回結果

用戶 SQL 是優化器的輸入原料，編寫高效 SQL 是開發者核心責任。

七大實戰優化策略

1. 索引設計黃金法則

• 建立索引：外鍵關聯列、頻繁 WHERE/JOIN/ORDER BY/GROUP BY 列、高選擇性列（唯一值多）。
• 避免索引：低選擇性列（如性別、狀態枚舉）、極少查詢的列、頻繁大批量寫入的表。
• 複合索引：遵循最左前綴原則，將選擇性最高的列置左；覆蓋索引可避免回表。
• 維護：定期 ANALYZE TABLE 更新統計信息；大量寫入後 OPTIMIZE TABLE 或重建索引消除碎片。

2. 消除不必要的排序

• 利用索引順序滿足 ORDER BY/GROUP BY，避免 Filesort。
• 複合索引列順序需與排序列順序一致，方向一致（均 ASC 或均 DESC）。
• 跨表排序無法使用索引，考慮應用層合併排序或物化視圖。

3. 避免大表順序掃描

• 關聯查詢**必須在 JOIN 列上建立索引**
• 將 OR 條件拆分為 UNION（各分支可用索引），例如：
  SELECT * FROM orders WHERE (cust=104 AND ord>1001) OR ord=1008
  →
  SELECT * FROM orders WHERE cust=104 AND ord>1001 UNION SELECT * FROM orders WHERE ord=1008

4. 警惕相關子查詢

• 外層每行觸發內層查詢，複雜度 O(N×M)。
• 改寫為 JOIN、EXISTS、IN 或應用層分步查詢。
• 若必須用子查詢，盡量在子查詢中過濾掉大部分行（LIMIT、高選擇性 WHERE）。

5. 正則與模糊查詢優化

• LIKE 'prefix%' 可用索引；LIKE '%suffix'、LIKE '%mid%' 全表掃描。
• 前綴查詢改為範圍查詢：zipcode LIKE '98___' → zipcode BETWEEN '98000' AND '98999'。
• 非起始子串搜尋（SUBSTRING(col,2,3)）不可用索引，考慮全文索引或 Elasticsearch。

6. 臨時表/物化視圖加速複雜報表

-- 將高頻複雜查詢結果物化
CREATE TEMPORARY TABLE tmp_cust_balance AS
SELECT c.name, r.balance, ...
FROM cust c JOIN rcvbles r ON c.id=r.cust_id
WHERE r.balance>0 AND c.postcode>'98000'
ORDER BY c.name;

-- 後續多維分析直接查詢臨時表
SELECT * FROM tmp_cust_balance WHERE region='East';

注意：臨時表不自動同步主表變更，高寫入場景需定時重建或改用物化視圖（MySQL 8.0+ 支援）。

7. 用排序替代隨機 I/O

隨機磁盤讀取（磁頭跳躍）極慢。將大量隨機點查改為批量排序後順序讀，可將 I/O 降低數量級。典型場景：大批量 ID 查詢 → 先排序 ID → 單次範圍掃描。

EXPLAIN 實戰讀懂執行計劃

EXPLAIN SELECT * FROM orders WHERE customer_id=1001 AND amount>500;

架構級加速手段

• 讀寫分離：主庫寫、從庫讀，分攤壓力。
• 分庫分表：水平拆分降低單表數據量，配合中間件（ShardingSphere、Vitess）。
• 緩存層：Redis 緩存熱點查詢結果，查詢前先查緩存。
• 異步處理：非即時一致性需求（如統計報表）寫入消息隊列，消費者異步更新。
• 搜尋引擎：全文/模糊/多維篩選遷移至 Elasticsearch/OpenSearch。

總結

MySQL 查詢優化是系統工程：索引設計是基石，SQL 改寫是手段，架構演進是終局。建立 EXPLAIN 審查機制，將慢查詢日誌（long_query_time=1）納入日常巡檢，持續迭代優化，才能支撐業務高速增長。