前言

SQL調優這塊呢，大廠面試必問的。最近金九銀十嘛，所以整理了SQL的調優思路，并且附幾個經典案例分析。

1.慢SQL優化思路。

1. 慢查詢日志記錄慢SQL
2. explain分析SQL的執行計劃
3. profile 分析執行耗時
4. Optimizer Trace分析詳情
5. 確定問題并采用相應的措施

1.1 慢查詢日志記錄慢SQL

如何定位慢SQL呢、我們可以通過慢查詢日志來查看慢SQL。默認的情況下呢，MySQL數據庫是不開啟慢查詢日志（slow query log）呢。所以我們需要手動把它打開。

查看下慢查詢日志配置，我們可以使用show variables like 'slow_query_log%'命令，如下：

• slow query log表示慢查詢開啟的狀態
• slow_query_log_file表示慢查詢日志存放的位置

我們還可以使用show variables like 'long_query_time'命令，查看超過多少時間，才記錄到慢查詢日志，如下：

• long_query_time表示查詢超過多少秒才記錄到慢查詢日志。

我們可以通過慢查日志，定位那些執行效率較低的SQL語句，重點關注分析。

1.2 explain查看分析SQL的執行計劃

當定位出查詢效率低的SQL后，可以使用explain查看SQL的執行計劃。

當explain與SQL一起使用時，MySQL將顯示來自優化器的有關語句執行計劃的信息。即MySQL解釋了它將如何處理該語句，包括有關如何連接表以及以何種順序連接表等信息。

一條簡單SQL，使用了explain的效果如下：

一般來說，我們需要重點關注type、rows、filtered、extra、key。

1.2.1 type

type表示連接類型，查看索引執行情況的一個重要指標。以下性能從好到壞依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

• system：這種類型要求數據庫表中只有一條數據，是const類型的一個特例，一般情況下是不會出現的。
• const：通過一次索引就能找到數據，一般用于主鍵或唯一索引作為條件，這類掃描效率極高，，速度非常快。
• eq_ref：常用于主鍵或唯一索引掃描，一般指使用主鍵的關聯查詢
• ref : 常用于非主鍵和唯一索引掃描。
• ref_or_null：這種連接類型類似于ref，區別在于MySQL會額外搜索包含NULL值的行
• index_merge：使用了索引合并優化方法，查詢使用了兩個以上的索引。
• unique_subquery：類似于eq_ref，條件用了in子查詢
• index_subquery：區別于unique_subquery，用于非唯一索引，可以返回重復值。
• range：常用于范圍查詢，比如：between ... and 或 In 等操作
• index：全索引掃描
• ALL：全表掃描

1.2.2 rows

該列表示MySQL估算要找到我們所需的記錄，需要讀取的行數。對于InnoDB表，此數字是估計值，并非一定是個準確值。

1.2.3 filtered

該列是一個百分比的值，表里符合條件的記錄數的百分比。簡單點說，這個字段表示存儲引擎返回的數據在經過過濾后，剩下滿足條件的記錄數量的比例。

1.2.4 extra

該字段包含有關MySQL如何解析查詢的其他信息，它一般會出現這幾個值：

• Using filesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情況才會出現。一般見于order by語句
• Using index ：表示是否用了覆蓋索引。
• Using temporary: 表示是否使用了臨時表,性能特別差，需要重點優化。一般多見于group by語句，或者union語句。
• Using where : 表示使用了where條件過濾.
• Using index condition：MySQL5.6之后新增的索引下推。在存儲引擎層進行數據過濾，而不是在服務層過濾，利用索引現有的數據減少回表的數據。

1.2.5 key

該列表示實際用到的索引。一般配合possible_keys列一起看。

1.3 profile 分析執行耗時

explain只是看到SQL的預估執行計劃，如果要了解SQL真正的執行線程狀態及消耗的時間，需要使用profiling。開啟profiling參數后，后續執行的SQL語句都會記錄其資源開銷，包括IO，上下文切換，CPU，內存等等，我們可以根據這些開銷進一步分析當前慢SQL的瓶頸再進一步進行優化。

profiling默認是關閉，我們可以使用show variables like '%profil%'查看是否開啟，如下：

可以使用set profiling=ON開啟。開啟后，可以運行幾條SQL，然后使用show profiles查看一下。

show profiles會顯示最近發給服務器的多條語句，條數由變量profiling_history_size定義，默認是15。如果我們需要看單獨某條SQL的分析，可以show profile查看最近一條SQL的分析，也可以使用show profile for query id（其中id就是show profiles中的QUERY_ID）查看具體一條的SQL語句分析。

除了查看profile ，還可以查看cpu和io，如上圖。

1.4 Optimizer Trace分析詳情

profile只能查看到SQL的執行耗時，但是無法看到SQL真正執行的過程信息，即不知道MySQL優化器是如何選擇執行計劃。這時候，我們可以使用Optimizer Trace，它可以跟蹤執行語句的解析優化執行的全過程。

我們可以使用set optimizer_trace="enabled=on"打開開關，接著執行要跟蹤的SQL，最后執行select * from information_schema.optimizer_trace跟蹤，如下：

大家可以查看分析其執行樹，會包括三個階段：

• join_preparation：準備階段
• join_optimization：分析階段
• join_execution：執行階段

1.5 確定問題并采用相應的措施

最后確認問題，就采取對應的措施。

• 多數慢SQL都跟索引有關，比如不加索引，索引不生效、不合理等，這時候，我們可以優化索引。
• 我們還可以優化SQL語句，比如一些in元素過多問題（分批），深分頁問題（基于上一次數據過濾等），進行時間分段查詢
• SQl沒辦法很好優化，可以改用ES的方式，或者數倉。
• 如果單表數據量過大導致慢查詢，則可以考慮分庫分表
• 如果數據庫在刷臟頁導致慢查詢，考慮是否可以優化一些參數，跟DBA討論優化方案
• 如果存量數據量太大，考慮是否可以讓部分數據歸檔

2. 慢查詢經典案例分析

2.1 案例1：隱式轉換

我們創建一個用戶user表

CREATETABLEuser(
idint(11)NOTNULLAUTO_INCREMENT,
userIdvarchar(32)NOTNULL,
agevarchar(16)NOTNULL,
namevarchar(255)NOTNULL,
PRIMARYKEY(id),
KEYidx_userid(userId)USINGBTREE
)ENGINE=InnoDBDEFAULTCHARSET=utf8;

userId字段為字串類型，是B+樹的普通索引，如果查詢條件傳了一個數字過去，會導致索引失效。如下：

如果給數字加上'',也就是說，傳的是一個字符串呢，當然是走索引，如下圖：

為什么第一條語句未加單引號就不走索引了呢？這是因為不加單引號時，是字符串跟數字的比較，它們類型不匹配，MySQL會做隱式的類型轉換，把它們轉換為浮點數再做比較。隱式的類型轉換，索引會失效。

2.2 案例2：最左匹配

MySQl建立聯合索引時，會遵循最左前綴匹配的原則，即最左優先。如果你建立一個（a,b,c）的聯合索引，相當于建立了(a)、(a,b)、(a,b,c)三個索引。

假設有以下表結構：

CREATETABLEuser(
idint(11)NOTNULLAUTO_INCREMENT,
user_idvarchar(32)NOTNULL,
agevarchar(16)NOTNULL,
namevarchar(255)NOTNULL,
PRIMARYKEY(id),
KEYidx_userid_name(user_id,name)USINGBTREE
)ENGINE=InnoDBDEFAULTCHARSET=utf8;

假設有一個聯合索引idx_userid_name，我們現在執行以下SQL，如果查詢列是name，索引是無效的：

explainselect*fromuserwherename='撿田螺的小男孩';

因為查詢條件列name不是聯合索引idx_userid_name中的第一個列，不滿足最左匹配原則，所以索引不生效。在聯合索引中，只有查詢條件滿足最左匹配原則時，索引才正常生效。如下，查詢條件列是user_id

2.3 案例3：深分頁問題

limit深分頁問題，會導致慢查詢，應該大家都司空見慣了吧。

limit深分頁為什么會變慢呢？ 假設有表結構如下：

CREATETABLEaccount(
idint(11)NOTNULLAUTO_INCREMENTCOMMENT'主鍵Id',
namevarchar(255)DEFAULTNULLCOMMENT'賬戶名',
balanceint(11)DEFAULTNULLCOMMENT'余額',
create_timedatetimeNOTNULLCOMMENT'創建時間',
update_timedatetimeNOTNULLONUPDATECURRENT_TIMESTAMPCOMMENT'更新時間',
PRIMARYKEY(id),
KEYidx_name(name),
KEYidx_create_time(create_time)//索引
)ENGINE=InnoDBAUTO_INCREMENT=1570068DEFAULTCHARSET=utf8ROW_FORMAT=REDUNDANTCOMMENT='賬戶表';

以下這個SQL，你知道執行過程是怎樣的呢？

selectid,name,balancefromaccountwherecreate_time>'2020-09-19'limit100000,10;

這個SQL的執行流程醬紫：

1. 通過普通二級索引樹idx_create_time，過濾create_time條件，找到滿足條件的主鍵id。
2. 通過主鍵id，回到id主鍵索引樹，找到滿足記錄的行，然后取出需要展示的列（回表過程）
3. 掃描滿足條件的100010行，然后扔掉前100000行，返回。

因此，limit深分頁，導致SQL變慢原因有兩個：

• limit語句會先掃描offset+n行，然后再丟棄掉前offset行，返回后n行數據。也就是說limit 100000,10，就會掃描100010行，而limit 0,10，只掃描10行。
• limit 100000,10 掃描更多的行數，也意味著回表更多的次數。

如何優化深分頁問題?

我們可以通過減少回表次數來優化。一般有標簽記錄法和延遲關聯法。

標簽記錄法

就是標記一下上次查詢到哪一條了，下次再來查的時候，從該條開始往下掃描。就好像看書一樣，上次看到哪里了，你就折疊一下或者夾個書簽，下次來看的時候，直接就翻到啦。

假設上一次記錄到100000，則SQL可以修改為：

selectid,name,balanceFROMaccountwhereid>100000limit10;

這樣的話，后面無論翻多少頁，性能都會不錯的，因為命中了id索引。但是這種方式有局限性：需要一種類似連續自增的字段。

延遲關聯法

延遲關聯法，就是把條件轉移到主鍵索引樹，然后減少回表。如下

selectacct1.id,acct1.name,acct1.balanceFROMaccountacct1INNERJOIN(SELECTa.idFROMaccountaWHEREa.create_time>'2020-09-19'limit100000,10)ASacct2onacct1.id=acct2.id;

優化思路就是，先通過idx_create_time二級索引樹查詢到滿足條件的主鍵ID，再與原表通過主鍵ID內連接，這樣后面直接走了主鍵索引了，同時也減少了回表。

2.4 案例4：in元素過多

如果使用了in，即使后面的條件加了索引，還是要注意in后面的元素不要過多哈。in元素一般建議不要超過200個，如果超過了，建議分組，每次200一組進行哈。

反例:

selectuser_id,namefromuserwhereuser_idin(1,2,3...1000000);

如果我們對in的條件不做任何限制的話，該查詢語句一次性可能會查詢出非常多的數據，很容易導致接口超時。尤其有時候，我們是用的子查詢，in后面的子查詢，你都不知道數量有多少那種，更容易采坑.如下這種子查詢：

select*fromuserwhereuser_idin(selectauthor_idfromartilcewheretype=1);

如果type = 1有1一千，甚至上萬個呢？肯定是慢SQL。索引一般建議分批進行，一次200個，比如：

selectuser_id,namefromuserwhereuser_idin(1,2,3...200);

in查詢為什么慢呢？

這是因為in查詢在MySQL底層是通過n*m的方式去搜索，類似union。

in查詢在進行cost代價計算時（代價 = 元組數 * IO平均值），是通過將in包含的數值，一條條去查詢獲取元組數的，因此這個計算過程會比較的慢，所以MySQL設置了個臨界值(eq_range_index_dive_limit)，5.6之后超過這個臨界值后該列的cost就不參與計算了。因此會導致執行計劃選擇不準確。默認是200，即in條件超過了200個數據，會導致in的代價計算存在問題，可能會導致Mysql選擇的索引不準確。

2.5 order by 走文件排序導致的慢查詢

如果order by 使用到文件排序，則會可能會產生慢查詢。我們來看下下面這個SQL：

selectname,age,cityfromstaffwherecity='深圳'orderbyagelimit10;

它表示的意思就是：查詢前10個，來自深圳員工的姓名、年齡、城市，并且按照年齡小到大排序。

查看explain執行計劃的時候，可以看到Extra這一列，有一個Using filesort，它表示用到文件排序。

order by文件排序效率為什么較低

大家可以看下這個下面這個圖:

order by排序，分為全字段排序和rowid排序。它是拿max_length_for_sort_data和結果行數據長度對比，如果結果行數據長度超過max_length_for_sort_data這個值，就會走rowid排序，相反，則走全字段排序。

2.5.1 rowid排序

rowid排序，一般需要回表去找滿足條件的數據，所以效率會慢一點。以下這個SQL，使用rowid排序，執行過程是這樣：

selectname,age,cityfromstaffwherecity='深圳'orderbyagelimit10;

1. MySQL為對應的線程初始化sort_buffer，放入需要排序的age字段，以及主鍵id；
2. 從索引樹idx_city， 找到第一個滿足 city='深圳’條件的主鍵id,假設id為X；
3. 到主鍵id索引樹拿到id=X的這一行數據， 取age和主鍵id的值，存到sort_buffer；
4. 從索引樹idx_city拿到下一個記錄的主鍵id，假設id=Y；
5. 重復步驟 3、4 直到city的值不等于深圳為止；
6. 前面5步已經查找到了所有city為深圳的數據，在sort_buffer中，將所有數據根據age進行排序；遍歷排序結果，取前10行，并按照id的值回到原表中，取出city、name 和 age三個字段返回給客戶端。

2.5.2 全字段排序

同樣的SQL，如果是走全字段排序是這樣的：

selectname,age,cityfromstaffwherecity='深圳'orderbyagelimit10;

1. MySQL 為對應的線程初始化sort_buffer，放入需要查詢的name、age、city字段；
2. 從索引樹idx_city， 找到第一個滿足 city='深圳’條件的主鍵 id，假設找到id=X；
3. 到主鍵id索引樹拿到id=X的這一行數據， 取name、age、city三個字段的值，存到sort_buffer；
4. 從索引樹idx_city 拿到下一個記錄的主鍵id，假設id=Y；
5. 重復步驟 3、4 直到city的值不等于深圳為止；
6. 前面5步已經查找到了所有city為深圳的數據，在sort_buffer中，將所有數據根據age進行排序；
7. 按照排序結果取前10行返回給客戶端。

sort_buffer的大小是由一個參數控制的：sort_buffer_size。

• 如果要排序的數據小于sort_buffer_size，排序在sort_buffer內存中完成
• 如果要排序的數據大于sort_buffer_size，則借助磁盤文件來進行排序。

借助磁盤文件排序的話，效率就更慢一點。因為先把數據放入sort_buffer，當快要滿時。會排一下序，然后把sort_buffer中的數據，放到臨時磁盤文件，等到所有滿足條件數據都查完排完，再用歸并算法把磁盤的臨時排好序的小文件，合并成一個有序的大文件。

2.5.3 如何優化order by的文件排序

order by使用文件排序，效率會低一點。我們怎么優化呢？

• 因為數據是無序的，所以就需要排序。如果數據本身是有序的，那就不會再用到文件排序啦。而索引數據本身是有序的，我們通過建立索引來優化order by語句。
• 我們還可以通過調整max_length_for_sort_data、sort_buffer_size等參數優化；

2.6 索引字段上使用is null， is not null，索引可能失效

表結構:

CREATETABLE`user`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`card`varchar(255)DEFAULTNULL,
`name`varchar(255)DEFAULTNULL,
PRIMARYKEY(`id`),
KEY`idx_name`(`name`)USINGBTREE,
KEY`idx_card`(`card`)USINGBTREE
)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8;

單個name字段加上索引，并查詢name為非空的語句，其實會走索引的，如下:

單個card字段加上索引，并查詢name為非空的語句，其實會走索引的，如下:

但是它兩用or連接起來，索引就失效了，如下：

很多時候，也是因為數據量問題，導致了MySQL優化器放棄走索引。同時，平時我們用explain分析SQL的時候，如果type=range,要注意一下哈，因為這個可能因為數據量問題，導致索引無效。

2.7 索引字段上使用（！= 或者 < >），索引可能失效

假設有表結構：

CREATETABLE`user`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`userId`int(11)NOTNULL,
`age`int(11)DEFAULTNULL,
`name`varchar(255)NOTNULL,
PRIMARYKEY(`id`),
KEY`idx_age`(`age`)USINGBTREE
)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8;

雖然age加了索引，但是使用了！= 或者< >，not in這些時，索引如同虛設。如下：

其實這個也是跟mySQL優化器有關，如果優化器覺得即使走了索引，還是需要掃描很多很多行的哈，它覺得不劃算，不如直接不走索引。平時我們用！= 或者< >，not in的時候，留點心眼哈。

2.8 左右連接，關聯的字段編碼格式不一樣

新建兩個表，一個user，一個user_job

CREATETABLE`user`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`name`varchar(255)CHARACTERSETutf8mb4DEFAULTNULL,
`age`int(11)NOTNULL,
PRIMARYKEY(`id`),
KEY`idx_name`(`name`)USINGBTREE
)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8;

CREATETABLE`user_job`(
`id`int(11)NOTNULL,
`userId`int(11)NOTNULL,
`job`varchar(255)DEFAULTNULL,
`name`varchar(255)DEFAULTNULL,
PRIMARYKEY(`id`),
KEY`idx_name`(`name`)USINGBTREE
)ENGINE=InnoDBDEFAULTCHARSET=utf8;

user表的name字段編碼是utf8mb4，而user_job表的name字段編碼為utf8。

執行左外連接查詢,user_job表還是走全表掃描，如下：

如果把它們的name字段改為編碼一致，相同的SQL，還是會走索引。

2.9 group by使用臨時表

group by一般用于分組統計，它表達的邏輯就是根據一定的規則，進行分組。日常開發中，我們使用得比較頻繁。如果不注意，很容易產生慢SQL。

2.9.1 group by執行流程

假設有表結構：

CREATETABLE`staff`(
`id`bigint(11)NOTNULLAUTO_INCREMENTCOMMENT'主鍵id',
`id_card`varchar(20)NOTNULLCOMMENT'身份證號碼',
`name`varchar(64)NOTNULLCOMMENT'姓名',
`age`int(4)NOTNULLCOMMENT'年齡',
`city`varchar(64)NOTNULLCOMMENT'城市',
PRIMARYKEY(`id`)
)ENGINE=InnoDBAUTO_INCREMENT=15DEFAULTCHARSET=utf8COMMENT='員工表';

我們查看一下這個SQL的執行計劃：

explainselectcity,count(*)asnumfromstaffgroupbycity;

• Extra 這個字段的Using temporary表示在執行分組的時候使用了臨時表
• Extra 這個字段的Using filesort表示使用了文件排序

group by是怎么使用到臨時表和排序了呢？我們來看下這個SQL的執行流程

selectcity,count(*)asnumfromstaffgroupbycity;

1. 創建內存臨時表，表里有兩個字段city和num；
2. 全表掃描staff的記錄，依次取出city = 'X'的記錄。

• 判斷臨時表中是否有為city='X'的行，沒有就插入一個記錄 (X,1);
• 如果臨時表中有city='X'的行，就將X這一行的num值加 1；

3. 遍歷完成后，再根據字段city做排序，得到結果集返回給客戶端。這個流程的執行圖如下：

臨時表的排序是怎樣的呢？

就是把需要排序的字段，放到sort buffer，排完就返回。在這里注意一點哈，排序分全字段排序和rowid排序

• 如果是全字段排序，需要查詢返回的字段，都放入sort buffer，根據排序字段排完，直接返回
• 如果是rowid排序，只是需要排序的字段放入sort buffer，然后多一次回表操作，再返回。

2.9.2 group by可能會慢在哪里？

group by使用不當，很容易就會產生慢SQL問題。因為它既用到臨時表，又默認用到排序。有時候還可能用到磁盤臨時表。

• 如果執行過程中，會發現內存臨時表大小到達了上限（控制這個上限的參數就是tmp_table_size），會把內存臨時表轉成磁盤臨時表。
• 如果數據量很大，很可能這個查詢需要的磁盤臨時表，就會占用大量的磁盤空間。

2.9.3 如何優化group by呢

從哪些方向去優化呢？

• 方向1：既然它默認會排序，我們不給它排是不是就行啦。
• 方向2：既然臨時表是影響group by性能的X因素，我們是不是可以不用臨時表？

我們一起來想下，執行group by語句為什么需要臨時表呢？group by的語義邏輯，就是統計不同的值出現的個數。如果這個這些值一開始就是有序的，我們是不是直接往下掃描統計就好了，就不用臨時表來記錄并統計結果啦?

可以有這些優化方案：

• group by 后面的字段加索引
• order by null 不用排序
• 盡量只使用內存臨時表
• 使用SQL_BIG_RESULT

2.10 delete + in子查詢不走索引！

之前見到過一個生產慢SQL問題，當delete遇到in子查詢時，即使有索引，也是不走索引的。而對應的select + in子查詢，卻可以走索引。

MySQL版本是5.7，假設當前有兩張表account和old_account,表結構如下：

CREATETABLE`old_account`(
`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主鍵Id',
`name`varchar(255)DEFAULTNULLCOMMENT'賬戶名',
`balance`int(11)DEFAULTNULLCOMMENT'余額',
`create_time`datetimeNOTNULLCOMMENT'創建時間',
`update_time`datetimeNOTNULLONUPDATECURRENT_TIMESTAMPCOMMENT'更新時間',
PRIMARYKEY(`id`),
KEY`idx_name`(`name`)USINGBTREE
)ENGINE=InnoDBAUTO_INCREMENT=1570068DEFAULTCHARSET=utf8ROW_FORMAT=REDUNDANTCOMMENT='老的賬戶表';

CREATETABLE`account`(
`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主鍵Id',
`name`varchar(255)DEFAULTNULLCOMMENT'賬戶名',
`balance`int(11)DEFAULTNULLCOMMENT'余額',
`create_time`datetimeNOTNULLCOMMENT'創建時間',
`update_time`datetimeNOTNULLONUPDATECURRENT_TIMESTAMPCOMMENT'更新時間',
PRIMARYKEY(`id`),
KEY`idx_name`(`name`)USINGBTREE
)ENGINE=InnoDBAUTO_INCREMENT=1570068DEFAULTCHARSET=utf8ROW_FORMAT=REDUNDANTCOMMENT='賬戶表';

執行的SQL如下：

deletefromaccountwherenamein(selectnamefromold_account);

查看執行計劃，發現不走索引：

但是如果把delete換成select，就會走索引。如下：

為什么select + in子查詢會走索引，delete + in子查詢卻不會走索引呢？

我們執行以下SQL看看：

explainselect*fromaccountwherenamein(selectnamefromold_account);
showWARNINGS;//可以查看優化后,最終執行的sql

結果如下：

select`test2`.`account`.`id`AS`id`,`test2`.`account`.`name`AS`name`,`test2`.`account`.`balance`AS`balance`,`test2`.`account`.`create_time`AS`create_time`,`test2`.`account`.`update_time`AS`update_time`from`test2`.`account`
semijoin(`test2`.`old_account`)
where(`test2`.`account`.`name`=`test2`.`old_account`.`name`)

可以發現，實際執行的時候，MySQL對select in子查詢做了優化，把子查詢改成join的方式，所以可以走索引。但是很遺憾，對于delete in子查詢，MySQL卻沒有對它做這個優化。

日常開發中，大家注意一下這個場景哈

審核編輯 ：李倩