Partitionテーブル

PARTITIONING

PostgreSQL Partition

PostgreSQLでは、RANGE、LIST、HASHパーティショニングをサポートしています。

  • PostgreSQLによるパーティション設定例
CREATE SEQUENCE travel_data_id START 1;



CREATE TABLE sample_travel_data (
id  bigint not null default nextval('travel_data_id'),
dept_date date not null,
dept_time time without time zone not null,
arrival_date date not null,
arrival_time time without time zone not null,
transit_flag int not null default 0,
travel_name varchar(100) not null default 'free plan',
curreated_at timestamp without time zone not null,
updated_at timestamp without time zone not null,
PRIMARY KEY (id,dept_date)
) PARTITION BY RANGE (dept_date);


CREATE TABLE travel_y2022m01 PARTITION OF sample_travel_data
    FOR VALUES FROM ('2022-01-01') TO ('2022-01-31');

CREATE TABLE travel_y2022m02 PARTITION OF sample_travel_data
    FOR VALUES FROM ('2022-02-01') TO ('2022-02-28');

CREATE TABLE travel_y2022m03 PARTITION OF sample_travel_data
    FOR VALUES FROM ('2022-03-01') TO ('2022-03-31');


oversea=> insert into sample_travel_data(dept_date,dept_time,arrival_date,arrival_time,curreated_at,updated_at) values('2022-01-01','12:30','2022-01-02','10:30',current_timestamp,current_timestamp);
INSERT 0 1
oversea=> insert into sample_travel_data(dept_date,dept_time,arrival_date,arrival_time,curreated_at,updated_at) values('2022-01-11','12:30','2022-01-02','10:30',current_timestamp,current_timestamp);
INSERT 0 1
oversea=> insert into sample_travel_data(dept_date,dept_time,arrival_date,arrival_time,curreated_at,updated_at) values('2022-01-21','12:30','2022-01-02','10:30',current_timestamp,current_timestamp);
INSERT 0 1
oversea=> insert into sample_travel_data(dept_date,dept_time,arrival_date,arrival_time,curreated_at,updated_at) values('2022-02-21','12:30','2022-02-23','10:30',current_timestamp,current_timestamp);
INSERT 0 1
oversea=> insert into sample_travel_data(dept_date,dept_time,arrival_date,arrival_time,curreated_at,updated_at) values('2022-02-21','12:30','2022-02-22','10:30',current_timestamp,current_timestamp);
INSERT 0 1
oversea=> insert into sample_travel_data(dept_date,dept_time,arrival_date,arrival_time,curreated_at,updated_at) values('2022-02-10','12:30','2022-02-12','10:30',current_timestamp,current_timestamp);
INSERT 0 1
oversea=> insert into sample_travel_data(dept_date,dept_time,arrival_date,arrival_time,curreated_at,updated_at) values('2022-03-10','12:30','2022-03-12','10:30',current_timestamp,current_timestamp);
INSERT 0 1
oversea=> 


oversea=> CREATE INDEX ON travel_y2022m01(dept_date);
CREATE INDEX
oversea=> CREATE INDEX ON travel_y2022m02(dept_date);
CREATE INDEX
oversea=> CREATE INDEX ON travel_y2022m03(dept_date);
CREATE INDEX
oversea=> 

留意:ALTER TABLEする時は、パーティションの子テーブルに継承する必用があるので、ONLYは付けない様にしてください。

  • パーティション環境でのEXPLAIN 
POC=# explain analyze select * from sample_travel_data where dept_date = '2022-01-01';
                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Seq Scan on travel_y2022m01 sample_travel_data  (cost=0.00..1.04 rows=1 width=270) (actual time=0.006..0.007 rows=1 loops=1)
   Filter: (dept_date = '2022-01-01'::date)
   Rows Removed by Filter: 2
 Planning Time: 1.240 ms
 Execution Time: 0.055 ms
(5 rows)

POC=#

Partition on PostgreSQL

  • データ分散状況の確認
/*** Database Objects ***/

CREATE SEQUENCE sample_data_id START 1;

CREATE TABLE sample_partition (
id  bigint not null default nextval('sample_data_id'),
note varchar(100) not null default 'none',
PRIMARY KEY (id)
) PARTITION BY RANGE (id);


/*** Sample Partition ****/

CREATE TABLE sample_pm01 PARTITION OF sample_partition
    FOR VALUES FROM (1) TO (300);

CREATE TABLE sample_pm02 PARTITION OF sample_partition
    FOR VALUES FROM (300) TO (600);

CREATE TABLE sample_pm03 PARTITION OF sample_partition
    FOR VALUES FROM (600) TO (1001);


/*** Loop Insert Dummy Data ***/

do $$
begin
for r in 1..1000 loop
insert into public.sample_partition(id) values(r);
end loop;
end;
$$;

上記で作成したサンプルテーブルにデータを流し込んだ結果

データ分散状況の確認

※備考: (上記サンプル処理では、Default値として設定したSequenceを無視してしまっているので、データ追加時にエラーになるので適宜処理や オブジェクト設計を考慮しましょう。)

POC=# select setval('sample_data_id',1, false);
 setval
--------
      1
(1 row)

POC=# insert into sample_partition(note) values('This is Sqeuence Test');
ERROR:  duplicate key value violates unique constraint "sample_pm01_pkey"
DETAIL:  Key (id)=(1) already exists.
POC=# select setval('sample_data_id',1001, false);
 setval
--------
   1001
(1 row)

POC=# CREATE TABLE sample_pm04 PARTITION OF sample_partition
POC-#     FOR VALUES FROM (1001) TO (2001);
CREATE TABLE
POC=# insert into sample_partition(note) values('This is Sqeuence Test');
INSERT 0 1
POC=# select * from sample_partition order by id desc limit 3;
  id  |         note
------+-----------------------
 1001 | This is Sqeuence Test
 1000 | none
  999 | none
(3 rows)

こんな感じであれば設定してあるSequecnceを使えますね。「本番環境で運用する時も、ここら辺はきちんと考慮しないといけませんね」

POC=# truncate table sample_partition;
TRUNCATE TABLE
POC=# select * from sample_partition;
 id | note
----+------
(0 rows)

POC=# select setval('sample_data_id', 1, false);
 setval
--------
      1
(1 row)

POC=# do $$
POC$# begin
POC$# for r in 1..1000 loop
POC$# insert into public.sample_partition(note) values('パーティションへのデータ投入');
POC$# end loop;
POC$# end;
POC$# $$;
DO
POC=# select * from sample_partition limit 3;
 id |             note
----+------------------------------
  1 | パーティションへのデータ投入
  2 | パーティションへのデータ投入
  3 | パーティションへのデータ投入
(3 rows)

POC=# select * from sample_partition order by id desc limit 3;
  id  |             note
------+------------------------------
 1000 | パーティションへのデータ投入
  999 | パーティションへのデータ投入
  998 | パーティションへのデータ投入
(3 rows)

POC=#
Explain on PostgreSQL Partition

TableをTruncateする予定があれば、以下の様にSequenceのオーナーテーブルの設定も必要ですね。

POC=# alter sequence sample_data_id owned by sample_partition.id;
ALTER SEQUENCE
Time: 4.657 ms


POC=# \d+ sample_data_id;
                       Sequence "public.sample_data_id"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
Owned by: public.sample_partition.id

POC=# select * from sample_data_id;
 last_value | log_cnt | is_called
------------+---------+-----------
       1000 |      23 | t
(1 row)

Time: 1.088 ms
POC=# TRUNCATE TABLE sample_partition RESTART IDENTITY;
TRUNCATE TABLE
Time: 47.883 ms
POC=# select * from sample_data_id;
 last_value | log_cnt | is_called
------------+---------+-----------
          1 |       0 | f
(1 row)

Time: 1.400 ms
POC=# do $$
POC$# begin
POC$# for r in 1..50000 loop
POC$# insert into public.sample_partition(id) values(r);
POC$# end loop;
POC$# end;
POC$# $$;
DO
Time: 559.106 ms
POC=# select count(*) from sample_partition;
 count
-------
 50000
(1 row)

Time: 12.934 ms
POC=#
Partition and Sequence Owner

参照:5.11. テーブルのパーティショニング

MySQL Partition

MySQLでは、RANGE、LIST、HASH、KEYパーティショニングをサポートしています。

  • RANGEパーティション
CREATE TABLE T_RANGE (
id int NOT NULL,
note varchar(1024) NOT NULL DEFAULT '',
updated_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(updated_date) ) (
PARTITION p202112 VALUES LESS THAN ( UNIX_TIMESTAMP('2021-12-01 00:00:00') ),
PARTITION p202201 VALUES LESS THAN ( UNIX_TIMESTAMP('2022-01-01 00:00:00') ),
PARTITION p202202 VALUES LESS THAN ( UNIX_TIMESTAMP('2022-02-01 00:00:00') ),
PARTITION p202203 VALUES LESS THAN ( UNIX_TIMESTAMP('2022-03-01 00:00:00') ),
PARTITION p999999 VALUES LESS THAN (MAXVALUE)
);
MySQL Partition Table
  • RANGE COLUMNSパーティション
CREATE TABLE `T_RANGE_COLUMN` (
  `id` int unsigned NOT NULL,
  `note` varchar(40) NOT NULL DEFAULT '',
  `AccessDate` date NOT NULL,
  `AccessTime` time NOT NULL,
  KEY `idx_AccessDate` (`AccessDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
/*!50500 PARTITION BY RANGE  COLUMNS(AccessDate)
(PARTITION P202112 VALUES LESS THAN ('2021-12-01') ENGINE = InnoDB,
 PARTITION P202201 VALUES LESS THAN ('2022-01-01') ENGINE = InnoDB,
 PARTITION P202202 VALUES LESS THAN ('2022-02-01') ENGINE = InnoDB,
 PARTITION P202203 VALUES LESS THAN ('2022-03-01') ENGINE = InnoDB,
 PARTITION P202204 VALUES LESS THAN ('2022-04-01') ENGINE = InnoDB,
 PARTITION P202205 VALUES LESS THAN ('2022-05-01') ENGINE = InnoDB,
 PARTITION P202206 VALUES LESS THAN ('2022-06-01') ENGINE = InnoDB,
 PARTITION P202207 VALUES LESS THAN ('2022-07-01') ENGINE = InnoDB,
 PARTITION P202208 VALUES LESS THAN ('2022-08-01') ENGINE = InnoDB,
 PARTITION P202209 VALUES LESS THAN ('2022-09-01') ENGINE = InnoDB,
 PARTITION P202210 VALUES LESS THAN ('2022-10-01') ENGINE = InnoDB,
 PARTITION P202211 VALUES LESS THAN ('2022-11-01') ENGINE = InnoDB,
 PARTITION P202212 VALUES LESS THAN ('2022-12-01') ENGINE = InnoDB) */
  • パーティション環境でのEXPLAIN 

特定のパーティションからデータを読み込んでいる事が分かる。ここでは、p202202からデータを参照している。 インデックスを利用して、特定のデータをピンポイントで参照出来る場合はパーティションを組まないでも良いが、特定の月や日からまとめてデータを持ってくる場合は、インデックスが適切に利用出来ない事があるので、その場合は特定のパーティションからデータを参照する様にした方が高速化出来る。

mysql> CREATE TABLE T_RANGE (
    -> id int NOT NULL,
    -> note varchar(1024) NOT NULL DEFAULT '',
    -> updated_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    -> )
    -> PARTITION BY RANGE ( UNIX_TIMESTAMP(updated_date) ) (
    -> PARTITION p202112 VALUES LESS THAN ( UNIX_TIMESTAMP('2021-12-01 00:00:00') ),
    -> PARTITION p202201 VALUES LESS THAN ( UNIX_TIMESTAMP('2022-01-01 00:00:00') ),
    -> PARTITION p202202 VALUES LESS THAN ( UNIX_TIMESTAMP('2022-02-01 00:00:00') ),
    -> PARTITION p202203 VALUES LESS THAN ( UNIX_TIMESTAMP('2022-03-01 00:00:00') ),
    -> PARTITION p999999 VALUES LESS THAN (MAXVALUE)
    -> );
Query OK, 0 rows affected (0.15 sec)

mysql> insert into T_RANGE(id,note) values(1,"This is Parition Table");
Query OK, 1 row affected (0.01 sec)

mysql> insert into T_RANGE(id,note) values(2,"This is Parition Table");
Query OK, 1 row affected (0.01 sec)

mysql> insert into T_RANGE(id,note) values(3,"This is Parition Table");
Query OK, 1 row affected (0.01 sec)

mysql> insert into T_RANGE(id,note) values(4,"This is Parition Table");
Query OK, 1 row affected (0.01 sec)

mysql> insert into T_RANGE(id,note) values(5,"This is Parition Table");
Query OK, 1 row affected (0.01 sec)

mysql> select * from T_RANGE;
+----+------------------------+---------------------+
| id | note                   | updated_date        |
+----+------------------------+---------------------+
|  1 | This is Parition Table | 2022-01-26 22:23:47 |
|  2 | This is Parition Table | 2022-01-26 22:23:59 |
|  3 | This is Parition Table | 2022-01-26 22:24:08 |
|  4 | This is Parition Table | 2022-01-26 22:24:12 |
|  5 | This is Parition Table | 2022-01-26 22:24:16 |
+----+------------------------+---------------------+
5 rows in set (0.00 sec)

mysql> explain select * from T_RANGE where updated_date > '2022-01-01' and updated_date < '2022-01-31'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: T_RANGE
   partitions: p202202
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql>

パーティションテーブルとインデックスの利用状況

以下の例では、日付でパーティションを設定してデータを分割して且つインデックスで処理出来ています。その為、少ないデータをメモリーにロード出来ると同時にアクセスするファイルI/Oを最小限にする事が出来ています。

Explain on MySQL Partition

MySQL Explain Partition

パーティションには制限事項も多いのでマニュアルをきちんと読んで、運用に入る前に検証しておきましょう。

備考:パーティションテーブルも他のオブジェクト同様にInformation_Schemaから確認可能です。

mysql> select TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,TABLE_ROWS from INFORMATION_SCHEMA.PARTITIONS where table_schema = 'POC';
+--------------+--------------------------------+----------------+----------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME                     | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
+--------------+--------------------------------+----------------+----------------------------+------------+
| POC          | T1                             | NULL           |                       NULL |          0 |
| POC          | T_JSON                         | NULL           |                       NULL |          0 |
| POC          | T_RANGE                        | p202112        |                          1 |          0 |
| POC          | T_RANGE                        | p202201        |                          2 |          0 |
| POC          | T_RANGE                        | p202202        |                          3 |          0 |
| POC          | T_RANGE                        | p202203        |                          4 |          0 |
| POC          | T_RANGE                        | p999999        |                          5 |          0 |
| POC          | members                        | NULL           |                       NULL |          3 |
| POC          | p11-10_12-jgd-g_busstop        | NULL           |                       NULL |       9832 |
| POC          | p11-10_13-jgd-g_busstop        | NULL           |                       NULL |       8526 |
| POC          | p11-10_14-jgd-g_busstop        | NULL           |                       NULL |       8521 |
| POC          | p12-10-g_tourismresource_point | NULL           |                       NULL |       2019 |
| POC          | tourism_and_busstop            | NULL           |                       NULL |      29950 |
| POC          | trip_generated_column_unquote  | NULL           |                       NULL |          3 |
| POC          | zone_area                      | NULL           |                       NULL |          0 |
+--------------+--------------------------------+----------------+----------------------------+------------+
15 rows in set (0.09 sec)

参照:

24.2 パーティショニングタイプ

24.6.1 パーティショニングキー、主キー、および一意キー

24.6 パーティショニングの制約と制限

Partition Table

カテゴリー:

最近のコメント

表示できるコメントはありません。