**背景** MySQL 5.6 開始支持index_condition_pushdown特性，即server層把可以在index進行filter的謂詞傳遞給引擎層完成過濾，然后結果返回到server。 **工作方式** 下面看一下InnoDB的處理方式: 通過設置set global optimizer_switch= "index_condition_pushdown=ON"來啟用這個特性。 例如: ~~~ CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `col1` int(11) DEFAULT NULL, `col2` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `t1_cc` (`col1`,`col2`) ) ENGINE=InnoDB; ~~~ ~~~ mysql> explain select * from t1 where col1>= 1 and col1 <= 4 and col2=11; +----+-------------+-------+-------+---------------+-------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+-------+---------+------+------+-----------------------+ | 1 | SIMPLE | t1 | range | t1_cc | t1_cc | 10 | NULL | 2 | Using index condition | +----+-------------+-------+-------+---------------+-------+---------+------+------+-----------------------+ ~~~ 1\. 評估 在執行計劃評估階段，通過push_index_cond函數把index filter謂詞傳遞給引擎handler。 2\. 執行 InnoDB通過row_search_for_mysql獲取每行記錄的時候，使用innobase_index_cond函數來check index filter謂詞條件是否成立。通過這種方式來完成index上的filter，整個過程并不復雜。 **收益和限制** 下面來看一下index_condition_pushdown的收益和限制: 收益: index_condition_pushdown所帶來的收益可以從三個方面來看: 1\. 數據copy 減少了InnoDB層返回給server層的數據量，減少了數據copy。 2\. 隨機讀取 對于二級索引的掃描和過濾，減少了回primary key上進行隨機讀取的次數 3\. 記錄鎖 記錄鎖是在InnoDB層完成的，比如如果是select for update語句，就會發現index_condition_pushdown會大大減少記錄鎖的個數。 限制: 目前index_condition_pushdown還有諸多的限制: 1\. 索引類型 如果索引類型是primary key，就不會采用，因為index_condition_pushdown最大的好處是減少回表的隨機IO，所以如果使用的index是PK，那么收益就大大減少，不過MySQL官方也在從新評估是否采用，見WL#6061。 2\. 性能衰減 如果在primary key上面使用， 或者index filter謂詞并不能有效過濾記錄的時候，會發現sysbench的測試性能相比較關閉ICP的方式略低。可以參考[http://s.petrunia.net/blog/?p=101的討論。](http://s.petrunia.net/blog/?p=101%E7%9A%84%E8%AE%A8%E8%AE%BA%E3%80%82) 3\. SQL類型 1\. 不支持多表update和delete語句，因為select和update會共用handler，而一個是一致性讀，一個是當前讀，同樣的filter都apply的話，update會找不到記錄。 2\. 如果JOIN是CONST 或者 SYSTEM，不能使用。 因為CONST和SYSTEM做了特別優化，只執行一次，做了緩存，而應用filter的話，會產生數據一致性問題。 **索引設計的原則** 除了MySQL提供的這些新特性以外，DBA或者開發在設計index的時候，應該遵循的一些原則: 1\. 查詢謂詞都能夠通過index進行掃描 2\. 排序謂詞都能夠利用index的有序性 3\. index包含了查詢所需要的所有字段 這就是傳說中的Three-star index。 可以參考《Wiley,.Relational.Database.Index.Design.and.the.Optimizers》 MySQL的index_condition_pushdown，前進了一大步，不過相比較Oracle的index掃描方式，還有空間。比如oracle的index掃描支持的index skip scan方式。