一個 20 秒 SQL 慢查詢優化處理方案

Posted on by

1.背景

頁面無法正確獲取數據，經排查原來是接口調用超時，而最後發現是因為SQL查詢長達到20多秒而導致瞭問題的發生。
這裡，沒有高深的理論或技術，隻是備忘一下經歷和解讀一些思想誤區。

2.復雜SQL語句的構成

這裡不過多對業務功能進行描述，但為瞭突出問題所在，會用類比的語句來描述當時的場景

復雜的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
)

3.關聯查詢

從上面簡化的SQL語句，可以看出，首先進行的是關聯查詢。

4.子查詢

其次，是嵌套的子查詢。此子查詢是為瞭找出多個用戶共同擁有的組ID。所以語句中的“100,102,103”是根據場景來定的，並且需要和後面“count(id) > 3”的個數對應。簡單來說，就是找用戶交集的組ID。

5.耗時在哪？

假設現在a_table表的數據量為20W，而b_table的數據量為2000W。大傢可以想一下，你覺得主要的耗時是在關聯查詢部分，還是在子查詢部分？
（思考空間。。。。）
（思考空間。。。。。。。）
（思考空間。。。。。。。。。。）

6.問題定位

對於SQL底層的原理和高深的理論，我暫時掌握不夠深入。但我知道可以通過類比和簡單的測試來驗證是哪一塊環節出瞭問題。

7.初步斷定

首先，對於隻有一個用戶ID時，我會把上面的語句簡化成：

ELECT * FROM a_table AS a 
LEFT JOIN b_table AS b ON a.id=b.id 
WHERE user_id IN (100)

所以，初步斷定應該是嵌套的子查詢部分占用瞭大部分的時間。

9.再進一步驗證

既然定位到瞭是嵌套的子查詢語句的問題，那又要分為兩塊待排查的區域：是子查詢本身耗時大，還是嵌套而導致慢查詢？
結果很容易發現，當我把子查詢單獨在DB中執行時，是非常快的。所以排除。
剩下的不言而喻，20秒的慢查詢是嵌套引起的。

但因為處於上線緊急的過程中，為瞭確保，我快速地驗證瞭我的結論：

  • 1、將子查詢的ID單獨執行，並把得到的結果序列手動拼成一段ID，如：1,2,3,4, … , 999
  • 2、將上面得到的序列ID，手動替換到原來的SQL語句
  • 3、執行，發現，很快！隻用瞭約150 ms

Well Done!  準備修復上線！

10.解決方案

線上的問題，很多時間都是在定位問題和分析原因，既然問題找到瞭，原因也找到瞭，解決方案不言而喻。代碼簡單處理即可。

11.另外一個需要註意的點

當前，實際的SQL語句，會比這個更為復雜，但已足以表達問題所在。但在前期，筆者也做瞭一些SQL的代碼。
因為b_table比a_table大，所以一開始b_table 左關聯a_table 時，很慢，大概是1秒多，而且數據量是很少的；但若反過來，a_table 左關聯b_table 時，則很快，大概是100毫秒。

所以，又發現一個有趣的現象：

大表 左關聯 小表，很慢；小表 左關聯 大表，很快。
當然，這些我們理論上都知道，但實際開發會忘卻。又或者一開始兩個表都為空時，而又沒考慮到後期這兩個表增長的速度時，日後就會埋下坑瞭。

總結：

首先，嵌套的子查詢是很慢的。
原因，我還沒仔細去研究，但在下班的路上和我的同事交流時，他說曾經看過這方面相關的書籍，是說每一次的子查詢都會產生一個SQL語句，所以就N次查詢瞭。而另外一位資深的QA同事則跟我說，應該是M*N的問題。
其次，我一開始使用嵌套子查詢，是存在這樣一個誤區：我覺得將這些操作交給MySQL自身來處理會更高效，畢竟DB內部會有良好的機制來執行這些查詢由。
然後，實際表白，我錯瞭。因為這不是簡單的合並MC批量查詢。
當我們決定使用一些底層的技術時，隻有當我們理解透徹瞭，才能使用更為恰當。而因為無知就斷定工具、框架、底層無所不能時，往往就會中招。

到此這篇關於一個 20 秒 SQL 慢查詢優化的經歷與處理方案的文章就介紹到這瞭,更多相關 SQL 慢查詢優化的經歷與處理方案內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：