合理創(chuàng)建索引 實(shí)現(xiàn)數(shù)據(jù)庫訪問的優(yōu)化

趙愛濤

（石家莊職業(yè)技術(shù)學(xué)院 信息工程系，河北 石家莊 050081）

數(shù)據(jù)庫技術(shù)在計(jì)算機(jī)應(yīng)用系統(tǒng)開發(fā)中占有重要的地位.數(shù)據(jù)查詢的效率直接影響著應(yīng)用系統(tǒng)的執(zhí)行效率.索引是對數(shù)據(jù)庫表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)，利用索引可快速訪問數(shù)據(jù)庫表中的特定信息，提高數(shù)據(jù)庫的查詢效率，從而大幅度提高系統(tǒng)的性能［1］.在數(shù)據(jù)庫中，索引分為聚簇索引和非聚簇索引.聚簇索引以數(shù)據(jù)存放的物理位置為順序，在一個(gè)數(shù)據(jù)庫表中只能建立一個(gè)聚簇索引.對于非聚簇索引，它所記錄的邏輯順序與物理順序間沒有必然的聯(lián)系，索引表中的記錄順序通常與實(shí)際記錄的物理存儲順序不一致，在一個(gè)數(shù)據(jù)庫表中可以建立多個(gè)非聚簇索引.在數(shù)據(jù)庫設(shè)計(jì)中，優(yōu)化與提高數(shù)據(jù)的查詢效率有很多方面［2］，本文從索引的創(chuàng)建方面測試、分析、總結(jié)并優(yōu)化數(shù)據(jù)查詢的方法.

1 數(shù)據(jù)庫的測試環(huán)境

數(shù)據(jù)庫服務(wù)器硬件配置如下：

CPU為Inter（R）Core（TM）2Duo CPU e7400＠2.8GHz.

內(nèi)存為2GB.

操作系統(tǒng)為Windows Server 2003.

數(shù)據(jù)庫管理系統(tǒng)為Microsoft SQL Server 2008.

2 不同索引方案的查詢測試

測試使用某系統(tǒng)中的“產(chǎn)品訂貨表”，該表已存儲10 000 000條記錄，結(jié)構(gòu)如表1所示.

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

在系統(tǒng)的應(yīng)用中，經(jīng)常會用到以下三種查詢語句：

本文設(shè)計(jì)了四種索引的建立方案，分別是：

方案P1，僅為id列設(shè)置主關(guān)鍵字；

方案P2，在orderdate上設(shè)計(jì)一個(gè)聚簇索引；

方案P3，在place，orderdate，amount上設(shè)計(jì)組合索引；

方案P4，在orderdate，place，amount上設(shè)計(jì)組合索引.

對于每個(gè)方案，分別測試上述三條查詢語句的執(zhí)行時(shí)間，每個(gè)方案的每條語句各執(zhí)行三次，并計(jì)算各方案中語句執(zhí)行的平均時(shí)間T（1，2，3）＝［（t1＋t2＋t3）／3］，時(shí)間的單位為 ms.測試結(jié)果見表2－表5.

表2 方案P1查詢執(zhí)行時(shí)間

表3 方案P2查詢執(zhí)行時(shí)間

表4 方案P3查詢執(zhí)行時(shí)間

表5 方案P4查詢執(zhí)行時(shí)間

3 測試分析

通過前文的測試，得到表6所示綜合分析表和圖1所示綜合分析圖.

表6 綜合分析表

圖1 綜合分析圖

在方案P1中只建立了主鍵索引，而在三個(gè)查詢句中都沒有涉及到主鍵id列，因此，利用方案P1查詢數(shù)據(jù)時(shí)并沒有利用到索引；方案P2在orderdate列上建立了一個(gè)聚簇索引，記錄數(shù)據(jù)在物理上是按順序存放的，因此，在查找時(shí)，可以先找到這個(gè)范圍的起止點(diǎn)，并只在此范圍內(nèi)掃描數(shù)據(jù)，這就避免了大范圍掃描，從而提高了數(shù)據(jù)的查詢效率；方案P3是建立在place，orderdate，amount上的組合索引，其前導(dǎo)列是place，但是語句L1和語句L2都沒有引用place，在執(zhí)行時(shí)并沒有利用到索引，而語句L3引用了place，并且引用的所有列都包含在組合索引中，形成了索引覆蓋，因此它的執(zhí)行效率得到了明顯提升；方案P4是建立在orderdate，place，amount上的組合索引，它將orderdate作為前導(dǎo)列，使三個(gè)語句都可以利用索引，并且在L1和L3中形成了索引覆蓋，因而性能達(dá)到了最優(yōu).

通過以上的測試與分析可知，在數(shù)據(jù)庫的設(shè)計(jì)中，索引的設(shè)計(jì)應(yīng)建立在對各種查詢的分析和預(yù)測上.一般來說應(yīng)注意以下幾個(gè)方面：（1）有大量重復(fù)值且經(jīng)常有范圍查詢（between，？＞，＜ ？，＞ ＝，＜ ？＝）和order by，group by發(fā)生的列，可建立聚簇索引.（2）經(jīng)常同時(shí)存取多列，且每列都含有重復(fù)值時(shí)可考慮建立組合索引.（3）組合索引要盡量使關(guān)鍵查詢形成索引覆蓋，其前導(dǎo)列一定是使用最頻繁的列.（4）聚簇索引要建立在查詢時(shí)最頻繁引用的、最需要排序的字段之上.

4 結(jié)束語

創(chuàng)建索引合理，可以有效地提高數(shù)據(jù)庫的查詢效率.在一個(gè)數(shù)據(jù)表中雖然可以創(chuàng)建多個(gè)索引，但過多的索引會降低系統(tǒng)的維護(hù)效率，增大數(shù)據(jù)的空間需求，降低系統(tǒng)的修改性能［3］.在實(shí)際應(yīng)用時(shí)，應(yīng)根據(jù)系統(tǒng)的實(shí)際情況，權(quán)衡修改性能和檢索性能的需求，選用合理的索引策略.

［1］孟英杰，曲晶晶.數(shù)據(jù)庫查詢優(yōu)化技術(shù)的研究 ［J］.才智，2011，（25）：72.

［2］高愛梅.淺談數(shù)據(jù)庫查詢優(yōu)化 ［J］.內(nèi)蒙古科技與經(jīng)濟(jì)，2010，（20）：128.

［3］朱東.基于SQL Server的查詢優(yōu)化 ［J］.信息與電腦：理論版，2011，（11）：178.