mysql索引条件下推_MySQL是怎么使用索引条件下推的?

MySQL的ICP技术的使用条件

Index Condition Pushdown (ICP) ,是索引条件下推,是MySQL利用索引快速获取数据的技术。

只要在查询语句中,使用了WHERE子句,且子句中有存在索引的条件表达式,比如a>3且列上存在索引，则可能使用ICP技术对读取数据进行优化。这个技术不区分存储引擎，各个类型的存储引擎都可以使用。

但是，有的时候，ICP可以被使用，有的时候却不能被使用，原因是什么？

例如：

CREATE TABLE `t1` (

`a` int(11) NOT NULL,

`b` int(11) NOT NULL,

`c` int(11) NOT NULL,

`d` int(11) NOT NULL,

`e` int(11) NOT NULL,

PRIMARY KEY (`a` DESC,`b`),

KEY `c_DESC_d_DESC` (`c` DESC,`d` DESC)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t2` (

`a` int(11) NOT NULL,

`b` int(11) NOT NULL,

`c` int(11) NOT NULL,

`d` int(11) NOT NULL,

`e` int(11) NOT NULL,

PRIMARY KEY (`a` DESC,`b`),

KEY `c_DESC_d_DESC` (`c` DESC,`d` DESC)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

插入语句：

INSERT INTO t1 VALUES (1, 1, 1, 2, 1);

INSERT INTO t1 VALUES (1, 2, 1, 1, 2);

INSERT INTO t1 VALUES (1, 3, 3, 2, 3);

INSERT INTO t1 VALUES (5, 1, 3, 5, 4);

INSERT INTO t1 VALUES (5, 2, 5, 5, 5);

INSERT INTO t1 VALUES (5, 3, 6, 6, 6);

INSERT INTO t1 VALUES (10, 1, 7, 9, 7);

INSERT INTO t1 VALUES (11, 2, 7, 8, 8);

INSERT INTO t1 VALUES (12, 3, 9, 9, 9);

ANALYZE TABLE t1;

INSERT INTO t2 VALUES (1, 1, 1, 2, 1);

INSERT INTO t2 VALUES (1, 2, 1, 1, 2);

INSERT INTO t2 VALUES (1, 3, 3, 2, 3);

INSERT INTO t2 VALUES (5, 1, 3, 5, 4);

INSERT INTO t2 VALUES (5, 2, 5, 5, 5);

INSERT INTO t2 VALUES (5, 3, 6, 6, 6);

INSERT INTO t2 VALUES (10, 1, 7, 9, 7);

INSERT INTO t2 VALUES (11, 2, 7, 8, 8);

INSERT INTO t2 VALUES (12, 3, 9, 9, 9);

ANALYZE TABLE t2;

同样的SQL语句，在不同的表上执行，查看并比较执行计划：

对于t1表：

mysql> EXPLAIN SELECT * FROM t1 WHERE a>3 ORDER BY a ASC, b ASC;

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+

| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                       |

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+

|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where; Using filesort |

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+

1 row in set, 1 warning (0.00 sec)表t1的查询执行计划，没有使用ICP。

对于t2表：

mysql> EXPLAIN SELECT * FROM t2 WHERE a>3 ORDER BY a ASC, b ASC;

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------+

| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                 |

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------+

|  1 | SIMPLE      | t2    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    4 |   100.00 | Using index condition; Using filesort |

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------+

1 row in set, 1 warning (0.00 sec)表t2的查询执行计划，使用了ICP。为什么t1却没有使用ICP呢？

ICP能被使用。这点可以通过类似如下的调用关系分析得知(此处举例使用了MyISAM引擎，InnoDB等与此类似)。

optimize()-->make_join_readinfo-->push_index_cond()-->ha_myisam::idx_cond_push(unsigned int keyno_arg, Item * idx_cond_arg)

在push_index_cond()函数中，规定了什么情况可以使用ICP，什么不可以使用ICP。

可以使用ICP的情况如下：

1 表带有WHERE子句，且

2 存储引擎支持ICP，且

3 系统参数

index_condition_pushdown

被打开，且

4 查询不是针对多表的update或delete操作，且

5 查询也不是子查询，且

6 查询的表不是常量表(如果是常量表因记录数最多为1则没有必要使用ICP)，且

7 索引不是聚集索引(如果是InnoDB的主键索引，则是聚集索引，此类索引的查询速度快，也不需要执行ICP以获取ICP能带来的好处)

至此，我们可以明白，为什么表t1没有使用ICP而表t2使用了ICP。

仔细查看表t1的定义，列a上定义的索引是主键索引。所以表t1没有使用ICP。