mysql sql 优化

Question

select * from (
select ru.parent_id as parentId,ru.function_name as name,ru.id ,1 as 
incidenceRelation,ru.sort  
from rmc_sys_permission ru
left join rmc_sys_role_permission ruo 
on ru.id=ruo.permission_id and ruo.role_id=#{roleId}
where ruo.role_id is null  
union all
select ru.parent_id as parentId,ru.function_name as name,ru.id ,2 as incidenceRelation,ru.sort
from rmc_sys_permission ru
left join rmc_sys_role_permission ruo 
on ru.id=ruo.permission_id and ruo.role_id=#{roleId}  
) as r order by r.sort asc

Answer 1

JOIN ON条件代替WHERE
UNION ALL代替UNION

select ru.parent_id as parentId,ru.function_name as name,ru.id ,1 as  
incidenceRelation,ru.sort  
from rmc_sys_permission ru  
left join rmc_sys_role_permission ruo  
on ru.id=ruo.permission_id and ruo.role_id=#{roleId}
where ruo.role_id is null
UNION ALL 
select ru.parent_id as parentId,ru.function_name as name,ru.id ,2 as incidenceRelation,ru.sort
from rmc_sys_permission ru
left join rmc_sys_role_permission ruo
on ru.id=ruo.permission_id and ruo.role_id=#{roleId}  
order by sort asc