如何使 SQL Server高效 --T-SQL

如何使 SQL Server高效 --T-SQL（ITPUT 讨论汇总）

2、      您认为在T-SQL编写（包括存储过程、函数和视图）上，哪些因素会影响SQL Server效率?

讨论汇总——索引使用

l  没有索引或者没有用到索引、I/O吞吐量小、没有创建计算列导致查询不优化、锁或者死锁、查询语句不够优化等

l  对大表而言。那些不走索引的语句，错误scan的语句，还有那些强制使用HINT的语句，可能会因为时间的推移，影响了SQL Server对最优计划的生成

l  避免无法使用索引的过滤条件。如 WHERE dbo.ufn.Test(ID)=1

l  确保存在适合查询的索引

l  根据查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当（最好是使用默认值0）。

l  如果是使用like进行查询的话，简单的使用index是不行的，但是全文索引，耗空间。 like 'a%' 使用索引 like '%a' 不使用索引用 like '%a%' 查询时，查询耗时和字段值总长度成正比,所以不能用CHAR类型，而是VARCHAR。对于字段的值很长的建全文索引

讨论汇总——游标：

l  游标的频繁 "开 / 关", 既消耗资源, 同时会给相关的表加"锁"

l  可以通过集合运算的方式去处理数据的情况下，避免使用游标

l  存储过程通过游标的方式，将函数的功能放在了游标的计算中，这样速度有了一些提高

l  考虑将游标用其他方式改写（需要验证方法是否有效，无法保证性能的改写，可能比直接使用游标更糟糕），或确保游标高效

l  个人补充：

a）  对于必须逐行处理的情况，建议直接使用游标，如果要避免对构成游标来源数据的影响，可以考虑使用静态游标。对于可以使用集合处理模式替代洲标的情况，也要考虑替代之后的查询复杂性和涉及的数据量，如果这个修改产生的查询非常复杂或涉及大量数据，那么用游标可能可以得到更佳或者更稳定的效果，建议通过测试验证决定最终的方案

b）  注意游标类型的选择

c）  经常使用游标片大量数据量，考虑适当调整cursor threshold选项（异步生成控制）

讨论汇总——临时表与表变量

l  表变量和临时表的使用要有规定，大小也要有个估计

l  临时表和内存表要分别对待

l  个人补充：表变量不受事务控制，其数据写入效率比临时表高；但SQL Server不为表变量生成统计信息，这意味着如果表变量参与查询的话，查询优化器无法评估表变量的数据，从而导致无法有效评估查询方案成本，所以，如果数据需要参与查询的话，始终建议使用临时表（尤其是在复杂查询中，这个非常重要）。另外注意的一点，表变量并不确保只在内存中存在，当数据量大时，其数据仍然会写入tempdb

讨论汇总——综合

l  存储过程、视图、函数的一个好处是查询计划重用（预先编译)），但还是要注意会导致重编译的操作，例如存储过程中引用和临时表、视图结构更改等

l  使用不合适的数据类型, 如与表字段不一致, 或发生隐式转换 (字符转数字)

l  SET NOCOUNT ON , 避免DML操作不必要的网络传输

l  最典型的函数加于字段上再判断。不正确的实现逻辑将导致性能问题。。当然，这本不属于DB平台的事儿了，是实现者的事儿

l  编写SQL语句时，在保证完成需要情况下尽量保证SQL简单易读，导致效率抵消的原因有：

a）  过大的事务操作导致事务之间严重阻塞

b）  返回过多无用数据引发磁盘和网络压力

c）  过多的非集合化操作，如在返回列频繁调用某个函数

d）  查询中使用负逻辑

e）  不必要的排序,，查询不需要的数据

f）  冗余的判断逻辑,，多层视图的嵌套引用.

l  检查不良的SQL，考虑其写法是否还有可优化内容

a）  检查子查询。考虑SQL子查询是否可以用简单连接的方式进行重新书写

b）  考虑数据库的优化器

c）  尽量避免大事务操作, 提高系统并发能力

d）  尽量避免向客户端返回大数据量, 如采用分页

e）  尽量避免游标使用进行大规模数据的连接和遍历

l  数据库引擎的本身的优化。每个数据库的查询引擎也是在不断进化当中的，有些以前高效的语句现在显得落伍了

l  .数据库设计优化：在针对不同主题，不同分析方向的时候数据本身的设计结构也会使得SQL语句的写法有所不同

l  70%性能问题出自不良的SQL语句。条件不符合SAR规范，游标滥用，事务过长，复杂业务逻辑实现追求功能，不考虑性能等等

l  避免大表的distinct

l  T-SQL编写要对复杂度进行规定，不能无限制的嵌套或者Union等等。T-SQL中检索数据规模也需要估计

l  在查询Select语句中用Where字句限制返回的行数,避免表扫描,如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表,后果严重。

l  存储过程要设计合理，当然提示数据库效率，主要在表的结构和使用合理的查询方法

l  视图的数据要放在内存中执行，这样才能提升效率，但是要加内存

l  join的時候要命中index，少用多層嵌套，可以考慮用臨時表

l  设计SQL后，应使用explain命令检查SQL，看是否使用到索引，是否存在filesort,重点检查检索的行数（rows）是否太大。一般来说.：

a）  rows<1000，是在可接受的范围内的。

b）  ows在1000~1w之间，在密集访问时可能导致性能问题，但如果不是太频繁的访问（频率低于1分钟一次），又难再优化的话，可以接受，但需要注意观察

c）  rows大于1万时，应慎重考虑SQL的设计，优化SQL，优化db，一般来说不允许频繁运行（频率低于1小时一次）

d）  rows达到10w级别时，坚决不能做为实时运行的SQL。但导数据场合除外，但导数据必须控制好时间，频度

l  在设计SQL，尤其是稍微复杂的SQL时，一定要在测试环境甚至是实际环境上预先进行explain

l  ＳＱＬ语句避免不符合ＳＡＲＳ条件的

l  游标和触发器尽量避免使用

l  避免长事务，避免出现阻塞和死锁　

l  复杂逻辑可以多次实现

l  动态拼接SQL语句是影响SQLServer效率的一个方面

l  存储过程中多表的连接查询也是影响SQL Server效率的一个方面，当然是许多数据库都具备的毛病，大概是CBO生成执行计划不佳的一个问题，迫不得已多使用临时表

l  应该采用函数或存储过程代替复杂的视图，性能更好

个人补充：使用自定义函数的时候要注意评估。返回单个值的函数最好能够保障是确定性函数；查询优化器不会为多语句表值函数创建统计信息，所以如果多语句表值函数是用于关联查询的话，可能会产生较差的查询方案

l  SQL Server没有oracle的非阻塞读，不光是写/写，读/写也会有冲突，应该劲量避免，比如：使用短小事务，合理设置并使用索引，如果可以的话做读写分离；影响SQL瓶颈的因素有很多，包括内存不足，硬件不行，或者没有足够的内存供SQL Server 使用，缺少有用的索引等，网络通讯不好，磁盘配置了，如tempdb 的配置，是否为查询优化器提供了优化复杂查询的最有利条件

个人补充：读写冲突，可以通过控制事务隔离级别（或者相关的表提示）来控制

l  .数据的量级：在数据量低大的情况下高效率的SQL语句在数据量高的情况下就不能用了，反之亦然

个人补充：T-SQL执行包括编译（查询方案评估选择）和执行两个步骤，在查询方案选择的步骤，查询优化器可以根据统计信息、对象结构评估出有效的查询方案，当然，如果查询过于复杂，或者是查询成本评估所需要的信息不准确，评估的结果也会存在误差，所以大部分情况下不会出现需要根据数据量随时调整查询的情况

l  T-SQL方面SQL Server缺乏Oracle完备的分析函数支持功能，也缺乏一些行列转换、树形目录结构方面的函数，虽然可以通过递归cte、xml等函数解决

个人补充：内置函数的多少无法对比，每个数据库都有自己的一些考虑，但常用的基本上是不缺的，对于需要但确实没有的，可以考虑自己写CLR函数。行列转换有 PIVOT 和 UNPIVOT；层次结构有 hierarchyid 数据类型和对应的一些函数

个人补充

l  复杂查询是需要特别注意控制的。越复杂的查询，意味着可选择的查询方案越多，查询评估所涉及的方面越广，这意味着查询优化器评估查询方案的成本越高，涉及的因素多，意味着评估的方案准确性越低。所以过于复杂的查询，往意味着性能很难保障。了解SQL语句的处理过程，有助于理解这个问题（在联机帮助中搜索“SQL语句处理”）可以得到这方面的说明。另外，要特别注意两种复杂查询：

a）  很多个CTE定义组成的查询。单看每个CTE定义，都很简单，但最终组合起来的，很可能是一个很复杂的查询，可以通过查看执行计划来确定。特别需要注意的是，通过CTE定义将执行步骤写得再清晰和一目了然，最终执行也基本上不是按照写的步骤去做的

b）  视图（包括表值函数）嵌套导致的复杂查询。查询涉及的每个视图可能很简单，但一层层嵌套组合起来，可能最终也形成一个复杂查询，这个也可以勇冠看执行计划确定。（尽量避免在数据库中使用模块化的设计思想）

l  注意实现方法的控制。比较常见的有下面这些：

a）  使用 JOIN 代替 EXISTS（包括IN）。这种很多时候容易出问题，特别是无法通过结构定义确定JOIN是否会产生一对多之类的情况下。EXISTS只需要考虑是否存在，JOIN还需要考虑数据是一对一，还是一对多或多对多，这两者导致的查询成本评估是不一样的

b）  过多的 IN(值的列表)。这种基本上会被判断为一堆的OR条件，而且会需要评估每个值，很多时候查询成本是比较高的。可以考虑把值先放入临时表，再IN 临时表

c）  减少OR条件。不少的情况下，将OR条件改成多个 UNIONALL的结果会更高效一些

d）  注意条件和JOIN中的数据类型转换。尽可能使用显式的数据类型转换，并将转换的目标放在常量/变量/参数或者数据少的一方。隐式（自动）的数据类型转换是根据类型优先级确定转换目标的，如果转换操作发生在数据量大的一方，那查询就会比较差了

 

讨论帖

之前的讨论话题：

1、 您认为在设计SQL Server对象时，主要会考虑哪些因素来避免出现性能问题?

 后续讨论话题：

3、 在设计数据库操作程序上，您认为应该注意哪些事项，以确保能够有效地使用数据库?

4、 在您的SQL Server使用过程中，有哪些令您非常困惑的性能问题 ?