MySQL深入09-字符串字段加索引
# MySQL深入-字符串字段加索引
现在假设我们要对用户表的邮箱字段加索引，在没加索引之前，对于邮箱的条件查询是需要走全表扫描的。为了提高查询的效率，我们需要在邮箱字段上加索引。
同时，MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。
mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));
- 对于第一条语句，创建的index1索引包含了邮箱整个字符串
- 对于第二条语句，创建的index2索引只包含了邮箱的前6个字节。
那么这两条语句创建索引有什么区别呢？
在之前我们学习到，非主键索引上除了记录主键索引的值，还会存储该索引对应字段的数据。由于 email(6) 这个索引结构中每个邮箱字段都只取前 6 个字节（即：zhangs），所以占用的空间会更小，这就是使用前缀索引的优势。
但这个存在一个问题：可能会增加额外的扫描次数。以下面这条SQL语句为例，我们来分析一下两种索引的执行情况：
select id,name,email from SUser where email='zhangssxyz@xxx.com';
使用Index1
- 从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得 ID2 的值；
- 到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；
- 取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email='zhangssxyz@xxx.com’的条件了，循环结束。
使用Index2
- 从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1；
- 到主键上查到主键值是 ID1 的行，判断出 email 的值不是’zhangssxyz@xxx.com’，这行记录丢弃；
- 取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；
- 重复上一步，直到在 idxe2 上取到的值不是’zhangs’时，循环结束。
两者的差异主要原因就是邮箱前缀为zhangs的有很多，所以如果我们要在字符串字段加索引的话，除了使用前缀索引来降低其存储的空间，还需要考虑前缀索引的长度，好的长度可以做到既节省空间，又不用额外增加太多的查询成本。
如何确定前缀索引的长度
索引最关键的就是区分度，区分度越高，重复的键值就越少
我们可以通过distinct来算出这个列上有多少个不同的值，然后根据可以接受的损失区分度来预先设定一个可以接受的损失比例，最后通过前缀索引进行截取，找到适合长度的索引。
比如现在有100个邮箱，我能接受的损失是5%，那么我就需要找到一个适合的索引长度来区别至少95个邮箱。
# 前缀索引对覆盖索引的影响
覆盖索引是一种解决回表的方式。现在有如下的语句：
select id,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 索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信息。
# 其他方式
如果对于身份证这种前缀区分度不够好的情况，该如何进行处理呢？
为了增加区分度，我们可能就需要把前缀索引的长度增长，但是增长就意味着存储的空间会越多，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。
方法一：倒序存储
select field_list from t where id_card =reverse('input_id_card_string');
由于身份证号的最后 6 位没有地址码这样的重复逻辑，所以最后这 6 位很可能就提供了足够的区分度。当然了，实践中你不要忘记使用 count(distinct) 方法去做个验证。
方法二：使用Hash字段
可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。
然后每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。
select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'
这样，索引的长度变成了 4 个字节，比原来小了很多。
对于倒序存储和Hash字段他们都是不支持范围查询的。
- 倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引方式查出身份证号码在[ID_X, ID_Y]的所有市民了。
- 同样地，hash 字段的方式也只能支持等值查询。
两者的区别：
- 占用空间上，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。
- CPU消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。
- 查询效率上，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。
# 小结
字符串字段创建索引：
- 直接创建完整索引，这样可能比较占用空间；
- 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
- 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
- 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。