前缀索引适合哪些字段类型和查询模式
前缀索引只对
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），而前缀长度选小了查得慢、选大了占空间，往往得先采样分析再决定——这步跳过，后面全是坑。
