如何给字符串添加索引

有一张教师表，表定义如下：

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

讲师要使用邮箱登录，所以业务代码中一定会出现类似于这样的语句：

select col1, col2 from teacher where email='xxx';

如果email这个字段上没有索引，那么这个语句就只能做全表扫描 。

1. 前缀索引

MySQL是支持前缀索引的。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字 符串。

alter table teacher add index index1(email);

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

这两种不同的定义在数据结构和存储上有什么区别呢？下图就是这两个索引的示意图。

• 如果使用的是index1（即email整个字符串的索引结构），执行顺序是这样的：

  1. 从index1索引树找到满足索引值是zhangssxyz@xxx.com的这条记录，取得ID2的值；
  2. 到主键上查到主键值是ID2的行，判断email的值是正确的，将这行记录加入结果集；
  3. 取index1索引树上刚刚查到的位置的下一条记录，发现已经不满足email='zhangssxyz@xxx.com ’的 条件了，循环结束。
     这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

• 如果使用的是index2（即email(6)索引结构），执行顺序是这样的：

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

也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。前面已经讲过区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。

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

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

select id,email from teacher where email='test@xxx.com';

如果使用index1(即emai整个字符串的索引结构)的话，可以利用覆盖索引，从index.1查到结果后直接就返回
了，不需要回到D索引再去查一次。而如果使用index:2(即email(6)索引结构)的话，就不得不回到ID索引再去判断emails字段的值。

即使你将index2的定义修改为email(18)的前缀索引，这时候虽然index2已经包含了所有的信息，但InnoDB还是要回到索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信息。

结论： 使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引 时需要考虑的一个因素。