9.2 前缀索引对覆盖索引的影响
前面我们说了使用前缀索引可能会增加扫描行数，这会影响到性能。其实，前缀索引的影响不止如此，我们再看一下另外一个场景：
如果使用 index1（即 email 整个字符串的索引结构）的话，可以利用覆盖索引，从 index1 查到结果后直接就返回了，不需要回到 ID 索引再去查一次。而如果使用 index2（即 email(6) 索引结构）的话，就不得不回到 ID 索引再去判断 email 字段的值。
即使你将 index2 的定义修改为 email(18) 的前缀索引，这时候虽然 index2 已经包含了所有的信息，但 InnoDB 还是要回到 id 索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信息。
select id,email from teacher where email='songhongkangexxx.com';
结论:
使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。
9.3 拓展内容
对于类似于邮箱这样的字段来说，使用前缀索引的效果可能还不错。但是，遇到前缀的区分度不够好的情况时，我们要怎么办呢?
比如，我们国家的身份证号，一共 18 位，其中前 6 位是地址码，所以同一个县的人的身份证号前 6 位一般会是相同的。
假设你维护的数据库是一个市的公民信息系统，这时候如果对身份证号做长度为 6 的前缀索引的话，这个索引的区分度就非常低了。按照我们前面说的方法，可能你需要创建长度为 12 以上的前缀索引，才能够满足区分度要求。
但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。
那么，如果我们能够确定业务需求里面只有按照身份证进行等值查询的需求，还有没有别的处理方法呢?这种方法，既可以占用更小的空间，也能达到相同的查询效率。有!
**第一种方式是使用倒序存储。**如果你存储身份证号的时候把它倒过来存，每次查询的时候：
mysql> select field list from teacher where id_card=reverse(input_id_card_string);
由于身份证号的最后 6 位没有地址码这样的重复逻辑，所以最后这 6 位很可能就提供了足够的区分度。当然，实践中你还要使用 count(distinct) 方法去做验证。
**第二种方式是使用 hash 字段。**你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。
mysql> alter table teacher add id_card_crc int unsignedadd index(id_card_crc);
然后每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段，由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。
mysql> select field list from twhere id_card_rc=crc32(input_id_card_string) and id_card=input id_card_string
这样，索引的长度变成了4个字节，比原来小了很多。
从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些，因为 crc32 算出来的值虽然有冲突的概率但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。
10. 索引下推
Index Condition Pushdown(ICP) 是 MySQL 5.6 中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP 可以减少存储引擎访问基表的次数以及 MySQL 服务器访问存储引擎的次数。
10.1 使用前后对比
在不使用 ICP 索引扫描的过程：
storage 层：只将满足 index key 条件的索引记录对应的整行记录取出，返回给 server 层
server 层：对返回的数据，使用后面的 where 条件过滤，直至返回最后一行。
使用 ICP 扫描的过程：
storage层：首先将 index key 条件满足的索引记录区间确定，然后在索引上使用 index filter 进行过滤。将满足的 index filter 条件的索引记录才去回表取出整行记录返回 server 层。不满足 index filter 条件的索引记录丢弃，不回表、也不会返回 server 层。
server 层：对返回的数据，使用 table filter 条件做最后的过滤。
使用前后的成本差别：
使用前，存储层多返回了需要被 index filter 过滤掉的整行记录
使用 ICP 后，直接就去掉了不满足 index filter 条件的记录，省去了他们回表和传递到 server 层的成本。
ICP 的加速效果取决于在存储引擎内通过 ICP 筛选掉的数据的比例。
索引中包含这个字段，但是没有使用到这个字段的索引(比如‘%a%’)，却可以使用这个字段在索引中进行条件过滤，从而减少回表的记录条数，这种情况就叫做索引下推
10.2 ICP 的开启、关闭
默认情况下启用索引条件下推。可以通过设置系统变量 optimizer_switch 控制 indexcondition_pushdown
#关闭索引下推 SET optimizer_switch=index_condition_pushdown=off; #打开索引下推 SET optimizerswitch=indexcondition_pushdown=on;
- 当使用索引条件下推时，
EXPLAIN语句输出结果中Extra列内容显示为Using index condition
10.3 ICP 使用案例
为该表定义联合索引 zip_last_first(zipcode, lastname, firstname) 。如果我们知道了一个人的邮编，但是不确定这个人的姓氏，我们可以进行如下检索:
SELECT * FROM people WHERE zipcode= '000001' AND lastname LIKE '%张%' AND address LIKE '%北京市%';
执行查看SQL的查询计划，Extra 中显示了Using index condition ，这表示使用了索引下推。即：先使用索引的zipcode字段进行匹配，然后 索引下推 使用lastname字段进行过滤，最后再进行回表。
另外，Using where表示条件中包含需要过滤的非索引列的数据，即address LIKE '%北京市%'这个条件并不是索引列，需要在服务端过滤掉。
11.4 开启和关闭ICP的性能对比
结果如下：
多次测试效率对比来看，使用ICP优化的查询效率会好一些。这里建议多存储一些数据效果更明显。
10.5 ICP 的使用条件
只能用于二级索引（secondary index）
explain 显示的执行计划中 type 值（join 类型）为 range 、 ref 、 eq_ref 或者 ref_or_null 。
并非全部 where 条件都可以用 ICP 筛选，如果 where 条件的字段不在索引列中，还是要读取整表的记录到 server 端做 where 过滤。
ICP 可以用于 MyISAM 和 InnnoDB 存储引擎
MySQL 5.6 版本的不支持分区表的 ICP 功能，5.7 版本的开始支持。
当 SQL 使用覆盖索引时，不支持 ICP 优化方法。
11. 普通索引 vs 唯一索引
在不同的业务场景下，应该选择普通索引，还是唯一索引？
假设你在维护一个居民系统，每个人都有一个唯一的身份证号，而且业务代码已经保证了不会写入两个重复的身份证号。如果居民系统需要按照身份证号查姓名：
select name from CUser where id_card='xxxxxxxyyyyyyzzzzz';
所以，你一定会考虑在 id_card 字段上建索引。
由于身份证号字段比较大，不建议把身份证号当做主键。现在有两个选择，要么给 id_card 字段创建 唯一素引，要么创建一个普通索引。如果业务代码已经保证了不会写入重复的身份证号，那么这两个选择逻辑上都是正确的。
你知道的，InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB中，每个数据页的大小默认是16KB
从性能的角度考虑，你选择唯一索引还是普通索引呢？选择的依据是什么呢？
假设，我们有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引，假设字段 k 上的值都不重复。 这个表的建表语句是:
mysql> create table test( id int primary key, k int not null, name varchar(16), index (k) )engine=InnoDB;
表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)。
