SQL Server索引的创建及查询

最新推荐文章于 2024-04-18 16:49:18 发布

平平无奇的小颜

最新推荐文章于 2024-04-18 16:49:18 发布

阅读量6.6k

点赞数 1

分类专栏：学习记录文章标签： sql 数据库

本文链接：https://blog.csdn.net/qq_27250279/article/details/126502889

版权

学习记录专栏收录该内容

4 篇文章 0 订阅

订阅专栏

2.创建聚集索引，并为表使用由 3 个部分组成的名称

3.使用唯一约束创建非聚集索引并指定排序顺序

一、索引

索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。

设计良好的索引可以减少磁盘 I/O 操作，并且消耗的系统资源也较少，从而可以提高查询性能。

1.语法：

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name

ON <object> ( column_name [ ASC | DESC ] [ ,...n ] )

[ WITH <backward_compatible_index_option> [ ,...n ] ]

[ ON { filegroup_name | "default" } ]

2.索引类型

UNIQUE

唯一索引不允许两行具有相同的索引键值。视图的聚集索引必须唯一，唯一索引中使用的列应设置为 NOT NULL，因为在创建唯一索引时，会将多个 Null 值视为重复值

CLUSTERED(聚集索引)

使用场景

可用于经常使用的查询。
提供高度唯一性。
可用于范围查询，使用运算符（如 BETWEEN，、>>=、<和 <=）返回值范围。
返回大型结果集。
使用 JOIN 子句；一般情况下，使用该子句的是外键列。
使用 ORDER BY 或 GROUP BY 子句。

NONCLUSTERED(非聚集索引)

使用场景

使用 JOIN 或 GROUP BY 子句。
不返回大型结果集的查询。
包含经常包含在查询的搜索条件（例如返回完全匹配的 WHERE 子句）中的列。

*还有其他的索引类型，这里未列出

二、创建索引

1.创建非聚集索引

CREATE INDEX index1 ON schema1.table1 (column1);

2.创建聚集索引，并为表使用由 3 个部分组成的名称

CREATE CLUSTERED INDEX index1 ON database1.schema1.table1 (column1);

3.使用唯一约束创建非聚集索引并指定排序顺序

CREATE UNIQUE INDEX index1 ON schema1.table1 (column1 DESC, column2 ASC, column3 DESC);

4.在线创建索引

ONLINE = { ON | OFF }

指定在索引操作期间基础表和关联的索引是否可用于查询和数据修改操作。默认为 OFF。

ON 在索引操作期间不持有长期表锁。在索引操作的主要阶段，源表上只使用意向共享 (IS) 锁。这使得能够继续对基础表和索引进行查询或更新。对本地临时表创建索引时，无法将 ONLINE 设置为 ON。
OFF 在索引操作期间应用表锁可以防止所有用户在操作期间访问基础表

CREATE INDEX index1 ON schema1.table1 (column1) WITH (ONLINE = ON)

耗时参考：五千多万数据的表，用在线的方式创建索引用时11分钟左右

三、索引查询

索引的信息，可以使用目录视图、系统函数和系统存储过程

1.使用目录视图

查看表所有索引

 SELECT i.name AS index_name  
    ,i.type_desc  
    ,is_unique  
    ,ds.type_desc AS filegroup_or_partition_scheme  
    ,ds.name AS filegroup_or_partition_scheme_name  
    ,ignore_dup_key  
    ,is_primary_key  
    ,is_unique_constraint  
    ,fill_factor  
    ,is_padded  
    ,is_disabled  
    ,allow_row_locks  
    ,allow_page_locks  
FROM sys.indexes AS i  
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id  
WHERE is_hypothetical = 0 AND i.index_id <> 0   
AND i.object_id = OBJECT_ID('表名');

查看指定表所有索引和索引列

SELECT i.name AS index_name  
    ,COL_NAME(ic.object_id,ic.column_id) AS column_name  
    ,ic.index_column_id   
FROM sys.indexes AS i  
INNER JOIN sys.index_columns AS ic
    ON i.object_id = ic.object_id AND i.index_id = ic.index_id  
WHERE i.object_id = OBJECT_ID('表名');