一篇文章讀懂什么是MySQL索引下推（ICP）
一、簡介
ICP（Index Condition Pushdown）是在MySQL 5.6版本上推出的查詢優(yōu)化策略，把本來由Server層做的索引條件檢查下推給存儲引擎層來做，以降低回表和訪問存儲引擎的次數(shù)，提高查詢效率。
二、原理
為了理解ICP是如何工作的，我們先了解下沒有使用ICP的情況下，MySQL是如何查詢的：
- 存儲引擎讀取索引記錄；
- 根據(jù)索引中的主鍵值，定位并讀取完整的行記錄；
- 存儲引擎把記錄交給Server層去檢測該記錄是否滿足WHERE條件。
使用ICP的情況下，查詢過程如下：
- 讀取索引記錄（不是完整的行記錄）；
- 判斷WHERE條件部分能否用索引中的列來做檢查，條件不滿足，則處理下一行索引記錄；
- 條件滿足，使用索引中的主鍵去定位并讀取完整的行記錄（就是所謂的回表）；
- 存儲引擎把記錄交給Server層，Server層檢測該記錄是否滿足WHERE條件的其余部分。
三、實踐
先創(chuàng)建一張表，并插入記錄
CREATE TABLE user (
id int(11) NOT NULL AUTO_INCREMENT COMMENT "主鍵",
name varchar(32) COMMENT "姓名",
city varchar(32) COMMENT "城市",
age int(11) COMMENT "年齡",
primary key(id),
key idx_name_city(name, city)
)engine=InnoDB default charset=utf8;
insert into user(name, city, age) values("ZhaoDa", "BeiJing", 20),("QianEr", "ShangHai", 21),("SunSan", "GuanZhou", 22), ("LiSi", "ShenZhen", 24), ("ZhouWu", "NingBo", 25), ("WuLiu", "HangZhou", 26), ("ZhengQi", "NanNing", 27), ("WangBa", "YinChuan", 28), ("LiSi", "TianJin", 29), ("ZhangSan", "NanJing", 30), ("CuiShi", "ZhengZhou", 65), ("LiSi", "KunMing", 29), ("LiSi", "ZhengZhou", 30);
查看一下表記錄
mysql> select * from user; +----+----------+-----------+------+ | id | name | city | age | +----+----------+-----------+------+ | 1 | ZhaoDa | BeiJing | 20 | | 2 | QianEr | ShangHai | 21 | | 3 | SunSan | GuanZhou | 22 | | 4 | LiSi | ShenZhen | 24 | | 5 | ZhouWu | NingBo | 25 | | 6 | WuLiu | HangZhou | 26 | | 7 | ZhengQi | NanNing | 27 | | 8 | WangBa | YinChuan | 28 | | 9 | LiSi | TianJin | 29 | | 10 | ZhangSan | NanJing | 30 | | 11 | CuiShi | ZhengZhou | 65 | | 12 | LiSi | KunMing | 29 | | 13 | LiSi | ZhengZhou | 30 | +----+----------+-----------+------+ 13 rows in set (0.00 sec)
注意，這張表里創(chuàng)建了聯(lián)合索引(name, city)，假設(shè)我們想查詢?nèi)缦抡Z句：
select * from user where name="LiSi" and city like "%Z%" and age > 25;
3.1 不使用索引下推
在不使用索引下推的情況下，根據(jù)聯(lián)合索引“最左匹配”原則，只有name列能用到索引，city列由于是模糊匹配，是不能用到索引的，此時的執(zhí)行過程是這樣的：
- 存儲引擎根據(jù)(name, city)聯(lián)合索引，找到name值為LiSi的記錄，共4條記錄；
- 然后根據(jù)這4條記錄中的id值，逐一進(jìn)行回表掃描，去聚簇索引中取出完整的行記錄，并把這些記錄返回給Server層；
- Server層接收到這些記錄，并按條件name="LiSi" and city like "%Z%" and age > 25進(jìn)行過濾，最終留下("LiSi", "ZhengZhou", 30)這條記錄。
畫張圖看一下：
未使用使用索引條件下推
3.2 使用索引下推
使用索引下推的情況下，執(zhí)行過程是這樣的：
- 存儲引擎根據(jù)(name, city)聯(lián)合索引，找到name='LiSi'的記錄，共4條；
- 由于聯(lián)合索引中包含city列，存儲引擎直接在聯(lián)合索引中按city like "%Z%"進(jìn)行過濾，過濾后剩下2條記錄；
- 根據(jù)過濾后的記錄的id值，逐一進(jìn)行回表掃描，去聚簇索引中取出完整的行記錄，并把這些記錄返回給Server層；
- Server層根據(jù)WHERE語句的其它條件age > 25，再次對行記錄進(jìn)行篩選，最終只留下("LiSi", "ZhengZhou", 30)這條記錄。
畫張圖看一下：
使用索引條件下推
另外，從執(zhí)行計劃里也可以看到使用了索引下推（Extra里顯示Using index condition）
mysql> explain select * from user where name="LiSi" and city like "%Z%" and age > 25; +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+------------------------------------+ | 1 | SIMPLE | user | NULL | ref | idx_name_city | idx_name_city | 99 | const | 4 | 7.69 | Using index condition; Using where | +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+------------------------------------+ 1 row in set, 1 warning (0.00 sec)
四、使用條件
- 只能用于range、 ref、 eq_ref、ref_or_null訪問方法；
- 只能用于InnoDB和 MyISAM存儲引擎及其分區(qū)表；
- 對InnoDB存儲引擎來說，索引下推只適用于二級索引（也叫輔助索引）;
tip：索引下推的目的是為了減少回表次數(shù)，也就是要減少IO操作。對于InnoDB的聚簇索引來說，完整的行記錄已經(jīng)加載到緩存區(qū)了，索引下推也就沒什么意義了。
- 引用了子查詢的條件不能下推；
- 引用了存儲函數(shù)的條件不能下推，因為存儲引擎無法調(diào)用存儲函數(shù)。
五、相關(guān)系統(tǒng)參數(shù)
索引條件下推默認(rèn)是開啟的，可以使用系統(tǒng)參數(shù)optimizer_switch來控制器是否開啟。
查看默認(rèn)狀態(tài)：
mysql> select @@optimizer_switch\G; *************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on 1 row in set (0.00 sec)
切換狀態(tài)：
set optimizer_switch="index_condition_pushdown=off"; set optimizer_switch="index_condition_pushdown=on";
總結(jié)
到此這篇關(guān)于什么是MySQL索引下推（ICP）的文章就介紹到這了,更多相關(guān)MySQL索引下推（ICP）內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信