實驗8數(shù)據(jù)庫存儲及效率

本章目錄

背景知識實驗示例實驗內(nèi)容與要求（選做）返回本章首頁背景知識返回本節(jié)首頁返回本節(jié)首頁

可以利用索引快速訪問數(shù)據(jù)庫表中的特定信息。索引是對數(shù)據(jù)庫表中一個或多個列(例如，employee表的姓氏(lname)列)的值進行排序的結(jié)構(gòu)。如果想按特定職員的姓來查找他或她，則與在表中搜索所有的行(因未建索引)相比，索引有助于更快地獲取信息。

索引可以簡單理解為是鍵值與鍵值關(guān)聯(lián)行的存取地址的一張表，而鍵值是根據(jù)指定的排序次序排列的。數(shù)據(jù)庫使用索引的方式與使用書的目錄很相似：通過搜索索引找到特定的鍵值，然后根據(jù)鍵值對應(yīng)的地址找到關(guān)聯(lián)行。返回本節(jié)首頁返回本節(jié)首頁

關(guān)于創(chuàng)建索引的建議如下：1、將更新盡可能多的行的查詢寫入單個語句內(nèi)，而不要使用多個查詢更新相同的行。僅使用一個語句，就可以利用優(yōu)化的索引維護。2、使用索引優(yōu)化向?qū)Х治霾樵儾@得索引建議。3、對聚集索引使用整型鍵。另外，在唯一列、非空列或IDENTITY列上創(chuàng)建聚集索引可以獲得性能收益。4、在查詢經(jīng)常用到的所有列上創(chuàng)建非聚集索引。這可以最大程度地利用隱蔽查詢。返回本節(jié)首頁5、物理創(chuàng)建索引所需的時間在很大程度上取決于磁盤子系統(tǒng)。需要考慮的重要因素包括：（1）用于存儲數(shù)據(jù)庫和事務(wù)日志文件的RAID(獨立磁盤冗余陣列)等級；（2）磁盤陣列中的磁盤數(shù)(如果使用了RAID)；（3）每個數(shù)據(jù)行的大小和每頁的行數(shù)。這將決定為創(chuàng)建索引須從磁盤讀取的數(shù)據(jù)頁的數(shù)目；（4）索引中的列數(shù)和使用的數(shù)據(jù)類型。這將決定必須寫入磁盤的索引頁的數(shù)目。6、檢查列的唯一性。7、在索引列中檢查數(shù)據(jù)分布。通常情況下，為包含很少唯一值的列創(chuàng)建索引或在這樣的列上執(zhí)行聯(lián)接將導(dǎo)致長時間運行的查詢。返回本節(jié)首頁實驗示例返回本節(jié)首頁1、創(chuàng)建表itbl,并插入4萬條記錄。

請在SQLServer集成管理器查詢子窗口中，選擇某用戶數(shù)據(jù)庫,執(zhí)行如下命令，生成80000條記錄，見圖8-1。CREATETABLEitbl(idbigintIDENTITY(1,1)NOTNULL,rqdatetimeNULL,srqvarchar(20)NULL,hhsmallintNULL,mmsmallintNULL,sssmallintNULL,numnumeric(12,3))declare@iintselect@i=1返回本節(jié)首頁while@i<=80000begininsertintoitbl(rq,srq,hh,mm,ss,num)values(getdate(),cast(getdate()asvarchar(20)),DATEPART(hh,getdate()),DATEPART(mi,getdate()),DATEPART(ss,getdate()),cast(rand(@i)*100asnumeric(12,3)))Select@i=@i+1End返回本節(jié)首頁圖8-1在表itbl中生成80000條記錄2、下面是測試命令執(zhí)行的代碼。運行時把“待測試命令”替換成你的測試命令，在查詢分析器中執(zhí)行后，能返回命令執(zhí)行的大致時間(單位為：毫秒)。declare@dt1datetimedeclare@iintdeclare@schar(40)declare@hm1intdeclare@hm2intselect@dt1=getdate()返回本節(jié)首頁select@hm1=DATEPART(hh,@dt1)*3600000+DATEPART(mi,@dt1)*60000+DATEPART(ss,@dt1)*1000+DATEPART(ms,@dt1)

“待測試命令”--此行將用測試命令替代select@dt1=getdate()select@hm2=DATEPART(hh,@dt1)*3600000+DATEPART(mi,@dt1)*60000+DATEPART(ss,@dt1)*1000+DATEPART(ms,@dt1)-@hm1select@s='time--'+convert(char(10),@hm2)RAISERROR(@s,16,1)返回本節(jié)首頁3、未建索引時：1)單記錄插入(約10毫秒，給出的毫秒數(shù)在特定環(huán)境下得出的，只作參考，下同)select@i=DATEPART(ms,@dt1)insertintoitbl(rq,srq,hh,mm,ss,num)values(getdate(),cast(getdate()asvarchar(20)),DATEPART(hh,getdate()),DATEPART(mi,getdate()),DATEPART(ss,getdate()),cast(rand(@i)*100asnumeric(12,3)))2)查詢所有記錄，按id排序(約2180毫秒)Select*fromitblorderbyid

把以上命令放在測試代碼段中，運行后得到運行時間2186毫秒，如圖8-2所示。返回本節(jié)首頁3)查詢所有記錄，按mm排序(約1960毫秒)Select*fromitblorderbymm

圖8-2按id升序查詢表itbl中的全部記錄返回本節(jié)首頁4、對itbl表id字段建非聚集索引1)建索引毫時(約330毫秒)CREATENONCLUSTEREDINDEXindexname1ONitbl(id)2)單記錄插入(約6毫秒)

插入命令同上“單記錄插入”3)查詢所有記錄，按id排序(約2690毫秒)Select*fromitblorderbyid4)查詢所有記錄，按mm排序(約2270毫秒)Select*fromitblorderbymm5)刪除索引(約95毫秒)dropindexitbl.indexname1返回本節(jié)首頁5、對itbl表mm字段建非聚集索引1)建索引毫時(約423毫秒)CREATENONCLUSTEREDINDEXindexname1ONitbl(mm)2)單記錄插入(約25毫秒)

插入命令同上“單記錄插入”3)查詢所有記錄，按id排序(約2700毫秒)Select*fromitblorderbyid4)查詢所有記錄，按mm排序(約1900毫秒)Select*fromitblorderbymm5)刪除索引(約48毫秒)dropindexitbl.indexname1返回本節(jié)首頁6、對itbl表id字段建聚集索引1)建索引毫時(約1400毫秒)CREATECLUSTEREDINDEXindexname1ONitbl(id)2)單記錄插入(約1毫秒)

插入命令同上“單記錄插入”3)查詢所有記錄，按id排序(約1858毫秒)Select*fromitblorderbyid4)查詢所有記錄，按mm排序(約2240毫秒)Select*fromitblorderbymm5)刪除索引(約225毫秒)dropindexitbl.indexname1返回本節(jié)首頁7、對itbl表mm字段建聚集索引1)建索引毫時(約1025毫秒)CREATECLUSTEREDINDEXindexname1ONitbl(mm)2)單記錄插入(約6毫秒)

插入命令同上“單記錄插入”3)查詢所有記錄，按id排序(約2835毫秒)Select*fromitblorderbyid4)查詢所有記錄，按mm排序(約1820毫秒)Select*fromitblorderbymm5)刪除索引(約276毫秒)dropindexitbl.indexname1返回本節(jié)首頁返回本節(jié)首頁

實驗總體要求1、列出多種數(shù)據(jù)庫系統(tǒng)如Access、MSSQLServer、Oracle等，了解其數(shù)據(jù)庫存放的存儲介質(zhì)情況及文件組織方式等。2、列出常用數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)文件的存儲結(jié)構(gòu)與存取方式方法等。3、索引的使用效果測試。4、了解數(shù)據(jù)庫系統(tǒng)效率相關(guān)的參數(shù)并測試這些參數(shù)的調(diào)節(jié)效果。實驗內(nèi)容與要求（選做）返回本節(jié)首頁返回本節(jié)首頁新建數(shù)據(jù)庫命令類似如下：CREATEDATABASEtestONPRIMARY(NAME=test1_dat,FILENAME='C:\test1_dat.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=15%),FILEGROUPfileGroupl(NAME=test2_dat,FILENAME='D:\test2_dat.ndf',SIZE=15,MAXSIZE=50,FILEGROWTH=5),FILEGROUPfileGroup2(NAME=test3_dat,FILENAME='E:\test3_dat.ndf',SIZE=15,MAXSIZE=50,FILEGROWTH=5)LOGON(NAME='test_log',FILENAME='C:\test_log.ldf',SIZE=50MB,MAXSIZE=100MB,FILEGROWTH=5MB)返回本節(jié)首頁

帶指定文件組的索引創(chuàng)建命令類似如下：CREATENONCLUSTEREDINDEXindexname1ONitbl(id)ONfileGroup1

其它索引創(chuàng)建命令類似地在最后加上“ONfileGroup1”或“ONfileGroup2”4、SQLServer中影響數(shù)據(jù)存取效率的因素還有：

（1）不同數(shù)據(jù)庫表分布創(chuàng)建于不同分區(qū)或磁盤上

（2）在創(chuàng)建數(shù)據(jù)表或索引時指定fillfactor選項。如：

下面的示例使用FILLFACTOR子句，將其設(shè)置為80。FILLFACTOR為80將使每一頁填滿80%，該選項對索引的創(chuàng)建也有影響。CREATECLUSTEREDINDEXindexname1ONitbl(id)ONfileGroup1WITHFILLFACTOR=80返回本節(jié)首頁

（3）DBCC作為SQLServer的數(shù)據(jù)庫控制臺命令。它能對數(shù)據(jù)庫的物理和邏輯一致性進行檢查，并能對檢測到的問題進行修復(fù)。為此對數(shù)據(jù)存取有影響。如：DBCCINDEXDEFRAG(test,itbl)能整理test數(shù)據(jù)庫中itbl表上的聚集索引和輔助索引碎片?？赏ㄟ^幫助查看DBCC的不同功能。

（4