写点什么

mysql 字符串字段索引优化

发布于: 2021 年 01 月 13 日
mysql字符串字段索引优化

字符串索引的问题

我们知道,innodb 引擎使用 b+tree 作为索引的存储方式,每一个 b+tree 的节点都可以存放非常多的信息,用来减少磁盘 IO。​如果索引的字段非常短,那么 b+tree 的每个节点(一般是一张内存页的大小)就能存放更多的信息,b+tree 的深度增长就会更好的控制。


当我们需要对一些字符串(varchar)字段进行加索引时,就会出现一些问题。因为字符串长度相对较大,在索引树中不仅会占用更多的空间,同时也会增加存储引擎比较的时间。因此选择在字符串字段上加合理的索引是非常有必要的。


优化方式


前缀索引


减少空间占用

以邮箱为例,类似xxxxxx@aaaa.com这样的格式,基本上所有系统都会存储这一字段,并且这一字段一般使用频率较高,为邮箱字段加合适的索引无疑是非常有必要的。


我们分析邮箱的格式,一般情况下,前面的部分区分度(可以理解为不一样的程度)较高,而后半部分则区分度较低。因此,我们可以尝试使用 mysql 的前缀索引


看下面这个例子:

我们的数据库创建这样的结构:

CREATE TABLE `test`.`users` (  `id` INT NOT NULL AUTO_INCREMENT,  `name` VARCHAR(45) NULL,  `email` VARCHAR(100) NULL,  PRIMARY KEY (`id`));
复制代码

其中有这样的数据:

{id:1,name:'n1',email:'abcd1234@163.com'}{id:2,name:'n2',email:'ahahahag@qq.com'}{id:3,name:'n3',email:'axixix@163.com'}{id:4,name:'n4',email:'woshishabi@163.com'}{id:5,name:'n5',email:'oopoom@ss.com'}
复制代码

首先,针对 email 这个字段,我们使用方案 1:使用 email 全字段索引。

alter table users add index ie1(email)
复制代码

此时我们执行以下语句:select name from users where email='oopoom@ss.com',执行流程如下:


  • 从 ie1 索引树中寻找满足索引值为'oopoom@ss.com'的行,取得 id 为 5 的行

  • 回表,到聚集索引 id 上,判断 email 正确,将数据加入结果中

  • 继续检查 ie1 索引,发现下一行已经不满足条件,扫描结束。


在上述的流程中,innodb 扫描了一行。


如果这些邮箱使用前缀索引iemail(email(6))

alter table users add index ie2(email(6))
复制代码

索引中存储的就变成了:

abcd12ahahahaxixixoopoomwoshis
复制代码

依然执行上面的 sql 语句,得到如下的流程。

  • 从 ie2 索引树中寻找满足索引值为'oopoom'的行,取得 id 为 5 的行

  • 回表,到聚集索引 id 上,判断 email 正确,将数据加入结果中

  • 继续检查 ie2 索引,发现下一行已经不满足条件,扫描结束。


在上述的流程中,innodb 还是扫描了一行。但是索引中存储的数据占用空间已经大幅度下降了。因此,使用前缀索引可以帮助我们减少索引空间占用。


增加回表次数

但是前缀索引也是有缺陷的。刚刚的案例是建立在数据最前面一部分区分度大的情况下的。因为前缀索引只保存了数据的最前面一部分,因此当最前面这部分区分度不大的时候,就会增加回表次数。


看下面这个例子,我们的数据库中有这样的一些邮箱:

123456@163.com123456@aa.com123456@bb.com123456@cc.com123456@qq.com
复制代码

这是一个极端的例子,这些邮箱的前缀区分度非常差,如果按照刚刚使用的email(6)的索引,甚至所有数据在索引中都是一模一样的!简单分析不难得到,我们如果要查询123456@qq.com这个例子,在 innodb 中要经过五次回表!


影响覆盖索引

在我们平常的开发中有时会用到覆盖索引来优化性能,比如下面这条语句。

select id,email from users where email="123456@qq.com";
复制代码

因为我们查询的数据idemail,在 ie1 索引中全部存在,所以会触发覆盖索引,避免回表,直接返回数据,而不用再回到聚簇索引中去再查一次。


而我们如果使用了 ie2(email(6))这个索引的话,在索引中是没法确定数据的,因此无法避免回表。也就是说,如果使用了前缀索引,那么我们就无法再利用覆盖索引了。


前缀索引总结


因此,“前缀索引”并不是银弹,它有自身的缺陷在。我们需要权衡利弊,综合空间占用和回表次数以及对覆盖索引的影响,来选择最合适的方式。


倒序存储

有时候我们的数据并不像 email 这样,前缀部分区分度高。而可能是后半部分区分度高。


比如学号。以这个学号为例:2021888801354。前面的 2021 是入学年份,中间的 8888 是学员号,后面的 01 是专业号,最后面的 354 是学生在该专业中的编号。


这个例子是典型的数据后半部分区分度高。对于这样的数据,我们该怎么建索引呢?是否可以有所谓的“后缀索引呢”?可惜的是,mysql 并不支持后缀索引。


我们可以采用一种小窍门,把学号字段倒序(reverse)存储,即为存储的时候,直接使用该字段 reverse 过的值。查询的时候将输入反转之后再去查询。这样我们就可以使用前缀索引了。


这样的方式除了前缀索引所有的缺点之外,还有以下两个缺点。

  • 每次查询、修改数据都需要 reverse,消耗更多的 cpu 资源

  • 无序,不能范围查找


自定义哈希字段

我们知道,innodb 使用的是 b+tree 索引。如果能使用 hash 来进行访问,那么查询效率会高一些。


还是以刚刚的邮箱为例子。我们删除所有在邮箱上的索引,建立一个新的 int 值字段,hash_email 字段,并给该字段加索引。


然后每次插入新记录的时候,都同时用 crc32() 这个函数得到的哈希值填到这个新字段。接下来查找的时候,就可以通过以下语句进行查找。

select name from users where hash_email=crc32("123456@qq.com");
复制代码

这样,由于该字段是 int 型变量,所以占用空间非常小。我们可以非常快速的找到想要的单条记录。


由于哈希可能存在冲突,也就是说两个不同的 email 通过 crc32() 函数得到的结果可能是相同的,所以你的查询语句 where 部分要判断 email 的值是否精确相同,即为使用下面这条语句:

select name from users where hash_email=crc32('123456@qq.com') and email='123456@qq.com';
复制代码


大家都知道 hash 查询很快,但它并不是没有缺点。相比起上述的两种方式,自定义 hash 字段这种方式有以下特点:

  • 只支持等值查询,无法范围查询

  • 多一个字段的存储空间,当然这一点上,由于存储的是 int 值(或更大的 big int),所以空间占用并不大

  • 需要手动维护 hash 值。该方式可以通过添加触发器来简化。

  • 这种方式同样需要消耗 cpu 资源,因此我们不要使用 sha1()和 md5()这种非常消耗资源的函数作为哈希函数。

  • 当数据量大时,可能会产生比较大的哈希冲突,因此我们可以采用自己重写一个 hash 算法,来扩大哈希桶的范围(对应的哈希字段可以使用 big int)


总结


我们分析了四种针对于字符串字段加索引的方式(包括全字段索引),四种方式各有优劣,实际应用中需要酌情考虑。


最后,欢迎大家来逛一逛我的博客:鹊君


参考:极客时间《mysql 实战 45 讲》、《高性能 mysql》


用户头像

还未添加个人签名 2020.10.24 加入

还未添加个人简介

评论

发布
暂无评论
mysql字符串字段索引优化