MySQL前缀索引用法：解决超长字段报错与优化查询效率

问题背景与原因分析

在进行表结构变更时，如果尝试对 `varchar` 类型字段建立完整索引，可能会遇到超出最大键长限制的错误。例如，在 GBK 字符集下（每个字符占2字节），若字段长度为512字符，则需存储1024字节，超过了 MySQL 索引最大767字节（旧版本引擎）的限制。系统通常会发出警告并自动截断索引长度。

root@test 07:58:40>alter table article add index ind_article_url(url);
Query OK, 144384 rows affected, 1 warning (19.52 sec)
...
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |

这一现象的核心在于不同字符集的字节占用差异：

• latin/GBK：通常双字节，最大支持约 383 个字符（767/2）。
• UTF-8：通常三字节，最大支持约 255 个字符（767/3）。

如何确定最佳前缀索引长度

为了在不牺牲过多查询效率的前提下减小索引体积，需要根据数据的“区分度”（选择性）来计算最佳前缀长度。区分度越高，说明该前缀越能唯一标识行记录。

第1步：查看全列选择性

首先对比全字段与部分字段的区分能力。使用以下 SQL 统计所有不重复值占总记录数的比例：

SELECT count(distinct(url))/count(*) FROM article;

假设结果接近 0.0750，这代表全字段提供的基数。

第2步：测试不同长度的前缀区分度

利用 LEFT(字段名, N) 函数逐步增加截取长度，观察区分度何时趋于稳定。当新增一个字符不再显著增加不重复值数量时，即可停止增长，以此作为最佳前缀长度。

SELECT
    count(distinct left(url, 76))/count(*) AS url_76,
    count(distinct left(url, 77))/count(*) AS url_77,
    count(distinct left(url, 85))/count(*) AS url_85
FROM article;

测试结果可能显示：当长度为 85 时，其选择性已非常接近全字段的结果（如从 0.0749 提升至 0.0750）。此时建立前缀索引不仅节省空间，且查询性能无损。

ALTER TABLE article ADD INDEX ind_article_url(url(85));

前缀索引的性能局限

虽然前缀索引能有效降低存储空间和提高内存缓存命中率，但它在某些查询场景中存在明显短板。对于前缀索引，MySQL 优化器往往无法直接用于 ORDER BY 和 GROUP BY 操作。

如下执行计划所示，即便存在索引，分组查询仍退化为全表扫描：

EXPLAIN SELECT id, url FROM article GROUP BY url;

输出结果为 All 类型，并且提示 Using temporary; Using filesort，这表明数据库必须构建临时表并进行文件排序，失去了索引加速的意义。因此，若业务逻辑重度依赖前缀字段的分组聚合，建议重新评估表结构设计，或考虑其他索引方案。

以上就是 MySQL 前缀索引用法的详细内容，更多关于 MySQL 索引优化的资料请关注本站其它相关文章！