ICP简介
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 eva luates the WHEREcondition for the rows. With ICP enabled, and if parts of the WHERE condition can be eva luated by using only fields from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then eva luates 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条件
?
测试
?
?
wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
tar jxf employees_db-full-1.0.6.tar.bz2
cd employees_db
mysql -uroot -p < employees.sql
?
?
表结构
?
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