mysql5.7官网直译SQL语句优化--索引条件压入优化

1.5index Condition Pushdown Optimization(索引条件压入优化)
索引条件压入(ICP)是一个优化对于mysql从表中使用索引来检索行时。没有ICP，存储引擎遍历索引定位表中的行数据，然后返回数据到mysql服务器评估WHERE条件对于行数。如果ICP可以用，如果where条件的部分可以只使用索引列来做出评估，mysql服务压这部分满足where条件的数据入存储引擎。存储引擎评估压入的索引条件通过使用索引entry并且如果满足条件则读取行数据从表中。ICP能够减少数倍时间从存储引擎到基本表，同样的从mysql服务到存储引擎也是提高几倍效率。
索引条件压入优化的使用条件如下：
1）ICP被使用，当range,ref,eq_ref,ref_or_null方法需要用到全表扫描时。
2）ICP能够被用到innoDB和myisam表中，包括分区表也可以。
3）对于InnoDB表，ICP使用只是第二索引。ICP的目标是减少全行数据读取数量，从而减少I/O操作。对于innoDB集群索引，完整的记录以及被缓存到innoDB的缓存中了，在这种情况下使用ICP不会减少I/O.
4) ICP不支持在虚拟生成列上的第二索引。innoDB支持在虚拟生成的列上创建第二索引。
5）在子查询中的条件不能被压入
6）存储函数的条件不能被压入，存储引擎不能激活存储函数。
7）触发器条件不能压入。(关于触发条件的信息，请看8.2.2.4优化子查询通过存在的策略)
为了理解这些优化是怎么样工作的，首先要思考的是一个索引扫描过程是怎么样完成的在不使用索引条件压入的情形下。
1）获取下一行，首先读取索引元组，然后通过索引元组定位并读取表中的完整行。
2）测试where条件中的部分在这张表中，接受或者拒绝行基于测试结果。
通过使用索引条件压入，扫描过程通过这样代替：
1）获取下一行的索引元组(但没有完整的行扫描)
2）测试在这张表中使用的where条件部分并只用索引列来核对。如果条件不满足，继续处理下一行的索引元组。
3）如果条件满足，通过索引元组定义表中行的位置，并读取完整的行。
4）测试依然留下的在这张表中使用where条件部分的数据。接受或者拒绝基于测试结果的行数据。
EXPLAIN输出中会有Using index condition在Extra列中，当索引条件压入被使用。当必须要全表扫描时，则不会展示Using index，因为根本不会使用索引。
假设一个表中包含了一个人和他的地址信息，并且在表中定义了一个索引INDEX(zipcode,lastname,firstname).如果你知道一个人的zipcode值，但是不确定last name,我们可以像这样查找：
SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';
mysql能够使用索引扫描通过人的zipcode='95054'。第二部分(lastname like '%etrunia%')不能限制必须要扫描的行数，所以没有索引条件压入，这个查询必须扫描全部行对于全部人的zipcode='95054'的数据。
通过索引条件压入，MYSQL核对lastname like '%etrunia%'部分在获取全表数据之前。这通过匹配zipcode条件的索引元组但不满足lastname条件从而过避免全表扫描。
索引压入默认可以使用，我们完全可以通过optimzier_switch系统变量来设置index_conditon_pushdown标志开启或者关闭：
SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';
具体请看8.9.3的选择优化项。
到此索引条件压入优化就结束了，接下来我们要看的是1.6的嵌套循环连接的算法。