在SQL Server中,设计有效的索引(Index)是影响数据库性能的重要因素之一,合理的索引可以显著提高数据库的查询性能。
视图是一个虚拟表,视图中数据来源于由定义视图所引用的表,并且能够实现动态引用,即表中数据发生变化,视图中的数据随之变化。
统计信息是查询优化器进行查询优化的依据,及时更新统计信息对优化的效果至关重要。SQL Server提供了自动和手动两种方式实现对统计信息的创建及更新功能。
规划索引
- 索引的用途
SQL Server的索引是为了加速对表中数据检索而创建的一种分散的、物理的数据结构。数据库中的索引的形式与图书的目录相似,键值就像目录中的标题,指针相当于页码。
索引是一个逻辑文件,包含从表或视图中一个或多个列生成的键,以及映射到指定数据行的存储位置指针。当SQL Server执行查询时,查询优化器会对可用的多种数据检索方法的成本进行估计,从中选用最有效的查询计划。
在数据库中使用索引的优点如下。
加速数据检索:索引能够以一列或多列值为基础实现快速查找数据行。
优化查询:查询优化器是依赖于索引起作用的,索引能够加速连接、排序和分组等操作。
强制实施行的唯一性:通过给列创建唯一索引,可以保证表中的数据不重复。 - 索引的类型
SQL Server 2016中常用的有聚集索引、非聚集索引和唯一索引3种类型。聚集索引和非聚集索引是按照索引的存储结构划分的,而唯一索引和非唯一索引是按照索引取值划分的。这是两种截然不同的索引类型划分方法。
聚集索引。在聚集索引中,索引键值的顺序与数据表中记录的物理顺序相同,即聚集索引决定了数据库表中记录行的存储顺序,每个表只能创建一个聚集索引。聚集索引按 B 树索引结构实现,B 树索引结构支持基于聚集索引键值对行进行快速检索。
非聚集索引。非聚集索引存储的数据顺序一般与表中记录的物理顺序不同。非聚集索引具有独立于数据行的结构,但非聚集索引的每一个键值项都含有指向该键值数据行的指针。
唯一索引。唯一索引可确保所有表中任意两行的索引列值(不包括NULL)不重复,如果在多列创建唯一索引,则该索引可以确保索引列中每个值组合都是唯一的。
在表中创建主键约束时,如果表上还没有创建聚集索引,则SQL Server将自动在创建主键约束的列或组合上创建聚集唯一索引。
其他索引类型如表8-1所示。
创建索引
SQL Server 2016中创建索引的方法包括:使用SSMS创建索引和利用CREATE INDEX语句创建索引。还可以在CREATE TABLE或ALTER TABLE语句中定义或修改表结构时创建索引。
创建索引之前,应该考虑到权限问题,只有表的拥有者才能在表上创建索引,每个表最多可以创建249个非聚集索引。
在创建聚集索引时还要考虑到数据库剩余空间的问题,创建聚集索引时所需要的可用空间是数据库表中数据量的120%。如果空间不足会降低性能,甚至导致索引操作失败。
1.利用SSMS创建索引
使用SQL Server Management Studio创建独立于约束的聚集索引的操作步骤如下:
启动SQL Server Management Studio,展开“资源管理器”窗口中teaching数据库“表”子目录。
选择student表并展开,右击“索引”项,如图8-1所示。在快捷菜单中选择“新建索引”“聚集索引”命令。
使用SQL Server Management Studio创建独立于约束的聚集索引的操作步骤如下:
在弹出的“新建索引”对话框中,选择“常规”选项卡,输入所引名称Idx_student,取代默认名称。如图8-2所示。其中各项说明如下。
① 表名:指出创建索引的表的名称,用户不可更改。
② 索引名称:输入所创建索引的名称,由用户设定。
③ 索引类型:本例在索引类型组合框中选择“聚集”。
④ 唯一:选中表示创建唯一性索引。本例唯一复选框为选中状态。
设置完成后,按照提示,单击索引键列的“添加”按钮,出现如图8-3所示的从Student表中选择列对话框。在“表列”列表中选中要建立索引的一列或多列,如选择studentno列。
索引键列设置完毕,单击“确定”按钮,返回到“新建索引”对话框,在“索引键列”中的“排序顺序”组合框中可以选择的“升序”或“降序”。如图8-4所示。
在“新建索引”对话框中查看“选项”选项卡,如图8-5所示。
在“新建索引”对话框中查看“存储”选项卡,如图8-6所示。
在“新建索引”对话框中查看“扩展属性”选项卡,如图8-7所示。进行必要的设置后,单击“脚本”,可以查看创建本索引Idx_student的代码。代码如下:
单击“确定”按钮,即完成了创建聚集索引的操作。此时,就可以在当前索引子目录中查看创建的索引文件。
2.利用CREATE INDEX命令创建索引
SQL Server 2016提供的创建索引的Transact-SQL语句是CREATE INDEX,其基本语法格式如下:
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name
ON { table_or_view_name }
(column [ ASC|DESC ][ ,...n ] )
[ INCLUDE (column_name[ ,...n])]
[ ON { filegroup_name |default } ][ ; ]
例8.1】在 teaching 数据库中的 student 表的Email列上创建唯一索引IDX_Email。
CREATE UNIQUE INDEX IDX_Email
ON student(Email)
执行如下插入语句:
INSERT INTO student(studentno,sname,sex,birthdate,classno,Email)
VALUES('18125121105','梁欣','女','1999-6-3',
'180802','[email protected] ')
唯一性约束确保索引列不包含重复的值,则插入操作出现重复键值时会发出错误消息,如
【例8.2】在 teaching 数据库中 student 表的 studentno 和 classno 列上创建组合索引 IDX_sc。
IF EXISTS(SELECT name FROM sysindexes WHERE name='IDX_sc')
DROP INDEX student.sc
GO
CREATE INDEX IDX_sc ON student(studentno,classno)
本例首先在系统表sysindexes中查找是否存在名称为IDX_sc的索引,如存在则将其删除,然后在student表上创建非聚集非唯一索引。本索引键值由列studentno和classno的值组合而成。
维护索引
在SQL Server 2016中修改索引的方法有两种:使用SQL Server Management Studio图形工具和Transact-SQL语句。
1 .在SSMS中修改索引
使用SSMS修改索引的参考操作步骤如下:
(1)启动SQL Server Management Studio,展开“资源管理器”窗口中的“teaching数据库” “表”student 子目录。
(2)选择并展开“索引”项,右击Idx_student索引,在快捷菜单中选择“属性”命令。
(3)出现“索引属性”对话框,在各选项卡中可以修改索引的设置。在“常规”选项卡中可以添加或删除索引键列、改变键列排序。
(4)在“选项”选项卡中可实现对于在访问索引时是否使用行锁和页锁、填充因子等索引选项的修改。如选中“设置填充因子”和“填充索引”复选框,并设置填充因子为80%。
(5)在“存储”选项卡中可实现对于索引的文件组和分区属性的修改。切换至“扩展属性”选项卡,可以修改与索引相关的扩展信息。
(6)切换至“碎片”选项卡,该选项卡用于查看索引碎片数据以确定是否需要重新组织索引,如图8-9所示。
(7)修改完毕,单击“脚本”按钮,可以查看修改索引的代码。单击“确定”按钮,即可完成操作。
2 .利用ALTER INDEX命令修改索引
SQL Server 2016提供的创建索引的Transact-SQL语句是ALTER INDEX,其语法基本格式如下:
ALTER INDEX { index_name | ALL }
ON