可能面試官都沒想到：我的MySQL索引底層技術竟會如此扎實！

前言

不知道你在面試的時候有沒有被問到MySQL的索引？作為互聯網大廠面試題的常客，與平時CRUD增刪改查不同，今天我們來聊聊底層的技術！

同時我也在B站上講過相關MySQL視頻，視頻傳送門：MYSQL底層原理教程全集

同時也準備好了面試題的相關資料：
資料領取方式：點擊這里【暗號：CSDN】

區域性原理

在InnoDB中，資料會存盤到磁盤上，在真正處理資料時需要先將資料加載到記憶體，表中讀取某些記錄時，
InnoDB存盤引擎不需要一條一條的把記錄從磁盤上讀出來，InnoDB采取的方式是：將資料劃分為若干個頁，以 頁作為磁盤和記憶體之間互動的基本單位，InnoDB中頁的大小一般為 16 KB，也就是說，當需要從磁盤中讀資料時每一次最少將從磁盤中讀取16KB的內容到記憶體中，每一次最少也會把記憶體中的16KB內容寫到磁盤中，

INNODB資料頁結構

頁是InnoDB管理存盤空間的基本單位，一個頁的大小默認是16KB，

SHOW GLOBAL STATUS like 'Innodb_page_size';

頁結構：

INNODB行格式

一行記錄可以以不同的格式存在InnoDB中，行格式分別是Compact、Redundant、Dynamic和Compressed行格式，
我們可以在創建或修改表的陳述句中指定行格式：

CREATE TABLE 表名 (列的資訊) ROW_FORMAT=行格式名稱
ALTER TABLE 表名 ROW_FORMAT=行格式名稱

COMPACT行格式

記錄的額外資訊

這部分資訊是服務器為了描述這條記錄而不得不額外添加的一些資訊，這些額外資訊分為3類，分別是：

• 變長欄位長度串列
• NULL值串列
• 記錄頭資訊

變長欄位長度串列

MySQL支持一些變長的資料型別，比如VARCHAR(M)、VARBINARY(M)、TEXT型別，BLOB型別，這些資料型別 修飾列稱為變長欄位，變長欄位中存盤多少位元組的資料不是固定的，所以我們在存盤真實資料的時候需要順便把這些資料占用的位元組數也存起來，在Compact行格式中，把所有變長欄位的真實資料占用的位元組長度都存放在記錄的開頭部位，從而形成一個變長欄位長度串列，

CHAR是一種固定長度的型別，VARCHAR則是一種可變長度的型別， VARCHAR(M)，M代表最大能存多少個字符，(
MySQL5.0.3以前是位元組，以后就是字符)

NTLL值串列

Compact行格式會把可以為NULL的列統一管理起來，存一個標記為在NULL值串列中，如果表中沒有允許存盤
NULL 的列，則 NULL值串列也不存在了，

• 二進制位的值為1時，代表該列的值為NULL，

• 二進制位的值為0時，代表該列的值不為NULL，

記錄頭資訊

除了變長欄位長度串列、NULL值串列之外，還有一個用于描述記錄的記錄頭資訊，它是由固定的5個位元組組成，
5個位元組也就是40個二進制位，不同的位代表不同的意思，如圖：

記錄的真實資料

實際上這幾個列的真正名稱其實是：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR，

一個表沒有手動定義主鍵，則會選取一個Unique鍵作為主鍵，如果連Unique鍵都沒有定義的話，則會為表默認添加一個名為row_id的隱藏列作為主鍵，所以row_id是在沒有自定義主鍵以及Unique鍵的情況下才會存在的，

行溢位資料

VARCHAR(M)型別的列最多可以占用65535個位元組，其中的M代表該型別最多存盤的字符數量，如果我們使用
ascii字符集的話，一個字符就代表一個位元組，我們看看VARCHAR(65535)是否可用：

mysql> CREATE TABLE varchar_size_demo(
-> c VARCHAR(65535)
-> ) CHARSET=ascii ROW_FORMAT=Compact;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type,
not counting BLOBs, is 65535. This includes storage overhead, check the manual. You
have to change some columns to TEXT or BLOBs
mysql>

報錯資訊表達的意思是：MySQL對一條記錄占用的最大存盤空間是有限制的，除BLOB或者TEXT型別的列之外， 其他所有的列（不包括隱藏列和記錄頭資訊）占用的位元組長度加起來不能超過65535個位元組，這個65535個位元組 除了列本身的資料之外，還包括一些其他的資料，比如說我們為了存盤一個VARCHAR(M)型別的列，其實需要占用3部分存盤空間：

1. 真實資料
2. 變長欄位真實資料的長度
3. NULL值標識

如果該VARCHAR型別的列沒有NOT NULL屬性，那最多只能存盤65532個位元組的資料，因為變長欄位的長度占用
2個位元組，NULL值標識需要占用1個位元組，

mysql> CREATE TABLE varchar_size_demo(
-> c VARCHAR(65532)
-> ) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.02 sec)

CREATE TABLE varchar_size_demo(
c VARCHAR(65533) not null
) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.02 sec)

記錄中的資料太多產生的溢位

一個頁的大小一般是16KB，也就是16384位元組，而一個VARCHAR(M)型別的列就最多可以存盤65533個位元組，這樣就可能出現一個頁存放不了一條記錄，
在Compact和Reduntant行格式中，對于占用存盤空間非常大的列，在記錄的真實資料處只會存盤該列的一部分 資料，把剩余的資料分散存盤在幾個其他的頁中，然后記錄的真實資料處用20個位元組存盤指向這些頁的地址（當然這20個位元組中還包括這些分散在其他頁面中的資料的占用的位元組數），從而可以找到剩余資料所在的頁，

Dynamic和Compressed行格式

這兩種行格式類似于COMPACT行格式，只不過在處理行溢位資料時有點兒分歧，它們不會在記錄的真實資料處存盤一部分資料，而是把所有的資料都存盤到其他頁面中，只在記錄的真實資料處存盤其他頁面的地址，另外，
Compressed行格式會采用壓縮演算法對頁面進行壓縮，

索引

索引的產生程序可以看下我講過的教程視頻：MYSQL底層原理教程全集

聚簇索引

聚簇索引的特點：

1. 按主鍵值的大小進行記錄和頁的排序：

   • 資料頁(葉子節點)里的記錄是按照主鍵值從小到大排序的一個單向鏈表，
   • 資料頁(葉子節點)之間也是是按照主鍵值從小到大排序的一個雙向鏈表，
   • B+樹中同一個層的頁目錄也是按照主鍵值從小到大排序的一個雙向鏈表，

2. B+樹的葉子節點存盤的是完整的用戶記錄，就是指這個記錄中存盤了所有列的值（包括隱藏列），

具有這兩種特性的B+樹稱為聚簇索引，所有完整的用戶記錄都存放在這個聚簇索引的葉子節點處，這種聚簇索引并不需要我們在MySQL陳述句中顯式的使用INDEX陳述句去創建，InnoDB存盤引擎會自動的為我們創建聚簇索引， 在InnoDB存盤引擎中，聚簇索引就是資料的存盤方式（所有的用戶記錄都存盤在了葉子節點），也就是所謂的索引即資料，資料即索引，

二級索引（復制索引）

聚簇索引只能在搜索條件是主鍵值時才能發揮作用，因為B+樹中的資料都是按照主鍵進行排序的，當我們想以別的列作為搜索條件時我們可以多建幾棵B+樹，不同的B+樹中的資料采用不同的排序規則，
二級索引與聚簇索引有幾處不同：

1. 按指定的索引列的值來進行排序
2. 葉子節點存盤的不是完整的用戶記錄，而只是索引列+主鍵，
3. 目錄項記錄中不是主鍵+頁號，變成了索引列+頁號，
4. 在對二級索引進行查找資料時，需要根據主鍵值去聚簇索引中再查找一遍完整的用戶記錄，這個程序叫做回表

聯合索引

以多個列的大小為排序規則建立的B+樹稱為聯合索引，本質上也是一個二級索引，

目錄項紀錄的唯一性

我們需要保證在B+樹的同一層內節點的目錄項記錄除頁號這個欄位以外是唯一的，所以對于二級索引的內節點的目錄項記錄的內容實際上是由三個部分構成的：

• 索引列的值
• 主鍵值
• 頁號

B+樹索引總結

1. 每個索引都對應一棵B+樹，用戶記錄都存盤在B+樹的葉子節點，所有目錄記錄都存盤在非葉子節點，
2. InnoDB存盤引擎會自動為主鍵（如果沒有它會自動幫我們添加）建立聚簇索引，聚簇索引的葉子節點包含完整的用戶記錄，
3. 可以為指定的列建立二級索引，二級索引的葉子節點包含的用戶記錄由索引列 + 主鍵組成，所以如果想通過二級索引來查找完整的用戶記錄的話，需要通過回表操作，也就是在通過二級索引找到主鍵值之后再到聚簇索引中查找完整的用戶記錄，
4. B+樹中每層節點都是按照索引列值從小到大的順序排序而組成了雙向鏈表，而且每個頁內的記錄（不論 是用戶記錄還是目錄項記錄）都是按照索引列的值從小到大的順序而形成了一個單鏈表，如果是聯合索引的話，則頁面和記錄先按照聯合索引前邊的列排序，如果該列值相同，再按照聯合索引后邊的列排序，
5. 通過索引查找記錄是從B+樹的根節點開始，一層一層向下搜索，由于每個頁面都按照索引列的值建立了頁目錄，所以在這些頁面中的查找非常快，

以上就是我總結的MySQL索引底層技術了，相信如果你理解透徹之后，一般的面試官是根本難不住你的！最后再為大家分享一波底層架構師資料

資料領取方式：點擊這里【暗號：CSDN】

資料領取方式：點擊這里【暗號：CSDN】