基于Oracle和溫度數(shù)據(jù)的SQL優(yōu)化方案

黃天峰

摘要：該文基于Oracle數(shù)據(jù)庫(kù)和某物流系統(tǒng)，使用SQL優(yōu)化技術(shù)和冷溫?zé)釘?shù)據(jù)劃分技術(shù)，設(shè)計(jì)并且驗(yàn)證了一個(gè)物流系統(tǒng)出庫(kù)表在Oracle數(shù)據(jù)庫(kù)中的SQL查詢(xún)優(yōu)化方案，并且對(duì)方案的可行性做出了論證和實(shí)現(xiàn)，基本達(dá)到了期初目標(biāo)的效果。本優(yōu)化方案可以廣泛地應(yīng)用于各個(gè)類(lèi)似與訂單系統(tǒng)中數(shù)據(jù)庫(kù)查詢(xún)效率的優(yōu)化。

關(guān)鍵詞：物流系統(tǒng)；Oracle；SQL優(yōu)化；冷溫?zé)釘?shù)據(jù)

中圖分類(lèi)號(hào)：TP391 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1009-3044（2018）21-0003-02

Abstract： This dissertation based on Oracle and a logistics system， using a variety of SQL optimization and temperature data partitioning technology. The SQL query optimization scheme of a logistics system outbound table in Oracle is designed and validated. The feasibility of the scheme is demonstrated and realized. Basically， the initial target has been achieved. This optimization scheme can be widely applied to the optimization of database query efficiency in various similar order systems.

Key words： Logistics System； Oracle； Temperature Data； SQL Optimization

1 國(guó)內(nèi)外SQL優(yōu)化現(xiàn)狀

查詢(xún)優(yōu)化概念自70年代啟以來(lái)一直成為研究的熱點(diǎn)，從近十年的研究情況來(lái)看，SQL性能優(yōu)化可以分為以下幾個(gè)方面：

第一、優(yōu)化SQL本身。從減少查詢(xún)嵌套、減少子查詢(xún)、查詢(xún)轉(zhuǎn)換、group by、視圖合并、公共子表達(dá)式消除、半連接、反連接、星形轉(zhuǎn)換、避免多個(gè)查詢(xún)塊中的自連接、物化視圖、游標(biāo)變量、批處理綁定等等方面優(yōu)化SQL查詢(xún)效率。

第二、優(yōu)化Oracle優(yōu)化器。通過(guò)研發(fā)新的優(yōu)化器，尋找更好的執(zhí)行計(jì)劃。替代Oracle自身優(yōu)化器生成的執(zhí)行計(jì)劃，以提高SQL查詢(xún)的執(zhí)行效率。

第三、優(yōu)化數(shù)據(jù)資源調(diào)配。在大型的分布是系統(tǒng)中，數(shù)據(jù)庫(kù)不只是一個(gè)節(jié)點(diǎn)，通過(guò)多來(lái)源數(shù)據(jù)資源訪問(wèn)的調(diào)配，達(dá)到最短時(shí)間檢索到數(shù)據(jù)的目標(biāo)。

第四、數(shù)據(jù)存儲(chǔ)優(yōu)化。在集中式數(shù)據(jù)庫(kù)中，通過(guò)海量數(shù)據(jù)存儲(chǔ)的優(yōu)化，通過(guò)數(shù)據(jù)分層等操作縮小數(shù)據(jù)檢索范圍、減少I(mǎi)/O的操作來(lái)提升SQL查詢(xún)的效率。

第五、存儲(chǔ)設(shè)備優(yōu)化研究。任何數(shù)據(jù)都是以存儲(chǔ)設(shè)備為媒介進(jìn)行存儲(chǔ)，存儲(chǔ)媒介有磁帶、軟盤(pán)、硬盤(pán)、閃存等存儲(chǔ)介質(zhì)。通過(guò)對(duì)硬件的研究，從硬件的I/O響應(yīng)速度和數(shù)據(jù)塊讀取效率出發(fā)提高SQL查詢(xún)的執(zhí)行效率。

第六、優(yōu)化業(yè)務(wù)應(yīng)用程序。數(shù)據(jù)庫(kù)中SQL查詢(xún)的執(zhí)行效率低下大部分是由于業(yè)務(wù)系統(tǒng)SQL的不合理產(chǎn)生，直接優(yōu)化業(yè)務(wù)應(yīng)用程序以提高SQL查詢(xún)的執(zhí)行效率。

2 SQL查詢(xún)優(yōu)化需求

2.1 SQL查詢(xún)效率

物流系統(tǒng)是一個(gè)龐大的系統(tǒng)，物流數(shù)據(jù)庫(kù)中有許多表，每張表都圍繞物流業(yè)務(wù)展開(kāi)。本文以物流出庫(kù)單為例展開(kāi)討論，物流出庫(kù)單在Oracle數(shù)據(jù)庫(kù)中主要涉及兩張表，物流出庫(kù)單主表和物流出庫(kù)單子表。在運(yùn)營(yíng)過(guò)程中，每一單出庫(kù)都會(huì)生成一條記錄，并且主單細(xì)單同時(shí)生成，因此這兩張表數(shù)據(jù)量非常龐大，特別是一個(gè)系統(tǒng)運(yùn)行幾年以后，其數(shù)據(jù)已經(jīng)達(dá)到不可想象的程度。物流中一個(gè)出庫(kù)的完成需要經(jīng)過(guò)許多工序，如生成單據(jù)、修改單據(jù)、關(guān)閉單據(jù)、打開(kāi)單據(jù)和廢除單據(jù)等，他們?cè)跀?shù)據(jù)庫(kù)中的體現(xiàn)就是Update、Insert和Select操作。由于物流特性，系統(tǒng)中不存在刪除單據(jù)，因此對(duì)于出庫(kù)單不存在Delete操作。物流出庫(kù)單的這些屬性使得出庫(kù)單表使用非常頻繁，系統(tǒng)高峰期SQL的性能非常低下。

2.2 數(shù)據(jù)質(zhì)量保證

臟數(shù)據(jù)，系統(tǒng)中任何操作都會(huì)產(chǎn)生一系列事物，每一個(gè)事物都可能出錯(cuò)，從而產(chǎn)生一些重復(fù)、錯(cuò)誤和無(wú)效的數(shù)據(jù)。由于物流系統(tǒng)非常龐大涉及全國(guó)的物流運(yùn)維，每小時(shí)的有效出庫(kù)單據(jù)量達(dá)到上萬(wàn)單。其中產(chǎn)生的臟數(shù)據(jù)量非常龐大，因此需要數(shù)據(jù)清洗。數(shù)據(jù)安全分為數(shù)據(jù)本身的安全和數(shù)據(jù)防護(hù)的安全。數(shù)據(jù)本身安全是指數(shù)據(jù)保密性、數(shù)據(jù)完整性、數(shù)據(jù)一致性等保護(hù)。數(shù)據(jù)防護(hù)的安全是指使用一些技術(shù)手段對(duì)數(shù)據(jù)進(jìn)行防護(hù)，比如數(shù)據(jù)備份、數(shù)據(jù)容災(zāi)等手段保證數(shù)據(jù)的安全。

2.3 物流出庫(kù)數(shù)據(jù)劃分

物流出庫(kù)表中的數(shù)據(jù)自從物流系統(tǒng)成立20年以來(lái)一直未做處理，表中已經(jīng)具有10億左右條數(shù)據(jù)，并且隨著時(shí)間的推移一直在增長(zhǎng)。在物流出庫(kù)業(yè)務(wù)高峰期，每小時(shí)的出庫(kù)單據(jù)量可以達(dá)到1萬(wàn)單以上。在這龐大的數(shù)據(jù)量中，為提高SQL的查詢(xún)效率，提高系統(tǒng)的運(yùn)營(yíng)能力，首先需要對(duì)10億數(shù)據(jù)進(jìn)行劃分。

2.4 存儲(chǔ)設(shè)備分配

存儲(chǔ)分配，即冷溫?zé)釘?shù)據(jù)的存儲(chǔ)歸屬。將經(jīng)常訪問(wèn)的數(shù)據(jù)（熱數(shù)據(jù)）存儲(chǔ)在快速存儲(chǔ)器上，不經(jīng)常訪問(wèn)的數(shù)據(jù)（溫?cái)?shù)據(jù)）存儲(chǔ)在速度稍慢的存儲(chǔ)器上，極少訪問(wèn)的數(shù)據(jù)（冷數(shù)據(jù)）存儲(chǔ)在速度很慢且比較便宜的存儲(chǔ)器上。如果熱數(shù)據(jù)變冷并且不經(jīng)常訪問(wèn)，那么可通過(guò)動(dòng)態(tài)方式將其移至速度較慢的存儲(chǔ)器。

3 SQL查詢(xún)優(yōu)化方案設(shè)計(jì)

3.1 優(yōu)化方案總體設(shè)計(jì)

通過(guò)存儲(chǔ)分配、熱力度處理等操作對(duì)系統(tǒng)進(jìn)行優(yōu)化，如圖1所示。

3.2 物流出庫(kù)表數(shù)據(jù)冷溫?zé)釀澐?/p>

首先按照月份年份把物流出庫(kù)單主表劃分成當(dāng)月、當(dāng)年非當(dāng)月和非當(dāng)年三類(lèi)數(shù)據(jù)。根據(jù)業(yè)務(wù)關(guān)系，每月初是前月對(duì)賬結(jié)轉(zhuǎn)時(shí)間，對(duì)賬結(jié)轉(zhuǎn)代表前月數(shù)據(jù)已經(jīng)進(jìn)入查詢(xún)和少量更改的操作，因此物流出庫(kù)單主表中數(shù)據(jù)可以做以下定義：定義當(dāng)月數(shù)據(jù)為熱數(shù)據(jù)；當(dāng)年非當(dāng)月數(shù)據(jù)為溫?cái)?shù)據(jù)；非當(dāng)年數(shù)據(jù)為冷數(shù)據(jù)。

3.3 熱數(shù)據(jù)劃分

對(duì)數(shù)據(jù)整體進(jìn)行劃分后，其中熱數(shù)據(jù)部分進(jìn)行進(jìn)一步劃分。首先，熱數(shù)據(jù)從0到9共分為10個(gè)等級(jí)，0最低9最高。每月結(jié)轉(zhuǎn)時(shí)，該月的數(shù)據(jù)全部設(shè)定為N。每月28至31天不等，每個(gè)等級(jí)固定對(duì)應(yīng)3天。每次SQL檢索范圍縮小為0至3天的數(shù)據(jù)量，有效地提升了SQL的效率。熱力度的數(shù)學(xué)模型如下，S代表分區(qū)表PSBILL_OUT_H的整體優(yōu)化查詢(xún)系統(tǒng)。系統(tǒng)中有兩種分區(qū)方式，一種是時(shí)間分區(qū)，另一種是數(shù)據(jù)熱力度的分區(qū)。S={To，Tn，Po，Pn，HD}，其中：

To：結(jié)轉(zhuǎn)后數(shù)據(jù)，按照每月一個(gè)分區(qū)表設(shè)計(jì)。

Tn：未結(jié)轉(zhuǎn)的本月數(shù)據(jù)，按照熱力度等級(jí)分布在各熱力分區(qū)上。

Po：為結(jié)轉(zhuǎn)數(shù)據(jù)分區(qū)，其命名格式為PSBILL_OUT_HYYMM，如PSBILL_OUT_H1701上存放2017年01月數(shù)據(jù)，其中PSBILL_OUT_H18MX為特殊分區(qū)，防止生產(chǎn)停止所設(shè)額外預(yù)留分區(qū)。

Pn：為未結(jié)轉(zhuǎn)本月數(shù)據(jù)分區(qū)，其命名格式為PSBILL_OUT_HN，如PSBILL_OUT_H2存放熱力度為2的數(shù)據(jù)，其中N滿(mǎn)足HDn函數(shù)。

HD：未結(jié)轉(zhuǎn)數(shù)據(jù)熱力度標(biāo)記，由于物流出庫(kù)單單據(jù)的熱力度由時(shí)間決定，因此離當(dāng)前時(shí)間sysdate越近則熱力度越高，所以其滿(mǎn)足函數(shù)

[HDn=0≤sysdate-TS-3*n≤2，其中n∈0，9null，月結(jié)時(shí)統(tǒng)一至為null并且n=N]

根據(jù)HDn函數(shù)，可以把函數(shù)轉(zhuǎn)換成熱數(shù)據(jù)圖靈機(jī)，如圖2所示。根據(jù)物流業(yè)務(wù)特點(diǎn)，圖靈機(jī)每日深夜執(zhí)行。根據(jù)數(shù)學(xué)模型，如果不考慮業(yè)務(wù)對(duì)數(shù)據(jù)分布形態(tài)的影響，那么對(duì)于物流出庫(kù)表中每一條數(shù)據(jù)查詢(xún)的復(fù)雜度為n/2，即每條數(shù)據(jù)的平均查詢(xún)次數(shù)為5次。而查詢(xún)一條數(shù)據(jù)的時(shí)間和數(shù)據(jù)掃描的范圍是成指數(shù)關(guān)系。假設(shè)查詢(xún)范圍為X萬(wàn)條數(shù)據(jù)，查詢(xún)一條數(shù)據(jù)所消耗的時(shí)間為T(mén)，可以得到公式T=A[X]，A>1決定時(shí)間的增長(zhǎng)基數(shù)。隨著時(shí)間的推移，T將變成原來(lái)的1/n，數(shù)據(jù)的查詢(xún)效率得到極大的提高。

3.4 物流出庫(kù)表詳細(xì)設(shè)計(jì)圖

通過(guò)熱數(shù)據(jù)的劃分，結(jié)合數(shù)據(jù)模型和圖靈機(jī)，物流出庫(kù)表主表的詳細(xì)設(shè)計(jì)如圖3所示。字段HD（熱力標(biāo)志）表示熱力度等級(jí)，當(dāng)月的熱力度分區(qū)共有11個(gè)分區(qū)，其中HD0至HD9用于劃分好的熱數(shù)據(jù)使用，HDN在月末給月結(jié)使用，并且月結(jié)標(biāo)志著數(shù)據(jù)從熱數(shù)據(jù)轉(zhuǎn)為溫?cái)?shù)據(jù)。HDN對(duì)應(yīng)的表空間存放于相對(duì)于SSD閃存比較緩慢的FC/SAS RAID存儲(chǔ)陣列。

4 總結(jié)

本課題論文的工作內(nèi)容是具體研究了一個(gè)物流系統(tǒng)出庫(kù)表在Oracle數(shù)據(jù)庫(kù)中的SQL查詢(xún)優(yōu)化方案的制定與實(shí)現(xiàn)。期望可以通過(guò)大數(shù)據(jù)劃分可以減少物理I/O的技術(shù)，基于Oracle數(shù)據(jù)庫(kù)的Partition對(duì)于數(shù)據(jù)塊控制的管理辦法，以熱數(shù)據(jù)的熱力度劃分為核心，完成對(duì)SQL查詢(xún)的優(yōu)化。本文課題與課題設(shè)計(jì)出的優(yōu)化方案以及實(shí)現(xiàn)邏輯，在大多數(shù)企業(yè)的生產(chǎn)應(yīng)用具有一定的指導(dǎo)意義，特別是對(duì)已經(jīng)運(yùn)行很長(zhǎng)時(shí)間的系統(tǒng)有明確的指導(dǎo)方向。

參考文獻(xiàn)：

[1]D. Li， L. Han and Y. Ding.SQL Query Optimization Methods of Relation Database System[J].Computer Engineering and Applications （ICCEA）， 2010 ，1 （1） ：557-560.

[2]S. Chande and M. Sinha.Genetic optimization for the join ordering problem of database Queries[J]. India Conference （INDICON）， 2012：1-5.

[3]S. Bellamokanda， R. Ahmand and A. Witkowski.Enhanced Subquery Optimizations in oracle[J]. Proceeding of the VLDB Endowment， 2009，2 （2） ：1366-1377.

[4]F. Sun and L. Wing.Paging Query Optimization of Massive Data in Oracle 10g Database[J]. Computer and Information Science and Service System （CSSS）， IEEE International Conference， 2011，28 （9） ：2388-2391.

[5]A. Hameurlain.Evolution of Query Optimization Methods： From Centralized Database Systems to Data Grid Systems[C]. Proceedings of the 20th International Conference on Database and Expert Systems Applications， 2009， 5690 ：460-470.

[6]H. Herodotou， N. Borisov and S. Babu.Query Optimization Techniques for Partitioned Tables[C]. ACM SIGMOD International Conference on Management of data， 2011，10 （4） ：49-60.

[7]張師超.大數(shù)據(jù)RD分割方法.中華人民共和國(guó)，知識(shí)產(chǎn)權(quán)類(lèi)，G06F，2013.10.02.

[8]Maksym Petrenko，Mike Winer and Joyce Coleman.Best Practices Multi-temperature Data Management[J].IBM? Smart Analytics System，2011：4-5.

[9]Guy Harrison.Oracle性能優(yōu)化求生指南[M].北京：人民郵電出版社，2012：56-72.

【通聯(lián)編輯：梁書(shū)】