SQLServer → 09：索引

一、索引概念

• 用途

我们对数据查询及处理速度已成为衡量应用系统成败的标准，而采用索引来加快数据处理速度通常是最普遍采用的优化方法。

• 概念

索引是一个单独的，存储在磁盘上的数据结构，它们包含则对数据表里所有记录的引用指针，使用索引用于快速找出在某个或多个列中有某一特定值的行，对相关列使用索引是降低查询操作时间的最佳途径。索引包含由表或视图中的一列或多列生成的键。

• 定义

数据库中的索引类似于一本书的目录，在一本书中使用目录可以快速找到你想要的信息，而不需要读完全书。在数据库中，数据库程序使用索引可以重啊到表中的数据，而不必扫描整个表。书中的目录是一个字词以及各字词所在的页码列表，数据库中的索引是表中的值以及各值存储位置的列表。

• 利弊

查询执行的大部分开销是I/O，使用索引提高性能的一个主要目标是避免全表扫描，因为全表扫描需要从磁盘上读取表的每一个数据页，如果有索引指向数据值，则查询只需要读少数次的磁盘就行啦。所以合理的使用索引能加速数据的查询。但是索引并不总是提高系统的性能，带索引的表需要在数据库中占用更多的存储空间，同样用来增删数据的命令运行时间以及维护索引所需的处理时间会更长。所以我们要合理使用索引，及时更新去除次优索引。

优点：
(1).通过创建唯一索引，可以保证数据库表的每一行数据的唯一性。
(2).可以大大加快数据的查询速度，这也是创建索引的最主要的原因。
(3).实现数据的参照完整性，可以速表和表之间的连接。
(4).在使用分组和排序子句进行查询时，也可以显著减少查询中分组和排序的时间。
缺点：
(1).创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
(2).索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到做大文件尺寸。
(3).当对表中的数据进行增加，删除和修改的时候，索引也要动态地维护，这样就就降低了数据的维护速度。

二、数据表的基本结构

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

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

　　页面有空间页面和数据页面之分。

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

　　表被创建之时，SQLS在混合扩展中为其分配至少一个数据页面，随着数据量的增长，SQLS可即时在混合扩展中分配出7个页面，当数据超过8个页面时，则从一致扩展中分配数据页面。

　　空间页面专门负责数据空间的分配和管理，包括：PFS页面（Page free space）：记录一个页面是否已分配、位于混合扩展还是一致扩展以及页面上还有多少可用空间等信息；GAM页面（Global allocation map）和SGAM页面(Secodary global allocation map)：用来记录空闲的扩展或含有空闲页面的混合扩展的位置。SQLS综合利用这三种类型的页面文件在必要时为数据表创建新空间；

　　数据页或索引页则专门保存数据及索引信息，SQLS使用4种类型的数据页面来管理表或索引：它们是IAM页、数据页、文本/图像页和索引页。

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

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

　　IAM的存在，使SQLS对数据表的物理管理有了可能。
　　IAM页从混合扩展中分配，记录了8个初始页面的位置和该扩展区的位置，每个IAM页面能管理512,000个数据页面，如果数据量太 大，SQLS也可以增加更多的IAM页，可以位于文件的任何位置。第一个IAM页被称为FirstIAM,其中记录了以后的IAM页的位置。

　　数据页和文本/图像页互反，前者保存非文本/图像类型的数据，因为它们都不超过8K的容量，后者则只保存超过8K容量的文本或图像类型数据。而索 引页顾名思义，保存的是与索引结构相关的数据信息。了解页面的问题有助我们下一步准确理解SQLS维护索引的方式，如页拆分、填充因子等。

2.1、页分裂

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

2.2、填充因子

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

三、索引的分类

SQL SERVER中有多种索引类型。
按存储结构区分：“聚集索引（又称聚类索引，簇集索引）”，“分聚集索引（非聚类索引，非簇集索引）”
按数据唯一性区分：“唯一索引”，“非唯一索引”
按键列个数区分：“单列索引”，“多列索引”。

3.1、聚集索引

在 SQL Server 中，索引是按 B 树结构进行组织的。
聚集索引单个分区中的结构:

聚集索引基于数据行的键值，在表内排序和存储这些数据行。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。
(1).每个表只能有一个聚集索引；
(2).表中的物理顺序和索引中行的物理顺序是相同的，创建任何非聚集索引之前要首先创建聚集索引，这是因为非聚集索引改变了表中行的物理顺序；
(3).关键值的唯一性使用UNIQUE关键字或者由内部的唯一标识符明确维护。
(4).在索引的创建过程中，SQL Server临时使用当前数据库的磁盘空间，所以要保证有足够的空间创建索引。

聚集索引（Clustered Index）特点

• 聚集索引的叶节点就是实际的数据页
• 聚集索引中的排序顺序仅仅表示数据页链在逻辑上是有序的。而不是按照顺序物理的存储在磁盘上
• 行的物理位置和行在索引中的位置是相同的
• 每个表只能有一个聚集索引
• 聚集索引的平均大小大约为表大小的5%左右

语法格式：

-- 建立聚集索引
CREATE CLUSTERED INDEX 索引名称 ON 表名
(
 列名 ASC
)

3.2、非聚集索引

　　非聚集索引与聚集索引具有相同的 B 树结构，它们之间的显著差别在于以下两点：

1. 基础表的数据行不按非聚集键的顺序排序和存储。
2. 非聚集索引的叶层是由索引页而不是由数据页组成。

下图示意了单个分区中的非聚集索引结构：

　　sqlserver默认情况下建立的索引是非聚集索引，他不重新组织表中的数据，而是对每一行存储索引列值并用一个指针指向数据所在的页面。他像汉语字典中的根据‘偏旁部首’查找要找的字，即便对数据不排序，然而他拥有的目录更像是目录，对查取数据的效率也是具有的提升空间，而不需要全表扫描。一个表可以拥有多个非聚集索引，每个非聚集索引根据索引列的不同提供不同的排序顺序。
　　非聚集索引具有完全独立于数据行的结构，使用非聚集索引不用将物理数据页中的数据按列排序，非聚集索引包含索引键值和指向表数据存储位置的行定位器。
　　可以对表或索引视图创建多个非聚集索引。通常，设计非聚集索引是为了改善经常使用的、没有建立聚集索引的查询的性能。
　　查询优化器在搜索数据值时，先搜索非聚集索引以找到数据值在表中的位置，然后直接从该位置检索数据。这使得非聚集索引成为完全匹配查询的最佳选择，因为索引中包含搜索的数据值在表中的精确位置的项。

非聚集索引 （Unclustered Index） 特点：

• 非聚集索引的页，不是数据，而是指向数据页的页。
• 若未指定索引类型，则默认为非聚集索引。
• 叶节点页的次序和表的物理存储次序不同
• 每个表最多可以有249个非聚集索引
• 在非聚集索引创建之前创建聚集索引（否则会引发索引重建）

语法格式： 

-- 建立非聚集索引
CREATE NONCLUSTERED INDEX 索引名称 ON 表名
(
 列名 ASC
)

何时使用？

(1).使用JOIN或者GROUP BY子句，应为连接和分组操作中所涉及的列创建多个非聚集索引，为任何外键创建一个聚集索引.
(2).包含大量唯一值的字段。
(3).不返回大型结果集的查询。创建筛选索引以覆盖从大型表中返回定义完善的的行子集的查询。
(4).经常包含在查询的搜索条件(如返回完全匹配的WHERE子句)中的列。

3.3、其它索引

除了聚集索引和非聚集索引之外，SQL Server中还提供了其它的索引类型。

唯一索引:确保索引键不包含重复的值，因此，表或视图中的每一行在某种程度上是唯一的。聚集索引和非聚集索引都可以是唯一索引。这种唯一性和主键约束是关联的，在某种程度上，主键约束等于唯一性的聚集索引。
包含列索引：一种非聚集索引，它扩展后不仅包含键列，还包含非坚列。
索引视图：在视图上添加索引后能提高视图的查询效率。视图的索引将具体化视图，并将结果集永久存储在唯一的聚集索引中，而且存储方式相同，创建聚集索引后，可以为视图添加非聚集索引。
全文索引：一种特殊类型的基于标记的功能索引，由SQL Server全文引擎生成和维护，用于帮助在字符串数据中搜索复杂的词，这种索引的结构与数据库引擎使用的聚集索引或非聚集索引的B树结构是不同的。
空间索引：一种针对geometry数据类型的列上建立的索引，这样可以更高效的列中的空间对象执行某些操作，空间索引可以减少需要应用开销相对较大的空间操作的对象数。
筛选索引:一种经过优化的的非聚集索引，尤其适用于涵盖从定义完善的数据子集中选择数据的查询。筛选索引使用筛选谓词对表中的的部分进行索引，与全表索引相比，设计良好的筛选索引可以提高查询性能，减少索引维护开销并可降低索引存储开销
XML索引：是与XML数据关联的索引形式，是XML二进制大对象(BLOB)的已拆分持久表示形式，XML索引可以分为主索引和辅助索引。

四、创建索引

语法格式：

CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED ]
INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[with[PAD_INDEX][[,]FILLFACTOR=fillfactor]
[[,]IGNORE_DUP_KEY]
[[,]DROP_EXISTING]
[[,]STATISTICS_NORECOMPUTE]
[[,]SORT_IN_TEMPDB]
]
[ ON filegroup ]

CREATE INDEX命令创建索引各参数说明如下：
UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。
CLUSTERED：用于指定创建的索引为聚集索引。
NONCLUSTERED：用于指定创建的索引为非聚集索引。
index_name：用于指定所创建的索引的名称。
table：用于指定创建索引的表的名称。
view：用于指定创建索引的视图的名称。
ASC|DESC：用于指定具体某个索引列的升序或降序排序方向。
Column：用于指定被索引的列。
PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的空间。
FILLFACTOR = fillfactor：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。
IGNORE_DUP_KEY：用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQL Server所作的反应。
DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。
STATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重新计算。
SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。
ON filegroup：用于指定存放索引的文件组。

例子：

--表bigdata创建一个名为idx_mobiel的非聚集索引，索引字段为mobiel
create index idx_mobiel
on bigdata(mobiel) 

--表bigdata创建一个名为idx_id的唯一聚集索引，索引字段为id
--要求成批插入数据时忽略重复值，不重新计算统计信息，填充因子为40
create unique clustered index idx_id
on bigdata(id) 
with pad_index,
fillfactor=40,
ignore_dup_key,
statistics_norecompute

五、管理索引

Exec sp_helpindex BigData   -- 查看索引定义

Exec sp_rename 'BigData.idx_mobiel','idx_big_mobiel'  -- 将索引名由'idx_mobiel' 改为'idx_big_mobiel'

drop index BigData.idx_big_mobiel  -- 删除bigdata表中的idx_big_mobiel索引

dbcc showcontig(bigdata,idx_mobiel) -- 检查bigdata表中索引idx_mobiel的碎片信息

dbcc indexdefrag(Test,bigdata,idx_mobiel)  -- 整理test数据库中bigdata表的索引idx_mobiel上的碎片

update statistics bigdata  -- 更新bigdata表中的全部索引的统计信息

六、索引的设计原则

　　对于一张表来说索引的有无和建立什么样的索引，要取决与where字句和Join表达式中。

索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍，高效的索引对于获得良好的性能非常重要。设计索引时，应该考虑以下原则：
(1).索引并非越多越好，一个表中如果有大量的索引，不仅占用大量的磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能。因为当表中数据更改的同时，索引也会进行调整和更新。
(2).避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
(3).数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
(4).在条件表达式中经常用到的、不同值较多的列上建立索引，在不同值较少的列上不要建立索引、比如在学生表的【性别】
　　字段上只有【男】或【女】两个不同值，因此就无需建立索引，如果建立索引，不但不会提高查询效率，反而会严重降低更新速度。
(5).当唯一性是某种数据本身的特征时，指定唯一索引，使用唯一索引能够确保定义的列的数据完整性，提高查询速度。
(6).在频繁进行排序或分组（即进行GROUP BY 或ORDER BY操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引.