学生成绩管理系统数据查询优化方法研究

      0引言 
　　互联网时代，信息技术给人们的工作和生活带来了极大的便利。一个功能完整的管理信息系统通常由两部分组成，即前台应用程序和后台数据库。前台应用程序通过互联网与后台数据库进行数据交互，包括数据的增加、删除、修改、查询，这就是数据库的4个基本操作，简称CRUD操作。在数据库的4个基本操作中，查询是使用频率最高的操作，因此查询效率的高低直接关系到应用程序性能的好坏。大数据时代数据量增长很快，如果不注重数据查询效率的优化，随着数据量的逐渐增加，数据查询效率将逐渐降低，最终将严重影响管理信息系统的用户体验。因此，必须将数据查询优化作为管理信息系统开发与维护的重要内容，贯穿系统生命周期始终。 
　　1研究对象与方法 
　　学生成绩管理系统是一个典型的基于数据库的管理信息系统，在教育管理中应用非常广泛。根据数据库设计原则，按照第三范式的要求设计数据表结构。学生成绩管理系统数据库的表结构由成绩表、学生表和课程表组成。成绩表中包含学期、学号、课程号、成绩等字段，学生表中包含学号、姓名、学籍表等字段，课程表中包含课程号、课程名称、学时、学分等字段。 
　　将学生成绩管理系统数据库部署在Windows Server 2008操作系统和 SQL Server 2008数据库管理系统上。数据查询一般通过SQL查询语句来实现各种查询逻辑。在外部环境保持稳定的状态下，数据查询效率越高，SQL语句的执行时间越短。因此，可以通过计算SQL语句的执行时间来进行数据查询效率比较分析。 
　　2数据查询效率影响因素 
　　由于数据查询是一个前台应用程序与后台数据库的交互过程，涉及很多环节，因此影响数据查询效率的因素有很多。归纳起来，影响数据查询效率的因素主要来自物理层、数据库层、应用层[1] 3个层面。 
　　在物理层，影响数据查询效率的主要因素包括服务器CPU性能、内存、硬盘、网络、操作系统等。在数据库层，影响数据查询效率的主要因素包括索引、视图、数据存储、数据冗余等。在应用层，影响数据查询效率的主要因素是SQL语句的写法和应用程序设计。 
　　3数据查询优化方法 
　　3.1物理层优化方法 
　　数据库部署在服务器上，服务器性能的好坏直接影响查询效率。针对物理层影响数据查询效率的主要因素，可以采取以下优化方法： 
　　（1）提高CPU性能。CPU是计算机负责执行指令和处理数据的核心部件。服务器性能的高低很大程度上由CPU的性能决定。数据库的查询操作特别依赖CPU的并行处理能力。因此，应该为数据库服务器配置高性能的CPU。 
　　（2）增加内存。数据查询分为物理读和逻辑读，物理读是从硬盘读取数据到内存缓冲区，逻辑读是直接从内存缓冲区中读取数据。内存的读写效率远远高于磁盘的读写效率，而且物理读还会增加磁盘I/O操作。因此，为了保证数据查询操作都能够在内存中完成，应该尽量为数据库服务器配置足够多的物理内存，同时要配置相应的虚拟内存。 
　　（3）配置多块硬盘。数据查询操作需要大量的I/O操作，将I/O操作尽可能平均分配在多块硬盘上才能有效提升硬盘的并行读写性能。因此，应该为数据库服务器配置多块硬盘，避免使用单块超大容量硬盘。 
　　（4）提高网络带宽和网络稳定性。应用程序与数据库之间通过互联网进行数据交互，因此应该提高数据库服务器的网络带宽和网络稳定性。 
　　（5）提高操作系统性能。数据库管理系统安装在操作系统上，应该加强操作系统的管理与维护，提高操作系统性能。 
　　3.2数据库层优化方法 
　　数据库管理系统负责SQL查询指令的执行，因此数据库层的优化是数据查询优化的核心，其对数据查询效率影响最为显著，可以采取以下优化方法： 
　　（1）索引优化。索引是对数据库表中一个或多个列的值预先进行结构排序。索引可以避免全表扫描，因而可显著加快数据库的查询速度[2]。索引分为聚集索引和非聚集索引。与非聚集索引相比，聚集索引通常能够提供更快的数据访问速度。一般应考虑将频繁查询、连接、排序或分组的列设为索引列，其中最频繁操作的列设为聚集索引列，避免在数据量较小的表上建立索引，同时避免在频繁进行插入、删除和修改操作的列上建立索引[3]。如成绩表一般按学号查询，因此将学号列设置为聚集索引列。同时根据需要将学期、课程号、成绩等列设置为非聚集索引列。 
　　（2）视图优化。视图是由一个或者多个表组成的虚拟表。通过连接查询（JOIN）和联合查询（UNION）建立视图，可以实现数据库中数据的合并与分割，极大方便了数据查询。如成绩管理系统中成绩表分别与学生表和课程表进行连接查询，建立一个视图，将学号、姓名、课程号、课程名称、学时、学分、成绩等信息放入一张虚拟表中，应用程序查询时只需要查询该视图即可获得所需数据。视图查询同时涉及多个物理表操作，当数据量较大时，容易产生查询效率低下的问题。为了提高查询效率，视图的定义深度一般不应超过三层。若三层视图不够用，则应在视图上定义临时表，在临时表上再定义视图。这样反复交迭定义，视图的深度就可以不受限制。既保留了视图的便利性，又兼顾了查询效率问题。

     （3）数据存储优化。数据库通常包括数据文件和日志文件。数据文件和日志文件默认存储在相同的位置。由于数据文件和日志文件的操作会产生大量的I/O，因此应将日志文件与数据文件分别存储在不同的硬盘上以分散I/O。通常情况下，数据库默认只有一个主数据文件，不生成次数据文件。为了提高查询效率，必要时可以通过定义文件组把数据库中的一些表分开存储在不同的数据文件里，即增加次数据文件，同时把不同的数据文件分散存储在不同的硬盘上[4]。此外，数据文件长期自动增长可能产生碎片，导致物理空间与数据的逻辑空间不再连续。因此，有必要定期整理数据库碎片，以提高数据库查询效率。 
　　（4）适当增加数据冗余。按照数据库的设计原则，数据表应该避免数据冗余。但是，为了提高数据的查询效率，有时需要降低范式标准，适当增加数据冗余，达到以空间换时间的目的。数据冗余包括字段冗余和表冗余。字段冗余是通过增加冗余字段，减少数据计算和连接查询。如学生表中的性别和出生日期，虽然可以从身份证号中获取，但是为了提高查询效率，应增加性别和出生日期字段。表冗余是通过增加冗余表提高查询效率。以成绩表为例，运行多年的成绩管理系统中保存了历届学生的成绩信息，包括已经毕业的学生成绩信息，多年累积下来，数据量不断增加，导致查询效率降低。此时，可以考虑建立一个数据冗余表，其表结构与成绩表的视图一致，但是只保存在校学生的成绩信息，数据量会大大减少。对在校生的成绩查询只需要查询冗余表，查询效率大大提升。成绩数据冗余表要能自动更新，以便与成绩表数据保持同步和一致。可利用SQL Server 2008的代理服务功能，建立一个每天凌晨定时自动执行的作业，作业分为两步： 
　　step1：清空老数据，相关SQL语句为： 
　　Truncate table cj_query 
　　step2：插入新数据，更新冗余表，相关SQL语句为： 
　　INSERT INTO cj_query （xq，xh，xm，kch，kcmc，xs，xf，cj） 
　　SELECT xq，xh，xm，kch，kcmc，xs，xf，cj 
　　FROM cj_v /* cj_v为成绩表、学生表、课程表作连接查询建立的视图*/ 
　　WHERE xjm = '01' /*在校学生的学籍码为01*/ 
　　3.3应用层优化方法 
　　应用层涉及SQL语句的编写和应用程序的设计，其是否合理很大程度上会对数据查询效率产生影响。针对应用层影响数据查询效率的主要因素，可以采取以下优化方法： 
　　（1）SQL语句写法优化。SQL语句优化要注意的地方很多，总的原则是限制返回结果集，尽量避免全表扫描。返回结果集越大，逻辑读数就越大，而且如果超出内存缓冲区的容量，还需要增加物理读数，从而增加磁盘I/O操作。因此应该限制返回结果集的大小，包括行数和字段列数。全表扫描是指搜索表中的每一条记录，直到所有符合给定条件的记录返回为止，效率非常低下，因此应该尽量避免全表扫描。根据优化总原则， SQL语句优化方法总结如下：①避免使用 select * from table，应该用具体的字段代替“*”，不要返回任何用不到的字段；②尽量避免在where子句中使用！=、<>、not、in、or等运算符，因为这些操作可能会引起全表扫描；③尽量避免在 where 子句中对字段进行函数运算和表达式运算，这将导致数据库放弃使用索引而进行全表扫描；④尽量避免使用子查询，如不能避免时，应尽量减少子查询的嵌套层次，并在子查询中过滤掉尽可能多的行；⑤尽量避免使用外连接，因为外连接必须对左表或右表查询所有行，应尽量使用内连接；⑥合理使用临时表和表变量，当需要重复使用数据量较大的表中某个数据集时，应当考虑使用临时表或表变量，这样可以大大提高查询效率[5]。表变量存储在内存中，临时表存储在系统数据库tempdb中。对于较小的数据集考虑使用表变量，对于大数据集，由于内存无法容纳，使用表变量效率反而不高，应该使用临时表。同时，应避免频繁创建和删除临时表，以减少系统表资源的消耗。 
　　（2）应用程序设计优化。应用程序设计有时也会影响数据查询效率。在可能的情况下，应用程序应尽量采用分页设计，这样可以分批多次获取数据集，提高单次查询响应速度。同时，应用程序设计时还需考虑并发性，防止出现数据库锁死和查询阻塞现象。此外，对于Web应用程序，还应考虑使用数据缓存和局部刷新技术，减少数据查询次数和查询数据量。 
　　4结语 
　　大数据时代，数据库中的数据量持续增加。为了保证信息管理系统始终具有快速的响应速度和良好的用户体验，必须深入研究数据查询优化技术。本文以学生成绩管理系统为例，从物理层、数据库层和应用层3个方面分析了影响数据查询效率的主要因素，给出了相应的数据查询优化方法。实际应用中，应从多个方面综合采取合适的优化策略，才能有效提高数据查询效率。 
　　参考文献参考文献： 
　　[1]刘辉兰， 陈卫东.数据查询优化技术的研究和探讨[J].中国数字医学， 2015 （7）：7274. 
　　[2]林勤花.关系数据库查询优化技术研究[J].电脑编程技巧与维护， 2014， 10（9）：3031. 
　　[3]樊新华.关系数据库的查询优化技术[J].计算机与数字工程， 2009， 37（12）：188192. 
　　[4]冯卫兵.关系数据库的查询优化[J].现代计算机， 2010 （1）：3033. 
　　[5]程学先，黄爱武.关系数据库的查询优化技术[J].软件导刊， 2007 （1）：7273. 
　　责任编辑（责任编辑：杜能钢）