左匹配原則，聚集索引，回表查詢，索引覆蓋 你真的懂了嗎

来源:https://www.cnblogs.com/CNYYGJ/archive/2020/04/17/12677690.html
-Advertisement-
Play Games

一個問題 有一張表test，這張表除了主鍵id外，還有a，b, c 三列 假設給這三個欄位建一個複合索引 index_abc (a, b, c)，問，下麵幾種查詢中，哪種查詢會用到索引 index_abc ？ 1. 查詢一 select * from test where a > 1000 and ...


一個問題

有一張表test，這張表除了主鍵id外，還有a，b,  c 三列

假設給這三個欄位建一個複合索引 index_abc (a, b, c)，問，下麵幾種查詢中，哪種查詢會用到索引 index_abc ？


1. 查詢一

select * from test where a > 1000 and b > 1000;

2. 查詢二

select * from test where a > 1000 and c > 1000；

3. 查詢三

select * from test where b > 1000 and c > 1000;

這是一個經典的面試題，由這個問題，我可以相關問你，什麼是 左匹配原則？什麼是 聚集索引？什麼是 索引覆蓋？什麼是 回表？

下麵給大家捋一捋，以下試驗基於MySQL5.7-InnoDB

左匹配原則

接著上面的問題，回到剛剛的三個查詢上，首先，我們怎麼知道查詢有沒有用到索引？有沒有什麼命令是可以幫助我們分析查詢語句呢？答案當然是有的，那就 explain 命令

我們分別對上面的語句進行 explain，看看有哪些信息：

mysql> explain select * from test where a > 1000 and b > 1000;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | range | index_abc     | index_abc | 4       | NULL | 5060 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+

mysql> explain select * from test where a > 1000 and c > 1000;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | range | index_abc     | index_abc | 4       | NULL | 5060 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+

mysql> explain select * from test where b > 1000 and c > 1000;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | index_abc | 12      | NULL | 10120 |    11.11 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+

 

我們可以看到，對查詢語句執行 explain 後，返回了12列信息，各列說明如下：

CloumnMeaning
id  查詢標識符
select_type 查詢類型
table 輸出行的表
partitions 匹配的分區
type 聯接類型，確切的說是一種資料庫引擎查找表的一種方式
possible_keys 可以選擇的可能索引，但不一定被查詢實際使用
key 實際選擇的索引
key_len 所選鍵的長度
ref 與索引相比的列
rows 估計要查詢的列
filtered 按表條件篩選的行百分比
Extra 其他信息

通常分析sql語句，我們只關註type，possible_keys，key，rows

對三條查詢語句進行explain後，我們發現：

  • where a > 1000 and b > 1000 和 where a > 1000 and c > 1000條件的查詢 結果是一樣的，其中type指明的索引查找方式為range，possible_keys 可能使用的索引為 index_abc，key 實際使用的索引為 index_abc
  • where b > 1000 and c > 1000 條件的查詢中，type的值為index，possible_keys為NULL，key的值為 index_abc


上面的range 和 index有什麼區別呢？

  • range：僅檢索給定範圍內的行，使用索引選擇行
  • index：索引聯接類型與 ALL 相同，只不過掃描索引樹，有兩種情況：
    • 如果索引是查詢的 覆蓋索引（後文有講），並且可用於滿足表中所需的所有數據，則僅掃描索引樹。在這種情況下，"額外"（Extra）列表示使用索引。 僅索引掃描通常比全部掃描快，因為索引的大小通常小於表數據
    • 使用索引中的讀取執行完整的表掃描，以按索引順序查找數據行。使用索引不顯示在"額外"列中，也就是說：如果不是覆蓋索引，使用索引不顯示在"額外"列中

換句話說，

range是使用了索引，並且能夠在對應的索引樹上使用快速查找的方法進行快速查找，是有範圍的查找，使用了range，就一定用到了我們建的索引，而index只能是通過掃描整個索引樹

上面也提到ALL，那麼type還有哪幾種比較常見的值呢？下麵列舉一下（具體其他類型值，看以參考官方文檔）：

  • system：該表只有一行 （= 系統表）。這是 const 聯接類型的特殊情況
  • const：表示通過索引一次就找到了，因為只匹配一行數據，所以很快。如將主鍵置於where列表中，MySQL就能將該查詢轉換為一個常量表最多有一個匹配行，在查詢開始時讀取該行。由於只有一行，因此優化器的其餘部分可以將該行中的列中的值視為常量。將主鍵或 UNIQUE 索引的所有部分與常量值進行比較時，將使用 const
  • eq_ref：唯一性索引掃描，對於前一表中的每一行組合，將從此表中讀取一行，常見於主鍵或唯一索引掃描。除了system 和 const 類型之外，這是最佳聯接類型
  • ref：非唯一性索引掃描，對於前一表中的每一行組合，將從此表中讀取具有匹配索引值的所有行
  • ALL：將遍歷全表以找到匹配的行

好，回到上面三條查詢語句上，為什麼where條件為a > 1000 and b > 1000 和 a > 1000 and c > 1000 的 type 是 range（用到索引）, 而where條件為 b > 1000 and c > 1000 的 type 是 index 呢？這裡面索引樹（B+樹）的構建方式及存儲結構有關

那麼複合索引B+樹是怎樣的呢？看圖，一圖勝百字

​

對於索引來說只不過比單值索引多了幾列，而這些索引列全都出現在索引樹上。對於複合索引，存儲引擎會首先根據第一個索引列排序，如上圖我們可以單看第一個索引列，如，1 1 4 15 18....他是單調遞增的；如果第一列相等則再根據第二列排序，依次類推就構成了上圖的索引樹

以創建的索引 index_abc (a, b, c)為例，如上圖所示，每個結點都有三個鍵值，從上往下分別對應這a，b，c三個索引列

構造索引樹時，首先使用多列索引的第一列構建的索引樹，以 index_abc (a, b, c) 為例就是優先使用a列構建，當b列值相等時再以c列排序

因此，索引的第一列也就是a列可以說是從左到右單調遞增的，但我們看b列和c列並沒有這個特性，它們只能在a列值相等的情況下這個小範圍內遞增，看上圖的左下角的結點可理解這點

劃重點：由於複合索引樹建的時候就是按照當初你建立索引時（index_abc (a, b, c)）對應索引列的順序從左到右來建的，因此你使用的時候你也得按照從左到右的規則來用，這就是索引的 左匹配原則

所以為什麼上面 where a > 1000 and b > 1000 和 where a > 1000 and c > 1000 條件查詢的type是range，而 where b > 1000 and c > 1000 的type是index 你明白來嗎？

回表，聚集索引

我們都知道，B+樹有個特點就是，其葉子結點存的是關鍵字和數據，非葉子結點存的都是索引關鍵字，那麼複合索引構造的B+樹中，其葉子結點存的數據是什麼呢？答案該條數據的主鍵值

劃重點：也就是說，利用複合索引查找數據的流程是，先在複合索引的B+樹上找到對應數據的主鍵值（ID，註：MyISAM的索引葉子節點存儲記錄指針）,然後再根據這個主鍵（ID）值，到主鍵索引樹（B+樹）上查找這個ID所在的行記錄（主鍵索引樹的頁子結點存儲的關鍵字和對應的行記錄數據），最後查找結束。這個查找流程操作也叫 回表查詢

有沒有註意到，B+樹中，有的葉子結點存儲的行記錄，有點存儲的是主鍵值

劃重點：

  • 葉子結點存儲行記錄的索引又叫 聚集索引，InnoDB必須要有，且只有一個聚集索引：
    • 如果定義了主鍵，則主鍵索引就是 聚集索引
    • 如果沒有定義主鍵，則第一個not NULL unique列是聚集索引
    • 否則，InnoDB會創建一個隱藏的row-id作為聚集索引
  • 葉子結點存儲主鍵值叫普通索引，也叫 非聚集索引


覆蓋索引

還是上面的例子，我們再次看一下 where條件為 b > 1000 and c > 1000 的查詢 explain後的信息

mysql> explain select * from test where b > 1000 and c > 1000;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | index_abc | 12      | NULL | 10120 |    11.11 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+

 

按照我們剛剛講的索引的 左匹配原則，這個查詢應該沒有有效用上我們建的索引 index_abc ，為什麼key（實際使用到的索引）列卻是 index_abc？這裡就涉及到了 覆蓋索引

什麼是覆蓋索引？覆蓋索引 就是：SQL只需要通過索引就可以返回查詢所需要的數據，而不必通過二級索引查到主鍵之後再去查詢數據（即回表查詢）

不難理解，因為我們的test表本來就只有四個欄位，id, a, b, c，其中（a, b, c）建立列索引，id又是主鍵，複合索引樹的葉子結點存的就是主鍵值，所以 select * from test where b > 1000 and c > 1000 查找的數據通過複合索引樹就可以全部得到，不需要回表，因此這裡面用到了索引，這個索引樹實際是什麼索引的索引樹呢？，當然是index_abc了，因為b, c 列包含在複合索引列中

為什麼possible_keys列（可能使用到的索引）為NULL，因為搜索引擎找不到以b列開頭的索引

所以，使用列索引覆蓋，Extra列也就有列Using index


最後，為什麼 a > 1000 and b > 1000 和 b > 1000 and a > 1000，explain的結果一樣呢？

mysql> explain select * from test where a > 1000 and b > 1000;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | range | index_abc     | index_abc | 4       | NULL | 5060 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+

mysql> explain select * from test where b > 1000 and a > 1000;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | range | index_abc     | index_abc | 4       | NULL | 5060 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+

這就該我們mysql 查詢優化器 幹活了，mysql查詢優化器會判斷糾正這條sql語句該以什麼樣的順序執行效率最高，最後才生成真正的執行計劃。


至此，索引的左匹配原則，聚集索引，回表查詢，覆蓋索引就分享完了

如有不妥之處，歡迎指正，交流

 

點個贊再走唄～thxs~~~~


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

-Advertisement-
Play Games
更多相關文章
  • Linux學習筆記（四）幫助命令
    幫助命令 man info help help man 英文原意：format and display the on line manual pages 功能：顯示聯機幫助手冊 語法：man 選項[ fk] 命令 info （和man命令不同的是，info命令的幫助信息是一套完整的資料） 語法：in ...
  • Linux—crontab 命令詳解（計劃任務）
    編輯定時任務 */10 * * * * python /root/a.py >> /www/a.log 2>&1 # 每隔10分鐘，執行一次 30 * * * * python /root/a.py >> /www/a.log 2>&1 # 每小時, 第30分鐘執行 * 2 * * * * curl ...
  • 製作自己的Docker鏡像
    製作鏡像有2種方式，一種是容器轉換成鏡像，另一種是使用dockerfile創建鏡像，一般後者更常用。 容器轉為鏡像 使用 命令將容器轉換成鏡像 需要轉移鏡像時，將該鏡像打成一個包 在另一臺電腦載入這個鏡像時，載入這個包 使用dockerfile創建鏡像（推薦） dockerfile是一個文本文件，包 ...
  • powershell 更新 IIS SSL 證書
    最近發現我們開發環境的 IIS 上的 SSL 證書過期了，為了後面方便維護和更新，搞了一個 powershell 腳本，以後要更新的時候直接跑一下腳本就可以了，所以有了這篇文章 ...
  • Spire.Cloud 私有化部署教程（二）- Ubuntu 18.04 系統
    本教程主要介紹如何在Ubuntu 18.04系統上實現Spire.Cloud私有化部署。CentOS 7系統部署請參考 這篇教程。 詳細步驟如下： 一、環境配置 1、關閉防火牆 1）首先查看防火牆狀態 ufw status verbose （非管理員需在最前面加sudo） 預設情況下，防火牆狀態是i ...
  • 基本GCC，objdump，GBD命令的使用
    基本GCC命令的使用 GCC是一套由GNU項目開發的編程語言編譯器，可處理C語言、 C++、Fortran、Pascal、Objective C、Java等等。GCC通常是 跨平臺軟體的編譯器首選。gcc是GCC套件中的編譯驅動程式名。 若電腦是x86 64位系統，為了編譯成IA 32指令集， 則 ...
  • Linux學習筆記（三）目錄和文件都能操作的命令
    目錄和文件都能操作的命令 rm cp mv rm 英文原意：remove files or directories 功能：刪除文件或目錄 語法：rm 選項[ fir] 文件或目錄 cp 英文原意：copy files and directories 功能：複製文件和目錄 語法：cp 選項[ adil ...
  • HBase協處理器載入的三種方式
    本文主要給大家羅列了HBase協處理器載入的三種方式：Shell載入（動態）、Api載入（動態）、配置文件載入（靜態）。其中靜態載入方式需要重啟HBase。 我們假設我們已經有一個現成的需要載入的協處理器Jar包： HelloCoprocessor 0.0.1.jar 。 協處理器載入的三種方式 S ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 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 ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類