SQL中优化IN的几种方法_MsSqlserver

一、前言

最近对一个存储过程进行优化，这里记录一下in在sqlserver中的一些优化和情况。

二、能代替in的几种方法效率对比

所以 union all其实是效率最好的，也就是 =any()

三、实际写法

3.1 in

select * from tablea where name in ('a','b','c');

3.2 or

select * from tablea where name='a' or name='b' or name='c';

3.3 exists

select *
from tablea a
where exists (
select 1
from ( values  ('a'),('b'),('c')) as tmp(name)

3.4 join

-- sql server/postgresql/mysql 8.0+ 语法
select tablea.*
from tablea
join ( values  ('a'),('b'),('c')) as tmp(name)
on tablea.name = tmp.name

3.4 union all

select * from tablea  where name='a'   union all
select * from tablea  where name='b'   union all
select * from tablea  where name='c '  
或
select * from tablea where name = any('a','b','c');

三、存储过程实例

实现

 select  * from tablea where
   a.name in  ('a','b','c') 
   and a.types in ('1','3','7','8') 
   and a.age in ('33','35','39')

存储过程

declare  
@name  varchar(100)='a,b,c',
@type  varchar(100)='1,3,7,8',
@age  varchar(100)='33,35,39'

--列转行
 if object_id('tempdb..#mytemp') is not null    
 begin    
  drop table #mytemp 
 end
   
 select * into #mytemptable1 from     
 (    
  select b.id,b.typeid      
  from   (      
     select [value] = convert(xml, '<v>' + replace(@name,',', '</v><v>') + '</v>')      
    ) a      
    outer apply(      
   select id = n.v.value('.', 'nvarchar(100)'),typeid=1    
   from   a.[value].nodes('/v') n(v)      
  ) b      
  union    
  select d.id,d.typeid      
  from   (      
     select [value] = convert(xml, '<v>' + replace(@type,',', '</v><v>') + '</v>')      
    ) c      
    outer apply(      
   select id = n.v.value('.', 'nvarchar(100)'),typeid=2    
   from   c.[value].nodes('/v') n(v)      
  ) d 
   union    
  select d.id,d.typeid      
  from   (      
     select [value] = convert(xml, '<v>' + replace(@age,',', '</v><v>') + '</v>')      
    ) e      
    outer apply(      
   select id = n.v.value('.', 'nvarchar(100)'),typeid=3    
   from   c.[value].nodes('/v') n(v)      
  )f
 ) as t   
--exists 子查询
 select * from tablea a where 
   a.name in  (select id from #mytemp where typeid=1) 
   and a.types in (select id from #mytemp where typeid=2) 
   and a.age in (select id from #mytemp where typeid=3)

四、实际情况

情况1

有一个很奇怪的情况，当@name='a’时，存储过程中

select * from tablea a where 
   a.name in  (select id from #mytemp where typeid=1) 
   and a.types in (select id from #mytemp where typeid=2) 
   and a.age in (select id from #mytemp where typeid=3)

a.name... 比 a.name='a' 或 a.name in ('a') 查询时间多了一倍。 types、age 确没影响

改成join后，得到解决：

select * from tablea a 
  join #mytemptable1 b 
		  on b.id = a.dataareaid 
		 and b.typeid=1
		where
   a.types in (select id from #mytemp where typeid=2) 
   and a.age in (select id from #mytemp where typeid=3)

情况2

在in 不走索引的情况下，union all 效率最高
再改进：

select * from tablea a 
  join #mytemptable1 b 
		  on b.id = a.dataareaid 
		 and b.typeid=1
		where
   a.types = any(select id from #mytemp where typeid=2) 
   and a.age = any(select id from #mytemp where typeid=3)