物料管理系統數據查詢優化技術的研究

楊盛嵐 桂林電子科技大學

引言

一個物料管理系統，在面對大量的數據時，如何高效的存取數據是一個非常巨大的挑戰。隨著數據量的增大，如果不注重數據查詢的優化，將會嚴重得影響到系統的運行效率及用戶使用的體驗，因此，數據的查詢優化在管理系統的開發和維護過程中，都是非常重要的一部分。

1 研究對象

物料管理系統部署在服務器上，同時我們在客戶端進行系統的操作從而進行測試，我們對其型號、CPU、內存、硬盤等硬件環境和運行物料管理系統的軟件和版本分別進行了說明，如表1-1和表1-2 所示。

表1-2 客戶端環境

2 查詢效率的影響因素

很多因素都會影響到數據查詢效率，總體上看主要涉及到物理層、應用層和數據庫層相關的信息。在物理層，內存，服務器性能和操作系統性能都會對數據查詢效率產生影響。在數據庫層，索引、數據存儲等因素和查詢效率存在密切關系。在應用層，查詢程序和SQL 語句是主要的影響因素。

3 查詢效率的優化技術

3.1 物理層優化策略

物理層的優化方案，主要體現在硬件方面的優化，可采用以下方法：

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

圖3-1 服務器數據查詢過程

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

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

3.2 數據庫層優化策略

數據庫層的優化方法主要從數據庫表結構的設計上出發，可以采用以下優化方法：

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

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

3.3 應用層優化策略

這種優化具體表現為優化查詢語句和程序。因為物料系統中的數據展示，多是展示物料的統計數據，即對于出庫入庫日志數據進行聚合統計展示，這就涉及到大量數據的多表查詢策略。

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

在經過實驗和數據分析后，本文歸納總結了一個優化算法，本算法通過邏輯判斷精確地找到數據所在的表,使每次查詢只需要查詢一張表，算法流程圖如圖3-2 所示。以下是優化算法的步驟：

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

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

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

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

(5）創建一個集合Set。

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

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

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

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

圖3-2 算法流程圖

4 結語

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