ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 写有效率的SQL查询(III)

写有效率的SQL查询(III)

原创 Linux操作系统 作者:sunger_chen 时间:2009-03-13 13:19:01 0 删除 编辑

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

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

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

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

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

这个“某些情况”,就是你的需求,你的系统行为。一般情况下,所有人对要在什么样的字段上创建聚集索引都非常了解;但是不是所有的人都对应该在什么样的系统行为下,不创建聚集索引了解。假设你的表中有字段col1, col2,col3,col4等等,col1col2的分布密度很低。你观察了系统行为,发现一半的查询是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的约束,聚集索引也就被无意识的建上了。就像一个记录用户活动的日志表,一般会有这么几个典型字段:IdLogTimeUserId。实际上对这种表的查询,大多集中在LogTimeUserId上,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出来。而且常常会臆测在t1t2上建个涵盖索引(或者分别在t1t2上建索引)会让性能提升。

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

 

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

 

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索引的区别在于,涵盖索引的所有列都是键列,索引行的物理存储顺序就是col1col2的顺序,这也是误区6之所以称为误区的原因。涵盖索引可以是聚集索引,也可以是非聚集索引。

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

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

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

         OK,给你一条SQL语句:

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

你既可以在上面创建涵盖索引col5col6,又可以创建include索引(col5/col6include(col1col2col3col4)。选择如何创建,就要看你的表各字段宽度、系统行为了。在此不再赘述。

 

最后讲讲如何拿到在文中频频提到的系统行为统计信息。这东西说白了就是各种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的用户在某段时间内的记录.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16998571/viewspace-567159/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-01-09

  • 博文量
    28
  • 访问量
    24425