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