一、查詢緩存

開啟與配置

[mysqld]
query_cache_type = 1        # 1=開啟, 0=關閉, 2=按需 (DEMAND)
query_cache_size = 256M     # 緩存總大小
query_cache_limit = 2M      # 單查詢結果上限

版本警告：MySQL 5.7.20 开始標記棄用，8.0 **完全移除**。生產環境建議改用 **Redis/ProxySQL** 等外部快取層。

使用細節與陷阱

• 大小寫敏感：SELECT * FROM t ≠ select * from t
• 空格敏感：首尾/多餘空格導致緩存未命中
• 非確定性函數不可緩存：NOW()、RAND()、UUID()、CONNECTION_ID()
• 表異動即失效：任何 INSERT/UPDATE/DELETE 導致相關表所有緩存失效 (粒度粗，寫入頻繁表緩存命中率極低)

手動控制

-- 強制不使用緩存
SELECT SQL_NO_CACHE * FROM huge_table WHERE ...;

-- 僅特定查詢使用 (query_cache_type=2 時)
SELECT SQL_CACHE * FROM lookup_table WHERE ...;

二、MySQL 自動優化：索引選擇與「30% 閾值」

優化器估算「掃描行數 / 總行數」> **~30%** 時，傾向**放棄索引改走全表掃描**，因為隨機 IO 成本超過順序掃描。

-- 例：SALES 表 100 萬行，滿足條件 60 萬行
SELECT AVG(price) FROM sales WHERE sale_date BETWEEN '2004-01-01' AND '2005-12-31';
-- 優化器可能選擇全表掃描而非 sale_date 索引

**應對**：強制索引 FORCE INDEX (idx_sale_date)、調整統計資訊 ANALYZE TABLE、或改寫查詢邏輯。

三、基於索引的排序優化

鐵律：MySQL 單查詢**最多使用一個索引** (5.6+ 引入 Index Merge 但受限)。

-- 問題：WHERE 用 name 索引，ORDER BY sale_date 無索引 → filesort
SELECT * FROM sales WHERE name='John' ORDER BY sale_date DESC;

-- 解法：複合索引覆蓋 WHERE + ORDER BY
ALTER TABLE sales DROP INDEX idx_name, ADD INDEX idx_name_saledate (name, sale_date);

-- 注意：WHERE 不能包含 sale_date，否則索引順序錯位

四、不可達查詢自動偵測

EXPLAIN SELECT * FROM sales WHERE name='A' AND name='B';
-- type: NULL, Extra: Impossible WHERE
-- 優化器直接判定無結果，table=NULL，零掃描

其他觸發場景：WHERE 1=0、WHERE id < 10 AND id > 20、WHERE NOT (col IS NULL OR col IS NOT NULL)。

五、綜合優化清單

1. EXPLAIN 先行：每條上線 SQL 必過 EXPLAIN
2. 覆蓋索引：SELECT 欄位 ⊆ 索引欄位，Extra 顯示 Using index
3. 避免 SELECT *：減少網路/記憶體/回表開銷
4. 範圍列獨立：WHERE date >= '2024-01-01' AND date < '2024-02-01' 而非函數包裹
5. JOIN 小表驅動大表：驅動表 (外層) 行數少，被驅動表有索引
6. LIMIT 分頁優化：延遲關聯/書籤分頁 (見文章 72485)
7. 定期 ANALYZE TABLE：更新統計資訊，助優化器決策
8. 慢查詢日誌：long_query_time=1、log_queries_not_using_indexes=ON，配合 pt-query-digest 分析