sql server 索引总结一

2022-08-25 14:40:06

一、存储结构

　　在SQL Server中，有许多不同的可用排列规则选项。

　　二进制：按字符的数字表示形式排序(ASCII码中，用数字32表示空格，用68表示字母"D")。因为所有内容都表示为数字，所以处理起来速度快，遗憾的是，它并不总是如人们所想象，在WHERE子句中进行比较时，使用该选项会造成严重的混乱。

　　字典顺序：这种排序方式与在字典中看到的排序方式一样，但是少有不同，可以设置大量不同的额外选项来决定是否区分大小写、音调和字符集。

　　1、平衡树(B-树)

　　平衡树或B-树仅是提供了一种以一致且相对低成本的方式查找特定信息的方法。其名称中的"平衡"是自说明的。平衡树是自平衡的，这意味着每次树进行分支时都有接近一半的数据在一边，而另一半数据在另一边。树命名的由来是因为，如果绘制该结构，再倒过来，发现很像一棵树，因此称树。

　　平衡树始于根节点。如果有少量的数据，这个根节点可以直接指向数据的实际位置。

　　结构图:

　　因此，从根节点开始并浏览记录，直到找到以小于查找值的值开始的后一页。然后获得指向该节点的一个指针并且浏览它。直到找到想要的行。

　　当数据很多时，根节点中指向中间的节点(非页级节点)。非页级节点是位于根节点和说明数据的物理存储位置的节点之间的节点

　　　　根节点->中间节点(非叶级节点)[n个]->存储位置节点(叶级节点)

　　非叶级节点可以指向其他非叶级节点或叶级节点。叶级节点是从中可获得实际物理数据的引用的节点。

　　从上图可以看出，查找开始于根节点，然后移动到以等于或小于查找值的高值开始的同时也在下一级节点中的节点。然后重复这个过程-查找具有等于或者小于查找值的高起始值节点。继续沿着树一级一级往下，直到二级节点-从而知道数据的物理位置。

　　2、页拆分

　　所有这些页在读取方面工作良好-但在插入时会有点麻烦。前面提到B-树结构，每次遇到树中的分支时，因为每一边都大约有一半的数据，所以B-树是平衡的。另外，由于添加新数据到树的方法一般可避免出现不平衡，所以B-树有时被认为是自平衡的。

　　通过将数据添加到树上，节点终将变满，并且将需要拆分。因为在 SQL Server中，一个节点相当于一个页-所以这被称为页拆分。如图所示：

　　当发生页拆分时，数据自动地四处移动以保持平衡，数据的前半部分保留在旧页上，而数据的剩余部分添加到新页 - 这样就形成了对半拆分，使得树保持平衡。

　　如果考虑下这个拆分过程，将认识到它在拆分时增加了大量的系统开销。不只是插入一页还将进行下列操作：

　　创建新页
　　将行从现有数据页移动到新页上
　　将新行添加到其中一页上
　　在父节点中添加另一个记录项

　　注意后一条，如果在父节点中添加记录时，父页也满了引起拆分，整个过程会重新开始。甚至会影响到根节点。并且，如果根节点拆分，那么实际终会创建两个额外的页，因此只能有一个根节点，所以之前的根节点的页被拆分成两个页，而且称为树的新中间级别节点。然后创建全新的根节点，并且将有两个记录项，指向刚刚由根节点分拆出来的两个中间节点。

　　由上面的原理可以知道，当向树的上层移动时，页拆分的数量变得越来越少。因为下级的一个页拆分对上级来说是一条记录。

　　虽然SQL Server有许多不同类型的索引，但是所有这些索引都以某种方式利用这种平衡树方法。事实上由于平衡树的灵活特性，所有索引在结构上都非常类似，不过他们实际上还有一点点区别，并且这些区别会对系统的性能产生影响。

　　3、SQL Server中访问数据的方式

　　从广义上讲，SQL Server检索所需数据的方法只有两种：

　　使用全表扫描
　　使用索引

　　1、使用全表扫描

　　表扫描是相当直观。当执行表扫描时，SQL Server从表的物理起点处开始，浏览表中的每一行。当发现和查询条件匹配的行时，就在结果集中包含它们。关于表扫描很多说法都是效率低，但是如果表数据减少的情况下，实际上使用表扫描却是快的。

　　2、使用索引

　　在查询优化过程中，优化器查看所有可用的索引结构并且选择好的一个(这主要基于在连接和WHERE子句中所指定的信息，以及SQL Server在索引结构中保存的统计信息)。一旦选择了索引，SQL Server将在树结构中导航至与条件匹配的数据位置，并且只提取它所需的记录。与表扫描的区别在于，因为数据时排序的，所以查询引擎知道它何时到达正在查找的当前范围的下界。然后它可以结束查询，或者根据需要移至下一数据范围。EXISTS的工作方式是查到匹配的记录SQL Server就立即停止。使用索引所获得的性能与使用EXISTS类似甚至更好，因为查找数据的过程的工作方式是类似的；也就是说，服务器可能使用某种索引知道何时没有相关内容，并且立即停止。此外，可以对数据执行非常快速的查找(称为SEEK)，而不是在整个表中查找。

　　3、索引类型和索引导航

　　尽管表面上在SQL Server中有两种索引结构(聚集索引和非聚集索引)，但就内部而言，有3种不同的索引类型。

　　聚集索引
　　非聚集索引，其中非聚集索引又包括以下两种：

　　堆上的非聚集索引
　　聚集表上的非聚集索引

　　物理数据的存储方式在聚集索引和非聚集索引中是不同的。而SQL Server遍历平衡树以到达末端数据的方式在所有3种索引类型中也是不同的。

　　所有的SQL Server索引都有叶级和非叶级页，叶级是保存标识记录的“键”的级别，非叶级是叶级的引导者。

　　索引在聚集表(如果表有聚集索引)或者堆(用于没有聚集索引的表)上创建。

　　(1)、聚集表

　　聚集表是在其上具有聚集索引的任意表。但是它们对于表而言意味着以指定顺序物理存储数据。通过使用聚集索引键地标志独立的行-聚集键即定义聚集索引的列。

　　如果聚集索引不是的，那将怎样？如果索引不是索引，那么聚集索引如何用于地标志一行？SQL Server会在内部添加一个后缀到键上，以保证行具有的标识符。

　　(2)、堆

　　堆是在其上没有聚集索引的一个表。在这种情况下，基于行的区段、页以及行偏移量(偏移页顶部的位置)的组合创建的标识符，或者称为行ID(RID)。如果没有可用的聚集键(没有聚集索引)，那么RID是必要的内容。堆表并不是B树结构。

　　4、聚集索引

　　聚集索引对于任意给定的表而言是的，一个表只能有一个聚集索引。不一定非要有聚集索引。聚集索引特殊的方面是：聚集索引的叶级是实际的数据-也就是说，数据重新排序，按照和聚集索引排序条件声明的相同物理顺序存储。这意味着，一旦到达索引的叶级，就到达了数据。而非聚集索引，到达了叶级只是找到了数据的引用。

　　任何新记录都根据聚集列正确的物理顺序插入到聚集索引中。创建新页的方式随需要插入记录的位置而变化。如果新记录需要插入到索引结构中间，就会发生正常的页拆分。来自旧页的后一半记录被移到新页，并且在适当的时候，将新记录插入到新页或旧页。如果新记录在逻辑上位于索引结构的末端，那么创建新页，但是只将新记录添加到新页。

　　从数据插入的角度看，这里应该能看到用int类型作为聚集索引的好处。

　　为了说明索引是表的顺序，请看一下表：

　　然后在Id列建立聚集索引：

CREATE CLUSTERED
　　INDEX Index_Name ON Person(Id)　　--建立Id列聚集索引

　　执行查询语句：

select top 3 * from Person

DROP INDEX Person.Index_Name    --删除索引
    
　　CREATE CLUSTERED
　　INDEX Index_Name ON Person(Name)    --再在重建Name列聚集索引

　　再执行查询语句：

select top 3 * from Person

　　输出结果如下：

　　留意到同样的语句，返回已经改变。可以聚集索引是表的顺序，会影响到top语句。

　　5、导航树

　　在SQL Server中甚至索引也是存储在平衡树中，在理论上，平衡树在作为树分支的每个可能方向上总是具有一般的剩余信息。聚集索引的平衡树形式如下图所示。

　　在这里，执行对数字158-400的范围查询(聚集索引非常擅长的事情)，只需要导航到个记录，并且包含在该页上的所有剩余记录。之所以知道需要该页的剩余部分，是因为来自于上一级节点的信息也需要来自一些其他页的数据。因为这是有序表，所以可以确信它是连续的-这意味着如果下一页有符合条件的记录，那么这个页的剩余部分必须被包含。无需任何验证。

　　首先导航到根节点。SQL Server能够给予Sys.indexes系统元数据视图中保存记录项定位根节点。

　　光说不练，纯属诈骗，下面以一个1万行的PersonTenThousand表来说明B树结构对数据页读取的提升。

　　表的内容大致如下：

　　一开始这张表并没有任何索引：

　　由于此表上没有索引，因此只能够通过堆表扫描获得所需数据，因此，无论是检索Id，还是Name列，都要整张表扫描一次。因此预读，逻辑读都要读取所有的数据页。

　　下面在该表的Id列建立一个聚集索引：

CREATE CLUSTERED INDEX index1 ON PersonTenThousand(ID)

　　再来执行相同的查询：

　　我们看到，由于ID列是聚集索引，因此根据ID查找，B树结构的优点就充分发挥了出来，只需要2次物理读就能够定位到数据。

　　而Name列上没有索引，因此还是需要预读838次(还是聚集表扫描)才能定位到数据。

　　以上例子充分说明了B-树结构的优点。

　　6、非聚集索引

　　6.1 非聚集索引优点:

　　1、因为在SQL Server中一页只是8K，页面空间有限，所以一行所包含的列数越少，它能保存的行就越多。非聚集索引通常不包含表中所有的列，它一般只包含非常少数的列。因此，一个页上将能包含比表行(所有的列)更多行的非聚集索引。因此，同样读取一页，在非聚集索引中可能包含200行，但是在表中可能只有10行，具体数据有表行的大小以及非聚集列的大小确定。

　　2、非聚集索引的另一个好处是，它有一个独立于数据表的结构，所以可以被放置在不同的文件组，使用不同的I/O路径，这意味着SQL Server可以并行访问索引和表，使查找更快速。

　　下面说明一下，非聚集索引的好处：

　　假设有一个单列的表，共有27行，每一页上存了3行。没有顺序，假如我们要从中查找值为5的行，那么需要的读次数为9，因为它必须扫描到后一页，才能够确定所有页都不存在值为5的行了。

　　假如建立了非聚集索引：

　　再次查找值为5的行，那么需要的读次数为2，为什么？因为非聚集索引是有顺序的，当SQL Server读取到值为6的那一行时，就知道不必再读下去了。那么如果要读取值为25的页呢？还是需要9个读操作。因为它刚巧就在后一页。恰好这个东西，可以通过B树结构来优化。B树算法小化了定位所需的键值访问的页面数量，从而加速了数据访问过程。

　　6.2 非聚集索引的开销

　　索引给性能带来的好处有一定的代价。有索引的表需要更多的存储和内存空间容纳数据页面之外的索引页面。数据的增删改可能会花费更长的时间，需要更多的处理时间以维护不断变化的表的索引。如果一个INSERT语句添加一行到表中，那么它也必须添加一行到索引结构中。如果索引是一个聚集索引，开销可能会更大，因为行必须以正确的顺序添加到数据页面(当然分int聚集列和string聚集列会不同)。UPDATE和DELETE类似。

　　虽然索引对增删改有一定的影响，但是别忘了，要UPDATE或DELETE一行的前提是必须找到一行，因此索引实际上对于有复杂WHERE条件的UPDATE或DELETE也是有帮助的。在使用索引定位一行的有效性通常能弥补更新索引所带来的额外开销。除非索引设计不合理。

　　7、堆上的非聚集索引

　　在这里要说明一点，无论是在堆上还是在聚集列上，非聚集索引都是排序后存储的。按非聚集索引列排序。

　　堆上的非聚集索引和聚集索引在大多数方面以类似的方式工具。其差别如下:

　　叶级不是数据-相反，它是一个可从中获得指向该数据的指针的级别。该指针以RID的形式出现（堆上一RID出现，聚集表上以聚集键出现），这种RID由索引指向的特定行的区段、页以及行偏移量构成。即叶级不是实际的数据，使用叶级也仅仅比使用聚集索引多一个步骤。因为RID具有行的位置的全部信息，所以可以直接到达数据。

　　差了一个步骤，实际上差别的系统开销是很大的。

　　使用聚集索引，数据在物理上是按照聚集索引的顺序排列的。这意味着，对于一定范围的数据，当找到在其上具有数据范围起点的行时，那么很可能有其他行在同一页上(也就是说，因为他们存储在一起，所以在物理上已几乎到达下一个记录)。

　　使用堆，数据并未通过除索引外的其他方法连接在一起。从物理上看，没有任意种类的排序。这意味着从物理读取的角度看，系统不得不从整个文件中检索记录。实际上，很可能终多次从同样的页中取出数据。SQL Server没有方法指导它将需要回到该物理位置，因为在数据之间没有连接。因此，堆上的非聚集索引的工作方式是：通过扫描堆上的非聚集索引，找到(Row_Number行号)，每找到一个RID，再通过RID取得数据。如果搜索是返回多个记录，则性能可能比不上扫描全表。下图显示用堆上的非聚集索引执行与上面聚集索引相同的查询：

　　主要通过索引导航，一切都按以前的方式工作，以相同的根节点开始，，并且遍历数，处理越来越集中的页。直到到达索引的叶级。这里有了区别。以聚集索引的方式，能够正好在这里停止，而以非聚集索引的方式，则需要做更多的工作。如果索引是在堆上，那么只要在进入一个级别，获得来自叶级页的RID，并且定位到该RID-直到这时才可以直接获得实际的数据。

　　8、聚集表上的非聚集索引

　　使用聚集表上的非集群索引时，还有一些类似性-但同样也有区别。和堆上的非集群索引一样，索引的非叶级及诶单的工作与使用聚集索引时几乎一样。区别出现在叶级。

　　在叶级，与使用其他两种索引结构所看到的内容有相当明显的区别。聚集表上的非集群索引有另外一个索引来查找。使用聚集索引，当到达叶级时，可以找到实际的数据，当使用堆上的非集群索引，不能找到实际的数据，但是可以找到能够直接获得数据的标识符(仅仅多了一步)。使用聚集表上的非聚集索引，可以找到聚集键。也就是说，找到足够的信息继续并利用聚集索引。

　　以上理解，说白了就是，当使用非聚集索引时，就是遍历非聚集索引找到聚集索引，后多次采用聚集索引找到数据。

　　终结果如下图所示：

　　首先是一个范围搜索。在索引中执行一次单独的查找，并且可以浏览非聚集索引以找到满足条件(T%)的连续数据范围。这种能够直接到达索引中的特定位置的查找被称为seek。

　　然后第二个查找-使用聚集索引查找，第二种查找非常迅速：问题在于它必须执行多次。可以看到。SQL Server从个索引中查找检索列表(所有名字以"T"开始的列表)，但是该列表在逻辑上并没有以任意连续的方式与聚集键相匹配-每个记录单独地查找。图下图所示：

　　自然，这种多个查找的情况比一开始仅能使用聚集索引引入了更多的系统开销。个索引查找-通过非聚集索引的方法-只需要非常少的逻辑读操作。

　　注意上图，使用聚集表上的非聚集索引，找到的是一个聚集索引键的列表。然后用这个列表，逐个使用聚集索引查找到所需的数据。

　　注意，如果表没有聚集索引，建立了非聚集索引，那么非聚集索引使用的是行号，如果此时你又添加了聚集索引，那么所有的非聚集索引引用的RID都要改为聚集索引键。这对性能的消耗是非常大的，因此好先建立聚集索引，在建立非聚集索引。

关于索引的几个要点：

群集索引通常比非群集索引快(书签)。
仅在将得到别选择性的列(90%以上)上放置非群集索引。
所有的数据操作语言(DML:INSERT、UPDATE、DELETE、SELECT)语句可以通过索引获益，但是插入、删除和更新会因为索引而变慢。
索引会占用空间。
仅当索引中的列和查询相关时才使用索引。
索引的负面影响和它的正面影响一样多 - 因此只建立需要的索引。
索引可为非结构化XML数据提供结构化的数据性能，但是要记住，和其他索引一样，会涉及到系统开销。

　　在SQL Server中，非聚集索引其实可以看做是一个含有聚集索引的表，但相对实际的表来说，非聚集索引中所存储的表的列数要少得多，一般就是索引列，聚集键(或RID)。非聚集索引仅仅包含源表中的非聚集索引的列和指向实际物理表的指针。

二、非聚集索引之INCLUDE

　　非聚集索引其实可以看做一个含有聚集索引的列表，当这个非聚集索引中包含了查询所需要的所有信息的时候，则就不再需要去查基本表，仅仅做非聚集索引就能够得到所需要的数据。INCLUDE实际上也能称为覆盖索引，但它不影响索引键的大小。

　　先来看下面一张表：

　　此表大约是15万数据左右。聚集索引列是Id，我们先来在Name列建立一个非聚集索引。

CREATE NONCLUSTERED 
　　INDEX Index_Name ON Person(Name)

　　然后执行查询：

SELECT Name,Age FROM Person where Name = '欧琳琳'

　　执行计划如下：

　　上面的执行过程是，先扫描非聚集索引列，找到聚集索引，然后在通过聚集索引定位到数据。

　　下面我们删除掉刚才那个索引，再建过另外一个。

DROP INDEX Person.Index_Name    --删除非聚集索引Index_Name

　　CREATE NONCLUSTERED             --再重新建过一次，这次我们INCLUDE Age列
　　INDEX Index_Name ON Person(Name)
　　INCLUDE (Age)

　　现在我们再来看看刚才的查询的执行计划：

　　由于Age列也被INCLUDE进了非聚集索引INDEX_Name中，因此这次仅仅通过查找非聚集索引就能够得到所需的全部数据。不需要再扫描聚集索引了。明显这次查询要比刚才快。

　　要注意的是INCLUDE进来的列，并不作为索引使用，能当索引扫描的，只是索引列。

　　INCLUDE好在以下情况中使用：

你不希望增加索引键的大小，但是仍然希望有一个覆盖索引；
你打算索引一种不能被索引的数据类型(除了文本、ntext和图像)；
你已经超过了一个索引的关键字列的大数量(但是好避免这个问题)；

二、非聚集索引之覆盖

　　索引覆盖指的是：建立的索引使得-SQL查询不用到达基本表仅仅通过索引查找就得到了所需数据。　　

　　如果查询遇到一个索引并且完全不需要引用数据表就得到了所需数据，那么这个索引就可以称为覆盖索引。覆盖索引对于减少查询的逻辑读是一种有用的技术。

　　下面删除之前创建的索引，在来看看索引的覆盖。

CREATE NONCLUSTERED INDEX INDEX_NAME ON Person(Name,Age)

　　SELECT Name,Age FROM Person WHERE Name = '欧琳琳'

　　看看执行计划：

　　可以看到，也是仅仅查找了非聚集索引就得到了结果。效率非常快。

　　下面来看看覆盖和前面的INCLUDE有什么区别呢？我们将搜索条件改为Age。

　　覆盖索引：

　　INCLUDE：

　　留意一下，INCLUDE是聚集表扫描了，而覆盖索引依然使用非聚集索引就找到了结果。

　　因此可以得出结论，INCLUDE列并不能当索引键使用。

　　为了利用覆盖索引，要注意SELECT语句的清单，应尽可能使用较少的列来保持小的覆盖索引的尺寸，使用INCLUDE语句来添加的列这时候才有意义。

　　在建立许多覆盖索引之前，考虑SQL Server如何有效和自动地使用索引交叉来为查询即时创建覆盖索引。

三、非聚集索引的交叉

　　如果一个表有多个索引，那么SQL Server可以使用多个索引来执行一个查询。SQL Server可以利用多个索引，根据每个索引选择小的数据子集，然后执行两个子集的一个交叉(即只返回满足所有条件的那些行)。SQL Server可以在一个表上开发多个索引，然后使用一个算法来在两个子集中得到交叉(可以理解为求交集)。

　　我们先删除掉前面建立的索引，再来新建过：

　　非聚集索引的本质是表，通过额外建立表使得几个非聚集索引之间进行像表一样的Join,从而使非聚集索引之间可以进行Join来在不访问基本表的情况下给查询优化器提供所需要的数据。

　　为了增进一个查询的性能，SQL Server可以在表上使用多个索引。因此，考虑创建多个窄索引来代替宽的索引键。SQL Server能够在需要的时候一起使用它们，当不需要时，查询可以从窄索引中获益。在创建一个覆盖索引时，需要确定索引的宽度是否可以接受，使用包含列是否可以完成任务。如果不行则确定现有的包含大部分覆盖索引所需要的列的非聚集索引。如果有可能，适当重新安排现有非聚集索引的列顺序，使优化器能够考虑两个非聚集索引之间的的一个索引交叉。

　　有时候，可能必须为一下原因创建一个单独的非聚集索引：

　　重新排列现有索引中的列不被允许；
　　覆盖索引所需要的一些列不能被包含在现有的非聚集索引中；
　　两个现有非聚集索引中的总列数可能多于覆盖索引所需要的列数；

　　在这些情况下，可以在剩下的列上创建非聚集索引。如果新索引符合和现有索引符合覆盖索引的要求，优化器将能够使用索引交叉。在为新确定列及其顺序时，也要注意其他查询，以尝试使其大化。

四、非聚集索引的连接

　　索引连接是索引交叉的特例，它将覆盖索引技术应用到索引交叉。如果没有单个覆盖查询的索引而存在多个索引一起可以覆盖该查询，SQL Server可以使用索引连接来完全满足查询而不需要转到基本表。

　　非聚集索引的连接实际上是非聚集索引的交叉的一种特例。使得多个非聚集索引交叉后可以覆盖所要查询的数据，从而使得从减少查询基本表变成了完全不用查询基本表。

--建立两个非聚集索引，一个在Name列，一个在INSiteId列
　　CREATE NONCLUSTERED INDEX INDEX_Name ON Person(Name) INCLUDE(Age)　　--索引还是刚才的索引，但是包含多一列
　　CREATE NONCLUSTERED INDEX INDEX_INSiteId ON Person(INSiteId) INCLUDE(Height)　　--同上

　　SELECT Name,Age,Height,INSiteId FROM Person WHERE INSiteId > 5155400 AND Name = '简单' 　　--注意条件，索引连接刚好能覆盖所需数据，从而避免查找基本表

　　查看结果：

　　索引交叉和索引连接有什么区别呢？前面说到果，索引连接是索引交叉的特例。索引连接在交叉了之后，不用再转到基本表，少了一步书签查找。而索引交叉之后，还有一步书签查找转到基本表获得数据，因为索引交叉的返回列并不能完全符合SELECT的列。

五、非聚集索引的过滤

　　过滤索引是使用过滤器的非聚集索引，这个过滤器基本上是一个WHERE子句，用来在可能没有很好选择性的一个或多个列上创建一个高选择性的关键字组。

　　例如，一个具有大量NULL值的列可能被存储为稀疏列来降低这些null值的开销。在这个列添加一个过滤索引将使你拥有在不是null的数据上的索引。

　　在下面的所使用的Person表中，Name列有超过50%是NULL值，执行查询：

SELECT Name,Age FROM Person WHERE Name IS NOT NULL

　　这是一个聚集表扫描，并没有有效地使用索引。

　　当我们建立非聚集索引，且加上过滤后：INCLUDE()是为了形成覆盖索引。

CREATE NONCLUSTERED INDEX INDEX_Name ON Person(Name) INCLUDE(Age) WHERE Name IS NOT NULL　　--过滤的索引上过滤掉NULL值的行

　　在我的数据库当中，建立索引，加不加过滤没太大区别(因为很遗憾，Name列基本上没有NULL的)，但是当过滤条件IS NOT NULL能够过滤很多条数据的时候，这时过滤的作用才能够展示出来。如果过滤条件，能够筛选掉很多条数据，那么性能无疑会大有提升。

　　过滤索引再许多方面带来回报：

　　减少索引尺寸从而增进查询效率；
　　建立更小的索引降低存储开销；
　　因为尺寸减小，降低了索引维护的成本；

　　实际上，索引的维护主要包括以下两个方面：

　　页拆分
　　碎片

　　这两个问题都和页密度有关，虽然两者的表现形式在本质上有所区别，但是故障排除工具是一样的，因为处理是相同的。

　　对于非常小的表(比64KB小得多)，一个区中的页面可能属于多余一个的索引或表---这被称为混合区。如果数据库中有太多的小表，混合区帮助SQL Server节约磁盘空间。

　　随着表(或索引)增长并且请求超过8个页面，SQL Server创建专用于该表(或索引)的区并且从该区中分配页面。这样一个区被称为统一区，它可以为多达8个相同表或索引的页面请求服务。

一、碎片

　　当数据库增长，页拆分，然后删除数据时，就会产生碎片。从增长的方面看，平衡树处理得很不错。但是对于删除方面，它并没有太大的作用。终可能会出现这种情况，一个页上有一条记录，而另一个页上有几个记录。在这种情况下，一个页上保存的数据量只是它能够保存总数据量的一小部分。

　　1、碎片会造成空间的浪费，SQL Server每次会分配一个区段，如果一个页上只有一条记录，则仍然会分配整个区段。

　　2、散布在各处的数据会造成数据检索时的额外系统开销。为了获取需要的10行记录，SQL Server不是只加载一个页，而是可能必须加载10个页来获取相同的信息。并不只是读取行导致了这一结果，在读取行前，SQL Server必须先读取页。更多的页意味着更多的工作量。

　　但是碎片也不只是有坏处，比如一个插入非常频繁的表就很喜欢碎片，因为在插入数据时几乎不用担心页拆分的问题。所以大量的碎片意味着较差的读取性能，但也意味着极好的插入性能。

　　关于碎片的理解，找到了数据库牛人CareySon的这篇文章 T-SQL查询—SQL Server索引中的碎片和填充因子，在这里消化一下

　　碎片分两种，外部碎片和内部碎片

　　外部碎片：

　　外部碎片指的是页拆分而产生的碎片。如向表中插入一行，而这一行导致现有的页空间无法容纳新插入的行，则导致页拆分。

　　新的页不断随数据的增长而产生，而聚集索引要求行之间连续，所以如果聚集索引不是自增列，页拆分后和原来的页在磁盘上并不连续-这就是外部碎片。 　　由于页拆分，导致数据在页之间的移动，所以如果插入更新等操作经常需要分页，则会大大消耗IO资源，造成性能下降。　　对于查找连说，在有特定搜索条件，如where子句有很细的限制或者返回无序结果集时，外部碎片并不会对性能产生影响。但如果要返回扫描聚集索引而且查找连续页面时，外部碎片就会产生性能上的影响。所以当要读取相同的数连续的数据时需要扫描更多的页，更多的区。而且连续数据不能预读，造成额外的物理读，增加磁盘IO。通常，外部碎片过多会造成频繁的区切换。

　　如果页面连续排序，预读功能可以提前读取页面而不需要太多的磁头移动。

　　内部碎片：　　

　　内部碎片是页拆分后，导致索引页的数据并不满，有空行。同样读取一个索引页，却只能拿到x%的数据。


--新建一张表
CREATE TABLE Person
(
    Id    int,
    Name    char(999),
    Addr    varchar(10)
) 
--聚集索引
CREATE CLUSTERED INDEX CIX ON Person(Id) 
--插入8条数据
DECLARE    @var INT
SET @var=100
WHILE(@var < 900)
BEGIN
INSERT INTO Person(Id,Name,Addr)
VALUES(@var,'xx','')
SET @var = @var+100
END

　　这个表每个行由int(4字节),char(999字节)和varchar(10字节组成），所以每行为1003个字节,则8行占用空间1003*8=8024字节加上一些内部开销，可以容纳在一个页面中。(原来这个表和数据搞得还挺巧的)。

　　执行查看语句：

SELECT page_count,avg_page_space_used_in_percent,record_count,avg_record_size_in_bytes,avg_fragmentation_in_percent,fragment_count 
　　FROM sys.dm_db_index_physical_stats
　　(DB_ID('Nx'),object_id('dbo.Person'),NULL,NULL,'sampled')

　　示例如下：

　　其中page_count是查看占用了多少个页，而第二个参数表示该页空间的使用率。因此从以上信息可以获得，这8条数据是放在一个页上，而且该页的空间使用率已经是百分之百了。

　　现在将其中一行的Addr改长一点：

UPDATE Person SET Addr = '广东广州' where Id = 100

　　则再执行检查索引语句：

　　可以看到，这个表已经有了两页，页面平均使用为50%左右。但是明显也造成了碎片，在列avg_fragmentation_in_percent上可以看到，碎片大约为50%。

　　页拆分后的示意图如下：

　　这个时候，继续插入数据，碎片会上升。在又插入了至达到48条记录后，碎片程度如下：

　　这个时候，执行一个查询计划，查看下IO性能：

　　可以看到I/O下降了不少。

二、元数据函数sys.dm_db_index_physical_stats分析碎片

　　SQL Server提供了一种特殊的元数据函数sys.dm_db_index_physical_stats，它有助于确定数据库中的页和区段有多满。然后用该信息作出一些维护数据库的决策。

　　该函数语法如下：

sys.dm_db_index_physical_stats(
    {<database id> | NULL |  | DEFAULT},
    { <object id> | NULL |  | DEFAULT },
    { <index id> } | NULL |  | -1 | DEFAULT },
    { <partition no> | NULL |  | DEFAULT },
    { <mode> | NULL | DEFAULT }
)

　　下面假设从SmartScan中获取所有的索引信息：

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'Nx');
SET @object_id = OBJECT_ID(N'Account')
SELECT database_id,object_id,index_id,index_depth,avg_fragmentation_in_percent,page_count
FROM sys.dm_db_index_physical_stats(@db_id,@object_id,NULL,NULL,NULL);

　　下面看看统计信息的说明：

列名

数据类型

说明

database_id

smallint

表或视图的数据库 ID。

object_id

int

索引所在的表或视图的对象 ID。

index_id

int

索引的索引 ID。

0 = 堆。

partition_number

int

所属对象内从 1 开始的分区号；表、视图或索引。

1 = 未分区的索引或堆。

index_type_desc

nvarchar(60)

索引类型的说明：

HEAP

CLUSTERED INDEX

NONCLUSTERED INDEX

PRIMARY XML INDEX

SPATIAL INDEX

XML INDEX

alloc_unit_type_desc

nvarchar(60)

对分配单元类型的说明：

IN_ROW_DATA

LOB_DATA

ROW_OVERFLOW_DATA

LOB_DATA 分配单元包含类型为text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max) 和 xml 的列中所存储的数据。

ROW_OVERFLOW_DATA 分配单元包含类型为 varchar(n)、nvarchar(n)、varbinary(n) 和sql_variant 的列（已推送到行外）中所存储的数据。

index_depth

tinyint

索引总级别数。

1 = 堆，或 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元。

index_level

tinyint

索引的当前位于B树结构中的级别。

0 表示索引叶级别、堆以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元。

大于 0 的值表示非叶索引级别。 index_level 在索引的根级别中属于别。

仅当 mode = DETAILED 时才处理非叶级别的索引。

avg_fragmentation_in_percent

float

索引的逻辑碎片，或 IN_ROW_DATA 分配单元中堆的区碎片。

此值按百分比计算，并将考虑多个文件。

0 表示 LOB_DATA 和 ROW_OVERFLOW_DATA 分配单元。

如果是堆表且mode模式为 Sampled 时，为 NULL。如果碎片小于10%~20%，碎片不太可能会成为问题，如果索引碎片在20%~40%，碎片可能成为问题，但是可以通过索引重组来消除索引解决，大规模的碎片(当碎片大于40%)，可能要求索引重建。

fragment_count

bigint

IN_ROW_DATA 分配单元的叶级别中的碎片数。

对于索引的非叶级别，以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，为 NULL。

对于堆，当 mode 为 SAMPLED 时，为 NULL。

avg_fragment_size_in_pages

float

IN_ROW_DATA 分配单元的叶级别中的一个碎片的平均页数。

对于索引的非叶级别，以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，为 NULL。

对于堆，当 mode 为 SAMPLED 时，为 NULL。

page_count

bigint

索引或数据页的总数。

对于索引，表示 IN_ROW_DATA 分配单元中 b 树的当前级别中的索引页总数。

对于堆，表示 IN_ROW_DATA 分配单元中的数据页总数。

对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示该分配单元中的总页数。

avg_page_space_used_in_percent

float

所有页中使用的可用数据存储空间的平均百分比。

对于索引，平均百分比应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。

对于堆，表示 IN_ROW_DATA 分配单元中所有数据页的平均百分比。

对于 LOB_DATA 或 ROW_OVERFLOW DATA 分配单元，表示该分配单元中所有页的平均百分比。

当 mode 为 LIMITED 时，为 NULL。

record_count

bigint

总记录数。

对于索引，记录的总数应用于 IN_ROW_DATA 分配单元中 b 树(包括非叶子数据页的数量)的当前级别。

对于堆，表示 IN_ROW_DATA 分配单元中的总记录数。

注意
对于堆，此函数返回的记录数可能与通过对堆运行 SELECT COUNT(*) 返回的行数不匹配。这是因为一行可能包含多个记录。例如，在某些更新情况下，单个堆行可能由于更新操作而包含一条前推记录和一条被前推记录。此外，多数大型 LOB 行在 LOB_DATA 存储中拆分为多个记录。

对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示整个分配单元中总记录数。

当 mode 为 LIMITED 时，为 NULL。

ghost_record_count

bigint

分配单元中将被虚影清除任务删除的虚影记录数。

对于 IN_ROW_DATA 分配单元中索引的非叶级别，为 0。

当 mode 为 LIMITED 时，为 NULL。

version_ghost_record_count

bigint

由分配单元中未完成的快照隔离事务保留的虚影记录数。

对于 IN_ROW_DATA 分配单元中索引的非叶级别，为 0。

当 mode 为 LIMITED 时，为 NULL。

min_record_size_in_bytes

int

小记录大小（字节）。

对于索引，小记录大小应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。

对于堆，表示 IN_ROW_DATA 分配单元中的小记录大小。

对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示整个分配单元中的小记录大小。

当 mode 为 LIMITED 时，为 NULL。

max_record_size_in_bytes

int

大记录大小（字节）。

对于索引，大记录的大小应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。

对于堆，表示 IN_ROW_DATA 分配单元中的大记录大小。

对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示整个分配单元中的大记录大小。

当 mode 为 LIMITED 时，为 NULL。

avg_record_size_in_bytes

float

平均记录大小（字节）。

对于索引，平均记录大小应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。

对于堆，表示 IN_ROW_DATA 分配单元中的平均记录大小。

对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示整个分配单元中的平均记录大小。

当 mode 为 LIMITED 时，为 NULL。

forwarded_record_count

bigint

堆中具有指向另一个数据位置的转向指针的记录数。（在更新过程中，如果在原始位置存储新行的空间不足，将会出现此状态。）

除 IN_ROW_DATA 分配单元外，对于堆的其他所有分配单元都为 NULL。

当 mode = LIMITED 时，对于堆为 NULL。

compressed_page_count

bigint

压缩页的数目。

对于堆，新分配的页未进行 PAGE 压缩。堆在以下两种特殊情况下进行 PAGE 压缩：大量导入数据时和重新生成堆时。导致页分配的典型 DML 操作不会进行 PAGE 压缩。当 compressed_page_count 值增长到超过您所需的阈值时，将重新生成堆。
对于具有聚集索引的表，compressed_page_count 值表示 PAGE 压缩的效率。

　　通常返回多行的时候，有个index_level列，这个列表示改行属于B树结构的第几层。

　　分析小表的碎片

　　不要过分关注小表的sys.dm_db_index_physical_stats输出。对于少于8个页面的小表或者索引，SQL Server使用混合区。例如，如果一个表仅包含两个页面，SQL Server从一个混合区中分配两个页面，二不是分配一个区给该表。混合区也可以包含其他小表或索引的页面。

　　跨越多个混合区的页面分布可能导致你相信在表或索引中有大量的外部碎片，而实际上这是SQL Server的设计，因而是可接受的。

　　先来建一张表如下，3个int字段，1个char(2000)字段。平均尺寸为4+4+4+2000=2012字节，8KB的页面多包含4行。在添加了28行之后，创建一个聚集索引来从屋里上排列行并将碎片减少到低限度。

　　咋一看，好像碎片非常厉害。实际上并不是这么回事。

　　分析如下：

　　avg_fragmentation_in_percent:尽管这个索引可能跨越多个区，这里看到碎片的情况并不是外部碎片的迹象，因为该索引保存在混合区上。
　　avg_page_space_used_in_percent:这说明所有或大部分县市在page_count中的7个页面中的数据存储状况良好。几乎满了，99点几。这消除了逻辑碎片的可能性。
　　fragment_count:这说明数据有碎片并且保存在多于一个区上，但是因为它的长度小于8个页面，SQL Server对存储该数据的地点没有很多选择。

　　尽管有上述引起误导的数值，一个少于8个页面的小表(或索引)不可能从去除碎片的工作中获益，因为它保存在混合区上。

　　索引说明：

三、关于碎片的解决方法

　　1.删除索引并重建

　　这种方式有如下缺点：

　　索引不可用：在删除索引期间，索引不可用。

　　阻塞：卸载并重建索引会阻塞表上所有的其他请求，也可能被其他请求所阻塞。

　　对于删除聚集索引，则会导致对应的非聚集索引重建两次(删除时重建，建立时再重建，因为非聚集索引中有指向聚集索引的指针)。

　　性约束：用于定义主键或者性约束的索引不能使用DROP INDEX语句删除。而且，性约束和主键都可能被外键约束引用。在主键卸载之前，所有引用该主键的外键必须首先被删除。尽管可以这么做，但这是一种冒险而且费时的碎片整理方法。

　　基于以上原因，不建议在生产数据库，尤其是非空闲时间不建议采用这种技术。

　　2.使用DROP_EXISTING语句重建索引

　　为了避免重建两次索引，使用DROP_EXISTING语句重建索引，因为这个语句是原子性的，不会导致非聚集索引重建两次，但同样的，这种方式也会造成阻塞。

CREATE UNIQUE CLUSTERED INDEX IX_C1 ON t1(c1)
WITH (DROP_EXISTING = ON)

　　缺点:

　　阻塞：与卸载重建方法类似，这种技术也导致并面临来自其他访问该表(或该表的索引)的查询的阻塞问题。

　　使用约束的索引：与卸载重建不同，具有DROP_EXISTING子句的CREATE INDEX语句可以用于重新创建使用约束的索引。如果该约束是一个主键或与外键相关的性约束，在CREATE语句中不能包含UNIQUE。

　　具有多个碎片化的索引的表：随着表数据产生碎片，索引常常也碎片化。如果使用这种碎片整理技术，表上所有索引都必须单独确认和重建。

　　3.使用ALTER INDEX REBUILD语句重建索引

　　使用这个语句同样也是重建索引，但是通过动态重建索引而不需要卸载并重建索引.是优于前两种方法的，但依旧会造成阻塞。可以通过ONLINE关键字减少锁，但会造成重建时间加长。

　　阻塞：这个依然有阻塞问题。

　　事务回滚：ALTER INDEX REBUILD完全是一个原子操作，如果它在结束前停止，所有到那时为止进行的碎片整理操作都将丢失，可以通过ONLINE关键字减少锁，但会造成重建时间加长。

　　4.使用ALTER INDEX REORGANIZE

　　这种方式不会重建索引，也不会生成新的页，仅仅是整理叶级数据，不涉及非叶级，当遇到加锁的页时跳过，所以不会造成阻塞。但同时，整理效果会差于前三种。

　　4种索引整理技术比较：

特性/问题	卸载并重建索引	DROP_EXISTING	ALTER INDEX REBUILD	ALTER INDEX REORGANIZE
在聚集索引碎片整理时，重建非聚集索引	两次	无	无	无
丢失索引	是	无	无	无
整理具有约束的索引的碎片	高度复杂	复杂性适中	简单	简单
同时进行多个索引的碎片整理	否	否	是	是
并发性	低	低	中等，取决于冰法用户活动	高
中途撤销	因为不使用事务，存在危险	进程丢失	进程丢失	进程被保留
碎片整理程度	高	高	高	中到低
应用新的填充因子	是	是	是	否
更新统计	是	是	是	否

四、填充因子FILLFACTOR

　　重建索引能够解决碎片的问题，但是重建索引的代码一来需要经常操作，二来会造成数据阻塞，影响使用。在数据比较少的情况下，重建索引代价很快，但是当索引比较大的时候，例如超过100M，那么重建索引的时间会非常长。

　　填充因子的作用是控制索引叶子页面中的空闲空间数量。说白了就是预留一些空间给INSERT和UPDATE。如果知道表上有很多的INSERT查询或者索引键列上有足够的UPDATE查询，可以预先使用填充因子来增加索引叶子页面的空闲空间已小化页面分割。如果表示只读的，可以创建一个高填充因子来减少索引页面的数量。

　　默认的填充因子为0，这意味着页面将被充满。　　