雷丽芳　李丽芬

摘 要：目前，南京市地铁基于AFC系统的数据统计主要是通过中央报表完成的，而报表的开发主要是以BO软件为开发工具。在使用过程中我们发现，报表的使用效率越来越低，报表的刷新速度随着数据量的增加越来越缓慢。通过多方面分析得出，报表使用效率低的根本原因是数据库聚集和数据库性能问题引起的。分析了报表系统和数据库聚集调优等方面存在的问题，并提出了解决措施。

关键词：BO报表；数据库；索引；SQL语句

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

报表有助于从现有数据中汇总出所需要的信息。南京市地铁AFC中央系统报表以BO软件为开发系统，主要包括维修类、收益类、客流类、库存类、操作类五类报表。提高南京市地铁AFC中央系统报表的性能，首先要从报表的数据源着手，提高数据库的性能和聚集效率。

1 BO简介

BO，全称Business Objects，主要功能是对数据仓库中的数据进行前台展示。BO是一个多层、瘦客户决策支持系统（决策支持系统），为非技术最终用户提供了对存储在数据库、数据仓库、数据市场和商业应用软件包中的数据进行随机查询和分析的功能。

BO报表的优点在于：①用户可以在一定程度上定制、修改报表；②报表与统计图之间可以互相任意转换；③报表可以对疑问数据进行一定层次内的追踪分析；④报表可以存为多种格式，可以被用户在Word文档中引用；⑤用户可以使用日常术语来访问数据，从而取代开发商自行进行一些日常维护；⑥报表可以对数据方便地自动进行各类汇总计算和分组排序。

2 BO报表存在的问题

2.1 聚集量过大

线路报表查询条件复杂、聚集量大，数据库每日、每刻和每分钟都有聚集运作，所产生的聚集数据一直堆积在数据库中，数据库中往往保存了3个月以上的聚集数据。初期数据删除程序未正常使用，数据一直未得到清理，使Repquarter、Repdaily等数据库表内容繁杂、查找困难，一些以这类数据库表为源表的报表也查询缓慢，最终导致报表刷新超时，无法显示。为预防此类现象出现，首先要检查数据库中聚集报表数据保存的天数，删减陈旧记录，例如日报表保存1个月的数据，月报表保存3个月的数据；然后检查数据删除程序是否正常运作，保证数据库中只保留一定期限的数据。

从目前中央系统数据库设计来看，未建立聚集索引是导致数据库聚集效率低的一个重要因素。聚集索引能够高效确定表中数据的物理顺序，其类似于电话簿按姓氏排列数据。由于聚集索引规定了数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引，但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字组织排列一样。

聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行与其物理相邻。例如，如果应用程序执行的一个查询任务经常检索某一日期范围内的记录，则使用聚集索引便可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的效率。同样，如果对表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都要进行排序，从而节省时间。

当索引值唯一时，使用聚集索引查找特定的行效率也很高。例如，使用唯一交易ID列TransactionID查找特定交易最快速的方法，是在TransactionID列上创建聚集索引或PRIMARY KEY约束。

2.2 不合理的索引设计

索引（index）是各种关系数据库系统中最常见的一种逻辑单元，是数据库系统重要的组成部分，对提高数据检索速度有着至关重要的作用。索引的原理是根据索引值得到行指针，然后快速定位到数据库记录。目前来看，AFC中央系统数据的使用效率和资源开销都需要得到进一步的优化，以提高数据查询速度，为交易、日志、事件、基础数据表建立合理的索引。数据库表更新大量数据后，不合适的索引严重影响了查询速度，大大降低了数据库的使用效率。

建立“适当”的索引是实现查询优化的首要前提。索引是除表之外另一重要的、用户定义的存储在物理介质上的数据结构。当根据索引码的值搜索数据时，索引提供了对数据的快速访问。事实上，没有索引，数据库也能根据SELECT语句成功检索到结果，但随着表变得越来越大，使用“适当”的索引能明显提高查询效率。

2.3 不合理的数据分区设计

对于一些超大型的表，分区是非常有用的。分区是一种逻辑概念，一个表就是一个整体，当发生数据访问的时候，也是对整个表或整个表的索引进行访问。所谓“分区”，通俗点讲，就是把表按一定的规律划分成更小的逻辑单位，当发生访问的时候，不以表为单位进行访问，而是先在表的基础上判断数据在哪个分区，然后对特定的分区进行访问。正确的分区有利于提高查询效率。

2.4 SQL语句条件过多，查询复杂

目前，南京市地铁AFC系统报表效率过低的另一重要原因就是聚集中使用的SQL语句只关注所得结果是否正确，而忽略了不同的实现方法之间可能存在的性能差异。一个SQL语句大约要经过三个阶段，即编译优化、执行和取值。大部分情况下，第一阶段要花掉60%的时间，所以绑定变量是很重要的。SQLserver有缓存区，用以存放最近使用的SQL语句，当有一条SQL语句到达数据库服务器时，数据库会首先搜索缓存区，看它是否存在可以重用的SQL语句。如果存在，则无需编译优化，因为缓存区的SQL语句都是编译优化好的，可以直接执行，节省了相当多的时间；如果没有发现该语句，则必须要经历语句编译分析、优化计划和安全检查等过程。这不仅耗费了大量的CPU功率，而且还在相当长的一段时间内锁住了一部分数据库缓存。这样执行SQL语句的人越多，等待的时间越长，系统的性能就会大幅降低。

3 解决措施

表的聚集量过大、不合适的表索引设计、不合适的数据库分区、效率低下的SQL语句都会影响到报表的使用。下面我们将针对目前南京市地铁中央系统数据库现行的报表系统处理过慢、效率过低这一问题提出解决措施。

3.1 减少聚集量

目前，每日数据库计划任务中的聚集信息包括各类设备的交易、事件、审计和日志等大量信息，而我们报表中用到的只有交易和审计，所以我们可以考虑在每日的聚集工作中只汇总交易和审计类信息，这样可以大大降低聚集工作的工作量。此外，由于聚集信息量过大，所需时间过长，这样在某段时间内就会与其他计划任务冲突，从而使聚集任务失败，而当再次执行聚集任务时，就会产生聚集信息重复的问题。对此，可以在聚集中添加事务回滚机制，从而提高报表的准确性。

3.2 建立合理的索引

索引表的优势主要体现在数据查询上，而且这个优势非常明显。索引表能够获得比标准表更快的查询速度，即使这张标准表已经建立了合适的索引。这与索引表的存储结构是分不开的。因为索引表的数据在存储的时候，所有的行记录都与排序过的主键列一起存储在数据库系统中，所以在查询的时候，只需找到主键，就可以查询到整条记录信息。索引表的使用减少了数据查询过程中的中间环节，避免了额外的数据块读取操作。目前，中央系统数据库AFCCOOKED各类交易信息表中已建立了一定的索引，例如，ObjTransaction是AFC系统中记录交易信息的表，观察其在不同索引下的查询运行状态，并测试其在C/S环境下的运行效果。以下为引用的内容：

select count（*） from ObjTransaction where

TransactionDateTime>20100630and DeviceID= ‘50465025

不建任何索引所需查询时间为120 s左右，在ObjTransaction上建立非聚簇索引查所需询时间为157 s。从查询效果分析，索引的有无、建立方式的不同将会导致不同的查询效果，选择什么样的索引基于用户对数据的查询条件，这些条件体现在where从句和join表达式中。所以，应在ObjTransaction表的主键列上建立聚簇索引，这样可以大大提高数据库的使用效率。

3.3 建立合理的数据分区

目前，南京市地铁AFC系统的交易表是一个非常大的表，存储了各种票卡的交易信息。现在查询总是按照交易日期和交易类型来执行，每天有大量的交易记录，通常我们也只是查询这个数据集中的一个相当小的数据，例如交易金额、进出站代码、交易日期等。检索某一交易信息时，这个索引可能指向无数个记录，而这种执行索引范围是非常大的。为了完成查询任务，系统需要执行全表扫描，必须扫描几万条记录，其中绝大部分不是我们所需的信息。使用智能分区方案，就可以将交易表常用信息与非常用信息进行分区存储。这样根据需要到某个特定区域内查找数据，能够大大提高查询效率。

3.4 合理的查询语句

要提高报表的效率，就要使用报表产生的SQL语句对查询进行优化，尽量避免全表扫描。首先应考虑在where和order by涉及的列上建立索引，以免在where子句中对字段进行null值判断，导致引擎放弃使用索引而进行全表扫描。

4 总结

本文通过解决聚集量过大的问题，设计合理的表索引、数据库分区，提高SQL语句的使用效率，大大提高了报表的使用效率。

〔编辑：王霞〕