有人问我一个问题为什么varchar上的索引必须要指定索引长度我第一反应是这问题至少得拆成两层来答一层是MySQL在引擎层面确实有历史包袱留下的硬性限制另一层是即便引擎不拦你你在设计索引时也必须考虑存储和性能的取舍。网上很多答案是扔一句InnoDB索引键最大767字节就完事这话不能说错但远远不够。它解决不了我在MySQL 8.0、utf8mb4、varchar(255)上建完整索引明明没报错到底还用不用指定前缀长度这类更常见的现实困惑。这篇文章就把这件事彻底说透。我会从你可能会遇到的报错出发讲清楚767字节和3072字节是怎么来的再用一笔一笔的字节账告诉你为什么前缀索引往往更划算最后给出一套区分度测试和验证方法。无论你是在改一张报了Specified key was too long的表还是纯粹想把索引设计得更合理这篇都适用。1. 先分清必须的两个层面引擎硬限制与设计取舍1.1 一个让无数人踩坑的报错很多人第一次接触这个话题都是被一段报错逼来的。在MySQL 5.5或者更早的版本里只要你对一个较长的varchar字段直接建索引大概率会看到这样一句话ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes这句话翻译成人话就是你建的索引键太长了超过了InnoDB允许的最大767字节。于是大家的第一个疑问就是为什么是767字节而不是1024也不是800这其实是个很古老的设计决定。InnoDB从早期版本开始就对索引键长度有物理层面的限制767字节这个数字源于对索引页结构的约束——在COMPACT行格式下一个索引项承载的字节数不能超过这个规格。虽然后来MySQL引入了innodb_large_prefix参数允许把上限扩大到3072字节但767这个数字因为在各类博客和文档里出现太频繁已经成了无数DBA和开发者的集体记忆。这里要特别提醒你在MySQL 5.7和8.0里看到的默认限制并不是767而是3072字节。很多人拿着老文章来问为什么我建索引没报错往往就是版本差异造成的。先把这个概念理清后面所有的计算才不会乱。1.2 字符集和字节数为什么utf8mb4最容易触发限制这里的关键不只是varchar的长度还有字符集。MySQL里varchar(n)的n是字符数不是字节数真正占用多少字节取决于字符集。常见的字符集对应关系是这样的字符集单个字符最大字节数举例latin11字节西欧字符、ASCIIgbk2字节中文简体常用字符utf8mb33字节曾用名utf8支持常用汉字utf8mb44字节完整Unicode含emojiutf8mb4是目前最主流的字符集也是最容易触发索引长度问题的那个。咱们算一笔账一个varchar(255)字段如果采用utf8mb4理论上最多能存255个字符每个字符最多4字节那么最大字节数就是255 × 4 1020字节这已经超过767字节的旧限制所以在老版本里直接对这个字段建完整索引是必然失败的。为了解决这个问题最常见的做法是把索引长度写成191191 × 4 764字节764刚好卡在767字节之内这也是为什么网上老有人说varchar(191)是上限——它其实是在767字节限制和utf8mb4字符集下最长的安全索引字符数。这些数字都不是拍脑袋定的全是从字节限制倒推出来的。1.3 新版MySQL不报错了为什么还要主动指定前缀长度到了MySQL 5.7.7之后innodb_large_prefix默认开启InnoDB的默认行格式也变成了DYNAMIC索引键上限被放宽到3072字节。再回到刚才的例子varchar(255)utf8mb4完整索引需要1020字节远小于3072所以不指定前缀长度数据库也不会报错。问题来了既然不报错为什么大家还习惯性地说必须指定索引长度因为引擎不拦你不代表这么做是合理的。一个varchar(255)的完整索引键本身就要占用1020字节和仅指定varchar(191)甚至更短前缀的索引相比对索引页空间、内存缓冲池、磁盘IO和写入性能的影响完全不同。这才是真正值得深入挖掘的地方也是我接下来要讲的重点。2. 从16KB索引页出发看完整索引 vs 前缀索引的空间账2.1 一条索引记录大概占多少字节InnoDB的索引页默认大小是16KB也就是16384字节。无论是主键索引还是普通二级索引数据最终都存放在这种固定大小的页里。一个页里能塞下多少索引记录直接决定了整个索引的页数量、B树的高度以及查询时要访问多少个页。一条二级索引的记录除了你建的索引键本身之外还会附带不少额外信息主键值如果主键是bigint占8字节记录头信息compact行格式下通常几个字节varchar变长长度的记录1到2字节取决于长度是否超过255字节可空标志位字段允许NULL时有1字节所以一条索引记录的估算大小大概是索引键字节数 主键字节数 十几字节固定开销。我用两个场景对比一下假设主键是bigint字段采用utf8mb4分别建完整索引和前缀索引一页能放多少记录字段定义索引写法索引键字节单条记录估算一页约能放varchar(100)完整索引400约415字节约39条varchar(100)前缀2080约95字节约170条varchar(500)完整索引2000约2015字节约8条varchar(500)前缀100400约415字节约39条这组数字是估算值不同行格式、不同NULL属性会导致细节有出入但量级关系是明确的索引键越大单个索引页能承载的记录越少整个索引占用的页数就越多。2.2 页容量下降如何推高B树高度和磁盘IOB树索引的高度取决于数据量和每个页能记录的数量。假设一张表有1000万条记录如果每个索引页能放39条记录那么索引需要大约25.6万个页如果每个页只能放8条记录则需要125万个页——页数差了将近5倍。页数变多带来的问题有两个。第一B树更容易长高。B树的一个关键特性是只有叶子节点存数据非叶子节点存索引键和指针。如果每页能容纳的记录少树的扇出就低树的层数就可能从3层变成4层。千万不要小看这多一层对InnoDB来说每多一层一次查询就可能多一次磁盘随机IO延迟翻倍都是常事。第二索引页占用的内存缓冲池空间大涨。InnoDB会把热门的索引页缓存在内存里页数越多能缓存的人均页面就越少缓存命中率下降后磁盘IO自然增加。用一个生活中的类比查字典时如果每页只能印8个词条字典就得做得很厚翻页次数多、查找也慢如果能印39个词条字典就薄了很多。指定索引长度这件事本质上就是把字典的每一页尽量填得更满。2.3 前缀索引本质上是粗筛回表精筛这里要打消一个常见的心理障碍很多人总觉得索引里存的数据不完整查询不就出问题了吗。其实不会。前缀索引存的确实是字段的前N个字符但它照样能用于等值匹配、LIKE abc%这种前缀匹配查询。以WHERE nickname 张三丰为例如果索引只存了前两个字符数据库会先在索引里定位所有前缀为张三的记录拿到这些记录的主键再回表读取完整行最后用完整的nickname 张三丰条件把真正的目标行筛出来。所以前缀索引的角色是粗筛它把95%的无关数据挡在索引扫描之外剩下的一小撮候选行回表确认。只要前缀长度选得够聪明回表的行数就不会多。明白这一点之后再去谈怎么选长度才会有底气。3. 前缀索引的适用边界排序、覆盖索引和范围查询的取舍3.1 哪些查询形态会被前缀索引拖累前缀索引虽然省空间但代价是索引里头没有完整数据。以下三类场景会受影响第一类排序分组。ORDER BY nickname或者GROUP BY nickname这种查询数据库需要的是完整字段值的有序序列。前缀索引只能保证前缀有序无法保证完整值有序。举个例子索引前缀是前3个字符记录abcdef和abcxyz会被排在一起但完整字段真正比较时abcdef在前还是abcxyz在前索引根本不知道。所以排序操作往往会退化成文件排序filesort严重时还会动用临时表。第二类覆盖索引。覆盖索引指的是要查的列都在索引里不用回表。如果索引是前缀索引而查询又要返回完整的nickname字段那索引里给不出完整值只能回表取。覆盖索引的优化效果直接就没了。第三类严格范围匹配。WHERE nickname abc需要把索引里所有和完整值比较后大于abc的记录取出来。前缀索引没有完整值没法做这个边界判断优化器就很难用它做高效的范围扫描。3.2 用一张表判断能不能用前缀索引实际开发中判断起来也不复杂把它对应到查询的形态上查询场景完整索引前缀索引等值匹配 nickname abc高效可用可能少量回表前缀匹配 LIKE abc%高效前缀长度充足时可接受后缀匹配 LIKE %abc不可用不可用范围查询 between可用基本走不通ORDER BY / GROUP BY可走索引基本会filesort覆盖索引有机会基本没机会索引体积大小这张表的关键结论是如果你的查询形态以等值匹配和前缀匹配为主前缀索引非常合适如果查询里有排序、分组、范围匹配那就要慎重或者考虑其他方案。3.3 一次真实索引调整带来的教训之前某项目里有个用户表需要按昵称精确查询原表结构里nickname是varchar(255)utf8mb4建的也是完整索引。当时只想着查询快没太在意索引体积后来表数据涨到几千万发现每次写入和更新时索引维护的负担明显变大内存缓冲池也不够用偶尔出现查询抖动。后来做了个调整先用区分度测试算了一下发现前缀16个字符的区分度已经能达到完整字段的99%以上就把索引从完整索引改成了idx_nickname(nickname(16))。改动之后索引体积缩小了一半多等值查询的响应时间反而更稳定了因为每次索引IO要扫描的页更少回表也就是零星几行的事。这个案例说明了一个很实际的原则如果你的业务场景就是精确匹配和前缀匹配短前缀索引往往是比完整索引更优的选择反而是那些你以为存全了肯定更好的完整索引在数据量大起来之后会变成负担。4. 把索引长度选对区分度测试、哈希列与EXPLAIN验证4.1 用区分度测试找到拐点长度指定索引长度不是拍脑袋指定一个数字你需要依据数据分布来决定。判断指标叫区分度简单说就是这个前缀能区分出多少不同的值。在线上库或者测试库执行下面这条SQL就能看出不同前缀长度下的去重表现-- 全字段的去重总数 SELECT COUNT(DISTINCT nickname) AS full_cnt FROM user; -- 不同前缀长度下的去重数 SELECT COUNT(DISTINCT LEFT(nickname, 8)) AS prefix8, COUNT(DISTINCT LEFT(nickname, 12)) AS prefix12, COUNT(DISTINCT LEFT(nickname, 16)) AS prefix16, COUNT(DISTINCT LEFT(nickname, 24)) AS prefix24 FROM user;算法是计算选择性比例某个前缀的去重数除以完整字段的去重数。比如prefix12 / full_cnt 0.97意味着取前12个字符就能保留97%的区分度那这个长度就已经很够用了。数据量特别大的时候全表COUNT(DISTINCT)会让数据库跑很久可以先抽一部分样做近似估算SELECT COUNT(DISTINCT LEFT(nickname, 16)) AS prefix16, COUNT(DISTINCT nickname) AS full_cnt FROM user WHERE id % 100 0;4.2 几个实用的长度选择经验选前缀长度时我一般按这几条经验来先看字段字符集。utf8mb4下长度最好不要超过191如果字段本身需要存很长内容更要主动控制前缀长度。看区分度曲线的拐点。很多字段的前4个字符区分度很低比如固定前缀的业务编号到第12、16个字符的时候区分度突然涨上去再往后增长趋缓。那个涨上去之后开始趋缓的位置就是好选择。不要为了追求极致短而砍得太狠。如果前缀太短区分度掉到80%以下回表行数会暴增性能未必比完整索引好。如果字段本身很短比如varchar(20)且平均长度差不多就是十几个字符那没必要刻意指定前缀直接完整索引也不占太大空间。4.3 等值匹配场景的终极方案哈希索引列有些场景比较极端字段值很长区分度也高无法用较短的字符前缀来替代但查询形态只有等值匹配。这个时候前缀索引就显得不够用更合理的做法是给字段增加一个哈希列然后对哈希列建索引。MySQL 5.7之后支持生成列generated column可以这样设计ALTER TABLE user ADD COLUMN nickname_hash BINARY(16) GENERATED ALWAYS AS (UNHEX(LEFT(SHA2(nickname, 256), 32))) STORED, ADD INDEX idx_nickname_hash (nickname_hash);查询时这样写SELECT * FROM user WHERE nickname_hash UNHEX(LEFT(SHA2(张三丰, 256), 32)) AND nickname 张三丰;用哈希列的索引定位候选行再配合完整字段做精确匹配是为了避免理论上极低概率的哈希冲突。哈希值是固定长度的二进制数据索引体积非常可控而且等值匹配效率极高。代价是它不能用于范围查询和排序所以只适合只用等值匹配的字段。4.4 什么情况下可以不指定长度聊了这么多要指定长度也得说清楚哪些情况可以大大方方不指定字段本身就短比如varchar(20)、varchar(32)在utf8mb4下完整索引最多也就128字节完全在合理范围内。字符集是单字节或双字节比如latin1、gbk同样的字符数字节数小很多。字段有唯一性约束。唯一索引要求对整个字段值保证唯一不能只对前缀建唯一索引否则张三和张三丰会被误判成同一个值。4.5 EXPLAIN里的key_len怎么读建完索引后用EXPLAIN看查询执行计划重点看key_len字段。它显示的是优化器实际使用的索引键字节数可以验证前缀是否真的生效。举例CREATE TABLE user ( id bigint NOT NULL, nickname varchar(50) NOT NULL, PRIMARY KEY (id), KEY idx_nickname (nickname(12)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; EXPLAIN SELECT * FROM user WHERE nickname 张三丰;这次查询的key_len会显示为50。算法是这样的前缀12个字符 × utf8mb4每字符4字节 48再加上varchar变长长度字段2字节 50。如果字段允许NULL通常还会再加1字节。反过来如果这个索引是完整索引key_len会是20250×42。看到50这个数字你就能确认优化器确实在用前缀索引而且用的是前缀12。学会读key_len之后很多为什么我的索引没生效的疑惑都会迎刃而解。5. 字符集、排序规则和767字节迷信5.1 不同字符集下的索引长度天花板既然索引长度限制的本质是字节数那同样一个767字节上限在不同的字符集下能对应的字符数差别很大字符集单字符最大字节767字节上限对应的字符数3072字节上限对应的字符数latin117673072gbk23831536utf8mb332551024utf8mb44191768用utf8mb4的时候191是那个被反复提到的数字用gbk的时候383才是界限。这说明必须指定索引长度并不是一个放之四海而皆准的铁律它完全取决于字符集和版本。5.2 排序规则影响的是比较不是长度还有一些人把排序规则collation和索引长度混在一起其实排序规则只影响字符串怎么比较大小不影响一个字符占几个字节。utf8mb4_general_ci和utf8mb4_0900_ai_ci在索引长度的计算上没有区别都是按utf8mb4的4字节上限算。但排序规则有一点值得注意建索引的字段排序规则一定要和查询条件里的字符串排序规则一致否则会触发隐式转换让索引直接失效。这个坑不常见踩一次却很疼。5.3 别被varchar(255)不能用这种说法带偏现在网上时不时能看到varchar(255)尽量别用的说法其实里面很多讨论都把问题简单化了。varchar(255)本身没问题关键在于你用它做了什么。如果你的列是varchar(255)、字符集是utf8mb4然后直接建完整索引且你的表数据量巨大那索引体积确实会失控。但如果字符集是latin1或者字段本身内容很短、很少被排序那varchar(255)和完整索引也不是什么恐怖故事。真正该养成的习惯是看到一个新的字符串字段要加索引时先算三笔账——字符集的最大字节数、字段的区分度拐点、你的查询形态是否依赖排序和覆盖索引。算完之后要不要指定长度、指定多少答案自然就出来了。最后再分享一个我个人的习惯每次给字符串字段建索引之前无论表大小我都会先跑一遍区分度测试再快速用EXPLAIN看一眼key_len是否符合预期。这套流程看起来很基础但它能帮你躲开大多数索引越界和索引失效的坑。毕竟索引设计这事怕的不是字段长而是不清楚自己到底换来了什么、牺牲了什么。
有人问我一个问题为什么varchar上的索引必须要指定索引长度我第一反应是这问题至少得拆成两层来答一层是MySQL在引擎层面确实有历史包袱留下的硬性限制另一层是即便引擎不拦你你在设计索引时也必须考虑存储和性能的取舍。网上很多答案是扔一句InnoDB索引键最大767字节就完事这话不能说错但远远不够。它解决不了我在MySQL 8.0、utf8mb4、varchar(255)上建完整索引明明没报错到底还用不用指定前缀长度这类更常见的现实困惑。这篇文章就把这件事彻底说透。我会从你可能会遇到的报错出发讲清楚767字节和3072字节是怎么来的再用一笔一笔的字节账告诉你为什么前缀索引往往更划算最后给出一套区分度测试和验证方法。无论你是在改一张报了Specified key was too long的表还是纯粹想把索引设计得更合理这篇都适用。1. 先分清必须的两个层面引擎硬限制与设计取舍1.1 一个让无数人踩坑的报错很多人第一次接触这个话题都是被一段报错逼来的。在MySQL 5.5或者更早的版本里只要你对一个较长的varchar字段直接建索引大概率会看到这样一句话ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes这句话翻译成人话就是你建的索引键太长了超过了InnoDB允许的最大767字节。于是大家的第一个疑问就是为什么是767字节而不是1024也不是800这其实是个很古老的设计决定。InnoDB从早期版本开始就对索引键长度有物理层面的限制767字节这个数字源于对索引页结构的约束——在COMPACT行格式下一个索引项承载的字节数不能超过这个规格。虽然后来MySQL引入了innodb_large_prefix参数允许把上限扩大到3072字节但767这个数字因为在各类博客和文档里出现太频繁已经成了无数DBA和开发者的集体记忆。这里要特别提醒你在MySQL 5.7和8.0里看到的默认限制并不是767而是3072字节。很多人拿着老文章来问为什么我建索引没报错往往就是版本差异造成的。先把这个概念理清后面所有的计算才不会乱。1.2 字符集和字节数为什么utf8mb4最容易触发限制这里的关键不只是varchar的长度还有字符集。MySQL里varchar(n)的n是字符数不是字节数真正占用多少字节取决于字符集。常见的字符集对应关系是这样的字符集单个字符最大字节数举例latin11字节西欧字符、ASCIIgbk2字节中文简体常用字符utf8mb33字节曾用名utf8支持常用汉字utf8mb44字节完整Unicode含emojiutf8mb4是目前最主流的字符集也是最容易触发索引长度问题的那个。咱们算一笔账一个varchar(255)字段如果采用utf8mb4理论上最多能存255个字符每个字符最多4字节那么最大字节数就是255 × 4 1020字节这已经超过767字节的旧限制所以在老版本里直接对这个字段建完整索引是必然失败的。为了解决这个问题最常见的做法是把索引长度写成191191 × 4 764字节764刚好卡在767字节之内这也是为什么网上老有人说varchar(191)是上限——它其实是在767字节限制和utf8mb4字符集下最长的安全索引字符数。这些数字都不是拍脑袋定的全是从字节限制倒推出来的。1.3 新版MySQL不报错了为什么还要主动指定前缀长度到了MySQL 5.7.7之后innodb_large_prefix默认开启InnoDB的默认行格式也变成了DYNAMIC索引键上限被放宽到3072字节。再回到刚才的例子varchar(255)utf8mb4完整索引需要1020字节远小于3072所以不指定前缀长度数据库也不会报错。问题来了既然不报错为什么大家还习惯性地说必须指定索引长度因为引擎不拦你不代表这么做是合理的。一个varchar(255)的完整索引键本身就要占用1020字节和仅指定varchar(191)甚至更短前缀的索引相比对索引页空间、内存缓冲池、磁盘IO和写入性能的影响完全不同。这才是真正值得深入挖掘的地方也是我接下来要讲的重点。2. 从16KB索引页出发看完整索引 vs 前缀索引的空间账2.1 一条索引记录大概占多少字节InnoDB的索引页默认大小是16KB也就是16384字节。无论是主键索引还是普通二级索引数据最终都存放在这种固定大小的页里。一个页里能塞下多少索引记录直接决定了整个索引的页数量、B树的高度以及查询时要访问多少个页。一条二级索引的记录除了你建的索引键本身之外还会附带不少额外信息主键值如果主键是bigint占8字节记录头信息compact行格式下通常几个字节varchar变长长度的记录1到2字节取决于长度是否超过255字节可空标志位字段允许NULL时有1字节所以一条索引记录的估算大小大概是索引键字节数 主键字节数 十几字节固定开销。我用两个场景对比一下假设主键是bigint字段采用utf8mb4分别建完整索引和前缀索引一页能放多少记录字段定义索引写法索引键字节单条记录估算一页约能放varchar(100)完整索引400约415字节约39条varchar(100)前缀2080约95字节约170条varchar(500)完整索引2000约2015字节约8条varchar(500)前缀100400约415字节约39条这组数字是估算值不同行格式、不同NULL属性会导致细节有出入但量级关系是明确的索引键越大单个索引页能承载的记录越少整个索引占用的页数就越多。2.2 页容量下降如何推高B树高度和磁盘IOB树索引的高度取决于数据量和每个页能记录的数量。假设一张表有1000万条记录如果每个索引页能放39条记录那么索引需要大约25.6万个页如果每个页只能放8条记录则需要125万个页——页数差了将近5倍。页数变多带来的问题有两个。第一B树更容易长高。B树的一个关键特性是只有叶子节点存数据非叶子节点存索引键和指针。如果每页能容纳的记录少树的扇出就低树的层数就可能从3层变成4层。千万不要小看这多一层对InnoDB来说每多一层一次查询就可能多一次磁盘随机IO延迟翻倍都是常事。第二索引页占用的内存缓冲池空间大涨。InnoDB会把热门的索引页缓存在内存里页数越多能缓存的人均页面就越少缓存命中率下降后磁盘IO自然增加。用一个生活中的类比查字典时如果每页只能印8个词条字典就得做得很厚翻页次数多、查找也慢如果能印39个词条字典就薄了很多。指定索引长度这件事本质上就是把字典的每一页尽量填得更满。2.3 前缀索引本质上是粗筛回表精筛这里要打消一个常见的心理障碍很多人总觉得索引里存的数据不完整查询不就出问题了吗。其实不会。前缀索引存的确实是字段的前N个字符但它照样能用于等值匹配、LIKE abc%这种前缀匹配查询。以WHERE nickname 张三丰为例如果索引只存了前两个字符数据库会先在索引里定位所有前缀为张三的记录拿到这些记录的主键再回表读取完整行最后用完整的nickname 张三丰条件把真正的目标行筛出来。所以前缀索引的角色是粗筛它把95%的无关数据挡在索引扫描之外剩下的一小撮候选行回表确认。只要前缀长度选得够聪明回表的行数就不会多。明白这一点之后再去谈怎么选长度才会有底气。3. 前缀索引的适用边界排序、覆盖索引和范围查询的取舍3.1 哪些查询形态会被前缀索引拖累前缀索引虽然省空间但代价是索引里头没有完整数据。以下三类场景会受影响第一类排序分组。ORDER BY nickname或者GROUP BY nickname这种查询数据库需要的是完整字段值的有序序列。前缀索引只能保证前缀有序无法保证完整值有序。举个例子索引前缀是前3个字符记录abcdef和abcxyz会被排在一起但完整字段真正比较时abcdef在前还是abcxyz在前索引根本不知道。所以排序操作往往会退化成文件排序filesort严重时还会动用临时表。第二类覆盖索引。覆盖索引指的是要查的列都在索引里不用回表。如果索引是前缀索引而查询又要返回完整的nickname字段那索引里给不出完整值只能回表取。覆盖索引的优化效果直接就没了。第三类严格范围匹配。WHERE nickname abc需要把索引里所有和完整值比较后大于abc的记录取出来。前缀索引没有完整值没法做这个边界判断优化器就很难用它做高效的范围扫描。3.2 用一张表判断能不能用前缀索引实际开发中判断起来也不复杂把它对应到查询的形态上查询场景完整索引前缀索引等值匹配 nickname abc高效可用可能少量回表前缀匹配 LIKE abc%高效前缀长度充足时可接受后缀匹配 LIKE %abc不可用不可用范围查询 between可用基本走不通ORDER BY / GROUP BY可走索引基本会filesort覆盖索引有机会基本没机会索引体积大小这张表的关键结论是如果你的查询形态以等值匹配和前缀匹配为主前缀索引非常合适如果查询里有排序、分组、范围匹配那就要慎重或者考虑其他方案。3.3 一次真实索引调整带来的教训之前某项目里有个用户表需要按昵称精确查询原表结构里nickname是varchar(255)utf8mb4建的也是完整索引。当时只想着查询快没太在意索引体积后来表数据涨到几千万发现每次写入和更新时索引维护的负担明显变大内存缓冲池也不够用偶尔出现查询抖动。后来做了个调整先用区分度测试算了一下发现前缀16个字符的区分度已经能达到完整字段的99%以上就把索引从完整索引改成了idx_nickname(nickname(16))。改动之后索引体积缩小了一半多等值查询的响应时间反而更稳定了因为每次索引IO要扫描的页更少回表也就是零星几行的事。这个案例说明了一个很实际的原则如果你的业务场景就是精确匹配和前缀匹配短前缀索引往往是比完整索引更优的选择反而是那些你以为存全了肯定更好的完整索引在数据量大起来之后会变成负担。4. 把索引长度选对区分度测试、哈希列与EXPLAIN验证4.1 用区分度测试找到拐点长度指定索引长度不是拍脑袋指定一个数字你需要依据数据分布来决定。判断指标叫区分度简单说就是这个前缀能区分出多少不同的值。在线上库或者测试库执行下面这条SQL就能看出不同前缀长度下的去重表现-- 全字段的去重总数 SELECT COUNT(DISTINCT nickname) AS full_cnt FROM user; -- 不同前缀长度下的去重数 SELECT COUNT(DISTINCT LEFT(nickname, 8)) AS prefix8, COUNT(DISTINCT LEFT(nickname, 12)) AS prefix12, COUNT(DISTINCT LEFT(nickname, 16)) AS prefix16, COUNT(DISTINCT LEFT(nickname, 24)) AS prefix24 FROM user;算法是计算选择性比例某个前缀的去重数除以完整字段的去重数。比如prefix12 / full_cnt 0.97意味着取前12个字符就能保留97%的区分度那这个长度就已经很够用了。数据量特别大的时候全表COUNT(DISTINCT)会让数据库跑很久可以先抽一部分样做近似估算SELECT COUNT(DISTINCT LEFT(nickname, 16)) AS prefix16, COUNT(DISTINCT nickname) AS full_cnt FROM user WHERE id % 100 0;4.2 几个实用的长度选择经验选前缀长度时我一般按这几条经验来先看字段字符集。utf8mb4下长度最好不要超过191如果字段本身需要存很长内容更要主动控制前缀长度。看区分度曲线的拐点。很多字段的前4个字符区分度很低比如固定前缀的业务编号到第12、16个字符的时候区分度突然涨上去再往后增长趋缓。那个涨上去之后开始趋缓的位置就是好选择。不要为了追求极致短而砍得太狠。如果前缀太短区分度掉到80%以下回表行数会暴增性能未必比完整索引好。如果字段本身很短比如varchar(20)且平均长度差不多就是十几个字符那没必要刻意指定前缀直接完整索引也不占太大空间。4.3 等值匹配场景的终极方案哈希索引列有些场景比较极端字段值很长区分度也高无法用较短的字符前缀来替代但查询形态只有等值匹配。这个时候前缀索引就显得不够用更合理的做法是给字段增加一个哈希列然后对哈希列建索引。MySQL 5.7之后支持生成列generated column可以这样设计ALTER TABLE user ADD COLUMN nickname_hash BINARY(16) GENERATED ALWAYS AS (UNHEX(LEFT(SHA2(nickname, 256), 32))) STORED, ADD INDEX idx_nickname_hash (nickname_hash);查询时这样写SELECT * FROM user WHERE nickname_hash UNHEX(LEFT(SHA2(张三丰, 256), 32)) AND nickname 张三丰;用哈希列的索引定位候选行再配合完整字段做精确匹配是为了避免理论上极低概率的哈希冲突。哈希值是固定长度的二进制数据索引体积非常可控而且等值匹配效率极高。代价是它不能用于范围查询和排序所以只适合只用等值匹配的字段。4.4 什么情况下可以不指定长度聊了这么多要指定长度也得说清楚哪些情况可以大大方方不指定字段本身就短比如varchar(20)、varchar(32)在utf8mb4下完整索引最多也就128字节完全在合理范围内。字符集是单字节或双字节比如latin1、gbk同样的字符数字节数小很多。字段有唯一性约束。唯一索引要求对整个字段值保证唯一不能只对前缀建唯一索引否则张三和张三丰会被误判成同一个值。4.5 EXPLAIN里的key_len怎么读建完索引后用EXPLAIN看查询执行计划重点看key_len字段。它显示的是优化器实际使用的索引键字节数可以验证前缀是否真的生效。举例CREATE TABLE user ( id bigint NOT NULL, nickname varchar(50) NOT NULL, PRIMARY KEY (id), KEY idx_nickname (nickname(12)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; EXPLAIN SELECT * FROM user WHERE nickname 张三丰;这次查询的key_len会显示为50。算法是这样的前缀12个字符 × utf8mb4每字符4字节 48再加上varchar变长长度字段2字节 50。如果字段允许NULL通常还会再加1字节。反过来如果这个索引是完整索引key_len会是20250×42。看到50这个数字你就能确认优化器确实在用前缀索引而且用的是前缀12。学会读key_len之后很多为什么我的索引没生效的疑惑都会迎刃而解。5. 字符集、排序规则和767字节迷信5.1 不同字符集下的索引长度天花板既然索引长度限制的本质是字节数那同样一个767字节上限在不同的字符集下能对应的字符数差别很大字符集单字符最大字节767字节上限对应的字符数3072字节上限对应的字符数latin117673072gbk23831536utf8mb332551024utf8mb44191768用utf8mb4的时候191是那个被反复提到的数字用gbk的时候383才是界限。这说明必须指定索引长度并不是一个放之四海而皆准的铁律它完全取决于字符集和版本。5.2 排序规则影响的是比较不是长度还有一些人把排序规则collation和索引长度混在一起其实排序规则只影响字符串怎么比较大小不影响一个字符占几个字节。utf8mb4_general_ci和utf8mb4_0900_ai_ci在索引长度的计算上没有区别都是按utf8mb4的4字节上限算。但排序规则有一点值得注意建索引的字段排序规则一定要和查询条件里的字符串排序规则一致否则会触发隐式转换让索引直接失效。这个坑不常见踩一次却很疼。5.3 别被varchar(255)不能用这种说法带偏现在网上时不时能看到varchar(255)尽量别用的说法其实里面很多讨论都把问题简单化了。varchar(255)本身没问题关键在于你用它做了什么。如果你的列是varchar(255)、字符集是utf8mb4然后直接建完整索引且你的表数据量巨大那索引体积确实会失控。但如果字符集是latin1或者字段本身内容很短、很少被排序那varchar(255)和完整索引也不是什么恐怖故事。真正该养成的习惯是看到一个新的字符串字段要加索引时先算三笔账——字符集的最大字节数、字段的区分度拐点、你的查询形态是否依赖排序和覆盖索引。算完之后要不要指定长度、指定多少答案自然就出来了。最后再分享一个我个人的习惯每次给字符串字段建索引之前无论表大小我都会先跑一遍区分度测试再快速用EXPLAIN看一眼key_len是否符合预期。这套流程看起来很基础但它能帮你躲开大多数索引越界和索引失效的坑。毕竟索引设计这事怕的不是字段长而是不清楚自己到底换来了什么、牺牲了什么。
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
