现有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我就讲完啦 大家根据需求调整代码
有遇到什么问题可以留言告诉我哦 欢迎评论区讨论🥰