表结构
mysql> show create table employees \G
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`),
KEY `index_bh` (`birth_date`,`hire_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
一些表数据
mysql> select @@optimizer_switch like '%index_condition_pushdown%' \G
*************************** 1. row ***************************
@@optimizer_switch like '%index_condition_pushdown%': 1
1 row in set (0.00 sec)
mysql> select @@optimizer_switch like '%index_condition_pushdown%' \G
*************************** 1. row ***************************
@@optimizer_switch like '%index_condition_pushdown%': 1
1 row in set (0.00 sec)
mysql> select @@query_cache_type;
+--------------------+
| @@query_cache_type |
+--------------------+
| OFF |
+--------------------+
1 row in set (0.01 sec)
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.17 sec)
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
建立索引
alter table employees add index index_bh (`birth_date`,`hire_date`);
查询分析
mysql> explain select * from employees where birth_date between '1955-01-01' and '1955-12-31' and datediff(hire_date,birth_date)>12300 and first_name like 'S%b%';
+----+-------------+-----------+-------+---------------+----------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+----------+---------+------+-------+-------------+
| 1 | SIMPLE | employees | range | index_bh | index_bh | 3 | NULL | 46318 | Using where |
+----+-------------+-----------+-------+---------------+----------+---------+------+-------+-------------+
1 row in set (0.00 sec)
mysql> SET optimizer_switch='index_condition_pushdown=on';
Query OK, 0 rows affected (0.00 sec)
mysql> explain select * from employees where birth_date between '1955-01-01' and '1955-12-31' and datediff(hire_date,birth_date)>12300 and first_name like 'S%b%';
+----+-------------+-----------+-------+---------------+----------+---------+------+-------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+----------+---------+------+-------+------------------------------------+
| 1 | SIMPLE | employees | range | index_bh | index_bh | 3 | NULL | 46318 | Using index condition; Using where |
+----+-------------+-----------+-------+---------------+----------+---------+------+-------+------------------------------------+
1 row in set (0.01 sec)
执行查询
mysql> show profiles; +----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 0.00278025 | desc employees |
| 2 | 0.00049775 | show create table employees |
| 3 | 0.07444550 | select * from employees where birth_date between '1955-01-01' and '1955-12-31' and datediff(hire_date,birth_date)>12300 and first_name like 'S%b%' |
| 4 | 0.00027500 | SET optimizer_switch='index_condition_pushdown=off' |
| 5 | 0.12347025 | select * from employees where birth_date between '1955-01-01' and '1955-12-31' and datediff(hire_date,birth_date)>12300 and first_name like 'S%b%' |
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
从结果可以看出来开启ICP之后确实快不少
启用ICP之后,可以用索引来筛选 datediff(hire_date,birth_date)>12300 记录,不需要读出整条记录
ICP原理
如下图所示(图来自MariaDB)
1、优化器没有使用ICP时
在存储引擎层,首先读取索引元组(index tuple),然后使用(index tuple)在基表中(base table)定位和读取整行数据
到服务器层,匹配where条件,如果该行数据满足where条件则使用,否则丢弃
指针向下一行移动,重复以上过程
2、使用ICP的时候
如果where条件的一部分能够通过使用索引中的字段进行过滤,那么服务器层将把这部分where条件Pushdown到存储引擎层
到存储引擎层,从索引中读取索引元组(index tuple),使用索引元组进行判断,如果没有满足where条件,则处理下一条索引元组(index tuple),只有当索引元组满足条件的时候,才会去基表中读取数据
ICP的使用条件
1、只能用于二级索引(secondary index)
2、explain显示的执行计划中type值(join 类型)为range、 ref、 eq_ref或者ref_or_null。且查询需要访问表的整行数据,即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)
3、ICP可以用于MyISAM和InnnoDB存储引擎,不支持分区表(5.7将会解决这个问题)
4、ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例