MySQL索引下推(ICP)的簡單理解與示例

 更新時間:2021年09月09日 15:04:52   作者:毛英東  
大家應該都知道索引下推可以提高查詢效率,所以下面這篇文章主要給大家介紹了關于MySQL索引下推(ICP)的簡單理解與示例的相關資料,需要的朋友可以參考下

前言

索引下推(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)內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家!

您可能感興趣的文章:

相關文章

  • MySql 備忘錄

    MySql 備忘錄

    在MySQL中如果不為NOT NULL字段賦值(等同于賦NULL值)也就是說,MySQL中NOT NULL并不是一個約束條件了
    2012-03-03
  • 史上最簡單的MySQL數據備份與還原教程(下)(三十七)

    史上最簡單的MySQL數據備份與還原教程(下)(三十七)

    這篇文章主要為大家詳細介紹了史上最簡單的MySQL數據備份與還原教程下篇,具有一定的參考價值,感興趣的小伙伴們可以參考一下
    2017-10-10
  • MySQL刪除和插入數據很慢的問題解決

    MySQL刪除和插入數據很慢的問題解決

    公司開發人員在測試環境中執行一條 insert 語句時,需要花費 10 幾秒才可以執行成功。所以本文就來解決一下這個問題,感興趣的小伙伴們可以參考一下
    2021-06-06
  • 詳細聊聊MySQL中慢SQL優化的方向

    詳細聊聊MySQL中慢SQL優化的方向

    由于在MySQL日常查詢中,查詢類型的語句占慢sql的大部分,所以下面這篇文章主要給大家介紹了關于MySQL中慢SQL優化方向的相關資料,文中通過示例代碼介紹的非常詳細,需要的朋友可以參考下
    2021-08-08
  • 小型Drupal數據庫備份以及大型站點MySQL備份策略分享

    小型Drupal數據庫備份以及大型站點MySQL備份策略分享

    為了防止web服務器出現故障而引起的數據丟失,數據庫備份顯得非常重要,以免出現重大損失。本文分析研究一下小型的Drupal站的備份策略以及大型站點的mysql備份策略
    2014-11-11
  • 基于Linux的mysql主從配置全過程記錄

    基于Linux的mysql主從配置全過程記錄

    這篇文章主要給大家介紹了基于Linux的mysql主從配置的相關資料,文中通過示例代碼介紹的非常詳細,對大家學習或者使用mysql具有一定的參考學習價值,需要的朋友們下面來一起學習學習吧
    2019-04-04
  • mysql 帶多個條件的查詢方式

    mysql 帶多個條件的查詢方式

    這篇文章主要介紹了mysql 帶多個條件的查詢方式,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教
    2021-06-06
  • MySQL的語法及其使用指南

    MySQL的語法及其使用指南

    數據庫的選取,創建,丟棄和變更 數據表和索引的創建,變更和丟棄從數據表檢索信息
    2008-04-04
  • 深入Mysql,SqlServer,Oracle主鍵自動增長的設置詳解

    深入Mysql,SqlServer,Oracle主鍵自動增長的設置詳解

    本篇文章是對Mysql,SqlServer,Oracle主鍵自動增長的設置進行了詳細的分析介紹,需要的朋友參考下
    2013-06-06
  • 詳解Mysql雙機熱備和負載均衡的實現步驟

    詳解Mysql雙機熱備和負載均衡的實現步驟

    MySQL數據庫沒有增量備份的機制,但它提供了一種主從備份的機制,就是把主數據庫的所有的數據同時寫到備份數據庫中。這篇文章主要介紹了Mysql的雙機熱備和負載均衡,需要的朋友可以參考下
    2019-10-10

最新評論

精品国内自产拍在线观看