前缀索引的取舍
前缀索引只索引长字符串列的前 N 个字符：MySQL 用「列名(N)」的语法建索引，索引体积明显缩小，写入与内存占用也跟着下降。代价是它无法覆盖查询——靠前缀定位到候选行后，还要回表核对完整值。PostgreSQL 没有这个语法，长文本匹配一般借助 pg_trgm 之类扩展。
基本信息
| 适用平台 | MySQL 特有；PostgreSQL 无此语法 |
|---|---|
| 语法 | ALTER TABLE 表 ADD INDEX 索引名 (列名(N)) |
| 区分度评估 | 用 COUNT(DISTINCT LEFT(列, N)) 对比，N 越大区分度越高、索引越大 |
| 限制 | 不能用于覆盖索引，也不能完整支撑 ORDER BY |
| PostgreSQL 替代 | 普通 B 树可做前缀匹配，模糊匹配用 pg_trgm |
典型使用场景
- URL、邮箱、地址这类长字符串列建索引
- 索引体积超出预算时用前缀做折中
- 评估加索引的写入成本前先算区分度
- 对比不同前缀长度的选择性，选够用的最小值
- 避免在长文本列上直接建全列索引
示例
示例
-- 先量化不同前缀长度的区分度，再决定长度
SELECT COUNT(DISTINCT LEFT(url, 10)) / COUNT(*) AS sel10,
COUNT(DISTINCT LEFT(url, 20)) / COUNT(*) AS sel20,
COUNT(DISTINCT url) / COUNT(*) AS sel_full
FROM pages;
-- 按选定的长度建前缀索引
ALTER TABLE pages ADD INDEX idx_url20 (url(20));
-- 验证命中：key 应为 idx_url20，且仍需要回表核对完整值
EXPLAIN SELECT id FROM pages WHERE url = 'https://example.com/a';前缀长度怎么选
| 前缀长度 | 区分度 | 索引体积 | 适用场景 |
|---|---|---|---|
| 10 | 较低 | 小 | 只按域名或前缀查询 |
| 20 | 中等 | 中 | 大多数 URL 检索 |
| 50 | 较高 | 较大 | 需要更精确的定位 |
| 整列 | 最高 | 最大 | 需要覆盖索引或精确排序 |
前缀索引始终需要回表：即使查询只用到这一列，MySQL 也要回表核对完整值，所以它替代不了覆盖索引，做纯只读加速时收益有限。
区分度不是越高越好：很多列前 10 个字符就能区分绝大多数值，把前缀加到 50 只增加体积却几乎不提升过滤能力，用统计对比定长度更靠得住。