MySQL 效能優化 36 計：索引、分頁、Join、計數與資料型別避坑指南

一、索引篇

1. 檢查執行計劃：EXPLAIN 關注 type=ALL (全表掃描) 和 key=NULL (未用索引)。
2. 避免索引列運算：WHERE DATE(created_at) = '2024-01-01' → 改為 WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'。
3. 拒絕前導萬用字元：LIKE '%keyword%' 無法用索引，改用全文檢索或外部搜尋引擎。
4. 複合索引最左前綴原則：索引 (a,b,c)，查詢必須包含 a 才能用到索引；WHERE b=1 失效，WHERE a=1 AND c=3 僅用到 a。
5. 覆蓋索引：SELECT a,b FROM t WHERE c=1 若索引為 (c,a,b)，可直接從索引返回，避免回表。
6. COUNT(*) vs COUNT(col)：COUNT(*) 由優化器選擇最小索引掃描；COUNT(col) 需檢查非空，若無索引則全表掃描。統一用 COUNT(*)。
7. 存在性檢查用 LIMIT 1：SELECT 1 FROM t WHERE id=123 LIMIT 1 比 COUNT(*) 快 (命中即停)。

二、高效分頁

深分頁問題

LIMIT 100000, 20 需掃描 10 萬行再丟棄，極慢。

優化方案：延遲關聯

-- 原始慢查詢
SELECT * FROM orders WHERE user_id=100 ORDER BY created_at DESC LIMIT 100000, 20;

-- 優化：先查主鍵，再回表
SELECT o.* FROM orders o
JOIN (
    SELECT id FROM orders WHERE user_id=100 ORDER BY created_at DESC LIMIT 100000, 20
) tmp USING (id);

游標分頁 (適合無限滾動)

-- 客戶端記住上一頁最後一筆的 created_at + id
SELECT * FROM orders
WHERE user_id=100
  AND (created_at, id) < ('2024-01-01 12:00:00', 9999)
ORDER BY created_at DESC, id DESC
LIMIT 20;

三、ORDER BY 索引優化

• 排序列需符合索引順序，且方向一致 (全 ASC 或全 DESC)，混合方向 ORDER BY a ASC, b DESC 會導致 filesort。
• WHERE 條件列 + ORDER BY 列 組成複合索引可同時過濾與排序。

四、隨機查詢優化

-- 極慢：全表排序
SELECT * FROM t WHERE type=1 ORDER BY RAND() LIMIT 1;

-- 改進：主鍵範圍隨機
SELECT * FROM t WHERE type=1 AND id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM t WHERE type=1))) LIMIT 1;

前提：主鍵為連續自增整數，刪除較少。

五、Join 優化

1. 小表驅動大表：驅動表 (外層循環) 應為結果集較小者。
2. 消除子查詢：IN (SELECT ...) 改為 JOIN，MySQL 8.0+ 已優化但仍建議顯式 JOIN。
3. Join 條件加索引：被驅動表的關聯欄位必須有索引。

六、資料型別與隱式轉換

-- 錯誤：account 為 CHAR(11)，傳入數字導致隱式轉換，索引失效
SELECT * FROM user WHERE account = 13800138000;

-- 正確：傳入字串
SELECT * FROM user WHERE account = '13800138000';

EXPLAIN 會顯示 type=ALL vs type=const 的天壤之別。

七、主鍵設計原則

• 優先自增整數 (BIGINT/UNSIGNED INT)：插入效能高、減少頁分裂、二級索引存主鍵值較小。
• 避免 UUID、字串、非單調值作主鍵。