倉儲管理系統(tǒng)的數(shù)據(jù)庫查詢優(yōu)化策略研究

潘蕾

（中國藥科大學(xué)信息管理教研室，江蘇南京211198）

倉儲管理系統(tǒng)的數(shù)據(jù)庫查詢優(yōu)化策略研究

潘蕾

（中國藥科大學(xué)信息管理教研室，江蘇南京211198）

本文針對以M ySQL建立的企業(yè)倉儲管理系統(tǒng)數(shù)據(jù)庫樣例，以提高數(shù)據(jù)庫查詢語句執(zhí)行效率為目的，進(jìn)行了典型查詢優(yōu)化策略研究，并對查詢操作進(jìn)行多角度的優(yōu)化嘗試，對優(yōu)化前后的查詢執(zhí)行效果進(jìn)行了測試，希望通過對各項(xiàng)關(guān)鍵技術(shù)進(jìn)行整合，設(shè)計(jì)和實(shí)現(xiàn)一個(gè)基于M ySQL的通用數(shù)據(jù)庫查詢優(yōu)化的模型框架.

數(shù)據(jù)庫；查詢優(yōu)化；M ySQL

數(shù)據(jù)庫技術(shù)自上世紀(jì)60年代誕生以來，經(jīng)歷了數(shù)十年的發(fā)展目前已經(jīng)成為現(xiàn)代計(jì)算機(jī)系統(tǒng)的核心技術(shù)之一.通常情況下，數(shù)據(jù)庫中的數(shù)據(jù)經(jīng)過一段時(shí)間的積累會達(dá)到一定的數(shù)據(jù)存量，比如一個(gè)企業(yè)的倉儲管理系統(tǒng)后臺數(shù)據(jù)庫累計(jì)到數(shù)百萬條甚至更多數(shù)據(jù)時(shí)，執(zhí)行查詢操作若進(jìn)行一次全表掃描往往需要數(shù)分鐘，甚至更久.此時(shí)必須采取一定的查詢優(yōu)化策略，取代效率低下的全表掃描工作，降低查詢操作的執(zhí)行時(shí)間.

目前的數(shù)據(jù)庫查詢優(yōu)化策略主要分布在兩個(gè)環(huán)節(jié)：物理層優(yōu)化和邏輯層優(yōu)化.其中涉及到了數(shù)據(jù)庫的物理結(jié)構(gòu)設(shè)計(jì)、體系結(jié)構(gòu)設(shè)計(jì)、邏輯設(shè)計(jì)以及數(shù)據(jù)庫管理系統(tǒng)設(shè)計(jì)等多個(gè)方面[1].而針對具體的數(shù)據(jù)庫應(yīng)用系統(tǒng)查詢優(yōu)化實(shí)現(xiàn)方案主要從以下兩個(gè)環(huán)節(jié)展開：一是借助于數(shù)據(jù)庫管理系統(tǒng)現(xiàn)有的查詢優(yōu)化器合理完善計(jì)算機(jī)系統(tǒng)的查詢功能，提高查詢效率，查詢設(shè)計(jì)器針對整個(gè)系統(tǒng)的開發(fā)流程，常規(guī)性的從數(shù)據(jù)庫設(shè)計(jì)、系統(tǒng)實(shí)現(xiàn)等多個(gè)方面考慮查詢效率；二是基于SQL查詢語句的各類查詢優(yōu)化，如合理建立和使用索引、避免或簡化排序、消除對大型表行數(shù)據(jù)的順序存、避免相關(guān)子查詢、避免困難的正規(guī)表達(dá)式、使用臨時(shí)表加速查詢、用排序來取代非順序存取等.本文將以某企業(yè)倉儲管理系統(tǒng)為例，從數(shù)據(jù)庫設(shè)計(jì)和SQL查詢語句兩個(gè)方面著手，實(shí)現(xiàn)對數(shù)據(jù)庫進(jìn)行基本的查詢優(yōu)化的驗(yàn)證.

1 樣例數(shù)據(jù)庫的建立

本設(shè)計(jì)以某企業(yè)倉庫管理系統(tǒng)數(shù)據(jù)庫為例，為該數(shù)據(jù)庫總共設(shè)計(jì)了7張數(shù)據(jù)表，分別是：物資信息表(Material)、物資庫存信息表(Material_kc)、物資入庫登記表、(Material_rk_1)、物資入庫表(Material_rk_2)、物資出庫登記表(Material_ck_1)、物資出庫表(Material_ck_2)、倉儲信息表(Store)，圖1是各個(gè)數(shù)據(jù)表之間的關(guān)系圖.

圖1 數(shù)據(jù)庫結(jié)構(gòu)圖

2 數(shù)據(jù)庫查詢優(yōu)化及優(yōu)化測試

2.1 數(shù)據(jù)庫設(shè)計(jì)優(yōu)化

通過上文分析得知一個(gè)好的數(shù)據(jù)庫結(jié)構(gòu)可以大大的提高查詢效率，數(shù)據(jù)庫結(jié)構(gòu)的優(yōu)化是數(shù)據(jù)庫查詢優(yōu)化的基礎(chǔ).

2.1.1 合理的使用索引

在數(shù)據(jù)庫中為數(shù)據(jù)表建立索引的首要目的就是提高系統(tǒng)查詢性能，目前數(shù)據(jù)庫產(chǎn)品中最常用的索引結(jié)構(gòu)是由IBM最先提出的ISAM結(jié)構(gòu).在進(jìn)行索引的設(shè)計(jì)和使用時(shí)為達(dá)到良好的優(yōu)化效果，需遵循一定的設(shè)計(jì)原則，本設(shè)計(jì)中驗(yàn)證與測試的索引使用原則如下：

圖2 建立索引之前結(jié)果示意圖

（1）將索引建立在連接需求量大且沒有被定義為外鍵的字段上，而對于不經(jīng)常連接使用的字段則由查詢優(yōu)化器自動生成索引.以下的例子以MyE-clipse作為測試工具，測試在Material_rk_1表的stor_no字段上建立索引前后執(zhí)行查詢語句的時(shí)間及效果.測試中要查詢倉庫號=4的負(fù)責(zé)人號，并查詢該倉庫接收的入庫單號.在Material_rk_1表中共有原始數(shù)據(jù)12421條記錄，在Store表中有20條記錄.在stor_no字段上建立索引之前測試結(jié)果如圖2所示，由測試運(yùn)行結(jié)果可得在Material_rk_1表的Stor_no字段上建立索引之前，運(yùn)行程序后系統(tǒng)執(zhí)行SQL語句用了31ms.在stor_no字段上建立索引之后測試結(jié)果如圖3所示，由測試運(yùn)行結(jié)果可得在Material_rk_1表的Stor_no字段上建立索引之后，運(yùn)行程序后系統(tǒng)執(zhí)行SQL語句用了15ms.比較在Material_rk_1表的Stor_no字段上建立索引前后執(zhí)行代碼得到的結(jié)果，可以很清楚的看到，在建立索引之后的程序運(yùn)行時(shí)間比建立索引之前的程序運(yùn)行時(shí)間節(jié)省了16ms.因此可以知道在經(jīng)常進(jìn)行連接，但是沒有指定為外鍵的列上建立索引是可以節(jié)省查詢時(shí)間的.

（2）盡量不在不同值少的列上建立索引，在這樣的列上建立索引不但提高不了查詢速度，在面臨更新操作時(shí)將會嚴(yán)重降低運(yùn)行速度，這樣的索引毫無意義可言.例如在material_ck_1表的has_audited列上只有“1”和“0”兩個(gè)屬性值分別表示審核與否，在此列上如果人為建立了索引將不會產(chǎn)生優(yōu)化效果.以下通過測試在Material_ck_1表的has_audited列上建立索引前后的查詢執(zhí)行時(shí)間來證實(shí)這一點(diǎn).在Material_ck_1表中插入了17395條記錄.現(xiàn)在要查詢Material_ck_1表中已經(jīng)審核過的信息，在Material_ck_1表的has_audited字段上建立索引前后，執(zhí)行SQL語句的時(shí)間并沒有改變，都為31ms.因此可知在不同值少的列上建立索引對優(yōu)化查詢語句在本例中并沒有節(jié)省查詢時(shí)間，即該查詢優(yōu)化是無效的.

圖3 建立索引之后結(jié)果示意圖

（3）使用系統(tǒng)工具.隨著數(shù)據(jù)庫中的數(shù)據(jù)不斷擴(kuò)充與更新，之前建立的有效索引可能會出現(xiàn)因?yàn)樵L問操作頻繁而執(zhí)行效率降低或失效的情況.此時(shí)可以采用一些監(jiān)控ONLINE系統(tǒng)后動情況的工具來檢查索引是否有效，必要時(shí)刪除、修復(fù)或重建被損壞的索引.

2.2 SQL語句的優(yōu)化

建立了一個(gè)合理的數(shù)據(jù)庫結(jié)構(gòu)以后并不等于所有優(yōu)化工作都已完成.系統(tǒng)運(yùn)行時(shí)還有大量的查詢、統(tǒng)計(jì)要通過SQL查詢語句來實(shí)現(xiàn).

2.2.1 SELECT語句避免使用*號

*(星號)通常作為通配符用來實(shí)現(xiàn)模糊搜索.在SQL語句里也不例外，在我們檢索某些特定的數(shù)據(jù)時(shí)，我們不需要知道具體列的信息，*號就能起到它的作用了.*號主要用在以下兩個(gè)方面：

（1）列出指定數(shù)據(jù)表中的所有列的數(shù)據(jù).其語法是：SELECT*FROM表格名

（2）在Count中，獲得數(shù)據(jù)的行數(shù).其語法是：SELECT COUNT(*)FROM表格名

第二種應(yīng)用的情況是很常用到的，而第一種應(yīng)用，則需視情況而定.在設(shè)計(jì)查詢時(shí)，開發(fā)人員往往為了減少代碼的量，會使用*號代替表的所有字段，但這樣實(shí)現(xiàn)SQL查詢的結(jié)果一方面由于當(dāng)數(shù)據(jù)的列變動后，Select*的結(jié)果也會變動，可能有不可預(yù)見的情況出現(xiàn)；另一方面也是尤為重要的是這樣一來雖然減少了代碼，卻降低了查詢的執(zhí)行效率.數(shù)據(jù)庫服務(wù)器在解析SQL語句時(shí)，如果碰到*號，會首先分析數(shù)據(jù)表的結(jié)構(gòu)，然后把數(shù)據(jù)表的所有字段名再羅列出來，這樣就增加了系統(tǒng)查詢的時(shí)間.所以一般情況下，還是應(yīng)盡量列出列名清單.以MyEclipse作為測試工具，測試在Material表中執(zhí)行是否使用*號的查詢語句的時(shí)間及效果.測試中在Material表中插入22381條記錄，由測試結(jié)果得到使用*號查詢Material表信息用了62ms，而將字段名列出進(jìn)行查詢所用時(shí)間可忽略不計(jì).

2.2.2 避免困難的正則表達(dá)式

在編寫處理字符串的程序或網(wǎng)頁時(shí)，經(jīng)常會有查找符合某些復(fù)雜規(guī)則的字符串的需要.正則表達(dá)式就是用于描述這些規(guī)則的工具.和通配符類似，正則表達(dá)式也是用來進(jìn)行文本匹配的工具，比起通配符，正則表達(dá)式能更精確地描述用戶的需求.預(yù)定義的元字符是整個(gè)正則表達(dá)式的強(qiáng)大功能的基礎(chǔ)，它們的基本功能是利用符合基本規(guī)則的非常小的模式空間來表達(dá)模式.不同的系統(tǒng)實(shí)現(xiàn)了常用正則表達(dá)式的各種擴(kuò)展.在SQL語句中，形成正則表達(dá)式的基礎(chǔ)是采用LIKE和MATCHES等關(guān)鍵字實(shí)現(xiàn)通配符的匹配.但采用正則表達(dá)式的匹配在系統(tǒng)運(yùn)行時(shí)尤其損耗運(yùn)行時(shí)間.例如以下SQL查詢語句：

SELECT a.material_no,a.material_name,a. material_xh,a.material_manufacture,a.product_dw, a.material_price

FROM material

WHERE material_no LIKE"100_ _"

在本查詢實(shí)例中，即使設(shè)計(jì)數(shù)據(jù)表時(shí)在material_no字段上建立了索引，執(zhí)行查詢時(shí)仍然需要對全表進(jìn)行順序掃描.如果將其中的條件表達(dá)式修改為“WHERE material_no>=10000 and material_no<10100”，則執(zhí)行查詢請求時(shí)就會借助索引來完成，從而顯著提高查詢效率.測試在Material表中采用上述兩種方式實(shí)現(xiàn)查詢材料號以100開頭且是五位數(shù)的材料信息的執(zhí)行時(shí)間及效果.在Material中共有原始數(shù)據(jù)22831條記錄.由測試結(jié)果得到使用包含通配符的正則表達(dá)式進(jìn)行查詢所用時(shí)間為47ms，而采用條件查詢時(shí)，執(zhí)行查詢語句的時(shí)間可以忽略不計(jì).

2.2.3 使用臨時(shí)表

在數(shù)據(jù)庫的存儲過程中有時(shí)會建立一些臨時(shí)表，是為了處理方便，用于存儲一些中間處理結(jié)果，臨時(shí)表建立后，可與其它數(shù)據(jù)表同樣的使用，但與服務(wù)器的一次連接會話完成后，就會自動被系統(tǒng)清除.為了避免出現(xiàn)相對于同一組數(shù)據(jù)對象的多重排序，可以在查詢過程中將表的目標(biāo)子集進(jìn)行排序后的結(jié)果生成臨時(shí)表，并針對臨時(shí)表進(jìn)行下一輪查詢操作，從而提升查詢速度.在執(zhí)行SQL查詢語句時(shí)，按照SQL語句的解析順序，最先執(zhí)行的將會是From語句，如果要對多表進(jìn)行連接查詢時(shí)，我們可以把其中涉及到的單獨(dú)的數(shù)據(jù)表先查詢并排序出目標(biāo)臨時(shí)表結(jié)果，再去連接其它表.例如在該企業(yè)倉庫管理系統(tǒng)中有以下的功能需求：需查詢出庫信息還未審核的材料的信息和數(shù)量.該查詢涉及到Material表、Material_ck_1表、Material_ck_2表的三表連接查詢.一種實(shí)現(xiàn)方法是不采用臨時(shí)表直接采用From三表的連接查詢；另一種優(yōu)化查詢方法是在From語句中先從Material表、和Material_ck_1表中選出還未審核的出庫單號，建成臨時(shí)表，再與Material_ck_2表進(jìn)行連接獲取目標(biāo)數(shù)據(jù).以MyE-clipse作為測試工具，測試在Material表、Material_ck_1表、Material_ck_2表中采用上述兩種方式實(shí)現(xiàn)查詢出庫信息還未審核的材料的信息和數(shù)量的執(zhí)行時(shí)間及效果.在Material表中共有原始數(shù)據(jù)記錄22381條，Material_ck_1表中共有原始數(shù)據(jù)記錄17395條，Material_ck_2表中共有原始數(shù)據(jù)記錄20條.由測試結(jié)果得到使用首先建立臨時(shí)表進(jìn)行查詢所用時(shí)間為47ms，而不使用了臨時(shí)表的查詢策略系統(tǒng)會先查詢出還沒有審核的materialckd_no，再去與material_ck_2連接，如果只有1/5未審核，也就是說只有1/5的記錄需要連接，這樣一來就大大的節(jié)省了連接的時(shí)間.

3 結(jié)論

本文以倉儲管理系統(tǒng)樣例數(shù)據(jù)庫為對象，以提高數(shù)據(jù)庫查詢語句執(zhí)行效率為目的進(jìn)行了典型查詢優(yōu)化策略研究.解析了作為測試樣例的倉庫管理系統(tǒng)數(shù)據(jù)庫的結(jié)構(gòu)，深入分析了針對該數(shù)據(jù)庫采用的查詢語句的若干優(yōu)化方法并對優(yōu)化效果進(jìn)行了測試，通過測試總結(jié)出對提高查詢語句效率有用的方法.希望在今后的研究中能夠通過對各項(xiàng)關(guān)鍵技術(shù)進(jìn)行整合，設(shè)計(jì)和實(shí)現(xiàn)一個(gè)基于MySQL的通用數(shù)據(jù)庫查詢優(yōu)化的模型框架，從而節(jié)省各類資源，提高查詢效率.

〔1〕李昶,余立人.數(shù)據(jù)庫應(yīng)用系統(tǒng)性能與數(shù)據(jù)查詢優(yōu)化[J].現(xiàn)代計(jì)算機(jī)，2002（3）：14-19.

〔2〕周楊，余麗華，付紅偉.數(shù)據(jù)庫查詢優(yōu)化技術(shù)的研究與實(shí)現(xiàn)[J].軟件導(dǎo)刊，2011（9）：150-152.

〔3〕石劍平，蔡光程.SQLServer2005查詢優(yōu)化技術(shù)的研究與實(shí)現(xiàn)[J].信息系統(tǒng)工程，2010（5）：78-79.

〔4〕張德，董逸生.Internet上的數(shù)據(jù)庫聯(lián)合查詢優(yōu)化[J].計(jì)算機(jī)學(xué)報(bào)，2000（2）：171-176.

〔5〕何炎祥，陳燕濤，孫祥勝.OODBS中查詢優(yōu)化和查詢處理技術(shù)[J].計(jì)算機(jī)應(yīng)用，1995（2）：27-29

〔6〕魏銀珍，陳征兵.SQLServer數(shù)據(jù)庫的查詢優(yōu)化策略研究[J].電腦知識與技術(shù)，2011（10）：7085-7087.

〔7〕許新華，唐勝群，宋振云，萬學(xué)斌.并行數(shù)據(jù)庫查詢優(yōu)化技術(shù)的最新進(jìn)展[J].計(jì)算機(jī)工程與設(shè)計(jì)，2009,30(16)：3814-3819.

〔8〕徐麗萍，金雄兵，趙小松.并行數(shù)據(jù)庫查詢優(yōu)化技術(shù)研究[J].華中科技大學(xué)學(xué)報(bào)(自然科學(xué)版), 2006（3）：12-13，20.

TM933

A

1673-260X（2013）09-0122-03