Java程式員在寫SQL程式時候常犯的10個錯誤

java程式員程式設計時需要混合面向對象思維和一般指令式程式設計的方法，能否完美的将兩者結合起來完全得依靠程式設計人員的水準：

技能(任何人都能容易學會指令式程式設計)

模式(有些人用“模式-模式”,舉個例子,模式可以應用到任何地方，而且都可以歸為某一類模式)

心境(首先，要寫個好的面向對象程式是比指令式程式難的多，你得花費一些功夫)

但當java程式員寫sql語句時，一切都不一樣了。sql是說明性語言而非面向對象或是指令式程式設計語言。在sql中要寫個查詢語句是很簡單的。但在java裡類似的語句卻不容易，因為程式員不僅要反複考慮程式設計範式，而且也要考慮算法的問題。

下面是java程式員在寫sql時常犯的錯誤(沒有特定的順序)： 

1.忘掉null

java程式員寫sql時對null的誤解可能是最大的錯誤。也許是因為(并非唯一理由)null也稱作unknown。如果被稱作unknown，這還好了解些。另一個原因是，當你從資料庫拿東西或是綁定變量時，jdbc将sql null 和java中的null對應了起來。這樣導緻了null = null(sql)和null=null(java)的誤解。

對于null最大的誤解是當null被用作行值表達式完整性限制條件時。

另一個誤解出現在對于null 在 not in anti-joins的應用中。

解決方法：

好好的訓練你自己。當你寫sql時要不停得想到null的用法：

這個null完整性限制條件是正确的?

null是否影響到結果? 

2.在java記憶體中處理資料

很少有java開發者能将sql了解的很好.偶爾使用的join,還有古怪的union,好吧.但是對于視窗函數呢?還有對集合進行分組呢?許多的java開發者将sql資料加載到記憶體中,将這些資料轉換成某些相近的集合類型,然後再那些集合上面使用邊界循環控制結構(至少在java8的集合更新以前)執行令人生厭的數學運算.

但是一些sql資料庫支援先進的(而且是sql 标準支援的!)olap特性,這一特性表現更好而且寫起來也更加友善.一個(并不怎麼标準的)例子就是oracle超棒的model分句.隻讓資料庫來做處理然後隻把結果帶到java記憶體中吧.因為畢竟所有非常聰明的家夥已經對這些昂貴的産品進行了優化.是以實際上,通過将olap移到資料庫,你将獲得一下兩項好處:

便利性.這比在java中編寫正确的sql可能更加的容易.

性能表現.資料庫應該比你的算法處理起來更加快.而且更加重要的是,你不必再去傳遞數百萬條記錄了.

完善的方法:

每次你使用java實作一個以資料為中心的算法時,問問自己:有沒有一種方法可以讓資料庫代替為我做這種麻煩事.

3. 使用union代替union all

太可恥了，和union相比union all還需要額外的關鍵字。如果sql标準已經規定了支援，那麼可能會更好點。

union(允許重複)

union distinct (去除了重複)

移除重複行不僅很少需要(有時甚至是錯的)，而且對于帶很多行的大資料集合會相當慢，因為兩個子select需要排序，而且每個元組也需要和它的子序列元組比較。

注意即使sql标準規定了intersect all和except all，很少資料庫會實作這些沒用的集合操作符。

處理方法：

每次你寫union語句時，考慮實際上是否需要union all語句。 

4.通過jdbc分頁技術給大量的結果進行分頁操作

大部分的資料庫都會支援一些分頁指令實作分頁效果，譬如limit..offset,top..start at,offset..fetch語句等。即使沒有支援這些語句的資料庫，仍有可能對rownum(甲骨文)或者是row number() over()過濾(db2,sql server2008等)，這些比在記憶體中實作分頁更快速。在處理大量資料中，效果尤其明顯。

糾正：

僅僅使用這些語句，那麼一個工具(例如jooq)就可以模拟這些語句的操作。 

5.在java記憶體中加入資料

從sql的初期開始，當在sql中使用join語句時，一些開發者仍舊有不安的感覺。這是源自對加入join後會變慢的固有恐懼。假如基于成本的優化選擇去實作嵌套循環，在建立一張連接配接表源前，可能加載所有的表在資料庫記憶體中，這可能是真的。但是這事發生的機率太低了。通過合适的預測，限制和索引，合并連接配接和哈希連接配接的操作都是相當的快。這完全是是關于正确中繼資料(在這裡我不能夠引用tom kyte的太多)。而且，可能仍然有不少的java開發人員加載兩張表通過分開查詢到一個映射中，并且在某種程度上把他們加到了記憶體當中。

假如你在各個步驟中有從各種表的查詢操作，好好想想是否可以表達你的查詢操作在單條語句中。 

6.在一個臨時的笛卡爾積集合中使用 distinct 或 union 消除重複項

通過複雜的連接配接，人們可能會對sql語句中扮演關鍵角色的所有關系失去概念。特别的，如果這涉及到多列外鍵關系的話，很有可能會忘記在join .. on子句中增加相關的判斷。這會導緻重複的記錄，但或許隻是在特殊的情況下。有些開發者是以可能選擇distinct來消除這些重複記錄。從三個方面來說這是錯誤的：

它(也許)解決了表面症狀但并沒有解決問題。它也有可能無法解決極端情況下的症狀。

對具有很多列的龐大的結果集合來說它很慢。distinct要執行order by操作來消除重複。

對龐大的笛卡爾積集合來說它很慢，還是需要加載很多的資料到記憶體中。

解決方法:

根據經驗，如果你獲得了不需要的重複記錄，還是檢查你的join判斷吧。可能在某個地方有一個很難覺察的笛卡爾積集合。 

7. 不使用merge語句

這并不是一個過失，但是可能是缺少知識或者對于強悍的merge語句信心不足。一些資料庫了解其它形式的更新插入(upsert)語句， 如 mysql的重複主鍵更新語句，但是merge在資料庫中确是很強大，很重要，以至于大肆擴充sql标準，例如sql server。

解決之道:

如果你使用像聯合insert和update或者聯合select .. for update然後在insert或update等更新插入時，請三思。你完全可以使用一個更簡單的merge語句來遠離冒險競争條件。 

8. 使用聚合函數代替視窗函數(window functions)

在介紹視窗函數之前，在sql中聚合資料意味着使用group by語句與聚合函數相映射。在很多情形下都工作得很好，如聚合資料需要濃縮正常資料，那麼就在join子查詢中使用group查詢。

但是在sql：2003中定義了視窗函數，這個在很多主流資料庫都實作了它。視窗函數能夠在結果集上聚合資料，但是卻沒有分組。事實上，每個視窗函數都有自己的、獨立的partition by語句，這個工具對于顯示報告太tm好了。

使用視窗函數：

使sql更易讀(但在子查詢中沒有group by語句專業)

提升性能，像關系資料庫管理系統能夠更容易優化視窗函數

當你在子查詢中使用group by語句時，請再三考慮是否可以使用視窗函數完成。

9. 使用記憶體間接排序

sql的order by語句支援很多類型的表達式，包括case語句，對于間接排序十分有用。你可能重來不會在java記憶體中排序資料，因為你會想：

sql排序很慢

sql排序辦不到

處理方法:

如果你在記憶體中排序任何sql資料，請再三考慮，是否不能在資料庫中排序。這對于資料庫分頁資料十分有用。

10. 一條一條的插入大量紀錄

jdbc ”懂“批處理(batch),你應該不會忘了它。不要使用insert語句來一條一條的出入成千上萬的記錄，(因為)每次都會建立一個新的preparedstatement對象。如果你的所有記錄都插入到同一個表時，那麼就建立一個帶有一條sql語句以及附帶很多值集合的插入批處理語句。你可能需要在達到一定量的插入記錄後才送出來保證undo日志瘦小，這依賴于你的資料庫和資料庫設定。

總是使用批處理插入大量資料。

相關書籍

一些讨論類似主題的相關書籍

sql antipatterns by bill karwin

sql performance explained by markus winand [@lesus 注：本書寫的非常好，對于和關系型資料庫打交道的開發人員，你值得擁有。http://book.douban.com/subject/21363149/]