一篇文章講清楚MySQL的聚簇/聯合/覆蓋索引、回表、索引下推

来源:https://www.cnblogs.com/yidengjiagou/archive/2022/06/25/16410968.html
-Advertisement-
Play Games

迎面走來了你的面試官,身穿格子衫,挺著啤酒肚,髮際線嚴重後移的中年男子。 手拿泡著枸杞的保溫杯,胳膊夾著MacBook,MacBook上還貼著公司標語:“加班使我快樂”。 面試官: 看你簡歷上用過MySQL,問你幾個簡單的問題吧。什麼是聚簇索引和非聚簇索引? 這個問題難不住我啊。來之前我看一下一燈M ...


迎面走來了你的面試官,身穿格子衫,挺著啤酒肚,髮際線嚴重後移的中年男子。
手拿泡著枸杞的保溫杯,胳膊夾著MacBook,MacBook上還貼著公司標語:“加班使我快樂”。

面試官: 看你簡歷上用過MySQL,問你幾個簡單的問題吧。什麼是聚簇索引和非聚簇索引?

這個問題難不住我啊。來之前我看一下一燈MySQL八股文。

我: 舉個例子:有這麼一張用戶表

CREATE TABLE `user` (
  `id` int COMMENT '主鍵ID',
  `name` varchar(10) COMMENT '姓名',
  `age` int COMMENT '年齡',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8 COMMENT='用戶表';

用戶表中存儲了這些數據:

id nane age
1 一燈 18
2 張三 22
3 李四 21
4 王二 19
5 麻子 20

那麼在索引中,這些數據是怎麼存儲的呢?

MySQL的InnoDB引擎中索引使用的B+樹結構。

別問為什麼根節點存儲了(1,4)兩個元素,左子節點又存儲了(1,2,3)三個元素,下麵帶有三個葉子節點,葉子節點之間又用有序鏈表相連?

問就是B+樹的特性,不瞭解的可以翻一下上期的文章。

如上圖所示,葉子節點中存儲了全部元素的索引,就是聚簇索引
一般主鍵索引就是聚簇索引,如果表中沒有主鍵,MySQL也會預設建立一個隱藏主鍵做主鍵索引。

什麼是非聚簇索引?

假設我們在age(年齡)欄位上建一個普通索引,age欄位上面的索引存儲結構就是下麵這樣:

葉子節點中只存儲了當前索引欄位和主鍵ID,這樣的存儲結構就是非聚簇索引。

面試官: 那什麼是聯合索引呢?

我: 有多個欄位組成的索引就是聯合索引。

面試官: 【暈】建聯合索引有什麼好處?它跟在單個欄位上建索引有什麼區別?

我: 假設有這麼一條查詢語句。

select * from user where age = 18 and name = '張三';

如果我們在age和name欄位上分別建兩個索引,這個查詢語句只會用到其中一個索引。

但是我們在age和name欄位建一個聯合索引(age,name),它的存儲結構就變成這樣了。

如果只在age上面建索引,會先查詢age上面非聚簇索引,有三條age=18的記錄,主鍵ID分別是1、4、5,然後再用這三個ID去查詢主鍵ID的聚簇索引。

如果在age和name上面建聯合索引,會先查詢age和name上面的非聚簇索引,匹配到一條記錄,主鍵ID是1,然後再用這個ID去查詢主鍵ID的聚簇索引。

由此可以得出,聯合索引的優點:大大減少掃描行數。

面試官: 你再說一下什麼是最左匹配原則?

我: 最左匹配原則是指在建立聯合索引的時候,遵循最左優先,以最左邊的為起點任何連續的索引都能匹配上。

當我們在(age,name)上建立聯合索引的時候,where條件中只有age可以用到索引,同時有age和name也可以用到索引。但是只有name的時候是無法用到索引的。

為什麼會出現這種情況呢?

看上面的圖,就理解了,(age,name)的聯合索引,是先按照age排序,age相等的行再按照name排序。如果where條件只有一個name,當然無法用到索引。

面試官: 什麼是覆蓋索引和回表查詢?

我: 這個就更簡單了,上面已經提到這個知識點了。

當我們在age上建索引的時候,查詢SQL是這樣的時候:

select id from user where age = 18;

就會用到覆蓋索引,因為ID欄位我們使用age索引的時候已經查出來,不需要再二次回表查詢了。

但是當查詢SQL是這樣的時候:

select * from user where age = 18;

想要查詢所有欄位,就需要二次回表查詢。因為我們第一次用age索引的時候只查出來了主鍵ID,還需要再用主鍵ID回表查詢出所有欄位。

面試官: 再問一個,你知道什麼是索引下推嗎?

這麼冷門的問題,你都問的出來,真的要面試造火箭啊!

我: 索引下推(Index Condition Pushdown)是MySQL5.6引入的一個優化索引的特性。

舉例:

在(age,name)上面建聯合索引,並且查詢SQL是這樣的時候:

select * from user where age = 18 and name = '張三';

如果沒有索引下推,會先匹配出 age = 18 的三條記錄,再用ID回表查詢,篩選出 name = '張三' 的記錄。

如果使用索引下推,會先匹配出 age = 18 的三條記錄,再篩選出 name = '張三' 的一條記錄,最後再用ID回表查詢。

由此得出,索引下推的優點:減少了回表的掃描行數。

**面試官: ** 小伙子,八股文背的挺溜啊。我給你出個實戰題,看你有沒有準備。下麵這個查詢SQL該怎麼建聯合索引?

select a from table where b = 1 and c = 2;

故意刁難我?你以為實戰題就不能背八股文了嗎?

我: 剛纔在講聯合索引的時候已經說了這個知識點了,where條件有b和c的等值查詢,聯合索引就建成(b,c),由於select後面有a,我們就建立 (b,c,a) 的聯合索引,並且可以用到覆蓋索引,查詢速度更快。

面試官: 小伙子,有點東西。一會兒就給你發offer,明天就來上班,薪資double。

文章持續更新,可以微信搜一搜「 一燈架構 」第一時間閱讀更多技術乾貨。


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

-Advertisement-
Play Games
更多相關文章
  • 背景 等值查找,有數組、列表、HashMap等,已經足夠了,範圍查找,該用什麼數據結構呢?下麵介紹java中非常好用的兩個類TreeMap和ConcurrentSkipListMap。 TreeMap的實現基於紅黑樹 每一棵紅黑樹都是一顆二叉排序樹,又稱二叉查找樹(Binary Search Tre ...
  • 在大部分涉及到資料庫操作的項目裡面,事務控制、事務處理都是一個無法迴避的問題。得益於Spring框架的封裝,業務代碼中進行事務控制操作起來也很簡單,直接加個@Transactional註解即可,大大簡化了對業務代碼的侵入性。那麼對@Transactional事務註解瞭解的夠全面嗎?知道有哪些場景可能... ...
  • 寫在前面 這是我在接觸爬蟲後,寫的第二個爬蟲實例。 也是我在學習python後真正意義上寫的第二個小項目,第一個小項目就是第一個爬蟲了。 我從學習python到現在,也就三個星期不到,平時課程比較多,python是額外學習的,每天學習python的時間也就一個小時左右。 所以我目前對於python也 ...
  • 多對一關係是什麼 Django使用django.db.models.ForeignKey定義多對一關係。 ForeignKey需要一個位置參數:與該模型關聯的類 class Info(models.Model): user = models.ForeignKey(other_model,on_del ...
  • 一、什麼是智能指針 一般來講C++中對於指針指向的對象需要使用new主動分配堆空間,在使用結束後還需要主動調用delete釋放這個堆空間。為了使得自動、異常安全的對象生存期管理可行,就出現了智能指針這個概念。簡單來看智能指針是 RAII(Resource Acquisition Is Initial ...
  • 我們在做採集數據的時候,過快或者訪問頻繁,或者一訪問就給彈出驗證碼,然後就蚌珠了~ 今天就給大家來一個簡單處理驗證碼的方法 環境模塊 本文使用的是 Python和pycharm 這裡需要用到一個 ddddocr 模塊 ,這是別人開源寫好的一個東西,簡單又好用,但是精確度差一點點,但是還是非常好用的。 ...
  • mysql服務端整體架構 主要分為兩部分,server層和存儲引擎 server層包括連接器、查詢緩存、分析器、優化器、執行器等,涵蓋mysql的大多數核心服務過功能,以及所有的內置函數,所有跨存儲引擎的功能都在這一層實現,比如存儲過程,觸發器,視圖等 存儲引擎層負責數據等存儲和讀取,其架構模式是插 ...
  • tunm二進位協議在python上的實現 tunm是一種對標JSON的二進位協議, 支持JSON的所有類型的動態組合 支持的數據類型 基本支持的類型 "u8", "i8", "u16", "i16", "u32", "i32", "u64", "i64", "varint", "float", "s ...
一周排行
    -Advertisement-
    Play Games
  • 一:背景 準備開個系列來聊一下 PerfView 這款工具,熟悉我的朋友都知道我喜歡用 WinDbg,這東西雖然很牛,但也不是萬能的,也有一些場景他解決不了或者很難解決,這時候藉助一些其他的工具來輔助,是一個很不錯的主意。 很多朋友喜歡在項目中以記錄日誌的方式來監控項目的流轉情況,其實 CoreCL ...
  • 本來閑來無事,準備看看Dapper擴展的源碼學習學習其中的編程思想,同時整理一下自己代碼的單元測試,為以後的進一步改進打下基礎。 突然就發現問題了,源碼也不看了,開始改代碼,改了好久。 測試Dapper.LiteSql數據批量插入的時候,耗時20秒,感覺不正常,於是我測試了非Dapper版的Lite ...
  • 需求如下,在DEV框架項目中,需要在表格中增加一列顯示圖片,並且能編輯該列圖片,然後進行保存等操作,最終效果如下 這裡使用的是PictureEdit控制項來實現,打開DEV GridControl設計器,在ColumnEdit選擇PictureEdit: 綁定圖片代碼如下: DataTable dtO ...
  • 前兩天微軟偷偷更新了Visual Studio 2022 正式版版本 17.3 發佈,發佈摘要: MAUI 工作負荷 GA 生成 MAUI/Blazor CSS 熱重載支持 現在,你將能夠使用我們的新增功能在 Visual Studio 中使用每個更新試用一系列新功能。 選擇每個功能以瞭解有關特定功 ...
  • 航天和軍工領域的數字化轉型和建設正在積極推進,在與航天二院、航天三院、航天六院、航天九院、無線電廠、兵工廠等單位交流的過程中,用戶更聚焦試驗和生產過程中的痛點,迫切需要解決軟體平臺統一監測和控制設備及軟體與設備協同的問題。 ...
  • .NET 項目預設情況下 日誌是使用的 ILogger 介面,預設提供一下四種日誌記錄程式: 控制台 調試 EventSource EventLog 這四種記錄程式都是預設包含在 .NET 運行時庫中。關於這四種記錄程式的詳細介紹可以直接查看微軟的官方文檔 https://docs.microsof ...
  • 一:背景 上一篇我們聊到瞭如何去找 熱點函數,這一篇我們來看下當你的程式出現了 非托管記憶體泄漏 時如何去尋找可疑的代碼源頭,其實思路很簡單,就是在 HeapAlloc 或者 VirtualAlloc 時做 Hook 攔截,記錄它的調用棧以及分配的記憶體量, PerfView 會將這個 分配量 做成一個 ...
  • 背景 在 CI/CD 流程當中,測試是 CI 中很重要的部分。跟開發人員關係最大的就是單元測試,單元測試編寫完成之後,我們可以使用 IDE 或者 dot cover 等工具獲得單元測試對於業務代碼的覆蓋率。不過我們需要一個獨立的 CLI 工具,這樣我們才能夠在 Jenkins 的 CI 流程集成。 ...
  • 一、應用場景 大家在使用Mybatis進行開發的時候,經常會遇到一種情況:按照月份month將數據放在不同的表裡面,查詢數據的時候需要跟不同的月份month去查詢不同的表。 但是我們都知道,Mybatis是ORM持久層框架,即:實體關係映射,實體Object與資料庫表之間是存在一一對應的映射關係。比 ...
  • 我國目前並未出台專門針對網路爬蟲技術的法律規範,但在司法實踐中,相關判決已屢見不鮮,K 哥特設了“K哥爬蟲普法”專欄,本欄目通過對真實案例的分析,旨在提高廣大爬蟲工程師的法律意識,知曉如何合法合規利用爬蟲技術,警鐘長鳴,做一個守法、護法、有原則的技術人員。 案情介紹 深圳市快鴿互聯網科技有限公司 2 ...