MySQL Index Condition Pushdown(ICP)的使用限制

chenfeng發表於2018-11-27

ICP(index condition pushdown)是mysql利用索引(二級索引)元組和篩欄位在索引中的where條件從表中提取資料記錄的一種最佳化操作。ICP的思想是:儲存引擎在訪問索引的時候檢查篩選欄位在索引中的where條件(pushed index condition,推送的索引條件),如果索引元組中的資料不滿足推送的索引條件,那麼就過濾掉該條資料記錄。ICP(最佳化器)儘可能的把index condition的處理從server層下推到storage engine層。storage engine使用索引過過濾不相關的資料,僅返回符合index condition條件的資料給server層。也是說資料過濾儘可能在storage engine層進行,而不是返回所有資料給server層,然後後再根據where條件進行過濾。

Index Condition Pushdown (ICP)是MySQL 5.6 版本中的新特性,是一種在儲存引擎層使用索引過濾資料的一種最佳化方式。

a 當關閉ICP時,index 僅僅是data access 的一種訪問方式,儲存引擎透過索引回表獲取的資料會傳遞到MySQL Server 層進行where條件過濾。

b 當開啟ICP時,如果部分where條件能使用索引中的欄位,MySQL Server 會把這部分下推到引擎層,可以利用index過濾的where條件在儲存引擎層進行資料過濾,而非將所有透過index access的結果傳遞到MySQL server層進行where過濾.

最佳化效果:ICP能減少引擎層訪問基表的次數和MySQL Server 訪問儲存引擎的次數,減少io次數,提高查詢語句效能。


ICP 開啟時 ,MySQL將在儲存引擎層 利用索引過濾資料,減少不必要的回表,注意 虛線的using where 表示如果where條件中含有沒有被索引的欄位,則還是要經過MySQL Server 層過濾。


 ICP的使用限制:

1 當sql需要全表訪問時,ICP的最佳化策略可用於range, ref, eq_ref,  ref_or_null 型別的訪問資料方法 。

2 支援InnoDB和MyISAM表。

3 ICP只能用於二級索引,不能用於主索引。

4 並非全部where條件都可以用ICP篩選。

   如果where條件的欄位不在索引列中,還是要讀取整表的記錄到server端做where過濾。

5 ICP的加速效果取決於在儲存引擎內透過ICP篩選掉的資料的比例。

6 5.6 版本的不支援分表的ICP 功能,5.7 版本的開始支援。

7 當sql 使用覆蓋索引時,不支援ICP 最佳化方法。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/15498/viewspace-2221758/,如需轉載,請註明出處,否則將追究法律責任。

相關文章