SQL语句调优(一) [MS SQLServer]

语句调优是和数据库打交道的必备基本功之一,也是相对来讲比较“单纯”和“有意思”的话题。 


单纯:基本上可以排除操作系统,网络,应用程序等因素。

有意思:语句的编译和运行背后有一整套数据访问和管理理论,而且很多东西是关系型数据库,所共有的,可以拿他当一门手艺来研究。 


当你面对一个“有问题”的语句时,应该怎麽分析他的问题所在,最后达到优化语句的目的呢? 首先要想一想,“有问题”的语句“问题”究竟在哪里?也就是说,你要优化的目标是什么? 常见的需求有:


 1、语句需要访问大量的数据页面,造成内存压力,磁盘繁忙等。 

对于这类问题,所关心的是为什麽语句执行要访问这麽多数据页面?是语句的结果集本身就比较大;还是SQL没有办法有效地seek,而是像大炮打苍蝇一样从大量的原始数据里找出需要返回的结果;还是因为数据页面有很多碎片,导致SQL读了很多页面,但是每个页面里的数据量不多。这些都是考虑因素。


 2、在内存没有压力的前提下(语句所访问的页面都事先缓存在内存里),语句运行的时间还是很长。 

语句的运行时间一般会主要花在这3步上:语句编译、语句执行、结果集返回。结果集返回的速度和SQL自身没有太大关系,所以一般不会在语句调优的时候来考虑。语句调优时要搞清楚编译和执行各花了多少时间,哪一段时间有优化空间,以及怎样优化。 


3、单个语句执行时间可以接受,但是其CPU使用量比较大,多个语句并发执行会造成SQLCPU高。 

有些语句单句执行可能一两秒就能执行完毕,对用户来讲还在可接受范围。但是他的CPU时间可能也是在一两秒,甚至更长。如果同时有十几个用户在跑同样的语句,SQL就会满负荷了。语句的CPU时间也分编译阶段和执行阶段。优化者要先搞清楚这两个阶段各用了多少CPU资源。然后,再看有没有优化降低CPU使用量的可能。 


4、语句单独执行看不出有大问题,但是并发执行就容易遇到阻塞或死锁。 

这个也是语句调优的一个重要人物。很多语句执行速度很快,使用资源量SQL也能够承受但是就是容易引起阻塞或死锁。这种现象往往是由于应用在某个表或者索引上的并发度特别高,而问题语句申请的锁数量比较大造成的。当然有时候可以使用Query Hint(查询提示 WITH(ROWLOCK))来强制SQL使用粒度比较小的锁。但是这往往不是最好的解决办法也可能解决不了问题。最理想的方法,是通过调整语句运行的方式,引导他申请尽可能少的、粒度尽可能小的锁。这里也要做语句调优。 


做这些调优的时候,首先要对目标语句做估算,看看他优化的空间有多大。有些语句本身比较简单可以通过调整索引的方法迅速提高性能,这样的调优是很值得做的。有些语句非常复杂,或者返回的结果集很大,通过调整SQL这里的设置,提供性能的空间往往不大。这时候要考虑语句本身是不是能够换一种方法实现。很多时候改一下语句,把一条大的语句拆分为若干条小的语句,或者去掉一些不必要的逻辑,会达到事半功倍的效果。


 在谈论如何做语句调优的具体方法之前,必须先来介绍一些最必需的背景知识。不了解这些知识,做语句调优就只能基本靠猜。所需要的背景知识主要包括:

(1)理解索引 。

(2)统计信息 。

(3)编译和重编译 。

(4)读懂语句执行计划。