Java开发人员编写SQL时常犯的十个错误

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*。不要为不同的查询重用相同的投影。总是尝试减少投影到真正需要的数据。  

注意，用对象关系映射（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​