MySQL 索引優化 DISTINCT 實戰：索引類型陷阱與多列去重解決方案

核心結論：索引類型決定 DISTINCT 能否走索引

MySQL 能否利用索引優化 DISTINCT 取決於**索引是否有序**。

• B-Tree 索引：有序 → 支持 DISTINCT 掃描（Using index for group-by / Using index）
• Hash 索引：無序 → **不支持** DISTINCT，只能全表掃描 + 臨時表

實驗驗證

表 m11 (InnoDB, 主鍵 B-Tree)

CREATE TABLE m11 (a INT PRIMARY KEY, b INT, c INT, d INT) ENGINE=InnoDB;
EXPLAIN SELECT DISTINCT a FROM m11;
-- type=index, key=PRIMARY, Extra=Using index  ✅ 走索引

表 m31 (MEMORY, 主鍵默認 Hash)

CREATE TABLE m31 (a INT PRIMARY KEY, b INT, c INT, d INT) ENGINE=MEMORY;
EXPLAIN SELECT DISTINCT a FROM m31;
-- type=ALL, key=NULL, Extra=NULL  ❌ 全表掃描，Hash 索引無序

表 m33 (MEMORY, 顯式指定 B-Tree)

CREATE TABLE m33 (a INT, b INT, c INT, d INT, INDEX USING BTREE (a)) ENGINE=MEMORY;
EXPLAIN SELECT DISTINCT a FROM m33;
-- type=index, key=a, Extra=NULL  ✅ 走 B-Tree 索引

避坑指南

1. MEMORY 引擎默認 Hash 索引 → 任何需排序/範圍/去重的查詢**必須顯式指定 USING BTREE**
2. 查看 EXPLAIN 的 key 與 Extra**：key=NULL 且 type=ALL 即索引失效
3. InnoDB 主鍵/二級索引均為 B-Tree**，無此問題

-- 錯誤：DISTINCT 只能跟單列？實際可跟多列，但結果是「多列組合去重」
SELECT DISTINCT title, posttime FROM table;  -- 正確語法，返回 (title,posttime) 唯一組合

-- 方案 1：子查詢取最小主鍵 (通用、可走索引)
SELECT t.* FROM table t
JOIN (SELECT MIN(id) AS id FROM table GROUP BY title) tmp USING (id);

-- 方案 2：GROUP BY 多列 (若需保留 posttime 組合)
SELECT title, posttime FROM table GROUP BY title, posttime;

-- 方案 3：窗口函數 (MySQL 8.0+，最靈活)
SELECT title, posttime, content FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY title ORDER BY id) rn
  FROM table
) t WHERE rn = 1;