数据库优化

关于开发过程中数据库访问的一些事情

目录

1. 前言 (1)

2. 关于性能 (1)

2.1. 关于冗余 (2)

2.2. 视图 (3)

2.3. 关于like的优化 (3)

2.4. 减少数据库访问次数 (4)

2.5. 关于索引 (6)

2.6. 慎用lob (7)

2.7. 把逻辑放到查询中 (8)

2.8. 自定义函数 (9)

2.9. 使用PreparedStatement (10)

3. 一些开发中的建议 (12)

3.1. 使用jdbcTemplate代替原生JDBC (12)

3.2. 使用Oracle dblink和同义词 (12)

3.3. 使用Oracle即时客户端 (14)

1.前言

本文用来说明一些在开发过程中经常碰到的数据库访问方面的问题，提供一些解决问题的思路和经验。同时，期望能够引起大家的重视，避免重犯类似的错误或者少走一些弯路。

如果本文能为大家的开发工作起到一些帮助，那是最好。但如果能启发大家对问题的思考，并因此而找出更好的解决办法，或者通过实践验证了文中的方法的错误和不足，那才是真正起到了抛砖引玉的作用，才让本文体现出了最大价值。

限于本人水平有限，如文中存在一些错误，恳请大家指证出来。毕竟，通过此文引起共鸣或者有助于研发部门内讨论沟通风气的形成，才是举办这种交流的最终目的。

2.关于性能

我们这种业务系统，是典型的OLTP（On-Line Transaction Processing联机事务处理）系统。系统本身是基于关系型数据库开发的，绝大部分业务需求的实现都离不开数据库的“增、删、改、查”操作，那么在开发过程中，难免就会碰到这样或那样的问题，也许你注意到了，也许你没注意到；也许问题的后果不严重，也许问题的后果很严重，客户很生气。总之，避免问题的发生和如何解决问题是我们需要去认真考虑的，下面就从数据访问的性能方面来做一些讨论。

多数人认为只有当用户感觉性能差时才需要进行调整，此时即使使用最有效的策略，往往也太迟了。如果你不愿意重新设计应用或者你无法承受修改所带来的代价，那也只能通过数据库优化这一条路可行了。但是，为什么在最初我们不

去关注这个问题呢？

其实，性能调整应该是跨越整个应用系统的开发生命周期的，我们作为应用程序开发人员，从进行设计到实际编码，都需要考虑性能问题。下面就从一些典型的问题来讨论一下。

2.1.关于冗余

关系数据库设计是对数据进行组织化和结构化的过程，核心问题是关系模型的设计。对于数据库规模较小的情况，我们可以比较轻松的处理数据库中的表结构。然而，随着项目规模的不断增长，相应的数据库也变得更加复杂，关系模型表结构更为庞杂，这时我们往往会发现我们写出来的SQL语句的是很笨拙并且效率低下的。更糟糕的是，由于表结构定义的不合理，会导致在更新数据时造成数据的不完整。因此，就有必要学习和掌握数据库的规范化流程，以指导我们更好的设计数据库的表结构，减少冗余的数据，借此可以提高数据库的存储效率，数据完整性和可扩展性。

大家都知道在进行数据库设计时要以三个基本范式作为基础，但是严格遵守范式，往往不是最好的设计。为了提高数据访问的效率，常常需要降低范式标准，适当增加冗余，达到以空间换时间的目的。

这里要注意的是，主键与外键在多表中重复出现，并非是冗余数据，非键字段的重复出现，才是数据冗余！

如，商品具有“单价、数量和总价”三个字段，总价是由单价乘以数量得来的，一般来说，这种派生冗余就值得尝试，在一些业务场景下，可能会大大提升效率。

我们要尽量避免的是所谓的重复性冗余，说白了就是某个字段在几个数据表中都存在且面临着不同的修改入口，这样的设计就会容易导致数据的不一致性出现。

2.2.视图

与基本表、代码表、中间表不同，视图是一种虚表，它依赖数据源的实表而存在。视图是供程序员使用数据库的一个窗口，是基表数据综合的一种形式，是数据处理的一种方法，是用户数据保密的一种手段。为了进行复杂处理、提高运算速度和节省存储空间，视图的定义深度一般不得超过三层。若三层视图仍不够用，则应在视图上定义临时表，在临时表上再定义视图。这样反复交迭定义，视图的深度就不受限制了。

2.3.关于like的优化

在SQL中，like关键字提供了模糊匹配的功能，但是使用like时要避免因为图省事而导致SQL性能差的问题。

简单的说，可尝试这样的优化：

尽量不使用“like ‘%……%’”的形式，因为这种形式很难优化，会导全表扫描（当然如果是在可预见的时期内数据量很少的表，则

无所谓）。如果必须要用，考虑使用数据库方言中的字符串函数，如

Oracle中的instr，最终的SQL写法可能如下：

select count(*) from table t where instr(t。column，'xx')>

DB2中可以考虑用Locate函数。

如果是“like ‘xx%’”的形式，可以用到索引（前提是列上有索引）。

如果是“like ‘%xyz’”的形式，麻烦一些，可考虑尝试使用数据库方言中的reverse函数和反转键索引。最终的SQL写法可能如下：

select * from table where reverse(column_name) like ‘zyx%’

2.4.减少数据库访问次数

要从一个表中提取多段信息时，采用多次数据库访问的做法非常糟糕，即使多段信息看似“无关”（但事实上往往并非如此）。例如，如果需要多个字段的数据，千万不要逐个字段地提取，而应一次操作全部完成。

稍微引申一下，在某个业务实现中，中间件服务器对数据库服务器访问的次数越多，性能越差。所以我们应尽可能根据业务特点，减少数据库访问次数，从而提升性能。

例1，如果我们要将100个文件实现“组卷”这个需求，最暴力的方法就是先insert一条案卷记录，再使用循环，每一条文件记录执行一次update。先不说其他的逻辑处理，光把文件和案卷关联起来就需要100次数据库访问……