歡迎光臨
每天分享高質量文章

8種常被忽視的SQL錯誤用法

sql陳述句的執行順序:

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. HAVING
  7. SELECT
  8. DISTINCT
  9. ORDER BY
  10. LIMIT

1. LIMIT 陳述句

分頁查詢是最常用的場景之一,但也通常也是最容易出問題的地方。比如對於下麵簡單的陳述句,一般 DBA 想到的辦法是在 type, name, create_time 欄位上加組合索引。這樣條件排序都能有效的利用到索引,效能迅速提升。

  1. SELECT * FROM   operation WHERE  type = 'SQLStats' 
  2. AND name = 'SlowLog' ORDER  BY create_time LIMIT  100010;

好吧,可能90%以上的 DBA 解決該問題就到此為止。但當 LIMIT 子句變成 “LIMIT 1000000,10” 時,程式員仍然會抱怨:我只取10條記錄為什麼還是慢?

要知道資料庫也並不知道第1000000條記錄從什麼地方開始,即使有索引也需要從頭計算一次。出現這種效能問題,多數情形下是程式員偷懶了。

在前端資料瀏覽翻頁,或者大資料分批匯出等場景下,是可以將上一頁的最大值當成引數作為查詢條件的。SQL 重新設計如下:

  1. SELECT * FROM operation WHERE type = 'SQLStats' 
  2. AND name = 'SlowLog' AND create_time > '2017-03-16 14:00:00' 
  3. ORDER BY create_time limit 10;

在新設計下查詢時間基本固定,不會隨著資料量的增長而發生變化。

2. 隱式轉換

SQL陳述句中查詢變數和欄位定義型別不匹配是另一個常見的錯誤。比如下麵的陳述句:

  1. mysql> explain extended SELECT * FROM  my_balance b 
  2.    > WHERE  b.bpn = 14000000123 
  3.    >       AND b.isverified IS NULL ;
  4. mysql> show warnings;
  5.  
  6. | Warning1739 | Cannot use ref access on index 'bpn' due to type or collation conversion on field 'bpn'

其中欄位 bpn 的定義為 varchar(20),MySQL 的策略是將字串轉換為數字之後再比較。函式作用於表欄位,索引失效。

上述情況可能是應用程式框架自動填入的引數,而不是程式員的原意。現在應用框架很多很繁雜,使用方便的同時也小心它可能給自己挖坑。

3. 關聯更新、刪除

雖然 MySQL5.6 引入了物化特性,但需要特別註意它目前僅僅針對查詢陳述句的最佳化。對於更新或刪除需要手工重寫成 JOIN。

比如下麵 UPDATE 陳述句,MySQL 實際執行的是迴圈/巢狀子查詢(DEPENDENT SUBQUERY),其執行時間可想而知。

  1. UPDATE operation o SET status = 'applying' WHERE  o.id 
  2. IN (SELECT id FROM (SELECT o.id,o.status FROM   operation o 
  3. WHERE  o.group123 AND o.status NOT IN ( 'done' )  
  4. ORDER  BY o.parent, o.id LIMIT  1) t);

執行計劃:

重寫為 JOIN 之後,子查詢的選擇樣式從 DEPENDENT SUBQUERY 變成 DERIVED,執行速度大大加快,從7秒降低到2毫秒。

  1. UPDATE operation o JOIN  (SELECT o.id, o.status FROM   operation o WHERE  o.group123 
  2. AND o.status NOT IN ( 'done' ) ORDER  BY o.parent,o.id LIMIT  1) t
  3. ON o.id = t.id SET    status = 'applying'

執行計劃簡化為:

4. 混合排序

MySQL 不能利用索引進行混合排序。但在某些場景,還是有機會使用特殊方法提升效能的。

  1. SELECT * FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.id 
  2. ORDER  BY a.is_reply ASC, a.appraise_time DESC LIMIT  020

執行計劃顯示為全表掃描:

由於 is_reply 只有0和1兩種狀態,我們按照下麵的方法重寫後,執行時間從1.58秒降低到2毫秒。

  1. SELECT * FROM (
  2. (SELECT * FROM my_order o INNER JOIN my_appraise a  ON a.orderid = o.id
  3. AND is_reply = 0 ORDER  BY appraise_time DESC LIMIT  020
  4.  
  5. UNION ALL 
  6. (SELECT * FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.id
  7. AND is_reply = 1 ORDER  BY appraise_time DESC LIMIT  020)) t 
  8. ORDER  BY  is_reply ASC, appraisetime DESC LIMIT  20;

5. EXISTS陳述句

MySQL 對待 EXISTS 子句時,仍然採用巢狀子查詢的執行方式。如下麵的 SQL 陳述句:

  1. SELECT *
  2. FROM   my_neighbor n 
  3.      LEFT JOIN my_neighbor_apply sra 
  4.             ON n.id = sra.neighbor_id 
  5.                AND sra.user_id = 'xxx' 
  6. WHERE  n.topic_status 4 
  7.      AND EXISTS(SELECT 1 
  8.                 FROM   message_info m 
  9.                 WHERE  n.id = m.neighbor_id 
  10.                        AND m.inuser = 'xxx'
  11.      AND n.topic_type <> 5

執行計劃為:

去掉 exists 更改為 join,能夠避免巢狀子查詢,將執行時間從1.93秒降低為1毫秒。

  1. SELECT *
  2. FROM   my_neighbor n 
  3.      INNER JOIN message_info m 
  4.              ON n.id = m.neighbor_id 
  5.                 AND m.inuser = 'xxx' 
  6.      LEFT JOIN my_neighbor_apply sra 
  7.             ON n.id = sra.neighbor_id 
  8.                AND sra.user_id = 'xxx' 
  9. WHERE  n.topic_status 4 
  10.      AND n.topic_type <> 5

新的執行計劃:

6. 條件下推

外部查詢條件不能夠下推到複雜的檢視或子查詢的情況有:

1、聚合子查詢; 2、含有 LIMIT 的子查詢; 3、UNION 或 UNION ALL 子查詢; 4、輸出欄位中的子查詢;

如下麵的陳述句,從執行計劃可以看出其條件作用於聚合子查詢之後:

  1. SELECT * 
  2. FROM   (SELECT target, 
  3.              Count(*) 
  4.       FROM   operation 
  5.       GROUP  BY target) t 
  6. WHERE  target = 'rm-xxxx'

確定從語意上查詢條件可以直接下推後,重寫如下:

  1. SELECT target, 
  2.      Count(*) 
  3. FROM   operation 
  4. WHERE  target = 'rm-xxxx' 
  5. GROUP  BY target

執行計劃變為:

關於 MySQL 外部條件不能下推的詳細解釋說明請參考以前文章:MySQL · 效能最佳化 · 條件下推到物化表 http://mysql.taobao.org/monthly/2016/07/08

7. 提前縮小範圍**

先上初始 SQL 陳述句:

  1. SELECT * 
  2. FROM   my_order o 
  3.      LEFT JOIN my_userinfo u 
  4.             ON o.uid = u.uid
  5.      LEFT JOIN my_productinfo p 
  6.             ON o.pid = p.pid 
  7. WHERE  ( o.display = 0 ) 
  8.      AND ( o.ostaus = 1 ) 
  9. ORDER  BY o.selltime DESC 
  10. LIMIT  015

該SQL陳述句原意是:先做一系列的左連線,然後排序取前15條記錄。從執行計劃也可以看出,最後一步估算排序記錄數為90萬,時間消耗為12秒。

由於最後 WHERE 條件以及排序均針對最左主表,因此可以先對 my_order 排序提前縮小資料量再做左連線。SQL 重寫後如下,執行時間縮小為1毫秒左右。

  1. SELECT * 
  2. FROM (
  3. SELECT * 
  4. FROM   my_order o 
  5. WHERE  ( o.display = 0 ) 
  6.      AND ( o.ostaus = 1 ) 
  7. ORDER  BY o.selltime DESC 
  8. LIMIT  015
  9. ) o 
  10.    LEFT JOIN my_userinfo u 
  11.             ON o.uid = u.uid 
  12.    LEFT JOIN my_productinfo p 
  13.             ON o.pid = p.pid 
  14. ORDER BY  o.selltime DESC
  15. limit 015

再檢查執行計劃:子查詢物化後(select_type=DERIVED)參與 JOIN。雖然估算行掃描仍然為90萬,但是利用了索引以及 LIMIT 子句後,實際執行時間變得很小。

8. 中間結果集下推

再來看下麵這個已經初步最佳化過的例子(左連線中的主表優先作用查詢條件):

  1. SELECT    a.*, 
  2.         c.allocated 
  3. FROM      ( 
  4.             SELECT   resourceid 
  5.             FROM     my_distribute d 
  6.                  WHERE    isdelete = 0 
  7.                  AND      cusmanagercode = '1234567' 
  8.                  ORDER BY salecode limit 20) a 
  9. LEFT JOIN 
  10.         ( 
  11.             SELECT   resourcesid, sum(ifnull(allocation, 0) * 12345) allocated 
  12.             FROM     my_resources 
  13.                  GROUP BY resourcesid) c 
  14. ON        a.resourceid = c.resourcesid

那麼該陳述句還存在其它問題嗎?不難看出子查詢 c 是全表聚合查詢,在表數量特別大的情況下會導致整個陳述句的效能下降。

其實對於子查詢 c,左連線最後結果集只關心能和主表 resourceid 能匹配的資料。因此我們可以重寫陳述句如下,執行時間從原來的2秒下降到2毫秒。

  1. SELECT    a.*, 
  2.         c.allocated 
  3. FROM      ( 
  4.                  SELECT   resourceid 
  5.                  FROM     my_distribute d 
  6.                  WHERE    isdelete = 0 
  7.                  AND      cusmanagercode = '1234567' 
  8.                  ORDER BY salecode limit 20) a 
  9. LEFT JOIN 
  10.         ( 
  11.                  SELECT   resourcesid, sum(ifnull(allocation, 0) * 12345) allocated 
  12.                  FROM     my_resources r, 
  13.                           ( 
  14.                                    SELECT   resourceid 
  15.                                    FROM     my_distribute d 
  16.                                    WHERE    isdelete = 0 
  17.                                    AND      cusmanagercode = '1234567' 
  18.                                    ORDER BY salecode limit 20) a 
  19.                  WHERE    r.resourcesid = a.resourcesid 
  20.                  GROUP BY resourcesid) c 
  21. ON        a.resourceid = c.resourcesid

但是子查詢 a 在我們的SQL陳述句中出現了多次。這種寫法不僅存在額外的開銷,還使得整個陳述句顯的繁雜。使用 WITH 陳述句再次重寫:

  1. WITH a AS 
  2.        SELECT   resourceid 
  3.        FROM     my_distribute d 
  4.        WHERE    isdelete = 0 
  5.        AND      cusmanagercode = '1234567' 
  6.        ORDER BY salecode limit 20)
  7. SELECT    a.*, 
  8.         c.allocated 
  9. FROM      a 
  10. LEFT JOIN 
  11.         ( 
  12.                  SELECT   resourcesid, sum(ifnull(allocation, 0) * 12345) allocated 
  13.                  FROM     my_resources r, 
  14.                           a 
  15.                  WHERE    r.resourcesid = a.resourcesid 
  16.                  GROUP BY resourcesid) c 
  17. ON        a.resourceid = c.resourcesid

總結

資料庫編譯器產生執行計劃,決定著SQL的實際執行方式。但是編譯器只是儘力服務,所有資料庫的編譯器都不是盡善盡美的。

上述提到的多數場景,在其它資料庫中也存在效能問題。瞭解資料庫編譯器的特性,才能避規其短處,寫出高效能的SQL陳述句。

程式員在設計資料模型以及編寫SQL陳述句時,要把演演算法的思想或意識帶進來。

編寫複雜SQL陳述句要養成使用 WITH 陳述句的習慣。簡潔且思路清晰的SQL陳述句也能減小資料庫的負擔 。

贊(0)

分享創造快樂