关闭

如何使 SQL Server高效 --T-SQL(ITPUT 讨论汇总)

8446人阅读 评论(1) 收藏 举报
分类:

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

讨论汇总——索引使用

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

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

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

确保存在适合查询的索引

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

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

讨论汇总——游标:

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

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

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

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

个人补充

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

b)  注意游标类型的选择

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

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

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

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

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

讨论汇总——综合

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

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

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

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

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

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

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

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

d)  查询中使用负逻辑

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

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

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

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

b)  考虑数据库的优化器

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

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

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

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

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

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

避免大表的distinct

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

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

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

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

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

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

a)  rows<1000,是在可接受的范围内的。

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

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

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

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

SQL语句避免不符合SARS条件的

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

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

复杂逻辑可以多次实现

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

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

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

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

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

个人补充:读写冲突,可以通过控制事务隔离级别(或者相关的表提示)来控制

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

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

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

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

个人补充

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

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

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

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

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

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

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

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

 
之前的讨论话题:
 后续讨论话题:
2
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1279608次
    • 积分:14879
    • 等级:
    • 排名:第813名
    • 原创:178篇
    • 转载:9篇
    • 译文:0篇
    • 评论:881条
    最新评论