Extra出现的Using index condition到底什么意思

我们兴致勃勃地建了一张InnoDB表，想测试一下索引的性能，对表中的聚簇索引肆意妄为，对id执行各种查询，玩得不亦乐乎。

这时候，你突发奇想，在表上建了个二级索引，执行了一下查询，发现Extra列里面出现了你从未见过、书上也没讲到的Using index condition。

你慌了，到网上找答案，找到的都是类似这样的东西：

你明白了，可是当你写了一个索引字段的范围查询后，面对着Extra中再次出现的Using index condition，又一次陷入了沉思······

其实，这个问题就是MySQL的版本问题，老版本中没有使用这样的优化算法，所以在老板本书上都很难发现。

Using index condition的出现，意味着MySQL在执行这条语句时使用了Index Condition Pushdown Optimization(ICP)，我愿意称之为”索引条件下压优化“。

这个优化的具体操作是这样的：

（先放一张MySQL层次图便于后面理解）

ICP用于当MySQL想要使用索引并且需要遍历表中的所有行的时候。如果没有ICP，存储引擎就会遍历通过索引锁定的行，并把这些行从表中返回到服务端，在服务端进行where条件的应用。

如果可以使用ICP，如果where条件可以被用于索引中存储的列，那么服务端就会将这一部分where条件压入存储引擎。然后存储引擎会在索引列上针对where条件进行筛选，只有某个索引列的值满足了条件，那么它背后所代表的行才能被读取。由此可见，ICP可以减少存储引擎访问存储系统（大概率是I/O操作）的次数和服务端访问存储引擎的次数。

Using index condition的出现，也就是ICP的使用情况，有以下几点注意事项：

1. 当type为range,ref,eq_ref,ref_or_null的查询需要获取一个表中的所有行时会使用ICP。
2. InnoDB和MyISAM的表都可以使用ICP，包括两种引擎各自的分区表。
3. 对于InnoDB来说，ICP只能用于二级索引，因为ICP的目标时减少全表读取的次数并以此减少I/O操作的频率。对于InnoDB的聚簇索引来说，整个表的记录都已经读入InnoDB的缓冲区了，再使用ICP已经无法减少I/O操作了。
4. ICP不能在虚拟列的二级索引上使用。（只有InnoDB支持在虚拟列上建立二级索引）
5. 子查询的where条件无法下压。
6. 引用已经建立的函数的where条件无法下压，存储引擎无法解析或执行函数。
7. 触发器类型条件不能下压。

为了更好地理解这个优化是如何工作的，我们先来看没有ICP的where查询中，索引扫描是如何进行的：

1. 获取表中下一行（先获取索引，然后通过索引来定位并扫描全表）
2. 根据where条件筛选刚刚获取的表中的行，得到结果集。

如果使用ICP的话，这个流程将变为：

1. 获取下一行的索引（不是整个行）
2. 将where条件下压到存储引擎，测试这个索引是否满足where条件，若不满足，则直接继续检查索引中的下一行。
3. 如果满足，再去获取整个表中的所有行。
4. 根据where条件筛选刚刚获取的表中的行，得到结果集。

举个栗子：

假设有一个表包含了一个人的住址信息，然后在其上建立了一个索引：

INDEX ( zipcode , lastname ,  firstname ) 

然后我们执行以下查询：

1.  
   SELECT * FROM people
2.  
   WHERE zipcode='95054'
3.  
   AND lastname LIKE '%etrunia%'
4.  
   AND address LIKE '%Main Street%';

小学二年级学过的知识告诉我们，这个查询没法用上我们的索引，因为lastname整了个通配符开头的LIKE，必须全表扫。

可是现在我们有ICP了，MySQL会在使用索引查找zipcode之后，检查这些索引列中是否满足last name LIKE "%etrunia%" ，而这个操作是在全表扫描之前的，也就是说，在这一步操作之后，我们就需要扫描满足条件的索引代表的行，不用进行全表扫描了，避免了我们读取满足zipcode条件，但是不满足lastname条件的行。

ICP默认使用，如果想要关闭或再打开，可以调用以下命令：

1.  
   SET optimizer_switch = 'index_condition_pushdown=off';
2.  
   SET optimizer_switch = 'index_condition_pushdown=on';

 （10205101490 数据库报告一部分 从官网上读取文档总结在此）

这篇好文章是转载于：学新通技术网