字符串字段加索引前必须限制长度，因 innodb 默认单列索引上限 767 字节（utf8mb4 下约 191 字符），超长需指定前缀长度，并结合区分度与实际数据分布选择最小有效长度。
字符串字段加索引前必须限制长度
MySQL 对 VARCHAR、TEXT 类型字段建索引时，不能直接对全字段加索引（尤其 > 767 字节），否则会报错 Specified key was too long。InnoDB 默认单列索引最大长度是 767 字节（utf8mb4 下约 191 个字符），超长必须显式指定前缀长度。
实操建议：
- 用
ALTER TABLE t ADD INDEX idx_name (name(191));替代ADD INDEX idx_name (name) - 前缀长度不是拍脑袋定的：先查数据实际分布，比如
SELECT MAX(LENGTH(name)) FROM t;，再结合区分度测试（SELECT COUNT(DISTINCT LEFT(name, 10)) / COUNT(*) FROM t;）选一个能覆盖 95%+ 唯一性的最小长度 - 避免用
TEXT直接建索引——它不支持全文索引以外的常规 B+Tree 索引，除非加前缀且字段允许为 NULL
utf8mb4 下索引长度计算容易踩坑
很多人按字符数设前缀长度，却忘了 MySQL 索引长度单位是「字节」。utf8mb4 编码下，一个汉字最多占 4 字节，所以 VARCHAR(255) 字段若全存中文，理论最大索引字节数是 255 × 4 = 1020，远超 767 上限。
常见错误现象：
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes- 建表时没报错，但插入长字符串后查询突然变慢——其实是索引失效，优化器改走全表扫描
解决方法：
- 确认当前字符集：
SHOW CREATE TABLE t;查DEFAULT CHARSET和字段定义 - 安全起见，utf8mb4 下前缀长度别超 191（191 × 4 = 764 字节）；若确定字段只存 ASCII，可放宽到 255
- MySQL 5.7.7+ 支持
innodb_large_prefix=ON（需配合ROW_FORMAT=DYNAMIC），可将上限提至 3072 字节，但线上环境慎开，部分旧客户端或备份工具不兼容
前缀索引会导致 ORDER BY 和 GROUP BY 失效
这是最容易被忽略的副作用：前缀索引只保存字段开头 N 个字符，MySQL 无法用它做完整值排序或分组。比如 INDEX name(10)，执行 ORDER BY name 时仍会触发 filesort。
使用场景判断：
- 仅用于
WHERE name = ?或WHERE name LIKE 'abc%'等等值 / 左匹配查询 → 前缀索引合适 - 需要
ORDER BY name、GROUP BY name、或SELECT DISTINCT name→ 必须用完整索引，或改用生成列 + 函数索引（MySQL 8.0+） - 想兼顾查询和排序？可建两个索引：
INDEX idx_name_prefix (name(191))+INDEX idx_name_full (name)，但注意磁盘和写入开销翻倍
MySQL 8.0+ 推荐用函数索引替代简单前缀索引
如果业务只需要按小写或去空格后匹配（比如邮箱校验、用户名模糊搜索），硬套前缀索引既浪费空间又限制灵活性。MySQL 8.0 引入的函数索引更精准。
示例：
ALTER TABLE users ADD INDEX idx_email_lower ((LOWER(email))); ALTER TABLE users ADD INDEX idx_name_trim ((TRIM(name)));优势：
- 索引值就是计算后的结果，
WHERE LOWER(email) = 'A@B.COM'可直接命中 - 不占用额外字段，比生成列更轻量
- 避免前缀截断导致的哈希冲突（比如“abcde”和“abcdf”前 3 位都是“abc”，索引区分度暴跌）
注意点：
- 函数索引只在 8.0.13+ 完全稳定，低版本勿用
- 函数必须是 deterministic（确定性），不能含
NOW()、RAND()等 - EXPLAIN 里
key列显示为函数表达式，不是字段名，排查时别看漏
实际业务中，字符串索引最常卡在「以为加了索引就万事大吉」，但 utf8mb4 字节计算、前缀截断、排序失效、函数索引兼容性这四点，任何一个没对齐都会让性能掉档。尤其是老系统升级到 8.0 后，别急着删旧索引——先看慢查日志里 type: ALL 是不是悄悄冒出来了。
