[每天進步一點點]mysql筆記整理（三）：索引

毛毛向前衝發表於2020-04-02

「筆記很無味，點贊請準備。」

索引定義

❝
資料庫索引，是資料庫管理系統中一個排序的資料結構，以協助快速查詢，更新資料庫表中的資料。
❞

從定義中可以看出，索引其實就是一種「資料結構」。資料都是以檔案的形式儲存在磁碟上的，每一行資料都有它的磁碟地址，如果沒有索引，要從幾百萬行資料中檢索一條資料，只能遍歷整張表才能找過結果。有了索引之後，只需要在索引裡面去檢索這條資料就可以了，因為索引是一種特殊的專門用來快速檢索的資料結構，當我們找到資料磁碟地址後，就可以拿到想要的資料。

索引型別

以InnoDB儲存引擎為例，索引型別有：

Normal（普通索引）

❝
也叫非唯一索引，是最普通的索引，沒有任何限制條件。
❞
Unique（唯一索引）

❝
唯一索引要求鍵值不能重複。

主鍵索引是一種特殊的唯一索引，它多了一個限制條件，要求鍵值不能為空。
❞
Fulltext（全文索引）

❝
全文索引主要是針對比較大的資料，比如我們存放的是訊息內容，有幾kb的資料，如果要解決like查詢效率低的問題，可以建立全文索引。只有文字型別的欄位才可以建立全文索引，比如char、varchar、text。
❞

全文索引的使用：
```
select * from table where match(content) against ('xxxx' IN NATURAL LANGUAGE MODE);
複製程式碼
```
聚集索引（聚簇索引）

❝
聚集索引就是索引鍵值的邏輯順序跟表資料行的物理儲存順序是一致的。在InnoDB中，主鍵索引就是聚集索引，非主鍵索引是非聚集索引。
❞

索引儲存模型

索引的儲存模型有二分查詢、二叉查詢樹（BST Binary Search Tree）、平衡二叉樹（AVL Tree）、多路平衡查詢樹（B Tree）、加強版多路平衡查詢樹（B+ Tree）。

儲存模型是一步一步演進過來的。

InnoDB邏輯儲存結構

mysql的儲存結構分為5級，表空間、段、簇、頁、行。

表空間

❝
表空間可以看做是InnoDB儲存引擎邏輯結構的最高層，所有的資料都存放在表空間中。表空間又分為：系統表空間、獨佔表空間、通用表空間、臨時表空間、Undo表空間。
❞

段（Segment）

❝
表空間是由各個段組成，段又分為：資料段、索引段、回滾段。

段是一個邏輯概念，一個ibd檔案（獨立的表空間檔案）裡面會有很多個段組成。

建立一個索引會建立兩個段，一個是索引段（leaf node segment），一個是資料段（non-leaf node segment）。索引段管理非葉子節點的資料，資料段管理葉子節點資料。
❞

簇（Extent）

❝
一個段又是由多個簇（也可以叫區）組成，每個簇的大小是1MB（64個連續的頁）。

每一個段至少有個一個簇，一個段所管理的空間大小是無限的，可以一直擴充套件下去，擴充套件的最小單位就是簇。
❞

頁（Page）

❝
簇是由連續的頁組成的空間，一個簇中有64個連續的頁。

一個表空間最多擁有2^32個頁，預設情況下一個頁的大小為16KB，也就是說一個表空間最多儲存64TB的資料。
❞

行（Row）

❝
InnoDB儲存引擎是面向行（Row-oriented）儲存的。
❞

索引使用原則

列的離散度

❝
列的離散度計算公式：count(distinct(column_name))/count(*)，列的全部不同值和所有資料行的比例。資料行數相同的情況下，分子越大，列的離散度就越高。

簡單來說，如果列的重複值越多，離散度就越低，重複值越少，離散度就越高。
❞
聯合索引最左匹配

❝
聯合索引在B+Tree中是複合的資料結構，它是按照從左到右的順序來建立搜尋樹的。
❞

我們在建立聯合索引的時候，一定要把最常用的列放到最左邊。

例如：給表table中建立聯合索引a和b，

select * from table where a='xxx' and b='xxx' /*是可以用到聯合索引的。*/

select * from table where a='xxx' /*可以用到聯合索引*/

select * from table where b='xxx' /*無法使用聯合索引*/

複製程式碼

給a和b建立聯合索引其實就是相當於（a）和（a，b）建立了兩個索引。

在給a、b、c建立聯合索引的時候其實就是建立了（a）索引，（a,b）索引，（a,b,c）索引，這時，如果條件where b=‘xxx‘和where b=‘xxx' and c='xxx'，還有where a='xxx' and c='xxx'都是用不到聯合索引的。

「說明聯合索引的使用條件時不能不使用第一個欄位，也不能中斷。」

什麼時候用不到索引

在索引列上使用函式（「replace、substr、concat、sum、count、avg」）、表示式、計算（「+、-、*、/」）。
字串不加引號，出現隱式轉換。
like條件前面帶%。
負向查詢

❝
not like 不能使用索引。

!=、<>、not in在某些情況下可能用到索引。

「注意：一個sql語句是否使用到索引，是跟資料庫版本，資料量、資料選擇度都有關係的。」
❞