臨時表VS表變量：因地制宜，合理使用

作者：shanks_gao 2014-09-25 09:43:29

數據庫

借用火影忍者中宇智波. 鼬的一句名言：”任何術都是有缺陷的” 同樣,在數據庫的世界里沒有哪項技術是完美無缺的.根據實際的場景,情形,選擇合理的實現方式才是我們的初衷。

一直以來大家對臨時表與表變量的孰優孰劣爭論頗多,一些技術群里的朋友甚至認為表變量幾乎一無是處,比如無統計信息,不支持事務等等.但事實并非如此.這里我就臨時表與表變量做個對比,對于大多數人不理解或是有歧義的地方進行詳細說明.

注:這里只討論一般臨時表,對全局臨時表不做闡述.

生命周期

臨時表:會話中,proc中,或使用顯式drop

表變量:batch中

這里用簡單的code說明表變量作用域

DECLARE @t TABLE(i int) ----定義表變量@t  
 
SELECT *FROM @t        -----訪問OK  
 
insert into @t select 1 -----插入數據OK  
 
select * from  @t      -------訪問OK  
go                     -------結束批處理  
select * from @t       -------不在作用域出錯

注意:雖然說sqlserver在定義表變量完成前不允許你使用定義的變量.但注意下面情況仍然可正常運行!

if 'a'='b' 
begin 
DECLARE @t TABLE(i int)  
end 
SELECT *FROM @t        -----仍然可以訪問！

日志機制

臨時表與表變量都會記錄在tempdb中記錄日志

不同的是臨時表的活動日志在事務完成前是不能截斷的.

這里應注意的是由于表變量不支持truncate,所以完全清空對象結果集時臨時表有明顯優勢,而表變量只能delete

事務支持

臨時表:支持

表變量:不支持

我們通過簡單的實例加以說明

create table #t (i int)  
declare @t table(i int)  
 
BEGIN TRAN ttt  
insert into #t select 1  
insert into @t select 1  
SELECT * FROM #t  ------returns 1 rows  
SELECT * FROM @t  ------returns 1 rows  
ROLLBACK tran ttt  
 
SELECT * FROM #t    -------no rows  
SELECT * FROM @t    -------still 1 rows  
drop table #t       ----no use drop @t in session

鎖機制(select)

臨時表會對相關對象加IS(意向共享)鎖

表變量會對相關對象加SCH-S(架構共享)鎖(相當于加了nolock hint)

可以看出雖說鎖的影響范圍不同,但由于作用域都只是會話或是batch中,臨時表的IS鎖雖說兼容性不如表變量的SCH-S但絕大多數情況基本無影響.

感興趣的朋友可以用TF1200測試

索引支持

臨時表支持

表變量條件支持(僅SQL2014)

沒錯,在sql2014中你可以在創建表的同時創建索引圖1-1

注:在sql2014之前表變量只支持創建一個默認的唯一性約束

cod

DECLARE @t TABLE   
(  
col1 int index inx_1 CLUSTERED,   
col2 int  index index_2 NONCLUSTERED,  
       index index_3 NONCLUSTERED(col1,col2)  
)

圖1-1

CREATE FUNCTION TVP_Customers (@cust nvarchar(10))  
RETURNS TABLE 
AS 
 RETURN 
 (SELECT RowNum, CustomerID, OrderDate, ShipCountry  
 FROM BigOrders  
 WHERE CustomerID = @cust);  
GO  
CREATE FUNCTION TVF_Customers (@cust nvarchar(10))  
RETURNS @T TABLE (RowNum int, CustomerID nchar(10), OrderDate date,  
 ShipCountry nvarchar(30))  
AS 
BEGIN 
 INSERT INTO @T  
  SELECT RowNum, CustomerID, OrderDate, ShipCountry  
  FROM BigOrders  
  WHERE CustomerID = @cust  
  RETURN 
END;  
 
DBCC FREEPROCCACHE  
GO  
SELECT * FROM TVF_Customers('CENTC');  
GO  
SELECT * FROM TVP_Customers('CENTC');  
GO  
SELECT * FROM TVF_Customers('SAVEA');  
GO  
SELECT * FROM TVP_Customers('SAVEA');  
GO  
 
select b.text,a.execution_count,a.* from sys.dm_exec_query_stats a  
cross apply sys.dm_exec_sql_text(a.sql_handle) b  
where b.text like '%_Customers%'

圖1-2

其它方面

表變量不支持select into，alter，truncate，dbcc等

表變量不支持table hint 如(force seek)

執行計劃預估

我想這里可能是引起使用何種方式爭論比較突出的地方,由于表變量沒有統計信息,無法添加索引等使得大家對其在執行計劃中的性能表現嗤之以鼻,但實際情況呢?我們需要深入分析.

關于臨時表的預估這里我就不做介紹了,主要對表變量的預估做詳細闡述.

表變量在sql2000引入的一個原因就是為了在一些執行過程中減少重編譯.以獲得更好的性能.當然帶來好處的同時也會帶來一定弊端.由于其不涉及重編譯,優化器其實并不知道表變量中的具體行數,此時他采取了保守的預估方式:預估行數為1行.如圖2-1

Code

declare @t table (i int)  
select * from @t-----此時0行預估行數為1行  
insert into @t select 1  
select * from @t-----此時1行,預估行數仍為1行  
insert into @t values (2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)  
select * from @t ----此時19行,預估行數仍為1行  
 
--....無論實際@t中有多少行,由于沒有重編譯,預估均為1行

圖2-1

所以當我們加上重編譯的的操作,此時優化器就知道了表變量的具體行數.如圖2-2

Code

declare @t table (i int)  
select * from @t option(recompile)-----此時0行預估行數為1行  
insert into @t select 1  
select * from @t  option(recompile)-----此時1行,預估行數為1行  
insert into @t values (2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)  
select * from @t  option(recompile)----此時19行,預估行數為19行  
--....當加入重編譯hint時,優化器就知道的表變量的行數.

圖2-2

至此,我們可以看到優化器知道了表變量中的行數.這樣在表變量掃描的過程中,尤其針對數據量較大的情形,不會因為預估總是1而引起一些問題.

如果你剛知道這里的預估原理,現有的代碼都加上重編譯那工作量可想而知了..這里介紹一個新的跟蹤標記,Trace Flag 2453.

TF2453可以一定程度上替代重編譯Hint,但只是在非簡單計劃（trivial plans）的情形下

注:TF2453只在sql2012 SP2和SQL2014中的補丁中起作用

#p#

表變量謂詞預估

由于表變量木有統計信息,在優化器知道整體行數的前提下將會根據謂詞的情形

采用不同的規則"猜"來進行預估.

注:這里有些規則筆者未找到微軟相應的算法文檔,經過自己根據數據推算得出.

看到這里的朋友請為我點個贊J(很長時間推算得出.可能數學忘得差不多了)

注:由于檢索對象本身及為變量,謂詞為變量,或是常數無影響

常見謂詞下預估算法:

a ">", "<" 運算符按照表變量數據量的30%進行預估

b "like" 運算符按照表變量數據量的10%進行預估

c "=" 運算符按照表變量數據量的0.75次方預估

實例如圖2-3

code

declare @i int 
set @i=13  
DECLARE @T TABLE(I INT);  
INSERT INTO @T VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)  
------表變量中存在個數字  
select * from @T where I < 1  option(recompile) ------20*30% 預估數為6  
select * from @T where I > @i option(recompile) --------20*30%預估數為6  
select * from @T where I like @i  option(recompile) --------20*10% 預估數為2  
select * from @T where I like 1  option(recompile)  --------20*10 預估數為2  
select * from @T where I = @i  option(recompile) --------POWER(20.00000,0.75) 預估數為9.45742  
select * from @T where I = 1  option(recompile)  --------POWER(20.00000,0.75) 預估數為9.45742  
 
insert into @T  
select DatabaseLogID from AdventureWorks2008R2.dbo.DatabaseLog------insert new records  
select * from @T option(recompile) ------------此時數據為行  
select * from @T where I = 1  option(recompile)--------------------POWER(1617.00000,0.75) 預估數為254.99550

圖2-3

可以看出根據不同的謂詞優化器會采用不同的預估方式,雖然它不如統計信息下的密度,直方圖等來的精確(尤其是等值預估,在數據量巨大的情形下,其效果可能接近統計信息),但在了解數據的前提下如果適合表變量我們還是可以大膽使用的.

Tempdb競爭

tempdb的競爭本身涵蓋的知識面比較大,這里我們只討論臨時表與表變量的孰優孰劣.

通過前面的介紹我們知道臨時表是支持事務的,而表變量時不支持的.正因如此很多人放棄了表變量的使用.但任何事情都有兩方面,支持就一定好嗎?由于臨時表對事務的支持,在高并發的情形中可能正因為其事務的支持造成系統表鎖,總而影響并發.

我們通過一個簡單的實例來說明

日常管理中,我發現很多開發人員在使用臨時表時采用select * into #t from …的語法,這樣的寫法如果數據量稍大,將會造成事務持有系統表鎖的時間變長,從而影響并發,吞吐.我們通過一個簡單的實例說明.如圖3-1

Code 我們通過sqlquerystress模擬并發

----SSMS測試數據  
Use tempdb  
create table t  
( id int identity,str1 char(8000))----more pages for many records  
 
insert into t select 'a' 
go 100  
 
----sqlquerystress  
select * into #t  
from t----57s  
 
----sqlquerystress  
declare @t table 
( id int,str1 char(8000))  
insert into @t  
select * from t-----1s

圖3-1

通過圖3-1可以看出上述情形中臨時表簡直不堪重負.臨時表與表變量到底該如何應用不是看誰比誰的優點多,應視具體情形而定

結語:借用火影忍者中宇智波. 鼬的一句名言：”任何術都是有缺陷的” 同樣,在數據庫的世界里沒有哪項技術是完美無缺的.根據實際的場景,情形,選擇合理的實現方式才是我們的初衷.

原文出自：http://www.cnblogs.com/shanksgao/p/3988089.html

責任編輯：林師授來源： shanks_gao的博客

臨時表表變量 SQL