PostgreSQL-慢sql查询优化过程-大SQL文件分割-大表分页提交update结果

江节胜-全栈工程师

于 2024-07-24 18:29:06 发布

阅读量669

点赞数 16

文章标签： postgresql 数据库 java

江节胜-胜行天下

本文链接：https://blog.csdn.net/weixin_42933305/article/details/140670098

版权

最新版本更新
https://code.jiangjiesheng.cn/article/345

本文关键词

大SQL文件分割，大表分页提交update结果、执行计划、主键、索引。

重要说明

本文涉及到的分析过程主要基于1.7本地环境，最终结果是否适用于线上环境，暂未验证。

1. 背景

标样核查记录异常 Tab
WHERE中的某些特定查询值在navicat中执行非常缓慢（在页面上调用接口今天未重现，返回比较快）
http://192.168.1.7:8800/envtoday/dist/index.html#/bms/waterMonitor/waterMonitorFacility/waterFacilitiesAbnormalWarn/AbnormalWarnList

2. 整体说明

考虑到在线上环境操作表结构可能会影响用户使用，所以将线上数据同步到本地1.7数据库环境。

2.1 同步数据到本地1.7数据库环境

通过navicat导出线上的t_water_md_run_log_main.sql、t_water_md_run_log_sub.sql非常大，分别有1.36G、4.44G。
将t_water_md_run_log_sub.sql本地环境导入到1.7数据库的过程中，非常缓慢，近2个小时，最终实时的处理进度也不太更新，整体无法导入。t_water_md_run_log_main.sql这个文件本身相对小，且导入前对文件做了一下修改保存，此表导入成功。

考虑要将t_water_md_run_log_sub.sql大文件分割成小文件，在网上未找到免费工具，所以通过Java代码来处理分割，相关代码和编码说明见 https://code.jiangjiesheng.cn/article/342。通过将大文件分割成3个小文件并再次尝试导入，实测导入成功。

2.2 慢SQL

SELECT COUNT
    ( * ) 
  FROM
    (
      SELECT
      main.monitor_id,
      main.equip_name,
      main.pol_id,
      to_char( main.data_time, 'YYYY-MM-DD' ) AS data_time,
      COUNT ( 1 ) AS warning_count 
      FROM
      t_water_md_run_log_main main
      INNER JOIN t_water_md_run_log_sub s ON main."id" = s.main_id 
      WHERE
      s.info_id = 'i13230' 
      AND s.info = '2' 
      AND main.dt = '105' 
      -- AND main.monitor_id IN ( 11111 ) 
      AND main.data_time BETWEEN '2024-06-01 00:00:00'
      AND '2024-07-01 23:59:59' 
      AND s.data_time BETWEEN '2024-06-01 00:00:00'
      AND '2024-07-01 23:59:59' 
      GROUP BY
      main.monitor_id,
      main.pol_id,
      main.equip_name,
      to_char( main.data_time, 'YYYY-MM-DD' ) 
      HAVING
      COUNT ( 1 ) >= 2 
      ORDER BY
      data_time DESC,
      warning_count DESC,
      main.pol_id,
      main.monitor_id 
    ) TOTAL

在线上环境通过navicat执行上述SQL，需要37秒左右，在本地环境执行，也需要1.6秒左右

2.3 线上环境执行计划

"Aggregate  (cost=400.17..400.18 rows=1 width=8) (actual time=75638.849..75638.852 rows=1 loops=1)"
"  Output: count(*)"
"  ->  Sort  (cost=400.15..400.16 rows=1 width=83) (actual time=75638.816..75638.829 rows=175 loops=1)"
"        Output: main.monitor_id, main.equip_name, main.pol_id, (to_char(main.data_time, 'YYYY-MM-DD'::text)), (count(1))"
"        Sort Key: (to_char(main.data_time, 'YYYY-MM-DD'::text)) DESC, (count(1)) DESC, main.pol_id, main.monitor_id"
"        Sort Method: quicksort  Memory: 49kB"
"        ->  GroupAggregate  (cost=400.11..400.14 rows=1 width=83) (actual time=75637.021..75637.605 rows=175 loops=1)"
"              Output: main.monitor_id, main.equip_name, main.pol_id, (to_char(main.data_time, 'YYYY-MM-DD'::text)), count(1)"
"              Group Key: main.monitor_id, main.pol_id, main.equip_name, (to_char(main.data_time, 'YYYY-MM-DD'::text))"
"              Filter: (count(1) >= 2)"
"              Rows Removed by Filter: 690"
"              ->  Sort  (cost=400.11..400.11 rows=1 width=75) (actual time=75637.005..75637.090 rows=1133 loops=1)"
"                    Output: main.monitor_id, main.equip_name, main.pol_id, (to_char(main.data_time, 'YYYY-MM-DD'::text))"
"                    Sort Key: main.monitor_id, main.pol_id, main.equip_name, (to_char(main.data_time, 'YYYY-MM-DD'::text)) DESC"
"                    Sort Method: quicksort  Memory: 207kB"
"                    ->  Nested Loop  (cost=0.86..400.10 rows=1 width=75) (actual time=15.619..75628.505 rows=1133 loops=1)"
"                          Output: main.monitor_id, main.equip_name, main.pol_id, to_char(main.data_time, 'YYYY-MM-DD'::text)"
"                          Join Filter: ((main.id)::text = (s.main_id)::text)"
"                          Rows Removed by Join Filter: 23272953"
"                          ->  Index Scan using _t_water_md_run_log_sub_18426_chu_t_water_md_run_log_sub_infoid on _timescaledb_internal._t_water_md_run_log_sub_18426_chunk s  (cost=0.43..147.71 rows=1 width=20) (actual time=1.582..7.072 rows=1133 loops=1)"
"                                Output: s.main_id"
"                                Index Cond: (((s.info_id)::text = 'i13230'::text) AND ((s.info)::text = '2'::text))"
"                                Filter: ((s.data_time >= '2024-06-01 00:00:00'::timestamp without time zone) AND (s.data_time <= '2024-07-01 23:59:59'::timestamp without time zone))"
"                                Rows Removed by Filter: 2303"
"                          ->  Index Scan using _t_water_md_run_log_main_18425__t_water_md_run_log_main_data_ti on _timescaledb_internal._t_water_md_run_log_main_18425_chunk main  (cost=0.43..249.45 rows=235 width=71) (actual time=0.114..63.455 rows=20542 loops=1133)"
"                                Output: main.monitor_id, main.equip_name, main.pol_id, main.data_time, main.id"
"                                Index Cond: ((main.data_time >= '2024-06-01 00:00:00'::timestamp without time zone) AND (main.data_time <= '2024-07-01 23:59:59'::timestamp without time zone) AND ((main.dt)::text = '105'::text))"
"Planning Time: 1.141 ms"
"Execution Time: 75638.902 ms"

"QUERY PLAN"
"Aggregate  (cost=400.15..400.16 rows=1 width=8) (actual time=34283.887..34283.888 rows=1 loops=1)"
"  Output: count(*)"
"  ->  GroupAggregate  (cost=400.11..400.14 rows=1 width=83) (actual time=34283.539..34283.877 rows=175 loops=1)"
"        Output: main.monitor_id, main.equip_name, main.pol_id, (to_char(main.data_time, 'YYYY-MM-DD'::text)), NULL::bigint"
"        Group Key: main.monitor_id, main.pol_id, main.equip_name, (to_char(main.data_time, 'YYYY-MM-DD'::text))"
"        Filter: (count(1) >= 2)"
"        Rows 
 by Filter: 690"
"        ->  Sort  (cost=400.11..400.11 rows=1 width=75) (actual time=34283.528..34283.567 rows=1133 loops=1)"
"              Output: main.monitor_id, main.equip_name, main.pol_id, (to_char(main.da