索引优化：索引下推

• 简介

Index Condition Pushdown(ICP)是MySQL5.6中新特性，是一种在存储擎层使用索引过滤数据的优化方式。
从二级索引中查找数据，如果二级索引中已经有了要筛选的列，但是又不能使用索引，会先从该二级索引筛选完以后再回表。
 
好处：ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。
但是， ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。

• 代码案例

# 举例1：
USE atguigudb1;
 
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
 
# 举例2：
# 创建表
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', '六', '赵', '天津市');
 
# 测试：where后的第1个条件使用到了索引，第2和第3个条件没有使用到索引，所以回表
EXPLAIN SELECT * FROM people
WHERE zipcode='000001'
AND lastname LIKE '%张%'
AND address LIKE '%北京市%';
 
# 测试：where后的3个条件都用到了索引
EXPLAIN SELECT * FROM people
WHERE zipcode='000001'
AND lastname LIKE '张%'
AND firstname LIKE '三%';

• 打开/关闭ICP

# 默认情况索引条件下推是开启的
 
# 关闭索引下推
SET optimizer_switch = 'index_condition_pushdown=off';
 
# 打开索引下推
SET optimizer_switch = 'index_condition_pushdown=on';

• ICP的使用条件

如果表访问的类型为range、 ref、 eq_ref和ref_or_null可以使用ICP
ICP可以用于InnoDB和MyISAM表，包括分区表InnoDB和MyISAM表
对于InnoDB表，ICP仅用于二级索引。IICP的目标是减少全行读取次数，从而减少I/O操作。
当SQL使用覆盖索引时，不支持ICP。因为这种情况下使用ICP不会减少I/O。
相关子查询的条件不能使用ICP