写有效率的SQL查询

最新推荐文章于 2022-09-02 11:01:08 发布

envykok

最新推荐文章于 2022-09-02 11:01:08 发布

阅读量303

点赞数

分类专栏：待拜读文章标签： sql sqlserver 存储 include io statistics

待拜读专栏收录该内容

5 篇文章 0 订阅

订阅专栏

[转载]http://www.cnblogs.com/cn_wpf/ ，非常感谢Nineteen@newsmth 的分分享。

写有效率的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 操作，但实际情况是：

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 就是根据它来估算不同查询计划的优劣。

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

以下是示例表的表结构：

各位可以注意到，该表上有一个 identity 字段 charge_no ，聚集索引就创建在它上面。有两个非聚集索引 indx_category_no ， indx_provider_no ，我们重点关注 indx_provider_no 。现在来看看 provider_no 字段的统计信息（有点长，我前边粘一部分，后边粘一部分）：

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

从上面的贴图可以看到，表中总行数为 1w ，采样行数为 1w 。 provider_no 值为 21 的只有 1 行，而值为 500 的行则有 4824 行。下面两张图是两条 SQL 的查询计划，我就不多嘴解释了。

那么问题来了：

我们知道， 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 上创建索引。

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

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的用户在某段时间内的记录.

写有效率的SQL查询（IV）

本文主要介绍写SQL的另外两个误区：
1、存储过程中使用局部变量而不使用参数变量（就是存储过程输入参数）做where条件
2、查询条件中类型不匹配

这两种错误都是非常非常容易犯且非常发指的错误，特别是2，太多次见过了。

一、关于存储过程使用局部变量，我们举例说明。

有这么一张表

存储过程：

create proc test

(

@id int

)

select * from charge where charge_no > @id

那么 exec test 99998 ，执行计划为：

请注意上图中的估计行数。

但是如果把存储过程修改为：

alter proc test

(

@id int

)

declare @local int

set @local = @id

select * from charge where charge_no > @local

再次观察 exec test 99998 的查询计划：

请再次注意估计行数，现在是 30000 了。而我们都知道，修改前存储过程和修改后的输出结果集都没有任何变化，为 2 。

由于 charge_no 是聚集索引，而我们的查询条件是 where charge_no > XXX ，不论 SQLServer 估计行数有多大，伊都会使用相同的 clustered index seek 查找到 XXX ，然后直接顺序遍历基础表剩下的叶节点。

但是，若 charge_no 是非聚集索引，由于估计结果集行数大小由两行变成了总行数的百分之三十（使用局部变量做查询条件，这种 where AAA > BBB ， SQLServer 无法估计结果集大小，所以它使用默认估计值： 30% ）， nonclustered index seek 变成 nonclustered index scan(SQL2k5 中若不是覆盖查询，会是 clustered index scan) ，这是巨大的性能损耗，必须避免。

在这里顺带着再次强调另外一个问题：缓存的查询计划可能会强力的伤害性能。为了更详细的说明它，我们把存储过程 test 改为：

alter proc test

(

@id int

)

select * from charge where charge_no > @id

然后看看执行计划 exec test 99998( 见上面的图，不重复贴了 ) 。再来看看 exec test 1 的执行计划：

我们可以注意到，尽管真实的结果集变动非常巨大，但是查询计划还是完全不变， SQLServer 在使用缓存。这种情况在使用聚集索引时不会让查询变得更糟，但是使用非聚集索引就会差上十万八千里， IO 开销会差上 n 个数量级（ n 取决于真实的结果集）。

所以如果你的查询由于输入参数的不同，选择性变动剧烈，最好在创建存储过程的时候使用 WITH RECOMPILE 选项。即：

create proc test

(

@id int

)

with recompile

select * from charge where charge_no > @id

OK ，但并不是所有的情况下在查询条件中使用局部变量都有问题。如果查询条件中涉及的索引， SQLServer 发现伊的分布密度非常小（比如一个 identity(1,1) 列或者一个unique），那么在 where AAA = XXX 的情况下， SQLServer 仍然会认为结果集相对总行数很小，而选择 index seek 类的查询计划。

二、查询条件中的类型不匹配。

所谓的类型不匹配是说，查询条件 where AAA = @var ，列 AAA 的定义和 @var 不同。例如， AAA 是 varchar(64) ， @var 是 bigint 。这种情况下，非常有可能让本来是 index seek 的运算变成 index scan，在大数据量表中，性能差距会非常明显。

从我的经验来看，并不是所有的隐式转换都会带来这样的问题。但是这样的问题大量的存在，并且在分析性能瓶颈、做索引调优时，会给你带来极大的困扰。必须分析缓存中查询计划对应的原始语句，看那玩意属于慢性自杀。

我们写 SQL 一般都会类型匹配，但是通过应用程序就非常容易出错。比方说一个表有个 MobileNo 字段用来存储手机号码，表中是 varchar 。但是应用程序你这么写：

SqlConnection conn = ...;

SqlCommand cmd = new SqlCommand ("select * from Users where MobileNo = @mo" , conn);

cmd.Parameters.Add(new SqlParameter ("@mo" , 13511223344));

SqlDataReader reader = cmd.ExecuteReader();

//....

那么你挂了……

到现在为止，我没有看到任何资料说哪种形式的隐式转换会让 SQL 无法判定结果集大小或者可以不去爬整棵索引树。所以我的建议是，使用最强类型去匹配查询列。查询列是啥，就写啥。是 varchar(64) 就别简单的 new SqlParamerer(“@mo”, “13511223344”) ，要精确指定它的类型、长度。这样做有另外一个好处，偶将在下一篇 blog ——比较拼 SQL 、参数化 SQL 、使用存储过程执行 DB 指令的优劣时说明(btw:我相信那是一个好坑:))。

==加个总结=====================================
1、存储过程中，能不使用本地变量就不使用，尽可能的使用参数变量（也就是输入参数）。如果不得不使用本地变量，那也得只用在分布密度足够小的索引上使用。
2、写查询条件时，应该尽可能的使类型匹配。使用诸如SqlCommand执行DB指令时，一定要让输入参数从类型到长度严格匹配相应的列。尽管DB端不是所有的隐式转换都会引起性能损耗。
================================================

稍微提一句，在msdn中SQL Server Database Engine>Troubleshooting the Database Engine > Troubleshooting Queries下有一篇《Troubleshooting Poor Query Performance: Constant Folding and Expression Evaluation During Cardinality Estimation》，尽管说的粗糙无比外带模棱两可，但还是推荐一读。

写有效率的SQL查询（V）

先站在应用程序的角度说说它们的不同。

1、直接拼 SQL

就像大家了解的那样，直接拼 SQL 带来了 SQL 注入攻击，带来了拼时些许的性能损失，但是拼不用添加 SqlParameter ，会少写很多代码——很多人喜欢直接拼，也许就因为这点。这种做法会把你拼好的 SQL 原样直接发送到 DB 服务器去执行。（注意类似 ”exec yourproc ‘param1’, 12” 的语句不在此范畴，这是调用存储过程的一种方式）

2、参数化 SQL

所谓的“参数化 SQL ”就是在应用程序侧设置 SqlCommand.CommandText 的时候使用参数（如： @param1 ），然后通过 SqlCommand.Parameters.Add 来设置这些参数的值。这种做法会把你准备好的命令通过 sp_executesql 系统存储过程来执行。通过参数化 SQL ，和直接拼 SQL 相比，最直接的好处就是没有 SQL 注入攻击了。

3、调用存储过程

直接调用存储过程其实和参数化 SQL 非常相似。唯一的本质不同在于你发送到 DB 服务器的指令不再是 sp_executesql ，而是直接的存储过程调用而已。

很多人非常非常厌恶在应用程序中使用存储过程，而宁愿使用拼 SQL 或者参数化 SQL ，理由是它们提供了更好的灵活性——这个理由其实非常非常的发指（俺现在喜欢上这个词了）。

现在做设计，一般都是从上到下来，重心都在业务逻辑上。传说中的领域模型设计完，测试用例都通过之后，才会考虑数据持久化方式。数据持久化是系统的一部分，但绝对不是最重要的部分，设计应该围绕业务逻辑开展，持久化应该仅仅是个附件。至少，高层应用应该尽可能的不关心处于最底层的物理存储结构（如：表）和数据持久、反持久方式（是拼 SQL 还是存储过程），所以用不用存储过程根本不重要。很多人害怕存储过程，其实是害怕存储过程中包括业务逻辑——真实情况是，如果存储过程中包含了业务逻辑，那一定最初需求分析不够导致用例提取不足，导致测试用例覆盖不够，导致领域模型设计不充分，要不就是偷懒。

=====

站在 DB 角度讨论它们的不同，主要从 cpu 、内存方面来考虑，其他诸如安全性， msdn 上都有， google 也能拿到一堆资料，不再赘述。

首先是查询计划。

SQL 编译完一条 SQL 之后，会把它缓存起来（可以通过 sys.syscacheobjects 系统视图查看），以后再有相同的查询过来（注意 sys.syscacheobjects 视图中的 sql 字段，和它存储的东西完全一样才能称为“相同的查询”），会直接使用缓存，而不再重新编译。

Ø 存储过程，伊只编译一遍（如果没有指定 with recompile 选项的话，如果指定了，根本就不会生成计划缓存）。

Ø 参数化 SQL ，和存储过程基本一样，只要是相同的查询，也都是只编译一次，以后重用（当然，指定了 option(recompile) 的除外）。这里不得不提 .NET SqlClient 组件的一个龌龊：如果你的参数中包含 varchar 或者 char 类型的参数，你在 Parameters.Add 的时候又没有指定长度，它都会根据你实际传入的字符串长度 ( 假设是 n) 给你重新定义成 nvarchar(n) 。如： select * from mytable where col1 = @p1 ，你设置 @p1 为 ’123456’ ，实际传到 sql 这边的命令是： exec sp_executesql N'select * from mytable where col1 = @p1',N'@p1 nvarchar(6)',@p1=N'123456' 。这样，系统缓存中实际存储的 sql 是： (@p1 nvarchar(6))select * from mytable where col1 = @p1 。看到了吧？如果你的输入参数变动比较多，那么看起来同样的一条语句，会被编译很多次，在缓存中存储很多份。 cpu 和内存都浪费了。这也是在《写有效率的 SQL 查询 IV 》中建议的使用最强类型参数匹配的原因之一。

Ø 拼 SQL 。到这里不说大家也猜的出来，拼 SQL 要浪费大量的 cpu 进行编译，浪费大量缓存空间来存储只用一次的查询计划。

服务器的物理内存有限， SQLServer 的缓存空间也有限。有限的空间应该被充分利用。通过性能计数器 SQL Server:Buffer Manager/Buffer Cache hit ratio 来观察缓存命中率。如果它小于百分之 90 ，你就得研究研究了。关注一把诸如 sys.dm_os_memory_cache_counters 、 sys.dm_os_memory_cache_entries 、 sys.dm_os_memory_cache_hash_tables 、 sys.syscacheobjects 等视图，基本可以确定问题出在哪儿。

cpu 方面需要关注三个性能计数器： SQLServer:SQL Statistics/Batch Requests/Sec 、 SQLServer:SQL Statistics/ SQLCompilations/sec 、 SQLServer:SQL Statistics/ SQL Re-Compilations/sec 。如果 compilations 数目超过 batch 请求数目的百分之 10 ，或者 recompilations 数目超过 compilations 数目的百分之 10 ，那基本可以说明 cpu 消耗了太多在编译查询计划上面。

    最后，我的建议是：
    1、DB中的所有操作都尽可能的使用存储过程，哪怕只是一句简单的select。
    2、鄙视拼SQL。

btw:MSDN中对拼SQL称为"ad hoc"，呵呵。

==================
补充一点，说明一下N'@p1 nvarchar(6)'换成N'@p1 nvarchar(30)'会重新编译:)。
程序代码如下：

SqlCommand cmd = new SqlCommand( " select * from myt where data = @d " , conn);
3

cmd.Parameters.Add( new SqlParameter( " @d " , " 1234567890 " ));
4

cmd.ExecuteNonQuery();
5

cmd = new SqlCommand( " select * from myt where data = @d " , conn);
7

cmd.Parameters.Add( new SqlParameter( " @d " , " 123 " ));
8

cmd.ExecuteNonQuery();
9

执行完这段程序，可以观察观察sys.syscacheobjects:

上图中的5、6行标记了缓存的查询计划。
=======

另外，再来说个更应该注意的地方：

SqlCommand cmd = new SqlCommand( " select * from myt where data = @d " , con);
3

cmd.Parameters.Add( new SqlParameter( " @d " , " 1234567890 " ));
4

cmd.ExecuteNonQuery();
5

cmd = new SqlCommand( " select * from myt where data = @d " , con);
7

cmd.Parameters.Add( new SqlParameter( " @d " , " 123 " ));
8

cmd.ExecuteNonQuery();
9

cmd = new SqlCommand( " select * from myt where data = @a " , con);
11

cmd.Parameters.Add( new SqlParameter( " @a " , " 123 " ));
12

cmd.ExecuteNonQuery();
13

注意，上述代码中最后一次操作我把@d参数重命名成了@a，然后再来看看sys.syscacheobjects里面有啥:

注意第六行。

================
稍微提一下“简单参数化”（SQL2k中称为自动参数化）和“强制参数化”。在简单参数化下，SQL会试图参数化你的语句，以减少查询计划编译和重编译，但是可以被参数化的语句非常有限。这个东东可以通过一条简单的insert语句测试到，偶就不贴图了。简单参数化是SQLServer的默认行为。

强制参数化可以通过设置库的属性PARAMETERIZATION为FORCED实现。强制参数化会在很大程度上参数化你的语句。但是它有很多的限制（见MSDN）。

但是要注意，由于查询计划不会有两种和两种以上的副本，所以SQL可能会选择一个不合适的计划来执行你的查询。这也是偶一再的说，如果你的输入参数引起选择性剧烈变化，最好指定recompile选项的原因。

写有效率的SQL查询（VI）

我们先看 NestedLoop 和 MergeJoin 的算法（以下为引用，见 RicCC 的《通往性能优化的天堂 - 地狱 JOIN 方法说明》 ):
==================================
NestedLoop:
   foreach rowA in tableA where tableA.col2=?
    {
    search rowsB from tableB where tableB.col1=rowA.col1 and tableB.col2=? ;
    if(rowsB.Count<=0)
        discard rowA ;
    else
        output rowA and rowsB ;
    }
MergeJoin:
两个表都按照关联字段排序好之后， merge join 操作从每个表取一条记录开始匹配，如果符合关联条件，则放入结果集中；否则，将关联字段值较小的记录抛弃，从这条记录对应的表中取下一条记录继续进行匹配，直到整个循环结束。
==================================

我们通过最简单的情况来计算 NestedLoop 和 MergeJoin 的消耗：
两张表 A 、 B ，分别有 m 、 n 行数据（ m < n ），占用基础表物理存储空间分别为 a 、 b 页，聚集索引树非叶节点都是两层（一层根节点，一层中间级节点）， A 、 B 的聚集索引建在 A.col1 、 B.col1 上。一条查询语句：
select A.col1, B.col2 from A inner join B where A.col1 = B.col1 。

执行 NestedLoop 操作：
A 作为 outer input ， B 作为 inner input 时： A 带来的 IO 为 a ；每次通过 clustered index seek 执行内部循环，花费 3( 一个根节点、一个中间集结点、一个叶节点。当然也可能直接从根节点就拿到要的数据，我们只考虑最坏的情况），这样执行整个嵌套循环过程消耗 IO 为 a + 3*m 。如果 B 作为 inner input ， A 作为 outer input 分析类似。

执行 MergeJoin :
MergeJoin 要把 A 、 B 两张表做个 Scan ，然后进行 Merge 操作。所以 A 、 B 分别带来 IO 为 a + b 就是总的逻辑 IO 开销。

从上述分析来看，若 a + 3*m << a + b ，即 3*m << b ，那么 NestedLoop 性能是极佳的。当然，我们比较 A 表的行和 B 表所占数据页大小看上去有点夸张，但是量化分析确实如此。在这里，我们没有计算 NestedLoop 和 MergeJoin 本身的 cpu 计算开销，特别是后者，这部分并不能完全忽略，但是也来得有限。

OK ，现在我们试图执行实际的语句验证我们的观点，看看能发现什么。

我有两张表，一张表 charge ，聚集索引在 charge_no 上，它是个 int identity(1,1) ，共 10 万行，数据页 582 张，聚集索引非叶节点 2 层。一张表 A ，聚集索引在 col1 上（唯一），共 999 行，数据页 2 张，聚集索引两层。 min(A.col1) = min(charge.charge_no) 、 Max(A.col1) < max(charge.charge_no) 。

我们在 set statistics io on 和 set statistics time on 之后，执行语句：

select A. col1, charge. member_no from A inner join charge

on A. col1 = charge. charge_no

option ( loop join) -– 执行 NestedLoop

select A. col1, charge. member_no from A inner join charge

on A. col1 = charge. charge_no

option ( merge join)-- 执行 MergeJoin 。

结果集都是 999 行，而且我们看到消息窗口中输出为：

（图 1 ）

从上图中我们注意到几点比较和最初分析不同的地方：

1. Nested Loop 时，表 A 的逻辑读是 4 ，而不是预计中的表 A 数据页大小 2 ； charge 逻辑读 2096 ，而不是预计中的 3 × 999 。

2. Merge Join 时，表 Charge 的逻辑读只有 8 。

对 1 来说，表 A 的逻辑读是 4 是因为 clustered index scan 需要从聚集索引树根节点开始去找最开始的那张数据页，表 A 的聚集索引树深度为 2 ，所以多了两个非页节点的 IO 。不是3×999是因为有些记录（设为n）直接从根节点就能找到，也就是说有些是2×n + （999-n)* 3

对 2 来说， MergeJoin 时，表 Charge 并不是从头到尾扫描，而是从 A 表的最大最小值圈定的范围之内进行扫描，所以实际上它只读取了 6 张数据页。

OK , 为了验证对 2 的解释，我们在表 A 中插入一条 col1 > max(charge.charge_no) 的记录，然后执行：

select A. col1, charge. member_no from A inner join charge

on A. col1 = charge. charge_no

option ( merge join)-- 执行 MergeJoin 。

（图 2 ）

现在 charge 逻辑读成了 582 + 2 = 584 ，验证了我们的想法。

那么如果 min(A.col1) > min(charge.charge_no) ， max(A.col1) = max(charge.charge_no) 时 SQLServer 会不会聪明到再次选择一个较小的扫描范围呢？很遗憾，不会 -_-…. 不知道 MS 这里基于什么考虑。

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

我们现在回到图 1 ，实际上我们从图 1 中还能发现 SQL 的分析编译占用时间相对执行占用时间不仅不能忽略，还占了很大比重，所以能避免编译、重编译，还是要尽可能的避免。

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

OK ，现在我们开始分析分析执行计划，看看 SQLServer 如何在不同的执行计划之间做选择。

我们首先把 A 表 truncate 掉，然后里面就填充一条数据， update statistics A 之后，看看执行计划：

（图 3 ： NestedLoop 的执行计划）

（图 4 ： MergeJoin 的执行计划）

我们把鼠标分别移到图 3 和图 4 中 A 表的 Clustered Index Scan 上，会看到完全一样的 tip ：

这个“ I/O 开销”就是两个逻辑 IO 的开销（就一条记录，自然是一个聚集索引根节点页，一个数据页，所以是 2 ）；估计行数为 1 ，很准确，我们就 1 行记录。

现在我们把鼠标分别移动到图 3 、图 4 中 charge 表的 Clustered Index Scan 上，看到的则略有不同

（图 5 ： NestedLoop ）（图 6 ： Merge Join ）

Nested Loop 中的开销评估看起来还算正常，运算符开销 = （估计 IO 开销 + 估计 CPU 开销）×估计行数。（注意， NestedLoop 中，大表是作为内存循环存在的，计算运算符开销别忘了乘上估计行数）。

但是 Merge Join 中我们发现“估计行数”很不正常，居然是总行数（相应的，估计 IO 开销和估计 CPU 开销自然都是全表扫描的开销，这个可以跟 select * from charge 的执行计划做个对比）。显然，执行计划中显示的和实际执行情况非常不同，实际情况按照我们上面的分析，应该就读取 3 张数据页，估计行数应该为 1 。误差是非常巨大的， 3IO 直接给估算成了 584IO 。翻了翻在 pk_charge 上的统计信息，采样行数 10w ，和总行数相同，再加上第二个结果集提供的信息，已经足够采取优化算法去评估查询计划。不知道 MS 为什么没有做。

好吧，我们假设执行计划的评估总是估算最坏的情况。由于 Merge Join 算法比较简单，后面我们只关注 NestedLoop.

我们首先给 A 表增加一行 ( 值为 2) ，然后再来分析执行计划。

（图 7 ： A 表NestedLoop） ( 图 8 ： charge 表NestedLoop )

我们从图 7 上可以看到， IO 开销没有增加， CPU 开销略微增加，这很容易理解， A 表只增加了一行，其占用索引页和数据页和原来一样。但是由于行数略有增加， cpu 消耗一定会略有增加。

奇怪的是图 8 显示的 charge 表上的 seek. 对比图 5 ，运算符开销并没有像我们预料的那样增加一倍，而是增加了 0.003412 – 0.003283 = 0.000129. 这个数值远小于 IO 开销。为了多对比一次，这次我们再往 A 表里面插入一条记录（值为 3 ），再来看看 charge 表上的运算：

（图 9 ， charge 表NestedLoop）

这次我们又发现，这次增加的消耗是 0.0035993 – 0.003412 = 0.0001873 ，仍然远远小于一次的 IO 开销。

好吧，那么我们假设执行计划估算算法认为，如果某一页缓存被读到 SQL Engine 中之后就不会再被重复读取。为了验证它，我们试试把 A 表连续地增加到 1000 行，然后看看执行计划：

（图 10 ， charge 表NestedLoop）

我们假设每次进行 clustered index seek 消耗的 cpu 是相同的，那么我们可以计算出来查询计划认为的 IO 共有：（运算符开销 – cpu 开销 *1000 ） / IO 开销 = 5.81984 。要知道 charge 表数据页总数为 582 ， 1000 行恰好是 100000 的百分之一， 1000 行恰好占用了 5.82 页……（提醒一把，这 1000 行是连续值）

OMG… 这次执行计划算法明显的比实际算法聪明。看上去像是， NestedLoop 在每次 Loop 时都会缓存本次 Loop 中读取的数据页，这样当下次 Loop 时，如果目标数据页已经读取过，就不再读取，而直接从 Engine 内存中取。

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

从上面的讨论可以看出，有时候执行计划挺聪明，有时候实际的执行又很聪明，总之，咱是不知道为啥微软不让执行计划和实际的执行一样聪明，或者一样愚蠢。这样，至少 SQL 引擎在评估查询计划的时候可以比较准确。

btw: 接着图 10 的例子，各位安达还可以自己去试试 insert 一条大于 max(charge.charge_no) 的记录到表 A 里，然后试试看看 charge 表运算符上有什么变化。

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

回到最初的主题，根据我们看到的SQL引擎实际执行看，只有 A 表行集远远小于 charge_no 的时候， SQLServer 为我们选择的 NestedLoop 才是非常高效的；为了保证更小的IO，当(B表索引树深度*A表行数>B表数据页+B表索引树深度）的时候，就可以考虑是否要指定MergeJoin。

值得一提的是，经过多次的实验， SQL 这样评估 MergeJoin 和 NestedLoop ，最后选择它认为更优的查询计划，居然多数情况下都是正确的……我是晕了，不知道你晕了没有。

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

刚才（22:00)本子待机了一次，然后再开机的时候我没办法重现SQLServer自己选择NestedLoop总是比MergeJoin的cpu占用时间短了。现在的情况是：SQLServer每次都错误的选择了NestedLoop，导致的结果是IO相差20 ～ 30倍，执行时间多了百分之50。
============================

俺也不知道有多少人读到了这里，呵呵。

So盼望有人可以解释以上这些东西。