Mysql中常見的8種SQL錯誤用法

前言

MySQL在2016年仍維持強勁的資料庫流行度成長趨勢。越來越多的客戶將自己的應用建立在MySQL資料庫之上，甚至是從Oracle遷移到MySQL上。但也存在部分客戶在使用MySQL資料庫的過程中遇到一些例如回應時間慢，CPU打滿等情況。

阿里雲RDS專家服務團隊幫助雲端上客戶解決過許多緊急問題。現將《ApsaraDB專家診斷報告》中出現的部分常見SQL問題總結如下，供大家參考。

1、LIMIT 語句

分頁查詢是最常用的場景之一，但也通常也是最容易出問題的地方。

例如下面簡單的語句，一般 DBA 想到的辦法是在 type, name, create_time 欄位上加組合索引。這樣條件排序都能有效的利用到索引，效能迅速提升。

SELECT *  FROM   operation  WHERE  type = 'SQLStats'         AND name = 'SlowLog'  ORDER  BY create_time  LIMIT  1000, 10;

好吧，可能90%以上的 DBA 解決該問題就到此為止。

但當 LIMIT 子句變成 “LIMIT 1000000,10” 時，程式設計師還是會抱怨：我只取10筆記錄為什麼還是慢？

要知道資料庫也不知道第1000000筆記錄從什麼地方開始，即使有索引也需要從頭計算一次。出現這種效能問題，多數情形下是程式設計師偷懶了。在前端資料瀏覽翻頁，或大數據分批匯出等場景下，是可以將上一頁的最大值當成參數作為查詢條件的。 SQL 重新設計如下：

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

2、隱含轉換

SQL語句中查詢變數和欄位定義類型不符是另一個常見的錯誤。例如下面的語句：

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

上述情況可能是應用程式框架自動填入的參數，而不是程式設計師的原意。現在應用框架很多很繁雜，使用方便的同時也小心它可能會為自己挖坑。

3、關聯更新、刪除

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

例如下面 UPDATE 語句，MySQL 實際執行的是循環/巢狀子查詢（DEPENDENT SUBQUERY)，其執行時間可想而知。

執行計劃：

4、混合排序

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

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

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

5、EXISTS语句

MySQL 对待 EXISTS 子句时，仍然采用嵌套子查询的执行方式。如下面的 SQL 语句：

执行计划为：

去掉 exists 更改为 join，能够避免嵌套子查询，将执行时间从1.93秒降低为1毫秒。

新的执行计划：

6、条件下推

外部查询条件不能够下推到复杂的视图或子查询的情况有：

聚合子查询；

含有 LIMIT 的子查询；

UNION 或 UNION ALL 子查询；

输出字段中的子查询；

如下面的语句，从执行计划可以看出其条件作用于聚合子查询之后：

确定从语义上查询条件可以直接下推后，重写如下：

SELECT target Count(*)  FROM   operation  WHERE  target = 'rm-xxxx'  GROUP  BY target

执行计划变为：

关于 MySQL 外部条件不能下推的详细解释说明请参考文章：

http://mysql.taobao.org/monthly/2016/07/08

相了解更多相关问题请访问PHP中文网：mysql视频教程

以上是Mysql中常見的8種SQL錯誤用法的詳細內容。更多資訊請關注PHP中文網其他相關文章！