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

25
北京信息科技大学
10.1.5 索引
在关系数据库中索引是提高查询性能的主要手 段。 索引一般创建在表的某个或某些列上，索引关 键字将会存储在一种B+树的数据结构中，使数 据库管理系统（如SQL Server）可以快速有效 地根据索引关键字查找到相关的记录。 查询优化器在执行查询时通常会选择最有效的 方法，它的依据就是索引。如果没有索引，查 询优化器就必须扫描整张表。
北京信息科技大学
在视图上建立索引举例

P262
35
北京信息科技大学
优化索引


任何对数据库的插入、更新和删除操作数据库管理系 统都会自动维护索引，从而会产生大量碎片，导致应 用程序响应缓慢。 在SQL Server中可以通过 sys.dm_db_index_physical_stats函数诊断碎片， 然后通过ALTER INDEX命令重组或重新生成索引。
31
北京信息科技大学
例10-16：在仓库关系上规定一个城市只设立一个仓库，为此 可以在仓库关系的城市属性上建立一个唯一索引：
CREATE UNIQUE INDEX city_idx ON 仓储.仓库(城市)
如果表中某城市已经有两个仓库？
32
北京信息科技大学
注意：
最好在创建任何非聚集索引之前创建聚集 索引。 PRIMARY KEY自动创建聚集索引。 UNIQUE约束自动创建唯一索引。 为提高查询速度建立普通索引。 索引会降低更新性能。
15
北京信息科技大学
通过文件组可以把指定对 象创建到指定物理磁盘。
16
北京信息科技大学
10.1.4 分区
分区就是把一个大型表的数据分门别类的分 割、存储到不同的物理文件，以方便管理、 提高效率（特别是提高并行处理能力）。 分区是针对大型表，所以只有SQL Server Enterprise Edition（企业版）才支持分区。
ALTER DATABASE im08 MODIFY FILE (NAME = test1dat2,SIZE = 10MB)
8
北京信息科技大学
例10-3：将im08数据库的test1dat2文件改名为 test1dat1。
ALTER DATABASE im08 MODIFY FILE ( NAME = Test1dat2, NEWNAME = Test1dat1 )

26
北京信息科技大学
索引的分类

聚集索引
• 聚集索引是一种物理排序的索引，所以在每个表上最多 只能有一个聚集索引，并且聚集索引关键字是唯一的。 • 当在表上创建PRIMARY KEY约束时就会自动建立聚集 索引。

非聚集索引
• 唯一索引
• 保证索引关键字的唯一性 • 当使用UNIQUE 约束时就会自动创建唯一索引
9
北京信息科技大学
例10-4：将im08数据库的test1dat1文件删除。
ALTER DATABASE im08 REMOVE FILE test1dat1
10
北京信息科技大学
例10-5：将is2009数据库的主数据文件 student.mdf（逻辑文件名是student）移 动到c:\2009_is\data\目录下。
北京信息科技大学
建立分区表

可以在CREATE TABLE语句的尾部使用ON
短语来指定使用的分区方案。
23
北京信息科技大学
例10-13：使用例10-12建立的分区方案建立一 个分区表。
CREATE TABLE TestPartitionTable ( col1 int, col2 char(10)) ON myRangePS1 (col1)

实现步骤：
1. 手工将文件移动到指定目录 2. 执行如下命令：
ALTER DATABASE is2009 MODIFY FILE(NAME = student, FILENAME =
'c:\2009_is\data\student.mdf')
3. 重新启动SQL Server服务
11
北京信息科技大学
ALTER DATABASE database { ADD FILE <filespec> [ ,...n ] | ADD LOG FILE <filespec> [ ,...n ] | REMOVE FILE logical_file_name | MODIFY FILE <filespec> }
• 普通索引
• 为提高查询速度建立的索引
27
北京信息科技大学
设计索引
1.
2.
3.
4.
5.
当表较小时一般不需要索引； 当数据库处于频繁修改期时不宜建立过多的索引，因 为系统必须为维护索引付出代价； 当数据库主要用于查询时，可以根据需要多建立一些 索引； 建立索引的字段一定是经常用来做查询条件的字段； 如果视图包含聚合或连接运算，在视图上建立相关索 引也可以显著提高性能。

33
北京信息科技大学
在视图上建立索引
对视图的操作将会转换为对表的操作，所以 一般不需要在视图上建立索引。 视图的某些列如果不是直接来自表、而是运 算的结果，可以在这样的列上建立索引来提 高查询速度。 要建立索引的视图需要使用如下短语绑定架 构： WITH SCHEMABINGING

34
北京信息科技大学
例10-7：把表创建到指定文件组。
目的是把表创建到指定物理磁盘。
CREATE TABLE MyTable ( cola int PRIMARY KEY, colb char(8) ) ON MyDB_FG1
14
北京信息科技大学
添加文件组、添加物理文件


例10-8 增加文件组
例10-9 为文件组增加文件

17
北京信息科技大学
建立分区表的步骤
1.
2. 3.
建立分区函数； 根据分区函数创建分区方案； 按分区方案建立表。
18
北京信息科技大学
建立分区函数
ቤተ መጻሕፍቲ ባይዱ
建立分区函数的命令是
CREATE PARTITION FUNCTION <分区函数名>(<参数类型> ) AS RANGE [ LEFT | RIGHT ] FOR VALUES ( [<临界值> [ ,...n ] ] )
聚集索引和唯一性索引有约束的作用，所以以上 原则一般只适用于普通索引。 28
北京信息科技大学
建立索引

建立索引的一般命令是CREATE INDEX，基本 格式如下：
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX <索引名> ON <表或视图> ( <列> [ ASC | DESC ] [ ,...n ] ) [ ON { <分区> ( <列名> ) | <文件组> } ]
24
北京信息科技大学
创建分区表的三步：


定义分区函数 建立分区方案 建立分区表
CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (1, 100, 1000) CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePF1 TO (test1fg, test2fg, test3fg, test4fg) CREATE TABLE TestPartitionTable ( col1 int, col2 char(10)) ON myRangePS1 (col1)
10.1.3 文件组

文件组是将物理存储文件分组。 文件组分为主文件组和用户定义文件组两大类。
• 主文件组包含主数据文件和任何没有明确分配给其他文件 组的其他文件。系统表的所有信息存储在主文件组中。 • 用户定义文件组是通过在CREATE DATABASE或ALTER DATABASE语句中使用FILEGROUP关键字指定的任何 文件组。


一个物理文件只可以是一个文件组的成员。 文件组与日志无关，日志空间与数据空间是分开管 理的。
12
例10-6：创建一个数据库MyDB，该数据库包括一个主数据文件、 一个用户定义文件组和一个日志文件。
CREATE DATABASE MyDB ON PRIMARY ( NAME='MyDB_Primary', FILENAME='c:\MSSQL\data\MyDB_Prm.mdf', SIZE=4MB, MAXSIZE=10MB, FILEGROWTH=1MB), FILEGROUP MyDB_FG1 ( NAME = 'MyDB_FG1_Dat1', FILENAME ='c:\MSSQL\data\MyDB_FG1_1.ndf', SIZE = 1MB, MAXSIZE=10MB, FILEGROWTH=1MB) LOG ON ( NAME='MyDB_log', FILENAME ='c:\MSSQL\log\MyDB.ldf', SIZE=1MB, MAXSIZE=10MB, FILEGROWTH=1MB)
19
北京信息科技大学
例10-10：建立一个基于整数类型的分区函数。
CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (1, 100, 1000)
1
100