MySQL 索引與查詢順序奧秘：為什麼 SELECT * 不按主鍵序？

MySQL InnoDB 中，SELECT * FROM table 若無 ORDER BY，回傳順序不保證遵循主鍵，而是取決於優化器選擇的存取路徑。本文透過實測案例，揭示二級索引導致的「按索引序輸出」現象，並說明 FORCE INDEX、覆蓋索引、叢集索引掃描的機制與控制手段。

實測場景重現

CREATE TABLE test_b (
    Id   INT NOT NULL,
    Type INT DEFAULT NULL,
    PRIMARY KEY (Id),
    KEY IDX_Type (Type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO test_b VALUES (1,1), (2,6), (3,2), (7,3), (4,1);

預設查詢 (無 ORDER BY)

SELECT * FROM test_b;

輸出：

順序為 Type 升序，且同 Type 下按 Id 升序 (1→4, 3, 7, 2→6)。

強制主鍵索引

SELECT * FROM test_b FORCE INDEX (PRIMARY);

輸出： 按 Id 1,2,3,4,7 — 符合主鍵叢集索引順序。

核心機制：優化器為何選二級索引？

1. SELECT * 需要所有欄位
2. 主鍵 (叢集索引) 葉節點包含完整行 → 掃描主鍵 = 全表掃描
3. 二級索引 IDX_Type 葉節點包含 (Type, Id) → 覆蓋索引 (Covering Index)，無需回表
4. 優化器評估：掃描二級索引 (較小、已排序) + 直接回傳 Id, Type 成本低於 掃描主鍵
5. 選擇 Index Full Scan on IDX_Type，輸出順序遵循索引鍵順序

同值 Type 時為何按 Id 排序？

InnoDB 二級索引鍵值實為 (Type, Id)，主鍵值隱式附加於二級索引鍵尾部 以保證唯一性。因此索引樹中 Type=1 下依 Id 排序，掃描自然產生 (1,1) → (4,1) 順序。

驗證：新增資料觀察

INSERT INTO test_b VALUES (9,3), (6,3), (10,3);
SELECT * FROM test_b;

輸出 Type=3 區段：6, 7, 9, 10 — 完全符合 (Type, Id) 排序。

控制輸出順序的最佳實務

關鍵洞見

• 無 ORDER BY = 無順序保證：SQL 標準規定，關聯模型集合無順序。任何依賴「預設順序」的應用邏輯皆為 Bug。
• 覆蓋索引改變存取路徑：SELECT * 若剛好被二級索引覆蓋，優化器會毫不猶豫選擇之，導致輸出順序隨索引定義改變。
• 主鍵即叢集索引：InnoDB 表必須有叢集索引 (PK 或首個非空唯一鍵或隱式 RowID)，資料物理順序即主鍵順序。
• FORCE INDEX 慎用：綁死執行計畫，統計資變更時可能導致災難性效能退化。優先用 ORDER BY 表達業務意圖。

延伸：SQL Server 對比

SQL Server 叢集索引同樣決定物理順序，但 SELECT * 無 ORDER BY 時，優化器通常選擇叢集索引掃描 (除非有覆蓋非叢集索引且極窄)。行為相似，但 SQL Server 更傾向維持叢集索引順序。

結論：永遠加上 ORDER BY。資料庫優化器的目標是「最低成本」，而非「符合你的直覺順序」。