mysql select/update where id in(上万个元素）的优化方案：

select/update where id in(上万个元素）的优化方案：

优化前： select
select docId from tab1 where word in (select word from tab1 where docId=123) group by docId limit 1000;
优化后：
select docId from (select word from tab1 where docId=123) as t2 join tab1 t on t.word=t2.word where t2.word is not null GROUP BY docId limit 1000

 

优化前:  update
update table1 t set t.column1=0 WHERE t.id in (SELECT tid FROM table2 b)
优化后：
update table1 t ,table2 b set t.column1=0 where t.id=b.id;

 

 

优化前:  delete a where id in (1千个）
优化后： delete a from a inner join b on a.id=b.id where b.id=123

 

 

--------------------------------------sql 表变量：使用示例----------------------------------------

declare @tempProductAuthUser table
(
SKU varchar(50) primary key
)

insert into @tempProductAuthUser
select distinct SKU from( select sku from a inner join b on a.id=b.id where cum=1) tb

改造前
select * from c
where c.sku in(select sku from a inner join b on a.id=b.id where cum=1)

改造后：
select * from c
where c.sku in(select sku from @tempProductAuthUser)

改造后：
select * from c iner join @tempProductAuthUser d on c.sku=d.sku