男人要慢，SQL要快：記一次慢SQL優化
問題
這是一個線上問題，從日志平臺查詢到的 SQL 執行情況，該 SQL 執行的時間為 11.146s，可以認定為是一個慢查詢，美化后的 SQL 如下：
先找到這個表的定義以及索引情況如下：
可見，主要有兩個聯合索引：status, to_account_id 和 status, from_account_id
問題分析
我們先用 explain 查看執行計劃：
先看看explain的含義吧。
id ：沒什么就是ID而已，如果沒有子查詢的話，通常就一行。
select_type ：大致分為簡單查詢和復雜查詢兩類，復雜查詢又分為簡單子查詢，派生表(from中的子查詢)和union。一般我們看見simple比較多，代表不包含子查詢和union，如果有復雜查詢則會標記成primary。
table ：表名
type ：表示關聯類型，決定Mysql通過什么方式查找行數據。這個一般就是我們看查詢時候的關鍵信息點。比如ALL就是全表掃描;index代表使用索引;range代表有限制的掃描索引，回比直接掃描全部索引好一些;ref也是索引查找，會返回匹配具體某個值的行數據，這個還有一些其他類型，比如eq_ref只返回符合的一條記錄，const會進行優化轉換成常量。
possible_keys ：顯示可以使用的索引，但不一定用。
key ：實際使用到的索引。
key_len ：索引使用的字節數。
ref ：代表上面key一列中使用索引查找用到的列或者常量值。
rows ：為了找到符合條件的數據讀取的行數。
filtered ：表示查詢符合條件的數據占表的行數百分比，rows*filtered可以大致得到關聯的行數，Mysql5.1之后新增的字段。
Extra ：額外信息，比如using index表示使用覆蓋索引，using where表示在存儲引擎之后進行過濾，using temporary表示使用臨時表，using filesort表示對結果進行外部排序。
基本上述的經驗，我們看到索引和掃描行數其實都沒啥問題，但是，我們發現執行計劃中使用了 using filesort。
綜合執行 SQL 和表定義，基本斷定問題出在 ORDER BY amount desc, create_time asc，在生產線上數據記錄較多，使用 order by 語句后引起 filesort，導致出現了外部排序，從而降低了 SQL 的查詢性能。
再來理解一下 order by 的工作原理，幫助我們更好的做 SQL 優化。
一般情況下，執行計劃中如果出現using filesort 就會走如上的執行流程，對于Mysql來說，數據量小則在內存中進行排序，數據量大則需要在磁盤中排序，這個過程統一都叫做filesort。
- 首先根據索引找到對應的數據，然后把數據放入排序緩沖區中
- 如果要排序的數據實際大小沒有超過緩沖區大小，就會使用內存排序，如快速排序，然后取出符合條件的數據返回
- 如果超過了緩沖區大小，就需要使用外部排序，算法一般使用多路歸并排序，首先對數據分塊，然后對每塊數據進行排序，排序結果保存在磁盤中，最后將排序結果合并
除了知道排序的流程之外，排序使用的是字段的定義最大長度，而不是實際存儲的長度，所以會花費更多的空間。
另外在5.6之前的版本，如果涉及到多表關聯查詢，排序字段來自不同表的話，會將關聯結果保存到臨時表中，這就是我們平時看到using temporary;using filesort的場景，如果這時候再使用limit，limit將會發生在排序之后，這樣也可能導致排序的數據量非常大。
整個情況來看，緩沖區大小、排序字段的數據長度、查詢數據條數等都會影響查詢性能。
分析了整個排序過程，指導的優化思想就是盡量不使用using filesort，尤其是在排序的數據量比較大的時候，那么優化的方式就是盡量讓查詢出來的數據已經是排好序的，也就是合理使用聯合索引以及覆蓋索引。
優化方向
優化1：調整索引結構
優化2：代碼結構優化
另外，我們發現一處代碼，在 for 循環中做操作，然后更新 DB 表中的狀態，這樣會導致 1500 次的 DB 更新，可以考慮將 DB 的更新做批量處理，減少 DB 寫的次數，比如 100 條記錄執行一次 DB 更新，這樣會大大降低寫 db 的次數。
這樣每次 方法調用，就會將 3000 次的寫操作，降低為 30 次的寫操作，當然批量的大小可以調節。
這里我們僅僅針對 SQL 調優，代碼問題就暫時不考慮了。
性能結果
測試環境數據量在30萬數據
- 優化前查詢在 1.5s 以上
- 優化后查詢在 0.4s 左右
查詢性能提升 3～4 倍。
從生產的從庫上查詢看到數據量大概有3KW+，符合 where 條件的數據大概在300萬左右
- 優化前查詢在 11s ～ 14s
- 優化后查詢在 0.8s 左右
性能提升10倍以上。
雖然這個優化比較簡單，但是還是需要我們平時有扎實的基礎才能選擇最合理的方式進行優化。
本文轉載自微信公眾號「艾小仙」，可以通過以下二維碼關注。轉載本文請聯系艾小仙公眾號。
