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' 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篩選掉的數據的比例
