MySQL之字符串索引
次访问
引子
一个邮箱登录的例子。
如果支持使用邮箱登录，一个思路就是：在用户表里根据邮箱筛选到一些字段信息，然后对其进行验证（最常见的就是验证密码是否正确）。
所以会出现下面一行语句：
select username,password from user where email='xxxxx@xxx.com‘
如果此时 email 字段上没有建立索引，则只能进行全盘扫描，如果用户表比较庞大的话，效率会急剧降低。
如果需要优化，则下一步自然想到的就是：加索引。在 MySQL 中，给字符串加索引，可以使用两种方式：给 email 字段内容部分加或者全部加，即所谓全串索引和前缀索引。
全串索引
全串索引，是对整个字符串加索引。
其结构图如下所示：
这种效果可以通过使用下面的命令来实现：
alter table user add index index1(email)
前缀索引
前缀索引，顾名思义，即对字符串的部分加索引.
上图是对 email的前 6 个字节加索引后的结构。
可以使用下面的命令实现：
alter table user add index index2(email(6))
对比
对比全局索引和前缀索引的结构，我们可以得出一个结论：如果前缀索引所占的空间更小，但是同时会引起更多的扫描损失；与之相反，全串索引所占的空间更大，但是扫描次数会少一点。
还是那个结论：没有最好的方法，只有最适用的场景。
对于两种看似互相矛盾的思路，我们还有一个词： trade-off。
我们努力找到适中的点，可以做到索引的树空间大小也在可以接受的范围内，同时对磁盘的扫描次数也不会太多。
平衡点
如何找平衡点呢？
前面的文章介绍，我们建立索引的时候，关注的是区分度。
我们可以使用下面的命令查看一列有多少个不同的值：
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%。
如果有多个都满足，则尽量选择长度最小的那个。
我们在实际的使用场景中，同样还需要考虑另外一个场景：前缀索引对覆盖索引的影响。
在前面的文章中讲到覆盖索引的概念，简单来说就是：我们想要的数据都可以在索引树中查到，而无需回表查询。
但是，使用了前缀索引之后，在全串索引可以覆盖索引的情形下，前缀索引却不能做到。
这个不能实现覆盖索引的损失，我们在权衡时也需要考虑进来。
前缀索引的优化
前缀索引区分度不好，但是使用全串索引又占用空间太大，这个时候怎么办呢？
还是要使用前缀索引，只是我们需要作出一点改变，原来前缀索引区分度不高，我们经过一点处理，让它的区分度变高。
常见的处理方式：
倒序存储
这种方法适用于从前往后区分度不高，但是从后往前区分度却很高的场景，比如身份证号。
hash
在表上再建立一个字段，用来存放这个字段的 hash 值，同时给这个字段加索引。
每次新插入一行记录时，我们都要对这个字段进行 hash 处理，将 hash 值存储到这个新字段中。
这两种方法做个比较下异同点：
相同点：
都不支持范围查询。
不同点：
- 倒序存储的方式不会占用额外的存储空间，而 hash 的方式却需要另外建立一个字段存储，消耗了额外的空间。
- 倒序存储每次读和写的时候，都要调用一次 reverse 函数，hash 的方法每次都要调用 crc32 函数，从这两个函数的复杂度来看， hash 方法消耗更多的 CPU 资源
- crc32 函数计算出来的值冲突概率较小，每次扫描的行数近似为 1，而倒序存储的方式或者前缀的方式扫描行数比较多。相比起来， 对字段进行 hash 的方法查询效率更为稳定一些。
（全文完）
参考资料
- 丁奇 《MySQL 45讲》