前缀索引仅适用于VARCHAR、TEXT等变长字符串字段，且需满足WHERE column LIKE 'abc%'或前缀等值查询，同时字段内容具有高头部区分度；反例为UUID等低区分度字段。
前缀索引适合哪些字段类型和查询模式
前缀索引只对 VARCHAR、TEXT 这类变长字符串字段有意义，对 INT、DATETIME 或定长 CHAR（且长度合理）完全没必要。它真正起作用的前提是：业务查询经常用到 WHERE column LIKE 'abc%' 或等值匹配前几位（比如邮箱域名查 @gmail.com 用户），且字段实际内容有明显“头部区分度”。
常见有效场景包括：
- 邮箱字段（
email VARCHAR(255)），取前 30 位通常就能覆盖@之后的域名+用户名前段，重复率极低 - URL 路径（
path TEXT），如匹配/api/v1/users/开头的请求 - 用户昵称或标题（
title VARCHAR(100)），前 15–20 字已足够区分绝大多数值
反例：UUID 字符串（CHAR(36)）或全随机 base64 值——前缀区分度差，建前缀索引反而导致大量哈希冲突，查询变慢。
怎么选前缀长度才不浪费空间又不失效
不能拍脑袋定 10 或 20，得看数据分布。核心是找一个最小长度 L，使得 COUNT(DISTINCT LEFT(column, L)) / COUNT(*) >= 0.95（即前缀去重率 ≥ 95%）。MySQL 8.0+ 可直接用这个语句探查：
SELECT len, COUNT(DISTINCT LEFT(email, len)) * 100 / COUNT(*) AS selectivity FROM users CROSS JOIN (SELECT 10 AS len UNION SELECT 20 UNION SELECT 30 UNION SELECT 40) AS lengths GROUP BY len ORDER BY selectivity DESC, len LIMIT 1;
注意点：
- 必须在真实数据量级上跑，小表测试结果在大表上可能失效
- 如果
selectivity到 30 还不到 90%，说明字段本身不适合前缀索引，考虑其他方案（如生成哈希列） -
LEFT()在WHERE中无法走索引，所以这个探查仅用于评估，不是运行时写法
建前缀索引时容易被忽略的语法和限制
创建语句看着简单，但几个细节一错就白忙：
- 语法必须是
INDEX idx_name (column(length))，不是INDEX idx_name ((SUBSTR(column, 1, length)))—— 后者是函数索引，MySQL 5.7 不支持，且无法用于LIKE 'xxx%' - 前缀长度单位是「字符数」，不是字节数；如果字段是
utf8mb4，一个 emoji 占 4 字节但只算 1 字符，别按字节长度硬套 - 唯一约束（
UNIQUE INDEX）允许加前缀，但校验逻辑只基于前缀值，意味着'abc123'和'abc456'在前缀长度为 3 时会被认为重复 -
EXPLAIN显示key_len会反映前缀长度（如utf8mb4下 30 字符 ≈ 120 字节），可用于验证是否真生效
比前缀索引更省空间的替代方案有哪些
前缀索引不是万能解药。当字段超长、区分度低，或需要精确匹配全文时，这些方式更稳：
- 添加生成列 + 普通索引：
ALTER TABLE users ADD COLUMN email_prefix VARCHAR(32) STORED AS (SUBSTRING_INDEX(email, '@', -1))，再对email_prefix建索引——把高区分度部分显式抽出来 - 用
MD5()或SHA2(email, 224)存哈希值（需注意碰撞概率），索引固定长度 28–32 字节，空间恒定 - MySQL 8.0+ 支持不可见列 + 函数索引：
CREATE INDEX idx_email_domain ON users ((SUBSTRING_INDEX(email, '@', -1)))，无需改表结构
真正难的是判断“值的分布是否值得动索引”。线上表加索引要锁表（除非用 ALGORITHM=INPLACE），而前缀长度选小了查得慢、选大了占空间，往往得先采样分析再决定——这步跳过，后面全是坑。
