数据库索引学习＆分享

本期将为大家分享一些up主之前关于数据库索引方面学习的整理，不能十分全面，主要偏向工作应用，求轻喷(￣￣)"

一、索引是什么？

在学习索引之前，我们先要知道什么是索引，为什么要用它。这样才会有往下研究的动力。那么什么是索引？索引可以带给我们什么？

索引是对数据库表中一列或多列的值进行排序的一种存储结构。在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录

索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。

简单来说，索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。

二、索引的作用

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

1、快速取数据； --索引的排序功能和存储逻辑

2、保证数据记录的唯一性；--唯一索引

3、实现表与表之间的参照完整性；--主键索引

4、在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间。--索引的排序功能和存储逻辑

以上是我们常见业务的索引使用场景，下文会具体分析。

三、索引的类型

索引是一个抽象的名次，它包含了很多具体的细分，不同的实现方式形成的不同类型的索引，也将在不同的场景中发挥各自的作用。在这里，我们根据数据库的功能，可以在数据库设计器中创建四种索引：普通索引、唯一索引、主键索引和聚集索引（非聚集索引）。

下面我依次做一个介绍。

1、普通索引

最基本的索引类型，没有唯一性之类的限制。普通索引可以通过以下几种方式创建：

创建索引，例如CREATE INDEX ON tablename (列的列表)；

修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表)； ALTER INDEX RENAME TO;[2]

创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) )；

2、唯一索引

唯一索引是不允许其中任何两行具有相同索引值的索引。

当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在 employee 表中职员的姓 (lname) 上创建了唯一索引，则任何两个员工都不能同姓。

对某个列建立UNIQUE索引后，插入新纪录时，数据库管理系统会自动检查新纪录在该列上是否取了重复值，在CREATE TABLE 命令中的UNIQE约束将隐式创建UNIQUE索引。

创建唯一索引的几种方式：

创建索引，例如CREATE UNIQUE INDEX ON tablename (列的列表)；

修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表); ；

创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) )；

3、主键索引

数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。

在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

提示尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键索引。

4、聚集索引（聚簇索引）

在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。

索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。聚集索引更适用于对很少对基表进行增删改操作的情况。

四、建立索引的思路

知道了索引的不同类型以及索引创建的语句，那么接下来我们就要了解需要索引的场景、索引创建的时机以及创建什么索引最恰当。

1、索引的场景

首先来归纳下需要索引的场景。一般来说，应该在这些列上创建索引：

2)、在经常需要搜索的列上，可以加快搜索的速度；

3)、在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

4)、在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

5)、在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

6)、在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

7)、在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

知道了索引的使用场景，那么建立什么样的索引最恰当呢？

2、建立索引的思路

1)、主键时常作为where子句的条件，应在表的主键列上建立聚簇索引，尤其当经常用它作为连接的时候。

2)、有大量重复值的列且该列经常涉及范围查询和排序、分组，或者非常频繁地被访问的列，可考虑建立聚簇索引。

3)、经常同时存取多列、且每列都含有重复值，可考虑建立复合索引来覆盖一个或一组查询，并把查询引用最频繁的列作为前导列，如果可能尽量使关键查询形成覆盖查询。

4)、如果知道索引键的所有值都是唯一的，那么确保把索引定义成唯一索引。

5)、在一个经常做插入操作的表上建索引时，使用fillfactor(填充因子)来减少页分裂，同时提高并发度降低死锁的发生。如果在只读表上建索引，则可以把fillfactor置为100。WITH (PAD_INDEX=ON, FILLFACTOR=50)

五、表和索引

一）、表

看到这，我们对索引的定义，作用，目的，使用也都有了一个初步的认识。基本也就可以在日常工作中使用起来了，相信可以对大部分的sql效率有明显的提升。那么我们是否止步于此呢，当然不行。正如正确使用索引可以提高表操作性能，正确的设置索引也可以提高我们索引的执行性能。所以，我们要做进一步的学习。

那么我们就从索引的使用对象——表来说吧，首先我们要正确认识表。

上图显示了表的组织。表包含在一个或多个分区中，每个分区在一个堆或一个聚集索引结构包含数据行。堆页或聚集索引页在一个或多个分配单元中进行管理，具体的分配单元数取决于数据行中的列类型。

我们先不理会聚集索引结构，就把表狭义的认为是堆结构，那么什么是堆结构。

堆是不含聚集索引的表。堆的 sys.partitions 中具有一行，对于堆使用的每个分区，都有 index_id = 0。默认情况下，一个堆有一个分区。当堆有多个分区时，每个分区有一个堆结构，其中包含该特定分区的数据。例如，如果一个堆有四个分区，则有四个堆结构；每个分区有一个堆结构。

如果一张表上没有聚集索引，表也被叫做堆。每张表要么是一个堆，要么是一个聚集索引。因此，尽管我们会描述索引的类型：聚集索引和非聚集索引。其实更重要的是，表有两种类型：聚集索引表和堆表。开发者经常会说一张表有或者没有聚集索引，更有意义的说法是，一张表是否是聚集索引表。

SQL Server在一张堆表中查询数据（除去使用非聚集索引）只有一个办法，从第一行开始，直到找到目标行。没有顺序，没有查询键，没有办法快速的定位要找的行。

所以，堆表的查询只能是一个顺序的全表遍历，效率可想而知。因此，就引出了我们接下来要讨论的两种索引结构，聚集索引和非聚集索引。

二）、索引

索引的机构由叶子和非叶子层组成（可参照下文的聚集索引和非聚集索引结构图）。尽管没有明显的说明，我们之前的级别主要集中于索引的叶子层。因此，聚集索引的叶子层就是表本身，每个叶子层的入口都是表中的一行。对于非聚集索引来说，在叶子层每行都有一个入口（过滤索引除外），每个入口由索引键列，可选的包含列，以及标签组成，标签的内存是聚集索引的键列，或者RID（Row ID）。

索引入口也叫做索引行，不管它是表的一行（聚集索引叶子入口），还是表中一行的引用（非聚集索引叶子层），还是指向更低级别（非叶子层）的一页。

非叶子层。电话本中，名叫“Meyer，Helen”的人，因为电话本是按照last name排序的，因此我们知道这个人应该再中间位置，直接跳到电话本的中间位置开始查找。但是SQL Server没有这种知识。它不知道哪一页是中间页，除非它从索引的开始访问到结束。因此，SQL Server在索引中构建了一些额外的结构。

非叶子层的入口只包含索引键的列和指向下一层页的指针。索引的包含列只存在于叶子层的入口，非叶子层的入口中没有这类信息。

索引中的每一页，除去根页，都包含两个额外的指针。一个指向下一页，一个指向上一页。页的双向链的结果就是，使得SQL Server可以正向或者反向扫描任何一层的页面。

非叶子层是构建在叶子层上的结构，使得SQL Server可以完成下面的工作：

以索引键的顺序维护索引的入口。

根据给定的索引键值，快速的找到叶子层。

三）、索引的优缺点

1、优点

a)、大大加快数据的检索速度;

b)、创建唯一性索引，保证数据库表中每一行数据的唯一性;

c)、加速表和表之间的连接;

d)、在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

2、缺点

a)、索引需要占物理空间。

b)、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

六、聚集索引和非聚集索引

为了更好的介绍索引，我使用了微软sqlserver的示范数据库。

这些例子使用的是微软的 AdventureWorks 示例数据库。我们主要使用销售订单部门。包含5张表：Customer, SalesPerson, Product, SalesOrderHeader, SalesOrderDetail。为了保持注意力的集中，我们使用部分的列。

AdventureWorks 设计的很规范，销售人的信息在三张表中都有：SalesPerson，Employee，Contact。在某些情况下，我们会把他们看成是一张表。下图是这些表之间的关系。

一)、非聚集索引

1、结构

要理解非聚集索引，还是要从它的存储结构来入手。

非聚集索引的根节点和中间节点是索引页，都只含下一层级的入口指针和入口值（位于存储位置的第一个键值）；非聚集索引的叶节点也是索引页，也存储有聚集索引和非聚集索引的键值；非聚集索引中的每个索引行（不论是根节点、中间节点还是叶节点）都包含非聚集键值和行定位符（本例为聚集索引键值），此定位符指向聚集索引或堆（没有聚集索引的表）中包含该键值的数据行。非聚集索引行中的行定位器可以是指向行的指针，也可以是行的聚集索引键，具体根据如下情况而定：

a)如果表是堆（意味着该表没有聚集索引），则行定位器是指向行的指针。该指针由文件标识符 (ID)、页码和页上的行数生成。整个指针称为行 ID (RID)；

b)如果表有聚集索引或索引视图上有聚集索引，则行定位器是行的聚集索引键（本例即为EmployeeId）。如果聚集索引不是唯一的索引，SQL Server 将添加在内部生成的值（称为唯一值）以使所有重复键唯一。此四字节的值对于用户不可见。仅当需要使聚集键唯一以用于非聚集索引中时，才添加该值。

c) SQL Server 通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。

2、测试

--Contact表的索引，做两份拷贝，一份建立FullName索引，一份不建立索引。

--在Contacts_index 表建立非聚集索引

测试一些简单的查询--非聚集索引

-- 开启统计

--测试一个完全覆盖的查询

第一个查询是一个覆盖索引的查询，获取contact表中lastname以S开头的记录的一部分列。下面是执行的信息。

覆盖查询的索引是个好东西。没有索引，就会进行全表扫描。2130行，表明以S开头的记录占到了10%的数据。

--测试一个非完全覆盖的查询

索引产生的影响 IO没有影响

在查询的过程中没有使用到索引。在这种情况下，SQL Server觉得使用索引查找，比不适用索引直接扫描，还要做更多的工作。

3、结论--非聚集索引

到目前为止，我们知道非聚集索引有下面的一些好处：

a)是一些有序的入口集合。

b)表中的每一行都有一个入口。

c)包含一个索引键和一个标签。

d)用户负责创建的。

e)SQL Server来维护。

f) SQL Server用来最小化查询尝试，来满足客户的请求。

当一个请求到达数据库的时候，SQL Server只有三种访问数据的方式：

a)访问非聚集索引，避免访问表。这只发生在索引包含了请求中的所有数据。

b)通过索引键访问非聚集索引，然后使用标签访问表中的行数据。

c)忽略非聚集索引，扫描表找到请求的行数据。

通常来说，第一种是理想的，第二种要比第三种好。在之前的介绍中，我们知道了如何增加索引的使用可能性，如何决定更高效的使用非聚集索引。但是我们需要知道更多的索引内部的细节。

二）、聚集索引

1.结构

不像非聚集索引，非聚集索引是独立的对象，有自己的存储空间，聚集索引和表是同一个。创建一个聚集索引的时候，你已经告诉SQL Server用key对表进行排序，并且在修改数据的时候维护排序。后面的级别或介绍到聚集索引的内部数据结构。现在，把聚集索引看做是一个排序的表。通过一行数据库的key，SQL Server可以快速的访问行数据，进而通过行来访问表。

再进一步去概括聚集索引：

聚集索引的入口就是表的行，在聚集索引的入口上没有标签信息。当SQL Server已经定位到一行的时候，不需要额外的信息来定位行数据。

聚集索引总是覆盖查询，因为聚集索引和表是同一个东西，表的每一列都在索引中。

表包含聚集索引，不影响你在表中建立非聚集索引的选择。

聚集索引的根节点和中间节点是索引页，都只包含下一层的入口指针和入口值（位于存储位置的第一个主键值）；聚集索引的叶节点就是数据页。聚集索引就是把数据按主键顺序存储；因为一张表中的数据只能有一个物理顺序，所以一张表只能有一个主键/聚集索引。

2、比较聚集索引表和堆表

为了评估聚集索引表和堆表的性能，我们拷贝了两份SalesOrderDetail表。一张表是堆表，一张表创建了聚集索引。两张表都没有非聚集索引。

在两张表中我们会执行三个相同的查询：一个是获取单行数据，一个是获取单个订单的所有数据，一个是获取同一个产品的所有数据。

--拷贝两个salesOrderDetail表，一个带索引，一个不带索引

-- 开启统计

-- 获取单个订单的所有数据

--堆表

--聚集索引表

-- 查询同一个产品的所有数据

3、结论

聚集索引表的顺序在你创建聚集索引的时候已经指定了，SQL Server负责维护它。表中的任意行都可以通过键来快速的定位。任意的多行，都可以通过键的范围来快速的定位。

每张表只能有一个聚集索引。哪些列作为聚集索引的键，在创建索引的时候是一个重要的决定。

七、数据库IO，存储单元

在理解了堆，以及两种索引的结构之后，我们再来看数据库的IO读写，就有迹可循了。

数据在逻辑上，而不是物理上，被分作很多文件，每块儿的大小是8K。这些8K大小的块儿被叫做页。因此第一个8K的文件就是page#0，接下来就是page#1，等等。一页是最小的IO单元。SQL Server每次IO读写都至少是一页。如果需要读取或者写入多个连续的页，SQL Server也可能会在一次IO中进行操作。

一页不仅仅是一个IO的单元，也是一个所有权的单元。如果一页包含表TableA的一行数据，它就只会包含表TableA中的行数据。如果一页包含一个非聚集索引的一个入口，它就只会包含这个非聚集索引的入口。除了数据，每页还包含一些头部信息，还有一些偏移的指针，用来帮助SQL Server定位页中的单行数据，或者是页中的入口信息。逻辑的顺序和物理的顺序没有必须是相同的需要。

SQL Server读取的不是行，读取的最小单位是页。SQL Server使用索引快读的访问行是一种误导的说法。正确的说法是，索引使得SQL Server快读的访问页，而不是行。一旦SQL Server往内存中加载一页或者更多的页，它会检查这些页，并且定位所需要的行。

同时，我们也要理解一下“分区”的概念。SQL Server在页之上还有一些逻辑的组，它把连续的8页叫做一个分区单元。正常来说，一个分区，和页一样，是一个所有权的单元。如果一个分区中的一页为表TableA或者索引IndexB所有，所有的8页也都为相同的所有者。一些非常小的表或者是索引除外，他们不能充满整个分区。在这种情况下，在同一个分区会出现多于一个的表或者索引。但是对大多数对象来说，分区还是一个所有权的单元。

说这么多，要记住的概念就是，SQL Server读取的不是行，读取的单位是一页或者更多页。页，是最小的IO单元，每页的大小是8K。一个分区包含8个连续的页。通常情况，一个分区，和他的页，只包含一个对象（堆表或者索引表）的行和入口。

八、唯一索引和唯一约束

一)、概念

唯一索引不同于其他索引，入口不允许有相同的索引键值。因为索引的每个入口都会映射表中的一行，不允许相同的索引入口，也就是不允许表中存在相同的行。这就是为什么唯一索引是强制主键和候选键约束的。

定义主键约束或者唯一索引约束，SQL Server会自动创建索引。你可以只包含索引，没有约束；但是不能只有约束，没有索引。在定义约束的时候，就会创建一个和约束同名的索引。删除约束之后，才能删除索引，因为没有索引，约束就不能存在。删除约束之后，关联的索引也会被删除。

每张表可以有多个唯一索引。AdventureWork数据库的Product表有四个唯一索引，在ProductID，ProductNumber，rowguid和ProductName列上都有。AdventureWork的设计者选择ProductID作为主键，其他三个作为“替代键”，有时候也叫做“候选键”。

二）、构建

--语句创建唯一索引

--或者是定义一个约束

在第一个列子中，你确保在产品表中没有相同的产品名称；第二个例子，确保产品表没有重复的ProductID。

因为定义主键约束或者是替代键约束的同时会创建索引，你必须在定义约束的时候指定必要的索引信息，就像ALTER TABLE中的CLUSTERED关键字。

如果表中包含违反约束或者索引限制的数据，在这样的表上使用create index语句会失败。

如果创建了索引，任何违反约束或者索引的数据操作都会失败。

三）、主键约束和唯一约束的不同

1）、主键约束不允许NULL值，唯一约束允许NULL值。但是，唯一约束视两个NULL为重复值，因此唯一约束列中只能存在一个NULL值。

2）、创建主键约束，顺便会创建聚集索引，以下情况除外：

表中已经包含聚集索引。

创建约束的时候指定了NONCLUSTERED关键字。

3）、创建唯一约束，顺便会创建非聚集索引，除非创建的时候指定了CLUSTERED关键字，并且表还没有聚集索引。

4）、每张表只能包含一个主键约束，可以包含多个唯一约束。

这里还有一点，唯一约束和唯一索引之间没有太大的区别。验证数据的方式相同，定义约束顺便创建的索引和直接创建的索引在查询优化方面是不同的。但是，如果数据完整性是目标的话，应该在列上创建唯一约束。这使得索引的目标很清晰。

九、包含列

一）、什么是包含列

在非聚集索引中有一些列，不是索引键的一部分，被叫做“包含列”。这些列不是键的一部分，不影响索引入口的排序。同样，我们将会看到，相比较键列，它们带来的消耗也较小。在创建非聚集索引的时候，在索引列之外，需要单独的指定包含列，就像下面的一样。

如果我们不指定包含列，索引可能是这样的。

但是，如果我们指定了包含列，索引就是下面的样子。

那么，有人就有疑问了，“为什么要有包含列？为什么不简单的OrderQty，UnitPrice和LineTotal加入索引键？”。

二）、包含列的好处

这些列不是索引键的一部分，它们不会影响索引入口的排序。反过来，减 少它们在索引中的消耗。举个例子，如果需要修改一行数据的ProductID或ModifiedDate的值，这行在索引中对应的入口就会被重新分配。但是如果修改UnitPrice的值，只会更新索引的入口，但是不需要移动；分配索引的入口带来的消耗会更小； 索引占用的空间会更小；索引的分布统计的维护会更容易。

那么什么如何判断一列什么时候作为索引键什么时候作为包含列呢？

决定一个索引列是否是索引键的一部分，还是只是一个包含列，在你做索引的决定的时候不是最重要的。也就是说，那些经常出现在select中的，而不是where子句中的列，最好是放在包含列中。

三）、比较三种索引场景

可以看到

第一次，需要全表扫描，每一行都会被读取，来判断是否满足查询的条件。

第二次，通过非聚集索引快速的定位，只有39次请求，但是还是要从表中获取其他列的信息，

第三次，非聚集索引包括了请求的全部信息，是一个最优的排序。直接跳到第一个入口，然后连续的读取39个入口，进行聚合计算，然后返回结果就行了。

测试第一个查询：从产品角度产生的全部活动

测试第二个查询：从日期角度产生的全部活动

扫描索引，而不是扫描表，有两个好处：

索引占用的空间比表小，需要的读取更少。

行已经被分组，需要的非读取活动更少。

经过查询统计分析，包含列使得非聚集索引可以覆盖各种查询，提高这些查询的性能，有时候是很吸引人的。包含列增加了索引的大小，增加了一些维护工作。在你创建非聚集索引的时候，尤其是包含外键的时候，问一问自己：“我应该在索引中增加哪些额外的列呢？”。

十、索引过滤

过滤一个索引的主要原因，是为了从索引中消除一个或者多个不经常被选择的值。看一下SalesOrderDetail表中的SpecialOfferID列，121317行的数据包含了12个不同的SpecialOfferID值，从1到16，每个值对应的行数如下

大部分的行，超过95%，SpecialOfferID的值是1.在SpecialOfferID列的非聚集索引对于SpecialOfferID=1的查询没有好处。查询将使用表扫描来查询115884行数据。但是，所以对于SpecialOfferID=5的查询是有好处的。

本文开头的创建索引对于115884行SpecialOfferID=1的数据行没有入口。因此索引很小，很高效，只包含5433个入口。

在我们的SalesOrderDetail例子中，需要过滤的主要值是“1”。在你自己的应用中，最常见的可能是NULL。在典型的事务数据库中，在可空的列中，如果null值占多数，NOT NULL就是例外。在这些列上创建索引的时候，要考虑过滤null值。

我们借助下面这个简单的根据ID查询的SQL来玩一玩索引的过滤。我们将六次执行下面的查询：

6次查询的结果如下

不管是否用到过滤选项，执行的效率是基本相似甚至相等的，但是过滤掉1的数据索引，可以为我们节省大量的索引空间在上面的例子中，对同一个索引来说，过滤列和索引键列是同一个列。当我们通过where子句指定过滤的时候，我们告诉SQL Server：“如果你查询那些SpecialOfferID1的行，这个索引有这些行的入口。”不管索引键是什么，让SQL Server知道这些信息都是有好处的。

在决定使用过滤索引的时候，要记住两个重要的问题。

1、SQL Server如何评估过滤的索引

你可能会很奇怪，把之前查询语句的where条件从SpecialOfferID1变成SpecialOfferID=2，就会防止SQL Server使用过滤的索引。这是因为SQL Server比较了select查询的where子句和create index的where子句，认为他们两个语法上是相等的，而不是比较逻辑的相等。因此，SQL Server没有意识到过滤的索引覆盖了查询。

另外，你不能通过复合的where子句，例如：where SpecialOfferID1 and SpecialOfferID=2来促使SQL Server使用过滤的索引。在后面的级别中，我们将会给出一些提示，教给你一些影响SQL Server选择索引的能力。现在，记住SQL Server在评估过滤索引的时候，做出的是语法的决定。

2、不要使用过滤的索引来弥补不好的数据库设计

在创建过滤索引的时候，不要创建索引来弥补违反三范式的数据库设计。

最后，对于索引过滤的结论：

过滤的索引消除了索引中无用的入口，产生的索引更小，更有利于查询。过滤的索引是通过在create index中指定where子句来实现的。在where子句中的列不同于索引键的列，也不同于include子句中的列。

如果一张表中的一个子集经常被访问，过滤的索引也能是一个覆盖的索引，也可以导致IO有一个相当大的减少。

不要将创建过滤索引作为正确设计数据库的替代选择。

十一、合并唯一索引和过滤索引

在上面提到的唯一索引中，只允许一个NULL值，经常会和一些业务需求有冲突。通常，在一列上对已经存在的值，我们会强制唯一性，但是允许其他行的这一列没有值。

例如，你是一个产品的供应商，你是从第三方的供应商拿货，将产品信息保存在名为ProductDemo的表中。表中的ProductID列保存你自己设计的ID，还有一个UPC（Universal Product Code），不是所有的产品都有UPC。

在第二列中，你需要强制UPC唯一，同时允许NULL值。最好的办法是提供一个合并唯一索引和过滤索引的功能。

现象：

--创建表

--创建唯一索引

--插入多条数据

--但是，当我们插入重复UPC的时候。

IGNORE_DUP_KEY：

在创建唯一索引的时候，可以使用 IGNORE_DUP_KEY选项，初始的创建索引语句应该是下面的样子：

这个选项的名字有一点误导，存在唯一索引的时候，不应该忽略重复键。更准确的说，在唯一索引中不允许重复键。这个选项只是在多行插入的时候，才是可用的，用来控制插入行为的。

IGNORE_DUP_KEY=OFF

整个insert语句会失败。会提示下面的错误信息。

Note: This choice is the default.

IGNORE_DUP_KEY = ON

只有重复的行会插入失败。会提示下面的错误信息。

Note: This choice cannot be used if the unique index is also a filtered index.

IGNORE_DUP_KEY选项只影响INSERT操作。会被update，create index和alter index语句忽略。IGNORE_DUP_KEY选项也可以在添加主键和唯一约束的使用使用。

结论：

尽管唯一索引的主要目的是提供数据的完整性，也可以帮助查询优化器决定获取数据的最优方法，即使在访问数据的时候没有用到索引。

唯一索引为主键和替代键约束提供支持。唯一索引可能存在对应的唯一约束，但是没有索引约束就不存在。

唯一索引也可以是一个过滤索引。这就允许在一列中既要强制唯一，也可以有多个NULL值。

IGNORE_DUP_KEY选项影响多行插入的行为。

十二、索引的深度

根页的位置和索引的其他信息存储在一张系统表中。当SQL Server需要访问给定索引键值的索引入口的时候，它就用自己的方式从根页开始，访问每一层的每一页，直到包含索引键入口的叶子层。在我们十亿行表的例子中，SQL Server访问到需要的叶子层入口只需要读取5页；在上图的例子中，只需要读取3页就可以了。在聚集索引中，叶子层的入口就是实际的数据行，在非聚集索引中，入口可能是聚集索引的键，也可能是RID（Row ID）。

层的数目，也叫做深度，AdventureWorks数据库中，没有深度超过3的索引。数据库中如果有很大的表，或者索引键的列很多，深度有可能会超过6或者更深。

sys.dm_db_index_physical_stats函数给我们提供了一些索引的信息，包括索引的类型，深度，和大小；是一个表值函数，可以执行查询。下面的例子就是查看SalesOrderDetail表的索引信息。

下面的代码会显示表的指定的索引的信息，SalesOrderDetail表的uniqueidentifier列的非聚集索引，结果中的一行就是索引的一层。

下面的代码会显示表的指定的索引的信息，SalesOrderDetail表的uniqueidentifier列的非聚集索引，结果中的一行就是索引的一层。

我们可以看出：

索引的叶子层有408页。

唯一的中间层只有2页。

根层只有1页

请记住，包含列只在非聚集索引中可用，他们只出现在叶子层的入口。在高层的入口中会忽略他们，这就是他们不增加非叶子层大小的原因。

索引的结构使得SQL Server可以快速的访问索引的入口。一旦发现入口，SQL Server就可以：

访问入口的数据行。

正向或者反向访问索引。

十三、重新组织和重新生成索引

一）、索引碎片

理解索引的重组和重新生成之前，我们要知道“索引碎片”的概念。无论何时对基础数据执行插入、更新或删除操作，SQL Server 数据库引擎都会自动维护索引。随着时间的推移，这些修改可能会导致索引中的信息分散在数据库中（含有碎片）。当索引包含的页中的逻辑排序（基于键值）与数据文件中的物理排序不匹配时，就存在碎片。 碎片非常多的索引可能会降低查询性能，导致应用程序响应缓慢。

二）、分析索引碎片

通常，小索引中的碎片是不可控制的。 小索引的页面有关存储在混合盘区中。 混合区最多可由八个对象共享，因此在重新组织或重新生成小索引之后可能不会减少该索引中的碎片。

-- 重新组织碎片索引

-- 重新组织表中的所有索引

-- 重新生成碎片索引

--重新生成表中的所有索引

十四、索引的最佳实践选择

1）、索引的主要目的是提高查询和操作数据的性能，除非你知道这些操作是什么，否则你没有希望改进他们。

2）、最好是在应用的开始就考虑，在设计和开发中加入。如果你继承了一个已经存在的数据库和应用，从两方面理解你继承的是什么：内部和外部。

索引太多和太少都是不好的。对表来说没有“最佳索引个数”这种说法。每张表的情况 都不同。但是如果你要在主键，候选键，合适的外键，潜在的查询列上建立索引，请在建立之前做一些分析。

3）、主键是关系理论的基础，所有关系系统的基本构成。没有主键的约束，或者是唯一索引，可能会导致意外的结果，或者不好的性能。

4）、让表成为聚集索引表而不是堆表。主要的好处是一个简单的事实，用户在查看表数据的时候肯定会以一个默认的顺序，所以就以哪个顺序来维护表。

5）、考虑在你的非聚集索引中添加包含列。因为一般的非聚集索引都是从某种角度查看表，或者是建立的外键的基础上，但是除了非聚集索引的键列，还会需要一些其他列，但是这些列不作为查询条件，只是需要显示或者统计它们，这时候，这些列就可以添加为包含列，就不用再去访问数据行了，直接在非聚集索引中就可以完成请求

终于码完了，以上就是up主对数据库索引相关的整理，大家有什么好的补充请留言和up主交流哈~~~（。＾＾）