MySQL09-如何给字符串加索引-前缀索引

现在， 几乎所有的系统都支持邮箱登录， 如何在邮箱这样的字段上建立合理的索引， 是我们今天要讨论的问题。

假设， 你现在维护一个支持邮箱登录的系统， 用户表是这么定义的

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

由于要使用邮箱登录， 所以业务代码中一定会出现类似于这样的语句：

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

从第4和第5篇讲解索引的文章中， 我们可以知道， 如果email这个字段上没有索引， 那么这个语句就只能做全表扫描。

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

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

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

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

那么， 这两种不同的定义在数据结构和存储上有什么区别呢？ 如图2和3所示， 就是这两个索引的示意图。

从图中你可以看到， 由于email(6)这个索引结构中每个邮箱字段都只取前6个字节（即：zhangs） ， 所以占用的空间会更小， 这就是使用前缀索引的优势。

但， 这同时带来的损失是， 可能会增加额外的记录扫描次数。

接下来， 我们再看看下面这个语句， 在这两个索引定义下分别是怎么执行的。

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

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

1. 从index1索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录， 取得ID2的值；
2. 到主键上查到主键值是ID2的行， 判断email的值是正确的， 将这行记录加入结果集；
3. 取index1索引树上刚刚查到的位置的下一条记录， 发现已经不满足
   email='zhangssxyz@xxx.com’的条件了， 循环结束。
   这个过程中， 只需要回主键索引取一次数据， 所以系统认为只扫描了一行。

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

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

通过这个对比， 你很容易就可以发现， 使用前缀索引后， 可能会导致查询语句读数据的次数变多。也就是说使用前缀索引， 定义好长度， 就可以做到既节省空间， 又不用额外增加太多的查询成本。

于是， 你就有个问题： 当要给字符串创建前缀索引时， 有什么方法能够确定我应该使用多长的前缀呢？

如何给字符串创建合适长度的前缀索引？

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

1、首先， 你可以使用下面这个语句， 算出这个列上有多少个不同的值

mysql> select count(distinct email) as L from SUser;

2、然后， 依次选取不同长度的前缀来看这个值， 比如我们要看一下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

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

前面我们说了使用前缀索引可能会增加扫描行数（类似模糊查询）， 这会影响到性能。 其实， 前缀索引的影响不止如此， 我们再看一下另外一个场景。

你先来看看这个SQL语句：

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

与前面例子中的SQL语句：

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

相比， 这个语句只要求返回id和email字段。

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

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

也就是说， 使用前缀索引就用不上覆盖索引对查询性能的优化了（因为使用覆盖索引是不去会表的，而前缀索引必定要回表）， 这也是你在选择是否使用前缀索引时需要考虑的一个因素。

其他方式

对于类似于邮箱这样的字段来说， 使用前缀索引的效果可能还不错。 但是， 遇到前缀的区分度不够好的情况时， 我们要怎么办呢？

比如， 我们国家的身份证号， 一共18位， 其中前6位是地址码， 所以同一个县的人的身份证号前6位一般会是相同的。

假设你维护的数据库是一个市的公民信息系统， 这时候如果对身份证号做长度为6的前缀索引的话， 这个索引的区分度就非常低了。因为一个县的居民很多。

按照我们前面说的方法， 可能你需要创建长度为12以上的前缀索引， 才能够满足区分度要求。

但是， 索引选取的越长， 占用的磁盘空间就越大， 相同的数据页能放下的索引值就越少， 搜索效率也就会越低。

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

第一种方式是使用倒序存储。 如果你存储身份证号的时候把它倒过来存， 每次查询的时候， 你可以这么写：

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

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

第二种方式是使用hash字段。 你可以在表上再创建一个整数字段， 来保存身份证的校验码，同时在这个字段上创建索引。

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

然后每次插入新记录的时候， 都同时用crc32()这个函数得到校验码填到这个新字段。

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