谈谈SQL 语句的优化技术 (1)
一、引言
一个凸现在很多开发者或数据库管理员面前的问题是数据库系统的性能问题。性能不理想的系统中除了一部分是因为应用程序的负载确实超过了服务器的实际处理能力外,更多的是因为系统存在大量的SQL语句需要优化。本文通过一个例子讲述如何应用简化技术来优化SQL 语句，也讨论在系统设计阶段应该考虑的和语句性能密切相关的一些问题。
如果读者不知道应该优化数据库系统的哪些SQL语句，那么建议读者参考笔者的另外一篇文章，《应用Profiler优化SQL Server数据库系统》。本文不讨论索引，建议读者参考笔者的文章《应用索引技术优化SQL语句》，因为索引技术是优化SQL语句的重要部分。
二、简化SQL语句
1．简化的原因
SQL语句越简单越好。语句越复杂，其执行性能就越不可预知。这里先说一下SQL Server 的查询优化器。SQL Server 查询优化器是基于成本的优化器。查询优化器分析可能的执行计划并选择一个估计成本最低的计划。对简单的语句而言，查询优化器会很快找到一个高效的执行计划如trivial plan或quick plan来执行语句。这是很理想的。因为对简单的执行计划而言SQL Server几乎不用耗费多少资源在它的生成上面。因为简单的缘故，这样的计划几乎都是最优的执行方式。
对那些复杂语句，其可能有上千个不同的执行计划。在这个情况下，查询优化器不会分析所有可能的组合，而是使用复杂算法找到一个成本与理论上的最小值相当接近的执行计划。语句全面优化是非常耗费资源的。语句越复杂，SQL Server越有可能在寻找最优计划的中途停下来，直接使用已经比较过的较优的一个计划来执行语句。如果寻找最优执行计划的时间和语句执行的时间差不多，那还不如直接执行语句。所以SQL Server产生的执行计划未必就是最优的执行计划。基于查询优化器的这种特性，为了获得稳定的执行性能， SQL语句越简单越好。对复杂的SQL语句，要设法对之进行简化。
2．简化的手段
简化的手段多种多样。在系统规划阶段就必须考虑如何避免复杂查询。一个不好的设计会使你不得不在无数表之间进行多次交叉连接才能得到数据，这大大降低了性能。常见的简化规则如下：
1）不要有超过5个以上的表连接（JOIN）
2）考虑使用临时表或表变量存放中间结果。
3）少用子查询
4）视图嵌套不要过深
连接的表越多，其编译的时间和连接的开销也越大，性能越不好控制。最好是把连接拆开成较小的几个部分逐个顺序执行。优先执行那些能够大量减少结果的连接。拆分的好处不仅仅是减少SQL Server优化的时间，更使得SQL语句能够以你可以预测的方式和顺序执行。SQL Server的查询优化器不是十全十美的。使用这样的简化技术可以避免SQL Server优化器产生不是最优的执行计划。如果一定需要连接很多表才能得到数据，那么很可能意味着设计上的缺陷。
不要有过深的视图嵌套。我就曾经看到有个系统，建立了好多视图，视图里面是复杂的语句，然后在视图的上面再建立视图，系统的视图嵌套有四层之多。我并不是反对使用视图，只是视图越多，语句展开后就越复杂，优化起来就越难。一般视图嵌套不要超过2个为宜。
使用临时表或表变量存放中间结果的目标也是简化SQL语句，使得其执行顺序和执行方式得到控制。这个技术在某些时候会产生戏剧化的效果。
3．简化的例子
可以从分析SQL语句的执行计划开始进行简化。从执行计划中常可以发现非常有价值的信息。充分利用这些信息可以在优化的时候做到事半功倍。让我们看个例子。
我们有如下的SQL语句：
select t1.PID 'PR Number',rsdt 'Request date',per.ename 'Requestor',
ped.ename 'Deliver to',dest 'Destination', pcat.cdesc 'Category',
'Claimable'= Case Claim When '1' Then 'Yes' else 'No' end,
'NRE'= case nre WHEN '1' THEN 'Yes' else 'No' End,
'PMCal' = case PmCal when '1' then 'Yes' else 'No' End,
'Cap Reld' = case caprel WHEN '1' then 'Yes' else 'No' End,
'Type' = Pt.TDesc, 'Section' = PSec.SectDesc,
str(t1.prvalue,15,4) 'PR Value', d.vndid 'Vendor Code', t1.status, pes.ename 'PR is/with'
from PrMer t1
Left outer join P_view per on per.ecode = t1.reqid And per.CMpcode = t1.reqidCMpno
Left outer join P_view ped on ped.ecode = t1.dlyid And ped.CMpcode = t1.dlyidCMpno
Left outer join P_view pes on pes.ecode = t1.status And pes.CMpcode = t1.statusCMpno
Left outer join PRcg pcat on pcat.catid = t1.catid And pcat.catidCMpno = t1.catidCMpno
Left outer Join PRte Pt on Pt.Typeid = t1.Type
Left outer Join PRst PSec on PSec.SectPRCode = t1.BuRelated
left outer join PRdtl d on t1.PID = d.PID and t1.CMpno = d.CMpno and d.itmno = '1'
where
( t1.type = '1')
and
(
t1.reqid = '22101' and t1.reqidCMpno = 'P'
or (
t1.PID in
( select distinct(PID) from Pra1 where apPID = '22101' and apPIDCMpno = 'P' )
and ( t1.CMpno in
( select CMpno from Pra1 where apPID = '22101' and apPIDCMpno = 'P'))
)
)
and
t1.PID like '%/0%'
or t1.PID like '%/1%'
or t1.PID like '%/2%'
or t1.PID like '%/3%'
or t1.PID like '%/4%'
or t1.PID like '%/5%'
or t1.PID like '%/6%'
or t1.PID like '%/7%'
or t1.PID like '%/8%'
or t1.PID like '%/9%'
order by t1.PID
Table 'Pra1'. Scan count 2, logical reads 13522, physical reads 5, read-ahead reads 13631.
Table 'Worktable'. Scan count 178595, logical reads 1114272, physical reads 0, read-ahead reads 0.
Table 'PrCM'. Scan count 1, logical reads 2986, physical reads 2, read-ahead reads 2999.
Table 'Pre2'. Scan count 3, logical reads 1659, physical reads 13, read-ahead reads 369.
Table 'Gb_mp'. Scan count 3, logical reads 5496, physical reads 0, read-ahead reads 1834.
Table 'Gb_ml'. Scan count 3, logical reads 81, physical reads 0, read-ahead reads 27.
Table 'PRcg'. Scan count 1, logical reads 4, physical reads 2, read-ahead reads 2.
Table 'PRte'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0.
Table 'PRst'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 1.
Table 'PRdtl'. Scan count 1, logical reads 9904, physical reads 3, read-ahead reads 9947.
相应的执行计划（部分）如下：
77 1 |--Filter(WHERE:(((((((((like([t1].[PrId], '%/1%', NULL) OR like([t1].[PrId], '%
89668 1 |--Nested Loops(Left Semi Join, WHERE:(((((((((((like([t1].[PrId], '%/1%',
89668 1 |--Sort(ORDER BY:([t1].[CompNo] ASC))
89668 1 | |--Merge Join(Left Semi Join, MANY-TO-MANY MERGE:([t1].[PrId])=([
89668 1 | |--Filter(WHERE:(((((((((([t1].[Type]='1' AND like([t1].[PrI
121820 1 | | |--Clustered Index Scan(OBJECT:([PR].[dbo].[PrCM].[PK_P
1131725 1 | |--Clustered Index Scan(OBJECT:([PR].[dbo].[Pra1].[PK_PrApp
89591 89591 |--Row Count Spool
1 1 |--Filter(WHERE:([Pra1].[ApprIdCompno]='P'))
1 1 |--Bookmark Lookup(BOOKMARK:([Bmk1025]), OBJECT:([PR].[dbo].
26 1 |--Index Seek(OBJECT:([PR].[dbo].[Pra1].[idx_PrApprova
先不说执行计划如何。光从语句本身本我发现了以下这些问题：
1）连接JOIN太多，有7个之多，还不包括视图里面可能包含的连接。要设法减少连接的个数。
2）连接是outer join，非常不好。因为outer join意味着必须对左表或右表查询所有行。如果表很大而没有相应的where语句，那么outer join很容易导致table scan或index scan。要尽量使用inner join避免scan整个表。
3）不相关子查询的使用有待斟酌。
4）Like语句使用了前置百分号，而Or子句大大增加了使用scan表的几率。
再看看statistics IO的输出，根据logical reads判断那些是最昂贵的表。一个是Pra1，logical reads 13522,，比较大。另一个是worktable，logical reads是1114272。Logical reads高的表应该优先优化。再来看执行计划。我从计划中发现了如下的问题：
1）那些like语句对应的PID 字段的index果然没有使用，而是在全部数据查询出来后再作Filter，所以这些like完全不能减少IO。
2）使用了Row Count Spool，这导致了worktable大量的logical reads。 SQL Server使用row count spool来存放中间结果，这个spool对应tempdb中的worktable。如果再仔细一点，你会发现，这个spool是因为SQL Server对一个小表进行了89591次nest loop scan所导致的。优化的关键是要避免这么多次的loop scan。
3）有两处使用了clustered index scan。Clustered index scan相当于全表的table scan。
估计是语句的where语句条件不够强或索引不够好所致。
4） 一个最关键的地方是，返回的行数是77行，但logical reads却有上百万。我们前面说过，如果结果行数和statistics IO的输出相差太大，那么意味着某个地方缺少优化。究竟是什么地方呢？是LIKE语句。这是因为只有like语句的那个Filter才大大减少了返回的行数，但是like语句却因为前置百分号而无法使用索引。
根据上面的分析，可以得出如下的优化建议：
1）使用临时表存放t1表的结果，共77行。还记得吗，能大大减少logical reads（或返回行数）的操作要优先执行。所以我们要首先试图得到这77行数据。 仔细分析语句，你会发现where中的条件全是针对表t1的，所以直接使用上面的where子句查询表t1，然后把结果存放再临时表＃t1中：
Select t1….. into #tt1 from t1 where…(和上面的where一样)
2）再把＃tt1和其他表进行连接:
Select #t1…
Left outer join …
Left outer join…
还记得拆分语句的好处吗？语句现在以我们能够预测的顺序和方式执行了。
3）修改程序，去掉前置百分号。
4）从系统设计的角度修改语句，去掉outer join。
5）考虑组合索引或覆盖索引消除clustered index scan。
上面1和2点建议立即消除了worktable，性能提高了几倍以上，效果非常明显。
（待续）