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 控制:
sql"># 关闭索引下推
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)单列索引(特殊情况)
sql">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;
sql"># 建立 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;
插入数据
sql">INSERT INTO `people` VALUES
('1', '000001', '三', '张', '北京市'),
('2', '000002', '四', '李', '南京市'),
('3', '000003', '五', '王', '上海市'),
('4', '000001','六', '赵', '天津市');
sql">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.准备数据
sql"># 创建存储过程,向 people 表中添加 1000000 条数据,测试 ICP 开启和关闭状态下的性能
DELIMITER //
CREATE PROCEDURE insert_people (max_num INT)
BEGINDECLARE i INT DEFAULT 0;SET autocommit = 0;REPEATSET i = i + 1;INSERT INTO people (zipcode, firstname ,lastname, address ) VALUES ('000001', '六', '赵', '天津市');UNTIL i = max_numEND REPEAT;COMMIT;
END //
DELIMITER;
sql"># 调用存储过程,插入 1000000 条数据
CALL insert_people(1000000);
10.5.2.性能对比
(1)打开 profiling。
sql">set profiling = 1;
(2)执行 SQL 语句,此时默认打开索引下推。
sql">SELECT * FROM people WHERE zipcode='008801' AND lastname LIKE '%张%';
(3)再次执行 SQL 语句,不使用索引下推(也可通过设置 optimizer_switch 来关闭索引下推)。
sql">SELECT /*+ no_icp (people) */ * FROM people WHERE zipcode=' e88801' AND lastname LIKE '%张%' ;
1
(4)查看 profiles。
对比结果可知,开启 ICP 后查询所消耗的时间明显少于关闭 ICP 的!