8個SQL錯誤：您是否犯了這些錯誤？
SQL編程既令人興奮又具有挑戰性。 即使是經驗豐富的SQL程序員，開發人員和數據庫管理員(DBA)有時也要面對SQL語言的挑戰。 本文旨在幫助用戶識別此類嚴重錯誤并學習克服它們。
讓我們深入研究以下幾節中最嚴重的8個SQL錯誤。
1)LIMIT語句
分頁查詢是最常見的情況之一，但也是一個非常普遍的問題。 例如，對于以下簡單語句，DBA通常為type，name和create_time字段添加復合索引。 這種條件排序可有效使用索引并快速提高性能。 這是90%以上的DBA解決此問題的常用方法。 但是，當LIMIT子句更改為" LIMIT 1000000,10"時，程序員經常抱怨僅檢索10條記錄會花費太長時間。 發生這種情況是因為數據庫不知道第1,000,000條記錄從何處開始。 因此，即使索引可用，也必須從頭開始計算。 通常由于程序員的懶惰而導致出現此性能問題。 在前端數據瀏覽和分頁或大數據的批量導出之類的方案中，可以將上一頁的最大值用作查詢條件。 重寫SQL代碼，如下所示：
使用新設計，查詢時間基本上是固定的，并且不會隨著數據量的增加而改變。
2)隱式轉換
當查詢變量與SQL語句中的字段定義類型不匹配時，會發生另一種常見錯誤。 以下語句就是這樣的一個例子：
bpn字段定義為varchar(20)，MySQL策略是在比較之前將字符串轉換為數字。 當函數作用于表字段時，索引變為無效。 前面的問題可能是由應用程序框架自動完成的參數引起的，而不是由于程序員的有意識的錯誤。 當前，許多應用程序框架都很復雜。 盡管它們使用起來非常方便，但是您還必須意識到它們可能引起的潛在問題。
3)更新和刪除加入
盡管實例化功能是MySQL 5.6中引入的，但是請注意，目前僅針對查詢語句進行了優化。 手動將UPDATE或DELETE語句重寫為JOIN語句。
例如，在下面的UPDATE語句中，MySQL實際上運行循環或嵌套子查詢(DEPENDENT SUBQUERY)，并且執行時間相對較長。
考慮以下執行計劃。
將語句重寫為JOIN語句后，子查詢選擇模式將從DEPENDENT SUBQUERY更改為DERIVED，從而將所需時間從7秒減少到2毫秒。
請參考以下簡化的執行計劃。
4)混合排序
MySQL不能將索引用于混合排序。 但是，在某些情況下，用戶仍然可以使用特殊方法來提高性能。
執行計劃以全表掃描形式呈現。
由于is_reply在按照方法重寫后僅具有狀態0和1，因此執行時間從1.58秒減少到2毫秒。
5)EXISTS 陳述
MySQL仍然使用嵌套子查詢來處理EXISTS子句。 例如，考慮下面的SQL語句：
請參考以下執行計劃。
將EXISTS語句更改為JOIN語句可避免嵌套子查詢，并將執行時間從1.93秒減少到1毫秒。
考慮下面的新執行計劃。
6)有條件下推
在以下情況下，無法將外部查詢條件下推到復雜視圖或子查詢：
- 匯總子查詢
- LIMIT個子查詢
- UNION或UNION ALL子查詢
- 輸出字段中的子查詢
在以下語句的執行計劃中，請注意，條件在聚集子查詢之后起作用。
確保直接向下推語義查詢條件，然后將其重寫如下：
請參考以下更新的執行計劃。
7)提前縮小范圍
從初始SQL語句開始，如下所示。
數量為900,000，執行時間為12秒。
由于最后一個WHERE條件和排序是在最左邊的主表上執行的，因此在執行左連接之前，首先要縮小數據量以進行my_order排序。 如下所示重寫SQL語句后，執行時間減少到大約1毫秒。
查看執行計劃。 實現子查詢后，select_type = DERIVED參與JOIN操作。 盡管估計要掃描的行數仍為900,000，但在應用索引和LIMIT子句后，實際的執行時間會減少。
8)下推中間結果集
讓我們看一下以下最初優化的示例(查詢條件首先作用于左聯接中的主表)：
此聲明還有其他問題嗎? 不難看出，子查詢c是全表聚合查詢。 因此，當表的數量特別多時，整個語句的性能會下降。
實際上，對于子查詢c，左聯接的最終結果集僅與匹配主表resourceid的數據有關。 因此，按如下所示重寫該語句，以將執行時間從2秒減少到2毫秒。
但是，子查詢多次出現在SQL語句中。 此方法不僅會導致額外的開銷，而且會使整個語句更加復雜。 使用WITH語句再次重寫該語句。
摘要
數據庫編譯器生成一個執行計劃，該計劃確定SQL語句的實際執行方法。 但是，編譯器僅盡其所能提供服務，而沒有哪個數據庫編譯器是完美的。
在大多數上述情況下，其他數據庫中也會出現性能問題。 您必須了解數據庫編譯器的功能，以避免其缺點并編寫高性能的SQL語句。
在設計數據模型和編寫SQL語句時，請結合您的算法思想和認識。 例如，在編寫復雜的SQL語句時，請盡可能使用WITH子句。 簡單明了的SQL語句還可以減輕數據庫的負擔。
