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  1000, 10;

好吧，可能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. | Warning | 1739 | 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.group = 123 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.group = 123 
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  0, 20

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

由於 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  0, 20) 
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  0, 20)) 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  0, 15

該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  0, 15
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 0, 15

再檢查執行計劃：子查詢物化後（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. ( 
3.        SELECT   resourceid 
4.        FROM     my_distribute d 
5.        WHERE    isdelete = 0 
6.        AND      cusmanagercode = '1234567' 
7.        ORDER BY salecode limit 20)
8. SELECT    a.*, 
9.         c.allocated 
10. FROM      a 
11. LEFT JOIN 
12.         ( 
13.                  SELECT   resourcesid， sum(ifnull(allocation, 0) * 12345) allocated 
14.                  FROM     my_resources r, 
15.                           a 
16.                  WHERE    r.resourcesid = a.resourcesid 
17.                  GROUP BY resourcesid) c 
18. ON        a.resourceid = c.resourcesid

總結

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

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

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

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