数据库极客时间-mysql索引
Source
Edit
History

如何给字符串加索引

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

举个例子，我们的数据库表中有一个字段存储的邮箱地址，类型是字符串。

1
2
3
4
alter table t add index index_1(email);

// 对于每个记录只是取前 6 个字节作为索引
alter table t add index index_2(email(6));

前缀索引只取了字符串的一部分字节，因此索引占用的内存空间会变小，这是使用前缀索引的优势。但是可能会增加额外的记录扫描次数，因为可能多个字符串索引的前缀是相同的。

因此，使用前缀索引，定义好一个合理的长度。就可以做到既节省内存，又不用额外增加太多的查询成本。

一、如何建立字符串前缀索引

我们在建立索引时需要关注区分度，区分度越高，意味着重复的键值就越少。我们可以通过 distinct 来查看某个列上有多少个不同的值，以及不同前缀长度的这个列上的不同值的数量。

1
2
3
4
5
6
select count(distinct email) as L from table_name;
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 table_name;

当然，使用前缀索引可能会损失区分度，所以需要预先设定一个可以接受的损失比例。

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

对于覆盖索引来说，从索引中即可获取到所需的信息，不需要再通过主键索引再查一次。

但如果是前缀索引的话，就用不上覆盖索引对于查询性能的优化，还是需要通过主键索引多查一次。

三、优化方式

倒序存储

对于不同的场景，比如前缀重复的较多，而后缀重复的较少，可以使用倒序存储，也就是存储的时候使用倒序存储，查询的倒序查询。

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

当然做之前最好使用 count(distinct) 方法去做验证。

增加辅助字段（hash的方式）

假设我们要查询的是字段A。可以在表上增加一个整数字段，来保存字段 A 的校验码，同时在这个整数字段上创建索引。

然后我们每次插入新记录的时候，都计算校验码（比如 crc32）填到这个整数字段。

由于校验码可能相同，而引起冲突。因为我们在查询字段 A 时，需要精确匹配字段 A。

1
select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string';

这样的话，索引的长度就变小了很多。

以上方法的对比

  • 从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间；而增加辅助字段 方式需要增加一个字段。当然，倒序存储方式，也需要建立前缀索引，前缀索引的长度可能大于辅助字段的长度，可能存在额外的存储空间。
  • 从 CPU 消耗来看，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 增加辅助字段方式则需要调用 生成校验码的算法函数（比如 crc32）。相比来说，reverse 函数的 cpu 消耗会小一点。
  • 从查询效率上来看，使用 辅助字段的方式的查询性能相对更稳定一点。虽然校验码有冲突的概率，但是概率非常小。可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用前缀索引的方式，也就是说还是会增加扫描行数。

四、小结

对于字符串字段创建索引的场景，我们可以使用的方式有：

  • 直接创建完整索引，这样可能比较占空间
  • 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引
  • 倒序存储后，再创建前缀索引，可以绕过字符串本身前缀的区分度不够的问题。还不支持范围扫描，因为倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引查询范围了。
  • 增加辅助字段，并创建辅助字段索引的方式，查询性能稳定，但有额外的存储和计算消耗。还不支持范围扫描，因为校验字段是无序的，只能支持等值查询。