signed

QiShunwang

“诚信为本、客户至上”

SQL server 索引

2021/3/21 8:58:14   来源:

文章目录

        • 文档
        • 创建索引
        • 查看索引`sp_helpindex`
        • 创建聚集索引`CLUSTERED`
        • 创建复合索引
        • 删除索引`DROP INDEX index ON table`Or`DROP INDEX CourseSelection.F_INDEX`
        • 索引重命名`sp_rename`
        • 创建一个填充索引
        • 禁用索引
        • 启用索引
        • 修改索引
        • 创建索引的默认值

文档

索引的分类
唯一索引(UNIQUE):每一行的索引值都是唯一的(创建了唯一约束,系统将自动创建唯一索引)
主键索引:当创建表时指定的主键列,会自动创建主键索引,并且拥有唯一的特性。
聚集索引(CLUSTERED):聚集索引就相当于使用字典的拼音查找,因为聚集索引存储记录是物理上连续存在的,即拼音 a 过了后面肯定是 b 一样。

非聚集索引(NONCLUSTERED):非聚集索引就相当于使用字典的部首查找,非聚集索引是逻辑上的连续,物理存储并不连续。
PS:聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个。

创建索引

默认创建的是非聚集索引(NONCLUSTERED)
[ ASC | DESC ] 索引列的升序或降序排序方向。 默认值为 ASC。

CREATE index test_index
ON Course(ID)

查看索引sp_helpindex

后面跟表名,查看这个表的索引信息

sp_helpindex Course

在这里插入图片描述
上图中的第一个索引PK__Course__3214EC27A83B3B4D就是系统自动为主键创建的索引

一个表中聚集索引只能有一个,唯一索引,非聚集索引可以有多个
重复创建聚集索引

CREATE unique CLUSTERED index test_index_unique_clustered
ON Course(ID)

在这里插入图片描述

创建聚集索引CLUSTERED

CREATE CLUSTERED INDEX cl_INDEX
ON Course(ID)

创建复合索引

CREATE INDEX index_NAME ON Student (ID)

CREATE INDEX index_NAME ON Student (ID DESC)

CREATE INDEX index_NAME ON Student (ID DESC,NAME DESC)

删除索引DROP INDEX index ON tableOrDROP INDEX CourseSelection.F_INDEX

DROP INDEX index_NAME ON Student
--OR
DROP INDEX Student.index_NAME

OR

DROP INDEX CourseSelection.F_INDEX

索引重命名sp_rename

语法
EXEC sp_rename '表名.索引名' , '新名' , 'INDEX'
结尾的INDEX表示是索引类型

EXEC sp_rename 'Student.index_NAME','NEW_IX_NAME','INDEX'

集聚索引的问题
聚集索引在table中插入额外的项来快速查找,但是查找快了,insert却满了,因为原本插入3项,索引会字动创建一项,就是4项
解决,insert的时候预留空间,把索引要创建的一项创建好,这要用到 索引填充PAD_INDEX = { ON | OFF }

PAD_INDEX = { ON | OFF }
指定索引填充。 默认为 OFF。关闭状态
ON
fillfactor 指定的可用空间百分比应用于索引的中间级页
OFF 或未指定 fillfactor
考虑到中间级页上的键集,将中间级页填充到接近其容量的程度,以留出足够的空间,使之至少能够容纳索引的最大的一行。
PAD_INDEX 选项只有在指定了 FILLFACTOR 时才有用,因为 PAD_INDEX 使用由 FILLFACTOR 指定的百分比。 如果为 FILLFACTOR 指定的百分比不够大,无法容纳一行,数据库引擎将在内部覆盖该百分比以允许最小值。 无论 fillfactor 的值有多小,中间级索引页上的行数永远都不会小于两行。
在向后兼容的语法中,WITH PAD_INDEX 等效于 WITH PAD_INDEX = ON------------------------------------------------------------------------------
填充因子(百分比)
FILLFACTOR =fillfactor
指定一个百分比,指示在数据库引擎创建或重新生成索引的过程中,应将每个索引页面的叶级填充到什么程度。 fillfactor 必须是 1100 之间的整数。 如果 fillfactor100,数据库引擎会创建完全填充叶级页的索引。
FILLFACTOR 设置仅在创建或重新生成索引时应用。 数据库引擎并不会在页中动态保持指定的可用空间百分比。 若要查看填充因子设置,请使用 sys.indexes 目录视图。

创建一个填充索引

CREATE CLUSTERED INDEX F_INDEX
ON CourseSelection(SID)
WITH(
	PAD_INDEX = ON,
	FILLFACTOR = 100 
)

禁用索引

--禁用名为 F_INDEX 的索引
alter index F_INDEX on CourseSelection disable

启用索引

--禁用名为 F_INDEX 的索引
alter index F_INDEX on CourseSelection REBUILD

修改索引

ALTER INDEX F_INDEX ON CourseSelection
REBUILD
	WITH(FILLFACTOR = 50)

创建索引的默认值

CREATE CLUSTERED INDEX F_INDEX
ON CourseSelection(SID)
CREATE CLUSTERED INDEX F_INDEX ON dbo.CourseSelection (  SID ASC  )  
WITH (  
	 PAD_INDEX = OFF ,
	 FILLFACTOR = 100  ,
	 SORT_IN_TEMPDB = OFF , 
	 IGNORE_DUP_KEY = OFF , 
	 STATISTICS_NORECOMPUTE = OFF ,
	 ONLINE = OFF , 
	 ALLOW_ROW_LOCKS = ON ,
	 ALLOW_PAGE_LOCKS = ON  )
ON [PRIMARY ]