SQL优化问题 | 达梦技术社区

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。
【DM版本】:
【操作系统】：
【CPU】:
【问题描述】*：多表连接情况下，获取数据时间要2s多，应该如何去调整？附件为完整SQL及执行计划

select * b.trade_union_name,
b.higher_union_name,
c.higher_union_name as superiorUnionName,
d.higher_union_name as districtUnionName,
t.trade_union_name as unionName,
e.login_code, (select case when count(1) > 0 then 1 else 0 end
from union_worker_assembly
where data_status=0 and conference_system in(1,3) and pk_trade_union=b.pk_trade_union) as congressNumber, (select case when count(1) > 0 then 1 else 0 end
from union_open_business
where data_status=0 and pk_trade_union=b.pk_trade_union) as openBusinessNumber, (select case when count(1) > 0 then 1 else 0 end
from union_director_supervisor
where data_status=0 and pk_trade_union=b.pk_trade_union) as directorsSupervisorsNumber,
now(),
b.pk_trade_union,
c1.company_property,
b.pk_union_federation_type
from trade_union b
inner join union_company c1
on b.pk_trade_union = c1.pk_trade_union and c1.data_status = 0
left join trade_union c
on c.pk_trade_union=b.higher_union_uuid and c.data_status=0 and c.union_status=0
left join trade_union d
on d.pk_trade_union=c.higher_union_uuid and d.data_status=0 and d.union_status=0
left join union_cld_member e
on e.pk_trade_union=b.pk_trade_union and e.usertype=20
left join union_federation_correlation f
on f.pk_union_jurisdiction = b.pk_trade_union and f.data_status = 0
left join trade_union t
on f.pk_trade_union = t.pk_trade_union
where b.data_status=0 and b.union_status=0 and b.trade_union_type != 2

data_status 字段只有 0 ，1 两个值；

SQL及执行计划.txt

SELECT 
    b.trade_union_name,
    b.higher_union_name,
    c.higher_union_name AS superiorUnionName,
    d.higher_union_name AS districtUnionName,
    t.trade_union_name AS unionName,
    e.login_code,
    CASE WHEN uwa.cnt > 0 THEN 1 ELSE 0 END AS congressNumber,
    CASE WHEN uob.cnt > 0 THEN 1 ELSE 0 END AS openBusinessNumber,
    CASE WHEN uds.cnt > 0 THEN 1 ELSE 0 END AS directorsSupervisorsNumber,
    NOW() AS current_time,
    b.pk_trade_union,
    c1.company_property,
    b.pk_union_federation_type
FROM 
    trade_union b
INNER JOIN 
    union_company c1 ON b.pk_trade_union = c1.pk_trade_union AND c1.data_status = 0
LEFT JOIN 
    trade_union c ON c.pk_trade_union = b.higher_union_uuid AND c.data_status = 0 AND c.union_status = 0
LEFT JOIN 
    trade_union d ON d.pk_trade_union = c.higher_union_uuid AND d.data_status = 0 AND d.union_status = 0
LEFT JOIN 
    union_cld_member e ON e.pk_trade_union = b.pk_trade_union AND e.usertype = 20
LEFT JOIN 
    union_federation_correlation f ON f.pk_union_jurisdiction = b.pk_trade_union AND f.data_status = 0
LEFT JOIN 
    trade_union t ON f.pk_trade_union = t.pk_trade_union
-- 将多个子查询合并成单个查询，使用联接
LEFT JOIN 
    (SELECT pk_trade_union, COUNT(1) AS cnt
     FROM union_worker_assembly
     WHERE data_status = 0 AND conference_system IN (1, 3)
     GROUP BY pk_trade_union) uwa ON uwa.pk_trade_union = b.pk_trade_union
LEFT JOIN 
    (SELECT pk_trade_union, COUNT(1) AS cnt
     FROM union_open_business
     WHERE data_status = 0
     GROUP BY pk_trade_union) uob ON uob.pk_trade_union = b.pk_trade_union
LEFT JOIN 
    (SELECT pk_trade_union, COUNT(1) AS cnt
     FROM union_director_supervisor
     WHERE data_status = 0
     GROUP BY pk_trade_union) uds ON uds.pk_trade_union = b.pk_trade_union
WHERE 
    b.data_status = 0 
    AND b.union_status = 0 
    AND b.trade_union_type != 2;