楊盛嵐 桂林電子科技大學

引言

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

1 研究對象

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

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

2 查詢效率的影響因素

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

圖3-2 算法流程圖

4 結語

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