Mysql如何给字符串添加索引(前缀索引)

在日常开发中，我们经常给字符串添加索引，那么给字段添加索引有什么技巧吗，我们看看下面的例子，我们给一个邮箱添加索引，应该如何添加呢

看看下面这条sql

select *  from user where email ='[email protected]'

如果我们不添加索引，肯定是要进行全表扫描的，那么我们如何添加呢有两种方式

alter table user add index index1(email)
alter table user add index index2(email(6))

上面两种方式都是在添加索引，不同点就是第二种添加的仅仅是邮箱的前缀索引，那么他的结构表现如下图

ndex2每个索引中仅仅存储了字段的前6个字符，而index1存储了整个字符串,我们分别在不同索引下执行下面的语句

select id,name,email  from user where email='[email protected]'

如果使用index1,他的执行过程如下

在index1中找到符合条件的记录，获取id=5
然后使用id=1,在主键索引上获取整行记录
在index1寻找下一条记录，直到发现不满足位置，循环结束
如果使用index2,他的执行过程如下

在index2中找到符合条件的值，获取到id=1
然后在主键索引中找到id=1的数据，发现不符合
在index2继续寻找，id=2,然后在主键上找到行记录，发现不符合
循环上面步骤，直到符合的记录
我们发现使用index2虽然占用的空间小，但是他对搜索的性能没有更好的提高，而是要多扫描几行记录。

是不是发现前缀索引不是很好呢，然而并不是的，我们如果把索引的长度修改成8，然后在index2中仅仅找到一条符合记录，因此只需要扫描一行就够了。

于是，我们发现选择合适的前缀长度，既可以节省空间，也可以不用增加更多的查询成本.

参考