核心原則：索引順序 = WHERE 條件順序 + ORDER BY 順序

MySQL 單次查詢**只能使用一個索引**（Index Merge 除外）。聯合索引 (a,b,c) 的生效規則：

1. 最左前綴原則： WHERE a=1 AND b=2 可用；WHERE b=2 不可用。
2. 範圍條件截斷： WHERE a=1 AND b>2 AND c=3 → c 無法使用索引（b 為範圍）。
3. ORDER BY 接續性： WHERE a=1 ORDER BY b, c 可避免 filesort；WHERE a=1 ORDER BY c 會 filesort（跳過 b）。
4. 方向一致性： ORDER BY a ASC, b DESC 若索引為 (a ASC, b ASC) 仍會 filesort（8.0+ 支援降序索引可解決）。

聯合索引 idx_a_b_c(a,b,c) 實測矩陣（MySQL 5.1.63）

圖表關鍵讀法：

• 綠色 = 使用索引且無 filesort
• 黃色 = 使用索引但有 filesort
• 紅色 = 無法使用索引（全表掃描）

思考題：3000 萬行大表優化

CREATE TABLE ttt (
  id INT(10) NOT NULL AUTO_INCREMENT,
  uid INT(10) NOT NULL,
  status TINYINT(2) NOT NULL,
  ordertime INT(11) NOT NULL,
  view VARCHAR(20) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_status_ordertime (status, ordertime),
  KEY idx_uid (uid)
) ENGINE=InnoDB;

-- 資料分佈
SELECT COUNT(DISTINCT uid) FROM ttt;           -- 9,112
SELECT COUNT(DISTINCT status, ordertime) FROM ttt; -- 5,424

-- 目標查詢
SELECT * FROM ttt
WHERE status = 6
  AND ordertime >= 1451012466
  AND ordertime <= 1451016066
ORDER BY uid ASC
LIMIT 100;

問題分析

• 現有索引 idx_status_ordertime(status, ordertime) 可服務 WHERE，但 ORDER BY uid 需 filesort。
• uid 基數 9,112，status+ordertime 基數 5,424，兩者相近。
• LIMIT 100 僅需前 100 筆，若能**按 uid 顺序讀取並過濾**，極快；但現有索引不支援。

優化方案對比

方案	索引	優點	缺點	適用版本
A	INDEX idx_uid_status_ordertime (uid, status, ordertime)	按 uid 掃描，WHERE 為 index condition pushdown (ICP) 過濾，無 filesort	需掃描較多 uid 值直到湊滿 100 筆	5.6+ (ICP)
B	INDEX idx_status_ordertime_uid (status, ordertime, uid)	WHERE 完美匹配，ORDER BY uid 滿足索引順序（status, ordertime 固定值）	若 status/ordertime 範圍大，掃描行數多	全版本
C	維持原索引 + SQL_SMALL_RESULT + 增大 sort_buffer_size	零改動	filesort 落盤風險	全版本

推薦：方案 B（最穩健）

ALTER TABLE ttt ADD INDEX idx_status_ordertime_uid (status, ordertime, uid);
-- EXPLAIN 驗證：type=ref, key=idx_status_ordertime_uid, Extra=Using where（無 filesort）

原理：WHERE status=6 AND ordertime BETWEEN ... 固定了前兩列，第三列 uid 在索引中**天然有序**，直接滿足 ORDER BY uid，無需額外排序。

避免 filesort 核心檢查清單

1. EXPLAIN 確認 Extra 無 Using filesort。
2. WHERE 條件列 + ORDER BY 列 = **聯合索引前綴**，且順序、方向完全一致。
3. 範圍條件（>, <, >=, <=, !=, LIKE '%...'）**之後的列**不可用於排序。
4. GROUP BY 同理：若分組列符合索引順序，可避免臨時表+排序。
5. 大表不可避免 filesort 時：LIMIT N + sort_buffer_size 適度調大，或改用應用端分頁游標。