在数据库表设计中，经常会遇到需要对较长字段建立索引的场景，比如VARCHAR类型的长文本字段、BLOB类型字段等。如果直接对整个字段建立普通索引，会占用大量存储空间，同时也会影响索引的维护效率和查询性能，这时候前缀索引就是一个非常合适的优化方案。
SQL前缀索引的使用场景
前缀索引并非适用于所有场景，只有在符合以下特征的业务场景中，使用前缀索引才能发挥最大价值：
- 字段长度较长，比如存储URL、邮箱、长描述文本的VARCHAR字段，字段长度超过50个字符时，全字段索引的存储成本会明显上升。
- 查询条件中经常使用该字段的前N个字符进行匹配，比如经常用邮箱的前缀部分做查询，或者用URL的域名部分做筛选。
- 对索引的存储空间有明确要求，需要在保障查询性能的前提下，尽可能减少索引占用的磁盘空间。
- 字段的前缀部分已经具备足够的区分度，即使只取前N个字符，也能有效过滤掉大部分不符合条件的数据。
前缀索引的设计思路
设计前缀索引的核心是在索引存储成本和查询区分度之间找到平衡，具体可以按照以下步骤推进：
1. 评估字段的区分度
首先需要统计字段整体的区分度，也就是字段的不同值数量占总行数的比例，区分度越高，索引的过滤效果越好。可以通过如下SQL统计字段的总不同值数量：
-- 统计user表的email字段的不同值数量 SELECT COUNT(DISTINCT email) AS distinct_count FROM user;
2. 测试不同前缀长度的区分度
依次测试不同前缀长度下的不同值数量，找到区分度接近全字段区分度的最小前缀长度。比如测试邮箱字段前5、10、15个字符的区分度：
-- 测试email字段前5个字符的不同值数量 SELECT COUNT(DISTINCT LEFT(email, 5)) AS prefix_5_distinct FROM user; -- 测试email字段前10个字符的不同值数量 SELECT COUNT(DISTINCT LEFT(email, 10)) AS prefix_10_distinct FROM user; -- 测试email字段前15个字符的不同值数量 SELECT COUNT(DISTINCT LEFT(email, 15)) AS prefix_15_distinct FROM user;
假设全字段不同值数量是100000，前10个字符的不同值数量是98000，前15个字符的不同值数量是99500，那么优先选择10作为前缀长度，因为已经能覆盖98%的区分度，同时比15长度更节省空间。
3. 创建前缀索引
确定好前缀长度后，就可以创建前缀索引，语法和普通索引类似，只需要在字段后面加上前缀长度即可：
-- 给user表的email字段创建长度为10的前缀索引 CREATE INDEX idx_email_prefix ON user(email(10));
4. 验证前缀索引的查询效果
创建完成后，需要验证查询语句是否能正确使用前缀索引，可以通过EXPLAIN命令查看执行计划：
-- 查看查询语句的执行计划 EXPLAIN SELECT * FROM user WHERE email LIKE 'test_1001%';
如果执行计划的key列显示使用了刚创建的idx_email_prefix索引，说明前缀索引生效。
前缀索引的注意事项
- 前缀索引不支持覆盖索引，因为索引中只存储了字段的前N个字符，无法获取字段的完整值，查询时如果需要返回字段完整内容，还是需要回表查询。
- 前缀索引不适合用于需要按字段完整值排序或者分组的场景，因为索引只存储了前缀部分，无法支撑完整的排序和分组逻辑。
- 如果字段的前缀部分重复率很高，比如很多记录的字段前10个字符都相同，那么即使增加前缀长度，区分度提升也不明显，这种场景不适合使用前缀索引。
前缀索引的设计没有固定的标准，需要结合具体业务场景的查询特征和数据分布来调整，最终目标是用最小的存储成本获得满足需求的查询性能。