【SQL server速成之路】索引与数据完整性

最新推荐文章于 2024-06-24 10:23:49 发布

原创

最新推荐文章于 2024-06-24 10:23:49 发布 · 2.3k 阅读

111

88 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sqlserver

🎉个人主页：这个昵称我想了20分钟
✨往期专栏：【速成之路】jQuery

🎖️专栏：【速成之路】SQL server
🔓往期回顾：
【SQL server速成之路】数据库基础
 【SQL server速成之路】数据库和表（一）
【SQL server速成之路】数据库和表（二）
【SQL server速成之路】数据库的查询
 【SQL server速成之路】数据库的视图和游标
 【SQL server速成之路】T-SQL语言（一）
【SQL server速成之路】T-SQL语言（二）
【SQL server速成之路】函数

索引

在数据库系统中建立索引主要有以下作用：

快速存取数据；
保证数据记录的唯一性；
实现表与表之间的参照完整性；
在使用ORDER BY、GROUP BY子句进行数据检索时，利用索引可以减少排序和分组的时间。

注：

Sql server支持在表中任何列（包括计算列）上定义索引；
唯一索引：不会有两行记录的索引键值相同；
不唯一索引：多个行共享同一索引键值；
复合索引：多列组合创建的索引。

一.索引的分类

1. 聚集索引
聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表物理顺序与索引顺序一致。SQL Server 2012是按B树（BTREE）方式组织聚集索引的，B树方式构建为包含了多个节点的一棵树。顶部的节点构成了索引的开始点，叫做根。每个节点中含有索引列的几个值，一个节点中的每个值又都指向另一个节点或者指向表中的一行，一个节点中的值必须是有序排列的。指向一行的一个节点叫做叶子页。叶子页本身也是相互连接的，一个叶子页有一个指针指向下一组。
在这里插入图片描述

在这里插入图片描述

注：表中的顺序和索引顺序一致。

2. 非聚集索引

非聚集索引完全独立于数据行的结构。SQL Server 2012也是按B树组织非聚集索引的，与聚集索引不同之处在于：非聚集索引B树的叶节点不存放数据页信息，而是存放非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。

对于非聚集索引，表中的数据行不按非聚集键的次序存储。
在非聚集索引内，从索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页的存储方式是堆集还是聚集。对于堆集，行定位器是指向行的指针。对于有聚集索引的表，行定位器是聚集索引键，只有在表上创建聚集索引时，表内的行才按特定顺序存储。这些行按聚集索引键顺序存储。如果一个表只有非聚集索引，它的数据行将按无序的堆集方式存储。
在这里插入图片描述

注：

一个表中最多只能有一个聚集索引，可以有多个非聚集索引；
创建索引时先创建聚集索引，然后再创建非聚集索引。

二. 系统表sysindexes

系统表sysindexes的主要字段如下表所示。
在这里插入图片描述
用户创建数据库时，系统自动创建系统表，用户创建的每个索引均将在系统表中登记。

三、索引的创建

在xsbook数据库中，经常要对xs、book和jy这3个表查询和更新，为了提高查询和更新速度，可以考虑对3个表建立如下索引：

（1）对于xs表，按借书证号建立主键索引（PRIMARY KEY约束），索引组织方式为聚集索引；按姓名建立非唯一索引，索引组织方式为非聚集索引；
（2）对于book表，按ISBN建立主键索引或者唯一索引，索引组织方式为聚集索引；
（3）对于jy表，按借书证号+ISBN建立唯一索引，索引组织方式为聚集索引。

1．界面方式创建索引

在“对象资源管理器”中展开“数据库xsbook”→展开“表”中的“dbo.xs”→右击其中的“索引”项，在弹出的快捷菜单上选择“新建索引”菜单项的“非聚集索引”。这时，用户可以在弹出的“新建索引”窗口中输入索引名称（索引名在表中必须唯一），如ck_xs，（如果是唯一索引，需要勾选“唯一”复选框）。单击新建索引窗口的“添加”按钮→在弹出选择列窗口（如图所示）中选择要添加的列→添加完毕后，单击“确定”按钮。
在这里插入图片描述
除了使用上面的方法创建索引之外，还可以直接在表设计器窗口创建索引。在表设计器窗口创建索引的方法如下：

（1）右击表名，在弹出的快捷菜单中选择“设计”菜单项。
（2）在“表设计器”窗口中，选择需要创建索引的属性列，右击鼠标，在弹出的快捷菜单中选择“索引/键”菜单项。

2．使用SQL命令创建索引

使用CREATE INDEX语句可以为表创建索引。语法格式：

CREATE [ UNIQUE ] 			/*指定索引是否唯一*/
    [ CLUSTERED | NONCLUSTERED ] 	/*索引的组织方式*/
    INDEX <索引名> 			/*索引名称*/
    ON {
  
  [ <数据库名>. [ <架构名> ] . | <架构名>. ] <表名或视图名>}
	 ( <列名> [ ASC | DESC ] [ ,...n ] ) 	/*索引定义的依据*/
    [ WITH ( <relational_index_option> [ ,...n ] ) ]	/*索引选项*/