一個20秒SQL慢查詢優(yōu)化的經(jīng)歷與處理方案
背景
前幾天在項目上線過程中，發(fā)現(xiàn)有一個頁面無法正確獲取數(shù)據(jù)，經(jīng)排查原來是接口調(diào)用超時，而最后發(fā)現(xiàn)是因為SQL查詢長達到20多秒而導(dǎo)致了問題的發(fā)生。
這里，沒有高深的理論或技術(shù)，只是備忘一下經(jīng)歷和解讀一些思想誤區(qū)。
復(fù)雜SQL語句的構(gòu)成
這里不過多對業(yè)務(wù)功能進行描述，但為了突出問題所在，會用類比的語句來描述當時的場景。復(fù)雜的SQL語句可以表達如下：
- SELECT * FROM a_table AS a
- LEFT JOIN b_table AS b ON a.id=b.id
- WHERE a.id IN (
- SELECT DISTINCT id FROM a_table
- WHERE user_id IN (100,102,103) GROUP BY user_id HAVING count(id) > 3
- )
關(guān)聯(lián)查詢
從上面簡化的SQL語句，可以看出，首先進行的是關(guān)聯(lián)查詢。
子查詢
其次，是嵌套的子查詢。此子查詢是為了找出多個用戶共同擁有的組ID。所以語句中的“100,102,103”是根據(jù)場景來定的，并且需要和后面“count(id) > 3”的個數(shù)對應(yīng)。簡單來說，就是找用戶交集的組ID。
耗時在哪?
假設(shè)現(xiàn)在a_table表的數(shù)據(jù)量為20W，而b_table的數(shù)據(jù)量為2000W。大家可以想一下，你覺得主要的耗時是在關(guān)聯(lián)查詢部分，還是在子查詢部分?
(思考空間。。。。)
(思考空間。。。。 。。。)
(思考空間。。。。 。。。 。。。)
問題定位
對于SQL底層的原理和高深的理論，我暫時掌握不夠深入。但我知道可以通過類比和簡單的測試來驗證是哪一塊環(huán)節(jié)出了問題。
初步斷定
首先，對于只有一個用戶ID時，我會把上面的語句簡化成：
- SELECT * FROM a_table AS a
- LEFT JOIN b_table AS b ON a.id=b.id
- WHERE user_id IN (100)
所以，初步斷定應(yīng)該是嵌套的子查詢部分占用了大部分的時間。
#p#
再進一步驗證
既然定位到了是嵌套的子查詢語句的問題，那又要分為兩塊待排查的區(qū)域：是子查詢本身耗時大，還是嵌套而導(dǎo)致慢查詢?
結(jié)果很容易發(fā)現(xiàn)，當我把子查詢單獨在DB中執(zhí)行時，是非?？斓?。所以排除。
剩下的不言而喻，20秒的慢查詢是嵌套引起的。
但因為處于上線緊急的過程中，為了確保，我快速地驗證了我的結(jié)論：
1、將子查詢的ID單獨執(zhí)行，并把得到的結(jié)果序列手動拼成一段ID，如：1,2,3,4, ... , 999
2、將上面得到的序列ID，手動替換到原來的SQL語句
3、執(zhí)行，發(fā)現(xiàn)，很快!只用了約150 ms
Well Done! 準備修復(fù)上線!
解決方案
線上的問題，很多時間都是在定位問題和分析原因，既然問題找到了，原因也找到了，解決方案不言而喻。代碼簡單處理即可。
另外一個需要注意的點
當前，實際的SQL語句，會比這個更為復(fù)雜，但已足以表達問題所在。但在前期，筆者也做了一些SQL的代碼。
因為b_table比a_table大，所以一開始 b_table 左關(guān)聯(lián) a_table 時，很慢，大概是1秒多，而且數(shù)據(jù)量是很少的;但若反過來，a_table 左關(guān)聯(lián) b_table 時，則很快，大概是100毫秒。
所以，又發(fā)現(xiàn)一個有趣的現(xiàn)象：
大表 左關(guān)聯(lián) 小表，很慢;小表 左關(guān)聯(lián) 大表，很快。
當然，這些我們理論上都知道，但實際開發(fā)會忘卻。又或者一開始兩個表都為空時，而又沒考慮到后期這兩個表增長的速度時，日后就會埋下坑了。
總結(jié)
首先，嵌套的子查詢是很慢的。
原因，我還沒仔細去研究，但在下班的路上和我的同事交流時，他說曾經(jīng)看過這方面相關(guān)的書籍，是說每一次的子查詢都會產(chǎn)生一個SQL語句，所以就N次查詢了。而另外一位資深的QA同事則跟我說，應(yīng)該是M*N的問題。
其次，我一開始使用嵌套子查詢，是存在這樣一個誤區(qū)：我覺得將這些操作交給MySQL自身來處理會更高效，畢竟DB內(nèi)部會有良好的機制來執(zhí)行這些查詢由。
然后，實際表白，我錯了。因為這不是簡單的合并MC批量查詢。
當我們決定使用一些底層的技術(shù)時，只有當我們理解透徹了，才能使用更為恰當。而因為無知就斷定工具、框架、底層無所不能時，往往就會中招。
