基于某通信系统的数据库性能分析及优化

2017-03-25赵金凤

科技与创新 2017年4期

赵金凤

文章编号：2095-6835（2017）04-0107-03

摘要：SQL SERVER作为一种企业级的数据库，其性能会影响软件的增删查改效率。而索引在数据库表中非常关键，尤其是一张表只能有一个聚集索引，应该合理利用。同时，要善于利用数据库中的各种工具进行有针对性的分析和性能调优。实践表明，SQL SERVER的性能优化是可行的，也是多方面的。

关键词：SQL SERVER；聚集索引；通信系统；服务器

中图分类号：TP311.13 文献标识码：A DOI：10.15913/j.cnki.kjycx.2017.04.107

1 软件的背景

本文主要是基于某通信系统项目中系统状态监视软件进行分析的。该软件为C/S架构，由客户端软件、服务器端软件和数据库软件组成。

软件的工作流程是：各分系统软件与服务器端软件建立TCP连接，并登录。在运行过程中，需每秒上报一次分系统的状态参数。每个分系统上报的状态参数中包含各分系统中主要设备的工作参数和故障告警信息，服务器端软件需接收该条状态信息，发送至客户端软件显示，并将与上一条数据相比发生变化的整条状态数据写入数据库中。

在实验室测试中，使用3个分系统模拟软件分别模拟3个分系统，向服务器发送分系统状态。当数据量累积到一年的数据量（约600万行）时，在客户端查询一个月或更大范围内的状态数据时，查询数据库的响应时间明显延长，在分页显示中点击下一页，等待的时间过长。针对这个情况，本文通过分析和测试该数据库性能，提出了优化方案，并为以后的数据库设计提出了合理建议。

2 系统及软件运行环境

服务器软件和数据库软件的运行平台分系统状态监视软件客户端安装于1台PC机，3个分系统软件（分系统1、分系统2、分系统3）分别部署于3台PC机。服务器软件分别部署于2台服务器，同时，有一台主服务器工作，另一台为备机。

2.1 性能原因分析

SQL SERVER调整优化主要涉及软件和硬件两部分。在工作过程中，就是要调整计算机系统硬件各主要组成部分，因为它们是造成瓶颈的主要因素。而在SQL SERVER系统中，比较常见的问题出现在CPU、内存和磁盘IO等部件中。但在数据库设计中，为数据表建立索引是得到所需数据的有效方法。对于每一个查询优化器，要确定是否有相关的索引可以用于访问数据。一个利用索引的访问与全表扫描相比，可以大大缩短查询时间。因此，本文主要从CPU、内存、磁盘IO和为数据表建立不同索引方面进行相关测试分析。

2.2 使用性能监视器监视系统性能

2.2.1 性能监视器的使用

性能监视器是Windows 2003的一个工具，可以同时监视Windows 2003和SQL SERVER 2000的性能表现，是一个很好的性能分析工具。虽然性能监视器提供了充足的计数器，但因为SQL SERVER 2000的性能极大地依赖于Windows 2003的性能情况，所以，在大多数情况下，也只需要监视关键的几个计数器，比如System：processor queue length，Processor：%processor time，Processor：%privileged time PhysicalDisk：Avg.Disk Queue Length，PhysicalDisk：%Disk Time，Memory：Pages/Sec，SQL SERVER緩冲管理器，缓存命中率等。只有在特殊情况下，才需要选择其他计数器来进一步监视。

2.2.2 监视数据的分析

使用性能监视器监视主服务器24 h的性能数据，记录了服务器在连续运行8 h的过程中不同计数器显示的数值，而且在软件运行过程中，测试人员不分时段的进行查询测试。由此可以推断，该主用服务器硬件配置已经满足了该服务器软件和数据库软件的运行。

2.3 SQL SERVER数据库配置

SQL SERVER 2000数据库提供了可视化配置界面。在数据配置过程中，可配置数据库可以使用的CPU数量、可使用的内存大小、限制用户连接数、用户身份验证方式、用户查询超时时间的设置等。当然，除了窗口界面的配置外，SQL SERVER也提供了数据库控制台命令（DBCC）来显示、配置不同的数据库参数，优化数据库性能。

设置SQL SERVER CPU使用不同数量的CPU和设置不同的内存使用量对比查询时间。在企业管理器中，右击“（LOCAL）（WINDOWS NT）”属性，点击处理器页，指定当前SQL SERVER要使用的处理器，最大工作线程选择32（最小值）。点击内存，设置允许SQL SERVER使用的内存量。在工作过程中，可以使用相应的查询语句查询数据量（5 437 149行），即SELECT * FROM T_DSBSTATUS WHERE dsb_timestamp BETWEEN‘2015-4-10AND‘2015-5-20ORDER BY dsb_no.

由于备服务器只有2 G内存，当加载进2张数据表，查询第3张数据表，且只查询1条返回数据时，时间超过了20 s。这个现象反映出备服务器内存不足的问题，导致数据表从磁盘加载数据进内存时，磁盘IO严重影响查询时间。而在主服务器进行相同查询时，内存使用量已经超过了2 G，且查询分析器显示执行时间为0 ms。因此，应判断为该数据库当前的数据量需要至少大于2 G的内存才能完成查询工作。

2.4 数据表建立索引

2.4.1 聚集索引

聚集索引（聚簇索引）是一种指明表数据物理存储顺序的索引。表数据按照指定作为聚集索引的一个或多个键列排序并存储。因为数据按照一个指定的顺序物理地存储，所以，一个表只能创建一个聚集索引。

2.4.2 非聚集索引

非聚集索引（非聚簇索引）的叶子节点含有索引行，用于存储确定行的位置信息。位置信息取决于表上是否存在聚集索引。如果该表有聚集索引，对于每行，该位置信息就是聚集索引的键值，可以作为该行的定位器。如果聚集索引不是一个唯一索引，SQL SERVER就自动地为所有相同的索引键值分配一个内部值以非聚集索引方式使用。如果表上没有聚集索引，则位置信息是一个行ID，而不是聚集索引的一个键值。行ID是一个指针，由文件ID、页号和行ID组成。这个指针能够精确地指示在哪里可以找到行。SQL SERVER允许一个表上最多可以有249个非聚集索引。

2.5 索引查询测试

查询主要基于T_DSBSTATUS表。由于查询一般基于时间范围且按照序号排序，所以，索引分别在dsb_timestamp和dsb_no列建立进行查询。查询之前，使用SET STATISTICSSQL TIME ON命令打开查询时间显示。每次查询之前，要先停止SQL SERVER服务器，使用Windows查看任务管理器显示当前内存使用量，发现内存使用量明显下降后，再重启SQL SERVER服务器进行查询，这样可避免缓存中已存入相同查询数据，影响查询结果。

索引测试共进行了3次查询，主要包括以下内容。

2.5.1 相同数据不同索引的查询

在T_DSBSTATUS表中，分别在dsb_no列和dsb_timestamp列单独建立聚集索引和非聚集索引，使用相同的查询语句查询（数据量39 628行），且在查询语句中分别使用dsb_no排序和不使用dsb_no排序比对查询时间。

在dsb_timestamp列上建立索引时，可大幅提高查询效率。建立聚集索引时，查询效率更高，而在dsb_no列上建立聚集索引时，会影响查询效率。由此可见，在查询范围的列上建立索引，可以有效提高查询效率，聚集索引的查询效率提高更明显。

2.5.2 不同数据的不同索引查询

在T_DSBSTATUS表中，在dsb_timestamp列分别建立聚集索引和非聚集索引。基于dsb_no列和dsb_timestamp列建立非聚集索引时，可使用查询语句进行查询。

在查询语句中，对dsb_no排序会延长查询时间。不论如何增加索引，使用排序的语句总是花费时间最长的查询。

插入/删除/更新的不同索引测试在dsb_timestamp列上分别建立聚集索引和非聚集索引进行比较，使用表语句进行操作：执行插入操作，有索引时反而可能会降低速度。这是因为增加了索引操作，而更新和删除操作首先是要按条件检索数据，然后进行更新操作。对于一个海量数据表来说，检索是主要的消耗，因此，这种情况下的更新和删除操作在有索引时性能有很大的提升。

2.6 临时数据库的有效使用

临时数据库Tempdb主要用于排序、建立工作表、格式化等内部处理和存储用户建立的临时表、索引，它是可被多用户共享的数据库。

为了验证查询语句是否会使用tempdb数据库，做了以下测试，即，设置限制，tempdb数据文件限制为9 M，日志文件限制为2 M。然后查询相应的语句，即SELECT * FROM T_DSBSTATUS WHERE dsb_timestamp between‘2015-5-10 AND ‘2015-6-20ORDER BY dsb_no desc.查询结果提示，“未能为数据库‘TEMPDB中的对象‘（SYSTEM table id：-664682958）分配空间，因为文件组‘DEFAULT已满”。

由此可以看出，在查询语句的查询过程中使用了临时数据库，并且默认空间已经不能满足需要。取消tempdb数据库文件的增长限制后，可正常查询，并且tempdb数据库文件会增大。

至于查询语句何时会使用临时数据库，SQL SERVER指导手册中给出了解释：数据库关系引擎有时也需要生成一个临时工作表执行SQL语句中指定的逻辑操作。一般为某些GROUP BY、ORDER BY或UNION查询生成工作表。例如，如果ORDER BY子句引用的列不为任何索引所覆盖，则关系引擎可能需要生成一个工作表，将结果集按所请求的顺序排列。而临时工作表在TEMPDB中生成，并在语句执行完成后被自动除去。

因此，临时数据库的设置也是会影响SQL SERVER性能的因素之一。为了验证临时数据库对查询性能的影响，做了以下2个测试。

设置tempdb数据库文件，按百分比自动增长。在企业管理器中查看tempdb的tempdev文件初始分配空間为8 M，设置文件按10%自动增长。查看templog文件的初始分配空间为2 M，设置文件按10%自动增长。清理SQL SERVER缓存，进行如下查询，即：

SET STATISTICS TIME ON

SELECT * FROM T_DSBSTATUS WHERE dsb_timestamp between ‘2015-3-10 8：00：00 AND ‘2015-6-10 17：00：00ORDER BY dsb_no asc

数据文件tempdev增长至718 M，日志文件templog增长至32 M。查询到6 560 065行数据，耗时148 710 ms。

初始设置时，给tempdb数据库文件比较大的空间。重启SQL SERVER服务器，清理tempdb文件空间，设置tempdev文件为800 M，设置templog分配的空间为50 M，清理缓存，执行之前提到的查询步骤。此操作共查询到6 560 065行数据，耗时119 187 ms，比第一个查询快了近30 s。

由此可见，在默认情况下，当SQL SERVER运行时，tempdb数据库会根据需要自动增长。与其他数据库不同的是，每次启动数据库引擎时，它会重置为其初始大小。如果为tempdb数据库定义的大小较小，则每次重新启动SQL SERVER 时，将tempdb数据库的大小自动增加到支持工作负荷所需的大小，那么，这一工作可能会成为系统处理负荷的一部分。为了避免这种开销，可以在企业管理器中配置或者使用ALTER DATABASE命令增加 tempdb 数据库的大小。

3 结论

由于客户端主要基于时间段查询，对于应用者来说，更新和删除的操作比较少，索引又会增加插入的时间，所以，建议数据库在时间列上建立非聚集索引。这样做，能适当提高查询效率，不会严重影响插入操作的效率。

实验室数据库数据表累积了一年的数据量，在查询时，会占用大量内存，所以，建议将服务器内存配置为4 G左右，避免内存不够成为限制数据库效率的瓶颈，并将常查询的数据表设置为驻留内存，避免从磁盘读取数据影响查询速度。

通过对临时数据表的测试，建议在实际应用过程中一次性为临时数据库配置800 M空间容量，避免在临时数据库自增长过程中影响数据库的查询性能。

4 意见建议

4.1 建立合理的索引

从索引的结构和原理中可以看出，虽然SQL SERVER可以通过索引访问避免对表的扫描，减少大量的I/O操作，提高系统的性能，但同时也会增加系统的负担，主要包括以下内容：①创建和维护索引要耗费系统时间；②存储索引需要额外的物理空间；③增加、删除和修改表中数据时，要动态地维护索引，这就减慢了数据的维护速度。

4.2 维护数据库注意事项

数据库经过长期运行，临时数据库文件增加至一定程度后，有可能会占满磁盘空间。当需再次查询时，临时数据库没有更多的空间增长，数据库便会报错。因此，为了避免出现这种问题，在不停止数据库服务器运行的情况下，数据库维护人员可以使用DBCC SHRINKDATABASE语句收缩临时数据库和数据库日志文件。

对于需要长期操作的表，可使用控制台命令DBCC PINTABLE将表驻留内存。这样可以避免从磁盘和高速缓存中不断切换该表。但是，这个操作会占用内存，且不会响应其他程序从内存中换出，所以，应在确保内存使用量充足的情况下进行该操作。

5 结束语

本文列出了一些SQL SERVER数据库优化方法，实际上，因为影响查询性能的因素很多，所以，不可能找到一个通用的优化方法。在开发和设计过程中，针对数据库的运行情况，有针对性地调整，以最小的开支和良好的执行性能访问数据库为目标，根据具体的开发环境和应用需要，综合考虑各方面因素，正确评价各种方法的优点和缺点，从中选择最佳方案。

参考文献

[1]王利.SQL SERVER数据库性能调整与优化[D].成都：电子科技大学，2007.

[2]周序，管丽娜，白海波，等.SQL Server 2000中文版入门与提高[M].北京：清华大学出版社，2001.

[3]何益斌，高景昌，杨亚红，等.Microsoft SQL Server的索引结构及其优化[J].吉林大学学报（信息科学版），2001，19（3）.

[4]Ryan K.S tephens，Ronald R.Pl ew.数据库设计[M].何玉洁，译.北京：机械工业出版社，2001.

[5]Robert Vi eira.SQL Server 2005高级程序设计[M].董明，译.北京：人民邮电出版社，2008.

[6]Microsoft.SQL SERVER 2000企业版的安装、配置和管理[M].北京：高等教育出版社，2003.

[7]许平格.数据库管理系统中查询优化的设计和实现[D].杭州：浙江大学，2005.

[8]杜军平，黄杰.SQL Server 2000数据库开发[M].北京：机械工业出版社，2001.

[9]庄成三，洪玫，杨秋辉.数据库系统原理及其应用[M].北京：電子工业出版社，2000.

[10]Jeffrey D.Ullman，Jennifer Widom.数据库系统基础教程[M].北京：清华大学出版社，1999.

[11]杨正洪，郑齐健，郑齐心.SQL SERVER 7 关系数据库系统管理与开发指南[M].北京：机械工业出版社，2000.