學習MYSQL之ICP、MRR、BKA
Index Condition Pushdown(ICP)
Index Condition Pushdown (ICP)是mysql使用索引從表中檢索行資料的一種最佳化方式。
ICP原理
禁用ICP,儲存引擎會透過遍歷索引定位基表中的行,然後返回給MySQL Server層,再去為這些資料行進行WHERE後的條件的過濾。
開啟ICP,如果部分WHERE條件能使用索引中的欄位,MySQL Server 會把這部分下推到儲存引擎層,儲存引擎透過索引過濾,把滿足的行從表中讀取出。ICP能減少引擎層訪問基表的次數和MySQL Server 訪問儲存引擎的次數。
ICP的目標是減少從基表中全紀錄讀取操作的數量,從而降低IO操作
對於InnoDB表,ICP只適用於輔助索引。
ICP標識
當使用ICP最佳化時,執行計劃的Extra列顯示Using indexcondition提示
相關引數
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,此時想查詢相關資訊。
若不使用ICP:則是透過二級索引中zipcode的值去基表取出所有zipcode='95054'的資料,然後server層再對lastname LIKE '%etrunia%'AND address LIKE '%Main Street%';進行過濾
若使用ICP:則lastname LIKE '%etrunia%'AND address LIKE '%Main Street%'的過濾操作在二級索引中完成,然後再去基表取相關資料
使用限制
l 只支援 select 語句
l 5.6 中只支援 MyISAM與InnoDB引擎
l 5.6中不支援分割槽表的ICP;從MySQL 5.7.3開始支援分割槽表的ICP
l ICP的最佳化策略可用於range、ref、eq_ref、ref_or_null 型別的訪問資料方法;
l 不支援主建索引的ICP(對於Innodb的聚集索引,完整的記錄已經被讀取到Innodb Buffer,此時使用ICP並不能降低IO操作)
l 當 SQL 使用覆蓋索引時但只檢索部分資料時,ICP 無法使用
l ICP的加速效果取決於在儲存引擎內透過ICP篩選掉的資料的比例。
Multi-Range Read (MRR)
MRR 的全稱是 Multi-Range Read Optimization,是最佳化器將隨機 IO 轉化為順序 IO 以降低查詢過程中 IO 開銷的一種手段。
MRR原理
select non_key_column from tb where ey_column=x;
在沒有使用MRR特性時,MySQL執行查詢的虛擬碼
第一步 先根據where條件中的輔助索引獲取輔助索引與主鍵的集合,結果集為rest。
select key_column, pk_column from tb where key_column=x order by key_column
第二步 透過第一步獲取的主鍵來獲取對應的值。
for each pk_column value in rest do:
select non_key_column from tb where pk_column=val
使用MRR特性時,MySQL執行查詢的虛擬碼
第一步 先根據where條件中的輔助索引獲取輔助索引與主鍵的集合,結果集為rest
select key_column, pk_column from tb where key_column = x order by key_column
第二步 將結果集rest放在buffer裡面(read_rnd_buffer_size 大小直到buffer滿了),然後對結果集rest按照pk_column排序,得到結果集是rest_sort
第三步 利用已經排序過的結果集,訪問表中的資料,此時是順序IO.
select non_key_column fromtb where pk_column in (rest_sort)
綜上
在不使用 MRR 時,最佳化器需要根據二級索引返回的記錄來進行“回表”,這個過程一般會有較多的隨機IO, 使用MRR時,SQL語句的執行過程是這樣的:
1) 最佳化器將二級索引查詢到的記錄放到一塊緩衝區中
2) 如果二級索引掃描到檔案的末尾或者緩衝區已滿,則使用快速排序對緩衝區中的內容按照主鍵進行排序
3) 使用者執行緒呼叫MRR介面取cluster index,然後根據cluster index 取行資料
4) 當根據緩衝區中的 cluster index取完資料,則繼續呼叫過程 2) 3),直至掃描結束
透過上述過程,最佳化器將二級索引隨機的 IO 進行排序,轉化為主鍵的有序排列,從而實現了隨機 IO 到順序 IO 的轉化,提升效能
此外MRR還可以將某些範圍查詢,拆分為鍵值對,來進行批次的資料查詢,如下:
SELECT * FROM tWHERE key_part1 >= 1000 AND key_part1 < 2000AND key_part2 = 10000;
表t上有二級索引(key_part1, key_part2),索引根據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標識
當使用ICP最佳化時,執行計劃的Extra列顯示Using MRR提示
相關引數
用optimizer_switch 的標記來控制是否使用MRR.設定mrr=on時,表示啟用MRR最佳化。
mrr_cost_based表示是否透過cost base的方式來啟用MRR.
當mrr=on,mrr_cost_based=on,則表示cost base的方式還選擇啟用MRR最佳化,當發現最佳化後的代價過高時就會不使用該項最佳化
當mrr=on,mrr_cost_based=off,則表示總是開啟MRR最佳化
SET @@optimizer_switch='mrr=on,mrr_cost_based=on';
引數read_rnd_buffer_size 用來控制鍵值緩衝區的大小。二級索引掃描到檔案的末尾或者緩衝區已滿,則使用快速排序對緩衝區中的內容按照主鍵進行排序
適用場景
#輔助索引key_part1,查詢key_part1在1000到2000範圍內的資料
SELECT * FROM t WHERE key_part1 >= 1000 AND key_part1 < 2000
不使用MRR:先透過二級索引的key_part1欄位取出滿足條件的key_part1,pk_col order by key_part1.然後透過pk_col去表中取出滿足條件的資料,此時,因為取出的pk_col是亂序的,而表又是pk_col存放資料的,當去表中取資料時,則會產生大量的隨機IO
使用MRR:先透過二級索引的key_part1欄位取出滿足條件的key_part1,pk_col order by key_part1.放到快取中(read_rnd_buffer_size),當對應的緩衝滿了以後,將這部分key值按照pk_col排序,最後再按照排序後的reset去取表中資料,此時pk_col1是順序的,將隨機IO轉化為順序IO,多頁資料記錄可一次性讀入或根據此次的主鍵範圍分次讀入,以減少IO操作,提高查詢效率
使用限制
MRR 適用於range、ref、eq_ref的查詢
Batched Key Access (BKA)和Block Nested-Loop(BNL)
Batched Key Access (BKA)-- 提高表join效能的演算法。
當被join的表能夠使用索引時,就先排好順序,然後再去檢索被join的表,聽起來和MRR類似,實際上MRR也可以想象成二級索引和primary key的join
如果被Join的表上沒有索引,則使用老版本的BNL策略(BLOCK Nested-loop)
BKA原理
對於多表join語句,當MySQL使用索引訪問第二個join表的時候,使用一個join buffer來收集第一個操作物件生成的相關列值。BKA構建好key後,批次傳給引擎層做索引查詢。key是透過MRR介面提交給引擎的(mrr目的是較為順序).這樣,MRR使得查詢更有效率。
大致的過程如下:
1 BKA使用join buffer儲存由join的第一個操作產生的符合條件的資料
2 然後BKA演算法構建key來訪問被連線的表,並批次使用MRR介面提交keys到資料庫儲存引擎去查詢查詢。
3 提交keys之後,MRR使用最佳的方式來獲取行並反饋給BKA
BNL和BKA都是批次的提交一部分行給被join的表,從而減少訪問的次數,那麼它們有什麼區別呢?
第一 BNL比BKA出現的早,BKA直到5.6才出現,而NBL至少在5.1裡面就存在。
第二 BNL主要用於當被join的表上無索引
第三 BKA主要是指在被join表上有索引可以利用,那麼就在行提交給被join的表之前,對這些行按照索引欄位進行排序,因此減少了隨機IO,排序這才是兩者最大的區別,但是如果被join的表沒用索引呢?那就使用NBL了。
BKA和BNL標識
Using join buffer (Batched Key Access)和Using join buffer (Block Nested Loop)
相關引數
BAK使用了MRR,要想使用BAK必須開啟MRR功能,而MRR基於mrr_cost_based的成本估算並不能保證總是使用MRR,官方推薦設定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的表/內部表就越順序。
BNL預設是開啟的,設定BNL相關引數:
SET optimizer_switch=’block_nested_loop’
適用場景
支援inner join, outer join, semi-join operations,including nested outer joins
BKA主要適用於join的表上有索引可利用,無索引只能使用BNL了
簡單總結
以下部分來源:http://www.cnblogs.com/zhoujinyi/p/4746483.html
ICP最佳化(Index Condition Pushdown)
Index Condition Pushdown (ICP)是MySQL用索引去表裡取資料的一種最佳化。禁用ICP(MySQL5.6之前),引擎層會利用索引在基表中尋找資料行,然後返回給MySQL Server層,再去為這些資料行進行WHERE後的條件的過濾(回表)。啟用ICP(MySQL5.6之後),如果部分WHERE條件能使用索引中的欄位,MySQL會把這部分下推到引擎層。儲存引擎透過使用索引把滿足的行從表中讀取出。ICP減少了引擎層訪問基表的次數和MySQL Server 訪問儲存引擎的次數。總之是 ICP的最佳化在引擎層就能夠過濾掉大量的資料,減少io次數,提高查詢語句效能
MRR最佳化(Multi-Range Read)
Multi-Range Read 多範圍讀(MRR) 它的作用是基於輔助/第二索引的查詢,減少隨機IO,並且將隨機IO轉化為順序IO,提高查詢效率。在沒有MRR之前(MySQL5.6之前),先根據where條件中的輔助索引獲取輔助索引與主鍵的集合,再透過主鍵來獲取對應的值。輔助索引獲取的主鍵來訪問表中的資料會導致隨機的IO(輔助索引的儲存順序並非與主鍵的順序一致),不同主鍵不在同一個page裡面時必然導致多次IO 和隨機讀。使用MRR最佳化(MySQL5.6之後),先根據where條件中的輔助索引獲取輔助索引與主鍵的集合,再將結果集放在buffer裡面(read_rnd_buffer_size 大小直到buffer滿了),然後對結果集按照pk_column排序,得到有序的結果集rest_sort。最後利用已經排序過的結果集,訪問表中的資料,此時是順序IO。即MySQL 將根據輔助索引獲取的結果集根據主鍵進行排序,將無序化為有序,可以用主鍵順序訪問基表,將隨機讀轉化為順序讀,多頁資料記錄可一次性讀入或根據此次的主鍵範圍分次讀入,以減少IO操作,提高查詢效率。
Nested Loop Join演算法:
將驅動表/外部表的結果集作為迴圈基礎資料,然後迴圈該結果集,每次獲取一條資料作為下一個表的過濾條件查詢資料,然後合併結果,獲取結果集返回給客戶端。Nested-Loop一次只將一行傳入內層迴圈, 所以外層迴圈(的結果集)有多少行, 記憶體迴圈便要執行多少次,效率非常差。
Block Nested-Loop Join演算法:
將外層迴圈的行/結果集存入join buffer, 內層迴圈的每一行與整個buffer中的記錄做比較,從而減少內層迴圈的次數。主要用於當被join的表上無索引。
Batched Key Access演算法:
當被join的表能夠使用索引時,就先排好順序,然後再去檢索被join的表。對這些行按照索引欄位進行排序,因此減少了隨機IO。如果被Join的表上沒有索引,則使用老版本的BNL策略(BLOCK Nested-loop)。
參考:
http://dev.mysql.com/doc/refman/5.7/en/select-optimization.html
http://blog.itpub.net/22664653
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/27000195/viewspace-2083826/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL MRR和ICP介紹MySql
- MySQL學習之explainMySqlAI
- MySQL學習之索引MySql索引
- MySQL中的Join 的演算法(NLJ、BNL、BKA)MySql演算法
- MySQL之檢視學習MySql
- PCL之ICP演算法演算法
- MySQL Index Condition Pushdown(ICP)的使用限制MySqlIndex
- MySQL學習之flush(刷髒頁)MySql
- MySQL Batched Key Access (BKA)原理和設定使用方法舉例MySqlBAT
- MySQL:關於ICP特性的說明(未完)MySql
- MySQL優化學習筆記之explainMySql優化筆記AI
- MySQL優化學習筆記之索引MySql優化筆記索引
- MySQL 學習之索引篇和查詢MySql索引
- Docker學習之搭建MySql容器服務DockerMySql
- MySQL學習之change buffer 和 redo logMySql
- jmeter學習指南之操作 mysql 資料庫JMeterMySql資料庫
- MySQL 5.7 學習心得之安全相關特性MySql
- MySQL學習之MVCC原理和當前讀MySqlMVC
- MySQL學習之全域性鎖和表鎖MySql
- Mysql學習MySql
- MySQL 學習MySql
- 學習MySQLMySql
- Mysql學習筆記---MySQL叢集架構之擴容方案MySql筆記架構
- Point Cloud Library學習之ICP迭代最近點匹配法NDT2D正態分佈轉換法Cloud
- 從零開始一起學習SALM-ICP原理及應用
- mysql學習筆記之備份與恢復MySql筆記
- MYSQL學習(二) --MYSQL框架MySql框架
- Mysql學習教程MySql
- MySQL學習 - 索引MySql索引
- MySQL深度學習MySql深度學習
- 學習mysql之後的一點總結(基礎)MySql
- 前端學習之Bootstrap學習前端boot
- MySQL學習系列之InnoDB下事務隔離機制MySql
- mysql學習筆記之快速搭建PXC叢集(Mycat分片)MySql筆記
- MYSQL-mysqldump學習MySql
- MySQL學習記錄MySql
- MySql學習筆記MySql筆記
- MYSQL學習總結MySql
- MySQL學習之行鎖MySql