数据处理优化

Author Avatar
Marmot 12月 25, 2018
  • 在其它设备中阅读本文章

最近都在处理数据优化方面的问题

感想

更优秀的查询策略可以几十上百倍的提高效率

1，处理查询过慢的情况（50+s➡120ms）

起因1:因为同一个设备可能多次属于一个系统(子系统)，旧代码是查询出所有设备，然后根据设备属于的子系统，去递归出根系统然后分类

解决方案1：

采用反向查询的策略，根据根系统查找设备，然后递归子系统查找设备，然后去重返回

起因2：如果设备数量增多，递归的多次查询设备表，由于设备表本身字段旧多，多次查询的每一次都会很慢，加上查询出来之后还要去重，会指数级别的变慢

解决方案2：

递归查询系统及子系统的id，然后根据系统id去关系表中去重查询（select DISTINCT equipment_id）设备id,再更具设备id，查询字段较多的设备表，
能较大的提高性能，同时能做到数据库直接分页

2，处理csv导出过慢并因为脏数据存在缺失的情况（2个月数据查询超时➡14s[6000±QPS]）

起因1：使用了存储过程，处理缺失数据，然后查询出来的结果和天气时间不是每一分钟都有数据，存在数据缺失，并且查询时间太长

解决方案1：

使用PREPARE预编译sql，然后查询（后来想可能是weather表拖后腿），然后每分钟查询一次，数据稳定了，但是依然很慢，反正我不能接受的慢，
顺便复习了预编译

1
2
3
4
5
6
7
8
9
PREPARE q(text) as
  select tb_1.air_course_pressure,
         tb_1.air_course_pressure,
         tb_1.timestamp
  FROM tb_1
  where tb_1.timestamp >= '2018-12-19 00:00:00'::timestamp
  ORDER BY tb_1.timestamp
  limit 1;
execute q('2018-12-19 00:00:00');

  1. Prepared语句只在session的整个生命周期中存在，一旦session结束，Prepared语句也不存在了。如果下次再使用需重新创建。
  2. Prepared语句不能在多个并发的client中共有。
  3. prepared语句可以通过DEALLOCATE命令清除。
  4. 当前session的prepared语句：select * from pg_prepared_statements;

    解决方案2：

    和涂老师讨论了
    redis缓存方案（工作量大，并且内存占用会越来越大）
    重新整理数据干净的数据库（人手不够)
    涂老师建议尝试一下 7.2.1.5. LATERAL Subqueries，当中也遇到了很多问题

    问题1.生成每分钟的时间序列

    1
    2
    3
    4
    5
    SELECT date_trunc('minute', mi)::timestamp
    FROM generate_series(
      '2018-12-24 00:00:00'::timestamp,
      '2018-12-24 00:10:00'::timestamp,
      '1 minutes'::interval) mi;

问题2.生成时间序列之后发现使用INNER JOIN LATERAL需要把时间序列用来做比较

  1. 使用虚拟表处理
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    -- 使用虚拟表
    CREATE TEMPORARY TABLE time_table(time_series TIMESTAMP NOT NULL);
    INSERT INTO time_table
    VALUES (date_trunc('minute', generate_series('2018-12-19 00:00:00'::timestamp, '2018-12-20 00:10:00'::timestamp,'1 minutes'::interval)));
    select time_table.time_seriesfrom time_table;
    --查询
    SELECT time_table.time_series, data.*
    from time_table
           INNER JOIN LATERAL
      (select *
       FROM tb_1
       where tb_1.timestamp >= time_table.time_series
       ORDER BY tb_1.timestamp
       limit 1) data ON TRUE;
    ```  
    2. 直接使用`mi.timestamp`
    ```sql
    -- 直接查询
    SELECT to_char(mi.timestamp + interval '8 hour', 'YYYY-MM-DD HH24:MI:SS'),data1.*,data2.*
    FROM generate_series(
             '2018-12-19 00:00:00'::timestamp,
             '2018-12-20 00:10:00'::timestamp,
             '1 minutes'::interval) mi
           INNER JOIN LATERAL
      (select t1.*
       from tb_1 t1
       where t1.timestamp >= mi.timestamp
       order by t1.timestamp
       limit 1) data1 on true
           INNER JOIN LATERAL
      (select t2.* FROM bms_service_weather as t2 where t2.time >= mi.timestamp order by t2.time limit 1) data2
                      on true;

问题3：发现设备表查询很快，天气表查询慢到令人发指，问了一下，涂老师，设备表采用了timescaledb的时序索引,给天气表也加上索引

关于timescaledb(postgres时序数据扩展型数据库)

1
2
3
4
5
6
7
8
--查看 pg 扩展
select * from pg_extension;
--时序分表
SELECT create_hypertable('bms_service_weather', 'time');
--时许索引
create index bms_service_weather_time_idx on bms_service_weather (time desc);
--引用旧表
INSERT INTO bms_service_weather_new(SELECT * FROM bms_service_weather);

问题4：pg链接过多

1
2
3
4
--查看空闲链接
select * from pg_stat_activity where state = 'idle';
--清理空闲链接
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle';