索引

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

無索引的查找：全表掃描(將整張表遍歷一遍)，性能極低，
有索引的查找：資料庫系統在存盤資料的同時會維護一種資料結構(如二叉樹)，當需要查找時，利用該資料結構進行查找，性能較高，
索引的優缺點

一. 索引結構

MySQL的索引是在存盤引擎層實作的，不同的存盤引擎有不同的結構，

索引在存盤引擎中的支持情況

平常所說的索引，如果沒有特別指明，一般都是說B+樹結構組織的索引，

1. B+樹

1.1 二叉樹

一種經典的資料結構，
二叉樹的兩個缺點：
- 順序存盤二叉樹時，會形成一條鏈表，二叉樹的深度很大，效率很低，
- 二叉樹的度不大于2，在資料庫中存有大量資料的時候深度很大，效率很低，

1.2 紅黑樹

一種自平衡的特殊二叉樹，
可以解決二叉樹可能形成鏈表的缺點，但是依舊存在資料量大時深度很大的問題，

1.3 B樹(平衡多路查找樹)

一種自平衡的樹，可以解決二叉樹的兩個缺點，
一個節點可以擁有兩個以上的子節點，

1.4 B+樹

B樹的變種，
B+樹與B樹的區別：
- 所有的元素都會出現在葉子節點，
- 葉子節點形成了一條單向鏈表，

1.5 MySQL中的B+樹

在MySQL中，對B+樹進行了優化，在原有基礎上，葉子節點改為了雙向回圈鏈表，提高區間訪問的性能，

2. Hash

哈希索引就是采用一定的Hash演算法，將鍵值換算成新的Hash值，映射到對應的槽位上，然后存盤在Hash表中，

如果兩個(或多個)鍵值映射到同一個槽位上，產生了Hash沖突，可以通過鏈表解決，

2.1 Hash索引的特點

只能用于對等比較(=，in)，不支持范圍查詢(between，>，<，...)，
無法利用索引完成排序操作，
查詢效率高，通常只需要一次檢索(不出現hash沖突)，效率高于B+樹，

2.2 存盤引擎支持

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

二. 索引分類

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

聚集索引的選取規則：
- 如果存在主鍵，主鍵索引就是聚集索引，
- 如果不存在主鍵，將適用第一個唯一(UNIQUE)索引作為聚集索引，
- 如果不存在主鍵，或沒有合適的唯一索引，則InnoDB會自動生成一個rowid作為隱藏的聚集索引，
聚集索引和二級索引的示意圖：

在查找時，先走二級索引，找到對應的主鍵后，再走聚集索引，找到對應的整個行，（回表查詢）

三. 索引語法

1. 創建索引

create [unique|fulltext] index {索引名} on {表名} ({欄位名},...);

unique 唯一索引｜fulltext 全文索引｜不加這兩個則說明是常規索引，
一個索引可以關聯多個欄位，如果一個索引只關聯一個欄位，叫單列索引，如果關聯多個欄位，叫聯合索引（組合索引），
聯合索引的欄位順序是有講究的，
索引名一般的命名規則：idx _ 表名 _ 欄位名

2. 查看索引

show index form {表名};

3. 洗掉索引

drop index {索引名} on {表名};

四. SQL性能分析

做性能分析是為了做SQL優化，SQL主要是做查詢優化，因為查詢操作比增刪改多，查詢優化的關鍵在于索引，

1. SQL執行頻率

# 查看當前資料庫的增刪改查的訪問頻次
show global status like 'Com_______';
# 模糊匹配'Com'后面是7個下劃線

根據執行頻率來判斷SQL優化需要在哪方面進行，也就是說這個資料庫哪個操作頻率高就優化哪個操作，

2. 慢查詢日志

慢查詢日志記錄了所有執行時間超過制定引數 (long_query_time，單位：秒，默認10秒) 的所有sql陳述句的日志

慢查詢日志用于找到執行慢的sql陳述句，進行針對性優化，

2.1 開啟慢查詢日志

MySQL的慢查詢日志默認沒有開啟，需要在MySQL的組態檔(/etc/my.cnf) 中配置，

查詢是否開啟

show variables like 'slow_query_log';

開啟慢查詢日志

在MySQL的組態檔(/etc/my.cnf) 中配置如下資訊：

# 開啟MySQL慢查詢日志開關
show_query_log = 1
# 設定慢查詢日志的時間為2秒，SQL陳述句執行時間超過2秒就會被記錄
long_query_time = 2

配置完畢后，需要重啟服務器，

# 重啟服務器
systemctl restart mysqld

2.2 查看慢查詢日志

# 慢查詢日志存放地址 Linux下
/var/lib/mysql/localhost-slow.log

3. profile詳情

Show profiles 能夠在做SQL優化時幫助我們了解時間都耗費到哪里去了，

3.1 查看MySQL是否支持profile操作

select @@have_profiling;

3.2 打開profile開關

# 查看是否打開
select @@profiling;

# 打開profile開關
set [session|global] profiling = 1;

profile默認是關閉的，
[session|global] 可以指定是會話級別的還是全域的，

3.3 查看profile詳情

# 查看每一條SQL的耗時基本情況
show profiles;

# 查看指定query_id的SQL陳述句各個階段的耗時情況
show profile for query query_id;

# 查看指定query_id的SQL陳述句CPU的使用情況
show profile cup for query query_id;

query_id指的是在profiles中的某一條指令的id，可以在show profiles中看到，

4. explain執行計劃

explain 或者 desc命令獲取MySQL如何執行select陳述句的資訊，包括在select陳述句執行程序中表如何連接和連接的順序，

# 直接在select陳述句之前加上關鍵字explain/desc
explain select {欄位串列} from {表名} where {條件};

explain執行計劃各欄位含義

五. 索引使用

1. 最左前綴法則

如果索引了多列(聯合索引)，要遵循最左前綴法則，最左前綴法則是指查詢從索引的最左列開始，并且不跳過索引中的列，
如果跳躍某一列，索引將部分失效（后面的欄位索引失效），
查詢時左邊欄位存在即符合最左前綴法則，不管它在代碼中的位置，

2. 范圍查詢

聯合索引中，出現范圍查詢（> , <），范圍查詢右側的列索引失效，
用（>= , <=）不會出現失效情況，

3. 索引列運算

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

4. 字串不加引號

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

5. 模糊查詢

如果是尾部進行模糊查詢，索引不會失效；如果是頭部進行模糊查詢，索引會失效，

6. or連接的條件

用or分割開的條件，如果or前的條件中的列有索引，二后面的列沒有索引，那么涉及的索引都不會被用到，
只要把沒有索引的建立一個索引就可以解決失效問題，

7. 資料分布影響

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

8. SQL提示

SQL提示是優化資料庫的一個重要手段，在SQL陳述句中加入一些人為的提示來達到優化操作的目的，

8.1 use index

使用指定索引(建議)

select * from {表名} use index({索引名}) where...;

8.2 ignore index

不使用某個索引

select * from {表名} ignore index({索引名}) where...;

8.3 force index

使用指定索引(必須)

select * from {表名} force index({索引名}) where...;

9. 覆寫索引

盡量使用覆寫索引（查詢使用了索引，并且需要回傳的列，在該索引中已經全部能夠找到），減少使用select *，
使用覆寫索引和沒有使用覆寫索引，在explain中的Extra列有不一樣的提示：
- （沒使用）using index condition : 查找使用了索引，但是需要回表查詢資料，
- （使用了）using where; using index : 查找使用了索引，但是需要的資料都在索引列中能夠找到，所以不需要回表查詢資料，
覆寫索引直接在二級索引中獲取了回傳所需的所有資料，所以不需要回表查詢，查詢速度快，
如果不是覆寫查詢，在二級索引中查詢到資料后，還需要拿到對應資料的主鍵，到聚焦索引中查詢行資料，這就叫回表查詢，所以速度慢，

10. 前綴索引

當欄位型別為字串時，有時候需要存盤很長的字串，如果建立索引，索引會變得很大，浪費大量磁盤IO，影響查詢效率，

此時可以只用字串的一部分前綴來建立索引（前綴索引），可以大大節約索引空間，從而提高效率，

10.1 創建前綴索引

create index {索引名} on {表名}({欄位名}({前綴的字符數}));

10.2 前綴長度的選擇

可以根據索引的選擇性來決定，
選擇性：不重復的索引值和資料表的記錄總數的比值，索引選擇性越高，效率越高，唯一索引的選擇性是1，是性能最好的，
求選擇性：

select count(distinct substring({欄位名},1,{截取長度}))/count(*) from {表名};

11. 單列索引和聯合索引

單列索引：一個索引只包含單個列
聯合索引：一個索引包含了多個列
在業務場景中，如果存在多個查詢條件，考慮針對查詢欄位建立索引時，建議使用聯合索引，
聯合索引的存盤結構：

六. 索引設計原則

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

標籤：其他

上一篇：DolphinDB +Python Airflow 高效實作資料清洗

下一篇：elasticsearch升級和索引重建。

MySQL學習筆記-索引

索引

一. 索引結構

1. B+樹

1.1 二叉樹

1.2 紅黑樹

1.3 B樹(平衡多路查找樹)

1.4 B+樹

1.5 MySQL中的B+樹

2. Hash

2.1 Hash索引的特點

2.2 存盤引擎支持

二. 索引分類

三. 索引語法

1. 創建索引

2. 查看索引

3. 洗掉索引

四. SQL性能分析

1. SQL執行頻率

2. 慢查詢日志

2.1 開啟慢查詢日志

2.2 查看慢查詢日志

3. profile詳情

3.1 查看MySQL是否支持profile操作

3.2 打開profile開關

3.3 查看profile詳情

4. explain執行計劃

五. 索引使用

1. 最左前綴法則

2. 范圍查詢

3. 索引列運算

4. 字串不加引號

5. 模糊查詢

6. or連接的條件

7. 資料分布影響

8. SQL提示

8.1 use index

8.2 ignore index

8.3 force index

9. 覆寫索引

10. 前綴索引

10.1 創建前綴索引

10.2 前綴長度的選擇

11. 單列索引和聯合索引

六. 索引設計原則