SqlServer调优（一）索引

前言：SqlServer调优预计是一个系列，包含索引、视图、触发器、存储过程、游标、事务、锁、死锁、追踪、执行计划几个部分，主要目的在于共同学习SqlServer调优，以达到业务软件调优的目的。

一、索引的定义与优缺点

SQL索引有两种，聚集索引和非聚集索引，索引主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间，但每次增删改时，索引都需要进行维护（自动），所以索引会降低增删改的效率。

下面举两个简单的例子：

1、图书馆：对于图书摆放位置，假定以书名为主键（不能有重复值），建立一个目录，例如：A开头的书，在排，B开头的在第二排，这样一来知道书名后想找一本书就比较方便了，像这样将所有书按照书名顺序摆放而成的目录，叫做聚集索引；可是有人想根据作者来找书怎么办？图书馆可以再维护一个目录，内容为某某作者的书分别在第几排，第几排，这就是一个非聚集索引，即目录对应的内容不按顺序摆放的。

2、字典：字典前面有多套目录，可以按照拼音或部首去查询，即可快速的定位到一个汉字，这个就是目录（索引）的好处，拼音查询法就是聚集索引（所有汉子按照拼音排序），部首查询就是一个非聚集索引（汉子没有按照部首排序）。

一个表只能有一个聚集索引，因为一张表只能同时按照一个规则去排序。

二、索引的实现

聚集索引按下列方式实现：

1、主键（PRIMARY KEY）

在为一张表创建主键的时候，如果表中不存在聚集索引，且未指定非聚集索引，系统将会自动对一列或多列创建聚集索引，并且主键不允许有空值。

步骤图如下：

创建一张表，包含test_id与test_name两个字段

将test_id字段设置为主键

查看索引

可见创建主键时，表中没有聚集索引的情况下，系统自动建立主键上的聚集索引

将刚刚创建的主键删除

2、约束（UNIQUE，即此列的值不能够重复）

在创建UNIQUE约束时，默认情况下将创建非聚集索引，以便强制约束，如果该表不存在聚集索引，则可以指定聚集索引。

步骤图如下：

为test_name列创建约束

点击添加，选择是的，此时表中没有聚集索引（主键已删除），则可以选择创建为聚集的

3、独立于约束的索引

在指定非聚集索引主键后，可以对非主键的列创建聚集索引。

步骤图如下：

首先删除全部索引和主键

在test_id上创建主键

打开索引页面

将主键的索引类型改为非聚集索引

在test_name列创建索引

此时表中的主键为非聚集索引，所以可以对于test_name创建聚集索引

4、其他实现方式

其他索引即实现方式包含索引视图、全文索引、空间索引、筛选索引、XML索引等，因使用较少，不做介绍。

三、索引的原理与存储机制

首先，无索引的表，查询时，是按照顺序存续的方法扫描每个记录来查找符合条件的记录，这样效率十分低下。

举个栗子，如果我们将字典的汉字随即打乱，没有前面的按照拼音或者部首查询，那么我们想找一个字，按照顺序的方式去一页页的找，这样效率有多低，大家可以想象。

再举个实际栗子，需要查询一条sql的结果，sql如下：

select * from gl_pzml where pzh='xxx'; 

如果在pzh一列没有索引（目录）存在，数据库就需要从庞大的数据中按照物理顺序（无序）来一行一行判断，直至全部判断完，而如果pzh有索引的存在，内容为：pzh为xxx的数据分别在第m行、第n行、第k行……，查询的时候就轻松多了。

聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。其实理解起来非常简单，还是举字典的例子：如果按照拼音查询，那么都是从a-z的，是具有连续性的，a后面就是b，b后面就是c，聚集索引就是这样的，他是和表的物理排列顺序是一样的，例如有id为聚集索引，那么1后面肯定是2，2后面肯定是3，所以说这样的搜索顺序的就是聚集索引。

非聚集索引就和按照部首查询是一样的，可能按照偏房查询的时候，根据偏旁‘弓’字旁，索引出两个汉字，张和弘，但是这两个其实一个在100页，一个在1000页，他们的索引顺序和数据库表的排列顺序是不一样的，这个样的就是非聚集索引。

原理明白了，那他们是怎么存储的呢？在这里简单的说一下，聚集索引就是在数据库被开辟一个物理空间存放他的排列的值，例如1-100，所以当插入数据时，他会重新排列整个物理空间，而非聚集索引其实可以看作是一个含有聚集索引的表，它仅包含原表中非聚集索引的列和指向实际物理表的指针（索引出两个汉字，张和弘，但是这两个其实一个在100页，一个在1000页），其实就有点和堆栈差不多的感觉了。

以下内容理解难度升级，非战斗人员先行撤离

四、数据表的基本结构

1、页、扩展区与堆

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

数据页

扩展区

堆

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

2、空间页面和数据页面

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

（1）空间页面专门负责数据空间的分配和管理，包括：

①PFS页面（Page free space）：记录一个页面是否已分配、位于混合扩展还是一致扩展以及页面上还有多少可用空间等信息；

②GAM页面（Global allocation map）和SGAM页面(Secodary global allocation map)：用来记录空闲的扩展或含有空闲页面的混合扩展的位置。SQLServer综合利用这三种类型的页面文件在必要时为数据表创建新空间。

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

①IAM（Index Allocation Map）

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

SQLServer中的索引分布映像页IAM负责。管理文件系统并记录对文件的一切操作，有了它，SQLServer才能够正常工作。

②数据页和文本/图像页

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

五、页分裂

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

六、填充因子

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

填充因子的缺省（默认）值为0，有效值介于0和100之间。填充因子设置为0或100时，页级别几乎完全填满。

很多资料都提到，缺省的填充因子取值一般情况下是较优的，而且建议除非你很清楚在做什么，否则不要更改填充因子取值。

本文部分内容来自于互联网。

本文来源https://www.modb.pro/db/442553