如何优雅的给字段加索引,能引导优化器走索引?-技术圈

比较喜欢的一段话：不经一番寒彻骨，怎得梅花扑鼻香。这篇文章的是向丁奇老师学习的！阅读这篇文章大概需要20分钟！

大家好前面我们大概了解了MySQL为什么会选错索引。今天介绍一下如何巧妙的给字符串字段加索引提高查询性能。

现在几乎所有的系统都支持，邮箱快捷登录，一些大型的国企网站还会支持身份证登录。比如软考官网，银行APP等。那么如何给这个字段建立合理的索引，达到最优性能呢？

先做一些准备工作

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

由于要使用邮箱登录，那么必不可少的验证语句如下

select ID, email from SUser where email='xxx';

学MySQL的应该都清楚，如果不给email建立索引的话，那么SUser这个表扫描的时候肯定会全表扫描的，我们可以模拟一下。typy=ALL就是最好的证明

图1 没有索引的情况下

那么接下来，我们该如何抉择，索引方案，其他方案。

索引方案

前缀索引(index2)

前缀索引这个还是比较熟悉的，下面这句代码就是给email字段加前6个字节的索引。

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

下面介绍一下加前6个字节的结构图

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

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

听起来如果比较迷糊的话，我们举一个例子，执行顺序如下

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

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

在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行。

直接加索引(index1)

这个是最普通的一种方式了，下面这句代码就是给email字段加索引。在查询的时候每个记录的整个字段串都会捞出来。

alter table SUser add index index1(email);

下面介绍一下直接加索引的结构图

从图中可以看出，每个email索引结构中全部取了出来即：zhangsh1234@xxx.com），索引树上占用的空间大于前缀索引，索引树过大，页上的数据就会缩小。

听起来比较迷，我们可以举一个例子。执行顺序如下。

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

从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得 ID2 的值；
到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；
取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email='zhangssxyz@xxx.com’的条件了，循环结束。

这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

其他方案

倒叙存储

倒叙存储这种方式的话是从存储层面解决索引的问题。那么就必须在设计系统的时候考虑到这一点。

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

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

如果想准确的区分每个数值，那就必须要建立一个长度为12的身份证号字段才能满足要求。索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。

根据性能问题，建立一个长度足够大的方案肯定是不行的。我们这里的解决方式就是倒叙存储，这也是倒叙存储方式的由来。

先来顿骚操作带你上SQL，如下图，前几位是市区编号，后几位才是我们容易区分的号码，所以利用倒叙存储的方式把身份证反过来。在设置存储的时候就可以设置 身份证号字段(6) 取前6个字节来查询啦。

hash字段

接倒叙存储为例。新增一个hash字段。通过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个字节，比原来小了很多。

方案优缺点

前缀索引与直接加索引

结合以上例子，前缀索引和直接加索引两种方案。前缀索引会导致读数据的次数变多。但是，对于这个查询语句来说，如果你定义的 index2 不是 email(6) 而是 email(7），也就是说取 email 字段的前 7 个字节来构建索引的话，即满足前缀’zhangss’的记录只有一个，也能够直接查到 ID2，只扫描一行就结束了。

增上所述：使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

那么问题就出现了，如何确保索引的长度呢？也就是说我们怎么得知要加多少个长度的索引呢？

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

上代码，一顿骚操作。如下代码就是查询出SUser这个表中一共有多少条数据，并且有多少条不同的值。根据当前SQL可以判断出表是否可以加前缀索引。

select count(*) as count,count(distinct email) as L from SUser;

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

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

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

倒叙存储与hash字段

首先，它们的相同点是，都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引方式查出身份证号码在[ID_X, ID_Y]的所有市民了。同样地，hash 字段的方式也只能支持等值查询。

它们的区别，主要体现在以下三个方面：

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

小结

上文例子我大概讲述了每钟方案的原因，利弊，对性能的影响。总结一下今天的几个核心点

直接创建完整索引，这样可能比较占用空间；
创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。