Java開發人員寫SQL時常犯的10個錯誤

標籤：

  

• 首頁
• 所有文章
• 資訊
• Web
• 架構
• 基礎技術
• 書籍
• 教程
• 我要投稿
• 更多頻道 »

- 導航條 -首頁所有文章資訊Web架構基礎技術書籍教程我要投稿更多頻道 »- iOS- Python- Android- Web前端  Java開發人員寫SQL時常犯的10個錯誤

2015/03/10 | 分類： 基礎技術 | 0 條評論 | 標籤： SQL

分享到：0本文由 ImportNew - zer0Black 翻譯自 jooq。歡迎加入翻譯小組。轉載請見文末要求。

我十分驚訝的發現，我最近的一篇文章——《Java開發人員寫SQL時常犯的10個錯誤》——最近在我的部落格和我的夥伴DZone上非常的受歡迎。（這篇部落格）的流行程度說明了幾件事：

• SQL在專業的Java開發中多麼重要。
• 基本的SQL知識被忘掉（的情況）普遍存在。
• 通過embracing SQL，你就能瞭解像 jOOQ或MyBatis這樣的以SQL為中心的庫正好反應了市場的需要。 令人驚喜的是有使用者提到了我部落格上貼的一篇“SLICK’s mailing list”，SLICK是Scala中的一個不以SQL為中心的資料庫訪問庫，像LINQ（還有LINQ-to-SQL），它側重語言整合，而不是SQL語句的產生。

無論如何，我之前倉促列出的常見錯誤還沒列完。因此我為你另外準備了10個沒那麼常見的，但Java開發人員在寫SQL語句時同樣愛犯的錯誤。

1、不用PreparedStatements

有意思的是，在JDBC出現了許多年後的今天，這個錯誤依然出現在部落格、論壇和郵件清單中，即便要記住和理解它是一件很簡單的事。開發人員不使用PreparedStatements的原因可能有如下幾個：

• 他們對PreparedStatements不瞭解
• 他們認為使用PreparedStatements太慢了
• 他們認為寫PreparedStatements太費力

來吧，我們來破除上面的謠言。96%的案例中，用PreparedStatement比靜態聲明語句更好。為什麼呢？就是下面這些簡單的原因：

• 使用內聯綁定值（inlining bind values）可以從源頭上避免糟糕的語句引起語法錯誤。
• 使用內聯綁定值可以避免糟糕的語句引起的SQL注入漏洞。
• 當插入更多“複雜的”資料類型（比如時間戳記、位元據等等）時，可以避免邊緣現象（edge-cases）。
• 你若保持PreparedStatements的串連開啟狀態而不馬上關閉，只要重新綁定新值就可以進行複用。
• 你可以在更多複雜的資料庫裡使用adaptive cursor sharing——自適應遊標共用（Oracle的說法）。這可以幫你在每次新設定綁定值時阻止SQL語句硬解析。

（譯者註：硬解析的弊端。硬解析即整個SQL語句的執行需要完完全全的解析，產生執行計畫。而硬解析，產生執行計畫需要耗用CPU資源，以及SGA資源。在此不得不提的是對庫緩衝中 閂的使用。閂是鎖的細化，可以理解為是一種輕量級的序列化裝置。當進程申請到閂後，則這些閂用於保護共用記憶體的數在同一時刻不會被兩個以上的進程修改。在 硬解析時，需要申請閂的使用，而閂的數量在有限的情況下需要等待。大量的閂的使用由此造成需要使用閂的進程排隊越頻繁，效能則逾低下）

某些特殊情況下你需要對值進行內聯綁定，這是為了給基於成本的效能最佳化器提示該查詢將要涉及的資料集。典型的情況是用“常量”判斷：

• DELETED = 1
• STATUS = 42

而不應該用一個“變數”判斷：

• FIRST_NAME LIKE “Jon%”
• AMOUNT > 19.95

要注意的是，現代資料庫已經實現了綁定資料窺探（bind-variable peeking）。因此，預設情況下，你也可以為你所有的查詢參數使用綁定值。在你寫嵌入的JPQL或嵌入的SQL時，用JPA CriteriaQuery或者jOOQ這類高層次的API可以很容易也很清晰的幫你產生PreparedStatements語句並綁定值。

更多的背景資料：

• 綁定資料窺探（bind-variable peeking）的附加說明：這個主題有一篇有趣的文章，它出自Oracle大師Tanel Poder之手。
• 遊標分享。StackOverflow上一個有趣的問題。

解決方案：

預設情況下，總是使用PreparedStatements來代替靜態聲明語句，而永遠不要在你的SQL語句嵌入內聯綁定值。

2、返回太多列

這個錯誤發生的非常頻繁，它不光會影響你的資料庫執行計畫，也會對你的Java應用造成不好的影響。讓我們先看看對後者的影響：

對Java程式的不良影響:

如 果你為了滿足不同DAO層之間的資料複用而select *或者預設的50個列，這樣將會有大量的資料從資料庫讀入到JDBC結果集中，即使你不從結果集讀取資料，它也被傳遞到了線路上並被JDBC磁碟機載入到 了記憶體中。如果你知道你只需要2-3列資料的話，這就造成了嚴重的IO和記憶體的浪費。

這個（問題的嚴重性）都是顯而易見的，要小心……

對資料庫執行計畫的不良影響：

這 些影響事實上可能比對Java應用的影響還要嚴重。當複雜的資料庫要針對你的查詢請求計算出最佳執行計畫時，它會進行大量的SQL轉換（SQL transformation ）。還好，請求中的一部分可以被略去，因為它們對SQL連映射或過濾條件起不了什麼作用。我最近寫了一篇部落格來講述這個問題：中繼資料模式會對Oracle查詢轉換產生怎樣的影響。

現在，給你展示一個錯誤的例子。想一想有兩個視圖的複雜查詢：

1234	SELECT *FROM  customer_view cJOIN  order_view oON  c.cust_id = o.cust_id

每個關聯了上述關聯表引用的視圖也可能再次關聯其他表的資料，像 CUSTOMER_ADDRESS、ORDER_HISTORY、ORDER_SETTLEMENT等等。進行select * 映射時，你的資料庫除了把所有串連表都載入進來以外別無選擇，實際上，你唯一感興趣的資料可能只有這些：

1234	SELECT c.first_name, c.last_name, o.amountFROM  customer_view cJOIN  order_view oON  c.cust_id = o.cust_id

一個好的資料庫會在轉換你的SQL語句時自動移除那些不需要的串連，這樣資料庫就只需要較少的IO和記憶體消耗。

解決方案：

永遠不要用select *（這樣的查詢）。也不要在執行不同請求時複用相同的映射。盡量嘗試減少映射到你所真正需要的資料。

需要注意的是，想在對象-關係映射（ORMs）上達成這個目標有些難。

3、把JOIN當做了SELECT的子句

對於效能或SQL語句的正確性來說，這不算錯。但是不管如何，SQL開發人員應該意識到JOIN子句不是SELECT語句的一部分。SQL standard 1992 定義了表引用：

123456789101112131415161718192021222324252627

6.3 <table reference> <table reference> ::=<table name> [ [ AS ] <correlation name>[ <left paren> <derived column list> <right paren> ] ]| <derived table> [ AS ] <correlation name>[ <left paren> <derived column list> <right paren> ]| <joined table> 7.4 <from clause> <from clause> ::=FROM <table reference> [ { <comma> <table reference> }... ] 7.5 <joined table> <joined table> ::=<cross join>| <qualified join>| <left paren> <joined table> <right paren> <cross join> ::=<table reference> CROSS JOIN <table reference> <qualified join> ::=<table reference> [ NATURAL ] [ <join type> ] JOIN<table reference> [ <join specification> ]

關聯資料庫是以表為中心的。許多的操作的某方面都是執行在物理表、串連表或派生表上的。為了有效寫出SQL語句，理解SELECT … FROM子句是以“，”分割表引用是非常重要的。

基於表引用（table references）的複雜性，一些資料庫也接受其它類型的複雜的表引用（table references），像INSERT、UPDATE、DELETE、MERGE。看看Oracle執行個體手冊，裡面解釋了如何建立可更新的視圖。

解決方案：

一定要考慮到，一般說來，FROM子句也是一個表引用（table references）。如果你寫了JOIN子句，要考慮這個JOIN子句是這個複雜的表引用的一部分：

1234	SELECT c.first_name, c.last_name, o.amountFROMcustomer_view cJOIN order_view oON c.cust_id = o.cust_id

4、使用ANSI 92標準之前串連文法

我 們已經說清了表引用是怎麼工作的（看上一節），因此我們應該達成共識，不論花費什麼代價，都應該避免使用ANSI 92標準之前的文法。就執行計畫而言，使用JOIN…ON子句或者WHERE子句來作串連謂語沒有什麼不同。但從可讀性和可維護性的角度看，在過濾條 件判斷和串連判斷中用WHERE子句會陷入不可自拔的泥沼，看看這個簡單的例子：

123456

SELECT c.first_name, c.last_name, o.amountFROM  customer_view c,order_view oWHERE  o.amount > 100AND    c.cust_id = o.cust_idAND    c.language = ‘en‘

你能找到join謂詞嗎？如果我們加入數十張表呢？當你使用外串連專有文法的時候會變得更糟，就像Oracle的（+）文法裡講的一樣。

解決方案：

一定要用ANSI 92標準的JOIN語句。不要把JOIN謂詞放到WHERE子句中。用ANSI 92標準之前的JOIN文法沒有半點鐘好處。

5、使用LIKE判定時忘了ESCAPE

SQL standard 1992 指出like判定應該如下：

12345

8.5 <like predicate> <like predicate> ::=<match value> [ NOT ] LIKE <pattern>[ ESCAPE <escape character> ]

當允許使用者對你的SQL查詢進行參數輸入時，就應該使用ESCAPE關鍵字。儘管資料中含有百分比符號（%）的情況很罕見，但底線（_）還是很常見的：

123	SELECT *FROM  tWHERE  t.x LIKE ‘some!_prefix%‘ ESCAPE ‘!‘

解決方案：

使用LIKE判定時，也要使用合適的ESCAPE

6、認為 NOT (A IN (X, Y)) 和 IN (X, Y) 的布爾值相反

對於NULLs，這是一個舉足輕重的細節！讓我們看看 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))的真正意思：

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

看起來和之前說的布爾值相反一樣？其實不是。如果X或Y中任何一個為NULL，NOT IN 條件產生的結果將是UNKNOWN，但是IN條件可能依然會返回一個布爾值。

或者換種說話，當 A IN (X, Y) 結果為TRUE或FALSE時，NOT(A IN (X, Y)) 結果為依然UNKNOWN而不是FALSE或TRUE。注意了，如果IN條件的右邊是一個子查詢，結果依舊。

不信？你自己看SQL Fiddle 去。它說了如下查詢給不出結果：

12345

SELECT 1WHERE    1 IN (NULL)UNION ALLSELECT 2WHERE NOT(1 IN (NULL))

更多細節可以參考我的上一篇部落格,上面寫了在同地區內不相容的一些SQL方言。

解決方案：

當涉及到可為NULL的列時，注意NOT IN條件。

7、認為NOT (A IS NULL)和A IS NOT NULL是一樣的

沒錯，我們記得處理NULL值的時候，SQL實現了三值邏輯。這就是我們能用NULL條件來檢測NULL值的原因。對嗎？沒錯。

但在NULL條件容易遺漏的情況下。要意識到下面這兩個條件僅僅在行值運算式（row value expressions）為1的時候才相等：

NOT (A IS NULL)
is not the same as A IS NOT NULL

如果A是一個大於1的行值運算式（row value expressions），正確的表將按照如下方式轉換：

• 如果A的所有值為NUll，A IS NULL為TRUE
• 如果A的所有值為NUll，NOT(A IS NULL) 為FALSE
• 如果A的所有值都不是NUll，A IS NOT NULL 為TRUE
• 如果A的所有值都不是NUll，NOT(A IS NOT NULL)  為FALSE

在我的上一篇部落格可以瞭解到更多細節。

解決方案：

當使用行值運算式（row value expressions）時，要注意NULL條件不一定能達到預期的效果。

8、不用行值運算式

行值運算式是SQL一個很棒的特性。SQL是一個以表格為中心的語言，表格又是以行為中心。通過建立能在同等級或行類型進行比較的點對點行模型，行值運算式讓你能更容易的描述複雜的判定條件。一個簡單的例子是，同時請求客戶的姓名

123	SELECT c.addressFROM  customer c,WHERE (c.first_name, c.last_name) = (?, ?)

可以看出，就將每行的謂詞左邊和與之對應的右邊比較這個文法而言，行值運算式的文法更加簡潔。特別是在有許多獨立條件通過AND串連的時候就特別有效。行值運算式允許你將相互聯絡的條件放在一起。對於有外鍵的JOIN運算式來說，它更有用：

1234	SELECT c.first_name, c.last_name, a.streetFROM  customer cJOIN  address aON  (c.id, c.tenant_id) = (a.id, a.tenant_id)

不幸的是，並不是所有資料庫都支援行值運算式。但SQL標準已經在1992對行值運算式進行了定義，如果你使用他們，像Oracle或Postgres這些的複雜資料庫可以使用它們計算出更好的執行計畫。在Use The Index, Luke這個頁面上有解析。

解決方案：

不管幹什麼都可以使用行值運算式。它們會讓你的SQL語句更加簡潔高效。

9、不定義足夠的限制條件（constraints）

我又要再次引用Tom Kyte 和 Use The Index, Luke 了。對你的中繼資料使用限制條件不能更贊了。首先，限制條件可以幫你防止資料質變，光這一點就很有用。但對我來說更重要的是，限制條件可以協助資料庫進行SQL語句轉換，資料庫可以決定。

• 哪些值是等價的
• 哪些子句是冗餘的
• 哪些子句是無效的（例如，會返回空值的語句）

有些開發人員可能認為限制條件會導致（資料庫）變慢。但相反，除非你插入大量的資料，對於大型操作是你可以禁用限制條件，或用一個無限制條件的臨時“載入表”，線下再把資料轉移到真實的表中。

解決方案：

儘可能定義足夠多的限制條件（constraints）。它們將幫你更好的執行資料庫請求。

10、認為50ms是一個快的查詢速度

NoSQL的炒作依然在繼續，許多公司認為它們像Twitter或Facebook一樣需要更快、擴充性更好的解決方案，想脫離ACID和關聯式模式橫向擴充。有些可能會成功（比如Twitter或Facebook），而其他的也許會走入誤區：

看這篇文章：https://twitter.com/codinghorror/status/347070841059692545。

對於那些仍被迫（或堅持）使用關係型資料 庫的公司，請不要自欺欺人的認為：“現在的關係型資料庫很慢，其實它們是被天花亂墜的宣傳弄快的”。實際上，它們真的很快，解析20Kb查詢文檔，計算 2000行執行計畫，如此龐大的執行，所需時間小於1ms，如果你和資料管理員（DBA）繼續最佳化調整資料庫，就能得到最大限度的運行。

它們會變慢的原因有兩種：一是你的應用濫用流行的ORM；二是ORM無法針對你複雜的查詢邏輯產生快的SQL語句。遇到這種情況，你就要考慮選擇像 JDBC、jOOQ 或MyBatis這樣的更貼近SQL核心，能更好的控制你的SQL語句的API。

因此，不要認為查詢速度50ms是很快或者可以接受的。完全不是！如果你程式已耗用時間是這樣的，請檢查你的執行計畫。這種潛在危險可能會在你執行更複雜的上下文或資料中爆發。

總結

SQL很有趣，同時在各種各樣的方面也很微妙。正如我的關於10個錯誤的部落格所展示的。跋山涉水也要掌握SQL是一件值得做的事。資料是你最有價值的資產。帶著尊敬的心態對待你的資料才能寫出更好的SQL語句。

Java開發人員寫SQL時常犯的10個錯誤