圖文並茂地講解Mysql索引(index)

2022-08-03 14:04:44

前言

本篇文章相對來說篇幅較長，不是一會半會能看完的，建議您收藏起來慢慢看，關於索引的相關知識基本上都記錄全了，通過這一篇文章足以讓您的Mysql知識更上一層樓！

1. 索引概述

1.1 什麼是索引？

索引（index）是幫助MySQL高效獲取資料的資料結構(有序)。在資料之外，資料庫系統還維護著滿足特定查詢演演算法的資料結構，這些資料結構以某種方式參照（指向）資料，這樣就可以在這些資料結構上實現高階查詢演演算法，這種資料結構就是索引。

想要學習好索引，那麼就一定要掌握mysql的資料結構，其實在一提到資料結構，對於基礎較差的來說，有時候是非常頭疼的，不過在這裡大家完全不用擔心，接下來也會重點講解資料結構，儘量會以白話文的形式敘述每一個資料結構！！！

1.2 使用索引和不使用索引的區別

在這裡我們主要演示不使用索引和使用索引的區別到底有多大。

表結構及其資料如下：

假如我們要執行的SQL語句為： select * from user where age = 45;

（1）無索引情況

在無索引情況下，就需要從第一行開始掃描，一直掃描到最後一行，我們稱之為全表掃描，效能很低。可能有的人該說了，明明在id為7的資料已經找到age為45的資料，為什麼還是全表掃描呢？

因為對於mysql當中他並不知道後面是否還存在age為45的資料，所以他會不落下任何一條資料！

（2）有索引情況

如果我們針對於這張表的age欄位建立了索引，假設索引結構就是二元樹，那麼也就意味著，會對age這個欄位建立一個二元樹的索引結構。而這個二元樹當中每個節點儲存了真正資料的位置，我們只要在樹當中找到了對應的age就意味著找到了真正的資料！

如下圖：當查詢age為45的時候，這時候會從根節點開始判斷，根節點為36，比36大所以開始走右邊的節點，光這一下子直接排除掉樹的左邊資料，然後又進行判斷比48小，這時候走左邊節點，然後就找到了，只需要掃描三次就可以找到資料了，極大的提高的查詢的效率。

不管是二元樹還是B+樹，一定都是有順序的，他都是在新增資料的時候，根據資料的大小進行了排序然後分叉。也正因為如此，所以提高了查詢速度！

備註：這裡我們只是假設索引的結構是二元樹，介紹一下索引的大概原理，只是一個示意圖，並不是索引的真實結構，索引的真實結構，後面會詳細介紹。

1.3 索引的特點

降低資料庫的IO，什麼是IO？

IO就是所謂的流，流又分為了讀和寫，當我們想要從檔案當中找資料就需要讀，當需要修改檔案的時候就需要寫，Mysql最終儲存的資料都是在磁碟檔案當中，那麼我們想要找一條資料，怎麼辦呢？

先想想我們現實當中想要在一個檔案找有沒有哪個資料是怎麼找的呢，直接開啟檔案，然後全域性搜尋，假如檔案比較大的話，搜尋也會有點卡頓。mysql他跟我們可不一樣，我們那屬於是人家windows系統給我們提供了這種便捷，我們可以直接開啟檔案，然後進行搜尋。

mysql假如是全表掃描，首先需要從資料檔案當中將這張表的資料給全部讀取到記憶體，然後再進行判斷哪個資料是符合條件的。其中這也考驗到了我們電腦的讀的能力，當然越高設定的電腦讀取速度越快。

假如加了索引，我們只需要將索引給讀取出來，因為索引他指向了資料在檔案上的地址。所以只需要找到對應資料的索引，然後通過索引獲取到資料的位置，再從資料檔案當中將這條資料給讀取出來即可，也因此降低了IO成本。

如果資料集都讀取到記憶體，假如電腦記憶體只有16G，而這張表有200G，一旦全表掃描，電腦豈不是直接掛掉了？

實際上，伺服器端並不需要儲存一個完整的結果集。取資料和發資料的流程是這樣的：

獲取一行，寫到 net_buffer 中。這塊記憶體的大小是由引數 net_buffer_length 定義的，預設是 16k。
重複獲取行，直到 net_buffer 寫滿，呼叫網路介面發出去。
如果傳送成功，就清空 net_buffer，然後繼續取下一行，並寫入 net_buffer。
如果傳送函數返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地網路棧（socket send buffer）寫滿了，進入等待。直到網路棧重新可寫，再繼續傳送。

所以我們在使用過程，基本上不可能會因為mysql查詢資料而導致伺服器記憶體爆滿，mysql主要是佔用我們伺服器的IO。

2. 索引結構

2.1 概述

MySQL的索引是在儲存引擎層實現的，不同的儲存引擎有不同的索引結構，主要包含以下幾種：

上述是MySQL中所支援的所有的索引結構，接下來，我們再來看看不同的儲存引擎對於索引結構的支援情況。

注意：實際開發當中會重點使用B+Tree，所以本篇我們也會重點講解B+Tree的儲存結構！我們平常所說的索引，如果沒有特別指明，都是指B+Tree結構組織的索引。

2.2 二元樹

假如說MySQL的索引結構採用二元樹的資料結構，比較理想的結構如下：

如果主鍵是順序插入的，則會形成一個單向連結串列，結構如下：

所謂的順序就是恰好每次插入的都比上個節點小，或者大，這樣就會形成一個連結串列

所以，如果選擇二元樹作為索引結構，會存在以下缺點：

順序插入時，會形成一個連結串列，查詢效能大大降低。
巨量資料量情況下，層級較深，檢索速度慢。

此時大家可能會想到，我們可以選擇紅黑樹，紅黑樹是一顆自平衡二元樹，那這樣即使是順序插入資料，最終形成的資料結構也是一顆平衡的二元樹,結構如下:

但是，即使如此，由於紅黑樹也是一顆二元樹，所以也會存在一個缺點：

巨量資料量情況下，層級較深，檢索速度慢。

所以，在MySQL的索引結構中，並沒有選擇二元樹或者紅黑樹，而選擇的是B+Tree，那麼什麼是B+Tree呢？在詳解B+Tree之前，先來介紹一個B-Tree。

2.3 B-Tree

在說B+Tree之前，我們先了解一下B-Tree，B-Tree又被稱之為B樹，而B+Tree是B-Tree的變種，B樹是一種多叉路平衡查詢樹，相對於二元樹，B樹每個節點可以有多個分支，即多叉。

以一顆最大度數（max-degree）為5(5階)的b-tree為例，那這個B樹每個節點最多儲存4個key，5個指標，指標永遠比key最多多1個：

知識小貼士: 樹的度數指的是一個節點的子節點個數。

我們可以通過一個資料結構視覺化的網站來簡單演示一下。https://www.cs.usfca.edu/~galles/visualization/BTree.html

插入一組資料： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 。然後觀察一些資料插入過程中，節點的變化情況。

插入資料過程一：由於設定的為五階，五階最多儲存4個key，5個指標，一旦節點儲存的key數量到達5，就會裂變。

插入資料過程二：直接進行了裂變，中間元素向上分裂

插入資料過程三：

插入資料過程四：這時候會發現556放到了右邊節點的中間位置，因為B-TREE是有序的

如下是最終結果，後面的我就不再演示了，強烈建議大家自己去網站插入看一下，這樣可以更好的熟悉是資料結構！

B-Tree特點：

5階的B樹，每一個節點最多儲存4個key，對應5個指標。
一旦節點儲存的key數量到達5，就會裂變，中間元素向上分裂。
在B樹中，非葉子節點和葉子節點都會存放資料。

2.4 B+Tree

B+Tree是B-Tree的變種，我們以一顆最大度數（max-degree）為4（4階）的b+tree為例，來看一下其結構示意圖：

我們可以看到，兩部分：

綠色框框起來的部分，是索引部分，僅僅起到索引資料的作用，不儲存資料。
紅色框框起來的部分，是資料儲存部分，在其葉子節點中要儲存具體的資料。

我們可以通過一個資料結構視覺化的網站來簡單演示一下。
https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

插入一組資料： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 。然後觀察一些資料插入過程中，節點的變化情況。

如下是最終插入的結果展示：

最終我們看到，B+Tree 與 B-Tree相比，主要有以下三點區別：

所有的資料都會出現在葉子節點。葉子節點形成一個單向連結串列。非葉子節點僅僅起到索引資料作用，具體的資料都是在葉子節點存放的。

上述我們所看到的結構是標準的B+Tree的資料結構，接下來，我們再來看看MySQL中優化之後的B+Tree。

所有的資料都會出現在葉子節點。
葉子節點形成一個單向連結串列。
非葉子節點僅僅起到索引資料作用，具體的資料都是在葉子節點存放的。

mysql當中一頁代表了B+TREE資料結構當中的一個葉子節點，並且一頁固定大小為16kb。

總結：mysql的B+Tree資料結構，就是在原來的B+Tree結構基礎上，將葉子節點的單向連結串列改為了雙向連結串列

2.5 Hash

MySQL中除了支援B+Tree索引，還支援一種索引型別—Hash索引。

（1）結構

雜湊索引就是採用一定的hash演演算法，將鍵值換算成新的hash值，對映到對應的槽位上，然後儲存在hash表中。

以下圖為例：將name設定為hash索引，假如想要找對應的name資料，首先會對name進行hash計算得出一個下標值，通過下標值來獲取到對應的資料。

如果兩個(或多個)鍵值，對映到一個相同的槽位上，他們就產生了hash衝突（也稱為hash碰撞），可以通過連結串列來解決。

（2）特點

Hash索引只能用於對等比較(=，in)，不支援範圍查詢（between，>，< ，…）
無法利用索引完成排序操作
查詢效率高，通常(不存在hash衝突的情況)只需要一次檢索就可以了，效率通常要高於B+tree索引

（3）儲存引擎支援

在MySQL中，支援hash索引的是Memory儲存引擎。而InnoDB中具有自適應hash功能，hash索引是InnoDB儲存引擎根據B+Tree索引在指定條件下自動構建的。

思考題：為什麼InnoDB儲存引擎選擇使用B+tree索引結構?

相對於二元樹，層級更少，搜尋效率高；
對於B-tree，無論是葉子節點還是非葉子節點，都會儲存資料，這樣導致一頁中儲存的鍵值減少，指標跟著減少，要同樣儲存大量資料，只能增加樹的高度，導致效能降低；
相對Hash索引，B+tree支援範圍匹配及排序操作；

3.索引分類

3.1 索引分類

在MySQL資料庫，將索引的具體型別主要分為以下幾類：主鍵索引、唯一索引、常規索引、全文索引。

其實索引結構就是索引型別分類，他兩本質就是一個東西，沒有區別！

索引分類，也可以在Navcat使用者端當中，檢視可選擇的索引型別！

3.2 聚集索引&二級索引

而在在InnoDB儲存引擎中，根據索引的儲存形式，又可以分為以下兩種：

聚集索引和二級索引跟我們上面說的索引型別可不是一個東西，切勿混淆了！

聚集索引選取規則:

如果存在主鍵，主鍵索引就是聚集索引。
如果不存在主鍵，將使用第一個唯一（UNIQUE）索引作為聚集索引。
如果表沒有主鍵，或沒有合適的唯一索引，則InnoDB會自動生成一個rowid作為隱藏的聚集索引。

基於B+Tree的索引型別，聚集索引和二級索引的具體結構如下：

假如有一張表，表裡分別有三個欄位id、name、gender，然後id為主鍵，那麼他就是聚集索引，然後我們又給name也新增了一個索引，那麼他就被稱為二級索引。

聚集索引的葉子節點下掛的是這一行的資料。
二級索引的葉子節點下掛的是該欄位值對應的主鍵值。

接下來，我們來分析一下，當我們執行如下的SQL語句時，具體的查詢過程是什麼樣子的。

具體過程如下:

由於是根據name欄位進行查詢，所以先根據name='Arm’到name欄位的二級索引中進行匹配查詢。但是在二級索引中只能查詢到 Arm 對應的主鍵值 10。
由於查詢返回的資料是*，所以此時，還需要根據主鍵值10，到聚集索引中查詢10對應的記錄，最終找到10對應的行row。
最終拿到這一行的資料，直接返回即可。

回表查詢：這種先到二級索引中查詢資料，找到主鍵值，然後再到聚集索引中根據主鍵值，獲取資料的方式，就稱之為回表查詢。

思考題：

（1）以下兩條SQL語句，那個執行效率高? 為什麼?

select * from user where id = 10 ;
select * from user where name = ‘Arm’ ;

備註: id為主鍵，name欄位建立的有索引；

解答： A 語句的執行效能要高於B 語句。

因為A語句直接走聚集索引，直接返回資料。而B語句需要先查詢name欄位的二級索引，然後再查詢聚集索引，也就是需要進行回表查詢。

（2）InnoDB主鍵索引的B+tree高度為多高呢?

關於頁相關知識不是很瞭解的，建議看看mysql儲存引擎篇：https://www.jb51.net/article/257845.htm

一頁最大為16KB，假設一行資料大小為1k，則一頁中可以儲存16行這樣的資料。InnoDB的指標占用6個位元組的空間，主鍵假設為bigint型別，那麼就是佔用位元組數為8。指標的數量是鍵值數量+1。

高度為2：

第一步：非葉子節點是不儲存資料的，那麼我們可以通過已知的現有條件來算出葉子節點可以儲存多少key值。 n * 8 + (n + 1) * 6 = 16*1024 ,算出n約為 1170（這裡的n代表的就是key值，8代表的是假設id為8位元組，n+1代表的是指標，6代表的是指標占用6位元組，16*1024代表的是一頁最大為16kb*1024位元組數）
第二步：有了key值數量後，通過key+1得出指標數量，指標數量就代表著最多有多少頁，因為本身高度為2，然後乘於頁大小，得出最大的空間為18736：1171* 16 = 18736
也就是說，如果樹的高度為2，假設一條資料為1kb，則可以儲存 18000 多條記錄。

高度為3：

1171 * 1171 * 16 = 21939856
有多少指標就代表有多少頁，因為我們要求的是求出最巨量資料量，所以一個指標肯定對應一個頁
也就是說，如果樹的高度為3，則可以儲存 2200w 左右的記錄。

4. 索引語法

建立索引：CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... ) ;

檢視索引：SHOW INDEX FROM table_name ;

刪除索引：DROP INDEX index_name ON table_name ;

案例演示: 先來建立一張表 tb_user，並且查詢測試資料。

CREATE TABLE tb_user (
	id INT PRIMARY KEY auto_increment COMMENT '主鍵',
	NAME VARCHAR ( 50 ) NOT NULL COMMENT '使用者名稱',
	phone VARCHAR ( 11 ) NOT NULL COMMENT '手機號',
	email VARCHAR ( 100 ) COMMENT '郵箱',
	profession VARCHAR ( 11 ) COMMENT '專業',
	age TINYINT UNSIGNED COMMENT '年齡',
	gender CHAR ( 1 ) COMMENT '性別 , 1: 男, 2: 女',
	STATUS CHAR ( 1 ) COMMENT '狀態',
	createtime datetime COMMENT '建立時間' 
) COMMENT '系統使用者表';

INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '呂布', '17799990000', 'lvbu666@163.com', '軟體工程', 23, '1', '6', '2001-02-02 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '曹操', '17799990001', 'caocao666@qq.com', '通訊工程', 33, '1', '0', '2001-03-05 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '趙雲', '17799990002', '17799990@139.com', '英語', 34, '1', '2', '2002-03-02 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '孫悟空', '17799990003', '17799990@sina.com', '工程造價', 54, '1', '0', '2001-07-02 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '花木蘭', '17799990004', '19980729@sina.com', '軟體工程', 23, '2', '1', '2001-04-22 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '大喬', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '露娜', '17799990006', 'luna_love@sina.com', '應用數學', 24, '2', '0', '2001-02-08 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, '1', '5', '2001-05-23 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '項羽', '17799990008', 'xiaoyu666@qq.com', '金屬材料', 43, '1', '0', '2001-09-18 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '白起', '17799990009', 'baiqi666@sina.com', '機械工程及其自動 化', 27, '1', '2', '2001-08-16 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '韓信', '17799990010', 'hanxin520@163.com', '無機非金屬材料工 程', 27, '1', '0', '2001-06-12 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '荊軻', '17799990011', 'jingke123@163.com', '會計', 29, '1', '0', '2001-05-11 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '蘭陵王', '17799990012', 'lanlinwang666@126.com', '工程造價', 44, '1', '1', '2001-04-09 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '狂鐵', '17799990013', 'kuangtie@sina.com', '應用數學', 43, '1', '2', '2001-04-10 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '貂蟬', '17799990014', '84958948374@qq.com', '軟體工程', 40, '2', '3', '2001-02-12 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '妲己', '17799990015', '2783238293@qq.com', '軟體工程', 31, '2', '0', '2001-01-30 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '羋月', '17799990016', 'xiaomin2001@sina.com', '工業經濟', 35, '2', '0', '2000-05-03 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', '1', '2001-08-08 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '狄仁傑', '17799990018', 'jujiamlm8166@163.com', '國際貿易', 30, '1', '0', '2007-03-12 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '安琪拉', '17799990019', 'jdodm1h@126.com', '城市規劃', 51, '2', '0', '2001-08-15 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '典韋', '17799990020', 'ycaunanjian@163.com', '城市規劃', 52, '1', '2', '2000-04-12 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '廉頗', '17799990021', 'lianpo321@126.com', '土木工程', 19, '1', '3', '2002-07-18 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '后羿', '17799990022', 'altycj2000@139.com', '城市園林', 20, '1', '0', '2002-03-10 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '姜子牙', '17799990023', '37483844@qq.com', '工程造價', 29, '1', '4', '2003-05-26 00:00:00' );

資料準備好了之後，接下來，我們就來完成如下需求：

name欄位為姓名欄位，該欄位的值可能會重複，為該欄位建立索引：CREATE INDEX idx_user_name ON tb_user(name);
phone手機號欄位的值，是非空，且唯一的，為該欄位建立唯一索引：CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);
為profession、age、status建立聯合索引（建立的索引同時指定了多個列，我們稱之為聯合索引或者組合索引）：CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);
為email建立合適的索引來提升查詢效率：CREATE INDEX idx_email ON tb_user(email);

完成上述的需求之後，我們再檢視tb_user表的所有的索引資料：

5. SQL效能分析

5.1 SQL執行頻率

MySQL 使用者端連線成功後，通過 show [session|global] status 命令可以提供伺服器狀態資訊。通過如下指令，可以檢視當前資料庫的INSERT、UPDATE、DELETE、SELECT的存取頻次：

-- session 是檢視當前對談 ; 
-- global 是查詢全域性資料 ; 
SHOW GLOBAL STATUS LIKE 'Com_______';

Com_delete: 刪除次數
Com_insert: 插入次數
Com_select: 查詢次數
Com_update: 更新次數

我們可以在當前資料庫再執行幾次查詢操作，然後再次檢視執行頻次，看看 Com_select 引數會不會變化。

通過上述指令，我們可以檢視到當前資料庫到底是以查詢為主，還是以增刪改為主，從而為資料庫優化提供參考依據。如果是以增刪改為主，我們可以考慮不對其進行索引的優化。如果是以查詢為主，那麼就要考慮對資料庫的索引進行優化了。

那麼通過查詢SQL的執行頻次，我們就能夠知道當前資料庫到底是增刪改為主，還是查詢為主。那假如說是以查詢為主，我們又該如何定位針對於那些查詢語句進行優化呢？次數我們可以藉助於慢查詢紀錄檔。

接下來，我們就來介紹一下MySQL中的慢查詢紀錄檔。

5.2 慢查詢紀錄檔

慢查詢紀錄檔記錄了所有執行時間超過指定引數（long_query_time，單位：秒，預設10秒）的所有SQL語句的紀錄檔。

MySQL的慢查詢紀錄檔預設沒有開啟，我們可以檢視一下系統變數 slow_query_log

 show variables like 'slow_query_log';

如果要開啟慢查詢紀錄檔，需要在MySQL的組態檔（如果是linux則是/etc/my.cnf，如果是windows在mysql安裝目錄下的my.ini）中設定如下資訊：注意設定一定要載入組態檔最後面，不然可能會出現問題！

這裡我用的是mysql5.5版本，然後是在windows下設定的以下引數，並沒有出現問題，如果您出現了問題，那麼可能mysql版本之間變數名稱有變化！

ft_min_word_len=1
#慢查詢紀錄檔存放路徑地址
log-slow-queries = D:slow.log    
#慢查詢記錄的時間
long_query_time = 2

測試： 設定完畢之後，重新啟動MySQL伺服器進行測試，檢視慢紀錄檔檔案中記錄的資訊。

mysql快速生成千萬資料：https://www.jb51.net/article/257848.htm

（1）執行如下SQL語句：

 -- 這條SQL執行效率比較高, 執行耗時 0.00sec 
select * from tb_user;
-- 由於t_user表中, 預先存入了700w的記錄, count一次,耗時6s
select count(*) from t_user;

（2）檢查慢查詢紀錄檔

最終我們發現，在慢查詢紀錄檔中，只會記錄執行時間超多我們預設時間（2s）的SQL，執行較快的SQL是不會記錄的。

那這樣，通過慢查詢紀錄檔，就可以定位出執行效率比較低的SQL，從而有針對性的進行優化。

5.3 profile詳情

show profiles 能夠在做SQL優化時幫助我們瞭解時間都耗費到哪裡去了。通過have_profiling引數，能夠看到當前MySQL是否支援profile操作：SELECT @@have_profiling ;

profile查詢出來的資料每個使用者端是相互隔離的。

檢視prifile是否開啟了：select @@profiling;

可以看到，當前MySQL是支援 profile操作的，但是開關是關閉的。可以通過set語句在session/global級別開啟profiling：

SET profiling = 1;

開關已經開啟了，接下來，我們所執行的SQL語句，都會被MySQL記錄，並記錄執行時間消耗到哪兒去了。我們直接執行如下的SQL語句：

select * from tb_user; 
select * from tb_user where id = 1; 
select * from tb_user where name = '白起'; 
select count(*) from t_user;

執行一系列的業務SQL的操作，然後通過如下指令檢視指令的執行耗時：

檢視每一條SQL的耗時基本情況：show profiles;
檢視指定query_id的SQL語句各個階段的耗時情況：show profile for query query_id;
檢視指定query_id的SQL語句CPU的使用情況：show profile cpu for query query_id;

檢視每一條SQL的耗時情況:

檢視指定SQL各個階段的耗時情況 :

5.4 explain

EXPLAIN 或者 DESC命令獲取 MySQL 如何執行 SELECT 語句的資訊，包括在 SELECT 語句執行過程中表如何連線和連線的順序。

語法:

-- 直接在select語句之前加上關鍵字 
explain / desc EXPLAIN SELECT 欄位列表 FROM 表名 WHERE 條件 ;

Explain 執行計劃中各個欄位的含義:

6. 索引使用

6.1 驗證索引效率

在講解索引的使用原則之前，先通過一個簡單的案例，來驗證一下索引，看看是否能夠通過索引來提升資料查詢效能。在演示的時候，我們還是使用之前準備的一張表 t_user, 在這張表中準備了700w的記錄。

這張表中id為主鍵，有主鍵索引，而其他欄位是沒有建立索引的。我們先來查詢其中的一條記錄，看看裡面的欄位情況，執行如下SQL：

可以看到即使有1000w的資料,根據id進行資料查詢,效能依然很快，因為主鍵id是有索引的。那麼接下來，我們再來根據 c_name欄位進行查詢，執行如下SQL：

我們可以看到根據c_name欄位進行查詢，查詢返回了一條資料，結果耗時 3.52 sec，就是因為c_name沒有索引，而造成查詢效率很低。

那麼我們可以針對於c_name欄位，建立一個索引，建立了索引之後，我們再次根據c_name進行查詢，再來看一下查詢耗時情況。
建立索引： create index idx_user_cname on t_user(c_name); 然後再次執行相同的SQL語句，再次檢視SQL的耗時。

我們明顯會看到，c_name欄位建立了索引之後，查詢效能大大提升。建立索引前後，查詢耗時都不是一個數量級的。

6.2 最左字首法則

如果索引了多列（聯合索引），要遵守最左字首法則。最左字首法則指的是查詢從索引的最左列開始，並且不跳過索引中的列。如果跳躍某一列，索引將會部分失效(後面的欄位索引失效)。

以 tb_user 表為例，我們先來檢視一下之前 tb_user 表所建立的索引。

在 tb_user 表中，有一個聯合索引，這個聯合索引涉及到三個欄位，順序分別為：profession，age，status。

對於最左字首法則指的是，查詢時，最左變的列，也就是profession必須存在，否則索引全部失效。而且中間不能跳過某一列，否則該列後面的欄位索引將失效。

（1）第一步我們先演示索引成功的案例，看一下具體的執行計劃：

explain select * from tb_user where profession = '軟體工程' and age = 31 and status = '0';

explain select * from tb_user where profession = '軟體工程' and age = 31;

explain select * from tb_user where profession = '軟體工程';

以上的這三組測試中，我們發現只要聯合索引最左邊的欄位 profession存在，索引就會生效，只不過索引的長度不同。而且由以上三組測試，我們也可以推測出profession欄位索引長度為36、age欄位索引長度為2、status欄位索引長度為4。我們下面主要根據這個長度來判斷組合索引當中哪個索引沒有生效！

注意：如果看不懂explain執行計劃的，一定要看explamin介紹的引數解釋！不同的資料庫編碼以及資料庫版本可能key_len長度也會不一樣，這裡我用的mysql是5.5版本，表用的是utf-8編碼

（2）下面我們進行演示最左邊的profession列沒用到的時候，索引失效的情況：

explain select * from tb_user where age = 31 and status = '0';

explain select * from tb_user where status = '0';

而通過上面的這兩組測試，我們也可以看到索引並未生效，原因是因為不滿足最左字首法則，聯合索引最左邊的列profession不存在。

（3）下面我們進行演示跳過中間的列，索引失效的情況：

explain select * from tb_user where profession = '軟體工程' and status = '0';
上述的SQL查詢時，存在profession欄位，最左邊的列是存在的，索引滿足最左字首法則的基本條件。但是查詢時，跳過了age這個列，所以後面的列索引是不會使用的，也就是索引部分生效，所以索引的長度就是36。

思考題：當執行SQL語句:
explain select * from tb_user where age = 31 and status = '0' and profession = '軟體工程'; 時，是否滿足最左字首法則，走不走上述的聯合索引，索引長度？

可以看到，是完全滿足最左字首法則的，索引長度42，聯合索引是生效的。

注意：最左字首法則中指的最左邊的列，是指在查詢時，聯合索引的最左邊的欄位(即是第一個欄位)必須存在，與我們編寫SQL時，條件編寫的先後順序無關。

6.3 索引失效情況

6.3.1 ><範圍查詢

聯合索引中，出現範圍查詢(>,<)，範圍查詢右側的列索引失效。

explain select * from tb_user where profession = '軟體工程' and age > 30 and status = '0';
當範圍查詢使用> 或 < 時，走聯合索引了，但是索引的長度為38，就說明範圍查詢右邊的status欄位是沒有走索引的。

explain select * from tb_user where profession = '軟體工程' and age >= 30 and status = '0';

當範圍查詢使用>= 或 <= 時，走聯合索引了，但是索引的長度為42，就說明所有的欄位都是走索引
的。

所以，在業務允許的情況下，儘可能的使用類似於 >= 或 <= 這類的範圍查詢，而避免使用 > 或 < 。

6.3.2 索引列運算

不要在索引列上進行運算操作，索引將失效。

在tb_user表中，除了前面介紹的聯合索引之外，還有一個索引，是phone欄位的單列索引。

當根據phone欄位進行等值匹配查詢時, 索引生效。

explain select * from tb_user where phone = '17799990015';

當根據phone欄位進行函數運算操作之後，索引失效。

explain select * from tb_user where substring(phone,10,2) = '15';

6.3.3 字串不加引號

字串型別欄位使用時，不加引號，索引將失效。

接下來，我們通過兩組範例，來看看對於字串型別的欄位，加單引號與不加單引號的區別.

第一組：

explain select * from tb_user where profession = '軟體工程' and age = 31 and status = '0';

explain select * from tb_user where profession = '軟體工程' and age = 31 and status = 0;

第二組：

explain select * from tb_user where phone = '17799990015';
explain select * from tb_user where phone = 17799990015;

6.3.4 模糊查詢

如果僅僅是尾部模糊匹配，索引不會失效。如果是頭部模糊匹配，索引失效。

接下來，我們來看一下這三條SQL語句的執行效果，檢視一下其執行計劃：

由於下面查詢語句中，都是根據profession欄位查詢，符合最左字首法則，聯合索引是可以生效的，我們主要看一下，模糊查詢時，%加在關鍵字之前，和加在關鍵字之後的影響。

explain select * from tb_user where profession like ‘軟體%';
explain select * from tb_user where profession like ‘%工程';
explain select * from tb_user where profession like ‘%工%';

經過上述的測試，我們發現，在like模糊查詢中，在關鍵字後面加%，索引可以生效。而如果在關鍵字前面加了%，索引將會失效。

6.3.5 or連線條件

用or分割開的條件，如果or前的條件中的列有索引，而後面的列中沒有索引，那麼涉及的索引都不會被用到。

explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = ‘17799990017' or age = 23;

由於age沒有索引，所以即使id、phone有索引，索引也會失效。所以需要針對於age也要建立索引。

然後，我們可以對age欄位建立索引：create index idx_user_age on tb_user(age);

建立了索引之後，我們再次執行上述的SQL語句，看看前後執行計劃的變化。

這裡我發現一個問題，我用的mysql5.5版本or不管兩邊是否都有索引，直接都不會走索引，但是又用了mysql 8測試了一下,.當age沒有索引的時候不走索引，當兩邊都有索引的時候確實會走索引。

6.3.6 資料分佈影響

如果MySQL評估使用索引比全表更慢，則不使用索引。

explain select * from tb_user where phone >= '17799990005';
explain select * from tb_user where phone >= '17799990015';

mysql5.5版本執行如下：

mysql8.0版本執行如下：

經過測試我們發現，在mysql8版本當中，相同的SQL語句，只是傳入的欄位值不同，最終的執行計劃完全不一樣，這是為什麼呢？

就是因為MySQL 8 版本在查詢時，會評估使用索引的效率與走全表掃描的效率，如果走全表掃描更快，則放棄索引，走全表掃描。因為索引是用來索引少量資料的，如果通過索引查詢返回大批次的資料，則還不如走全表掃描來的快，此時索引就會失效。

接下來，我們再來看看 is null 與 is not null 操作是否走索引。

explain select * from tb_user where profession is null;
explain select * from tb_user where profession is not null;

接下來，我們做一個操作將profession欄位值全部更新為null：update tb_user set profession = null;

然後，再次執行上述的兩條SQL，檢視SQL語句的執行計劃。

最終我們看到，一模一樣的SQL語句，先後執行了兩次，結果查詢計劃是不一樣的，為什麼會出現這種現象，這是和資料庫的資料分佈有關係。查詢時MySQL會評估，走索引快，還是全表掃描快，如果全表掃描更快，則放棄索引走全表掃描。因此，is null 、is not null是否走索引，得具體情況具體分析，並不是固定的。

6.4 SQL提示

目前tb_user表的索引情況如下:

（1）執行SQL : explain select * from tb_user where profession = '軟體工程';

查詢走了聯合索引。

（2）執行SQL，建立profession的單列索引：create index idx_user_pro on tb_user(profession);
建立單列索引後，再次執行A中的SQL語句，檢視執行計劃，看看到底走哪個索引。

測試結果，我們可以看到，possible_keys中 idx_user_pro_age_sta,idx_user_pro 這兩個索引都可能用到，最終MySQL選擇了idx_user_pro_age_sta索引。這是MySQL自動選擇的結果。

那麼，我們能不能在查詢的時候，自己來指定使用哪個索引呢？答案是肯定的，此時就可以藉助於MySQL的SQL提示來完成。接下來，介紹一下SQL提示。

SQL提示，是優化資料庫的一個重要手段，簡單來說，就是在SQL語句中加入一些人為的提示來達到優化操作的目的。

use index ：建議MySQL使用哪一個索引完成此次查詢（僅僅是建議，mysql內部還會再次進行評估）。
explain select * from tb_user use index(idx_user_pro) where profession = '軟體工程';
範例演示：

ignore index ：忽略指定的索引。
explain select * from tb_user ignore index(idx_user_pro) where profession = '軟體工程';
範例演示：

force index ：強制使用索引。
explain select * from tb_user force index(idx_user_pro) where profession = '軟體工程';
範例演示：

6.5 覆蓋索引

儘量使用覆蓋索引，減少select *。那麼什麼是覆蓋索引呢？覆蓋索引是指查詢使用了索引，並且需要返回的列，在該索引中已經全部能夠找到。說白了就是避免回表查詢

接下來，我們來看一組SQL的執行計劃，看看執行計劃的差別，然後再來具體做一個解析。

explain select id, profession from tb_user where profession = ‘軟體工程' and age = 31 and status = ‘0' ;
explain select id,profession,age, status from tb_user where profession = ‘軟體工程' and age = 31 and status = ‘0' ;
explain select id,profession,age, status, name from tb_user where profession = ‘軟 件工程' and age = 31 and status = ‘0' ;
explain select * from tb_user where profession = ‘軟體工程' and age = 31 and status = ‘0';

mysql5.5執行結果：

mysql8.0執行結果：

從上述的執行計劃我們可以看到，這四條SQL語句的執行計劃前面所有的指標都是一樣的，看不出來差異。但是此時，我們主要關注的是後面的Extra。

在5.5版本當中，前面兩條SQL的結果為 Using where; Using Index ; 而後面兩條SQL的結果為: Using where 。

在8.0版本當中，前面兩條SQL的結果為 Using Index ; 而後面兩條SQL的結果為: NULL 。

因為，在tb_user表中有一個聯合索引 idx_user_pro_age_sta，該索引關聯了三個欄位profession、age、status，而這個索引也是一個二級索引，所以葉子節點下面掛的是這一行的主鍵id。所以當我們查詢返回的資料在 id、profession、age、status 之中，則直接走二級索引直接返回資料了。如果超出這個範圍，就需要拿到主鍵id，再去掃描聚集索引，再獲取額外的資料了，這個過程就是回表。而我們如果一直使用select * 查詢返回所有欄位值，很容易就會造成回表查詢（除非是根據主鍵查詢，此時只會掃描聚集索引）。

表結構及索引示意圖:

id是主鍵，是一個聚集索引。 name欄位建立了普通索引，是一個二級索引（輔助索引）。

執行SQL : select * from tb_user where id = 2;

根據id查詢，直接走聚集索引查詢，一次索引掃描，直接返回資料，效能高。

執行SQL：selet id,name from tb_user where name = 'Arm';

執行SQL：selet id,name,gender from tb_user where name = 'Arm';

由於在name的二級索引中，不包含gender，所以，需要兩次索引掃描，也就是需要回表查詢，效能相對較差一點。

思考題： 一張表, 有四個欄位(id, username, password, status), 由於資料量大, 需要對以下SQL語句進行優化, 該如何進行才是最優方案:

select id,username,password from tb_user where username ='zhangsan';

答案: 針對於 username, password建立聯合索引, sql為:
create index idx_user_name_pass on tb_user(username,password);
這樣可以避免上述的SQL語句，在查詢的過程中，出現回表查詢。

6.6 字首索引

當欄位型別為字串（varchar，text，longtext等）時，有時候需要索引很長的字串，這會讓索引變得很大，查詢時，浪費大量的磁碟IO，影響查詢效率。此時可以只將字串的一部分字首，建立索引，這樣可以大大節約索引空間，從而提高索引效率。

語法：create index idx_xxxx on table_name(column(n)) ;

範例: 為tb_user表的email欄位，建立長度為5的字首索引。

create index idx_email_5 on tb_user(email(5));

可以根據索引的選擇性來決定，而選擇性是指不重複的索引值（基數）和資料表的記錄總數的比值，索引選擇性越高則查詢效率越高，唯一索引的選擇性是1，這是最好的索引選擇性，效能也是最好的。

select count(distinct email) / count(*) from tb_user ; 
select count(distinct substring(email,1,5)) / count(*) from tb_user ;

字首索引的查詢流程

6.7 單列索引與聯合索引

單列索引：即一個索引只包含單個列。
聯合索引：即一個索引包含了多個列。

我們先來看看 tb_user 表中目前的索引情況：在查詢出來的索引中，既有單列索引，又有聯合索引。

接下來，我們來執行一條SQL語句，看看其執行計劃：

通過上述執行計劃我們可以看出來，在and連線的兩個欄位 phone、name上都是有單列索引的，但是最終mysql只會選擇一個索引，也就是說，只能走一個欄位的索引，此時是會回表查詢的。

緊接著，我們再來建立一個phone和name欄位的聯合索引來查詢一下執行計劃。

此時，查詢時，就走了聯合索引，而在聯合索引中包含 phone、name的資訊，在葉子節點下掛的是對應的主鍵id，所以查詢是無需回表查詢的。

在業務場景中，如果存在多個查詢條件，考慮針對於查詢欄位建立索引時，建議建立聯合索引，而非單列索引。

如果查詢使用的是聯合索引，具體的結構示意圖如下：

7. 索引設計原則

針對於資料量較大，且查詢比較頻繁的表建立索引。
針對於常作為查詢條件（where）、排序（order by）、分組（group by）操作的欄位建立索引。
儘量選擇區分度高的列作為索引，儘量建立唯一索引，區分度越高，使用索引的效率越高。
如果是字串型別的欄位，欄位的長度較長，可以針對於欄位的特點，建立字首索引。
儘量使用聯合索引，減少單列索引，查詢時，聯合索引很多時候可以覆蓋索引，節省儲存空間，避免回表，提高查詢效率。
要控制索引的數量，索引並不是多多益善，索引越多，維護索引結構的代價也就越大，會影響增刪改的效率。create unique index idx_user_phone_name on tb_user(phone,name);
如果索引列不能儲存NULL值，請在建立表時使用NOT NULL約束它。當優化器知道每列是否包含NULL值時，它可以更好地確定哪個索引最有效地用於查詢。

本篇要求掌握：

什麼是索引?
索引是個什麼樣的資料結構呢?
為什麼使用索引？
Hash 索引和 B+ 樹索引有什麼區別或者說優劣呢?
什麼是聚簇索引（一個）
說一說索引的底層實現？（一般就是指的B+Tree）
索引有哪些優缺點？
聚簇索引和非聚簇索引的區別（非聚簇索引就是二級索引）
MySQL中有幾種索引型別，可以簡單說說嗎？（主鍵，唯一，常規（常規又分為了多列和單列，多列的一般稱之為組合索引），全文）
覆蓋索引是什麼？（覆蓋索引指的就是查詢的列儘量是索引所覆蓋的列，這樣可以避免回表查）
非聚簇索引一定會回表查詢嗎?（假如恰好select的列是條件當中用到的索引列，是不用回表的）
聯合索引是什麼?為什麼需要注意聯合索引中的順序?（最左字首法則）
建立的索引有沒有被使用到?或者說怎麼才可以知道這條語句執行很慢的原因?（使用explain）
索引什麼情況下會失效?
為什麼Mysql用B+樹做索引而不用B-樹或紅黑樹、二元樹？
索引在什麼情況下遵循最左字首的規則？（組合索引）

總結

到此這篇關於Mysql索引(index)的文章就介紹到這了,更多相關Mysql索引詳解內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！