浅谈FireBird数据库SQL语句的优化

刘华

摘要：数据库是计算机信息管理系统的核心部分，必不可少的。该文主要分析了基于FireBird数据库的SQL语句优化技术，通过实例进行优化技术前后性能指标的分析与总结，阐述了SQL语句的优化对数据库系统性能的改善和提升起到了重要的作用。

关键词：FireBird；数据库；SQL语句；优化

中图分类号：TP311 文献标识码：A 文章编号：1009-3044（2016）16-0018-02

1 数据库优化背景知识

数据库最常见的优化手段是对硬件的升级，据统计，对网络、硬件、操作系统、数据库参数进行优化所获得的性能提升，全部加起来只占数据库系统性能提升的40%左右，其余的60%系统性能提升来自对应用程序的优化。应用程序的优化分为源代码和SQL语句优化。由于涉及对程序逻辑的改变，源代码的优化在时间成本和风险上代价很高，而对数据库性能提升收效有限。SQL语句在执行中消耗了70%～90%的数据库资源，对SQL语句进行优化不会影响程序逻辑，而对于SQL语句的优化成本较低、收益却比较高，所以对SQL语句进行优化改进，对于提高数据库性能和效率是非常有必要的。

2 分析SQL优化问题

许多程序员认为查询优化与编写的SQL语句关系不大，这是错误的认识，一个好的SQL查询语句往往可以使程序性能提高数十倍，同时减轻数据库服务器的承载压力。实际应用程序开发过程中还是以用户提交的SQL语句作为系统优化的基础，很难设想一个原本糟糕的SQL查询语句经过系统的优化之后会变得高效.查询优化技术在关系数据库系统中有着非常重要的地位，关系数据库系统和非过程化的SQL语言能够取得巨大的成功，关键是得益于查询优化技术的发展。从本质上讲。用户希望查询的运行速度能够尽可能地快，无论是将查询运行的时间从10分钟缩减为1分钟，还是将运行的时间从2秒缩短为1秒钟，最终的目标都是减少运行时间。由于应用程序可能生成非常复杂的SQL语句，查询优化程序必须精心构建、功能强大，以保障良好的执行性能。查询优化程序可转换SQL语句。使复杂的语句转换成为等价的但执行性能更好的SQL语句。

3 SQL语句优化策略

本文以关系数据库系统FireBird为例，结合数据库理论，以实际软件开发过

程中的实例为说明，介绍了SQL语句优化技术在现实系统中的运用。

3.1 索引原理

索引是一个单独的、物理的数据库结构.FireBird数据库以及其他大型的关系式数据库引擎，都具备各式索引功能。

建立索引的优点：

1）大大加快数据的检索速度，提高数据库执行效率。

2）创建唯一性索引，保证数据库表中每一行数据的唯一性；

3）在表与表之间建立关联字段索引，加速表和表之间数据访问连接速度；

4）对频繁进行GROUPBY（分组）或ORDERBY（排序）操作的表结构字段建立索引。

以实际ERP项目开发中服装加工数据为例，一张为服装入库表：fz_rk（批号、服装单号、加工人员）约5000条记录，另一张为服装出库表： fz_ck （批号、服装单号、加工人员）约6000条记录，现要查看相同服装单号的入出库加工人员信息并对服装单号排序，SQL执行语句：

select o.bh，o.xm rk，p.xm ck from fz_rk oleft join fz_ck p on （p.bh=o.bh）order by o.bh，以服装单号字段作为为表结构索引，现将未建索引和建立索引后的数据分析如下：

从表中数据知，在相同情况下执行相同SQL语句查询，索引优化不管是占用CPU内存还是缓存数据读取，性能都有大幅度的提升，执行时间更是呈几何级速度下降，数据库查询效率得到明显的提高。

3.2 把对视图的访问转换为对基本表的访问

视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制，利用视图可以大大简化用户的工作。本人在程序开发初期也创建过较多的视图简化工作量，但是从查询优化的角度来看，视图则不利于查询效率的提高。SQL语言是一个描述性的非过程化语言，用户在写SQL语句时，不用知道需要操作的数据具体是如何存放以及按照什么步骤进行处理，查询处理器会自动完成这些工作。但是，查询语句操作的数据库对象除了基本表以外，还可能是视图。如果查询处理器直接对视图进行操作，查询优化器所能生成的执行计划是先执行视图定义，这种处理方式在绝大多数情况下效率极低（大数据量的话效率更低）。因此，在优化查询速度上，应尽量避开使用视图，将对视图的引用转换为对视图所涉及的基本表的引用，从而得到一个功能上完全等价的SQL语句查询，重写后的查询效率性能上肯定比原先创建的视图提高了很多倍。

还是以上表为例作分析，创建视图：

create view v_rck（bh，rk，ck） as select o.bh，o.xm rk，p.xm ck from fz_rk oleft join fz_ck p on （p.bh=o.bh）如果直接对视图数据进行查询，select * from v_rck，6000条数据记录查询时间将近32ms，如果创建相同表结构进行存储数据后直接对表数据进行查询，则时间在16ms左右，如果数据集容量扩大至十万、百万级，则数据库查询中以表代替视图SQL语句的执行效率会越来越明显。

3.3 调用存储过程实现程序优化

一般的，FireBird数据库存储过程可划分为两大类：一类是选择式存储过程，返回一个数据集，可以直接用select语句调用存储过程；另一类是执行式存储过程，它不返回数据集，使用Execute Procedure来调用存储过程。下面以选择式存储过程用法来举例说明：在阳光纺织ERP项目开发过程中，品种贯穿整个生产流程的始终，如果要分析某个品种的成分信息，品种中不同的字母代表生产流程中不同的工艺、不同的生产要求，则核算成本价格也不同，如果在程序执行时每次都对该数据库品种字段进行循环调用处理分析，则会严重影响执行数据库执行效率。在此利用FireBird数据库选择式存储过程的优点，定义如下：

在此定义了一个pz_fx品种分析存储过程，只需在SQL语句中调用该定义的存储过程即可分析表中品种字段的不同成分信息.select a.ph，case when（select r from pz_fx （a.ph，H'））<>''then a.ph end ph from jh_ph a group by a.ph

4 SQL语句优化等价变换

SQL语句查询优化重写的首要目的是将查询结果转化为效率更高的执行方式，通过等价变换，最大限度发挥服务器性能，提高用户查询速度和效率。

1）尽量避免使用<>、or、is not null、in、not in、like等这样的操作符。

这些操作符会使系统无法使用索引，而只能直接搜索全表中的数据，大大影响执行效率。例如：select ph from jh_ph Where ph like ‘%37200%如果使用该前后包含SQL语句，将对表中25万条记录逐个比对分析，效率极低，运行时间大概5s，而如果在数据表中添加一个数字品种字段phsz，预先分离数字信息，换成如下方式查询，则效果会非常好：select ph from jh_ph where phsz=‘37200，执行时间大概30ms左右，大大提高了运行速度。

2）尽量避免在Where子句中对字段进行函数或表达式操作。

Select ph from jh_ph here substring（ph from 1 for 4）=‘ABCD

等价于：Select ph from jh_ph where ph like ‘ABCD%执行效率要高于上一表达式。

3）数据查询读取中exists和union执行效率的比对：

入库表cp_rk，5万数据记录（ph，sl）出库表cp_ck，8万数据记录（ph，sl）.

select ph，sum（rk）rk，sum（ck） ck from（select o.ph，sum（o.sl）rk，0 ck from cp_rk o

where exists（select ph from cp_ck p where id=o.id）group by o.ph union allselect o.ph，0，sum（o.sl）ck from cp_ck owhere exists （select ph from cp_rk p where id=o.id）group by o.ph） group by ph.以exists语句统计成品入出库表中品种的产量，执行时间接近3s，改写后SQL语句：select o.ph，sum（o.sl） rk， sum（p.sl）ck from cp_rk oleft join cp_ck p on （p.id=o.id） group by o.ph. 执行结果和第一种方法结果完全一致，执行时间20ms，语句简洁明了，执行效率大大提高。

上面所举三个SQL语句等价交换例子，有针对性的反应出了FireBird数据库在SQL语句编写上的一些规则，每条规则提高的效率可能不太明显，但如果查询语句的WHERE条件同时使用多条规则关联，在数据量比较大，嵌套使用SQL语句执行时，效率提高将非常可观，这也是SQL语句优化的本质所在。

5 结束语

在数据库的开发和维护中，查询的优化设计可以提高系统性能，特别对于经常要用于查询数据量大的数据库系统更显得重要.SQL语句查询优化的实质就是在确保结果正确的前提下，用优化器可以识别的语句，充分利用索引，尽量减少、避免表搜索的循环发生，提高数据库执行效率，实现快速、高效的数据查询和应用分析，同时也使硬件资源得到最充分的发挥。

参考文献：

[1] 徐雷. Firebird数据库及其开发应用[J]. 软件技术， 2004（7）.

[2] 郭忠南， 孟凡荣. 关系数据库性能优化研究[J]. 计算机工程与设计， 2006， 27（23）.

[3] 张敏. SQL语句优化研究[J]. 现代电子技术， 2010（4）.

[4] 朱鸿宇，刘瑰，等. 数据库查询优化中的智能预取技术[J]. 计算机应用研究， 2007，24（5）.