基于SQL和表设计的Oracle数据库开发审计研究

曾明霏+刘强

摘 要：企业级信息系统的数据库表设计和SQL语句优化，对提高信息系统性能和稳定性、可靠性有着重要意义。基于数据库表设计和SQL语句优化技术，提出了一套数据库开发审计标准，根据标准对一个企业级信息系统进行了多次数据库开发审计，发现了系统潜在的性能隐患，采取相应措施，改善了信息系统性能。

关键词：SQL优化；企业级信息系统；表设计；索引优化；数据库开发审计

DOIDOI：10.11907/rjdk.162063

中图分类号：TP391

文献标识码：A文章编号：1672-7800（2016）012-0136-03

0 引言

随着计算机技术和网络技术的普及，数据库技术得到了长足发展，成为现代计算机应用与信息系统的核心技术。随着信息系统用户规模和应用范围的不断扩展，数据库性能问题越来越突出，越来越难以优化，需要在信息系统开发及试运行阶段进行库表设计、索引优化和SQL优化来解决系统性能瓶颈，以确保数据库和信息系统稳定、高效运行。

Oracle数据库是当前市场占有率最高、使用范围最广的关系型数据库。对于使用关系型数据库的信息系统而言，SQL语句的好坏直接影响系统性能。例如：某公司Call center系统坐席登录非常慢，严重影响了日常工作，原因是SQL选错执行计划，走全表扫描导致[1]；某房地产开发商ERP系统因为一条全表扫描的SQL语句，导致在业务繁忙时段引起大量的读写等待事件，最终数据库失去响应；某政府系统因为一个SQL语句的索引设计不当导致大量的IO读，引起数据库性能缓慢，最终信息系统失去响应。从上述事例可以看出，一条性能低下的SQL语句就可以拖垮整个系统，甚至导致数据库服务器失去响应或整个数据库挂起。

基于Oracle数据库建设经验，本文研究了数据库的表设计和SQL语句优化，提出了数据库开发审计标准，以指导大型信息系统在开发及试运行阶段的性能优化工作。在实践过程中发现了多处信息系统性能隐患，完善了审计标准。

1 数据库性能指标

业界通用的Oracle数据库性能评价指标主要有系统吞吐量、用户平均响应时间、磁盘IO、数据库AWR报告中的AAS/CPU_Count（DB time/Elapsed）比值4个指标。

（1）系统吞吐量。系统吞吐量指单位时间内数据库完成的SQL语句数目，以每秒的事务量（TPS）表示。

（2）用户平均响应时间。响应时间指用户从提交SQL语句开始到获得结果集的第一行所需要的时间，是应用作出反应的时间，以毫秒或秒表示。

（3）磁盘IO。数据库中发生的每个动作几乎都将产生某种类型的IO活动，该活动可以是逻辑的（在内存中），也可以是物理的（在磁盘上）。通过降低不必要的IO开销，可增加用户任务获得的吞吐量，缩短用户“响应时间”。其中，磁盘IO操作是数据库性能最重要的方面，是计算机最大的开销。因此，通过减少不必要的磁盘IO，可大大提高系统性能。

（4）AAS/CPU_Count比值。DB Time为某一时段时间数据库使用的CPU时间的总和，平均活动会话Average Active Session（AAS）=DB Time/elaspsed time（历时），通过AAS指标可以衡量数据库的繁忙程度。每一个CPU时间由操作系统分成CPU时间片，CPU时间片以轮询模式分配给线程或进程，计算在最小单位CPU片段内整个系统允许的最大CPU个数。通过比较AAS值与CPU可以衡量数据库繁忙程度。

AAS/CPU_Count ≈0非常空闲；

AAS/CPU_Count <= 0.5没堵塞；

AAS/CPU_Count≈1部分进程已达100%，应用开始出现缓慢；

AAS/CPU_Count>或>>1出现性能问题或堵死、挂死状态。

2 数据库开发审计

基于Oracle数据库的开发审计主要对SQL语句和表设计两个维度进行分析。

2.1 SQL语句分析

较大的表使用全表扫描的SQL会导致过多的逻辑读，降低SQL的执行效率，引起latch：cache buffer chain、direct path read 等异常等待事件，最终导致数据库主机CPU、IO使用率过高，数据库繁忙。

高耗时的SQL会引起数据库的大部分资源（IO、CPU等）被少数几条SQL占用，引起数据库繁忙，大大降低系统的吞吐量。

SELECT *语句虽然获取了表中的所有列数据，但实际情况却可能只需要其中一列或少许列，其它列数据对应用功能是多余的，导致数据库采用最大的可能来满足应用需求。如果应用代码能够明确写明编程意图，列出具体要获取哪些字段值，就可以提高数据库的处理能力。如果所需的列在索引里已存在，Oracle就只要查询索引即可返回结果，不需要查询表数据本身，这样可极大提高SQL的执行效率，大幅降低对IO的请求，减少信息系统和数据库通讯的数据量。

在条件列进行函数运算，将会导致列上的索引无法使用，数据库被迫进行全表扫描，大大降低SQL的执行效率。

2.2 表设计分析

大对象数据一般非常大，被databuffer缓存的可能性非常小，因此读写大对象数据会导致比较多的IO开销。如大对象数据使用独立表空间就可以独立进行磁盘管理，甚至分散到多个磁盘来存储，从而提高IO的吞吐量[3]。

大对象读写的数据单元一般大于数据库默认的数据块大小。如果大数据字段的数据块配置太小，会导致比较多的IO次数。增加大对象独立表空间的数据块大小可以减少IO次数，提高性能。

应用程序动态创建实体表，表定义会保留在数字字典中。大量动态实体表创建会导致Oracle数字字典过大，降低数据库性能。信息系统如大量采用动态表创建机制而清理不及时，会大大增加数据字典数量，进而增加SQL的执行时间。此外，对大数据量的表执行全表扫描，会导致过多的逻辑读，导致数据库繁忙、CPU使用率过高。

主键是表内数据唯一性的主要标识，主键设计可以有效保障数据质量，避免重复数据。在多表关联的SQL语句中，主键和外键是常见的关联条件列。如果没有主键和外键索引存在，就会严重影响SQL语句的执行计划生成和执行效率，大大降低系统吞吐量，增加用户响应时间。

创建大量的单列索引会导致索引数量太多，降低DML语句性能和IO效率。单列索引对数据的过滤能力（离散度）有限，对识别能力不强的字段创建索引，会导致即使有索引也要读取大量的无效数据。太多的单列索引可能会导致Oracle执行计划困难，甚至产生不恰当的执行计划，严重影响执行效率。

如果表上只有主键而没有普通索引，那么这个表发生全表扫描的可能性非常大。特别是系统用户数、数据量较大时，会产生严重的性能问题，导致过高的磁盘IO和缓慢的用户响应，降低系统吞吐量。

2.3 数据库开发审计标准

数据库开发审计由于缺乏定量指标，无法直接用于数据库开发。根据上述原理，本文提出一种数据库开发审计标准，在数据库开发过程或系统上线前对SQL及表索引设计进行审计，以避免系统在实际生产过程中出现性能瓶颈。

2.3.1 表审计

表审计是在数据库的所有表中查找设计不佳的表，并对这些表的数量进行统计。设计不佳的表有3种类型：①大对象使用：统计存在大对象但是对大对象字段没有单独的表空间存放；②动态创建表：在应用程序运行过程中，动态创建实体表； ③大数据量表：当前（或预期）数据量大于2 000万行（或大于2GB）的、尚未制定数据生命周期策略的表。数据生命周期策略包括转储策略、索引分区、数据分区、数据分离等[2]。

2.3.2 索引审计

索引审计是在数据库的所有索引中查找设计不佳的索引，并对这些索引数量进行统计。设计不佳的索引类型有：①主键设计：统计没有设计主键且没有唯一索引的表。在数据模型设计过程中，主键是一张表的重要设计项目，原则上每张表必须确保有合适的主键；②组合索引：统计拥有5个或以上索引的表的数量。一个表的索引多于5个就要进行审核和监控，判断这些索引设计是否合理；③无普通索引：统计所有只有主键没有其它索引的表的数量。当SQL使用非主键的列作为条件列来查询数据时，如果没有合适的索引，就会导致全表扫描。

2.3.3 SQL审计

SQL审计是在数据库的所有执行记录中查找性能不佳的SQL，并对这些SQL数量进行统计。性能不佳的SQL类型：①全表扫描：统计出现全表扫描的SQL数量。全表扫描是指Oracle在访问目标表里的数据时，会从该表所占用的第一个区（EXTENT）的第一个块（BLOCK）开始扫描，一直扫描到该表的高水位线（HWM，High Water Mark），这段范围内所有的数据块Oracle都必须读到。在OLTP类型的数据库中，对大表的全表扫描所耗费的IO资源和时间，随着数据量的增加而增加；②条件列函数运算：统计SQL语句中对表的查询条件字段添加了计算函数（如substr，length等）的SQL数量。这类SQL导致该字段上的索引无法使用，从而导致低效的全表扫描，并可能改变执行计划，这种现象必须尽量在应用程序中避免；③SELECT *：统计所有含有SELECT *的SQL语句数量。Oracle在SQL解析过程中，会将“*”依次转换成所有的列名。这个工作通过查询数据字典完成，意味着将耗费更多的时间。为提高SQL语句解释速度，应避免在SQL语句使用“*”代替所有字段名；④高耗时SQL：统计所有执行时间大于2秒的SQL语句数量。实时监控信息系统中正常执行的Top SQL，将这些SQL不间断优化，以保持信息系统数据库的良好性能状况。

2.3.4 数据库开发审计实施

为防止不稳定或不成熟的系统上线，必须建立一套准确有效的数据库开发审计标准，在测试环境或准正式环境中模拟运行系统所有功能，使数据库审计能有效获取系统执行痕迹，估算各种痕迹可能存在的性能隐患，并进行评定，根据评定分值衡量一个系统的性能健康程度。

当系统达标上线后，由于数据量或数据分布情况的不断变化，大量的系统功能BUG会不停地变更修复，同时新的功能也会不断加入进来。为了保障这些变更或新增功能的有效运行，必须对每次系统的升级变更进行全面的性能审计，及早发现问题。对于审计后的系统运行也需要不断监控，发现问题及时通过SQL和Index进行优化。上述过程是有效保障系统数据库性能稳定的有效方法。

2.4 数据库开发审计实践

某大型国企的核心信息系统建设项目，业主采用基于SQL语句和表设计的数据库开发审计标准进行数据库开发审计。在系统出厂测试时进行第一次开发审计，随后在部署完成、试运行、正式运行3个关键时间节点进行3次数据库开发审计复核。这4次开发审计的综合评定结果如表1所示。

在每次开发审计完成后，均要求项目开发商针对评定结果、详细问题列表和改善建议进行整改。经过开发商和数据库管理员的多次整改，所有审计违规项均大幅减少，主要数据库性能指标有明显改善，详见表2和图1。

从图1可以看出，经过多次审计和整改，系统数据库各项性能指标均有大幅提升。系统吞吐量由54上升至186，提升近3倍；用户响应时间明显下降，第3次复核后的响应时间仅是初次审计前的2%；磁盘IO显著降低，读写不再是系统瓶颈；反应数据库繁忙程度的指标AAS/CPU_Count也从0.7下降至0.2。这些性能指标的提升说明数据库开发审计对信息系统性能改善有极大的促进作用，明确了整改方向，提升了整改效果。

3次整改使信息系统达到上线标准，如期上线投入使用。上线后，系统吞吐量、用户响应时间、磁盘IO、AAS/CPU_Count等各项性能指标均符合预期，系统运行状况良好，没有出现性能问题。

3 结语

基于SQL和表设计的Oracle数据库开发审计标准可及时发现信息系统建设过程中的性能隐患、快速定位信息系统问题原因、有效开展具有针对性整改，从而提高信息系统开发质量，提高信息系统的支撑服务能力和系统稳定性。该套数据库开发审计标准可应用在各类大型信息系统开发、建设和运维工作中。

参考文献：

[1] 崔华.基于Oracle的SQL优化[M].北京：电子工业出版社，2014.

[2] 谭怀远.让Oracle跑得更快2—基于海量数据的数据库设计与优化[M].北京：电子工业出版社，2011.

[3] THOMAS KYTE.Oracle database 9i/10g/11g编程艺术[M].苏金国，王小振，译.北京：人民邮电出版社，2009.

（责任编辑：杜能钢）