相信大家看過無數的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

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

• MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS^[7]
• How the SQL join actually works?^[8]

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/ ↩︎

本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】

※網頁設計公司推薦更多不同的設計風格，搶佔消費者視覺第一線

※廣告預算用在刀口上，網站設計公司幫您達到更多曝光效益

※自行創業 缺乏曝光? 下一步"網站設計"幫您第一時間規劃公司的門面形象

※南投搬家前需注意的眉眉角角，別等搬了再說!

※新北清潔公司,居家、辦公、裝潢細清專業服務