最近的一篇文章——《Java開(kāi)發(fā)者寫(xiě)SQL時(shí)常犯的10個(gè)錯誤》——最近在DZone上非常的受歡迎。（這篇博客）的流行程度說(shuō)明了幾件事：
無(wú)論如何，我之前倉促列出的常見(jiàn)錯誤還沒(méi)列完。因此我為你另外準備了10個(gè)沒(méi)那么常見(jiàn)的，但Java開(kāi)發(fā)者在寫(xiě)SQL語(yǔ)句時(shí)同樣愛(ài)犯的錯誤。
1、不用PreparedStatements
有意思的是，在JDBC出現了許多年后的今天，這個(gè)錯誤依然出現在博客、論壇和郵件列表中，即便要記住和理解它是一件很簡(jiǎn)單的事。開(kāi)發(fā)者不使用PreparedStatements的原因可能有如下幾個(gè)：
來(lái)吧，我們來(lái)破除上面的謠言。96%的案例中，用PreparedStatement比靜態(tài)聲明語(yǔ)句更好。為什么呢？就是下面這些簡(jiǎn)單的原因：
（譯者注：硬解析的弊端。硬解析即整個(gè)SQL語(yǔ)句的執行需要完完全全的解析，生成執行計劃。而硬解析，生成執行計劃需要耗用CPU資源，以及SGA資源。在此不得不提的是對庫緩存中 閂的使用。閂是鎖的細化，可以理解為是一種輕量級的串行化設備。當進(jìn)程申請到閂后，則這些閂用于保護共享內存的數在同一時(shí)刻不會(huì )被兩個(gè)以上的進(jìn)程修改。在 硬解析時(shí)，需要申請閂的使用，而閂的數量在有限的情況下需要等待。大量的閂的使用由此造成需要使用閂的進(jìn)程排隊越頻繁，性能則逾低下）
某些特殊情況下你需要對值進(jìn)行內聯(lián)綁定，這是為了給基于成本的性能優(yōu)化器提示該查詢(xún)將要涉及的數據集。典型的情況是用“常量”判斷：
而不應該用一個(gè)“變量”判斷：
要注意的是，現代數據庫已經(jīng)實(shí)現了綁定數據窺探（bind-variable peeking）。因此，默認情況下，你也可以為你所有的查詢(xún)參數使用綁定值。在你寫(xiě)嵌入的JPQL或嵌入的SQL時(shí)，用JPA CriteriaQuery或者jOOQ這類(lèi)高層次的API可以很容易也很清晰的幫你生成PreparedStatements語(yǔ)句并綁定值。
更多的背景資料：
解決方案：
默認情況下，總是使用PreparedStatements來(lái)代替靜態(tài)聲明語(yǔ)句，而永遠不要在你的SQL語(yǔ)句嵌入內聯(lián)綁定值。
2、返回太多列
這個(gè)錯誤發(fā)生的非常頻繁，它不光會(huì )影響你的數據庫執行計劃，也會(huì )對你的Java應用造成不好的影響。讓我們先看看對后者的影響：
對Java程序的不良影響:
如 果你為了滿(mǎn)足不同DAO層之間的數據復用而select *或者默認的50個(gè)列，這樣將會(huì )有大量的數據從數據庫讀入到JDBC結果集中，即使你不從結果集讀取數據，它也被傳遞到了線(xiàn)路上并被JDBC驅動(dòng)器加載到 了內存中。如果你知道你只需要2-3列數據的話(huà)，這就造成了嚴重的IO和內存的浪費。
這個(gè)（問(wèn)題的嚴重性）都是顯而易見(jiàn)的，要小心……
對數據庫執行計劃的不良影響：
這 些影響事實(shí)上可能比對Java應用的影響還要嚴重。當復雜的數據庫要針對你的查詢(xún)請求計算出最佳執行計劃時(shí)，它會(huì )進(jìn)行大量的SQL轉換（SQL transformation ）。還好，請求中的一部分可以被略去，因為它們對SQL連映射或過(guò)濾條件起不了什么作用。我最近寫(xiě)了一篇博客來(lái)講述這個(gè)問(wèn)題：元數據模式會(huì )對Oracle查詢(xún)轉換產(chǎn)生怎樣的影響。
現在，給你展示一個(gè)錯誤的例子。想一想有兩個(gè)視圖的復雜查詢(xún)：
每個(gè)關(guān)聯(lián)了上述關(guān)聯(lián)表引用的視圖也可能再次關(guān)聯(lián)其他表的數據，像 CUSTOMER_ADDRESS、ORDER_HISTORY、ORDER_SETTLEMENT等等。進(jìn)行select * 映射時(shí)，你的數據庫除了把所有連接表都加載進(jìn)來(lái)以外別無(wú)選擇，實(shí)際上，你唯一感興趣的數據可能只有這些：
一個(gè)好的數據庫會(huì )在轉換你的SQL語(yǔ)句時(shí)自動(dòng)移除那些不需要的連接，這樣數據庫就只需要較少的IO和內存消耗。
解決方案：
永遠不要用select *（這樣的查詢(xún)）。也不要在執行不同請求時(shí)復用相同的映射。盡量嘗試減少映射到你所真正需要的數據。
需要注意的是，想在對象-關(guān)系映射（ORMs）上達成這個(gè)目標有些難。
3、把JOIN當做了SELECT的子句
對于性能或SQL語(yǔ)句的正確性來(lái)說(shuō)，這不算錯。但是不管如何，SQL開(kāi)發(fā)者應該意識到JOIN子句不是SELECT語(yǔ)句的一部分。SQL standard 1992定義了表引用：
關(guān)聯(lián)數據庫是以表為中心的。許多的操作的某方面都是執行在物理表、連接表或派生表上的。為了有效的寫(xiě)出SQL語(yǔ)句，理解SELECT … FROM子句是以“，”分割表引用是非常重要的。
基于表引用（table references）的復雜性，一些數據庫也接受其它類(lèi)型的復雜的表引用（table references），像INSERT、UPDATE、DELETE、MERGE?？纯?/span>Oracle實(shí)例手冊，里面解釋了如何創(chuàng )建可更新的視圖。
解決方案：
一定要考慮到，一般說(shuō)來(lái)，FROM子句也是一個(gè)表引用（table references）。如果你寫(xiě)了JOIN子句，要考慮這個(gè)JOIN子句是這個(gè)復雜的表引用的一部分：
4、使用ANSI 92標準之前連接語(yǔ)法
我 們已經(jīng)說(shuō)清了表引用是怎么工作的（看上一節），因此我們應該達成共識，不論花費什么代價(jià)，都應該避免使用ANSI 92標準之前的語(yǔ)法。就執行計劃而言，使用JOIN…ON子句或者WHERE子句來(lái)作連接謂語(yǔ)沒(méi)有什么不同。但從可讀性和可維護性的角度看，在過(guò)濾條 件判斷和連接判斷中用WHERE子句會(huì )陷入不可自拔的泥沼，看看這個(gè)簡(jiǎn)單的例子：
你能找到j(luò )oin謂詞么？如果我們加入數十張表呢？當你使用外連接專(zhuān)有語(yǔ)法的時(shí)候會(huì )變得更糟，就像Oracle的（+）語(yǔ)法里講的一樣。
解決方案：
一定要用ANSI 92標準的JOIN語(yǔ)句。不要把JOIN謂詞放到WHERE子句中。用ANSI 92標準之前的JOIN語(yǔ)法沒(méi)有半點(diǎn)好處。
5、使用LIKE判定時(shí)忘了ESCAPE
SQL standard 1992指出like判定應該如下：
當允許用戶(hù)對你的SQL查詢(xún)進(jìn)行參數輸入時(shí)，就應該使用ESCAPE關(guān)鍵字。盡管數據中含有百分號（%）的情況很罕見(jiàn)，但下劃線(xiàn)（_）還是很常見(jiàn)的：
解決方案：
使用LIKE判定時(shí)，也要使用合適的ESCAPE
6、認為 NOT (A IN (X, Y)) 和 IN (X, Y) 的布爾值相反
對于NULLs，這是一個(gè)舉足輕重的細節！讓我們看看 A IN (X, Y) 真正意思吧：
A IN (X, Y)
is the same as A = ANY (X, Y)
is the same as A = X OR A = Y
When at the same time, NOT (A IN (X, Y)) really means:
NOT (A IN (X, Y))
is the same as A NOT IN (X, Y)
is the same as A != ANY (X, Y)
is the same as A != X AND A != Y
看起來(lái)和之前說(shuō)的布爾值相反一樣？其實(shí)不是。如果X或Y中任何一個(gè)為NULL，NOT IN 條件產(chǎn)生的結果將是UNKNOWN，但是IN條件可能依然會(huì )返回一個(gè)布爾值。
或者換種說(shuō)話(huà)，當 A IN (X, Y) 結果為T(mén)RUE或FALSE時(shí)，NOT(A IN (X, Y)) 結果為依然UNKNOWN而不是FALSE或TRUE。注意了，如果IN條件的右邊是一個(gè)子查詢(xún)，結果依舊。
不信？你自己看SQL Fiddle 去。它說(shuō)了如下查詢(xún)給不出結果：
更多細節可以參考我的上一篇博客,上面寫(xiě)了在同區域內不兼容的一些SQL方言。
解決方案：
當涉及到可為NULL的列時(shí)，注意NOT IN條件。
7、認為NOT (A IS NULL)和A IS NOT NULL是一樣的
沒(méi)錯，我們記得處理NULL值的時(shí)候，SQL實(shí)現了三值邏輯。這就是我們能用NULL條件來(lái)檢測NULL值的原因。對么？沒(méi)錯。
但在NULL條件容易遺漏的情況下。要意識到下面這兩個(gè)條件僅僅在行值表達式（row value expressions）為1的時(shí)候才相等：
NOT (A IS NULL)
is not the same as A IS NOT NULL
如果A是一個(gè)大于1的行值表達式（row value expressions），正確的表將按照如下方式轉換：
在我的上一篇博客可以了解到更多細節。
解決方案：
當使用行值表達式（row value expressions）時(shí)，要注意NULL條件不一定能達到預期的效果。
8、不用行值表達式
行值表達式是SQL一個(gè)很棒的特性。SQL是一個(gè)以表格為中心的語(yǔ)言，表格又是以行為中心。通過(guò)創(chuàng )建能在同等級或行類(lèi)型進(jìn)行比較的點(diǎn)對點(diǎn)行模型，行值表達式讓你能更容易的描述復雜的判定條件。一個(gè)簡(jiǎn)單的例子是，同時(shí)請求客戶(hù)的姓名
可以看出，就將每行的謂詞左邊和與之對應的右邊比較這個(gè)語(yǔ)法而言，行值表達式的語(yǔ)法更加簡(jiǎn)潔。特別是在有許多獨立條件通過(guò)AND連接的時(shí)候就特別有效。行值表達式允許你將相互聯(lián)系的條件放在一起。對于有外鍵的JOIN表達式來(lái)說(shuō)，它更有用：
不幸的是，并不是所有數據庫都支持行值表達式。但SQL標準已經(jīng)在1992對行值表達式進(jìn)行了定義，如果你使用他們，像Oracle或Postgres這些的復雜數據庫可以使用它們計算出更好的執行計劃。在Use The Index, Luke這個(gè)頁(yè)面上有解析。
解決方案：
不管干什么都可以使用行值表達式。它們會(huì )讓你的SQL語(yǔ)句更加簡(jiǎn)潔高效。
9、不定義足夠的限制條件（constraints）
我又要再次引用Tom Kyte 和 Use The Index, Luke 了。對你的元數據使用限制條件不能更贊了。首先，限制條件可以幫你防止數據質(zhì)變，光這一點(diǎn)就很有用。但對我來(lái)說(shuō)更重要的是，限制條件可以幫助數據庫進(jìn)行SQL語(yǔ)句轉換，數據庫可以決定。
有些開(kāi)發(fā)者可能認為限制條件會(huì )導致（數據庫）變慢。但相反，除非你插入大量的數據，對于大型操作是你可以禁用限制條件，或用一個(gè)無(wú)限制條件的臨時(shí)“載入表”，線(xiàn)下再把數據轉移到真實(shí)的表中。
解決方案：
盡可能定義足夠多的限制條件（constraints）。它們將幫你更好的執行數據庫請求。
10、認為50ms是一個(gè)快的查詢(xún)速度
NoSQL的炒作依然在繼續，許多公司認為它們像Twitter或Facebook一樣需要更快、擴展性更好的解決方案，想脫離ACID和關(guān)系模型橫向擴展。有些可能會(huì )成功（比如Twitter或Facebook），而其他的也許會(huì )走入誤區：
看這篇文章：https://twitter.com/codinghorror/status/347070841059692545。
對于那些仍被迫（或堅持）使用關(guān)系型數據 庫的公司，請不要自欺欺人的認為：“現在的關(guān)系型數據庫很慢，其實(shí)它們是被天花亂墜的宣傳弄快的”。實(shí)際上，它們真的很快，解析20Kb查詢(xún)文檔，計算 2000行執行計劃，如此龐大的執行，所需時(shí)間小于1ms，如果你和數據管理員（DBA）繼續優(yōu)化調整數據庫，就能得到最大限度的運行。
它們會(huì )變慢的原因有兩種：一是你的應用濫用流行的ORM；二是ORM無(wú)法針對你復雜的查詢(xún)邏輯產(chǎn)生快的SQL語(yǔ)句。遇到這種情況，你就要考慮選擇像 JDBC、jOOQ 或MyBatis這樣的更貼近SQL核心，能更好的控制你的SQL語(yǔ)句的API。
因此，不要認為查詢(xún)速度50ms是很快或者可以接受的。完全不是！如果你程序運行時(shí)間是這樣的，請檢查你的執行計劃。這種潛在危險可能會(huì )在你執行更復雜的上下文或數據中爆發(fā)。
總結
SQL很有趣，同時(shí)在各種各樣的方面也很微妙。正如我的關(guān)于10個(gè)錯誤的博客所展示的。跋山涉水也要掌握SQL是一件值得做的事。數據是你最有價(jià)值的資產(chǎn)。帶著(zhù)尊敬的心態(tài)對待你的數據才能寫(xiě)出更好的SQL語(yǔ)句。