PostgreSQL的分区功能

2 年 ago
科, 雅
2 minutes

分区是什么意思

這是將一個邏輯上的大表格，在物理上分成小的分區。
分割有”表格間的分割”和”節點間的分割”兩種，但是這次我們專門研究了表格間的分割，所以這裡將對此進行總結。

桌子之间的分割

使用触发器将数据存储到每个子表（分区）中，从应用程序的角度来看，这些子表看起来像一个表。

スクリーンショット 2017-01-08 16.14.06.png

优点

– 通过分割来缩小搜索范围，提高处理效率。
– 可以批量删除分区（drop table）。
– 能够有效利用缓存。

缺点

由于使用触发器将插入操作分发到子表中，插入性能会变差。

分割方式

分割方法有两种，一种是水平分割，另一种是垂直分割。

    1. 水平分割是将表格的每一行分散到各自的分区的方法。可以根据用户的地址将其分割到每个省份的分区，或者按创建的月份进行分割。

垂直分割是将表格的一部分提取到分区的方法。只提取使用频率低的列或者非常大的列。

切割標準

在进行分割时，使用分割键来确定如何进行分割的音是分割的准则，可以分为“范围分割”、“列表分割”和“哈希分割”这三种类型。

    1. Range Partitioning: 根据分割键的值是否在范围内进行分割。

 

    1. List Partitioning: 根据分割键的值是否存在于列表中进行分割。

 

    Hash Partitioning: 根据哈希函数的值决定是否将其包含在分区中。

实际尝试一下

我将参考下面链接中的文件进行操作。
https://www.postgresql.jp/document/9.4/html/ddl-partitioning.html

创建一张桌子

首先创建主表（父表）。

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

创建各自的分区（子表）

这次我们将为每个月份创建分区。（在文档中进行了一些修改）

CREATE TABLE measurement_y2016m12 (
    CHECK ( logdate >= DATE '2016-12-01' AND logdate < DATE '2017-01-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2016m11 (
    CHECK ( logdate >= DATE '2016-11-01' AND logdate < DATE '2016-12-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2016m10 (
    CHECK ( logdate >= DATE '2016-10-01' AND logdate < DATE '2016-11-01' )
) INHERITS (measurement);

我們創建的這個表繼承於measurement（主表），所以結構是相同的。
此外，我們在每個表上都使用CHECK來進行日期約束。

改变设置

给它添加索引

CREATE INDEX measurement_y2016m12_logdate ON measurement_y2016m12 (logdate);
CREATE INDEX measurement_y2016m11_logdate ON measurement_y2016m11 (logdate);
CREATE INDEX measurement_y2016m10_logdate ON measurement_y2016m10 (logdate);

创建一个函数并设置触发器。

如果您想要将数据放入全新的分区，请按照以下方式进行设置。

--関数の作成
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO measurement_y2016m12 VALUES (NEW.*);
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

-- トリガの作成
CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON measurement
    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

处理数据

设置触发器，插入已设置的12月份数据。

INSERT INTO measurement(city_id, logdate, peaktemp, unitsales) VALUES(1, '2016-12-01', 20, 1000);
-- INSERT 0 0

SELECT * FROM measurement;
SELECT * FROM measurement_y2016m12;

插入值变成0，这是因为在内部取消了原始的插入操作，并对分区进行了另一个插入操作，所以显示为0。
选择操作可以进行，触发器可以很好地进行分配。

插入未设置触发器的数据。

只考虑12月份的数据尚未包含在内。
如果加入11月的数据，会产生什么结果呢？

INSERT INTO measurement(city_id, logdate, peaktemp, unitsales) VALUES(1, '2016-11-01', 20, 1000);

ERROR:  new row for relation "measurement_y2016m12" violates check constraint "measurement_y2016m12_logdate_check"
DETAIL:  Failing row contains (1, 2016-11-01, 20, 1000).
CONTEXT:  SQL statement "INSERT INTO measurement_y2016m12 VALUES (NEW.*)"
PL/pgSQL function measurement_insert_trigger() line 3 at SQL statement

这次果然出了错误。
11月的数据也分配到了12月的表中，看起来被检查的约束排斥了。
在设置分区时，必须注意输入的数据是什么样的。

总结

我认为，根据文件设置本身非常易懂。但是，最好测量一下处理效率的提升程度。

bannerAds