MySQL 前綴索引導致 ORDER BY 慢查詢深度分析：原理、EXPLAIN 解讀與 Schema 設計教訓

一個毀三觀的案例：用了索引反而比全表掃描慢 100 倍

線上專案回報：`SELECT * FROM rosterusers ORDER BY username LIMIT 10000, 3` 極慢，`EXPLAIN` 顯示 `Using filesort`。但 `username` 明明有索引 (聯合主鍵 `i_rosteru_user_jid`)。深挖發現：**前綴索引 `username(75)` 導致排序無法使用索引**，被迫外部排序。本文完整還原排查過程、原理剖析與 Schema 設計教訓。

現象重現

-- 不加 ORDER BY：極快
EXPLAIN SELECT * FROM rosterusers LIMIT 10000, 3;
-- type=ALL, rows=2010066, Extra= (無 filesort)

-- 加 ORDER BY username：極慢
EXPLAIN SELECT * FROM rosterusers ORDER BY username LIMIT 10000, 3;
-- type=ALL, rows=2010087, Extra=Using filesort  ← 關鍵差異

表結構真相

CREATE TABLE `rosterusers` (
  `username` VARCHAR(250) NOT NULL,
  `jid` VARCHAR(250) NOT NULL,
  UNIQUE KEY `i_rosteru_user_jid` (`username`(75), `jid`(75)),  -- 前綴索引！
  KEY `i_rosteru_jid` (`jid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

核心原理：前綴索引 ≠ 完整列索引

• **前綴索引** 僅索引列前 N 個字節 (此處 75 bytes，utf8 約 25 個中文字)。
• **ORDER BY username** 需要按**完整列值**排序。
• 優化器發現索引只包含前綴，**無法保證完整列順序**，拒絕使用索引排序 → 退化為 `Using filesort` (內部快排/歸併排序，需臨時表/磁盤)。
• **同理：`GROUP BY username` 也無法使用前綴索引**。
• **InnoDB 主鍵/唯一索引** 若定義為前綴，**不再是真正的聚簇/唯一約束完整列**，語義改變。

為何不加 ORDER BY 反而快？

InnoDB **聚簇索引** (主鍵/隱式 Row ID) 決定資料物理順序。`LIMIT offset, n` 無 ORDER BY 時，優化器直接按聚簇索引順序掃描跳過 offset 行，**雖然掃描行數大，但順序 I/O、無排序開銷**，實測比 filesort 快得多。

嘗試強制索引失敗

SELECT * FROM rosterusers FORCE INDEX (i_rosteru_user_jid) ORDER BY username LIMIT 10000, 3;
-- 依然 Using filesort，優化器判斷索引不適用於完整列排序

根因：Schema 設計失誤

• 原系統 (ejabberd) 為規避 InnoDB 索引長度限制 (767 bytes / 3072 bytes)，對 `VARCHAR(250)` 使用前綴 75。
• 實際業務中 `username` **遠小於 75 字節**，完全不需要前綴。
• **前綴索引適用場景**：極長文本列 (如 `TEXT`、`BLOB`、超長 `VARCHAR`) 且**不需排序/分組/精確比較**完整值。

解決方案 (受限於第三方表結構不可改)

1. 業務妥協：去除 `ORDER BY`，接受任意順序分頁 (或應用層排序小結果集)。
2. 遷移重建：同步資料至新表 `username VARCHAR(100) NOT NULL, UNIQUE KEY (username, jid)` 完整索引。
3. 覆蓋索引變通：若只查特定欄位，建立 `INDEX (username(75), jid, col1, col2...)` 覆蓋查詢，但排序仍需 filesort。

Schema 設計鐵律

總結

前綴索引**不是萬能藥**。它解決「長列建索引」但引入「排序/分組失效、唯一性語義弱化」副作用。**合理的欄位長度設計永遠優於前綴索引補救**。此案例中，`username(75)` 純屬多餘，導致核心查詢退化為 filesort，典型的「自己挖坑自己跳」。