一、索引下推概念

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

1.1、使用前后对比

• 如果没有ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给 MySQL 服务器，由MySQL服务器评估 WHERE 后面的条件是否保留行
• 启用 ICP 后，如果部分 WHERE 条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分 WHERE 条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行
  • 好处：ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数
  • 但是，ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例

1.2、ICP的开启/关闭

默认情况下启用索引条件下推，可以通过设置系统变量optimizer_switch控制：index_condition_pushdown

• 关闭索引下推

SET OPTIMIZER_SWITCH = 'index_condition_pushdown=off';

• 打开索引下推

SET OPTIMIZER_SWITCH = 'index_condition_pushdown=on';

• 当使用索引条件下推时，EXPLAIN语句输出结果中Extra列内容显示未 Using index condition

二、准备数据

CREATE TABLE `people`
(
    `id`        INT NOT NULL AUTO_INCREMENT,
    `zipcode`   VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
    `firstname` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
    `lastname`  VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
    `address`   VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `zip_last_first` (`zipcode`, `lastname`, `firstname`)
);

• 插入数据

INSERT INTO `people`
VALUES ('1', '000001', '三', '张', '北京市'),
       ('2', '000002', '四', '李', '南京市'),
       ('3', '000003', '五', '王', '上海市'),
       ('4', '000001', '六', '赵', '天津市');

三、实战

3.1、知道邮编时，查询

• SQL

EXPLAIN
SELECT *
FROM people
WHERE zipcode = '000001'
  AND lastname LIKE '%张%'
  AND address LIKE '%北京市%';

• EXPLAIN image.png

• 开起索引下推

SET optimizer_switch = 'index_condition_pushdown=on';

• EXPLAIN Using index condition.png

• 小结

Extra中显示了Using index condition，这表示使用了索引下推。Using where表示条件中包含需要过滤的非索引列的数据，即address LIKE '%北京市%'这个条件并不是索引列，需要在服务端过滤掉

四、开启和关闭 ICP 的性能对比

创建存储过程，主要目的就是插入很多 000001 的数据，这样查询的时候为了在存储引擎层做过滤，减少IO，也为了减少缓冲池（缓存数据页，没有IO）的作用

• 存储过程

DELIMITER $
CREATE PROCEDURE insert_people(max_num INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
        SET i = i + 1;
        INSERT INTO people (zipcode, firstname, lastname, address) VALUES ('000001', '六', '赵', '天津市');
    UNTIL i = max_num
        END REPEAT;
    COMMIT;
END $

DELIMITER ;

• 调用存储过程

CALL insert_people(1000000);

4.1、开启 PROFILING

SET PROFILING = 1;

4.2、查询SQL

SELECT *
FROM people
WHERE zipcode = '000001'
  AND lastname LIKE '%张%';

4.3、查看SHOW PROFILES

SHOW PROFILES

image.png

4.4、关闭 ICP

SET optimizer_switch = 'index_condition_pushdown=off';

• SQL

SELECT *
FROM people
WHERE zipcode = '000001'
  AND lastname LIKE '%zhang%';

image.png

• 查看耗时的详细过程，那个步骤最耗时

show profile for query 查询id;

执行时最耗时.png

• 小结：多次测试比较来看，使用 ICP 优化的查询效率会更好

五、 ICP的使用条件

• 1、只能用于二级索引
• 2、explain显示的执行计划中type值（join 类型）为 range 、 ref 、 eq_ref 或者 ref_or_null 可以使用ICP
• 3、并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录到server端做where过滤
• 4、ICP可以用于MyISAM和InnnoDB存储引擎
• 5、MySQL 5.6版本的不支持分区表的ICP功能，5.7版本的开始支持
• 6、 当SQL使用覆盖索引时，不支持ICP优化方法
• 7、 相关子查询的条件不能使用ICP

六、在不使用 ICP 索引扫描的过程

• storage层：只将满足index key条件的索引记录对应的整行记录取出，返回给server层
• server 层：对返回的数据，使用后面的where条件过滤，直至返回最后一行

image.png image.png

七、使用 ICP 索引扫描的过程

• storage层

首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤。将满足的index filter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃，不回表、也不会返回server层

• server 层：

对返回的数据，使用table filter条件做最后的过滤

image.png image.png

八、小结

• 不适用ICP，存储层多返回了需要被index filter过滤掉的整行记录
• 使用ICP后，直接就去掉了不满足index filter条件的记录，省去了他们回表和传递到server层的成本
• ICP的 加速效果 取决于在存储引擎内通过 ICP筛选 掉的数据的比例