mysql中使用前缀索引与性能优化技巧

前缀索引长度应通过计算区分度确定：用COUNT(DISTINCT LEFT(col,N))/COUNT(*)≥0.95为基准，再结合EXPLAIN验证key_len和rows；必须显式指定长度，不支持ORDER BY/GROUP BY；复合索引中需注意字段顺序与区分度。

前缀索引到底该取多长？别猜，用 SELECT 算出来

前缀索引长度不是拍脑袋定的。太短，重复值多，索引失效；太长，浪费空间、拖慢写入。关键看字段值的**区分度**——即前 N 个字符能区分多少行数据。

实操步骤：

• 先估算区分度：用 COUNT(DISTINCT LEFT(column_name, N)) / COUNT(*)，N 从 3 开始逐步试到 10 或 20
• 当结果 ≥ 0.95（即 95% 行能被唯一前缀覆盖），基本够用
• 对比不同 N 下的 EXPLAIN 输出，确认 key_len 和 rows 是否明显下降

SELECT
  COUNT(*) AS total,
  COUNT(DISTINCT LEFT(email, 5)) AS prefix5,
  COUNT(DISTINCT LEFT(email, 8)) AS prefix8,
  COUNT(DISTINCT LEFT(email, 12)) AS prefix12
FROM users;

ALTER TABLE ... ADD INDEX 加前缀索引时，必须显式指定长度

MySQL 不允许对 TEXT/VARCHAR 类型字段直接建普通索引（除非你设了 innodb_large_prefix=ON 且行格式支持），但更常见的是加前缀索引。漏写长度会报错：ERROR 1071 (42000): Specified key was too long。

正确写法：

• ALTER TABLE users ADD INDEX idx_email_prefix (email(12)); —— 括号里是数字，不是函数
• 不能写成 email(LEFT(email,12)) 或 email USING BTREE（后者语法错误）
• 如果字段是 TEXT，必须加长度；VARCHAR(255) 同样要写，否则默认用全列，可能超限制

前缀索引不支持 ORDER BY 和 GROUP BY 的完整排序/分组

这是最容易踩的坑：前缀索引只加速“查找”，不保存完整字段值，所以无法用于排序或去重逻辑。

比如：

• SELECT * FROM users WHERE email LIKE 'abc%'; → 走前缀索引 ✅
• SELECT DISTINCT email FROM users; → 无法用前缀索引去重 ❌（实际走全表扫描）
• SELECT * FROM users ORDER BY email LIMIT 10; → 即使有 email(12) 索引，也大概率不走 ✅（除非 WHERE 条件已限定范围且优化器认为划算）

如果你依赖 ORDER BY email 性能，要么建完整长度索引（注意长度限制），要么在应用层缓存排序结果。

复合索引里混用前缀索引，顺序和长度都影响命中率

前缀索引可以作为复合索引的一部分，但它的“有效信息量”比完整索引低，容易让优化器放弃使用整个索引。

例如建了 (status, email(10), created_at)：

• 查询 WHERE status = 'active' AND email LIKE 'test%' → 可能走索引 ✅
• 但 WHERE email LIKE 'test%' AND created_at > '2025-01-01' → 很可能不走，因为 email 不是索引最左列 ❌
• 如果 email(10) 区分度差（比如大量邮箱以 user_ 开头），优化器可能直接跳过这个复合索引

建议：把高区分度字段放前面；前缀字段尽量控制在区分度 ≥ 0.9；必要时用 FORCE INDEX 验证，但别在线上滥用。

前缀索引不是银弹——它省空间、快查询，但代价是丧失排序能力、增加评估成本。真正难的不是怎么建，而是判断“这里值不值得建”。每次加之前，先跑一遍 SELECT COUNT(DISTINCT ...)，再看 EXPLAIN，比凭经验靠谱得多。