项目一 任务5创建和管理索引
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
在视图中也可以建立索引，称பைடு நூலகம்视图索引
全文索引
全文索引是一种特殊类型的基于标记的功能性索引，是实现全文 检索功能的。全文检索只对字符模式进行检索
相关知识
3．创建索引的注意事项 ❖ 创建索引要花费时间和占用存储空间； ❖ 索引虽然加快了检索速度，却减慢了数据修改的速度。
以下情况适合创建索引
不考虑建立索引的情况
在索引名称中 为索引命名。
一、创建索引
创建索引的列 name。
【是唯一的】文 本框中选择“是” 来创建一个唯一
性约束。
图5-1创建表主键索引和唯一索引
一、创建索引
（2）用SQL Server Management Studio在已存在的表上创建索引
❖ 【训练5-2】为书刊数据表中的作者字段创建一个降序、填充因子为
▪ With子句中的Drop_Existing是指如果表中已经存在同名的索引则将其删除，重建 索引。
一、创建索引 二、管理索引
任务实施
任务实施
二、管理索引
1．查看和修改索引 ⑴ 使用SQL Server Management Studio查看和修改索引
✓在SQL Server
Management Studio对
一、创建索引 二、管理索引
任务实施
任务实施
一、创建索引
1. 使用SQL Server Management Studio向导建立索引
（1）用SQL Server Management Studio在建表时创建索引
❖ 【训练5-1】在建立读者借阅信息表readers时，按读者编号readerID
建立主键约束，索引方式为聚集索引；按读者姓名Name建立唯一性
则在查询时SQL Server要对整个表进行扫描。当表中的数 据很多时，搜索数据就需要很长的时间。索引是一种依赖 于表建立的、存储在数据库中的独立文件，它保存着表中 排序的索引列，并且记录了索引列在数据表中的物理存储 位置，实现了表中数据的逻辑排序。
相关知识
非聚集索引
唯一性索引
聚集索引 全文索引
视图索引
60%的非聚集索引。
INDEX_author
【索引名称】
索引类型
图5-2 新建索引（常规）
① 在对象资源管理器窗口中 选中要创建索引的表books， 并展开出现“索引”列； ② 用鼠标右击“索引”列，
在快捷菜单中选择【新建索引】 命令；打开【新建索引】对话 框， ③ 在【新建索引】对话框中
单击【添加】按钮，打开【从 ‘dbo.books’中选择列】对 话框，在该对话框列表中选择 要创建索引的列author，（可 以选择多列）。单击【确定】 按钮退出。
✓ 可以用一条DROP INDEX语句删除多个索引，索引之间要用逗号分开。
相关知识
4．索引名称更改
可以使用系统存储过程给索引更名，其语法格式如下：
EXEC Sp_rename ‘table_name.index_name’, ‘new_name’
✓table_name.index_name为原有索引名， ✓new_name为新索引名
索引的类型
✓ 索引是一种依赖于数据 库表建立的、存储在数 据库中的独立文件。
✓ 索引组织了一个数据表 中的关键值列表。通过 这个列表，SQL Server 可以快速地查询到需要 的数据页，从而得到查 询结果。
相关知识
2．索引的类型
类型 聚集索引 非聚集索引
唯一索引 视图索引
含义
在聚集索引中，行的物理存储顺序与索引逻辑顺序完全相同，即 索引的顺序决定了表中行的存储顺序，因为行是经过排序的，所 以每个表只能有一个聚集索引。
相关知识
二、索引的创建和使用 1．建立索引的T-SQL语句CREATE INDEX
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON { table_name | view_name }
( column_name [ ASC | DESC ] [ ,...n ] )
SQL_Server
数据库项目化教程
项目目录
一
创建图书管理数据库
二
使用图书管理数据库
三
管理图书管理数据库
四
开发图书管理数据库
项目一
创建图书管理数据库
任务目录
1
安装和使用SQL Server
2
创建图书管理数据库
3 创建和维护图书管理数据库表
4 维护数据完整性
5 创建和管理索引
任务五
创建和管理索引
索引； ✓DISABLE 表示禁用索引
相关知识
3．删除索引的T-SQL语句DROP INDEX
DROP INDEX table_name.index_name GO ✓table_name用于指定索引列所在的表； ✓index_name用于指定要删除的索引名称。
✓ DROP INDEX命令不能删除由CREATE TABLE或者ALTER TABLE命令创建 的主键或者唯一性约束索引，也不能删除系统表中的索引。
✓ 用作查询条件的列，如主键，由于主键可 ✓ 很少或从来不作为查询条件的列；
以唯一表示行，通过主键可以快速定位到 ✓ 在小表中通过索引查找行可能会比简单
表的某一行，因此，一般要在主键上创建
地进行全表扫描还慢；
索引；
✓ 只从很小的范围内取值的列；
✓ 定义外键的列可以建立索引，外键的列通 ✓ 数据类型为text、ntext、image或bit的列
约束，索引方式为非聚集索引。
① 在SQL Server Management Studio的对象资源管理器中，右击要建 立约束的表readers，在快捷菜单中选择【设计】命令，在表设计器中打 开该表。 ② 在表设计器中，选择“readerID”字段，在【表设计器】工具栏中选 择【设置主键】命令，则在“readerID”字段的前面多了一个小钥匙的 图标，表示已创建了主键。 ③ 在任意列上右击，在弹出的快捷菜单中选择【索引/键】命令，打开 【索引/键】对话框。 ④ 在【索引/键】对话框中单击【添加】按钮。创建索引。
一、创建索引
2. 使用T-SQL语句建立索引 ❖ 【训练5-3】为读者借阅卡信息表（readers）中的电话号（tel）创建
一个唯一性的非聚集索引。
USE library GO CREATE UNIQUE NONCLUSTERED INDEX Ix_tel On readers(tel) GO
▪ UNIQUE关键字代表创建唯一性索引，NONCLUSTERED关键字代表创建非聚集 索引，该关键字可以省略，SQL Server默认创建非聚集索引，Ix_tel是用户自定义 的索引名。
✓UNIQUE表示创建唯一性索引； ✓CLUSTERED表示创建聚集索引； ✓NONCLUSTERED表示创建非聚集索引； ✓ON 表示可以在表或视图上创建索引，这里指定表或视图名称和相应 的列名称； ✓index_name 用于指定所创建的索引名称； ✓ASC表示索引为升序排序，DESC表示索引为降序排序，默认为ASC。
相关知识
2．修改索引的T-SQL语句ALTER INDEX
ALTER INDEX { index_name | ALL } ON <object> { REBUILD [ WITH ( <rebuild_index_option> [ ,...n ] )] | DISABLE }
✓index_name为索引文件名； ✓object 为索引所在的对象名，即数据库中的指定表或视图名称； ✓REBUILD指定将使用相同的列、索引类型、唯一性属性和排序顺序重新生成
使用聚集索引 √ √ × √ × × √ √ ×
使用非聚集索引 √ × × × √ √ √ √ √
相关知识
二、索引的创建和使用
❖ 创建索引有直接和间接两种方法。
▪ 直接创建索引就是使用命令或者在SQL Server Management Studio 中直接创建索引；
▪ ·间接创建索引就是通过创建数据表而附加创建了索引，例如在表 中定义主键约束（Primary Key）或唯一性约束（Unique）时，系 统自动创建了索引。
读者借阅卡信息 表上所有索引的 名称、类型和建
索引的列
图5-5 利用存储过程查看索引信息
二、管理索引
⑶ 使用T-SQL语句禁用及重新生成索引
▪ 索引数据更新后可能会分散在硬盘的各个位置，由此 产生碎片，并影响查询性能。可以使用重新生成索引 来删除索引碎片。
▪ 重新生成索引将原有的索引删除并重新创建一相同的 索引。
象资源管理器窗口中，
可以展开表的索引列，
看到已创建的索引。
✓右键单击索引名称，
在弹出的快捷菜单中选
索引 选项
创建索引的 脚本
择【属性】命令，可以 打开【索引属性】对话
框。
图5-4 查看索引脚本
二、管理索引
⑵ 使用系统存储过程Sp_helpindex查看索引信息 USE library GO EXEC Sp_helpindex readers
统计项的非唯一、非聚集索引。 3．使用创建索引向导为读者信息表的姓名创建一个唯一性的非聚集索引。 4．使用T-SQL语句为书刊数据表中书的价格创建一个升序、填充因子为
70%的非聚集索引。 5．将读者信息表中的姓名索引改为非唯一、填充因子60%。
相关知识
一、索引概述 1．索引的概念 ❖ 对数据库最频繁的操作就是数据的查询，如果没有索引，
一、创建索引
【选项】
【设置填充因子】
图5-3 新建索引（选项）
✓【选项】选项卡，对创建的索 引进一步编辑
✓【设置填充因子】选项，它用 来指定在创建索引的过程中， 对各索引页的叶级进行填充的 程度。该项默认值为0，表示将 叶子节点索引页全部填满。设 置为0与设置为100意义完全相 同。
✓数据表中的数据不经常改动， 最好将填充因子设置得大一点， 相反则将填充因子设置得小一 点。填充因子只在创建索引时 才有用。
非聚集索引并不是在物理上排列数据，即索引中的逻辑顺序并不 等同于表中行的物理顺序，索引仅仅记录指向表中行的位置的指 针，这些指针本身是有序的，通过这些指针可以在表中快速定位 数据。
按照索引实现的功能还可以划分为唯一性索引和非唯一性索引。 一个唯一性索引能够保证在创建索引的列或多列的组合上不包括 重复的数据，聚集索引和非聚集索引都可以是唯一性索引。
能力目标和知识目标
能力目标
1． 能够使用SQL Server Management Studio在表中建 立必要的索引；
2．能够使用CREATE INDEX、 DROP INDEX等SQL语句建 立、修改和删除索引。
知识目标
1．熟悉索引的概念、作用和分类； 2．了解合理使用索引的准则。
任务导入
❖ 数据库设计者的一个重要责任就是正确定义具有优化功能的数据表。 SQL Server 提供了表的索引和关键字机制来帮助SQL Server优化查 询响应的速度。在前面的任务中我们已经创建了数据库和数据表，在 此基础上我们还需要建立必要的索引，以便优化数据库的性能，提高 数据的查询速度以及数据处理（包括数据表的连接等）的速度。在本 项目中我们的任务主要有以下几点：
1．在SQL Server Management Studio 中为数据表创建索引； 2．使用CREATE INDEX命令创建索引； 3．修改索引； 4．删除索引。
任务导入
❖ 对Library数据库进行如下操作： 1．为书刊数据表中的作者字段创建一个降序、填充因子为60%的非聚集
索引。 2．为读者信息表的部门编号创建一个降序、填充因子为60%、重新计算
常用于数据表与数据表之间的连接，在其
上不要创建索引，因为这些类型的数据
上建立索引可以加快数据表的连接；
列的数据量要么很大，要么很小，不利
✓ 频繁按范围搜索的列，如学生基本
于
相关知识
❖ 聚集索引查询速度更快，但只能创建一个； ❖ 非聚集索引维护比较容易，可以创建249个。
动作描述 列经常被分组排序 返回某范围内的数据 一个或极少不同值 少数目的不同值 大数目的不同值 频繁更新的列 外键列 主键列 频繁修改索引列
▪ CLUSTERED关键字代表创建聚集索引，DESC代表索引的排序方法是降序，默认 是升序ASC。
一、创建索引
❖ 【训练5-4】为书刊信息表中的书刊名称创建一个非唯一性的非聚集 索引。
USE library GO CREATE NONCLUSTERED INDEX Ix_bookname
On books(bookName) With Drop_Existing GO
全文索引
全文索引是一种特殊类型的基于标记的功能性索引，是实现全文 检索功能的。全文检索只对字符模式进行检索
相关知识
3．创建索引的注意事项 ❖ 创建索引要花费时间和占用存储空间； ❖ 索引虽然加快了检索速度，却减慢了数据修改的速度。
以下情况适合创建索引
不考虑建立索引的情况
在索引名称中 为索引命名。
一、创建索引
创建索引的列 name。
【是唯一的】文 本框中选择“是” 来创建一个唯一
性约束。
图5-1创建表主键索引和唯一索引
一、创建索引
（2）用SQL Server Management Studio在已存在的表上创建索引
❖ 【训练5-2】为书刊数据表中的作者字段创建一个降序、填充因子为
▪ With子句中的Drop_Existing是指如果表中已经存在同名的索引则将其删除，重建 索引。
一、创建索引 二、管理索引
任务实施
任务实施
二、管理索引
1．查看和修改索引 ⑴ 使用SQL Server Management Studio查看和修改索引
✓在SQL Server
Management Studio对
一、创建索引 二、管理索引
任务实施
任务实施
一、创建索引
1. 使用SQL Server Management Studio向导建立索引
（1）用SQL Server Management Studio在建表时创建索引
❖ 【训练5-1】在建立读者借阅信息表readers时，按读者编号readerID
建立主键约束，索引方式为聚集索引；按读者姓名Name建立唯一性
则在查询时SQL Server要对整个表进行扫描。当表中的数 据很多时，搜索数据就需要很长的时间。索引是一种依赖 于表建立的、存储在数据库中的独立文件，它保存着表中 排序的索引列，并且记录了索引列在数据表中的物理存储 位置，实现了表中数据的逻辑排序。
相关知识
非聚集索引
唯一性索引
聚集索引 全文索引
视图索引
60%的非聚集索引。
INDEX_author
【索引名称】
索引类型
图5-2 新建索引（常规）
① 在对象资源管理器窗口中 选中要创建索引的表books， 并展开出现“索引”列； ② 用鼠标右击“索引”列，
在快捷菜单中选择【新建索引】 命令；打开【新建索引】对话 框， ③ 在【新建索引】对话框中
单击【添加】按钮，打开【从 ‘dbo.books’中选择列】对 话框，在该对话框列表中选择 要创建索引的列author，（可 以选择多列）。单击【确定】 按钮退出。
✓ 可以用一条DROP INDEX语句删除多个索引，索引之间要用逗号分开。
相关知识
4．索引名称更改
可以使用系统存储过程给索引更名，其语法格式如下：
EXEC Sp_rename ‘table_name.index_name’, ‘new_name’
✓table_name.index_name为原有索引名， ✓new_name为新索引名
索引的类型
✓ 索引是一种依赖于数据 库表建立的、存储在数 据库中的独立文件。
✓ 索引组织了一个数据表 中的关键值列表。通过 这个列表，SQL Server 可以快速地查询到需要 的数据页，从而得到查 询结果。
相关知识
2．索引的类型
类型 聚集索引 非聚集索引
唯一索引 视图索引
含义
在聚集索引中，行的物理存储顺序与索引逻辑顺序完全相同，即 索引的顺序决定了表中行的存储顺序，因为行是经过排序的，所 以每个表只能有一个聚集索引。
相关知识
二、索引的创建和使用 1．建立索引的T-SQL语句CREATE INDEX
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON { table_name | view_name }
( column_name [ ASC | DESC ] [ ,...n ] )
SQL_Server
数据库项目化教程
项目目录
一
创建图书管理数据库
二
使用图书管理数据库
三
管理图书管理数据库
四
开发图书管理数据库
项目一
创建图书管理数据库
任务目录
1
安装和使用SQL Server
2
创建图书管理数据库
3 创建和维护图书管理数据库表
4 维护数据完整性
5 创建和管理索引
任务五
创建和管理索引
索引； ✓DISABLE 表示禁用索引
相关知识
3．删除索引的T-SQL语句DROP INDEX
DROP INDEX table_name.index_name GO ✓table_name用于指定索引列所在的表； ✓index_name用于指定要删除的索引名称。
✓ DROP INDEX命令不能删除由CREATE TABLE或者ALTER TABLE命令创建 的主键或者唯一性约束索引，也不能删除系统表中的索引。
✓ 用作查询条件的列，如主键，由于主键可 ✓ 很少或从来不作为查询条件的列；
以唯一表示行，通过主键可以快速定位到 ✓ 在小表中通过索引查找行可能会比简单
表的某一行，因此，一般要在主键上创建
地进行全表扫描还慢；
索引；
✓ 只从很小的范围内取值的列；
✓ 定义外键的列可以建立索引，外键的列通 ✓ 数据类型为text、ntext、image或bit的列
约束，索引方式为非聚集索引。
① 在SQL Server Management Studio的对象资源管理器中，右击要建 立约束的表readers，在快捷菜单中选择【设计】命令，在表设计器中打 开该表。 ② 在表设计器中，选择“readerID”字段，在【表设计器】工具栏中选 择【设置主键】命令，则在“readerID”字段的前面多了一个小钥匙的 图标，表示已创建了主键。 ③ 在任意列上右击，在弹出的快捷菜单中选择【索引/键】命令，打开 【索引/键】对话框。 ④ 在【索引/键】对话框中单击【添加】按钮。创建索引。
一、创建索引
2. 使用T-SQL语句建立索引 ❖ 【训练5-3】为读者借阅卡信息表（readers）中的电话号（tel）创建
一个唯一性的非聚集索引。
USE library GO CREATE UNIQUE NONCLUSTERED INDEX Ix_tel On readers(tel) GO
▪ UNIQUE关键字代表创建唯一性索引，NONCLUSTERED关键字代表创建非聚集 索引，该关键字可以省略，SQL Server默认创建非聚集索引，Ix_tel是用户自定义 的索引名。
✓UNIQUE表示创建唯一性索引； ✓CLUSTERED表示创建聚集索引； ✓NONCLUSTERED表示创建非聚集索引； ✓ON 表示可以在表或视图上创建索引，这里指定表或视图名称和相应 的列名称； ✓index_name 用于指定所创建的索引名称； ✓ASC表示索引为升序排序，DESC表示索引为降序排序，默认为ASC。
相关知识
2．修改索引的T-SQL语句ALTER INDEX
ALTER INDEX { index_name | ALL } ON <object> { REBUILD [ WITH ( <rebuild_index_option> [ ,...n ] )] | DISABLE }
✓index_name为索引文件名； ✓object 为索引所在的对象名，即数据库中的指定表或视图名称； ✓REBUILD指定将使用相同的列、索引类型、唯一性属性和排序顺序重新生成
使用聚集索引 √ √ × √ × × √ √ ×
使用非聚集索引 √ × × × √ √ √ √ √
相关知识
二、索引的创建和使用
❖ 创建索引有直接和间接两种方法。
▪ 直接创建索引就是使用命令或者在SQL Server Management Studio 中直接创建索引；
▪ ·间接创建索引就是通过创建数据表而附加创建了索引，例如在表 中定义主键约束（Primary Key）或唯一性约束（Unique）时，系 统自动创建了索引。
读者借阅卡信息 表上所有索引的 名称、类型和建
索引的列
图5-5 利用存储过程查看索引信息
二、管理索引
⑶ 使用T-SQL语句禁用及重新生成索引
▪ 索引数据更新后可能会分散在硬盘的各个位置，由此 产生碎片，并影响查询性能。可以使用重新生成索引 来删除索引碎片。
▪ 重新生成索引将原有的索引删除并重新创建一相同的 索引。
象资源管理器窗口中，
可以展开表的索引列，
看到已创建的索引。
✓右键单击索引名称，
在弹出的快捷菜单中选
索引 选项
创建索引的 脚本
择【属性】命令，可以 打开【索引属性】对话
框。
图5-4 查看索引脚本
二、管理索引
⑵ 使用系统存储过程Sp_helpindex查看索引信息 USE library GO EXEC Sp_helpindex readers
统计项的非唯一、非聚集索引。 3．使用创建索引向导为读者信息表的姓名创建一个唯一性的非聚集索引。 4．使用T-SQL语句为书刊数据表中书的价格创建一个升序、填充因子为
70%的非聚集索引。 5．将读者信息表中的姓名索引改为非唯一、填充因子60%。
相关知识
一、索引概述 1．索引的概念 ❖ 对数据库最频繁的操作就是数据的查询，如果没有索引，
一、创建索引
【选项】
【设置填充因子】
图5-3 新建索引（选项）
✓【选项】选项卡，对创建的索 引进一步编辑
✓【设置填充因子】选项，它用 来指定在创建索引的过程中， 对各索引页的叶级进行填充的 程度。该项默认值为0，表示将 叶子节点索引页全部填满。设 置为0与设置为100意义完全相 同。
✓数据表中的数据不经常改动， 最好将填充因子设置得大一点， 相反则将填充因子设置得小一 点。填充因子只在创建索引时 才有用。
非聚集索引并不是在物理上排列数据，即索引中的逻辑顺序并不 等同于表中行的物理顺序，索引仅仅记录指向表中行的位置的指 针，这些指针本身是有序的，通过这些指针可以在表中快速定位 数据。
按照索引实现的功能还可以划分为唯一性索引和非唯一性索引。 一个唯一性索引能够保证在创建索引的列或多列的组合上不包括 重复的数据，聚集索引和非聚集索引都可以是唯一性索引。
能力目标和知识目标
能力目标
1． 能够使用SQL Server Management Studio在表中建 立必要的索引；
2．能够使用CREATE INDEX、 DROP INDEX等SQL语句建 立、修改和删除索引。
知识目标
1．熟悉索引的概念、作用和分类； 2．了解合理使用索引的准则。
任务导入
❖ 数据库设计者的一个重要责任就是正确定义具有优化功能的数据表。 SQL Server 提供了表的索引和关键字机制来帮助SQL Server优化查 询响应的速度。在前面的任务中我们已经创建了数据库和数据表，在 此基础上我们还需要建立必要的索引，以便优化数据库的性能，提高 数据的查询速度以及数据处理（包括数据表的连接等）的速度。在本 项目中我们的任务主要有以下几点：
1．在SQL Server Management Studio 中为数据表创建索引； 2．使用CREATE INDEX命令创建索引； 3．修改索引； 4．删除索引。
任务导入
❖ 对Library数据库进行如下操作： 1．为书刊数据表中的作者字段创建一个降序、填充因子为60%的非聚集
索引。 2．为读者信息表的部门编号创建一个降序、填充因子为60%、重新计算
常用于数据表与数据表之间的连接，在其
上不要创建索引，因为这些类型的数据
上建立索引可以加快数据表的连接；
列的数据量要么很大，要么很小，不利
✓ 频繁按范围搜索的列，如学生基本
于
相关知识
❖ 聚集索引查询速度更快，但只能创建一个； ❖ 非聚集索引维护比较容易，可以创建249个。
动作描述 列经常被分组排序 返回某范围内的数据 一个或极少不同值 少数目的不同值 大数目的不同值 频繁更新的列 外键列 主键列 频繁修改索引列
▪ CLUSTERED关键字代表创建聚集索引，DESC代表索引的排序方法是降序，默认 是升序ASC。
一、创建索引
❖ 【训练5-4】为书刊信息表中的书刊名称创建一个非唯一性的非聚集 索引。
USE library GO CREATE NONCLUSTERED INDEX Ix_bookname
On books(bookName) With Drop_Existing GO