《数据库原理及应用》课件-数据库原理及应用实验指导实验8 数据库存储及效率

实验8数据库存储及效率

本章目录

背景知识实验示例实验内容与要求（选做）返回本章首页背景知识返回本节首页返回本节首页

可以利用索引快速访问数据库表中的特定信息。索引是对数据库表中一个或多个列(例如，employee表的姓氏(lname)列)的值进行排序的结构。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行(因未建索引)相比，索引有助于更快地获取信息。

索引可以简单理解为是键值与键值关联行的存取地址的一张表，而键值是根据指定的排序次序排列的。数据库使用索引的方式与使用书的目录很相似：通过搜索索引找到特定的键值，然后根据键值对应的地址找到关联行。返回本节首页返回本节首页

关于创建索引的建议如下：1、将更新尽可能多的行的查询写入单个语句内，而不要使用多个查询更新相同的行。仅使用一个语句，就可以利用优化的索引维护。2、使用索引优化向导分析查询并获得索引建议。3、对聚集索引使用整型键。另外，在唯一列、非空列或IDENTITY列上创建聚集索引可以获得性能收益。4、在查询经常用到的所有列上创建非聚集索引。这可以最大程度地利用隐蔽查询。返回本节首页5、物理创建索引所需的时间在很大程度上取决于磁盘子系统。需要考虑的重要因素包括：（1）用于存储数据库和事务日志文件的RAID(独立磁盘冗余阵列)等级；（2）磁盘阵列中的磁盘数(如果使用了RAID)；（3）每个数据行的大小和每页的行数。这将决定为创建索引须从磁盘读取的数据页的数目；（4）索引中的列数和使用的数据类型。这将决定必须写入磁盘的索引页的数目。6、检查列的唯一性。7、在索引列中检查数据分布。通常情况下，为包含很少唯一值的列创建索引或在这样的列上执行联接将导致长时间运行的查询。返回本节首页实验示例返回本节首页1、创建表itbl,并插入4万条记录。

请在SQLServer集成管理器查询子窗口中，选择某用户数据库,执行如下命令，生成80000条记录，见图8-1。CREATETABLEitbl(idbigintIDENTITY(1,1)NOTNULL,rqdatetimeNULL,srqvarchar(20)NULL,hhsmallintNULL,mmsmallintNULL,sssmallintNULL,numnumeric(12,3))declare@iintselect@i=1返回本节首页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返回本节首页图8-1在表itbl中生成80000条记录2、下面是测试命令执行的代码。运行时把“待测试命令”替换成你的测试命令，在查询分析器中执行后，能返回命令执行的大致时间(单位为：毫秒)。declare@dt1datetimedeclare@iintdeclare@schar(40)declare@hm1intdeclare@hm2intselect@dt1=getdate()返回本节首页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)返回本节首页3、未建索引时：1)单记录插入(约10毫秒，给出的毫秒数在特定环境下得出的，只作参考，下同)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所示。返回本节首页3)查询所有记录，按mm排序(约1960毫秒)Select*fromitblorderbymm

图8-2按id升序查询表itbl中的全部记录返回本节首页4、对itbl表id字段建非聚集索引1)建索引毫时(约330毫秒)CREATENONCLUSTEREDINDEXindexname1ONitbl(id)2)单记录插入(约6毫秒)

插入命令同上“单记录插入”3)查询所有记录，按id排序(约2690毫秒)Select*fromitblorderbyid4)查询所有记录，按mm排序(约2270毫秒)Select*fromitblorderbymm5)删除索引(约95毫秒)dropindexitbl.indexname1返回本节首页5、对itbl表mm字段建非聚集索引1)建索引毫时(约423毫秒)CREATENONCLUSTEREDINDEXindexname1ONitbl(mm)2)单记录插入(约25毫秒)

插入命令同上“单记录插入”3)查询所有记录，按id排序(约2700毫秒)Select*fromitblorderbyid4)查询所有记录，按mm排序(约1900毫秒)Select*fromitblorderbymm5)删除索引(约48毫秒)dropindexitbl.indexname1返回本节首页6、对itbl表id字段建聚集索引1)建索引毫时(约1400毫秒)CREATECLUSTEREDINDEXindexname1ONitbl(id)2)单记录插入(约1毫秒)

插入命令同上“单记录插入”3)查询所有记录，按id排序(约1858毫秒)Select*fromitblorderbyid4)查询所有记录，按mm排序(约2240毫秒)Select*fromitblorderbymm5)删除索引(约225毫秒)dropindexitbl.indexname1返回本节首页7、对itbl表mm字段建聚集索引1)建索引毫时(约1025毫秒)CREATECLUSTEREDINDEXindexname1ONitbl(mm)2)单记录插入(约6毫秒)

插入命令同上“单记录插入”3)查询所有记录，按id排序(约2835毫秒)Select*fromitblorderbyid4)查询所有记录，按mm排序(约1820毫秒)Select*fromitblorderbymm5)删除索引(约276毫秒)dropindexitbl.indexname1返回本节首页返回本节首页

实验总体要求1、列出多种数据库系统如Access、MSSQLServer、Oracle等，了解其数据库存放的存储介质情况及文件组织方式等。2、列出常用数据库系统数据文件的存储结构与存取方式方法等。3、索引的使用效果测试。4、了解数据库系统效率相关的参数并测试这些参数的调节效果。实验内容与要求（选做）返回本节首页返回本节首页新建数据库命令类似如下：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)返回本节首页

带指定文件组的索引创建命令类似如下：CREATENONCLUSTEREDINDEXindexname1ONitbl(id)ONfileGroup1

其它索引创建命令类似地在最后加上“ONfileGroup1”或“ONfileGroup2”4、SQLServer中影响数据存取效率的因素还有：

（1）不同数据库表分布创建于不同分区或磁盘上

（2）在创建数据表或索引时指定fillfactor选项。如：

下面的示例使用FILLFACTOR子句，将其设置为80。FILLFACTOR为80将使每一页填满80%，该选项对索引的创建也有影响。CREATECLUSTEREDINDEXindexname1ONitbl(id)ONfileGroup1WITHFILLFACTOR=80返回本节首页

（3）DBCC作为SQLServer的数据库控制台命令。它能对数据库的物理和逻辑一致性进行检查，并能对检测到的问题进行修复。为此对数据存取有影响。如：DBCCINDEXDEFRAG(test,itbl)能整理test数据库中itbl表上的聚集索引和辅助索引碎片。可通过帮助查看DBCC的不同功能。

（4