数据库优化

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

目录

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次数据库访问……

例2，如果我们需要将一批数据插入到一个表中，最暴力的方式就是执行N 次的insert……

这样的例子很多，但是大家应该想想有没有更优的方案去实现。

针对例1，为什么不能用一条Update为文件和案卷建立关联关系？为什么不能借助rownum来为文件生成序号？

针对例2，为什么不能用insert into …… select 的语法去执行？

我们在实现业务逻辑的时候，应该先想好这个业务逻辑要做什么？需要哪些数据，这些所需数据是作为参数传递进来的呢还是需要从数据库获取到的。如果是需要从数据库获取到的，那么能不能通过1次数据库访问就获取到全部的参数？写完一个方法之后，统计一下你一共访问了多少次数据库，看看能不能减少？我想多数情况下，我们的实现里还是有很大的优化余地的。

如果你在自己的业务逻辑实现里发现了在循环体中访问数据库的代码，那么这很可能就是导致性能差的原因之一。以我个人的经验来看，绝大多数这样的代码都是开发人员没有认真分析设计导致的，并且都可以通过一定的技巧来避免这种实现。

少部分极为特殊的情况下，可能无法避免在循环体中进行数据库访问，那么好，能不能考虑一下批量操作？以数据迁移为例，从A库批量读取N条数据，再批量向B库插入M条，性能远远好过“读一条、写一条”的方式。

2.5.关于索引

简单的说，索引不是万能的，但是没有索引是万万不能的。

建立合适的索引不一定简单，但可以遵循一些简单的普适性原则：

对出现在select和条件子句中的列建立索引，一般情况下，要根据谓词的选择读来排列索引中各列的位置，选择度大的谓词所使用的列放在索引的前面，把那些只存在于select语句中的列放在索引的最后。

基数较大的列很适合用来作索引。

尽量避免在索引中使用多于5个的列。

对于多列索引，将查询中引用最多的列放在定义的前面。

避免添加与已有索引相似的索引。

不要创建冗余的索引，如“INX1=(A，B)， and INX2=(A，B，C)”就是冗余索引，INX1是不必要的

对某字段使用函数时，则该字段上的索引并不能起作用。当然，你可以建立函数索引，这意味着要对函数的结果加索引，而不是为字段加索引。

2.6.慎用lob

这应该是个见仁见智的问题。

如果你的初衷只是想保存一段文本（例如xml或者其他数据），并且这段文本本身在可预见的范围内不会无限制的扩大，其本身大小有限，那真的不适合用lob字段去存储。但假如你要存储二进制文件，可能无法避免的要使用lob（不过说实在的，企业级应用里直接使用数据库的lob来存储音视频、图像的恐怕也不多了，当然也许是我见得太少）。

文本数据为什么不首先考虑用varchar？（其实这里还隐含着一个问题，真的有必要使用xml来承载数据么？为什么不用更轻量级的JSON？），把一个varchar文本转换为xml对象应该比直接使用lob要简单不少吧？

如果你实践过了，证明VARCHAR真的不适合（还有JSON）承载数据，那么也许就只有xml更合适（这点我也持保留意见，即所谓的用XML来适应扩展性），但是必须使用lob来存储么？

在我们的系统里，有相当一些数据就是采用的XML结构形式来存储在lob 字段里的。

存储、查询和更新诸如非结构化 LOB 的数据，或者将数据“分解”到关系表中，然后再将其重新组合。该方法导致编程的复杂且低效，因为这些访问机制是不成熟的。

Oracle从9i开始（DB2从v8开始），都已经具备支持XML数据的特性，提供了用于在数据库中存储、检索和操作XML数据的新功能，并随着版本的升级，

极大的扩展了这些功能。我们可以直接使用XQuery来查询到所需要的数据，而不是从lob里把整个文件内容读出来再使用XML访问特性去获取所需的那一点数据。

所以，如果真的不能以JSON格式代替XML，那么请考虑使用数据库的XML 特性，而不是lob。

2.7.把逻辑放到查询中

在数据库应用程序中实现过程逻辑的方法有几种。 SQL语句内部可实现某种程度上的过程逻辑（尽管 SQL 语句应该说明做什么，而不是怎么做）。即便内嵌式 SQL的宿主语言非常完善，依然推荐尽量将上述过程逻辑放在SQL语句当中，而不是宿主语言当中，因为前一种做法效率更高。过程性语言的特点在于拥有执行迭代（循环）和条件（ if。。。then。。。else结构）逻辑的能力。 SQL 不需要循环能力，因为它本质上是在操作集合， SQL只需要执行条件逻辑的能力。

条件逻辑包含两部分—— IF和ELSE。要实现 IF的效果相当容易——where子句可以胜任，困难的是实现ELSE逻辑。例如，要取出一些记录，然后对其分组，每组进行不同的转换。 case表达式（Oracle早已在 decode() （注1 ）中提供了功能等效的操作符）可以容易地模拟 ELSE逻辑：根据每条记录值的不同，返回具有不同值的结果集。下面用伪代码表达case结构的使用：

CASE

WHEN condition THEN

WHEN condition THEN

。。。

WHEN condition THEN

ELSE

数值或日期的比较则简单明了。操作字符串可以用 Oracle的greatest()或 least()，或者 MySQL的strcmp() 。有时，可以为 insert 语句增加过程逻辑，具体办法是多重 insert 及条件 insert，并借助merge语句。如果DBMS 提供了这样语句，毫不犹豫地使用它。也就是说，有许多逻辑可以放入SQL语句中；虽然仅执行多条语句中的一条这种逻辑价值不大，但如果设法利用case 、merge或类似功能将多条语句合并成一条，价值可就大了。

总结：只要有可能，应尽量把条件逻辑放到SQL 语句中，而不是 SQL的宿主语言中。

2.8.自定义函数

如果某些处理在应用层实现效果很差，那么可以考虑一下使用自定义函数，例如文号字段的形式可能如下图所示:

如果客户要求根据文号字段中的年度和发文号进行排序，那么就需要使用函数对文号值进行处理，Oracle提供了正则功能，可以与字符串函数结合方便的提取字段值中的“年度”

与“发文号”。但如果数据库没提供有合适的函数，你也可以自己写一个自定义函数来实现类似功能（当然，处理方案不只这一种，也许你可以考虑用视图和连接）。

这里的建议是，函数中最好不要包含查询等逻辑，而只是包含一些简单的针对所给值的处理。

还有要注意的是，如果在列上用了函数，那么该列上的索引是使用不上的。

2.9.使用PreparedStatement

很多面试题里也出现过，PreparedStatement和statement的区别？哪个性能更好一些？你能准确的回答出来么？

当一个数据库接收到一条语句的时候，数据库引擎首先解析这条语句，查看语法错误。一旦语句解析了，数据库需要找出最有效的方法来执行这条语句。这个计算起来代价很大。数据库检查什么索引(如果有的话)能有所帮助，或者它是否能全部读出一张表中所有的记录。数据库根据这些关于数据库所存数据的统计数字来找出最好的办法。一旦制订出查询方案，就可以由数据库引擎来执行。需要CPU 来产生访问方案。理想的情况，如果我们把相同的语句给数据库发送两次，我们期望数据库重用第一条记录的访问方案。这会比第二次重新产生方案要使用较少的CPU。语句缓冲数据库可以进行调节来做语句缓冲。通常包含一些类型的语句缓冲。缓冲使用语句本身作为关键字，访问方案和相应的语句存储在缓冲区中。这样就允许数据库引擎对以前执行过的语句所使用的访问方案进行重用。举个例子来说，如果我们向数据库发送这样一条语句"select a，b from t where c = 2"，计算好的访问方案就放入缓冲区了。如果我们以后再使用同样的语句，数据库就能重用以前的访问方案，这样就能节省CPU。但是要注意，整条语句是一个关键字。例如，如果我们后来发送的语句是"select a，b from t where c = 3"，那么就不会找出以前的访问方案。因为"c=3"和"c=2" 是不一样的。

为了解决这类问题，我们就需要使用参数化SQL，写成“select a，b from t where c = ?”的形式。在"c=?" 部分带有不同的参数。这样就允许数据库重用语句的访问方案，使程序在数据库内部运行得更高效。这基本上能使你的程序运行得更快，或者使数据库用户能更多地使用CPU。

当我们使用J2EE 服务器的时候，事情会变得更加复杂。通常情况下，一个预先准备好的语句(prepared statement) 是和一个单独的数据库连接相关联的。当连接关闭时，语句就被丢弃了。一般来说，一个胖客户端应用程序在得到一个数据库连接后会一直保持到程序结束。一个J2EE 应用程序只在一个请求的生存时间中保持一个连接。这意味着在他处理每一个请求时都会重新创建语句，就不象胖客户端只创建一次，而不是每个请求都创建那样有效，当J2EE 服务器给你的程序一个连接时，并不是一个真正的连接，而是一个经过包装的。你可以通过查看那个连接的类的名字来检验一下。它不是一个数据库的JDBC 连接，是你的服务器创建的一个类。通常，如果你调用一个连接的close 方法，那么jdbc 驱动程序会关闭这个连接。我们希望的是当J2EE 应用程序调用close 的时候，连接会返回到连接池中。通常会通过设计一个代理的jdbc 连接类来做这些，但看起来就象是实际的连接。当我们调用这个连接的任何方法时，代理类就会把请求前递给实际的连接。但是，当我们调用类似close 的方法时，并不调用实际连接的close 方法，只是简单地把连接返回给连接池，然后把代理连接标记为无效，这样当它被应用程序重新使用时，我们会得到异常。包装是非常有用的，因为它帮助J2EE 应用程序服务器实现者比较聪明地加上预先准备语句的支持。当程序调用Connection。prepareStatement 时，由驱动程序返回一个PreparedStatement 对象。当应用程序得到它时，保存这个句柄，并且在请求完成时，关闭请求之前关闭这个句柄。但是，在连接返回到连接池之后，以后被同样或者另一个应用程序重用时，那么，我们就理论上希望同样的PreparedStatement 返回给应用程序。J2EE PreparedStatement 缓冲J2EE PreparedStatement 缓冲由J2EE 服务器内部的连接池管理器使用一个缓冲区来实现。J2EE 服务器在连接池中保存一个所有数据库的预先准备语句的一个列表。当一个程序调用一个连接的prepareStatement 方法时，服务器先检查这个语句是否已经有了，如果是，相应的PreparedStatement 就在缓冲区内，就返回给应用程序，如果不是，请求就会传递给jdbc 驱动程序，请求/预先准备语句对象就会加入到缓冲区里。对于每一个连接我们需要一个缓冲区，因为这是jdbc 驱动程序的工作要求。任何返回的preparedStatement 都是针对这个连

接的。

如果我们要利用缓冲区的优势，要使用和前面相同的规则我们需要使用参数话的查询，这样它们就会和已经在缓冲区的某一个匹配。大多数应用程序服务器都允许你调整缓冲区的大小。

3.一些开发中的建议

这部分是一些开发过程中的建议，因为我们用得最多的是Oracle，所以内容也只针对Oracle，但不代表其他数据库不具备那些特性。

3.1.使用jdbcTemplate代替原生JDBC

我们使用JDBC来实现DAO层，没有使用其他的O/R解决方案比如hibernate，也没有使用诸如myBatis、JPA、EJB之类的解决方案。这合理么？也许不。但这里要讨论的不是这个问题。

我们在系统中使用了Spring作为基础框架，她同样提供了对JDBC操作的友好的封装，这里建议在所有使用到JDBC的地方，都使用spring提供的jdbcTemplate及其相关组件。在我的项目里，除了遗留代码之外，都要求使用Spring的jdbcTemplate来处理数据库访问。具体的用法请大家参考Spring的手册和API doc，这里不做赘述。

3.2.使用Oracle dblink和同义词

这方法主要是针对两台不同的数据库服务器，从一台数据库服务器的一个用户读取另一台数据库服务器下的某个用户的数据，这个时候可以使用dblink（当然前提是客户方面的

DBA允许我们建立dblink）。

一个最典型的业务场景就是历史数据的迁移，此时我们就不需要配置两个数据源来回的切换了。

同义词是数据库对象的别名，这个对象可以是表，视图，序列，过程，函数，程序包等。通过使用同义词，用户可以访问其它用户的数据库对象而无需指定用户前缀。如用户CQDL 要访问CQDL0001的表D_FILE1_0001，必须使用CQDL0001.D_FILE1_0001来引用，如果为CQDL 创建一个D_FILE1_0001的同义词代表CQDL0001.D_FILE1_0001，那么CQDL就可以用该同义词像访问自己的表一样引用CQDL0001.D_FILE1_0001了。

同义词用途:

简化SQL语句

隐藏对象的名称和所有者

为分布式数据库的远程对象提供了位置透明性

提供对对象的公共访问

显而易见的，大家可以考虑考虑采用同义词简化我们的“辅库”的访问方式（干掉分布式事物？）。当然，配置同义词也需要客户那里的DBA的认可。

采用DBLINK和同义词的好处显而易见，但如果你只使用了一个连接池，那么也许你要考虑是否对连接数进行放大。

3.3.使用Oracle即时客户端

通常在客户那里我们需要在服务器上安装PL/SQL或者Toad来访问远程Oracle数据库，也许你不介意在硬盘里保存一份几百兆的Oracle安装包，但实际上你可以使用Oracle instant client来解决这个问题，相比之下她很小巧，只有几十兆，可以近似等同于绿色软件。而且Oracle提供了windows、linux和AIX等不同的版本。试试吧，的确挺方便。

最后，祝大家春节快乐。