最左匹配原則

導讀：

? 首先創建一張 test 表，并插入一些數據：

CREATE TABLE `test` (

? `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',

? `a` int(11) NOT NULL,

? `b` int(11) NOT NULL,

? `c` int(11) NOT NULL,

? `d` int(11) NOT NULL,

? PRIMARY KEY (`id`),

? KEY `idx_abc` (`a`,`b`,`c`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

? 其中，test 表中有一個聯合索引 idx_abc，其包含索引（a）、索引（ab）、索引（abc）。而我們都知道聯合索引在使用時需要遵循最左匹配原則，也就是說，在執行 select * from test where a = 1 and b = 2 and c = 3 使用 索引（abc）、select * from test where a = 1 and b = 2 索引（ab），雖然使用到的索引列不同，但是歸根結底都使用的是 idx_abc 索引。

? 但是在執行 select * from test where a = 1 and b > 2 and c = 3 時，使用到的索引列僅僅為（ab），但是查詢條件中不是三個索引列都存在嗎，為什么只用到了兩個索引列呢？這個便是我們今天要討論的問題，即聯合索引中，為什么前一個索引列使用了范圍查詢后，下一個索引列將無法使用索引進行匹配？

一、最左匹配原則使用示例：

1、全值匹配查詢時

例如：

select * from test where a = 1 and b = 2 and c = 3;

select * fom test where b = 2 and a = 1 and c = 3;

select * from test where c = 3 and b = 2 and a = 1;

這些查詢都能用到索引 idx_abc，因為 MySQL 中有查詢優化器，會自動優化查詢順序，所以查詢條件中將索引列順序調換不影響聯合索引的使用。

2、匹配左邊的列時

例如：

select * from test where a = '1';

select * from test where a = '1' and b = '2';

select * from test where a = '1' and b = '2' and c = '3'

這些查詢都從最左邊開始連續匹配，用到了索引。

但如果查詢條件沒有從最左邊開始，則不會使用聯合索引，查詢會轉為全表掃描：

select * from test where b = '2';

select * from test where c = '3';

select * from test where b = '1' and c = '3'

這些沒有從最左邊開始，最后查詢沒有用到索引，用的是全表掃描。

select * from test where a = 1 and c = 3

部分索引，只有 a 列用到了索引，c 列無法使用索引。

3、匹配列前綴（%）

對于字符型字段的匹配：

  • 如果查詢條件是前綴匹配（例如 a like 'As%'），可以使用索引，因為前綴是有序的。
  • 如果查詢條件是后綴或中綴匹配（例如 a like '%As' 或 a like '%As%'），則不能使用索引，需要全表掃描。

例：

  • select * from test where a like 'As%'（前綴匹配，走索引查詢）
  • select * from test where a like '%As'（全表查詢）
  • select * from test where a like '%As%'（全表查詢）

4、匹配范圍值

例：

  • select * from test where a > 1 and a < 3：一個列進行范圍查詢，前綴匹配，走索引查詢。
  • select * from test where a > 1 and a < 3 and b > 1：多個列同時進行范圍查找時。只有對索引最左邊的列進行范圍查找才用到 B+ 樹索引。因此，只有 a 列用到了索引，b 列無法使用索引，查詢會基于 1 < a < 3 的范圍查找記錄后，繼續逐條過濾。

5、精確匹配某一列并范圍匹配另外一列

例：

  • select * from test where a = 1 and b > 3

在這種查詢中，如果左邊的列是精確查找，右邊的列可以進行范圍查找，可以進行范圍查找，聯合索引會加速查詢。

6、精確匹配某一列并范圍匹配另外一列，再精確或范圍匹配另外一列

例：

  • select * from test where a = 1 and b > 3 and c = 2

在這種查詢中，聯合索引使用到的索引列仍然只有（a，b）。

解釋：

? MySQL 中的索引結構是 B+ 樹，葉子節點中的數據是以索引列從小到大的順序組織起來的，對于聯合索引來說，因為索引列不止有一列，所以數據的排列排列先按 a 列進行從小到大的排序，再按 b 列進行排序，最后按 c 列進行排序，對于上述 test 表中的數據，索引列 idx_abc 的葉子節點數據排序可以簡化為：

a

b

c

1

2

4

1

2

8

1

6

2

1

6

3

2

2

7

2

7

4

3

2

4

3

2

6

3

3

2

3

3

8

? 可以看出，a 是有序的（構建一顆 B+ 樹只能根據一個值來構建，因此數據庫依據聯合索引最左的字段來構建 B+ 樹。），而 b，c 都是無序的。但是當 a 相同時，b 是有序的；當 b 相同時，c 又是有序的。

? 通過對聯合索引的結構的了解，那么就可以很好的了解為什么最左匹配原則中如果遇到范圍查詢就會停止了。以 select * from test where a=5 and b > 0 and c =1 為例，當查詢到 b 的值以后（這是一個范圍值），c 是無序的，所以就不能根據聯合索引來確定到底該取哪一行。

二、小結

根據上述使用示例，可以得出，最左匹配原則有以下特性：

  • 最左優先：以最左邊的字段為起點，任何連續的索引都能匹配上。
  • 范圍查詢字段后停止：當遇到 >、<、BETWEEN、LIKE之后，下一個列就不會再使用索引進行匹配。

三、索引下推：

? 在搞清楚"聯合索引中，前一個索引列使用了范圍查詢后，下一個索引列將無法使用索引進行快速定位"的問題后，我對上述示例6中的 SQL select * from test where a = 1 and b > 3 and c = 2; 進行了執行計劃分析，如下：

? 這個 Using index condition 就是我們接下來要討論的東西。

? 我們已知，上述 SQL 會使用到聯合索引 idx_abc 中的 a、b 索引列，但是 c 索引列不會使用索引進行匹配。那么對于 where 條件中的 c = 2 該怎么處理呢？

? 在 MySQL 5.6 之前，我們通過非聚簇索引 + 聚簇索引定位到一條記錄后，會將行數據返回給 Server 層，Server 層會根據 c = 2 對數據進行過濾，只要符合條件的記錄才會被返回給客戶端，此時的 Extra 中的信息應為 Using where。

? 但是從 MySQL 5.6 開始，引入了索引下推 (Index Condition Pushdown,ICP)來進行查詢優化，最主要的區別是，對于索引列 c 的處理不會再讓 Server 層去處理了，而是下推到存儲引擎層，即在對聯合索引進行數據匹配時，直接對使用不到索引的索引列條件進行判斷，最后的結果就是只對符合條件的記錄進行回表，這樣的話就可以大大減少回表的次數，從而提升查詢效率。

總結：

  • 索引下推 (ICP) 是 MySQL 5.6 引入的一項關鍵的查詢優化技術。??
  • ??核心思想：?? 將部分可以由索引包含的列完成的 WHERE 條件過濾操作，從服務器層“下推”到存儲引擎層執行。
  • ??主要應用場景：?針對聯合索引 (INDEX (col1, col2, ...)) 的查詢，特別是當 WHERE 條件中：
    • 查詢使用了索引的第一列（通常是范圍查詢：>, >=, <, <=, BETWEEN, LIKE 'a%'），并且
    • 后面還包含其他索引列作為??等值(=)??或??范圍??條件 (col2 = X, col3 > Y, etc.)
  • 可下推的條件：??下推的條件需要是??索引本身包含的列?? (稱為 Index Column)。在 idx_abc 例子中，c 是該索引的列，所以 c = 2 可以被下推。where a = 1 and b > 3 and c = 2 and d =7 中的 d = 7 因為 索引列不包含 b，所以其不能被下推。
  • ??執行計劃標識：??使用 EXPLAIN 命令查看查詢執行計劃。如果看到 Extra 列顯示 ??Using index condition??，則說明優化器對該查詢使用了索引下推。
  • ??巨大價值：??它允許存儲引擎在??回表讀取完整數據行之前??，就利用索引中存儲的后續列的值過濾掉大量不滿足所有條件的記錄，??顯著減少不必要的回表操作次數，從而大幅降低磁盤 I/O 和 CPU 開銷，提升查詢性能??。

舉例：

四、參考：

  • MYSQL最左匹配原則及其底層邏輯-CSDN博客
  • https://www.zhihu.com/question/52536048/answer/1906024782132125707

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/pingmian/87572.shtml
繁體地址，請注明出處：http://hk.pswp.cn/pingmian/87572.shtml
英文地址，請注明出處：http://en.pswp.cn/pingmian/87572.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

MySQL 8.0 OCP 1Z0-908 題目解析(17)

MySQL 8.0 OCP 1Z0-908 題目解析(17)

題目65 Choose two. Which two are characteristics of snapshot-based backups? □ A) The frozen file system can be cloned to another virtual machine immediately into active service. □ B) There is no need for InnoDB tables to perform its own recovery when re…
閱讀更多...
Level2_12小球與擋板（移動+反彈）

Level2_12小球與擋板（移動+反彈）

一、前引 #已經學習完了: #1.數據結構&#xff1a;集合、元組、字典 #2.函數 #3.類和對象 #4.繼承與多態 #1.規劃編程項目: #&#xff08;1&#xff09;你想做什么什么樣功能的項目&#xff1f; # 接小球游戲,碰到擋板時自動反彈 #&#xff08;2&#xff09;功能有哪些&#x…
閱讀更多...
win11 2025開機禁用微軟賬號登錄，改本地用戶登錄，品牌預裝機福音

win11 2025開機禁用微軟賬號登錄，改本地用戶登錄，品牌預裝機福音

今天開箱了品牌商出廠系統一臺華為筆記本&#xff0c;開機提示連接wifi并需要登錄微軟賬號&#xff0c;其中過程實在緩慢&#xff0c;而且老是提示自動更新&#xff0c;速度太慢了&#xff0c;等的花都謝了&#xff0c;進到桌面大概得要30-40分鐘&#xff0c;還不如本地用戶登錄…
閱讀更多...
【嵌入式ARM匯編基礎】-ELF文件格式內部結構詳解（三）

【嵌入式ARM匯編基礎】-ELF文件格式內部結構詳解（三）

ELF文件格式內部結構詳解（三） 文章目錄 ELF文件格式內部結構詳解（三）12、動態部分和動態加載13、依賴加載（需要）14、程序重定位14.1 靜態重定位14.2 動態重定位14.3 全局偏移表 (GOT)14.4 過程鏈接表 (PLT)12、動態部分和動態加載 ELF 文件格式中的 .dynamic 部分用于指…
閱讀更多...
HTML知識復習2

HTML知識復習2

文章目錄 HTML5簡介什么是HTML5HTML5優勢 新增語義化標簽新增布局標簽新增狀態標簽新增列表標簽新增文本標簽 新增表單功能表單控件新增屬性input新增屬性值 新增多媒體標簽視頻標簽音頻標簽 HTML5兼容性處理 HTML5簡介 什么是HTML5 HTML5 是新一代的 HTML 標準&#xff0c;2…
閱讀更多...
棧(Stack)和隊列(Queue)

棧(Stack)和隊列(Queue)

文章目錄 前言1. 棧(Stack)1.1 什么是棧1.2 棧的常用操作1.3 棧的模擬實現1.4 棧的應用場景1.4.1 元素序列處理1.4.2 字符串反轉1.4.3 括號匹配1.4.4 逆波蘭表達式求值1.4.5 棧的壓入、彈出序列1.4.6 最小棧1.4.7 遞歸轉循環 1.5 概念區分1.5.1 數據結構中的棧1.5.2 JVM中的虛擬…
閱讀更多...
5G MEC四大核心挑戰技術解析報告

5G MEC四大核心挑戰技術解析報告

一、MEC園區部署挑戰：數據本地化與低時延接入 痛點深度解析 數據不出園區：工業質檢、醫療影像等敏感業務需數據在本地閉環處理。但運營商基站與企業MEC間若經公網繞行，時延超50ms且存在泄露風險。L2網絡局限：傳統L2接入網無法實現基站→UPF的智能路由，導致業務流繞行城域…
閱讀更多...
【硬核拆解】英偉達Blackwell芯片架構如何重構AI算力邊界？

【硬核拆解】英偉達Blackwell芯片架構如何重構AI算力邊界？

前言 前些天發現了一個巨牛的人工智能免費學習網站&#xff0c;通俗易懂&#xff0c;風趣幽默&#xff0c;忍不住分享一下給大家。點擊跳轉到網站 一、Blackwell誕生的算力危機&#xff08;2025現狀&#xff09; graph TD A[2025年AI算力需求] --> B[千億參數模型訓練能耗…
閱讀更多...
【深度學習模塊】圖像的相對位置編碼

【深度學習模塊】圖像的相對位置編碼

這個是一個常用的模塊&#xff0c;就是我們可以對輸入的特征嵌入位置編碼。 位置編碼&#xff08;Positional Encoding&#xff09;是一種將空間位置信息嵌入到特征中的方法&#xff0c;通常用于幫助模型更好地理解特征的空間關系。 這里介紹的這個是相對位置編碼&#xff0c;…
閱讀更多...
osg加入實時光照SilverLining 天空和3D 云

osg加入實時光照SilverLining 天空和3D 云

OSG系列文章目錄 文章目錄 OSG系列文章目錄一、前言官網的介紹&#xff1a; 二、編譯官網例子 一、前言 osg本身也可以加入動態云&#xff0c;但是效果有點差強人意&#xff0c;這里我們使用sundog公司的動態云&#xff1a;SilverLining 天空和 3D 云。 官網的介紹&#xff1…
閱讀更多...
spring-ai-alibaba 1.0.0.2 學習（十二）——聊天記憶擴展包

spring-ai-alibaba 1.0.0.2 學習（十二）——聊天記憶擴展包

學習spring-ai時提到過&#xff0c;spring-ai除了內置的InMemoryChatMemoryRepository&#xff0c;還提供jdbc、cassandra、neo4j三個擴展包。 而spring-ai-alibaba則提供了jdbc、redis、elasticsearch三個擴展包。 兩者都提供了jdbc擴展包&#xff0c;有什么區別呢&#xff…
閱讀更多...
c語言-指針（數組）練習2

c語言-指針（數組）練習2

題目&#xff1a;將數組中n個元素按逆序存放并打印出來&#xff0c;使用函數封裝與指針 思路&#xff1a; 1.定義一個數組arr[5]和用于存放數組大小&#xff08;數組大小通過sizeof關鍵字來進行計算&#xff09;的變量len&#xff1b; 2.創建三個函數initArr、printArr、rev…
閱讀更多...
Redis服務器

Redis服務器

Redis&#xff0c;一款Key-Value型內存數據庫 常用于網站開發場景 Redis服務器只發布了Linux版本 Redis服務器安裝&#xff0c;2種辦法 自動安裝 apt install redis-server手動編譯安裝 從官網下載源碼&#xff0c;編譯&#xff0c;部署 1 安裝redis apt install redis-s…
閱讀更多...
LeetCode 第91題：解碼方法

LeetCode 第91題：解碼方法

題目描述&#xff1a; 一條包含字母A-Z的消息通過以下映射進行了編碼 1-A ...... 26-Z 要特別注意&#xff0c;11106可以映射為AAJF或KJF 06不是一個合法編碼 給你一個只含數字的非空字符串s&#xff0c;請計算并返回解碼方法的總數。如果沒有合法的方法解碼整個字符串&#xf…
閱讀更多...
Rocky Linux 9 源碼包安裝Mysql8

Rocky Linux 9 源碼包安裝Mysql8

Rocky Linux 9 源碼包安裝Mysql8 大家好我是星哥&#xff0c;之前介紹了&#xff0c;Rocky Linux 9 源碼包安裝Mysql5.7。 本文將介紹如何在Rocky Linux 9操作系統上&#xff0c;從源碼一步步安裝MySQL 8&#xff0c;為您提供一個穩定、高效且可控的數據庫解決方案。 為什么…
閱讀更多...
AI小智項目全解析：軟硬件架構與開發環境配置

AI小智項目全解析：軟硬件架構與開發環境配置

AI小智項目全解析&#xff1a;軟硬件架構與開發環境配置 一、項目整體架構 AI小智是一款基于ESP32的智能物聯網設備&#xff0c;集成了語音交互、邊緣計算等功能。整體系統架構如下&#xff1a; 終端設備&#xff1a;ESP32模組作為核心通信方式&#xff1a; WebSocket實現實…
閱讀更多...
設計模式之上下文對象設計模式

設計模式之上下文對象設計模式

目錄 一、模式介紹 二、架構設計 三、Demo 示例 四、總結 一、模式介紹 上下文對象&#xff08;Context Object&#xff09;模式 最早由《Core J2EE Patterns》第二版提出&#xff0c;其核心目標是在多層或多組件間共享與當前作用域&#xff08;如一次請求、一次會話、一次…
閱讀更多...
@Linux服務器加域退域

@Linux服務器加域退域

文章目錄 **一、加入Active Directory域****1. 準備工作****2. 配置步驟****步驟1&#xff1a;驗證網絡和DNS****步驟2&#xff1a;發現域****步驟3&#xff1a;加入域****步驟4&#xff1a;配置SSSD&#xff08;可選&#xff09;****步驟5&#xff1a;配置sudo權限&#xff08…
閱讀更多...
鴻蒙系統（HarmonyOS）4.2 設備上實現無線安裝 APK 并調試

鴻蒙系統（HarmonyOS）4.2 設備上實現無線安裝 APK 并調試

在鴻蒙系統&#xff08;HarmonyOS&#xff09;4.2 設備上實現無線安裝 APK 并調試的步驟與 Android 類似&#xff0c;但需注意鴻蒙系統的特殊設置。以下是詳細操作指南&#xff1a; 鴻蒙系統特殊準備 開啟開發者選項&#xff1a; - 設置 > 關于手機 > 連續點擊"H…
閱讀更多...
MyBatis時間戳查詢實戰指南

MyBatis時間戳查詢實戰指南

在 MyBatis 中通過時間戳&#xff08;Timestamp&#xff09;作為查詢條件&#xff0c;需注意數據庫時間類型與 Java 類型的映射。以下是具體實現方式&#xff1a; 一、Java 實體類與數據庫字段映射 實體類定義 使用 java.sql.Timestamp 或 java.time.LocalDateTime&#xff08;…
閱讀更多...
最新文章

Copyright @ 2022~2023