前缀索引是什么
长字符串列（URL、邮箱、长编码）建完整索引会让索引体积迅速膨胀，前缀索引只取前 N 个字符建索引，在可写性与空间上明显更划算。判断标准是选择性：不同前缀长度下去重计数与总行数的比值接近 1，就说明这个长度足够区分。它的主要代价是不能用于覆盖索引，也可能影响排序与部分条件下的匹配，换取的是更小的索引与更好的写入性能。
基本信息
| 创建 | ALTER TABLE 库.表 ADD INDEX 索引名 (列名(N)) |
|---|---|
| 适用类型 | CHAR / VARCHAR / VARBINARY 等字符串列 |
| 选择长度 | 让 COUNT(DISTINCT LEFT(列, N)) / COUNT(*) 接近 1 |
| 可用查询 | 等值查询与 LIKE '前缀%' 可用 |
| 不适用 | 无法作为覆盖索引消除回表；排序常需额外 filesort |
典型使用场景
- 长 URL 去重表：按前 20 个字符建索引，索引体积远小于全列索引
- 邮箱域名过滤：按邮箱前缀建索引加速按前缀的模糊检索
- 写入压力大的表：缩小索引体积降低每次写入维护开销
- 联合索引只能放长列时：用前缀替代全列，避免索引页快速膨胀
- 空间受限环境：磁盘或内存紧张时用前缀索引换容量
选择性评估与创建
选择性评估与创建
-- 先算不同前缀长度下的区分度，选最接近全列的那个长度
SELECT COUNT(*),
COUNT(DISTINCT LEFT(url, 8)) AS d8,
COUNT(DISTINCT LEFT(url, 12)) AS d12,
COUNT(DISTINCT LEFT(url, 20)) AS d20,
COUNT(DISTINCT url) AS full_cnt
FROM appdb.pages;
-- 建前缀索引
ALTER TABLE appdb.pages ADD INDEX idx_url (url(20));
-- 验证是否被使用，重点看 key_len 与 rows
EXPLAIN SELECT id FROM appdb.pages WHERE url = 'https://qiqicto.com/docs';取舍对照
| 维度 | 全列索引 | 前缀索引 |
|---|---|---|
| 索引体积 | 大，长列尤其明显 | 小，与前缀长度成正比 |
| 写入开销 | 较高 | 较低 |
| 覆盖索引 | 可用于覆盖索引免回表 | 通常不可，仍需回表 |
| 排序支持 | 可支持排序 | 混合排序场景常需 filesort |
| 唯一性保证 | 可做唯一索引 | 不保证唯一，需业务兜底 |
选择长度的实操方法：从 8、12、16、20 依次测算区分度，选第一个已经接近全列去重数的长度。确定性要求高的场景可以取略长的值换取更稳的选择性。
执行计划里的 key_len 显示的是前缀占用的字节数，结合字符集可以反推实际用了几列几个字符，比对计划时不要按全列长度解读。