SQL Server索引设计
SQL Server索引设计
SQL Server索引的设计主要考虑因素如下：
检查WHERE条件和连接条件列；
使用窄索引；
检查列的选择性；
检查列的数据类型；
考虑列顺序；
考虑索引类型(聚集索引OR非聚集索引)；
一、检查WHERE条件列和链接条件列
当一个查询提交到SQL Server时，查询优化器尝试为查询中引用的所有表查找最佳的数据访问机制。下面列出查询优化器针对WHERE和连接的工作方式：
- 优化器识别WHERE子句和连接条件中包含的列。
- 接着优化器检查这些列上的索引。
- 优化器通过从索引上维护的统计确定子句的选择性，评估每个索引的有效性。
- 最终，优化器根据前面几个步骤中手机的信息，估计读取所限定行开销最低的方法。
为了理解WHERE子句在查询中的重要性，来考虑一个示例。
SELECT * FROM Person WHERE Id = 100;
假设上面的表Id列为聚集索引。上面的语句有了WHERE子句，查询优化器将检查WHERE子句的列Id，确定Id列上有聚集索引，从聚集索引上的统计评估WHERE子句的高选择性，并且决定使用这个索引。
查询优化器的表现说明，WHERE子句列帮助优化器选择一个对查询最优的索引操作。这也适用于两个表之间的连接条件中使用的列。优化器查找到WHERE子句或连接条件列上的索引，如果可用，考虑使用该索引来从表中检索行。查询优化器在执行一个查询时，考虑WHERE子句或连接条件列上的索引。因此WHERE子句或连接条件中频繁使用的列上有索引将帮助优化器避免基本表的扫描。
但是，当一个表中的数据总量非常小以至于可以放入一个数据页，那么表扫描可能比索引查找更快，如果有一个好的索引，但是仍然进行扫描，可以考虑这个问题。
二、使用窄索引
可以在表中的一个列组合上创建索引，但是为了最好的性能，尽量在索引中使用较少的列。还应该避免在索引中使用宽数据类型的列。
- 窄索引：索引中的列数尽可能少；
- 宽数据类型：占用空间比较大的数据类型，如：CHAR、VARCHAR、NVARCHAR、CLOB等。除非绝对必须，否则在索引中要把大尺寸的宽数据类型的列的使用降到最少。
窄索引可以在8KB的索引页面上容纳比宽索引更多的行，这将有如下优点：
- 减少I/O数量(读取更少的8KB页面)；
- 使数据库缓存更有效，因为SQL Server可以缓存更少的索引页面，从而减少内存中的索引页面所需的逻辑读操作；
- 减少数据库存储空间；
下面以后一个示例来说明窄索引的好处：
第一次，我们的索引仅仅包含Name列：
第二次，我们的索引INCLUDE多了两列：
我们看到。包含多了两个列之后，逻辑读取比一个列多？为什么呢？因为包含多了两个列，索引需要占用更大的空间，一个数据页放的索引行少了，就需要读取更多的数据页。
三、索引列的选择性
索引，特别是非聚集索引，主要在索引中有相当高级别的选择性的情况下是有益的。所谓选择性，指的是列中唯一值的百分比。列中的唯一值百分比越高，选择性就越高，从而索引的溢出就越大。如果一个表中有2000条记录，表索引列有1990个不同的值，那么这个索引的选择性就是1980/2000=0.99。
在前面的学习中已经了解到，在非聚集索引中的查询实际上只是开始。要找到真正的数据，仍需要对聚集索引再执行一次循环遍历。甚至使用堆上的非聚集索引，仍然需要执行多个单独的物理读操作。
如果在非聚集索引中的一个查找将要在聚集索引上产生多个额外的查找，那么进行表扫描可能更好。这里可能产生的影响实际上是非常惊人的。如果被索引的列唯一性达不到90%~95%，那么考虑由非聚集索引创建的循环过程是不值得的。比如一个性别选项，设置为了bit，然后建索引。查询优化器是不会考虑使用这种索引的。
由以上的分析，可知主键的选择性是100%，选择性越接近主键，建在该列的索引的效率就会越高。
索引的可选择性是衡量索引的利用率的方法，比如在极端的情况下，一个表记录数是1000，而索引列的值只有5个不同的值，则索引的可选择性很差（只有0.005）。这样的情形使用全表扫描要比采用索引还好。
下面来实操下，计算下索引的选择性，当然测试表数据小，可能查询时即使有了索引，SQLServer也未必会用。
由以上信息可计算得到对fdkeyname列的索引选择性为
110/119 = 0.924
这样还是麻烦啊？难道手工算吗？下面给出一条SQL语句查出选择性的方法：
SELECT CAST(count(DISTINCT fdkeyname) AS FLOAT) / CAST(count(*) AS FLOAT) FROM JM_Keyword;
选择性规则的一个例外与外键有关，如果表中有一列是外键，那么在该列上有一个索引，这很可能是有益的。为什么是外键而不是其他列呢？外键常常是与它们引用的表连接的目标。不管选择性如何，索引在连接性能方面是非常有帮助的，因为它们允许合并连接。合并连接从每个表中获取一行进行比较，查看它们是否和连接条件匹配。因为两个表中的相关列上都存在索引，所以对两个行的查找是非常快的。
下面以一个示例来说明问题：
我在一个Person表中的性别列建立了一个索引，然后来查看查询优化器的查询方式：
为什么查询优化器不选择从Gender列的索引来查找数据呢?
我要返回前10条性别为"男"数据，如果使用索引，我们知道这个Gender列上的索引的选择性大约为50%。SQL Server即使通过索引找到了前10条性别为男的聚集列，也还要再通过Id到聚集索引中去查找数据，这样还不如直接扫描聚集表快。因此SQL Server的查询优化器忽略了这个索引。
通过WITH INDEX(索引名)可以强制使用索引查找，下面给出这两种查询方式的读取次数比较：
强制索引方式读取：
查询优化器选择读取：
由上面我们可以看到，强制使用索引的话，逻辑读取高，但是预读少。我们知道预读是与分析并行执行，而且能够载入缓存中的。从SQL Server的选择来看，基本上可以得出一个结论，逻辑读比预读更加占用时间。
四、检查索引的数据类型
索引列的数据类型也是很重要的。例如，在一个整数键值上的索引查询是非常快的，这是因为int数据类型的尺寸很小，而且算数操纵很容易。也可以使用int数据的其他变种(bigint,smallint,tinyint)作为索引列，而字符串数据类型(char、varchar、nchar、ncarchar、)需要字符串匹配操作，这通常比整数匹配操作的开销更大。
假设希望在一列上创建索引但却有两个候选列，一个是int数据类型，一个是char(4)数据类型。这两种数据类型在SQL Server 2008中大小都是4字节，但是仍然应该首选int数据类型作为索引。因为char(4)数据类型中的值1实际上保存为1后面跟着3个空格，4个字节组合是0x35、0x20、0x20、0x20。CPU不理解如何在这个数据上执行算数运算。因此在算数操作之前要将其转换为一个整数，而在int数据类型中，值1被保存为0x00000001。CPU可以简单地在这个数据上执行算数运算。
五、索引列顺序
索引键值在索引的第一列上排序，然后再一次再下一列中排序。
假设我们的在一张表中建立一个复合索引：
CREATE NONCLUSTERED INDEX indexName ON Table(c1,c2)
那么索引中的数据大概如下：
| c1 | c2 |
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 3 | 1 |
| 3 | 2 |
SELECT * FROM Table WHERE c1 = 1 或 2 SELECT * FROM Table WHERE c2 = 1 或 2 AND c1 = 1 或 2
(c2,c1)对上面两个查询都有利，但是(c1,c2)上的索引就不合适，因为它首先在c1上排序，而第一个SQL语句需要在c2上排序。
这就好比使用电话本。所有项都是按先姓后名的方式进行索引-如果值知道要通电话的人的名是“备”，那么这种排列顺序不能带来什么好处。另一方面，如果只知道他的姓是“刘”，那么索引将可以用来缩小查找范围。
六、考虑索引类型
考虑索引的类型 SQL Server中有两种主要的索引类型：聚集索引和非聚集索引。这两种类型都为B-树结构。两者之间的主要区别是聚集索引中的叶子页是表的数据。因此表中的数据和聚集索引的顺序相同，这意味着，聚集索引就是该表。在决定使用索引类型时，两种索引类型的叶子级别上的差别变得非常重要。
一个表只有一个聚集索引，应该明智地选择它。
SQLServer在默认情况下，主键和聚集索引是一起创建的。如果不想将主键声明为聚集索引，那么在创建表时，只需添加NONCLUSTERED关键字。
CREATE TABLE MyTableKeyExample { Column1 int IDENTITY PRIMARY KEY NONCLUSTERED, Column2 int }
一旦创建了索引，改变它的唯一方法是删除和重建它，所以需要一开始就做对。
如果改变了聚集索引所在的列，那么SQL Server将需要对整个表完全重新排序(因为对于聚集索引，表的排列顺序和索引顺序是相同的)。
对于数据比较多的表，改变聚集索引，需要重新排序的数据非常多，要从以下几个方面进行考虑。
它将需要花费多长时间。
是否有足够的空间？为了在聚集索引上执行重新排序，额外需要的平均空间量将为表已经占用空间量的1.2倍。确保有足够的空间来操作。
应当使用SORT_IN_TEMPDB选项吗？如果tempdb位于与主数据库不同的物理阵列上，并且它有足够的空间，那么答案是肯定的。
1、正面观点
如果列常作为范围查询的对象，那么聚集索引对这类查询是很有用的。这类查询通常使用between语句或<or>符号。使用GROUP BY以及利用MAX、MIN和COUNT聚合函数的查询也是使用范围和偏好聚集索引的查询的重要示例。聚集索引适合用于此处，是因为搜索可以直接到达物理数据中的特定点，可一直读数据，直到范围的末端，然后停止。这种方法非常有效。当想要数据基于聚集键排序(ORDER BY),聚集也是极好的方法。
2、反面观点
有两种情况下，你可能不想创建聚集索引。
(1)、当有更好的位置来使用它时。不要因为列看上去适合做聚集索引就将它用作聚集索引(主键是最常见的罪魁祸首)-要确定没有更合适的其他列。
(2)、在将要以非连续的顺序进行大量插入时。这会进行页拆分，并且会消耗大量时间。
例如，一个交易系统，用
ARXXXX
GLXXXX
APXXXX
作为主键，并使用默认的聚集索引，那么在插入数据的时候，经常会发生页拆分。因为数据会按照聚集索引进行排序，那么不停的录入数据，就可能会经常性地发生页拆分，引起短暂停顿。
幸运的是，有一些方法可以避免以上情形：
选择在插入时是连续的聚集键。可以以此创建一个标识列，或者也可以使用另一个列，该列对于任何输入交易来说，在逻辑上都是连续的。
选择不在这个表上使用聚集索引。对于类似的这里的情形来说，这通常是最好的选择，因为在对上非聚集索引中的插入一般比在聚集键上的插入更快。
何时应该使用聚集索引与非聚集索引
动作描述 使用聚集索引 使用非聚集索引
列经常被分组排序 应 应
返回某范围内的数据 应 不应
一个或极少不同值 不应 不应
小数目的不同值 应 不应
大数目的不同值 不应 应
频繁更新的列 不应 应
外键列 应 应
主键列 应 应
频繁修改索引列 不应 应
事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如你的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时你查询2010年1月1日到2013年1月1日之间的全部数据时，这个速度就将是很快的，因为你的这本字段正文是按日期进行排序的，聚集索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。
3、结合实际，谈索引使用的误区
下面列出在实践中的一些误区:
1、主键就是索引
这种想法是极端错误的，是对聚集索引的一种浪费。虽然SQL SERVER默认是在竹简上简历聚集索引的。通常，我们会在每个表都建立一个Id列，以区分每条数据，并且这个Id列是自动增大的，增长量一半设为1。以一个办公自动化的紫铜为例子。如果将Id列设为主键，SQL SERVER会将此列默认为聚集索引，这样做有好处，就是可以让你的数据在数据库中按照Id进行物理排序，但这样做的意义不大。聚集索引的速优势是非常明显的，而每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。
从我们前面谈到的聚集索引的定义我们可以看出，使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。在实际应用中，因为Id号是自动生成的，我们并不知道每条记录的Id号，所以我们很难在时间中用Id号来进行查询。这个主键作为聚集索引成为一种资源浪费。其次，让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则；当然，这种情况只是针对用户经常修改记录内容，特别是索引项的时候会负作用，但对于查询速度并没有影响。在办公自动化系统中，无论是系统首页显示的需要用户签收的文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段的是“日期”还有用户本身的“用户名”。
通常，办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我们的where语句可以仅仅限制当前用户尚未签收的情况，但如果您的系统已建立了很长时间，并且数据量很大，那么，每次每个用户打开首页的时候都进行一次全表扫描，这样做意义是不大的，绝大多数的用户1个月前的文件都已经浏览过了，这样做只能徒增数据库的开销而已。事实上，我们完全可以让用户打开系统首页时，数据库仅仅查询这个用户近3个月来未阅览的文件，通过“日期”这个字段来限制表扫描，提高查询速度。如果您的办公自动化系统已经建立的2年，那么您的首页显示速度理论上将是原来速度8倍，甚至更快。
在这里之所以提到“ 理论上”三字，是因为如果您的聚集索引还是盲目地建在ID这个主键上时，您的查询速度是没有这么高的，即使您在“日期”这个字段上建立的索引（非聚合索引）。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现（3个月内的数据为25万条）：
（1）仅在主键上建立聚集索引，并且不划分时间段：
Select gid,fariqi,neibuyonghu,title from tgongwen
用时：128470毫秒（即：128秒）
（2）在主键上建立聚集索引，在fariq上建立非聚集索引：
select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi> dateadd(day,-90,getdate())
用时：53763毫秒（54秒）
（3）将聚合索引建立在日期列（fariqi）上：
select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi> dateadd(day,-90,getdate())
用时：2423毫秒（2秒）
虽然每条语句提取出来的都是25万条数据，各种情况的差异却是巨大的，特别是将聚集索引建立在日期列时的差异。事实上，如果您的数据库真的有1000万容量的话，把主键建立
在ID列上，就像以上的第1、2种情况，在网页上的表现就是超时，根本就无法显示。这也是我摒弃ID列作为聚集索引的一个最重要的因素。
得出以上速度的方法是：在各个select语句前加：declare @d datetime
set @d=getdate()
并在select语句后加：
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())
2、只要建立索引就能显著提高查询速度
事实上，我们可以发现上面的例子中，第2、3条语句完全相同，且建立索引的字段也相同；不同的仅是前者在fariqi字段上建立的是非聚合索引，后者在此字段上建立的是聚合索引，但查询速度却有着天壤之别。所以，并非是在任何字段上简单地建立索引就能提高查询速度。从建表的语句中，我们可以看到这个有着1000万数据的表中fariqi字段有5003个不同记录。在此字段上建立聚合索引是再合适不过了。在现实中，我们每天都会发几个文件，这几个文件的发文日期就相同，这完全符合建立聚集索引要求的：“既不能绝大多数都相同，又不能只有极少数相同”的规则。由此看来，我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的。 事实上，我们可以发现上面的例子中，第2、3条语句完全相同，且建立索引的字段也相同；不同的仅是前者在fariqi字段上建立的是非聚合索引，后者在此字段上建立的是聚合索引，但查询速度却有着天壤之别。所以，并非是在任何字段上简单地建立索引就能提高查询速度。从建表的语句中，我们可以看到这个有着1000万数据的表中fariqi字段有5003个不同记录。在此字段上建立聚合索引是再合适不过了。在现实中，我们每天都会发几个文件，这几个文件的发文日期就相同，这完全符合建立聚集索引要求的：“既不能绝大多数都相同，又不能只有极少数相同”的规则。由此看来，我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的。
3、把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度
上面已经谈到：在进行数据查询时都离不开字段的是“日期”还有用户本身的“用户名”。既然这两个字段都是如此的重要，我们可以把他们合并起来，建立一个复合索引（compound index）。
很多人认为只要把任何字段加进聚集索引，就能提高查询速度，也有人感到迷惑：如果把复合的聚集索引字段分开查询，那么查询速度会减慢吗？带着这个问题，我们来看一下以下的查询速度（结果集都是25万条数据）：（日期列fariqi首先排在复合聚集索引的起始列，用户名neibuyonghu排在后列）
（1）select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5'
查询速度：2513毫秒
（2）select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5' and neibuyonghu='办公室'
查询速度：2516毫秒
（3）select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu='办公室'
查询速度：60280毫秒
从以上试验中，我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，甚至比用上全部的复合索引列还要略快（在查询结果集数目一样的情况下）；而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。当然，语句1、2的查询速度一样是因为查询的条目数一样，如果复合索引的所有列都用上，而且查询结果少的话，这样就会形成“索引覆盖”，因而性能可以达到最优。同时，请记住：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。
其他书上没有的索引使用经验总结
1、用聚合索引比用不是聚合索引的主键速度快
下面是实例语句：（都是提取25万条数据）
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'
使用时间：3326毫秒
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000
使用时间：4470毫秒
这里，用聚合索引比用不是聚合索引的主键速度快了近1/4。
2、用聚合索引列比用一般的主键作order by时速度快，特别是在小数据量情况下
select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi
用时：12936
select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid
用时：18843
这里，用聚合索引比用一般的主键作order by时，速度快了3/10。事实上，如果数据量很小的话，用聚集索引作为排序列要比使用非聚集索引速度快得明显的多；而数据量如果很大的话，如10万以上，则二者的速度差别不明显。
3、使用聚合索引列内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1'
用时：6343毫秒（提取100万条）
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-6-6'
用时：3170毫秒（提取50万条）
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'
用时：3326毫秒（和上句的结果一模一样。如果采集的数量一样，那么用大于号和等于号是一样的）
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' and fariqi<'2004-6-6'
用时：3280毫秒
4 、日期列不会因为有分秒的输入而减慢查询速度
下面的例子中，共有100万条数据，2004年1月1日以后的数据有50万条，但只有两个不同的日期，日期精确到日；之前有数据50万条，有5000个不同的日期，日期精确到秒。
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' order by fariqi
用时：6390毫秒
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi<'2004-1-1' order by fariqi
用时：6453毫秒
5、其他注意事项
“水可载舟，亦可覆舟”，索引也一样。索引有助于提高检索性能，但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引，数据库就要做更多的工作。过多的索引甚至会导致索引碎片。
所以说，我们要建立一个“适当”的索引体系，特别是对聚合索引的创建，更应精益求精，以使您的数据库能得到高性能的发挥。
当然，在实践中，作为一个尽职的数据库管理员，您还要多测试一些方案，找出哪种方案效率最高、最为有效。
六、聚集索引的重要性和如何选择聚集索引
在上一节的标题中，笔者写的是：实现小数据量和海量数据的通用分页显示存储过程。这是因为在将本存储过程应用于“办公自动化”系统的实践中时，笔者发现这第三种存储过程在小数据量的情况下，有如下现象：
1、分页速度一般维持在1秒和3秒之间。
2、在查询最后一页时，速度一般为5秒至8秒，哪怕分页总数只有3页或30万页。
虽然在超大容量情况下，这个分页的实现过程是很快的，但在分前几页时，这个1－3秒的速度比起第一种甚至没有经过优化的分页方法速度还要慢，借用户的话说就是“还没有ACCESS数据库速度快”，这个认识足以导致用户放弃使用您开发的系统。
笔者就此分析了一下，原来产生这种现象的症结是如此的简单，但又如此的重要：排序的字段不是聚集索引！
本篇文章的题目是：“查询优化及分页算法方案”。笔者只所以把“查询优化”和“分页算法”这两个联系不是很大的论题放在一起，就是因为二者都需要一个非常重要的东西――聚集索引。
在前面的讨论中我们已经提到了，聚集索引有两个最大的优势：
1、以最快的速度缩小查询范围。
2、以最快的速度进行字段排序。
第1条多用在查询优化时，而第2条多用在进行分页时的数据排序。
而聚集索引在每个表内又只能建立一个，这使得聚集索引显得更加的重要。聚集索引的挑选可以说是实现“查询优化”和“高效分页”的最关键因素。
但要既使聚集索引列既符合查询列的需要，又符合排序列的需要，这通常是一个矛盾。
笔者前面“索引”的讨论中，将fariqi，即用户发文日期作为了聚集索引的起始列，日期的精确度为“日”。这种作法的优点，前面已经提到了，在进行划时间段的快速查询中，比用ID主键列有很大的优势。
但在分页时，由于这个聚集索引列存在着重复记录，所以无法使用max或min来最为分页的参照物，进而无法实现更为高效的排序。而如果将ID主键列作为聚集索引，那么聚集索引除了用以排序之外，没有任何用处，实际上是浪费了聚集索引这个宝贵的资源。
为解决这个矛盾，笔者后来又添加了一个日期列，其默认值为getdate()。用户在写入记录时，这个列自动写入当时的时间，时间精确到毫秒。即使这样，为了避免可能性很小的重合，还要在此列上创建UNIQUE约束。将此日期列作为聚集索引列。
有了这个时间型聚集索引列之后，用户就既可以用这个列查找用户在插入数据时的某个时间段的查询，又可以作为唯一列来实现max或min，成为分页算法的参照物。
经过这样的优化，笔者发现，无论是大数据量的情况下还是小数据量的情况下，分页速度一般都是几十毫秒，甚至0毫秒。而用日期段缩小范围的查询速度比原来也没有任何迟钝。
聚集索引是如此的重要和珍贵，所以笔者总结了一下，一定要将聚集索引建立在：
1、您最频繁使用的、用以缩小查询范围的字段上；
2、您最频繁使用的、需要排序的字段上。
SQL Server索引设计
