神奇的 SQL 之 ICP → 索引條件下推

来源:https://www.cnblogs.com/youzhibing/archive/2020/03/09/12318565.html
-Advertisement-
Play Games

開心一刻 樓主:來,我們先排練一遍 小伙伴們:好 嘿、哈、嚯 樓主:非常好,就是這個節奏,我們開始吧 樓主:啊、啊、啊,疼 ! 你們是不是故意的 ? 回表與覆蓋索引 正式講 ICP 之前了,我們先將相關的概念捋一捋,知道的就當回顧,不知道的就當瞭解了,這有助於對 ICP 的理解 建個示例表 tbl_ ...


開心一刻

  樓主:來,我們先排練一遍

  小伙伴們:好

  嘿、哈、嚯

  樓主:非常好,就是這個節奏,我們開始吧

  樓主:啊、啊、啊,疼 ! 你們是不是故意的 ?

回表與覆蓋索引

  正式講 ICP 之前了,我們先將相關的概念捋一捋,知道的就當回顧,不知道的就當瞭解了,這有助於對 ICP 的理解

  建個示例表 tbl_index 

CREATE TABLE tbl_index (
    c1 INT,
    c2 INT,
    c3 CHAR(1),
    PRIMARY KEY(c1),
    KEY idx_c2 (c2)
);

  覆蓋索引

    如果 where 條件的列和 select 的列都在一個索引中,通過這個索引就可以完成查詢,這就叫就叫覆蓋索引;當然,覆蓋索引基本針對的是組合索引(InnoDB 的聚簇索引有點特殊,具體可以看下麵的圖)

    針對上面的 tbl_index, select c2 from tbl_index where c2 = 4; 是覆蓋索引查詢,但是這條 SQL 沒有意義,如果我們在 tbl_index 表上增加索引 index idx_c2_c3 (c2,c3) ,那麼 select c3 from tbl_index where c2 = 4; 走覆蓋索引查詢還是很有意義的,那問題又來了,覆蓋索引的意義何在 ? 我們往下看

  回表

    通過某個索引無法直接完成 SQL 查詢(where 條件的列和 select 的列不全部存在於任何一個索引中),那麼此時需要獲取完整的數據記錄來完成此次查詢,從索引項記錄到獲取對應的完整數據記錄的過程就叫回表;概念可能說的有些抽象,我們結合 MySQL 來看看具體什麼是回表

    InnoDB 的回表

    InnoDB 的索引結構有些特殊,非聚簇索引(二級索引)回表到聚簇索引的過程類似如下

    InnoDB的聚簇索引即數據,索引和數據是存在一起的;那麼直接走聚簇索引查詢的 SQL 是不存在回表一說的,比如 select * from tbl_index where c1 = 10; ,只有從二級索引出發,並且二級索引獨自完成不了查詢的時候才會回表到聚簇索引完成查詢

    MyISAM 的回表

    有這樣一種說法: MyISAM 中的索引都是二級索引 ,其實說的是聚簇索引和二級索引的結構基本一致,只是聚簇索引有個唯一性約束

    MyISAM 聚簇索引和二級索引,以及它們的回表過程類似如下

    MyISAM 的回表過程指的是根據葉子節點中的數據記錄的地址來獲取完整記錄的過程,無論是聚簇索引還是二級索引都可能存在回表的過程;MyISAM 的回表與 InnoDB 還是有差別的

  無論是 InnoDB 的回表還是 MyISAM 的回表,很有可能會造成額外的磁碟 IO,這會嚴重影響查詢效率,覆蓋索引的目的就是儘量能夠一次完成 SQL 查詢,避免有回表過程,從而提高效率

  如何確認 MySQL 是進行了覆蓋索引查詢,還是進行了回表查詢 ?

  看 MySQL 的執行計劃,如果 Extra 中只有 using index 則說明使用了覆蓋索引查詢,如果 Extra 中出現了 using index condition 或 using index & using where 則說明進行了回表查詢

ICP

  Index Condition Pushdown,MySQL 5.6 中引入的一種優化策略

  那麼究竟是將什麼從哪 Push Down 到哪,優化了什麼?要弄清楚這 4 個問題,我們需要先弄清楚 where 條件的提取與應用,具體可查看:神奇的 SQL 之 WHERE 條件的提取與應用

  where 條件會被提取成 3 部分: Index KeyIndex Filter,Table Filter ,在 MySQL 5.6 之前,並不區分 Index Filter 與 Table Filter,統統將 Index First Key 與 Index Last Key 範圍內的索引記錄,回表讀取完整記錄,然後返回給 MySQL Server 層進行過濾,而在 MySQL 5.6 之後,Index Filter 與 Table Filter 分離,Index Filter 下降到引擎層(InnoDB和MyISAM)的索引層面進行過濾,減少了回表與返回 MySQL Server 層的記錄交互開銷,提高了 SQL 的執行效率

  ICP 優化過程

    假設我們有表: tbl_icp 

create table tbl_icp (a int primary key, b int, c int, d int, e varchar(50));
create index idx_bcd on tbl_icp(b, c, d);
insert into tbl_icp values (4,3,1,1,'a');
insert into tbl_icp values (1,1,1,2,'d');
insert into tbl_icp values (8,8,7,8,'h');
insert into tbl_icp values (2,2,1,2,'g');
insert into tbl_icp values (5,2,2,5,'e');
insert into tbl_icp values (3,3,2,1,'c');
insert into tbl_icp values (7,4,0,5,'b');
insert into tbl_icp values (6,5,2,4,'f');
View Code

    若沒有使用 ICP,則 SQL 查詢類似如下

    沒有使用 ICP 時,引擎層會將滿足 Index Key 範圍限制的所有數據記錄(示例中一共 6 條)逐條返回給 Server 層,然後由 server 層應用 Index Filter 和 Table Filter (MySQL 5.6 之前不區分 Index Filter 和 Table Filter),最後將滿足條件的數據返回給客戶端;

    若使用 ICP,則 SQL 查詢類似如下

    使用了 ICP,Server 層會將 Index Filter 下推到引擎層,引擎層在對 Index First Key 與 Index Last Key 範圍內的索引項逐條進行過濾的時候,會應用上 Index Filter,對不滿足 Index Filter 條件的索引項直接過濾掉,無需回表操作,也無需返回給 Server 層,從而提供執行效率;上圖中的索引項: 3 1 1 、 3 2 1 不滿足 Index Filter 中的 d != 1 , 4 0 5 不滿足 c > 0 ,所以這 3 個索引項無需進行回表操作,也不需要返回給 Server 層

  相信到這裡,大家對 ICP 的 4 個問題應該就比較清楚了

  ICP 的適用條件

    雖說 ICP 能提高 SQL 執行效率,但也不是任何情況下都適用的,它只適用於某些情況

    1、當 SQL 需要全表訪問時,ICP 的優化策略可用於 range, ref, eq_ref,  ref_or_null 類型的數據訪問方式

    2、只適用於 InnoDB 和 MyISAM 兩種存儲引擎

    3、在 InnoDB 中,ICP 只適用於二級索引

      ICP 的目的就是為了減少回表導致的磁碟 I/O,而 InnoDB 的聚簇索引的葉子節點存放的就是完整的數據記錄,只要索引數據被讀到記憶體了,那麼索引項對應的完整數據記錄也就讀到記憶體了,那麼通過索引項獲取數據記錄的過程就在記憶體中進行了,無需進行磁碟 I/O;也就說聚簇索引上應用 ICP,不會減少磁碟 I/O,也就沒有使用的意義了

    4、不支持覆蓋索引

      其實和第 3 點一樣,因為覆蓋索引無需回表,ICP 也就沒意義了

    5、不支持子查詢條件的下推

    6、不支持存儲過程條件、觸發器條件的下推

  至於 ICP 的優化效果,取決於在存儲引擎內通過 ICP 篩選掉的數據的比例,過濾掉的數據比例大,那就性能提升大,反之則性能提升小

總結

  1、索引覆蓋與回表

    這兩個往往是一起來考慮的,因為覆蓋索引的目的就是減少因回表產生的磁碟 I/O,從而提高執行效率

    在實際應用中,我們往往也需要考慮儘可能用覆蓋索引來完成我們的 SQL 查詢

  2、ICP的四個問題

    將什麼從哪 Push Down 到哪,優化了什麼

    將 Index Filter 從 Server 層 Push Down 到了引擎層,減少了因回表產生的磁碟 I/O,也減少了與 Server 層的交互,提高了 SQL 執行效率

  3、疑問點

    為什麼這麼明顯的優化策略到 MySQL 5.6 才引入,個人感覺很容易就能考慮到呀,MySQL 的開發者們是腫麽肥事 ?

    可能是樓主在巨人的肩膀上,站著說話不腰疼吧......

參考

  Index Condition Pushdown Optimization

  Index Condition Pushdown

  MySQL的索引


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Linux系統一般有4個主要部分: 內核、shell、文件系統和應用程式。內核、shell和文件系統一起形成了基本的操作系統結構,它們使得用戶可以運行程式、管理文件並使用系統。部分層次結構如圖1-1所示。 1. linux內核 Linux內核是世界上最大的開源項目之一,內核是與電腦硬體介面的易替換 ...
  • 一、伺服器端搭建: 1. 安裝 curl -s https://install.zerotier.com/ | sudo bash 2.給文件夾授權以及生成moon配置文件 sudo chmod 777 /var/lib/zerotier-onecd /var/lib/zerotier-onesud ...
  • 加速電腦開機關機速度 首先要感謝快手平臺,讓我能得到更多有用的知識^v^ 好了,下麵開始吧: 方法一 1. 按下Win + R鍵,彈出運行視窗,輸入msconfig,點擊確定。 2. 選擇>>選擇性啟動,取消勾選>>載入啟動項。 3. 點擊引導,選擇>>無GUI引導,點擊確定。 4. 點擊>>重新啟 ...
  • 先看拓撲圖:Ambassador 主要用來解決跨伺服器通訊,因為同伺服器通訊相對簡單,直接用--link參數,或者用bridge網路即可。 伺服器B的Docker2:centos想要訪問伺服器A的Docker1:nginx,但是不知道對方的埠和ip,這時就需要通過Ambassador來解決。(Am ...
  • 由於nginx功能強大,性能突出,越來越多的web應用採用nginx作為http和反向代理的web伺服器。而nginx的訪問日誌不管是做用戶行為分析還是安全分析都是非常重要的數據源之一。如何有效便捷的採集nginx的日誌進行有效的分析成為大家關註的問題。本文通過幾個實例來介紹如何通過filebeat... ...
  • 目錄 " 一.InnoDB索引 " " 二.B+樹 " " 三.聚集索引和輔助索引 " " 四.索引實戰 " " 五.索引操作與規則 " 重建索引 索引覆蓋 最左首碼原則 索引下推 用索引和用索引快速定位卻別 " 六.普通索引和唯一索引如何選擇 " 前提 普通索引和唯一索引下的查詢 普通索引和唯一索 ...
  • 在SQL Server中,如何快速刪除大表中的數據呢? 回答這個問題前,我們必須弄清楚上下文環境和以及結合實際、具體的需求,不同場景有不同的應對方法。 1: 整張表的數據全部刪除 如果是整張表的數據全部清空、刪除,這種場景倒是非常簡單,TRUNCATE TABLE肯定是最快的。 反而用DELETE處... ...
  • Over()分析函數 說明:聚合函數(如sum()、max()等)可以計算基於組的某種聚合值,但是聚合函數對於某個組只能返回一行記錄。若想對於某組返回多行記錄,則需要使用分析函數。 rank()/dense_rank over(partition by ... order by ...) 說明:ov ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...