数据库技术与应用 索引 MMSQL 学习笔记7

7 索引

7.1 索引的概念

索引是一个单独的、物理的数据库结构，在SQL Server中，索引是为了加速对表中数据行的检索而创建的一种分散存储结构。它是针对一个表而建立的，每个索引页面中的行都含有逻辑指针，指向数据表中的物理位置，以便加速检索物理数据。因此，对表中的列是否创建索引，将对查询速度有很大的影响。

7.2 索引的优缺点

7.2.1 索引的优点

1.创建唯一性索引，保证数据库表中每一行数据的唯一性。
2.大大加快数据的检索速度，这也是创建索引的最主要原因。
3.加速表与表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4.在使用分组和排序子句进行数据检索时，同样可以减少查询中分组和排序的时间。
5.通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。

7.2.2 索引的缺点

1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚集索引，那么需要的空间就会更大。
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，降低了数据的维护速度。

7.3 索引设计

7.4 索引的分类

7.4.1 聚集索引

聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。
PRIMARY KEY 和 UNIQUE 约束
独立于约束的索引
索引视图

7.4.2 非聚集索引

非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。
从非聚集索引中的索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页是存储在堆中还是聚集表中。对于堆，行定位器是指向行的指针。对于聚集表，行定位器是聚集索引键。

7.5 索引的操作

7.5.1 索引的创建

7.5.1.1 以界面方式创建索引

视图的创建：
（1）启动SQL Server Management Studio，并连接到SQL Server 中的数据库。
（2）在“对象资源浏览器”中展开“数据库”节点，展开指定的数据库。
（3）然后展开要创建索引的表，在表的下级菜单中，鼠标右击“索引”，选择‘“新建索引”。
（4）点击“添加”，在该窗口中单击要添加索引键的表列。
（5）最后确认，确认就可以了。

7.5.1.2 使用T-SQL创建索引

1.为Student表的创建非聚集索引。
Create index IX_Stu_Sno1
On Student(Sno)
2.为Student表的创建唯一聚集索引。
Create unique clustered index IX_Stu_Sno2
On Student(Sno)
With IGNORE_DUP_KEY
3.为Student表的Sno列创建组合索引。
Create index IX_Stu_Sno3
On Student(Sno,Sname DESC)
With IGNORE_DUP_KEY
4.为Student表的创建一个填充因子为100的非聚集索引。
Create nonclustered index IX_Stu_Sno4
On Student(Sno)
with fillfactor=100
5.为Student表的Sno列创建唯一聚集索引，并且不能输入重复值。
Create unique clustered index IX_Stu_Sno5
On Student(Sno)
With IGNORE_DUP_KEY

无法对表创建多个聚集索引

7.5.2 索引的删除

7.5.1.1 以界面方式删除索引

7.5.1.2 使用T-SQL删除索引

可以同时删除多个不同表的索引在后面加（，表名.索引名）。

if exists(select * from sysindexes where name='索引名')
Drop index 表名.索引名

7.5.3 索引的修改

7.5.3.1 以界面方式修改索引

（1）启动SQL Server Management Studio，并连接到SQL Server 中的数据库。
（2）在“对象资源浏览器”中展开“数据库”节点，展开指定的数据库。
（3）展开表，选择要修改的表，鼠标右键，选择设计。
（4）弹出表结构设计窗体，鼠标右键，在“索引/键”对话框就可以修改索引的相关信息。

7.5.3.2使用T-SQL语句修改索引名称

EXEC sp_rename'表名.索引名','新索引名'

7.5.4 查看索引信息

7.5.3.1 以界面方式查看索引

（1）启动SQL Server Management Studio，并连接到SQL Server 中的数据库。
（2）在“对象资源浏览器”中展开“数据库”节点，展开指定的数据库。
（3）展开表，选择要修改的表，鼠标右键，选择设计。
（4）弹出表结构设计窗体，鼠标右键，在“索引/键”对话框就可以修改索引的相关信息。

7.5.3.2使用系统存储过程查看索引

exec sp_helpindex 表名

7.5.3.3使用系统表查看索引信息

select id from sysindexes
where id=(select id from sysobjects where name='表名')