SQL Server Index-索引

     SQL Server索引基本上项目中都会用到，但是很多时候使用不规范甚者滥用，原因很简单就是对SQL Server 索引原理了解不清导致的。本篇文章旨在以详细通俗的方式介绍SQL Server索引。

     索引是什么？

      索引是与表或试图关联的磁盘上的结构，可以加快从表或试图中检索行的速度。索引包含由表或试图中的一列或多列生成的键。这些键存储在一个结构（B树）中，使SQL Server可以快速有效的查找与键值关联的行。

      为什么以B数作为索引和表（有聚集索引）的结构，首先需要理解SQL SERVER存储数据的原理。

       在SQL Server中，存储的最小单位是页（PAGE），页是不可再分的。这意味着SQL SERVER对于页的读取，要么整个读取，要么完全不读取，没有折中。

       在数据库检索来说，对于磁盘IO扫描是最消耗时间的，所以B数设计的初衷是wield减少对于磁盘的扫描次数。如果一个表或索引没有使用B树（对于没有聚集索引的表使用堆heap存储），那么查找一个数据，需要在整个表包含的数据页中全盘扫描。这无疑会大大加重IO负担，而在sql server中使用B树进行存储，则仅仅需要将B树的根节点存入内存，经过几次查找后就可以找到存放所需数据的被叶子节点包含的页，从而避免全盘扫描从而提高性能。

      聚集索引：聚集索引基于聚集索引键按顺序排序和存储表或试图中的数据行。索引定义中包含了聚集索引列。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。只有当表包含聚集索引时，表中的数据行才按排序顺序存储。如果表具有聚集索引，则该表称为聚集表。如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。聚集索引按B树结构实现，B树索引结构支持基于聚集索引键进行快速检索。

      由于在聚集索引下，数据在物理上是按序排列在数据页上的，重复值也在一起，因为包含范围查询（between，<,>,<=,>=）或使用group by或order by的查询时，一旦找到第一个键值的行，后面数据都是连续存储，避免大范围查询，可以大大提高查询效率。

       在SQL Server中，聚集索引的存储是以B树存储，B树的叶子直接存储聚集索引的数据：

 

      非聚集索引：非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针；从非聚集索引中索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页是存储在堆中还是聚集表中。对于堆，行定位器是指向数据行的指针，对于聚集表，行定位器是聚集索引键；非聚集索引的覆盖索引，当查询中所有列都作为键列或非键列包含在索引中时，带有包含性非键列的索引可以显著提高查询性能。因为查询器可以在索引中找到所有列值，不再访问表或聚集索引数据，从而减少磁盘I/O操作。

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

      SQL Server默认情况下建立的是非聚集索引，它不重新组织表的数据，而是对每一数据行存储数据列值并用一个指针指向数据所在的页面。

     非聚集索引，本质上来说也是聚集索引的一种，非聚集索引并不改变其所在表的物理结构，而是额外生成一个聚集索引的B树结构，但叶节点是对于其所在表的引用，分为2种：如果其所在表上没有聚集索引，则引用行号；如果其所在表上已经有了聚集索引，则引用聚集索引的页。

 

    索引和约束：

     对表定义了PRIMARYKEY约束和UNIQUE约束时，会自动创建索引。例如，在创建带有UNIQUE约束的表时，数据引擎会自动创建非聚集索引。如果配置了PRIMARYKEY，数据库引擎会自动创建聚集索引（除非聚集索引已存在）。如果你尝试对现有表强制执行PRIMARYKEY约束，且此表上已有聚集索引，SQL Server使用非聚集索引强制执行主键。

   查询优化器如何使用索引：

    设计良好的索引可以减少磁盘I/O操作，并且消耗的资源也较少，从而可以提高查询性能。执行查询时，查询优化器评估可用于检索数据的每个方法，然后选择最有效的方法。可能采用的方法包含扫描表和扫描一个或多个索引（如果有）。

    扫描表时，查询优化器读取表中的所有行，并提取满足查询条件的行。扫描表会有许多磁盘I/O操作，并占用大量资源。但是，查询的结果集是占表中较高百分比的行，扫描表会是最为有效的方法。

     查询优化器使用索引时，查询优化器使用索引时，搜索索引键列，查找到查询所需行的存储位置，然后从该位置提取匹配行。 通常，搜索索引比搜索表要快很多，因为索引与表不同，一般每行包含的列非常少，且行遵循排序顺序。

     查询优化器在执行查询时通常会选择最有效的方法。 但如果没有索引，则查询优化器必须扫描表。

    数据表的基本结构：一个表被创建时，系统将在磁盘中分配一段以8kWie单位的连续空间，当字段的值从内存写入磁盘时，就在这一既定的空间内随机保存，当一个8k用完的时候，数据库指针会自动分配一个8k的空间。这里，每个8k空间被称为一个数据页（Page）,并分配从0-7的页号，每个文件的第0页记录引导信息，叫文件头（File header）；每8个数据页（64k）组合形成扩展区（Extent），全部数据页组成堆（heap）。

      SQL 规定行不能跨数据页，所以每行记录的最大数据量是8k，这就是char和varchar这两种字符串类型容量要限制在8k以内的原因，存储超过8k的数据应使用text类型，实际上，text类型的字段值不能直接保存只是存储一个指针，指向由若干8k的文本数据页组成的扩展区，真正的数据是存储在这些数据页中。

     页面分为空间页面和数据页面。

      当一个扩展区的8个数据页既包含了空间页面又包含了数据或索引页面时，称为混合扩展（Mixed Extent）,每张表都以混合扩展开始；反之，称为一致扩展（Uniform Extent），专门保存数据及索引信息。

      SQL Server使用4种类型的数据页面来管理表或索引：IAM（Index Allocation Map），数据页，文本/图像页和索引页。

      在windows中，我们对文件执行的每一步操作，在磁盘上的物理位置只有系统（system）才知道；SQL Server沿袭了这种工作方式，在插入数据过程中，不但每个字段在数据页的保存位置是随机的，而且每个数据页在“堆”中的排列位置也只有系统（system）才知道。

      OS之所以能管理DISK，是因为在系统启动时首先加载了文件分配表FAT（File Allocation Table），正是由于它管理文件系统并记录对文件的一切操作，系统才得以正常运行；同理，作为管理系统级的SQL Server，也有这样一张类似FAT的表存在，它就是索引分布映像页IAM（Index Allocation Map）.

      页分裂

       一半的数据保留在老页面，而另一半将放入新页面，并且新页面可以被分配到任何可用的页。所以，频繁页分裂后果很严重，将使物理表产生大量的数据碎片，直接导致I/O效率的下降，最后，停止SQL Server运行重建索引将是我们唯一选择。

     填充因子

      索引的一个特性，定义该索引每页上的可用空间量。FILLFACTOR（填充因子）适合以后表数据的扩展并减少了页拆分的可能性。填充因子是从0-100的百分比数值，设为100表示将数据页填满。只有当不会对数据进行更改时（例如只读表中）采用此设置。值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中进行页分裂的需要，但这一操作需要占用更多的磁盘空间。填充因子指定不当，会降低数据库的读取性能，其降低量与填充因子设置值成反比。

    索引的设计原则

     系统一般会给自增字段自动建立聚集索引。有大量重复值且经常有范围查询和排序，分组的列，或者经常频繁访问的列，考虑建立聚集索引。在一个经常做插入操作的表中建立索引，应使用FILLFACOR（填充因子）来减少页分裂，同时提高并发度降低死锁的发生。如果在表为只读表，填充因子可设为100。在选择索引键时，尽可能采用小数据类型的列作为键以使每个索引页能容纳尽可能多的索引键和指针，通过这种方式，可使一个查询遍历的索引页面降低到最小，此外，尽可能使用整数作为键值，因为整数的访问速度更快。