五分鐘帶你搞懂MySQL索引下推

 更新時間:2021年09月09日 15:48:24   作者:三分惡  
這篇文章主要介紹了Mysql的索引下推,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧

如果你在面試中,聽到MySQL5.6”、“索引優化” 之類的詞語,你就要立馬get到,這個問的是“索引下推”。

什么是索引下推

索引下推(Index Condition Pushdown,簡稱ICP),是MySQL5.6版本的新特性,它能減少回表查詢次數,提高查詢效率。

索引下推優化的原理

我們先簡單了解一下MySQL大概的架構:

MySQL服務層負責SQL語法解析、生成執行計劃等,并調用存儲引擎層去執行數據的存儲和檢索。

索引下推的下推其實就是指將部分上層(服務層)負責的事情,交給了下層(引擎層)去處理。

我們來具體看一下,在沒有使用ICP的情況下,MySQL的查詢:

  • 存儲引擎讀取索引記錄;
  • 根據索引中的主鍵值,定位并讀取完整的行記錄;
  • 存儲引擎把記錄交給Server層去檢測該記錄是否滿足WHERE條件。

使用ICP的情況下,查詢過程:

  • 存儲引擎讀取索引記錄(不是完整的行記錄);
  • 判斷WHERE條件部分能否用索引中的列來做檢查,條件不滿足,則處理下一行索引記錄;
  • 條件滿足,使用索引中的主鍵去定位并讀取完整的行記錄(就是所謂的回表);
  • 存儲引擎把記錄交給Server層,Server層檢測該記錄是否滿足WHERE條件的其余部分。

索引下推的具體實踐

理論比較抽象,我們來上一個實踐。

使用一張用戶表tuser,表里創建聯合索引(name, age)。

如果現在有一個需求:檢索出表中名字第一個字是張,而且年齡是10歲的所有用戶。那么,SQL語句是這么寫的:

select * from tuser where name like '張%' and age=10;

假如你了解索引最左匹配原則,那么就知道這個語句在搜索索引樹的時候,只能用 張,找到的第一個滿足條件的記錄id為1。

那接下來的步驟是什么呢?

沒有使用ICP

在MySQL 5.6之前,存儲引擎根據通過聯合索引找到name likelike '張%' 的主鍵id(1、4),逐一進行回表掃描,去聚簇索引找到完整的行記錄,server層再對數據根據age=10進行篩選

我們看一下示意圖:

可以看到需要回表兩次,把我們聯合索引的另一個字段age浪費了。

使用ICP

而MySQL 5.6 以后, 存儲引擎根據(name,age)聯合索引,找到name likelike '張%',由于聯合索引中包含age列,所以存儲引擎直接再聯合索引里按照age=10過濾。按照過濾后的數據再一一進行回表掃描。

我們看一下示意圖:

可以看到只回表了一次。

除此之外我們還可以看一下執行計劃,看到Extra一列里 Using index condition,這就是用到了索引下推。

+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tuser | NULL       | range | na_index      | na_index | 102     | NULL |    2 |    25.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

索引下推使用條件

  • 只能用于range ref eq_refref_or_null訪問方法;
  • 只能用于InnoDB和 MyISAM存儲引擎及其分區表;
  • InnoDB存儲引擎來說,索引下推只適用于二級索引(也叫輔助索引);

索引下推的目的是為了減少回表次數,也就是要減少IO操作。對于InnoDB的聚簇索引來說,數據和索引是在一起的,不存在回表這一說。

  • 引用了子查詢的條件不能下推;
  • 引用了存儲函數的條件不能下推,因為存儲引擎無法調用存儲函數。

相關系統參數

索引條件下推默認是開啟的,可以使用系統參數optimizer_switch來控制器是否開啟。

查看默認狀態:

mysql> select @@optimizer_switch\G;
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)

切換狀態:

set optimizer_switch="index_condition_pushdown=off";
set optimizer_switch="index_condition_pushdown=on";

總結

本篇文章就到這里了,希望能夠給你帶來幫助,也希望您能夠多多關注腳本之家的更多內容!

相關文章

  • Workbench通過遠程訪問mysql數據庫的方法詳解

    Workbench通過遠程訪問mysql數據庫的方法詳解

    這篇文章主要給大家介紹了Workbench通過遠程訪問mysql數據庫的相關資料,文中通過圖文介紹的非常詳細,對大家具有一定的參考學習價值,需要的朋友們下面來一起看看吧。
    2017-06-06
  • MySQL中字符串與Num類型拼接報錯的解決方法

    MySQL中字符串與Num類型拼接報錯的解決方法

    在使用mysql的時候經常要用到拼接的功能,最近的工作就遇到拼接的問題,在將字符串拼接Num類型的時候發現居然報錯,下面通過這篇文章來看看解決的方法吧,有需要的朋友們可以參考借鑒。
    2016-10-10
  • mytop 使用介紹 mysql實時監控工具

    mytop 使用介紹 mysql實時監控工具

    mytop 是一個類似 Linux 下的 top 命令風格的 MySQL 監控工具,可以監控當前的連接用戶和正在執行的命令
    2012-05-05
  • Mysql 用戶權限管理實現

    Mysql 用戶權限管理實現

    MySQL 是一個多用戶數據庫,具有功能強大的訪問控制系統,可以為不同用戶指定不同權限。本文就來介紹一下Mysql 用戶權限管理實現,感興趣的可以了解一下
    2021-05-05
  • MySQL使用binlog日志做數據恢復的實現

    MySQL使用binlog日志做數據恢復的實現

    這篇文章主要介紹了MySQL使用binlog日志做數據恢復的實現,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
    2021-03-03
  • MySQL8.0.20壓縮版本安裝教程圖文詳解

    MySQL8.0.20壓縮版本安裝教程圖文詳解

    這篇文章主要介紹了MySQL8.0.20壓縮版本安裝教程,需本文通過圖文并茂的形式給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,要的朋友可以參考下
    2020-08-08
  • MySQL 觸發器的基礎操作(六)

    MySQL 觸發器的基礎操作(六)

    這篇文章主要為大家詳細介紹了MySQL 觸發器的基礎操作,告訴大家什么是MySQL觸發器,如何查看觸發器,感興趣的小伙伴們可以參考一下
    2016-08-08
  • Mysql以utf8存儲gbk輸出的實現方法提供

    Mysql以utf8存儲gbk輸出的實現方法提供

    Mysql以utf8存儲gbk輸出的實現方法提供...
    2007-11-11
  • Mysql實現簡易版搜索引擎的示例代碼

    Mysql實現簡易版搜索引擎的示例代碼

    前段時間,因為項目需求,需要根據關鍵詞搜索聊天記錄,所以本文實現了Mysql實現簡易版搜索引擎,具有一定的參考價值,感興趣的可以了解一下
    2021-08-08
  • spark rdd轉dataframe 寫入mysql的實例講解

    spark rdd轉dataframe 寫入mysql的實例講解

    今天小編就為大家分享一篇spark rdd轉dataframe 寫入mysql的實例講解,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧
    2018-06-06

最新評論

精品国内自产拍在线观看