MySQL索引下推（ICP）的簡單理解與示例

Posted on by

前言

索引下推（Index Condition Pushdown, 簡稱ICP）是MySQL 5.6 版本的新特性，它能減少回表查詢次數，提升檢索效率。

MySQL體系結構

要明白索引下推，首先要瞭解MySQL的體系結構：

上圖來自MySQL官方文檔。

通常把MySQL從上至下分為以下幾層：

  • MySQL服務層：包括NoSQL和SQL接口、查詢解析器、優化器、緩存和Buffer等組件。
  • 存儲引擎層：各種插件式的表格存儲引擎，實現事務、索引等各種存儲引擎相關的特性。
  • 文件系統層: 讀寫物理文件。

MySQL服務層負責SQL語法解析、觸發器、視圖、內置函數、binlog、生成執行計劃等，並調用存儲引擎層去執行數據的存儲和檢索。“索引下推”的“下”其實就是指將部分上層（服務層）負責的事情，交給瞭下層（存儲引擎）去處理。

索引下推案例

假設用戶表數據和結構如下：

id age birthday name
1 18 01-01 User1
2 19 03-01 User2
3 20 03-01 User3
4 21 03-01 User4
5 22 05-01 User5
6 18 06-01 User6
7 24 01-01 User7

創建一個聯合索引(age, birthday)，並查詢出年齡>20，且生日為03-01的用戶：

select * from user where age>20 and birthday="03-01"

由於age字段使用瞭范圍查詢，根據最左前綴原則，這種情況隻能使用age字段進行范圍查詢，索引中的birthday字段無法使用。使用explain查看執行計劃：

+------+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
| id   | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra                 |
+------+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
|    1 | SIMPLE      | user  | range | age_birthday  | age_birthday | 4       | NULL | 3    | Using index condition |
+------+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+

可以看到雖然使用瞭age_birthday索引，但是索引長度key_len隻有4，說明隻有聯合索引隻有age字段生效瞭（因為age字段是int類型，占用4個字節）。最後Extra列的Using index condition表示這個查詢使用瞭索引下推優化。

為在沒有索引下推的情況下，執行步驟如下：

  • 存儲引擎根據索引查找出age>20的用戶id，分別是：4,5,7
  • 存儲引擎到表格中取出id in (4,5,7)的3條記錄，返回給服務層
  • 服務層過濾掉不符合birthday=”03-01″條件的記錄，最後返回查詢結果為id=4的1行記錄。

如果開啟瞭索引下推優化，執行步驟如下：

  1. 存儲引擎根據索引查找出age>20的用戶id，並使用索引中的birthday字段過濾掉不符合birthday=”03-01″條件的記錄，最後得到id=4；
  2. 存儲引擎到表格中取出id=4的1條記錄，返回給服務層；
  3. 服務層過濾掉不符合birthday=”03-01″條件的記錄，最後返回查詢結果為id=4的1行記錄。

啟用索引下推後，把where條件由MySQL服務層放到瞭存儲引擎層去執行，帶來的好處就是存儲引擎根據id到表格中讀取數據的次數變少瞭。在上面這個例子中，沒有索引下推時需要多回表查詢2次。並且回表查詢很可能是離散IO，在某些情況下，對數據庫性能會有較大提升。

總結

到此這篇關於MySQL索引下推（ICP）的簡單理解與示例的文章就介紹到這瞭,更多相關MySQL索引下推（ICP）內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：