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

[[331839]]

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

1)LIMIT語(yǔ)句

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

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

2)隱式轉(zhuǎn)換

當(dāng)查詢變量與SQL語(yǔ)句中的字段定義類(lèi)型不匹配時(shí)，會(huì)發(fā)生另一種常見(jiàn)錯(cuò)誤。 以下語(yǔ)句就是這樣的一個(gè)例子：

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

3)更新和刪除加入

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

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

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

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

請(qǐng)參考以下簡(jiǎn)化的執(zhí)行計(jì)劃。

4)混合排序

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

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

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

5)EXISTS 陳述

MySQL仍然使用嵌套子查詢來(lái)處理EXISTS子句。 例如，考慮下面的SQL語(yǔ)句：

請(qǐng)參考以下執(zhí)行計(jì)劃。

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

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

6)有條件下推

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

• 匯總子查詢
• LIMIT個(gè)子查詢
• UNION或UNION ALL子查詢
• 輸出字段中的子查詢

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

確保直接向下推語(yǔ)義查詢條件，然后將其重寫(xiě)如下：

請(qǐng)參考以下更新的執(zhí)行計(jì)劃。

7)提前縮小范圍

從初始SQL語(yǔ)句開(kāi)始，如下所示。

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

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

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

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

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

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

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

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

摘要

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

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

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