MySQL 前綴索引實戰：從 383 位元組降至 85 位元組的選擇性計算法

MySQL 前綴索引實戰：從 383 位元組降至 85 位元組的選擇性計算法

背景：索引長度限制觸發警告

ALTER TABLE article ADD INDEX ind_article_url(url);
-- Warning: Specified key was too long; max key length is 767 bytes

url VARCHAR(512)，字元集 GBK（雙位元組）→ 1024 bytes > 767 限制。MySQL 自動建立前綴索引 url(383)（766 bytes）。

索引長度限制規則

前綴索引長度怎麼選？核心指標：選擇性

選擇性 = COUNT(DISTINCT prefix) / COUNT(*)，越接近 1 越好。

實測計算

-- 全欄位選擇性
SELECT COUNT(DISTINCT url)/COUNT(*) FROM article;  -- 0.0750

-- 不同前綴長度選擇性
SELECT 
  COUNT(DISTINCT LEFT(url,76))/COUNT(*) AS url_76,
  COUNT(DISTINCT LEFT(url,77))/COUNT(*) AS url_77,
  ...
  COUNT(DISTINCT LEFT(url,85))/COUNT(*) AS url_85
FROM article;

結果

**85 字元即達到全欄位選擇性**，無需用 383 字元。建立索引：

ALTER TABLE article ADD INDEX ind_article_url(url(85));

前綴索引限制

⚠️ 無法用於 ORDER BY 和 GROUP BY：

EXPLAIN SELECT id, url, deleted FROM article GROUP BY url;
-- type=ALL, Extra=Using temporary; Using filesort

需全欄位索引才能支援排序/分組。

最佳實踐總結

1. 遇 Specified key was too long 警告 → 需用前綴索引
2. 計算全欄位選擇性作為基準
3. 二分法或遞增法測試前綴長度，找到選擇性接近全欄位的最小長度
4. 評估是否需支援 ORDER BY/GROUP BY，若需則考慮其他方案（如 hash 索引、應用層處理）

💡 小索引不僅省空間、減記憶體，**更能提升快取命中率與掃描速度**。