MySQL中InnoDB及索引深入剖析

點(diǎn)擊上方藍(lán)色字體，選擇“設(shè)為星標(biāo)”

回復(fù)”資源“獲取更多資源

大數(shù)據(jù)技術(shù)與架構(gòu)

點(diǎn)擊右側(cè)關(guān)注，大數(shù)據(jù)開發(fā)領(lǐng)域最強(qiáng)公眾號！

暴走大數(shù)據(jù)

點(diǎn)擊右側(cè)關(guān)注，暴走大數(shù)據(jù)！

InnoDB頁

將數(shù)據(jù)劃分為若干個(gè)頁，以頁作為磁盤和內(nèi)存之間交互的基本單位，InnoDB中頁的大小一般為 16 KB。也就是在一般情況下，一次最少從磁盤中讀取16KB的內(nèi)容到內(nèi)存中，一次最少把內(nèi)存中的16KB內(nèi)容刷新到磁盤中。

InnoDB行格式

我們平時(shí)是以記錄為單位來向表中插入數(shù)據(jù)的，這些記錄在磁盤上的存放方式也被稱為行格式或者記錄格式。

記錄的額外信息

記錄的額外信息：分別是變長字段長度列表、NULL值列表和記錄頭信息

1.分別是變長字段長度列表
MySQL中比如VARCHAR(M)、VARBINARY(M)、各種TEXT類型，各種BLOB類型這些數(shù)據(jù)類型的列稱為變長字段，變長字段中存儲多少字節(jié)的數(shù)據(jù)是不固定的。
所以這些變長字段占用的存儲空間分為兩部分：真正的數(shù)據(jù)內(nèi)容、占用的字節(jié)數(shù)

在行格式中，把所有變長字段的真實(shí)數(shù)據(jù)占用的字節(jié)長度都存放在記錄的開頭部位，從而形成一個(gè)變長字段長度列表，各變長字段數(shù)據(jù)占用的字節(jié)數(shù)按照列的順序逆序存放 。

變長字段長度列表中只存儲值為 非NULL 的列內(nèi)容占用的長度，值為 NULL 的列的長度是不儲存的 。

并不是所有記錄都有這個(gè) 變長字段長度列表 部分，比方說表中所有的列都不是變長的數(shù)據(jù)類型的話，這一部分就不需要有。

2.NULL值列表
表中的某些列可能存儲NULL值，如果把這些NULL值都放到記錄的真實(shí)數(shù)據(jù)中存儲會很占地方，所以Compact行格式把這些值為NULL的列統(tǒng)一管理起來，存儲到NULL值列表中，它的處理過程是這樣的：

1. 首先統(tǒng)計(jì)表中允許存儲NULL的列有哪些。

2. 如果表中沒有允許存儲 NULL 的列，則 NULL值列表 也不存在了

3. 記錄頭信息
   

Copy
先創(chuàng)建一個(gè)表：
mysql> CREATE TABLE page_demo(
    ->     c1 INT,
    ->     c2 INT,
    ->     c3 VARCHAR(10000),
    ->     PRIMARY KEY (c1)
    -> ) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.03 sec)

主鍵的生成策略
InnoDB表對主鍵的生成策略：優(yōu)先使用用戶自定義主鍵作為主鍵，如果用戶沒有定義主鍵，則選取一個(gè)Unique鍵作為主鍵，如果表中連Unique鍵都沒有定義的話，則InnoDB會為表默認(rèn)添加一個(gè)名為row_id的隱藏列作為主鍵。

這個(gè)新創(chuàng)建的page_demo表有3個(gè)列，其中c1和c2列是用來存儲整數(shù)的，c3列是用來存儲字符串的。需要注意的是，我們把 c1 列指定為主鍵，所以在具體的行格式中InnoDB就沒必要為我們?nèi)?chuàng)建那個(gè)所謂的 row_id 隱藏列了。

簡化后的行格式示意圖就是這樣：

InnoDB數(shù)據(jù)頁結(jié)構(gòu)

數(shù)據(jù)頁代表的這塊16KB大小的存儲空間可以被劃分為多個(gè)部分，不同部分有不同的功能，各個(gè)部分如圖所示：

每當(dāng)我們插入一條記錄，都會從Free Space部分，也就是尚未使用的存儲空間中申請一個(gè)記錄大小的空間劃分到User Records部分，當(dāng)Free Space部分的空間全部被User Records部分替代掉之后，也就意味著這個(gè)頁使用完了，如果還有新的記錄插入的話，就需要去申請新的頁了，這個(gè)過程的圖示如下：

這里我們繼續(xù)上面的page_demo例子：

Copy
先創(chuàng)建一個(gè)表：
mysql> CREATE TABLE page_demo(
    ->     c1 INT,
    ->     c2 INT,
    ->     c3 VARCHAR(10000),
    ->     PRIMARY KEY (c1)
    -> ) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.03 sec)

向page_demo表中插入幾條記錄：
mysql> INSERT INTO page_demo VALUES(1, 100, 'aaaa'),
									  (2, 200, 'bbbb'),
  								  (3, 300, 'cccc'),
									  (4, 400, 'dddd');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

那么，這些記錄的示意圖就是：

• delete_mask
  這個(gè)屬性標(biāo)記著當(dāng)前記錄是否被刪除。
  這些被刪除的記錄之所以不立即從磁盤上移除，是因?yàn)橐瞥鼈冎蟀哑渌挠涗浽诖疟P上重新排列需要性能消耗，所以只是打一個(gè)刪除標(biāo)記而已。
  所有被刪除掉的記錄都會組成一個(gè)所謂的垃圾鏈表，在這個(gè)鏈表中的記錄占用的空間稱之為所謂的可重用空間，之后如果有新記錄插入到表中的話，可能把這些被刪除的記錄占用的存儲空間覆蓋掉。

• min_rec_mask
  B+樹的每層非葉子節(jié)點(diǎn)中的最小記錄都會添加該標(biāo)記，min_rec_mask值都是0，意味著它們都不是B+樹的非葉子節(jié)點(diǎn)中的最小記錄。

• n_owned
  在頁目錄分組時(shí)使用，每個(gè)組的最后一條記錄（也就是組內(nèi)最大的那條記錄）的頭信息中的n_owned屬性表示該記錄擁有多少條記錄，也就是該組內(nèi)共有幾條記錄。

• heap_no
  這個(gè)屬性表示當(dāng)前記錄在本頁中的位置，從圖中可以看出來，我們插入的4條記錄在本頁中的位置分別是：2、3、4、5。
  heap_no值為0和1的記錄，稱為偽記錄或者虛擬記錄。這兩個(gè)偽記錄一個(gè)代表最小記錄，一個(gè)代表最大記錄。
  

• record_type
  這個(gè)屬性表示當(dāng)前記錄的類型，一共有4種類型的記錄，0表示普通記錄，1表示B+樹非葉節(jié)點(diǎn)記錄，2表示最小記錄，3表示最大記錄。

• next_record
  它表示從當(dāng)前記錄的真實(shí)數(shù)據(jù)到下一條記錄的真實(shí)數(shù)據(jù)的地址偏移量。比方說第一條記錄的next_record值為32，意味著從第一條記錄的真實(shí)數(shù)據(jù)的地址處向后找32個(gè)字節(jié)便是下一條記錄的真實(shí)數(shù)據(jù)。
  下一條記錄指得并不是按照我們插入順序的下一條記錄，而是按照主鍵值由小到大的順序的下一條記錄。而且規(guī)定 Infimum記錄（也就是最小記錄） 的下一條記錄就是本頁中主鍵值最小的用戶記錄，而本頁中主鍵值最大的用戶記錄的下一條記錄就是 Supremum記錄（也就是最大記錄）
  
  從圖中可以看出來，我們的記錄按照主鍵從小到大的順序形成了一個(gè)單鏈表。
  如果從中刪除掉一條記錄，這個(gè)鏈表也是會跟著變化的，比如我們把第2條記錄刪掉：

Copy
mysql> DELETE FROM page_demo WHERE c1 = 2;
Query OK, 1 row affected (0.02 sec)

Copy
* 第2條記錄并沒有從存儲空間中移除，而是把該條記錄的delete_mask值設(shè)置為1。
* 第2條記錄的next_record值變?yōu)榱?，意味著該記錄沒有下一條記錄了。
* 第1條記錄的next_record指向了第3條記錄。
* 還有一點(diǎn)你可能忽略了，就是最大記錄的n_owned值從5變成了4

因?yàn)橹麈I值為2的記錄被我們刪掉了，但是存儲空間卻沒有回收，如果我們再次把這條記錄插入到表中：

Copy
mysql> INSERT INTO page_demo VALUES(2, 200, 'bbbb');
Query OK, 1 row affected (0.00 sec)

Page Directory（頁目錄）

現(xiàn)在我們了解了記錄在頁中按照主鍵值由小到大順序串聯(lián)成一個(gè)單鏈表，那如果我們想根據(jù)主鍵值查找頁中的某條記錄該咋辦呢？

設(shè)計(jì)InnoDB的大叔們?yōu)槲覀兊挠涗浺仓谱髁艘粋€(gè)類似的目錄，他們的制作過程是這樣的：

1. 將所有正常的記錄（包括最大和最小記錄，不包括標(biāo)記為已刪除的記錄）劃分為幾個(gè)組。

2. 每個(gè)組的最后一條記錄（也就是組內(nèi)最大的那條記錄）的頭信息中的n_owned屬性表示該記錄擁有多少條記錄，也就是該組內(nèi)共有幾條記錄

3. 將每個(gè)組的最后一條記錄的地址偏移量單獨(dú)提取出來，用作查找。

注意：這個(gè)頁目錄是為主鍵服務(wù)的。

對于最小記錄所在的分組只能有 1 條記錄，最大記錄所在的分組擁有的記錄條數(shù)只能在 1-8 條之間，剩下的分組中記錄的條數(shù)范圍只能在是 4-8 條之間。

分組是按照下邊的步驟進(jìn)行：

• 初始情況下一個(gè)數(shù)據(jù)頁里只有最小記錄和最大記錄兩條記錄，它們分屬于兩個(gè)分組。

• 之后每插入一條記錄，都會從頁目錄中找到主鍵值比本記錄的主鍵值大并且差值最小的槽，然后把該槽對應(yīng)的記錄的n_owned值加1，表示本組內(nèi)又添加了一條記錄，直到該組中的記錄數(shù)等于8個(gè)。

• 在一個(gè)組中的記錄數(shù)等于8個(gè)后再插入一條記錄時(shí)，會將組中的記錄拆分成兩個(gè)組，一個(gè)組中4條記錄，另一個(gè)5條記錄。這個(gè)過程會在頁目錄中新增一個(gè)槽來記錄這個(gè)新增分組中最大的那條記錄的偏移量。

我們再添加12條記錄看看效果：

Copy
mysql> INSERT INTO page_demo VALUES(5, 500, 'eeee'), (6, 600, 'ffff'), (7, 700, 'gggg'), (8, 800, 'hhhh'), (9, 900, 'iiii'), (10, 1000, 'jjjj'), (11, 1100, 'kkkk'), (12, 1200, 'llll'), (13, 1300, 'mmmm'), (14, 1400, 'nnnn'), (15, 1500, 'oooo'), (16, 1600, 'pppp');
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

因?yàn)榘?6條記錄的全部信息都畫在一張圖里太占地方，讓人眼花繚亂的，所以只保留了用戶記錄頭信息中的n_owned和next_record屬性。

因?yàn)楦鱾€(gè)槽代表的記錄的主鍵值都是從小到大排序的，所以我們可以使用所謂的二分法來進(jìn)行快速查找。

所以在一個(gè)數(shù)據(jù)頁中查找指定主鍵值的記錄的過程分為兩步：

1. 通過二分法確定該記錄所在的槽，并找到該槽所在分組中主鍵值最小的那條記錄。

2. 通過記錄的next_record屬性遍歷該槽所在的組中的各個(gè)記錄。

注意：若查到數(shù)據(jù)在槽2的分組中，由于槽2是指向最后一個(gè)記錄，所以需要向上找一個(gè)槽位，定位到上一個(gè)槽位最后一行，然后再向下找。

File Header（文件頭部）

File Header針對各種類型的頁都通用，也就是說不同類型的頁都會以File Header作為第一個(gè)組成部分，它描述了一些針對各種頁都通用的一些信息，比方說這個(gè)頁的編號是多少，它的上一個(gè)頁、下一個(gè)頁是誰等。

FIL_PAGE_OFFSET
每一個(gè)頁都有一個(gè)單獨(dú)的頁號，就跟你的身份證號碼一樣，InnoDB通過頁號來可以唯一定位一個(gè)頁。

FIL_PAGE_PREV和FIL_PAGE_NEXT
FIL_PAGE_PREV和FIL_PAGE_NEXT就分別代表本頁的上一個(gè)和下一個(gè)頁的頁號。這樣通過建立一個(gè)雙向鏈表把許許多多的頁就都串聯(lián)起來了

B+樹索引

InnoDB數(shù)據(jù)頁的主要組成部分。各個(gè)數(shù)據(jù)頁可以組成一個(gè)雙向鏈表，而每個(gè)數(shù)據(jù)頁中的記錄會按照主鍵值從小到大的順序組成一個(gè)單向鏈表，每個(gè)數(shù)據(jù)頁都會為存儲在它里邊兒的記錄生成一個(gè)頁目錄。再通過主鍵查找某條記錄的時(shí)候可以在頁目錄中使用二分法快速定位到對應(yīng)的槽。

在一個(gè)頁中的查找

• 以主鍵為搜索條件
  這個(gè)查找過程我們已經(jīng)很熟悉了，可以在頁目錄中使用二分法快速定位到對應(yīng)的槽，然后再遍歷該槽對應(yīng)分組中的記錄即可快速找到指定的記錄。

• 以其他列作為搜索條件
  對非主鍵列的查找的過程可就不這么幸運(yùn)了，因?yàn)樵跀?shù)據(jù)頁中并沒有對非主鍵列建立所謂的頁目錄，所以我們無法通過二分法快速定位相應(yīng)的槽。這種情況下只能從最小記錄開始依次遍歷單鏈表中的每條記錄，然后對比每條記錄是不是符合搜索條件。

在很多頁中查找

1. 定位到記錄所在的頁。

2. 從所在的頁內(nèi)中查找相應(yīng)的記錄。

在沒有索引的情況下，不論是根據(jù)主鍵列或者其他列的值進(jìn)行查找，由于我們并不能快速的定位到記錄所在的頁，所以只能從第一個(gè)頁沿著雙向鏈表一直往下找，在每一個(gè)頁中根據(jù)我們剛剛嘮叨過的查找方式去查找指定的記錄。

索引

為了故事的順利發(fā)展，我們先建一個(gè)表：

Copy
mysql> CREATE TABLE index_demo(
    ->     c1 INT,
    ->     c2 INT,
    ->     c3 CHAR(1),
    ->     PRIMARY KEY(c1)
    -> ) ROW_FORMAT = Compact;
Query OK, 0 rows affected (0.03 sec)

為了我們理解上的方便，我們簡化了一下index_demo表的行格式示意圖：

record_type
這個(gè)屬性表示當(dāng)前記錄的類型，一共有4種類型的記錄，0表示普通記錄，1表示B+樹非葉節(jié)點(diǎn)記錄，2表示最小記錄，3表示最大記錄。
next_record
記錄頭信息的一項(xiàng)屬性，表示下一條地址相對于本條記錄的地址偏移量

把一些記錄放到頁里邊的示意圖就是：

一個(gè)簡單的索引方案：
我們?yōu)楦鶕?jù)主鍵值快速定位一條記錄在頁中的位置而設(shè)立的頁目錄，目錄中記錄的數(shù)據(jù)頁必須下一個(gè)數(shù)據(jù)頁中用戶記錄的主鍵值必須大于上一個(gè)頁中用戶記錄的主鍵值。

我們這里需要做一個(gè)假設(shè)：假設(shè)我們的每個(gè)數(shù)據(jù)頁最多能存放3條記錄（實(shí)際上一個(gè)數(shù)據(jù)頁非常大，可以存放下好多記錄）。有了這個(gè)假設(shè)之后我們向index_demo表插入3條記錄：

Copy
mysql> INSERT INTO index_demo VALUES(1, 4, 'u'), (3, 9, 'd'), (5, 3, 'y');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

從圖中可以看出來，index_demo表中的3條記錄都被插入到了編號為10的數(shù)據(jù)頁中了。此時(shí)我們再來插入一條記錄：

Copy
mysql> INSERT INTO index_demo VALUES(4, 4, 'a');
Query OK, 1 row affected (0.00 sec)

因?yàn)轫?0最多只能放3條記錄，所以我們不得不再分配一個(gè)新頁：

頁10中用戶記錄最大的主鍵值是5，而頁28中有一條記錄的主鍵值是4，因?yàn)? > 4，所以這就不符合下一個(gè)數(shù)據(jù)頁中用戶記錄的主鍵值必須大于上一個(gè)頁中用戶記錄的主鍵值的要求，所以在插入主鍵值為4的記錄的時(shí)候需要伴隨著一次記錄移動，也就是把主鍵值為5的記錄移動到頁28中，然后再把主鍵值為4的記錄插入到頁10中，這個(gè)過程的示意圖如下：

這個(gè)過程叫做頁分裂。

由于數(shù)據(jù)頁的編號可能并不是連續(xù)的，所以在向index_demo表中插入許多條記錄后，可能是這樣的效果：

因?yàn)檫@些16KB的頁在物理存儲上可能并不挨著，所以如果想從這么多頁中根據(jù)主鍵值快速定位某些記錄所在的頁，我們需要給它們做個(gè)目錄，每個(gè)頁對應(yīng)一個(gè)目錄項(xiàng)，每個(gè)目錄項(xiàng)包括下邊兩個(gè)部分：
* 頁的用戶記錄中最小的主鍵值，我們用key來表示。
* 頁號，我們用page_no表示。

所以我們?yōu)樯线厧讉€(gè)頁做好的目錄就像這樣子：

比方說我們想找主鍵值為20的記錄，具體查找過程分兩步：

1. 先從目錄項(xiàng)中根據(jù)二分法快速確定出主鍵值為20的記錄在目錄項(xiàng)3中（因?yàn)?12 < 20 < 209），它對應(yīng)的頁是頁9。

2. 再根據(jù)前邊說的在頁中查找記錄的方式去頁9中定位具體的記錄。

這個(gè)目錄有一個(gè)別名，稱為索引。

InnoDB中的索引方案

在InnoDB中復(fù)用了之前存儲用戶記錄的數(shù)據(jù)頁來存儲目錄項(xiàng)，為了和用戶記錄做一下區(qū)分，我們把這些用來表示目錄項(xiàng)的記錄稱為目錄項(xiàng)記錄。

用record_type來區(qū)分普通的用戶記錄還是目錄項(xiàng)記錄。

一個(gè)頁只有16KB大小，能存放的目錄項(xiàng)記錄也是有限的，那如果表中的數(shù)據(jù)太多，以至于一個(gè)數(shù)據(jù)頁不足以存放所有的目錄項(xiàng)記錄，會再多整一個(gè)存儲目錄項(xiàng)記錄的頁。

假設(shè)一個(gè)存儲目錄項(xiàng)記錄的頁最多只能存放4條目錄項(xiàng)記錄（請注意是假設(shè)哦，真實(shí)情況下可以存放好多條的），所以如果此時(shí)我們再向上圖中插入一條主鍵值為320的用戶記錄的話：

在這個(gè)查詢步驟的第1步中我們需要定位存儲目錄項(xiàng)記錄的頁，但是這些頁在存儲空間中也可能不挨著，如果我們表中的數(shù)據(jù)非常多則會產(chǎn)生很多存儲目錄項(xiàng)記錄的頁，那我們怎么根據(jù)主鍵值快速定位一個(gè)存儲目錄項(xiàng)記錄的頁呢？其實(shí)也簡單，為這些存儲目錄項(xiàng)記錄的頁再生成一個(gè)更高級的目錄，就像是一個(gè)多級目錄一樣，大目錄里嵌套小目錄，小目錄里才是實(shí)際的數(shù)據(jù)，所以現(xiàn)在各個(gè)頁的示意圖就是這樣子：

隨著表中記錄的增加，這個(gè)目錄的層級會繼續(xù)增加，如果簡化一下，那么我們可以用下邊這個(gè)圖來描述它：

從圖中可以看出來，我們的實(shí)際用戶記錄其實(shí)都存放在B+樹的最底層的節(jié)點(diǎn)上，這些節(jié)點(diǎn)也被稱為葉子節(jié)點(diǎn)或葉節(jié)點(diǎn)，其余用來存放目錄項(xiàng)的節(jié)點(diǎn)稱為非葉子節(jié)點(diǎn)或者內(nèi)節(jié)點(diǎn)，其中B+樹最上邊的那個(gè)節(jié)點(diǎn)也稱為根節(jié)點(diǎn)。

聚簇索引

我們上邊介紹的B+樹本身就是一個(gè)目錄，或者說本身就是一個(gè)索引。它有兩個(gè)特點(diǎn)：

1. 使用記錄主鍵值的大小進(jìn)行記錄和頁的排序

2. B+樹的葉子節(jié)點(diǎn)存儲的是完整的用戶記錄。

我們把具有這兩種特性的B+樹稱為聚簇索引，所有完整的用戶記錄都存放在這個(gè)聚簇索引的葉子節(jié)點(diǎn)處。這種聚簇索引并不需要我們在MySQL語句中顯式的使用INDEX語句去創(chuàng)建（后邊會介紹索引相關(guān)的語句），InnoDB存儲引擎會自動的為我們創(chuàng)建聚簇索引。另外有趣的一點(diǎn)是，在InnoDB存儲引擎中，聚簇索引就是數(shù)據(jù)的存儲方式（所有的用戶記錄都存儲在了葉子節(jié)點(diǎn)），也就是所謂的索引即數(shù)據(jù)，數(shù)據(jù)即索引。

二級索引

這個(gè)B+樹與上邊介紹的聚簇索引有幾處不同：

• 使用記錄c2列的大小進(jìn)行記錄和頁的排序，這包括三個(gè)方面的含義：

• 頁內(nèi)的記錄是按照c2列的大小順序排成一個(gè)單向鏈表。

• 各個(gè)存放用戶記錄的頁也是根據(jù)頁中記錄的c2列大小順序排成一個(gè)雙向鏈表。

• 存放目錄項(xiàng)記錄的頁分為不同的層次，在同一層次中的頁也是根據(jù)頁中目錄項(xiàng)記錄的c2列大小順序排成一個(gè)雙向鏈表。

• B+樹的葉子節(jié)點(diǎn)存儲的并不是完整的用戶記錄，而只是c2列+主鍵這兩個(gè)列的值。

• 目錄項(xiàng)記錄中不再是主鍵+頁號的搭配，而變成了c2列+頁號的搭配。

以查找c2列的值為4的記錄為例：

1. 確定目錄項(xiàng)記錄頁，也就是頁44

2. 通過目錄項(xiàng)記錄頁確定用戶記錄真實(shí)所在的頁。
   在頁42中可以快速定位到實(shí)際存儲用戶記錄的頁，但是由于c2列并沒有唯一性約束，所以c2列值為4的記錄可能分布在多個(gè)數(shù)據(jù)頁中，又因?yàn)? < 4 ≤ 4，所以確定實(shí)際存儲用戶記錄的頁在頁34和頁35中。

3. 在真實(shí)存儲用戶記錄的頁中定位到具體的記錄。

4. 但是這個(gè)B+樹的葉子節(jié)點(diǎn)中的記錄只存儲了c2和c1（也就是主鍵）兩個(gè)列，所以我們必須再根據(jù)主鍵值去聚簇索引中再查找一遍完整的用戶記錄。這個(gè)過程被稱為回表。

如果把完整的用戶記錄放到葉子節(jié)點(diǎn)是可以不用回表，但是太占地方了呀～相當(dāng)于每建立一棵B+樹都需要把所有的用戶記錄再都拷貝一遍，這就有點(diǎn)太浪費(fèi)存儲空間了。

聯(lián)合索引

我們也可以同時(shí)以多個(gè)列的大小作為排序規(guī)則，也就是同時(shí)為多個(gè)列建立索引，比方說我們想讓B+樹按照c2和c3列的大小進(jìn)行排序：

• 先把各個(gè)記錄和頁按照c2列進(jìn)行排序。

• 在記錄的c2列相同的情況下，采用c3列進(jìn)行排序
  

如圖所示，我們需要注意一下幾點(diǎn)：

• 每條目錄項(xiàng)記錄都由c2、c3、頁號這三個(gè)部分組成，各條記錄先按照c2列的值進(jìn)行排序，如果記錄的c2列相同，則按照c3列的值進(jìn)行排序。

• B+樹葉子節(jié)點(diǎn)處的用戶記錄由c2、c3和主鍵c1列組成。

索引的代價(jià)

• 空間上的代價(jià)
  每建立一個(gè)索引都要為它建立一棵B+樹，每一棵B+樹的每一個(gè)節(jié)點(diǎn)都是一個(gè)數(shù)據(jù)頁，一個(gè)頁默認(rèn)會占用16KB的存儲空間

• 時(shí)間上的代價(jià)
  每次對表中的數(shù)據(jù)進(jìn)行增、刪、改操作時(shí)，都需要去修改各個(gè)B+樹索引。

B+樹每層節(jié)點(diǎn)都是按照索引列的值從小到大的順序排序而組成了雙向鏈表。不論是葉子節(jié)點(diǎn)中的記錄，還是內(nèi)節(jié)點(diǎn)中的記錄（也就是不論是用戶記錄還是目錄項(xiàng)記錄）都是按照索引列的值從小到大的順序而形成了一個(gè)單向鏈表。而增、刪、改操作可能會對節(jié)點(diǎn)和記錄的排序造成破壞，所以存儲引擎需要額外的時(shí)間進(jìn)行一些記錄移位，頁面分裂、頁面回收啥的操作來維護(hù)好節(jié)點(diǎn)和記錄的排序。

B+樹索引適用的條件

那么再次建議回過頭把前邊的內(nèi)容看完了再來，要不然讀文章對你來說是一種折磨。首先，B+樹索引并不是萬能的，并不是所有的查詢語句都能用到我們建立的索引。下邊介紹幾個(gè)我們可能使用B+樹索引來進(jìn)行查詢的情況。

Copy
CREATE TABLE person_info(
    id INT NOT NULL auto_increment,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);

person_info表會為聚簇索引和idx_name_birthday_phone_number索引建立2棵B+樹。

在記錄結(jié)構(gòu)中只保留name、birthday、phone_number、id這四個(gè)列的真實(shí)數(shù)據(jù)值，所以示意圖就長這樣：

內(nèi)節(jié)點(diǎn)中存儲的是目錄項(xiàng)記錄，葉子節(jié)點(diǎn)中存儲的是用戶記錄（由于不是聚簇索引，所以用戶記錄是不完整的，缺少country列的值）。

• 先按照name列的值進(jìn)行排序。

• 如果name列的值相同，則按照birthday列的值進(jìn)行排序。

• 如果birthday列的值也相同，則按照phone_number的值進(jìn)行排序。

全值匹配

如果我們的搜索條件中的列和索引列一致的話，這種情況就稱為全值匹配，比方說下邊這個(gè)查找語句：

Copy
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239';

• 因?yàn)锽+樹的數(shù)據(jù)頁和記錄先是按照name列的值進(jìn)行排序的，所以先可以很快定位name列的值是Ashburn的記錄位置。

• 在name列相同的記錄里又是按照birthday列的值進(jìn)行排序的，所以在name列的值是Ashburn的記錄里又可以快速定位birthday列的值是'1990-09-27'的記錄。

• 如果很不幸，name和birthday列的值都是相同的，那記錄是按照phone_number列的值排序的，所以聯(lián)合索引中的三個(gè)列都可能被用到。

調(diào)換name、birthday、phone_number這幾個(gè)搜索列的順序?qū)Σ樵兊膱?zhí)行過程是沒有影響的。

匹配左邊的列

Copy
SELECT * FROM person_info WHERE name = 'Ashburn';

或者包含多個(gè)左邊的列也行：

Copy
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27';

只有左邊的列才能匹配，下邊的語句就用不到這個(gè)B+樹索引：

Copy
SELECT * FROM person_info WHERE birthday = '1990-09-27';

因?yàn)锽+樹的數(shù)據(jù)頁和記錄先是按照name列的值排序的，在name列的值相同的情況下才使用birthday列進(jìn)行排序，也就是說name列的值不同的記錄中birthday的值可能是無序的。

如果我們想使用聯(lián)合索引中盡可能多的列，搜索條件中的各個(gè)列必須是聯(lián)合索引中從最左邊連續(xù)的列。

匹配列前綴

person_info表上建立的聯(lián)合索引idx_name_birthday_phone_number會先用name列的值進(jìn)行排序。

也就是說這些字符串的前n個(gè)字符，也就是前綴都是排好序的，所以對于字符串類型的索引列來說，我們只匹配它的前綴也是可以快速定位記錄的，比方說我們想查詢名字以'As'開頭的記錄，那就可以這么寫查詢語句：

Copy
SELECT * FROM person_info WHERE name LIKE 'As%';

同理，這樣也是無法匹配的：

Copy
SELECT * FROM person_info WHERE name LIKE '%As%';

匹配范圍值

所有記錄都是按照索引列的值從小到大的順序排好序的，所以這極大的方便我們查找索引列的值在某個(gè)范圍內(nèi)的記錄。

Copy
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

由于所有記錄都是由鏈表連起來的（記錄之間用單鏈表，數(shù)據(jù)頁之間用雙鏈表），所以他們之間的記錄都可以很容易的取出來嘍～
找到這些記錄的主鍵值，再到聚簇索引中回表查找完整的記錄。

如果對多個(gè)列同時(shí)進(jìn)行范圍查找的話，只有對索引最左邊的那個(gè)列進(jìn)行范圍查找的時(shí)候才能用到B+樹索引，比方說這樣：

Copy
SELECT * FROM person_info WHERE name > ‘Asa’ AND name < ‘Barlow’
AND birthday > '1980-01-01';

上邊這個(gè)查詢可以分成兩個(gè)部分：

1. 通過條件name > 'Asa' AND name < 'Barlow'來對name進(jìn)行范圍。

2. 對這些name值不同的記錄繼續(xù)通過birthday > '1980-01-01'條件繼續(xù)過濾。

對于聯(lián)合索引idx_name_birthday_phone_number來說，只能用到name列的部分，而用不到birthday列的部分，因?yàn)橹挥衝ame值相同的情況下才能用birthday列的值進(jìn)行排序，而這個(gè)查詢中通過name進(jìn)行范圍查找的記錄中可能并不是按照birthday列進(jìn)行排序的，所以在搜索條件中繼續(xù)以birthday列進(jìn)行查找時(shí)是用不到這個(gè)B+樹索引的。

精確匹配某一列并范圍匹配另外一列

雖然對多個(gè)列都進(jìn)行范圍查找時(shí)只能用到最左邊那個(gè)索引列，但是如果左邊的列是精確查找，則右邊的列可以進(jìn)行范圍查找，比方說這樣：

Copy
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday < '2000-12-31' AND phone_number > '15100000000';

由于name列是精確查找，所以通過name = 'Ashburn'條件查找后得到的結(jié)果的name值都是相同的，它們會再按照birthday的值進(jìn)行排序。所以此時(shí)對birthday列進(jìn)行范圍查找是可以用到B+樹索引的。

phone_number > '15100000000'，通過birthday的范圍查找的記錄的birthday的值可能不同，所以這個(gè)條件無法再利用B+樹索引了，只能遍歷上一步查詢得到的記錄。

用于排序

有的時(shí)候可能查詢的結(jié)果集太大以至于不能在內(nèi)存中進(jìn)行排序的話，還可能暫時(shí)借助磁盤的空間來存放中間結(jié)果，排序操作完成后再把排好序的結(jié)果集返回到客戶端。在MySQL中，把這種在內(nèi)存中或者磁盤上進(jìn)行排序的方式統(tǒng)稱為文件排序（英文名：filesort）。

但是如果ORDER BY子句里使用到了我們的索引列，就有可能省去在內(nèi)存或文件中排序的步驟，比如下邊這個(gè)簡單的查詢語句：

Copy
SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;

因?yàn)檫@個(gè)B+樹索引本身就是按照上述規(guī)則排好序的，所以直接從索引中提取數(shù)據(jù)，然后進(jìn)行回表操作取出該索引中不包含的列就好了。

使用聯(lián)合索引進(jìn)行排序注意事項(xiàng)
ORDER BY的子句后邊的列的順序也必須按照索引列的順序給出，如果給出ORDER BY phone_number, birthday, name的順序，那也是用不了B+樹索引。

不可以使用索引進(jìn)行排序的幾種情況

1.ASC、DESC混用
對于使用聯(lián)合索引進(jìn)行排序的場景，我們要求各個(gè)排序列的排序順序是一致的，也就是要么各個(gè)列都是ASC規(guī)則排序，要么都是DESC規(guī)則排序。

idx_name_birthday_phone_number聯(lián)合索引中記錄的結(jié)構(gòu)：

• 先按照記錄的name列的值進(jìn)行升序排列。

• 如果記錄的name列的值相同，再按照birthday列的值進(jìn)行升序排列。

• 如果記錄的birthday列的值相同，再按照phone_number列的值進(jìn)行升序排列。

2.WHERE子句中出現(xiàn)非排序使用到的索引列
如果WHERE子句中出現(xiàn)了非排序使用到的索引列，那么排序依然是使用不到索引的，比方說這樣：

Copy
SELECT * FROM person_info WHERE country = ‘China’ ORDER BY name LIMIT 10;

這個(gè)查詢只能先把符合搜索條件country = 'China'的記錄提取出來后再進(jìn)行排序，是使用不到索引。

3.排序列包含非同一個(gè)索引的列
有時(shí)候用來排序的多個(gè)列不是一個(gè)索引里的，這種情況也不能使用索引進(jìn)行排序，比方說：

Copy
SELECT * FROM person_info ORDER BY name, country LIMIT 10;

4.排序列使用了復(fù)雜的表達(dá)式
要想使用索引進(jìn)行排序操作，必須保證索引列是以單獨(dú)列的形式出現(xiàn)，而不是修飾過的形式，比方說這樣：

Copy
SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;

回表的代價(jià)

還是用idx_name_birthday_phone_number索引為例，看下邊這個(gè)查詢：

Copy
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

在使用idx_name_birthday_phone_number索引進(jìn)行查詢時(shí)大致可以分為這兩個(gè)步驟：

1. 從索引idx_name_birthday_phone_number對應(yīng)的B+樹中取出name值在Asa～Barlow之間的用戶記錄。

2. 由于索引idx_name_birthday_phone_number對應(yīng)的B+樹用戶記錄中只包含name、birthday、phone_number、id這4個(gè)字段，而查詢列表是*，意味著要查詢表中所有字段，也就是還要包括country字段。這時(shí)需要把從上一步中獲取到的每一條記錄的id字段都到聚簇索引對應(yīng)的B+樹中找到完整的用戶記錄，也就是我們通常所說的回表，然后把完整的用戶記錄返回給查詢用戶。

讀取索引idx_name_birthday_phone_number數(shù)據(jù)中，在Asa～Barlow之間的記錄在磁盤中的存儲是相連的，集中分布在一個(gè)或幾個(gè)數(shù)據(jù)頁中，我們可以很快的把這些連著的記錄從磁盤中讀出來，這種讀取方式我們也可以稱為順序I/O。

而獲取到的記錄的id字段的值可能并不相連，而在聚簇索引中記錄是根據(jù)id（也就是主鍵）的順序排列的，所以根據(jù)這些并不連續(xù)的id值到聚簇索引中訪問完整的用戶記錄可能分布在不同的數(shù)據(jù)頁中，這樣讀取完整的用戶記錄可能要訪問更多的數(shù)據(jù)頁，這種讀取方式我們也可以稱為隨機(jī)I/O。

所以這個(gè)使用索引idx_name_birthday_phone_number的查詢有這么兩個(gè)特點(diǎn)：

• 會使用到兩個(gè)B+樹索引，一個(gè)二級索引，一個(gè)聚簇索引。

• 訪問二級索引使用順序I/O，訪問聚簇索引使用隨機(jī)I/O。

需要回表的記錄越多，使用二級索引的性能就越低，甚至讓某些查詢寧愿使用全表掃描也不使用二級索引。比方說name值在Asa～Barlow之間的用戶記錄數(shù)量占全部記錄數(shù)量90%以上，那么如果使用idx_name_birthday_phone_number索引的話，有90%多的id值需要回表，這不是吃力不討好么，還不如直接去掃描聚簇索引（也就是全表掃描）。

查詢優(yōu)化器做的工作，查詢優(yōu)化器會事先對表中的記錄計(jì)算一些統(tǒng)計(jì)數(shù)據(jù)，然后再利用這些統(tǒng)計(jì)數(shù)據(jù)根據(jù)查詢的條件來計(jì)算一下需要回表的記錄數(shù)，需要回表的記錄數(shù)越多，就越傾向于使用全表掃描，反之傾向于使用二級索引 + 回表的方式。

比方說上邊的查詢可以改寫成這樣：

Copy
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' LIMIT 10;

添加了LIMIT 10的查詢更容易讓優(yōu)化器采用二級索引 + 回表的方式進(jìn)行查詢。

覆蓋索引

為了徹底告別回表操作帶來的性能損耗，我們建議：最好在查詢列表里只包含索引列。

Copy
SELECT name, birthday, phone_number FROM person_info WHERE name > 'Asa' AND name < 'Barlow'

因?yàn)槲覀冎徊樵僴ame, birthday, phone_number這三個(gè)索引列的值，所以在通過idx_name_birthday_phone_number索引得到結(jié)果后就不必到聚簇索引中再查找記錄的剩余列，也就是country列的值了，這樣就省去了回表操作帶來的性能損耗。

我們把這種只需要用到索引的查詢方式稱為索引覆蓋。

我們很不鼓勵(lì)用*號作為查詢列表，最好把我們需要查詢的列依次標(biāo)明。

如何挑選索引

只為用于搜索、排序或分組的列創(chuàng)建索引

也就是說，只為出現(xiàn)在WHERE子句中的列、連接子句中的連接列，或者出現(xiàn)在ORDER BY或GROUP BY子句中的列創(chuàng)建索引。而出現(xiàn)在查詢列表中的列就沒必要建立索引了

Copy
SELECT birthday, country FROM person_name WHERE name = 'Ashburn';

考慮列的基數(shù)

記錄行數(shù)一定的情況下，列的基數(shù)越大，該列中的值越分散，列的基數(shù)越小，該列中的值越集中。

假設(shè)某個(gè)列的基數(shù)為1，也就是所有記錄在該列中的值都一樣，那為該列建立索引是沒有用的，因?yàn)樗兄刀家粯泳蜔o法排序，無法進(jìn)行快速查找了。

而且如果某個(gè)建立了二級索引的列的重復(fù)值特別多，那么使用這個(gè)二級索引查出的記錄還可能要做回表操作，這樣性能損耗就更大了。

索引列的類型盡量小

如果我們想要對某個(gè)整數(shù)列建立索引的話，在表示的整數(shù)范圍允許的情況下，盡量讓索引列使用較小的類型。
因?yàn)椋?/p>

• 數(shù)據(jù)類型越小，在查詢時(shí)進(jìn)行的比較操作越快（這是CPU層次的東東）

• 數(shù)據(jù)類型越小，索引占用的存儲空間就越少，在一個(gè)數(shù)據(jù)頁內(nèi)就可以放下更多的記錄，從而減少磁盤I/O帶來的性能損耗，也就意味著可以把更多的數(shù)據(jù)頁緩存在內(nèi)存中，從而加快讀寫效率。

因?yàn)椴粌H是聚簇索引中會存儲主鍵值，其他所有的二級索引的節(jié)點(diǎn)處都會存儲一份記錄的主鍵值，如果主鍵適用更小的數(shù)據(jù)類型，也就意味著節(jié)省更多的存儲空間和更高效的I/O。

讓索引列在比較表達(dá)式中單獨(dú)出現(xiàn)

如：

1. WHERE my_col * 2 < 4

2. WHERE my_col < 4/2

第1個(gè)WHERE子句中my_col列并不是以單獨(dú)列的形式出現(xiàn)的，而是以my_col * 2這樣的表達(dá)式的形式出現(xiàn)的，存儲引擎會依次遍歷所有的記錄，計(jì)算這個(gè)表達(dá)式的值是不是小于4。

如果索引列在比較表達(dá)式中不是以單獨(dú)列的形式出現(xiàn)，而是以某個(gè)表達(dá)式，或者函數(shù)調(diào)用形式出現(xiàn)的話，是用不到索引的。

主鍵插入順序

對于一個(gè)使用InnoDB存儲引擎的表來說，在我們沒有顯式的創(chuàng)建索引時(shí)，表中的數(shù)據(jù)實(shí)際上都是存儲在聚簇索引的葉子節(jié)點(diǎn)的。而記錄又是存儲在數(shù)據(jù)頁中的，數(shù)據(jù)頁和記錄又是按照記錄主鍵值從小到大的順序進(jìn)行排序，所以如果我們插入的記錄的主鍵值是依次增大的話，那我們每插滿一個(gè)數(shù)據(jù)頁就換到下一個(gè)數(shù)據(jù)頁繼續(xù)插。

如果我們插入的主鍵值忽大忽小的話，就會需要頁分裂和記錄移位，意味著：性能損耗！所以如果我們想盡量避免這樣無謂的性能損耗，最好讓插入的記錄的主鍵值依次遞增，這樣就不會發(fā)生這樣的性能損耗了。

所以我們建議：讓主鍵具有AUTO_INCREMENT，讓存儲引擎自己為表生成主鍵，而不是我們手動插入。

歡迎點(diǎn)贊+收藏+轉(zhuǎn)發(fā)朋友圈素質(zhì)三連

文章不錯(cuò)？點(diǎn)個(gè)【在看】吧！??