临时表VS表变量:因地制宜,合理使用

数据库
借用火影忍者中宇智波. 鼬的一句名言:”任何术都是有缺陷的” 同样,在数据库的世界里没有哪项技术是完美无缺的.根据实际的场景,情形,选择合理的实现方式才是我们的初衷。

一直以来大家对临时表与表变量的孰优孰劣争论颇多,一些技术群里的朋友甚至认为表变量几乎一无是处,比如无统计信息,不支持事务等等.但事实并非如此.这里我就临时表与表变量做个对比,对于大多数人不理解或是有歧义的地方进行详细说明.

注:这里只讨论一般临时表,对全局临时表不做阐述.

生命周期

临时表:会话中,proc中,或使用显式drop

表变量:batch中

这里用简单的code说明表变量作用域

  1. DECLARE @t TABLE(i int----定义表变量@t  
  2.  
  3. SELECT *FROM @t        -----访问OK  
  4.  
  5. insert into @t select 1 -----插入数据OK  
  6.  
  7. select * from  @t      -------访问OK  
  8. go                     -------结束批处理  
  9. select * from @t       -------不在作用域出错 

注意:虽然说sqlserver在定义表变量完成前不允许你使用定义的变量.但注意下面情况仍然可正常运行!

  1. if 'a'='b' 
  2. begin 
  3. DECLARE @t TABLE(i int)  
  4. end 
  5. SELECT *FROM @t        -----仍然可以访问!  

日志机制

临时表与表变量都会记录在tempdb中记录日志

不同的是临时表的活动日志在事务完成前是不能截断的.

这里应注意的是由于表变量不支持truncate,所以完全清空对象结果集时临时表有明显优势,而表变量只能delete

事务支持

临时表:支持

表变量:不支持

我们通过简单的实例加以说明

  1. create table #t (i int)  
  2. declare @t table(i int)  
  3.  
  4. BEGIN TRAN ttt  
  5. insert into #t select 1  
  6. insert into @t select 1  
  7. SELECT * FROM #t  ------returns 1 rows  
  8. SELECT * FROM @t  ------returns 1 rows  
  9. ROLLBACK tran ttt  
  10.  
  11. SELECT * FROM #t    -------no rows  
  12. SELECT * FROM @t    -------still 1 rows  
  13. 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

  1. DECLARE @t TABLE   
  2. (  
  3. col1 int index inx_1 CLUSTERED,   
  4. col2 int  index index_2 NONCLUSTERED,  
  5.        index index_3 NONCLUSTERED(col1,col2)  

图1-1

  1. CREATE FUNCTION TVP_Customers (@cust nvarchar(10))  
  2. RETURNS TABLE 
  3. AS 
  4.  RETURN 
  5.  (SELECT RowNum, CustomerID, OrderDate, ShipCountry  
  6.  FROM BigOrders  
  7.  WHERE CustomerID = @cust);  
  8. GO  
  9. CREATE FUNCTION TVF_Customers (@cust nvarchar(10))  
  10. RETURNS @T TABLE (RowNum int, CustomerID nchar(10), OrderDate date,  
  11.  ShipCountry nvarchar(30))  
  12. AS 
  13. BEGIN 
  14.  INSERT INTO @T  
  15.   SELECT RowNum, CustomerID, OrderDate, ShipCountry  
  16.   FROM BigOrders  
  17.   WHERE CustomerID = @cust  
  18.   RETURN 
  19. END;  
  20.  
  21. DBCC FREEPROCCACHE  
  22. GO  
  23. SELECT * FROM TVF_Customers('CENTC');  
  24. GO  
  25. SELECT * FROM TVP_Customers('CENTC');  
  26. GO  
  27. SELECT * FROM TVF_Customers('SAVEA');  
  28. GO  
  29. SELECT * FROM TVP_Customers('SAVEA');  
  30. GO  
  31.  
  32. select b.text,a.execution_count,a.* from sys.dm_exec_query_stats a  
  33. cross apply sys.dm_exec_sql_text(a.sql_handle) b  
  34. where b.text like '%_Customers%' 

图1-2

其它方面

表变量不支持select into,alter,truncate,dbcc等

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

 

执行计划预估

我想这里可能是引起使用何种方式争论比较突出的地方,由于表变量没有统计信息,无法添加索引等使得大家对其在执行计划中的性能表现嗤之以鼻,但实际情况呢?我们需要深入分析.

关于临时表的预估这里我就不做介绍了,主要对表变量的预估做详细阐述.

表变量在sql2000引入的一个原因就是为了在一些执行过程中减少重编译.以获得更好的性能.当然带来好处的同时也会带来一定弊端.由于其不涉及重编译,优化器其实并不知道表变量中的具体行数,此时他采取了保守的预估方式:预估行数为1行.如图2-1

Code

  1. declare @t table (i int)  
  2. select * from @t-----此时0行预估行数为1行  
  3. insert into @t select 1  
  4. select * from @t-----此时1行,预估行数仍为1行  
  5. insert into @t values (2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)  
  6. select * from @t ----此时19行,预估行数仍为1行  
  7.  
  8. --....无论实际@t中有多少行,由于没有重编译,预估均为1行 

 

图2-1

 所以当我们加上重编译的的操作,此时优化器就知道了表变量的具体行数.如图2-2

Code

  1. declare @t table (i int)  
  2. select * from @t option(recompile)-----此时0行预估行数为1行  
  3. insert into @t select 1  
  4. select * from @t  option(recompile)-----此时1行,预估行数为1行  
  5. insert into @t values (2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)  
  6. select * from @t  option(recompile)----此时19行,预估行数为19行  
  7. --....当加入重编译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

  1. declare @i int 
  2. set @i=13  
  3. DECLARE @T TABLE(I INT);  
  4. INSERT INTO @T VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)  
  5. ------表变量中存在个数字  
  6. select * from @T where I < 1  option(recompile) ------20*30% 预估数为6  
  7. select * from @T where I > @i option(recompile) --------20*30%预估数为6  
  8. select * from @T where I like @i  option(recompile) --------20*10% 预估数为2  
  9. select * from @T where I like 1  option(recompile)  --------20*10 预估数为2  
  10. select * from @T where I = @i  option(recompile) --------POWER(20.00000,0.75) 预估数为9.45742  
  11. select * from @T where I = 1  option(recompile)  --------POWER(20.00000,0.75) 预估数为9.45742  
  12.  
  13. insert into @T  
  14. select DatabaseLogID from AdventureWorks2008R2.dbo.DatabaseLog------insert new records  
  15. select * from @T option(recompile) ------------此时数据为行  
  16. 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模拟并发

  1. ----SSMS测试数据  
  2. Use tempdb  
  3. create table t  
  4. ( id int identity,str1 char(8000))----more pages for many records  
  5.  
  6. insert into t select 'a' 
  7. go 100  
  8.  
  9. ----sqlquerystress  
  10. select * into #t  
  11. from t----57s  
  12.  
  13. ----sqlquerystress  
  14. declare @t table 
  15. ( id int,str1 char(8000))  
  16. insert into @t  
  17. select * from t-----1s 

图3-1

通过图3-1可以看出上述情形中临时表简直不堪重负.临时表与表变量到底该如何应用不是看谁比谁的优点多,应视具体情形而定

结语:借用火影忍者中宇智波. 鼬的一句名言:”任何术都是有缺陷的” 同样,在数据库的世界里没有哪项技术是完美无缺的.根据实际的场景,情形,选择合理的实现方式才是我们的初衷.

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

责任编辑:林师授 来源: shanks_gao的博客
相关推荐

2010-09-27 08:52:06

搭建无线局域网

2010-09-15 11:02:36

搭建无线局域网

2011-06-17 10:10:02

2015-03-19 17:03:57

2011-05-05 15:43:29

投影机

2014-07-29 09:23:13

LTEEPC无线

2017-06-16 15:54:53

数据中心自动化IT

2009-03-09 09:16:00

无线局域网无线网络实施

2009-03-02 14:19:33

CiscoWi-Fi通话调度

2009-12-03 10:45:28

2011-05-24 09:49:02

有线无线3G

2022-10-27 10:09:59

东数西算布局

2010-09-16 15:10:48

SQL Server表

2011-03-29 13:22:07

SQL Server临时表表变量

2010-07-22 16:02:29

2010-09-08 17:35:25

SQL表变量

2009-10-22 16:06:57

网络综合布线系统

2017-08-02 16:15:12

2010-05-17 16:31:48

Mysql临时表

2010-10-13 16:25:44

MySQL临时表
点赞
收藏

51CTO技术栈公众号