8個SQL錯誤：您是否犯了這些錯誤？

SQL編程既令人興奮又具有挑戰(zhàn)性。即使是經(jīng)驗豐富的SQL程序員，開發(fā)人員和數(shù)據(jù)庫管理員（DBA）有時也要面對SQL語言的挑戰(zhàn)。本文旨在幫助用戶識別此類嚴(yán)重錯誤并學(xué)習(xí)克服它們。

讓我們深入研究以下幾節(jié)中最嚴(yán)重的8個SQL錯誤。

1）LIMIT語句

分頁查詢是最常見的情況之一，但也是一個非常普遍的問題。例如，對于以下簡單語句，DBA通常為type，name和create_time字段添加復(fù)合索引。這種條件排序可有效使用索引并快速提高性能。這是90％以上的DBA解決此問題的常用方法。但是，當(dāng)LIMIT子句更改為" LIMIT 1000000,10"時，程序員經(jīng)常抱怨僅檢索10條記錄會花費太長時間。發(fā)生這種情況是因為數(shù)據(jù)庫不知道第1,000,000條記錄從何處開始。因此，即使索引可用，也必須從頭開始計算。通常由于程序員的懶惰而導(dǎo)致出現(xiàn)此性能問題。在前端數(shù)據(jù)瀏覽和分頁或大數(shù)據(jù)的批量導(dǎo)出之類的方案中，可以將上一頁的最大值用作查詢條件。重寫SQL代碼，如下所示：

使用新設(shè)計，查詢時間基本上是固定的，并且不會隨著數(shù)據(jù)量的增加而改變。

2）隱式轉(zhuǎn)換

當(dāng)查詢變量與SQL語句中的字段定義類型不匹配時，會發(fā)生另一種常見錯誤。以下語句就是這樣的一個例子：

bpn字段定義為varchar（20），MySQL策略是在比較之前將字符串轉(zhuǎn)換為數(shù)字。當(dāng)函數(shù)作用于表字段時，索引變?yōu)闊o效。前面的問題可能是由應(yīng)用程序框架自動完成的參數(shù)引起的，而不是由于程序員的有意識的錯誤。當(dāng)前，許多應(yīng)用程序框架都很復(fù)雜。盡管它們使用起來非常方便，但是您還必須意識到它們可能引起的潛在問題。

3）更新和刪除加入

盡管實例化功能是MySQL 5.6中引入的，但是請注意，目前僅針對查詢語句進(jìn)行了優(yōu)化。手動將UPDATE或DELETE語句重寫為JOIN語句。

例如，在下面的UPDATE語句中，MySQL實際上運行循環(huán)或嵌套子查詢（DEPENDENT SUBQUERY），并且執(zhí)行時間相對較長。

考慮以下執(zhí)行計劃。

將語句重寫為JOIN語句后，子查詢選擇模式將從DEPENDENT SUBQUERY更改為DERIVED，從而將所需時間從7秒減少到2毫秒。

請參考以下簡化的執(zhí)行計劃。

4）混合排序

MySQL不能將索引用于混合排序。但是，在某些情況下，用戶仍然可以使用特殊方法來提高性能。

執(zhí)行計劃以全表掃描形式呈現(xiàn)。

由于is_reply在按照方法重寫后僅具有狀態(tài)0和1，因此執(zhí)行時間從1.58秒減少到2毫秒。

5）EXISTS 陳述

MySQL仍然使用嵌套子查詢來處理EXISTS子句。例如，考慮下面的SQL語句：

請參考以下執(zhí)行計劃。

將EXISTS語句更改為JOIN語句可避免嵌套子查詢，并將執(zhí)行時間從1.93秒減少到1毫秒。

考慮下面的新執(zhí)行計劃。

6）有條件下推

在以下情況下，無法將外部查詢條件下推到復(fù)雜視圖或子查詢：

· 匯總子查詢

· LIMIT個子查詢

· UNION或UNION ALL子查詢

· 輸出字段中的子查詢

在以下語句的執(zhí)行計劃中，請注意，條件在聚集子查詢之后起作用。

確保直接向下推語義查詢條件，然后將其重寫如下：

請參考以下更新的執(zhí)行計劃。

7）提前縮小范圍

從初始SQL語句開始，如下所示。

數(shù)量為900,000，執(zhí)行時間為12秒。

由于最后一個WHERE條件和排序是在最左邊的主表上執(zhí)行的，因此在執(zhí)行左連接之前，首先要縮小數(shù)據(jù)量以進(jìn)行my_order排序。如下所示重寫SQL語句后，執(zhí)行時間減少到大約1毫秒。

查看執(zhí)行計劃。實現(xiàn)子查詢后，select_type = DERIVED參與JOIN操作。盡管估計要掃描的行數(shù)仍為900,000，但在應(yīng)用索引和LIMIT子句后，實際的執(zhí)行時間會減少。

8）下推中間結(jié)果集

讓我們看一下以下最初優(yōu)化的示例（查詢條件首先作用于左聯(lián)接中的主表）：

此聲明還有其他問題嗎？不難看出，子查詢c是全表聚合查詢。因此，當(dāng)表的數(shù)量特別多時，整個語句的性能會下降。

實際上，對于子查詢c，左聯(lián)接的最終結(jié)果集僅與匹配主表resourceid的數(shù)據(jù)有關(guān)。因此，按如下所示重寫該語句，以將執(zhí)行時間從2秒減少到2毫秒。

但是，子查詢多次出現(xiàn)在SQL語句中。此方法不僅會導(dǎo)致額外的開銷，而且會使整個語句更加復(fù)雜。使用WITH語句再次重寫該語句。

摘要

數(shù)據(jù)庫編譯器生成一個執(zhí)行計劃，該計劃確定SQL語句的實際執(zhí)行方法。但是，編譯器僅盡其所能提供服務(wù)，而沒有哪個數(shù)據(jù)庫編譯器是完美的。

在大多數(shù)上述情況下，其他數(shù)據(jù)庫中也會出現(xiàn)性能問題。您必須了解數(shù)據(jù)庫編譯器的功能，以避免其缺點并編寫高性能的SQL語句。

在設(shè)計數(shù)據(jù)模型和編寫SQL語句時，請結(jié)合您的算法思想和認(rèn)識。例如，在編寫復(fù)雜的SQL語句時，請盡可能使用WITH子句。簡單明了的SQL語句還可以減輕數(shù)據(jù)庫的負(fù)擔(dān)。

原始資料來源：

https://www.alibabacloud.com/blog/8-sql-pitfalls-are-you-making-these-mistakes_596168

(本文翻譯自Alibaba Cloud的文章《8 SQL Pitfalls: Are You Making These Mistakes?》，參考：https://medium.com/dataseries/8-sql-pitfalls-are-you-making-these-mistakes-b8f9d8181a1c)