KingBaseES 分区表与分区索引

分区表概述

分区表是将一个大表按照一定的规则分割成多个小表的技术，每个小表称为一个分区。分区表可以提高查询性能、简化数据管理和优化维护操作。KingBaseES 支持多种分区类型，包括范围分区、列表分区、哈希分区和复合分区。

分区表的优势

提高查询性能：查询可以只扫描相关分区，减少 I/O 操作。
简化数据管理：可以单独管理每个分区，如备份、恢复、删除等。
优化维护操作：维护操作（如 VACUUM、ANALYZE）可以针对单个分区进行，提高维护效率。
提高并发性能：多个分区可以并行操作，提高并发性能。
支持大数据量：可以处理更大的数据量，突破单表的大小限制。

分区表的适用场景

历史数据存储：按时间范围分区，便于管理和查询历史数据。
地理数据存储：按地区或地理位置分区，便于管理和查询地理数据。
业务数据存储：按业务类型或业务线分区，便于管理和查询业务数据。
大表查询优化：对查询频繁的大表进行分区，提高查询性能。
数据生命周期管理：对不同生命周期的数据进行分区，便于数据归档和清理。

分区类型

KingBaseES 支持以下分区类型：

1. 范围分区（Range Partitioning）

范围分区是根据列值的范围将表分割成多个分区，例如按时间范围、数值范围等。

适用场景

按时间范围分区（如按年、月、日分区）
按数值范围分区（如按 ID 范围、金额范围分区）
按连续值分区

创建范围分区表

sql

-- 按时间范围分区（按年）
CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    sale_date DATE NOT NULL
)
PARTITION BY RANGE (sale_date);

-- 创建分区
CREATE TABLE sales_2021 PARTITION OF sales
    FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

CREATE TABLE sales_2022 PARTITION OF sales
    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

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

-- 按数值范围分区（按 ID 范围）
CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    order_date DATE NOT NULL
)
PARTITION BY RANGE (order_id);

-- 创建分区
CREATE TABLE orders_1 PARTITION OF orders
    FOR VALUES FROM (0) TO (1000000);

CREATE TABLE orders_2 PARTITION OF orders
    FOR VALUES FROM (1000000) TO (2000000);

CREATE TABLE orders_3 PARTITION OF orders
    FOR VALUES FROM (2000000) TO (3000000);

2. 列表分区（List Partitioning）

列表分区是根据列值的离散值将表分割成多个分区，例如按地区、状态、类型等。

适用场景

按地区分区（如按省份、城市分区）
按状态分区（如按订单状态、用户状态分区）
按类型分区（如按产品类型、业务类型分区）
按离散值分区

创建列表分区表

sql

-- 按地区分区
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    region VARCHAR(50) NOT NULL
)
PARTITION BY LIST (region);

-- 创建分区
CREATE TABLE customers_north PARTITION OF customers
    FOR VALUES IN ('北京', '天津', '河北', '山西', '内蒙古');

CREATE TABLE customers_east PARTITION OF customers
    FOR VALUES IN ('上海', '江苏', '浙江', '安徽', '福建', '江西', '山东');

CREATE TABLE customers_south PARTITION OF customers
    FOR VALUES IN ('广东', '广西', '海南');

CREATE TABLE customers_west PARTITION OF customers
    FOR VALUES IN ('重庆', '四川', '贵州', '云南', '西藏');

-- 按状态分区
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL
)
PARTITION BY LIST (status);

-- 创建分区
CREATE TABLE orders_pending PARTITION OF orders
    FOR VALUES IN ('pending', 'processing');

CREATE TABLE orders_completed PARTITION OF orders
    FOR VALUES IN ('completed', 'shipped', 'delivered');

CREATE TABLE orders_cancelled PARTITION OF orders
    FOR VALUES IN ('cancelled', 'refunded');

3. 哈希分区（Hash Partitioning）

哈希分区是根据列值的哈希值将表分割成多个分区，用于将数据均匀分布到多个分区中。

适用场景

数据分布不均匀，需要均匀分布数据
没有明显的分区键范围或列表
希望查询负载均匀分布到多个分区

创建哈希分区表

sql

-- 按 customer_id 哈希分区
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    order_date DATE NOT NULL
)
PARTITION BY HASH (customer_id);

-- 创建分区
CREATE TABLE orders_hash_1 PARTITION OF orders
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE orders_hash_2 PARTITION OF orders
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE orders_hash_3 PARTITION OF orders
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE orders_hash_4 PARTITION OF orders
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 按 product_id 哈希分区
CREATE TABLE inventory (
    product_id INT NOT NULL,
    warehouse_id INT NOT NULL,
    quantity INT NOT NULL,
    last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (product_id, warehouse_id)
)
PARTITION BY HASH (product_id);

-- 创建分区
CREATE TABLE inventory_hash_1 PARTITION OF inventory
    FOR VALUES WITH (MODULUS 8, REMAINDER 0);

CREATE TABLE inventory_hash_2 PARTITION OF inventory
    FOR VALUES WITH (MODULUS 8, REMAINDER 1);

-- 创建其他分区...

4. 复合分区（Composite Partitioning）

复合分区是指同时使用两种或多种分区类型，例如先按范围分区，再按列表分区，或者先按范围分区，再按哈希分区。

适用场景

需要同时按多个维度分区
数据量非常大，需要更细粒度的分区
希望结合不同分区类型的优势

创建复合分区表

sql

-- 先按时间范围分区，再按地区列表分区
CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    sale_date DATE NOT NULL,
    region VARCHAR(50) NOT NULL
)
PARTITION BY RANGE (sale_date) SUBPARTITION BY LIST (region);

-- 创建分区模板
CREATE TABLE sales_range_template (
    sale_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    sale_date DATE NOT NULL,
    region VARCHAR(50) NOT NULL
)
PARTITION BY LIST (region);

-- 创建主分区
CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01')
    PARTITION BY LIST (region);

-- 创建子分区
CREATE TABLE sales_2023_north PARTITION OF sales_2023
    FOR VALUES IN ('北京', '天津', '河北');

CREATE TABLE sales_2023_east PARTITION OF sales_2023
    FOR VALUES IN ('上海', '江苏', '浙江');

CREATE TABLE sales_2023_south PARTITION OF sales_2023
    FOR VALUES IN ('广东', '广西', '海南');

CREATE TABLE sales_2023_west PARTITION OF sales_2023
    FOR VALUES IN ('四川', '云南', '贵州');

-- 先按范围分区，再按哈希分区
CREATE TABLE logs (
    log_id SERIAL PRIMARY KEY,
    log_time TIMESTAMP NOT NULL,
    log_level VARCHAR(20) NOT NULL,
    message TEXT NOT NULL,
    client_ip VARCHAR(50) NOT NULL
)
PARTITION BY RANGE (log_time) SUBPARTITION BY HASH (client_ip);

-- 创建主分区
CREATE TABLE logs_202301 PARTITION OF logs
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01')
    PARTITION BY HASH (client_ip);

-- 创建子分区
CREATE TABLE logs_202301_hash_1 PARTITION OF logs_202301
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE logs_202301_hash_2 PARTITION OF logs_202301
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE logs_202301_hash_3 PARTITION OF logs_202301
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE logs_202301_hash_4 PARTITION OF logs_202301
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

分区索引

分区表的索引可以分为两种类型：全局索引和局部索引。

1. 局部索引（Local Index）

局部索引是指每个分区有自己的索引，索引与分区一一对应。局部索引的优点是：

索引创建和维护效率高
分区维护（如添加、删除分区）不会影响其他分区的索引
查询时可以只扫描相关分区的索引

创建局部索引

sql

-- 创建局部索引
CREATE INDEX idx_sales_product_id ON sales(product_id);

-- 创建局部唯一索引
CREATE UNIQUE INDEX idx_sales_sale_id ON sales(sale_id);

-- 创建局部复合索引
CREATE INDEX idx_sales_product_id_sale_date ON sales(product_id, sale_date DESC);

2. 全局索引（Global Index）

全局索引是指跨越所有分区的索引，索引数据存储在一个单独的索引文件中。全局索引的优点是：

可以保证全局唯一性
适合范围查询跨越多个分区的场景

创建全局索引

sql

-- 创建全局索引
CREATE INDEX idx_sales_global ON sales(product_id) GLOBAL;

-- 创建全局唯一索引
CREATE UNIQUE INDEX idx_sales_global_unique ON sales(sale_id) GLOBAL;

分区索引的选择原则

局部索引优先：大多数情况下，局部索引是更好的选择，因为它具有更好的性能和可维护性。
全局唯一索引：如果需要保证全局唯一性，必须使用全局唯一索引。
范围查询跨越多个分区：如果查询经常跨越多个分区，全局索引可能更适合。
分区维护频率：如果频繁进行分区维护（如添加、删除分区），局部索引更适合。

分区表的管理

1. 添加分区

sql

-- 为范围分区表添加分区
ALTER TABLE sales ADD PARTITION sales_2024
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- 为列表分区表添加分区
ALTER TABLE customers ADD PARTITION customers_northeast
    FOR VALUES IN ('辽宁', '吉林', '黑龙江');

-- 为哈希分区表添加分区
ALTER TABLE orders ADD PARTITION orders_hash_5
    FOR VALUES WITH (MODULUS 5, REMAINDER 4);

2. 删除分区

sql

-- 删除分区（同时删除分区数据）
ALTER TABLE sales DROP PARTITION sales_2021;

-- 删除分区并保留数据（需要先创建表）
CREATE TABLE sales_2021 AS SELECT * FROM sales WHERE sale_date >= '2021-01-01' AND sale_date < '2022-01-01';
ALTER TABLE sales DETACH PARTITION sales_2021;

3. 拆分分区

sql

-- 拆分范围分区
ALTER TABLE sales SPLIT PARTITION sales_2023 AT ('2023-07-01') INTO (
    PARTITION sales_2023_half1,
    PARTITION sales_2023_half2
);

-- 拆分列表分区
ALTER TABLE customers SPLIT PARTITION customers_east VALUES IN ('上海', '江苏', '浙江') INTO (
    PARTITION customers_east_coastal,
    PARTITION customers_east_inland
);

4. 合并分区

sql

-- 合并范围分区
ALTER TABLE sales MERGE PARTITIONS sales_2023_half1, sales_2023_half2 INTO
    PARTITION sales_2023;

-- 合并列表分区
ALTER TABLE customers MERGE PARTITIONS customers_east_coastal, customers_east_inland INTO
    PARTITION customers_east;

5. 重命名分区

sql

-- 重命名分区
ALTER TABLE sales RENAME PARTITION sales_2023 TO sales_2023_old;

6. 交换分区

交换分区是指将分区与普通表交换，用于快速加载或卸载数据。

sql

-- 创建与分区结构相同的普通表
CREATE TABLE sales_2024_new (
    sale_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    sale_date DATE NOT NULL
);

-- 加载数据到普通表
COPY sales_2024_new FROM '/path/to/sales_2024.csv' WITH CSV HEADER;

-- 交换分区
ALTER TABLE sales EXCHANGE PARTITION sales_2024 WITH TABLE sales_2024_new;

分区表的查询优化

1. 分区裁剪（Partition Pruning）

分区裁剪是指查询优化器根据查询条件自动排除不需要扫描的分区，只扫描相关的分区，提高查询性能。

确保分区裁剪生效

sql

-- 查看查询计划，确认是否使用了分区裁剪
EXPLAIN SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

-- 示例输出（显示只扫描了 sales_2023 分区）
QUERY PLAN
-------------------------------------------------------------------
Append  (cost=0.00..35.50 rows=1000 width=24)
  ->  Seq Scan on sales_2023  (cost=0.00..35.50 rows=1000 width=24)
        Filter: ((sale_date >= '2023-01-01'::date) AND (sale_date <= '2023-12-31'::date))

优化分区裁剪

确保查询条件中包含分区键
避免在分区键上使用函数或表达式
确保统计信息是最新的，定期运行 ANALYZE

2. 分区wise Join

分区wise Join 是指优化器将两个分区表的 Join 操作分解为对应分区之间的 Join 操作，提高 Join 性能。

确保分区wise Join 生效

sql

-- 查看查询计划，确认是否使用了分区wise Join
EXPLAIN SELECT s.*, c.* FROM sales s JOIN customers c ON s.customer_id = c.customer_id WHERE s.sale_date BETWEEN '2023-01-01' AND '2023-12-31';

优化分区wise Join

确保两个表使用相同的分区键和分区策略
确保统计信息是最新的
调整 enable_partitionwise_join 参数

3. 并行查询

分区表支持并行查询，可以同时扫描多个分区，提高查询性能。

启用并行查询

sql

-- 设置并行查询参数
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
ALTER SYSTEM SET max_parallel_workers = 8;

-- 查看查询计划，确认是否使用了并行查询
EXPLAIN SELECT COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

分区表的维护

1. 收集统计信息

定期收集分区表的统计信息，确保查询优化器生成最优的查询计划。

sql

-- 收集整个分区表的统计信息
ANALYZE VERBOSE sales;

-- 收集单个分区的统计信息
ANALYZE VERBOSE sales_2023;

2. 重建索引

定期重建分区表的索引，提高索引性能。

sql

-- 重建整个分区表的索引
REINDEX TABLE sales;

-- 重建单个分区的索引
REINDEX TABLE sales_2023;

-- 重建单个索引
REINDEX INDEX idx_sales_product_id;

3. 清理分区表

定期清理分区表，回收不再使用的空间。

sql

-- 清理整个分区表
VACUUM VERBOSE sales;

-- 清理单个分区
VACUUM VERBOSE sales_2023;

-- 深度清理
VACUUM FULL VERBOSE sales_2023;

分区表的性能监控

1. 查看分区表的大小

sql

-- 查看分区表的总大小
SELECT pg_size_pretty(pg_total_relation_size('sales')) AS total_size;

-- 查看每个分区的大小
SELECT 
    inhparent::regclass AS parent_table,
    inhrelid::regclass AS partition_name,
    pg_size_pretty(pg_total_relation_size(inhrelid)) AS partition_size
FROM 
    sys_inherits
WHERE 
    inhparent = 'sales'::regclass;

2. 查看分区表的查询性能

sql

-- 查看分区表的查询统计信息
SELECT 
    relname AS table_name,
    idx_scan AS index_scans,
    seq_scan AS sequential_scans,
    idx_tup_read AS index_tuples_read,
    idx_tup_fetch AS index_tuples_fetched,
    n_tup_ins AS tuples_inserted,
    n_tup_upd AS tuples_updated,
    n_tup_del AS tuples_deleted,
    n_live_tup AS live_tuples,
    n_dead_tup AS dead_tuples
FROM 
    sys_stat_user_tables
WHERE 
    relname LIKE 'sales%';

3. 查看分区裁剪的效果

sql

-- 查看分区裁剪的统计信息
SELECT 
    relname AS table_name,
    partprune_stats
FROM 
    sys_stat_user_tables
WHERE 
    relname = 'sales';

分区表的版本差异

V8 与 V7 版本的差异

分区类型增强：
- V8 版本新增了哈希分区和复合分区支持。
- V8 版本增强了范围分区和列表分区的功能。
分区管理增强：
- V8 版本新增了更多的分区管理命令，如 SPLIT、MERGE、EXCHANGE 等。
- V8 版本支持在线分区管理，减少了对业务的影响。
查询优化增强：
- V8 版本优化了分区裁剪算法，提高了分区裁剪的准确性和性能。
- V8 版本新增了分区wise Join 功能，提高了分区表 Join 操作的性能。

V8R3 与 V8R2 版本的差异

分区性能提升：
- V8R3 版本优化了分区表的查询性能，特别是在大表查询场景下。
- V8R3 版本优化了分区索引的维护性能，减少了索引维护的开销。
分区管理增强：
- V8R3 版本支持更多的分区管理操作，如 ONLINE 分区管理。
- V8R3 版本新增了分区表的监控视图，便于 DBA 监控分区表的使用情况。
新分区特性：
- V8R3 版本新增了自动分区功能，可以自动创建和管理分区。
- V8R3 版本新增了分区表的压缩功能，减少了分区表的存储空间占用。

常见问题（FAQ）

如何选择合适的分区类型？

范围分区：适合按连续值分区，如时间、数值等。
列表分区：适合按离散值分区，如地区、状态等。
哈希分区：适合数据分布不均匀，需要均匀分布数据的场景。
复合分区：适合需要同时按多个维度分区的场景。

如何选择合适的分区键？

查询频率：选择查询中频繁使用的列作为分区键。
数据分布：选择数据分布均匀的列作为分区键。
业务需求：根据业务需求选择分区键，如按时间分区便于历史数据管理。
维护需求：选择便于维护的分区键，如按年分区便于每年的数据归档。

如何处理分区表的全局唯一性约束？

使用全局唯一索引：创建全局唯一索引来保证全局唯一性。
使用序列生成唯一ID：使用序列生成全局唯一的ID，避免冲突。
应用层保证唯一性：在应用层实现全局唯一性检查。

如何优化分区表的查询性能？

确保分区裁剪生效：查询条件中包含分区键，避免在分区键上使用函数。
使用合适的索引类型：大多数情况下，局部索引是更好的选择。
启用并行查询：对于大表查询，启用并行查询可以提高性能。
定期维护分区表：定期收集统计信息、重建索引和清理分区表。

如何处理分区表的历史数据？

归档旧分区：将旧分区数据归档到低成本存储设备。
删除旧分区：对于不再需要的旧分区，可以直接删除。
交换分区：使用分区交换功能快速卸载旧数据。
分区压缩：对旧分区进行压缩，减少存储空间占用。

总结

分区表是 KingBaseES 中用于管理大表的重要技术，可以提高查询性能、简化数据管理和优化维护操作。DBA 需要根据业务需求和数据特点选择合适的分区类型和分区键，合理设计和管理分区表。

在实际生产环境中，DBA 需要：

理解不同分区类型的特点和适用场景
选择合适的分区键和分区策略
合理设计分区索引
定期维护分区表，包括收集统计信息、重建索引和清理分区表
监控分区表的性能，及时优化
根据业务需求调整分区策略

通过合理的分区表设计和管理，可以提高 KingBaseES 数据库的性能和可维护性，支持更大的数据量和更复杂的业务需求。

KingBaseES 分区表与分区索引 ​

分区表概述 ​

分区表的优势 ​

分区表的适用场景 ​

分区类型 ​

1. 范围分区（Range Partitioning） ​

适用场景 ​

创建范围分区表 ​

2. 列表分区（List Partitioning） ​

适用场景 ​

创建列表分区表 ​

3. 哈希分区（Hash Partitioning） ​

适用场景 ​

创建哈希分区表 ​

4. 复合分区（Composite Partitioning） ​

适用场景 ​

创建复合分区表 ​

分区索引 ​

1. 局部索引（Local Index） ​

创建局部索引 ​

2. 全局索引（Global Index） ​

创建全局索引 ​

分区索引的选择原则 ​

分区表的管理 ​

1. 添加分区 ​

2. 删除分区 ​

3. 拆分分区 ​

4. 合并分区 ​

5. 重命名分区 ​

6. 交换分区 ​

分区表的查询优化 ​

1. 分区裁剪（Partition Pruning） ​

确保分区裁剪生效 ​

优化分区裁剪 ​

2. 分区wise Join ​

确保分区wise Join 生效 ​

优化分区wise Join ​

3. 并行查询 ​

启用并行查询 ​

分区表的维护 ​

1. 收集统计信息 ​

2. 重建索引 ​

3. 清理分区表 ​

分区表的性能监控 ​

1. 查看分区表的大小 ​

2. 查看分区表的查询性能 ​

3. 查看分区裁剪的效果 ​

分区表的版本差异 ​

V8 与 V7 版本的差异 ​

V8R3 与 V8R2 版本的差异 ​

常见问题（FAQ） ​

如何选择合适的分区类型？ ​

如何选择合适的分区键？ ​

如何处理分区表的全局唯一性约束？ ​

如何优化分区表的查询性能？ ​

如何处理分区表的历史数据？ ​

总结 ​

KingBaseES 分区表与分区索引

分区表概述

分区表的优势

分区表的适用场景

分区类型

1. 范围分区（Range Partitioning）

适用场景

创建范围分区表

2. 列表分区（List Partitioning）

适用场景

创建列表分区表

3. 哈希分区（Hash Partitioning）

适用场景

创建哈希分区表

4. 复合分区（Composite Partitioning）

适用场景

创建复合分区表

分区索引

1. 局部索引（Local Index）

创建局部索引

2. 全局索引（Global Index）

创建全局索引

分区索引的选择原则

分区表的管理

1. 添加分区

2. 删除分区

3. 拆分分区

4. 合并分区

5. 重命名分区

6. 交换分区

分区表的查询优化

1. 分区裁剪（Partition Pruning）

确保分区裁剪生效

优化分区裁剪

2. 分区wise Join

确保分区wise Join 生效

优化分区wise Join

3. 并行查询

启用并行查询

分区表的维护

1. 收集统计信息

2. 重建索引

3. 清理分区表

分区表的性能监控

1. 查看分区表的大小

2. 查看分区表的查询性能

3. 查看分区裁剪的效果

分区表的版本差异

V8 与 V7 版本的差异

V8R3 与 V8R2 版本的差异

常见问题（FAQ）

如何选择合适的分区类型？

如何选择合适的分区键？

如何处理分区表的全局唯一性约束？

如何优化分区表的查询性能？

如何处理分区表的历史数据？

总结