应用系统中数据库性能优化研究

忽佳文，张梅梅，王　影

（咸阳师范学院 计算机学院，陕西　咸阳　712000）



应用系统中数据库性能优化研究

忽佳文，张梅梅，王影

（咸阳师范学院 计算机学院，陕西咸阳712000）

摘要：人们知道的重要SQLSERVER数据库对象有索引、视图、触发器和存储过程。为了起到提高系统安全性、提高执行效率、方便代码管理等效果，就需要在教务管理系统开发中有效应用这些数据库对象技术。文章首先分析了这些数据库对象的特点和优化策略，其次介绍了视图、触发器和存储过程在教务管理系统中的应用，并结合源代码就存储过程和触发器技术这两种主要技术在ASP.NET 环境中开发教务管理系统这一应用提出自己的设想。

关键词：存储过程；触发器；性能优化；教务系统

凡涉及数据库技术的计算机应用系统都可以称之为数据库应用系统。我们知道数据库应用系统的应用非常广泛，无论是联机事务处理或是联机分析处理、又或是小型单机事务处理和大型信息系统、再或者传统的业务数据处理及计算机辅助设计/制造、图像处理、地理信息系统、办公信息系统、Web信息管理、数据挖掘，以及电子商务、数字化出版等等，越来越多的应用领域采用数据库技术来存储和处理信息资源。

当前各种大型的数据库应用系统中，数据量越来越大，数据处理越来越复杂，对数据库性能的要求也越来越高。可是，要知道我们拥有的软硬件资源是有限的，这就催生出一个重要的研究课题，即是如何充分利用有限的软硬件资源，来达到获取最大的系统吞吐量及数据的强大处理能力的效果。我们知道衡量数据库系统性能良好的重要指标之一就是响应时间和吞吐量，响应越快，吞吐量越大，系统性能自然就越好。本文主要对应用系统中数据库对象（视图、存储过程、触发器）的优化进行探讨，并以教务管理系统为例进行说明。

1　数据库对象优化策略

1.1视图的应用

根据数据库研究领域给出的视图的最新定义：视图是从一个或几个基本表（或视图）导出的表，与基本表不同之处在于它是一个虚表。它只是作为用户查看数据库表中数据的一种方式。视图的定义被存放于数据库中，而视图对应的数据则不存放于数据库中，这些数据仍存放在原来的基本表中。一个视图可以派生出其它的视图，可以表示来自于不同的源的数据。为一个应用程序的数据库代码部分定义的视图，实质上相当于很多表，我们可以对特定的视图进行插入数据、修改数据和删除数据的操作。在优化设计中合理地创建视图将变得尤为重要，其主要作用有：（1）提供一定的数据逻辑独立性。使用视图可避免直接调用基本表，当基本表结构改变时，我们只需要修改视图的定义而不用修改本子系统的应用程序，这极大地为系统的开发提供了便利。（2）消除复杂SQL编程。用户在实际的应用情况中，有很多像统计、做报表等这类需要复杂工作量的需求，这时，为了有效降低SQL编程的复杂度，就要在多个基本表和应用程序之间建立视图。同时，由于SQL语句每次执行时系统都要进行查询分析和查询检查，但倘若我们使用了视图，就会最大限度地提高效率，因为对视图来说只需检查一次，这无形中提高了系统的开发和运行效率。（3）安全保密作用。我们可以为用户定义带有权限的视图，也就是定义他们对部分数据有相应查看权限的虚表，用户访问基表是不安全的，这样做可以让他们以访问虚表的方式取代直接访问基表，从而实现了数据的安全控制。

1.2索引的优化

建立索引是加快查询速度的有效手段。为了减少甚至避免全表扫描，最大限度地减少磁盘I/O的次数以及切实有效加快表的查询，我们通常使用索引。但是，要注意的一点是不能对任何数据表都建立索引，这是因为索引在提高查、改、删语句的性能的同时但会大大降低插入insert语句的性能。因此，索引建立不当，反而会使数据库运行更慢。如果索引的数量过于庞大，还会增加维护上的开销，同时降低系统的性能。因此，对索引的合理使用是非常重要的。

SQL Server中的索引包括聚集索引和非聚集索引、唯一索引和非唯一索引、单列索引和复合索引。SQL Server执行查询时会自动选择使用哪个索引，查询优化器会对可用的数据检索方法进行成本评估，从中选择最有效的方法。

以下给出几个建立索引应遵循的原则：（1）主键属性建立聚集索引（一般系统会自动建立）。（2）存在范围查询和排序、分组这两项有大量重复值且经常进行的活动、对某属性列访问频繁的情况，此时考虑建立聚集索引。（3）如果有属性列出现在查询条件或表连接条件中，这时需要建立相应索引，如果某些属性列一起构成了一个集合出现在查询条件或表连接条件中，则就需要建立复合索引。（4）如果一个基表需要做频繁的插入操作，我们就需要建立索引，这时应使用fill factor（填充因子）来减少页分裂，这样可以有效同高并发度以及降低死锁的发生概率。（5）如果有索引关键字这类需要，那么就应尽可能采用小数据类型的属性列作为键，这样做是为了使尽可能多的索引键和指针包含在每个索引页中。采取了这种方法，便可使一个查询必须遍历的索引页面大大减少。（6）对于那些频繁更新的列来说，最好不要包含在索引列中。

1.3存储过程和触发器的应用

存储过程是一组实现特定功能的、由SQL语句和流程控制语句共同组成的一个子程序，经过数据库编译和优化后存储在数据库服务器中。用户或应用程序通过需要的存储过程的名字及相应入口参数用以进行调用，调用结束后即返回状态值用来向用户提示操作是否成功或失败，而且能够在远程数据库中运行。它的执行速度比独立执行同样的程序要快。倘若任何一个数据库应用程序都用到了存储过程这一数据库对象，我们可以说它的设计是非常良好的。下面就存储过程的优点主要做以下几方面小结：（1）存储过程可以实现较快的执行速度，提高系统效率。存储过程在首次执行时查询优化器对其进行分析、优化并进行编译，然后生成最终被存在系统表中的执行计划。倘若某客户端需要调用存储在服务器中的存储过程，那么他要做的是只需通过网络发送该存储过程名以及入口参数即可，剩下的就是交给数据库服务器了，由数据库服务器来执行该存储过程，无需再进行编译，真正做到了“一次编译多次执行”。待数据库服务器执行完指定调用的存储过程后，会将结果集数据返回给刚才调用存储过程的客户端应用程序，这就避免了因在网上传送大量的命令和中间结果数据而导致的低性能和网络负担。这样做不仅可以大大提高系统效率，还可以达到充分利用服务器的高性能来提高运算速度的目的。（2）存储过程允许标准组件式编程，提高系统可维护性。修改存储过程是独立的，与程序的其它部分无关。修改硬编码SQL语句一般与修改存储过程相比比较麻烦，并且存储过程和组件能够做到一次编写多次调用，因而我们通常选择修改的不是硬编码SQL语句而是存储过程。并且只要过程接口不发生变化，对应用程序源代码是没有影响的影响，这也就极大地提高了程序的可编程性和可移植性。（3）存储过程可作为一种安全机制来充分利用，增强系统的安全性。我们可以针对不同等级用户创建带有自定义特殊操作权限的存储过程，并将执行该过程的权限授予用户，此时用户只能通过我们所赋予特殊权限的存储过程执行它所规定的数据库操作，而不能进行其它操作（例如访问表）。例如，可以将执行存储过程（查询表）的权限授予一个用户，那么他只能执行查询操作且因未获得其他权限从而不能执行除查询以外的其他操作。（4）存储过程能够减少网络负荷。当客户端向数据库服务器发出执行存储过程的请求时，在网络上传送的只有执行存储过程的命令及入口参数，当这些命令及参数到达数据库服务器时，服务器就会运行用户指定的存储过程，运行结束后将执行结果和状态信息一并返回客户端。因为我们定义了存储过程，这就避免了硬编码SQL语句在网络上的传递，使得客户机与服务器的通讯量降至最小，从而最大程度上地降低了网络负担。

在SQL Server中存储过程分为五类：系统存储过程、本地存储过程（用户创建的存储过程）、临时存储过程、远程存储过程、扩展存储过程。本文提到的存储过程一般来说是指用户自行创建、且是本地的存储过程，用以完成某一特定功能的存储过程。在SQL Server存储过程中，支持输入（Input）、输出参数（Output），也支持返回值参数（Return Value）。返回值参数通过Return语句返回，而且必须返回INT型的数据，RETURN语句将终止SQL Server存储过程。

使用CREATE PROCEDURE创建存储过程的语法形式如下：

可以将本文提到的另一种数据库对象——触发器看成是一种特殊的存储过程，它的特殊之处在于当满足某个特定条件时，会自动触发执行，是一种自动触发的存储过程，较存储过程而言用户是不能直接调用它的。任何用户对表的增、删、改等操作均由服务器自动激活与之对应的触发器。这样，就可以解决高级形式的业务规则和复杂行为限制。

主键、外键和CHECK所不能保证的复杂的参照完整性和数据一致性若用到SQL Server数据库触发器就能够实现。SQL Server数据库触发器支持2种类型的触发器：（1）after触发器（之后触发）：对于此类触发器来说，一旦执行了某一诸如insert、update、delete的操作，触发器此时被立即触发，并且它只能被定义在表上。可分为insert插入触发器、update更新触发器、delete删除触发器。在先前我们规定有约束条件，如果操作违反了这类条件，结果是导致事务的回滚，触发器就不会被触发执行。（2）instead of 触发器 （之前触发）：该类触发器意味着并不执行其定义的操作（insert、update、delete）而仅执行触发器本身。既可以在表上定义instead of触发器，也可以在视图上定义。

用CREATE TRIGGER命令创建触发器，其定义语句为：

2　教务管理系统的设计与实现

下面以一个教务管理系统的设计开发来说明数据库对象的优化使用。

2.1数据表及关系

教务管理系统数据库命名为TeachingManage，主要包括以下数据表。数据表之间的关系图如图1所示。

院系表（数据库表名：tb_Department）：存放各院系相关数据。

专业表（数据库表名：tb_Major）：存放各院系专业相关数据。

教室表（数据库表名：tb_Classroom）：存放学校教室相关数据。

课程表（数据库表名：tb_Course）：存放学校开设课程相关数据。

学生表（数据库表名：tb_Student）：存放学生相关数据。

教师表（数据库表名：tb_Teacher）：存放教师相关数据。

开课表（数据库表名：tb_PlanCourse）：存放教师在某教室开设课程的相关数据。

选课表（数据库表名：tb_Grade）：存放学生选修某课程的成绩数据。

2.2创建视图、存储过程、触发器

为每个院系学生用CREATE VIEW创建每个院系学生自己的视图，将该视图的使用权限赋予给相关院系的管理人员，这样可以有效地保护数据的安全性，而且还可以降低SQL编程的复杂度，提高系统运行性能。

CREATE VIEW V_Compu_College --创建信息工程学院的学生视图

AS

SELECT StudentID， SName， SCome， SSex，SIdentityCardNumber， SPassword， STelephone，SNativePlac， MajorID， ClassName， TotalCredits

FROM tb_Student

WHERE ClassName = '信息工程学院'

WITH CHECK OPTION

所有对数据库的操作包括对数据库数据增删改查的操作，都用存储过程实现以提高系统效率，比如，下面存储过程pro_SelectNotificationByTitle实现了按标题进行查询，其中@nTitle是参数，可以实现对标题的模糊查询。系统在服务器端进行查询，将查询结果反馈到用户端界面：

图1　数据表之间的关系

触发器可以有效地实现复杂的业务规则和行为限制。教务管理系统中有一项重要功能就是学生选课，可以创建触发器统计选课人数并判断选课人数是否超过限选人数，如果选课人数没有超过限选人数，则该生可以选修该课程；否则该生不能选修该课程。

2.3创建应用程序类及页面文件

本系统在Visual Studio2010集成环境下实现。在开发过程中，我们采用ASP.NET分层开发思想，即将应用程序结构划分为三层独立的包，包括用户表示层、业务逻辑层、数据访问层。首先建立一个ASP.NET动态网站，命名为TeachingManageSystem（教务管理系统）。下面以一个查询为例说明存储过程的调用。

在网站中创建一个页面，以查询关键字operName为形参，在页面代码中调用a d m in .cs类中的方法SelectNotificationByTitle。

admin.cs类中包含了管理员对数据的所有操作，在该类中实现存储过程的构造，并调用SqlHelper.cs类文件中的方法ExecuteStoredProcedure执行存储过程。

public static DataTable SelectNotificationByTitl e（string nTitle）--业务逻辑层，构造存储过程

在SqlHelper.cs类文件中实现对数据库的连接、数据库命令的执行及数据结果的返回。SqlHelper.cs类文件中ExecuteStoredProcedure方法实现各个存储过程。

3　结语

本文在教务管理系统的开发过程中，为了达到提高系统的开发效率以及方便地维护程序这样的效果，以视图和存储过程等方式，将与数据库相关的增、删、改、查等的操作写到数据库组件中，而通过数据控件调用视图或存储过程来实现数据呈现。一旦维护人员有了想修改某些与数据库相关的功能模块的想法后，那么他便可以直接在后台数据库修改相应的视图和存储过程即可，无须改程序代码，无须重新编译，真正做到“一次编译到处运行”。如果在教务系统软件开发过程甚至于其他应用到数据库技术的系统开发过程中综合运用存储过程和视图，将会起到一系列诸如软件设计极大便利、软件高度安全可靠、软件运行效率高等的惊人效果。SQLSERVER数据库对象除了存储过程和视图以外，另有触发器和索引等，如果能在今后应用程序开发过程中，综合运用之前提到的数据库组件，也将会带来意想不到的“一石多鸟”的效果。

［参考文献］

［1］李俊.基于Asp.net的B2C电子商务系统设计与实现［D］.重庆：重庆大学，2012.

［2］吴伶琳，杨正校.SQL Server 2005数据库基础［M］.大连：大连理工大学出版社，2010.

［3］欧阳昉.网上商城购物系统研究与开发［D］.成都：电子科技大学，2007.

［4］王珊，萨师煊.数据库系统概论［M］.5版.北京：高等教育出版社，2015.

［5］孟宪虎.大型数据库管理系统技术应用与实例分析［M］.北京：电子工业出版社，2011.

［6］刘卫国，熊拥军.数据库技术与应用----SQL Server2005［M］.北京：清华大学出版社，2012.

［7］李春芬，吴英斌.存储过程在勘探设备管理系统中的应用［J］.物探装备，2011（5）：327-330.

［8］陈晓丹，王娟.存储过程在信息发布系统中的研究与应用［J］.武汉工程职业技术学院学报，2010（2）：36-38.

［9］吴伶琳.存储过程在网上商城系统开发中的应用［J］.计算机时代，2013（2）：31-33.

Research on Database Performance Optimization in Application System

Hu Jiawen, Zhang Meimei, Wang Ying
(School of Computer Science, Xianyang Normal University, Xianyang712000, China)

Abstract：As we all know， indexes， views， triggers and stored procedures are important database objects in SQLSERVER. In order to play important effects， such as to improve system security， improve the effciency， easy code management etc， the effective application of these database objects technology needs to be used in Educational Management System. Firstly， the paper analyzes the characteristics and optimization strategies of these database objects, secondly it introduces the applications of views, triggers and stored procedures in educational management system. Finally， put forward our own ideas for stored procedures and triggers technology in Educational Administration System in ASP.NET environment combined with the source code.

Key words：stored procedure; trigger; performance optimization; educational management system

基金项目：咸阳师范学院科研项目；项目编号：12XSYK069。陕西省大学生创新创业训练计划项目；项目编号：2015004。

作者简介：忽佳文（1995-），男，河南郑州，本科。