信息管理系统数据库性能优化研究

朱 萍,吴 蕾

(安徽警官职业学院 信息管理系,合肥 230061)

信息管理系统数据库性能优化研究

朱 萍,吴 蕾

(安徽警官职业学院 信息管理系,合肥 230061)

随着信息化管理系统的发展,作为信息管理系统的重要组成部分,数据库技术已经得到了广泛的应用.为了保证信息管理系统的正常、高效、安全的运行,就必须要考虑数据库的性能问题.数据库的性能优化包括缩短查询响应时间,减小查询所需要的CPU时间和磁盘I/O时间,提高数据库服务器的吞吐量.数据库优化的方法主要包括数据库设计规范优化、SQL语句优化、改善查询计划等手段.通过对数据库优化方法的研究,并且以一个具体的信息管理系统数据库优化实例,对数据库优化方法的具体应用进行介绍.为数据库技术和信息管理系统的进一步发展尽自己的一点努力.

信息管理系统; 数据库技术; 性能优化

数据库技术主要用于辅助信息管理系统进行数据的管理和维护,是目前应用最为广泛的,同时也是发展最为迅速的技术之一.随着数据库管理系统应用越来越广泛,如何进行数据库系统的构建,提高数据库运行效率,已经成为了目前信息技术一个非常重要的研究方向.为了提高数据库的运行效率,需要从数据库的设计、SQL语句的编写、数据访问方式等多个方面来进行全方面的优化,从而实现在一定的软硬件条件下,尽可能的提高数据性能.

1　数据库性能优化概述

1.1数据库性能优化的概念

性能是对数据库系统的处理能力、响应时间以及可用性的衡量,其中响应时间是指用户从提交SQL数据操作语句到获得操作结果的时间; 吞吐量是指单位时间内系统处理SQL语句的速率.一般而言,数据库系统的响应速度越快,吞吐量越大,则表示数据库系统的性能越好.

数据库性能优化就是提升数据库响应速度,增大数据库吞吐量所采取的各种数据库管理策略.通过缩短每个数据库查询的响应时间,减小查询所需要的CPU时间和磁盘I/O时间,提高数据库服务器的吞吐量,从而提高整个数据库管理系统的性能.

1.2数据库优化的内容

数据库性能优化的五个重要阶段如图1所示[1].

数据库优化主要包括如下几个方面的内容:

(1) 操作系统级别的优化

主要是对服务器的软硬件性能进行改进,减少磁盘I/O瓶颈、内存交换过度、CPU过载等问题.

(2) 反规范化设计

数据库的规范化设计有利于保证数据库中的一致性和安全性,但是规范化设计同时会增加数据表链接、外键和表的数量,从而导致性能下降.一定程度的反规范化设计,可以提高数据库性能,优化数据查询.

(3) SQL语句优化

虽然不同的SQL语句设计方法可能会达到相同的数据维护目的,但是高效的SQL语句,合理的应用游标和安排事务可以提高数据维护性能[2].

图1　数据库性能优化流程

2　数据库性能优化设计

目前,市场上有各种各样的数据库管理系统产品,不同的数据库管理系统产品虽然其理念和接口大多类似,但是仍然存在一些细微的差别.在本文中主要以SQL Server数据库管理系统为例,对数据库性能优化的具体方案进行研究.

2.1数据库设计规范优化

数据库规范化设计可以保证数据的一致性和安全性,但是同时也会导致数据维护性能下降.一般来说数据库设计需要满足前三级数据规范化标准:第1范式即关系中的每个属性不可再分简单项,是最基本的范式形式; 第2范式是在第1范式的基础上,每个非主属性都完全依赖于每个关系键; 第3范式是在第2范式的基础上,每个非主属性都不传递依赖于关系模式的关系键.

一定程度的非规范化设计,可以改善系统性能,因此在数据库的具体设计过程中,可以通过如下的实践经验来提高系统性能:

(1) 重新定义数据表,减少行数据或者外部属性数据的管理开支.

(2) 将数据表中的总计、最大值等需要进行全表查询的操作结果写入到数据库实体中.

(3) 如果表中产生了4路及其以上的合并关系,可以考虑添加重复列.

2.2SQL语句优化

SELECT查询语句是数据库操作最为频繁的语句,高效的SQL查询语句可以极大的提高数据库性能[3].SQL查询语句的优化方式包括如下几个方面的内容.

2.2.1避免使用SELECT *语句

SELECT*语句会导致SQL语句查询数据表的每一列,从而增加数据查询开销,因此需要根据“需要多少,提多少”的原则,显示搜索数据表的具体列,从而减少搜索的范围,以及查询返回的数据量,避免CPU处理时间和I/O资源的浪费.

2.2.2避免使用DISTINCT

在查询语句中使用DISTINCT,可以保证不会返回重复记录,但是使用DISTINCT

,需要对整个表进行多次筛选来删除重复记录,从而极大的增加了I/O操作次数和CPU处理时间,因此尽可能避免使用DISTINC

.

2.2.3正确使用ORDER BY子句

ORDER BY子句与DISTINCT类似,需要对查询的结果进行排序,当查询结果记录数较多时,就会显著的降低查询效率,因此应尽可能避免ORDER BY子句的使用.如果查询结果的顺序很重要,也要尽可能减少需要进行排序的列数.

2.2.4 正确使用包含非操作符的语句

数据库管理系统可以根据SQL语句中的WHERE,自动选择能够缩小搜索空间的表索引,实现自动优化.在SQL语句中,如果一个表达式不满足扫描参数(SARG),那么无法进行自动优化.而不满足SARG的表达式都包含非操作符,例如NOT、NOT LIKE、NOT IN、NOT EXISTS等,因此在SQL语句中应用这些包括非操作符的子句时需要根据具体情况特别注意.

2.2.5 WHERE子句的正确使用

WHERE子句的内容和顺序会决定数据表的连接[4],因此为了提高系统性能,WHERE子句的使用应该注意如下几个方面的内容.

(1) WHERE子句应该根据索引的范围大小和顺序来确定前后顺序,尽量保证索引范围从大到小,字段顺序与索引顺序一致.

(2) 不要在WHERE子句“=”的左边使用算术、函数运算或者其他表达式运算,否则将会导致数据库系统中的索引无法被正确使用.

2.3改善查询计划

除了进行SQL语句优化以外,还可以通过如下的技巧来改善数据库查询计划,达到提高数据库查询优化的目的.

2.3.1合理使用索引

索引是数据库中非常重要的数据结构之一,合理使用索引技术可以解决95%以上的数据库性能问题,从而很好的提高系统性能[5].索引的合理使用需要注意如下几个原则:

(1) 在非外键,而且需要经常连接的列上建立索引,而不需要经常连接的列则有优化器自动建立索引.

(2) 在需要进行频繁的ORDER BY或者GROUP BY的列上建立索引.

(3) 在不同值较多的列上建立索引,若在不同值较少的列上建立索引可能会严重降低更新速度.

(4) 对于大量需要进行范围查询、重复值的列上可以考虑建立索引.

(5) 当应用需要频繁的存储多列,而且这些列都含有重复值时可以考虑建立组合索引.所建立的组合索引尽量覆盖关键查询.

2.3.2正确使用主键

整型主键的处理开销要比字符型主键的处理开销要大,当数量小的时候可能不会有太大的影响,但是当数据表中的数据量达到一定规模时,较小的改动也会极大的优化数据库性能.

2.3.3使用临时表

尤其是数据量较大的时候,可以根据条件建立临时表,从而避免反复访问数据表,通过条件建立查询表可以减少数据表中的记录数,有利于提高连接性能,减少了磁盘I/O,大幅提高数据库性能[6].在建立临时表时,尽量不建立全局临时表,而且在临时表使用完之后,要及时进行删除.

2.3.4使用存储过程

在数据库访问时,通常需要经过如下五个步骤:①将SQL查询语句发送到服务器; ②服务器进行SQL语句的编译; ③查询执行计划优化; ④查询语句执行; ⑤返回查询结果.存储过程的可以建立优化的查询方案,并将查询方案保存在高速缓存中,在下一次应用时可以节省编译和优化的时间,从而提高了数据查询性能.

使用存储过程优化数据库的原则如下:

(1) 对在应用中频繁使用的SQL语句建立存储过程.

(2) 尽量不使用自定义的返回参数,尽量使用SQL自带的返回参数.

(3) 在存储过程中,尽量减少不必要的参数.

3　数据库优化实例

3.1实例基本情况

在某信息管理数据库管理系统中包含了预约信息表、保险信息表、诊疗信息表等共计122个信息表,其中系统功能表为20个,基本信息表为102个,总字段有七百多个.基本信息表之间的连接关系较为简单,主要通过ProcNum、PartNum等字段进行关联.

应用系统大量的信息表需要较多的服务器资源,因此为了满足应用系统性能需求,需要进行数据库系统的优化.具体的优化措施包括如下几个方面:

3.2数据库设计优化

进行适当的非规范化设计,通过有益冗余来减少数据表之间的连接,对一些数据表进行拆分,例如系统中的Procedurecode表中字段如表1所示:

表1　Procedurecode数据表字段

在数据查询过程中,很多查询只是根据ProcCat字段的判断来查询数据表中的Dscription字段和ADACode字段,而其它字段很少涉及.为此,可以将Procedurecode数据表按照常用字段和非常用字段拆分成两个表,其中一个表中包含了ProcCat、Dscription和ADACode三个字段,其余的字段加上ADACode字段作为主键组成一个新表.

经过数据表的拆分,虽然ADACode字段被分到两个数据表中,造成了数据的冗余,但是在进行数据查询时,只需要扫描三个字段,当数据表中数据量较大时,可以显著的节省数据扫描时间,提高数据查询效率.

除此之外,可以针对数据表中的三个字段建立组合索引,形成“索引覆盖”,进一步加快系统响应速度.

3.3SQL语句优化

在应用系统中,可以根据病人的名字进行模糊查询来查询病人信息.该SQL查询语句为:“SELECT PatStatus,… FROM patient WHERE Lname LIKE ‘%f’”.这个SQL语句的查询效率如图2所示.

在该查询SQL语句中LIKE ‘%f’不能使用索引扫描,而如果改成LIKE ‘f%’,则可以利用索引扫描来提高查询效率.SQL查询语句优化后的执行效率如图3所示.

图2　优化前的SQL查询语句的执行效率

图3　优化后的SQL查询语句的执行效率

通过SQL查询语句优化前后的查询计划对比可以看出,优化前后的CPU const降低了0.155ms(降低了96.8%),I/O const降低了32.05ms(降低了83.68%),优化效果显著.

[1] 吴洁明,周 锦.基于Oracle数据库SQL查询语句优化规则的研究[J].山西大学理工学院学报(自然科学版),2013(06):41～43

[2] 马 汉.数据库SQL优化大总结[J].计算机与网络,2014(07):119～121

[3] 薛 亚.SQL server数据库查询优化探讨[J].软件工程师,2014(07):13～14

[4] 宋国平.SQL Server数据库性能优化策略探析[J].吉林广播电视大学学报,2014(05):97～98

[5] 周 涛.SQL Server 2005大型数据库优化之表分区详解[J].云南大学(自然科学版),2013(12):87～89

[6] 林元彦.SQL Server数据库性能分析与优化[J].电子技术与软件工程,2013(10):103～105

The Performance Optimization of Information Management System Database

ZHU Ping,WU Lei
(Department of Information Management,Anhui Vocational College of Police Officers,Hefei 230061,China)

With the development of information management system,as an important part of the management information system,database technology has been used widely.In order to ensure the information management systems operate normally,efficiently and safely,such issue as database performance optimization must be considered.Database performance optimization including reducing query response time,reduce the query need time and disk CPU I/O time,improve the throughput of database server.The mainly method includes database design specification optimization means,SQL statement optimization,improve the query plan etc.Through the study of database optimization method,and with a specific information management system database optimization example,this paper introduced the specific application of database optimization methods,which could do some efforts for the further development of database technology and information management system.

information management system; database technology; performance optimization

TP311.13

A

1672-5298(2015)01-0033-04

2015-01-02

朱 萍(1972- ),女,山东肥城人,硕士,安徽警官职业学院信息管理系副教授.主要研究方向:计算机网络技术