使用SQL语句管理索引

使用SQL语句管理索引

1. 创建索引

本次实验环境基于DMS数据库实验室的RDS-SQL Server，前往并登录DMS数据库实验室。

如果要在一本书中快速地查找所需的信息，可以利用目录快速地进行查找，而不是一页页地查找。数据库中的索引与书籍中的目录类似，可以让数据库应用程序利用索引快速地查找到所需内容，而不需要扫描整个数据库。在图书中，目录是内容和页码的列表清单。在数据库中，索引就是表中数据和相应存储位置的列表。索引是以表的列为基础的数据库对象，是为了加速对表中数据行的检索而创建的一种单独的存储结构。当 SQL Server 执行查询时，查询优化器评估可用于检索数据的每种方法，然后选择最有效的方法。可能采用的方法包括扫描表和扫描一个或多个索引（如果有）。扫描表时，查询优化器读取表中的所有行，并提取满足查询条件的行。扫描表时会有许多磁盘 I/O 操作，并占用大量资源。查询优化器使用索引时，搜索索引键列，查找到所需查询行的存储位置，然后从该位置提取匹配行。通常，搜索索引比搜索表要快得多，这是因为索引与表不同，一般每行包含的列非常少，且行遵循排序顺序。

使用SQL语句创建索引、修改索引和删除索引的具体操作步骤如下：

创建索引。

使用SQL语句创建所以的基本语法格式：

CREATE [UNIQUE][CLUSTERED|NONCLUSTERED] INDEX index_name ON {table_or_view_name}(column[ASC|DESC][,…n])}

参数说明：

UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。省略该参数则表示创建的索引为非唯一索引。 CLUSTERED：用于指定创建的索引为聚集索引。 NOCLUSTERED：用于指定创建的索引为非聚集索引，为默认值。 index_name：索引名称。索引名称在表或视图中必须唯一，但在数据库中不必唯一。索引名称必须符合标识符的命名规则。 table_or_view_name：要建立索引的表或视图的名称。 column：索引中包含的列的名称。指定两个或多个索引，可以为指定的列组合值创建复合索引。 ASC | DESC：用于指定某个索引列以升序或降序方式排序。默认设置为ASC。

示例：

在SQL Console页签，输入如下在students表的id列上创建唯一索引index_id的SQL语句，单击执行，然后在执行确认对话框中，单击直接执行。

说明：本例在students表上创建非聚集唯一索引，该索引将自动检查表中id列是否有重复的值。

CREATE UNIQUE INDEX index_id ON students(id)

在表区域，单击 图标刷新，选择[dms].students>索引，您可以查看到新增的唯一索引index_id。

修改索引。

使用 ALTER INDEX 语句可以修改索引的定义，其基本语法格式：

ALTER INDEX{index_name|ALL} ON { REBUILD [[WITH([,…n])] |[ PARTITION = partition_number [WITH( [,…n])]]] | DISABLE | REORGANIZE [PARTITION = partition_number]

[WITH(LOB_COMPACTION= {ON|OFF})] | SET([,…n]) }[;]

参数说明：

index_name：索引的名称。 ALL：指定与表或视图相关联的所有索引。 object：索引所基于的表或视图的名称。 REBUILD：指定将使用相同的列、索引类型、唯一性属性和排序顺序重新生成索引。 rebuild_index_option：对填充因子等索引选项的重新设置。 PARTITION=partition_number：指定只重新生成或重新组织索引的一个分区。 single_partition_rebulid_index_option：单个分区重建索引参数。 DISABLE：将索引标记为禁用，从而不能由数据库引擎使用。 REORGANIZE：指定将重新组织的索引页级。 LOB_COMPACTION={ON | OFF}：指定压缩所有包括大型对象（LOB）数据的页。 set_index_option：指定不重新生成或重新组织索引的索引选项。

删除索引。

使用 DROP INDEX 语句可以删除当前数据库中一个或多个索引，其语法格式：

DROP INDEX {table_name | view_name}.index_name[…,n]

示例：

在SQL Console页签，输入如下删除students表上的index_id索引的SQL语句，单击执行，然后在执行确认对话框中，单击直接执行。

DROP INDEX students.index_id

在表区域，单击 图标刷新，选择[dms].students>索引，您可以查看到students表上的index_id索引已被删除。

2. 实验作业

表1 student_info表的数据

表2 curriculum表的数据

表3 grade表的数据

使用SQL语句ALTER TABLE分别删除studentsdb数据库的student_info表、grade表、curriculum表的主键索引。

使用SQL语句为curriculum表的课程编号创建唯一性索引，命名为cno_idx。

使用SQL语句为grade表的“分数”字段创建一个普通索引，命名为grade_idx。

使用SQL语句为grade表的“学号”和“课程编号”字段创建一个复合唯一索引，命名为grade_sid_cid_idx。

查看grade表上的索引信息。

使用SQL语句删除索引grade_idx。再次查看grade表上的索引信息。

实验链接：https://developer.aliyun.com/adc/scenario/01f4686f1f6048098d9323671db9b41c