0%

索引条件下推

在MySQL5.6引入了索引下推优化(index condition pushdown),简称ICP,是指在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

一、定义

  1. Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.
    • 存储引擎层根据索引尽可能的过滤数据,然后再返回给服务器层根据where其他条件进行过滤。
  2. 开启/关闭 set optimizer_switch='index_condition_pushdown=on/off';
  3. 使用场景
    • ICP is used for the range, ref, eq_ref, and ref_or_null access methods when there is a need to access full table rows.
    • ICP can be used for InnoDB and MyISAM tables, including partitioned InnoDB and MyISAM tables.
    • For InnoDB tables, ICP is used only for secondary indexes. The goal of ICP is to reduce the number of full-row reads and thereby reduce I/O operations. For InnoDB clustered indexes, the complete record is already read into the InnoDB buffer. Using ICP in this case does not reduce I/O.
    • ICP is not supported with secondary indexes created on virtual generated columns. InnoDB supports secondary indexes on virtual generated columns.
    • Conditions that refer to subqueries cannot be pushed down.
    • Conditions that refer to stored functions cannot be pushed down. Storage engines cannot invoke stored functions.
    • Triggered conditions cannot be pushed down.

二、使用

  1. 初始化表
1
2
3
4
5
6
7
8
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`ismale` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB
  1. 初始化数据

INSERT INTO user(name,age,ismale) VALUES('张三',10,1),('张三',10,1),('张三',10,2),('张三',20,1),('李四',10,1),('李四',10,1);

  1. 查询SQL select * from user where name like '张 %' and age=10 and ismale=1;,此查询语句会在索引内部先过滤掉age不等于10的记录,然后再进行回表

三、参考

  1. ICP