MySQL索引下推index condition pushdown
索引下推(Index Condition Pushdown,簡稱ICP)是MySQL 5.6版本引入的一種數(shù)據(jù)庫查詢優(yōu)化技術(shù)。ICP的主要目的是通過利用數(shù)據(jù)庫引擎中的索引和過濾條件,將部分過濾工作下推到存儲引擎層面進(jìn)行處理,從而減少不必要的數(shù)據(jù)讀取和傳輸,提升查詢性能和整體系統(tǒng)效率。
索引下推的工作原理
在傳統(tǒng)的查詢執(zhí)行過程中,數(shù)據(jù)庫引擎首先根據(jù)索引定位到符合過濾條件的數(shù)據(jù)行,并將這些數(shù)據(jù)行讀取到內(nèi)存中,然后再進(jìn)一步進(jìn)行過濾操作。而索引下推則在這一步驟中盡可能地將過濾操作下推到存儲引擎層面,避免將不符合條件的數(shù)據(jù)行讀取到內(nèi)存中。具體實(shí)現(xiàn)方式可以是通過存儲引擎提供的接口或者鉤子函數(shù),讓存儲引擎在讀取索引頁時(shí)就進(jìn)行額外的過濾操作。
索引下推的優(yōu)勢
- 減少數(shù)據(jù)讀取和傳輸:通過索引下推,可以在存儲引擎層面就過濾掉不符合條件的數(shù)據(jù),減少了需要傳遞給查詢引擎的數(shù)據(jù)量和內(nèi)存消耗。
- 降低磁盤I/O:由于減少了不必要的數(shù)據(jù)讀取,因此也降低了磁盤I/O的次數(shù),這對于提升查詢性能尤為重要。
- 提高查詢效率:在復(fù)雜查詢條件、多列條件的查詢中,索引下推能夠更有效地減少不必要的數(shù)據(jù)讀取和傳輸,從而提高查詢效率。
索引下推的應(yīng)用場景
- 索引下推并不是對所有類型的查詢都適用,它更適用于那些包含復(fù)雜查詢條件、多列條件的查詢場景。在這些場景中,索引下推能夠顯著減少查詢過程中的數(shù)據(jù)讀取和傳輸量,從而提升查詢性能。
索引下推與回表查詢
- 在聯(lián)合索引的場景中,索引下推還可以減少回表查詢的次數(shù)。例如,當(dāng)為age和name字段創(chuàng)建了聯(lián)合索引,并執(zhí)行查詢語句SELECT * FROM EMPLOYEES WHERE first_name LIKE ‘James’ AND last_name = ‘Landry’;時(shí),如果沒有使用索引下推,MySQL會(huì)根據(jù)聯(lián)合索引查詢first_name 字段等于"James"的數(shù)據(jù),然后進(jìn)行回表查詢以獲取完整的數(shù)據(jù)行。而使用索引下推后,MySQL會(huì)在存儲引擎層面就進(jìn)一步判斷l(xiāng)ast_name 是否等于Landry,只有同時(shí)滿足這兩個(gè)條件的數(shù)據(jù)行才會(huì)被讀取并返回給查詢引擎,從而減少了回表查詢的次數(shù)。
索引下推的啟用與關(guān)閉
select @@optimizer_switch -- 默認(rèn)是打開的 -- 關(guān)閉ICP set optimizer_switch = 'index_condition_pushdown=off'; -- 打開ICP set optimizer_switch = 'index_condition_pushdown=on'; set profiling=1 -- 使用索引下推 SELECT * FROM EMPLOYEES WHERE first_name LIKE 'James' AND last_name = 'Landry'; (root@localhost) 09:12:52 [test1]> SELECT * FROM EMPLOYEES WHERE first_name LIKE 'James' AND last_name = 'Landry'; +-------------+------------+-----------+---------+---------------+------------+----------+---------+----------------+------------+---------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_decimal | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | +-------------+------------+-----------+---------+---------------+------------+----------+---------+----------------+------------+---------------+ | 127 | James | Landry | JLANDRY | 650.124.1334 | 2007-01-14 | ST_CLERK | 2400.00 | NULL | 120 | 50 | +-------------+------------+-----------+---------+---------------+------------+----------+---------+----------------+------------+---------------+ 1 row in set (0.00 sec) (root@localhost) 09:08:52 [test1]> explain SELECT * FROM EMPLOYEES WHERE first_name LIKE 'James' AND last_name = 'Landry'; +----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | EMPLOYEES | NULL | range | EMP_NAME_IX | EMP_NAME_IX | 140 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.01 sec) show profiles; show profile for query 1; show profile for query 2;
禁用 索引下推,觀察執(zhí)行計(jì)劃
禁用 Index Condition Pushdown(ICP) 索引下推 (root@localhost) 09:22:32 [test1]> explain SELECT /*+ no_ipc () */ * FROM EMPLOYEES WHERE first_name LIKE 'James' AND last_name = 'Landry'; +----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | EMPLOYEES | NULL | range | EMP_NAME_IX | EMP_NAME_IX | 140 | NULL | 1 | 100.00 | Using where | +----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) (root@localhost) 09:22:46 [test1]> explain SELECT * FROM EMPLOYEES WHERE first_name LIKE 'James' AND last_name = 'Landry'; +----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | EMPLOYEES | NULL | range | EMP_NAME_IX | EMP_NAME_IX | 140 | NULL | 1 | 100.00 | Using where | +----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
總結(jié)
索引下推是MySQL 5.6及以上版本提供的一種查詢優(yōu)化技術(shù),它通過將部分過濾工作下推到存儲引擎層面進(jìn)行處理,減少了不必要的數(shù)據(jù)讀取和傳輸,降低了磁盤I/O次數(shù),提高了查詢性能和整體系統(tǒng)效率。在復(fù)雜查詢條件、多列條件的查詢場景中,索引下推能夠發(fā)揮更大的作用。
到此這篇關(guān)于MySQL索引下推index condition pushdown的文章就介紹到這了,更多相關(guān)MySQL索引下推內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
mysql聚合統(tǒng)計(jì)數(shù)據(jù)查詢緩慢的優(yōu)化方法
這篇文章主要給大家介紹了關(guān)于mysql聚合統(tǒng)計(jì)數(shù)據(jù)查詢緩慢的優(yōu)化方法,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02自學(xué)MySql內(nèi)置函數(shù)知識點(diǎn)總結(jié)
在本篇文章里小編給大家整理的是關(guān)于MySql內(nèi)置函數(shù)的知識點(diǎn)總結(jié)內(nèi)容,需要的朋友們可以學(xué)習(xí)參考下。2020-01-01Centos6.4編譯安裝mysql 8.0.0 詳細(xì)教程
這篇文章主要為大家分享了Centos6.4編譯安裝mysql 8.0.0 詳細(xì)教程,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2016-11-11MySQL實(shí)現(xiàn)自動(dòng)化部署腳本的詳細(xì)教程
在當(dāng)前的DevOps環(huán)境中,自動(dòng)化部署已成為提升運(yùn)維效率的核心手段,本教程將手把手教你編寫一個(gè)智能化的MySQL部署腳本,感興趣的小伙伴跟著小編一起來看看吧2025-03-03mysql學(xué)習(xí)筆記之?dāng)?shù)據(jù)引擎
插件式存儲引擎是MySQL數(shù)據(jù)庫最重要的特征之一,用戶可以根據(jù)應(yīng)用的需要尋找如何存儲和索引數(shù)據(jù)、是否使用事務(wù)等。MySQL默認(rèn)支持多種存儲引擎,以適用于不同領(lǐng)域的數(shù)據(jù)庫應(yīng)用需求,用戶可以通過選擇選擇不同的存儲引擎提供應(yīng)用的效率,提供靈活的存儲2017-02-02MySQL問答系列之什么情況下會(huì)用到臨時(shí)表
MySQL在很多情況下都會(huì)用到臨時(shí)表,下面這篇文章主要給大家介紹了關(guān)于MySQL在什么情況下會(huì)用到臨時(shí)表的相關(guān)資料,文中介紹的非常詳細(xì),需要的朋友可以參考借鑒,下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-09-09