物料管理系統(tǒng)數(shù)據(jù)查詢優(yōu)化技術(shù)的研究

楊盛嵐 桂林電子科技大學(xué)

引言

一個(gè)物料管理系統(tǒng)，在面對(duì)大量的數(shù)據(jù)時(shí)，如何高效的存取數(shù)據(jù)是一個(gè)非常巨大的挑戰(zhàn)。隨著數(shù)據(jù)量的增大，如果不注重?cái)?shù)據(jù)查詢的優(yōu)化，將會(huì)嚴(yán)重得影響到系統(tǒng)的運(yùn)行效率及用戶使用的體驗(yàn)，因此，數(shù)據(jù)的查詢優(yōu)化在管理系統(tǒng)的開發(fā)和維護(hù)過程中，都是非常重要的一部分。

1 研究對(duì)象

物料管理系統(tǒng)部署在服務(wù)器上，同時(shí)我們?cè)诳蛻舳诉M(jìn)行系統(tǒng)的操作從而進(jìn)行測試，我們對(duì)其型號(hào)、CPU、內(nèi)存、硬盤等硬件環(huán)境和運(yùn)行物料管理系統(tǒng)的軟件和版本分別進(jìn)行了說明，如表1-1和表1-2 所示。

表1-2 客戶端環(huán)境

2 查詢效率的影響因素

很多因素都會(huì)影響到數(shù)據(jù)查詢效率，總體上看主要涉及到物理層、應(yīng)用層和數(shù)據(jù)庫層相關(guān)的信息。在物理層，內(nèi)存，服務(wù)器性能和操作系統(tǒng)性能都會(huì)對(duì)數(shù)據(jù)查詢效率產(chǎn)生影響。在數(shù)據(jù)庫層，索引、數(shù)據(jù)存儲(chǔ)等因素和查詢效率存在密切關(guān)系。在應(yīng)用層，查詢程序和SQL 語句是主要的影響因素。

3 查詢效率的優(yōu)化技術(shù)

3.1 物理層優(yōu)化策略

物理層的優(yōu)化方案，主要體現(xiàn)在硬件方面的優(yōu)化，可采用以下方法：

(1）提高服務(wù)器內(nèi)存：服務(wù)器內(nèi)存是直接關(guān)系到程序運(yùn)行速度的因素，而數(shù)據(jù)的查詢期間需要進(jìn)行預(yù)讀取、物理讀取相關(guān)的操作，過程如圖3-1 所示。預(yù)讀取主要是在生成相應(yīng)的數(shù)據(jù)查詢計(jì)劃期間，通過估計(jì)的信息來讀取硬盤中的數(shù)據(jù)；物理讀取則是在建立起相應(yīng)的查詢計(jì)劃基礎(chǔ)上，如果緩存中相關(guān)的數(shù)據(jù)信息缺失，則將數(shù)據(jù)讀入到緩存中；邏輯讀取即直接從緩存中讀取數(shù)據(jù)?？v觀整個(gè)過程，我們可以看出：緩存的容量是整個(gè)數(shù)據(jù)讀取過程的關(guān)鍵。我們知道，在數(shù)據(jù)讀取過程中，對(duì)比分析可知從緩存中讀取數(shù)據(jù)的速度顯著高于從硬盤中的。所以緩存容量越大，數(shù)據(jù)從硬盤中讀取的次數(shù)就會(huì)減少，從而使數(shù)據(jù)讀取的速度更快。

圖3-1 服務(wù)器數(shù)據(jù)查詢過程

(2）提升處理器性能：CPU功能可對(duì)計(jì)算指令進(jìn)行分解和執(zhí)行，據(jù)此可看出服務(wù)器的性能很大程度上由CPU 性能決定，我們可以在預(yù)算允許的情況下為服務(wù)器配置更高性能的CPU。

(3）減少建立數(shù)據(jù)庫連接代價(jià)：可以通過使用連接池，來減少建立數(shù)據(jù)庫連接的消耗。當(dāng)進(jìn)行一次數(shù)據(jù)查詢時(shí)，必須經(jīng)過建立數(shù)據(jù)庫連接、打開數(shù)據(jù)庫、讀取數(shù)據(jù)庫、關(guān)閉數(shù)據(jù)庫等一系列操作，既耗費(fèi)時(shí)間又耗費(fèi)服務(wù)器資源，如果每次都要經(jīng)過這些步驟，這對(duì)系統(tǒng)性能也會(huì)產(chǎn)生明顯的影響。使用數(shù)據(jù)庫連接池則可有效的避免此種問題。這種連接池在處理過程中建立了緩沖池進(jìn)行數(shù)據(jù)庫連接，在處理過程中需要先在其中設(shè)置相應(yīng)的連接，在需要進(jìn)行數(shù)據(jù)庫連接情況下，單純的從“緩沖池”中取出，結(jié)束后則放回。在此操作過程中，為避免無限的數(shù)據(jù)庫連接問題出現(xiàn)，則應(yīng)該確定出相應(yīng)的最大連接數(shù)。

3.2 數(shù)據(jù)庫層優(yōu)化策略

數(shù)據(jù)庫層的優(yōu)化方法主要從數(shù)據(jù)庫表結(jié)構(gòu)的設(shè)計(jì)上出發(fā)，可以采用以下優(yōu)化方法：

(1）為數(shù)據(jù)庫表建立索引：數(shù)據(jù)庫中的數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)和索引密切相關(guān)，可將其看作為數(shù)據(jù)庫表中的結(jié)構(gòu)排序。索引的優(yōu)勢(shì)表現(xiàn)為可有效的提高數(shù)據(jù)庫的查詢速度。數(shù)據(jù)量保持一致條件下，通過實(shí)驗(yàn)測試在主鍵和非主鍵字段上建立聚集索引和非聚集索引的查詢速度，討論在主鍵和非主鍵字段上建立索引的原則。以查詢某廳倉下所有倉庫的所有物資出入庫記錄為例，近一年內(nèi)的記錄數(shù)據(jù)量約540 萬條。在主鍵id上建立聚集索引，查詢近一年的出入庫記錄，用時(shí)2129 毫秒。而在created_at 字段上添加非聚集索引之后，查詢相同時(shí)間段的數(shù)據(jù)，用時(shí)1208 毫秒。用時(shí)縮減了將近50%，因此，選取伙食的字段簡歷索引，將會(huì)對(duì)數(shù)據(jù)查詢速度產(chǎn)生很大的影響。

（2）適當(dāng)增加數(shù)據(jù)冗余。為更好的滿足數(shù)據(jù)處理性能要求，需要數(shù)據(jù)表中不會(huì)出現(xiàn)數(shù)據(jù)冗余的現(xiàn)象。不過一些情況下需要降低范式標(biāo)準(zhǔn)，設(shè)置一定量的數(shù)據(jù)冗余，這樣可提高查詢效率。具體分析可知數(shù)據(jù)冗余包括字段、表冗余。前者也就是設(shè)置冗余字段，降低相應(yīng)的數(shù)據(jù)計(jì)算比例。比如可從身份證號(hào)中獲取用戶的身份信息，不過為滿足查詢效率相關(guān)的要求，也應(yīng)該設(shè)置性別和出生日期相關(guān)信息。表冗余則基于增加冗余表而進(jìn)行高效查詢。比如對(duì)出入庫日志表，想要獲取長時(shí)間段內(nèi)的出入庫數(shù)據(jù)并計(jì)算展示最后剩余物料的數(shù)量，這個(gè)表中只有物料的id 號(hào)而并沒有物料名稱等相關(guān)信息，當(dāng)我們?cè)谶叢樵兒陀?jì)算大量的日志，邊匹配物料信息時(shí)，查詢效率就會(huì)降低。此時(shí)，可以考慮在該表中將物料名稱字段冗余，這樣就能大大降低查詢時(shí)的耗時(shí)。

3.3 應(yīng)用層優(yōu)化策略

這種優(yōu)化具體表現(xiàn)為優(yōu)化查詢語句和程序。因?yàn)槲锪舷到y(tǒng)中的數(shù)據(jù)展示，多是展示物料的統(tǒng)計(jì)數(shù)據(jù)，即對(duì)于出庫入庫日志數(shù)據(jù)進(jìn)行聚合統(tǒng)計(jì)展示，這就涉及到大量數(shù)據(jù)的多表查詢策略。

對(duì)于mysql 數(shù)據(jù)庫多表查詢，我們通常會(huì)使用union 方法來進(jìn)行處理：首先根據(jù)查詢條件掃描全部數(shù)據(jù)表，確定出滿足要求的記錄，接著基于union 合成一張臨時(shí)表，此表為滿足要求的數(shù)據(jù)，再在這張臨時(shí)表的基礎(chǔ)上做查詢和聚合統(tǒng)計(jì)。在這個(gè)查詢過程中，時(shí)間主要消耗在了掃描全表和union 操作上，那么我們可以從這兩個(gè)方向下手，即減少分表掃描的個(gè)數(shù)，同時(shí)查詢的速度也提高。

在經(jīng)過實(shí)驗(yàn)和數(shù)據(jù)分析后，本文歸納總結(jié)了一個(gè)優(yōu)化算法，本算法通過邏輯判斷精確地找到數(shù)據(jù)所在的表,使每次查詢只需要查詢一張表，算法流程圖如圖3-2 所示。以下是優(yōu)化算法的步驟：

(1）根據(jù)客戶端給出查詢條件得出查詢所涉及的表為t1、t2、t3。

(2）根據(jù)查詢條件從各個(gè)表中查詢出符合條件的記錄數(shù)，記為c1、c2、c3，并且求出三個(gè)表中符合條件的記錄總數(shù)為total =c1+c2+c3。

(3）假設(shè)頁面大小為pageSize，可求出總頁碼數(shù)量pageCount = total / pageSize.

(4）根據(jù)客戶端傳過來的頁碼數(shù)page 求出偏移量offset=(page-1)*pageSize。

(5）創(chuàng)建一個(gè)集合Set。

(6）如果offset + pageSize＜= c1，則結(jié)果集全部落在t1 中，然后查詢t1將所有符合條件的數(shù)據(jù)存入Set 中。否則繼續(xù)（7）步驟。

(7) 如果offset＜c1且offset+pageSize＞c1 且offset+pageSize＜c1+c2，結(jié)果集部分落在t1 中部分落在t2 中，查詢t1,t2 將符合條件的數(shù)據(jù)存入結(jié)果集Set 中。否則繼續(xù)（8）步驟。

(8）如果offset＜count1 且offset+pageSize＞c1+c2，結(jié)果集分別落在 t1,t2,t3 中，查詢t1,t2,t3 將符合條件的數(shù)據(jù)存入結(jié)果集Set 中。否則繼續(xù)（9）步驟。

(12）最后返回總頁數(shù)pageCount 和數(shù)據(jù)集Set.

圖3-2 算法流程圖

4 結(jié)語

信息爆炸式地增長，給我們系統(tǒng)數(shù)據(jù)查詢處理帶來了更多的挑戰(zhàn)，我們需要關(guān)注信息數(shù)據(jù)處理的方式及優(yōu)化，并且更加深入研究數(shù)據(jù)查詢的相關(guān)技術(shù)，這樣可提高系統(tǒng)的快速響應(yīng)性能，用戶的滿意度提升。本文從物理層、數(shù)據(jù)庫層等角度對(duì)數(shù)據(jù)查詢效率的影響因素進(jìn)行具體分析，在此基礎(chǔ)上建立了一個(gè)在應(yīng)用層方面的合理的優(yōu)化算法，使得數(shù)據(jù)庫查詢效率大大提高。相關(guān)的優(yōu)化方法還有很多，我們還需要在保證系統(tǒng)正常運(yùn)行的同時(shí)不斷優(yōu)化，使用戶獲得更佳的體驗(yàn)。