淺談FireBird數(shù)據(jù)庫SQL語句的優(yōu)化

劉華

摘要：數(shù)據(jù)庫是計算機(jī)信息管理系統(tǒng)的核心部分，必不可少的。該文主要分析了基于FireBird數(shù)據(jù)庫的SQL語句優(yōu)化技術(shù)，通過實(shí)例進(jìn)行優(yōu)化技術(shù)前后性能指標(biāo)的分析與總結(jié)，闡述了SQL語句的優(yōu)化對數(shù)據(jù)庫系統(tǒng)性能的改善和提升起到了重要的作用。

關(guān)鍵詞：FireBird；數(shù)據(jù)庫；SQL語句；優(yōu)化

中圖分類號：TP311 文獻(xiàn)標(biāo)識碼：A 文章編號：1009-3044（2016）16-0018-02

1 數(shù)據(jù)庫優(yōu)化背景知識

數(shù)據(jù)庫最常見的優(yōu)化手段是對硬件的升級，據(jù)統(tǒng)計，對網(wǎng)絡(luò)、硬件、操作系統(tǒng)、數(shù)據(jù)庫參數(shù)進(jìn)行優(yōu)化所獲得的性能提升，全部加起來只占數(shù)據(jù)庫系統(tǒng)性能提升的40%左右，其余的60%系統(tǒng)性能提升來自對應(yīng)用程序的優(yōu)化。應(yīng)用程序的優(yōu)化分為源代碼和SQL語句優(yōu)化。由于涉及對程序邏輯的改變，源代碼的優(yōu)化在時間成本和風(fēng)險上代價很高，而對數(shù)據(jù)庫性能提升收效有限。SQL語句在執(zhí)行中消耗了70%～90%的數(shù)據(jù)庫資源，對SQL語句進(jìn)行優(yōu)化不會影響程序邏輯，而對于SQL語句的優(yōu)化成本較低、收益卻比較高，所以對SQL語句進(jìn)行優(yōu)化改進(jìn)，對于提高數(shù)據(jù)庫性能和效率是非常有必要的。

2 分析SQL優(yōu)化問題

許多程序員認(rèn)為查詢優(yōu)化與編寫的SQL語句關(guān)系不大，這是錯誤的認(rèn)識，一個好的SQL查詢語句往往可以使程序性能提高數(shù)十倍，同時減輕數(shù)據(jù)庫服務(wù)器的承載壓力。實(shí)際應(yīng)用程序開發(fā)過程中還是以用戶提交的SQL語句作為系統(tǒng)優(yōu)化的基礎(chǔ)，很難設(shè)想一個原本糟糕的SQL查詢語句經(jīng)過系統(tǒng)的優(yōu)化之后會變得高效.查詢優(yōu)化技術(shù)在關(guān)系數(shù)據(jù)庫系統(tǒng)中有著非常重要的地位，關(guān)系數(shù)據(jù)庫系統(tǒng)和非過程化的SQL語言能夠取得巨大的成功，關(guān)鍵是得益于查詢優(yōu)化技術(shù)的發(fā)展。從本質(zhì)上講。用戶希望查詢的運(yùn)行速度能夠盡可能地快，無論是將查詢運(yùn)行的時間從10分鐘縮減為1分鐘，還是將運(yùn)行的時間從2秒縮短為1秒鐘，最終的目標(biāo)都是減少運(yùn)行時間。由于應(yīng)用程序可能生成非常復(fù)雜的SQL語句，查詢優(yōu)化程序必須精心構(gòu)建、功能強(qiáng)大，以保障良好的執(zhí)行性能。查詢優(yōu)化程序可轉(zhuǎn)換SQL語句。使復(fù)雜的語句轉(zhuǎn)換成為等價的但執(zhí)行性能更好的SQL語句。

3 SQL語句優(yōu)化策略

本文以關(guān)系數(shù)據(jù)庫系統(tǒng)FireBird為例，結(jié)合數(shù)據(jù)庫理論，以實(shí)際軟件開發(fā)過

程中的實(shí)例為說明，介紹了SQL語句優(yōu)化技術(shù)在現(xiàn)實(shí)系統(tǒng)中的運(yùn)用。

3.1 索引原理

索引是一個單獨(dú)的、物理的數(shù)據(jù)庫結(jié)構(gòu).FireBird數(shù)據(jù)庫以及其他大型的關(guān)系式數(shù)據(jù)庫引擎，都具備各式索引功能。

建立索引的優(yōu)點(diǎn)：

1）大大加快數(shù)據(jù)的檢索速度，提高數(shù)據(jù)庫執(zhí)行效率。

2）創(chuàng)建唯一性索引，保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性；

3）在表與表之間建立關(guān)聯(lián)字段索引，加速表和表之間數(shù)據(jù)訪問連接速度；

4）對頻繁進(jìn)行GROUPBY（分組）或ORDERBY（排序）操作的表結(jié)構(gòu)字段建立索引。

以實(shí)際ERP項(xiàng)目開發(fā)中服裝加工數(shù)據(jù)為例，一張為服裝入庫表：fz_rk（批號、服裝單號、加工人員）約5000條記錄，另一張為服裝出庫表： fz_ck （批號、服裝單號、加工人員）約6000條記錄，現(xiàn)要查看相同服裝單號的入出庫加工人員信息并對服裝單號排序，SQL執(zhí)行語句：

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，以服裝單號字段作為為表結(jié)構(gòu)索引，現(xiàn)將未建索引和建立索引后的數(shù)據(jù)分析如下：

從表中數(shù)據(jù)知，在相同情況下執(zhí)行相同SQL語句查詢，索引優(yōu)化不管是占用CPU內(nèi)存還是緩存數(shù)據(jù)讀取，性能都有大幅度的提升，執(zhí)行時間更是呈幾何級速度下降，數(shù)據(jù)庫查詢效率得到明顯的提高。

3.2 把對視圖的訪問轉(zhuǎn)換為對基本表的訪問

視圖是關(guān)系數(shù)據(jù)庫系統(tǒng)提供給用戶以多種角度觀察數(shù)據(jù)庫中數(shù)據(jù)的重要機(jī)制，利用視圖可以大大簡化用戶的工作。本人在程序開發(fā)初期也創(chuàng)建過較多的視圖簡化工作量，但是從查詢優(yōu)化的角度來看，視圖則不利于查詢效率的提高。SQL語言是一個描述性的非過程化語言，用戶在寫SQL語句時，不用知道需要操作的數(shù)據(jù)具體是如何存放以及按照什么步驟進(jìn)行處理，查詢處理器會自動完成這些工作。但是，查詢語句操作的數(shù)據(jù)庫對象除了基本表以外，還可能是視圖。如果查詢處理器直接對視圖進(jìn)行操作，查詢優(yōu)化器所能生成的執(zhí)行計劃是先執(zhí)行視圖定義，這種處理方式在絕大多數(shù)情況下效率極低（大數(shù)據(jù)量的話效率更低）。因此，在優(yōu)化查詢速度上，應(yīng)盡量避開使用視圖，將對視圖的引用轉(zhuǎn)換為對視圖所涉及的基本表的引用，從而得到一個功能上完全等價的SQL語句查詢，重寫后的查詢效率性能上肯定比原先創(chuàng)建的視圖提高了很多倍。

還是以上表為例作分析，創(chuàng)建視圖：

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）如果直接對視圖數(shù)據(jù)進(jìn)行查詢，select * from v_rck，6000條數(shù)據(jù)記錄查詢時間將近32ms，如果創(chuàng)建相同表結(jié)構(gòu)進(jìn)行存儲數(shù)據(jù)后直接對表數(shù)據(jù)進(jìn)行查詢，則時間在16ms左右，如果數(shù)據(jù)集容量擴(kuò)大至十萬、百萬級，則數(shù)據(jù)庫查詢中以表代替視圖SQL語句的執(zhí)行效率會越來越明顯。

3.3 調(diào)用存儲過程實(shí)現(xiàn)程序優(yōu)化

一般的，F(xiàn)ireBird數(shù)據(jù)庫存儲過程可劃分為兩大類：一類是選擇式存儲過程，返回一個數(shù)據(jù)集，可以直接用select語句調(diào)用存儲過程；另一類是執(zhí)行式存儲過程，它不返回數(shù)據(jù)集，使用Execute Procedure來調(diào)用存儲過程。下面以選擇式存儲過程用法來舉例說明：在陽光紡織ERP項(xiàng)目開發(fā)過程中，品種貫穿整個生產(chǎn)流程的始終，如果要分析某個品種的成分信息，品種中不同的字母代表生產(chǎn)流程中不同的工藝、不同的生產(chǎn)要求，則核算成本價格也不同，如果在程序執(zhí)行時每次都對該數(shù)據(jù)庫品種字段進(jìn)行循環(huán)調(diào)用處理分析，則會嚴(yán)重影響執(zhí)行數(shù)據(jù)庫執(zhí)行效率。在此利用FireBird數(shù)據(jù)庫選擇式存儲過程的優(yōu)點(diǎn)，定義如下：

在此定義了一個pz_fx品種分析存儲過程，只需在SQL語句中調(diào)用該定義的存儲過程即可分析表中品種字段的不同成分信息.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語句優(yōu)化等價變換

SQL語句查詢優(yōu)化重寫的首要目的是將查詢結(jié)果轉(zhuǎn)化為效率更高的執(zhí)行方式，通過等價變換，最大限度發(fā)揮服務(wù)器性能，提高用戶查詢速度和效率。

1）盡量避免使用<>、or、is not null、in、not in、like等這樣的操作符。

這些操作符會使系統(tǒng)無法使用索引，而只能直接搜索全表中的數(shù)據(jù)，大大影響執(zhí)行效率。例如：select ph from jh_ph Where ph like ‘%37200%如果使用該前后包含SQL語句，將對表中25萬條記錄逐個比對分析，效率極低，運(yùn)行時間大概5s，而如果在數(shù)據(jù)表中添加一個數(shù)字品種字段phsz，預(yù)先分離數(shù)字信息，換成如下方式查詢，則效果會非常好：select ph from jh_ph where phsz=‘37200，執(zhí)行時間大概30ms左右，大大提高了運(yùn)行速度。

2）盡量避免在Where子句中對字段進(jìn)行函數(shù)或表達(dá)式操作。

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

等價于：Select ph from jh_ph where ph like ‘ABCD%執(zhí)行效率要高于上一表達(dá)式。

3）數(shù)據(jù)查詢讀取中exists和union執(zhí)行效率的比對：

入庫表cp_rk，5萬數(shù)據(jù)記錄（ph，sl）出庫表cp_ck，8萬數(shù)據(jù)記錄（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語句統(tǒng)計成品入出庫表中品種的產(chǎn)量，執(zhí)行時間接近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. 執(zhí)行結(jié)果和第一種方法結(jié)果完全一致，執(zhí)行時間20ms，語句簡潔明了，執(zhí)行效率大大提高。

上面所舉三個SQL語句等價交換例子，有針對性的反應(yīng)出了FireBird數(shù)據(jù)庫在SQL語句編寫上的一些規(guī)則，每條規(guī)則提高的效率可能不太明顯，但如果查詢語句的WHERE條件同時使用多條規(guī)則關(guān)聯(lián)，在數(shù)據(jù)量比較大，嵌套使用SQL語句執(zhí)行時，效率提高將非常可觀，這也是SQL語句優(yōu)化的本質(zhì)所在。

5 結(jié)束語

在數(shù)據(jù)庫的開發(fā)和維護(hù)中，查詢的優(yōu)化設(shè)計可以提高系統(tǒng)性能，特別對于經(jīng)常要用于查詢數(shù)據(jù)量大的數(shù)據(jù)庫系統(tǒng)更顯得重要.SQL語句查詢優(yōu)化的實(shí)質(zhì)就是在確保結(jié)果正確的前提下，用優(yōu)化器可以識別的語句，充分利用索引，盡量減少、避免表搜索的循環(huán)發(fā)生，提高數(shù)據(jù)庫執(zhí)行效率，實(shí)現(xiàn)快速、高效的數(shù)據(jù)查詢和應(yīng)用分析，同時也使硬件資源得到最充分的發(fā)揮。

參考文獻(xiàn)：

[1] 徐雷. Firebird數(shù)據(jù)庫及其開發(fā)應(yīng)用[J]. 軟件技術(shù)， 2004（7）.

[2] 郭忠南， 孟凡榮. 關(guān)系數(shù)據(jù)庫性能優(yōu)化研究[J]. 計算機(jī)工程與設(shè)計， 2006， 27（23）.

[3] 張敏. SQL語句優(yōu)化研究[J]. 現(xiàn)代電子技術(shù)， 2010（4）.

[4] 朱鴻宇，劉瑰，等. 數(shù)據(jù)庫查詢優(yōu)化中的智能預(yù)取技術(shù)[J]. 計算機(jī)應(yīng)用研究， 2007，24（5）.