vlambda博客
学习文章列表

看小说的都知道17K，背八股的都知道MySQL的16K。

MySQL一个页的大小是16K，决定了索引列的长度。

我们先分析一下到底影响了什么，然后再举例看实际场景里是怎么规避这些影响的。

一、索引长度受限

1.字节和字符

现在我们的项目大多数都是用utf8编码，这种编码方式下，1个字符可能占1/2/3/4个字节。

在MySQL里常用的有 UTF-8 和 UTF-8mb4。

其中 UTF-8mb4 支持 emoji 表情。

2.索引长度和层高

假如要在一个 varchar 类型的列上建索引，MySQL为了保证无论什么字符都有空间，会以每个字符的最大可能长度来计算字节个数。

• 如果是 UTF-8mb4编码，那么每个字符占4字节

• 如果是 UTF-8编码，那么每个字符占3字节

假设我们有个 varchar(50) 的字段，该字段上有索引（普通索引或者唯一索引）。那么这个索引的长度就是 150byte 或 200byte。

MySQL一页也就 16KByte，按 200Byte计算的话， 16*1024/200≈80。也就是说一页也就只能存储80个节点。

MySQL的索引结构是 B+Tree，一个页一个节点可以存80条记录。数据再大就要分层：

• 2层可以存 80*80条记录

• 3层可以存 80*80*80条记录

以此类推，存100万数据就要4层了，而正常情况下一个 bigint 的索引，3层都能存储两千万条了。

而且这是只理想状态，毕竟我们还没有计算主键长度。

层高意味着索引查询速度，所以索引越短越好。

3.MySQL的索引长度限制

以上是理想计算情况，MySQL还有话要讲。

如果我们在一个过长的、且为 UTF-8mb4 编码的字段创建索引时，很可能遇到MySQL的报错：Specifiedkey was toolong;max key lengthis1000bytes

因为MySQL限制了单字段最大索引长度，不同的版本限制长度还不一样。

不仅限制了单个字段索引长度，还限制了联合索引的长度。

当然MySQL有明确的限制长度，但是记录这个没什么必要。

难道是为了卡阈值建索引吗？大可不必。

因为但凡开始思考这个问题，就说明索引已经过长了。

二、长字段怎么建索引

长字段存到MySQL中，在数据量很大的情况下，我们是绝不会用范围查找，或者模糊匹配的。因为性能太差了。所以往往需要的是精确查找。有如下几种办法：

• 对数据特征值建索引

• 建立前缀索引

• 对字段拆分

1.对数据特征值建索引

我们可以新建一列，用来保存计算出来的计算的特征值，对特征值列建索引。

网上大多数推荐的是用 crc32，这个计算快，而且计算出的长度短，不需要处理直接就能用。

但是crc32 有个缺点就是很容易冲突，只适合普通索引。建普通索引时，还需要进行后续的匹配。

如果要创建唯一索引，我们可以对数据做md5或者sha1（sha1就是git在用的）。散列后的特征值很长，但我们只需要取前面几位就能保证唯一性。

保守一点的可以取前15位，激进一点的前10位就够了。

毕竟据说有一个世界级的git代码库，只用到了特征值的前7位就能区分所有的提交了。

2.建立前缀索引

建立前缀索引可以减少索引长度，而且支持范围查找。

但是只适合前缀区分度高的字段，比如：email。而身份证号和url就不适合用前缀索引。

而且如果使用前缀索引，查询时必然要回表，无法支持 cover index。

3.对字段拆分

如果散列特征值和前缀索引都不可用。就只能把字段拆分了。甚至拆分还可以结合其他方法。

这个就需要按业务见招拆招了。

比如要保存url，可以分为：

• schema: http/https

• host: www.baidu.com

• path: /user/login

• queryParams: ?a=1&b=1

这种情况下 schema、host、path重复程度很高，可以替换成具体的数字——相当于自定义的hash计算特征值。

然后再将特征值与queryParams做联合索引。