MySQL 中索引是如何實現的，有哪些型別的索引，如何進行最佳化索引

ZhanLi發表於2023-03-27

原文網址 : https://www.cnblogs.com/ricklz/p/17262747.html

MySql索引型別

MySQL 中的索引

MySQL 中的索引

前言

上篇文章聊完了 MySQL 中的鎖，這裡接著來看下 MySQL 中的索引。

一般當我們資料庫中的某些查詢比較慢的時候，正常情況下，一頓分析下來，大多數我們會考慮對這個查詢加個索引，那麼索引是如何工作的呢？為什麼索引能加快查詢的速度，下面來具體的分析下。

在關聯式資料庫中，索引是一種單獨的、物理的對資料庫表中一列或多列的值進行排序的一種儲存結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的資料頁的邏輯指標清單。

索引的作用相當於圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。

索引的優點:

1、索引大大減少了伺服器需要掃描的資料量；

2、索引可以幫助伺服器避免排序和臨時表；

3、索引可以將隨機 I/O 變成順序 I/O。

如何評價一個索引，Relational Database Index Design and the Optimizers 一書介紹瞭如何評價一個索引是否符合某個查詢的三個星際判斷標準：

1、一星：索引將相關的記錄放在一起就評定為一星；

2、二星：如果索引中的資料順序和查詢中的排序順序一致就評定為二星；

3、三星：如果索引中的列包含了查詢中需要的全部列就評定為三星。

建索引的幾大原則

1、最左字首匹配原則，非常重要的原則，mysql 會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配，比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立 (a,b,c,d) 順序的索引，d 是用不到索引的，如果建立 (a,b,d,c) 的索引則都可以用到，a,b,d 的順序可以任意調整；

2、= 和 in 可以亂序，比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢最佳化器會幫你最佳化成索引可以識別的形式；

3、儘量選擇區分度高的列作為索引，區分度的公式是 count(distinct col)/count(*)，表示欄位不重複的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀態、性別欄位可能在大資料面前區分度就是0，那可能有人會問，這個比例有什麼經驗值嗎？使用場景不同，這個值也很難確定，一般需要 join 的欄位我們都要求是 0.1 以上，即平均 1 條掃描 10 條記錄；

4、索引列不能參與計算，保持列“乾淨”，比如 from_unixtime(create_time) = ’2014-05-29’ 就不能使用到索引，原因很簡單，b+ 樹中存的都是資料表中的欄位值，但進行檢索時，需要把所有元素都應用函式才能比較，顯然成本太大。所以語句應該寫成 create_time = unix_timestamp(’2014-05-29’)；

5、儘量的擴充套件索引，不要新建索引。比如表中已經有a的索引，現在要加 (a,b) 的索引，那麼只需要修改原來的索引即可。

索引的實現

InnoDB 支援三種索引模型：

1、雜湊索引；

2、全文索引；

3、B+ 樹索引。

雜湊索引

雜湊表也稱為雜湊，是一種以鍵-值 (key-value) 儲存資料的結構。輸入查詢的 key,就能找到對應的 value。雜湊的思路很簡單，把值放在陣列裡，用一個雜湊函式把 key 換算成一個確定的位置，然後把 value 放在陣列的這個位置。

當然會存在雜湊衝突，對個 key 在經過雜湊演算法處理後可能出現在雜湊表中的同一個槽位，當出現雜湊衝突的時候，可以使用連結串列來解決。這樣發生衝突的資料都放入到連結串列中。在進行資料查詢的時候，首先找到雜湊表中的槽位，然後在連結串列中依次遍歷找到對應的值。

雜湊表的這種結構適合於等值查詢的場景，在最優場景的下的時間複雜度能達到 O(1)。

雜湊索引的缺點

1、因為是雜湊表儲存的是 Hash 運算之後的 Hash值，所以它只能用於等值的查詢，範圍查詢在雜湊索引中不支援；

2、無法利用索引排序，索引中儲存的只是 Hash 計算之後的 Hash 值，對於排序，索引本身無法支援；

3、組合索引不能利用部分索引，也就是不支援最左匹配原則，對於組合索引，Hash 索引在計算 Hash 值的時候是組合索引鍵合併後再一起計算 Hash 值，而不是單獨計算 Hash值，所以透過組合索引的前面一個或幾個索引鍵進行查詢的時候，Hash 索引也無法被利用；

4、雜湊索引需要進行回表查詢，因為雜湊索引儲存的都是雜湊值和行指標，所以不能避免使用索引來避免讀取行。

InnoDB 中還有一個特殊的功能叫做"自適應雜湊索引(adaptive hash index)"。當 InnoDB 注意到某些索引值被使用的非常頻繁時，它會在記憶體中基於 B+tree 索引之上在建立一個雜湊索引，這樣就能讓 B+tree 索引也有雜湊索引快速查詢的優點了，這是一個完全自動，內部的過程，使用者無法控制或者配置，不過這個功能可以手動關閉。

InnoDB 中的自適應 Hash 相當於是“索引的索引”，採用 Hash 索引儲存的是 B+ 樹索引中的頁面的地址。這也就是為什麼可以稱自適應 Hash 為索引的索引。採用自適應 Hash 索引目的是可以根據 SQL 的查詢條件加速定位到葉子節點，特別是當 B+ 樹比較深的時候，透過自適應 Hash 索引可以提高資料的檢索效率。同時對於非聚簇索引中的查詢，自適應雜湊也能減少回表的次數。

全文索引

全文索引就是將儲存於資料庫中的整本書或整篇文章中的任意內容資訊找出來的技術，可以根據需求獲取全文中的有關文章，節，段，句，詞等資訊，也能進行統計和分析。

InnoDB 最早是不支援儲存全文索引的，想要使用全文索引就要選用 MySIAM 儲存引擎,從版本 1.2.x 開始才增加了全文索引支援。

全文索引一般使用倒排索引(inverted index)實現，倒排索引和 B+ 樹索引一樣，也是一種索引結構。

倒排索引在輔助表 (auxiliary table) 中儲存了單詞與單詞自身在一個或多個文件中所在位置之間的對映。這樣當全文索引，匹配到對應的單詞就能知道對應的文件位置了。

倒排索引是區別於正排索引的概念：

正排索引：是以文件物件的唯一 ID 作為索引，以文件內容作為記錄的結構。

倒排索引：Inverted index，指的是將文件內容中的單詞作為索引，將包含該詞的文件 ID 作為記錄的結構。

倒排索引中文件和單詞的關聯關係，通常使用關聯資料實現，主要有兩種實現方式：

1、inverted file index: 會記錄單詞和單詞所在的文件 ID 之間的對映；

2、full inverted index: 這種方式記錄的更詳細，除了會記錄單詞和單詞所在的文件 ID 之間的對映，還會記錄單詞所在文件中的具體位置。

下面來舉個例子

DocumentId 表示全文索引中檔案的 id, Text 表示儲存的內容。這些儲存的文件就是全文索引需要查詢的物件。

inverted file index

可以看到關聯中，記錄了單詞和 DocumentId 的對映，這樣透過對應的單詞就能找到，單詞所在的文件，不用一個個遍歷文件了。

full inverted index

這種方式和上面的 inverted file index 一樣也會記錄單詞和文件的對映，只不過記錄的更詳細了，還記錄了單詞在文件中的具體位置。

相比於 inverted file index 優點就是定位更精確，缺點也是很明顯，需要用更多的空間。

InnoDB 儲存引擎支援全文索引採用 full inverted index 的方式，將(DocumentId，Position) 視為一個 ilist。

因此在全文檢索的表中，一共有兩列，一列是 word 欄位，另一個是 ilist，並且在 word 欄位上設有索引。

記錄單詞和 DocumentId 的對映關係的表稱為 Auxiliary Table（輔助表）。

輔助表是存在與磁碟上的持久化的表，由於磁碟 I/O 比較慢，因此提供 FTS Index Cache（全文檢索索引快取）來提高效能。FTS Index Cache 是一個紅黑樹結構，根據（word, list）排序，在有資料插入時，索引先更新到快取中，而後 InnoDB 儲存引擎會批次進行更新到輔助表中。

B+ 樹索引

B+ 樹就是傳統意義上的索引，這是目前關係型資料庫中查詢最為常用和最為有效的索引。B+ 樹構造的索引類似於二叉樹，根據鍵值快速 (Key Value) 快速找到資料。

有一點需要注意的是，B+ 樹索引並不能找到給定鍵值具體的行。B+ 樹索引能找到的只是被查詢資料行所在的頁。然後把頁讀入到記憶體中，再在記憶體中查詢，找到查詢的目標資料。

B+ 樹是 B 樹的變種，這裡需要了解下 B 樹。

為什麼要引入 B 樹或者 B+ 樹呢？

紅黑樹等其它的資料結構也可以用來實現索引，為什麼要使用 B 樹或者 B+ 樹，簡單點講就是為了減少磁碟的 I/O。

一般來說，索引本身的資料量很大，全部放入到記憶體中是不太現實的，因此索引往往以索引檔案的形式儲存在磁碟中，磁碟 I/O 的消耗相比於記憶體中的讀取還是大了很多的，在機械硬碟時代，從磁碟隨機讀一個資料塊需要10 ms左右的定址時間。

為了讓一個查詢儘量少地讀磁碟，就需要減少樹的高度，就不能使用二叉樹，而是使用 N 叉樹了，這樣就能在遍歷較少節點的情況下也就是較少 I/O 查詢的情況下找到目標值。

比如一個二叉樹，訪問底部資料需要進行4次 I/O 操作。

如果使用4叉樹，那麼樹的層架就會變矮，這時候只需要進行3次 I/O 操作。

資料量越來越大，N 叉樹的效果更明顯，在有相同資料的情況下，對於二叉樹，能夠大大縮小樹的高度。

所以 B 樹和 B+ 樹就被慢慢演變而來了。

自平衡二叉樹雖然能保持查詢操作的時間複雜度在 O(logn)，但是因為它本質上是一個二叉樹，每個節點只能有 2 個子節點，那麼當節點個數越多的時候，樹的高度也會相應變高，這樣就會增加磁碟的 I/O 次數，從而影響資料查詢的效率。

為了解決降低樹的高度的問題，後面就出來了 B 樹，它不再限制一個節點就只能有 2 個子節點，而是允許 M 個子節點 (M>2)，從而降低樹的高度。

為什麼 MySQL 用的是 B+ 樹而不是 B 樹呢，這裡來看下區別？

B-tree 和 B+ 樹最重要的區別是 B+ 樹只有葉子節點儲存資料，其他節點用於索引，而 B-tree 對於每個索引節點都有 Data 欄位。

B 樹簡單的講就是一種多叉平衡查詢樹，它類似於普通的平衡二叉樹。不同的是 B-tree 允許每個節點有更多的子節點，這樣就能大大減少樹的高度。

B-Tree 結構圖中可以看到每個節點中不僅包含資料的 key 值，還有 data 值。而每一個頁的儲存空間是有限的，如果 data 資料較大時將會導致每個節點（即一個頁）能儲存的 key 的數量很小，當儲存的資料量很大時同樣會導致 B-Tree 的深度較大，增大查詢時的磁碟 I/O 次數，進而影響查詢效率。在 B+Tree 中，所有資料記錄節點都是按照鍵值大小順序存放在同一層的葉子節點上，而非葉子節點上只儲存 key 值資訊，這樣可以大大加大每個節點儲存的 key 值數量，降低 B+Tree 的高度。

B+ 樹相比與 B 樹：

1、非葉子節點只儲存索引資訊；

2、所有葉子節點都有一個鏈指標，所以B+ 樹可以進行範圍查詢；

3、資料都放在葉子節點中。

索引的分類

下面聊的索引，如果沒有特殊說明，都是基於 InnoDB 儲存引擎來分析的。

從物理角度可分為

1、聚簇索引（clustered index）；

2、非聚簇索引（non-clustered index）。

聚簇索引（clustered index）

聚簇索引並不是一種單獨的索引型別，而是一種資料儲存的方式。在 InnoDB 中聚簇索引實際上是在同一個結構中儲存了 B+ 索引和資料行。

聚簇字面意思就是資料行和索引緊緊在一起的儲存在一起。因為一個所以只能和一個資料儲存在一起，所以一個表中只有一個聚簇索引。

下面這裡來討論下 InnoDB 中聚簇索引的實現。

InnoDB 中必有要求有聚簇索引的存在，預設會在主鍵上建立聚簇索引，如果沒有主鍵欄位，表中第一個非空唯一索引將會被建立聚簇索引，在前面兩者都沒有的情況下，InnoDB 將自動生成一個隱式的自增 id 列，並在此列上建立聚簇索引。

聚簇索引的優點

資料訪問更快，因為聚簇索引將索引和資料儲存在同一個B+樹中，因此從聚簇索引中獲取資料比非聚簇索引更快。

缺點

1、插入速度嚴重依賴於插入順序，按照主鍵的順序插入是最快的方式，否則將會出現頁分裂，嚴重影響效能。因此，對於 InnoDB 表，一般都會定義一個自增的 ID 列為主鍵；

2、更新聚簇索引的代價很高，因為會強制 InnoDB 將每個更新的行移動到新的位置；

3、二級索引訪問可能需要經過兩次查詢。

二級索引中儲存的不是指向行的物理位置的指標，而是行的主鍵值，這就意味著透過二級索引查詢行，儲存引擎需要找到二級索引的葉子結點獲取對應的主鍵值，然後根據這個值去聚簇索引中找到對應的行。所以有兩次的查詢過程，這種叫做回表操作，在 InnoDB 中，自適應雜湊索引能夠減少這樣重複的工作。

非聚簇索引（non-clustered index）

非聚簇索引也叫二級索引或者輔助索引，輔助索引葉子節點儲存的不是具體的行資料，而是行的主鍵值，所以使用輔助索引會面臨二次查詢的問題，也就是回表。儲存引擎需要找到二級索引的葉子結點獲取對應的主鍵值，然後根據這個值去聚簇索引中找到對應的行。所以有兩次的查詢過程，這種就叫做回表查詢，在 InnoDB 中，自適應雜湊索引能夠減少這樣重複的工作。

聯合索引

聯合索引指對錶上多個列進行索引，聯合索引的建立方法和單列索引的建立方法一樣，不同的是聯合索引有多個索引列。

聯合索引中有一個很重要的原則就是最左匹配原則，即最左優先，在檢索資料時從聯合索引的最左邊開始匹配。

create table t
(
id int auto_increment primary key,
a int,
b int,
key index_a_b (a,b)
)ENGINE=INNODB;

比如上面的建立了 a 和 b 的聯合索引，來看下下面幾種查詢：

1、SELECT * FROM t WHERE a=1 AND b=3 這樣 a 和 b 的查詢會命中聯合索引 index_a_b；

2、SELECT * FROM t WHERE a=1 這樣 a=3 的查詢會命中聯合索引 index_a_b；

3、SELECT * FROM t WHERE b=3 AND a=1 這樣 a 和 b 的查詢也會命中聯合索引 index_a_b；

4、SELECT * FROM t WHERE b=3 這樣 b=3 是不會命中聯合索引 index_a_b，因為 b 位於聯和索引 index_a_b 第二個位置，不滿足最左匹配原則；

來看下聯合索引中的最左匹配原則的實現

可以看到聯合索引中 index_a_b (a,b) a 是有順序的，所以索引 a 列是可以使用這個聯合索引的，索引 b 列只是相對索引 a 列是有序的，本身是無序，所以單索引 b 列是不能使用這個聯合索引的。

同時因為聯合索引在第一個鍵值固定的情況下，第二個鍵值進行了排序，所以適合下面的查詢

SELECT * FROM t WHERE a=6 ORDER BY b desc

聯合索引中索引欄位的先後順序該如何選擇，慄如 index_a_b (a,b) 這個聯合索引，建立的時候，應該將索引列 a 放前面還是索引 b 列放前面呢？

有一個原則就是：將選擇性最高的列放到最前面。

選擇性最高值得是資料的重複值最少，因為區分度高的列能夠很容易過濾掉很多的資料。組合索引中第一次能夠過濾掉很多的資料，後面的索引查詢的資料範圍就小了很多了。

慄如，一個性別欄位，值只有兩種男或者女，這種區分度就很低了，搜尋的話也只能夠過濾掉一半的資料，資料量大的時候，這種效果是不明顯的。

這裡有一個區分度的公式 count(distinct col)/count(*) 表示欄位不重複的比例，比例越大掃描的記錄書越少，唯一鍵的區分都是1，例如性別等的欄位在資料量很大的情況下接近於0。這個值多少是個標準呢？使用場景不同，這個值也很難確定，一般需要 join 的欄位我們都要求是 0.1 以上，即平均 1 條掃描 10 條記錄。

覆蓋索引

索引確實能夠提高查詢的效率，但二級索引會有某些情況會存在二次查詢也就是回表的問題，這種情況合理的使用覆蓋索引，能夠提高索引的效率，減少回表的查詢。

覆蓋索引將需要查詢的值建立聯合索引，這樣索引中就能包含查詢的值，這樣查詢如果只查詢索引中的值和主鍵值就不用進行二次查詢了，因為當前索引中的資訊已經能夠滿足當前查詢值的請求。

回表查詢

回表查詢可以理解為二級索引的查詢，先定位主鍵然後，在定位行記錄的過程，它的效能相較於一次就定位到資料的查詢，效率更低。

一般建立的索引，不管是單列索引還是聯合索引，一個索引就對應一課獨立的 B+ 樹，索引 B+ 樹的節點僅僅包含了索引中幾個常見的欄位以及主鍵值。

如果根據索引查詢到了需要的資料，如果查詢的值僅僅是索引中的值和主鍵值，那麼這時候是不需要進行二次查詢的，也就是回表查詢，因為當前索引中的資訊已經能夠滿足當前查詢值的請求，如果查詢的欄位是還有其他的欄位，這種情況，索引中的值不能覆蓋了，就需要二次查詢了，透過主鍵值去聚簇索引中找到對應的行，然後返回。

所以說非聚簇索引一定會回表查詢嗎，答案是否定的，這涉及到查詢語句所要求的欄位是否全部命中了索引，如果是，那麼就不需要回表查詢。

explain 使用

參見文章MySQL學習----explain檢視一條sql 的效能

索引最佳化

索引下推

索引下推(index condition pushdown)，是 MySQL5.6 開始支援的一種根據索引進行查詢的最佳化方式。

索引下推，主要是用來透過減少回表的次數，提高查詢的效能。

索引條件下推 (ICP) 是針對 MySQL 使用索引從表中檢索行的情況的最佳化。如果沒有 ICP，儲存引擎會遍歷索引以定位基表中的行，並將它們返回給 MySQL 伺服器，MySQL 伺服器會評估這些 WHERE 行的條件。啟用 ICP 後，如果 WHERE僅使用索引中的列可以評估部分條件，則 MySQL 伺服器會推送這部分條件 WHERE 條件下降到儲存引擎。然後，儲存引擎使用索引條目評估推送的索引條件，只有在滿足條件時才會從表中讀取行。ICP 可以減少儲存引擎必須訪問基表的次數和 MySQL 伺服器必須訪問儲存引擎的次數。

簡單點講就是在索引遍歷過程中，對索引中包含的欄位先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。

舉個例子

假設有一個表包含了一個人的住址資訊，然後在其上建立了一個索引：

INDEX ( age , lastname )

然後我們執行以下查詢：

SELECT * FROM people
WHERE age = 24
AND name LIKE '%小%';

因為 name 使用了萬用字元開頭的 like，就需要全表掃描了。所以上面的聯合索引，只命中了索引 age。

在沒有索引下推之前：MySQL 就首先透過 age 索引定位查詢的資料，然後命中一部分資料，之後 name 會在這些資料中進行全資料的掃描，首先透過 id 回表查詢到對應的資料，然後在對比欄位值。

有了索引下推：可以在索引遍歷過程中，對索引中包含的欄位先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。

沒有新增索引下推

索引下推最佳化之後

這樣可以看到經過索引下推的最佳化，原本需要進行 4 次的回表查詢，最佳化之後只需要 2 次的回表查詢了。

ICP 的一些使用限制：

1、當 SQL 需要全表訪問時，ICP 的最佳化策略可用於 range, ref, eq_ref, ref_or_null 型別的訪問資料方法；

2、支援 InnoDB 和 MyISAM 表；

3、ICP 只能用於二級索引，不能用於主索引；

4、並非全部 WHERE 條件都可以用 ICP 篩選，如果 WHERE 條件的欄位不在索引列中，還是要讀取整表的記錄到 Server 端做 WHERE 過濾；

5、ICP 的加速效果取決於在儲存引擎內透過 ICP 篩選掉的資料的比例；

6、MySQL 5.6 版本的不支援分表的 ICP 功能，5.7 版本的開始支援；

7、當 SQL 使用覆蓋索引時，不支援 ICP 最佳化方法。

給字串欄位加索引

給字串建立所以可以考慮使用字首索引，定義好長度，就可以做到既節省空間，又不用額外增加太多的查詢成本。

實際上，在建立索引的時候，需要關注區分度，區分度越高越好。因為區分度越高，意味著重複的鍵值越少。因此，我們可以透過統計索引上有多少個不同的值來判斷要使用多長的字首。

可以使用下面這個語句，算出這個列上有多少個不同的值：

select count(distinct email) as L from SUser;

然後，依次選取不同長度的字首來看這個值，比如我們要看一下4~7個位元組的字首索引，可以用這個語句：

select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

當然，使用字首索引很可能會損失區分度，所以你需要預先設定一個可以接受的損失比例，比如 5%。然後，在返回的 L4~L7 中，找出不小於 L * 95% 的值，假設這裡 L6、L7 都滿足，你就可以選擇字首長度為 6。

不過需要注意的是使用了字首索引就不能使用覆蓋索引對查詢效能的最佳化了。

如果字首索引的區分度不是很高，還有沒有其他的方法了呢？

1、使用倒序儲存；

例如身份證資訊，前幾位大部分都是相同的，區分度不高，但是最後面即為有很高的區分度，那麼就可以把身份證的資訊，倒序儲存。

select field_list from t where id_card = reverse('input_id_card_string');

2、使用 hash 欄位；

可以在表上新建一個欄位，用來儲存身份證的校驗碼，同時在該欄位上建立索引。

每次插入資料除了記錄省份證資訊，還把身份證的 hash 碼，也儲存起來，這樣查詢的使用，透過這個 hash 碼，就能很快的找到身份證資訊了。

不過因為 hash 碼會存在相同的情況，所以查詢的條件本上還需要帶上身份證號碼。

select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

MySQL 中的 count 查詢

MySQL中的 count(*) 的實現方式

MyISAM 引擎把一個表的總行數存在了磁碟上，因此執行 count(*) 的時候會直接返回這個數，效率很高；
而 InnoDB 引擎就麻煩了，它執行 count(*) 的時候，需要把資料一行一行地從引擎裡面讀出來，然後累積計數。

InnoDB 中 count(*)、count(主鍵id)和count(1) ，返回的都是滿足條件的結果集的總行數，count(欄位)，則表示返回滿足條件的資料行裡面，引數“欄位”不為 NULL 的總個數。

count(主鍵id): InnoDB 引擎會遍歷整張表，把每一行的 id 值取出來，返回給 server 層，server層拿到 id 後，判斷是不可能為空的，就按行累加；

count(1): InnoDB引擎遍歷整張表，但不取值。server層對於返回的每一行，放一個數字“1”進去，判斷是不可能為空的，按行累加；

count(欄位):

1、如果這個“欄位”是定義為 not null 的話，一行行地從記錄裡面讀出這個欄位，判斷不能為 null，按行累加；

2、如果這個“欄位”定義允許為 null，那麼執行的時候，判斷到有可能是 null，還要把值取出來再判斷一下，不是 null 才累加。

count(*): 這個統計專門做了最佳化，不用取值，count(*) 肯定不是 null，按行累加。

所以按照效率排序 count(欄位)<count(主鍵id)<count(1)≈count(*)。

MySQL 中的 order by

MySQL 中的排序是我們經常用到的操作，下面來研究下排序的實現過程。

CREATE TABLE `t_user_city` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

insert into t_user_city values(1, "鄭州", "小明",12,"鄭州大街6666號201");
insert into t_user_city values(2, "杭州", "小紅",20,"杭州大街6666號201");
insert into t_user_city values(3, "杭州", "小白",19,"杭州大街6666號201");
insert into t_user_city values(4, "上海", "張三",24,"上海大街6666號201");
insert into t_user_city values(5, "上海", "李四",25,"上海大街6666號201");
insert into t_user_city values(6, "上海", "王五",26,"上海大街6666號201");
insert into t_user_city values(7, "上海", "王六",29,"上海大街6666號201");
insert into t_user_city values(8, "鄭州", "小明001",12,"鄭州大街6666號201");
insert into t_user_city values(9, "鄭州", "小明002",12,"鄭州大街6666號201");
insert into t_user_city values(10, "鄭州", "小明003",12,"鄭州大街6666號201");

可以看到上面的表，是有一個 city 欄位的索引。

select city,name,age from t_user_city where city='上海' order by name limit 1000  ;

來分析下排序的過程

全欄位排序

MySQL 會給每個查詢執行緒分配一個用於排序的記憶體: sort_buffer。

透過上面查詢的栗子來看下MySQL 中是如何使用 sort_buffer 來進行排序的。

1、首先 MySQL 會給米一個查詢執行緒分配一快大小為 sort_buffer_size 的排序記憶體 sort_buffer，放入查詢和排序的欄位，所以欄位 city,name,age 都會被放入到排序記憶體 sort_buffer 中；

2、查詢首先使用索引 city 來確定查詢的資料，然後查詢到的資料都會透過查詢到的主鍵 id 進行一次回表操作，查詢到 city,name,age 欄位，然後放入到 sort_buffer 中；

3、所有的資料都放入到排序記憶體 sort_buffer 之後，會根據排序欄位對 sort_buffer 中的資料進行排序；

4、按照排序結果取前 1000 行返回給客戶端，整個排序結束。

如果查詢的資料量很大，sort_buffer 記憶體中就放不下了，這時候就需要使用磁碟臨時檔案輔助排序。

使用的就是外部排序，一般使用歸併排序。使用外部排序的時候，MySQL 會將排序的檔案分成 N 份，每一份單獨排序後放入到一個臨時檔案中，然後再把這 N 個有序檔案合成一個有序的大檔案。

如果 sort_buffer_size 超過了需要排序的資料量的大小，number_of_tmp_files 就是0，表示排序可以直接在記憶體中完成。

在排序的檔案在一定額度的情況下，如果 sort_buffer_size 越小，那麼藉助於磁碟排序徐的時候，需要的臨時檔案也就越多，發生 I/O 的次數也就越多，效能也就越差。

可以透過下面的命令來檢視一個排序語句是否使用了臨時檔案

/* 開啟optimizer_trace，只對本執行緒有效 */
SET optimizer_trace='enabled=on'; 

/* @a儲存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 執行語句 */
select city,name,age from t_user_city where city='上海' order by name limit 1000  ;

/* 檢視 OPTIMIZER_TRACE 輸出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b儲存Innodb_rows_read的當前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 計算Innodb_rows_read差值 */
select @b-@a;

這裡來重點看下

"filesort_summary": {
	"memory_available": 262144,
	"key_size": 512,
	"row_size": 654,
	"max_rows_per_buffer": 15,
	"num_rows_estimate": 15,
	"num_rows_found": 4, // 參與排序的行
	"num_initial_chunks_spilled_to_disk": 0, // 表示產生了多少個檔案用於外部排序，如果為0，說明沒有外部排序
	"peak_memory_used": 32800,
	"sort_algorithm": "std::sort",
	"sort_mode": "<fixed_sort_key, packed_additional_fields>"
}

sort_mode

MySQL 有 3 種排序模式

1、< sort_key, rowid > 對應的是 MySQL 4.1 之前的“原始排序模式”，即 rowid 排序；

2、< sort_key, additional_fields > 對應的是 MySQL 4.1 以後引入的“修改後排序模式”即全欄位排序；

3、< sort_key, packed_additional_fields > 是 MySQL 5.7.3 以後引入的進一步最佳化的”打包資料排序模式”，是對全欄位排序的最佳化。對於額外欄位資料型別為：CHAR、VARCHAR、以及可為 NULL 的固定長度資料型別，其欄位值長度是可以被壓縮的。例如，在無壓縮的情況下，欄位資料型別為VARCHAR(255)，當欄位值只有 3 個字元時，卻需要佔用 sort buffer 255 個字元長度的記憶體空間大小；而在有壓縮的情況下，欄位值為 3 個字元，卻只佔用 3 個字元長度 + 2 個位元組長度標記的記憶體空間大小；當欄位值為 NULL 時，只需透過一個位掩碼來標識。

rowid 排序

全欄位排序會把欄位放入到在 sort_buffer 或臨時檔案中進行排序，但是當查詢的返回的欄位很多，那麼 sort_buffer 中要放入的欄位很多，那麼就意味著能夠放下的條數很少了，需要生成的臨時檔案就會變多，排序的效能會變差。

如何最佳化呢？MySQL 中使用了 rowid 排序來解決這種場景。

rowid 排序原理的大致思路就是，不會將 SQL 語句中 select 後面的所有欄位都放入到 sort_buffer 中，而是隻會將需要被排序的欄位和主鍵 id 放入到 sort_buffer 中，對應到本文的例子中就是：將 name 欄位和主鍵 id 欄位放入到 sort_buffer 中。

那麼 MySQL 判斷單行長度的標準是什麼呢？

透過 max_length_for_sort_data 欄位，MySQL 中專門用於控制排序的行資料長度的欄位，如果超過了這個長度，那麼就會使用 rowid 排序演算法了。

來看下 rowid 排序的過程

1、首先 MySQL 會為應用程式分配一塊記憶體大小為 sort_buffer_size 的記憶體，然後確定放入的欄位，假定這時候欄位 city,name,age 的長度之和已超過了 max_length_for_sort_data 的限制，這時候就需要用到 rowid 排序了，這時候放入到 sort_buffer 中的欄位只有要排序的列 name 欄位和主鍵 id;

2、查詢首先使用索引 city 來確定查詢的資料，然後查詢到的資料都會透過查詢到的主鍵 id 進行一次回表操作(第一次回表)，查詢到的 name,id 欄位放入到 sort_buffer 中；

3、所有的資料都放入到排序記憶體 sort_buffer 之後，會根據排序欄位對 sort_buffer 中的資料進行排序；

4、遍歷排序結果，取前 1000 行，並按照 id 的值回到原表中取出 city、name和age 三個欄位返回給客戶端(第二次回表)，排序結束。

可以看到相比於相比全欄位排序而言，rowid 排序的多了一次回表的查詢操作。

總結下來就是

如果 MySQL 實在是擔心排序記憶體太小，會影響排序效率，才會採用 rowid 排序演算法，這樣排序過程中一次可以排序更多行，但是需要再回到原表去取資料。

如果 MySQL 認為記憶體足夠大，會優先選擇全欄位排序，把需要的欄位都放到 sort_buffer 中，這樣排序後就會直接從記憶體裡面返回查詢結果了，不用再回到原表去取資料。

這也就體現了 MySQL 的一個設計思想：如果記憶體夠，就要多利用記憶體，儘量減少磁碟訪問。

對於 InnoDB 表來說，rowid 排序會要求回表多造成磁碟讀，因此不會被優先選擇。

MySQL 之所以需要生成臨時表，並且在臨時表上做排序操作，其原因是原來的資料都是無序的。如何最佳化，在排序的欄位上建索引。因為索引是有序的，給排序欄位建立索引，就能用到索引的有序性來排序了。

聯合索引

針對上面排序的兩種方式，想要擁有更好的查詢效能，我們可以考慮加聯合索引，例如上面的栗子，我們可以考慮給 city,name 建立聯合索引，因為索引是有序的，這樣既能滿足查詢的最佳化需求，也能滿足排序的需求。

這是最優解嗎？

因為查詢的 age 欄位不在索引內，所以查詢排序之後還是需要進行回表操作的。

這個查詢的最優解就是建立 city,name,age 欄位的聯合索引也叫覆蓋索引，這樣透過索引就能返回所有查詢的值了。

主鍵選擇自增還是使用 UUID

主鍵是用自增還是 UUID 呢？

主鍵索引最好是自增的。

InnoDB 建立主鍵索引預設為聚簇索引，資料被存放在了 B+Tree 的葉子節點上。也就是說，同一個葉子節點內的各個資料是按主鍵順序存放的，因此，每當有一條新的資料插入時，資料庫會根據主鍵將其插入到對應的葉子節點中。

1、如果表使用自增主鍵，那麼每次插入新的記錄，記錄就會順序新增到當前索引節點的後續位置，當一頁寫滿，就會自動開闢一個新的頁；

2、如果使用非自增主鍵（如uuid），由於每次插入主鍵的值近似於隨機，因此每次新紀錄都要被插到索引頁的隨機某個位置，此時 MySQL 為了將新記錄插到合適位置而移動資料，甚至目標頁面可能已經被回寫到磁碟上而從快取中清掉，此時又要從磁碟上讀回來，這增加了很多開銷，同時頻繁的移動、分頁操作造成索引碎片，得到了不夠緊湊的索引結構，後續不得不透過 OPTIMIZE TABLE 來重建表並最佳化填充頁面。

總結

1、索引的優點:

1、索引大大減少了伺服器需要掃描的資料量；
2、索引可以幫助伺服器避免排序和臨時表；
3、索引可以將隨機 I/O 變成順序 I/O。

2、InnoDB 中模式的索引模型是 B+ 樹索引；

3、B 樹簡單的講就是一種多叉平衡查詢樹，它類似於普通的平衡二叉樹。不同的是 B-tree 允許每個節點有更多的子節點，這樣就能大大減少樹的高度；

4、聚簇索引；