mysql数据库sql优化（六）看这里之--索引下推

本文链接：https://blog.csdn.net/qq_39746820/article/details/123863518

使用前后对比

Index Condition Pushdown(ICP)是mysql5.6中新特性，是一种在存储引擎层使用索引过滤数据的优化方式。

①如果没有ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给mysql服务器，由mysql服务器评估where后面的条件是否保留行。

②启用ICP后，如果部分where条件可以仅使用索引中的列进行筛选，则mysql服务器会把这部分where条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。

Ⅰ 好处：ICP可以减少存储引擎必须访问基表的次数和mysql服务器必须访问存储引擎的次数

Ⅱ 但是ICP加速效果取决于存储引擎内通过ICP筛选掉的数据的比例。

ICP的开启/关闭

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

关闭是：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(58) COLLATE utf8_bin DEFAULT NULL,
	PRIMARY KEY(id),
	INDEX 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,'000004','六','赵','天津市');

我们可以看到，表有一个联合索引字段是zipcode,lastname,firstname。

如果我们知道了一个人的邮编不确定这个人的姓氏，我们可以进行如下的搜索

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

ICP的使用条件

①如果表访问的类型为range、ref、eq_ref和ref_or_null可以使用icp

②ICP可以用于innodb和myisam表，包括分区表

③对于innodb表，ICP仅用于二级索引。ICP目的是减少全行读取次数，从而减少IO操作

④当sql使用覆盖索引时，不支持ICP，因为这种情况下使用不会减少IO

⑤相关子查询的条件不能使用icp

⑥并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录到server端做where过滤。