「 MySQL高階篇 」MySQL索引原理，設計原則

Melo~發表於2022-02-04
MySql索引

大家好，我是melo，一名大二後臺練習生，大年初三，我又來充當反內卷第一人了！！！

?專欄引言

MySQL，一個熟悉又陌生的名詞，早在學習Javaweb的時候，我們就用到了MySQL資料庫，在那個階段，MySQL對我們來說似乎只是一個儲存資料的好東西，儲存時一股腦往裡邊塞，查詢時也是盲目的全表查詢(不帶一點點優化)。
​

我們總是自欺欺人的覺得，我們通過其他方面來優化就好了阿，遲遲不願面對MySQL高階，轉而學習一些看似更為"高階"的東西，學Redis，來分擔MySQL的壓力，學MyCat等中介軟體，實現主從複製，讀寫分離，分庫分表等等。(說的就是melo沒錯了)
​

到了準備面試的時候，發現面試題裡邊的MySQL一問三不知~

ed867ed3-8f42-4c94-9d34-de129cff50e7.png
而自己學到的前沿中介軟體，問的幾乎很少！！自己也只是會用，寫簡歷時只能弱弱寫上"瞭解"xxx中介軟體……
​

當然了，學習MySQL高階篇，不單單只是為了面試，實際的專案中，這一塊的優化是十分重要的，體驗過伺服器當機後，只能默默........
image.png
​

從現在開始吧，此時上岸還來得及！！！趁著大二上的寒假，補充補充MySQL高階篇的知識點，從如下幾方面開啟MySQL高階篇之旅

image.png

?本篇速覽

早在MySQL基礎篇，我們就聽說了索引這麼個東西，聽起來是個很高階的東西，但當時只停留在了，索引能夠加快查詢的效率這一階段的認知。這篇將從如下幾點，來帶你逐一攻破ta：

  • 索引到底是什麼
  • 索引底層的實現
  • 聚簇索引是什麼？二級索引呢？
  • 最左字首原則
  • 如何設計索引，遵循的原則
  • 索引相關語法

​

本篇篇幅較長，全文近6000字，可以收藏下來慢慢啃，沒事就掏出來翻閱翻閱，覺得對您有幫助的話，melo還會持續更進完善本篇文章和MySQL專欄

  • 不過就怕等到我更新時，那會您不方便找到我了hhh(高情商求關注?)

索引定義

MySQL官方對索引的定義為：索引（index）是幫助MySQL高效獲取資料的資料結構（有序）。索引是在資料庫表的欄位上新增的，是為了提高查詢效率存在的一種機制。在資料之外，資料庫系統還維護著滿足特定查詢演算法的資料結構，這些資料結構以某種方式引用（指向）資料， 這樣就可以在這些資料結構上實現高階查詢演算法，這種資料結構就是索引。如下面的示意圖所示 :

其實簡單來說，索引就是一個排好序的資料結構

image.png
​

左邊是資料表，一共有兩列七條記錄，最左邊的是資料記錄的實體地址（注意邏輯上相鄰的記錄在磁碟上也並不是一定物理相鄰的）。為了加快Col2的查詢，可以維護一個右邊所示的二叉查詢樹，每個節點分別包含索引鍵值和一個指向對應資料記錄實體地址的指標，這樣就可以運用二叉查詢快速獲取到相應資料。

一般來說索引本身也很大，不可能全部儲存在記憶體中，因此索引往往以索引檔案的形式儲存在磁碟上。索引是資料庫中用來提高效能的最常用的工具。

索引優勢

  • 加快查詢和排序的速率，降低資料庫的IO成本以及CPU的消耗
  • 通過建立唯一性索引，可以保證資料庫表中每一行資料的唯一性。

索引劣勢

  1. 索引實際上也是一張表，儲存了主鍵和索引欄位，並指向實體類的記錄，本身需要佔用空間
  2. 雖然增加了查詢效率，但對於增刪改，每次改動表，還需要更新一下索引
    1. 新增：自然需要在索引樹中新增節點
    2. 刪除：索引樹中指向的記錄可能會失效，意味著這棵索引樹很多節點，都是失效的
    3. 改動：索引樹中節點的指向可能需要改變

索引結構

索引是在MySQL的儲存引擎層中實現的，而不是在伺服器層實現的。所以每種儲存引擎的索引都不一定完全相同，而且也不是所有的引擎都支援所有的索引型別。
​

  • BTREE 索引 ： 最常見的索引型別，大部分索引都支援 B 樹索引。
  • HASH 索引：只有Memory引擎支援 ， 使用場景簡單 。
  • R-tree 索引（空間索引）：空間索引是MyISAM引擎的一個特殊索引型別，主要用於地理空間資料型別，通常使用較少，不做特別介紹。
  • Full-text （全文索引） ：全文索引也是MyISAM的一個特殊索引型別，主要用於全文索引，InnoDB從Mysql5.6版本開始支援全文索引。


MyISAM、InnoDB、Memory三種儲存引擎對各種索引型別的支援

索引 INNODB引擎 MYISAM引擎 MEMORY引擎
BTREE索引 支援 支援 支援
HASH 索引 不支援 不支援 支援
R-tree 索引 不支援 支援 不支援
Full-text 5.6版本之後支援 支援 不支援

我們平常所說的索引，如果沒有特別指明，都是指B+樹（多路搜尋樹，並不一定是二叉的）結構組織的索引。其中聚集索引、複合索引、字首索引、唯一索引預設都是使用 B+tree 索引，統稱為 索引。

BTREE

多路平衡搜尋樹，一棵m階(m叉)BTREE滿足：

  • 每個節點最多m個孩子
    • 孩子個數：ceil(m/2) 到 m
    • 關鍵字個數：ceil(m/2)-1 到 m-1

ceil表示向上取整，ceil(2.3)=3

插入關鍵字案例

image.png

保證不破壞m階B樹的性質

由於3階，最多隻能2個節點，所以一開始26和30在一起，之後再來個85就要開始分裂了，30作為中間上位，26保持，85去到右邊
即：中間位置上位，然後左邊留在舊節點，右邊去到新結點

如圖中的70再插入的時候，70剛好是中間位置上位，然後62保持，85又去分一個新節點出來

image.png

上位後又需要分裂

繼續向上分裂即可，同理的

image.png

相比優勢

相比二叉搜尋樹，高度/深度更低，自然查詢效率更高。

B+TREE

  • B+樹有兩種型別的節點：內部結點（也稱索引結點）和葉子結點。內部節點就是非葉子節點，內部節點不儲存資料，只儲存索引，資料都儲存在葉子節點。
  • 內部結點中的key都按照從小到大的順序排列，對於內部結點中的一個key，左樹中的所有key都小於它，右子樹中的key都大於等於它。葉子結點中的記錄也按照key的大小排列。
  • 每個葉子結點都存有相鄰葉子結點的指標，葉子結點本身依關鍵字的大小自小而大順序連結。
  • 父節點存有右孩子的第一個元素的索引。

​

image.png

相比優勢

  • B+Tree的查詢效率更加穩定。由於B+Tree只有葉子節點儲存key資訊，查詢任何key都要從root走到葉子，所以更穩定。
  • 只需遍歷葉子節點，就可以實現整棵樹的遍歷。

​

MySQL中的B+Tree

MySql索引資料結構對經典的B+Tree進行了優化。在原B+Tree的基礎上，增加一個指向相鄰葉子節點的連結串列指標(整體類似一個雙向連結串列的結構)，就形成了帶有順序指標的B+Tree，提高區間訪問的效能。
​

MySQL中的 B+Tree 索引結構示意圖：
image.png

索引原理

BTree索引：
image.png

初始化介紹


淺藍色的稱之為一個磁碟塊，可以看到每個磁碟塊包含幾個資料項（深藍色所示）和指標（黃色所示)
如磁碟塊1包含資料項17和35，包含指標P1、P2、P3,
P1表示小於17的磁碟塊，P2表示在17和35之間的磁碟塊，P3表示大於35的磁碟塊。

  • 真實的資料存在於葉子節點即3、5、9、10、13、15、28、29、36、60、75、79、90、99。`
  • 非葉子節點不儲存真實的資料，只儲存指引搜尋方向的資料項，如17、35並不真實存在於資料表中。`

查詢過程


如果要查詢資料項29，那麼首先會把磁碟塊1由磁碟載入到記憶體，此時發生一次IO。在記憶體中用二分查詢確定29在17和35之間，鎖定磁碟塊1的P2指標，記憶體時間因為非常短（相比磁碟的IO）可以忽略不計，通過磁碟塊1的P2指標的磁碟地址把磁碟塊3由磁碟載入到記憶體，發生第二次IO，29在26和30之間，鎖定磁碟塊3的P2指標，通過指標載入磁碟塊8到記憶體，發生第三次IO，同時記憶體中通過二分查詢搜尋到29，結束查詢，總計三次IO。


真實的情況是，3層的B+樹可以表示上百萬的資料，如果上百萬的資料查詢只需要三次IO，效能提高將是巨大的，如果沒有索引，每個資料項都要發生一次IO，那麼總共需要百萬次的IO，顯然成本非常非常高。

?索引分類

在InnoDB中，表都是根據主鍵順序以索引的形式存放的，這種儲存方式的表稱為索引組織表。又因為前面我們提到的，InnoDB使用了B+樹索引模型，所以資料都是儲存在B+樹中的。
​

每一個索引在InnoDB裡面對應一棵B+樹。
假設，我們有一個主鍵列為ID的表，表中有欄位k，並且在k上有索引。
這個表的建表語句是：

mysql> create table T( 
  id int primary key, 
  k int not null,  
  name varchar(16), 
  index (k))engine=InnoDB;

表中R1~R5的(ID,k)值分別為(100,1)、(200,2)、(300,3)、(500,5)和(600,6)，兩棵樹的示例示意圖如下：
image.png
從圖中不難看出，根據葉子節點的內容，索引型別分為主鍵索引和非主鍵索引。

主鍵索引

資料表的主鍵列使用的就是主鍵索引，且會預設建立，這也是為什麼，我們還沒學索引的時候，老師經常跟我們說根據主鍵查會快一點，原來主鍵本身就建好了索引。
主鍵索引的葉子節點存的是整行資料。在InnoDB裡，主鍵索引也被稱為聚簇索引（clustered index）。

輔助索引

輔助索引的葉子節點內容是主鍵的值。在InnoDB裡，輔助索引也被稱為二級索引（secondary index）。
​

如下圖：

  • 主鍵索引存放了整行資料
  • 輔助索引只存放了自己本身，以及id主鍵用於回表查詢

​

image.png
​

根據上面的索引結構，我們來討論一個問題：基於主鍵索引和輔助索引的查詢有什麼區別？
​

  • 如果語句是select * from T where ID=500，即主鍵查詢方式，則只需要搜尋ID這棵B+樹；
  • 如果語句是select * from T where k=5，即普通索引查詢方式，則需要先搜尋k索引樹，得到ID的值為500，再到ID索引樹搜尋一次。這個過程稱為回表。


也就是說，基於輔助索引的查詢需要多掃描一棵索引樹。因此，我們在應用中應當儘量使用主鍵查詢。
​

除非說，我們所要查詢的資料，剛好就是我們索引樹上存在的，此時我們稱之為覆蓋索引--即索引列中包含了我們要查詢的所有資料。


同時，二級索引又分為了如下幾種(先簡單略過即可，後續我們再慢慢了解)：
​

  • 唯一索引(Unique Key) ：唯一索引也是一種約束。唯一索引的屬性列不能出現重複的資料，但是允許資料為 NULL，一張表允許建立多個唯一索引。 建立唯一索引的目的大部分時候都是為了該屬性列的資料的唯一性，而不是為了查詢效率。
  • 普通索引(Index) ：普通索引的唯一作用就是為了快速查詢資料，一張表允許建立多個普通索引，並允許資料重複和 NULL。
  • 字首索引(Prefix) ：字首索引只適用於字串型別的資料。字首索引是對文字的前幾個字元建立索引，相比普通索引建立的資料更小， 因為只取前幾個字元。
  • 全文索引(Full Text) ：全文索引主要是為了檢索大文字資料中的關鍵字的資訊，是目前搜尋引擎資料庫使用的一種技術。Mysql5.6 之前只有 MYISAM 引擎支援全文索引，5.6 之後 InnoDB 也支援了全文索引

最左字首原則

所謂最左字首，可以想象成一個爬樓梯的過程，假設我們有一個複合索引：name，status，address，那這個樓梯由低到高依次順序是：name，status，address，最左字首，要求我們不能出現跳躍樓梯的情況，否則會導致我們的索引失效：
​

  1. 按樓梯從低到高，無出現跳躍的情況--此時符合最左字首原則，索引不會失效

image.png

  1. 出現跳躍的情況
  • 直接第一層name都不走，當然都失效

image.png

  • 走了第一層，但是後續直接第三層，只有出現跳躍情況前的不會失效（此處就只有name成功）

image.png

  • 同時，這個順序並不是由我們where中的排列順序決定，比如：
    • where name='小米科技' and status='1' and address='北京市'
    • where status='1' and name='小米科技' and address='北京市'

這兩個儘管where中欄位的順序不一樣，第二個看起來越級了，但實際上效果是一樣的

其實是因為我們MySQL有一個Optimizer（查詢優化器），查詢優化器會將SQL進行優化，選擇最優的查詢計劃來執行。

  • 關於這個查詢優化器，後續文章我們也會談談MySQL的邏輯架構與儲存引擎

?索引設計原則

針對表

  1. 查詢頻次高，且資料量多的表

針對欄位

  1. 最好從where子句的條件中提取，如果where子句中的組合比較多，那麼應當挑選最常用、過濾效果最好的列的組合。

其他原則

  1. 最好用唯一索引，區分度越高，使用索引的效率越高

  2. 不是越多越好，維護也需要時間和空間代價，建議單張表索引不超過 5 個

因為 MySQL 優化器在選擇如何優化查詢時，會根據統一資訊，對每一個可以用到的索引來進行評估，以生成出一個最好的執行計劃，如果同時有很多個索引都可以用於查詢，就會增加 MySQL 優化器生成執行計劃的時間，同樣會降低查詢效能。

比如：

我們建立了三個單列索引，name，status，address

當我們where中根據status和address兩個欄位來查詢時，資料庫只會選擇最優的一個索引，不會所有單列索引都使用。


最優的索引：具體是指所查詢表中，辨識度最高(所佔比例最少)的索引列，比如此處address中有一個辨識度很高的 '西安市'資料；
image.png
​

  1. 使用短索引,索引建立之後也是使用硬碟來儲存的，因此提升索引訪問的I/O效率，也可以提升總體的訪問效率。假如構成索引的欄位總長度比較短，那麼在給定大小的儲存塊內可以儲存更多的索引值，相應的可以有效的提升MySQL訪問索引的I/O效率。

  2. 利用最左字首，比如有N個欄位，我們不一定需要建立N個索引，可以用複合索引

也就是說，我們儘量建立複合索引，而不是單列索引

建立複合索引:
	CREATE INDEX idx_name_email_status ON tb_seller(name,email,status);

就相當於
	對name 建立索引 ;
	對name , email 建立了索引 ;
	對name , email, status 建立了索引 ;

⏰舉個例子

假設我們有這麼一個表，id為主鍵，沒有建立索引：

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB

如果要在此處建立複合索引，我們要遵循什麼原則呢？
​

通過調整順序，可以少維護一個索引

  • 比如我們的業務需求裡邊，有如下兩種查詢方式：
    1. 根據name查詢
    2. 根據name和age查詢


如果我們建立索引（age,name），由於最左字首原則，我們這個索引能實現的是根據age，根據age和name查詢，並不能單純根據name查詢（因為跳躍了），為了實現我們的需求，我們還得再建立一個name索引；
​

而如果我們通過調整順序，改成（name，age），就能實現我們的需求了，無需再維護一個name索引，這就是通過調整順序，可以少維護一個索引。

考慮空間（短索引）

  • 比如我們的業務需求裡邊，有以下兩種查詢方式：
    1. 根據name查詢
    2. 根據age查詢
    3. 根據name和age查詢


我們有兩種方案：

  1. 建立聯合索引(name,age)，建立單列索引：age索引。
  2. 建立聯合索引(age,name)，建立單列索引：name索引。

​

這兩種方案都能實現我們的需求，這個時候我們就要考慮空間了，name欄位是比age欄位大的，顯然方案1所耗費的空間是更小的，所以我們更傾向於方案1。

何時建立索引

  1. where中的查詢欄位
  2. 查詢中與其他表關聯的欄位，比如外來鍵
  3. 排序的欄位
  4. 統計或分組的欄位

何時達咩索引

  1. 表中資料量很少
  2. 經常改動的表
  3. 頻繁更新的欄位
  4. 資料重複且分佈均勻的表欄位（比如包含了很多重複資料，那此時多叉樹的二分查詢，其實用處不大，可以理解為O(logn)退化了）

索引相關語法

建立索引

預設會為主鍵建立索引--primary

CREATE 	[UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name 
[USING  index_type]
ON tbl_name(index_col_name,...)

index_col_name : column_name[(length)][ASC | DESC]

查詢索引

結尾加上\G，可以變成豎屏顯示

select index from tbl_name\G;

刪除索引

drop INDEX index_name on tbl_name ;

變更索引

1). alter  table  tb_name  add  primary  key(column_list); 
	該語句新增一個主鍵，這意味著索引值必須是唯一的，且不能為NULL	
	
2). alter  table  tb_name  add  unique index_name(column_list);
	這條語句建立索引的值必須是唯一的（除了NULL外，NULL可能會出現多次）
	
3). alter  table  tb_name  add  index index_name(column_list);
	新增普通索引， 索引值可以出現多次。
	
4). alter  table  tb_name  add  fulltext  index_name(column_list);
	該語句指定了索引為FULLTEXT， 用於全文索引

檢視索引使用情況

show status like 'Handler_read%';	 -- 檢視當前會話索引使用情況

show global status like 'Handler_read%';	-- 檢視全域性索引使用情況

Handler_read_first：索引中第一條被讀的次數。如果較高，表示伺服器正執行大量全索引掃描（這個值越低越好）。
​

Handler_read_key：如果索引正在工作，這個值代表一個行被索引值讀的次數，如果值越低，表示索引得到的效能改善不高，因為索引不經常使用（這個值越高越好）。
​

Handler_read_next ：按照鍵順序讀下一行的請求數。如果你用範圍約束或如果執行索引掃描來查詢索引列，該值增加。
​

Handler_read_prev：按照鍵順序讀前一行的請求數。該讀方法主要用於優化ORDER BY ... DESC。
​

Handler_read_rnd ：根據固定位置讀一行的請求數。如果你正執行大量查詢並需要對結果進行排序該值較高。你可能使用了大量需要MySQL掃描整個表的查詢或你的連線沒有正確使用鍵。這個值較高，意味著執行效率低，應該建立索引來補救。
​

Handler_read_rnd_next：在資料檔案中讀下一行的請求數。如果你正進行大量的表掃描，該值較高。通常說明你的表索引不正確或寫入的查詢沒有利用索引。

?下篇預告

這篇我們主要講的都是索引的理論知識，還簡單介紹了索引的語法，可以看得出索引用起來其實是不難的，關鍵在於如何設計和優化。
因為在很多情況下，索引其實很容易失效，我們要如何避免，以及如何正確使用索引來進行SQL優化，敬請期待下篇。

?參考文獻

點贊小企鵝.jpg

收藏=白嫖，點贊+關注才是真愛！！！本篇文章如有不對之處，還請在評論區指出，歡迎新增我的微信一起交流：Melo__Jun

?友鏈

相關文章