mysql如何选择索引长度_mysql索引存储优化技巧-mysql教程-PHP中文网

varchar(255) 不该直接建全文索引，因utf8mb4下最大需1020字节，超innodb默认767字节限制；即使启用innodb_large_prefix提升至3072字节，过长前缀仍会增加i/o、降低缓存命中率，且唯一索引仅校验前缀部分，易引发重复问题。

mysql如何选择索引长度_mysql索引存储优化技巧

为什么 varchar(255) 不该直接建全文索引

MySQL 对 VARCHAR 字段建立索引时，若不指定长度，InnoDB 会尝试索引全部内容——但单列索引有前缀长度限制（默认 767 字节，utf8mb4 下最多约 191 个字符）。超出就报错：Specified key was too long; max key length is 767 bytes。这不是“能力不够”，而是 B+ 树节点大小和页分裂效率的硬约束。

utf8mb4 编码下，一个中文字符占 4 字节，varchar(255) 理论最大索引长度是 255×4 = 1020 字节 → 必须截断
索引长度不是越长越好：过长前缀增加索引体积、降低缓存命中率、拖慢 INSERT/UPDATE
真正需要区分的往往只是前几位，比如邮箱字段，email(128) 和 email(255) 的选择性（cardinality）可能几乎一样

怎么算出合适的前缀索引长度

核心是平衡「区分度」和「存储开销」。用 COUNT(DISTINCT) 对不同前缀长度做采样对比，找到区分度陡升后的“拐点”。

SELECT
  COUNT(DISTINCT LEFT(email, 30)) / COUNT(*) AS sel30,
  COUNT(DISTINCT LEFT(email, 50)) / COUNT(*) AS sel50,
  COUNT(DISTINCT LEFT(email, 80)) / COUNT(*) AS sel80,
  COUNT(DISTINCT LEFT(email, 128)) / COUNT(*) AS sel128
FROM users;

如果 sel80 已达 0.995，而 sel128 是 0.996，那 80 就是更优解。别盲目拉到 128 或 191。

MySQL

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

下载

生产表数据量大时，先在 LIMIT 10000 子集上快速试算，再验证全量
注意 WHERE 条件是否覆盖索引前缀——WHERE email LIKE 'a%' 能用前缀索引，WHERE email LIKE '%a' 则完全不能
对 UUID 类型字段（如 CHAR(36)），前 8–16 位通常已足够区分，没必要索引全部

innodb_large_prefix 和 ROW_FORMAT 的实际影响

MySQL 5.7+ 默认开启 innodb_large_prefix，配合 ROW_FORMAT=DYNAMIC 或 COMPRESSED，可将单列索引上限提升至 3072 字节（utf8mb4 下约 768 字符）。但这不等于“推荐你这么干”。

开启后需确认表格式：SHOW CREATE TABLE users 查看 ROW_FORMAT 是否为 DYNAMIC
即使支持 3072，B+ 树深度仍随键长增长——长键导致每页存更少记录，查询需更多磁盘 I/O
复制环境要小心：从库 MySQL 版本低于主库，或未同步开启 innodb_large_prefix，建表会失败
ALTER TABLE ... ROW_FORMAT=DYNAMIC 需锁表（除非用 ALGORITHM=INPLACE 且满足条件）

唯一索引 + 前缀长度的坑

加 UNIQUE 约束时，MySQL 只校验索引前缀部分。这意味着：UNIQUE INDEX idx_name ON users(name(10)) 允许 'Alice123' 和 'Alice456' 同时存在——因为前 10 位都是 'Alice123'（截断后相同）。

唯一性业务逻辑必须靠应用层兜底，或改用完整字段索引（并接受长度限制）
想兼顾唯一性和长度，可组合哈希字段：ADD COLUMN name_hash CHAR(32) AS (MD5(name)) STORED，再对 name_hash 建唯一索引
SHOW INDEX FROM users 中的 Sub_part 列明确显示当前索引长度，定期检查是否有误设为 NULL（即全字段）

索引长度不是配置项，是权衡结果；每次调整都该基于真实数据分布和查询模式，而不是“别人说 191 安全”。线上表一旦索引过大，清理起来比加索引麻烦十倍。