MySQL 字符串索引太长时怎么选择前缀索引

字符串列索引太长时，前缀索引的正确用法不是随便写一个较小数字，而是先观察“前 N 个字符能排除多少重复值”，再核对字符集和存储引擎的字节上限。比如 utf8mb4 VARCHAR 列的前缀长度按字符填写，但索引限制按字节计算；前缀过短会让很多值落在同一个索引值组里，查询仍要回表检查。

为什么完整字符串索引会变得过宽

索引项需要保存键值的一部分以及行定位信息。对邮箱、URL、文件路径、长名称等列，完整值可能明显扩大索引页，影响缓存命中，也增加写入时维护索引的成本。MySQL 允许在索引定义中写 列名(N)，只保存字符串左侧的 N 个字符；这会缩小索引，但不是把原列截断。

前缀索引只能把候选行筛出来。假设两个值都以 https://example.com/user/ 开头，索引部分相同，MySQL 找到这个索引值组后仍要读取原列比较剩余内容。因此，长度选择的核心不是“越短越省空间”，而是让常见前缀尽量有区分度。

前缀长度先看选择性，再看字节上限

可以在测试库对几个候选长度做对比。下面的查询只用于估算数据分布，不改变表结构：

-- 对比前缀长度的不同值数量，找出区分度开始趋稳的长度
SELECT
  COUNT(*) AS total_rows,
  COUNT(DISTINCT LEFT(url, 8))  AS distinct_8,
  COUNT(DISTINCT LEFT(url, 16)) AS distinct_16,
  COUNT(DISTINCT LEFT(url, 32)) AS distinct_32,
  COUNT(DISTINCT LEFT(url, 64)) AS distinct_64
FROM download_task;

如果从 32 增加到 64 后不同值数量几乎不再增加，32 可以作为候选；但还要按真实查询分布抽样。若大量值共享相同的目录或域名前缀，候选长度应越过这个公共部分。可以把重复值最多的前缀单独找出来，而不要只看全表平均值。

语法示例：

-- 非二进制字符串按字符指定前缀长度
CREATE INDEX idx_task_url_prefix ON download_task (url(32));

-- 查看实际记录的前缀长度与统计基数
SHOW INDEX FROM download_task;

SHOW INDEX 中的 Sub_part 能显示部分索引的长度，Cardinality 是优化器使用的基数估计，不应把它当成精确去重结果。数据变化明显后，可以在业务低峰执行 ANALYZE TABLE 更新统计信息。

字符集决定能否把“字符数”直接当成“字节数”

对 CHAR、VARCHAR、TEXT 等非二进制字符串，url(32) 表示 32 个字符；对 BINARY、VARBINARY、BLOB，长度按字节解释。InnoDB 的具体前缀上限还与行格式有关，常见 DYNAMIC 或 COMPRESSED 行格式的上限是 3072 字节，旧的 REDUNDANT 或 COMPACT 行格式上限是 767 字节。实际建索引前，应以目标实例的表定义和字符集为准。

如果是 TEXT，前缀长度通常是必须的；如果前缀超过列类型或引擎允许范围，严格 SQL 模式下可能直接报错。生产环境不要只在一台测试实例上试出一个数字后照搬。

前缀索引能过滤什么，不能替代什么

建好索引后，至少用一个真实等值查询和一个范围或前缀匹配查询检查计划：

-- 用真实条件观察是否走前缀索引，以及预计扫描行数
EXPLAIN SELECT id, url
FROM download_task
WHERE url = 'https://example.com/user/2026/report.csv';

-- LIKE 的常量前缀可能使用索引；通配符放在开头通常无法利用左侧前缀
EXPLAIN SELECT id, url
FROM download_task
WHERE url LIKE 'https://example.com/user/%';

等值条件如果只命中一小组前缀值，前缀索引通常有价值；如果所有值前 8 个字符都一样，优化器可能认为它不值得使用。LIKE '%report%' 不能从字符串左侧开始定位，不能因为列上有前缀索引就期待它自动变快。

前缀索引也不适合直接承担“完整字符串唯一”的语义：不同完整值可能拥有相同前缀。需要完整唯一性时，应使用完整索引（在长度允许的前提下）、短且稳定的哈希生成列加原值校验，或重新设计键。需要按内容检索时，则应评估 FULLTEXT，不要用不断加长的前缀索引替代全文索引。

改完索引后的最小核对清单

1. 记录列类型、字符集、排序规则和 InnoDB 行格式。
2. 对至少三个 N 值比较 COUNT(DISTINCT LEFT(...))，说明最终长度的理由。
3. 用 SHOW INDEX 确认 Sub_part，再用 EXPLAIN 比较真实查询的 key 和 rows。
4. 单独检查 ORDER BY、UNIQUE、前导通配符和全文检索需求。

这个顺序能把“索引太长”的问题拆成空间、区分度和查询语义三个决定。前缀索引的最佳长度没有通用常数，数据前缀分布变化后也应重新观察。

常见问题

前缀索引长度是不是越长越好？

不是。越长通常越接近完整索引，但会增加索引体积和写入成本；应选择区分度已足够、且能满足主要查询的拐点。

前缀索引可以保证字符串不重复吗？

不能把它当作完整字符串的唯一约束。两个完整值只要前缀相同，就可能产生相同的索引键。

为什么 EXPLAIN 显示用了索引，查询还是慢？

前缀选择性可能不足，索引筛出大量候选行，剩余比较和回表成本仍然很高。重点查看估算行数、实际数据分布以及是否存在前导通配符。