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

要优化这个查询以提高速度,可以考虑以下几个方面:

  1. 索引优化: 确保所有涉及到连接条件和过滤条件的列都有相应的索引。对于 t_dts_collect 表的 is_deleted 和 version_id 列,t_patch_info 表的 id 列,t_dts_details 表的 create_time 列都尝试创建索引。

  2. 子查询优化: 将子查询中的聚合操作移至主查询中。将 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

请注意,以上是一些建议的优化方法,实际效果可能因数据库结构和数据量的不同而有所差异。建议在进行任何优化之前,先对数据库进行性能分析,确定具体的瓶颈所在。

SQL 查询优化:提高查询速度的技巧

原文地址: https://www.cveoy.top/t/topic/qsNx 著作权归作者所有。请勿转载和采集!

免费AI点我,无需注册和登录