资源简介

(共29张PPT)
关系数据库标准语言SQL
第3章
目录
01
SQL概述
02
数据库的创建
与管理
03
数据表及其操作
04
数据查询
05
视图
06
索引
本章主要内容
结构化查询语言（Structured Query Language，SQL）是关系数据库管理的标准语言，具有数据定义、数据查询、数据操纵和数据控制四个方面的功能。SQL结构简单，功能齐全，是目前广泛应用的关系数据库查询语言。
本章主要介绍SQL Server 2019数据库管理系统各种工具的使用和SQL的使用。
索引
第3章
06
3.6 索引
用户对数据库最基本、最频繁的操作是数据查询。一般情况下，数据库在进行查询操作时需要对整个数据表进行扫描。当表中的记录很多时，扫描数据需要很长的时间，这就造成了系统资源的浪费。为了提高检索数据的能力，数据库引入了索引机制。
3.6.1 索引的概念
如果要在一本书中快速地查找所需的信息，可以利用目录快速地进行查找，而不是一页页地查找。数据库中的索引与书籍中的目录类似，可以让数据库应用程序利用索引快速地查找到所需内容，而不需要扫描整个数据库。在图书中，目录是内容和页码的列表清单。在数据库中，索引就是表中数据和相应存储位置的列表。
索引是以表的列为基础的数据库对象，是为了加速对表中数据行的检索而创建的一种单独的存储结构。当SQL Server执行查询时，查询优化器评估可用于检索数据的每种方法，然后选择最有效的方法。可能采用的方法包括扫描表和扫描一个或多个索引（如果有）。扫描表时，查询优化器读取表中的所有行，并提取满足查询条件的行。扫描表时会有许多磁盘I/O操作，并占用大量资源。查询优化器使用索引时，搜索索引键列，查找到所需查询行的存储位置，然后从该位置提取匹配行。通常，搜索索引比搜索表要快得多，这是因为索引与表不同，一般每行包含的列非常少，且行遵循排序顺序。
3.6.1 索引的概念
索引作为单独的物理数据库结构，与其所依附的数据表是不能割裂开的。索引提供了一种对数据库表记录进行逻辑排序的内部方法。索引一旦建立成功，将由数据库引擎自动维护和管理。当对索引所依附的表进行记录的添加、更新或删除操作时，数据库引擎会及时更新与调整索引的内容，使其与表一致。在数据库系统中建立索引能够极大地改善系统的性能，并且使用索引能够加快数据检索的速度，索引具有以下几个方面的优点。
加速数据检索的速度。索引能够以一列或多列的值为基础，迅速查找表中的数据。
加快表与表之间的连接速度。连接、排序和分组都需要数据检索，在建立好索引后，其数据检索速度会加快，从而加速连接等操作。
在数据检索过程中使用查询优化器，提高系统性能。查询优化器依赖于索引，用于决定到底哪些索引可以使该查询最快。
确保数据记录的唯一性。通过给列创建唯一索引，可以保证表中的数据不重复。
在使用ORDER BY、GROUP BY子句进行数据检索时，利用索引机制，能够显著地减少查询中排序和分组消耗的时间。
3.6.1 索引的概念
索引虽然可以提高查询的速度，但是需要牺牲一定的系统性能。创建索引和维护索引需要耗费时间，对表中数据进行插入、删除和修改操作时，要进行索引维护。另外，索引还占有一定的物理存储空间。因此，对表中的列是否创建索引及如何创建索引，需要进行判断评估。创建索引应考虑的主要因素如下。
一个表如果建有大量索引会影响INSERT、UPDATE和DELETE语句的性能，因为当表中的数据更改时，所有索引都需要进行适当的调整。
避免对经常更新的表建立过多的索引，并且索引应保持较窄，即列要尽可能少。
使用多个索引可以提高更新少而数据量大的表的查询性能。大量索引可以提高不修改数据的表的查询性能，因为查询优化器有更多的索引可供选择，从而可以确定最快的访问方法。
对简单的表进行索引可能不会产生优化效果，因为查询优化器在遍历用于搜索数据的索引时，花费的时间可能比执行表的扫描还长。因此，简单的表的索引可能从来不使用，但却必须在表中的数据更改时进行维护。
对于查询中很少涉及的列或者重复值比较多的列，不要建立索引。
定义主键和外键的列，最好建立索引。
3.6.2 索引的类型
SQL Server 2019中包含两种最基本的索引，即聚集索引和非聚集索引。此外还有唯一索引、包含列索引、视图索引、全文索引、空间索引、筛选索引和XML索引等。
（1）聚集索引
在聚集索引中，索引键值的顺序与数据表中记录的物理顺序相同，即聚集索引决定了数据库表中记录行的存储顺序。由于记录行只能按一个物理顺序存储，因此每个表只能有一个聚集索引。
由于聚集索引的顺序与表中记录的物理存储相同，因此聚集索引适用于范围查找，但聚集索引不适用于频繁更改的列。
（2）非聚集索引
非聚集索引具有独立于数据行的结构，非聚集索引的每个键值项都有指向包含该键值的数据行的指针。每个表最多可以创建249个非聚集索引，用以满足多种查询的需要。
与聚集索引相比，非聚集索引的查询速度较慢，但维护的代价较小。
3.6.2 索引的类型
（3）唯一索引
唯一索引可以确保所有数据行中任意两行的索引列不包括NULL在内的重复值。如果在多列上创建唯一索引，则该索引可以确保索引列中每个值的组合都是唯一的。
（4）包含列索引
在SQL Server中，索引列的数量（最多16个）和字节总数（最大900字节）是受限制的。使用包含列索引，可以通过非键列添加到非聚集索引的叶级来扩展其功能，创建覆盖更多查询的非聚集索引。
（5）视图索引
视图索引是为视图创建的索引，其存储方法与带聚集索引表的存储方法相同。
（6）全文索引
全文索引是一种特殊类型的基于标记的共功能性索引，由SQL Server全文引擎服务创建和维护。其目的是帮助用户在字符串数据库中检索复杂的词语。
3.6.2 索引的类型
（7）空间索引
空间索引是针对包含空间数据的表定义的。每个空间索引指向一个有限空间。利用空间索引，可以更高效地对geometry数据类型的列中的空间对象执行某些操作。空间索引可减少需要应用开销相对较大的空间操作的对象数。
（8）筛选索引
筛选索引是一种经过优化的非聚集索引，尤其适用于从定义完善的数据子集中选择数据的查询。筛选索引使用筛选谓词对表中的部分行进行索引。与全文索引相比，设计良好的筛选索引可以提高查询性能、降低索引的维护开销的同时降低索引的存储开销。
（9）XML索引
XML索引是与XML数据关联的索引形式，是XML二进制BLOB的已拆分持久表示形式，可分为主索引和辅助索引。
3.6.3 创建索引
SQL Server 2019中创建索引的方法包括：使用SQL Server Management Studio对象资源管理器创建索引；利用CREATE INDEX语句创建索引；使用CREATE TABLE或ALTER TABLE语句定义，或修改表结构时自动创建索引（该方法为默认形式，创建表的时候已创建过索引，下文不再详述）。
（1）使用对象资源管理器创建索引
1）启动SQL Server Management Studio，在对象资源管理器中，依次展开“数据库”→“TeachSystem”→“表”。
2）选择表student并展开，右击“非聚集索引”项，在弹出的快捷菜单中选择“新建索引”命令。
3.6.3 创建索引
3）在弹出图所示的“新建索引”对话框后，输入索引名称index_sno。其中各项说明如下。
表名：指出创建的索引的表的名称，用户不可更改。
索引名称：输入用户创建的索引名称，由用户设定。
索引类型：用户可以选择“聚集”“非聚集”“空间”，或“主XML”索引类型。
唯一：选中此复选框表示创建唯一性索引。
4）设置完成后，单击“添加”按钮，出现图所示的“从dbo.student中选择列”对话框。在“表列”列表中选中要建立索引的一列或多列，例如选择SNO列。
3.6.3 创建索引
5）索引键列设置完毕，单击“确定”按钮，返回“新建索引”对话框，在“索引键 列”选项卡的“排序顺序”组合框中可以选择“升序”。
6）在“新建索引”对话框中查看“选项”“包含性 列”“存储”等选项卡并进行必要的设置后，单击“确定”按钮，即完成了创建索引的操作。
（2）使用T-SQL语句创建索引
SQL Server 2019中提供的创建索引的语句是CREATE INDEX，其基本语法格式如下。
3.6.3 创建索引
【例3.74】在TeachSystem数据库中的student表的SN列上创建唯一索引index_SN。
【例3.75】在TeachSystem数据库中的student表的SNO和DNO列上创建组合索引。
3.6.4 修改索引
创建索引后，可以使用SQL Server Management Studio的对象资源管理器修改索引，也可以使用T-SQL中的ALTER INDEX语句修改索引。
（1）使用对象资源管理器修改索引
1）启动SQL Server Management Studio，依次展开“资源管理器”→“数据库”→TeachSystem→“表”→“student”。
2）选择并展开“索引”项，右击index_SN索引，在弹出的快捷菜单中选择“属性”命令。
3）弹出“索引属性”对话框，在各选项卡中可以修改索引的设置。在“常规”选项卡中可实现对于索引类型、索引键列等排序顺序的修改，如图所示。
3.6.4 修改索引
4）在“选项”选项卡中可实现对在访问索引时是否使用行锁和页锁、填充因子等索引选项的修改。如选中“设置填充因子”和“填充索引”复选框，并设置填充因子为80%。
5）在“包含性 列”选项卡中可添加包含在索引中的列，该选项卡只对非聚集索引可用。在“存储”选项卡中可实现对索引的文件组和分区属性的修改。
6）打开“碎片”选项卡，该选项卡用于查看索引碎片数据以确定是否需要重新组织索引。如选中“重新组织索引”复选框，则执行修改可重新组织索引。
7）打开“扩展属性”选项卡，可以修改与索引相关的扩展信息。
8）修改完毕，单击“确定”按钮，即可完成操作。
3.6.4 修改索引
（2）使用T-SQL语句修改索引
使用T-SQL中的ALTER INDEX语句可以修改索引的定义，其基本语法格式如下。
3.6.5 检测索引碎片
SQL Server 2019的索引数据是随着表数据的更新而自动维护的，无论何时对基础数据执行插入、更新或删除操作，SQL Server 2019数据库引擎都会自动维护索引。随着时间的推移，这些修改可能会导致索引中的信息分散在数据库中，本来可以存储在一个页中的索引却不得不存储在两个或更多的页上，这样的情况称为索引中存在碎片。
当索引包含的页中基于键值的逻辑排序与数据文件中的物理排序不匹配时，就会存在碎片。碎片非常多的索引可能会降低查询性能，导致应用程序响应缓慢。
3.6.5 检测索引碎片
SQL Server 2019可以通过重新组织索引或重新生成索引来修复索引碎片，以解决上述问题。决定使用哪种碎片整理方法的前提是检测索引碎片并进行分析，以此确定碎片程度。
SQL Server 2019提供了查看和检测有关索引碎片信息的方法，并且可以通过对检测结果的分析，确定处理碎片的最佳方法。在检测结果中，逻辑碎片的百分比属性中的取值可用来决定下一步的处理方法。一般情况下，如该属性值小于或等于30%，推荐采用索引重组，如果该属性值大于30%，推荐采用索引重建。
3.6.5 检测索引碎片
（1）索引重组
索引重组是通过对索引的叶级页进行物理重新排序，使其与叶节点的逻辑顺序相匹配，从而对表或视图的聚集索引和非聚集索引的叶级页进行碎片整理，使页有序排列，从而可以提高索引扫描的性能。索引重组需要注意以下问题。
1）索引在分配给它的现有页内重新组织，而不会分配新页。如果索引跨越多个文件，则将一次重新组织一个文件，不会在文件之间迁移页。
2）重新组织还会压缩索引页。如果还有可用的磁盘空间，将删除此压缩过程中生成的所有空页。压缩基于设置的填充因子值。
3.6.5 检测索引碎片
3）重新组织进程使用最少的系统资源，而且是自动联机执行的。
4）索引碎片不太多时，可以重新组织索引。如果索引碎片非常多，重新生成索引则可以获得更好的结果。
【例3.76】重新组织TeachSystem数据库中student表上的index_SN索引。
3.6.5 检测索引碎片
（2）索引重建
索引重建将删除已存在的索引并创建一个新索引。此过程中将删除碎片，通过使用指定的或现有的填充因子设置压缩页来回收磁盘空间，并在连续页中对索引行重新排序。这样可以减少获取所请求数据需要的页读取数，从而提高磁盘性能。
【例3.77】重新生成TeachSystem数据库中student表上的index_SN索引，设置填充索引，将填充因子设置为80%。
3.6.6 查看索引
在SQL Server 2019中，有两种查看索引的方法：一种是使用SQL Server Management Studio查看索引；另一种是通过系统存储过程查看索引，一般多使用此方法。下面主要介绍使用系统存储过程查看用户创建的索引的信息。
（1）sp_helpindex：用于查看有关表或视图上索引的信息。其基本语法格
式为
sp_helpindex[@objname=]'name'
其中，参数name为查看其索引的用户定义的表或视图的名称，无默认值。
例如：
EXEC sp_helpindex student
3.6.6 查看索引
（2）sp_help：用于显示数据库对象或数据类型的基本信息。对于视图，可查看其名称、所有者、创建时间以及视图的列名、数据类型、长度、是否允许为空等信息。其基本语法格式为
sp_help[[@objname=]'name']
其中，参数name为要查看的数据对象或数据类型名称，其默认值为NULL，不能接受数据库名称。
SQL Server 2019还提供了若干用于查看用户创建的索引的信息系统视图和系统函数。如系统视图sys.indexes，可以查看索引所属对象ID、索引名称、索引类型、文件组或分区方案和索引选项的当前设置。
3.6.7 删除索引
当一个索引不再被需要时，可以将其从数据库中删除，以回收它当前使用的磁盘空间。根据索引的创建方式，要删除的索引分为两类：一类是创建表约束时自动创建的索引，这类索引必须先删除PRIMARY_KEY或UNIQUE约束，才能删除约束使用的索引；另一类是通过创建索引的方式创建的独立于约束的索引，这类索引可以利用SQL Server Management Studio工具或Drop Index语句直接删除。
3.6.7 删除索引
（1）使用对象资源管理器删除索引
1）启动SQL Server Management Studio，依次展开“资源管理器”→“数据库”→“TeachSystem”→“表”→“student”。
2）选择并展开“索引”项，右击索引index_SN，在弹出的快捷菜单中选择“删除”命令。
3）在弹出的“删除对象”对话框中，会显示要删除的索引，单击“确定”按钮即可完成删除操作。
3.6.7 删除索引
（2）使用T-SQL语句删除索引
使用DROP INDEX语句可以删除当前数据库中一个或多个索引，其语法格式如下。
DROP INDEX {table_name | view_name}.index_name[…,n]
【例3.78】删除TeachSystem数据库中student表上的index_SN索引。
本章小结
本章主要介绍了SQL的四大功能，即数据定义、数据查询、数据操纵和数据控制，其功能可以使用CREATE、ALTER、DROP、SELECT、INSERT、UPDATE、DELETE等命令实现。在讲解SQL的同时，进一步介绍了关系数据库有关的概念，例如表、视图和索引等，同时介绍了使用图形化工具SQL Server Management Studio的对象资源管理器创建和管理数据库对象的方法。

展开更多......

收起↑