第10章数据库存储管理与数据恢复

北京信息科技大学1第10章数据库存储管理与数据恢复数据库存储管理与存储优化备份与恢复北京信息科技大学2本章主要内容文件组、分区表和索引备份、恢复技术在SQLServer中的备份/恢复操作北京信息科技大学310.1数据库存储管理与存储优化SQLServer数据库的存储结构调整数据库文件组分区索引北京信息科技大学4SQLServer数据库的存储结构SQL用户SQLServer系统数据库master数据库tempdb数据库model数据库用户数据库基本表基本表基本表视图视图用户数据库………………物理文件物理文件物理文件……图2-2SQLServer的数据库结构北京信息科技大学5优化存储的手段文件组：通过文件组可以实现分类存储，可以把指定的数据存储到指定的物理文件。分区：通过建立分区表，可以将超大型的表按指定的分区函数存储到指定的物理文件。索引：索引是提高查询性能的常用手段。北京信息科技大学6调整数据库调整或修改数据库的命令是ALTERDATABASE。ALTERDATABASEdatabase{ADDFILEfilespec[,...n]|ADDLOGFILEfilespec[,...n]|REMOVEFILElogical_file_name|MODIFYFILEfilespec}增加新的数据文件（ADDFILE）增加新的日志文件（ADDLOGFILE）删除逻辑文件（REMOVEFILE），同时自动删除对应的物理文件（只有文件为空才可以删除）修改已有物理文件的相关属性（MODIFYFILE）北京信息科技大学7例10-1：为im08数据库增加一个5M大小的物理文件ALTERDATABASEim08ADDFILE(NAME=Test1dat2,FILENAME='C:\2009_is\data\t1dat2.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)北京信息科技大学8例10-2：将im08数据库test1dat2所对应的物理文件增加到10M大小。ALTERDATABASEim08MODIFYFILE(NAME=test1dat2,SIZE=10MB)北京信息科技大学9例10-3：将im08数据库的test1dat2文件改名为test1dat1。ALTERDATABASEim08MODIFYFILE(NAME=Test1dat2,NEWNAME=Test1dat1)北京信息科技大学10例10-4：将im08数据库的test1dat1文件删除。ALTERDATABASEim08REMOVEFILEtest1dat1北京信息科技大学11例10-5：将is2009数据库的主数据文件student.mdf（逻辑文件名是student）移动到c:\2009_is\data\目录下。实现步骤：1.手工将文件移动到指定目录2.执行如下命令：ALTERDATABASEis2009MODIFYFILE(NAME=student,FILENAME='c:\2009_is\data\student.mdf')3.重新启动SQLServer服务北京信息科技大学1210.1.3文件组文件组是将物理存储文件分组。文件组分为主文件组和用户定义文件组两大类。•主文件组包含主数据文件和任何没有明确分配给其他文件组的其他文件。系统表的所有信息存储在主文件组中。•用户定义文件组是通过在CREATEDATABASE或ALTERDATABASE语句中使用FILEGROUP关键字指定的任何文件组。一个物理文件只可以是一个文件组的成员。文件组与日志无关，日志空间与数据空间是分开管理的。CREATEDATABASEMyDBONPRIMARY(NAME='MyDB_Primary',FILENAME='c:\MSSQL\data\MyDB_Prm.mdf',SIZE=4MB,MAXSIZE=10MB,FILEGROWTH=1MB),FILEGROUPMyDB_FG1(NAME='MyDB_FG1_Dat1',FILENAME='c:\MSSQL\data\MyDB_FG1_1.ndf',SIZE=1MB,MAXSIZE=10MB,FILEGROWTH=1MB)LOGON(NAME='MyDB_log',FILENAME='c:\MSSQL\log\MyDB.ldf',SIZE=1MB,MAXSIZE=10MB,FILEGROWTH=1MB)例10-6：创建一个数据库MyDB，该数据库包括一个主数据文件、一个用户定义文件组和一个日志文件。北京信息科技大学14例10-7：把表创建到指定文件组。CREATETABLEMyTable(colaintPRIMARYKEY,colbchar(8))ONMyDB_FG1目的是把表创建到指定物理磁盘。北京信息科技大学15添加文件组、添加物理文件例10-8增加文件组例10-9为文件组增加文件北京信息科技大学16通过文件组可以把指定对象创建到指定物理磁盘。北京信息科技大学1710.1.4分区分区就是把一个大型表的数据分门别类的分割、存储到不同的物理文件，以方便管理、提高效率（特别是提高并行处理能力）。分区是针对大型表，所以只有SQLServerEnterpriseEdition（企业版）才支持分区。北京信息科技大学18建立分区表的步骤1.建立分区函数；2.根据分区函数创建分区方案；3.按分区方案建立表。北京信息科技大学19建立分区函数建立分区函数的命令是CREATEPARTITIONFUNCTION分区函数名(参数类型)ASRANGE[LEFT|RIGHT]FORVALUES([临界值[,...n]])北京信息科技大学20例10-10：建立一个基于整数类型的分区函数。CREATEPARTITIONFUNCTIONmyRangePF1(int)ASRANGELEFTFORVALUES(1,100,1000)11001000北京信息科技大学21建立分区方案建立分区方案的命令是CREATEPARTITIONSCHEME，命令格式如下：CREATEPARTITIONSCHEME分区方案名ASPARTITION分区函数名[ALL]TO({文件组名|[PRIMARY]}[,...n])北京信息科技大学22例10-12：根据例10-10建立的分区函数myRangePF1建立分区方案。CREATEPARTITIONSCHEMEmyRangePS1ASPARTITIONmyRangePF1TO(test1fg,test2fg,test3fg,test4fg)北京信息科技大学23建立分区表可以在CREATETABLE语句的尾部使用ON短语来指定使用的分区方案。北京信息科技大学24例10-13：使用例10-12建立的分区方案建立一个分区表。CREATETABLETestPartitionTable(col1int,col2char(10))ONmyRangePS1(col1)北京信息科技大学25创建分区表的三步：定义分区函数建立分区方案建立分区表CREATEPARTITIONFUNCTIONmyRangePF1(int)ASRANGELEFTFORVALUES(1,100,1000)CREATEPARTITIONSCHEMEmyRangePS1ASPARTITIONmyRangePF1TO(test1fg,test2fg,test3fg,test4fg)CREATETABLETestPartitionTable(col1int,col2char(10))ONmyRangePS1(col1)北京信息科技大学2610.1.5索引在关系数据库中索引是提高查询性能的主要手段。索引一般创建在表的某个或某些列上，索引关键字将会存储在一种B+树的数据结构中，使数据库管理系统（如SQLServer）可以快速有效地根据索引关键字查找到相关的记录。查询优化器在执行查询时通常会选择最有效的方法，它的依据就是索引。如果没有索引，查询优化器就必须扫描整张表。北京信息科技大学27索引的分类聚集索引•聚集索引是一种物理排序的索引，所以在每个表上最多只能有一个聚集索引，并且聚集索引关键字是唯一的。•当在表上创建PRIMARYKEY约束时就会自动建立聚集索引。非聚集索引•唯一索引•保证索引关键字的唯一性•当使用UNIQUE约束时就会自动创建唯一索引•普通索引•为提高查询速度建立的索引北京信息科技大学28设计索引1.当表较小时一般不需要索引；2.当数据库处于频繁修改期时不宜建立过多的索引，因为系统必须为维护索引付出代价；3.当数据库主要用于查询时，可以根据需要多建立一些索引；4.建立索引的字段一定是经常用来做查询条件的字段；5.如果视图包含聚合或连接运算，在视图上建立相关索引也可以显著提高性能。聚集索引和唯一性索引有约束的作用，所以以上原则一般只适用于普通索引。北京信息科技大学29建立索引建立索引的一般命令是CREATEINDEX，基本格式如下：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名ON表或视图(列[ASC|DESC][,...n])[ON{分区(列名)|文件组}]北京信息科技大学30例10-14：在订购单上经常需要按供应商号（供货方）进行查询，则可以为之建立一个普通索引。CREATEINDEXsup_idxON订货.订购单(供货方)北京信息科技大学31例10-15：如果经常需要根据多个字段的条件进行查询，也可以在多个字段上建立索引。CREATEINDEXsup_emp_idxON订货.订购单(供货方,经手人DESC)北京信息科技大学32例10-16：在仓库关系上规定一个城市只设立一个仓库，为此可以在仓库关系的城市属性上建立一个唯一索引：CREATEUNIQUEINDEXcity_idxON仓储.仓库(城市)如果表中某城市已经有两个仓库？北京信息科技大学33注意：最好在创建任何非聚集索引之前创建聚集索引。PRIMARYKEY自动创建聚集索引。UNIQUE约束自动创建唯一索引。为提高查询速度建立普通索引。索引会降低更新性能。北京信息科技大学34在视图上建立索引对视图的操作将会转换为对表的操作，所以一般不需要在视图上建立索引。视图的某些列如果不是直接来自表、而是运算的结果，可以在这样的列上建立索引来提高查询速度。要建立索引的视图需要使用如下短语绑定架构：WITHSCHEMABINGING北京信息科技大学35在视图上建立索引举例P262北京信息科技大学36优化索引任何对数据库的插入、更新和删除操作数据库管理系统都会自动维护索引，从而会产生大量碎片，导致应用程序响应缓慢。在SQLServer中可以通过sys.dm_db_index_physical_stats函数诊断碎片，然后通过ALTERINDEX命令重组或重新生成索引。北京信息科技大学37函数sys.dm_db_index_physical_stats的返回结果列说明avg_fragmentation_in_percent逻辑碎片（索引中的无序页）的百分比。fragment_count索引中的碎片（物理上连续的叶页）数量。avg_fragment_size_in_pages索引中一个碎片的平均页数。一般可以按照avg_fragmentation_in_percent的值决定如何进行优化：•当5%时，不需要对索引进行优化；•当5%且=30%时，使用ALTERINDEXREORGANIZE重新组织索引；•当30%时，使用ALTERINDEXREBUILD重新生成索引。北京信息科技大学38获取碎片信息P263例10-20北京信息科技大学39重组或重新生成索引可用ALTERINDEX命令对索引进行重组或重新生成ALTERINDEX命令的基本格式是：ALTERINDEX索引名ON表或视