OTTO
Search K

Appearance

怎么给字符串加索引 ​

在笔者的毕设中，使用到了邮箱登录，想到了一个问题，如果用户量比较大，应该怎么加快查询 User 表呢？

假设现在有一个 User 表：

sql
create table User 
(
    id    bigint auto_increment comment 'id' primary key,
    email varchar(256) not null comment '邮箱' unique key,
    name  varchar(256) not null comment '用户昵称',
    ......
) comment '用户表' collate = utf8mb4_unicode_ci;

如果使用邮箱登录，会用到类似的 SQL：

sql
select * from User where email='123@456.com';

如果 email 字段上没有索引，这个语句就只能走全表扫描。为了加快查询效率，自然而然就会想到索引，那么这个索引应该怎么加呢？

两种加索引的方式 ​

MySQL 是支持前缀索引的，即支持定义字符串的一部分作为索引。默认地，如果创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

那么，就有两种加索引的方式：

  • alter table User add index idx1(email);
    • 索引里面包含了每个记录的整个字符串
  • alter table User add index idx2(email(6));
    • 索引里只记录了每个记录的前 6 个字节

如果使用了前缀索引，虽然占用空间小了，但是会增加额外的记录扫描次数。

前缀索引的优化方式 ​

上面提到，前缀索引虽然占用空间小，但是会扫描多行记录。我们可以从扫描的记录行数出发，选择合适的长度，做到既节省空间，有不用额外的查询成本

怎么确定用多长的前缀

我们在建立索引时关注的是区分度，区分度越高越好。可以通过统计索引上有不同的值来判断要使用多长的前缀。

首先算出这个列上有多少不同的值

sql
select count(distinct email) from User

然后依次选取不同长度的前缀来看区分度，比如看一下 4～7 个字节的前缀索引

sql
select 
    count(distinct left(email, 4),
    count(distinct left(email, 5),
    count(distinct left(email, 6),
    count(distinct left(email, 7)
from User

上面的 SQL 语句会得到不同前缀长度的区分度。我们在使用前缀索引不可避免会损失区分度，事先需要预设一个可以接受的值，然后选择满足预期的最小的前缀长度即可。

前缀索引对覆盖索引的影响 ​

假设有这么一条 SQL：

sql
select id, email from User where email='123@456.com'

如果是 idx1，由于使用了 email 整个字符串做索引，因此可以使用覆盖索引，不需要回表查询，从 idx1 查到结果就可以直接返回了。

如果是 idx2，就无法使用覆盖索引了，即使 idx2 上已经包含了所有信息，但是 InnoDB 还是需要到主键索引上再查一下，因为系统不能确定前缀索引上是否截断了完整信息。

其他方式 ​

对于类似于邮箱这样的字段来说，使用前缀索引的效果可能还不错，但是，遇到前缀的区分度不够好的情况，比如身份证号，前缀索引的性能就会下降。

如果索引选取的越长，占用的磁盘空间就越大，相同数据页能放下的索引值就越少，搜索效率也会降低。因此，我们需要一些占用更小空间并且查询效率良好的方法。

倒序存储 ​

把需要存储的字符串倒转过来，用后缀来做索引。

使用这种方法的前提是后缀要有一定的区分度，那么查询的语句就会变为：

sql
select * from t where email = reverse('123@456.com');

使用 hash 字段 ​

在表上再创建一个整数字段，来保存字符串的 hash 值，同时在这个字段上建索引。

在插入时先使用 crc32() 计算一个 hash 值，填到新字段上。由于 hash 值可能存在冲突，在查询时 where 部分要判断字符串的值是否相同，如：

sql
select * from t where crc = '12345678' and email = '123@456.com';

总结

相同点

这两种方式只支持等值查询，不支持范围查询。

不同点

  1. 从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。当然，如果倒序存储方式使用的前缀长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。
  2. 在CPU消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。
  3. 从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

上次更新于: