sqlserver的表变量在没有预估偏差的情况下，与物理表可join产生的性能问题

众所周知，在sqlserver中，表变量最大的特性之一就是没有统计信息，无法较为准备预估其数据分布情况，因此不适合参与较为复杂的sql运算。
当sql相对简单的时候，使用表变量，在某些场景下，即便是对表变量的预估没有产生偏差的情况下，仍旧会有问题。
sqlserver的优化引擎对于表变量的支持十分不友好，再次对表变量的使用产生了警惕。

 

测试环境搭建

理搭建一个简单的测试环境，来验证本文的想要表达的主题，
测试表testtablevariable 上有keycode1 ~keycode5 5个字段，分别创建非聚集索引，
对于数据分布，刻意设计出当前这种场景：keycode1 ~keycode5的字段值，分别趋于稀疏（非空值的越来越少，null值越来越多）
如下，写入100w行数据，就可以出来下面要表达的效果了。

create table testtablevariable
(
    id int identity(1,1),
    keycode1 varchar(10),
    keycode2 varchar(10),
    keycode3 varchar(10),
    keycode4 varchar(10),
    keycode5 varchar(10),
    createdate datetime
)

alter table testtablevariable
add constraint pk_testtablevariable primary key(id) 


create index idx_keycode1 on testtablevariable(keycode1)
create index idx_keycode2 on testtablevariable(keycode2)
create index idx_keycode3 on testtablevariable(keycode3)
create index idx_keycode4 on testtablevariable(keycode4)
create index idx_keycode5 on testtablevariable(keycode5)

insert into testtablevariable(keycode1,createdate) values (concat('xx',cast(rand()*1000000 as int)),getdate())
go 1000000



update testtablevariable set keycode2 = keycode1 where id%10 = 0
update testtablevariable set keycode3 = keycode1 where id%1000 = 0
update testtablevariable set keycode4 = keycode1 where id%10000= 0
update testtablevariable set keycode5 = keycode1 where id%100000 = 0
go

 

问题重现

对于普通的查询，找一个keycode1 ~keycode5均有值的条件进行查询，执行计划都在预期之中，均可以用到索引，不过多表述

select * from testtablevariable where keycode1 = 'xx156876'
select * from testtablevariable where keycode2 = 'xx156876'
select * from testtablevariable where keycode3 = 'xx156876'
select * from testtablevariable where keycode4 = 'xx156876'
select * from testtablevariable where keycode5 = 'xx156876'

下面将查询条件写入一张表变，让表变量与物理表testtablevariable进行join
如下语句，分别用keycode1 ~keycode5进行查询，对于非空值分布相对较多的keycode1 ~keycode3，做查询的时候，执行计划也在预期之中（索引查找）

 

从非空值分布越来越少的keycode4开始，执行计划开始变成非预期的索引查找，变成了表扫描

keycode5依旧是非预期的索引查找，也是表扫描

 

这里不是提出类似问题的解决办法的，当然解决办法也比较简单，
1，添加一个不影响逻辑的条件，相当于简单地改写sql，如下增加where a.keycode5 is not null 筛选条件，因为null值不等于任何值，包括null值，因此增加这个条件不会影响这个sql的逻辑
2，将表变量的数据写入临时表，让临时表与测试表join，其他不做任何修改
两种方式都可以达到index seek的效果。

declare @tb table ( keycode varchar(10))
insert into @tb values ('xx156876')
select * from testtablevariable a inner join @tb b on a.keycode5 = b.keycode
where a.keycode5 is not null
go

declare @tb table ( keycode varchar(10))
insert into @tb values ('xx156876')
select * into #t from @tb
select * from testtablevariable a inner join #t b on a.keycode5 = b.keycode
go

以下是两者的执行计划，都是index seek

以上是解决办法，暂不过多表述。

 

存在的疑问

问题就在于：
即便是表变量没有统计信息，sqlserver默认情况下总是会预估为1行（不加任何查询提示），既然预估为1行，在当前情况下也是准确的，不认为是预估出现偏差导致执行计划出现非最优。
对于临时表，同样是1行数据，来驱动物理表testtablevariable，就可以正常使用到index seek，而表变量不行？
再就是，对于testtablevariable表上的统计信息，经过几个sql查询过后，触发了统计信息的更新，统计信息也相对准确地预估到了999999行为null，1行是一个特定的值xx156876）

1，对于物理表testtablevariable与表变量的join，由于null值跟任何值对比都是没有结果的，换句话说就是，不管表变量里的数据量有多少，按照统计信息中的预估，这个查询对于testtablevariable这个表来说，最多只有1行数据（统计信息中的那个非null）的数据参与查询运算
2，对于表变量，既然预估为1行，哪有为什么不使用索引查找的方式，就算是用不到索引查找，join双方，按照预估，都只有一行数据参与运算的情况下，为什么竟然要选择hash join？

表变量参数join的时候，优化器为什么连这么一个简单的推断逻辑都做不到，并没有非常复杂的逻辑，或者说数据分布异常的情况在里面，最终选择了最差的执行计划进行运算。
反观临时表，用临时表join的情况下，一切都回归到预期的索引查找，可否认为，sqlserver对表变量的join或者说运算，支持的非常不友好（2014~2016均没有改善）。

 

后面怀疑是不是keycode5上的统计信息取样百分比不够大，造成的执行计划错误，尝试100%取样

继续测试，问题依旧

当前这个case，并不是那种经典的，因为对表变量预估偏差造成的执行计划错误，暂时也无法理解，sqlserver为什么会对表变量参数参与的join，在当前这种case中，采用如此保守的执行方式。

 

越来越多的case证明，在sqlserver中使用表变量参与join，就好比是一颗定时炸弹，随时可以引爆你的系统，看来要慎重。