?譯者 | 李睿

審校 | 孫淑娟

SQL開(kāi)發(fā)商Data Geeker公司首席執(zhí)行官Lukas Eder日前表示，他在博客文章列出了Java開(kāi)發(fā)人員在編寫(xiě)SQL時(shí)常犯的10個(gè)錯(cuò)誤。這篇文章得到廣泛關(guān)注，這讓他感到非常驚訝。這種受歡迎程度說(shuō)明了幾點(diǎn):  

• SQL對(duì)于專業(yè)的Java世界有多重要。  
• 忘記一些基本的SQL內(nèi)容是多么常見(jiàn)。  
• 以SQL為中心的庫(kù)（例如jOOQ或MyBatis）是如何通過(guò)采用SQL來(lái)響應(yīng)市場(chǎng)需求的。

一個(gè)有趣的事實(shí)是，用戶甚至在slick的郵件列表中提到了他寫(xiě)的博客文章。Slick是Scala中的一個(gè)不以SQL為中心的數(shù)據(jù)庫(kù)訪問(wèn)庫(kù)。和LINQ（以及LINQ-TO-SQL）一樣，它關(guān)注的是語(yǔ)言集成，而不是SQL代碼生成。  

無(wú)論如何，Eder列出的一些錯(cuò)誤還遠(yuǎn)遠(yuǎn)不夠，下面將介紹Java開(kāi)發(fā)人員在編寫(xiě)SQL時(shí)常犯的10個(gè)錯(cuò)誤。  

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

有趣的是，在JDBC出現(xiàn)多年之后，這種錯(cuò)誤或誤解仍然出現(xiàn)在博客、論壇和郵件列表中，即使它是關(guān)于一個(gè)在記憶和理解方面非常簡(jiǎn)單的的事情。一些開(kāi)發(fā)人員似乎因?yàn)橐韵略蚨苊馐褂妙A(yù)處理語(yǔ)句:  

• 不知道預(yù)處理語(yǔ)句。
• 認(rèn)為預(yù)處理語(yǔ)句速度較慢。  
• 認(rèn)為編寫(xiě)預(yù)處理語(yǔ)句需要花費(fèi)更多的精力。

首先需要打破以上誤區(qū)。在96%的情況下，編寫(xiě)預(yù)處理語(yǔ)句要比編寫(xiě)靜態(tài)語(yǔ)句更好。為什么？其原因很簡(jiǎn)單：

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

需要注意的是，在極少數(shù)情況下，確實(shí)需要內(nèi)聯(lián)綁定值，以便讓數(shù)據(jù)庫(kù)的基于成本的優(yōu)化器了解真正將受到查詢影響的數(shù)據(jù)類型。通常，這會(huì)導(dǎo)致“常量”謂詞，例如:  

• deleted = 1
• status = 42

但它不應(yīng)該導(dǎo)致“變量”謂詞，例如：

• first_name like “jon%”
• amount > 19.95

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

解決辦法：

在默認(rèn)情況下，總是使用預(yù)處理語(yǔ)句而不是靜態(tài)語(yǔ)句，并且永遠(yuǎn)不要將綁定值內(nèi)聯(lián)到SQL中。

2.返回太多的列  

這種錯(cuò)誤非常常見(jiàn)，可能會(huì)在數(shù)據(jù)庫(kù)的執(zhí)行計(jì)劃和Java應(yīng)用程序中導(dǎo)致非常糟糕的影響。先看看第二個(gè)效果：

（1）對(duì)Java應(yīng)用程序的不良影響

如果選擇*(星號(hào))或50列的“默認(rèn)”集合(在各種數(shù)據(jù)訪問(wèn)對(duì)象之間重用)，則需要將大量數(shù)據(jù)從數(shù)據(jù)庫(kù)傳輸?shù)絁DBC結(jié)果集。即使沒(méi)有從結(jié)果集中讀取數(shù)據(jù)，它也已經(jīng)通過(guò)網(wǎng)絡(luò)傳輸，并由JDBC驅(qū)動(dòng)程序加載到內(nèi)存中。如果知道只需要2～3個(gè)這樣的列，這相當(dāng)浪費(fèi)IO和內(nèi)存。  

這是顯而易見(jiàn)的，但也要小心。

（2）對(duì)數(shù)據(jù)庫(kù)執(zhí)行計(jì)劃的不良影響

這些影響實(shí)際上可能比對(duì)Java應(yīng)用程序的影響要嚴(yán)重得多。復(fù)雜的數(shù)據(jù)庫(kù)在為查詢計(jì)算最佳執(zhí)行計(jì)劃時(shí)執(zhí)行大量SQL轉(zhuǎn)換。很可能查詢的某些部分可以被轉(zhuǎn)換掉，因?yàn)橹浪鼈儾粫?huì)對(duì)投影(選擇子句)或過(guò)濾謂詞產(chǎn)生影響。

考慮一個(gè)復(fù)雜的選擇，它將連接兩個(gè)視圖:  

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

連接到上述連接表引用的每個(gè)視圖可能再次連接來(lái)自幾十個(gè)表的數(shù)據(jù)，例如customeraddress、order history、order settlement等?？紤]到select*投影，數(shù)據(jù)庫(kù)別無(wú)選擇，只能完全加載所有這些聯(lián)接表，而實(shí)際上，唯一感興趣的是：

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

一個(gè)出色的數(shù)據(jù)庫(kù)將以一種可以刪除大部分“隱藏”連接的方式轉(zhuǎn)換SQL，這將顯著地減少數(shù)據(jù)庫(kù)中的IO和內(nèi)存消耗。  

解決方法：

從不執(zhí)行select*。不要為不同的查詢重用相同的投影?？偸菄L試減少投影到真正需要的數(shù)據(jù)。  

注意，用對(duì)象關(guān)系映射（ORM）很難實(shí)現(xiàn)這一點(diǎn)。  

3.認(rèn)為join是select子句

這并不是一個(gè)對(duì)性能或SQL正確性有很大影響的錯(cuò)誤，但是，SQL開(kāi)發(fā)人員應(yīng)該意識(shí)到這樣一個(gè)事實(shí)：join子句本身不是select語(yǔ)句的一部分。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> ]

關(guān)系數(shù)據(jù)庫(kù)主要以表為中心。許多操作都以這樣或那樣的方式在物理表、連接表或派生表上執(zhí)行。為了有效地編寫(xiě)SQL，重要的是要理解select..From子句需要一個(gè)以逗號(hào)分隔的表引用列表，無(wú)論它們以何種形式提供。  

根據(jù)表引用的復(fù)雜性，有些數(shù)據(jù)庫(kù)還接受其他語(yǔ)句中的復(fù)雜表引用，如插入、更新、刪除和合并。

解決方法：

始終將from子句作為一個(gè)整體來(lái)考慮表引用。如果寫(xiě)一個(gè)連接子句，把這個(gè)連接子句想象成一個(gè)復(fù)雜表引用的一部分：

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連接語(yǔ)法  

既然已經(jīng)闡明了表引用是如何工作的，那么無(wú)論如何都要避免使用pre-ansi連接語(yǔ)法對(duì)于執(zhí)行計(jì)劃，如果在join..on子句或where子句中指定連接謂詞，通常沒(méi)有區(qū)別。但從可讀性和維護(hù)的角度來(lái)看，對(duì)過(guò)濾謂詞和連接謂詞都使用where子句是一個(gè)主要的障礙?？紤]這個(gè)簡(jiǎn)單的例子：

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'

能發(fā)現(xiàn)連接謂詞嗎?如果加入幾十張表呢?當(dāng)為外部連接應(yīng)用專有語(yǔ)法(例如oracle的(+)語(yǔ)法)時(shí)，情況會(huì)變得更糟。  

解決方法：

始終使用ansi-join語(yǔ)法。永遠(yuǎn)不要將連接謂詞放在where子句中。使用pre-ansi連接語(yǔ)法絕對(duì)沒(méi)有好處。

5. 忘記轉(zhuǎn)義like謂詞的輸入

SQL標(biāo)準(zhǔn)1992指定like謂詞如下:  

8.5 <like predicate>
<like predicate> ::=
    <match value> [ not ] like <pattern>
      [ escape <escape character> ]

當(dāng)允許在SQL查詢中使用用戶輸入時(shí)，幾乎總是應(yīng)該使用escape關(guān)鍵字。雖然百分比符號(hào)(%)可能很少被認(rèn)為是數(shù)據(jù)的一部分，但下劃線(_)很可能是:  

select *
from   t
where  t.x like 'some!_prefix%' escape '!'

解決方法： 

在使用like謂詞時(shí)，始終要考慮適當(dāng)?shù)霓D(zhuǎn)義。  

6.認(rèn)為not（in（x，y））是in（x、y）的布爾逆

這一點(diǎn)很微妙，但對(duì)于null來(lái)說(shuō)非常重要！以下回顧一下in（x，y）的真正含義：

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

同時(shí)， 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

這看起來(lái)像前一個(gè)謂詞的布爾逆，但實(shí)際上不是！如果x或y中的任何一個(gè)為null，則not-in謂詞將導(dǎo)致未知，而in謂詞可能仍然返回布爾值。

換句話說(shuō)，當(dāng)in（x，y）產(chǎn)生true或false時(shí)，not（a in（x、y））仍可能產(chǎn)生unknown，而不是false或true。注意，如果in謂詞的右側(cè)是子查詢，這也是正確的。

不相信嗎?看看這個(gè)sql fiddle。它表明以下查詢沒(méi)有產(chǎn)生結(jié)果:  

select 1
where     1 in (null)
union all
select 2
where not(1 in (null))

解決方法：  

當(dāng)涉及nullable列時(shí)，要注意not in謂詞!

7. 認(rèn)為not（a為null）與a不為null相同

人們記得SQL在處理null值時(shí)實(shí)現(xiàn)了三值邏輯。這就是為什么可以使用null謂詞來(lái)檢查null的原因。

但即使是null謂詞也很微妙。注意，以下兩個(gè)謂詞僅對(duì)度數(shù)為1的行值表達(dá)式等效： 

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

如果a是一個(gè)度數(shù)大于1的行值表達(dá)式，那么真值表轉(zhuǎn)換為：

• 只有當(dāng)a中的所有值都為null時(shí)，a is null才會(huì)產(chǎn)生true  
• 只有當(dāng)a中的所有值都為null時(shí)，not(a is null)才會(huì)產(chǎn)生false
• 只有當(dāng)a中的所有值都不為null時(shí)，a is not null才會(huì)產(chǎn)生true  
• 只有當(dāng)a中的所有值都不是null時(shí)，not(a is not null) 才會(huì)產(chǎn)生false  

解決方法：  

使用行值表達(dá)式時(shí)，請(qǐng)注意null謂詞，它可能無(wú)法按預(yù)期工作。

8.在支持行值表達(dá)式的地方?jīng)]有使用行值表達(dá)式  

行值表達(dá)式是一個(gè)很好的SQL特性。當(dāng)SQL是一種以表為中心的語(yǔ)言時(shí)，表也以行為中心。行值表達(dá)式通過(guò)創(chuàng)建可以與具有相同度數(shù)和行類型的其他行進(jìn)行比較的本地特殊行，從而更容易地描述復(fù)雜謂詞。一個(gè)簡(jiǎn)單的例子是同時(shí)查詢客戶的姓和名。  

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

可以看到，這種語(yǔ)法比等價(jià)語(yǔ)法(謂詞左邊的每一列都與右邊的相應(yīng)列進(jìn)行比較)稍微簡(jiǎn)潔一些。如果許多獨(dú)立謂詞與and組合在一起，則尤其如此。使用行值表達(dá)式允許將相關(guān)謂詞組合為一個(gè)謂詞。這對(duì)于復(fù)合外鍵上的連接表達(dá)式非常有用:  

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)

不幸的是，并非所有數(shù)據(jù)庫(kù)都以相同的方式支持行值表達(dá)式。但是SQL標(biāo)準(zhǔn)在1992年就已經(jīng)定義了它們，如果使用它們，像Oracle或postgres這樣復(fù)雜的數(shù)據(jù)庫(kù)可以使用它們來(lái)計(jì)算更好的執(zhí)行計(jì)劃。  

解決方法：

盡可能使用行值表達(dá)式。它們將使SQL更簡(jiǎn)潔，甚至可能更快。  

9.沒(méi)有定義足夠的約束  

要引用TomKyte，再使用索引和Luke。元數(shù)據(jù)中不能有足夠的約束。首先，約束幫助防止數(shù)據(jù)損壞，這已經(jīng)非常有用了。但更重要的是，約束將幫助數(shù)據(jù)庫(kù)執(zhí)行SQL轉(zhuǎn)換，因?yàn)閿?shù)據(jù)庫(kù)可以決定：  

• 有些值是等價(jià)的  
• 有些子句是多余的  
• 某些子句是“無(wú)效的”（即它們不會(huì)返回任何值）  

一些開(kāi)發(fā)人員可能認(rèn)為約束是緩慢的。與其相反，除非插入大量大量的數(shù)據(jù)，在這種情況下，可以禁用大型操作的約束，或者使用沒(méi)有約束的臨時(shí)“加載表”，不受約束地將數(shù)據(jù)脫機(jī)傳輸?shù)綄?shí)際表。

解決方法：

定義盡可能多的約束。它們將幫助數(shù)據(jù)庫(kù)在查詢時(shí)更好地執(zhí)行。

10.認(rèn)為50ms是快速查詢執(zhí)行  

對(duì)于NoSQL的炒作仍在繼續(xù)，許多企業(yè)仍然認(rèn)為他們是Twitter或Facebook，迫切需要更快、更可擴(kuò)展的解決方案，逃避ACID和關(guān)系模型來(lái)橫向擴(kuò)展。有些可能會(huì)成功(如Twitter或Facebook)。  

對(duì)于那些被迫或選擇堅(jiān)持使用經(jīng)過(guò)驗(yàn)證的關(guān)系數(shù)據(jù)庫(kù)的人來(lái)說(shuō)，如果要和DBA相處得很好并將數(shù)據(jù)庫(kù)調(diào)到最大的話，不要誤以為現(xiàn)代數(shù)據(jù)庫(kù)很慢，事實(shí)上它們非?？?，并在不到一毫秒的時(shí)間內(nèi)解析20kb的查詢文本，計(jì)算2000行執(zhí)行計(jì)劃。  

它們可能會(huì)變慢，因?yàn)閼?yīng)用程序誤用了流行的對(duì)象關(guān)系映射（ORM），或者因?yàn)檫@一ORM不能為復(fù)雜的查詢邏輯生成快速SQL。在這種情況下，可能想要選擇一個(gè)更以SQL為中心的API，例如JDBC，jOOQ或MyBatis，這將重新控制SQL。

所以，不要認(rèn)為50毫秒的查詢執(zhí)行速度很快，甚至可以接受。事實(shí)并非如此。如果在開(kāi)發(fā)時(shí)獲得了這些速度，確保研究了執(zhí)行計(jì)劃。這些數(shù)據(jù)可能會(huì)在生產(chǎn)環(huán)境中爆發(fā)式增長(zhǎng)，因?yàn)樯a(chǎn)環(huán)境中有更復(fù)雜的場(chǎng)景和數(shù)據(jù)。  

結(jié)論

SQL非常有趣，但在許多方面也非常微妙。正如Eder之前關(guān)于10個(gè)常見(jiàn)錯(cuò)誤的博客文章所表明的那樣，要做到這一點(diǎn)并不容易。但是SQL是可以掌握的。數(shù)據(jù)是最寶貴的資產(chǎn)。因此需要尊重?cái)?shù)據(jù)，并編寫(xiě)更好的SQL。

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