有關T-SQL的10個好習慣

1.在生產環境中不要出現Select *

這一點我想大家已經是比較熟知了，這樣的錯誤相信會犯的人不會太多。但我這里還是要說一下。

不使用Select *的原因主要不是坊間所流傳的將*解析成具體的列需要產生消耗，這點消耗在我看來完全可以忽略不計。更主要的原因來自以下兩點:

擴展方面的問題
造成額外的書簽查找或是由查找變為掃描

擴展方面的問題是當表中添加一個列時，Select *會把這一列也囊括進去，從而造成上面的第二種問題。

而額外的IO這點顯而易見，當查找不需要的列時自然會產生不必要的IO，下面我們通過一個非常簡單的例子來比較這兩種差別，如圖1所示。

圖1.*帶來的不必要的IO

2.聲明變量時指定長度

這一點有時候會被人疏忽，因為對于T-SQL來說，如果對于變量不指定長度，則默認的長度會是1.考慮下面這個例子，如圖2所示。

圖2.不指定變量長度有可能導致丟失數據

3.使用合適的數據類型

合適的數據類型首先是從性能角度考慮，關于這一點，我寫過一篇文章詳細的介紹過，有興趣可以閱讀：對于表列數據類型選擇的一點思考，這里我就不再細說了

不要使用字符串類型存儲日期數據，這一點也需要強調一些，有時候你可能需要定義自己的日期格式，但這樣做非常不好，不僅是性能上不好，并且內置的日期時間函數也不能用了。

4.使用Schema前綴來選擇表

解析對象的時候需要更多的步驟，而指定Schema.Table這種方式就避免了這種無謂的解析。

不僅如此，如果不指定Schema容易造成混淆，有時會報錯。

還有一點是，Schema使用的混亂有可能導致更多的執行計劃緩存，換句話說，就是同樣一份執行計劃被多次緩存，讓我們來看圖3的例子。

圖3.不同的schema選擇不同導致同樣的查詢被多次緩存

5.命名規范很重要

推薦使用實體對象+操作這種方式，比如Customer_Update這種方式。在一個大型一點的數據庫會存在很多存儲過程，不同的命名方式使得找到需要的存儲過程變得很不方便。因此有可能造成另一種問題，就是重復創建存儲過程，比如上面這個例子，有可能命名規范不統一的情況下又創建了一個叫UpdateCustomer的存儲過程。

6.插入大量數據時，盡量不要使用循環，可以使用CTE，如果要使用循環，也放到一個事務中

這點其實顯而易見。SQL Server是隱式事務提交的，所以對于每一個循環中的INSERT,都會作為一個事務提交。這種效率可想而知，但如果將1000條語句放到一個事務中提交，效率無疑會提升不少。

打個比方，去銀行存款，是一次存1000效率高，還是存10次100？下面，根據吉日的要求，補個例子,見代碼1.

CREATE TABLE dbo.TestInsert
(
	Number INT PRIMARY KEY
);
--循環插入，不給力,我的筆記本45秒
DECLARE @index INT;
SET @index = 1;

WHILE @index <= 100000
BEGIN
	INSERT dbo.TestInsert(Number) VALUES( @index);
	SET @index = @index + 1;
END



--放到一個事務中循環，略好，但也不是最好,我的筆記本1秒
BEGIN TRAN
DECLARE @index INT;
SET @index = 1;

WHILE @index <= 100000
BEGIN
	INSERT dbo.TestInsert(Number) VALUES( @index);
	SET @index = @index + 1;
END

COMMIT

--批量插入，10W行，顯示0秒，有興趣的同學改成100W行進行測試
INSERT dbo.TestInsert(Number)
	SELECT TOP (100000) rn = ROW_NUMBER() OVER
		(ORDER BY c1.[object_id])
		FROM sys.columns AS c1
		CROSS JOIN sys.columns AS c2
		CROSS JOIN sys.columns AS c3
		ORDER BY c1.[object_id];


--CTE方式，和上面那種方式大同小異，也是批量插入,比如:
WITH cte AS(
	SELECT TOP (100000) rn = ROW_NUMBER() OVER
		(ORDER BY c1.[object_id])
		FROM sys.columns AS c1
		CROSS JOIN sys.columns AS c2
		CROSS JOIN sys.columns AS c3
		ORDER BY c1.[object_id]
)
INSERT dbo.TestInsert(Number) SELECT rn FROM cte

代碼1.幾種插入方式的比較

7.where條件之后盡量減少使用函數或數據類型轉換

換句話說,WHERE條件之后盡量可以使用可以嗅探參數的方式，比如說盡量少用變量，盡量少用函數，下面我們通過一個簡單的例子來看這之間的差別。如圖4所示。

圖4.在Where中使用不可嗅探的參數導致的索引查找

對于另外一些情況來說,盡量不要讓參數進行類型轉換，再看一個簡單的例子，我們可以看出在Where中使用隱式轉換代價巨大。如圖5所示。

圖5.隱式轉換帶來的性能問題

8.不要使用舊的連接方式，比如（from x,y,z）

可能導致效率低下的笛卡爾積,當你看到下面這個圖標時，說明查詢分析器無法根據統計信息估計表中的數據結構，所以無法使用Loop join,merge Join和Hash Join中的一種，而是使用效率地下的笛卡爾積。

> 這里我再補充一點，我說得是“可能”導致，因為上面這個查詢可能作為中間結果或是子查詢，當你忘寫了where條件時，會是笛卡爾積。你在最終結果中再用where過濾，可能得到的結果一模一樣，但是中間的過程卻大不相同

所以，盡量使用Inner join的方式替代from x,y,z這種方式。

9.使用游標時，加上只讀只進選項

首先，我的觀點是：游標是邪惡的，盡量少用。但是如果一定要用的話，請記住，默認設置游標是可進可退的，如果你僅僅設置了

declare c cursor

    for

這樣的形式，那么這種游標要慢于下面這種方式。

 declare c cursor

    local static read_only forward_only

    for…

所以，在游標只讀只進的情況下，加上上面代碼所示的選項。

10.有關Order一些要注意的事情

首先，要注意，不要使用Order by+數字的形式，比如圖6這種。

圖6.Order By序號

當表結構或者Select之后的列變化時，這種方式會引起麻煩，所以老老實實寫上列名。

還有一種情況是,對于帶有子查詢和CTE的查詢，子查詢有序并不代表整個查詢有序，除非顯式指定了Order By,讓我們來看圖7。

圖7.雖然在CTE中中有序，但顯式指定Order By,則不能保證結果的順序

posted @ 2012-10-11 11:06 CareySon 閱讀(33671) 評論(110) 收藏舉報

刷新頁面返回頂部