做数据库的早晚都会撞上这么一个问题表里的数据越来越厚查询越来越慢清理历史数据比登天还难。openGauss的分区表就是针对这一串问题最直接、最成熟的解法。这篇文章我不讲虚的直接从openGauss的实际操作出发把分区表的类型、创建、维护、排障一条龙讲透看完你就能在自己的环境里直接用起来。无论你是刚接触openGauss的开发、需要维护生产库的DBA还是正在做数据架构选型这篇文章都适用。我会把分区表的核心机制拆开揉碎再配上可直接复制的SQL和实操心得确保你看完不只是知道而是真的会动手配、会排查、会避坑。1. 分区表到底解决了什么问题1.1 数据膨胀后的那三件头疼事先还原一个我经常遇到的场景。某业务流水表上线时跑得好好的一年后数据量到了几亿行问题接踵而来。第一是查询变慢。就算你建了索引几亿行数据下B树的深度会增加索引扫描的代价会跟着涨更不用说某些查询条件组合导致索引失效时优化器会选择全表扫描。几亿行的全表扫描一次就是几十秒报表业务直接卡死。第二是维护窗口不够用。你想清掉半年前的历史数据一条DELETE FROM xxx WHERE create_time 2023-01-01下去锁表锁半天期间业务写入全部被阻塞。生产环境哪能给你这么长的维护窗口第三是数据归档困难。月报、年报需要读老数据但老数据又不该跟新数据混在一起影响性能。物理上分开存逻辑上还能按统一视图查这是最理想的状态。分区表正好把这三件事一起解决了。一张大表在逻辑上仍然是一张表但底层按照你指定的规则拆成若干个物理独立的小分区。每个分区可以单独查、单独清理、单独备份、单独归档互相之间不拖累。查询时数据库还会自动跳过无关分区这就是后面要重点讲的分区裁剪机制。1.2 分区裁剪性能提升的真正核心很多人以为分区表性能好是因为数据被拆小了这个理解只说对了一半。拆小是手段分区裁剪才是真正的杀招。分区裁剪是指SQL执行时优化器根据WHERE条件里的分区键过滤条件在执行计划生成阶段就直接排除掉不可能命中的分区。比如表按create_time做了月度范围分区你查WHERE create_time 2024-06-15优化器直接只扫描6月那个分区其它11个分区碰都不碰。注意这个排除动作发生在生成执行计划时不是在真正扫数据时才判断。打个比方你有个文件柜按月份贴好了标签要找6月的材料直接走到6月那格抽屉就行。没分区时你等于要把一堆堆了几个年份的杂物抽屉整个翻一遍。1/12的扫描量性能差距自然拉开。但分区裁剪有个前提条件也是很多人实战中容易踩的坑WHERE条件里必须直接用分区键做范围或等值过滤而且不能在分区键上套函数或表达式。比如你写WHERE to_char(create_time, YYYY-MM) 2024-06优化器无法预判这个表达式落在哪个分区只能老老实实扫所有分区。这个坑后面排查章节我再细讲。2. openGauss分区表类型怎么选2.1 范围分区时间序列数据的首选范围分区RANGE是openGauss里用得最多、最好理解的分区类型。它的思路是根据分区键的值落在哪个区间来决定数据进哪个分区典型场景就是按日期、按自增ID划分。CREATE TABLE order_log ( id bigint, order_no varchar(32), create_time date, amount numeric(12,2) ) PARTITION BY RANGE (create_time) ( PARTITION p_before_2024 VALUES LESS THAN (2024-01-01), PARTITION p_2024_q1 VALUES LESS THAN (2024-04-01), PARTITION p_2024_q2 VALUES LESS THAN (2024-07-01), PARTITION p_2024_q3 VALUES LESS THAN (2024-10-01), PARTITION p_max VALUES LESS THAN (MAXVALUE) );注意最后那个p_max它的作用是兜底。任何超出前面分区边界的数据都会被扔进这个最大分区避免插入数据时因为没有匹配分区而直接报错。生产环境我强烈建议保留一个MAXVALUE分区否则哪天业务来了个异常大日期整条写入直接失败这个锅可不好背。选择范围分区时分区粒度怎么定是个学问。我一般建议遵循一个原则让最常见的查询条件能精确命中一个或少量分区。比如业务查询经常按天查那就按天分区如果经常按月查就按月分区。分区太多也有副作用后面维护时你会发现分区数量几百上千管理起来同样痛苦。2.2 列表分区按离散值归堆整理列表分区LIST适合分区键是一组离散值的场景比如地区、业务类型、状态码。它跟范围分区不同不是按大小区间而是按枚举值列表来划分归属。CREATE TABLE customer_info ( id bigint, region varchar(20), name varchar(50), level int ) PARTITION BY LIST (region) ( PARTITION p_east VALUES (上海, 江苏, 浙江, 安徽), PARTITION p_south VALUES (广东, 福建, 海南), PARTITION p_west VALUES (四川, 重庆, 云南), PARTITION p_other VALUES (DEFAULT) );列表分区在做区域类业务、按租户隔离的场景下特别好用。同一个区域的查询只会扫对应分区天然做了隔离。而且DEFAULT分区可以捕获所有没有明确指定的值跟MAXVALUE兜底是一个思路。不过列表分区有个限制要想清楚如果分区键的枚举值特别多或者经常变化维护列表定义会变成负担。比如省份这种相对稳定的值还好但如果是一种不断新增的标签类型每加一个值都要ALTER分区定义操作成本和出错概率都会上升。所以列表分区更适合值域稳定、数量可控的场景。2.3 哈希分区把数据均匀打散哈希分区HASH的思路是按分区键的哈希值把数据均匀分布到固定数量的分区中。它不像范围和列表那样按业务语义划分而是纯粹为了均衡。CREATE TABLE user_session ( user_id bigint, session_id varchar(64), login_time timestamp, payload text ) PARTITION BY HASH (user_id) ( PARTITION p0, PARTITION p1, PARTITION p2, PARTITION p3, PARTITION p4, PARTITION p5, PARTITION p6, PARTITION p7 );哈希分区最适合那种分区键没有业务语义、但查询又高频按它过滤的场景。比如用户ID、订单ID数据天然无规律用范围分区容易产生数据倾斜某些分区数据特别多用哈希分区则能比较均匀地打散。哈希分区的数量一旦定下来后期扩容是个麻烦事。数据分布是跟着哈希函数和分区数量走的你从8个分区扩到16个大量历史数据的分区归属需要重新计算实际操作中往往要重建表或做数据搬迁。所以哈希分区的数量要一次性规划到位宁可多分一些也不要后续频繁扩容。我在生产上规划哈希分区数量时通常会结合未来三到五年的数据增长预期来做。2.4 间隔分区给范围分区装上自动挡间隔分区INTERVAL是范围分区的一种自动扩展模式。它比普通范围分区多了一个INTERVAL子句当插入的数据超出了当前最大分区的边界时openGauss会自动创建新的分区不需要你手动ALTER TABLE。CREATE TABLE sys_event_log ( id bigint, event_time date, event_type varchar(32), detail text ) PARTITION BY RANGE (event_time) INTERVAL (1 month) ( PARTITION p_before_2024 VALUES LESS THAN (2024-01-01) );这个表刚建出来只有1个分区当你插入一条event_time 2024-05-20的数据时数据库会自动创建边界到2024-06-01的分区再过一个月插入新数据又会自动创建下一个分区。整个过程不需要DBA干预对业务完全透明。间隔分区特别适合那种数据量不可预期、持续增长、按时间归档的业务比如设备上报日志、操作审计日志。它省去了人工定期加分区的麻烦也避免了漏加分区导致写入失败的风险。我自己的习惯是只要场景允许能用间隔分区就用间隔分区人工维护分区的环节越少出问题的概率越低。3. 手把手创建分区表3.1 单级范围分区表的完整创建过程创建一个分区表核心就是把普通建表语句里的PARTITION BY子句加上再把各分区的定义写全。下面我用一个贴近实战的例子把完整流程走一遍。假设要创建一张订单流水表按订单日期做范围分区CREATE TABLE t_orders ( order_id bigint NOT NULL, user_id bigint, order_date date, status varchar(10), total_amount numeric(10,2) ) PARTITION BY RANGE (order_date) ( PARTITION p_2024_jan VALUES LESS THAN (2024-02-01), PARTITION p_2024_feb VALUES LESS THAN (2024-03-01), PARTITION p_2024_mar VALUES LESS THAN (2024-04-01), PARTITION p_2024_apr VALUES LESS THAN (2024-05-01), PARTITION p_2024_may VALUES LESS THAN (2024-06-01), PARTITION p_2024_jun VALUES LESS THAN (2024-07-01) );这里要特别注意边界值的设计。openGauss的VALUES LESS THAN是小于语义也就是说p_2024_jan这个分区里存的是order_date 2024-02-01的数据也就是整个1月的数据。每个分区管到比下一个分区边界小一丁点的位置边界日期本身属于下一个分区。理解这个规则你才不会在建表时把日期边界搞错。建完之后可以用下面的语句查看分区创建结果SELECT relname, partstrategy, boundaries FROM pg_partition WHERE parentid t_orders::regclass;如果你发现某个分区数据量异常偏大还可以单独查这个分区的统计信息。分区表在openGauss里每个分区都是独立的物理存储单元你用\d t_orders可以看到每个独立分区的名称和存储属性。3.2 多级分区两级维度联合拆分业务复杂了单个维度的分区可能不够。比如订单表既要按日期划分又要按地区划分这时候就可以用多级分区子分区。openGauss支持范围分区下挂子分区的组合方式。CREATE TABLE t_orders_sub ( order_id bigint NOT NULL, user_id bigint, order_date date, region varchar(20), total_amount numeric(10,2) ) PARTITION BY RANGE (order_date) ( PARTITION p_2024_q1 VALUES LESS THAN (2024-04-01) ( SUBPARTITION p_2024_q1_east VALUES (上海, 江苏, 浙江), SUBPARTITION p_2024_q1_south VALUES (广东, 福建), SUBPARTITION p_2024_q1_other VALUES (DEFAULT) ), PARTITION p_2024_q2 VALUES LESS THAN (2024-07-01) ( SUBPARTITION p_2024_q2_east VALUES (上海, 江苏, 浙江), SUBPARTITION p_2024_q2_south VALUES (广东, 福建), SUBPARTITION p_2024_q2_other VALUES (DEFAULT) ) ) PARTITION BY LIST (region) SUBPARTITION BY LIST (region);外层分区管时间段内层子分区管地域两级联合把数据切分得更细。查询时如果WHERE条件同时带上时间和地区优化器能同时裁剪外层分区和内层子分区扫描的数据量能压到极低。多级分区的管理复杂度是成倍上升的。每次新增一个外层分区你都得给它同时定义好对应的所有子分区。加分区漏了子分区定义后续写入可能直接报错。我建议在做多级分区前先想清楚是不是单级分区真的扛不住。能用单级解决的尽量不要上多级毕竟分区表的维护成本也是成本。3.3 分区键怎么选三条原则必须守分区键的选择直接决定分区表的生死。选对了查询快、维护顺选错了性能可能比普通表还差。我在实际操作中总结出三条硬原则。第一分区键必须是查询条件里的高频列。分区裁剪的价值全靠在WHERE里命中分区键如果业务查询基本不按这个列过滤那分区就形同虚设每次查询都是全分区扫描。这种表做了分区等于白做还徒增维护负担。第二要确保分区键的数据分布相对均匀。按日期分区每天数据量差距不会太离谱但如果按某个分布极不均匀的字段分区可能一个分区占了90%的数据其它分区都是空的这就起不到均衡的作用。哈希分区在解决这类问题时会有优势前提是你选了一个区分度高的列。用户ID、订单号天然适合而像性别、状态这类只有几个值的列做哈希分区基本没有意义。第三分区键要尽量避免后续更新。分区键的值决定了数据落在哪个分区如果业务会修改这个字段openGauss需要把数据从一个分区搬到另一个分区。实际生产中有没有遇到很少但真遇到一次数据量稍微大点执行时间就让人抓狂。所以在表结构设计阶段就要想清楚让分区键成为一个写入后基本不变的字段。另外提醒一点分区键不要选那种长度特别大的文本字段。分区键在每行数据里都要参与判断在索引中也要参与存储字段越长存储和比较的开销越大。能用ID或日期就不要用长描述文本。4. 分区表的日常运维每天都在用的操作4.1 新增、删除、清空分区三个高频操作分区表上线之后最频繁的运维操作就是新增分区。特别是普通范围分区非间隔分区你得定期手动加分区否则数据写入到边界外就会报错。新增分区ALTER TABLE t_orders ADD PARTITION p_2024_jul VALUES LESS THAN (2024-08-01);这条命令执行速度很快本质上是在元数据里注册一个新的存储对象。如果你用的是范围分区且数据按月划分建议在每个月月底就把下个月的分区先创建好给业务留出缓冲时间。删除分区ALTER TABLE t_orders DROP PARTITION p_2024_jan;删除分区的语义是连数据带分区定义一起删掉比DELETE快得多因为它直接丢弃整个物理文件不需要逐行标记删除。做历史数据清理时我强烈建议用DROP PARTITION代替大事务DELETE这是分区表在数据生命周期管理上最大的优势。清空分区ALTER TABLE t_orders TRUNCATE PARTITION p_2024_jan;TRUNCATE和DROP的区别在于TRUNCATE只清数据分区定义和表结构还在。适合那种定期重算的临时分区表。我在运维中还有个小习惯任何涉及删除分区的操作先确认分区的数据确实没有保留价值再动手最好先做一个分区级备份。删除分区是瞬间完成的事没有后悔药。4.2 分区拆分与合并动态调整边界业务发展过程中分区粒度可能需要调整。比如按季度分区的表到了大促月份季度分区里的数据量暴涨你想把这个季度单独拆成几个月度分区用拆分操作。ALTER TABLE t_orders SPLIT PARTITION p_2024_q3 AT (2024-08-01) INTO (PARTITION p_2024_jul VALUES LESS THAN (2024-08-01), PARTITION p_2024_aug_sep VALUES LESS THAN (2024-10-01));这条语句会把原来p_2024_q3里的数据按边界值2024-08-01拆成两个新分区。拆分过程中数据库会做数据重分布如果这个分区里数据量很大执行时间会相应变长要放在维护窗口执行。反过来如果分区太细了想合并用MERGE操作ALTER TABLE t_orders MERGE PARTITION p_2024_jul, p_2024_aug INTO PARTITION p_2024_q3;合并之后两个旧分区的数据会汇总到新分区里。一个容易踩的坑是合并操作要求两个分区的边界是相邻的否则数据归属会逻辑混乱。我做合并前一般先查一下pg_partition里的边界定义确认前后分区的连续性再执行操作。4.3 存储过程里动态管理分区分区操作写死在SQL里总有不够灵活的时候比如固定每个月1号自动给下一个月建分区。这种场景最适合用存储过程封装。openGauss兼容PL/pgSQL和Oracle风格的存储过程我以一个常用的动态建分区存储过程为例。CREATE OR REPLACE PROCEDURE add_next_month_partition() AS $$ DECLARE v_partition_name text; v_next_month date; v_sql text; BEGIN v_next_month : date_trunc(month, now()) interval 1 month; v_partition_name : p_ || to_char(v_next_month, YYYYMM); v_sql : ALTER TABLE t_orders ADD PARTITION || v_partition_name || VALUES LESS THAN ( || to_char(v_next_month interval 1 month, YYYY-MM-DD) || ); EXECUTE IMMEDIATE v_sql; END; $$ LANGUAGE plpgsql;把这个存储过程挂到定时任务里每个月月初跑一次分区就自动创建了。如果你用的是间隔分区这一步都可以省掉不过存储过程的方式在需要加额外判断逻辑时比如检查分区是否已存在会更灵活。这里有个重要细节动态SQL里的分区名和边界值一定要用变量拼接防止SQL注入风险同时写之前要确认新分区名不能跟已有分区冲突。我见过有同事在循环调用时因为分区名重复整个改造脚本崩掉建议在存储过程里加一个分区存在性检查。IF EXISTS (SELECT 1 FROM pg_partition WHERE parentid t_orders::regclass AND relname v_partition_name) THEN RETURN; END IF;5. 常见问题与排查技巧实录5.1 会话闲置超时断开session unused timeout使用gsql连接openGauss时你可能会遇到下面这样的报错信息opengauss# \l WARNING: session unused timeout. FATAL: terminating connection by timeout这个报错是openGauss的会话超时机制在起作用。openGauss有一个session_timeout参数用来限制一个连接的空闲时间上限。如果客户端连接在那儿发呆超过这个阈值服务端会主动断开连接避免空闲会话长期占用数据库资源。处理这个问题的思路分两层。第一层是调整服务端参数如果你确认业务环境允许更长的空闲时间可以调大这个值SHOW session_timeout; ALTER SYSTEM SET session_timeout 3600;注意ALTER SYSTEM设置后根据参数的类型可能需要重启数据库或重新加载配置文件才会完全生效。第二层是从客户端侧解决定期探活、使用连接池并配置连接存活检查不要让连接一直闲置。对于长连接的业务应用连接池里加一个每隔几分钟执行一条轻量SQL的保活机制就基本不会触发这个断连。这类会话超时问题在开发环境下尤其常见写了个脚本跑到一半停下来调试回来再敲命令就被断连了。知道这个机制后遇到FATAL: terminating connection就不会慌了重新连接然后继续操作即可。5.2 查询没走分区裁剪先查这三个点分区表建好了查询却发现扫描的分区数量不对性能提升不明显这时候按顺序排查三个点。第一WHERE条件里有没有直接使用分区键。如果你查询的是按create_time分区的表但WHERE里只写了user_id优化器没法定位分区只能全分区扫描。解决办法是把分区键的条件补上让查询能命中裁剪。第二分区键上有没有套函数或隐式类型转换。前文提过to_char(create_time, YYYY-MM)这种写法会让优化器失去裁剪能力。更隐蔽的是隐式转换比如分区键是varchar类型你参数传的是int数据库可能在背后做了类型转换导致无法直接匹配。解决办法是写SQL时保证参数类型跟分区键类型一致。第三确认统计信息没有过期。分区裁剪虽然是在执行计划阶段做的但优化器判断哪个分区值得扫时会依赖统计信息估算数据量。如果统计信息严重滞后优化器有可能做出错误的选择。定期执行ANALYZE是数据库运维的好习惯分区表也适用。排查完之后用EXPLAIN看一下执行计划EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM t_orders WHERE order_date 2024-05-10;如果执行计划里出现类似Partition Iterator并且只列出少量分区说明裁剪生效如果列出全部分区就是没裁剪按上面三点继续查。5.3 分区表上的索引本地索引还是全局索引分区表上建索引有个特殊选择本地索引LOCAL还是全局索引GLOBAL。这个选择对查询和维护的影响都很大。本地索引在每个分区内独立创建每个分区的索引只管理自己的数据。创建语法是CREATE INDEX idx_orders_date_local ON t_orders (order_date) LOCAL;本地索引的好处是维护成本低删除或重建某个分区时只需处理这个分区自己的索引不影响其它分区。查询时如果分区裁剪生效只需要扫命中分区的本地索引性能很好。全局索引是跨所有分区的一个统一索引语法上指定GLOBALCREATE INDEX idx_orders_user_global ON t_orders (user_id) GLOBAL;全局索引适合那种查询条件经常不带分区键、但又需要走索引的场景。比如按user_id查订单分区键是order_date查询时很难裁剪到具体分区这时候全局索引就能派上用场。代价是全局索引在分区维护操作DROP、SPLIT、MERGE等之后可能需要重建否则索引会失效。这在大型分区表上是个不小的开销。我给个经验性的选择标准查询经常走分区键就选本地索引查询经常不带分区键必须走索引才考虑全局索引同时要做好分区维护后的索引重建预案。两种索引不是互斥的可以混用同一张表某些列用本地、某些列用全局这在实际生产里很常见。6. 分区表性能优化的额外心得6.1 分区粒度在设计阶段就要想清楚分区粒度太粗单分区数据量仍然很大裁剪效果不明显粒度太细分区数量动辄几百上千元数据管理、统计信息收集都会变成负担。我做过一张按天分区的流水表跑了一年后一千多个分区每次备份和恢复都要处理大量分区对象运维成本扑面而来。我的经验是以业务最常见的时间窗口为粒度基准。业务查询按日报表走至少做到按月分区按周或按天分区除非数据量极大否则不太划算。一个分区里的数据量控制在百万到千万这个量级是查询性能和运维成本都比较舒适的区间。数据量过亿再考虑拆粒度不要一开始就把分区切得过分碎。6.2 分区表和临时表的配合做数据归档时一个很实用的操作是先把待归档数据导入一张普通临时表然后通过交换分区的方式把整个分区的数据和临时表做一次物理交换。CREATE TABLE tmp_orders_2024_jan (LIKE t_orders); INSERT INTO tmp_orders_2024_jan SELECT * FROM t_orders WHERE order_date 2024-02-01; ALTER TABLE t_orders EXCHANGE PARTITION p_2024_jan WITH TABLE tmp_orders_2024_jan;交换分区操作非常快因为它只修改元数据不搬数据。做完交换后原分区变成一张独立的普通表你爱怎么处理都行新分区则指向了那份归档数据。这套操作可以完美避开大事务DELETE的锁竞争生产环境归档时我一直在用。6.3 定期收集统计信息分区表的统计信息比普通表更分散每个分区的数据分布都可能不同。如果统计信息过期优化器估算分区数据量时可能严重偏离现实导致执行计划走偏。我习惯定期对所有分区表执行ANALYZE t_orders;或者针对单个分区ANALYZE t_orders PARTITION (p_2024_q1);统计信息新鲜分区裁剪才能精准索引选择才能合理。做数据批量加载后这个操作一定要做别等性能问题暴露了才想起来。最后再分享一个小经验分区表不是银弹它解决的是大数据量下的查询裁剪和管理效率问题。如果你的表数据量还只有几百万行分区带来的收益有限反而增加运维复杂度。我的建议是数据量到了千万级别再考虑分区分区键要跟业务查询习惯强绑定。做对了规划openGauss的分区表能让你在大数据量场景下睡个安稳觉做错了选择后期调整分区的成本足够让人头疼很久。拿这篇文章里的操作过一遍你就能对这些细节心里有数了。