溫馨提示×

溫馨提示×

您好,登錄后才能下訂單哦!

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

如何理解MySQL索引原理

發布時間:2021-10-15 13:55:39 來源:億速云 閱讀:139 作者:iii 欄目:web開發

本篇內容主要講解“如何理解MySQL索引原理”,感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷,實用性強。下面就讓小編來帶大家學習“如何理解MySQL索引原理”吧!

案例背景

假設面試官問你:在電商平臺的訂單中心系統中,通常要根據商品類型、訂單狀態篩選出需要的訂單,并按照訂單創建的時間進行排序,那針對下面這條 SQL,你怎么通過索引來提高查詢效率呢?

select * from order where status = 1 order by create_time asc
有的同學會認為,單獨給 status 建立一個索引就可以了。
但是更優的方式是建立一個 status 和 create_time 組合索引,這是為了避免 MySQL 數據庫發生文件排序。

因為在查詢時,你只能用到 status 的索引,但如果要對 create_time 排序,就要用文件排序 filesort,也就是在 SQL 執行計劃中,Extra 列會出現 Using filesort 。

所以你要利用索引的有序性,在 status 和 create_time 列建立聯合索引,這樣根據 status 篩選后的數據就是按照 create_time 排好序的,避免在文件排序。

案例分析

通過這個案例,你可以發現“索引知識”的重要性,

數據庫索引底層使用的是什么數據結構和算法呢?

  • 為什么 MySQL InnoDB 選擇 B+Tree 當默認的索引數據結構?

  • 如何通過執行計劃查看索引使用詳情?

  • 有哪些情況會導致索引失效?

  • 平時有哪些常見的優化索引的方法?

……

總結起來就是如下幾點:

  • 理解 MySQL InnoDB 的索引原理;

  • 掌握 B+Tree 相比于其他索引數據結構(如 B-Tree、二叉樹,以及 Hash 表)的優勢;

  • 掌握 MySQL 執行計劃的方法;

  • 掌握導致索引失效的常見情況;

  • 掌握實際工作中常用的建立高效索引的技巧(如前綴索引、建立覆蓋索引等)。

如果你曾經被問到其中某一個問題,那你就有必要認真夯實 MySQL 索引及優化的內容了。

案例解答

MySQL InnoDB 的索引原理

從數據結構的角度來看, MySQL 常見索引有 B+Tree 索引、HASH 索引、Full-Text 索引 。MySQL 常見的存儲引擎 InnoDB、MyISAM 和 Memory 分別支持的索引類型。(后兩個存儲引擎在實際工作和面試中很少提及,因此只講 InnoDB) 。

如何理解MySQL索引原理

索引類型

在實際應用中,InnoDB 是 MySQL 建表時默認的存儲引擎,B+Tree 索引類型也是 MySQL 存儲引擎采用最多的索引類型。

在創建表時,InnoDB 存儲引擎默認使用表的主鍵作為主鍵索引,該主鍵索引就是聚簇索引(Clustered Index),如果表沒有定義主鍵,InnoDB 就自己產生一個隱藏的 6 個字節的主鍵 ID 值作為主鍵索引, 而創建的主鍵索引默認使用的是 B+Tree 索引 。

接下來我們通過一個簡單的例子,說明一下 B+Tree 索引在存儲數據中的具體實現,為的是讓你理解通過 B+Tree 做索引的原理。

首先,我們創建一張商品表:

CREATE TABLE `product`  (   `id` int(11) NOT NULL,   `product_no` varchar(20)  DEFAULT NULL,   `name` varchar(255) DEFAULT NULL,   `price` decimal(10, 2) DEFAULT NULL,   PRIMARY KEY (`id`) USING BTREE ) CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

然后新增幾行數據:

如何理解MySQL索引原理

通過主鍵查詢(主鍵索引)商品數據的過程

此時當我們使用主鍵索引查詢商品 15 的時候,那么按照 B+Tree 索引原理,是如何找到對應數據的呢?

select * from product where id = 15

我們可以通過數據手動構建一個 B+Tree,它的每個節點包含 3 個子節點(B+Tree 每個節點允許有 M 個子節點,且 M>2),根節點中的數據值 1、18、36 分別是子節點(1,6,12),(18,24,30)和(36,41,52)中的最小值。

每一層父節點的數據值都會出現在下層子節點的數據值中,因此在葉子節點中,包括了所有的數據值信息,并且每一個葉子節點都指向下一個葉子節點,形成一個鏈表。如圖所示:

如何理解MySQL索引原理

我們舉例講解一下 B+Tree 的查詢流程,比如想要查找數據值 15,B+Tree 會自頂向下逐層進行查找:

  1. 鴻蒙官方戰略合作共建——HarmonyOS技術社區

  2. 將 15 與根節點的數據 (1,18,36) 比較,15 在 1 和 18 之間,所以根據 B+Tree的搜索邏輯,找到第二層的數據塊 (1,6,12);

  3. 在第二層的數據塊 (1,6,12) 中進行查找,因為 15 大于 12,所以找到第三層的數據塊 (12,15,17);

  4. 在葉子節點的數據塊 (12,15,17) 中進行查找,然后我們找到了數據值 15;

最終根據數據值 15 找到葉子節點中存儲的數據。

整個過程一共進行了 3 次 I/O 操作,所以 B+Tree 相比于 B 樹和二叉樹來說,最大的優勢在于查詢效率。

那么問題來了,如果你當前查詢數據時候,不是通過主鍵 ID,而是用商品編碼查詢商品,那么查詢過程又是怎樣的呢?

通過非主鍵(輔助索引)查詢商品數據的過程

如果你用商品編碼查詢商品(即使用輔助索引進行查詢),會先檢索輔助索引中的 B+Tree 的 商品編碼,找到對應的葉子節點,獲取主鍵值,然后再通過主鍵索引中的 B+Tree 樹查詢到對應的葉子節點,然后獲取整行數據。 這個過程叫回表 。

以上就是索引的實現原理。

在面試時,面試官一般不會讓你直接描述查詢索引的過程,但是會通過考察你對索引優化方法的理解,來評估你對索引原理的掌握程度,比如為什么 MySQL InnoDB 選擇 B+Tree 作為默認的索引數據結構?MySQL 常見的優化索引的方法有哪些?

所以接下來,我們就詳細了解一下在面試中如何回答索引優化的問題。

B+Tree 索引的優勢

如果你被問到“為什么 MySQL 會選擇 B+Tree 當索引數據結構?”其實在考察你兩個方面:B+Tree 的索引原理;B+Tree 索引相比于其他索引類型的優勢。

我們剛剛已經講了 B+Tree 的索引原理,現在就來回答一下 B+Tree 相比于其他常見索引結構,如 B 樹、二叉樹或 Hash 索引結構的優勢在哪兒?

B+Tree 相對于 B 樹 索引結構的優勢

B+Tree 只在葉子節點存儲數據,而 B 樹 的非葉子節點也要存儲數據,所以 B+Tree 的單個節點的數據量更小,在相同的磁盤 I/O 次數下,就能查詢更多的節點。

另外,B+Tree 葉子節點采用的是雙鏈表連接,適合 MySQL 中常見的基于范圍的順序查找,而 B 樹無法做到這一點。

B+Tree 相對于二叉樹索引結構的優勢

對于有 N 個葉子節點的 B+Tree,其搜索復雜度為O(logdN),其中 d 表示節點允許的最大子節點個數為 d 個。

在實際的應用當中, d 值是大于100的,這樣就保證了,即使數據達到千萬級別時,B+Tree 的高度依然維持在 3~4 層左右,也就是說一次數據查詢操作只需要做 3~4 次的磁盤 I/O 操作就能查詢到目標數據(這里的查詢參考上面 B+Tree 的聚簇索引的查詢過程)。

而二叉樹的每個父節點的兒子節點個數只能是 2 個,意味著其搜索復雜度為 O(logN),這已經比 B+Tree 高出不少,因此二叉樹檢索到目標數據所經歷的磁盤 I/O 次數要更多。

B+Tree 相對于 Hash 表存儲結構的優勢

我們知道范圍查詢是 MySQL 中常見的場景,但是 Hash 表不適合做范圍查詢,它更適合做等值的查詢,這也是 B+Tree 索引要比 Hash 表索引有著更廣泛的適用場景的原因。

至此,你就知道“為什么 MySQL 會選擇 B+Tree 來做索引”了。在回答時,你要著眼于 B+Tree 的優勢,然后再引入索引原理的查詢過程(掌握這些知識點,這個問題其實比較容易回答)。

接下來,我們進入下一個問題:在實際工作中如何查看索引的執行計劃。

通過執行計劃查看索引使用詳情 我這里有一張存儲商品信息的演示表 product:

CREATE TABLE `product`  (   `id` int(11) NOT NULL,   `product_no` varchar(20)  DEFAULT NULL,   `name` varchar(255) DEFAULT NULL,   `price` decimal(10, 2) DEFAULT NULL,   PRIMARY KEY (`id`) USING BTREE,   KEY 'index_name' ('name').   KEY 'index_id_name' ('id', 'name') ) CHARACTER SET = utf8 COLLATE = utf8_general_ci

表中包含了主鍵索引、name 字段上的普通索引,以及 id 和 name 兩個字段的聯合索引?,F在我們來看一條簡單查詢語句的執行計劃:

如何理解MySQL索引原理

執行計劃

對于執行計劃,參數有 possible_keys 字段表示可能用到的索引,key 字段表示實際用的索引,key_len 表示索引的長度,rows 表示掃描的數據行數。

這其中需要你重點關注 type 字段, 表示數據掃描類型,也就是描述了找到所需數據時使用的掃描方式是什么,常見掃描類型的執行效率從低到高的順序為(考慮到查詢效率問題,全表掃描和全索引掃描要盡量避免):

ALL(全表掃描);

index(全索引掃描);

range(索引范圍掃描);

ref(非唯一索引掃描);

eq_ref(唯一索引掃描);

const(結果只有一條的主鍵或唯一索引掃描)。

總的來說,執行計劃是研發工程師分析索引詳情必會的技能(很多大廠公司招聘 JD 上寫著“SQL 語句調優” ),所以你在面試時也要知道執行計劃核心參數的含義,如 type。在回答時,也要以重點參數為切入點,再擴展到其他參數,然后再說自己是怎么做 SQL 優化工作的。

索引失效的常見情況

在工作中,我們經常會碰到 SQL 語句不適用已有索引的情況,來看一個索引失效的例子:

這條帶有 like 查詢的 SQL 語句,沒有用到 product 表中的 index_name 索引。

我們結合普通索引的 B+Tree 結構看一下索引失效的原因:當 MySQL 優化器根據 name like ‘%路由器’ 這個條件,到索引 index_name 的 B+Tree 結構上進行查詢評估時,發現當前節點的左右子節點上的值都有可能符合 '%路由器' 這個條件,于是優化器判定當前索引需要掃描整個索引,并且還要回表查詢,不如直接全表掃描。

當然,還有其他類似的索引失效的情況:

索引列上做了計算、函數、類型轉換操作,這些情況下索引失效是因為查詢過程需要掃描整個索引并回表,代價高于直接全表掃描;

like 匹配使用了前綴匹配符 '%abc';

字符串不加引號導致類型轉換;

我給你的建議是, 如果 MySQL 查詢優化器預估走索引的代價比全表掃描的代價還要大,則不走對應的索引,直接全表掃描,如果走索引比全表掃描代價小,則使用索引。

常見優化索引的方法

前綴索引優化

前綴索引就是用某個字段中,字符串的前幾個字符建立索引,比如我們可以在訂單表上對商品名稱字段的前 5 個字符建立索引。使用前綴索引是為了減小索引字段大小,可以增加一個索引頁中存儲的索引值,有效提高索引的查詢速度。在一些大字符串的字段作為索引時,使用前綴索引可以幫助我們減小索引項的大小。

但是,前綴索引有一定的局限性,例如 order by 就無法使用前綴索引,無法把前綴索引用作覆蓋索引。

覆蓋索引優化

覆蓋索引是指 SQL 中 query 的所有字段,在索引 B+tree 的葉子節點上都能找得到的那些索引,從輔助索引中查詢得到記錄,而不需要通過聚簇索引查詢獲得。

假設我們只需要查詢商品的名稱、價格,有什么方式可以避免回表呢?

我們可以建立一個組合索引,即商品ID、名稱、價格作為一個組合索引。如果索引中存在這些數據,查詢將不會再次檢索主鍵索引,從而避免回表。所以,使用覆蓋索引的好處很明顯,即不需要查詢出包含整行記錄的所有信息,也就減少了大量的 I/O 操作。

聯合索引

聯合索引時,存在最左匹配原則,也就是按照最左優先的方式進行索引的匹配。

比如聯合索引 (userpin, username),如果查詢條件是 WHERE userpin=1 AND username=2,就可以匹配上聯合索引;或者查詢條件是 WHERE userpin=1,也能匹配上聯合索引,但是如果查詢條件是 WHERE username=2,就無法匹配上聯合索引。

另外,建立聯合索引時的字段順序,對索引效率也有很大影響。越靠前的字段被用于索引過濾的概率越高,實際開發工作中建立聯合索引時,要把區分度大的字段排在前面,這樣區分度大的字段越有可能被更多的 SQL 使用到。

區分度就是某個字段 column 不同值的個數除以表的總行數,比如性別的區分度就很小,不適合建立索引或不適合排在聯合索引列的靠前的位置,而 uuid 這類字段就比較適合做索引或排在聯合索引列的靠前的位置。

到此,相信大家對“如何理解MySQL索引原理”有了更深的了解,不妨來實際操作一番吧!這里是億速云網站,更多相關內容可以進入相關頻道進行查詢,關注我們,繼續學習!

向AI問一下細節

免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。

AI

亚洲午夜精品一区二区_中文无码日韩欧免_久久香蕉精品视频_欧美主播一区二区三区美女