一个带有多个子查询的sql,性能非常慢;通过减少子查询改写后性能大幅度提升。
- 原SQL&执行计划:
SELECT
t1.version_no,
t3.instance_id
FROM (SELECT
v.version_no,
v.version_id,
v.message,
v.state,
v.start_time,
v.end_time,
t.task_id,
t.data_freq_unit,
t.team_id
FROM tb_data_version v, tb_task t
WHERE v.task_id = t.task_id AND v.task_id = 5757
) t1 LEFT JOIN (
SELECT version_id, MAX(instance_id) AS mx
FROM tb_instance
WHERE task_id = 5757
GROUP BY version_id
) t2 ON t2.version_id = t1.version_id
LEFT JOIN tb_instance t3 ON t3.instance_id = t2.mx
- 优化后的SQL&执行计划:
SELECT
v.version_no,
i.instance_id
FROM tb_data_version v
JOIN tb_task t ON (v.task_id = t.task_id)
LEFT JOIN tb_instance i ON (
v.version_id = i.version_id
AND v.task_id = i.task_id
and i.instance_id = (
SELECT MAX(instance_id) FROM tb_instance WHERE version_id = v.version_id AND task_id = t.task_id
)
)
WHERE v.task_id = 5757
- 疑惑:怀疑旧SQL中的第二个子查询,对于第一个子查询结果中的每行都会执行一次,但是还是不太确定,如果有类似经验的还请帮忙确认一下!