PostgreSQL 分区表——范围分区SQL实践
PostgreSQL 分区表——范围分区SQL实践
- 1、环境准备
- 1-1、新增原始表
- 1-2、执行脚本新增2400w行
- 1-3、创建pg分区表-分区键为创建时间
- 1-4、创建24年所有分区
- 1-5、设置默认分区（兜底用）
- 1-6、迁移数据
- 1-7、创建分区表索引
- 2、SQL增删改查测试
- 2-1、查询速度对比
- 2-1-1、查询总数量时间对比
- 查询总数量反直觉分析
- 2-2-1、带上分区键查询对比
- 带上分区键在分表区间内查询
- 带上分区键跨分表区间查询
- 2-2、删除速度对比
- 2-2-1、全量删除TRUNCATE
- 2-2-2、删除某个月数据
- 原始表DELETE 145.372s
- 分区表DELETE（未指定分区）225.896s
- 分区表DELETE（指定分区）242.770s
- 分区表TRUNCATE（指定分区）13.156s
- 2-2-3、删除一条数据
- 原始表 平均8.009s
- 分区表（未带上分区键） 平均7.858s
- 分区表（带上分区键） 平均1.567s
- 2-3、写入速度对比
- 2-2-1、全量插入2400w数据，均匀分布在每个月
- 原始表 3387.520s
- 分区表 4988.111s
- 2-2-2、指定某个月插入10w数据
- 原始表 63.572s
- 分区表（未指定分区） 58.759s
- 分区表（指定分区） 48.098s
- 2-4、更新速度对比
- 2-4-1、修改指定月份的处理人信息
- 原始表 538.133s
- 分区表（未指定分区） 1252.286s
- 分区表（指定分区） 1252.286s
- 2-4-1、根据主键修处理人信息
- 原始表 12.597s
- 分区表（未带上分区键） 10.870s
- 分区表（带上分区键） 0.438s
- 3、总结
1、环境准备
1-1、新增原始表
CREATE TABLE t_common_work_order_log ( work_order_log_id varchar(32) COLLATE pg_catalog.default NOT NULL, operation_type int2, work_order_id varchar(32) COLLATE pg_catalog.default, work_order_name varchar(100) COLLATE pg_catalog.default, work_order_type int4, biz_location_id int4, planned_completion_time timestamp(6), actual_completion_time timestamp(6), handle_user_account varchar(100) COLLATE pg_catalog.default, handle_user_name varchar(100) COLLATE pg_catalog.default, create_time timestamp(0), create_by_uuid varchar(32) COLLATE pg_catalog.default, create_by_account varchar(32) COLLATE pg_catalog.default, create_by_name varchar(100) COLLATE pg_catalog.default, last_update_time timestamp(0), last_update_uuid varchar(32) COLLATE pg_catalog.default, last_update_account varchar(32) COLLATE pg_catalog.default, last_update_name varchar(100) COLLATE pg_catalog.default, biz_attribute_1 varchar(255) COLLATE pg_catalog.default, biz_attribute_2 varchar(255) COLLATE pg_catalog.default, biz_attribute_3 varchar(255) COLLATE pg_catalog.default, biz_attribute_4 varchar(255) COLLATE pg_catalog.default, biz_attribute_5 varchar(255) COLLATE pg_catalog.default, biz_attribute_6 varchar(255) COLLATE pg_catalog.default, biz_attribute_7 varchar(255) COLLATE pg_catalog.default, biz_attribute_8 varchar(255) COLLATE pg_catalog.default, biz_attribute_9 varchar(255) COLLATE pg_catalog.default, biz_attribute_10 varchar(255) COLLATE pg_catalog.default, biz_attribute_11 varchar(255) COLLATE pg_catalog.default, biz_attribute_12 varchar(255) COLLATE pg_catalog.default, biz_attribute_13 varchar(255) COLLATE pg_catalog.default, biz_attribute_14 varchar(255) COLLATE pg_catalog.default, biz_attribute_15 varchar(255) COLLATE pg_catalog.default, biz_attribute_16 varchar(255) COLLATE pg_catalog.default, biz_attribute_17 varchar(255) COLLATE pg_catalog.default, biz_attribute_18 varchar(255) COLLATE pg_catalog.default, biz_attribute_19 varchar(255) COLLATE pg_catalog.default, biz_attribute_20 varchar(255) COLLATE pg_catalog.default ) ; ALTER TABLE t_common_work_order_log OWNER TO postgres; CREATE INDEX order_log_biz_location_id_index ON t_common_work_order_log USING btree ( biz_location_id pg_catalog.int4_ops ASC NULLS LAST ); CREATE INDEX order_log_create_by_account_index ON t_common_work_order_log USING btree ( create_by_account COLLATE pg_catalog.default pg_catalog.text_ops ASC NULLS LAST ); CREATE INDEX order_log_create_time_index ON t_common_work_order_log USING btree ( create_time pg_catalog.timestamp_ops ASC NULLS LAST ); CREATE INDEX order_log_handle_user_account_index ON t_common_work_order_log USING btree ( handle_user_account COLLATE pg_catalog.default pg_catalog.text_ops ASC NULLS LAST ); CREATE INDEX order_log_last_update_account_index ON t_common_work_order_log USING btree ( last_update_account COLLATE pg_catalog.default pg_catalog.text_ops ASC NULLS LAST ); CREATE INDEX order_log_last_update_time_index ON t_common_work_order_log USING btree ( last_update_time pg_catalog.timestamp_ops ASC NULLS LAST ); CREATE INDEX order_log_operation_type_index ON t_common_work_order_log USING btree ( operation_type pg_catalog.int2_ops ASC NULLS LAST ); CREATE INDEX order_log_work_order_name_index ON t_common_work_order_log USING btree ( work_order_name COLLATE pg_catalog.default pg_catalog.text_ops ASC NULLS LAST ); CREATE INDEX order_log_work_order_type_index ON t_common_work_order_log USING btree ( work_order_type pg_catalog.int4_ops ASC NULLS LAST ); COMMENT ON COLUMN t_common_work_order_log.work_order_log_id IS '工单日志ID'; COMMENT ON COLUMN t_common_work_order_log.operation_type IS '操作类型'; COMMENT ON COLUMN t_common_work_order_log.work_order_id IS '工单ID'; COMMENT ON COLUMN t_common_work_order_log.work_order_name IS '工单名称'; COMMENT ON COLUMN t_common_work_order_log.work_order_type IS '工单类型'; COMMENT ON COLUMN t_common_work_order_log.biz_location_id IS '业务位置ID'; COMMENT ON COLUMN t_common_work_order_log.planned_completion_time IS '计划完成时间'; COMMENT ON COLUMN t_common_work_order_log.actual_completion_time IS '实际完成时间'; COMMENT ON COLUMN t_common_work_order_log.handle_user_account IS '处理人账号'; COMMENT ON COLUMN t_common_work_order_log.handle_user_name IS '处理人名称'; COMMENT ON COLUMN t_common_work_order_log.create_time IS '创建时间'; COMMENT ON COLUMN t_common_work_order_log.create_by_uuid IS '创建人uuid'; COMMENT ON COLUMN t_common_work_order_log.create_by_account IS '创建人账号'; COMMENT ON COLUMN t_common_work_order_log.create_by_name IS '创建人名称'; COMMENT ON COLUMN t_common_work_order_log.last_update_time IS '最后更新时间'; COMMENT ON COLUMN t_common_work_order_log.last_update_uuid IS '最后跟新人uuid'; COMMENT ON COLUMN t_common_work_order_log.last_update_account IS '最后更新人账号'; COMMENT ON COLUMN t_common_work_order_log.last_update_name IS '最后更新人名称'; COMMENT ON COLUMN t_common_work_order_log.biz_attribute_1 IS '业务属性1'; COMMENT ON COLUMN t_common_work_order_log.biz_attribute_2 IS '业务属性2'; COMMENT ON COLUMN t_common_work_order_log.biz_attribute_3 IS '业务属性3'; COMMENT ON COLUMN t_common_work_order_log.biz_attribute_4 IS '业务属性4'; COMMENT ON COLUMN t_common_work_order_log.biz_attribute_5 IS '业务属性5'; COMMENT ON COLUMN t_common_work_order_log.biz_attribute_6 IS '业务属性6'; COMMENT ON COLUMN t_common_work_order_log.biz_attribute_7 IS '业务属性7'; COMMENT ON COLUMN t_common_work_order_log.biz_attribute_8 IS '业务属性8'; COMMENT ON COLUMN t_common_work_order_log.biz_attribute_9 IS '业务属性9'; COMMENT ON COLUMN t_common_work_order_log.biz_attribute_10 IS '业务属性10'; COMMENT ON COLUMN t_common_work_order_log.biz_attribute_11 IS '业务属性11'; COMMENT ON COLUMN t_common_work_order_log.biz_attribute_12 IS '业务属性12'; COMMENT ON COLUMN t_common_work_order_log.biz_attribute_13 IS '业务属性13'; COMMENT ON COLUMN t_common_work_order_log.biz_attribute_14 IS '业务属性14'; COMMENT ON COLUMN t_common_work_order_log.biz_attribute_15 IS '业务属性15'; COMMENT ON COLUMN t_common_work_order_log.biz_attribute_16 IS '业务属性16'; COMMENT ON COLUMN t_common_work_order_log.biz_attribute_17 IS '业务属性17'; COMMENT ON COLUMN t_common_work_order_log.biz_attribute_18 IS '业务属性18'; COMMENT ON COLUMN t_common_work_order_log.biz_attribute_19 IS '业务属性19'; COMMENT ON COLUMN t_common_work_order_log.biz_attribute_20 IS '业务属性20'; COMMENT ON TABLE t_common_work_order_log IS '某工单操作日志表'; COMMENT ON INDEX order_log_biz_location_id_index IS '位置索引'; COMMENT ON INDEX order_log_create_by_account_index IS '创建人索引'; COMMENT ON INDEX order_log_create_time_index IS '创建时间索引'; COMMENT ON INDEX order_log_handle_user_account_index IS '处理人索引'; COMMENT ON INDEX order_log_last_update_account_index IS '最后更新人索引'; COMMENT ON INDEX order_log_last_update_time_index IS '最后更新时间索引'; COMMENT ON INDEX order_log_operation_type_index IS '操作类型索引'; COMMENT ON INDEX order_log_work_order_name_index IS '工单名称索引'; COMMENT ON INDEX order_log_work_order_type_index IS '工单类型索引';
1-2、执行脚本新增2400w行
-- 1. 首先创建随机中文名函数 CREATE OR REPLACE FUNCTION random_chinese_name() RETURNS varchar(100) AS $$ DECLARE surnames varchar[] := ARRAY['张','王','李','赵','刘','陈','杨','黄','吴','周','徐','孙','马','朱','胡','林','郭','何','高','罗']; givennames varchar[] := ARRAY['伟','芳','娜','秀英','敏','静','丽','强','磊','军','洋','勇','艳','杰','娟','涛','明','超','秀兰','霞']; BEGIN RETURN surnames[floor(random()*array_length(surnames,1)+1)] || givennames[floor(random()*array_length(givennames,1)+1)]; END; $$ LANGUAGE plpgsql; -- 2. 执行数据生成DO块 DO $$ DECLARE month_start timestamp; month_end timestamp; month_intervals interval[] := ARRAY[ interval '0 months', interval '1 month', interval '2 months', interval '3 months', interval '4 months', interval '5 months', interval '6 months', interval '7 months', interval '8 months', interval '9 months', interval '10 months', interval '11 months' ]; month_interval interval; BEGIN -- 设置维护内存提高性能 SET LOCAL maintenance_work_mem = '1GB'; SET LOCAL work_mem = '256MB'; -- 为每个月生成200万条数据 FOREACH month_interval IN ARRAY month_intervals LOOP month_start := (date '2024-01-01' + month_interval)::timestamp; month_end := (date '2024-01-01' + month_interval + interval '1 month')::timestamp; RAISE NOTICE 'Generating data for month: % (%)', to_char(month_start, 'YYYY-MM'), to_char(clock_timestamp(), 'YYYY-MM-DD HH24:MI:SS'); -- 每月插入200万条 INSERT INTO t_common_work_order_log ( work_order_log_id, operation_type, work_order_id, work_order_name, work_order_type, biz_location_id, planned_completion_time, actual_completion_time, handle_user_account, handle_user_name, create_time, create_by_uuid, create_by_account, create_by_name, last_update_time, last_update_uuid, last_update_account, last_update_name, biz_attribute_1, biz_attribute_2, biz_attribute_3, biz_attribute_4, biz_attribute_5, biz_attribute_6, biz_attribute_7, biz_attribute_8, biz_attribute_9, biz_attribute_10, biz_attribute_11, biz_attribute_12, biz_attribute_13, biz_attribute_14, biz_attribute_15, biz_attribute_16, biz_attribute_17, biz_attribute_18, biz_attribute_19, biz_attribute_20 ) SELECT substr(md5(random()::text || i::text), 1, 32), floor(random() * 5 + 1)::int2, 'WO' || (2000000*extract(month FROM month_start) + i)::varchar, (ARRAY['设备维修','软件升级','网络故障','数据迁移','常规维护'])[floor(random()*5+1)], floor(random() * 10 + 1)::int4, floor(random() * 100 + 1)::int4, month_start + random() * (month_end - month_start) + (interval '1 day' * floor(random() * 3 + 1)), CASE WHEN random() > 0.2 THEN month_start + random() * (month_end - month_start) + (interval '1 hour' * floor(random() * 4 - 2)) ELSE NULL END, 'user' || floor(random() * 100 + 1)::varchar, random_chinese_name(), month_start + random() * (month_end - month_start), substr(md5(random()::text || i::text), 1, 32), 'admin' || floor(random() * 10 + 1)::varchar, '系统管理员' || floor(random() * 5 + 1)::varchar, month_start + random() * (month_end - month_start) + (interval '1 hour' * floor(random() * 24)), substr(md5(random()::text || i::text), 1, 32), 'user' || floor(random() * 100 + 1)::varchar, random_chinese_name(), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text) FROM generate_series(1, 2000000) AS i; RAISE NOTICE 'Completed month: %, % records inserted', to_char(month_start, 'YYYY-MM'), 2000000; END LOOP; END $$;1-3、创建pg分区表-分区键为创建时间
-- 1-创建分区表 CREATE TABLE t_common_work_order_log_new ( work_order_log_id varchar(32) NOT NULL, operation_type int2, work_order_id varchar(32), work_order_name varchar(100), work_order_type int4, biz_location_id int4, planned_completion_time timestamp, actual_completion_time timestamp, handle_user_account varchar(100), handle_user_name varchar(100), create_time timestamp(0), create_by_uuid varchar(32), create_by_account varchar(32), create_by_name varchar(100), last_update_time timestamp(0), last_update_uuid varchar(32), last_update_account varchar(32), last_update_name varchar(100), biz_attribute_1 varchar(255), biz_attribute_2 varchar(255), biz_attribute_3 varchar(255), biz_attribute_4 varchar(255), biz_attribute_5 varchar(255), biz_attribute_6 varchar(255), biz_attribute_7 varchar(255), biz_attribute_8 varchar(255), biz_attribute_9 varchar(255), biz_attribute_10 varchar(255), biz_attribute_11 varchar(255), biz_attribute_12 varchar(255), biz_attribute_13 varchar(255), biz_attribute_14 varchar(255), biz_attribute_15 varchar(255), biz_attribute_16 varchar(255), biz_attribute_17 varchar(255), biz_attribute_18 varchar(255), biz_attribute_19 varchar(255), biz_attribute_20 varchar(255) ) PARTITION BY RANGE (create_time) ;
1-4、创建24年所有分区
-- 2-创建所24年有分区 CREATE TABLE t_common_work_order_log_202401 PARTITION OF t_common_work_order_log_new FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'); CREATE TABLE t_common_work_order_log_202402 PARTITION OF t_common_work_order_log_new FOR VALUES FROM ('2024-02-01') TO ('2024-03-01'); CREATE TABLE t_common_work_order_log_202403 PARTITION OF t_common_work_order_log_new FOR VALUES FROM ('2024-03-01') TO ('2024-04-01'); CREATE TABLE t_common_work_order_log_202404 PARTITION OF t_common_work_order_log_new FOR VALUES FROM ('2024-04-01') TO ('2024-05-01'); CREATE TABLE t_common_work_order_log_202405 PARTITION OF t_common_work_order_log_new FOR VALUES FROM ('2024-05-01') TO ('2024-06-01'); CREATE TABLE t_common_work_order_log_202406 PARTITION OF t_common_work_order_log_new FOR VALUES FROM ('2024-06-01') TO ('2024-07-01'); CREATE TABLE t_common_work_order_log_202407 PARTITION OF t_common_work_order_log_new FOR VALUES FROM ('2024-07-01') TO ('2024-08-01'); CREATE TABLE t_common_work_order_log_202408 PARTITION OF t_common_work_order_log_new FOR VALUES FROM ('2024-08-01') TO ('2024-09-01'); CREATE TABLE t_common_work_order_log_202409 PARTITION OF t_common_work_order_log_new FOR VALUES FROM ('2024-09-01') TO ('2024-10-01'); CREATE TABLE t_common_work_order_log_202410 PARTITION OF t_common_work_order_log_new FOR VALUES FROM ('2024-10-01') TO ('2024-11-01'); CREATE TABLE t_common_work_order_log_202411 PARTITION OF t_common_work_order_log_new FOR VALUES FROM ('2024-11-01') TO ('2024-12-01'); CREATE TABLE t_common_work_order_log_202412 PARTITION OF t_common_work_order_log_new FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');1-5、设置默认分区（兜底用）
当写入数据的创建时间没有匹配到已有分区时，会写入默认分区，避免数据库异常
-- 3. 设置默认分区（兜底用） CREATE TABLE t_common_work_order_log_default PARTITION OF t_common_work_order_log_new DEFAULT;
1-6、迁移数据
-- 4. 迁移数据 INSERT INTO t_common_work_order_log_new SELECT * FROM t_common_work_order_log;
1-7、创建分区表索引
-- 5. 创建分区表索引 CREATE INDEX order_log_biz_location_id_p_index ON t_common_work_order_log_new USING btree ( biz_location_id pg_catalog.int4_ops ASC NULLS LAST ); CREATE INDEX order_log_create_by_account_p_index ON t_common_work_order_log_new USING btree ( create_by_account COLLATE pg_catalog.default pg_catalog.text_ops ASC NULLS LAST ); CREATE INDEX order_log_create_time_p_index ON t_common_work_order_log_new USING btree ( create_time pg_catalog.timestamp_ops ASC NULLS LAST ); CREATE INDEX order_log_handle_user_account_p_index ON t_common_work_order_log_new USING btree ( handle_user_account COLLATE pg_catalog.default pg_catalog.text_ops ASC NULLS LAST ); CREATE INDEX order_log_last_update_account_p_index ON t_common_work_order_log_new USING btree ( last_update_account COLLATE pg_catalog.default pg_catalog.text_ops ASC NULLS LAST ); CREATE INDEX order_log_last_update_time_p_index ON t_common_work_order_log_new USING btree ( last_update_time pg_catalog.timestamp_ops ASC NULLS LAST ); CREATE INDEX order_log_operation_type_p_index ON t_common_work_order_log_new USING btree ( operation_type pg_catalog.int2_ops ASC NULLS LAST ); CREATE INDEX order_log_work_order_name_p_index ON t_common_work_order_log_new USING btree ( work_order_name COLLATE pg_catalog.default pg_catalog.text_ops ASC NULLS LAST ); CREATE INDEX order_log_work_order_type_p_index ON t_common_work_order_log_new USING btree ( work_order_type pg_catalog.int4_ops ASC NULLS LAST );
2、SQL增删改查测试
2-1、查询速度对比
2-1-1、查询总数量时间对比
- 非分区表首次查询约为800s，预热后查询为2.4s
- 分区表首次查询约为380s，预热后查询为1.26s
-- 819.769s/2.427s SELECT COUNT(*) FROM t_common_work_order_log; -- 387.680s/1.260s SELECT COUNT(*) FROM t_common_work_order_log_new;
查询总数量反直觉分析
为什么查询总数量分区表会比非分区表快？
执行EXPLAIN ANALYZE可以发现，分区表为并行扫描，最后会将并行扫描结果汇总：
-- 819.769s/2.427s EXPLAIN ANALYZE SELECT COUNT(*) FROM t_common_work_order_log; -- 387.680s/1.260s EXPLAIN ANALYZE SELECT COUNT(*) FROM t_common_work_order_log_new;
Finalize Aggregate (cost=4126001.24..4126001.25 rows=1 width=8) (actual time=2554.156..2559.141 rows=1 loops=1) -> Gather (cost=4126001.03..4126001.24 rows=2 width=8) (actual time=2554.069..2559.129 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=4125001.03..4125001.04 rows=1 width=8) (actual time=2540.244..2540.245 rows=1 loops=3) -> Parallel Seq Scan on t_common_work_order_log (cost=0.00..4100001.02 rows=10000002 width=0) (actual time=0.036..2256.003 rows=8000000 loops=3) Planning Time: 0.155 ms JIT: Functions: 8 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 0.595 ms, Inlining 66.620 ms, Optimization 16.470 ms, Emission 12.494 ms, Total 96.179 ms Execution Time: 2559.602 msFinalize Aggregate (cost=2071847.96..2071847.97 rows=1 width=8) (actual time=1737.444..1748.516 rows=1 loops=1) -> Gather (cost=2071847.74..2071847.95 rows=2 width=8) (actual time=1737.313..1748.503 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=2070847.74..2070847.75 rows=1 width=8) (actual time=1725.717..1725.721 rows=1 loops=3) -> Parallel Append (cost=0.43..2045847.74 rows=10000000 width=0) (actual time=35.470..1495.583 rows=8000000 loops=3) -> Parallel Index Only Scan using t_common_work_order_log_202406_operation_type_idx on t_common_work_order_log_202406 t_common_work_order_log_new_6 (cost=0.43..274942.74 rows=833486 width=0) (actual time=42.229..175.568 rows=2000366 loops=1) Heap Fetches: 94752 -> Parallel Index Only Scan using t_common_work_order_log_202407_operation_type_idx on t_common_work_order_log_202407 t_common_work_order_log_new_7 (cost=0.43..203921.26 rows=833669 width=0) (actual time=42.227..164.912 rows=2000805 loops=1) Heap Fetches: 67128 -> Parallel Index Only Scan using t_common_work_order_log_202410_work_order_type_idx on t_common_work_order_log_202410 t_common_work_order_log_new_10 (cost=0.43..25125.31 rows=833677 width=0) (actual time=0.073..101.861 rows=2000824 loops=1) Heap Fetches: 0 -> Parallel Index Only Scan using t_common_work_order_log_202404_work_order_type_idx on t_common_work_order_log_202404 t_common_work_order_log_new_4 (cost=0.43..25116.53 rows=833459 width=0) (actual time=0.055..101.724 rows=2000302 loops=1) Heap Fetches: 0 -> Parallel Index Only Scan using t_common_work_order_log_202403_work_order_type_idx on t_common_work_order_log_202403 t_common_work_order_log_new_3 (cost=0.43..25115.34 rows=833405 width=0) (actual time=0.075..101.928 rows=2000172 loops=1) Heap Fetches: 0 -> Parallel Index Only Scan using t_common_work_order_log_202402_work_order_type_idx on t_common_work_order_log_202402 t_common_work_order_log_new_2 (cost=0.43..25115.08 rows=833393 width=0) (actual time=0.049..101.426 rows=2000144 loops=1) Heap Fetches: 0 -> Parallel Index Only Scan using t_common_work_order_log_202401_work_order_type_idx on t_common_work_order_log_202401 t_common_work_order_log_new_1 (cost=0.43..25114.91 rows=833385 width=0) (actual time=0.079..101.461 rows=2000125 loops=1) Heap Fetches: 0 -> Parallel Index Only Scan using t_common_work_order_log_202412_work_order_type_idx on t_common_work_order_log_202412 t_common_work_order_log_new_12 (cost=0.43..22083.01 rows=732663 width=0) (actual time=0.044..89.077 rows=1758391 loops=1) Heap Fetches: 0 -> Parallel Index Only Scan using t_common_work_order_log_default_work_order_type_idx on t_common_work_order_log_default t_common_work_order_log_new_13 (cost=0.29..3030.63 rows=100470 width=0) (actual time=0.050..12.340 rows=241127 loops=1) Heap Fetches: 0 -> Parallel Seq Scan on t_common_work_order_log_202409 t_common_work_order_log_new_9 (cost=0.00..341619.16 rows=833216 width=0) (actual time=0.053..196.980 rows=666573 loops=3) -> Parallel Seq Scan on t_common_work_order_log_202411 t_common_work_order_log_new_11 (cost=0.00..341578.15 rows=833115 width=0) (actual time=0.048..296.090 rows=999738 loops=2) -> Parallel Seq Scan on t_common_work_order_log_202408 t_common_work_order_log_new_8 (cost=0.00..341574.07 rows=833107 width=0) (actual time=0.057..584.129 rows=1999457 loops=1) -> Parallel Seq Scan on t_common_work_order_log_202405 t_common_work_order_log_new_5 (cost=0.00..341511.55 rows=832955 width=0) (actual time=21.951..597.384 rows=1999092 loops=1) Planning Time: 0.608 ms JIT: Functions: 47 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 1.001 ms, Inlining 65.639 ms, Optimization 19.031 ms, Emission 21.833 ms, Total 107.504 ms Execution Time: 1749.153 ms2-2-1、带上分区键查询对比
带上分区键在分表区间内查询
查询2024年4月份总数据量
-- 原始表2.066s SELECT COUNT(*) FROM t_common_work_order_log WHERE create_time > '2024-04-01' AND create_time '2024-04-01' AND create_time
带上分区键跨分表区间查询
查询2024年5月~6月总数据量
-- 原始表3.303s SELECT COUNT(*) FROM t_common_work_order_log WHERE create_time > '2024-05-01' AND create_time '2024-05-01' AND create_time
2-2、删除速度对比
2-2-1、全量删除TRUNCATE
相同的数据量分区表删除耗时远远大于原始表
-- 清空速度对比： TRUNCATE TABLE t_common_work_order_log; TRUNCATE TABLE t_common_work_order_log_new;
2-2-2、删除某个月数据
原始表DELETE 145.372s
DELETE FROM t_common_work_order_log WHERE create_time >= '2024-05-01 00:00:00' AND create_time
分区表DELETE（未指定分区）225.896s
分区表DELETE（指定分区）242.770s
分区表TRUNCATE（指定分区）13.156s
2-2-3、删除一条数据
测试10次，每次删除一条
原始表 平均8.009s
-- 113.489s 10.768s 7.393s 6.713s 8.896s 6.089s 7.506s 8.005s 7.955s 6.838s DELETE FROM t_common_work_order_log WHERE work_order_log_id = '0e817fcd240bf4ecbaf0b5d05e793b9f';
分区表（未带上分区键） 平均7.858s
-- 33.275s 7.227s 7.545s 8.674s 6.186s 10.045s 7.103s 5.707s 7.112s 8.972s DELETE FROM t_common_work_order_log_new WHERE work_order_log_id = '8326be2f926c1437a8b623b044518888';
分区表（带上分区键） 平均1.567s
-- 0.346s 2.341s 1.003s 2.226s 0.620s 2.955s 1.191s 0.071s 7.196s 1.855s DELETE FROM t_common_work_order_log_new WHERE create_time = '2024-3-27 23:58:50' AND work_order_log_id = '040ba71c39f80e1217296f262e8e999f';
2-3、写入速度对比
2-2-1、全量插入2400w数据，均匀分布在每个月
插入sql可以参考执行脚本新增2400w行
原始表 3387.520s
分区表 4988.111s
2-2-2、指定某个月插入10w数据
原始表 63.572s
-- 生成10万条6月数据 INSERT INTO t_common_work_order_log ( work_order_log_id, operation_type, work_order_id, work_order_name, work_order_type, biz_location_id, planned_completion_time, actual_completion_time, handle_user_account, handle_user_name, create_time, create_by_uuid, create_by_account, create_by_name, last_update_time, last_update_uuid, last_update_account, last_update_name, biz_attribute_1, biz_attribute_2, biz_attribute_3, biz_attribute_4, biz_attribute_5, biz_attribute_6, biz_attribute_7, biz_attribute_8, biz_attribute_9, biz_attribute_10 ) SELECT -- work_order_log_id: UUID简写 substr(md5(random()::text || i::text), 1, 32), -- operation_type: 1-5随机值 floor(random() * 5 + 1)::int2, -- work_order_id: WO+6月标识+序号 'WO202406' || lpad(i::text, 5, '0'), -- work_order_name: 随机工单类型 (ARRAY['设备维修','软件升级','网络故障','数据迁移','常规维护'])[floor(random()*5+1)], -- work_order_type: 1-10随机值 floor(random() * 10 + 1)::int4, -- biz_location_id: 1-100随机 floor(random() * 100 + 1)::int4, -- planned_completion_time: 6月随机时间+1-3天 (timestamp '2024-06-01' + random() * interval '30 days') + (interval '1 day' * floor(random() * 3 + 1)), -- actual_completion_time: 80%有值，在计划时间±2小时 CASE WHEN random() > 0.2 THEN (timestamp '2024-06-01' + random() * interval '30 days') + (interval '1 hour' * floor(random() * 4 - 2)) ELSE NULL END, -- handle_user_account: user+随机编号 'user' || floor(random() * 50 + 1)::varchar, -- handle_user_name: 随机中文名 random_chinese_name(), -- create_time: 6月随机时间 timestamp '2024-06-01' + random() * interval '30 days', -- create_by_uuid: 随机UUID简写 substr(md5(random()::text || i::text), 1, 32), -- create_by_account: admin+随机编号 'admin' || floor(random() * 5 + 1)::varchar, -- create_by_name: 管理员+编号 '系统管理员' || floor(random() * 3 + 1)::varchar, -- last_update_time: create_time+0-24小时 timestamp '2024-06-01' + random() * interval '30 days' + (interval '1 hour' * floor(random() * 24)), -- last_update_uuid: 随机UUID简写 substr(md5(random()::text || i::text), 1, 32), -- last_update_account: user+随机编号 'user' || floor(random() * 50 + 1)::varchar, -- last_update_name: 随机中文名 random_chinese_name(), -- biz_attribute_1-10: 随机MD5值 md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text) FROM generate_series(1, 100000) AS i;
分区表（未指定分区） 58.759s
-- 生成10万条6月数据 INSERT INTO t_common_work_order_log_new ( work_order_log_id, operation_type, work_order_id, work_order_name, work_order_type, biz_location_id, planned_completion_time, actual_completion_time, handle_user_account, handle_user_name, create_time, create_by_uuid, create_by_account, create_by_name, last_update_time, last_update_uuid, last_update_account, last_update_name, biz_attribute_1, biz_attribute_2, biz_attribute_3, biz_attribute_4, biz_attribute_5, biz_attribute_6, biz_attribute_7, biz_attribute_8, biz_attribute_9, biz_attribute_10 ) SELECT -- work_order_log_id: UUID简写 substr(md5(random()::text || i::text), 1, 32), -- operation_type: 1-5随机值 floor(random() * 5 + 1)::int2, -- work_order_id: WO+6月标识+序号 'WO202406' || lpad(i::text, 5, '0'), -- work_order_name: 随机工单类型 (ARRAY['设备维修','软件升级','网络故障','数据迁移','常规维护'])[floor(random()*5+1)], -- work_order_type: 1-10随机值 floor(random() * 10 + 1)::int4, -- biz_location_id: 1-100随机 floor(random() * 100 + 1)::int4, -- planned_completion_time: 6月随机时间+1-3天 (timestamp '2024-06-01' + random() * interval '30 days') + (interval '1 day' * floor(random() * 3 + 1)), -- actual_completion_time: 80%有值，在计划时间±2小时 CASE WHEN random() > 0.2 THEN (timestamp '2024-06-01' + random() * interval '30 days') + (interval '1 hour' * floor(random() * 4 - 2)) ELSE NULL END, -- handle_user_account: user+随机编号 'user' || floor(random() * 50 + 1)::varchar, -- handle_user_name: 随机中文名 random_chinese_name(), -- create_time: 6月随机时间 timestamp '2024-06-01' + random() * interval '30 days', -- create_by_uuid: 随机UUID简写 substr(md5(random()::text || i::text), 1, 32), -- create_by_account: admin+随机编号 'admin' || floor(random() * 5 + 1)::varchar, -- create_by_name: 管理员+编号 '系统管理员' || floor(random() * 3 + 1)::varchar, -- last_update_time: create_time+0-24小时 timestamp '2024-06-01' + random() * interval '30 days' + (interval '1 hour' * floor(random() * 24)), -- last_update_uuid: 随机UUID简写 substr(md5(random()::text || i::text), 1, 32), -- last_update_account: user+随机编号 'user' || floor(random() * 50 + 1)::varchar, -- last_update_name: 随机中文名 random_chinese_name(), -- biz_attribute_1-10: 随机MD5值 md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text) FROM generate_series(1, 100000) AS i;
分区表（指定分区） 48.098s
2-4、更新速度对比
2-4-1、修改指定月份的处理人信息
原始表 538.133s
UPDATE t_common_work_order_log SET handle_user_account = 'hander-system' WHERE create_time > '2024-07-01' AND create_time
分区表（未指定分区） 1252.286s
UPDATE t_common_work_order_log_new SET handle_user_account = 'hander-system' WHERE create_time > '2024-07-01' AND create_time
分区表（指定分区） 1252.286s
UPDATE t_common_work_order_log_new SET handle_user_account = 'hander-system' WHERE create_time > '2024-07-01' AND create_time
2-4-1、根据主键修处理人信息
原始表 12.597s
UPDATE t_common_work_order_log SET handle_user_account = 'hander-system-byid' WHERE work_order_log_id = 'db3114ba4a676937269e2cf0ef7454b8';
分区表（未带上分区键） 10.870s
UPDATE t_common_work_order_log_new SET handle_user_account = 'hander-system-byid' WHERE work_order_log_id = '9ac76e7e262cd7b374d6939a7e0f4144';
分区表（带上分区键） 0.438s
UPDATE t_common_work_order_log_new SET handle_user_account = 'hander-system-by shard id' WHERE create_time = '2024-12-31 23:59:19' AND work_order_log_id = '9ac76e7e262cd7b374d6939a7e0f4144';
3、总结
文本基于PostgreSQL原生分区能力，以创建时间作为分区键进行水平分表验证，要想发挥分区表的优势，多需要带上分区键进行操作，例如上文的删除以及更新操作中，带上分区键比未带分区键操作，性能存在10倍左右差异，但是进行大批量的操作，分区表的优势不明显，甚至有些场景还会非常慢。
PostgreSQL分区为数据库引擎内置功能，无需额外的应用层中间件，性能损失较小，提供了基础的分区能力，适用于如下场景：
- 数据量在单机可承受范围内（
免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们。
