一個MySQL語句的優化

事情是這樣：資料庫是MySQL，有若干個連接保持每秒2~3條的速度向表同一個表裡寫入「日誌型」記錄。表的結構除了一個必要的自增主鍵id之外，還有一個做了索引的type（tinyint）、time（timestamp）和一個ipc（float）類型的欄位。一個讀進程定時會要抓取每種type的最後一條記錄。資料庫的讀寫都很熱，所以要儘可能的保證性能。

多年之後，再次拾起Database engineer的活兒，有點不太適應。起初，這個問題交到我手裡的時候，type的定義很少，只有區區兩種，原來的做法是簡單的分兩次select：

SELECT * FROM log WHERE type = TYPE1 ORDER BY id DESC LIMIT 1;nSELECT * FROM log WHERE type = TYPE2 ORDER BY id DESC LIMIT 1;n

幾乎是教課書式的做法，由於是日誌型插入數據，而且使用主鍵排序相比time排序更有優勢。由於是簡單的index查詢，效率不錯。但問題來了：

1. 如果進一步引入多個type——而事實上這就是這次重構的目的。每次都會多一次查詢。
2. 由於多次查詢且寫入相當頻繁的緣故，往往在兩次查詢之間，會有新的數據寫入，那麼兩次或者說多次查詢不能保證在同一時間維度之下，即多次查詢的經典問題，不具備原子性。

既然說到了每次最大值，那麼就是group by語句出馬的時候了；說到原子性，也就是必須一個命令搞定，於是就出爐了優化版本1.

SELECT n *nFROMn lognWHEREn id IN (SELECT n MAX(id)n FROMn logn GROUP BY type )n

不確定部署時的兼容性，就沒用group having。先是一個子查詢，查出每個type類型的最大id，然後再做一次查詢取出每種類型的最大id的記錄。這樣做的問題是需要先做一個range；接下來儘管是一個主鍵查詢，但畢竟還是一個full_table。一時犯懶，在5.7下測了300w級別的性能，感覺還能滿足需求，就直接交了差。

接下來沒多久需求變了，原先的「最後一條記錄」變成了「最後兩條記錄」。區區的一個小變化，由於沒有讓這條查詢賴以生存的max(id)函數無從下手。

既然有「最後兩條」的需求，那麼就排序了，可MySQL自古就沒有出過類似Oracle的rank()或者SQL Server的top那麼好用的方式，只能用反過頭來找傳說中的「臨時變數」。於是有了第二版

SELECT n *nFROMn (SELECT n a.*, n (CASE a.typen WHEN @t THEN @r:=@r + 1n ELSE @t:=a.typen END) AS rankn FROMn log a, (SELECT @r:=1, @t = 0) bn ORDER BY type , id DESC) cnnwhere rank < 3n

涉及了兩次全表查詢，一次file_sort。性能上跟上一次肯定有了一倍的差距，幾乎到了能接受的極限。繼續開動優化大法：

SELECT n *nFROMn (SELECT n a.*, n (CASE a.typen WHEN @t THEN @r:=@r + 1n ELSE @t:=a.typen END) AS rankn FROMn (SELECT * FROM log order by id desc limit 10000) a, (SELECT @r:=1, @t = 0) bn ORDER BY type, id desc ) cnnwhere rank < 3n

這次優化的思路在於：之前的filesort是由於大量的記錄都參與到了排序的過程中，性能奇差。我們假定所有命中的記錄都會出現在最後的10000條記錄里，那麼只要最後的10000條記錄參與排序就夠了。

最終，性能甚至高過了最初的方案！

各種結論：

1. MySQL真的是快，因為它是在裸奔！
2. 只要花點時間擠，性能總會有的。
3. SQL語句並不是越長性能越垃圾。
4. 數據結構真的很重要！

推薦閱讀：