mysql 字符串字段索引优化
字符串索引的问题
我们知道,innodb 引擎使用 b+tree 作为索引的存储方式,每一个 b+tree 的节点都可以存放非常多的信息,用来减少磁盘 IO。如果索引的字段非常短,那么 b+tree 的每个节点(一般是一张内存页的大小)就能存放更多的信息,b+tree 的深度增长就会更好的控制。
当我们需要对一些字符串(varchar)字段进行加索引时,就会出现一些问题。因为字符串长度相对较大,在索引树中不仅会占用更多的空间,同时也会增加存储引擎比较的时间。因此选择在字符串字段上加合理的索引是非常有必要的。
优化方式
前缀索引
减少空间占用
以邮箱为例,类似xxxxxx@aaaa.com
这样的格式,基本上所有系统都会存储这一字段,并且这一字段一般使用频率较高,为邮箱字段加合适的索引无疑是非常有必要的。
我们分析邮箱的格式,一般情况下,前面的部分区分度(可以理解为不一样的程度)较高,而后半部分则区分度较低。因此,我们可以尝试使用 mysql 的前缀索引。
看下面这个例子:
我们的数据库创建这样的结构:
其中有这样的数据:
首先,针对 email 这个字段,我们使用方案 1:使用 email 全字段索引。
此时我们执行以下语句:select name from users where email='oopoom@ss.com'
,执行流程如下:
从 ie1 索引树中寻找满足索引值为
'oopoom@ss.com'
的行,取得 id 为 5 的行回表,到聚集索引 id 上,判断 email 正确,将数据加入结果中
继续检查 ie1 索引,发现下一行已经不满足条件,扫描结束。
在上述的流程中,innodb 扫描了一行。
如果这些邮箱使用前缀索引iemail(email(6))
,
索引中存储的就变成了:
依然执行上面的 sql 语句,得到如下的流程。
从 ie2 索引树中寻找满足索引值为
'oopoom'
的行,取得 id 为 5 的行回表,到聚集索引 id 上,判断 email 正确,将数据加入结果中
继续检查 ie2 索引,发现下一行已经不满足条件,扫描结束。
在上述的流程中,innodb 还是扫描了一行。但是索引中存储的数据占用空间已经大幅度下降了。因此,使用前缀索引可以帮助我们减少索引空间占用。
增加回表次数
但是前缀索引也是有缺陷的。刚刚的案例是建立在数据最前面一部分区分度大的情况下的。因为前缀索引只保存了数据的最前面一部分,因此当最前面这部分区分度不大的时候,就会增加回表次数。
看下面这个例子,我们的数据库中有这样的一些邮箱:
这是一个极端的例子,这些邮箱的前缀区分度非常差,如果按照刚刚使用的email(6)
的索引,甚至所有数据在索引中都是一模一样的!简单分析不难得到,我们如果要查询123456@qq.com
这个例子,在 innodb 中要经过五次回表!
影响覆盖索引
在我们平常的开发中有时会用到覆盖索引来优化性能,比如下面这条语句。
因为我们查询的数据id
和email
,在 ie1 索引中全部存在,所以会触发覆盖索引,避免回表,直接返回数据,而不用再回到聚簇索引中去再查一次。
而我们如果使用了 ie2(email(6))这个索引的话,在索引中是没法确定数据的,因此无法避免回表。也就是说,如果使用了前缀索引,那么我们就无法再利用覆盖索引了。
前缀索引总结
因此,“前缀索引”并不是银弹,它有自身的缺陷在。我们需要权衡利弊,综合空间占用和回表次数以及对覆盖索引的影响,来选择最合适的方式。
倒序存储
有时候我们的数据并不像 email 这样,前缀部分区分度高。而可能是后半部分区分度高。
比如学号。以这个学号为例:2021888801354
。前面的 2021 是入学年份,中间的 8888 是学员号,后面的 01 是专业号,最后面的 354 是学生在该专业中的编号。
这个例子是典型的数据后半部分区分度高。对于这样的数据,我们该怎么建索引呢?是否可以有所谓的“后缀索引呢”?可惜的是,mysql 并不支持后缀索引。
我们可以采用一种小窍门,把学号字段倒序(reverse)存储,即为存储的时候,直接使用该字段 reverse 过的值。查询的时候将输入反转之后再去查询。这样我们就可以使用前缀索引了。
这样的方式除了前缀索引所有的缺点之外,还有以下两个缺点。
每次查询、修改数据都需要 reverse,消耗更多的 cpu 资源
无序,不能范围查找
自定义哈希字段
我们知道,innodb 使用的是 b+tree 索引。如果能使用 hash 来进行访问,那么查询效率会高一些。
还是以刚刚的邮箱为例子。我们删除所有在邮箱上的索引,建立一个新的 int 值字段,hash_email 字段,并给该字段加索引。
然后每次插入新记录的时候,都同时用 crc32() 这个函数得到的哈希值填到这个新字段。接下来查找的时候,就可以通过以下语句进行查找。
这样,由于该字段是 int 型变量,所以占用空间非常小。我们可以非常快速的找到想要的单条记录。
由于哈希可能存在冲突,也就是说两个不同的 email 通过 crc32() 函数得到的结果可能是相同的,所以你的查询语句 where 部分要判断 email 的值是否精确相同,即为使用下面这条语句:
大家都知道 hash 查询很快,但它并不是没有缺点。相比起上述的两种方式,自定义 hash 字段这种方式有以下特点:
只支持等值查询,无法范围查询
多一个字段的存储空间,当然这一点上,由于存储的是 int 值(或更大的 big int),所以空间占用并不大
需要手动维护 hash 值。该方式可以通过添加触发器来简化。
这种方式同样需要消耗 cpu 资源,因此我们不要使用 sha1()和 md5()这种非常消耗资源的函数作为哈希函数。
当数据量大时,可能会产生比较大的哈希冲突,因此我们可以采用自己重写一个 hash 算法,来扩大哈希桶的范围(对应的哈希字段可以使用 big int)
总结
我们分析了四种针对于字符串字段加索引的方式(包括全字段索引),四种方式各有优劣,实际应用中需要酌情考虑。
最后,欢迎大家来逛一逛我的博客:鹊君
参考:极客时间《mysql 实战 45 讲》、《高性能 mysql》
评论