select 'preorder_history' as tablename,t1.id,t1.login_name,t1.level,t1
from ms_preorder_history mph
inner join (
select id,login_name,level from members
)
) t1 on mph.id=t1.id
where 1=1
union
select 'consume_history' as tablename,t2.id,t2.login_name,t2.group_level,t2
from ms_consume_history mch
inner join (
select id,login_name,level from members
)
) t2 on mch.id=t2.id
where 1=1
假如语句如上,union前后两个查询,inner join里面的表都是一样的,但是不是查询时会执行2次查询,怎么优化只查询一次?
from ms_preorder_history mph
inner join (
select id,login_name,level from members
)
) t1 on mph.id=t1.id
where 1=1
union
select 'consume_history' as tablename,t2.id,t2.login_name,t2.group_level,t2
from ms_consume_history mch
inner join (
select id,login_name,level from members
)
) t2 on mch.id=t2.id
where 1=1
假如语句如上,union前后两个查询,inner join里面的表都是一样的,但是不是查询时会执行2次查询,怎么优化只查询一次?
members的数据大概是80W
因为我们通常在 JOIN 的列上有索引的,索引查找并不意味着一定要 SCAN 一次完整的索引,所以 JOIN 两次并不意味着一定性能就比一次差
UNION 之后再 JOIN,反而可能会因为这个 UNION 操作导致索引失效,从而降低效率
本版等级:T1
结帖率:0%