[[414289]]

問題

這是一個線上問題，從日志平臺查詢到的 SQL 執(zhí)行情況，該 SQL 執(zhí)行的時間為 11.146s，可以認定為是一個慢查詢，美化后的 SQL 如下：

先找到這個表的定義以及索引情況如下：

可見，主要有兩個聯(lián)合索引：status, to_account_id 和 status, from_account_id

問題分析

我們先用 explain 查看執(zhí)行計劃：

先看看explain的含義吧。

id ：沒什么就是ID而已，如果沒有子查詢的話，通常就一行。

select_type ：大致分為簡單查詢和復雜查詢兩類，復雜查詢又分為簡單子查詢，派生表(from中的子查詢)和union。一般我們看見simple比較多，代表不包含子查詢和union，如果有復雜查詢則會標記成primary。

table ：表名

type ：表示關聯(lián)類型，決定Mysql通過什么方式查找行數(shù)據(jù)。這個一般就是我們看查詢時候的關鍵信息點。比如ALL就是全表掃描;index代表使用索引;range代表有限制的掃描索引，回比直接掃描全部索引好一些;ref也是索引查找，會返回匹配具體某個值的行數(shù)據(jù)，這個還有一些其他類型，比如eq_ref只返回符合的一條記錄，const會進行優(yōu)化轉換成常量。

possible_keys ：顯示可以使用的索引，但不一定用。

key ：實際使用到的索引。

key_len ：索引使用的字節(jié)數(shù)。

ref ：代表上面key一列中使用索引查找用到的列或者常量值。

rows ：為了找到符合條件的數(shù)據(jù)讀取的行數(shù)。

filtered ：表示查詢符合條件的數(shù)據(jù)占表的行數(shù)百分比，rows*filtered可以大致得到關聯(lián)的行數(shù)，Mysql5.1之后新增的字段。

Extra ：額外信息，比如using index表示使用覆蓋索引，using where表示在存儲引擎之后進行過濾，using temporary表示使用臨時表，using filesort表示對結果進行外部排序。

基本上述的經驗，我們看到索引和掃描行數(shù)其實都沒啥問題，但是，我們發(fā)現(xiàn)執(zhí)行計劃中使用了 using filesort。

綜合執(zhí)行 SQL 和表定義，基本斷定問題出在 ORDER BY amount desc, create_time asc，在生產線上數(shù)據(jù)記錄較多，使用 order by 語句后引起 filesort，導致出現(xiàn)了外部排序，從而降低了 SQL 的查詢性能。

再來理解一下 order by 的工作原理，幫助我們更好的做 SQL 優(yōu)化。

一般情況下，執(zhí)行計劃中如果出現(xiàn)using filesort 就會走如上的執(zhí)行流程，對于Mysql來說，數(shù)據(jù)量小則在內存中進行排序，數(shù)據(jù)量大則需要在磁盤中排序，這個過程統(tǒng)一都叫做filesort。

1. 首先根據(jù)索引找到對應的數(shù)據(jù)，然后把數(shù)據(jù)放入排序緩沖區(qū)中
2. 如果要排序的數(shù)據(jù)實際大小沒有超過緩沖區(qū)大小，就會使用內存排序，如快速排序，然后取出符合條件的數(shù)據(jù)返回
3. 如果超過了緩沖區(qū)大小，就需要使用外部排序，算法一般使用多路歸并排序，首先對數(shù)據(jù)分塊，然后對每塊數(shù)據(jù)進行排序，排序結果保存在磁盤中，最后將排序結果合并

除了知道排序的流程之外，排序使用的是字段的定義最大長度，而不是實際存儲的長度，所以會花費更多的空間。

另外在5.6之前的版本，如果涉及到多表關聯(lián)查詢，排序字段來自不同表的話，會將關聯(lián)結果保存到臨時表中，這就是我們平時看到using temporary;using filesort的場景，如果這時候再使用limit，limit將會發(fā)生在排序之后，這樣也可能導致排序的數(shù)據(jù)量非常大。

整個情況來看，緩沖區(qū)大小、排序字段的數(shù)據(jù)長度、查詢數(shù)據(jù)條數(shù)等都會影響查詢性能。

分析了整個排序過程，指導的優(yōu)化思想就是盡量不使用using filesort，尤其是在排序的數(shù)據(jù)量比較大的時候，那么優(yōu)化的方式就是盡量讓查詢出來的數(shù)據(jù)已經是排好序的，也就是合理使用聯(lián)合索引以及覆蓋索引。

優(yōu)化方向

優(yōu)化1：調整索引結構

優(yōu)化2：代碼結構優(yōu)化

另外，我們發(fā)現(xiàn)一處代碼，在 for 循環(huán)中做操作，然后更新 DB 表中的狀態(tài)，這樣會導致 1500 次的 DB 更新，可以考慮將 DB 的更新做批量處理，減少 DB 寫的次數(shù)，比如 100 條記錄執(zhí)行一次 DB 更新，這樣會大大降低寫 db 的次數(shù)。

這樣每次 方法調用，就會將 3000 次的寫操作，降低為 30 次的寫操作，當然批量的大小可以調節(jié)。

這里我們僅僅針對 SQL 調優(yōu)，代碼問題就暫時不考慮了。

性能結果

測試環(huán)境數(shù)據(jù)量在30萬數(shù)據(jù)

1. 優(yōu)化前查詢在 1.5s 以上
2. 優(yōu)化后查詢在 0.4s 左右

查詢性能提升 3～4 倍。

從生產的從庫上查詢看到數(shù)據(jù)量大概有3KW+，符合 where 條件的數(shù)據(jù)大概在300萬左右

• 優(yōu)化前查詢在 11s ～ 14s
• 優(yōu)化后查詢在 0.8s 左右

性能提升10倍以上。

雖然這個優(yōu)化比較簡單，但是還是需要我們平時有扎實的基礎才能選擇最合理的方式進行優(yōu)化。

本文轉載自微信公眾號「艾小仙」，可以通過以下二維碼關注。轉載本文請聯(lián)系艾小仙公眾號。