快捷導(dǎo)航

MySQL索引下推index condition pushdown

更新時間：2024年08月05日 10:20:47 作者：zxrhhm

索引下推是MySQL 5.6版本引入的一種數(shù)據(jù)庫查詢優(yōu)化技術(shù),本文主要介紹了MySQL索引下推index condition pushdown,具有一定的參考價值,感興趣的可以了解一下

索引下推（Index Condition Pushdown，簡稱ICP）是MySQL 5.6版本引入的一種數(shù)據(jù)庫查詢優(yōu)化技術(shù)。ICP的主要目的是通過利用數(shù)據(jù)庫引擎中的索引和過濾條件，將部分過濾工作下推到存儲引擎層面進行處理，從而減少不必要的數(shù)據(jù)讀取和傳輸，提升查詢性能和整體系統(tǒng)效率。

索引下推的工作原理

在傳統(tǒng)的查詢執(zhí)行過程中，數(shù)據(jù)庫引擎首先根據(jù)索引定位到符合過濾條件的數(shù)據(jù)行，并將這些數(shù)據(jù)行讀取到內(nèi)存中，然后再進一步進行過濾操作。而索引下推則在這一步驟中盡可能地將過濾操作下推到存儲引擎層面，避免將不符合條件的數(shù)據(jù)行讀取到內(nèi)存中。具體實現(xiàn)方式可以是通過存儲引擎提供的接口或者鉤子函數(shù)，讓存儲引擎在讀取索引頁時就進行額外的過濾操作。

索引下推的優(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’;時，如果沒有使用索引下推，MySQL會根據(jù)聯(lián)合索引查詢first_name 字段等于"James"的數(shù)據(jù)，然后進行回表查詢以獲取完整的數(shù)據(jù)行。而使用索引下推后，MySQL會在存儲引擎層面就進一步判斷l(xiāng)ast_name 是否等于Landry，只有同時滿足這兩個條件的數(shù)據(jù)行才會被讀取并返回給查詢引擎，從而減少了回表查詢的次數(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í)行計劃

禁用 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ù)，它通過將部分過濾工作下推到存儲引擎層面進行處理，減少了不必要的數(shù)據(jù)讀取和傳輸，降低了磁盤I/O次數(shù)，提高了查詢性能和整體系統(tǒng)效率。在復(fù)雜查詢條件、多列條件的查詢場景中，索引下推能夠發(fā)揮更大的作用。

到此這篇關(guān)于MySQL索引下推index condition pushdown的文章就介紹到這了,更多相關(guān)MySQL索引下推內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: