本文整理自网络，侵删。
假设，你现在维护一个支持邮箱登录的系统，用户表是这么定义的：
create table SUser( ?ID bigint unsigned primary key, ?email varchar(64), ?... ?)engine=innodb;
由于要使用邮箱登录，所以业务代码中一定会出现类似于这样的语句：
select f1, f2 from SUser where email='xxx';
如果 email 这个字段上没有索引，那么这个语句就只能做全表扫描。
1）那我可以在邮箱地址这个字段上面建索引吗？
MySQL 是支持前缀索引的，可以定义字符串的一部分作为索引
2）如果创建索引的语句不指定前缀长度，那么会怎么样？
索引就会包含整个字符串
3）能举例来说明一下吗？
alter table SUser add index index1(email); ?或 ?alter table SUser add index index2(email(6));
index1 索引里面，包含了每个记录的整个字符串
index2 索引里面，对于每个记录都是只取前 6 个字节
4）这两种不同的定义在数据结构和存储上有什么区别呢？
明显看出email(6) 这个索引结构占用的空间会更小
5）email(6) 这个索引结构有什么缺点吗？
可能会增加额外的记录扫描次数
6）下面这个语句，在这两个索引定义下分别是怎么执行的？
select id,name,email from SUser where email='zhangssxyz@xxx.com';
index1（即 email 整个字符串的索引结构），执行顺序
从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得 ID2 的值；
回表查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；
继续在index索引树的下一条记录，发现已经不满足 email='zhangssxyz@xxx.com’的条件了，循环结束。
这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。
index2（即 email(6) 索引结构），执行顺序
从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1；
到主键上查到主键值是 ID1 的行，判断出 email 的值不是’zhangssxyz@xxx.com’，这行记录丢弃；
取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；
重复上一步，直到在 idxe2 上取到的值不是’zhangs’时，循环结束。
在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行。
7）通过上面的对比，能得出什么结论？
使用前缀索引后，可能会导致查询语句读数据的次数变多。
8）前缀索引真的一无是处吗？
如果我们定义的 index2 不是 email(6) 而是 email(7），那满足前缀’zhangss’的记录只有一个，直接就查到 ID2了，只扫描一行就结束了。
9）那么使用前缀索引有哪些注意事项？
长度选择合理
10）当要给字符串创建前缀索引时，我咋知道我该用多长的前缀索引呢？
统计索引上有多少个不同的值来判断要使用多长的前缀。
11）怎样统计索引上有多少个不同的值？
select count(distinct email) as L from SUser;
12）拿到了索引对应的有多少个不同的值之后下一步该做什么？
依次选取不同长度的前缀来看这个值
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;
然后，在 L4~L7 中，找出第一个不小于 L * 95% 的值，说明通过这个索引可以找出百分之95以上的数据。
13）前缀索引对覆盖索引的影响是什么？
相关阅读 >>
更多相关阅读请进入《mysql》频道 >>
数据库系统概念 第6版
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。
