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

来源：这里教程网时间：2026-02-28 20:39:04 作者：

前缀索引到底该取多长？别猜，用

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 > '2023-01-01'
→ 很可能不走，因为
email
不是索引最左列 ❌ 如果
email(10)
区分度差（比如大量邮箱以
user_
开头），优化器可能直接跳过这个复合索引

建议：把高区分度字段放前面；前缀字段尽量控制在区分度 ≥ 0.9；必要时用