SQL-索引

理解“聚集索引”和“非聚集索引”

① 聚集索引（clustered   index，也称聚类索引、簇集索引）：把内容本身就是一种按照一定规则排列的目录称为“聚集索引”

       我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。

② 非聚集索引（nonclustered   index，也称非聚类索引、非簇集索引）：目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”

       如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。

由以上解释，就很容易理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。

索引使用的误区

① 主键就是聚集索引

        通常，我们会在每个表中都建立一个 ID 列，以区分每条数据，并且这个 ID 列是自动增大的，步长一般为 1 。这种 ID 是自动生成，我们并不知道每条记录的ID号，所以我们很难在实践中用 ID 号来进行查询（ 若要查询需要提前知道要查询记录的 ID 号，这就有点本末倒置了 ）。

② 只要建立索引就能显著提高查询速度

        并非是在任何字段上简单地建立索引就能提高查询速度，因此建立“适当”的聚合索引对于我们提高查询速度是非常重要的

③ 把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度

        仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，甚至比用上全部的复合索引列还要略快（在查询结果集数目一样的情况下）；而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。

索引使用经验总结

① 用聚合索引比用不是聚合索引的主键速度快

② 用聚合索引比用一般的主键作order   by时速度快，特别是在小数据量情况下

        如果数据量很小的话，用聚集索引作为排序列要比使用非聚集索引速度快得明显的多；而数据量如果很大的话，如10万以上，则二者的速度差别不明显。

③ 使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个

④ 日期列不会因为有分秒的输入而减慢查询速度

⑤ 建立一个“适当”的索引体系，特别是对聚合索引的创建

        引有助于提高检索性能，但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引，数据库就要做更多的工作。过多的索引甚至会导致索引碎片。

不良的sql

   不良的 SQL 往往来自于 不恰当的索引设计、不充份的连接条件 和 不可优化的 where 子句 。在对它们进行适当的优化后，其运行速度有了明显地提高！

① 不恰当的索引设计

缺省情况下建立的索引是非聚集索引，但有时它并不是最佳的；合理的索引设计要建立在对各种查询的分析和预测上。一般来说：

• 有大量重复值、且经常有范围查询（ between , > , < , >= , <= ）和 order by 、group by 发生的列，可考虑建立聚集索引；

• 经常同时存取多列，且每列都含有重复值可考虑建立组合索引； 

• 组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。

② 不充份的连接条件

eg：表 card 有 7896行，在 card_no 上有一个非聚集索引，表 account 有 191122行，在 account_no 上有一个非聚集索引。试看在不同的表连接条件下，两个 SQL 的执行情况：

1 select sum(a.amount) from account a,card b where a.card_no = b.card_no（20秒）
2 -- 将SQL改为：
3 select sum(a.amount) from account a,card b where a.card_no = b.card_no and a.account_no=b.account_no（< 1秒）

4 -- 在第一个连接条件下，最佳查询方案是将 account 作外层表，card 作内层表，利用 card 上的索引，其 I/O 次数可由以下公式估算为：
5 ---- 外层表 account 上的 22541页 +（ 外层表 account 的 191122行 * 内层表 card 上对应外层表第一行所要查找的3页 ）= 595907 次 I/O


6 -- 在第二个连接条件下，最佳查询方案是将 card 作外层表，account 作内层表，利用 account 上的索引，其 I/O 次数可由以下公式估算为：
7 ---- 外层表 card 上的 1944页 +（ 外层表 card 的 7896行 * 内层表 account 上对应外层表每一行所要查找的4页 ）= 33528 次 I/O

可见，只有充份的连接条件，真正的最佳方案才会被执行。

        多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表。

       内外表的选择可由公式：外层表中的匹配行数 * 内层表中每一次查找的次数确定，乘积最小为最佳方案。

③ 不可优化的 where 子句

        # where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表搜索。

eg：

 1 select * from record where substring ( card_no , 1 , 4) ="5378"(13秒)
 2 --改为下面的SQL
 3 select * from record where card_no like "5378%"（ <1秒 )
 4 
 5 select * from record where amount/30 < 1000 ( 11秒 )
 6 --改为下面的SQL
 7 select * from record where amount < 1000*30（<1秒）
 8 
 9 select * from record where convert ( char(10) , date , 112 ) = "19991201"（10秒）
10 --改为下面的SQL
11 select * from record where date= "1999/12/01"（< 1秒）

# 所谓优化即where子句利用了索引，不可优化即发生了表扫描或额外开销

eg：表 stuff 有 200000 行，id_no 上有非聚集索引，请看下面这个 SQL ：

select count(*) from stuff where id_no in("0","1") （23秒）

       where条件中的 "in" 在逻辑上相当于 "or" ，所以语法分析器会将 in ( "0" , "1" ) 转化为 id_no = "0" or id_no = "1" 来执行。

　　我们期望它会根据每个 or 子句分别查找，再将结果相加，这样可以利用 id_no 上的索引；但实际上（ 根据 showplan ）,它却采用了 " OR 策略 " ，即先取出满足每个 or 子句的行，存入临时数据库的工作表中，再建立唯一索引以去掉重复行，最后从这个临时表中计算结果。因此，实际过程没有利用 id_no 上索引，并且完成时间还要受 tempdb 数据库性能的影响。

▶ 实践证明，表的行数越多，工作表的性能就越差，当 stuff 有 620000行 时，执行时间竟达到 220秒 ！还不如将 or 子句分开：

select count(*) from stuff where id_no="0"
select count(*) from stuff where id_no="1"

得到两个结果，再作一次加法合算。因为每句都使用了索引，执行时间只有3秒，在 620000行 下，时间也只有 4秒。

▶ 用更好的方法，写一个简单的存储过程：

 1 create proc count_stuff as
 2 declare @a int
 3 declare @b int
 4 declare @c int
 5 declare @d char(10)
 6 begin
 7 select @a=count(*) from stuff where id_no="0"
 8 select @b=count(*) from stuff where id_no="1"
 9 end
10 select @c=@a+@b
11 select @d=convert(char(10),@c)
12 print @d

直接算出结果，执行时间同上面一样快！

由以上三点，可以总结以下结论

 

任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

 

in 、or 子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。

 

要善于使用存储过程，它使 SQL 变得更加灵活和高效。

 

---- 从以上这些例子可以看出，SQL 优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的 I/O 次数，尽量避免表搜索的发生。

 

FILLFACTOR （ 填充因子 ）

要理解填充因子的作用，首先需要理解什么是页拆分

页拆分

        在创建聚集索引时，表中的数据按照索引列中的值的顺序存储在数据库的数据页中。在表中插入新的数据行或更改索引列中的值时，SQL Server 必须重新组织表中的数据存储，以便为新行腾出空间，保持数据的有序存储。这同样适用于非聚集索引。添加或更改数据时，SQL Server 不得不重新组织非聚集索引页中的数据存储。向一个已满的索引页添加某个新行时，SQL Server 把大约一半的行移到新页中以便为新行腾出空间。这种重组称为页拆分。

        页拆分会降低性能并使表中的数据存储产生碎片。

填充因子的作用理解

       当创建一个新索引，或重建一个存在的索引时，你可以指定一个填充因子，它是在索引创建时索引里的数据页被填充的数量。

       填充因子设置为 100 意味着每个索引页 100％ 填满，50％ 意味着每个索引页 50％ 填满。 如果你创建一个填充因子为 100 的聚集索引（ 在一个非单调递增的列上 ），那意味着每当一个记录被插入（ 或修改 ）时，页拆分都会发生，因为在现存的页上没有这些数据的空间。

       eg：假定你刚刚用缺省的填充因子新创建了一个索引。当sqlserver创建它时，它把索引放在相邻的物理页面上，因为数据能够顺序的读所以这样会有最优的i/o访问。但当表随着、、增加和改变时，发生了页拆分。当页拆分发生时，sqlserver必须在磁盘的某处分配一个新的页，这些新的页和最初的物理页不是连续的。因此，访问使用的是随机的i/o，而不是有顺序的i/o，这样访问索引页会变得更慢。

填充因子如何设置比较好

原则：

低更改的表（读写比率为100：1）：100％的填充因子

高更改的表（写超过读）50-70％的填充因子

读写各一半的：80-90％的填充因子