sql查询同比、环比的优化

Question

mysql数据库进行环比、同比查询应该如何查询才不会显的臃肿，而且这后期数据量增大后对于查询性能也是要有要求的，
目前是通过左关联进行查询环比跟同比，sql如下：

select t.`name` area_name,ifnull(t1.burnWasteWeight,0),ifnull(t1.plantWasteWeight,0),ifnull(t1.foodWasteWeight,0),
        ifnull(t2.burnWasteWeight,0),ifnull(t2.plantWasteWeight,0),ifnull(t2.foodWasteWeight,0),
        ifnull(t3.burnWasteWeight,0),ifnull(t3.plantWasteWeight,0),ifnull(t3.foodWasteWeight,0)
 from r_std_city_code t left join (
    select b.adcode,
            truncate(SUM(CASE WHEN rrri.recycling_name = '焚烧垃圾' THEN weight ELSE 0 END) / 1000, 2) AS burnWasteWeight,
            truncate(SUM(CASE WHEN rrri.recycling_name = '填埋垃圾' THEN weight ELSE 0 END) / 1000, 2) AS plantWasteWeight,
            truncate(SUM(CASE WHEN rrri.recycling_name = '餐厨垃圾' THEN weight ELSE 0 END) / 1000, 2) AS foodWasteWeight
    from r_sub_order b
    LEFT JOIN r_order ro on ro.order_id=b.order_id 
    LEFT JOIN r_recy_resource_info rrri on b.resource_id=rrri.id
    where date_format( ro.census_time, '%Y-%m' ) ='2021-10'
    group by b.adcode ) t1 on t.adcode=t1.adcode
LEFT JOIN
(
    select b.adcode,
            truncate(SUM(CASE WHEN rrri.recycling_name = '焚烧垃圾' THEN weight ELSE 0 END) / 1000, 2) AS burnWasteWeight,
            truncate(SUM(CASE WHEN rrri.recycling_name = '填埋垃圾' THEN weight ELSE 0 END) / 1000, 2) AS plantWasteWeight,
            truncate(SUM(CASE WHEN rrri.recycling_name = '餐厨垃圾' THEN weight ELSE 0 END) / 1000, 2) AS foodWasteWeight
    from r_sub_order b 
    LEFT JOIN r_order ro on ro.order_id=b.order_id 
    LEFT JOIN r_recy_resource_info rrri on b.resource_id=rrri.id
    where date_format( ro.census_time, '%Y-%m' ) ='2021-09'
    group by b.adcode
) t2 on t.adcode=t2.adcode