SQL執行內幕：從執行原理看調優的本質

itzhai發表於2020-06-28
SQL

相信大家看過無數的MySQL調優經驗貼了，會告訴你各種調優手段，如：

  • 避免 select *；
  • join欄位走索引；
  • 慎用in和not in，用exists取代in；
  • 避免在where子句中對欄位進行函式操作；
  • 儘量避免更新聚集索引；
  • group by如果不需要排序，手動加上 order by null；
  • join選擇小表作為驅動表；
  • order by欄位儘量走索引...

其中有些手段也許跟隨者MySQL版本的升級過時了。我們真的需要背這些調優手段嗎？我覺得是沒有必要的，在掌握MySQL儲存架構和SQL執行原理的情況下，我們就很自然的明白，為什麼要提議這麼優化了，甚至能夠發現別人提的不太合理的優化手段。

在 洞悉MySQL底層架構：遊走在緩衝與磁碟之間 這篇文章中，我們已經介紹了MySQL的儲存架構，詳細對你在MySQL儲存、索引、緩衝、IO相關的調優經驗中有了一定的其實。

本文，我們重點講解常用的SQL的執行原理，從執行原理，以及MySQL內部對SQL的優化機制，來分析SQL要如何調優，理解為什麼要這樣...那樣...那樣...調優。

如果沒有特別說明，本文以MySQL5.7版本作為講解和演示。

閱讀完本文，您將瞭解到：

  • COUNT： MyISAM和InnoDB儲存引擎處理count的區別是什麼？
  • COUNT： count為何效能差？
  • COUNT： count有哪些書寫方式，怎麼count統計會快點？
  • ORDER BY： order by語句有哪些排序模式，以及每種排序模式的優缺點？
  • ORDER BY： order by語句會用到哪些排序演算法，在什麼場景下會選擇哪種排序演算法
  • ORDER BY： 如何檢視和分析sql的order by優化手段(執行計劃 + OPTIMIZER_TRACE日誌)
  • ORDER BY： 如何優化order by語句的執行效率？(思想：減小行查詢大小，儘量走索引，能夠走覆蓋索引最佳，可適當增加sort buffer記憶體大小)
  • JOIN： join走索引的情況下是如何執行的？
  • JOIN： join不走索引的情況下是如何執行的？
  • JOIN： MySQL對Index Nested-Loop Join做了什麼優化？(MMR，BKA)
  • JOIN： BNL演算法對快取會產生什麼影響？有什麼優化策略？
  • JOIN： 有哪些常用的join語句？
  • JOIN： 針對join語句，有哪些優化手段？
  • UNION： union語句執行原理是怎樣的？
  • UNION： union是如何去重的？
  • GROUP BY： group by完全走索引的情況下執行計劃如何？
  • GROUP BY： 什麼情況下group by會用到臨時表？什麼情況下會用到臨時表+排序？
  • GROUP BY： 對group by有什麼優化建議？
  • DISTINCT： distinct關鍵詞執行原理是什麼？
  • 子查詢： 有哪些常見的子查詢使用方式？
  • 子查詢： 常見的子查詢優化有哪些？
  • 子查詢： 真的要儘量使用關聯查詢取代子查詢嗎？
  • 子查詢：in 的效率真的這麼慢嗎？
  • 子查詢： MySQL 5.6之後對子查詢做了哪些優化？(SEMIJOIN，Materializatioin，Exists優化策略)
  • 子查詢： Semijoin有哪些優化策略，其中Materializatioin策略有什麼執行方式，為何要有這兩種執行方式？
  • 子查詢： 除了in轉Exists這種優化優化，MariaDB中的exists轉in優化措施有什麼作用？

1、count

儲存引擎的區別

  • MyISAM引擎每張表中存放了一個meta資訊，裡面包含了row_count屬性，記憶體和檔案中各有一份，記憶體的count變數值通過讀取檔案中的count值來進行初始化。[1]但是如果帶有where條件，還是必須得進行表掃描

  • InnoDB引擎執行count()的時候，需要把資料一行行從引擎裡面取出來進行統計。

下面我們介紹InnoDB中的count()。

count中的一致性檢視

InnoDB中為何不像MyISAM那樣維護一個row_count變數呢？

前面 洞悉MySQL底層架構：遊走在緩衝與磁碟之間 一文我們瞭解到，InnoDB為了實現事務，是需要MVCC支援的。MVCC的關鍵是一致性檢視。一個事務開啟瞬間，所有活躍的事務(未提交)構成了一個檢視陣列，InnoDB就是通過這個檢視陣列來判斷行資料是否需要undo到指定的版本。

如下圖，假設執行count的時候，一致性檢視得到當前事務能夠取到的最大事務ID DATA_TRX_ID=1002，那麼行記錄中事務ID超過1002都都要通過undo log進行版本回退，最終才能得出最終哪些行記錄是當前事務需要統計的：

row1是其他事務新插入的記錄，當前事務不應該算進去。所以最終得出，當前事務應該統計row2，row3。

執行count會影響其他頁面buffer pool的命中率嗎？

我們知道buffer pool中的LRU演算法是是經過改進的，預設情況下，舊子列表(old區)佔3/8，count載入的頁面一直往舊子列表中插入，在舊子列表中淘汰，不會晉升到新子列表中。所以不會影響其他頁面buffer pool的命中率。

count(主鍵)

count(主鍵)執行流程如下：

  • 執行器請求儲存引擎獲取資料；
  • 為了保證掃描資料量更少，儲存引擎找到最小的那顆索引樹獲取所有記錄，返回記錄的id給到server。返回記錄之前會進行MVCC及其可見性的判斷，只返回當前事務可見的資料；
  • server獲取到記錄之後，判斷id如果不為空，則累加到結果記錄中。

count(1)

count(1)與count(主鍵)執行流程基本一致，區別在於，針對查詢出的每一條記錄，不會取記錄中的值，而是直接返回一個"1"用於統計累加。統計了所有的行。

count(欄位)

與count(主鍵)類似，會篩選非空的欄位進行統計。如果欄位沒有新增索引，那麼會掃描聚集索引樹，導致掃描的資料頁會比較多，效率相對慢點。

count(*)

count(*)不會取記錄的值，與count(1)類似。

執行效率對比：count(欄位) < count(主鍵) < count(1)

2、order by

以下是我們本節作為演示例子的表，假設我們有如下表：

索引如下：

對應的idx_d索引結構如下(這裡我們做了一些誇張的手法，讓一個頁資料變小，為了展現在索引樹中的查詢流程)：

2.1、如何跟蹤執行優化

為了方便分析sql的執行流程，我們可以在當前session中開啟 optimizer_trace:

SET optimizer_trace='enabled=on';

然後執行sql，執行完之後，就可以通過以下堆疊資訊檢視執行詳情了：

SELECT * FROM information_schema.OPTIMIZER_TRACE\G;

以下是

select a, b, c, d from t20 force index(idx_abc)  where a=3 order by d limit 100,2;

的執行結果，其中符合a=3的有8457條記錄，針對order by重點關注以下屬性：

"filesort_priority_queue_optimization": {  // 是否啟用優先順序佇列
  "limit": 102,           // 排序後需要取的行數，這裡為 limit 100,2，也就是100+2=102
  "rows_estimate": 24576, // 估計參與排序的行數
  "row_size": 123,        // 行大小
  "memory_available": 32768,    // 可用記憶體大小，即設定的sort buffer大小
  "chosen": true          // 是否啟用優先順序佇列
},
...
"filesort_summary": {
  "rows": 103,                // 排序過程中會持有的行數
  "examined_rows": 8457,      // 參與排序的行數，InnoDB層返回的行數
  "number_of_tmp_files": 0,   // 外部排序時，使用的臨時檔案數量
  "sort_buffer_size": 13496,  // 記憶體排序使用的記憶體大小
  "sort_mode": "sort_key, additional_fields"  // 排序模式
}

2.1.1、排序模式

其中 sort_mode有如下幾種形式：

  • sort_key, rowid：表明排序緩衝區元組包含排序鍵值和原始錶行的行id，排序後需要使用行id進行回表，這種演算法也稱為original filesort algorithm(回表排序演算法)；
  • sort_key, additional_fields：表明排序緩衝區元組包含排序鍵值和查詢所需要的列，排序後直接從緩衝區元組取資料，無需回表，這種演算法也稱為modified filesort algorithm(不回表排序)；
  • sort_key, packed_additional_fields：類似上一種形式，但是附加的列(如varchar型別)緊密地打包在一起，而不是使用固定長度的編碼。

如何選擇排序模式

選擇哪種排序模式，與max_length_for_sort_data這個屬性有關，這個屬性預設值大小為1024位元組：

  • 如果查詢列和排序列佔用的大小超過這個值，那麼會轉而使用sort_key, rowid模式；
  • 如果不超過，那麼所有列都會放入sort buffer中，使用sort_key, additional_fields或者sort_key, packed_additional_fields模式；
  • 如果查詢的記錄太多，那麼會使用sort_key, packed_additional_fields對可變列進行壓縮。

2.1.2、排序演算法

基於參與排序的資料量的不同，可以選擇不同的排序演算法：

  • 如果排序取的結果很小，小於記憶體，那麼會使用優先順序佇列進行堆排序；

    • 例如，以下只取了前面10條記錄，會通過優先順序佇列進行排序：

    • select a, b, c, d from t20 force index(idx_abc)  where a=3 order by d limit 10;

  • 如果排序limit n, m，n太大了，也就是說需要取排序很後面的資料，那麼會使用sort buffer進行快速排序：

    • 如下，表中a=1的資料又三條，但是由於需要limit到很後面的記錄，MySQL會對比優先順序佇列排序和快速排序的開銷，選擇一個比較合適的排序演算法，這裡最終放棄了優先順序佇列，轉而使用sort buffer進行快速排序：

    • select a, b, c, d from t20 force index(idx_abc)  where a=1 order by d limit 300,2;

  • 如果參與排序的資料sort buffer裝不下了，那麼我們會一批一批的給sort buffer進行記憶體快速排序，結果放入排序臨時檔案，最終使對所有排好序的臨時檔案進行歸併排序，得到最終的結果;

    • 如下，a=3的記錄超過了sort buffer，我們要查詢的資料是排序後1000行起，sort buffer裝不下1000行資料了，最終MySQL選擇使用sort buffer進行分批快排，把最終結果進行歸併排序：

    • select a, b, c, d from t20 force index(idx_abc)  where a=3 order by d limit 1000,10;

2.2、order by走索引避免排序

執行如下sql：

select a, b, c, d from t20 force index(idx_d) where d like 't%' order by d limit 2;

我們看一下執行計劃：

發現Extra列為：Using index condition，也就是這裡只走了索引。

執行流程如下圖所示：

通過idx_d索引進行range_scan查詢，掃描到4條記錄，然後order by繼續走索引，已經排好序，直接取前面兩條，然後去聚集索引查詢完整記錄，返回最終需要的欄位作為查詢結果。這個過程只需要藉助索引。

如何檢視和修改sort buffer大小？

我們看一下當前的sort buffer大小：

可以發現，這裡預設配置了sort buffer大小為512k。

我們可以設定這個屬性的大小：

SET GLOBAL sort_buffer_size = 32*1024;

或者

SET sort_buffer_size = 32*1024;

下面我們統一把sort buffer設定為32k

SET sort_buffer_size = 32*1024;

2.3、排序演算法案例

2.3.1、使用優先順序佇列進行堆排序

如果排序取的結果很小，並且小於sort buffer，那麼會使用優先順序佇列進行堆排序；

例如，以下只取了前面10條記錄：

select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 10;

a=3的總記錄數：8520。檢視執行計劃：

發現這裡where條件用到了索引，order by limit用到了排序。我們進一步看看執行的optimizer_trace日誌：

"filesort_priority_queue_optimization": {
  "limit": 10,
  "rows_estimate": 27033,
  "row_size": 123,
  "memory_available": 32768,
  "chosen": true  // 使用優先順序佇列進行排序
},
"filesort_execution": [
],
"filesort_summary": {
  "rows": 11,
  "examined_rows": 8520,
  "number_of_tmp_files": 0,
  "sort_buffer_size": 1448,
  "sort_mode": "sort_key, additional_fields"
}

發現這裡是用到了優先順序佇列進行排序。排序模式是：sort_key, additional_fields，即先回表查詢完整記錄，把排序需要查詢的所有欄位都放入sort buffer進行排序。

所以這個執行流程如下圖所示：

  1. 通過where條件a=3掃描到8520條記錄；
  2. 回表查詢記錄；
  3. 把8520條記錄中需要的欄位放入sort buffer中；
  4. 在sort buffer中進行堆排序；
  5. 在排序好的結果中取limit 10前10條，寫入net buffer，準備傳送給客戶端。

2.3.2、內部快速排序

如果排序limit n, m，n太大了，也就是說需要取排序很後面的資料，那麼會使用sort buffer進行快速排序。MySQL會對比優先順序佇列排序和歸併排序的開銷，選擇一個比較合適的排序演算法。

如何衡量究竟是使用優先順序佇列還是記憶體快速排序？
一般來說，快速排序演算法效率高於堆排序，但是堆排序實現的優先順序佇列，無需排序完所有的元素，就可以得到order by limit的結果。
MySQL原始碼中宣告瞭快速排序速度是堆排序的3倍，在實際排序的時候，會根據待排序數量大小進行切換演算法。如果資料量太大的時候，會轉而使用快速排序。

有如下SQL：

select a, b, c, d from t20 force index(idx_abc)  where a=1 order by d limit 300,2;

我們把sort buffer設定為32k：

SET sort_buffer_size = 32*1024;

其中a=1的記錄有3條。檢視執行計劃：

可以發現，這裡where條件用到了索引，order by limit 用到了排序。我們進一步看看執行的optimizer_trace日誌：

"filesort_priority_queue_optimization": {
  "limit": 302,
  "rows_estimate": 27033,
  "row_size": 123,
  "memory_available": 32768,
  "strip_additional_fields": {
    "row_size": 57,
    "sort_merge_cost": 33783,
    "priority_queue_cost": 61158,
    "chosen": false  // 對比發現快速排序開銷成本比優先順序佇列更低，這裡不適用優先順序佇列
  }
},
"filesort_execution": [
],
"filesort_summary": {
  "rows": 3,
  "examined_rows": 3,
  "number_of_tmp_files": 0,
  "sort_buffer_size": 32720,
  "sort_mode": "<sort_key, packed_additional_fields>"
}

可以發現這裡最終放棄了優先順序佇列，轉而使用sort buffer進行快速排序。

所以這個執行流程如下圖所示：

  1. 通過where條件a=1掃描到3條記錄；
  2. 回表查詢記錄；
  3. 把3條記錄中需要的欄位放入sort buffer中；
  4. 在sort buffer中進行快速排序；
  5. 在排序好的結果中取limit 300, 2第300、301條記錄，寫入net buffer，準備傳送給客戶端。

2.3.3、外部歸併排序

當參與排序的資料太多，一次性放不進去sort buffer的時候，那麼我們會一批一批的給sort buffer進行記憶體排序，結果放入排序臨時檔案，最終使對所有排好序的臨時檔案進行歸併排序，得到最終的結果。

有如下sql：

select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 1000,10;

其中a=3的記錄有8520條。執行計劃如下：

可以發現，這裡where用到了索引，order by limit用到了排序。進一步檢視執行的optimizer_trace日誌：

"filesort_priority_queue_optimization": {
  "limit": 1010,
  "rows_estimate": 27033,
  "row_size": 123,
  "memory_available": 32768,
  "strip_additional_fields": {
    "row_size": 57,
    "chosen": false,
    "cause": "not_enough_space"  // sort buffer空間不夠，無法使用優先順序佇列進行排序了
  }
},
"filesort_execution": [
],
"filesort_summary": {
  "rows": 8520,
  "examined_rows": 8520,
  "number_of_tmp_files": 24,  // 用到了24個外部檔案進行排序
  "sort_buffer_size": 32720,
  "sort_mode": "<sort_key, packed_additional_fields>"
}

我們可以看到，由於limit 1000，要返回排序後1000行以後的記錄，顯然sort buffer已經不能支撐這麼大的優先順序佇列了，所以轉而使用sort buffer記憶體排序，而這裡需要在sort buffer中分批執行快速排序，得到多個排序好的外部臨時檔案，最終執行歸併排序。（外部臨時檔案的位置由tmpdir引數指定）

其流程如下圖所示：

2.4、排序模式案例

2.4.1、sort_key, additional_fields模式

sort_key, additional_fields，排序緩衝區元組包含排序鍵值和查詢所需要的列（先回表取需要的資料，存入排序緩衝區中），排序後直接從緩衝區元組取資料，無需再次回表。

上面 2.3.1、2.3.2節的例子都是這種排序模式，就不繼續舉例了。

2.4.2、<sort_key, packed_additional_fields>模式

sort_key, packed_additional_fields：類似上一種形式，但是附加的列(如varchar型別)緊密地打包在一起，而不是使用固定長度的編碼。

上面2.3.3節的例子就是這種排序模式，由於參與排序的總記錄大小太大了，因此需要對附加列進行緊密地打包操作，以節省記憶體。

2.4.3、<sort_key, rowid>模式

前面我們提到，選擇哪種排序模式，與max_length_for_sort_data[2]這個屬性有關，max_length_for_sort_data規定了排序行的最大大小，這個屬性預設值大小為1024位元組：

也就是說如果查詢列和排序列佔用的大小小於這個值，這個時候會走sort_key, additional_fields或者sort_key, packed_additional_fields演算法，否則，那麼會轉而使用sort_key, rowid模式。

現在我們特意把這個值設定小一點，模擬sort_key, rowid模式：

SET max_length_for_sort_data = 100;

這個時候執行sql：

select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 10;

這個時候再檢視sql執行的optimizer_trace日誌：

"filesort_priority_queue_optimization": {
  "limit": 10,
  "rows_estimate": 27033,
  "row_size": 49,
  "memory_available": 32768,
  "chosen": true
},
"filesort_execution": [
],
"filesort_summary": {
  "rows": 11,
  "examined_rows": 8520,
  "number_of_tmp_files": 0,
  "sort_buffer_size": 632,
  "sort_mode": "<sort_key, rowid>"
}

可以發現這個時候切換到了sort_key, rowid模式，在這個模式下，執行流程如下：

  1. where條件a=3掃描到8520條記錄；
  2. 回表查詢記錄；
  3. 找到這8520條記錄的id和d欄位，放入sort buffer中進行堆排序；
  4. 排序完成後，取前面10條；
  5. 取這10條的id回表查詢需要的a，b，c，d欄位值；
  6. 依次返回結果給到客戶端。

可以發現，正因為行記錄太大了，所以sort buffer中只存了需要排序的欄位和主鍵id，以時間換取空間，最終排序完成，再次從聚集索引中查詢到所有需要的欄位返回給客戶端，很明顯，這裡多了一次回表操作的磁碟讀，整體效率上是稍微低一點的。

2.5、order by優化總結

根據以上的介紹，我們可以總結出以下的order by語句的相關優化手段：

  • order by欄位儘量使用固定長度的欄位型別，因為排序欄位不支援壓縮；
  • order by欄位如果需要用可變長度，應儘量控制長度，道理同上；
  • 查詢中儘量不用用select *，避免查詢過多，導致order by的時候sort buffer記憶體不夠導致外部排序，或者行大小超過了max_length_for_sort_data導致走了sort_key, rowid排序模式，使得產生了更多的磁碟讀，影響效能；
  • 嘗試給排序欄位和相關條件加上聯合索引，能夠用到覆蓋索引最佳。

3、join

為了演示join，接下來我們需要用到這兩個表：

CREATE TABLE `t30` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY idx_a(a)
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE `t31` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `f` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY idx_a(a)
) ENGINE=InnoDB CHARSET=utf8mb4;

insert into t30(a,b,c) values(1, 1, 1),(12,2,2),(3,3,3),(11, 12, 31),(15,1,32),(33,33,43),(5,13,14),(4,13,14),(16,13,14),(10,13,14);

insert into t31(a,f,c) values(1, 1, 1),(21,2,2),(3,3,3),(12, 1, 1),(31,20,2),(4,10,3),(2,23,24),(22,23,24),(5,23,24),(20,23,24);

在MySQL官方文件中 8.8.2 EXPLAIN Output Format[3] 提到：MySQL使用Nested-Loop Loin演算法處理所有的關聯查詢。使用這種演算法，意味著這種執行模式：

  • 從第一個表中讀取一行，然後在第二個表、第三個表...中找到匹配的行，以此類推；
  • 處理完所有關聯的表後，MySQL將輸出選定的列，如果列不在當前關聯的索引樹中，那麼會進行回表查詢完整記錄；
  • 繼續遍歷，從表中取出下一行，重複以上步驟。

下面我們所講到的都是Nested-Loop Join演算法的不同實現。

多表join：不管多少個表join，都是用的Nested-Loop Join實現的。如果有第三個join的表，那麼會把前兩個表的join結果集作為迴圈基礎資料，在執行一次Nested-Loop Join，到第三個表中匹配資料，更多多表同理。

3.1、join走索引（Index Nested-Loop Join）

3.1.1、Index Nested-Loop Join

我們執行以下sql：

select * from t30 straight_join t31 on t30.a=t31.a;

檢視執行計劃：

可以發現：

  • t30作為驅動表，t31作為被驅動表；
  • 通過a欄位關聯，去t31表查詢資料的時候用到了索引。

該sql語句的執行流程如下圖：

  1. 首先遍歷t30聚集索引；
  2. 針對每個t30的記錄，找到a的值，去t31的idx_a索引中找是否存在記錄；
  3. 如果存在則拿到t30對應索引記錄的id回表查詢完整記錄；
  4. 分別取t30和t31的所有欄位作為結果返回。

由於這個過程中用到了idx_a索引，所以這種演算法也稱為：Index Nested-Loop (索引巢狀迴圈join)。其虛擬碼結構如下：

// A 為t30聚集索引
// B 為t31聚集索引
// BIndex 為t31 idx_a索引
void indexNestedLoopJoin(){
  List result;
  for(a in A) {
    for(bi in BIndex) {
      if (a satisfy condition bi) {
        output <a, b>;
      }
    }
  }
}

假設t30記錄數為m，t31記錄數為n，每一次查詢索引樹的複雜度為log2(n)，所以以上場景，總的複雜度為：m + m*2*log2(n)。

也就是說驅動表越小，複雜度越低，越能提高搜尋效率。

3.1.2、Index nested-Loop Join的優化

我們可以發現，以上流程，每次從驅動表取一條資料，然後去被驅動表關聯取數，表現為磁碟的隨記讀，效率是比較低低，有沒有優化的方法呢？

這個就得從MySQL的MRR（Multi-Range Read）[4]優化機制說起了。

3.1.2.1、Multi-Range Read優化

我們執行以下程式碼，強制開啟MMR功能：

set optimizer_switch="mrr_cost_based=off"

然後執行以下SQL，其中a是索引：

select * from t30 force index(idx_a) where a<=12 limit 10;

可以得到如下執行計劃：

可以發現，Extra列提示用到了MRR優化。

這裡為了演示走索引的場景，所以加了force index關鍵詞。

正常不加force index的情況下，MySQL優化器會檢查到這裡即使走了索引還是需要回表查詢，並且表中的資料量不多，那乾脆就直接掃描全表，不走索引，效率更加高了。

如果沒有MRR優化，那麼流程是這樣的：

  1. 在idx_a索引中找到a<10的記錄；
  2. 取前面10條，拿著id去回表查詢完整記錄，這裡回表查詢是隨機讀，效率較差；
  3. 取到的結果通過net buffer返回給客戶端。

使用了MRR優化之後，這個執行流程是這樣的：

  1. 在idx_abc索引中找到a<10的記錄；
  2. 取10條，把id放入read rnd buffer;
  3. read rnd buffer中的id排序；
  4. 排序之後回表查詢完整記錄，id越多，排好序之後越有可能產生連續的id，去磁碟順序讀；
  5. 查詢結果寫入net buffer返回給客戶端；

3.1.2.2、Batched Key Access

與Multi-Range Read的優化思路類似，MySQL也是通過把隨機讀改為順序讀，讓Index Nested-Loop Join提升查詢效率，這個演算法稱為Batched Key Access(BKA)[5]演算法。

我們知道，預設情況下，是掃描驅動表，一行一行的去被驅動表匹配記錄。這樣就無法觸發MRR優化了，為了能夠觸發MRR，於是BKA演算法登場了。

在BKA演算法中，驅動表通過使用join buffer批量在被驅動表的輔助索引中關聯匹配資料，得到一批結果，一次性傳遞個資料庫引擎的MRR介面，從而可以利用到MRR對磁碟讀的優化。

為了啟用這個演算法，我們執行以下命令（BKA依賴於MRR）：

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

我們再次執行以下關聯查詢sql：

select * from t30 straight_join t31 on t30.a=t31.a;

我們可以得到如下的執行計劃：

可以發現，這裡用到了：Using join buffer(Batched Key Access)。

執行流程如下：

  1. 把驅動表的資料批量放入join buffer中；
  2. 在join buffer中批與被驅動表的輔助索引匹配結果，得到一個結果集；
  3. 把上一步的結果集批量提交給引擎的MRR介面；
  4. MRR介面處理同上一節，主要進行了磁碟順序讀的優化；
  5. 組合輸出最終結果，可以看到，這裡的結果與沒有開啟BKA優化的順序有所不同，這裡使用了t31被驅動表的id排序作為輸出順序，因為最後一步對被驅動表t31讀取進行MRR優化的時候做了排序。

如果join條件沒走索引，又會是什麼情況呢，接下來我們嘗試執行下對應的sql。

3.2、join不走索引（Block Nested-Loop Join）

3.2.1、Block Nested-Loop Join (BNL)

我們執行以下sql：

select * from t30 straight_join t31 on t30.c=t31.c;

檢視執行計劃：

可以發現：

  • t30作為驅動表，t31作為被驅動表；
  • 通過c欄位關聯，去t31表查詢資料的時候沒有用到索引；
  • join的過程中用到了join buffer，這裡提示用到了Block Nested Loop Join；

該語句的執行流程如下圖：

  1. t30驅動表中的資料分批(分塊)存入join buffer，如果一次可以全部存入，則這裡會一次性存入；
  2. t31被驅動表中掃描記錄，依次取出與join buffer中的記錄對比(記憶體中對比，快)，判斷是否滿足c相等的條件；
  3. 滿足條件的記錄合併結果輸出到net buffer中，最終傳輸給客戶端。

然後清空join buffer，存入下一批t30的資料，重複以上流程。

顯然，每批資料都需要掃描一遍被驅動表，批次越多，掃描越多，但是記憶體判斷總次數是不變的。所以總批次越小，越高效。所以，跟上一個演算法一樣，驅動表越小，複雜度越低，越能提高搜尋效率。

3.2.2、BNL問題

在 洞悉MySQL底層架構：遊走在緩衝與磁碟之間 一文中，我們介紹了MySQL Buffer Pool的LRU演算法，如下：

預設情況下，同一個資料頁，在一秒鐘之後再次訪問，那麼就會晉升到新子列表(young區)。

恰巧，如果我們用到了BNL演算法，那麼分批執行的話，就會重複掃描被驅動表去匹配每一個批次了。

考慮以下兩種會影響buffer pool的場景：

  • 如果這個時候join掃描了一個很大的冷表，那麼在join這段期間，會持續的往舊子列表(old區)寫資料頁，淘汰隊尾的資料頁，這會影響其他業務資料頁晉升到新子列表，因為很可能在一秒內，其他業務資料就從舊子列表中被淘汰掉了；
  • 而如果這個時候BNL演算法把驅動表分為了多個批次，每個批次掃描匹配被驅動表，都超過1秒鐘，那麼這個時候，被驅動表的資料頁就會被晉升到新子列表，這個時候也會把其他業務的資料頁提前從新子列表中淘汰掉。

3.2.3、BNL問題解決方案

3.2.3.1、調大 join_buffer_size

針對以上這種場景，為了避免影響buffer pool，最直接的辦法就是增加join_buffer_size的值，以減少對被驅動表的掃描次數。

3.2.3.2、把BNL轉換為BKA

我們可以通過把join的條件加上索引，從而避免了BNL演算法，轉而使用BKA演算法，這樣也可以加快記錄的匹配速度，以及從磁碟讀取被驅動表記錄的速度。

3.2.3.3、通過新增臨時表

有時候，被驅動表很大，但是關聯查詢又很少使用，直接給關聯欄位加索引太浪費空間了，這個時候就可以通過把被驅動表的資料放入臨時表，在零時表中新增索引的方式，以達成3.2.3.2的優化效果。

3.2.3.4、使用hash join

什麼是hash join呢，簡單來說就是這樣的一種模型：

把驅動表滿足條件的資料取出來，放入一個hash結構中，然後把被驅動表滿足條件的資料取出來，一行一行的去hash結構中尋找匹配的資料，依次找到滿足條件的所有記錄。

一般情況下，MySQL的join實現都是以上介紹的各種nested-loop演算法的實現，但是從MySQL 8.0.18[6]開始，我們可以使用hash join來實現表連續查詢了。感興趣可以進一步閱讀這篇文章進行了解：[Hash join in MySQL 8 | MySQL Server Blog](https://mysqlserverteam.com/hash-join-in-mysql-8/#:~:text=MySQL only supports inner hash,more often than it does.)

3.3、各種join

我們在平時工作中，會遇到各種各樣的join語句，主要有如下：

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

LEFT JOIN EXCLUDING INNER JOIN

RIGHT JOIN EXCLUDING INNER JOIN

OUTER JOIN EXCLUDING INNER JOIN

更詳細的介紹，可以參考：

3.3、join使用總結

  • join優化的目標是儘可能減少join中Nested-Loop的迴圈次數，所以請讓小表做驅動表；
  • 關聯欄位儘量走索引，這樣就可以用到Index Nested-Loop Join了；
  • 如果有order by，請使用驅動表的欄位作為order by，否則會使用 using temporary；
  • 如果不可避免要用到BNL演算法，為了減少被驅動表多次掃描導致的對Buffer Pool利用率的影響，那麼可以嘗試把 join_buffer_size調大；
  • 為了進一步加快BNL演算法的執行效率，我們可以給關聯條件加上索引，轉換為BKA演算法；如果加索引成本較高，那麼可以通過臨時表新增索引來實現；
  • 如果您使用的是MySQL 8.0.18，可以嘗試使用hash join，如果是較低版本，也可以自己在程式中實現一個hash join。

4、union

通過使用union可以把兩個查詢結果合併起來，注意：

union all不會去除重複的行，union則會去除重複讀的行。

4.1、union all

執行下面sql：

(select id from t30 order by id desc limit 10) union all (select c from t31 order by id desc limit 10)

該sql執行計劃如下圖：

執行流程如下：

  1. 從t30表查詢出結果，直接寫出到net buffer，傳回給客戶端；
  2. 從331表查詢出結果，直接寫出到net buffer，傳回給客戶端。

4.2、union

執行下面sql：

(select id from t30 order by id desc limit 10) union (select c from t31 order by id desc limit 10)

該sql執行計劃如下圖：

執行流程如下：

  1. 從t30查詢出記錄，寫入到臨時表；
  2. 從t30查詢出記錄，寫入臨時表，在臨時表中通過唯一索引去重；
  3. 把臨時表的資料通過net buffer返回給客戶端。

5、group by

5.1、完全走索引

我們給t30加一個索引：

alter table t30 add index idx_c(c);

執行以下group bysql：

select c, count(*) from t30 group by c;

執行計劃如下：

發現這裡只用到了索引，原因是idx_c索引本身就是按照c排序好的，那麼直接順序掃描idx_c索引，可以直接統計到每一個c值有多少條記錄，無需做其他的統計了。

5.2、臨時表

現在我們把剛剛的idx_c索引給刪掉，執行以下sql：

select c, count(*) from t30 group by c order by null;

為了避免排序，所以我們這裡新增了 order by null，表示不排序。

執行計劃如下：

可以發現，這裡用到了記憶體臨時表。其執行流程如下：

  1. 掃描t30聚集索引；
  2. 建立一個臨時表，以欄位c為主鍵，依次把掃描t30的記錄通過臨時表的欄位c進行累加；
  3. 把最後累加得到的臨時表返回給客戶端。

5.3、臨時表 + 排序

如果我們把上一步的order by null去掉，預設情況下，group by的結果是會通過c欄位排序的。我們看看其執行計劃：

可以發現，這裡除了用到臨時表，還用到了排序。

我們進一步看看其執行的OPTIMIZER_TRACE日誌：

"steps": [
  {
    "creating_tmp_table": {
      "tmp_table_info": {
        "table": "intermediate_tmp_table",  // 建立中間臨時表
        "row_length": 13,
        "key_length": 4,
        "unique_constraint": false,
        "location": "memory (heap)",
        "row_limit_estimate": 1290555
      }
    }
  },
  {
    "filesort_information": [
      {
        "direction": "asc",
        "table": "intermediate_tmp_table",
        "field": "c"
      }
    ],
    "filesort_priority_queue_optimization": {
      "usable": false,
      "cause": "not applicable (no LIMIT)" // 由於沒有 limit，不採用優先順序佇列排序
    },
    "filesort_execution": [
    ],
    "filesort_summary": {
      "rows": 7,
      "examined_rows": 7,
      "number_of_tmp_files": 0,
      "sort_buffer_size": 344,
      "sort_mode": "<sort_key, rowid>"  // rowid排序模式
    }
  }
]

通過日誌也可以發現，這裡用到了中間臨時表，由於沒有limit限制條數，這裡沒有用到優先順序佇列排序，這裡的排序模式為sort_key, rowid。其執行流程如下：

  1. 掃描t30聚集索引；
  2. 建立一個臨時表，以欄位c為主鍵，依次把掃描t30的記錄通過臨時表的欄位c進行累加；
  3. 把得到的臨時表放入sort buffer進行排序，這裡通過rowid進行排序；
  4. 通過排序好的rowid回臨時表查詢需要的欄位，返回給客戶端。

臨時表是存放在磁碟還是記憶體？

tmp_table_size 引數用於設定記憶體臨時表的大小，如果臨時表超過這個大小，那麼會轉為磁碟臨時表：

可以通過以下sql設定當前session中的記憶體臨時表大小：SET tmp_table_size = 102400;

5.5、直接排序

檢視官方文件的 SELECT Statement[9]，可以發現SELECT後面可以使用許多修飾符來影響SQL的執行效果：

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [into_option]
    [FOR UPDATE | LOCK IN SHARE MODE]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

這裡我們重點關注下這兩個：

  • SQL_BIG_RESULT：可以在包含group by 和distinct的SQL中使用，提醒優化器查詢資料量很大，這個時候MySQL會直接選用磁碟臨時表取代記憶體臨時表，避免執行過程中發現記憶體不足才轉為磁碟臨時表。這個時候更傾向於使用排序取代二維臨時表統計結果。後面我們會演示這樣的案例；
  • SQL_SMALL_RESULT：可以在包含group by 和distinct的SQL中使用，提醒優化器資料量很小，提醒優化器直接選用記憶體臨時表，這樣會通過臨時表統計，而不是排序。

當然，在平時工作中，不是特定的調優場景，以上兩個修飾符還是比較少用到的。

接下來我們就通過例子來說明下使用了SQL_BIG_RESULT修飾符的SQL執行流程。

有如下SQL：

select SQL_BIG_RESULT c, count(*) from t30 group by c;

執行計劃如下：

可以發現，這裡只用到了排序，沒有用到索引或者臨時表。這裡用到了SQL_BIG_RESULT修飾符，告訴優化器group by的資料量很大，直接選用磁碟臨時表，但磁碟臨時表儲存效率不高，最終優化器使用陣列排序的方式來完成這個查詢。（當然，這個例子實際的結果集並不大，只是作為演示用）

其執行結果如下：

  1. 掃描t30表，逐行的把c欄位放入sort buffer；
  2. 在sort buffer中對c欄位進行排序，得到一個排序好的c陣列；
  3. 遍歷這個排序好的c陣列，統計結果並輸出。

5.4、group by 優化建議

  • 儘量讓group by走索引，能最大程度的提高效率；
  • 如果group by結果不需要排序，那麼可以加上group by null，避免進行排序；
  • 如果group by的資料量很大，可以使用SQL_BIG_RESULT修飾符，提醒優化器應該使用排序演算法得到group的結果。

6、distinct[10]

在大多數情況下，DISTINCT可以考慮為GROUP BY的一個特殊案例，如下兩個SQL是等效的：

select distinct a, b, c from t30;

select a, b, c from t30 group by a, b, c order by null;

這兩個SQL的執行計劃如下：

由於這種等效性，適用於Group by的查詢優化也適用於DISTINCT。

區別：distinct是在group by之後的每組中取出一條記錄，distinct分組之後不進行排序。

6.1、Extra中的distinct

在一個關聯查詢中，如果您只是查詢驅動表的列，並且在驅動表的列中宣告瞭distinct關鍵字，那麼優化器會進行優化，在被驅動表中查詢到匹配的第一行時，將停止繼續掃描。如下SQL：

explain select distinct t30.a  from t30, t31 where t30.c=t30.c;

執行計劃如下，可以發現Extra列中有一個distinct，該標識即標識用到了這種優化[10:1]：

7、子查詢

首先，我們來明確幾個概念：

子查詢：可以是巢狀在另一個查詢(select insert update delete)內，子查詢也可以是巢狀在另一個子查詢裡面。

MySQL子查詢稱為內部查詢，而包含子查詢的查詢稱為外部查詢。子查詢可以在使用表示式的任何地方使用。

接下來我們使用以下表格來演示各種子查詢：

create table class (
  id bigint not null auto_increment,
  class_num varchar(10) comment '課程編號',
  class_name varchar(100) comment '課程名稱',
  pass_score integer comment '課程及格分數',
  primary key (id)
) comment '課程';

create table student_class (
  id bigint not null auto_increment,
  student_name varchar(100) comment '學生姓名',
  class_num varchar(10) comment '課程編號',
  score integer comment '課程得分',
  primary key (id)
) comment '學生選修課程資訊';

insert into class(class_num, class_name, pass_score) values ('C001','語文', 60),('C002','數學', 70),('C003', '英文', 60),('C004', '體育', 80),('C005', '音樂', 60),('C006', '美術', 70);

insert into student_class(student_name, class_num, score) values('James', 'C001', 80),('Talor', 'C005', 75),('Kate', 'C002', 65),('David', 'C006', 82),('Ann', 'C004', 88),('Jan', 'C003', 70),('James', 'C002', 97), ('Kate', 'C005', 90), ('Jan', 'C005', 86), ('Talor', 'C006', 92);

子查詢的用法比較多，我們先來列舉下有哪些子查詢的使用方法。

7.1、子查詢的使用方法

7.1.1、where中的子查詢

7.1.1.1、比較運算子

可以使用比較運演算法，例如=，>，<將子查詢返回的單個值與where子句表示式進行比較，如

查詢學生選擇的編號最大的課程資訊：

SELECT class.* FROM class WHERE class.class_num = ( SELECT MAX(class_num) FROM student_class );

7.1.1.2、in和not in

如果子查詢返回多個值，則可以在WHERE子句中使用其他運算子，例如IN或NOT IN運算子。如

查詢學生都選擇了哪些課程：

SELECT class.* FROM class WHERE class.class_num IN ( SELECT DISTINCT class_num FROM student_class );

7.1.2、from子查詢

在FROM子句中使用子查詢時，從子查詢返回的結果集將用作臨時表。該表稱為派生表或例項化子查詢。如 查詢最熱門和最冷門的課程分別有多少人選擇：

SELECT max(count), min(count) FROM (SELECT class_num, count(1) as count FROM student_class group by class_num) as t1;

7.1.3、關聯子查詢

前面的示例中，您注意到子查詢是獨立的。這意味著您可以將子查詢作為獨立查詢執行。

與獨立子查詢不同，關聯子查詢是使用外部查詢中的資料的子查詢。換句話說，相關子查詢取決於外部查詢。對於外部查詢中的每一行，對關聯子查詢進行一次評估。

下面是比較運算子中的一個關聯子查詢。

查詢每門課程超過平均分的學生課程記錄：

SELECT t1.* FROM student_class t1 WHERE t1.score > ( SELECT AVG(score) FROM student_class t2 WHERE t1.class_num = t2.class_num);

關聯子查詢中，針對每一個外部記錄，都需要執行一次子查詢，因為每一條外部記錄的class_num可能都不一樣。

7.1.3.1、exists和not exists

當子查詢與EXISTS或NOT EXISTS運算子一起使用時，子查詢將返回布林值TRUE或FALSE。

查詢所有學生總分大於100分的課程：

select * from class t1 
where exists(
  select sum(score) as total_score from student_class t2 
  where t2.class_num=t1.class_num group by t2.class_num having total_score > 100
)

7.2、子查詢的優化

上面我們演示了子查詢的各種用法，接下來，我們來講一下子查詢的優化[11]。

子查詢主要由以下三種優化手段：

  • Semijoin，半連線轉換，把子查詢sql自動轉換為semijion；
  • Materialization，子查詢物化；
  • EXISTS策略，in轉exists；

其中Semijoin只能用於IN，= ANY，或者EXISTS的子查詢中，不能用於NOT IN，<> ALL，或者NOT EXISTS的子查詢中。

下面我們做一下詳細的介紹。

真的要儘量使用關聯查詢取代子查詢嗎？

在《高效能MySQL》[12]一書中，提到：優化子查詢最重要的建議就是儘可能使用關聯查詢代替，但是，如果使用的是MySQL 5.6或者更新版本或者MariaDB，那麼就可以直接忽略這個建議了。因為這些版本對子查詢做了不少的優化，後面我們會重點介紹這些優化。

in的效率真的這麼慢嗎？

在MySQL5.6之後是做了不少優化的，下面我們就逐個來介紹。

7.2.1、Semijoin

Semijoin[13]，半連線，所謂半連線，指的是一張表在另一張表棧道匹配的記錄之後，返回第一張表的記錄。即使右邊找到了幾條匹配的記錄，也最終返回左邊的一條。

所以，半連線非常適用於查詢兩個表之間是否存在匹配的記錄，而不關注匹配了多少條記錄這種場景。

半連線通常用於IN或者EXISTS語句的優化。

7.2.1.1、優化場景

上面我們講到：接非常適用於查詢兩個表之間是否存在匹配的記錄，而不關注匹配了多少條記錄這種場景。

in關聯子查詢

這種場景，如果使用in來實現，可能會是這樣：

SELECT class_num, class_name
    FROM class
    WHERE class_num IN
        (SELECT class_num FROM student_class where condition);

在這裡，優化器可以識別出IN子句要求子查詢僅從student_class表返回唯一的class_num。在這種情況下，查詢會自動優化為使用半聯接。

如果使用exists來實現，可能會是這樣：

SELECT class_num, class_name
    FROM class
    WHERE EXISTS
        (SELECT * FROM student_class WHERE class.class_num = student_class.class_num);

優化案例

統計有學生分數不及格的課程：

SELECT t1.class_num, t1.class_name
    FROM class t1
    WHERE t1.class_num IN
        (SELECT t2.class_num FROM student_class t2 where t2.score < t1.pass_score);

我們可以通過執行以下指令碼，檢視sql做了什麼優化：

explain extended SELECT t1.class_num, t1.class_name FROM class t1 WHERE t1.class_num IN         (SELECT t2.class_num FROM student_class t2 where t2.score < t1.pass_score);
show warnings\G;

得到如下執行執行計劃，和SQL重寫結果：

從這個SQL重寫結果中，可以看出，最終子查詢變為了semi join語句：

/* select#1 */ select `test`.`t1`.`class_num` AS `class_num`,`test`.`t1`.`class_name` AS `class_name` 
from `test`.`class` `t1` 
semi join (`test`.`student_class` `t2`) where ((`test`.`t2`.`class_num` = `test`.`t1`.`class_num`) and (`test`.`t2`.`score` < `test`.`t1`.`pass_score`))

而執行計劃中，我們看Extra列：

Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)

Using join buffer這項是在join關聯查詢的時候會用到，前面講join語句的時候已經介紹過了，現在我們重點看一下FirstMatch(t1)這個優化項。

FirstMatch(t1)是Semijoin優化策略中的一種。下面我們詳細介紹下Semijoin有哪些優化策略。

7.2.1.2、Semijoin優化策略

MySQL支援5中Semijoin優化策略，下面逐一介紹。

7.2.1.2.1、FirstMatch

在內部表尋找與外部表匹配的記錄，一旦找到第一條，則停止繼續匹配。

案例 - 統計有學生分數不及格的課程：

SELECT t1.class_num, t1.class_name
    FROM class t1
    WHERE t1.class_num IN
        (SELECT t2.class_num FROM student_class t2 where t2.score < t1.pass_score);

執行計劃：

執行流程，圖比較大，請大家放大觀看：

  1. 掃描class表，把class表分批放入join buffer中，分批處理；
  2. 在批次中依次取出每一條記錄，在student_class表中掃描查詢符合條件的記錄，如果找到，則立刻返回，並從該條匹配的class記錄取出查詢欄位返回；
  3. 依次繼續掃描遍歷。

您也可以去MariaDB官網，檢視官方的FirstMatch Strategy[14]解釋。

7.2.1.2.2、Duplicate Weedout

將Semijoin作為一個常規的inner join，然後通過使用一個臨時表去重。

具體演示案例，參考MariaDB官網：DuplicateWeedout Strategy[15]，以下是官網例子的圖示：

可以看到，灰色區域為臨時表，通過臨時表唯一索引進行去重。

7.2.1.2.3、LooseScan

把內部表的資料基於索引進行分組，取每組第一條資料進行匹配。

具體演示案例，參考MariaDB官網：LooseScan Strategy[16]，以下是官網例子的圖示：

7.2.1.4、Materialization[17]

如果子查詢是獨立的(非關聯子查詢)，則優化器可以選擇將獨立子查詢產生的結果儲存到一張物化臨時表中。

為了觸發這個優化，我們需要往表裡面新增多點資料，好讓優化器認為這個優化是有價值的。

我們執行以下SQL：

select * from class t1 where t1.class_num in(select t2.class_num from student_class t2 where t2.score > 80) and t1.class_num like 'C%';

執行流程如下：

  1. 執行子查詢：通過where條件從student_class 表中找出符合條件的記錄，把所有記錄放入物化臨時表；
  2. 通過where條件從class表中找出符合條件的記錄，與物化臨時表進行join操作。

物化表的唯一索引

MySQL會報物化子查詢所有查詢欄位組成一個唯一索引，用於去重。如上面圖示，灰色連線的兩條記錄衝突去重了。

join操作可以從兩個方向執行：

  • 從物化表關聯class表，也就是說，掃描物化表，去與class表記錄進行匹配，這種我們稱為Materialize-scan；
  • 從class表關聯物化表，也就是，掃描class表，去物化表中查詢匹配記錄，這種我們稱為Materialize-lookup，這個時候，我們用到了物化表的唯一索引進行查詢，效率會很快。

下面我們介紹下這兩種執行方式。

Materialize-lookup

還是以上面的sql為例：

select * from class t1 where t1.class_num in(select t2.class_num from student_class t2 where t2.score > 80) and t1.class_num like 'C%';

執行計劃如下：

可以發現：

  • t2表的select_type為MATERIALIZED，這意味著id=2這個查詢結果將儲存在物化臨時表中。並把該查詢的所有欄位作為臨時表的唯一索引，防止插入重複記錄；
  • id=1的查詢接收一個subquery2的表名，這個表正式我們從id=2的查詢得到的物化表。
  • id=1的查詢首先掃描t1表，依次拿到t1表的每一條記錄，去subquery2執行eq_ref，這裡用到了auto_key，得到匹配的記錄。

也就是說，優化器選擇了對t1(class)表進行全表掃描，然後去物化表進行所以等值查詢，最終得到結果。

執行模型如下圖所示：

原則：小表驅動大表，關聯欄位被驅動表新增索引

如果子查詢查出來的物化表很小，而外部表很大，並且關聯欄位是外部表的索引欄位，那麼優化器會選擇掃描物化表去關聯外部表，也就是Materialize-scan，下面演示這個場景。

Materialize-scan

現在我們嘗試給class表新增class_num唯一索引：

alter table class add unique uk_class_num(class_num);

並且在class中插入更多的資料。然後執行同樣的sql，得到以下執行計劃：

可以發現，這個時候id=1的查詢是選擇了subquery2，也就是物化表進行掃描，掃描結果逐行去t1表(class)進行eq_ref匹配，匹配過程中用到了t1表的索引。

這裡的執行流程正好與上面的相反，選擇了從class表關聯物化表。

現在，我問大家：Materialization策略什麼時候會選擇從外部表關聯內部表？相信大家心裡應該有答案了。

執行模型如下：

原則：小表驅動大表，關聯欄位被驅動表新增索引

現在留給大家另一個問題：以上例子中，這兩種Materialization的開銷分別是多少(從行讀和行寫的角度統計)

答案：

Materialize-lookup：40次讀student_class表，40次寫物化臨時表，42次讀外部表，40次lookup檢索物化臨時表；

Materialize-scan：15次讀student_class表，15次寫物化臨時表，15次掃描物化臨時表，執行15次class表索引查詢。

7.2.2、Materialization

優化器使用Materialization(物化)來實現更加有效的子查詢處理。物化針對非關聯子查詢進行優化。

物化通過把子查詢結果儲存為臨時表（通常在記憶體中）來加快查詢的執行速度。MySQL在第一次獲取子查詢結果時，會將結果物化為臨時表。隨後如果再次需要子查詢的結果，則直接從臨時表中讀取。

優化器可以使用雜湊索引為臨時表建立索引，以使查詢更加高效，並且通過索引來消除重複項，讓表保持更小。

子查詢物化的臨時表在可能的情況下儲存在記憶體中，如果表太大，則會退回到磁碟上進行儲存。

為何要使用物化優化

如果未開啟物化優化，那麼優化器有時會將非關聯子查詢重寫為關聯子查詢。

可以通過以下命令查詢優化開關(Switchable Optimizations[18])狀態：

SELECT @@optimizer_switch\G;

也就是說，如下的in獨立子查詢語句：

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

會重寫為exists關聯子查詢語句：

SELECT * FROM t1
WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);

開啟了物化開關之後，獨立子查詢避免了這樣的重寫，使得子查詢只會查詢一次，而不是重寫為exists語句導致外部每一行記錄都會執行一次子查詢，嚴重降低了效率。

7.2.3、EXISTS策略

考慮以下的子查詢：

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

MySQL“從外到內”來評估查詢。也就是說，它首先獲取外部表示式outer_expr的值，然後執行子查詢並獲取其產生的結果集用於比較。

7.2.3.1、condition push down 條件下推

如果我們可以把outer_expr下推到子查詢中進行條件判斷，如下：

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

這樣就能夠減少子查詢的行數了。相比於直接用IN來說，這樣就可以加快SQL的執行效率了。

而涉及到NULL值的處理，相對就比較複雜，由於篇幅所限，這裡作為延伸學習，感興趣的朋友可以進一步閱讀：

8.2.2.3 Optimizing Subqueries with the EXISTS Strategy[19]

延伸：
除了讓關聯的in子查詢轉為exists進行優化之外。在MariaDB 10.0.2版本中，引入了另一種相反的優化措施：可以讓exists子查詢轉換為非關聯in子查詢，這樣就可以用上非關聯資產性的物化優化策略了。

詳細可以閱讀：EXISTS-to-IN Optimization[20]

7.2.4、總結

總結一下子查詢的優化方式：

  • 首先優先使用Semijoin來進行優化，消除子查詢，通常選用FirstMatch策略來做表連線；
  • 如果不可以使用Semijoin進行優化，並且當前子查詢是非關聯子查詢，則會物化子查詢，避免多次查詢，同時這一步的優化會遵循選用小表作為驅動表的原則，儘量走索引欄位關聯，分為兩種執行方式：Materialize-lookup，Materialization-scan。通常會選用雜湊索引為物化臨時表提高檢索效率；
  • 如果子查詢不能物化，那就只能考慮Exists優化策略了，通過condition push down把條件下推到exists子查詢中，減少子查詢的結果集，從而達到優化的目的。

8、limit offset, rows

limit的用法：

limit [offset], [rows]

其中 offset表示偏移量，rows表示需要返回的行數。

offset  limit  表中的剩餘資料
 _||_   __||__   __||__
|    | |      | |
RRRRRR RRRRRRRR RRR...
       |______|
          ||
         結果集

8.1、執行原理

MySQL進行表掃描，讀取到第 offset + rows條資料之後，丟棄前面offset條記錄，返回剩餘的rows條記錄。

比如以下sql：

select * from t30 order by id limit 10000, 10;

這樣總共會掃描10010條。

8.2、優化手段

如果查詢的offset很大，避免直接使用offset，而是通過id到聚集索引中檢索查詢。

  1. 利用自增索引，如：
select * from t30 where id > 10000 limit 10;

當然，這也是會有問題的，如果id中間產生了非連續的記錄，這樣定位就不準確了。寫到這裡，篇幅有點長了，最後這個問題留給大家思考，感興趣的朋友可以進一步思考探討與延伸。

這篇文章的內容就差不多介紹到這裡了，能夠閱讀到這裡的朋友真的是很有耐心，為你點個贊。

本文為arthinking基於相關技術資料和官方文件撰寫而成，確保內容的準確性，如果你發現了有何錯漏之處，煩請高抬貴手幫忙指正，萬分感激。

大家可以關注我的部落格：itzhai.com 獲取更多文章，我將持續更新後端相關技術，涉及JVM、Java基礎、架構設計、網路程式設計、資料結構、資料庫、演算法、併發程式設計、分散式系統等相關內容。

如果您覺得讀完本文有所收穫的話，可以關注我的賬號，或者點贊吧，碼字不易，您的支援就是我寫作的最大動力，再次感謝！

關注我的公眾號，及時獲取最新的文章。

更多文章

  • 關注公眾號進入會話視窗獲取
  • JVM系列專題：公眾號傳送 JVM

本文作者： arthinking

部落格連結： https://www.itzhai.com/database/how-sql-works-understand-the-essence-of-tuning-by-the-execution-principle.html

SQL執行內幕：從執行原理看調優的本質

版權宣告： BY-NC-SA許可協議：創作不易，如需轉載，請聯絡作者，謝謝！

References

  1. https://zhuanlan.zhihu.com/p/54378839. Retrieved from https://zhuanlan.zhihu.com/p/54378839 ↩︎

  2. 8.2.1.14 ORDER BY Optimization. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html ↩︎

  3. 8.8.2 EXPLAIN Output Format. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/explain-output.html ↩︎

  4. Batched Key Access: a Significant Speed-up for Join Queries. Retrieved from https://conferences.oreilly.com/mysql2008/public/schedule/detail/582 ↩︎

  5. Batched Key Access Joins. Retrieved from http://underpop.online.fr/m/mysql/manual/mysql-optimization-bka-optimization.html ↩︎

  6. [Hash join in MySQL 8. MySQL Server Blog. Retrieved from https://mysqlserverteam.com/hash-join-in-mysql-8/#:~:text=MySQL only supports inner hash,more often than it does](https://mysqlserverteam.com/hash-join-in-mysql-8/#:~:text=MySQL only supports inner hash,more often than it does) ↩︎

  7. MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS. Retrieved from https://www.guru99.com/joins.html ↩︎

  8. How the SQL join actually works?. Retrieved from https://stackoverflow.com/questions/34149582/how-the-sql-join-actually-works ↩︎

  9. 13.2.9 SELECT Statement. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/select.html ↩︎

  10. 8.2.1.18 DISTINCT Optimization. Retrieved from https://dev.mysql.com/doc/refman/8.0/en/distinct-optimization.html ↩︎ ↩︎

  11. Subquery Optimizer Hints. Retrieved from https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-subquery ↩︎

  12. 高效能MySQL第3版[M]. 電子工業出版社, 2013-5:239. ↩︎

  13. 8.2.2.1 Optimizing Subqueries, Derived Tables, and View References with Semijoin Transformations. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/semijoins.html ↩︎

  14. FirstMatch Strategy. Retrieved from https://mariadb.com/kb/en/firstmatch-strategy/ ↩︎

  15. DuplicateWeedout Strategy. Retrieved from https://mariadb.com/kb/en/duplicateweedout-strategy/ ↩︎

  16. LooseScan Strategy. Retrieved from https://mariadb.com/kb/en/loosescan-strategy/ ↩︎

  17. Semi-join Materialization Strategy. Retrieved from https://mariadb.com/kb/en/semi-join-materialization-strategy/ ↩︎

  18. Switchable Optimizations. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/switchable-optimizations.html ↩︎

  19. 8.2.2.3 Optimizing Subqueries with the EXISTS Strategy. Retrieved from https://dev.mysql.com/doc/refman/8.0/en/subquery-optimization-with-exists.html ↩︎

  20. EXISTS-to-IN Optimization. Retrieved from https://mariadb.com/kb/en/exists-to-in-optimization/ ↩︎

相關文章