作者：小孩子
公衆號：我們都是小青蛙

對於我們這些 MySQL的使用者來說，平時用的最多的就是查詢功能。DBA時不時丟過來一些慢查詢語句讓優化，如果連查詢是怎麼執行的都不清楚還優化個毛線，所以是時候掌握真正的技術了。 MySQL有一個稱爲 查詢優化器的模塊，一條查詢語句進行語法解析之後就會被交給查詢優化器來進行優化，優化的結果就是生成一個所謂的 執行計劃，這個執行計劃表明了應該使用哪些索引進行查詢，表之間的連接順序是啥樣的，最後會按照執行計劃中的步驟調用存儲引擎提供的方法來真正的執行查詢，並將查詢結果返回給用戶。不過查詢優化這個主題有點兒大，在學會跑之前還得先學會走，所以本章先來瞅瞅 MySQL怎麼執行單表查詢（就是 FROM子句後邊只有一個表，最簡單的那種查詢～）。

爲了故事的發展，先得有個表：

一文搞懂 MySQL 單表查詢的底層實現

我們爲這個 single_table表建立了1個聚簇索引和4個二級索引，分別是：

  • 爲 id列建立的聚簇索引。
  • 爲 key1列建立的 idx_key1二級索引。
  • 爲 key2列建立的 idx_key2二級索引，而且該索引是唯一二級索引。
  • 爲 key3列建立的 idx_key3二級索引。
  • 爲 key_part1、 key_part2、 key_part3列建立的 idx_key_part二級索引，這也是一個聯合索引。

然後我們需要爲這個表插入10000行記錄，除 id列外其餘的列都插入隨機值就好了，具體的插入語句我就不寫了，自己寫個程序插入吧（id列是自增主鍵列，不需要我們手動插入）。

訪問方法（access method）的概念

想必各位都用過高德地圖來查找到某個地方的路線吧（此處沒有爲高德地圖打廣告的意思，他們沒給我錢，大家用百度地圖也可以啊），如果我們搜西安鐘樓到大雁塔之間的路線的話，地圖軟件會給出n種路線供我們選擇，如果我們實在閒的沒事兒幹並且足夠有錢的話，還可以用南轅北轍的方式繞地球一圈到達目的地。也就是說，不論採用哪一種方式，我們最終的目標就是到達大雁塔這個地方。回到 MySQL中來，我們平時所寫的那些查詢語句本質上只是一種聲明式的語法，只是告訴 MySQL我們要獲取的數據符合哪些規則，至於 MySQL背地裏是怎麼把查詢結果搞出來的那是 MySQL自己的事兒。對於單個表的查詢來說，設計MySQL的大叔把查詢的執行方式大致分爲下邊兩種：

使用全表掃描進行查詢

這種執行方式很好理解，就是把表的每一行記錄都掃一遍嘛，把符合搜索條件的記錄加入到結果集就完了。不管是啥查詢都可以使用這種方式執行，當然，這種也是最笨的執行方式。

使用索引進行查詢

因爲直接使用全表掃描的方式執行查詢要遍歷好多記錄，所以代價可能太大了。如果查詢語句中的搜索條件可以使用到某個索引，那直接使用索引來執行查詢可能會加快查詢執行的時間。使用索引來執行查詢的方式五花八門，又可以細分爲許多種類：

  • 針對主鍵或唯一二級索引的等值查詢
  • 針對普通二級索引的等值查詢
  • 針對索引列的範圍查詢
  • 直接掃描整個索引

設計 MySQL的大叔把 MySQL執行查詢語句的方式稱之爲 訪問方法或者 訪問類型。同一個查詢語句可能可以使用多種不同的訪問方法來執行，雖然最後的查詢結果都是一樣的，但是執行的時間可能差老鼻子遠了，就像是從鐘樓到大雁塔，你可以坐火箭去，也可以坐飛機去，當然也可以坐烏龜去。下邊細細道來各種 訪問方法的具體內容。

const

有的時候我們可以通過主鍵列來定位一條記錄，比方說這個查詢：

SELECT * FROM single_table WHERE id = 1438;

MySQL會直接利用主鍵值在聚簇索引中定位對應的用戶記錄，就像這樣：

一文搞懂 MySQL 單表查詢的底層實現

原諒我把聚簇索引對應的複雜的 B+樹結構搞了一個極度精簡版，爲了突出重點，我們忽略掉了 頁的結構，直接把所有的葉子節點的記錄都放在一起展示，而且記錄中只展示我們關心的索引列，對於 single_table表的聚簇索引來說，展示的就是 id列。我們想突出的重點就是： B+樹葉子節點中的記錄是按照索引列排序的，對於的聚簇索引來說，它對應的 B+樹葉子節點中的記錄就是按照 id列排序的。 B+樹本來就是一個矮矮的大胖子，所以這樣根據主鍵值定位一條記錄的速度賊快。類似的，我們根據唯一二級索引列來定位一條記錄的速度也是賊快的，比如下邊這個查詢：

SELECT * FROM single_table WHERE key2 = 3841;

這個查詢的執行過程的示意圖就是這樣：

一文搞懂 MySQL 單表查詢的底層實現

可以看到這個查詢的執行分兩步，第一步先從 idx_key2對應的 B+樹索引中根據 key2列與常數的等值比較條件定位到一條二級索引記錄，然後再根據該記錄的 id值到聚簇索引中獲取到完整的用戶記錄。

設計 MySQL的大叔認爲通過主鍵或者唯一二級索引列與常數的等值比較來定位一條記錄是像坐火箭一樣快的，所以他們把這種通過主鍵或者唯一二級索引列來定位一條記錄的訪問方法定義爲： const，意思是常數級別的，代價是可以忽略不計的。不過這種 const訪問方法只能在主鍵列或者唯一二級索引列和一個常數進行等值比較時纔有效，如果主鍵或者唯一二級索引是由多個列構成的話，索引中的每一個列都需要與常數進行等值比較，這個 const訪問方法纔有效（這是因爲只有該索引中全部列都採用等值比較纔可以定位唯一的一條記錄）。

對於唯一二級索引來說，查詢該列爲 NULL值的情況比較特殊，比如這樣：

SELECT * FROM single_table WHERE key2 IS NULL;

因爲唯一二級索引列並不限制 NULL值的數量，所以上述語句可能訪問到多條記錄，也就是說上邊這個語句不可以使用 const訪問方法來執行。

ref

有時候我們對某個普通的二級索引列與常數進行等值比較，比如這樣：

SELECT * FROM single_table WHERE key1 = 'abc';

對於這個查詢，我們當然可以選擇全表掃描來逐一對比搜索條件是否滿足要求，我們也可以先使用二級索引找到對應記錄的 id值，然後再回表到聚簇索引中查找完整的用戶記錄。由於普通二級索引並不限制索引列值的唯一性，所以可能找到多條對應的記錄，也就是說使用二級索引來執行查詢的代價取決於等值匹配到的二級索引記錄條數。如果匹配的記錄較少，則回表的代價還是比較低的，所以 MySQL可能選擇使用索引而不是全表掃描的方式來執行查詢。設計 MySQL的大叔就把這種搜索條件爲二級索引列與常數等值比較，採用二級索引來執行查詢的訪問方法稱爲： ref。我們看一下采用 ref訪問方法執行查詢的圖示：

一文搞懂 MySQL 單表查詢的底層實現

從圖示中可以看出，對於普通的二級索引來說，通過索引列進行等值比較後可能匹配到多條連續的記錄，而不是像主鍵或者唯一二級索引那樣最多隻能匹配1條記錄，所以這種 ref訪問方法比 const差了那麼一丟丟，但是在二級索引等值比較時匹配的記錄數較少時的效率還是很高的（如果匹配的二級索引記錄太多那麼回表的成本就太大了），跟坐高鐵差不多。不過需要注意下邊兩種情況：

1、二級索引列值爲 NULL的情況，不論是普通的二級索引，還是唯一二級索引，它們的索引列對包含 NULL值的數量並不限制，所以我們採用 key IS NULL這種形式的搜索條件最多隻能使用 ref的訪問方法，而不是 const的訪問方法。

2、對於某個包含多個索引列的二級索引來說，只要是最左邊的連續索引列是與常數的等值比較就可能採用 ref的訪問方法，比方說下邊這幾個查詢：

SELECT * FROM single_table WHERE key_part1 = 'god like';
SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary';
SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary' AND key_part3 = 'penta kill'

但是如果最左邊的連續索引列並不全部是等值比較的話，它的訪問方法就不能稱爲 ref了，比方說這樣：


SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary';

refornull

有時候我們不僅想找出某個二級索引列的值等於某個常數的記錄，還想把該列的值爲 NULL的記錄也找出來，就像下邊這個查詢：

SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;

當使用二級索引而不是全表掃描的方式執行該查詢時，這種類型的查詢使用的訪問方法就稱爲 ref_or_null，這個 ref_or_null訪問方法的執行過程如下：

一文搞懂 MySQL 單表查詢的底層實現

可以看到，上邊的查詢相當於先分別從 idx_key1索引對應的 B+樹中找出 key1 IS NULL和 key1='abc'的兩個連續的記錄範圍，然後根據這些二級索引記錄中的 id值再回表查找完整的用戶記錄。

range

我們之前介紹的幾種訪問方法都是在對索引列與某一個常數進行等值比較的時候纔可能使用到（ ref_or_null比較奇特，還計算了值爲 NULL的情況），但是有時候我們面對的搜索條件更復雜，比如下邊這個查詢：

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 39);

我們當然還可以使用全表掃描的方式來執行這個查詢，不過也可以使用 二級索引+回表的方式執行，如果採用 二級索引+回表的方式來執行的話，那麼此時的搜索條件就不只是要求索引列與常數的等值匹配了，而是索引列需要匹配某個或某些範圍的值，在本查詢中 key2列的值只要匹配下列3個範圍中的任何一個就算是匹配成功了：

  • key2的值是 1438
  • key2的值是 6328
  • key2的值在 38和 79之間。

設計 MySQL的大叔把這種利用索引進行範圍匹配的訪問方法稱之爲： range。

小貼士：

此處所說的使用索引進行範圍匹配中的 索引 可以是聚簇索引，也可以是二級索引。

如果把這幾個所謂的 key2列的值需要滿足的 範圍在數軸上體現出來的話，那應該是這個樣子：

一文搞懂 MySQL 單表查詢的底層實現

也就是從數學的角度看，每一個所謂的範圍都是數軸上的一個 區間，3個範圍也就對應着3個區間：

  • 範圍1： key2=1438
  • 範圍2： key2=6328
  • 範圍3： key2∈[38,39]，注意這裏是閉區間。

我們可以把那種索引列等值匹配的情況稱之爲 單點區間，上邊所說的 範圍1和 範圍2都可以被稱爲單點區間，像 範圍3這種的我們可以稱爲連續範圍區間。

index

看下邊這個查詢：

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

由於 key_part2並不是聯合索引 idx_key_part最左索引列，所以我們無法使用 ref或者 range訪問方法來執行這個語句。但是這個查詢符合下邊這兩個條件：

  • 它的查詢列表只有3個列： key_part1, key_part2, key_part3，而索引 idx_key_part又包含這三個列。
  • 搜索條件中只有 key_part2列。這個列也包含在索引 idx_key_part中。

也就是說我們可以直接通過遍歷 idx_key_part索引的葉子節點的記錄來比較 key_part2='abc'這個條件是否成立，把匹配成功的二級索引記錄的 key_part1, key_part2, key_part3列的值直接加到結果集中就行了。由於二級索引記錄比聚簇索記錄小的多（聚簇索引記錄要存儲所有用戶定義的列以及所謂的隱藏列，而二級索引記錄只需要存放索引列和主鍵），而且這個過程也不用進行回表操作，所以直接遍歷二級索引比直接遍歷聚簇索引的成本要小很多，設計 MySQL的大叔就把這種採用遍歷二級索引記錄的執行方式稱之爲： index

all

最直接的查詢執行方式就是我們已經提了無數遍的全表掃描，對於 InnoDB表來說也就是直接掃描聚簇索引，設計 MySQL的大叔把這種使用全表掃描執行查詢的方式稱之爲： all

34張架構史上最全技術知識圖譜

相关文章