SQL 查询优化:提高查询速度的技巧
SQL 查询优化:提高查询速度的技巧
本文将对一个复杂的 SQL 查询进行优化,以提高其执行速度。原始查询如下:
with issue as (select cmm_id,string_agg(l3_flag, ';') as l3_flag,
string_agg(care_id, ';') as care_id,
string_agg(care_name, ';') as care_name,
string_agg(ump_id, ';') as issue_id from t_issue_track_summary_info where deleted is null group by cmm_id)
Select collect.id,
COALESCE(issue.l3_flag, '') AS L3flag,
issue.care_id,
COALESCE(issue.care_name, '') AS CareName,
issue.issue_id,
collect.patch_plan,
collect.patch_id,
collect.cur_step,
collect.turns,
collect.cur_test,
....
from t_dts_collect collect
left join t_patch_info patch on collect.patch_id=patch.id
left join t_dts_details details on collect.dts_biz_no = details.dts_biz_no
left join t_dts_details details_parent on details.parent_no = details_parent.dts_biz_no
left join issue on collect.dts_biz_no = issue.cmm_id
where collect.is_deleted = 0
and collect.version_id = '23735486'
and collect.patch_plan in ('CP2118','CP3224')
order by details.create_time DESC
limit 200
要优化这个查询以提高速度,可以考虑以下几个方面:
-
索引优化: 确保所有涉及到连接条件和过滤条件的列都有相应的索引。对于
t_dts_collect表的is_deleted和version_id列,t_patch_info表的id列,t_dts_details表的create_time列都尝试创建索引。 -
子查询优化: 将子查询中的聚合操作移至主查询中。将
issue表的子查询移至主查询的FROM子句中,并使用LEFT JOIN将其连接到t_dts_collect表。这样可以减少子查询的执行次数。
优化后的 SQL 如下:
SELECT collect.id,
COALESCE(issue.l3_flag, '') AS L3flag,
issue.care_id,
COALESCE(issue.care_name, '') AS CareName,
issue.issue_id,
collect.patch_plan,
collect.patch_id,
collect.cur_step,
collect.turns,
collect.cur_test,
...
FROM t_dts_collect collect
LEFT JOIN t_patch_info patch ON collect.patch_id = patch.id
LEFT JOIN t_dts_details details ON collect.dts_biz_no = details.dts_biz_no
LEFT JOIN t_dts_details details_parent ON details.parent_no = details_parent.dts_biz_no
LEFT JOIN (
SELECT cmm_id,
string_agg(l3_flag, ';') AS l3_flag,
string_agg(care_id, ';') AS care_id,
string_agg(care_name, ';') AS care_name,
string_agg(ump_id, ';') AS issue_id
FROM t_issue_track_summary_info
WHERE deleted IS NULL
GROUP BY cmm_id
) issue ON collect.dts_biz_no = issue.cmm_id
WHERE collect.is_deleted = 0
AND collect.version_id = '23735486'
AND collect.patch_plan IN ('CP2118', 'CP3224')
ORDER BY details.create_time DESC
LIMIT 200
请注意,以上是一些建议的优化方法,实际效果可能因数据库结构和数据量的不同而有所差异。建议在进行任何优化之前,先对数据库进行性能分析,确定具体的瓶颈所在。
原文地址: https://www.cveoy.top/t/topic/qsNx 著作权归作者所有。请勿转载和采集!