Java 程序员写 SQL 时易犯的另外 10 个错误

Tags: java database

我们的使命是指导你使用 SQL。但大多数情况下,我们要教会你如何欣赏 SQL,你会爱上它的!

Java 程序员编写 SQL 时容易出现的错误远远不止之前一篇Java 程序员写 SQL 时易犯的 10 个错误那些,因此在这篇文章中我将列举另外 10 个 Java 程序员编写 SQL 时容易出现的有趣的错误。

1. 没有使用 PreparedStatements

有趣的是,在 JDBC 发布这么多年后,这个错误以及讨论仍然在博客论坛以及邮件列表中讨论着,尽管这是一个非常容易记忆和理解的概念。一些开发人员基于以下理由拒绝使用 PreparedStatements :

  • 他们不知道有这玩意

  • 他们觉得它很慢

  • 他们觉得使用它需要花很多精力

首先,让我们驳斥上面的说法,在大多数情况下(96%),最好使用 PreparedStatement 而不是静态的 Statement,为什么?基于以下几个简单的理由:

  1. 使用字符串拼接 SQL 可能会由于错误的字符串连接导致 SQL 语法错误。

  2. 使用字符串拼接 SQL 可能会由于错误的字符串连接导致 SQL 注入漏洞。

  3. 可以使用更多的数据类型,比如 TIMESTAMP 二进制数据以及其他类型

  4. 在循环中可以重复使用 PreparedStatement 语句,为他们绑定新值而不是每次关闭后重新创建新的 Statement(在 Postgres 中很有用)

  5. 你可以在更高级的数据库系统中应用自适应游标共享(adaptive cursor sharing, Oracle 这么称呼它)。 这有助于防止每次对新绑定值硬解析SQL语句。

相信了吗?是的。注意,当你打算使用内联绑定来让你的数据库引擎可以进行基于成本的优化(cost-based optimiser)时,你需要注意你的查询会影响的是什么样的数据,通常来说,“不变”的断言可以进行优化,比如:

  • DELETED = 1

  • STATUS = 42

而“可变”的断言则不行,比如:

  • FIRST_NAME LIKE “Jon%”

  • AMOUNT > 19.95

需要注意的是现代的数据库实现了绑定变量窥视。因此,默认情况下,你最好对你所有的查询参数使用绑定变量。

一些背景信息:

解药

总是使用 PreparedStatements 而不是静态的 Statement。一定不要使用字符串拼接 SQL 语句。

2. 返回太多的列

这是一个很常见的错误,并且非常影响性能,无论是你数据库的执行计划和你的 Java 应用。我们先看看第二个影响。

对 Java 程序的影响

如果你在各个 DAO 中重用的 select 语句使用 "*" 或者 "default" 检索包含 50 个字段的表,数据库传输给 JDBC ResultSet 的数据量会非常大。即使你不使用这些数据,但这些数据已经通过网络传输 JDBC 驱动并加载到内存中了。如果你仅使用其中的 2-3 列,那么传输这么多数据对系统 IO 和内存是极大的浪费。

对数据库执行计划的影响

这个影响实际上比对 Java 应用程序的影响坏很多,当为你的查询计算执行计划时,数据库引擎会执行很多 SQL 转换操作。这可能是由于你查询的某些部分可以被“移走”,因为这些部分对映射(SELECT 语句)或者过滤子句没有帮助。

考虑一下下面复杂的 SELECT 语句,它会连接两个视图

SELECT *
FROM   customer_view c
JOIN   order_view o
  ON   c.cust_id = o.cust_id

上面查询中每个连接的视图由连接了一堆表,比如:CUSTOMER_ADDRESS, ORDER_HISTORY, ORDER_SETTLEMENT, 等等。如果使用 SELECT * 映射(projection),你的数据库引擎除了加载所有连接表别无其他选择,然而,事实上,你需要使用的仅仅是:

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 *。不要在不同的查询中重用同一个映射(projection)。要根据你实际要操作的数据来缩小映射(projection)。

注:在使用 ORM 时,达到这个目标比较难。

3. 认为 JOIN 是 SELECT 的子句

这个错误对性能和 SQL 语句正正确性没有太大影响,不过,SQL 开发人员需要知道这样一个事实:JOIN 并不是 SELECT 语句的子句。SQL standard 1992 是这样定义表引用(table reference)的:

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 子句以及 JOINED TABLE 都可以用来进行表关联(table references):

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 子句期望可以以任何形式提供的用逗号(,)分隔的表引用列表。

依据表引用的复杂程度,一些数据库可以在其他语句中使用复杂的表引用,比如:INSERT, UPDATE, DELETE, MERGE。可以参考这个 Oracle 手册,这篇手册讲解了如何创建可更新的视图。

解药

总是考虑在你的 FROM 子句中整体使用表引用,如果你编写了一个 JOIN 子句,考虑一下这个 JOIN 子句应该用于复杂的表引用。

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. 使用 ANSI 92 之前的 JOIN 语法

现在我们已经了解了表引用是如何工作的(参考前面部分),那么应该避免使用 ANSI 92 标准之前的 JOIN 语法的观点就更明确了。在 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'

如果我们连接了一堆表,你可以指出连接子句吗?当使用专有的 OUTER JOIN 时这个问题会更复杂,比如 Oracle(+) 语法

解药

使用使用 ANSI 92 的 JOIN 语法,不要在 WHERE 子句中使用 JOIN 子句,使用 ANSI 92 之前的语法是没有任何优势的。

5. 忘记对 LIKE 子句的输入进行转义

下面是 SQL standard 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 (A IN (X, Y)) 与 A IN(X, Y) 在逻辑上是相反的

这条很微妙,但对于 NULL 来说却非常中哟啊,让我们回顾一下 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

同时,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

看上去这两个语法在逻辑上是相反的,不过不是的!如果 xy 中任意一个为 NULL,NOT IN 子句的返回值为 UNKNOW 然而 IN 子句的仍然返回布尔值。

换句话说,当 A IN (X, Y) 得到 TRUEFALSE 结果时,NOT (A IN (X, Y)) 可能会得到 UNKNOW 而不是 TRUEFALSE。注意:这同样也使用于 IN 子句右边是子查询的情况。

不相信?看看 SQL Fiddle 这篇文章。它表明,以下查询产生任何结果:

SELECT 1
WHERE     1 IN (NULL)
UNION ALL
SELECT 2
WHERE NOT(1 IN (NULL))

更多的细节可以参考我的博客,它同样会告诉你一些 SQL 方言在这方面是不兼容的。

解药

在列允许为空是,使用 NOT IN 子句要小心。

7. 认为 NOT (A IS NULL) 与 A IS NOT NULL 是相同的

没错,我们应该记得 SQL 处理 NULL 时使用三值逻辑(three-valued logic)。这就是为什么我们可以使用 NULL 断言来检查 NULL 值,对吗?对的!

不过 NULL 断言是很微妙的,要注意的是,下面两个断言仅仅在1阶行值表达式时是相等的:

                   NOT (A IS NULL)
is not the same as A IS NOT NULL

如果 A 是一个大于 1 阶的行值表达式,那么真值表会变成下面这样:

  1. 只有当 A 中的值全为 NULL 时,A IS NULL 的结果才是 TRUE

  2. 只有当 A 中的值全为 NULL 时, NOT (A IS NULL) 的结果才是 TRUE

  3. 只有当 A 中的值全不为 NULL 时,A IS NOT NULL 的结果才是 TRUE

  4. 只有当 A 中的值全不为 NULL 时,NOT (A IS NOT NULL) 的结果才为 FALSE

更多信息请参考我之前的博客

解药

当使用行值表达式时,当心 NULL 断言,它可能不能按照预期工作。

8. 在支持行值表达式时没有使用它

行值表达式是 SQL 语言非常棒的特性。由于 SQL 是以表为中心的,而表是以行为中心的。通过创建相同维度和行类型的本地临时行(local ad-hoc rows),来与其他行进行比较,行值表达式能让你更容易描述复杂的断言。一个简单的例子是,同时查询客户的 first name 和 last name:

SELECT c.address
FROM   customer c,
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)

不幸的是,不是所有的数据库都以同样的方式支持行值表达式。虽然早在 1992 年 SQL 标准就定义了它们,并且如果你使用它,向 Oracle 或者 Postgres 这类复杂的数据库系统可以依据它来计算更好的执行计划,参考这个广受欢迎网站 Use The Index,Luke

解药

如果能使用则一定要使用行值表达式,它会让你的 SQL 更简洁,执行起来也会更快。

9. 没有定义足够的约束

好吧,我再次引用 Tom KyteUse The Index, Luck。你不能在元数据中定义过少的约束。首先,约束能帮助你保持数据清洁(或不变脏),这个已经用的很多了。不过对于我来说,更重要的是,约束可以帮助数据库执行 SQL 转换,并且数据库可以决定:

  1. 哪些值是相等的

  2. 哪些子句是重复的

  3. 哪些子句是 "void" 的(比如:他们不会返回任何值)

一些开发人员可能认为约束影响速度。在相反的情况下,除非你插入大量数据,在这种情况下你可以在执行这种操作的时候禁用约束,或者使用一个没有约束的临时"导入表",线下将数据传输到实际的表中。

解药

竟可能的定义约束,它可以在查询时帮助数据库引擎更快的执行。

10. 认为 50ms 是很快的查询

NoSQL 的炒作仍然在继续,并且很多公司认为他们是 Twitte 或 Facebook 迫切需要更快的、更具扩展性的解决方案,通过去掉 ACID 和关系模型来进行横向扩展。有些可能成功了(比如:Twitter 或者 Facebook),其他的则不然。

对于其他的被强迫(或自愿)选择使用经过验证的关系型数据库的人来说,不要被欺骗,以为流行的数据库引擎很慢。他们实际上是超级快的。事实上,他们 是如此之快,如果你和你的 DBA 关系融洽且对你的数据库进行了最大化的调优,那么这些怪物可以在毫秒级时间内解析你 20kb 的查询文本,计算和执行 2000 行执行计划。

如果你的应用程序不正确的使用了流行的 ORM 工具,或者 ORM 工具无法为你复杂的查询逻辑生成高效的 SQL 语句。在这种情况下,你最好选择 JDBCMyBatis 或者 jOOQ 这类以 SQL 为中心的 API,它们会让你夺回 SQL 控制权的。

所以,不要认为一个执行了 50ms 的查询很快并且是可以接受的。如果你开发时得到这种执行时间,那么你要确保你仔细查看了执行计划,这些怪物可能会在生产环境爆发,因为那里有更复杂的背景和数据。

本文链接:http://www.4byte.cn/learning/84950/java-cheng-xu-yuan-xie-sql-shi-yi-fan-de-ling-wai-10-ge-cuo-wu.html