SQL基础-索引-源动力

雨点打透心脏的1/2处 2023-10-14 09:00 33阅读 0赞

## 索引的基本概念 ##

### 什么是索引？ ###

索引可以帮助数据库引擎在表中查找数据而不需要扫描整个表。  
索引是减少磁盘IO和逻辑读次数的最佳方法之一。  
索引帮助我们提高获取数据的性能，但同时也会影响DML的性能。

### SQL Server支持2类索引： ###

*  聚集索引（Clustered Index：CI)
 *  非聚集索引（Non Clustered Index：NCI）

索引是按 B+ 树结构组织的

### 堆表（Heap Table） ###

*  堆表是不含聚集索引的表
 *  堆表在sys.partitions里有1条index\_id = 0 的记录
 *  SQL Server 使用 IAM 页在堆之间移动
 *  堆内的数据页和行没有任何特定的顺序，也不链接在一起
 *  数据页之间唯一的逻辑连接是记录在 IAM 页内的信息
 *  可在堆表上创建一个或多个非聚集索引

使用下列命令可以获得IAM页

DBCC IND('databasename','Tablename',-1)

上述查询的输出结果里，Page Type列值为10的记录就是IAM页

![format_png][]

select * from sys.partitions where object_id=object_id('students_heap')

![format_png 1][]

select a.* from sys.allocation_units a
    inner join sys.partitions b on b.hobt_id=a.container_id
    where b.object_id=object_id('students_heap')

![format_png 2][]

dbcc ind(testdb,students_heap,-1)

PageType=10的就是IAM页，IndexLevel=0的是叶子层

![format_png 3][]

### 聚集表（Clustered Table） ###

*  聚集表是含有聚集索引的表
 *  聚集表在sys.partitions里有1条index\_id = 1 的记录
 *  聚集索引基于数据行的键值在表内排序存储
 *  每个表只能有一个聚集索引
 *  在聚集索引中，叶节点包含基础表的数据页
 *  创建 PRIMARY KEY 约束时，将在列上自动创建- 唯一索引，且不允许为NULL。 默认情况下，此引是聚集索引，但是在创建约束时，可以指定创建非聚集索引
 *  在非 UNIQUE 的列上创建聚集索引，数据库引擎会自动向表添加一个 4 字节的唯一标识符列以使每个键唯一
 *  聚集索引键的最大字节数不能超过 900

![format_png 4][]

聚集表（Clustered Table） - 唯一列的聚集索引

![format_png 5][]

聚集表（Clustered Table） - 非唯一列的聚集索引

![format_png 6][]

### 非聚集索引（Non Clustered Index） ###

*  非聚集索引具有独立于数据行的结构，是主体表的子集
 *  非聚集索引在sys.partitions里index\_id > 1 的都有对应的1条记录
 *  非聚集索引页节点包含非聚集索引键值和指向表数据存储位置的行定位器
 *  对于堆，行定位器是指向行的指针（RID）。 对于聚集表，行定位器是聚集索引键
 *  可以向非聚集索引的叶级添加非键列以跳过现有的索引键限制，并执行完整范围内的索引查询
 *  每当修改了表数据后，都会自动维护索引中的数据
 *  非聚集索引的最大字节数不能超过 1,700 字节。 低于 SQL Server 2016 的所有版本对所有索引类型都支持 900 字节

![format_png 7][]

--堆表上的非聚集索引（非唯一）
    create index ix_name on students_heap(name)

![format_png 8][]

![format_png 9][]

## 索引的使用 ##

### 聚集索引的设计要求 ###

*  唯一性（Uniqueness）  
    允许在非唯一列创建聚集索引，但数据库引擎会自动向表添加一个 4 字节的唯一标识符列，与非唯一列组合当做聚集键，增加了聚集键的长度。
 *  静态的（Static）  
    聚集索引键应是静态的，不会被修改的列。在非静态列定义聚集索引时，会让UPDATE语句更加耗费资源，它需要把记录移到不同的页来保证聚集索引的逻辑顺序，同样非聚集索引的叶子层也要更新。
 *  聚集索引键大小（Size of the clustered index key）  
    聚集索引键的字节数应尽可能的小，这样索引页可以保存更多的索引行。聚集索引键大小的增加会导致中间层页数和索引深度的增加，导致IO操作的增加。
 *  连续性（Sequential）  
    把聚集索引定义在自增长列（连续的）是最佳做法。聚集索引定义在非连续列（如uniqueidentifier列）会带来碎片。一个非连续的聚集索引列会强制把记录插在中间用来保持数据的逻辑顺序。这会导致页分裂，也是造成外部和内部碎片的原因。

### 非聚集索引的设计要求 ###

**唯一索引**  
唯一索引能够保证索引键中不包含重复的值，从而使表中的每一行从某种方式上具有唯一性。

*  多列唯一索引能够保证索引键中值的每个组合都是唯一的。
 *  只要每个列中的数据是唯一的，就可以为同一个表创建一个唯一聚集索引和多个唯一非聚集索引。
 *  唯一索引能够确保定义的列的数据完整性。
 *  唯一索引提供帮助查询优化器生成更高效的执行计划的其他信息。
 *  如果数据中存在重复的键值，则不能创建唯一索引、UNIQUE 约束或 PRIMARY KEY 约束。
 *  唯一非聚集索引可以包括包含性非键列。

唯一索引可通过以下方式实现：

*  独立于约束的索引

Create Unique [Clustered | NonClustered] Index On TableName(ColName)

*  PRIMARY KEY 或 UNIQUE 约束

**唯一索引 - PRIMARY KEY 约束**

表通常具有包含唯一标识表中每一行的值的一列或一组列。 这样的一列或多列称为表的主键 (PK)，用于强制表的实体完整性。 由于主键约束可保证数据的唯一性，因此经常对标识列（identity）定义这种约束。

*  一个表只能包含一个主键约束。
 *  主键不能超过 16 列且总密钥长度不能超过 900 个字节。
 *  由主键约束生成的索引不会使表中的索引数超过 999 个非聚集索引和 1 个聚集索引。
 *  如果没有为主键约束指定聚集或非聚集索引，并且表中没有聚集索引，则使用聚集索引。
 *  在主键约束中定义的所有列都必须定义为不为 Null。 如果没有指定为 Null 性，则参与主键约束的所有列的为 Null 性都将设置为不为 Null。

ALTER TABLE TableName ADD  CONSTRAINT ConstName PRIMARY KEY CLUSTERED (ColName)

**唯一索引 - UNIQUE 约束**

约束是 SQL Server 数据库引擎 为您强制执行的规则。可以使用 UNIQUE 约束确保在非主键列中不输入重复的值。

*  UNIQUE 约束允许 NULL 值，这一点与 PRIMARY KEY 约束不同。 不过，当与参与 UNIQUE 约束的任何值一起使用时，每列只允许一个空值。 FOREIGN KEY 约束可以引用 UNIQUE 约束。
 *  在创建 UNIQUE 约束时，默认情况下将创建唯一非聚集索引，以便强制 UNIQUE 约束。 如果不存在该表的聚集索引，则可以指定唯一聚集索引。
 *  创建 UNIQUE 约束和创建独立于约束的唯一索引没有明显的区别。
 *  如果您的目的是要实现数据完整性，则应为列创建 UNIQUE 或 PRIMARY KEY 约束。 这样做才能使索引的目标明确。

ALTER TABLE TableName ADD  CONSTRAINT ConstName UNIQUE NONCLUSTERED ([ColName])

**唯一索引 - 堆表上的唯一非聚集索引**  
跟前面的 堆表上的非聚集索引（非唯一） 的区别是什么？

create unique index ux_name on students_heap(name)

![format_png 10][]

**唯一索引 - 堆表上的唯一非聚集索引**

跟前面的 堆表上的非聚集索引（非唯一） 的区别是什么？

create unique index ux_name on students_heap(name)

![format_png 11][]

**筛选索引**

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

筛选索引与全表索引相比具有以下优点：

*  提高了查询性能和执行计划质量
 *  减少了索引维护开销
 *  减少了索引存储开销

当列中的值大部分为 NULL 并且查询只从非 NULL 值中进行选择时，可以为非 NULL 数据行创建筛选索引；如果查询条件和过滤索引的条件完全一致，则过滤索引中的列可以不在索引列或包含列中  
参数化查询时，执行计划无法使用筛选索引，但动态生成的语句可以使用筛选索引

**带有包含列的索引**

当索引包含查询引用的所有列时，通常称为“覆盖索引”。

带有包含性非键列的索引可以显著提高查询性能。 因为查询优化器可以在索引中找到所有列值；不访问表或聚集索引数据，从而减少磁盘 I/O 操作。

*  将非键列包含在非聚集索引中，以避免超过当前索引大小的限制（最大键列数为 32，最大索引键大小为 1,700 字节，而在 SQL Server 2016 (13.x) 以前，最大键列数为 16，最大索引键大小为 900 字节）。 **数据库引擎 计算索引键列数或索引键大小时，不考虑非键列。**
 *  索引定义中非键列的顺序不会影响使用该索引的查询的性能。
 *  只能对非聚集索引定义非键列。
 *  除了 text、 ntext 和 image 之外的所有数据类型都可以用作非键列。
 *  只要允许将计算列数据类型作为非键索引列，从 image、 ntext 和 text 数据类型派生的计算列就可以作为非键索引列。
 *  除非先删除某一表的索引，否则无法从该表中删除非键列。
 *  带有包含列的索引能够帮助解决阻塞和死锁。

**带有包含列的索引和把非键列放在键列中的覆盖索引有什么区别？**

**带有包含列的索引**

create index ix_name_i on students(name) include(birthday)

![format_png 12][]

**非键列放在键列的覆盖索引**

create index ix_name_birthday on students(name,birthday)

![format_png 13][]

**索引键列的顺序**

*  将相等运算符的字段列放在最前面（索引字段的最左边）
 *  将不相等运算符的字段列放在最后面（索引字段的最右边）
 *  将选择的字段包含在索引的Include子句中
 *  若有几个相等运算符的字段，则根据这些字段的选择性排列，选择性高的放在最前面（行数较少的一般就是选择性比较高的）

**设计索引时，应考虑以下数据库准则：**

*  **对表编制大量索引会影响 INSERT、UPDATE、DELETE 和 MERGE 语句的性能，**因为当表中的数据更改时，所有索引都须适当调整。 例如，如果在多个索引中使用了某个列，并且执行了修改该列数据的 UPDATE 语句，则必须更新包含该列的每个索引以及基础的基表（堆或聚集索引）中的该列。
 *   *  **避免对经常更新的表进行过多的索引，**并且索引应保持较窄，就是说，列要尽可能少。
 *   *  \*\*使用多个索引可以提高更新少而数据量大的查询的性能。 \*\*大量索引可以提高不修改数据的查询（例如 SELECT 语句）的性能，因为查询优化器有更多的索引可供选择，从而可以确定最快的访问方法。
 *  **对小表进行索引可能不会产生优化效果，**因为查询优化器在遍历用于搜索数据的索引时，花费的时间可能比执行简单的表扫描还长。 因此，小表的索引可能从来不用，但仍必须在表中的数据更改时进行维护。

### 索引对执行计划的影响 ###

![format_png 14][]

![format_png 15][]

![format_png 16][]

![format_png 17][]

![format_png 18][]

![format_png 19][]

![format_png 20][]

[format_png]: https://img-blog.csdnimg.cn/img_convert/8eb39d791d47b45d64f57f2d95ee64a9.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/44b3eeb8a089f8e2ac4f3799155fa211.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/36c025be943a74ce70f0695f1140436e.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/f9532f817d335af6fead769b4e78ad89.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/8eceb9f1a736c8514d36097ae9015776.webp?x-oss-process=image/format,png
[format_png 5]: https://img-blog.csdnimg.cn/img_convert/03496c8fdcd58c3b9a2cb10718341f24.webp?x-oss-process=image/format,png
[format_png 6]: https://img-blog.csdnimg.cn/img_convert/882b7dadb7b49d897fb87eb33aab8698.webp?x-oss-process=image/format,png
[format_png 7]: https://img-blog.csdnimg.cn/img_convert/4064633906ef2367abeedca5cf05ddce.webp?x-oss-process=image/format,png
[format_png 8]: https://img-blog.csdnimg.cn/img_convert/6b932222c60f6036e7e7036deca0664f.webp?x-oss-process=image/format,png
[format_png 9]: https://img-blog.csdnimg.cn/img_convert/2f1b52076d5af106cb4e07740a10f69a.webp?x-oss-process=image/format,png
[format_png 10]: https://img-blog.csdnimg.cn/img_convert/3ffacd442be6ae4ea3443e890b4b6459.webp?x-oss-process=image/format,png
[format_png 11]: https://img-blog.csdnimg.cn/img_convert/2f2ca121bd280035ebc9dcf625d55a38.webp?x-oss-process=image/format,png
[format_png 12]: https://img-blog.csdnimg.cn/img_convert/51f6f26cfdc7226e997a5276cded5cd1.webp?x-oss-process=image/format,png
[format_png 13]: https://img-blog.csdnimg.cn/img_convert/1742c894a6c99b70ed072afc4ce0273e.webp?x-oss-process=image/format,png
[format_png 14]: https://img-blog.csdnimg.cn/img_convert/65313b0427f4d15c3533dccc2f007f4d.webp?x-oss-process=image/format,png
[format_png 15]: https://img-blog.csdnimg.cn/img_convert/a3b7d86db633a70d07324e9a4c4238cd.webp?x-oss-process=image/format,png
[format_png 16]: https://img-blog.csdnimg.cn/img_convert/77a42e47613e3061c0ab6fe6749d42dd.webp?x-oss-process=image/format,png
[format_png 17]: https://img-blog.csdnimg.cn/img_convert/1cd0d860d2a646a23e22feafbb104987.webp?x-oss-process=image/format,png
[format_png 18]: https://img-blog.csdnimg.cn/img_convert/b437c601c093f658a98c5b2b63f7baf7.webp?x-oss-process=image/format,png
[format_png 19]: https://img-blog.csdnimg.cn/img_convert/b43279d021c4bca88d2e70b8d135759f.webp?x-oss-process=image/format,png
[format_png 20]: https://img-blog.csdnimg.cn/img_convert/a474645f56f9ca1f7c70db527b332cdf.webp?x-oss-process=image/format,png