MySQL 查詢性能優化基礎：查詢緩存、自動優化、索引排序與不可達查詢檢測

MySQL 查詢性能優化四大基石

一、查詢緩存

MySQL 4.0.1+ 支援。開啟：my.ini 設 query_cache_type=1、query_cache_size。

• 完全相同 SQL（區分大小寫、空格）命中緩存，直接返回結果。
• 臨時禁用：SELECT SQL_NO_CACHE ...。
• 按需啟用：query_cache_type=2 + SELECT SQL_CACHE ...。

注意：緩存不自動處理大小寫/空格，SQL 寫法必須統一。

二、MySQL 自動優化：索引選擇邏輯

當滿足條件行數超過表總行數 ~30%（經驗值），優化器放棄索引改走全表掃描，因為索引回表+排序成本高於順序掃描。具體閾值隨存儲引擎不同而異。

三、基於索引的排序優化

MySQL 單表查詢最多使用一個索引。若 WHERE 已用索引，ORDER BY 無法再用索引排序 → filesort 慢。

解決：建立複合索引覆蓋 WHERE + ORDER BY 列。

ALTER TABLE sales DROP INDEX name, ADD INDEX (name, sale_date);
-- 查詢：WHERE name='x' ORDER BY sale_date DESC 即可用索引排序

前提：WHERE 不能包含排序列，否則排序列無單獨索引導致查詢變慢。

四、不可達查詢檢測

MySQL 執行前分析 WHERE 條件，若邏輯永假（如 value < 100 AND value > 200 或 name='a' AND name='b'），直接返回空結果，不掃描表。

驗證：EXPLAIN SELECT ... 中 table 顯示 NULL 或 Impossible WHERE。

總結建議

1. 讀多寫少、重複查詢多 → 開啟查詢緩存，統一 SQL 風格。
2. 大範圍查詢預期走全表 → 別強行加索引，信任優化器。
3. WHERE + ORDER BY 高頻 → 設計複合索引。
4. 業務邏輯避免互斥條件，或交由 MySQL 自動優化。