MySQL優化學習手札(四) 單表訪問方法

北冥有隻魚發表於2022-04-17
MySql優化
本篇是介紹MySQL執行計劃的鋪墊，今天終於想好了該如何組織這部分內容，先是大致介紹查詢的實現，再由此引出執行計劃。

概述

我們日常的查詢，基本可以分為三類:

  1. 單表查詢
  2. 子查詢
  3. 連線查詢

這三種可以組合，也可以分開，上面的順序也是我們學習SQL的順序，我們下面介紹其實現，也是按照上面這種順序。看本篇之前建議先看這個本篇的前幾篇:

  • MySQL優化學習手札(一)
  • MySQL優化學習手札(二)
  • MySQL優化學習手札(三)

當然如果你對MySQL的B+樹索引比較熟悉也可以不看。

單表訪問方法

我們寫了一個單表查詢的語句，MySQL是如何獲取我們查詢語句所對應的記錄的呢：

SELECT * FROM Student WHERE ID = 1;

我們忽略語法解析、連線建立這些步驟，這些都搞定了，那麼MySQL該如何定位記錄呢？MySQL中執行查詢的方式一共有以下兩種:

  • 全表掃描(一條記錄一條記錄的去比較)

  • 使用索引進行查詢，索引也有不同的型別，所以就算是使用索引進行查詢，也分為幾種不同的情況:

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

在MySQL中執行查詢語句的方式稱之為訪問方法或者訪問型別。

通過主鍵列等值匹配來定位記錄-const

SELECT * FROM Student WHERE ID = 1; ID是主鍵

Id是Student這張表的主鍵，這裡讓我們在回憶一下MySQL儲存資料的基本結構:

  • InnoDB將資料劃分為若干頁，以頁作為磁碟和記憶體之間互動的基本單位，InnoDB中頁的大小一般為16KB
  • InnoDB儲存引擎會自動為主鍵(如果沒有它會自動幫我們新增)建立聚簇索引，聚簇索引的葉子結點包含完整的使用者記錄。
  • 每個索引都對應一顆B+樹，B+樹分為好多層，最下邊一層是葉子結點，其餘的是內結點。所有的使用者記錄都儲存在B+樹的葉子結點。所有的目錄項記錄都儲存在內結點
  • 我們也可以為自己感興趣的列建立二級索引，二級索引的葉子結點包含的使用者記錄由索引列+主鍵組成，所以如果想通過二級索引來查詢完整的使用者記錄的話，需要通過回表操作，也就是在通過二級索引找到主鍵值之後再到聚簇索引中查詢完整的使用者記錄。

  • B+樹的每層結點都是按照索引列值的從小到達的順序排序而組成了雙向連結串列，而每個頁內的記錄(不論是使用者記錄還是目錄項記錄)都是按照索引列的值從小到達的順序而形成了一個單連結串列。如果是聯合索引的話，則頁面和記錄先按照聯合索引前邊的列排序，如果該列的值相同，再按照聯合索引後邊的列進行排序。

    也就是ID這一列是聚簇索引，同時按ID進行排序，所以這個相當快，可以先用定位到目錄項地行為這一列位於哪個資料頁，定位到之後，再用二分查詢定位這條記錄在哪個位置。現在讓我們為Student再加上一列name，併為該列建立唯一索引，我們去執行如下查詢:

select  * from student where name ='aa'

也同聚簇索引類似，但是name列由於是非聚簇索引列，葉子結點沒有完整的記錄，定位到name= ‘aa’這條記錄後，還用用這條記錄對應的主鍵去主鍵索引列去查完整的記錄。即便有回表的代價，MySQL的開發人員仍然認為這種查詢方式是非常快的，將這種訪問方法定義為: const,也就是常數級別。但如果查詢NULL值，情況就又有所不同:

select * from  student where name is null

因為唯一索引並不限制NULL值的數量，所以上面的查詢語句可能會訪問到多條記錄。

ref

用唯一索引列去查詢NULL值，會查詢到多行，這種情況和用非唯一索引列去匹配記錄類似，查詢步驟和用唯一索引列去查詢是一樣的，定位這條記錄在哪個資料頁，然後到具體的頁裡面去匹配記錄，由於我們是select * , 所以還要回表查詢。 如果匹配的記錄比較少，回表的代價還是比較低的，MySQL就更傾向於使用索引+回表的方式來查詢，採用二級索引進行等值查詢記錄的方式，MySQL將其定義為ref。

但對於某個包含多個索引列的二級索引列來說，只要最左邊的連續索引列是與常數的等值比較就可能採用ref的訪問方法。至於為什麼是可能原因在於還是成本的衡量，如果你是select * ， 這就要回表。如果查詢的記錄比較多，讓MySQL覺得與其索引列+回表還不如直接掃描全表的話。

現在讓我們再為Student再加: age, sex，同時為age、sex建一個普通的索引。如果我們查詢的語句寫成了下面這樣:

select * from student where age = '18' and sex >  '女'

這種訪問方法在MySQL中的查詢級別就不是ref，原因在於對sex這一列使用的是範圍查詢。

ref_or_null

根據普通索引進行匹配，但同時查詢該索引列為null的值，像下面這樣:

select * from  student where name = 'aa' and  name is  null

這種查詢級別在MySQL中我們稱之為ref_or_null.

range-範圍查詢

我們日常的開發中範圍查詢也是高頻出現，像下面這樣:

select * from student where age in ('96','18') OR (age >= 28 and key <= 79);

對於MySQL來說執行這個查詢有兩種選擇，省事速度慢自然是全表掃描，當然也可以使用二級索引+回表的方式。上面的條件是一個搜尋範圍，在MySQL中將這種查詢級別定義為range。

index

select sex,age from student where sex = '男'

age和sex組成的聯合索引中，age在前，sex和age上都有索引，那對於MySQL來說就有兩種選擇，一種是用sex上的索引+回表查出age。 另一種是遍歷sex和age對應的聯合索引，這種效率事實上更高，因為非主鍵索引只儲存了主鍵列和索引列，資料頁會更小，也不需要回表，代價更小。這種查詢級別在MySQL中被定義為index。

all

如其名，掃描全表。

該如何回表

前面我們嘮叨了回表這個詞，事實上行對於不同的查詢場景也有不同的回表策略。

  • 情況一, 查詢用到了兩個索引列，該如何回表:
select  *  from student where age > '50'  and name = '張三'

age 和 name 都是索引列, 該使用哪個索引呢，MySQL優化器一般會根據Student的統計資料來判斷到底使用哪個條件對應的二級索引中查詢掃描的行數會更少。然後將從二級所以呢中查詢的結果經過回表得到完整的使用者記錄，再根據另一個條件過濾記錄。一般情況來說，等值查詢比範圍匹配需要掃描的範圍更少，這裡假設查詢優化器使用name列進行查詢。所以對於上面的語句，查詢步驟如下:

根據name = ‘張三’去name對應的索引上去查詢對應二級索引的記錄。

然後回表二級索引對應的主鍵去聚簇索引中找到對應的完整的使用者記錄，再根據age > ‘50’進行過濾。

  • 情況三: 有的搜尋條件無法使用索引
我們再為Student 新增一個card的欄位，然後執行下面的查詢:
select * from student where age > 30 and card = '001'

對於這種情況，card上面沒有索引，MySQL會傾向於使用age上的索引然後回表查出記錄，再用card = ‘001’進行過濾。

那如果是or呢？

select * from student where age > 30 or card = '001'

這種情況就沒有辦法用到age上的索引，因為age索引上就只有索引列和主鍵，MySQL就可能會進行掃描。

索引合併

MySQL在一般情況下執行一個查詢是最多隻會用到單個二級所以呢，有一般就會有特殊情況，在一些特殊情況可能在一個查詢中使用到多個二級索引，使用到多個二級索引的這種情況，在MySQL中被稱為：index merge.

Intersection合併

下面的討論中暫時移除age身上的單獨索引列，僅保留age、sex的聯合索引。

Intersection 意為交集，簡單的理解就是一個查詢可以使用多個二級索引，將多個二級索引中查詢到的結果取交集:

select * from  Student  where age = '18'and sex = '男' and name = '張三'

age 和 name 上都有索引，那麼執行上面的語句，就有兩種執行方案供MySQL所選擇(不要提全表):

  • 選擇一個條件去對應的索引列上去查詢記錄，然後回表，用另一個條件過濾。
  • age索引列去查詢記錄，name索引列查詢記錄。由於所以儲存的有主鍵列，這兩個結果集求交集。

那哪種訪問方式成本比較低呢，一般情況下MySQL更傾向於選擇讀取多個二級索引的方式，因為讀取二級索引是順序I/O, 回表是隨機I/O.

所以如果只讀取一個二級索引時需要回表的記錄數特別多，而讀取多個二級索引之後取交集的記錄數非常少，這種情況回表的損耗可能就要比訪問多個索引更高。

下面兩個查詢就不能使用Intersection索引合併:

select * from Student where  age = '18' and sex = '男' and name > 'zhangsan'

我們分析一下為什麼這種情況為什麼就不能使用索引合併求交集，原因在於時間複雜度的問題，age是範圍匹配(age 和 sex建立聯合索引,)，掃描到的主鍵列未必有序，有序集合求交集的時間複雜度O(n)，無序集合求交集的時間複雜度為O(n^2).

下面的查詢是一樣的原因，不能用到索引合併:

select * from Student where  age = '18'  and name =  'zhangsan'

聯合索引age相同，按照sex進行排序，但是僅根據age這一列得到的記錄主鍵可能還是無序的，所以也無法用到索引合併。

主鍵列可以是範圍匹配的查詢:

select  * from Student where id > 1 and name = '張三'

上面不是說了範圍匹配無法用到索引合併嗎？ 但是索引儲存了索引列和主鍵，我們甚至可以認為這個只用到了name這一列，然後再回表。如個只用name列再回表的代價大於主鍵列和name列使用索引列合併的代價，那麼MySQL就可能傾向於使用id列和name進行索引合併。

Union合併

有求交集，就有求並集，這是一對雙生子。MySQL在某些特定去年高考下才可能會使用到Union索引合併:

  • 情況一: 二級索引列是等值匹配的情況，對於聯合索引來說，在聯合索引中的每個列都必須等值匹配，不能出現只匹配部分列的情況。
select * from student where name = 'a' or (age = '18' and sex = '男')

下面兩個查詢就不能進行Union索引

select * from student where name > 'a' or (age = '18' and sex = '男')
select * from student where name = 'a' or age = '18'

原因還是和排序有關，兩個集合進行排序。

  • 情況二: 主鍵列可以是範圍匹配
  • 情況三：使用Intersection 索引合併的搜尋條件
可以理解為兩個交集的並。

Sort-Union合併

Union索引合併的使用條件有點苛刻，必須保證各個二級索引列再進行等值匹配的條件下才可能被用到。

select * from Student  where name > 'a' and age < '25'

上面這個SQL語句就無法用到Union排序，原因在於從name和age這兩個列查到的主鍵值不是排好序的，但如果排序的代價不高呢，MySQL在代價不高的情況下會如下執行:

  • 先根據條件name > ‘a’ 取值，然後根據主鍵進行排序
  • 再根據age < ‘25’ 取值，然後根據主鍵進行排序。

拍好序剩下的操作就和Union索引合併方式就一樣的。

索引合併的注意事項

我們先為card屬性新增一個普通索引：

select * from student where name = '張三' and card = '001'

這個查詢之所以可能用到索引合併的原因在於，name 和 card 是兩個索引，兩個列要是一個索引就不用MySQL來合併了。這樣就不用讀B+樹了。

寫在最後

本篇其實也是看本文的參考資料，用自己的思路梳理了一下，做的學習筆記。

參考資料

  • 《MySQL 是怎樣執行的：從根兒上理解 MySQL》 小孩子煮

相關文章