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)表的。我們在PUBS數(shù)據(jù)庫中對用到的表進(jìn)行了很大擴(kuò)充，對很多表增加了好幾萬行。
??????子查詢優(yōu)化
??????一條好的值得稱贊的規(guī)則是盡量用連接代替所有的子查詢。優(yōu)化器有時(shí)可以自動將子查詢“扁平化”，并且用常規(guī)或外連接代替。但那樣也不總是有效。明確的連接對選擇表的順序和找到最可能的計(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ù)行并且對結(jié)果進(jìn)行排序的副作用，而UNION ALL不會做這些工作。選擇無重復(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會在數(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è)查詢更有效率，特別是對大表。在這個(gè)例子中兩個(gè)查詢返回同樣的結(jié)果集，雖然順序不同。在我們的測試中有兩個(gè)臨時(shí)表。你的結(jié)果可能會稍有差異。
| ??????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命令允許你禁止所有在你的會話事務(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è)存儲過程和腳本開始時(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)前的會話。為了移除這個(gè)限制執(zhí)行SET ROWCOUNT 0。
一些實(shí)際的任務(wù)用TOP or SET ROWCOUNT比用標(biāo)準(zhǔn)的SQL命令對編程是更有效率的。讓我們在幾個(gè)例子中證明：
??????TOP n
??????在幾乎所有的數(shù)據(jù)庫中最流行的一個(gè)查詢是請求一個(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)制對整個(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è)表，并對每一行執(zhí)行了一個(gè)關(guān)聯(lián)子查詢。在小表上，性能的差異是很小的。但是在一個(gè)大表上，第一個(gè)方案的處理時(shí)間可能是數(shù)個(gè)小時(shí)，而后兩個(gè)方法是數(shù)秒。
??????當(dāng)確定查詢需要時(shí)，請考慮是否只需要其中幾行，如果是，使用TOP子句將節(jié)約大量時(shí)間。
???? （北京鑄銳數(shù)碼科技有限公司 www.InnovateDigital.com）