什么是索引下推

ICP（Index Condition Pushdown）是在MySQL 5.6版本上推出的查詢優化策略，把本來由Server層做的索引條件檢查下推給存儲引擎層來做，以降低回表和訪問存儲引擎的次數，提高查詢效率。

回顧下mysql的架構分層，連接層、server層、引擎層：

分析索引下推

索引下推是 MySQL 5.6引入了一種優化技術，默認開啟，查看開啟情況

select @@optimizer_switch

使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以將其關閉。

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

官方文檔中給的例子和解釋如下： people表中（zipcode，lastname，firstname）構成一個索引SELECT * FROM people WHERE zipcode=’200030′ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;

我們先來創建這個表

CREATE TABLE people (`id` int NOT NULL AUTO_INCREMENT COMMENT 'id',zipcode VARCHAR(10),lastname VARCHAR(50),firstname VARCHAR(50),address VARCHAR(200),PRIMARY KEY (`id`)
);
CREATE INDEX idx_zc_people_names ON people(zipcode,lastname, firstname);

再初始化一些數據

-- 常見姓名 + 地址 + 郵編
INSERT INTO people (zipcode, lastname, firstname, address) VALUES ('100000', '王', '偉', '北京市海淀區中關村大街1號');
INSERT INTO people (zipcode, lastname, firstname, address) VALUES ('200030', '李', '娜', '上海市徐匯區淮海中路1234弄56號');
INSERT INTO people (zipcode, lastname, firstname, address) VALUES ('510000', '張', '強', '廣州市天河區體育西路789號');
INSERT INTO people (zipcode, lastname, firstname, address) VALUES ('310012', '劉', '洋', '杭州市西湖區文三路456號');
INSERT INTO people (zipcode, lastname, firstname, address) VALUES ('518000', '陳', '靜', '深圳市福田區深南大道101號');-- 復姓 + 詳細地址
INSERT INTO people (zipcode, lastname, firstname, address) VALUES ('266071', '歐陽', '晨曦', '青島市市南區香港中路10號');
INSERT INTO people (zipcode, lastname, firstname, address) VALUES ('215006', '司馬', '浩然', '蘇州市姑蘇區干將東路288號');-- 少數民族姓名 + 地區特色地址
INSERT INTO people (zipcode, lastname, firstname, address) VALUES ('830000', '阿依', '古麗', '烏魯木齊市天山區解放南路22號');
INSERT INTO people (zipcode, lastname, firstname, address) VALUES ('550001', '扎西', '多吉', '拉薩市城關區北京中路12號');

我們執行下這個語句，分析下開啟索引下推和不開啟的區別

EXPLAIN select zipcode from people where zipcode='100000' and lastname like '%劉%' and address like '%海淀區%';

不開啟索引下推

如果沒有使用索引下推技術，則MySQL會通過zipcode=’200030’從存儲引擎中查詢對應的數據，返回到MySQL服務層，然后MySQL服務層基于lastname LIKE ‘%etrunia%’和address LIKE ‘%Main Street%’來判斷數據是否符合條件。

關閉索引下推：SET optimizer_switch = 'index_condition_pushdown=off';

Extra的內容為：Using where ，代表著服務層的條件過濾 。

開啟索引下推

如果使用了索引下推技術，則MYSQL首先會返回符合zipcode=’95054’的索引，然后根據lastname LIKE ‘%etrunia%’來判斷索引是否符合條件。

如果符合條件，則根據該索引來定位對應的數據，如果不符合，則直接reject掉。有了索引下推優化，可以在有like條件查詢的情況下，減少回表次數。

開啟索引下推：SET optimizer_switch = 'index_condition_pushdown=on';

Extra的內容為：

Using index condition ，使用了索引下推 。

Using where ，代表著服務層的條件過濾（address字段沒有在組合索引里面，所以需要在進行一次條件過濾） 。

當一條SQL使用到索引下推時，explain的執行計劃中的extra字段中內容為：Using index condition

索引下推的作用

有了索引下推的優化，在滿足一定條件下，存儲 引擎層會在回表查詢之前對數據進行過濾，可以減少存儲引擎回表查詢的次數。對于InnoDB的聚簇索引來說，完整的行記錄已經加載到緩存區了，索引下推也就沒什么意義了。

附錄

mysql的架構圖

找了一圈都沒找到5.7的架構圖，只在官網找到了8.0的https://dev.mysql.com/doc/refman/8.0/en/pluggable-storage-overview.html

mysql5.7的innodb的架構圖

https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html

1. 內存中結構

? 緩沖池（Buffer Pool）：緩存頻繁訪問的數據，減少磁盤I/O操作，提高查詢性能。

? 變更緩沖區（Change Buffer）：緩存對二級索引頁的更改，當這些頁不在緩沖池中時，以避免耗時的I/O操作。

? 自適應哈希索引（Adaptive Hash Index）：為某些讀操作提供快速的內存中查找機制，加速對頻繁查詢索引頁的訪問。

? 日志緩沖區（Log Buffer）：保存要寫入事務日志的更改，通過先寫入內存再定期刷新到磁盤上的重做日志，來提高性能。

2. 磁盤上結構

?系統表空間（System Tablespace）：存儲變更緩沖區，InnoDB使用一個或多個數據文件來存儲系統表空間。

? 各個表的獨立表空間（File-per-table Tablespaces）：每個InnoDB表都可以有自己的表空間。

? 通用表空間（General Tablespaces）：可以容納多個表的表空間。

? 撤銷表空間（Undo Tablespaces）：存儲撤銷日志，這些日志記錄了事務進行中必須保留的舊數據版本。

? 臨時表空間（Temporary Tablespaces）：存儲臨時數據，如排序操作或哈希索引創建過程中的數據。

? 雙寫緩沖區（Doublewrite Buffer）：保護數據不因崩潰而損壞，通過先寫入雙寫緩沖區再寫入表空間文件。

? 重做日志（Redo Log）：記錄數據變更，以便在系統崩潰后恢復數據。

? 撤銷日志（Undo Logs）：記錄了事務進行中必須保留的舊數據版本，以支持事務回滾和MVCC。

資料參考：

1.https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html

2.MySQL 架構_mysql架構圖-CSDN博客

原創不易，若有問題，還請批評指正，感謝！