?譯者 | 李睿

審校 | 孫淑娟

SQL開發商Data Geeker公司首席執行官Lukas Eder日前表示，他在博客文章列出了Java開發人員在編寫SQL時常犯的10個錯誤。這篇文章得到廣泛關注，這讓他感到非常驚訝。這種受歡迎程度說明了幾點:  

• SQL對于專業的Java世界有多重要。  
• 忘記一些基本的SQL內容是多么常見。  
• 以SQL為中心的庫（例如jOOQ或MyBatis）是如何通過采用SQL來響應市場需求的。

一個有趣的事實是，用戶甚至在slick的郵件列表中提到了他寫的博客文章。Slick是Scala中的一個不以SQL為中心的數據庫訪問庫。和LINQ（以及LINQ-TO-SQL）一樣，它關注的是語言集成，而不是SQL代碼生成。  

無論如何，Eder列出的一些錯誤還遠遠不夠，下面將介紹Java開發人員在編寫SQL時常犯的10個錯誤。  

1.不使用預處理語句(Prepared Statements)

有趣的是，在JDBC出現多年之后，這種錯誤或誤解仍然出現在博客、論壇和郵件列表中，即使它是關于一個在記憶和理解方面非常簡單的的事情。一些開發人員似乎因為以下原因而避免使用預處理語句:  

• 不知道預處理語句。
• 認為預處理語句速度較慢。  
• 認為編寫預處理語句需要花費更多的精力。

首先需要打破以上誤區。在96%的情況下，編寫預處理語句要比編寫靜態語句更好。為什么？其原因很簡單：

• 在內聯綁定值時，可以省略由錯誤的字符串連接引起的語法錯誤。  
• 當內聯綁定值時，可以忽略由于字符串連接錯誤造成的SQL注入漏洞。  
• 當內聯更復雜的數據類型(如時間戳、二進制數據等)時，可以避免使用邊緣用例。  
• 可以讓打開的預處理語句保留一段時間，用新的bind值重用它們，而不是立即關閉它們(例如，在postgres中很有用)。  
• 可以在更復雜的數據庫中使用自適應游標共享(Oracle語言)。這有助于防止對每一組新的綁定值進行硬解析SQL語句。  

需要注意的是，在極少數情況下，確實需要內聯綁定值，以便讓數據庫的基于成本的優化器了解真正將受到查詢影響的數據類型。通常，這會導致“常量”謂詞，例如:  

• deleted = 1
• status = 42

但它不應該導致“變量”謂詞，例如：

• first_name like “jon%”
• amount > 19.95

需要注意的是，現代數據庫實現了綁定變量窺視。因此，在默認情況下，還可以為所有查詢參數使用綁定值。另外，在編寫嵌入式JPQL或嵌入式SQL時，諸如JPA CriteriaQuery或jOOQ等高級API將幫助您生成預處理語句并非常容易和透明地綁定值。  

解決辦法：

在默認情況下，總是使用預處理語句而不是靜態語句，并且永遠不要將綁定值內聯到SQL中。

2.返回太多的列  

這種錯誤非常常見，可能會在數據庫的執行計劃和Java應用程序中導致非常糟糕的影響。先看看第二個效果：

（1）對Java應用程序的不良影響

如果選擇*(星號)或50列的“默認”集合(在各種數據訪問對象之間重用)，則需要將大量數據從數據庫傳輸到JDBC結果集。即使沒有從結果集中讀取數據，它也已經通過網絡傳輸，并由JDBC驅動程序加載到內存中。如果知道只需要2～3個這樣的列，這相當浪費IO和內存。  

這是顯而易見的，但也要小心。

（2）對數據庫執行計劃的不良影響

這些影響實際上可能比對Java應用程序的影響要嚴重得多。復雜的數據庫在為查詢計算最佳執行計劃時執行大量SQL轉換。很可能查詢的某些部分可以被轉換掉，因為知道它們不會對投影(選擇子句)或過濾謂詞產生影響。

考慮一個復雜的選擇，它將連接兩個視圖:  

select *
from   customer_view c
join   order_view o
  on   c.cust_id = o.cust_id
onc.cust_id=o.cust_id

連接到上述連接表引用的每個視圖可能再次連接來自幾十個表的數據，例如customeraddress、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*。不要為不同的查詢重用相同的投影?？偸菄L試減少投影到真正需要的數據。  

注意，用對象關系映射（ORM）很難實現這一點。  

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>

from子句和連接表可以使用這樣的表引用:  

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子句需要一個以逗號分隔的表引用列表，無論它們以何種形式提供。  

根據表引用的復雜性，有些數據庫還接受其他語句中的復雜表引用，如插入、更新、刪除和合并。

解決方法：

始終將from子句作為一個整體來考慮表引用。如果寫一個連接子句，把這個連接子句想象成一個復雜表引用的一部分：

select c.first_name, c.last_name, o.amount
from
    customer_view c
      join order_view o
      on c.cust_id = o.cust_id

4.使用pre-ansi連接語法  

既然已經闡明了表引用是如何工作的，那么無論如何都要避免使用pre-ansi連接語法對于執行計劃，如果在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'

能發現連接謂詞嗎?如果加入幾十張表呢?當為外部連接應用專有語法(例如oracle的(+)語法)時，情況會變得更糟。  

解決方法：

始終使用ansi-join語法。永遠不要將連接謂詞放在where子句中。使用pre-ansi連接語法絕對沒有好處。

5. 忘記轉義like謂詞的輸入

SQL標準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謂詞時，始終要考慮適當的轉義。  

6.認為not（in（x，y））是in（x、y）的布爾逆

這一點很微妙，但對于null來說非常重要！以下回顧一下in（x，y）的真正含義：

a in (x, y)
is the same as    a = any (x, y)
is the same as    a = x or a = y

同時， 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謂詞將導致未知，而in謂詞可能仍然返回布爾值。

換句話說，當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))

解決方法：  

當涉及nullable列時，要注意not in謂詞!

7. 認為not（a為null）與a不為null相同

人們記得SQL在處理null值時實現了三值邏輯。這就是為什么可以使用null謂詞來檢查null的原因。

但即使是null謂詞也很微妙。注意，以下兩個謂詞僅對度數為1的行值表達式等效： 

not (a is null)
is not the same as a is not null

如果a是一個度數大于1的行值表達式，那么真值表轉換為：

• 只有當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  

解決方法：  

使用行值表達式時，請注意null謂詞，它可能無法按預期工作。

8.在支持行值表達式的地方沒有使用行值表達式  

行值表達式是一個很好的SQL特性。當SQL是一種以表為中心的語言時，表也以行為中心。行值表達式通過創建可以與具有相同度數和行類型的其他行進行比較的本地特殊行，從而更容易地描述復雜謂詞。一個簡單的例子是同時查詢客戶的姓和名。  

select c.address
from   customer c,
where (c.first_name, c.last_name) = (?, ?)
Where(c.first_name,c.last_name)=(?,?)

可以看到，這種語法比等價語法(謂詞左邊的每一列都與右邊的相應列進行比較)稍微簡潔一些。如果許多獨立謂詞與and組合在一起，則尤其如此。使用行值表達式允許將相關謂詞組合為一個謂詞。這對于復合外鍵上的連接表達式非常有用:  

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這樣復雜的數據庫可以使用它們來計算更好的執行計劃。  

解決方法：

盡可能使用行值表達式。它們將使SQL更簡潔，甚至可能更快。  

9.沒有定義足夠的約束  

要引用TomKyte，再使用索引和Luke。元數據中不能有足夠的約束。首先，約束幫助防止數據損壞，這已經非常有用了。但更重要的是，約束將幫助數據庫執行SQL轉換，因為數據庫可以決定：  

• 有些值是等價的  
• 有些子句是多余的  
• 某些子句是“無效的”（即它們不會返回任何值）  

一些開發人員可能認為約束是緩慢的。與其相反，除非插入大量大量的數據，在這種情況下，可以禁用大型操作的約束，或者使用沒有約束的臨時“加載表”，不受約束地將數據脫機傳輸到實際表。

解決方法：

定義盡可能多的約束。它們將幫助數據庫在查詢時更好地執行。

10.認為50ms是快速查詢執行  

對于NoSQL的炒作仍在繼續，許多企業仍然認為他們是Twitter或Facebook，迫切需要更快、更可擴展的解決方案，逃避ACID和關系模型來橫向擴展。有些可能會成功(如Twitter或Facebook)。  

對于那些被迫或選擇堅持使用經過驗證的關系數據庫的人來說，如果要和DBA相處得很好并將數據庫調到最大的話，不要誤以為現代數據庫很慢，事實上它們非常快，并在不到一毫秒的時間內解析20kb的查詢文本，計算2000行執行計劃。  

它們可能會變慢，因為應用程序誤用了流行的對象關系映射（ORM），或者因為這一ORM不能為復雜的查詢邏輯生成快速SQL。在這種情況下，可能想要選擇一個更以SQL為中心的API，例如JDBC，jOOQ或MyBatis，這將重新控制SQL。

所以，不要認為50毫秒的查詢執行速度很快，甚至可以接受。事實并非如此。如果在開發時獲得了這些速度，確保研究了執行計劃。這些數據可能會在生產環境中爆發式增長，因為生產環境中有更復雜的場景和數據。  

結論

SQL非常有趣，但在許多方面也非常微妙。正如Eder之前關于10個常見錯誤的博客文章所表明的那樣，要做到這一點并不容易。但是SQL是可以掌握的。數據是最寶貴的資產。因此需要尊重數據，并編寫更好的SQL。

原文標題：??10 More Common Mistakes Java Developers Make when Writing SQL??，作者：Lukas Eder?