第十一讲笔记——怎么给字符串字段加索引

以一个邮箱登陆为例，用户表是这么定义的：

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

那么一定会出现这样的语句：

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

前缀索引

MySQL 支持前缀索引，也就是，你可以定义字符串的一部分作为索引。如果你在创建时不指定长度，则默认包含整个字符串。比如：

alter table SUser add index index1(email);
alter table SUser add index index2(enail(6));

第一句创建的 index1 中，包含了邮箱的整个字段；而第二句创建的 index2 中，只包含邮箱的前 6 个字节。

以下就是两个索引的区别：

index2 所占用的空间更小，但会增加额外的扫描次数。

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

༼ つ ◕_◕ ༽つ但怎么确定我要用多长的呢？

我们主要关注区分度。我们可以用一下语句算出这个列上有多少不同的值：

select count(distinct email) as L from SUser;

然后依次选取不同的前缀看这个值，比如看一下 4 ~ 7 个字节的前缀索引：

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'

对于这条语句，如果用 index1（即 email 整个字符串的索引结构）的话，可以用覆盖索引，这样就不用回表。

如果用 index2（email(6) 的索引），就必须得回到 ID 索引去判断 email 字段的值。

哪怕用一个 email(18) 的索引，还是要会 ID 索引去看一下。因为 MySQL 不知道前缀索引是否截断了完整信息。

前缀索引就用不上覆盖索引的查询优化了。

其他方式

如果是一个市身份证呢？（注：一个市的身份证，前面 6 位基本相同）

如果创建一个 12 位的前缀索引，所占空间太大了。有什么办法呢？

倒序存储

存储时，将身份证号倒过来存，每次查询时这么写：

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

会用到 reverse() 函数。由于后六位没有地址码这样重复逻辑，所以可以用前缀索引。

但别忘了使用 count(distinct) 方法做验证。

hash字段

在表上在创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

alter table t add id_card_crc int unsigned, add index(id_card_crc);

这样在插入时，使用 crc32() 这个函数得到校验码填入。不过有可能存在冲突，所以不仅要判断校验码，还要判断 id_card 是否相同。

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

这样索引长度就只要 4 个字节。

这两种方式的异同点

相同点：都不支持范围查询，只能等值查询。

不同点：

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