为什么需要给数据库表加前缀索引
有一台跑论坛的服务器，帖子表的 title 字段是 varchar(255) utf8mb4，一百六十多万行。站长为了让「按标题搜索」变快，在这列上建了一个普通索引，结果索引文件占了 4.6G，几乎和表数据一样大。更麻烦的是把 index_buffer_size 调到 512M 之后，缓冲池里能装的索引页还是太少，随机读盘的比例反而上升，搜索页的响应时间从 80ms 涨到 400ms 以上。
这是一个很典型的场景：索引建在长字符串列上，前缀区分度足够，却被整列索引白白吃掉空间。MySQL 提供了前缀索引来解决它，但用错的人比用对的人多——要么前缀长度随手写个 10，要么以为前缀索引能做覆盖扫描，要么在主从复制或排序时被隐式回表打得很惨。这篇把原理、选长度的实测方法、以及四个常见翻车点讲清楚。
前缀索引到底省了什么
先明确一件事：MySQL 的 B+ 树二级索引，叶子节点里存的是索引列的值 + 主键值。对于 KEY idx_title (title) 这样的整列索引，叶子节点要存完整的 255 字符（utf8mb4 下最多 1020 字节）。而对 KEY idx_title (title(20))，叶子节点只存前 20 个字符（最多 80 字节）。
省下来的部分体现在三个地方：
- 索引文件体积：扇出变大，树高降低，同样的 buffer pool 能缓存更多索引页；
- 写入代价：每次 INSERT / UPDATE 写索引时的页分裂和重组织成本按比例下降；
- 内存压力：mysqld 进程常驻内存里的索引缓存变少，对 2G、4G 内存的小机器影响明显。
开销则只有一个：前缀相同但后缀不同的记录会产生「假命中」，MySQL 拿到候选行后必须回表读出完整值再逐行比对。也就是说前缀索引把「一次 B+ 树定位」变成了「一次定位 + N 次回表 + N 次字符串比较」。所以前缀长度的选择本质上是用回表次数换空间，而这个平衡点完全取决于数据本身的分布。
第一步：量化「前缀区分度」
不要凭感觉选长度，直接让 MySQL 告诉你每个长度的选择性。核心 SQL 是「不同前缀值的数量 ÷ 总行数」：
SELECT COUNT(DISTINCT LEFT(title, 5)) / COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(title, 8)) / COUNT(*) AS sel8,
COUNT(DISTINCT LEFT(title, 10)) / COUNT(*) AS sel10,
COUNT(DISTINCT LEFT(title, 12)) / COUNT(*) AS sel12,
COUNT(DISTINCT LEFT(title, 16)) / COUNT(*) AS sel16,
COUNT(DISTINCT LEFT(title, 20)) / COUNT(*) AS sel20,
COUNT(DISTINCT LEFT(title, 30)) / COUNT(*) AS sel30,
COUNT(DISTINCT title) / COUNT(*) AS sel_full
FROM posts WHERE title <> '';在 160 万行的实测数据上，输出是这样的：
| 前缀长度 | 选择性 | 索引大小估算 |
|---|---|---|
| 5 | 0.412 | 约 90M |
| 8 | 0.731 | 约 145M |
| 10 | 0.884 | 约 180M |
| 12 | 0.951 | 约 215M |
| 16 | 0.983 | 约 290M |
| 20 | 0.991 | 约 360M |
| 30 | 0.996 | 约 540M |
| 整列 | 0.998 | 4.6G |
读法是找曲线拐点：12 到 20 之间每多两个字符只换来 0.4% 的选择性提升，却要多付近一倍空间。所以这篇表选 title(14) 是比较合适的——选择性 0.96 左右，索引约 250M，约为整列索引的 5%。
这里有个判断经验：选择性达到 0.9 以上就基本够用了。因为 MySQL 优化器判断是否走索引并不只看选择性，还要看回表成本；前缀索引把选择性的极限锁死在整列值以下，追求 0.99 以上通常是浪费。
第二步：估算真实体积，别只看行数
上面表格里的体积是粗估。想更准，可以在测试库上用 ALTER TABLE ... ADD INDEX 建出来，再看 information_schema.TABLES：
SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, SUB_PART,
CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'forum' AND TABLE_NAME = 'posts';
SUB_PART 就是前缀长度。要估总体积，更稳的办法是用 innodb_index_stats：
SELECT index_name, stat_name, stat_value
FROM mysql.innodb_index_stats
WHERE database_name = 'forum' AND table_name = 'posts'
AND stat_name IN ('size', 'n_leaf_pages', 'n_diff_pfx02');
size 这一行的 stat_value 单位是页（16KB），乘 16384 就是字节数。建索引前后各取一次，差值非常准，比任何估算公式都可靠。务必在从库或临时实例上先做这一步，1.6G 的表在主库上直接加索引会长时间持有 MDL，写操作全部排队。
翻车点一：order by 与 group by 被前缀打断
这是最容易忽略的代价。假如有查询：
SELECT id, title FROM posts WHERE title LIKE 'nginx%' ORDER BY title LIMIT 20;用整列索引时，MySQL 可以在索引上有序扫描，直接取前 20 条，Extra 显示 Using index 或至少没有 Using filesort。换成 title(14) 之后，为了对完整 title 排序，MySQL 必须把所有候选行回表取回完整值再排序——一旦 LIKE 'nginx%' 命中上万行，就是一次全量回表加排序。
排查方法就是看 EXPLAIN 的 Extra 列有没有新出现的 Using filesort、Using temporary，以及 rows 是否暴涨。如果这个排序是核心查询路径，就不要在前缀索引上省钱，要么保留整列索引，要么把查询改写成「先按前缀索引取主键、再按主键 IN 查详情」——但这样 LIMIT 语义就变了，分页会出问题。
翻车点二：以为前缀索引能覆盖查询
覆盖索引要求索引里包含查询所需的全部列。title(14) 只存了前 14 个字符，如果 SELECT 里要返回完整 title，MySQL 就必须回表。所以：
EXPLAIN SELECT title FROM posts WHERE title = '某个很长的完整标题';用整列索引时是 Using index（不需要回表）；换成前缀索引后变成 Using where（要回表）。很多人做完前缀索引发现查询反而慢了，就是因为在原本「覆盖扫描」的热点查询上引入了回表。
判断办法很简单：列出这个索引服务的所有 SQL，逐条 EXPLAIN 前后对比。如果其中有用到覆盖扫描的，把这些查询单独拎出来评估——它们要么保留整列索引（多建一个），要么改写查询逻辑。
翻车点三：前缀长度写成了字符数还是字节数
MySQL 的 title(20) 里，20 指的是字符数，不是字节数。utf8mb4 下一个中文字符占 3~4 字节，所以 varchar(255) 建 title(20) 索引，单条索引键最大是 80 字节，完全在 InnoDB 的 767 字节（或 DYNAMIC 行格式下 3072 字节）限制之内，没问题。
真正会踩的坑是反过来：有人把长度写成字节数，比如以为「60 字节够用」就写 title(60)，那是 60 个字符，索引直接膨胀三倍。另一个坑是在 utf8mb4 与 utf8（实际是 utf8mb3）混用的老库上迁移时，前缀长度不变但字节数变了，如果原本贴着 767 字节上限，迁移后会直接报 Specified key was too long。
翻车点四：LIKE 查询只对前缀有效
前缀索引对 LIKE 'abc%' 有效，对 LIKE '%abc' 和 LIKE '%abc%' 完全无效——这两种本来就是全表扫描，有没有索引、索引多长都一样。所以如果这张表的主要查询是全文模糊匹配，前缀索引解决不了问题，该上的是全文索引（FULLTEXT + ngram 分词）或者外部的搜索引擎。
反过来说，如果你的查询大多是 LIKE 'xxx%' 这种右侧开放的匹配，前缀索引不仅省空间，还能正常工作，这是它最理想的使用场景。
什么时候该用、什么时候不该用
结合上面的分析，给出一个判断清单：
- ✅ 适合：长字符串列（varchar 100 以上）只用来做等值或右模糊查找；该列不会被 ORDER BY / GROUP BY；查询不需要覆盖扫描；表很大而内存很小。
- ⚠️ 谨慎：前缀值分布极不均匀的列。比如 URL 列，前 20 个字符往往都是
https://www.example.com/，选择性可能只有 0.01。这种列要先做前缀裁剪（存 path 而不是完整 URL），或者干脆建在计算列上：ALTER TABLE t ADD COLUMN url_hash BINARY(16) AS (UNHEX(MD5(url))) STORED, ADD INDEX (url_hash);用哈希做等值查找才是正解。 - ❌ 不适合：主键、唯一约束字段（前缀唯一不等于值唯一，
UNIQUE KEY (slug(10))会把两个前缀相同的不同 slug 判成冲突，直接导致写入失败）；需要精确排序的列；本身就很短的列（varchar(20)以下没必要）。
线上替换的安全步骤
最后是执行顺序，这一步千万别图快：
- 在从库或临时实例上用同构数据建前缀索引，取
innodb_index_stats的真实体积和cardinality； - 对这个索引服务的每条 SQL 跑
EXPLAIN，记录type / rows / Extra三列，确认没有新增filesort或丢失Using index； - 主库执行
ALTER TABLE posts ADD INDEX idx_title_p14 (title(14)), ALGORITHM=INPLACE, LOCK=NONE;先建新的、保留旧的； - 观察一周
performance_schema.table_io_waits_summary_by_index_usage，确认新索引确实被用上、旧索引只剩 0 次读； - 把旧索引
ALTER TABLE ... ALTER INDEX idx_title INVISIBLE;隐藏观察 3 天，无异常再 DROP。
这套「先加后验证再删」的顺序，是避免「改完发现某条后台查询慢了十倍」的唯一办法。前缀索引属于典型的「省空间但改变执行计划」的改动，任何一次执行计划变化都必须在删旧索引之前被看到。
一个反面案例的复盘
回到开头那台服务器，最终的做法并不是简单地把 title 换成 title(14)。排查发现真正的问题有两个：一是搜索页用的是 LIKE '%关键词%'，整列索引根本没被用上，那 4.6G 是纯粹的浪费；二是几个后台统计 SQL 用了 ORDER BY title。
所以最终方案是：删掉无用的整列索引，改上 title(14) 供 LIKE '关键词%' 的前缀搜索使用，同时把搜索页改成全文索引；后台统计改为按 created_at 排序（业务上本来就不需要按标题排）。改完索引总占用从 5.2G 降到 340M，buffer pool 命中率从 92% 回到 99.4%，搜索页 P95 从 400ms 降到 60ms。
这个案例的启示是：先确认索引到底有没有被用上，再讨论用多长。前缀索引是空间优化工具，不是性能银弹；一个从未被优化器选中的索引，无论多短都不会让查询变快。