记一次sql优化

最新推荐文章于 2024-05-19 13:01:37 发布

weixin_44200952

最新推荐文章于 2024-05-19 13:01:37 发布

阅读量39

点赞数

文章标签： sql 数据库 sqlserver

本文链接：https://blog.csdn.net/weixin_44200952/article/details/129518258

版权

原sql
SELECT
t1.id,
t1.column1,
t1.column2,
t1.column3,
array_to_string(ARRAY (SELECT tt.column1||‘：’||tt.column2 FROM table2 tt WHERE tt.foreign_key=t1.id),‘；’) as column4,
array_to_string(ARRAY (SELECT tt.column1||‘,’||tt.column2 FROM table3 tt WHERE tt.foreign_key=info.id),‘；’) as column5,
array_to_string(ARRAY (SELECT tt.column3||‘：’||tt.column2 FROM table4 tt WHERE tt.foreign_key=info.id),‘；’) as column6
FROM table1 t1

优化后sql
SELECT
t1.id,
t1.column1,
t1.column2,
t1.column3,
t2.column4,
t3.column5,
t4.column6
FROM table1 t1
left join (select tt.foreign_key,string_agg(tt.column1||‘：’||tt.column2,‘；’) as column4 from table2 tt group by tt.foreign_key) t2 on t1.id = t2.foreign_key
left join (select tt.foreign_key,string_agg(tt.column1||‘：’||tt.column2,‘；’) as column4 from table2 tt group by tt.foreign_key) t3 on t1.id = t3.foreign_key
left join (select tt.foreign_key,string_agg(tt.column1||‘：’||tt.column2,‘；’) as column4 from table2 tt group by tt.foreign_key) t4 on t1.id = t4.foreign_key

优化前1W条数据查询需要200秒
优化后1W条数据查询只需1秒