談談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，效能提高了幾倍以上，效果非常明顯。

（待續）