前缀索引的优缺点解析 - 数码工场数据备份专栏

数据库里存着大量用户信息，比如手机号、邮箱这类字段动辄几十个字符。为了加快查询速度，不少人会考虑使用前缀索引——只对字段的前几位建立索引。这招确实能节省空间，提升效率，但用不好也会踩坑。

拿用户邮箱举例，完整的 email 字段可能是 zhangsan@example.com，长度超过20。如果为整个字段建索引，每个索引项都得存这么长的字符串，占空间也影响性能。前缀索引就是只取前 N 位，比如前8位 zhangsan 来建索引。

MySQL 中创建方式如下：

ALTER TABLE users ADD INDEX idx_email_prefix (email(8));

最直观的好处是减少了索引占用的磁盘空间。字段越长，节省越明显。比如一个 VARCHAR(255) 的 URL 字段，全量索引可能让 B+ 树层级变深，而用前10位做索引，树更扁平，查询自然更快。

另一个好处是写入性能提升。索引体积小了，插入和更新时维护索引的成本也跟着下降。对于日志类数据频繁写入的场景，这点尤为关键。

问题出在“前缀不够独特”。比如公司内部系统中，所有员工邮箱都是 xxx@company.com，前缀很可能都是相同的几个字母。这时候即使建立了前缀索引，查询时仍需回表逐条比对，索引效果大打折扣。

可以通过统计不同前缀长度下的区分度来评估：

SELECT 
  COUNT(DISTINCT LEFT(email, 5)) AS prefix_5,
  COUNT(DISTINCT LEFT(email, 8)) AS prefix_8,
  COUNT(*) 
FROM users;

如果 prefix_8 接近总行数，说明8位基本够用；否则就得加长，甚至放弃前缀索引。

有时候我们希望只走索引就能拿到结果，不用回表。但前缀索引只存了部分值，无法满足这种需求。比如下面这条语句：

SELECT email FROM users WHERE email LIKE 'zhang%';

即便有前缀索引，也必须回到主键索引去查完整 email 值，因为索引里压根没存全。

在做数据备份系统的元信息管理时，经常要记录文件路径、哈希值等长字符串。像 SHA-256 这种哈希长达64位，完全可以取前12位建前缀索引。由于哈希本身分布均匀，前几位就有很高的唯一性，冲突概率极低。

但如果是 URL 路径这种结构化强、头部雷同多的数据，就得小心。比如大量页面路径都是 /static/assets/...，前十几位完全一样，前缀索引几乎失效。

当发现前缀索引不给力时，可以考虑反转字符串再建索引。比如把邮箱倒过来存成 moc.elpmaxe@nasnahz，再对前几位建索引，能更好分散热点。

或者直接用哈希字段辅助：额外加一个字段存储 email 的 CRC32 或 MD5 值，并为其建立完整索引。虽然多占点空间，但查询稳定性更高。

前缀索引在数据备份中的实际应用与利弊分析