数据库设计方案经典推荐

数据库设计方案经典推荐 

http://blog.csdn.net/JavaProgramers/archive/2008/01/18/2051935.aspx

写有效率的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

)

as

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

 

note: from envykok

col1, col2建索引，所以在索引页中的顺序是按 col1, col2 排的（col1 顺序，col2为次顺序)，如果查询有'col2 >XXX' , 那么次索引没有多大用处

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

 

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

)

as

select * from charge where charge_no > @id

 

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

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

 

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

alter proc test

(

    @id int

)

as

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

)

as

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

as

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)' 会重新编译:) 。
程序代码如下：

1 //
2 SqlCommand cmd =  new  SqlCommand("select * from myt where data = @d", conn);
3 cmd.Parameters.Add( new  SqlParameter("@d", "1234567890"));
4 cmd.ExecuteNonQuery();
5
6 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 行标记了缓存的查询计划。
=======

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

 1 //
 2 SqlCommand cmd =  new  SqlCommand("select * from myt where data = @d", con);
 3 cmd.Parameters.Add( new  SqlParameter("@d", "1234567890"));
 4 cmd.ExecuteNonQuery();
 5
 6 cmd =  new  SqlCommand("select * from myt where data = @d", con);
 7 cmd.Parameters.Add( new  SqlParameter("@d", "123"));
 8 cmd.ExecuteNonQuery();
 9
10 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 选项的原因。