如下SQL的优化:
select count(*) from (
select id,name,col1,col2 from t1 where name='xxxx'
union
select id,name ,null as col1,'' as col2 from t2 where id!=1
)
原SQL慢原因:由于union需要过滤 重复记录。所以会有后台的一个group by id,name,col1,col2的操作。
优化:去掉group by。统计出 t1表,t2 表。去除重复记录的次数
优化后:
select sum(a)-sum(dis) from
(
SELECT count(*) as a ,0 as dis FROM t1 a where name='xxxx'
union all
SELECT count(*) as a,0 as dis FROM t2 b WHERE status !=8
union all
select 0 as a, count(1) as dis from t1 a inner join t2 b a.id=b.id and a.name=b.name where a.name='xxxx' and a.col1 is null and a.col2='' and b.status!=8
) as v