測試雜談——一條SQL引發的思考

溫一壺清酒發表於2022-03-21
SQL

此篇只是個人記錄，相信各位大神早已輕車熟路，不喜勿噴；有錯之處，歡迎指正。

有一天收到新人的諮詢，是關於sql的問題。

問題1：為什麼sql查詢的資料與介面展示的不準確；

問題2：為什麼sql查詢時間那麼久。

在仔細檢視sql後，問題得到解決。這個問題很簡單，來看sql，如下所示：

SELECT
 v.ep_id,
 COUNT(*)
FROM
 dw_l_v v 
WHERE
 v.id = 'XXX' 
 AND v.ep_id = 'XXX' 
 OR v.ep_id = 'XXX' 
 OR v.ep_id = 'XXX' 
 OR v.ep_id = 'XXX' 
 AND v.language = 'zh_cn'
GROUP BY
 v.ep_id;

問題給新人解答後，自己有感而發，所以稍加整理了此篇。

sql查詢資料不對

依然來看這2個問題，先看問題1，資料不對的問題。

這個問題很好解答，在SQL必知必會的章節中，也有提到該問題。出現這個問題的原因，就在於，資料過濾條件的優先順序了。

常用的邏輯運算子有：and / or / not
所對應的優先順序為：() > not > and > or

sql在處理OR操作符前，優先處理AND操作符。

由於上述sql沒有將OR操作符用小括號提高優先順序，從而導致先處理了AND操作符，再來處理OR操作符，最終結果就是資料不準確。

我們將sql修改成如下即可：

SELECT
 v.ep_id,
 COUNT(*)
FROM
 dw_l_v v 
WHERE
 v.id = 'XXX' 
 AND (v.ep_id = 'XXX' 
 OR v.ep_id = 'XXX' 
 OR v.ep_id = 'XXX' 
 OR v.ep_id = 'XXX' )
 AND v.language = 'zh_cn'
GROUP BY
 v.ep_id;

或者ep_id欄位條件用IN操作符來處理，修改如下：

SELECT
 v.ep_id,
 COUNT(*)
FROM
 dw_l_v v 
WHERE
 v.id = 'XXX' 
 AND v.ep_id IN （'XXX','XXX','XXX','XXX'）
 AND v.language = 'zh_cn'
GROUP BY
 v.ep_id;

sql調整後，再來執行核對資料，資料查詢準確。

我們從上述修改的sql看出，IN操作符與OR操作符實現的功能是一樣的。的確是這樣，最終得到的結果完全一致。

但IN操作符對比OR操作符，還有如下優點：

  • 存在很多合法選項時，IN操作符的語法更清楚、更直觀；
  • 在與其他AND和OR操作符組合使用IN時，求值順序更容易管理；
  • IN操作符一般比一組OR操作符執行的更快；
  • IN最大優點是可以包含其他 SELECT 語句，能夠動態的建立 WHERE 子句。

sql查詢時間久

在解決了sql查詢資料不對的問題，我們再來看sql查詢花費時間久的問題。

在mysql中，我們可以通過 EXPLAIN 來檢視sql的執行計劃。我們分別檢視修改前跟修改後sql的執行計劃，如下所示：

修改前的sql執行計劃：

修改後的sql執行計劃：

直接拿2張圖來對比，為什麼查詢慢，想必就很清楚了吧，一目瞭然。

從圖中可知，修改前的sql，rows 欄位，客戶端傳送160W+的資料量；而修改後的sql，rows 欄位，客戶端傳送2000+的資料量。先不對比其他的欄位，單憑這點就可以看出sql的問題所在了。

由於數量級的差異，從而sql查詢的花費時間也就不一樣了。

在不修改之前，sql查詢的時間要花費15s左右；而修改後的sql查詢，查詢結果的時間1s都不需要。

從上而知，平時在專案中的sql優化也是很有必要的。

在看完rows欄位後，我們再來看個type欄位。

通過type欄位可以看出，修改前的sql，是使用了全表掃描，所以看到這，也能解答為什麼客戶端傳送了160W+的資料量了，原因就是全表掃描的原因。

mysql執行計劃的其他欄位，大家可以自行百度瞭解，今天就先不多聊了。

好了，今天的分享就到這了，文章有誤之處，歡迎批評指正。

相關文章