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

jopen 9年前發布 | 23K 次閱讀 SQL Java開發

本文由 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語句的產生。
    • </ul>

    無論如何，我之前倉促列出的常見錯誤還沒列完。因此我為你另外準備了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語句并綁定值。

    更多的背景資料：

    解決方案：

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

    2、返回太多列

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

    對Java程序的不良影響:

    如 果你為了滿足不同DAO層之間的數據復用而select *或者默認的50個列，這樣將會有大量的數據從數據庫讀入到JDBC結果集中，即使你不從結果集讀取數據，它也被傳遞到了線路上并被JDBC驅動器加載到 了內存中。如果你知道你只需要2-3列數據的話，這就造成了嚴重的IO和內存的浪費。

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

    對數據庫執行計劃的不良影響：

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

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

    SELECT *
FROM  customer_view c
JOIN  order_view o
ON  c.cust_id = o.cust_id

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

    SELECT c.first_name, c.last_name, o.amount
FROM  customer_view c
JOIN  order_view o
ON  c.cust_id = o.cust_id

    一個好的數據庫會在轉換你的SQL語句時自動移除那些不需要的連接，這樣數據庫就只需要較少的IO和內存消耗。

    解決方案：

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

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

    3、把JOIN當做了SELECT的子句

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

    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子句是這個復雜的表引用的一部分： 

    SELECT c.first_name, c.last_name, o.amount
FROMcustomer_view c
JOIN order_view o
ON c.cust_id = o.cust_id

    4、使用ANSI 92標準之前連接語法

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

    SELECT c.first_name, c.last_name, o.amount
FROM  customer_view c,
order_view o
WHERE  o.amount > 100
AND    c.cust_id = o.cust_id
AND    c.language = 'en'

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

    解決方案：

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

    5、使用LIKE判定時忘了ESCAPE

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

    8.5 <like predicate>

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

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

    SELECT *
FROM  t
WHERE  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 去。它說了如下查詢給不出結果： 

    SELECT 1
WHERE    1 IN (NULL)
UNION ALL
SELECT 2
WHERE 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是一個以表格為中心的語言，表格又是以行為中心。通過創建能在同等級或行類型進行比較的點對點行模型，行值表達式讓你能更容易的描述復雜的判定條件。一個簡單的例子是，同時請求客戶的姓名 

    SELECT c.address
FROM  customer c,
WHERE (c.first_name, c.last_name) = (?, ?)

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

    SELECT c.first_name, c.last_name, a.street
FROM  customer c
JOIN  address a
ON  (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的炒作依然在繼續，許多公司認為它們像推ter或非死book一樣需要更快、擴展性更好的解決方案，想脫離ACID和關系模型橫向擴展。有些可能會成功（比如推ter或非死book），而其他的也許會走入誤區：

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

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

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

    因此，不要認為查詢速度50ms是很快或者可以接受的。完全不是！如果你程序運行時間是這樣的，請檢查你的執行計劃。這種潛在危險可能會在你執行更復雜的上下文或數據中爆發。

    總結

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

    原文鏈接： jooq 翻譯： ImportNew.com - zer0Black
    譯文鏈接： http://www.importnew.com/15291.html

 本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
 轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
 本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！