数据库设计方案经典推荐

最新推荐文章于 2019-11-01 10:00:00 发布

iteye_19871

最新推荐文章于 2019-11-01 10:00:00 发布

阅读量182

点赞数

文章标签：数据结构 WPF SQL F# EXT

写有效率的SQL查询（I）

大型系统的生产环境，一般情况下，我们评价一条查询是否有效率，更多的是关注逻辑IO(至于为什么，回头补一篇)。我们常说，“要建彪悍的索引”、“要写高效的SQL”，其实最终目的就是在相同结果集情况下，尽可能减少逻辑IO。

1.1 where条件的列上都得有统计信息。

没统计信息SQLServer就无法估算不同查询计划开销优劣，而只能采用最稳妥的Scan（不管是table scan还是clustered index scan）。一般情况下我们不会犯这种错误——where条件里不使用非索引列是个常识。索引上的统计信息是无法删除的。

1.2 尽量不使用不等于（!=）或者NOT逻辑运算符。

这条规则被广为传颂，原因据联机文档和百敬同学的书讲，也是SQLServer无法评估不同查询计划开销的优劣。但是SqlServer2k5聪明了很多，试验发现尽管用了!=或者not，查询还是会被优化。如下：

create table tb1

(

col1 int identity(1,1) primary key,

col2 int not null,

col3 varchar(64) not null

)

create index ix_tb1_col2 on tb1

(

col2

)

create index ix_tb1_col3 on tb1

(

col3

)

declare @f int

set @f = 0

while @f < 9999

begin

insert into tb1 (col2, col3) values(1, 'ssdd')

set @f = @f + 1

end

insert into tb1 (col2, col3) values(0, 'aadddd')

insert into tb1 (col2, col3) values(2, 'bbddd')

insert into tb1 (col2, col3) values(3, 'bbaaddddddaa')

通过上述代码，各位可以看到数据分布。col2值为1的有9999条；col2值为0、2、3的分别有1条。

按照本条规则，!= 和NOT带来的应该是个scan操作，但实际情况是：
<shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><stroke joinstyle="miter"></stroke><formulas><f eqn="if lineDrawn pixelLineWidth 0"></f><f eqn="sum @0 1 0"></f><f eqn="sum 0 0 @1"></f><f eqn="prod @2 1 2"></f><f eqn="prod @3 21600 pixelWidth"></f><f eqn="prod @3 21600 pixelHeight"></f><f eqn="sum @0 0 1"></f><f eqn="prod @6 1 2"></f><f eqn="prod @7 21600 pixelWidth"></f><f eqn="sum @8 21600 0"></f><f eqn="prod @7 21600 pixelHeight"></f><f eqn="sum @10 21600 0"></f></formulas><path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></path><lock aspectratio="t" v:ext="edit"></lock></shapetype><shape id="_x0000_i1061" style="WIDTH: 378.75pt; HEIGHT: 152.25pt" alt="" type="#_x0000_t75"><imagedata o:href="http://www.cnblogs.com/images/cnblogs_com/cn_wpf/p1.GIF" src="file:///C:%5CDOCUME~1%5Clidawei%5CLOCALS~1%5CTemp%5Cmsohtml1%5C01%5Cclip_image001.gif"></imagedata></shape>
<shape id="_x0000_i1062" style="WIDTH: 378.75pt; HEIGHT: 152.25pt" alt="" type="#_x0000_t75"><imagedata o:href="http://www.cnblogs.com/images/cnblogs_com/cn_wpf/p2.GIF" src="file:///C:%5CDOCUME~1%5Clidawei%5CLOCALS~1%5CTemp%5Cmsohtml1%5C01%5Cclip_image002.gif"></imagedata></shape>

SQL2k5很聪明，它依据统计信息分析得出来，应该采用index seek而不是index scan。（稍微解释解释index seek和index scan：索引是一颗B树，index seek是查找从B树的根节点开始，一级一级找到目标行。index scan则是从左到右，把整个B树遍历一遍。假设唯一的目标行位于索引树（假设是非聚集索引，树深度2，叶节点占用k页物理存储）最右的叶节点上（如上例）。index seek引起的IO是4，而index scan引起的IO是K，性能差别巨大。关于索引，可以仔细读读联机文档关于物理数据库体系结构部分）。

1.3 查询条件中不要包含运算

这些运算包括字符串连接（如：select * from Users where UserName + ‘pig’ = ‘张三pig’），通配符在前面的Like运算（如：select * from tb1 where col4 like ‘%aa’），使用其他用户自定义函数、系统内置函数、标量函数等等（如：select * from UserLog where datepart(dd, LogTime) = 3）。

SQLServer在处理以上语句时，一样没办法估算开销。最终结果当然是clustered index scan或者table scan了。

1.4 查询条件中不要包含同一张表内不同列之间的运算

所谓的“运算”包括加减乘除或通过一些function（如：select * from tb where col1 – col2 = 1997），也包括比较运算（如：select * from tb where col1 > col2）。这种情况下，SQLServer一样没办法估算开销。不论col1、col2上都有索引还是创建了col1、col2上的覆盖索引还是创建了col1 include col2的索引。

但是这种查询有解决办法，可以在表上多创建一个计算字段，其值设置为你的“运算”结果，再在该字段上创建一个索引，就Ok了。

To Be Continue…

(II)中将介绍统计信息值分布不均匀对查询的影响和如何避免这些影响，捎带更多的说说返回多行结果时，为啥SQLServer有时会选择index seek，而有时会选择index scan。
(III)中主要介绍传说中的“Foldable”和“NonFoldable”表达式。并举例说说所谓的“Nonfoldable"表达式某些情况下也不是那么可怕。
(IV)中则主要说说在程序中执行SQL。如：安全性，拼SQL、参数化SQL和存储过程之间对DB来说有什么区别，参数化SQL的一些技巧。捎带着，会大概介绍介绍SQLServer的Buffer Pool

写有效率的SQL查询（II）

上回我们说到评估一条语句执行效率主要看逻辑IO（啥是逻辑IO，啥是物理IO见联机文档），这次我们继续。

我们先说说，返回多行结果时，为什么SQLServer有时会选择index seek，有时会选择index scan。

以nonclustered index为例说明。

像所有的索引B树一样，非聚集索引树也包括完全由索引数据组成的根节点和中间级节点；但是和聚集索引树不同的是，聚集索引树叶节点包含的是基础表的数据页（我们常说，表的物理存储顺序和聚集索引相同，就是这个原因）,非聚集索引树叶节点是索引页。SQLServer通过非聚集索引查找数据时，会通过这个非聚集索引键值去搜索聚集索引，进而检索基础表数据行。

假设有这样一张表，非聚集索引树深度为2，一层根节点（1个索引页），一层叶节点（4个索引页）。聚集索引树深度为3，一层根节点（1个索引页），一层中间级节点（2个索引页），一层叶节点（250页，也就是基础表物理存储页）表的数据假设1w行。注：所有数据均为假设，只为说明原理。

我们首先，再强调一遍，SQLServer获取数据，总是以页为单位，就算是只读取一行也会获取整张页（见《写有效率的SQL查询（I）》）

现在有一条简单查询(如：select * from tb where col2 = 99，col2是tb表中的非聚集索引)，假设会返回100行。

Ok，我们来分析如果以Index seek来查找这100行会有多少IO。index seek每次都从索引树根节点开始查找，找到中间级节点（99对应的索引行），然后从该节点行开始连续遍历所有col2为99的索引行。在遍历这些行时，每拿到一条，都会通过该条索引行中聚集索引键值去聚集索引树中index seek，然后从数据页中获取数据。在最坏的情况下，col2为99对应的索引行跨越了全部4个叶级非聚集索引页（当然，这没啥可能性，举例而已，切勿深究）；每次通过聚集索引树进行index seek，IO开销最坏情况下是一个根节点，一个中间级节点，一个数据页,一共要seek100次，开销300个逻辑IO。综上，通过nonclustered index seek总共开销是305个IO。

要知道，我们的基础表数据页一共才250页，这说明了啥？说明就算是我从头到尾扫描一遍表也比noncustered index seek快。这时，SQL2k5会产生一个完完全全的clustered index scan执行计划来搞定表扫描。

好了，现在我们再来分析select * from tb1 where col2 = 1。假设它的结果集为5行。如果这时还是进行nonclustered index seek的话，逻辑IO按照上面相似的分析，应该是19个IO，远远要小于整个的clustered index scan。这时，SQLServer自然会采用nonclustered index seek。

我们再来看聚集索引。聚集索引和非聚集索引最大的不同在于聚集索引的存储顺序就是基础表的物理存储顺序。还是上面的表tb，假设聚集索引建在了col1上.如果where条件是col1 = XX的话，自然是index seek，因为IO最小，撑死了只有3（一个聚集索引根节点页，一个聚集索引中间级节点页，一个数据页）；如果where条件是col1 > XX的话，不管行集是多大，SQLServer总是首先通过index seek拿到XX对应的数据页，然后挨梆往后遍历基础表数据页到尾巴就OK了。最坏情况XX恰好比表中最小的col1小，那就读取所有行。如果where条件是col1 < XX，那就倒着检索聚集索引，无他。

OK，到这里，我们明白了为啥SQLServer会选择index seek和index scan。也顺便明白了通过非聚集索引查询时，结果集相对总行数多寡对查询计划选择的巨大影响。

（结果集/总行数）被称为选择性，比值越大，选择性就越高。

你得到了它，本文的重点就是选择性。

统计信息，说白了，就是表中某个字段取某个值时有多少行结果集。统计信息可以说是一种选择性的度量，SQLServer就是根据它来估算不同查询计划的优劣。

后面将通过一个实际的例子来说明统计信息对查询计划的影响。

以下是示例表的表结构：
<shape id="_x0000_i1055" style="WIDTH: 619.5pt; HEIGHT: 411pt" alt="" type="#_x0000_t75"><imagedata o:href="http://www.cnblogs.com/images/cnblogs_com/cn_wpf/p3.GIF" src="file:///C:%5CDOCUME~1%5Clidawei%5CLOCALS~1%5CTemp%5Cmsohtml1%5C01%5Cclip_image003.gif"></imagedata></shape>

各位可以注意到，该表上有一个identity字段charge_no，聚集索引就创建在它上面。有两个非聚集索引indx_category_no，indx_provider_no，我们重点关注indx_provider_no。现在来看看provider_no字段的统计信息（有点长，我前边粘一部分，后边粘一部分）：
<shape id="_x0000_i1056" style="WIDTH: 498.75pt; HEIGHT: 30.75pt" alt="" type="#_x0000_t75"><imagedata o:href="http://www.cnblogs.com/images/cnblogs_com/cn_wpf/p4.GIF" src="file:///C:%5CDOCUME~1%5Clidawei%5CLOCALS~1%5CTemp%5Cmsohtml1%5C01%5Cclip_image004.gif"></imagedata></shape>

<shape id="_x0000_i1057" style="WIDTH: 498.75pt; HEIGHT: 143.25pt" alt="" type="#_x0000_t75"><imagedata o:href="http://www.cnblogs.com/images/cnblogs_com/cn_wpf/p5.GIF" src="file:///C:%5CDOCUME~1%5Clidawei%5CLOCALS~1%5CTemp%5Cmsohtml1%5C01%5Cclip_image005.gif"></imagedata></shape>
<shape id="_x0000_i1058" style="WIDTH: 498.75pt; HEIGHT: 198.75pt" alt="" type="#_x0000_t75"><imagedata o:href="http://www.cnblogs.com/images/cnblogs_com/cn_wpf/p6.GIF" src="file:///C:%5CDOCUME~1%5Clidawei%5CLOCALS~1%5CTemp%5Cmsohtml1%5C01%5Cclip_image006.gif"></imagedata></shape>

（上述各字段含义，见联机文档对DBCC SHOW_STATISTICS的描述）

从上面的贴图可以看到，表中总行数为1w，采样行数为1w。provider_no值为21的只有1行，而值为500的行则有4824行。下面两张图是两条SQL的查询计划，我就不多嘴解释了。
<shape id="_x0000_i1059" style="WIDTH: 380.25pt; HEIGHT: 153.75pt" alt="" type="#_x0000_t75"><imagedata o:href="http://www.cnblogs.com/images/cnblogs_com/cn_wpf/p7.GIF" src="file:///C:%5CDOCUME~1%5Clidawei%5CLOCALS~1%5CTemp%5Cmsohtml1%5C01%5Cclip_image007.gif"></imagedata></shape>

<shape id="_x0000_i1060" style="WIDTH: 380.25pt; HEIGHT: 153.75pt" alt="" type="#_x0000_t75"><imagedata o:href="http://www.cnblogs.com/images/cnblogs_com/cn_wpf/p8.GIF" src="file:///C:%5CDOCUME~1%5Clidawei%5CLOCALS~1%5CTemp%5Cmsohtml1%5C01%5Cclip_image008.gif"></imagedata></shape>

那么问题来了：

我们知道，SQLServer会缓存查询计划，假如有这么一个存储过程：

create proc myproc

(

@pno int

)

select * from charge where provider_no = @pno

第一次我们传进来一个21，OK，它会缓存该存储过程的执行计划为nonclustered index seek那个。后来我们又传进来一个500，完蛋了，服务器发现它有一个myproc的缓存，so，又通过nonclustered index seek执行，接着你的同伙看到你的查询花费了巨量的IO，于是，你被鄙视了。

这说明了啥？说明如果你的查询选择性变动剧烈，你应该告诉SQLServer不要缓存查询计划，每次都应该重新评估、编译。实现方法很简单，查询的尾巴上加一个option（recompile）好了。而且SQL2k5还有一个nb的feature，可以每次只重新编译存储过程的一部分（当然，你也可以选择重新编译整个存储过程，这取决于你的需求。详见联机文档。）

=======彪悍的分割线================================

后面blog会提到索引优化。其实百敬同学那本《SQL性能调校》这方面讲的不少了。那本书唯一的缺憾就是某些规则在SQL2k5中不适合。我想我会尽力都写出来。

写有效率的SQL查询（III）

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

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上创建索引。