最新版本更新
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