mysql-ICP

ICP（index condition pushdown）的作用：

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。

ICP可以減少存儲引擎通路基表的次數以及MySQL伺服器通路存儲引擎的次數。

ICP過程：

the storage engine traverses the index to locate rows in the base table， if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. 

存儲引擎将周遊索引來查找基表中的行，如果WHERE隻能使用索引中的列來評估條件的某些部分 ，MySQL伺服器會将這部分WHERE條件下降到存儲引擎。然後，存儲引擎使用索引條目評估推送索引條件，并且隻有滿足時才從表中讀取該行。

使用限制：

ICP用于range，ref，eq_ref，和ref_or_null通路方法時，需要通路完整的表行

ICP可用于innodb和myisam表，包括分區innodd和myisam表

對于innodb表，ICP僅用于二級索引。（ICP的目的是減少全行讀取的次數，進而減少IO操作），對于innodb聚集索引，完整的記錄已被讀入到innodb緩沖區，在這種情況下，ICP不會減少io

ICP不支援在虛拟生成的列上建立二級索引，innodb支援。

引用子查詢的條件不能被下推

涉及存儲功能的條件不能被下推，存儲引擎不能調用存儲函數

觸發條件不能被pushdown

在不使用ICP索引掃描的過程：

1.首先讀取索引元組，然後使用索引元組來定位并讀取整個表格行。

2.測試适用于該表where的部分條件。根據測試結果接受或拒絕該行。

使用ICP掃描的過程：

1.擷取下一行的索引元組（但不是全表行）

2.隻使用索引列檢查适用此表的where部分條件，如果不符合，就轉到下一行的索引元組

3.如果滿足條件，則使用索引元組來定位并讀取整個表格行

4.測試适用于該表where的部分條件。根據測試結果接受或拒絕該行

當使用ICP時，通過explain後檢視執行計劃中Extra會顯示 Using index condition，當在讀取全表行時就不會顯示

例如：

二級索引：（zipcode,lastname,firstname）

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';
      

說明：不使用ICP，mysql通過zipcode='95054'來掃描，lastname LIKE '%etrunia%'不會用于限制必須掃描的行數

使用ICP後，mysql會在讀取整個行之前會通過lastname LIKE '%etrunia%'進行篩選。

可以通過SET optimizer_switch = 'index_condition_pushdown=off/on';來關閉或開啟ICP

譯者介紹：家華，從事mysqlDBA的工作，記錄自己對mysql的一些總結