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";

因为我们查询的数据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() 这个函数得到的哈希值填到这个新字段。接下来查找的时候，就可以通过以下语句进行查找。

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》