背景

    MySQL 5.6 开始支持index_condition_pushdown特性，即server层把可以在index进行filter的谓词传递给引擎层完成过滤，然后结果返回到server。

    工作方式

    下面看一下InnoDB的处理方式:

    通过设置set global optimizer_switch= “index_condition_pushdown=ON”来启用这个特性。

    例如:

    1. CREATE TABLE `t1` (
    2. `id` int(11) NOT NULL AUTO_INCREMENT,
    3. `col1` int(11) DEFAULT NULL,
    4. `col2` int(11) DEFAULT NULL,
    5. PRIMARY KEY (`id`),
    6. KEY `t1_cc` (`col1`,`col2`)
    7. )  ENGINE=InnoDB;
    1. mysql> explain select * from t1 where col1>= 1 and col1 <= 4 and col2=11;
    2. +----+-------------+-------+-------+---------------+-------+---------+------+------+-----------------------+
    3. | id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                 |
    4. +----+-------------+-------+-------+---------------+-------+---------+------+------+-----------------------+
    5. |  1 | SIMPLE      | t1    | range | t1_cc         | t1_cc | 10      | NULL |    2 | Using index condition |
    6. +----+-------------+-------+-------+---------------+-------+---------+------+------+-----------------------+

    1. 评估

    在执行计划评估阶段，通过push_index_cond函数把index filter谓词传递给引擎handler。

    2. 执行

    InnoDB通过row_search_for_mysql获取每行记录的时候，使用innobase_index_cond函数来check index filter谓词条件是否成立。通过这种方式来完成index上的filter，整个过程并不复杂。

    收益和限制

    下面来看一下index_condition_pushdown的收益和限制:

    收益: index_condition_pushdown所带来的收益可以从三个方面来看:

    1. 数据copy

    减少了InnoDB层返回给server层的数据量，减少了数据copy。

    2. 随机读取

    对于二级索引的扫描和过滤，减少了回primary key上进行随机读取的次数

    3. 记录锁

    记录锁是在InnoDB层完成的，比如如果是select for update语句，就会发现index_condition_pushdown会大大减少记录锁的个数。

    限制: 目前index_condition_pushdown还有诸多的限制:

    1. 索引类型

    如果索引类型是primary key，就不会采用，因为index_condition_pushdown最大的好处是减少回表的随机IO，所以如果使用的index是PK，那么收益就大大减少，不过MySQL官方也在从新评估是否采用，见WL#6061。

    2. 性能衰减

    如果在primary key上面使用， 或者index filter谓词并不能有效过滤记录的时候，会发现sysbench的测试性能相比较关闭ICP的方式略低。可以参考http://s.petrunia.net/blog/?p=101的讨论。

    3. SQL类型

    1. 不支持多表update和delete语句，因为select和update会共用handler，而一个是一致性读，一个是当前读，同样的filter都apply的话，update会找不到记录。

    2. 如果JOIN是CONST 或者 SYSTEM，不能使用。 因为CONST和SYSTEM做了特别优化，只执行一次，做了缓存，而应用filter的话，会产生数据一致性问题。

    索引设计的原则

    除了MySQL提供的这些新特性以外，DBA或者开发在设计index的时候，应该遵循的一些原则:

    1. 查询谓词都能够通过index进行扫描

    2. 排序谓词都能够利用index的有序性

    3. index包含了查询所需要的所有字段

    这就是传说中的Three-star index。

    可以参考《Wiley,.Relational.Database.Index.Design.and.the.Optimizers》

    MySQL的index_condition_pushdown，前进了一大步，不过相比较Oracle的index扫描方式，还有空间。比如oracle的index扫描支持的index skip scan方式。