01、線上慢 SQL 背景

慢 SQL 會影響用戶使用體驗，降低數(shù)據(jù)庫的整體性能，嚴重的甚至會導致服務器掛掉、整個系統(tǒng)癱瘓。筆者通過監(jiān)控平臺發(fā)現(xiàn)線上存在這樣一條慢SQL（原始SQL已脫敏，表結(jié)構(gòu)出于簡化的目的做了一定刪減，實際執(zhí)行耗時以文中提供數(shù)據(jù)為準），其執(zhí)行耗時在分鐘級。

select t1.*,t2.x from t_table1 t1 leftjoin t_table2 t2 on t1.a = t2.a orderby t1.c desc;

表結(jié)構(gòu)如下：

CREATETABLE `t_table1` (
  `id` bigint(20) unsigned NOTNULL AUTO_INCREMENT COMMENT '主鍵',
  `a` varchar(64) NOTNULL,
  `b` varchar(64) NOTNULL,
  `c` varchar(20) NOTNULL,
  PRIMARYKEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=0DEFAULT CHARSET=utf8mb4;


CREATETABLE `t_table2` (
  `id` bigint(20) unsigned NOTNULL AUTO_INCREMENT COMMENT '主鍵',
  `a` varchar(64) NOTNULL,
  `x` varchar(64) NOTNULL,
  `y` varchar(20) NOTNULL,
  PRIMARYKEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0DEFAULT CHARSET=utf8mb4;

其他信息：

當發(fā)現(xiàn)慢SQL時，筆者的第一反應是使用Explain查看SQL的執(zhí)行計劃，結(jié)果如下：

通過Explain初步分析：兩張表均執(zhí)行了全表掃描，結(jié)合兩張表的數(shù)據(jù)規(guī)模分析全表掃描并非耗時達到分鐘級的主要原因。另外執(zhí)行計劃extra種提示的Using temporary; Using filesort; Using join buffer (Block Nested Loop)又分別代表什么含義呢？

02、原理探究

2.1Join 算法原理

2.1.1 驅(qū)動表和被驅(qū)動表

在Join語句中，執(zhí)行引擎優(yōu)先掃描的表被稱為驅(qū)動表，另一張表被稱為被驅(qū)動表。執(zhí)行引擎在選擇驅(qū)動表時，除了必須要遵守的特定語義外，最重要的考慮便是執(zhí)行效率。

首先列舉兩種特定語義約束驅(qū)動表選取的場景：

場景一：Straight join指定連接順序，強制要求執(zhí)行引擎優(yōu)先掃描左側(cè)的表。

場景二：Left/Right [outer] join，方向連接的特點是反方向表中如果不存在關聯(lián)的數(shù)據(jù)則填充NULL值，這一特性要求方向查詢時優(yōu)先掃描相同方向的表。倘若where條件中明確指明反方向表中的部分列非空，則驅(qū)動表的選擇就不受此語義的限制，執(zhí)行引擎會依據(jù)效率選取驅(qū)動表。

當沒有特定語義的約束時，執(zhí)行引擎便會依據(jù)執(zhí)行效率選取驅(qū)動表，如何判斷哪張表作為驅(qū)動表的效率更高呢？下文會結(jié)合Join的兩種算法更深入地探討這個問題。

2.1.2 Block Nested-Loop Join

假設一個數(shù)據(jù)量為m行的驅(qū)動表與一個數(shù)據(jù)量為n行的被驅(qū)動表進行join查詢。

最簡單的一種算法：

1. 從驅(qū)動表掃描一行數(shù)據(jù)；
2. 對被驅(qū)動表進行全表掃描，得到的結(jié)果依次與驅(qū)動表的數(shù)據(jù)進行join并把滿足條件的數(shù)據(jù)加入結(jié)果集；
3. 接著掃描驅(qū)動表，每掃描一行數(shù)據(jù)，均重復執(zhí)行一次步驟2，直至驅(qū)動表的全部數(shù)據(jù)被掃描完畢。

這種算法的磁盤掃描開銷為m*n，非常低效，MySQL在實際中并未直接使用該算法，而是采用緩存的思想（分配一塊Join buffer）對該算法進行改進，并命名為Block Nested-Loop join(BNL)。

BNL的算法步驟為：

1. 從驅(qū)動表一次掃描K條數(shù)據(jù)，并把數(shù)據(jù)緩存在Join buffer；
2. 對被驅(qū)動表進行全表掃描，得到的結(jié)果依次與驅(qū)動表的K條數(shù)據(jù)進行join并把滿足條件的數(shù)據(jù)加入結(jié)果集；
3. 清空join_buffer；
4. 接著從驅(qū)動表再取出K條數(shù)據(jù)，重復步驟2、3，直至掃描完驅(qū)動表的全部數(shù)據(jù)。

上述算法中，驅(qū)動表分段取數(shù)的次數(shù)記為l，整個算法的磁盤掃描開銷為m+ln。由于分段的次數(shù)與驅(qū)動表的數(shù)據(jù)成正相關，所以公式可以記為m+λmn，λ的取值范圍為(0,1)。

當兩張表的行數(shù)（m、n大?。┕潭ǖ那闆r下，m對結(jié)果的影響更大，m越小整體掃描的代價越小，所以執(zhí)行引擎優(yōu)先選擇數(shù)據(jù)量更小的表作為驅(qū)動表(符合“小表驅(qū)動大表”的說法)。

2.1.3 Index Nested-Loop Join

BNL算法使用了Join buffer結(jié)構(gòu)，雖然有可能通過減少重復掃描來降低磁盤掃描開銷，然而驅(qū)動表分段掃描的次數(shù)過多依然可能會導致查詢的低效。索引是MySQL查詢提效的重要結(jié)構(gòu)，當被驅(qū)動表的關聯(lián)鍵存在索引時，MySQL會使用Index Nested-Loop Join（NLJ）算法。

該算法的步驟為：

1. 從驅(qū)動表掃描一行數(shù)據(jù)；
2. 使用驅(qū)動表的關聯(lián)鍵搜索被驅(qū)動表的索引樹，通過被驅(qū)動表的索引結(jié)構(gòu)找到被驅(qū)動表的主鍵，再通過主鍵回表查詢出被驅(qū)動表的關聯(lián)數(shù)據(jù)（暫不考慮覆蓋索引的情況）；
3. 接著掃描驅(qū)動表，每掃描一行數(shù)據(jù)，均重復執(zhí)行一次步驟2，直至驅(qū)動表的全部數(shù)據(jù)被掃描完畢。

每次搜索一棵樹的復雜度近似為log2 n，上述過程在被驅(qū)動表掃描一行數(shù)據(jù)的時間復雜度是2log2 n，算法的整體復雜度為m+2mlog2 n，在該算法中，依舊是m對結(jié)果的影響更大，m越小整體掃描的代價越小，所以執(zhí)行引擎總是選擇數(shù)據(jù)量更小的表作為驅(qū)動表(符合“小表驅(qū)動大表”的說法)。

2.2Order by 算法原理

2.2.1 全字段排序

MySQL會為每個線程分配一塊內(nèi)存（Sort buffer）用于排序，當Sort buffer的空間不足時（通過系統(tǒng)參數(shù)sort_buffer_size設置Sort buffer的大小），執(zhí)行引擎不得不開辟磁盤臨時文件用于排序，此時排序的性能也會大幅降低。

全字段排序是將查詢需要的所有字段進行暫存，并按照排序字段進行排序，并將排序后的結(jié)果集直接返回。

2.2.2 Rowid 排序

若要查詢的數(shù)據(jù)單行占用空間較大，Sort buffer中可以容納的排序行數(shù)將會減少，此時使用磁盤臨時文件進行排序的概率將會增大。為了提高排序性能，執(zhí)行引擎提供一種只存儲排序字段的算法，稱為Rowid排序算法。

該算法的步驟為：

1. 將參與排序的字段和主鍵進行臨時存儲；
2. 按照排序字段進行排序，得到有序的主鍵；
3. 根據(jù)有序的主鍵進行回表，按順序?qū)⑺幸樵兊臄?shù)據(jù)返回。

Rowid排序在單行查詢數(shù)據(jù)較大時可以通過節(jié)省臨時排序空間從而達到降低排序開銷的目的，然而該算法的代價是會增加磁盤掃描的次數(shù)（主鍵回表），所以是否選擇使用該算法需要根據(jù)實際情況進行取舍（通過系統(tǒng)參數(shù)max_length_for_sort_data設置）。

03、調(diào)優(yōu)過程

3.1執(zhí)行過程分析

了解了Join和Order by的工作原理，我們推測執(zhí)行計劃的大致過程為：

1. t_table_1與t_table_2進行Join查詢，使用了BNL算法（Using join buffer (Block Nested Loop)）
2. 將Join的結(jié)果暫存臨時表（Using temporary）
3. 對臨時表中的數(shù)據(jù)進行排序后返回（Using filesort）

為了佐證筆者的推測以及了解每一步的開銷情況，Optimizer_trace命令可以提供更多執(zhí)行過程細節(jié)。

{
     "considered_execution_plans": [
               {
                 "table": "`t_table1` `t1`",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
                       "rows_to_scan": 3000,
                       "access_type": "scan",
                       "resulting_rows": 3000,
                       "cost": 615,
                       "chosen": true,
                       "use_tmp_table": true
                     }
                   ] /* considered_access_paths */
                 } /* best_access_path */,
                 "rest_of_plan": [
                   {
                     "table": "`t_table2` `t2`",
                     "best_access_path": {
                       "considered_access_paths": [
                         {
                           "rows_to_scan": 69882,
                           "access_type": "scan",
                           "using_join_cache": true,
                           "buffers_needed": 5,
                           "resulting_rows": 69882,
                           "cost": 4.19e7,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
                     } /* best_access_path */,
                     "rows_for_plan": 2.1e8,
                     "cost_for_plan": 4.19e7,
                     "sort_cost": 2.1e8,
                     "new_cost_for_plan": 2.52e8,
                     "chosen": true
                   }
                 ] /* rest_of_plan */
               }
             ] /* considered_execution_plans */
   }

上圖展示的即為執(zhí)行引擎預估的執(zhí)行計劃，從Optimizer_trace的輸出結(jié)果中可以佐證上述對于執(zhí)行過程的推測。另外我們可以得到執(zhí)行代價的結(jié)果為：

• t_table1的掃描行數(shù)為3000，代價為615;
• t_table2的掃描行數(shù)為69882，由于BNL算法t_table2會被多次全表掃描，整體代價為4.19e7;
• 對Join結(jié)果進行排序的開銷為2.1e8。

從執(zhí)行引擎預估的執(zhí)行計劃可以看出執(zhí)行引擎認為排序的開銷最大，另外由于使用BNL算法會導致被驅(qū)動表執(zhí)行多次全表掃描，其執(zhí)行代價僅次于排序。然而預估的執(zhí)行計劃并不代表真正的執(zhí)行結(jié)果，下面展示Optimizer_trace命令對于真實執(zhí)行結(jié)果部分參數(shù)：

{
       "join_execution": {
         "select#": 1,
         "steps": [
           {
             "creating_tmp_table": {
               "tmp_table_info": {
                 "table": "intermediate_tmp_table",
                 "row_length": 655,
                 "key_length": 0,
                 "unique_constraint": false,
                 "location": "memory (heap)",
                 "row_limit_estimate": 25614
               } /* tmp_table_info */
             } /* creating_tmp_table */
           },
           {
             "filesort_summary": {
               "rows": 3000,
               "examined_rows": 3000,
               "number_of_tmp_files": 0,
               "sort_buffer_size": 60200,
               "sort_mode": "<sort_key, rowid>"
             } /* filesort_summary */
           }
         ] /* steps */
       } /* join_execution */
}

從執(zhí)行結(jié)果參數(shù)來看：

• 執(zhí)行引擎使用臨時表保存Join的結(jié)果，且臨時表是一張內(nèi)存表。
• 參與排序的數(shù)據(jù)行數(shù)為3000行，沒有使用磁盤臨時文件進行排序，排序算法選擇的是Rowid排序。

綜合執(zhí)行引擎的預估的執(zhí)行計劃和真實的執(zhí)行結(jié)果參數(shù)可以得出，執(zhí)行引擎預估最大的執(zhí)行開銷為排序，但實際上排序并未使用到磁盤臨時文件，且Rowid排序的回表操作是在內(nèi)存中進行的（在內(nèi)存臨時表中進行回表），3000條數(shù)據(jù)的內(nèi)存排序開銷是極快的，所以真實的最大開銷是BNL算法導致的對被驅(qū)動表多次進行全表掃描的開銷。

3.2最終的優(yōu)化

對于BNL算法，可以通過在被驅(qū)動表添加索引使其轉(zhuǎn)化為NLJ算法來進行優(yōu)化（此處注意一些索引失效的場景，筆者在實際調(diào)優(yōu)中遇到了字符集不同導致的索引失效場景）。在t_table2表添加索引后，觀察一周內(nèi)的SQL監(jiān)控如下，可以看到SQL最大響應時間不超過20ms，執(zhí)行效率得到了大幅提升。

04、總結(jié)

本文完整的介紹了一個SQL調(diào)優(yōu)案例，通過這個案例可以歸納出SQL調(diào)優(yōu)的基本思想。首先，需要了解SQL語句中的關鍵字（Join、Order by...）的基本工作原理，并輔助一些執(zhí)行過程數(shù)據(jù)（Explain、Optimizer_trace），通過實驗驗證猜想，最終達成調(diào)優(yōu)的目的。