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表，在Java中的HashMap，TreeMap就是Hash表結構，以鍵值對的方式儲存資料。我們使用Hash表儲存表資料Key可以儲存索引列，Value可以儲存行記錄或者行磁碟地址。Hash表在等值查詢時效率很高，時間複雜度為O(1)；但是不支援範圍快速查詢，範圍查詢時還是隻能通過掃描全表方式。

顯然這種並不適合作為經常需要查詢和範圍查詢的資料庫索引使用。

二叉查詢樹

二元樹，我想大家都會在心裡有個圖。

在這裡插入圖片描述

二元樹特點：每個節點最多有2個分叉，左子樹和右子樹資料順序左小右大。

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

在這裡插入圖片描述

顯然這種情況不穩定的我們再選擇設計上必然會避免這種情況的

平衡二元樹

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

使用平衡二叉查詢樹查詢的效能接近於二分查詢法，時間複雜度是 O(log2n)。查詢id=6，只需要兩次IO。

在這裡插入圖片描述

就這個特點來看，可能各位會覺得這就很好，可以達到二元樹的理想的情況了。然而依然存在一些問題：

時間複雜度和樹高相關。樹有多高就需要檢索多少次，每個節點的讀取，都對應一次磁碟 IO 操作。樹的高度就等於每次查詢資料時磁碟 IO 操作的次數。磁碟每次尋道時間為10ms，在表資料量大時，查詢效能就會很差。（1百萬的資料量，log2n約等於20次磁碟IO，時間20*10=0.2s）
平衡二元樹不支援範圍查詢快速查詢，範圍查詢時需要從根節點多次遍歷，查詢效率不高。

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樹中查詢資料的情況：

假如我們查詢值等於10的資料。查詢路徑磁碟塊1->磁碟塊2->磁碟塊5。

第一次磁碟IO：將磁碟塊1載入到記憶體中，在記憶體中從頭遍歷比較，10<15，走左路，到磁碟定址磁碟塊2。

第二次磁碟IO：將磁碟塊2載入到記憶體中，在記憶體中從頭遍歷比較，7<10，到磁碟中定址定位到磁碟塊5。

第三次磁碟IO：將磁碟塊5載入到記憶體中，在記憶體中從頭遍歷比較，10=10，找到10，取出data，如果data儲存的行記錄，取出data，查詢結束。如果儲存的是磁碟地址，還需要根據磁碟地址到磁碟中取出資料，查詢終止。

相比二叉平衡查詢樹，在整個查詢過程中，雖然資料的比較次數並沒有明顯減少，但是磁碟IO次數會大大減少。同時，由於我們的比較是在記憶體中進行的，比較的耗時可以忽略不計。B樹的高度一般2至3層就能滿足大部分的應用場景，所以使用B樹構建索引可以很好的提升查詢的效率。

過程如圖：

看到這裡一定覺得B樹就很理想了，但是前輩們會告訴你依然存在可以優化的地方：

B樹不支援範圍查詢的快速查詢，你想想這麼一個情況如果我們想要查詢10和35之間的資料，查詢到15之後，需要回到根節點重新遍歷查詢，需要從根節點進行多次遍歷，查詢效率有待提高。

如果data儲存的是行記錄，行的大小隨著列數的增多，所佔空間會變大。這時，一個頁中可儲存的資料量就會變少，樹相應就會變高，磁碟IO次數就會變大。

B+樹：改造B樹

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

B樹：非葉子節點和葉子節點都會儲存資料。
B+樹：只有葉子節點才會儲存資料，非葉子節點至儲存鍵值。葉子節點之間使用雙向指標連線，最底層的葉子節點形成了一個雙向有序連結串列。

在這裡插入圖片描述

B+樹的最底層葉子節點包含了所有的索引項。從圖上可以看到，B+樹在查詢資料的時候，由於資料都存放在最底層的葉子節點上，所以每次查詢都需要檢索到葉子節點才能查詢到資料。所以在需要查詢資料的情況下每次的磁碟的IO跟樹高有直接的關係，但是從另一方面來說，由於資料都被放到了葉子節點，所以放索引的磁碟塊鎖存放的索引數量是會跟這增加的，所以相對於B樹來說，B+樹的樹高理論上情況下是比B樹要矮的。也存在索引覆蓋查詢的情況，在索引中資料滿足了當前查詢語句所需要的全部資料，此時只需要找到索引即可立刻返回，不需要檢索到最底層的葉子節點。

舉個例子：

等值查詢：

假如我們查詢值等於9的資料。查詢路徑磁碟塊1->磁碟塊2->磁碟塊6。

第一次磁碟IO：將磁碟塊1載入到記憶體中，在記憶體中從頭遍歷比較，9<15，走左路，到磁碟定址磁碟塊2。

第二次磁碟IO：將磁碟塊2載入到記憶體中，在記憶體中從頭遍歷比較，7<9<12，到磁碟中定址定位到磁碟塊6。

第三次磁碟IO：將磁碟塊6載入到記憶體中，在記憶體中從頭遍歷比較，在第三個索引中找到9，取出data，如果data儲存的行記錄，取出data，查詢結束。如果儲存的是磁碟地址，還需要根據磁碟地址到磁碟中取出資料，查詢終止。（這裡需要區分的是在InnoDB中Data儲存的為行資料，而MyIsam中儲存的是磁碟地址。）

過程如圖：

範圍查詢：

假如我們想要查詢9和26之間的資料。查詢路徑是磁碟塊1->磁碟塊2->磁碟塊6->磁碟塊7。

首先查詢值等於9的資料，將值等於9的資料快取到結果集。這一步和前面等值查詢流程一樣，發生了三次磁碟IO。

查詢到15之後，底層的葉子節點是一個有序列表，我們從磁碟塊6，鍵值9開始向後遍歷篩選所有符合篩選條件的資料。

第四次磁碟IO：根據磁碟6後繼指標到磁碟中定址定位到磁碟塊7，將磁碟7載入到記憶體中，在記憶體中從頭遍歷比較，9<25<26，9<26<=26，將data快取到結果集。

主鍵具備唯一性（後面不會有<=26的資料），不需再向後查詢，查詢終止。將結果集返回給使用者。

可以看到B+樹可以保證等值和範圍查詢的快速查詢，MySQL的索引就採用了B+樹的資料結構。

Mysql的索引實現

介紹完了索引資料結構，那肯定是要帶入到Mysql裡面看看真實的使用場景的，所以這裡分析Mysql的兩種儲存引擎的索引實現：MyISAM索引和InnoDB索引

MyIsam索引

以一個簡單的user表為例。user表存在兩個索引，id列為主鍵索引，age列為普通索引

CREATE TABLE `user`
(
  `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 = MyISAM
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8;

在這裡插入圖片描述

MyISAM的資料檔案和索引檔案是分開儲存的。MyISAM使用B+樹構建索引樹時，葉子節點中儲存的鍵值為索引列的值，資料為索引所在行的磁碟地址。

主鍵索引

在這裡插入圖片描述

表user的索引儲存在索引檔案user.MYI中，資料檔案儲存在資料檔案 user.MYD中。

簡單分析下查詢時的磁碟IO情況：

根據主鍵等值查詢資料：

select * from user where id = 28;

先在主鍵樹中從根節點開始檢索，將根節點載入到記憶體，比較28<75，走左路。（1次磁碟IO）
將左子樹節點載入到記憶體中，比較16<28<47，向下檢索。（1次磁碟IO）
檢索到葉節點，將節點載入到記憶體中遍歷，比較16<28，18<28，28=28。查詢到值等於30的索引項。（1次磁碟IO）
從索引項中獲取磁碟地址，然後到資料檔案user.MYD中獲取對應整行記錄。（1次磁碟IO）
將記錄返給使用者端。

磁碟IO次數：3次索引檢索+記錄資料檢索。

在這裡插入圖片描述

根據主鍵範圍查詢資料：

select * from user where id between 28 and 47;

先在主鍵樹中從根節點開始檢索，將根節點載入到記憶體，比較28<75，走左路。（1次磁碟IO）
將左子樹節點載入到記憶體中，比較16<28<47，向下檢索。（1次磁碟IO）
檢索到葉節點，將節點載入到記憶體中遍歷比較16<28，18<28，28=28<47。查詢到值等於28的索引項。

根據磁碟地址從資料檔案中獲取行記錄快取到結果集中。（1次磁碟IO）

我們的查詢語句時範圍查詢，需要向後遍歷底層葉子連結串列，直至到達最後一個不滿足篩選條件。
向後遍歷底層葉子連結串列，將下一個節點載入到記憶體中，遍歷比較，28<47=47，根據磁碟地址從資料檔案中獲取行記錄快取到結果集中。（1次磁碟IO）
最後得到兩條符合篩選條件，將查詢結果集返給使用者端。

磁碟IO次數：4次索引檢索+記錄資料檢索。

在這裡插入圖片描述

**備註：**以上分析僅供參考，MyISAM在查詢時，會將索引節點快取在MySQL快取中，而資料快取依賴於作業系統自身的快取，所以並不是每次都是走的磁碟，這裡只是為了分析索引的使用過程。

輔助索引

在 MyISAM 中,輔助索引和主鍵索引的結構是一樣的，沒有任何區別，葉子節點的資料儲存的都是行記錄的磁碟地址。只是主鍵索引的鍵值是唯一的，而輔助索引的鍵值可以重複。

查詢資料時，由於輔助索引的鍵值不唯一，可能存在多個擁有相同的記錄，所以即使是等值查詢，也需要按照範圍查詢的方式在輔助索引樹中檢索資料。

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的資料和索引儲存在一個檔案t_user_innodb.ibd中。InnoDB的資料組織方式，是聚簇索引。

主鍵索引的葉子節點會儲存資料行，輔助索引只會儲存主鍵值。

在這裡插入圖片描述

等值查詢資料：

select * from user_innodb where id = 28;

先在主鍵樹中從根節點開始檢索，將根節點載入到記憶體，比較28<75，走左路。（1次磁碟IO）
將左子樹節點載入到記憶體中，比較16<28<47，向下檢索。（1次磁碟IO）
檢索到葉節點，將節點載入到記憶體中遍歷，比較16<28，18<28，28=28。查詢到值等於28的索引項，直接可以獲取整行資料。將改記錄返回給使用者端。（1次磁碟IO）

磁碟IO數量：3次。

在這裡插入圖片描述

輔助索引

除聚簇索引之外的所有索引都稱為輔助索引，InnoDB的輔助索引只會儲存主鍵值而非磁碟地址。

以表user_innodb的age列為例，age索引的索引結果如下圖。

在這裡插入圖片描述

底層葉子節點的按照（age，id）的順序排序，先按照age列從小到大排序，age列相同時按照id列從小到大排序。

使用輔助索引需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然後使用主鍵到主索引中檢索獲得記錄。

畫圖分析等值查詢的情況：

select * from t_user_innodb where age=19;

在這裡插入圖片描述

根據在輔助索引樹中獲取的主鍵id，到主鍵索引樹檢索資料的過程稱為回表查詢。

磁碟IO數：輔助索引3次+獲取記錄回表3次

組合索引

還是以自己建立的一個表為例：表 abc_innodb，id為主鍵索引，建立了一個聯合索引idx_abc(a,b,c)。

CREATE TABLE `abc_innodb`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a`  int(11)     DEFAULT NULL,
  `b`  int(11)     DEFAULT NULL,
  `c`  varchar(10) DEFAULT NULL,
  `d`  varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_abc` (`a`, `b`, `c`)
) ENGINE = InnoDB;

select * from abc_innodb order by a, b, c, id;

在這裡插入圖片描述

組合索引的資料結構：

在這裡插入圖片描述

組合索引的查詢過程：

select * from abc_innodb where a = 13 and b = 16 and c = 4;

在這裡插入圖片描述

最左匹配原則：

最左字首匹配原則和聯合索引的索引儲存結構和檢索方式是有關係的。

在組合索引樹中，最底層的葉子節點按照第一列a列從左到右遞增排列，但是b列和c列是無序的，b列只有在a列值相等的情況下小範圍內遞增有序，而c列只能在a，b兩列相等的情況下小範圍內遞增有序。

就像上面的查詢，B+樹會先比較a列來確定下一步應該搜尋的方向，往左還是往右。如果a列相同再比較b列。但是如果查詢條件沒有a列，B+樹就不知道第一步應該從哪個節點查起。

可以說建立的idx_abc(a,b,c)索引，相當於建立了(a)、（a,b）（a,b,c）三個索引。、

組合索引的最左字首匹配原則：使用組合索引查詢時，mysql會一直向右匹配直至遇到範圍查詢(>、<、between、like)就停止匹配。

覆蓋索引

覆蓋索引並不是說是索引結構，覆蓋索引是一種很常用的優化手段。因為在使用輔助索引的時候，我們只可以拿到主鍵值，相當於獲取資料還需要再根據主鍵查詢主鍵索引再獲取到資料。但是試想下這麼一種情況，在上面abc_innodb表中的組合索引查詢時，如果我只需要abc欄位的，那是不是意味著我們查詢到組合索引的葉子節點就可以直接返回了，而不需要回表。這種情況就是覆蓋索引。

可以看一下執行計劃：

覆蓋索引的情況：

在這裡插入圖片描述

未使用到覆蓋索引：

在這裡插入圖片描述

總結

看到這裡，你是不是對於自己的sql語句裡面的索引的有了更多優化想法呢。比如：

避免回表

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

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

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

這裡就是一個典型的使用覆蓋索引的優化策略減少回表的情況。

聯合索引的使用

聯合索引，在建立索引的時候，儘量在多個單列索引上判斷下是否可以使用聯合索引。聯合索引的使用不僅可以節省空間，還可以更容易的使用到索引覆蓋。試想一下，索引的欄位越多，是不是更容易滿足查詢需要返回的資料呢。比如聯合索引（a_b_c），是不是等於有了索引：a，a_b，a_b_c三個索引，這樣是不是節省了空間，當然節省的空間並不是三倍於（a，a_b，a_b_c）三個索引，因為索引樹的資料沒變，但是索引data欄位的資料確實真實的節省了。

聯合索引的建立原則，在建立聯合索引的時候因該把頻繁使用的列、區分度高的列放在前面，頻繁使用代表索引利用率高，區分度高代表篩選粒度大，這些都是在索引建立的需要考慮到的優化場景，也可以在常需要作為查詢返回的欄位上增加到聯合索引中，如果在聯合索引上增加一個欄位而使用到了覆蓋索引，那我建議這種情況下使用聯合索引。

聯合索引的使用

考慮當前是否已經存在多個可以合併的單列索引，如果有，那麼將當前多個單列索引建立為一個聯合索引。
當前索引存在頻繁使用作為返回欄位的列，這個時候就可以考慮當前列是否可以加入到當前已經存在索引上，使其查詢語句可以使用到覆蓋索引。

敖丙

CSDN認證部落格專家 CSDN簽約作者演演算法工程師 B站網紅UP

微信搜一搜【敖丙】關注這個文縐縐的程式設計師，關注後回覆【資料】有我準備的一線大廠面試資料和簡歷模板。

一文搞懂MySQL索引所有知識點（建議收藏）

Mysql索引

索引介紹

索引是什麼

索引的優勢和劣勢

索引型別

主鍵索引

普通索引

唯一索引

全文索引

空間索引

字首索引

其他（按照索引列數量分類）

索引的資料結構

Hash表

二叉查詢樹

平衡二元樹

B樹：改造二元樹

B+樹：改造B樹

Mysql的索引實現

MyIsam索引

主鍵索引

輔助索引

InnoDB索引

主鍵索引（聚簇索引）

輔助索引

組合索引

覆蓋索引

總結

避免回表

聯合索引的使用