10.索引下推

10.索引下推

10.1.什么是索引下推？
（1）索引下推 (Index Condition Pushdown, ICP) 是 MySQL 5.6 中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。
（2）如果没有 ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给 MySQL 服务器，由 MySQL 服务器评估 WHERE 后面的条件是否保留行。
（3）启用 ICP 后（一般是默认开启的），如果部分 WHERE 条件可以仅使用索引中的列进行筛选，则 MySQL 服务器会把这部分 WHERE 条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。
① 优点：ICP 可以减少存储引擎必须访问基表的次数和 MySQL 服务器必须访问存储引擎的次数。
② 缺点：ICP 的加速效果取决于在存储引擎内通过 ICP 筛选掉的数据的比例。

10.2.索引下推的开启与关闭
（1）默认情况下启用索引条件下推。可以通过设置系统变量 optimizer_switch 控制：

# 关闭索引下推
SET optimizer_switch = 'index_condition_pushdown=off';
# 打开索引下推
SET optimizer_switch = 'index_condition_pushdown=on';

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

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

10.4.案例
（1）单列索引（特殊情况）

USE atguigudb1;

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

以上面的 SQL 语句为例，开启 ICP 后，在使用二级索引 idx_key1 的情况下，假设通过查询条件 key1 > ‘z’ 过滤得到了 1000 条记录（注意这里并不是完整的记录，只是包括索引列+主键，即 key1 + id），此时先不着急回表，而是在后面的查询语句 key1 LIKE ‘%a’ 的基础上，对这 1000 条记录再次进行过滤，假设只剩下 100 条记录，此时再根据 id 进行回表操作查找完整的记录即可。

（2）联合索引（普遍情况）

USE atguigudb1;

# 建立 people 表
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`)
)ENGINE=INNODB AUTo_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;

插入数据

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

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

① 由上面的查询计划可知，key_len = 63，这说明索引 zip_last_first 中只有索引列 zip 被使用了（like 以通配符 % 开头索引失效）。

② 开启 ICP 后，在使用二级索引（联合索引） zip_last_first 的情况下，假设通过查询条件 zipcode = ‘000001’ 过滤得到了 1000 条记录（注意这里并不是完整的记录，只是包括索引列+主键，即 zipcode, lastname, firstname + id），此时先不着急回表，而是在后面的查询语句 lastname LIKE ‘%张%’ 的基础上，对这 1000 条记录再次进行过滤，假设只剩下 100 条记录，而此时由于字段 address 不在 zipcode, lastname, firstname + id 中，所以无法再次过滤，此时再根据 id 进行回表操作查找完整的记录即可。

③ 如果关闭 ICP，在使用二级索引（联合索引） zip_last_first 的情况下，假设通过查询条件 zipcode = ‘000001’ 过滤得到了 1000 条记录，此时会直接回表，根据 id 查询到完整的记录，然后再根据后面的 2 个查询条件进行过滤。其缺点就比较明显，如果通过查询条件 zipcode = ‘000001’ 过滤得到的记录数非常大（比如有几百万条），那么此时直接全部回表的效率会比较低。

④ 注：具体 key_len 的计算可以查看MySQL高级篇知识点——性能分析工具的使用这篇文章的第 6.4.7 节，key_len = 63 的由来如下：
varchar(20) 变长字段且允许 NULL = 20 * (character set:utf8 = 3, gbk = 2, latin1 = 1) + 1(NULL) + 2(变长字段) = 20 * 3 + 1 + 2 = 63

10.5.开启/关闭索引下推的性能对比
10.5.1.准备数据

# 创建存储过程，向 people 表中添加 1000000 条数据，测试 ICP 开启和关闭状态下的性能
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;

# 调用存储过程，插入 1000000 条数据
CALL insert_people(1000000);

10.5.2.性能对比
（1）打开 profiling。

set profiling = 1;

（2）执行 SQL 语句，此时默认打开索引下推。

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

（3）再次执行 SQL 语句，不使用索引下推（也可通过设置 optimizer_switch 来关闭索引下推）。

SELECT /*+ no_icp (people) */ * FROM people WHERE zipcode=' e88801' AND lastname LIKE '%张%' ;

1

（4）查看 profiles。

对比结果可知，开启 ICP 后查询所消耗的时间明显少于关闭 ICP 的！