歡迎光臨
每天分享高質量文章

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


我十分驚訝的發現,我最近的一篇文章——《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查詢轉換產生怎樣的影響。

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

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

::=

[ [ AS ]

[ ] ]
| [ AS ]
[ ]
|
7.4
::=
FROM

[ {

}... ]

7.5
::=
|
|
::=

CROSS JOIN

::=

[ NATURAL ] [ ] JOIN

[ ]


關聯資料庫是以表為中心的。許多的操作的某方面都是執行在物理表、連接表或派生表上的。為了有效的寫出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
::=
[ NOT ] LIKE
[ ESCAPE ]

當允許用戶對你的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的炒作依然在繼續,許多公司認為它們像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陳述句。



英文出處:jooq

譯文出處:ImportNew.com-zer0Black
譯文鏈接:
http://www.importnew.com/15291.html

赞(0)

分享創造快樂

© 2020 知識星球   网站地图