ICP是MYSQL使用索引從表中檢索行數據的壹種優化方式。
目標
減少從基表中讀取操作的數量，從而降低I/O操作。
禁用ICP
存儲引擎會通過遍歷索引定位基表中的行，然後返回給Server 層，在去為這些數據進行WHERE 後的條件過濾。
開啟ICP特性
如果部分where 條件能夠使用索引中的字段，那麽 MySQLServer就會把這部分下推導存儲引擎層。存儲引擎通過索引過濾，把滿足的行從表中讀取出。
效果決定於存儲引擎通過ICP篩選掉的數據的比例。如果引擎層能夠過濾掉大量的數據，就能減少I/O次數、提高查詢語句性能。
對於InnoDB 表，ICP只適用於輔助索引，當時用ICP優化時，執行計劃的Extra列顯示 Using index condition提示。
mysql開啟ICP
SET optimizer_switch="index_condition_pushdown=on"
登錄後復制
mysql關閉ICP
SET optimizer_switch="index_condition_pushdown=off"
登錄後復制
實驗舉例：
壹張表默認只有壹個主索引，因為ICP只能作用於二級索引，所以我們建立壹個二級索引。
CREATE TABLE `employees` (
`emp_no` int NOT NULL AUTO_INCREMENT,
`birth_date` date NOT NULL,
`first_name` varchar(14) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`last_name` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`gender` enum('M','F') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`) USING BTREE,
INDEX `first_name_last_name`(`first_name`, `last_name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
登錄後復制
為了明確查詢性能，啟用profiling並關閉Query Cache
SET profiling = 1;
登錄後復制
SET query_cache_type = 0;
登錄後復制
SET GLOBAL query_cache_size = 0;
登錄後復制
但是因為我用的是8.x版本的數據庫，由於在8.x版本query cache已經被廢棄了，所以這裏會提示為
Unknown system variable 'query_cache_size'
登錄後復制
在SELECT * FROM employees WHERE first_name = 'Mary' AND last_name like '%man';語句中，根據MySQL索引的前綴匹配原則，兩者對索引的使用是壹致的，即只有first_name采用索引，last_name由於采用模糊前綴，無法使用索引進行匹配。
查詢三次select，並執行show profiles.
SELECT * FROM employees WHERE first_name = 'Mary' AND last_name like '%man';
登錄後復制
show profiles;
登錄後復制
查看執行計劃，在select前加explain就可以了.
EXPLAIN SELECT * FROM employees WHERE first_name = 'Mary' AND last_name like '%man';
登錄後復制
同理，關閉ICP，在查詢三次測試
耗時
開啟ICP
關閉ICP
比較執行計劃
開啟ICP
關閉ICP
執行計劃參數含義：
type表的連接類型，如果是ALL （最差的壹種類型，從頭到尾全表掃描）
key表示查詢實際使用到的索引。
possible_keys列指出MySQL能使用哪個索引在該表中找到行。如果該列是NULL，則沒有相關的索引。
rows表示mysql在表中進行查詢時必須檢查的行數。
extra列顯示mysql在處理查詢時的詳細信息，主要包括
using index這個說明MySQL使用了覆蓋索引，避免訪問了表的數據行;
using where這說明服務器在存儲引擎收到行後將進行過濾；
using temporary說明mysql對查詢結果進行排序的時候使用了臨時表，
using filesort這個說明mysql會對數據使用壹個外部的排序，MySQL 中無法利用索引完成的排序操作稱為“文件排序”
（查詢優化）Multi-Range Read Optimization（多量程讀優化）（MRR）
MRR是優化器將隨機I/O轉化為順序I/O，目的是減少磁盤的隨機訪問，以降低查詢過程中I/O的開銷，對I/O-bound類型的SQL語句性能帶來極大的提升。
在不使用MRR時，優化器需要根據二級索引返回的記錄來進行回表，這個過程壹般會有較多的隨機I/O。
在使用MRR時，MRR的優化在於，並不是每次通過輔助索引回表取記錄，而是將rowid緩存起來，然後對rowid進行排序後在去訪問記錄，優化器將二級索引隨機的I/O進行排序，轉化為主鍵的有序排列，從而實現隨機I/O到順序I/O的轉化，大幅提升性能。
對比mrr=on 和mrr=off時的執行計劃
CREATE TABLE t1
(
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL ,
`c` int(11) DEFAULT NULL ,
PRIMARY KEY (`id`),
KEY `mrrx` (`a`,`b`),
KEY `xx` (`c`)
);
登錄後復制
在關掉MRR的情況下，當執行計劃使用的是索引xx?,即從索引 xx 上讀取壹條數據後回表，取回該主鍵的完整數據，當數據較多且比較分散的情況下會有較多的隨機I/O，導致性能低下。
SET optimizer_switch="mrr=off";
登錄後復制
EXPLAIN SELECT * FROM t1 WHERE (a BETWEEN 1 AND 10) AND (c BETWEEN 9 AND 10);
登錄後復制
基於成本的算法過於保守，導致大部分情況下優化器都不會選擇MRR 特性。為了確保優化器使用MRR特性，需要執行 SQL 語句：
SET optimizer_switch="mrr=on,mrr_cost_based=off";
登錄後復制
發現在Extra 的輸出中多了Using MRR 信息，對MRR Optimization I/O層進行了優化，可以減少 I/O 方面的開銷。
在不使用MRR之前，先根據where 條件中的輔助索引獲取輔助索引與主鍵的集合，在通過主鍵來獲取對應的值。利用輔助索引獲取的主鍵來訪問表中的數據會導致多次I/O 和隨機讀。
使用MRR 優化的好處是能使數據訪問變得較有順序。它將根據輔助索引獲取的結果集根據主鍵進行排序，將無序化為有序，可以用主鍵順序訪問基表，將隨機讀轉化為順序讀，多頁數據記錄可壹次性讀入或根據此次的主鍵範圍分次讀入，減少 I/O操作，提高查詢效率。
相關參數：
mrr=on,mrr_cost_based=on: 表示cost base 的方式還選擇啟用MRR優化，當發現優化後的代價過高時就會不使用該項優化。
mrr=on,mrr_cost_based=off: 表示總是開啟MRR優化。
（查詢優化）Batched Key Access（批處理鍵訪問）(BKA)
BKA是提高表join性能的算法，是在表連接的過程中為了提升join 性能而使用的壹種 join buffer ，作用是在讀取被連接表的記錄時使用順序I/O。
對於嵌套循環，如果關聯的表數據量很大，那麽join 關聯的時間會很長，後來引入了BNL（Block Nested Loop）算法來優化嵌套循環。BNL算法通過使用在外部循環中讀取行的緩沖來減少內部循環中的表必須被讀取的次數。
BKA的原理是對於多表join語句，將外部表中相關的列放入join buffer 中。批量地將Key（索引鍵值）發送到Multi-Range Read （MRR）接口。Multi-Range Read（MRR）根據收到的Key 對應的ROWID進行排序，然後進行數據得到讀取操作。
BKA join 算法將能極大地提高 SQL 的執行效率，特別是在內表上有索引並且該索引為非主鍵，聯表需要訪問內部表主鍵上的索引情況下。這時BKA算法會調用 Multi-Range Read （MRR）接口，批量地進行索引鍵的匹配和主鍵索引上獲取數據的操作，以此來提高聯接的執行效率，因為讀取數據是以順序磁盤I/O而不是隨機磁盤I/O進行的。
BKA使用 join buffer size 來確定buffer的大小，buffer越大緩沖區越大，對聯表操作的右側表的順序訪問就越多，可以顯著提高性能。
創建表employees.
CREATE TABLE employees (
emp_no INT NOT NULL,
birth_date DATE NOT NULL,
first_name VARCHAR(14) NOT NULL,
last_name VARCHAR(16) NOT NULL,
gender ENUM ('M','F') NOT NULL,
hire_date DATE NOT NULL,
PRIMARY KEY (emp_no)
);
登錄後復制
創建表dept_emp
CREATE TABLE dept_emp (
emp_no INT NOT NULL,
dept_no CHAR(4) NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no,dept_no)
);
登錄後復制
啟動BKA
SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
登錄後復制
查詢計劃
EXPLAIN SELECT a.gender,b.dept_no FROM employees a,dept_emp b WHERE a.birth_date=b.from_date;
登錄後復制
添加索引在查看查詢計劃
ALTER TABLE employees ADD INDEX (birth_date);
登錄後復制
EXPLAIN SELECT a.gender,b.dept_no FROM employees a,dept_emp b WHERE a.birth_date=b.from_date;
登錄後復制
索引
mysql
數據庫
索引優化
算法
高清播放機，圖片大全，點擊查看詳情！
精選推薦
廣告MYSQL的索引優化技術ICP、MRR、BKA
109閱讀·0評論·0點贊
2020年5月25日
mysql關於ICP、MRR、BKA等特性
113閱讀·0評論·0點贊
2020年4月30日
隨寫02 關於ICP、MRR、BKA等特性
133閱讀·0評論·0點贊
2020年9月8日
Mysql優化算法-MRR(Multi-Range Read Optimization)
287閱讀·0評論·0點贊
2022年9月7日
mysql數據庫BKA算法詳解
702閱讀·0評論·0點贊
2021年8月16日
索引下推ICP詳解
813閱讀·0評論·3點贊
2020年4月29日
播放機-高清視頻播放器，壹鍵直達！
精選推薦
廣告MySQL-小章節-ICP/MRR/BKA特性
50閱讀·0評論·0點贊
2022年6月1日
bka mysql,MYSQL之ICP、MRR、BKA
38閱讀·0評論·0點贊
2021年4月29日
MySQL--BNL/ICP/MRR/BKA
98閱讀·0評論·0點贊
2018年6月27日
優化器 MRR & BKA
449閱讀·0評論·0點贊
2016年2月3日
MySQL---MRR策略和BKA算法
211閱讀·0評論·0點贊
2021年7月13日
InnoDB ICP 代碼路徑
78閱讀·0評論·0點贊
2016年5月10日
InnoDB ICP、MRR、BAK特性
335閱讀·1評論·1點贊
2020年8月15日
二級索引和回表優化之MRR
168閱讀·0評論·0點贊
2022年1月5日
mysql mrr_cost_based_MySQL MRR介紹
79閱讀·0評論·0點贊
2021年1月19日
優化的道路永無止境——Mysql的ICP及MRR
149閱讀·0評論·0點贊
2020年12月3日
mysql 5.7中的MBR和BKA算法
955閱讀·0評論·0點贊
2017年10月3日
說說mysql的MRR,ICP,BKA優化。
404閱讀·0評論·2點贊
2020年3月7日
學習MYSQL之ICP、MRR、BKA
4234閱讀·0評論·2點贊
2016年8月14日
mysql使用bka_MySQL Batched Key Access (BKA)原理和設置使用方法舉例
328閱讀·0評論·0點贊
2021年1月19日
去首頁
看看更多熱門內容
評論1
Xd聊架構
贊
來互動吧，幫我點贊最新的壹篇文章，謝謝！！！