【SQL SERVER】索引

在做开发过程中经常会接触数据库索引，不只是dba才需要知道索引知识，了解索引可以让我们写出更高质量代码。

索引概述

       索引的存在主要为了提高数据检索速度，设计高效的索引对于获得良好的数据库和应用程序性能极为重要。

       索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息，就像平常我们用的新华字典的目录，假如新华字典没有目录有找一个字就必须从第一页一直翻到最后一页，这是多么令人绝望的事情。

　　索引是占有而外空间，是一种典型的空间换时间的做法，所以对待索引必须要保持敬畏之心，要建立在经常筛选的条件上，查询数据要时刻想着利用索引，竟然都花额外空间存储索引，不能让它白白浪费掉。

 

聚集索引

聚集索引基于数据行的键值在表内排序和存储这些数据行。 每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。 　

简单来说就是聚集索引和数据的存放顺序是一致的，聚集索引叶节点就是数据。

创建准则

1. 定义聚集索引键时使用的列越少越好

2. 唯一或包含许多不重复的值（若创建聚集索引时没使用唯一属性，sql server会自动添加一个4字节的唯一标识列）

3. 经常需要顺序访问数据

4. 经常用于对表中检索到的数据进行排序

5. 列大小不超过900字节

适合使用聚集索引情况

1. 使用运算符（如 between、>、>=、< 和 <=）返回一系列值

2. 返回大型结果集

3. 使用 join 子句；一般情况下，使用该子句的是外键列

4. 使用 order by 或 group by 排序/分组数据（因为分组数据也是要先排序）

不适合使用聚集索引情况

1. 频繁更改的列，每次更改都会导致索引页不断重新构建。

2. 若干列或若干大型列的组合，每次构建排序需要大量计算

tsql创建聚集索引

--唯一的聚集索引
create unique clustered index [ix_tablename_name] on [dbo].[tablename] ([fieldname] asc)

--不唯一的聚集索引
create clustered index [ix_tablename_name] on [dbo].[tablename] ([fieldname] asc)

 

非聚集索引

非聚集索引包含索引键值和指向表数据存储位置的行定位器。 可以对表或索引视图创建多个非聚集索引。 通常，设计非聚集索引是为改善经常使用的、没有建立聚集索引的查询的性能。

简单来说不是聚集索引的就是非聚集索引。额，好像没解释一样。。。非聚集索引的叶节点是定位器。

创建准则

1. 不超过1700字节（其他文档说是900字节，我测试sql server是1700字节）

2. 避免添加不必要的列，因为增加索引维护成本

3. 包含许多不重复的值，这样才能更好利用索引查询效率

4. 列的长度尽可能小

适合使用非聚集索引情况

1. 使用 join 或 group by 子句

2. 不返回大型结果集的查询

3. 经常包含在查询的搜索条件中的列

不适合使用非聚集索引情况

1. 列的重复值较少

2. 不是经常查询的搜索条件的列

3. 列的长度过长，因为增加索引维护成本

聚集索引与非聚集索引区别（知识有限，未必全，希望补充）

1. 聚集索引是按物理顺序排列，非聚集索引逻辑顺序排列

2. 聚集索引一个表只能有一个，非聚集索引可以多个

3. 聚集索引的叶节点是数据，非聚集索引的叶节点是定位器

4. 聚集索引不能附加信息，非聚集索引可以包含附加信息

tsql创建非聚集索引

create index ix_tablename_fieldname on datatable(fieldname desc)

 

唯一索引

唯一索引能够保证索引键中不包含重复的值，从而使表中的每一行从某种方式上具有唯一性。 只有当唯一性是数据本身的特征时，指定唯一索引才有意义。

保证索引键中不包含重复的值（包含null值）

创建准则

 1. 只有需要保证数据唯一性的情况下才使用

适合使用唯一索引情况

1. 需要保证数据唯一性

不适合使用唯一索引情况 

1. 不需要保证数据唯一性

tsql创建唯一索引

create unique index ix_tablename_fieldname on datatable(fieldname asc) 

 

筛选索引

 筛选索引是一种经过优化的非聚集索引，尤其适用于涵盖从定义完善的数据子集中选择数据的查询。 筛选索引使用筛选谓词对表中的部分行进行索引。

只把符合条件的数据做索引，相当于在一个表的子集做索引。从而达到下面几个好处

1. 提高了查询性能 ，因为索引页的数据比全表索引小

2. 减少索引维护开销，因为只有符合条件才会对索引页维护

3. 减少索引存储开销，道理跟上面一样

创建准则

1. 筛选的条件必须是明确的值

2. 通常来说经常查询出现的条件跟过滤条件符合

3. 经常检索的都是数据的子集

适合使用筛选索引情况

1. 经常筛选表的子集数据，例如通常我们只查询有效的订单，无效的订单很少查，或者基本不查，这种情况适合建立筛选索引

2. 只查最近数据，例如记录只查最近一个月，可以通过定期在数据库空闲的时重新维护筛选索引达到加快查询效率

不适合使用筛选索引情况

1. 经常查询条件包含筛选值外，这样导致走全表扫描（前提没其他索引覆盖到）

2. 查询条件不固定

tsql创建唯一索引

create index ix_tablename_fieldname on [dbo].[tablename](fieldname asc)  where state > 1

 

非聚集索引包含列

 通过将非键列添加到非聚集索引的叶级，扩展非聚集索引的功能。 通过包含非键列，可以创建覆盖更多查询的非聚集索引

通过把包含的列同时维护在索引页，达到当查询的数据都包含在索引中的数据的时候，因为在索引页找到所有数据，就不需要访问表的数据页，从而减少i/o操作，这种通常称为“覆盖查询”

创建准则

1. 必须至少定义一个键列

2. 在 create index 语句的 include 子句中定义非键列

3. 只能对表或索引视图的非聚集索引定义非键列

4. 允许除 text、 ntext和 image之外的所有数据类型

5. 精确或不精确的确定性计算列都可以是包含列

6. 不能同时在 include 列表和键列列表中指定列名

7. include 列表中的列名不能重复

8. 索引键列（不包括非键）必须遵守现有索引大小的限制

9. 所有非键列的总大小只受 include 子句中所指定列的大小限制；例如， varchar(max) 列限制为 2 gb

适合使用非聚集索引包含列

1. 筛选的列是索引键 && 查询的列都是包含的列

不适合使用非聚集索引包含列

2. 筛选的列不是索引键  ||  查询的列有不在包含列中的

tsql创建筛选索引

create  index ix_tablename_fieldname on datatable(field1 asc) include(field2)

转发请标明出处： https://www.cnblogs.com/wilsonpan/p/12625364.html

参考文章

sql server 索引体系结构和设计指南 - sql server | microsoft docs