SQL: Data Definition: Table Partitioning

 20th August 2020 at 2:19pm

表格分区,指把一张数据库大的表,分散到多个小的物理存储块中。好处在于:

  • 查询性能得到显著提高,特别是热点行处于同个分区中时
  • 如果分区规划得好,使得查询或更新时数据大多处在同个分区中时,性能会大幅提升。因为可以利用硬盘的顺序读,而不用使用随机读取散落在各处的数据
  • 批量操作更快了

在数据库系统有分区功能之前,人工进行分表已经是常见操作。数据库直接提供分区直接,则方便了使用者。

分区在表特别大时才有意义。一般来讲特别大指表的数据量超过了 DB 服务器的物理内存大小。

使用 重要

建一张大表,表示按 logdata 字段分区:

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

然后建分区:

CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

...

这表示向 measurement 中插入数值时,会按 logdata 的值分到不同的分区去存储。如果 logdata 的值所处的 分区不存在则会报错

对于被用来分区的字段,建一个索引来加速查询是惯常的做法:

CREATE INDEX ON measurement (logdate);

删除数据 一般

删掉老数据是常见场景。有两种方法:

  • 直接 DROP 相应的表:DROP TABLE measurement_y2006m02。这个操作速度很快,因为不需要像没分区时去一张大表一行一行删除;但是它在删除期间仍然会给母表上一个 ACCESS EXCLUSIVE
  • DETACH 分区(推荐):ALTER TABLE measurement DETACH PARTITION measurement_y2006m02。这个操作不会删除数据,速度更快,不会上锁。后续对母表的查询,则再查不到这块数据

Partition Attachment 不重要

分区表也可以先在外部创建,再 attach 到母表上。这样做的好处是,可以从外部加载数据,对数据做校验或者清理工作后,再 attach 到母表上被使用。操作示例:

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
  TABLESPACE fasttablespace;

ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );

\copy measurement_y2008m02 from 'measurement_y2008m02'
-- possibly some other data preparation work

ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );

注意:第二步中的 CHECK 如果不做,attach 时分区表和母表都会上锁。Attach 后再把这个 check constraint 去掉即可。

其他 不重要

其他细节没有深究。有需要时再查 文档

分区的类型

  • Range Partitioning
  • List Partitioning
  • Hash Partitioning