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

Tags: java database
Java 开发人员会混合面向对象思维和命令行思维,这与他们的工作经验和能力相关:
  • 技术能力(任何人都可以编写命令式代码)

  • 教条主义(使用"模式-模式",比如,在任何地方都使用设计模式,并且给它们命名)

  • 心情(真正的面向对象会比命令行编程要多写很多代码)

不过,当 Java 开发人员编写 SQL 时,一切都变了。SQL 是声明式语言,它没有任何面向对象和命令行的编程思想。使用 SQL 可以非常容易的编写传。在SQL中要写个查询语句是很简单的。但在Java里类似的语句却不容易,因为程序员不仅要反复考虑编程范式,而且也要考虑算法的 问题。

下面会介绍 Java 开发人员使用 JDBC 编写 SQL 语句时常见的 10 个错误(无先后顺序),要看看 Java 程序员写 SQL 时易犯的另外 10 个错误,猛戳这里

1. 忘记了啥是 NULL

对 NULL 的错误理解是 Java 程序员在编写 SQL 时可能出现的最严重的错误。这是由于(不仅仅是) NULL 也被称作 UNKNOWN 的原因,如果我们将 NULL 称之为 UNKNOWN 或许会更好理解。另一个原因是 JDBC 在读取数据或绑定变量时会将 SQL 的 NULL 映射为 Java 的 null,这会让开发人员认为 SQL 中的 NULL 和 Java 中的 null 是相同的,它们应该有相同的行为。

一个误解 NULL 的疯狂的例子是,当NULL谓词用于行值表达式

另一个比较合适的误解 NULL 的例子是,比较 MySQL 中的 NOT IN, NOT EXISTS, LEFT JOIN / IS NULL

解药:

多练习,当你写SQL时要不停的思考NULL的用法:

  • 这个NULL完整性约束条件是正确的?

  • NULL是否影响到结果?

2. 使用 Java 在内存中处理数据

很多 Java 程序员不是很精通 SQL,偶尔用用 JOIN 和 UNION,没用过 window 函数和分组。很多 Java 开发人员会使用 SQL 读取数据,将数据转换到某个 Java 集合类型中,通过循环遍历集合进行计算(至少在 Java 8 集合类型改进前是这样的)

不过一些 SQL 数据库支持高级 OLAP 特性可以更好的完成这类工作并且更易于编写。一个(非标准的)示例是Oracle MODEL 子句。让数据库来处理数据吧,仅将结果返回给 Java 程序,毕竟一些非常聪明的家伙已经优化了这些昂贵的产品。所以,通过将数据库转换为 OLAP,你可以获得两个好处:

  • 简单:编写正确的 SQL 来完成功能通常比 Java 要容易些

  • 性能:数据库通常比你自己的算法要高效,并且更重要的是,你不需要传输成千上万的记录到 Java 应用服务器的内存中了。

解药:

每当你在 Java 代码中实现数据仓库的逻辑时,最好问问自己,有没有办法让数据库来完成这些工作呢?

3. 使用UNION而不是UNION ALL

汗,UNION ALL 就比 UNION 多一个关键字。如果在SQL标准被定义为下面内容,会好很多:

  • UNION (允许重复)

  • UNION DISTINCT (去重)

不仅是去除重复很少需要使用到(有时甚至是错误的),而且这在处理有很多字段的大数据时是非常缓慢的,因为子查询需要排序,并且每条数据需要和前面的数据进行比对。

需要注意的是,即使SQL标准规定了 INTERSECT ALL 和 EXCEPT ALL,几乎没有任何数据库实现这些用处不大的操作集。

解药:

任何时候你打算使用 UNION 时,考虑一下你心里想的是不是 UNION ALL。

4. 使用 JDBC 来分页大结果集

大部分数据库都能够通过某种方式支持对已排序的结果集进行分页,比如:LIMIT ... OFFSET, TOP ... START AT, OFFSET ... FETCH 子句。对于没有直接支持这类语法的数据库,实际上也可以使用这类方法来进行分页 ROWNUM (Oracle), ROW_NUMBER() OVER() filtering (DB2, SQL Server 2008 及更低版本), 这些方法对大数据集的分页比在内存中快 N 倍。

解药:

使用这些子句进行分页,或者其他的框架(Hibernate之类)。

5. 在 Java 内存中连接数据

从SQL的初期,一些开发人员在他们的 SQL 语句中使用 SQL 连接时仍然有不安的感觉,还有就是对 JOIN 动作缓慢速度的内在恐惧。当基于成本的优化器选择执行一个嵌套循环(nested loop)时这个顾虑是对的,因为它有可能在创建连接表前,将整张表加载进内存。但是,这种几率很小,当使用合适的预测、约束和索引的情况下,MERGE JOIN 和 HASH JOIN 的速度是非常快的,这些都需要定义正确的元数据(参考这篇文章)。然而,还是有很多 Java 程序员会将分别将表数据加载到 Java 集合类型中并且在 Java 内存中进行连接。

解药:

如果你在多个步骤中从不同的表选择数据,考虑一下是否可以使用一个 SQL 语句解决这类问题。

6. 在笛卡尔积中使用 DISTINCT 或 UNION 去重

通过复杂的连接,人们可能会对 SQL 语句中扮演关键角色的所有关系失去概念。特别的,当处理多外键关联时,可能会忘记在 JOIN ... ON 语句中相关的断言。这种情况会返回重复的记录,不过也许仅在特殊的情况下才会这样。一些开发人员可能会选择使用 DISTINCT 来移除重复数据,这是不正确的:

  • 这(可能)解决了表象,但没有解决问题。在特殊情况下可能也没有解决表象。

  • 当结果集很大且字段很多时,这是非常慢的。因为 DISTINCT 在移除重复数据时会先执行 ORDER BY

  • 这对于大型笛卡尔积查询结果非常缓慢,因为它会将所有数据加载到内存

解药:

作为一个经验法则,当你得到不正确的重复数据时,首先需要检查你的 JOIN 断言,有可能在某个地方有一个很难察觉的笛卡尔积集合。

7. 没有使用 MERGE 语句

这是不是一个错误,但可能因为对一些理论的欠缺或信心不足而没使用强大的MERGE语句。有些数据库具备其他形式的 UPSERT 语句,比如:MySQL 的 ON DUPLICATE KEY UPDATE 语法。但MERGE真的非常强大,尤其是在扩展了 SQL 标准数据库中,比如: SQL Server

解药:

如果你使用像联合INSERT和UPDATE或者联合SELECT .. FOR UPDATE然后在INSERT或UPDATE等更新插入时,请三思。你完全可以使用一个更简单的MERGE语句来远离冒险竞争条件。

8. 使用 aggregate 函数而没有使用 window 函数

在介绍 window 函数前,在 SQL 中聚合数据就是代表着结合 GROUP BY 子句和聚合函数。这在大多数情况下工作的很好,如聚合数据需要浓缩常规数据,那么就在join子查询中使用group查询。

不过 SQL 2003 实现了大多数流行数据库都实现的 window 函数。window 函数可以在未进行分组的结果集上聚合数据。事实上,每个 window 有它自己的、独立的 PARTITION BY 子句,对于做报表来说,这是个非常棒的工具。使用 window 函数可以:

  • 编写可读性更好的 SQL(但在子查询中没有GROUP BY语句专业)

  • 提高性能,RDBMS 可以更容易的优化 window 函数

解药:

当你在子查询中写 GROUP BY 子句时,考虑一下是否可以使用 window 函数完成同样的功能。

9. 使用内存间接排序

SQL的ORDER BY语句支持很多类型的表达式,包括CASE语句,对于间接排序十分有用。你可能认为你应该在 Java 内存中排序数据,因为你会想:

  • SQL排序很慢

  • SQL排序办不到

解药:

如果你在内存中排序任何SQL数据,请再三考虑,是否可以在数据库中排序。这与使用数据库分页的情形很相似。

10. 逐条插入大量数据

JDBC 可以处理批处理,并且你应该在 JDBC 中使用批处理。不要一条条插入上千台哦数据,因为这会每次重新创建 PreparedStatement 对象。如果你要插入的所有数据都在一张表中,创建一个 INSERT 语句的批处理,使用一条语句并多次绑定值集合。根据你数据库和数据库配置不同,你可能需要在插入一定量数据后执行提交(commit)动作,这样可以减小 UNDO log 的体积。

解药:

总是使用批处理插入大量数据。

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



相关文章