MySQL索引下推的實現(xiàn)示例
索引下推(Index Condition Pushdown,簡稱 ICP)是 MySQL 5.6 引入的一項優(yōu)化技術(shù),它通過將部分查詢條件“下推”到索引掃描階段,從而減少不必要的行訪問和回表操作,提高查詢性能。
1. 索引下推的概念
在傳統(tǒng)的索引掃描過程中,MySQL 會首先通過索引找到符合索引條件的記錄,然后回表(即訪問實際的表數(shù)據(jù)行)讀取所需的列,最后再應用其他過濾條件(非索引條件)來判斷這條記錄是否符合查詢要求。這意味著即使某些記錄最終被過濾掉了,MySQL 也必須先回表讀取它們的全部數(shù)據(jù),這會導致額外的 I/O 操作和性能開銷。
索引下推優(yōu)化的思想是:在索引掃描階段,將部分查詢條件直接應用于索引記錄,從而減少回表操作。只有在索引中滿足所有條件的記錄才會被回表讀取其完整數(shù)據(jù)。
2. 索引下推的工作原理
索引下推的工作原理可以通過以下步驟來理解:
- 索引掃描:MySQL 在索引中掃描符合索引條件的記錄。
- 索引條件過濾:在掃描索引記錄時,MySQL 會將可以應用于索引的查詢條件“下推”到索引掃描階段。如果索引中的記錄不符合這些條件,MySQL 會直接跳過該記錄,不進行回表操作。
- 回表操作:只有那些在索引中同時滿足索引條件和下推條件的記錄,MySQL 才會回表讀取完整的數(shù)據(jù)行。
- 剩余條件過濾:回表讀取的數(shù)據(jù)行會進一步應用其他查詢條件進行過濾,以確保最終返回的結(jié)果集是準確的。
3. 索引下推的示例
假設我們有一個表 employees
,表結(jié)構(gòu)如下:
CREATE TABLE employees ( emp_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), department_id INT, salary DECIMAL(10, 2), INDEX idx_lastname_salary(last_name, salary) );
現(xiàn)在,我們有一個查詢:
SELECT * FROM employees WHERE last_name LIKE 'S%' AND salary > 50000;
- 索引
idx_lastname_salary
包含last_name
和salary
兩個列。 - 查詢條件中的
last_name LIKE 'S%'
可以用索引來加速查找。 - 查詢條件中的
salary > 50000
也是idx_lastname_salary
索引的一部分,但在傳統(tǒng)情況下,它不會在索引掃描階段應用,而是在回表之后再進行過濾。
沒有索引下推的執(zhí)行過程:
- MySQL 使用
last_name LIKE 'S%'
在索引中找到所有符合條件的記錄。 - 對于每一個符合條件的記錄,MySQL 都會回表讀取
salary
列的值。 - 回表后的數(shù)據(jù)行會被檢查
salary > 50000
這個條件,不滿足的記錄會被過濾掉。
啟用索引下推后的執(zhí)行過程:
- MySQL 使用
last_name LIKE 'S%'
在索引中找到符合條件的記錄。 - 在索引掃描過程中,MySQL 直接在索引中檢查
salary > 50000
這個條件,只有滿足條件的記錄才會進行回表操作。 - 由于很多不符合
salary > 50000
的記錄在索引掃描階段就被過濾掉,回表操作大幅減少,查詢性能提升。
4. 索引下推的好處
- 減少回表操作:通過將更多的條件在索引掃描階段應用,索引下推減少了不必要的回表操作,減少了 I/O 開銷。
- 提高查詢性能:由于減少了數(shù)據(jù)行的訪問次數(shù),索引下推可以顯著提高查詢的整體性能。
- 特別適合組合索引:在使用復合索引(多個列的聯(lián)合索引)時,索引下推的優(yōu)化效果尤為明顯。
5. 索引下推的適用條件
索引下推優(yōu)化的適用條件包括:
- 查詢中包含的條件是可以在索引中評估的。例如,如果索引包含的列可以滿足查詢中的部分條件,這些條件就可以被下推到索引掃描階段。
- 查詢使用了復合索引,且索引中的多個列參與了查詢條件的判斷。
6. 如何查看索引下推是否生效
我們可以使用 EXPLAIN
語句來查看索引下推是否在查詢中生效。
EXPLAIN SELECT * FROM employees WHERE last_name LIKE 'S%' AND salary > 50000;
在 EXPLAIN
輸出的 Extra
列中,如果出現(xiàn) Using index condition
,這意味著 MySQL 在該查詢中使用了索引下推優(yōu)化。
7. 適用和不適用場景
適用場景:
- 使用組合索引且查詢中涉及索引中的多個列時,索引下推可以有效減少回表操作。
- 查詢條件比較復雜,且可以在索引中進行部分判斷的情況下,索引下推能夠提高效率。
不適用場景:
- 如果查詢中涉及的條件無法在索引中評估(如涉及計算或函數(shù)運算),則無法使用索引下推。
- 如果查詢中的條件涉及的列不在索引中,也無法使用索引下推。
8. 示例數(shù)據(jù)和執(zhí)行計劃
假設表中有如下數(shù)據(jù):
INSERT INTO employees VALUES (1, 'Smith', 'John', 10, 60000), (2, 'Smith', 'Alice', 10, 40000), (3, 'Brown', 'Charlie', 20, 55000), (4, 'Davis', 'David', 30, 45000);
執(zhí)行查詢:
EXPLAIN SELECT * FROM employees WHERE last_name LIKE 'S%' AND salary > 50000;
在 EXPLAIN
輸出中,我們可能會看到類似以下的結(jié)果:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra ----------------------------------------------------------------------------------------------------------- 1 | SIMPLE | employees | range | idx_lastname_salary | idx_lastname_salary | 102 | NULL | 2 | Using index condition; Using where
在 Extra
列中顯示 Using index condition
,表示 MySQL 使用了索引下推來優(yōu)化這個查詢。
9. 總結(jié)
索引下推(ICP)是 MySQL 5.6 引入的一個重要優(yōu)化技術(shù),它通過將部分查詢條件“下推”到索引掃描階段來減少回表操作,從而提高查詢性能。索引下推特別適合使用復合索引的場景,通過有效地減少不必要的 I/O 操作,能夠顯著提升查詢的執(zhí)行效率。在實際應用中,可以通過 EXPLAIN
語句來查看索引下推是否生效,并結(jié)合查詢模式和索引設計來充分利用這一優(yōu)化技術(shù)。
到此這篇關(guān)于MySQL索引下推的實現(xiàn)示例的文章就介紹到這了,更多相關(guān)MySQL索引下推內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
mysql group_concat()函數(shù)用法總結(jié)
這篇文章主要介紹了mysql group_concat()函數(shù)用法,結(jié)合實例形式較為詳細的group_concat()函數(shù)的功能、使用方法與相關(guān)注意事項,需要的朋友可以參考下2016-06-06解決從集合運算到mysql的not like找不出NULL的問題
這篇文章主要介紹了解決從集合運算到mysql的not like找不出NULL的問題,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01MySQL使用全庫備份數(shù)據(jù)恢復單表數(shù)據(jù)的方法
這篇文章主要給大家介紹了關(guān)于MySQL使用全庫備份數(shù)據(jù)恢復單表數(shù)據(jù)的相關(guān)資料,文中通過示例代碼介紹的非常詳細,對大家學習或者使用mysql具有一定的參考學習價值,需要的朋友們下面來一起看看吧2018-12-12mysql中如何用varchar字符串按照數(shù)字排序
這篇文章主要介紹了mysql中用varchar字符串按照數(shù)字排序方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-08-08MySQL安裝過程中在第四步initializing database出錯的解決方法
安裝mysql時,在第四步一直卡住了顯示失敗,文中通過圖文介紹的解決方法非常詳細,具有一定的參考價值,感興趣的小伙伴們可以參考一下,希望能幫助到大家2023-09-09