mysql字符串字段索引优化
字符串索引的问题
我们知道，innodb引擎使用b+tree作为索引的存储方式，每一个b+tree的节点都可以存放非常多的信息，用来减少磁盘IO。如果索引的字段非常短，那么b+tree的每个节点（一般是一张内存页的大小）就能存放更多的信息，b+tree的深度增长就会更好的控制。
当我们需要对一些字符串（varchar）字段进行加索引时，就会出现一些问题。因为字符串长度相对较大，在索引树中不仅会占用更多的空间，同时也会增加存储引擎比较的时间。因此选择在字符串字段上加合理的索引是非常有必要的。
优化方式
前缀索引
减少空间占用
以邮箱为例，类似这样的格式，基本上所有系统都会存储这一字段，并且这一字段一般使用频率较高，为邮箱字段加合适的索引无疑是非常有必要的。
我们分析邮箱的格式，一般情况下，前面的部分区分度（可以理解为不一样的程度）较高，而后半部分则区分度较低。因此，我们可以尝试使用mysql的前缀索引。
看下面这个例子：
我们的数据库创建这样的结构：
CREATE TABLE `test`.`users` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NULL,
`email` VARCHAR(100) NULL,
PRIMARY KEY (`id`));其中有这样的数据：
{id:1,name:'n1',email:'abcd1234@163.com'}
{id:2,name:'n2',email:'ahahahag@qq.com'}
{id:3,name:'n3',email:'axixix@163.com'}
{id:4,name:'n4',email:'woshishabi@163.com'}
{id:5,name:'n5',email:'oopoom@ss.com'}首先，针对email这个字段，我们使用方案1：使用email全字段索引。
alter table users add index ie1(email)此时我们执行以下语句：，执行流程如下：
从ie1索引树中寻找满足索引值为的行，取得id为5的行
回表，到聚集索引id上，判断email正确，将数据加入结果中
继续检查ie1索引，发现下一行已经不满足条件，扫描结束。
在上述的流程中，innodb扫描了一行。
如果这些邮箱使用前缀索引，
alter table users add index ie2(email(6))索引中存储的就变成了：
abcd12
ahahah
axixix
oopoom
woshis依然执行上面的sql语句，得到如下的流程。
从ie2索引树中寻找满足索引值为的行，取得id为5的行
回表，到聚集索引id上，判断email正确，将数据加入结果中
继续检查ie2索引，发现下一行已经不满足条件，扫描结束。
在上述的流程中，innodb还是扫描了一行。但是索引中存储的数据占用空间已经大幅度下降了。因此，使用前缀索引可以帮助我们减少索引空间占用。
增加回表次数
但是前缀索引也是有缺陷的。刚刚的案例是建立在数据最前面一部分区分度大的情况下的。因为前缀索引只保存了数据的最前面一部分，因此当最前面这部分区分度不大的时候，就会增加回表次数。
看下面这个例子，我们的数据库中有这样的一些邮箱：
123456@163.com
123456@aa.com
123456@bb.com
123456@cc.com
123456@qq.com这是一个极端的例子，这些邮箱的前缀区分度非常差，如果按照刚刚使用的的索引，甚至所有数据在索引中都是一模一样的！简单分析不难得到，我们如果要查询这个例子，在innodb中要经过五次回表！
影响覆盖索引
在我们平常的开发中有时会用到覆盖索引来优化性能，比如下面这条语句。
select id,email from users where email="123456@qq.com";因为我们查询的数据和，在ie1索引中全部存在，所以会触发覆盖索引，避免回表，直接返回数据，而不用再回到聚簇索引中去再查一次。
而我们如果使用了ie2（email(6)）这个索引的话，在索引中是没法确定数据的，因此无法避免回表。也就是说，如果使用了前缀索引，那么我们就无法再利用覆盖索引了。
前缀索引总结
因此，“前缀索引”并不是银弹，它有自身的缺陷在。我们需要权衡利弊，综合空间占用和回表次数以及对覆盖索引的影响，来选择最合适的方式。
倒序存储
有时候我们的数据并不像email这样，前缀部分区分度高。而可能是后半部分区分度高。
比如学号。以这个学号为例：。前面的2021是入学年份，中间的8888是学员号，后面的01是专业号，最后面的354是学生在该专业中的编号。
这个例子是典型的数据后半部分区分度高。对于这样的数据，我们该怎么建索引呢？是否可以有所谓的“后缀索引呢”？可惜的是，mysql并不支持后缀索引。
我们可以采用一种小窍门，把学号字段倒序(reverse)存储，即为存储的时候，直接使用该字段reverse过的值。查询的时候将输入反转之后再去查询。这样我们就可以使用前缀索引了。
这样的方式除了前缀索引所有的缺点之外，还有以下两个缺点。
每次查询、修改数据都需要reverse，消耗更多的cpu资源
无序，不能范围查找
自定义哈希字段
我们知道，innodb使用的是b+tree索引。如果能使用hash来进行访问，那么查询效率会高一些。
还是以刚刚的邮箱为例子。我们删除所有在邮箱上的索引，建立一个新的int值字段，hash_email字段，并给该字段加索引。
然后每次插入新记录的时候，都同时用 crc32() 这个函数得到的哈希值填到这个新字段。接下来查找的时候，就可以通过以下语句进行查找。
select name from users where hash_email=crc32("123456@qq.com");这样，由于该字段是int型变量，所以占用空间非常小。我们可以非常快速的找到想要的单条记录。
由于哈希可能存在冲突，也就是说两个不同的email通过 crc32() 函数得到的结果可能是相同的，所以你的查询语句 where 部分要判断email的值是否精确相同，即为使用下面这条语句：
select name from users where hash_email=crc32('123456@qq.com') and email='123456@qq.com';大家都知道hash查询很快，但它并不是没有缺点。相比起上述的两种方式，自定义hash字段这种方式有以下特点：
只支持等值查询，无法范围查询
多一个字段的存储空间，当然这一点上，由于存储的是int值（或更大的big int），所以空间占用并不大
需要手动维护hash值。该方式可以通过添加触发器来简化。
这种方式同样需要消耗cpu资源，因此我们不要使用sha1()和md5()这种非常消耗资源的函数作为哈希函数。
当数据量大时，可能会产生比较大的哈希冲突，因此我们可以采用自己重写一个hash算法，来扩大哈希桶的范围（对应的哈希字段可以使用big int）
总结
我们分析了四种针对于字符串字段加索引的方式（包括全字段索引），四种方式各有优劣，实际应用中需要酌情考虑。
参考：极客时间《mysql实战45讲》、《高性能mysql》