[大数据]连表查询SQL优化

现有alarm（历史故障?某时刻某设备发生故障时 记录一条）

fan（所有设备 点位历史数据表每秒记录一条）两张表

需求：某一段时间内 故障发生时 某点位的数据

通过需求了解查询是要将两张表联合查询 当写出sql后才发现事情并没有那么简单

fan表中数据量过大 查询时间跨度越大 连表查询的条数越大 一条86400条 一个月数据量就是86400*30*设备台数*一个月故障条数

我选择的解决方法是做伪表 连表前后带上查询条件

数据库：clickhouse

10天alarm表数据：2000条

10天fan表数据：86w条

未优化sql：

select 字段.. from alarm a
left join fan f 
on fan.id = a.fan_id and a.begin_time =fan.create_time
where begin_time between '2022-08-01 00:00:00' and '2022-08-10 00:00:00';

耗时：800ms左右

这条sql是先联合两张全部数据量 再根据时间查询 导致两张表全部数据量结合 fan表数据量很大 应该先限制fan表时间后 再连表查 如果查询的mysql数据库 待查好几十秒了

优化第一次：

select 字段.. from alarm a 
left join 
(SELECT 字段.. from fan where create_time between '2022-08-01 00:00:00' and '2022-08-10 00:00:00')fan
on fan.id = ad.fan_id and ad.begin_time =fan.create_time
where begin_time between '2022-08-01 00:00:00' and '2022-08-10 00:00:00';

耗时：700ms左右

优化一次后 fan表中全表数据已经限制到了需要查询的时间 按照这个逻辑 我们把alarm表也限制在查询时间内

最终sql：

select 字段.. from 
(SELECT 字段.. from alarm where begin_time between '2022-08-01 00:00:00' and '2022-08-10 00:00:00' order by begin_time desc)a
LEFT join (SELECT 字段.. from fan where create_time between '2022-08-01 00:00:00' and '2022-08-10 00:00:00')fan 
on fan.id = a.fan_id and a.begin_time =fan.create_time;

耗时：300ms左右

最后优化的sql是先查询出alarm，fan表这个时间段的数据 再进行连表查 相比两种全表关联 大大减少了查询条数和等待时间

了解查询优化后 现在还有个需求 展示最近时间一百条故障时的点位数据

未优化sql：

select 字段.. from alarm a
left join fan f 
on fan.id = a.fan_id and a.begin_time =fan.create_time
order by begin_time desc limit 100

没有了时间限制 怎么优化呢 因为只展示一百条故障数据

1 先用limit查询出100条故障

2 获取到这100条故障中最大时间和最小时间 拼接成条件限制fan表

优化后sql：

select 字段.. from 
(
SELECT 字段.. from alarm order by begin_time desc limit 100
)al LEFT join (
SELECT 字段.. from fan 
where create_time between
(select min(begin_time) from (select begin_time from alarm order by begin_time desc limit 100)) and 
(select max(begin_time) from (select begin_time from alarm order by begin_time desc limit 100)) 
)fan 
on fan.id = al.fan_id and al.begin_time =fan.create_time;

//先获取最近100条故障发生时的最大和最小时间
select max(begin_time) from (select begin_time from alarm order by begin_time desc limit 100);
select min(begin_time) from (select begin_time from alarm order by begin_time desc limit 100);

总结：

当遇到连表查询时 先将条件拼接在连表查询前 避免全表关联

最后 连表优化sql我就讲完啦?大家根据需求调整代码

有遇到什么问题可以留言告诉我哦 欢迎评论区讨论🥰