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í)間。
|