浅谈数据库访问效率的提高

浅谈数据库访问效率的提高

摘要：本文针对数据库的访问，从实时和批处理系统角度分别讨论了一些优化策略，在实时处理系统中，使用了数据库连接池、把常用数据放入内存、优化数据库索引和使用存储过程的方法对数据库进行优化，而在批处理中又增加了使用数据冗余、对数据进行汇总后处理、对数据表只写入最终处理结果以及使用分段事务的策略。

关键词：连接池，索引，存储过程，数据冗余、事务

数据库系统作为数据处理的基础平台，广泛应用于银行、保险、证券等行业。随着交易量的日益增长，应用系统所承受的处理压力越来越大。目前，解决这一问题的主要途径之一就是对硬件进行升级。这虽是一种简便、有效的方法，但需要投入大量的财力。如果在对应用软件进行设计及改造的过程中，能按照一定的设计规范，充分挖掘系统的处理潜力，最大程度地提高应用系统的处理效率，则可以用最小的投入保证应用系统在大交易量、大数据量的情况下稳定运行。下面就大概说一说数据库访问效率提高的几种方法。

（一）实时处理系统效率的提升

实时处理系统也即ＯＬＴＰ（On-Line Transaction Processing），联机事务处理）系统，其特点是单个请求的处理时间短，处理数据量小、并发量高。因此，针对这种特点，我们的目标就是缩短单个进程的处理时间，减少单个进程对系统ＣＰＵ、Ｉ/Ｏ等主要资源的占用。

1、使用数据库连接池

       比如说在一个用jsp/servlet/javabean实现的bs架构的信息系统中，一般情况下，在使用开发基于数据库的WEB程序时，传统的模式基本是按以下步骤：

　　1． 在主程序（如Servlet、Beans）中建立数据库连接。

　　2． 进行SQL操作，取出数据。

　　3． 断开数据库连接。

　　使用这种模式开发，存在很多问题。首先，我们要为每一次WEB请求（例如察看某一篇文章的内容）建立一次数据库连接，对于一次或几次操作来讲，或许你觉察不到系统的开销，但是，对于WEB程序来讲，即使在某一较短的时间段内，其操作请求数也远远不是一两次，而是数十上百次（想想全世界的网友都有可能在您的网页上查找资料），在这种情况下，系统开销是相当大的。事实上，在一个基于数据库的WEB系统中，建立数据库连接的操作将是系统中代价最大的操作之一。很多时候，可能您的网站速度瓶颈就在于此。

　　其次，使用传统的模式，你必须去管理每一个连接，确保他们能被正确关闭，如果出现程序异常而导致某些连接未能关闭，将导致数据库系统中的内存泄露，最终我们将不得不重启数据库。

针对以上问题，我们首先想到可以采用一个全局的Connection对象，创建后就不关闭，以后程序一直使用它，这样就不存在每次创建、关闭连接的问题了。但是，同一个连接使用次数过多，将会导致连接的不稳定，进而会导致WEB SERVER的频频重启。故而，这种方法也不可取。实际上，我们可以使用连接池技术来解决上述问题。当程序中需要建立数据库连接时，只须从内存中取一个来用而不用新建。同样，使用完毕后，只需放回内存即可。而连接的建立、断开都有连接池自身来管理。同时，我们还可以通过设置连接池的参数来控制连接池中的连接数、每个连接的最大使用次数等等。通过使用连接池，将大大提高程序效率，同时，我们可以通过其自身的管理机制来监视数据库连接的数量、使用情况等。下面我们以一个名为ConnectionPool的连接池为例来看看连接池的实现。先看看ConnectionPool的基本属性：

　　m_ConnectionPoolSize：连接池中连接数量下限

　　m_ConnectionPoolMax：连接池中连接数量上限

　　m_ConnectionUseCount：一个连接的最大使用次数

　　m_ConnectionTimeout：一个连接的最长空闲时间

　　m_MaxConnections = -1：同一时间的最大连接数

　　m_timer：定时器

　　这些属性定义了连接池与其中的每个连接的有效状态值。连接池的自我管理，实际上就是通过定时的对每个连接的状态、连接的数量进行判断而进行相应操作。

我们可以定义出ConnectionPool要完成管理所需要的基本接口：

public class ConnectionPool implements TimerListener{

　　public boolean initialize() //连接池初始化

　　public void destroy() //连接池的销毁

　　public synchronized java.sql.Connection getConnection() //取一个连接

　　public synchronized void close() //关闭一个连接

　　private synchronized void removeFromPool() //把一个连接从连接池中删除

　　private synchronized void fillPool() //维护连接池大小

　　public synchronized void TimerEvent() //定时器事件处理函数

}

　　通过这几个接口，已经可以完成连接池的基本管理。在TimeEvent（）函数中完成连接池的状态检验工作，fillPool()时连接池至少保持最小连接数。因为我们要保存每一个连接的状态，所以还需要一个数据库连接对象：

class ConnectionObject{

　　public java.sql.Connection con; public boolean inUse; //是否被使用标志

　　public long lastAccess; //最近一次开始使用时间

　　public int useCount; //被使用次数

}

加入了ConnectionObject对象后，在ConnectionPool中操作的应该只是ConnectionObject,而其他进程需要的只是ConnectionObject的con属性，因此我们再加入一个类，作为其他进程获得与返回连接的接口： CLASS Conn{

　　GetConnection(); //从连接池中取出一个有效连接

　　CloseConnection(); //返回连接，此时并没有关闭连接，只是放回了连接池

　　DestroyPool(); //销毁连接池

}

通过上面的介绍，我们可以看出，连接池技术的关键就是其自身的管理机制，以上的管理流程只是本人一点见解，关键是想向大家介绍一种思路，在此基础上，您可以进一步完善连接池技术为您所用.

2、减少对标准数据表的读操作，把常用数据放入内存

标准数据是指在ＯＬＴＰ系统中所需要使用的各类参数表、控制表。这类数据的特点是数据量小、数据更新频度低。对这类数据如果只简单地通过数据库的select 语句进行读操作，则会使后端服务在处理每一个交易请求时，有相当一部分资源和处理时间用在对参数表的读操作上，而且随着系统功能的进一步扩展，参数表的数量会增加，系统性能将随着功能的扩展而下降。

为了解决这一矛盾，可以将标准数据读入内存，并根据关键字用二分查找法搜索所需要的数据。

 标准数据守护进程（进程Ａ）是提供tuxedo标准数据查找功能的守护进程。该进程实现如下功能：①进程启动时将标准数据按关键字排序读入数组；②初始化消息队列；③提供每一标准数据，按关键字进行二分查找功能。后端服务进程（进程Ｂ）处理具体交易请求的tuxedo服务，进程Ｂ一旦需要使用标准数据，则不再以读数据库表的形式实现，而是通过消息队列向进程Ａ 发送请求，进程Ｂ接收前端请求后通过二分查找法直接从数组中抽取相应数据返回给进程Ａ使用。为了进一步提高系统性能，可以启动多个标准数据守护进程，建立多条消息队列，将进程Ｂ发送的请求均衡到各个标准数据守护进程中。实践表明，采用这种方式实现对标准数据的读操作的性能比读数据表的方式有大幅度提高。

3、优化数据库索引

在ＯＬＴＰ系统中对数据表建立合适的索引是进行数据结构设计时的基本要求，本文对数据库文档中建索引应遵循的规则不再重复，此处只强调三点。①ＯＬＴＰ使用的数据表每一张表必须有一个惟一的索引，并且应用程序能够使用该索引方便地查找所需要的记录，即该索引所使用的键值，应该是应用程序能够从其他模块或数据表中取得的。例如，假设明细账的索引为账号、交易日期、流水号，现在需要得到某账户末笔明细账，如果分户账中没有登记该账户最近一次的交易日期和流水号，就只能根据账号在明细账中用游标的方式取得末笔记录，使系统付出额外开销。这样的信息如果在分户账中有登记，就可以方便地通过索引定位并获取所需要的记录。②尽量少用复合索引，如果需要，其键值应尽可能少。③索引中的各字段值应该是发散的，即该字段所有可能的值不能过少，例如储蓄挂失表中的挂失状态，由于其值只有口挂、书挂、密挂等，这样的字段不应该建立索引，复合索引中也不应有该字段。

下面看一个例子。1.多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表；内外表的选择可由公式：外层表中的匹配行数*内层表中每一次查找的次数确定，乘积最小为最佳方案。

 

2.查看执行方案的方法-- 用set showplanon，打开showplan选项，就可以看到连接顺序、使用何种索引的信息；想看更详细的信息，需用sa角色执行dbcc(3604,310,302)。

 

三、不可优化的where子句

1.例：下列SQL条件语句中的列都建有恰当的索引，但执行速度却非常慢：

 

select * from record where

substring(card_no,1,4)='5378'(13秒)

select * from record where

amount/30< 1000（11秒）

select * from record where

convert(char(10),date,112)='19991201'（10秒）

 

分析：

where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表搜索，因此将SQL重写成下面这样：

 

select * from record where card_no like

'5378%'（< 1秒）

select * from record where amount

< 1000*30（< 1秒）

select * from record where date= '1999/12/01'

（< 1秒）

 

你会发现SQL明显快起来！

 

2.例：表stuff有200000行，id_no上有非群集索引，请看下面这个SQL：

 

select count(*) from stuff where id_no in('0','1')

（23秒）

 

分析：

where条件中的'in'在逻辑上相当于'or'，所以语法分析器会将in ('0','1')转化为id_no ='0' or id_no='1'来执行。我们期望它会根据每个or子句分别查找，再将结果相加，这样可以利用id_no上的索引；但实际上（根据showplan）,它却采用了"OR策略"，即先取出满足每个or子句的行，存入临时数据库的工作表中，再建立唯一索引以去掉重复行，最后从这个临时表中计算结果。因此，实际过程没有利用id_no上索引，并且完成时间还要受tempdb数据库性能的影响。

 

实践证明，表的行数越多，工作表的性能就越差，当stuff有620000行时，执行时间竟达到220秒！还不如将or子句分开：

 

select count(*) from stuff where id_no='0'

select count(*) from stuff where id_no='1'

 

得到两个结果，再作一次加法合算。因为每句都使用了索引，执行时间只有3秒，在620000行下，时间也只有4秒。或者，用更好的方法，写一个简单的存储过程：

create proc count_stuff as

declare @a int

declare @b int

declare @c int

declare @d char(10)

begin

select @a=count(*) from stuff where id_no='0'

select @b=count(*) from stuff where id_no='1'

end

select @c=@a+@b

select @d=convert(char(10),@c)

print @d

 

直接算出结果，执行时间同上面一样快！

总结：

 

可见，所谓优化即where子句利用了索引，不可优化即发生了表扫描或额外开销。

 

1.任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

 

2.in、or子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。

4、使用存储过程

将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来, 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完成命令。

存储过程的优点：

1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

3.存储过程可以重复使用,可减少数据库开发人员的工作量

4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权

（二）批处理系统效率的提升

批处理系统是指每一次交易请求中，接收大量的请求数据进行处理或按照请求的条件对数据库中的大量数据进行处理。金融交易系统中的批量代发代扣、日终处理、各类查询统计处理等，处理数据量大、响应时间长。采取多进程并发方式处理是优化批处理系统的基本手段之一，但系统资源是有限的，当进程数达到一定限度时，性能反而会下降，而且在同一个系统中，还要同时处理其他交易请求。例如，银行主机在进行日结处理时，仍要处理ＰＯＳ消费交易、ＡＴＭ取款交易等，不可以将系统资源全部分配给批处理系统。因此，采用合理策略提高应用系统本身的处理效率，并采取合理的并发处理策略，才是根本的解决途径。

 提高ＯＬＴＰ系统效率的策略对批处理系统同样适用。但基于批处理系统自身的特点，还可以考虑以下策略。

1、 使用数据冗余，用空间换时间

主要思路是对同一张表的数据进行冗余，使得处理该表时不再和其他表进行关联操作。例如，银行储蓄交易流水中对交易账号及账号所属机构进行了登记，而根据账号中的网点号和机构参数表进行关联也可以得到机构编码，因此流水账中的机构编码是冗余数据，但为了在日结处理中减少表的关联，设计ＯＬＴＰ交易时，对流水账可采取对账号所属机构编码进行冗余登记的策略。

2、 对数据进行汇总后处理

数据库中select语句的group by子句的使用，通常有一种误区，这就是无法用此句直接得到所需结果时，往往通过游标的方式直接对源表进行处理。其实，我们可以先对源数据进行一次汇总，再对汇总的结果进行二次加工。由于经过汇总后的记录数明显减少，因此可以极大地提高处理速度。

例如，分析客户分户账的数据得到预期的利息支出，由于利息计算比较复杂，无法像余额统计那样用select,group by等语句直接实现。如果账户的储种、开户日期相同，则其利率也相同，因此，可以按照如下用法得到中间结果：

select 储种，开户日期，sum（余额）

 from 分户账  

 where  账户未销户

 group by 储种，开户日期

 into temp 账户汇总表

 再对账户汇总表进行利息计算处理，效率的提升就显而易见了。

3、 对数据表只写入最终处理结果

如果数据无法通过汇总处理，必须通过逐笔分析才能得到统计数据，则可以采用此策略。如果每分析一条记录就更新一次统计结果，会使系统执行大量不必要的ｕｐｄａｔｅ操作，使系统处理效率低下。此时，可以采取对统计结果建立数组的方法，每分析一条记录，就去修改数据组中的值，而不是修改统计结果表中的值，待分析工作全部完成后，再将数组中的值写入数据库。

4、 使用分段事务

在批处理中如果事务过长，不仅会使系统处理速度下降，而且一旦出现长事务回滚，则会使系统性能进一步下降。因此，将长事务进行分段提交，可以提高系统效率和安全性。由于一旦出现差错则无法全部回滚，在使用分段事务时要设计系统的差错处理机制。

总结：

本文针对数据库的访问，从实时和批处理系统角度分别讨论了一些优化策略，在实时中，使用了数据库连接池、把常用数据放入内存、优化数据库索引和使用存储过程的方法对数据库进行优化，而在批处理中又增加了使用数据冗余、对数据进行汇总后处理、对数据表只写入最终处理结果以及使用分段事务的策略。应用系统的效率优化涉及主机系统、操作系统、数据库、网络、应用开发等方面，本文主要讨论了应用开发方面效率提升的策略。本文并非刻意追求效率的提高，而是希望将部分人的开发经验转化为群体知识，进而产生一个统一的规范，以有效保证项目开发的质量。

参考文献：

1、   党玉龙，《海量数据库的查询优化及分页算法方案》，http://www.csdn.net

2、   snapbug《如何让你的SQL运行得更快》http://online-degrees.9team.com/22/22519.htm

3、   孟春雷 《提升数据库应用系统处理效率的策略》《中国金融电脑》

4、  Snapbug 《有效发挥数据库的最大效率》http://www.snapbug.net/