一篇文章讀懂什么是MySQL索引下推（ICP）
更新時(shí)間：2021年09月09日 15:22:38 作者：牧碼人zhouz
當(dāng)MySQL使用一個(gè)索引來(lái)檢索表中的行時(shí),可以使用ICP作為一種優(yōu)化方案,下面這篇文章主要給大家介紹了如何通過(guò)一篇文章讀懂什么是MySQL索引下推（ICP）的相關(guān)資料,文中通過(guò)示例代碼介紹的非常詳細(xì),需要的朋友可以參考下
一、簡(jiǎn)介
ICP（Index Condition Pushdown）是在MySQL 5.6版本上推出的查詢優(yōu)化策略，把本來(lái)由Server層做的索引條件檢查下推給存儲(chǔ)引擎層來(lái)做，以降低回表和訪問(wèn)存儲(chǔ)引擎的次數(shù)，提高查詢效率。
二、原理
為了理解ICP是如何工作的，我們先了解下沒(méi)有使用ICP的情況下，MySQL是如何查詢的：
- 存儲(chǔ)引擎讀取索引記錄；
- 根據(jù)索引中的主鍵值，定位并讀取完整的行記錄；
- 存儲(chǔ)引擎把記錄交給Server層去檢測(cè)該記錄是否滿足WHERE條件。
使用ICP的情況下，查詢過(guò)程如下：
- 讀取索引記錄（不是完整的行記錄）；
- 判斷WHERE條件部分能否用索引中的列來(lái)做檢查，條件不滿足，則處理下一行索引記錄；
- 條件滿足，使用索引中的主鍵去定位并讀取完整的行記錄（就是所謂的回表）；
- 存儲(chǔ)引擎把記錄交給Server層，Server層檢測(cè)該記錄是否滿足WHERE條件的其余部分。
三、實(shí)踐
先創(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(yǔ)句：
select * from user where name="LiSi" and city like "%Z%" and age > 25;
3.1 不使用索引下推
在不使用索引下推的情況下，根據(jù)聯(lián)合索引“最左匹配”原則，只有name列能用到索引，city列由于是模糊匹配，是不能用到索引的，此時(shí)的執(zhí)行過(guò)程是這樣的：
- 存儲(chǔ)引擎根據(jù)(name, city)聯(lián)合索引，找到name值為L(zhǎng)iSi的記錄，共4條記錄；
- 然后根據(jù)這4條記錄中的id值，逐一進(jìn)行回表掃描，去聚簇索引中取出完整的行記錄，并把這些記錄返回給Server層；
- Server層接收到這些記錄，并按條件name="LiSi" and city like "%Z%" and age > 25進(jìn)行過(guò)濾，最終留下("LiSi", "ZhengZhou", 30)這條記錄。
畫(huà)張圖看一下：
未使用使用索引條件下推
3.2 使用索引下推
使用索引下推的情況下，執(zhí)行過(guò)程是這樣的：
- 存儲(chǔ)引擎根據(jù)(name, city)聯(lián)合索引，找到name='LiSi'的記錄，共4條；
- 由于聯(lián)合索引中包含city列，存儲(chǔ)引擎直接在聯(lián)合索引中按city like "%Z%"進(jìn)行過(guò)濾，過(guò)濾后剩下2條記錄；
- 根據(jù)過(guò)濾后的記錄的id值，逐一進(jìn)行回表掃描，去聚簇索引中取出完整的行記錄，并把這些記錄返回給Server層；
- Server層根據(jù)WHERE語(yǔ)句的其它條件age > 25，再次對(duì)行記錄進(jìn)行篩選，最終只留下("LiSi", "ZhengZhou", 30)這條記錄。
畫(huà)張圖看一下：
使用索引條件下推
另外，從執(zhí)行計(jì)劃里也可以看到使用了索引下推（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訪問(wèn)方法；
- 只能用于InnoDB和 MyISAM存儲(chǔ)引擎及其分區(qū)表；
- 對(duì)InnoDB存儲(chǔ)引擎來(lái)說(shuō)，索引下推只適用于二級(jí)索引（也叫輔助索引）;
tip：索引下推的目的是為了減少回表次數(shù)，也就是要減少I(mǎi)O操作。對(duì)于InnoDB的聚簇索引來(lái)說(shuō)，完整的行記錄已經(jīng)加載到緩存區(qū)了，索引下推也就沒(méi)什么意義了。
- 引用了子查詢的條件不能下推；
- 引用了存儲(chǔ)函數(shù)的條件不能下推，因?yàn)榇鎯?chǔ)引擎無(wú)法調(diào)用存儲(chǔ)函數(shù)。
五、相關(guān)系統(tǒng)參數(shù)
索引條件下推默認(rèn)是開(kāi)啟的，可以使用系統(tǒng)參數(shù)optimizer_switch來(lái)控制器是否開(kāi)啟。
查看默認(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)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
mysql之delete刪除記錄后數(shù)據(jù)庫(kù)大小不變
這篇文章主要介紹了mysql之delete刪除記錄后數(shù)據(jù)庫(kù)大小不變的相關(guān)資料,需要的朋友可以參考下2016-06-06
MySQL復(fù)制表結(jié)構(gòu)和內(nèi)容到另一張表中的SQL語(yǔ)句
這篇文章主要介紹了MySQL復(fù)制表結(jié)構(gòu)和內(nèi)容到另一張表中的SQL語(yǔ)句,需要的朋友可以參考下2014-07-07
CentOS系統(tǒng)下編譯安裝MySQL以及設(shè)置相關(guān)yum源的教程
這篇文章主要介紹了CentOS系統(tǒng)下編譯安裝MySQL以及設(shè)置相關(guān)yum源的教程,使用RedHat系的Linux系統(tǒng)為環(huán)境的都可以借鑒,需要的朋友可以參考下2015-12-12
MySQL 表空間碎片的概念及相關(guān)問(wèn)題解決
這篇文章主要介紹了MySQL 表空間碎片的概念及相關(guān)問(wèn)題解決，幫助大家更好的理解和學(xué)習(xí)使用MySQL，感興趣的朋友可以了解下2021-05-05
MySQL學(xué)習(xí)之基礎(chǔ)命令實(shí)操總結(jié)
MySQL 是最流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，在WEB應(yīng)用方面MySQL是最好的。本文將為大家詳細(xì)介紹一些MySQL的基礎(chǔ)命令，需要的可以參考一下2022-03-03
這篇文章主要給大家介紹了關(guān)于MySQL壓縮版zip安裝問(wèn)題的解決方法，文中介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者使用mysql具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-03-03
docker下mysql 8.0.20 安裝配置方法圖文教程
這篇文章主要介紹了docker下mysql 8.0.20 安裝配置方法圖文教程，文中安裝步驟介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2020-05-05
搭建MySQL主從庫(kù)過(guò)濾復(fù)制,備份指定數(shù)據(jù)庫(kù),有利于數(shù)據(jù)庫(kù)的管理,本文主要介紹了MySQL主從庫(kù)過(guò)濾復(fù)制配置教程,具有一定的參考價(jià)值,感興趣的可以了解一下2024-03-03
sql中替換函數(shù)replace()用法與實(shí)例總結(jié)
這篇文章主要給大家介紹了關(guān)于sql中替換函數(shù)replace()用法與實(shí)例的相關(guān)資料,在SQL中REPLACE函數(shù)用于替換一個(gè)字符串中的一部分為另一個(gè)字符串,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-02-02
MySQL group by對(duì)單字分組序和多字段分組的方法講解
今天小編就為大家分享一篇關(guān)于MySQL group by對(duì)單字分組序和多字段分組的方法講解，小編覺(jué)得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來(lái)看看吧2019-03-03