Index Condition Pushdown(ICP)

Index Condition Pushdown (ICP)是mysql使用索引從表中檢索行數(shù)據(jù)的一種優(yōu)化方式。

ICP原理

禁用ICP，存儲引擎會通過遍歷索引定位基表中的行，然后返回給MySQL Server層，再去為這些數(shù)據(jù)行進行WHERE后的條件的過濾。

開啟ICP，如果部分WHERE條件能使用索引中的字段，MySQL Server 會把這部分下推到存儲引擎層，存儲引擎通過索引過濾，把滿足的行從表中讀取出。ICP能減少引擎層訪問基表的次數(shù)和MySQL Server 訪問存儲引擎的次數(shù)。

ICP的目標是減少從基表中全紀錄讀取操作的數(shù)量，從而降低IO操作

對于InnoDB表，ICP只適用于輔助索引。

ICP標識

當(dāng)使用ICP優(yōu)化時，執(zhí)行計劃的Extra列顯示Using indexcondition提示

相關(guān)參數(shù)

optimizer_switch="index_condition_pushdown=on”;

適用場景

#輔助索引INDEX (zipcode, lastname, firstname).

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

People表有個二級索引INDEX (zipcode, lastname, firstname),用戶只知道某用戶的zipcode,和大概的lastname、address,此時想查詢相關(guān)信息。

若不使用ICP：則是通過二級索引中zipcode的值去基表取出所有zipcode='95054'的數(shù)據(jù)，然后server層再對lastname LIKE '%etrunia%'AND address LIKE '%Main Street%';進行過濾

若使用ICP：則lastname LIKE '%etrunia%'AND address LIKE '%Main Street%'的過濾操作在二級索引中完成，然后再去基表取相關(guān)數(shù)據(jù)

使用限制

l  只支持 select 語句

l  5.6 中只支持 MyISAM與InnoDB引擎

l  5.6中不支持分區(qū)表的ICP;從MySQL 5.7.3開始支持分區(qū)表的ICP

l  ICP的優(yōu)化策略可用于range、ref、eq_ref、ref_or_null 類型的訪問數(shù)據(jù)方法；

l  不支持主建索引的ICP（對于Innodb的聚集索引，完整的記錄已經(jīng)被讀取到Innodb Buffer，此時使用ICP并不能降低IO操作）

l  當(dāng) SQL 使用覆蓋索引時但只檢索部分數(shù)據(jù)時，ICP 無法使用

l  ICP的加速效果取決于在存儲引擎內(nèi)通過ICP篩選掉的數(shù)據(jù)的比例。

Multi-Range Read (MRR)

MRR 的全稱是 Multi-Range Read Optimization，是優(yōu)化器將隨機 IO 轉(zhuǎn)化為順序 IO 以降低查詢過程中 IO 開銷的一種手段。

MRR原理

select non_key_column from tb where ey_column=x;

在沒有使用MRR特性時，MySQL執(zhí)行查詢的偽代碼

第一步 先根據(jù)where條件中的輔助索引獲取輔助索引與主鍵的集合，結(jié)果集為rest。

select key_column, pk_column from tb where key_column=x order by key_column

第二步 通過第一步獲取的主鍵來獲取對應(yīng)的值。

for each pk_column value in rest do:

select non_key_column from tb where pk_column=val

使用MRR特性時，MySQL執(zhí)行查詢的偽代碼

第一步 先根據(jù)where條件中的輔助索引獲取輔助索引與主鍵的集合，結(jié)果集為rest

select key_column, pk_column from tb where key_column = x order by key_column

第二步 將結(jié)果集rest放在buffer里面(read_rnd_buffer_size 大小直到buffer滿了)，然后對結(jié)果集rest按照pk_column排序，得到結(jié)果集是rest_sort

第三步 利用已經(jīng)排序過的結(jié)果集，訪問表中的數(shù)據(jù)，此時是順序IO.

select non_key_column fromtb where pk_column in (rest_sort)

綜上

在不使用 MRR 時，優(yōu)化器需要根據(jù)二級索引返回的記錄來進行“回表”，這個過程一般會有較多的隨機IO, 使用MRR時，SQL語句的執(zhí)行過程是這樣的：

1)   優(yōu)化器將二級索引查詢到的記錄放到一塊緩沖區(qū)中

2)   如果二級索引掃描到文件的末尾或者緩沖區(qū)已滿，則使用快速排序?qū)�緩沖區(qū)中的內(nèi)容按照主鍵進行排序

3)   用戶線程調(diào)用MRR接口取cluster index，然后根據(jù)cluster index 取行數(shù)據(jù)

4)   當(dāng)根據(jù)緩沖區(qū)中的 cluster index取完數(shù)據(jù)，則繼續(xù)調(diào)用過程 2) 3)，直至掃描結(jié)束

通過上述過程，優(yōu)化器將二級索引隨機的 IO 進行排序，轉(zhuǎn)化為主鍵的有序排列，從而實現(xiàn)了隨機 IO 到順序 IO 的轉(zhuǎn)化，提升性能

此外MRR還可以將某些范圍查詢，拆分為鍵值對，來進行批量的數(shù)據(jù)查詢，如下：

SELECT * FROM tWHERE key_part1 >= 1000 AND key_part1 < 2000AND key_part2 = 10000;

表t上有二級索引(key_part1, key_part2)，索引根據(jù)key_part1,key_part2的順序排序。

若不使用MRR：索引掃描會將key_part1在1000到2000的索引元組，而不管key_part2的值，這樣對key_part2不等于10000的索引元組也做了額外的掃描。此時掃描的范圍是：

[{1000, 10000}, {2000, MIN_INT}]此間隔可能包含key_part2不等于10000的部分

若使用MRR：掃描則分為多個范圍，對于每一個Key_part1(1000,1001…,1999)單個值的掃描只需要掃描索引中key_part2為10000的元組。如果索引中包含很多key_part2不為10000的元組，最終MRR的效果越好。MRR掃描的范圍是多個單點間隔[{1000, 10000}], ..., [{1999, 10000}] 此間隔只包含key_part2=10000的部分。

MRR標識

當(dāng)使用ICP優(yōu)化時，執(zhí)行計劃的Extra列顯示Using MRR提示

相關(guān)參數(shù)

用optimizer_switch 的標記來控制是否使用MRR.設(shè)置mrr=on時，表示啟用MRR優(yōu)化。

mrr_cost_based表示是否通過cost base的方式來啟用MRR.

當(dāng)mrr=on,mrr_cost_based=on,則表示cost base的方式還選擇啟用MRR優(yōu)化,當(dāng)發(fā)現(xiàn)優(yōu)化后的代價過高時就會不使用該項優(yōu)化

當(dāng)mrr=on,mrr_cost_based=off,則表示總是開啟MRR優(yōu)化

SET  @@optimizer_switch='mrr=on,mrr_cost_based=on';

參數(shù)read_rnd_buffer_size 用來控制鍵值緩沖區(qū)的大小。二級索引掃描到文件的末尾或者緩沖區(qū)已滿，則使用快速排序?qū)�緩沖區(qū)中的內(nèi)容按照主鍵進行排序

適用場景

#輔助索引key_part1，查詢key_part1在1000到2000范圍內(nèi)的數(shù)據(jù)

SELECT * FROM t WHERE key_part1 >= 1000 AND key_part1 < 2000

不使用MRR：先通過二級索引的key_part1字段取出滿足條件的key_part1,pk_col order by key_part1.然后通過pk_col去表中取出滿足條件的數(shù)據(jù)，此時，因為取出的pk_col是亂序的，而表又是pk_col存放數(shù)據(jù)的，當(dāng)去表中取數(shù)據(jù)時，則會產(chǎn)生大量的隨機IO

使用MRR：先通過二級索引的key_part1字段取出滿足條件的key_part1,pk_col order by key_part1.放到緩存中（read_rnd_buffer_size），當(dāng)對應(yīng)的緩沖滿了以后，將這部分key值按照pk_col排序，最后再按照排序后的reset去取表中數(shù)據(jù)，此時pk_col1是順序的，將隨機IO轉(zhuǎn)化為順序IO，多頁數(shù)據(jù)記錄可一次性讀入或根據(jù)此次的主鍵范圍分次讀入，以減少IO操作，提高查詢效率

使用限制

MRR 適用于range、ref、eq_ref的查詢

Batched Key Access (BKA)和Block Nested-Loop(BNL)

Batched Key Access (BKA)-- 提高表join性能的算法。

當(dāng)被join的表能夠使用索引時，就先排好順序，然后再去檢索被join的表，聽起來和MRR類似，實際上MRR也可以想象成二級索引和primary key的join

如果被Join的表上沒有索引，則使用老版本的BNL策略(BLOCK Nested-loop)

BKA原理

對于多表join語句，當(dāng)MySQL使用索引訪問第二個join表的時候，使用一個join buffer來收集第一個操作對象生成的相關(guān)列值。BKA構(gòu)建好key后，批量傳給引擎層做索引查找。key是通過MRR接口提交給引擎的（mrr目的是較為順序）.這樣，MRR使得查詢更有效率。

大致的過程如下:

1 BKA使用join buffer保存由join的第一個操作產(chǎn)生的符合條件的數(shù)據(jù)

2 然后BKA算法構(gòu)建key來訪問被連接的表，并批量使用MRR接口提交keys到數(shù)據(jù)庫存儲引擎去查找查找。

3 提交keys之后，MRR使用最佳的方式來獲取行并反饋給BKA

BNL和BKA都是批量的提交一部分行給被join的表，從而減少訪問的次數(shù)，那么它們有什么區(qū)別呢？

BKA和BNL標識

Using join buffer (Batched Key Access)和Using join buffer (Block Nested Loop)

相關(guān)參數(shù)

BAK使用了MRR，要想使用BAK必須打開MRR功能，而MRR基于mrr_cost_based的成本估算并不能保證總是使用MRR，官方推薦設(shè)置mrr_cost_based=off來總是開啟MRR功能。打開BAK功能(BAK默認OFF)：

SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

BKA使用join buffer size來確定buffer的大小，buffer越大，訪問被join的表/內(nèi)部表就越順序。

BNL默認是開啟的，設(shè)置BNL相關(guān)參數(shù)：

SET optimizer_switch=’block_nested_loop’

適用場景

支持inner join, outer join, semi-join operations,including nested outer joins

BKA主要適用于join的表上有索引可利用，無索引只能使用BNL了

ICP優(yōu)化（Index Condition Pushdown）

Index Condition Pushdown (ICP)是MySQL用索引去表里取數(shù)據(jù)的一種優(yōu)化。禁用ICP(MySQL5.6之前)，引擎層會利用索引在基表中尋找數(shù)據(jù)行，然后返回給MySQL Server層，再去為這些數(shù)據(jù)行進行WHERE后的條件的過濾（回表）。啟用ICP(MySQL5.6之后)，如果部分WHERE條件能使用索引中的字段，MySQL會把這部分下推到引擎層。存儲引擎通過使用索引把滿足的行從表中讀取出。ICP減少了引擎層訪問基表的次數(shù)和MySQL Server 訪問存儲引擎的次數(shù)。總之是 ICP的優(yōu)化在引擎層就能夠過濾掉大量的數(shù)據(jù)，減少io次數(shù)，提高查詢語句性能

MRR優(yōu)化（Multi-Range Read）

Multi-Range Read 多范圍讀(MRR) 它的作用是基于輔助/第二索引的查詢，減少隨機IO，并且將隨機IO轉(zhuǎn)化為順序IO，提高查詢效率。在沒有MRR之前(MySQL5.6之前)，先根據(jù)where條件中的輔助索引獲取輔助索引與主鍵的集合，再通過主鍵來獲取對應(yīng)的值。輔助索引獲取的主鍵來訪問表中的數(shù)據(jù)會導(dǎo)致隨機的IO(輔助索引的存儲順序并非與主鍵的順序一致)，不同主鍵不在同一個page里面時必然導(dǎo)致多次IO 和隨機讀。使用MRR優(yōu)化(MySQL5.6之后)，先根據(jù)where條件中的輔助索引獲取輔助索引與主鍵的集合，再將結(jié)果集放在buffer里面(read_rnd_buffer_size 大小直到buffer滿了)，然后對結(jié)果集按照pk_column排序，得到有序的結(jié)果集rest_sort。最后利用已經(jīng)排序過的結(jié)果集，訪問表中的數(shù)據(jù)，此時是順序IO。即MySQL 將根據(jù)輔助索引獲取的結(jié)果集根據(jù)主鍵進行排序，將無序化為有序，可以用主鍵順序訪問基表，將隨機讀轉(zhuǎn)化為順序讀，多頁數(shù)據(jù)記錄可一次性讀入或根據(jù)此次的主鍵范圍分次讀入，以減少IO操作，提高查詢效率。

Nested Loop Join算法：

將驅(qū)動表/外部表的結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)，然后循環(huán)該結(jié)果集，每次獲取一條數(shù)據(jù)作為下一個表的過濾條件查詢數(shù)據(jù)，然后合并結(jié)果，獲取結(jié)果集返回給客戶端。Nested-Loop一次只將一行傳入內(nèi)層循環(huán), 所以外層循環(huán)(的結(jié)果集)有多少行, 內(nèi)存循環(huán)便要執(zhí)行多少次，效率非常差。

Block Nested-Loop Join算法：

將外層循環(huán)的行/結(jié)果集存入join buffer, 內(nèi)層循環(huán)的每一行與整個buffer中的記錄做比較，從而減少內(nèi)層循環(huán)的次數(shù)。主要用于當(dāng)被join的表上無索引。

Batched Key Access算法：

當(dāng)被join的表能夠使用索引時，就先排好順序，然后再去檢索被join的表。對這些行按照索引字段進行排序，因此減少了隨機IO。如果被Join的表上沒有索引，則使用老版本的BNL策略(BLOCK Nested-loop)