给字符串加索引

mysql

MySQL是支持前缀索引的，前缀索引的优势就是占用的空间小，这同时带来的损失是，可能会增加额外的记录扫描次数。

比如一些用户表，登录账户是邮箱

如果要使用的是邮箱登录，所以代码中一定会有这种类似的语句

1
select f1, f2 from tableName where email='xxx';

如果email这个字段上没有索引的话，那这些语句就只能做全表扫描

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

比如，这两个在 email 字段上创建索引的语句：

1
2
alter table t add index index1(email);
alter table t add index index2(email(6));

第一个语句创建的 index1 索引里面，包含了每个记录的整个字符串；

而第二个语句创建的 index2 索引里面，对于每个记录都是只取前 6 个字节。

其中email(6)这个索引结构中每个邮箱字段只取前6个字节，占用的空间会比较小，这是使用前缀索引的优势,但是带来的损失可能会增加额外的记录扫描次数

看看下面这个语句

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

如果使用的是这种索引index1（即 email 整个字符串的索引结构），执行顺序是这样的：

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

这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

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

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

在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行。

所以使用前缀索引有可能会使查询语句读数据的次数变多

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

如果我们能够确定业务需求里面只有按照身份证进行等值查询的需求，这种方法，既可以占用更小的空间，也能达到相同的查询效率。

有以下2中方式

  1. 就是使用倒序存储，比如身份证倒序，查询的时候再用函数转一下
  2. 以及使用hash字段，在表上创建一个整数字段，来保存身份证的校验码，同时在这个字段上加索引

这两种方式对比区别

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