一 概念介紹

Index Condition Pushdown (ICP)是MySQL 5.6 版本中的新特性,是一種在存儲(chǔ)引擎層使用索引過(guò)濾數(shù)據(jù)的一種優(yōu)化方式。

a 當(dāng)關(guān)閉ICP時(shí),index 僅僅是data access 的一種訪問(wèn)方式，存儲(chǔ)引擎通過(guò)索引回表獲取的數(shù)據(jù)會(huì)傳遞到MySQL Server 層進(jìn)行where條件過(guò)濾。

b 當(dāng)打開(kāi)ICP時(shí),如果部分where條件能使用索引中的字段,MySQL Server 會(huì)把這部分下推到引擎層,可以利用index過(guò)濾的where條件在存儲(chǔ)引擎層進(jìn)行數(shù)據(jù)過(guò)濾,而非將所有通過(guò)index access的結(jié)果傳遞到MySQL server層進(jìn)行where過(guò)濾.

優(yōu)化效果:ICP能減少引擎層訪問(wèn)基表的次數(shù)和MySQL Server 訪問(wèn)存儲(chǔ)引擎的次數(shù),減少io次數(shù)，提高查詢(xún)語(yǔ)句性能。

二 原理

Index Condition Pushdown is not used:

  1 Get the next row, first by reading the index tuple, and then by using the index tuple to locate and read the full table row.
  2 Test the part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.
Index Condition Pushdown is used
  1 Get the next row s index tuple (but not the full table row).
  2 Test the part of the WHERE condition that applies to this table and can be checked using only index columns.
    If the condition is not satisfied, proceed to the index tuple for the next row.
  3 If the condition is satisfied, use the index tuple to locate and read the full table row.
  4 est the remaining part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

三 實(shí)踐案例

a 環(huán)境準(zhǔn)備
   數(shù)據(jù)庫(kù)版本 5.6.16
   關(guān)閉緩存