MySQL 前綴索引實戰：從警告資訊到最佳長度計算

觸發警告的場景

對 VARCHAR(512) 的 url 欄位建立索引時出現警告：

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

MySQL 自動建立 url(383) 前綴索引（GBK 雙位元組，383×2=766 位元組）。

字元集與索引長度限制對照表

註：MySQL 5.7+ 配合 innodb_large_prefix=ON 可放寬至 3072 位元組。

前綴索引選擇性計算實戰

-- 全欄位選擇性
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 字元時選擇性 0.0750 已接近全欄位，無需使用預設 383 字元。

優化建議與限制

1. 建立最佳前綴索引：ALTER TABLE article ADD INDEX ind_article_url(url(85))
2. 節省空間：索引大小縮減 78%（383→85 字元）
3. 已知限制：ORDER BY url、GROUP BY url 無法使用前綴索引，會觸發 Using filesort

驗證 GROUP BY 無法用前綴索引

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