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

转自：http://blog.163.com/li_hx/blog/static/183991413201422710411918/

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。