【MySQL】———

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??作者主頁：? ? ?作者主頁

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 本篇博客專欄：Linux

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??創作時間：2025年7月11日

Mysql索引

索引介紹

索引是什么

根據官方對索引的介紹，索引是幫助MySQL高效的獲取數據的數據結構，在我看來，索引就相當于一本書的目錄項，能加快查找數據的速度
當然，索引本身也是一種資源，索引也要存儲在MySQL中，但是索引一般不會存儲在內存中，因為索引占據的內存還是比較大的，一般的索引都是存儲在磁盤中的文件中
我們通常所說的索引，包括聚集索引、覆蓋索引、組合索引、前綴索引、唯一索引等，沒有特別說明，默認都是使用B+樹結構組織（多路搜索樹，并不一定是二叉的）的索引。

索引的優勢和劣勢

優勢：

索引可以提高數據檢索的效率，可以快速找到我們想要找到的資源，降低數據庫IO的成本，類似于書的目錄
通過索引對數據進行排序，可以降低數據排序的成本，降低CPU的消耗

被索引的列會自動進行排序，包括【單列索引】和【組合索引】，只是組合索引的排序要復雜一些。
如果按照索引列的順序進行排序，對應order by語句來說，效率就會提高很多。

劣勢：

索引會占據磁盤空間
索引雖然會提高查詢效率，但是會降低更新表的效率。比如每次對表進行增刪改操作，MySQL不僅要保存數據，還有保存或者更新對應的索引文件。

索引類型

主鍵索引

索引列中的值必須是唯一的，不允許有空值。

普通索引

MySQL中基本索引類型，沒有什么限制，允許在定義索引的列中插入重復值和空值。

唯一索引

索引列中的值必須是唯一的，但是允許為空值。

全文索引

只能在文本類型CHAR,VARCHAR,TEXT類型字段上創建全文索引。字段長度比較大時，如果創建普通索引，在進行like模糊查詢時效率比較低，這時可以創建全文索引。 MyISAM和InnoDB中都可以使用全文索引。

空間索引

MySQL在5.7之后的版本支持了空間索引，而且支持OpenGIS幾何數據模型。MySQL在空間索引這方面遵循OpenGIS幾何數據模型規則。

前綴索引

在文本類型如CHAR,VARCHAR,TEXT類列上創建索引時，可以指定索引列的長度，但是數值類型不能指定。

索引的數據結構

Hash表

我們使用Hash表存儲表數據Key可以存儲索引列，Value可以存儲行記錄或者行磁盤地址。Hash表在等值查詢時效率很高，時間復雜度為O(1)；但是不支持范圍快速查找，范圍查找時還是只能通過掃描全表方式。

顯然這種并不適合作為經常需要查找和范圍查找的數據庫索引使用。

二叉搜索樹

二叉樹，我想大家都會在心里有個圖。

這個特點就是為了保證每次查找都可以這折半而減少IO次數，但是二叉樹就很考驗第一個根節點的取值，因為很容易在這個特點下出現我們并發想發生的情況“樹不分叉了”，這就很難受很不穩定。

平衡二叉樹

平衡二叉樹是采用二分法思維，平衡二叉查找樹除了具備二叉樹的特點，最主要的特征是樹的左右兩個子樹的層級最多相差1。在插入刪除數據時通過左旋/右旋操作保持二叉樹的平衡，不會出現左子樹很高、右子樹很矮的情況。

B樹：改造二叉樹

MySQL的數據是存儲在磁盤文件中的，查詢處理數據時，需要先把磁盤中的數據加載到內存中，磁盤IO 操作非常耗時，所以我們優化的重點就是盡量減少磁盤 IO 操作。訪問二叉樹的每個節點就會發生一次IO，如果想要減少磁盤IO操作，就需要盡量降低樹的高度。那如何降低樹的高度呢？

假如key為bigint=8字節，每個節點有兩個指針，每個指針為4個字節，一個節點占用的空間16個字節（8+4*2=16）。

因為在MySQL的InnoDB存儲引擎一次IO會讀取的一頁（默認一頁16K）的數據量，而二叉樹一次IO有效數據量只有16字節，空間利用率極低。為了最大化利用一次IO空間，一個簡單的想法是在每個節點存儲多個元素，在每個節點盡可能多的存儲數據。每個節點可以存儲1000個索引（16k/16=1000），這樣就將二叉樹改造成了多叉樹，通過增加樹的叉樹，將樹從高瘦變為矮胖。構建1百萬條數據，樹的高度只需要2層就可以（1000*1000=1百萬），也就是說只需要2次磁盤IO就可以查詢到數據。磁盤IO次數變少了，查詢數據的效率也就提高了。

這種數據結構我們稱為B樹，B樹是一種多叉平衡查找樹，如下圖主要特點：

B樹的節點中存儲著多個元素，每個內節點有多個分叉。

節點中的元素包含鍵值和數據，節點中的鍵值從大到小排列。也就是說，在所有的節點都儲存數據。

父節點當中的元素不會出現在子節點中。

所有的葉子結點都位于同一層，葉節點具有相同的深度，葉節點之間沒有指針連接。

B+樹：改造B樹

B+樹，作為B樹的升級版，在B樹基礎上，MySQL在B樹的基礎上繼續改造，使用B+樹構建索引。B+樹和B樹最主要的區別在于非葉子節點是否存儲數據的問題

B樹：非葉子節點和葉子節點都會存儲數據。
B+樹：只有葉子節點才會存儲數據，非葉子節點至存儲鍵值。葉子節點之間使用雙向指針連接，最底層的葉子節點形成了一個雙向有序鏈表。

Mysql的索引實現

介紹完了索引數據結構，那肯定是要帶入到Mysql里面看看真實的使用場景的，所以這里分析一下一種實現方式：InnoDB索引

InnoDB索引

主鍵索引（聚簇索引）

每個InnoDB表都有一個聚簇索引，聚簇索引使用B+樹構建，葉子節點存儲的數據是整行記錄。一般情況下，聚簇索引等同于主鍵索引，當一個表沒有創建主鍵索引時，InnoDB會自動創建一個ROWID字段來構建聚簇索引。InnoDB創建索引的具體規則如下：

在表上定義主鍵PRIMARY KEY，InnoDB將主鍵索引用作聚簇索引。
如果表沒有定義主鍵，InnoDB會選擇第一個不為NULL的唯一索引列用作聚簇索引。
如果以上兩個都沒有，InnoDB 會使用一個6 字節長整型的隱式字段 ROWID字段構建聚簇索引。該ROWID字段會在插入新行時自動遞增。

除聚簇索引之外的所有索引都稱為輔助索引。在中InnoDB，輔助索引中的葉子節點存儲的數據是該行的主鍵值都。在檢索時，InnoDB使用此主鍵值在聚簇索引中搜索行記錄。

這里以user_innodb為例，user_innodb的id列為主鍵，age列為普通索引。

CREATE TABLE `user_innodb`
(`id`       int(11) NOT NULL AUTO_INCREMENT,`username` varchar(20) DEFAULT NULL,`age`      int(11)     DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE,KEY `idx_age` (`age`) USING BTREE
) ENGINE = InnoDB;

總結：

避免回表

在InnoDB的存儲引擎中，使用輔助索引查詢的時候，因為輔助索引葉子節點保存的數據不是當前記錄的數據而是當前記錄的主鍵索引，索引如果需要獲取當前記錄完整數據就必然需要根據主鍵值從主鍵索引繼續查詢。這個過程我們成位回表。想想回表必然是會消耗性能影響性能。那如何避免呢？

使用索引覆蓋，舉個例子：現有User表（id(PK),name(key),sex,address,hobby…）

如果在一個場景下，select id,name,sex from user where name ='zhangsan';這個語句在業務上頻繁使用到，而user表的其他字段使用頻率遠低于它，在這種情況下，如果我們在建立 name 字段的索引的時候，不是使用單一索引，而是使用聯合索引（name，sex）這樣的話再執行這個查詢語句是不是根據輔助索引查詢到的結果就可以獲取當前語句的完整數據。這樣就可以有效地避免了回表再獲取sex的數據。

最后：

十分感謝你可以耐著性子把它讀完和我可以堅持寫到這里，送幾句話，對你，也對我：

1.一個冷知識：
屏蔽力是一個人最頂級的能力，任何消耗你的人和事，多看一眼都是你的不對。

2.你不用變得很外向，內向挺好的，但需要你發言的時候，一定要勇敢。
正所謂：君子可內斂不可懦弱，面不公可起而論之。

3.成年人的世界，只篩選，不教育。

4.自律不是6點起床，7點準時學習，而是不管別人怎么說怎么看，你也會堅持去做，絕不打亂自己的節奏，是一種自我的恒心。

5.你開始炫耀自己，往往都是災難的開始，就像老子在《道德經》里寫到：光而不耀，靜水流深。

最后如果覺得我寫的還不錯，請不要忘記點贊?，收藏?，加關注?哦(｡･ω･｡)

愿我們一起加油，奔向更美好的未來，愿我們從懵懵懂懂的一枚菜鳥逐漸成為大佬。加油，為自己點贊！