分区表

简介

数据库表分区是一种数据库优化的技术，可以将大表物理上分割成多个较小的部分以提高查询性能。每个部分称为一个分区，这些分区可以存储在不同的存储设备上。

• 优点：
  • 提高查询性能：通过减少扫描的数据量使查询性能有显著地提升。
  • 优化存储：通过把不同分区存放到不同的存储介质上，来平衡性能和成本。
  • 增加可维护性：分区表的维护操作（数据清理、重建索引）可以从分区的粒度来进行，减少对整个系统的影响。
  • 提高并发性：分区表可以提高并发性，因为多个分区可以并行处理。例如，多个查询可以同时访问不同的分区，而不会相互干扰。
• 缺点：
  • 内存资源占用：分区表使用内存大致为（分区数 * 3 / 1024）MB，当分区数太多导致内存不足时，会间接导致性能急剧下降。
  • 分区策略复杂性：制定和实施合适的分区策略需要技术知识和经验。如果分区策略选择不当，可能会导致数据分布不均衡，进一步影响性能。
  • 备份恢复的复杂性：虽然可以单独备份和恢复分区，但这也意味着需要更细致的备份策略和管理工作。

注意事项

• 适合使用分区表的场景
  • 提高查询性能：表的数据量大，且具有某些特性的数据在其中某个场景中经常会用到，可以通过减少查询时扫描数据量来提高性能。如：经常以月、季度、年为单位做分析的表。
  • 平衡性能和成本：表的数据量过大，需要将冷数据（不常访问的数据）移动到低成本存储，而将热数据（频繁访问的数据）保留在高性能存储上。
  • 大数据量表管理：表的数据量过大，需要在多个存储介质上存储的场景。
• 分区键的选择：

  选择分区表的分区键是一个重要的设计决策，因为它直接影响到数据库的性能、可维护性以及数据管理的效率。

  • 优化查询：选择常用查询的分区键。例如，经常根据日期进行查询的表，可以选择日期字段为分区键。
  • 数据分布：选择分区键时，考虑数据的分布情况。避免某些分区数据量过大而其他分区数据量过小的情况。
  • 分区数量和管理：控制分区数量，避免创建过多分区，分区过多会导致管理复杂度上升和性能下降。

分区表类型

示例

--范围分区表示例1。
gaussdb=# CREATE TABLE test_range1(
    id INT, 
    info VARCHAR(20)
) PARTITION BY RANGE (id) (
    PARTITION p1 VALUES LESS THAN (100), 
    PARTITION p2 VALUES LESS THAN (200),
    PARTITION p3 VALUES LESS THAN (300),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE

--范围分区表示例2。
gaussdb=# CREATE TABLE test_range2(
    id INT, 
    info VARCHAR(20)
) PARTITION BY RANGE (id) (
    PARTITION p1 START(1) END(600) EVERY(200),    
    PARTITION p2 START(600) END(800),
    PARTITION pmax START(800) END(MAXVALUE)
);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE

--列表分区表示例。
gaussdb=# CREATE TABLE test_list ( NAME VARCHAR ( 50 ), area VARCHAR ( 50 ) ) 
PARTITION BY LIST (area) (
    PARTITION p1 VALUES ('Beijing'),
    PARTITION p2 VALUES ('Shanghai'),
    PARTITION p3 VALUES ('Guangzhou'),
    PARTITION p4 VALUES ('Shenzhen'),
    PARTITION pdefault VALUES (DEFAULT)
);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'name' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE

--哈希分区表示例1。
gaussdb=# CREATE TABLE test_hash1(c1 int) PARTITION BY HASH(c1) PARTITIONS 3;
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE

--哈希分区表示例2。
gaussdb=# CREATE TABLE test_hash2(c1 int) PARTITION BY HASH(C1)(
    PARTITION pa,
    PARTITION pb,
    PARTITION pc
);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE

--删除表。
gaussdb=# DROP TABLE test_range1;
DROP TABLE
gaussdb=# DROP TABLE test_range2;
DROP TABLE
gaussdb=# DROP TABLE test_list;
DROP TABLE
gaussdb=# DROP TABLE test_hash1;
DROP TABLE
gaussdb=# DROP TABLE test_hash2;
DROP TABLE