9.如何给字符串添加索引

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

create table teacher(
ID bigint unsigned primary key,
email varchar(64),
…
)engine=innodb;
1
2
3
4
5
讲师要使用邮箱登录，所以业务代码中一定会出现类似于这样的语句：

mysql> select col1, col2 from teacher where email=‘xxx’;
1
如果 email 这个字段上没有索引，那么这个语句就只能做全表扫描。

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

mysql> alter table teacher add index index1(email);

或

mysql> alter table teacher add index index2(email(6));
1
2
3
（2）这两种不同的定义在数据结构和存储上有什么区别呢？下图就是这两个索引的示意图。

（3）如果使用的是 index1（即 email 整个字符串的索引结构），执行顺序是这样的：
① 从 index1 索引树找到满足索引值是 ‘zhangssxyz@xxx.com’ 的这条记录，取得 ID2 的值；
② 到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；
③ 取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email = ‘zhangssxyz@xxx.com’ 的条件了，循环结束。

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

（4）如果使用的是 index2（即 email(6) 索引结构），执行顺序是这样的：
① 从 index2 索引树找到满足索引值是 ’zhangs’ 的记录，找到的第一个是ID1；
② 到主键上查到主键值是 ID1 的行，判断出email的值不是 ’zhangssxyz@xxx.com‘，这行记录丢弃；
③ 取 index2 上刚刚查到的位置的下一条记录，发现仍然是 ”zhangs‘，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；
④ 重复上一步，直到在 indxe2 上取到的值不是 ‘zhangs’ 时，循环结束。

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

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