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

??專欄引言

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

我們總是自欺欺人的覺得，我們通過其他方面來優化就好了阿，遲遲不愿面對MySQL高級，轉而學習一些看似更為"高級"的東西，學Redis，來分擔MySQL的壓力，學MyCat等中間件，實作主從復制，讀寫分離，分庫分表等等，(說的就是melo沒錯了)
?

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

而自己學到的前沿中間件，問的幾乎很少！！自己也只是會用，寫簡歷時只能弱弱寫上"了解"xxx中間件……
?

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

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

??本篇速覽

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

索引到底是什么
索引底層的實作
聚簇索引是什么？二級索引呢？
最左前綴原則
如何設計索引，遵循的原則
索引相關語法

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

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

索引定義

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

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

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

索引優勢

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

索引劣勢

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

但實際上呢，我們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

插入關鍵字案例

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

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

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

上位后又需要分裂

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

相比優勢

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

B+TREE

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

相比優勢

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

MySQL中的B+Tree

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

細心的同學可以看出，這張圖跟我們的二叉查找樹簡圖的一個最大區別是什么？

從二叉查找樹過渡到B樹，有一個顯著的變化就是，一個節點可以存盤多個資料了，相當于一個磁盤塊里邊可以存盤多個資料，大大減少了我們的 IO次數！！

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

二叉查找樹簡圖：

索引原理

BTree索引：

初始化介紹

淺藍色的稱之為一個磁盤塊，可以看到每個磁盤塊包含幾個資料項（深藍色所示）和指標（黃色所示)
如磁盤塊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)，兩棵樹的示例示意圖如下：

從圖中不難看出，根據葉子節點的內容，索引型別分為主鍵索引和非主鍵索引，

主鍵索引

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

輔助索引

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

如下圖：

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

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

如果陳述句是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)，索引中也是按這個欄位來存盤的，類似圖中這樣：

復合索引樹(只存盤索引列和主鍵用于回表)

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

我們執行這樣一條陳述句：

SELECT * FROM tb_seller WHERE name like '小米%' and status ='1' ;

首先我們在復合索引樹上，找到了第一個以小米開頭的name -- 小米1
此時我們不著急回表(回到主鍵索引樹搜索的程序，我們稱為回表)，而是先在復合索引樹判斷status是否=1，此時status=0，我們直接就不回表了，直接繼續找下一個以小米開頭的name

找到第二個-- 小米2，判斷status=1，則根據id=2去主鍵索引樹上找，得到所有的資料

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

最左前綴原則

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

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

出現跳躍的情況

直接第一層name都不走，當然都失效

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

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

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

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

關于這個查詢優化器，后續文章我們也會談談MySQL的邏輯架構與存盤引擎

??索引設計原則

針對表

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

針對欄位

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

??其他原則

最好用唯一索引，區分度越高，使用索引的效率越高
不是越多越好，維護也需要時間和空間代價，建議單張表索引不超過 5 個

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

比如：

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

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

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

?

使用短索引,索引創建之后也是使用硬碟來存盤的，因此提升索引訪問的I/O效率，也可以提升總體的訪問效率，假如構成索引的欄位總長度比較短，那么在給定大小的存盤塊內可以存盤更多的索引值，相應的可以有效的提升MySQL訪問索引的I/O效率，
利用最左前綴，比如有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查詢

我們有兩種方案：

建立聯合索引(name,age)，建立單列索引：age索引，
建立聯合索引(age,name)，建立單列索引：name索引，

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

何時建立索引

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

何時達咩索引

表中資料量很少
經常改動的表
頻繁更新的欄位
資料重復且分布均勻的表欄位（比如包含了很多重復資料，那此時多叉樹的二分查找，其實用處不大，可以理解為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：在資料檔案中讀下一行的請求數，如果你正進行大量的表掃描，該值較高，通常說明你的表索引不正確或寫入的查詢沒有利用索引，

??總結

索引簡單來說就是一個排好序的資料結構，可以方便我們檢索資料，而不需要盲目的進行全表掃描，
- 索引底層有很多種實作結構，這篇主要只是講解了BTREE索引，如果對樹這一資料結構還不太熟悉的小伙伴，可以關注我后續資料結構專欄，會整理關于普通樹，二叉樹，二叉排序樹的文章，
索引分類：
1. 主鍵索引
2. 輔助索引

這里我們還擴展了索引下推，是一個十分重要的知識點，需要仔細回味，

索引的相關設計原則，索引雖好，但也不可貪杯，不能為了用索引而建索引，
索引的相關語法，很容易上手的，
查看索引的使用情況，

??下篇預告

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

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

??參考文獻

掘金：https://juejin.cn/post/6844903967365791752
思否：https://segmentfault.com/a/1190000020416577
MySQL45講
黑馬MySQL高級篇

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

??友鏈

??我的一年后臺練習生涯
聊聊Java
分布式開發實戰
Redis入門與實戰
資料結構與演算法

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/520712.html

標籤：MySQL

上一篇：簡明的binlog event決議

下一篇：約束

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

??專欄引言

??本篇速覽

索引定義

索引優勢

索引劣勢

索引結構

BTREE

插入關鍵字案例

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

上位后又需要分裂

相比優勢

B+TREE

相比優勢

MySQL中的B+Tree

索引原理

BTree索引：

初始化介紹

查找程序

??索引分類

主鍵索引

輔助索引

??擴展--索引下推

最左前綴原則

??索引設計原則

針對表

針對欄位

??其他原則

?舉個栗子

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

考慮空間->短索引

何時建立索引

何時達咩索引

索引相關語法

創建索引

查找索引

洗掉索引

變更索引

查看索引使用情況

??總結

??下篇預告

??參考文獻

??友鏈