11 | 怎么给字符串字段加索引？

HigginCui
145 阅读7分钟

假设现在维护一个邮箱登录系统，用户表的定义如下：

mysql> create table SUser(
    ID bigint unsigned primary key,
    email varchar(64),
    ...
)engine=innodb;

由于使用邮箱登录，业务查询代码如下：

select f1, f2 from SUser where email='xxx';

  • 如果email这个字段上没有索引，那么这个语句只能做全表扫描。

  • MySql支持前缀索引，可以定义字符串的一部分作为索引，如果不指定前缀长度，那么索引会包含整个字符串。

mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));

第一个语句创建的index1索引，包含了每个记录的整个字符串。
第二个语句创建的index2索引，对于每个记录只取前6个字节。

image.png

image.png

使用email(6)这种方式的前缀索引：

  • 好处：只取前6个字节，这样占用的空间会更小，这就是使用前缀索引的优势。
  • 缺点：可能会增加额外的记录扫描次数以及回表次数。

判断这个语句的执行细节：

select id,name,email from SUser where email='zhangssxyz@xxx.com';

若使用index1(即eamil整个字符串的索引结构)，执行顺序如下：

  1. 从index1索引树找到满足索引值"zhangssxyz@xxx.com"这条记录，取得主键值=ID2的值。
  2. 到主键索引上查到主键值是ID2的行，判断email的值是否正确，将这行记录加入结果集。
  3. 取index1索引树上刚刚查询到的位置的下一条记录，发现已经不满足email="zhangssxyz@xxx.com'的条件了，循环结束。

【总结】：使用index1索引，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

如果使用的是index2(即email(6)索引结构)，执行顺序是如下：

  1. 从index2索引树找到满足索引值是"zhangs"的记录，找到的第一个是ID1。
  2. 到主键上朝招主键值是ID1的行，判断email的值不是"zhangssxyz@xxx.com",这行记录丢弃。
  3. 取index2上刚刚查到的位置的下一条记录，发现仍然是"zhangs"，取出ID2，再到ID索引上取整行然后判断，这次值对了，将这行记录加入结果集。
  4. 重复上一步，直到在index2上取到的值不是"zhangs"时，循环结束。

【总结】：使用index2要回主键索引取4次数据，即扫描了4行。

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

建立索引时，查看区分度

  • 使用下面语句，算出这个列上多列少个不同值：
mysql> select count(distinct email) as L from SUser;
  • 依次选取不同长度的前缀来看这个值，比如我们要看一下 4~7 个字节的前缀索引，可以用这个语句：
mysql> select
     count(distinct left(email,4)）as L4,
     count(distinct left(email,5)）as L5,
     count(distinct left(email,6)）as L6,
     count(distinct left(email,7)）as L7,
     from SUser;

使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如 5%。然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为 6。

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

前缀索引需要回表。

select id,email from SUser where email='zhangssxyz@xxx.com';

select id,name,email from SUser where email='zhangssxyz@xxx.com';

如果使用 index1（即 email 整个字符串的索引结构）的话，可以利用覆盖索引，从index1 查到结果后直接就返回了，不需要回到 ID 索引再去查一次。而如果使用index2（即 email(6) 索引结构）的话，就不得不回到 ID 索引再去判断 email 字段的值。

即使你将 index2 的定义修改为 email(18) 的前缀索引，这时候虽然 index2 已经包含了所有的信息，但 InnoDB 还是要回到 id 索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信息。

其他方式

有没有什么既可以占用更小的空间，也能达到相同的查询效率的方法？

1.倒序存储

如果你存储身份证号的时候把它倒过来存，每次查询的时候， 你可以这么写：

select field_list from t where id_card = reverse('input_id_card_string');

由于身份证号的最后 6 位没有地址码这样的重复逻辑，所以最后这 6 位很可能就提供了足够的区分度。当然了，实践中你不要忘记使用 count(distinct) 方法去做个验证。

2.使用 hash 字段

可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。
然后每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。

mysql> select field_list from t 
        where id_card_crc=crc32('input_id_card_string') 
        and id_card='input_id_card_string'

使用倒序存储和使用 hash 字段这两种方法的异同点

相同点：

  • 都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引方式查出身份证号码在 [ID_X, ID_Y] 的所有市民了。同样地，hash 字段的方式也只能支持等值查询。

区别，主要三个方面：

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

小结

  1. 直接创建完整索引，这样可能比较占用空间；

  2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；

  3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；

  4. 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，

都不支持范围扫描。

avatar
文章
阅读
粉丝