【转载】写有效率的SQL查询（III）

转载：本文转自cnblogs上Nineteen@newsmth的博客

先说说这些误区。所谓“误区”，有一些是新手很容易犯的错误或者很容易忽略的问题，另外一些，则是像“耗子吃了盐会变成蝙蝠”一样，让我们从小就认为是正确的事情。如下：

1、  表上不管用得着用不着，都加个聚集索引。

我们知道，表以两种方式组织物理存储：有聚集索引的“聚集表”；没有聚集索引的“堆”。在聚集表中，数据行按照聚集索引的顺序存储（这也是为啥一张表最多只能有一个聚集索引的原因）；堆中，数据行的存储可以认为是不确定的。

在偶《写有效率的SQL查询（II）》中曾经介绍过DB引擎如何在聚集表中通过非聚集索引查找目标数据：从非聚集索引树根开始seek，查找到目标索引行，然后通过索引行上存储的聚集索引键值，爬聚集索引树，并最终通过聚集索引行上的指针拿到目标数据。

但是堆上的非聚集索引存储的不是聚集索引键值，它存储的是指向目标行的指针。也就是说，如果在同样的表是堆，通过非聚集索引seek数据将省掉爬聚集索引树的损耗，而可以直接通过非聚集索引行上的行指针直接拿到目标数据。也就是说，在某些情况下，使用堆可以提高系统效率。

这个“某些情况”，就是你的需求，你的系统行为。一般情况下，所有人对要在什么样的字段上创建聚集索引都非常了解；但是不是所有的人都对应该在什么样的系统行为下，不创建聚集索引了解。假设你的表中有字段col1, col2,col3,col4等等，col1、col2的分布密度很低。你观察了系统行为，发现一半的查询是XXXX where col1 = YYYY，另一半的查询是XXXX where col2 = YYYY。这种情况下，使用堆就是更好的选择。

2、  primary key就是聚集索引。

primary key上是得有索引，但是这个索引可不见得一定得是聚集索引。尽管语句

create table testPK

(

           id int identity(1,1) primary key,

           fname varchar(64)

)

会在id列上创建聚集索引。当然，一般主键都是聚集索引，但也仅仅是“一般”而已。个人感觉，聚集索引的唯一目标就是数据检索，它应该建在什么字段上，完全由系统行为决定。“一般主键都是聚集索引”也仅仅是因为多数情况下，primary key字段上建所有更有益于效率而已。

create table testPK

(

           id int identity(1,1) primary key nonclustered,

           fname varchar(64)

)

可以创建primary key为非聚集索引

3、  Log类的表，有事没事加个自增的Id列。

这事相信干过的人很多，哈，而且一般还会顺手在这个Id列上加上个primary key的约束，聚集索引也就被无意识的建上了。就像一个记录用户活动的日志表，一般会有这么几个典型字段：Id、LogTime、UserId。实际上对这种表的查询，大多集中在LogTime和UserId上，Id完全没有实际意义。你的客服系统查找的，可能仅仅是某个用户的操作记录（一般按时间排序的），或者你的报表系统要生成每天的用户操作统计。想想看，如果干脆砍了Id列，并直接把聚集索引创建在LogTime上多爽。

4、  是个表就给加个primary key约束

就像3中的例子，primary完全没必要。呵呵，这条看着简单是简单了，犯这错误的人，那也不比3少。

5、  在where条件里对同一个表中的列做运算或比较，以为创建某种类型的索引可以提高效率。（这种情况下，任何索引都无法提升性能。解决办法见偶前面的“写有效率的sql查询”）

见过了无数的这种写法。最常见的，如：一张用户表里有用户注册时间（t1,YYYYDD），有退订时间（t2,YYYYDD），现在让你获取存活时间大于3天的用户总数：很多人一不注意，就整一个select count(*) from Users where t1 – t2 > 3出来。而且常常会臆测在t1、t2上建个涵盖索引（或者分别在t1、t2上建索引）会让性能提升。

6、  在表上创建了col1、col2顺序的涵盖索引（聚集的或非聚集的），但是where条件里就一个col2 > XXX。这种情况下，就不如分别在col1、col2上创建索引。

 

以上的误区，都是在工作中常常犯或遇到的，没遇到的肯定还有，欢迎各位安达补充：）

 

OK，接下来我们说说“涵盖索引”和include索引。

所谓的涵盖索引，就是传统方式在多个列上创建的索引。“inlude索引”是SQL2k5提供的新功能，允许添加非键列到非聚集索引的叶节点上。

创建涵盖索引：

create index ix_tb_col1_col2 on tb

(

        col1,

        col2

)

创建include索引：

create index ix_tb_col1 on tb

(

        col1

)include(col2, col3,col4)

         涵盖索引和include索引的区别在于，涵盖索引的所有列都是键列，索引行的物理存储顺序就是col1、col2的顺序，这也是误区6之所以称为误区的原因。涵盖索引可以是聚集索引，也可以是非聚集索引。

         include索引include的列并不影响索引行的物理存储顺序，它们作为一个挂件“挂在”索引行上。挂着这些挂件的作用在于，诸如select col2, col3, col4 from tb where col1 = XXX只需要seek一把非聚集索引ix_tb1_col1就OK了，拿到索引行就拿到了需要的所有数据。挂件们是要占用索引行空间的，我们知道，索引字段宽度要尽可能窄是选择索引的一项基本原则（这项原则背后的原理是尽可能让索引树深度小），所以并不是include的字段越多越好，这得跟你的系统行为有一个平衡。

         从上面叙述可以看到，涵盖索引实际上是include索引的加强版。也就是说，你的where条件里除了涵盖索引的第一个索引列之外还有其他索引列的比较，创建涵盖索引要比include索引高效一点点。同样，维护涵盖索引的消耗也会多少高于Include索引。

         聚集索引的索引行直接包含了数据行指针，也就是说，通过聚集索引行，可以直接拿到其他所有列的数据，从某种意义上说，聚集索引就是最大的include索引，这也是include索引只能是非聚集索引的原因所在。

         OK，给你一条SQL语句：

select col1, col2, col3, col4 from tb where col5 > XXX and col6 > yyy

你既可以在上面创建涵盖索引col5、col6，又可以创建include索引（col5/col6）include(col1、col2、col3、col4)。选择如何创建，就要看你的表各字段宽度、系统行为了。在此不再赘述。

 

最后讲讲如何拿到在文中频频提到的系统行为统计信息。这东西说白了就是各种SQL的执行次数、逻辑IO、物理IO、执行消耗CPU时间等等等等。想想看，假如你拿了一份系统中所有SQL的文本、执行总次数、逻辑IO占用总IO比例、物理IO占用总IO比例、平均逻辑IO、平均物理IO等等等等，你八成能够指出系统瓶颈所在，老板和伙计们的眼光也会会极大的满足你小小的虚荣心，哈。这些东西就在动态视图sys.dm_exec_query_stats里面，自个翻翻联机文档吧：）

拿到系统行为统计信息之后，你终于调整了索引，于是系统明显nb了。如果你要看看它变得有多nb，可以关注动态视图sys.dm_db_index_usage_stats，这个也就不多说了。

 

最后，多读联机文档，多做尝试，尽力不用工具而手写SQL才是硬道理。

=====================

关于表上是不是都需要一个聚集索引,各位安达展开了剧烈讨论.摘录部分到这里:
from RicCC:
描述的确不足，是否选择聚集索引不是这么简单
1. heap表的查询，除了table scan和covering index之外，都需要bookmark lookup，covering index的使用是有限的，剩下的都是成本很高的操作。除非对这个表的查询很少。
2. heap的数据页之间没有link，顺序读取数据性能低，I/O开销大。除非每次都用unique index seek。
3. heap每个insert数据都是在末尾，并发的insert阻塞问题比较大。因为insert位置一次只能有一个任务加排它锁。可以用clustered改善。
4. delete多时，heap比clustered更浪费磁盘空间，碎片更严重，并且没有正常的方式消除heap数据页的碎片，只能建clustered或者drop table重建。

目前为止我基本没有发现充足的证据使用heap.
============
index seek跟unique index seek不一样，例如你要找8.1-8.9号的log，执行计划里面只会看到一个index seek，它seek的是第一条数据，从第一条数据到最后一条用的是scan，并且heap肯定要用到rid/index lookup，假如要取的是1.1-8.9，rid/index lookup的成本很可能导致sql server放弃index而使用table scan

综合考虑，使用heap的范围实在是太狭窄，clustered index怎样建倒很有文章，需要极为认真的对待.
============
index是unique的，index条件都给出来了并且全部是=，每次seek操作输出都只有一条记录，就是unique index seek，oracle是有这个操作的
如果不是unique index seek，就一定会有range index scan。sql server heap表的range index scan需要在IAM跟数据页间切换，效率不好，clustered index就是用于改善这种状况，并且充分利用磁盘设备读取连续数据的优化措施
========================================================================

Me:到目前为止,我找到的最有理由使用堆的地方是一张每天产生kw级记录的日志表,这张表上的查询主要以查询指定Id的用户在某段时间内的记录.