KingbaseES分区表使用指南：解决常见问题提升性能

KingbaseES分区表是将逻辑上一个大表分割成多个物理上 smaller、更易管理的部分的技术。每个分区有自己的名称和存储特性，但从用户角度仍然是一个完整的表。

创建分区表的基本语法：

CREATE TABLE sales (
id INT,
sale_date DATE,
amount NUMERIC
) PARTITION BY RANGE (sale_date);

分区表的主要优势包括：

• 提高查询性能：查询时只需扫描相关分区
• 简化管理：可独立对分区进行维护操作
• 增加可用性：某个分区出现问题不影响其他分区
• 提高数据加载速度：可并行加载不同分区的数据

结论：合理使用分区表能够显著提升大数据量场景下的数据库性能，但需要根据业务特点选择合适的分区策略。

创建分区表时常见问题包括：

问题：选择高基数的列作为分区键可能导致大量小分区

解决：选择查询频繁、具有良好区分度的列作为分区键

问题：分区数量过少或过多影响性能

解决：根据数据量和查询模式确定合适的分区数量，一般建议每个分区大小在GB级别

问题：添加、删除或修改分区时出现错误

解决：确保分区键值范围不重叠，执行维护操作前备份数据

— 创建销售数据分区表示例
CREATE TABLE sales (
id INT,
sale_date DATE,
amount NUMERIC,
customer_id INT
) PARTITION BY RANGE (sale_date);

— 创建2023年各季度分区
CREATE TABLE sales_q1_2023 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

CREATE TABLE sales_q2_2023 PARTITION OF sales
FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');

CREATE TABLE sales_q3_2023 PARTITION OF sales
FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');

CREATE TABLE sales_q4_2023 PARTITION OF sales
FOR VALUES FROM ('2023-10-01') TO ('2024-01-01');

结论：创建分区表时需谨慎选择分区键和分区数量，合理规划分区结构才能发挥分区表的最大效能。

分区表查询优化常见技巧：

KingbaseES会自动识别查询条件中的分区键值，只扫描相关分区。确保查询包含分区键条件以启用分区裁剪。

不使用分区键条件的查询会导致所有分区都被扫描，严重影响性能。

为每个分区创建本地索引，可根据查询模式选择全局或局部索引。

— 查看分区裁剪是否生效
EXPLAIN ANALYZE SELECT * FROM sales WHERE sale_date BETWEEN '2023-03-01' AND '2023-06-30';

— 创建分区索引
CREATE INDEX idx_sales_q1_2023 ON sales_q1_2023 (id);
CREATE INDEX idx_sales_q2_2023 ON sales_q2_2023 (id);

分区裁剪工作流程：

#publish-mermaid-1787502429546-0{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1787502429546-0 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1787502429546-0 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1787502429546-0 .error-icon{fill:#552222;}#publish-mermaid-1787502429546-0 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1787502429546-0 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1787502429546-0 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1787502429546-0 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1787502429546-0 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1787502429546-0 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1787502429546-0 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1787502429546-0 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1787502429546-0 .marker.cross{stroke:#333333;}#publish-mermaid-1787502429546-0 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1787502429546-0 p{margin:0;}#publish-mermaid-1787502429546-0 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1787502429546-0 .cluster-label text{fill:#333;}#publish-mermaid-1787502429546-0 .cluster-label span{color:#333;}#publish-mermaid-1787502429546-0 .cluster-label span p{background-color:transparent;}#publish-mermaid-1787502429546-0 .label text,#publish-mermaid-1787502429546-0 span{fill:#333;color:#333;}#publish-mermaid-1787502429546-0 .node rect,#publish-mermaid-1787502429546-0 .node circle,#publish-mermaid-1787502429546-0 .node ellipse,#publish-mermaid-1787502429546-0 .node polygon,#publish-mermaid-1787502429546-0 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1787502429546-0 .rough-node .label text,#publish-mermaid-1787502429546-0 .node .label text,#publish-mermaid-1787502429546-0 .image-shape .label,#publish-mermaid-1787502429546-0 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1787502429546-0 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1787502429546-0 .rough-node .label,#publish-mermaid-1787502429546-0 .node .label,#publish-mermaid-1787502429546-0 .image-shape .label,#publish-mermaid-1787502429546-0 .icon-shape .label{text-align:center;}#publish-mermaid-1787502429546-0 .node.clickable{cursor:pointer;}#publish-mermaid-1787502429546-0 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1787502429546-0 .arrowheadPath{fill:#333333;}#publish-mermaid-1787502429546-0 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1787502429546-0 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1787502429546-0 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1787502429546-0 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1787502429546-0 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1787502429546-0 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1787502429546-0 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1787502429546-0 .cluster text{fill:#333;}#publish-mermaid-1787502429546-0 .cluster span{color:#333;}#publish-mermaid-1787502429546-0 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1787502429546-0 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1787502429546-0 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1787502429546-0 .icon-shape,#publish-mermaid-1787502429546-0 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1787502429546-0 .icon-shape p,#publish-mermaid-1787502429546-0 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1787502429546-0 .icon-shape .label rect,#publish-mermaid-1787502429546-0 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1787502429546-0 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1787502429546-0 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1787502429546-0 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1787502429546-0 [data-look=\”neo\”].node rect,#publish-mermaid-1787502429546-0 [data-look=\”neo\”].cluster rect,#publish-mermaid-1787502429546-0 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787502429546-0 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1787502429546-0 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1787502429546-0 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787502429546-0 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1787502429546-0 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787502429546-0 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1787502429546-0 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787502429546-0 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787502429546-0 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}包含分区键条件不包含分区键条件

SQL查询

分析查询条件

确定相关分区

扫描所有分区

仅扫描相关分区

返回查询结果

结论：合理利用分区裁剪和索引技术可显著提升分区表查询性能，避免全表扫描。

分区表常见维护与故障排查：

• 添加新分区：使用PARTITION OF FOR VALUES语法
• 删除分区：使用DROP PARTITION语句
• 合并分区：可考虑创建新分区后删除旧分区

• 分区重建：ALTER TABLE … REBUILD PARTITION
• 分区收缩：ALTER TABLE … SHRINK PARTITION
• 分区统计信息更新：ANALYZE PARTITION

• 检查分区完整性：查询系统视图pg_partitions
• 分区空间使用情况：查询pg_total_relation_size
• 分区索引状态：检查索引有效性

— 检查分区信息
SELECT schemaname, tablename, partitionname, partitions FROM pg_partitions
WHERE tablename = 'sales';

— 添加新分区
CREATE TABLE sales_q1_2024 PARTITION OF sales
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

— 删除旧分区
DROP PARTITION sales_q1_2023;

— 更新分区统计信息
ANALYZE sales PARTITION (sales_q1_2023);

结论：定期维护和及时排查分区表问题可确保系统稳定运行，分区维护操作需谨慎执行，特别是在生产环境中。

最小示例与注意事项

— 创建测试分区表示例
CREATE TABLE test_partition (
id INT,
data VARCHAR(100),
create_time TIMESTAMP
) PARTITION BY RANGE (create_time);

— 创建两个测试分区
CREATE TABLE test_partition_2023 PARTITION OF test_partition
FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2024-01-01 00:00:00');

CREATE TABLE test_partition_2024 PARTITION OF test_partition
FOR VALUES FROM ('2024-01-01 00:00:00') TO ('2025-01-01 00:00:00');

— 插入测试数据
INSERT INTO test_partition (id, data, create_time) VALUES
(1, 'test1', '2023-05-15 10:00:00'),
(2, 'test2', '2023-08-20 14:30:00'),
(3, 'test3', '2024-02-10 09:15:00');

— 查询测试数据
SELECT * FROM test_partition WHERE create_time >= '2023-06-01 00:00:00';

注意事项：