2.索引

2.1概述

2.1.1 介紹

索引（index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)(有序)杏慰。在數(shù)據(jù)之外干旁，數(shù)據(jù)庫系統(tǒng)還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)魏烫，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)沐悦， 這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實現(xiàn)高級查找算法侥蒙，這種數(shù)據(jù)結(jié)構(gòu)就是索引,個人理解來說,其實就是按索引,將你的數(shù)據(jù)根據(jù)索引,按照B+Tree的數(shù)據(jù)結(jié)構(gòu),將表的數(shù)據(jù)重新組織起來.

2.1.2演示

表結(jié)構(gòu)及其數(shù)據(jù)如下：

image.png

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

1). 無索引情況

image.png

在無索引情況下，就需要從第一行開始掃描印机，一直掃描到最后一行矢腻，我們稱之為 全表掃描，性能很
低
2). 有索引情況
如果我們針對于這張表建立了索引射赛，假設(shè)索引結(jié)構(gòu)就是二叉樹多柑，那么也就意味著，會對age這個字段建
立一個二叉樹的索引結(jié)構(gòu)楣责。

image.png

備注： 這里我們只是假設(shè)索引的結(jié)構(gòu)是二叉樹初嘹，介紹一下索引的大概原理，只是一個示意圖沮趣，并
不是索引的真實結(jié)構(gòu)屯烦，索引的真實結(jié)構(gòu)，后面會記錄房铭。

2.1.3 特點

image.png

2.2索引結(jié)構(gòu)

2.2.1 概述

MySQL的索引是在存儲引擎層實現(xiàn)的驻龟，不同的存儲引擎有不同的索引結(jié)構(gòu)，主要包含以下幾種：

image.png

2.2.2 二叉樹

假如說MySQL的索引結(jié)構(gòu)采用二叉樹的數(shù)據(jù)結(jié)構(gòu)缸匪，比較理想的結(jié)構(gòu)如下：

image.png

但是,如果主鍵是順序插入的翁狐，則會形成一個單向鏈表，結(jié)構(gòu)如下：

image.png

2.大數(shù)據(jù)量情況下窃躲，層級較深计贰，檢索速度慢。
此時大家可能會想到蒂窒，我們可以選擇紅黑樹躁倒，紅黑樹是一顆自平衡二叉樹荞怒，那這樣即使是順序插入數(shù)
據(jù)，最終形成的數(shù)據(jù)結(jié)構(gòu)也是一顆平衡的二叉樹,結(jié)構(gòu)如下:
但是秧秉，即使如此褐桌，由于紅黑樹也是一顆二叉樹，所以也會存在一個缺點：
大數(shù)據(jù)量情況下象迎，層級較深荧嵌，檢索速度慢。
所以砾淌，在MySQL的索引結(jié)構(gòu)中啦撮，并沒有選擇二叉樹或者紅黑樹，而選擇的是B+Tree汪厨，那么什么是
B+Tree呢赃春？在詳解B+Tree之前，先來介紹一個B-Tree劫乱。

2.2.3 B-Tree

B-Tree织中，B樹是一種多叉路衡查找樹，相對于二叉樹衷戈，B樹每個節(jié)點可以有多個分支狭吼，即多叉。
以一顆最大度數(shù)（max-degree）為5(5階)的b-tree為例殖妇，那這個B樹每個節(jié)點最多存儲4個key搏嗡，5
個指針：

image.png

我們可以通過一個數(shù)據(jù)結(jié)構(gòu)可視化的網(wǎng)站來簡單演示一下。 https://www.cs.usfca.edu/~gall
es/visualization/BTree.html

image.png

image.png

2.2.4 B+Tree

B+Tree是B-Tree的變種叉橱，我們以一顆最大度數(shù)（max-degree）為4（4階）的b+tree為例，來看一
下其結(jié)構(gòu)示意圖：

image.png

我們可以看到者蠕，兩部分：
綠色框框起來的部分窃祝，是索引部分，僅僅起到索引數(shù)據(jù)的作用踱侣，不存儲數(shù)據(jù)粪小。
紅色框框起來的部分大磺，是數(shù)據(jù)存儲部分，在其葉子節(jié)點中要存儲具體的數(shù)據(jù)探膊。
我們可以通過一個數(shù)據(jù)結(jié)構(gòu)可視化的網(wǎng)站來簡單演示一下杠愧。 https://www.cs.usfca.edu/~gall
es/visualization/BPlusTree.html

最終我們看到，B+Tree 與 B-Tree相比逞壁，主要有以下三點區(qū)別：
1.所有的數(shù)據(jù)都會出現(xiàn)在葉子節(jié)點流济。
2.葉子節(jié)點形成一個單向鏈表。
3.非葉子節(jié)點僅僅起到索引數(shù)據(jù)作用腌闯，具體的數(shù)據(jù)都是在葉子節(jié)點存放的袭灯。

上述我們所看到的結(jié)構(gòu)是標(biāo)準(zhǔn)的B+Tree的數(shù)據(jù)結(jié)構(gòu)，接下來绑嘹，我們再來看看MySQL中優(yōu)化之后的
B+Tree稽荧。
MySQL索引數(shù)據(jù)結(jié)構(gòu)對經(jīng)典的B+Tree進(jìn)行了優(yōu)化。在原B+Tree的基礎(chǔ)上工腋，增加一個指向相鄰葉子節(jié)點
的鏈表指針姨丈，就形成了帶有順序指針的B+Tree，提高區(qū)間訪問的性能擅腰，利于排序蟋恬。

image.png

2.3 索引分類

2.3.1 索引分類

在MySQL數(shù)據(jù)庫，將索引的具體類型主要分為以下幾類：主鍵索引趁冈、唯一索引歼争、常規(guī)索引、全文索引渗勘。

image.png

2.3.2 聚集索引&二級索引

image.png

聚集索引選取規(guī)則:
1.如果存在主鍵沐绒，主鍵索引就是聚集索引。
2.如果不存在主鍵旺坠，將使用第一個唯一（UNIQUE）索引作為聚集索引乔遮。
3.如果表沒有主鍵，或沒有合適的唯一索引取刃，則InnoDB會自動生成一個rowid作為隱藏的聚集索
引蹋肮。
聚集索引和二級索引的具體結(jié)構(gòu)如下：

image.png

1.聚集索引的葉子節(jié)點下掛的是這一行的數(shù)據(jù) 。
2.二級索引的葉子節(jié)點下掛的是該字段值對應(yīng)的主鍵值璧疗。

接下來坯辩，我們來分析一下，當(dāng)我們執(zhí)行如下的SQL語句時崩侠，具體的查找過程是什么樣子的漆魔。

image.png

具體過程如下:

1. 由于是根據(jù)name字段進(jìn)行查詢，所以先根據(jù)name='Arm'到name字段的二級索引中進(jìn)行匹配查
   找。但是在二級索引中只能查找到 Arm 對應(yīng)的主鍵值 10有送。
2. 由于查詢返回的數(shù)據(jù)是*淌喻，所以此時僧家，還需要根據(jù)主鍵值10雀摘，到聚集索引中查找10對應(yīng)的記錄，最
   終找到10對應(yīng)的行row八拱。
3. 最終拿到這一行的數(shù)據(jù)阵赠，直接返回即可。

這個就是回表查詢

回表查詢： 這種先到二級索引中查找數(shù)據(jù)肌稻，找到主鍵值清蚀，然后再到聚集索引中根據(jù)主鍵值，獲取
數(shù)據(jù)的方式爹谭，就稱之為回表查詢枷邪。

2.4 索引語法

1.創(chuàng)建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... ) ;

2.查看索引

SHOW INDEX FROM table_name ;

3.刪除索引

DROP INDEX index_name ON table_name ;

2.5 SQL性能分析

2.5.1 SQL執(zhí)行頻率

MySQL 客戶端連接成功后，通過 show [session|global] status 命令可以提供服務(wù)器狀態(tài)信
息诺凡。通過如下指令东揣，可以查看當(dāng)前數(shù)據(jù)庫的INSERT、UPDATE腹泌、DELETE嘶卧、SELECT的訪問頻次

-- session 是查看當(dāng)前會話 ; 
-- global 是查詢?nèi)謹(jǐn)?shù)據(jù) ; 
SHOW GLOBAL STATUS LIKE 'Com_______';

image.png

Com_delete: 刪除次數(shù)
Com_insert: 插入次數(shù)
Com_select: 查詢次數(shù)
Com_update: 更新次數(shù)

通過上述指令，我們可以查看到當(dāng)前數(shù)據(jù)庫到底是以查詢?yōu)橹髁垢ぃ€是以增刪改為主芥吟，從而為數(shù)據(jù)
庫優(yōu)化提供參考依據(jù)。 如果是以增刪改為主专甩，我們可以考慮不對其進(jìn)行索引的優(yōu)化钟鸵。 如果是以
查詢?yōu)橹鳎敲淳鸵紤]對數(shù)據(jù)庫的索引進(jìn)行優(yōu)化了涤躲。

那么通過查詢SQL的執(zhí)行頻次携添，我們就能夠知道當(dāng)前數(shù)據(jù)庫到底是增刪改為主，還是查詢?yōu)橹鳌?那假
如說是以查詢?yōu)橹髀ㄒ叮覀冇衷撊绾味ㄎ会槍τ谀切┎樵冋Z句進(jìn)行優(yōu)化呢烈掠？ 次數(shù)我們可以借助于慢查詢
日志。
接下來缸托，我們就來介紹一下MySQL中的慢查詢?nèi)罩尽?br> 慢查詢?nèi)罩居涗浟怂袌?zhí)行時間超過指定參數(shù)（long_query_time，單位：秒俐镐，默認(rèn)10秒）的所有
SQL語句的日志矫限。

2.5.2 慢查詢?nèi)罩?/h3>

慢查詢?nèi)罩居涗浟怂袌?zhí)行時間超過指定參數(shù)（long_query_time，單位：秒，默認(rèn)10秒）的所有
SQL語句的日志叼风。
MySQL的慢查詢?nèi)罩灸J(rèn)沒有開啟取董，我們可以查看一下系統(tǒng)變量 slow_query_log。

image.png

如果要開啟慢查詢?nèi)罩疚匏蓿枰贛ySQL的配置文件（/etc/my.cnf）中配置如下信息：

#開啟MySQL慢日志查詢開關(guān) 
slow_query_log=1 
# 設(shè)置慢日志的時間為2秒茵汰，SQL語句執(zhí)行時間超過2秒，就會視為慢查詢孽鸡，記錄慢查詢?nèi)罩?
long_query_time=2 

配置完畢之后蹂午，通過以下指令重新啟動MySQL服務(wù)器進(jìn)行測試，查看慢日志文件中記錄的信息
/var/lib/mysql/localhost-slow.log彬碱。

systemctl restart mysqld

然后豆胸，再次查看開關(guān)情況，慢查詢?nèi)罩揪鸵呀?jīng)打開了巷疼。

image.png

可以查看locakhost-slow.log文件內(nèi)容,,如果有sql執(zhí)行超過兩秒,則回被記錄在內(nèi)

image.png

2.5.3 profile詳情

show profiles 能夠在做SQL優(yōu)化時幫助我們了解時間都耗費到哪里去了晚胡。通過have_profiling
參數(shù)，能夠看到當(dāng)前MySQL是否支持profile操作：

SELECT @@have_profiling ; 

image.png

可以看到嚼沿，當(dāng)前MySQL是支持 profile操作的估盘，但是開關(guān)是關(guān)閉的》幔可以通過set語句在
session/global級別開啟profiling：

SET profiling = 1;

開關(guān)已經(jīng)打開了忿檩，接下來，我們所執(zhí)行的SQL語句爆阶，都會被MySQL記錄燥透，并記錄執(zhí)行時間消耗到哪兒去
了。 我們直接執(zhí)行如下的SQL語句：

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

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

2.5.4 explain

EXPLAIN 或者 DESC命令獲取 MySQL 如何執(zhí)行 SELECT 語句的信息辨图，包括在 SELECT 語句執(zhí)行
過程中表如何連接和連接的順序班套。
語法:

-- 直接在select語句之前加上關(guān)鍵字 explain / desc 
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 條件 ;

image.png

Explain 執(zhí)行計劃中各個字段的含義:

image.png