mysql前缀索引适合哪些场景_mysql索引空间优化

前缀索引仅适用于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），而前缀长度选小了查得慢、选大了占空间，往往得先采样分析再决定——这步跳过，后面全是坑。