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

語言: CN / TW / HK

theme: qklhk-chocolate highlight: solarized-light


「這是我參與2022首次更文挑戰的第1天,活動詳情檢視:2022首次更文挑戰」。

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

💎專欄引言

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

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

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

而自己學到的前沿中介軟體,問的幾乎很少!!自己也只是會用,寫簡歷時只能弱弱寫上"瞭解"xxx中介軟體……​

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



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

image.png

👉本篇速覽

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

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


本篇篇幅較長,全文近6000字,可以收藏下來慢慢啃,沒事就掏出來翻閱翻閱。 建議通過側邊欄目錄檢索對您有幫助的部分,其中有emoji表情字首屬於重點部分,覺得對您有幫助的話,melo還會持續更進完善本篇文章和MySQL專欄。

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

索引定義

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

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

image.png

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

索引優勢

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

索引劣勢

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

但實際上呢,我們MySQL中並不是用二叉查詢樹來儲存,為何呢?

要知道,二叉查詢樹,此處一個節點只能儲存一條資料,而一個節點呢,在MySQL裡邊又對應一個磁碟塊,這樣我們每次讀取一個磁碟塊,只能獲取一條資料,效率特別的低,所以我們會想到採用B樹這種結構來儲存。

索引結構

索引是在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,提高區間訪問的效能。

細心的同學可以看出,這張圖跟我們的二叉查詢樹簡圖的一個最大區別是什麼?

  • 二叉查詢樹過渡到B樹,有一個顯著的變化就是,一個節點可以儲存多個數據了,相當於一個磁碟塊裡邊可以儲存多個數據,大大減少了我們的 IO次數!!

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上有索引。
這個表的建表語句是: sql 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 也支援了全文索引

🧵擴充套件--索引下推

所謂下推,顧名思義,其實是推遲我們的回表操作,MySQL不會輕而易舉讓我們去回表,因為很浪費。什麼意思呢?來看下邊這個例子。

我們建立了一個複合索引(name,status,address),索引中也是按這個欄位來儲存的,類似圖中這樣:

複合索引樹(只儲存索引列和主鍵用於回表)

| name | status | address | id(主鍵) | | ---- | ------ | ------- | ------ | | 小米1 | 0 | 1 | 1 | | 小米2 | 1 | 1 | 2 |

我們執行這樣一條語句:

sql SELECT name FROM tb_seller WHERE name like '小米%' and password ='1' ;

  1. 首先我們在複合索引樹上,找到了第一個以小米開頭的name -- 小米1
  2. 此時我們不著急回表(回到主鍵索引樹搜尋的過程,我們稱為回表),而是先在複合索引樹判斷status是否=1,此時status=0,我們直接就不回表了,直接繼續找下一個以小米開頭的name
  1. 找到第二個-- 小米2,判斷status=1,則根據id=2去主鍵索引樹上找,得到所有的資料

這種先在自身索引樹上判斷是否滿足其他的where條件,不滿足則直接pass掉,不進行回表的操作,就叫做索引下推。

最左字首原則

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

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

image.png

  1. 出現跳躍的情況
  2. 直接第一層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個索引,可以用複合索引

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

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

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

⏰舉個栗子

假設我們有這麼一個表,id為主鍵,沒有建立索引: sql CREATE TABLE `tuser` ( `id` int(11) NOT NULL, `name` varchar(32) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`), ) ENGINE=InnoDB 如果要在此處建立複合索引,我們要遵循什麼原則呢?

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

  • 比如我們的業務需求裡邊,有如下兩種查詢方式:
  • 根據name查詢
  • 根據name和age查詢


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

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

考慮空間->短索引

  • 比如我們的業務需求裡邊,有以下兩種查詢方式:
  • 根據name查詢
  • 根據age查詢
  • 根據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

```sql 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,可以變成豎屏顯示

sql select index from tbl_name\G;

刪除索引

sql drop INDEX index_name on tbl_name ;

變更索引

```sql 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, 用於全文索引 ```

檢視索引使用情況

```sql 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:在資料檔案中讀下一行的請求數。如果你正進行大量的表掃描,該值較高。通常說明你的表索引不正確或寫入的查詢沒有利用索引。

🎆總結

  1. 索引簡單來說就是一個排好序的資料結構,可以方便我們檢索資料,而不需要盲目的進行全表掃描。

    • 索引底層有很多種實現結構,這篇主要只是講解了BTREE索引,如果對樹這一資料結構還不太熟悉的小夥伴,可以關注我後續資料結構專欄,會整理關於普通樹,二叉樹,二叉排序樹的文章。
  2. 索引分類:

    1. 主鍵索引
    2. 輔助索引

這裡我們還擴充套件了索引下推,是一個十分重要的知識點,需要仔細回味。

  1. 索引的相關設計原則,索引雖好,但也不可貪杯,不能為了用索引而建索引。
  2. 索引的相關語法,很容易上手的。

  3. 檢視索引的使用情況。

💠下篇預告

這篇我們主要講的都是索引的理論知識,還簡單介紹了索引的語法,可以看得出索引用起來其實是不難的,關鍵在於如何設計和優化。

因為在很多情況下,索引其實很容易失效,我們要如何避免,以及如何正確使用索引來進行SQL優化,敬請期待下篇。

🖨參考文獻


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

🧿友鏈