MySQL 查詢效能優化基礎教程：緩存、索引、排序與不可達查詢

查詢是資料庫最核心操作，雖然流程簡單（Client → SQL → Server → Result），但不同寫法與配置對效能影響巨大。本文系統講解四大優化維度：查詢緩存、優化器自動決策、索引排序技巧、不可達查詢檢測。

一、查詢緩存：以空間換時間

原理與適用場景

MySQL 4.0.1+ 內建 Query Cache，將完全相同的 SELECT 文本（區分大小寫、空格）與結果集對應緩存。適用於資料變動頻率低、重複查詢比例高、可容忍秒級延遲的場景（如配置表、字典表、報表快照）。

配置啟用

在 my.cnf / my.ini 中：

[mysqld]
query_cache_type = 1
query_cache_size = 64M

• query_cache_type=1：啟用（預設 OFF）。
• query_cache_size：分配記憶體，建議 64M~256M，過大反而增加鎖爭用。

使用細節與陷阱

• 區分大小寫與空格：SELECT * FROM t 與 select * from t 視為不同查詢；首尾空格不自動修剪。
• 強制繞過緩存：SELECT SQL_NO_CACHE * FROM t（即時資料需求）。
• 按需啟用：設 query_cache_type=2，僅 SELECT SQL_CACHE * FROM t 才緩存。
• 失效機制：表任意寫入（INSERT/UPDATE/DELETE/TRUNCATE）即失效該表所有緩存，高寫入場景反而拖慢效能。

二、優化器自動決策：索引不總是最快

成本模型：掃描比例閾值

優化器根據「符合條件行數 / 總行數」比例決定是否使用索引。經驗閾值約 **30%**（視引擎而異）。若預估掃描超過 30%，優化器傾向全表掃描（Sequential Scan）而非索引掃描+回表。

範例：SALES 表百萬行

SELECT AVG(PRICE) FROM SALES
WHERE ID = 1000 AND SALE_DATE BETWEEN '2004-01-01' AND '2005-12-31';

若 ID=1000 佔 50% 行數，使用 SALE_DATE 索引需大量回表排序，優化器會選擇全表掃描直接聚合。

實務啟示

• 避免在低選擇性欄位（性別、狀態碼）建立單列索引。
• 複合索引將高選擇性欄位置前（如 INDEX (ID, SALE_DATE)）。
• 使用 EXPLAIN 觀察 type=ALL（全表掃描）vs type=ref/range（索引掃描）。

三、基於索引的排序：避開 Filesort

單索引限制

MySQL 單次查詢**最多使用一個索引**。若 WHERE 已用索引，ORDER BY 無法再用索引，觸發 Using filesort（記憶體/磁臨時表排序），大幅降速。

解決方案：覆蓋索引 / 複合索引

ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME, SALE_DATE);

查詢：SELECT * FROM SALES WHERE NAME='xxx' ORDER BY SALE_DATE DESC → 複合索引同時滿足 WHERE 篩選與 ORDER BY 排序，實現 Using index。

注意事項

• WHERE 中不可包含排序欄位（否則複合索引首欄不匹配，索引失效）。
• ORDER BY 欄位順序與複合索引定義順序一致（或完全反向），否則仍需 filesort。

四、不可達查詢檢測：短路無效執行

什麼是不可達查詢

WHERE 條件邏輯恆為假，無論資料如何變化都無結果。例如：

• WHERE value < 100 AND value > 200
• WHERE name='A' AND name='B'

MySQL 自動優化

執行前解析 WHERE，偵測到恆假條件直接返回空結果，**不存取任何資料表**（EXPLAIN 顯示 table: NULL, Extra: Impossible WHERE）。

驗證範例

EXPLAIN SELECT * FROM SALES WHERE NAME='name1' AND NAME='name2';

輸出 table: NULL 證明未掃描表。

總結優化檢查清單

持續監控 Slow Query Log、Performance Schema，配合定期 ANALYZE TABLE 更新統計資訊，讓優化器決策更精準。