如何在MySQL5.7中通过前缀索引优化超长VARCHAR列的存储？-mysql教程-PHP中文网

varchar(255)不该直接建普通索引，因utf8mb4下最坏索引键达1020字节，超innodb单页限制，致b+树变深、写入延迟上升；须用前缀索引并依区分度（≥0.95）选长度。

如何在mysql5.7中通过前缀索引优化超长varchar列的存储？

前缀索引不能减少数据本身的存储体积，只压缩索引体积；对超长 VARCHAR 列建全字段索引会显著拖慢写入、撑大磁盘，必须用前缀索引控制索引大小。

为什么 `VARCHAR(255)` 不该直接建普通索引

MySQL 5.7 默认使用 utf8mb4 字符集，一个字符最多占 4 字节。如果对 VARCHAR(255) 建全字段索引，最坏情况下单个索引键长达 1020 字节（255×4），远超 InnoDB 单页 16KB 的限制，会导致：
– 索引树层级变深，查询时 B+ 树遍历 IO 次数增加
– 插入/更新需维护更大更重的索引结构，写入延迟明显上升
– SHOW INDEX FROM table_name 中的 Sub_part 字段显示为 NULL，表示用了全列，但实际已触发内部截断或警告

怎么选前缀长度：不是拍脑袋，得看区分度

前缀太短 → 大量重复前缀 → 索引失效（rows 预估扫描行数飙升）
前缀太长 → 失去轻量化意义，逼近全字段索引代价
正确做法是用统计语句验证：

SELECT
  COUNT(DISTINCT LEFT(content, 10)) / COUNT(*) AS sel_10,
  COUNT(DISTINCT LEFT(content, 20)) / COUNT(*) AS sel_20,
  COUNT(DISTINCT LEFT(content, 30)) / COUNT(*) AS sel_30
FROM article;

目标是让选择性（selectivity）≥ 0.95，即 95% 的值能靠前 N 个字符区分开。常见经验：
– 用户邮箱：前 30 字符基本够用（@ 前用户名 + 部分域名）
– 文章摘要：可能需要 50~100 字符才能避开大量“本文介绍了…”开头的重复
– URL 字段：前 128 字符通常已覆盖协议+域名+关键路径

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

创建和验证前缀索引的实操要点

MySQL 5.7 不支持在已有索引上直接修改前缀长度，必须先删后建：

删除旧索引：DROP INDEX idx_full_content ON article;
新建前缀索引：CREATE INDEX idx_content_prefix ON article (content(50));
确认生效：SHOW INDEX FROM article 查看 Sub_part 列是否为 50
检查是否命中：EXPLAIN SELECT * FROM article WHERE content LIKE 'MySQL优化%';，确保 key 显示为 idx_content_prefix，且 Extra 不含 Using filesort 或 Using temporary

注意：LIKE 查询只有前导匹配（'abc%'）能走前缀索引；'%abc' 或 '%abc%' 完全无法利用。

容易被忽略的坑：前缀索引不支持 ORDER BY 和范围查询的完整加速

前缀索引只保证前 N 字符有序，不代表整列有序。这意味着：
– ORDER BY content 仍可能触发 Using filesort
– WHERE content > 'abc' AND content 无法精准剪枝，InnoDB 得回表比对完整值<br>– 如果业务强依赖排序或区间扫描，宁可牺牲一点索引体积，把前缀拉到足够覆盖高频区分段（比如 80 而非 50），也不要指望前缀索引能替代全字段索引的功能