OK,到这里,我们明白了为啥SQLServer会选择indexseek和indexscan。
也顺便明白了通过非聚集索引查询时,结果集相对总行数多寡对查询计划选择的巨大影响。
(结果集/总行数)被称为选择性,比值越大,选择性就越高。
你得到了它,本文的重点就是选择性。
统计信息,说白了,就是表中某个字段取某个值时有多少行结果集。
统计信息可以说是一种选择性的度量,SQLServer就是根据它来估算不同查询计划的优劣。
后面将通过一个实际的例子来说明统计信息对查询计划的影响。
以下是示例表的表结构:
各位可以注意到,该表上有一个identity字段charge_no,聚集索引就创建在它上面。
有两个非聚集索引indx_category_no,indx_provider_no,我们重点关注indx_provider_no。
现在来看看provider_no字段的统计信息(有点长,我前边粘一部分,后边粘一部分):
(上述各字段含义,见联机文档对DBCCSHOW_STATISTICS的描述)
从上面的贴图可以看到,表中总行数为1w,采样行数为1w。
provider_no值为21的只有1行,而值为500的行则有4824行。
下面两张图是两条SQL的查询计划,我就不多嘴解释了。
那么问题来了:
我们知道,SQLServer会缓存查询计划,假如有这么一个存储过程:
createprocmyproc
(
@pnoint
)
as
select*fromchargewhereprovider_no=@pno
第一次我们传进来一个21,OK,它会缓存该存储过程的执行计划为nonclusteredindexseek那个。
后来我们又传进来一个500,完蛋了,服务器发现它有一个myproc的缓存,so,又通过nonclusteredindexseek执行,接着你的同伙看到你的查询花费了巨量的IO,于是,你被鄙视了。
这说明了啥?
说明如果你的查询选择性变动剧烈,你应该告诉SQLServer不要缓存查询计划,每次都应该重新评估、编译。
实现方法很简单,查询的尾巴上加一个option(recompile)好了。
而且SQL2k5还有一个nb的feature,可以每次只重新编译存储过程的一部分(当然,你也可以选择重新编译整个存储过程,这取决于你的需求。
详见联机文档。
)
=======彪悍的分割线================================
后面blog会提到索引优化。
其实百敬同学那本《SQL性能调校》这方面讲的不少了。
那本书唯一的缺憾就是某些规则在SQL2k5中不适合。
我想我会尽力都写出来。
写有效率的SQL查询(III)
先说说这些误区。
所谓“误区”,有一些是新手很容易犯的错误或者很容易忽略的问题,另外一些,则是像“耗子吃了盐会变成蝙蝠”一样,让我们从小就认为是正确的事情。
如下:
1、 表上不管用得着用不着,都加个聚集索引。
我们知道,表以两种方式组织物理存储:
有聚集索引的“聚集表”;没有聚集索引的“堆”。
在聚集表中,数据行按照聚集索引的顺序存储(这也是为啥一张表最多只能有一个聚集索引的原因);堆中,数据行的存储可以认为是不确定的。
在偶《写有效率的SQL查询(II)》中曾经介绍过DB引擎如何在聚集表中通过非聚集索引查找目标数据:
从非聚集索引树根开始seek,查找到目标索引行,然后通过索引行上存储的聚集索引键值,爬聚集索引树,并最终通过聚集索引行上的指针拿到目标数据。
但是堆上的非聚集索引存储的不是聚集索引键值,它存储的是指向目标行的指针。
也就是说,如果在同样的表是堆,通过非聚集索引seek数据将省掉爬聚集索引树的损耗,而可以直接通过非聚集索引行上的行指针直接拿到目标数据。
也就是说,在某些情况下,使用堆可以提高系统效率。
这个“某些情况”,就是你的需求,你的系统行为。
一般情况下,所有人对要在什么样的字段上创建聚集索引都非常了解;但是不是所有的人都对应该在什么样的系统行为下,不创建聚集索引了解。
假设你的表中有字段col1,col2,col3,col4等等,col1、col2的分布密度很低。
你观察了系统行为,发现一半的查询是XXXXwherecol1=YYYY,另一半的查询是XXXXwherecol2=YYYY。
这种情况下,使用堆就是更好的选择。
2、 primarykey就是聚集索引。
primarykey上是得有索引,但是这个索引可不见得一定得是聚集索引。
尽管语句
createtabletestPK
(
idintidentity(1,1)primarykey,
fnamevarchar(64)
)
会在id列上创建聚集索引。
当然,一般主键都是聚集索引,但也仅仅是“一般”而已。
个人感觉,聚集索引的唯一目标就是数据检索,它应该建在什么字段上,完全由系统行为决定。
“一般主键都是聚集索引”也仅仅是因为多数情况下,primarykey字段上建所有更有益于效率而已。
createtabletestPK
(
idintidentity(1,1)primarykeynonclustered,
fnamevarchar(64)
)
可以创建primarykey为非聚集索引
3、 Log类的表,有事没事加个自增的Id列。
这事相信干过的人很多,哈,而且一般还会顺手在这个Id列上加上个primarykey的约束,聚集索引也就被无意识的建上了。
就像一个记录用户活动的日志表,一般会有这么几个典型字段:
Id、LogTime、UserId。
实际上对这种表的查询,大多集中在LogTime和UserId上,Id完全没有实际意义。
你的客服系统查找的,可能仅仅是某个用户的操作记录(一般按时间排序的),或者你的报表系统要生成每天的用户操作统计。
想想看,如果干脆砍了Id列,并直接把聚集索引创建在LogTime上多爽。
4、 是个表就给加个primarykey约束
就像3中的例子,primary完全没必要。
呵呵,这条看着简单是简单了,犯这错误的人,那也不比3少。
5、 在where条件里对同一个表中的列做运算或比较,以为创建某种类型的索引可以提高效率。
(这种情况下,任何索引都无法提升性能。
解决办法见偶前面的“写有效率的sql查询”)
见过了无数的这种写法。
最常见的,如:
一张用户表里有用户注册时间(t1,YYYYDD),有退订时间(t2,YYYYDD),现在让你获取存活时间大于3天的用户总数:
很多人一不注意,就整一个selectcount(*)fromUserswheret1–t2>3出来。
而且常常会臆测在t1、t2上建个涵盖索引(或者分别在t1、t2上建索引)会让性能提升。
6、 在表上创建了col1、col2顺序的涵盖索引(聚集的或非聚集的),但是where条件里就一个col2>XXX。
这种情况下,就不如分别在col1、col2上创建索引。
以上的误区,都是在工作中常常犯或遇到的,没遇到的肯定还有,欢迎各位安达补充:
)
OK,接下来我们说说“涵盖索引”和include索引。
所谓的涵盖索引,就是传统方式在多个列上创建的索引。
“inlude索引”是SQL2k5提供的新功能,允许添加非键列到非聚集索引的叶节点上。
创建涵盖索引:
createindexix_tb_col1_col2ontb
(
col1,
col2
)
创建include索引:
createindexix_tb_col1ontb
(
col1
)include(col2,col3,col4)
涵盖索引和include索引的区别在于,涵盖索引的所有列都是键列,索引行的物理存储顺序就是col1、col2的顺序,这也是误区6之所以称为误区的原因。
涵盖索引可以是聚集索引,也可以是非聚集索引。
include索引include的列并不影响索引行的物理存储顺序,它们作为一个挂件“挂在”索引行上。
挂着这些挂件的作用在于,诸如selectcol2,col3,col4fromtbwherecol1=XXX只需要seek一把非聚集索引ix_tb1_col1就OK了,拿到索引行就拿到了需要的所有数据。
挂件们是要占用索引行空间的,我们知道,索引字段宽度要尽可能窄是选择索引的一项基本原则(这项原则背后的原理是尽可能让索引树深度小),所以并不是include的字段越多越好,这得跟你的系统行为有一个平衡。
从上面叙述可以看到,涵盖索引实际上是include索引的加强版。
也就是说,你的where条件里除了涵盖索引的第一个索引列之外还有其他索引列的比较,创建涵盖索引要比include索引高效一点点。
同样,维护涵盖索引的消耗也会多少高于Include索引。
聚集索引的索引行直接包含了数据行指针,也就是说,通过聚集索引行,可以直接拿到其他所有列的数据,从某种意义上说,聚集索引就是最大的include索引,这也是include索引只能是非聚集索引的原因所在。
OK,给你一条SQL语句:
selectcol1,col2,col3,col4fromtbwherecol5>XXXandcol6>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才是硬道理。
=====================
关于表上是不是都需要一个聚集索引,各位安达展开了剧烈讨论.摘录部分到这里:
fromRicCC:
描述的确不足,是否选择聚集索引不是这么简单
1.heap表的查询,除了tablescan和coveringindex之外,都需要bookmarklookup,coveringindex的使用是有限的,剩下的都是成本很高的操作。
除非对这个表的查询很少。
2.heap的数据页之间没有link,顺序读取数据性能低,I/O开销大。
除非每次都用uniqueindexseek。
3.heap每个insert数据都是在末尾,并发的insert阻塞问题比较大。
因为insert位置一次只能有一个任务加排它锁。
可以用clustered改善。
4.delete多时,heap比clustered更浪费磁盘空间,碎片更严重,并且没有正常的方式消除heap数据页的碎片,只能建clustered或者droptable重建。
目前为止我基本没有发现充足的证据使用heap.
============
indexseek跟uniqueindexseek不一样,例如你要找8.1-8.9号的log,执行计划里面只会看到一个indexseek,它seek的是第一条数据,从第一条数据到最后一条用的是scan,并且heap肯定要用到rid/indexlookup,假如要取的是1.1-8.9,rid/indexlookup的成本很可能导致sqlserver放弃index而使用tablescan
综合考虑,使用heap的范围实在是太狭窄,clusteredindex怎样建倒很有文章,需要极为认真的对待.
============
index是unique的,index条件都给出来了并且全部是=,每次seek操作输出都只有一条记录,就是uniqueindexseek,oracle是有这个操作的
如果不是uniqueindexseek,就一定会有rangeindexscan。
sqlserverheap表的rangeindexscan需要在IAM跟数据页间切换,效率不好,clusteredindex就是用于改善这种状况,并且充分利用磁盘设备读取连续数据的优化措施
========================================================================
Me:
到目前为止,我找到的最有理由使用堆的地方是一张每天产生kw级记录的日志表,这张表上的查询主要以查询指定Id的用户在某段时间内的记录.
写有效率的SQL查询(VI)
我们先看NestedLoop和MergeJoin的算法(以下为引用,见RicCC的《通往性能优化的天堂-地狱JOIN方法说明》):
==================================
NestedLoop:
foreachrowAintableAwheretableA.col2=?
{
searchrowsBfromtableBwheretableB.col1=rowA.col1andtableB.col2=?
;
if(rowsB.Count<=0)
discardrowA;
else
outputrowAandrowsB;
}
MergeJoin:
两个表都按照关联字段排序好之后,mergejoin操作从每个表取一条记录开始匹配,如果符合关联条件,则放入结果集中;否则,将关联字段值较小的记录抛弃,从这条记录对应的表中取下一条记录继续进行匹配,直到整个循环结束。
==================================
我们通过最简单的情况来计算NestedLoop和MergeJoin的消耗:
两张表A、B,分别有m、n行数据(m