SQL Server SQL語句調(diào)優(yōu)技巧
????????www.InnovateDigital.com 整理

??????通過例子和解析計(jì)劃，本文展示了在Microsoft SQL Server上提高查詢效率有效的一些技巧。在編程中有很多小提示和技巧。了解這些技巧可以擴(kuò)展你在性能優(yōu)化上的可用機(jī)能。在這部分里我們所有的例子都選擇使用Microsoft SHOWPLAN_ALL輸出，因?yàn)樗o湊并且展示典型的信息。（Sybase的查詢計(jì)劃基本與此相同，可能包含其它一些信息）大部分的例子都是要么基于PUBS數(shù)據(jù)庫，要么基于標(biāo)準(zhǔn)系統(tǒng)表的。我們?cè)赑UBS數(shù)據(jù)庫中對(duì)用到的表進(jìn)行了很大擴(kuò)充，對(duì)很多表增加了好幾萬行。

??????子查詢優(yōu)化

??????一條好的值得稱贊的規(guī)則是盡量用連接代替所有的子查詢。優(yōu)化器有時(shí)可以自動(dòng)將子查詢“扁平化”，并且用常規(guī)或外連接代替。但那樣也不總是有效。明確的連接對(duì)選擇表的順序和找到最可能的計(jì)劃給出了更多的選項(xiàng)。當(dāng)你優(yōu)化一個(gè)特殊查詢時(shí)，了解一下是否去掉自查詢可產(chǎn)生很大的差異。

??????示例
??????下面查詢選擇了pubs數(shù)據(jù)庫中所有表的名字，以及每個(gè)表的聚集索引（如果存在）。如果沒有聚集索引，表名仍然顯示在列表中，在聚集索引列中顯示為虛線。兩個(gè)查詢返回同樣的結(jié)果集，但第一個(gè)使用了一個(gè)子查詢，而第二個(gè)使用一個(gè)外連接時(shí)。比較Microsoft SQL Server產(chǎn)生的查詢計(jì)劃

??????SUBQUERY SOLUTION ??????---------------------- ??????SELECT st.stor_name AS 'Store', ??????(SELECT SUM(bs.qty) ??????FROM big_sales AS bs ??????WHERE bs.stor_id = st.stor_id), 0) ??????AS 'Books Sold' ??????FROM stores AS st ??????WHERE st.stor_id IN ??????(SELECT DISTINCT stor_id ??????FROM big_sales)

JOIN SOLUTION ---------------------- SELECT st.stor_name AS 'Store', SUM(bs.qty) AS 'Books Sold' FROM stores AS st JOIN big_sales AS bs ON bs.stor_id = st.stor_id WHERE st.stor_id IN (SELECT DISTINCT stor_id FROM big_sales) GROUP BY st.stor_name

??????SUBQUERY SOLUTION ??????---------------------- ??????SQL Server parse and compile time: ??????????CPU time = 28 ms ??????????elapsed time = 28 ms ??????SQL Server Execution Times: ??????????CPU time = 145 ms ??????????elapsed time = 145 ms ??????Table 'big_sales'. Scan count 14, logical reads ??????1884, physical reads 0, read-ahead reads 0. ??????Table 'stores'. Scan count 12, logical reads 24, ??????physical reads 0, read-ahead reads 0.

JOIN SOLUTION ---------------------- SQL Server parse and compile time: ????CPU time = 50 ms ????elapsed time = 54 ms SQL Server Execution Times: ????CPU time = 109 ms ????elapsed time = 109 ms Table 'big_sales'. Scan count 14, logical reads 966, physical reads 0, read-ahead reads 0. Table 'stores'. Scan count 12, logical reads 24, physical reads 0, read-ahead reads 0.

??????不必更深探索，我們可以看到在CPU和總的實(shí)耗時(shí)間方面連接更快，僅需要子查詢方案邏輯讀的一半。此外，這兩種情況伴隨著相同的結(jié)果集，雖然排序的順序不同，這是因?yàn)檫B接查詢（由于它的GROUP BY子句）有一個(gè)隱含的ORDER BY:

??????Store Books Sold ??????------------------------------------------------- ??????Barnum's 154125 ??????Bookbeat 518080 ??????Doc-U-Mat: Quality Laundry and Books 581130 ??????Eric the Read Books 76931 ??????Fricative Bookshop 259060 ??????News & Brews 161090 ??????(6 row(s) affected) ??????Store Books Sold ??????------------------------------------------------- ??????Eric the Read Books 76931 ??????Barnum's 154125 ??????News & Brews 161090 ??????Doc-U-Mat: Quality Laundry and Books 581130 ??????Fricative Bookshop 259060 ??????Bookbeat 518080 ??????(6 row(s) affected)

??????查看這個(gè)子查詢方法展示的查詢計(jì)劃:

??????|--Compute Scalar(DEFINE:([Expr1006]=isnull([Expr1004], 0))) ??????|--Nested Loops(Left Outer Join, OUTER REFERENCES:([st].[stor_id])) ??????|--Nested Loops(Inner Join, OUTER REFERENCES:([big_sales].[stor_id])) ?????? ??| |--Stream Aggregate(GROUP BY:([big_sales].[stor_id])) ????????????| | |--Clustered Index Scan(OBJECT:([pubs].[dbo].[big_sales]. ????????????[UPKCL_big_sales]), ORDERED FORWARD) ?????? ??| |--Clustered Index Seek(OBJECT:([pubs].[dbo].[stores].[UPK_storeid] ??????AS [st]), ??????SEEK:([st].[stor_id]=[big_sales].[stor_id]) ORDERED FORWARD) ?????? |--Stream Aggregate(DEFINE:([Expr1004]=SUM([bs].[qty]))) ??????|--Clustered Index Seek(OBJECT:([pubs].[dbo].[big_sales]. ????????[UPKCL_big_sales] AS [bs]), ??????SEEK:([bs].[stor_id]=[st].[stor_id]) ORDERED FORWARD)

??????反之，求和查詢操作我們可以得到：

??????|--Stream Aggregate(GROUP BY:([st].[stor_name]) ????????DEFINE:([Expr1004]=SUM([partialagg1005]))) ??????|--Sort(ORDER BY:([st].[stor_name] ASC)) ??????|--Nested Loops(Left Semi Join, OUTER REFERENCES:([st].[stor_id])) ??????|--Nested Loops(Inner Join, OUTER REFERENCES:([bs].[stor_id])) ????????| |--Stream Aggregate(GROUP BY:([bs].[stor_id]) ??????????DEFINE:([partialagg1005]=SUM([bs].[qty]))) ???????????? | | |--Clustered Index Scan(OBJECT:([pubs].[dbo].[big_sales]. ????????????[UPKCL_big_sales] AS [bs]), ORDERED FORWARD) ????????| |--Clustered Index Seek(OBJECT:([pubs].[dbo].[stores]. ????????????[UPK_storeid] AS [st]), ????????SEEK:([st].[stor_id]=[bs].[stor_id]) ORDERED FORWARD) ??????|--Clustered Index Seek(OBJECT:([pubs].[dbo].[big_sales]. ??????????[UPKCL_big_sales]), ????????SEEK:([big_sales].[stor_id]=[st].[stor_id]) ORDERED FORWARD)

??????使用連接是更有效的方案。它不需要額外的流聚合（stream aggregate），即子查詢所需在big_sales.qty列的求和。

??????UNION vs UNION ALL

??????無論何時(shí)盡可能用UNION ALL 代替UNION。其中的差異是因?yàn)閁NION有排除重復(fù)行并且對(duì)結(jié)果進(jìn)行排序的副作用，而UNION ALL不會(huì)做這些工作。選擇無重復(fù)行的結(jié)果需要建立臨時(shí)工作表，用它排序所有行并且在輸出之前排序。（在一個(gè)select distinct 查詢中顯示查詢計(jì)劃將發(fā)現(xiàn)存在一個(gè)流聚合，消耗百分之三十多的資源處理查詢）。當(dāng)你確切知道你得需要時(shí)，可以使用UNION。但如果你估計(jì)在結(jié)果集中沒有重復(fù)的行，就使用UNION ALL吧。它只是從一個(gè)表或一個(gè)連接中選擇，然后從另一個(gè)表中選擇，附加在第一條結(jié)果集的底部。UNION ALL不需要工作表和排序（除非其它條件引起的）。在大部分情況下UNION ALL更具效率。一個(gè)有潛在危險(xiǎn)的問題是使用UNION會(huì)在數(shù)據(jù)庫中產(chǎn)生巨大的泛濫的臨時(shí)工作表。如果你期望從UNION查詢中獲得大量的結(jié)果集時(shí)，這就可能發(fā)生。

??????示例
??????下面的查詢是選擇pubs數(shù)據(jù)庫中的表sales的所有商店的ID，也選擇表big_sales中的所有商店的ID，這個(gè)表中我們加入了70，000多行數(shù)據(jù)。在這兩個(gè)方案間不同之處僅僅是UNION 與UNION ALL的使用比較。但在這個(gè)計(jì)劃中加入ALL關(guān)鍵字產(chǎn)生了三大不同。第一個(gè)方案中，在返回結(jié)果集給客戶端之前需要流聚合并且排序結(jié)果。第二個(gè)查詢更有效率，特別是對(duì)大表。在這個(gè)例子中兩個(gè)查詢返回同樣的結(jié)果集，雖然順序不同。在我們的測(cè)試中有兩個(gè)臨時(shí)表。你的結(jié)果可能會(huì)稍有差異。

??????UNION SOLUTION ??????-----------------------	??????UNION ALL SOLUTION ??????-----------------------
??????SELECT stor_id FROM big_sales ??????UNION ??????SELECT stor_id FROM sales ??????----------------------------	??????SELECT stor_id FROM big_sales ??????UNION ALL ??????SELECT stor_id FROM sales ??????----------------------------
??????|--Merge Join(Union) ?????? |--Stream Aggregate(GROUP BY: ??????([big_sales].[stor_id])) ??????| |--Clustered Index Scan ??????(OBJECT:([pubs].[dbo]. ??????[big_sales]. ??????[UPKCL_big_sales]), ??????ORDERED FORWARD) ??????|--Stream Aggregate(GROUP BY: ??????([sales].[stor_id])) ?????? |--Clustered Index Scan ?????? (OBJECT:([pubs].[dbo]. ?????? [sales].[UPKCL_sales]), ?????? ORDERED FORWARD)	??????|--Concatenation ??????|--Index Scan ??????(OBJECT:([pubs].[dbo]. ?????? [big_sales].[ndx_sales_ttlID])) ??????|--Index Scan ??????(OBJECT:([pubs].[dbo]. ??????[sales].[titleidind]))

??????UNION SOLUTION ??????----------------------- ??????Table 'sales'. Scan count 1, logical ??????reads 2, physical reads 0, ??????read-ahead reads 0. ??????Table 'big_sales'. Scan count 1, ??????logical ??????reads 463, physical reads 0, ??????read-ahead reads 0.

??????UNION ALL SOLUTION ??????----------------------- ??????Table 'sales'. Scan count 1, logical ??????reads 1, physical reads 0, ??????read-ahead reads 0. ??????Table 'big_sales'. Scan count 1, ??????logical ??????reads 224, physical reads 0, ??????read-ahead reads 0.

??????雖然在這個(gè)例子的結(jié)果集是可互換的，你可以看到UNION ALL語句比UNION語句少消耗一半的資源。所以應(yīng)當(dāng)預(yù)料你的結(jié)果集并且確定已經(jīng)沒有重復(fù)時(shí)，使用UNION ALL子句。

??????函數(shù)和表達(dá)式約束索引

??????當(dāng)你在索引列上使用內(nèi)置的函數(shù)或表達(dá)式時(shí)，優(yōu)化器不能使用這些列的索引。盡量重寫這些條件，在表達(dá)式中不要包含索引列。

??????示例

??????你應(yīng)該幫助SQL Server移除任何在索引數(shù)值列周圍的表達(dá)式。下面的查詢是從表jobs通過唯一的聚集索引的唯一鍵值選擇出的一行。如果你在這個(gè)列上使用表達(dá)式，這個(gè)索引就不起作用了。但一旦你將條件’job_id-2=0’ 該成‘job_id=2’，優(yōu)化器將在聚集索引上執(zhí)行seek操作。

??????QUERY WITH SUPPRESSED INDEX ??????-----------------------	??????OPTIMIZED QUERY USING INDEX ??????-----------------------
??????SELECT * ??????FROM jobs ??????WHERE (job_id-2) = 0	??????SELECT * ??????FROM jobs ??????WHERE job_id = 2
??????|--Clustered Index Scan(OBJECT: ??????([pubs].[dbo].[jobs]. ??????[PK__jobs__117F9D94]), ??????WHERE:(Convert([jobs].[job_id])- ??????2=0))	??????|--Clustered Index Seek(OBJECT: ??????([pubs].[dbo].[jobs]. ??????[PK__jobs__117F9D94]), ??????SEEK:([jobs].[job_id]=Convert([@1])) ??????ORDERED FORWARD) ??????Note that a SEEK is much better than a SCAN, ??????as in the previous query.

??????下面表中列出了多種不同類型查詢示例，其被禁止使用列索引，同時(shí)給出改寫的方法，以獲得更優(yōu)的性能。

??????QUERY WITH SUPPRESSED INDEX ??????---------------------------------------	??????OPTIMIZED QUERY USING INDEX ??????--------------------------------------
??????DECLARE @job_id VARCHAR(5) ??????SELECT @job_id = ‘2’ ??????SELECT * ??????FROM jobs ??????WHERE CONVERT( VARCHAR(5), ??????job_id ) = @job_id ??????-------------------------------	??????DECLARE @job_id VARCHAR(5) ??????SELECT @job_id = ‘2’ ??????SELECT * ??????FROM jobs ??????WHERE job_id = CONVERT( ??????SMALLINT, @job_id ) ??????-------------------------------
??????SELECT * ??????FROM authors ??????WHERE au_fname + ' ' + au_lname ??????= 'Johnson White' ??????-------------------------------	??????SELECT * ??????FROM authors ??????WHERE au_fname = 'Johnson' ??????AND au_lname = 'White' ??????-------------------------------
??????SELECT * ??????FROM authors ??????WHERE SUBSTRING( au_lname, 1, 2 ) = 'Wh' ??????-------------------------------	??????SELECT * ??????FROM authors ??????WHERE au_lname LIKE 'Wh%' ??????-------------------------------
??????CREATE INDEX employee_hire_date ??????ON employee ( hire_date ) ??????GO ??????-- Get all employees hired ??????-- in the 1st quarter of 1990: ??????SELECT * ??????FROM employee ??????WHERE DATEPART( year, hire_date ) = 1990 ??????AND DATEPART( quarter, hire_date ) = 1 ??????-------------------------------	??????CREATE INDEX employee_hire_date ??????ON employee ( hire_date ) ??????GO ??????-- Get all employees hired ??????-- in the 1st quarter of 1990: ??????SELECT * ??????FROM employee ??????WHERE hire_date >= ‘1/1/1990’ ??????AND hire_date < ‘4/1/1990’ ??????-------------------------------
??????-- Suppose that hire_date may ??????-- contain time other than 12AM ??????-- Who was hired on 2/21/1990? ??????SELECT * ??????FROM employee ??????WHERE CONVERT( CHAR(10), ??????hire_date, 101 ) = ‘2/21/1990’	??????-- Suppose that hire_date may ??????-- contain time other than 12AM ??????-- Who was hired on 2/21/1990? ??????SELECT * ??????FROM employee ??????WHERE hire_date >= ‘2/21/1990’ ??????AND hire_date < ‘2/22/1990’

??????SET NOCOUNT ON

??????使用SET NOCOUNT ON 提高T-SQL代碼速度的現(xiàn)象使SQL Server開發(fā)者和數(shù)據(jù)庫系統(tǒng)管理者驚訝難解。你可能已經(jīng)注意到成功的查詢返回了關(guān)于受影響的行數(shù)的系統(tǒng)信息。在很多情況下，你不需要這些信息。這個(gè)SET NOCOUNT ON命令允許你禁止所有在你的會(huì)話事務(wù)中的子查詢的信息，直到你發(fā)出SET NOCOUNT OFF。
??????這個(gè)選項(xiàng)不只在于其輸出的裝飾效果。它減少了從服務(wù)器端到客戶端傳遞的信息量。因此，它幫助降低了網(wǎng)絡(luò)通信量并提高了你的事務(wù)整體響應(yīng)時(shí)間。傳遞單個(gè)信息的時(shí)間可以忽略，但考慮到這種情況，一個(gè)腳本在一個(gè)循環(huán)里執(zhí)行一些查詢并且發(fā)送好幾千字節(jié)無用的信息給用戶。

??????為做個(gè)例子，一個(gè)文件含T-SQL批處理，其在big_sales表插入了9999行。

??????-- Assumes the existence of a table called BIG_SALES, a copy of pubs..sales

??????SET NOCOUNT ON

??????DECLARE @separator VARCHAR(25),

??????@message VARCHAR(25),

??????@counter INT,

??????@ord_nbr VARCHAR(20),

??????@order_date DATETIME,

??????@store_nbr INT,

??????@qty_sold INT,

??????@terms VARCHAR(12),

??????@title CHAR(6),

??????@starttime DATETIME

??????SET @STARTTIME = GETDATE()

??????SELECT @counter = 0,

??????@separator = REPLICATE( '-', 25 )

??????WHILE @counter < 9999

??????BEGIN

??????SET @counter = @counter + 1

??????SET @ord_nbr = 'Y' + CAST(@counter AS VARCHAR(5))

??????SET @order_date = DATEADD(hour, (@counter * 8), 'Jan 01 1999')

??????SET @store_nbr =

??????CASE WHEN @counter < 999 THEN '6380'

??????WHEN @counter BETWEEN 1000 AND 2999 THEN '7066'

??????WHEN @counter BETWEEN 3000 AND 3999 THEN '7067'

??????WHEN @counter BETWEEN 4000 AND 6999 THEN '7131'

??????WHEN @counter BETWEEN 7000 AND 7999 THEN '7896'

??????WHEN @counter BETWEEN 8000 AND 9999 THEN '8042'

??????ELSE '6380'

??????END

??????SET @qty_sold =

??????CASE WHEN @counter BETWEEN 0 AND 2999 THEN 11

??????WHEN @counter BETWEEN 3000 AND 5999 THEN 23

??????ELSE 37

??????END

??????SET @terms =

??????CASE WHEN @counter BETWEEN 0 AND 2999 THEN 'Net 30'

??????WHEN @counter BETWEEN 3000 AND 5999 THEN 'Net 60'

??????ELSE 'On Invoice'

??????END

??????-- SET @title = (SELECT title_id FROM big_sales WHERE qty = (SELECT MAX(qty)

??????FROM big_sales))

??????SET @title =

??????CASE WHEN @counter < 999 THEN 'MC2222'

??????WHEN @counter BETWEEN 1000 AND 1999 THEN 'MC2222'

??????WHEN @counter BETWEEN 2000 AND 3999 THEN 'MC3026'

??????WHEN @counter BETWEEN 4000 AND 5999 THEN 'PS2106'

??????WHEN @counter BETWEEN 6000 AND 6999 THEN 'PS7777'

??????WHEN @counter BETWEEN 7000 AND 7999 THEN 'TC3218'

??????ELSE 'PS1372'

??????END

??????-- PRINT @separator

??????-- SELECT @message = STR( @counter, 10 ) -- + STR( SQRT( CONVERT( FLOAT,

??????@counter ) ), 10, 4 )

??????-- PRINT @message

??????BEGIN TRAN

??????INSERT INTO [pubs].[dbo].[big_sales]([stor_id], [ord_num], [ord_date],

??????[qty], [payterms], [title_id])

??????VALUES(@store_nbr, CAST(@ord_nbr AS CHAR(5)), @order_date, @qty_sold,

??????@terms, @title)

??????COMMIT TRAN

??????END

??????SET @message = CAST(DATEDIFF(ms, @starttime, GETDATE()) AS VARCHAR(20))

??????PRINT @message

??????/*

??????TRUNCATE table big_sales

??????INSERT INTO big_sales

??????SELECT * FROM sales

??????SELECT title_id, sum(qty)

??????FROM big_sales

??????group by title_id

??????order by sum(qty)

??????SELECT * FROM big_sales

??????*/

??????當(dāng)帶SET NOCOUNT OFF命令運(yùn)行，實(shí)耗時(shí)間是5176毫秒。當(dāng)帶SET NOCOUNT ON命令運(yùn)行，實(shí)耗時(shí)間是1620毫秒。如果不需要輸出中的行數(shù)信息，考慮在每一個(gè)存儲(chǔ)過程和腳本開始時(shí)增加SET NOCOUNT ON 命令將。

??????TOP 和 SET ROWCOUNT

??????SELECT 語句中的TOP子句限制單個(gè)查詢返回的行數(shù)，而SET ROWCOUNT限制所有后續(xù)查詢影響的行數(shù)。在很多編程任務(wù)中這些命令提供了高效率。
??????SET ROWCOUNT在SELECT，INSERT，UPDATE OR DELETE語句中設(shè)置可以被影響的最大行數(shù)。這些設(shè)置在命令執(zhí)行時(shí)馬上生效并且只影響當(dāng)前的會(huì)話。為了移除這個(gè)限制執(zhí)行SET ROWCOUNT 0。
一些實(shí)際的任務(wù)用TOP or SET ROWCOUNT比用標(biāo)準(zhǔn)的SQL命令對(duì)編程是更有效率的。讓我們?cè)趲讉€(gè)例子中證明：

??????TOP n

??????在幾乎所有的數(shù)據(jù)庫中最流行的一個(gè)查詢是請(qǐng)求一個(gè)列表中的前N項(xiàng)。在 pubs數(shù)據(jù)庫案例中，我們可以查找銷售最好CD的前五項(xiàng)。比較用TOP，SET ROWCOUNT和使用ANSI SQL的三種方案。

??????純 ANSI SQL:

??????Select title,ytd_sales

??????From titles a

??????Where (select count(*)

??????From titles b

??????Where b.ytd_sales>a.ytd_sales

??????)<5

??????Order by ytd_sales DESC

??????這個(gè)純ANSI SQL方案執(zhí)行一個(gè)效率可能很低的關(guān)聯(lián)子查詢，特別的在這個(gè)例子中，在ytd_sales上沒有索引支持。另外，這個(gè)純的標(biāo)準(zhǔn)SQL命令沒有過濾掉在ytd_sales的空值,也沒有區(qū)別多個(gè)CD間有關(guān)聯(lián)的情況。

??????使用 SET ROWCOUNT:

??????SET ROWCOUNT 5

??????SELECT title, ytd_sales

??????FROM titles

??????ORDER BY ytd_sales DESC

??????SET ROWCOUNT 0

??????使用 TOP n:

??????SELECT TOP 5 title, ytd_sales

??????FROM titles

??????ORDER BY ytd_sales DESC

??????第二個(gè)方案使用SET ROWCOUNT來停止SELECT查詢，而第三個(gè)方案是當(dāng)它找到前五行時(shí)用TOP n來停止。在這種情況下，在獲得結(jié)果之前我們也要有一個(gè)ORDER BY子句強(qiáng)制對(duì)整個(gè)表進(jìn)行排序。兩個(gè)查詢的查詢計(jì)劃實(shí)際上是一樣的。然而，TOP優(yōu)于SET ROWCOUNT的關(guān)鍵點(diǎn)是SET必須處理ORDER BY子句所需的工作表，而TOP 不用。

??????在一個(gè)大表上，我們可以在ytd_sales上創(chuàng)建一個(gè)索引以避免排序。查詢將使用該索引找到前5行并停止。與第一個(gè)方案相比較，其掃描了整個(gè)表，并對(duì)每一行執(zhí)行了一個(gè)關(guān)聯(lián)子查詢。在小表上，性能的差異是很小的。但是在一個(gè)大表上，第一個(gè)方案的處理時(shí)間可能是數(shù)個(gè)小時(shí)，而后兩個(gè)方法是數(shù)秒。

??????當(dāng)確定查詢需要時(shí)，請(qǐng)考慮是否只需要其中幾行，如果是，使用TOP子句將節(jié)約大量時(shí)間。

???? （北京鑄銳數(shù)碼科技有限公司 www.InnovateDigital.com）