パーティション(確認, 追加, 削除)

2022/11/12

ログ情報など膨大なデータを扱うとき、月毎にテーブルを作成する方法もありますが、テーブルがたくさん作られ管理しずらくなります。パーティショニングを利用すると、テーブルは1つのままで、内部で分割できます。

動作確認テーブル

下記クエリで生成したテーブルで動作確認します。

CREATE TABLE `test_logs` (
  `id` INT AUTO_INCREMENT,
  `message`  VARCHAR(255) NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`, `created_at`) 
);

以下のようにレコードが格納されています。

mysql> SELECT DATE_FORMAT(`created_at`, '%Y-%m') as `grouping_column`,
    ->        COUNT(`created_at`) as count
    -> FROM `test_logs`
    -> GROUP BY `grouping_column`;
+-----------------+-------+
| grouping_column | count |
+-----------------+-------+
| 2015-01         |   427 |
| 2015-02         |   376 |
| 2015-03         |   438 |
| 2015-04         |   405 |
| 2015-05         |   420 |
| 2015-06         |   397 |
| 2015-07         |   407 |
| 2015-08         |   449 |
| 2015-09         |   439 |
| 2015-10         |   403 |
| 2015-11         |   433 |
| 2015-12         |   427 |
| 2016-01         |   462 |
| 2016-02         |   374 |
| 2016-03         |   424 |
| 2016-04         |   390 |
| 2016-05         |   435 |
| 2016-06         |   416 |
| 2016-07         |   441 |
| 2016-08         |   415 |
| 2016-09         |   415 |
| 2016-10         |   426 |
| 2016-11         |   377 |
| 2016-12         |   414 |
+-----------------+-------+
24 rows in set (0.01 sec)

パーティション操作

パーティションの追加・確認

パーティションの種類は、いくつかあります。

LISTパーティショニング
RANGEパーティショニング
HASHパーティショニング

ここでは、月毎の範囲に基づいた RANGEパーティション を作ってみます。

パーティション追加前

mysql> SELECT TABLE_SCHEMA,
    ->        TABLE_NAME,
    ->        PARTITION_NAME,
    ->        PARTITION_ORDINAL_POSITION,
    ->        TABLE_ROWS
    -> FROM INFORMATION_SCHEMA.PARTITIONS
    -> WHERE TABLE_NAME='test_logs';
+--------------+------------+----------------+----------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
+--------------+------------+----------------+----------------------------+------------+
| sample       | test_logs  | NULL           |                       NULL |      10010 |
+--------------+------------+----------------+----------------------------+------------+
1 row in set (0.01 sec)

パーティション追加

ALTER TABLE `test_logs`
PARTITION BY RANGE COLUMNS(`created_at`) (
  PARTITION  p201501 VALUES LESS THAN ('2015-02-01 00:00:00'),
  PARTITION  p201502 VALUES LESS THAN ('2015-03-01 00:00:00'),
  PARTITION  p201503 VALUES LESS THAN ('2015-04-01 00:00:00'),
  PARTITION  p201504 VALUES LESS THAN ('2015-05-01 00:00:00'),
  PARTITION  p201505 VALUES LESS THAN ('2015-06-01 00:00:00'),
  PARTITION  p201506 VALUES LESS THAN ('2015-07-01 00:00:00'),
  PARTITION  p201507 VALUES LESS THAN ('2015-08-01 00:00:00'),
  PARTITION  p201508 VALUES LESS THAN ('2015-09-01 00:00:00'),
  PARTITION  p201509 VALUES LESS THAN ('2015-10-01 00:00:00'),
  PARTITION  p201510 VALUES LESS THAN ('2015-11-01 00:00:00'),
  PARTITION  p201511 VALUES LESS THAN ('2015-12-01 00:00:00'),
  PARTITION  p201512 VALUES LESS THAN ('2016-01-01 00:00:00'),
  PARTITION  p201601 VALUES LESS THAN ('2016-02-01 00:00:00'),
  PARTITION  p201602 VALUES LESS THAN ('2016-03-01 00:00:00'),
  PARTITION  p201603 VALUES LESS THAN ('2016-04-01 00:00:00'),
  PARTITION  p201604 VALUES LESS THAN ('2016-05-01 00:00:00'),
  PARTITION  p201605 VALUES LESS THAN ('2016-06-01 00:00:00'),
  PARTITION  p201606 VALUES LESS THAN ('2016-07-01 00:00:00'),
  PARTITION  p201607 VALUES LESS THAN ('2016-08-01 00:00:00'),
  PARTITION  p201608 VALUES LESS THAN ('2016-09-01 00:00:00'),
  PARTITION  p201609 VALUES LESS THAN ('2016-10-01 00:00:00'),
  PARTITION  p201610 VALUES LESS THAN ('2016-11-01 00:00:00'),
  PARTITION  p201611 VALUES LESS THAN ('2016-12-01 00:00:00'),
  PARTITION  p201612 VALUES LESS THAN ('2017-01-01 00:00:00')
);

パーティション追加後

mysql> SELECT TABLE_SCHEMA,
    ->        TABLE_NAME,
    ->        PARTITION_NAME,
    ->        PARTITION_ORDINAL_POSITION,
    ->        TABLE_ROWS
    -> FROM INFORMATION_SCHEMA.PARTITIONS
    -> WHERE TABLE_NAME='test_logs';
+--------------+------------+----------------+----------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
+--------------+------------+----------------+----------------------------+------------+
| sample       | test_logs  | p201501        |                          1 |        427 |
| sample       | test_logs  | p201502        |                          2 |        376 |
| sample       | test_logs  | p201503        |                          3 |        438 |
| sample       | test_logs  | p201504        |                          4 |        405 |
| sample       | test_logs  | p201505        |                          5 |        420 |
| sample       | test_logs  | p201506        |                          6 |        397 |
| sample       | test_logs  | p201507        |                          7 |        407 |
| sample       | test_logs  | p201508        |                          8 |        449 |
| sample       | test_logs  | p201509        |                          9 |        439 |
| sample       | test_logs  | p201510        |                         10 |        403 |
| sample       | test_logs  | p201511        |                         11 |        433 |
| sample       | test_logs  | p201512        |                         12 |        427 |
| sample       | test_logs  | p201601        |                         13 |        462 |
| sample       | test_logs  | p201602        |                         14 |        374 |
| sample       | test_logs  | p201603        |                         15 |        424 |
| sample       | test_logs  | p201604        |                         16 |        390 |
| sample       | test_logs  | p201605        |                         17 |        435 |
| sample       | test_logs  | p201606        |                         18 |        416 |
| sample       | test_logs  | p201607        |                         19 |        441 |
| sample       | test_logs  | p201608        |                         20 |        415 |
| sample       | test_logs  | p201609        |                         21 |        415 |
| sample       | test_logs  | p201610        |                         22 |        426 |
| sample       | test_logs  | p201611        |                         23 |        377 |
| sample       | test_logs  | p201612        |                         24 |        414 |
+--------------+------------+----------------+----------------------------+------------+
24 rows in set (0.02 sec)

パーティション指定の参照

パーティションを作成すると、以下のようにパーティション指定で参照できます。

mysql> SELECT COUNT(*)
    -> FROM `test_logs`;
+----------+
| COUNT(*) |
+----------+
|    10010 |
+----------+
1 row in set (0.01 sec)
mysql> 
mysql> 
mysql> SELECT COUNT(*)
    -> FROM `test_logs`
    -> PARTITION (`p201611`, `p201612`);
+----------+
| COUNT(*) |
+----------+
|      791 |
+----------+
1 row in set (0.00 sec)

パーティションの削除( データも削除 )

パーティションの削除を行うと、そのパーティションに格納されていたレコードも削除されるので注意が必要です。

mysql> SELECT COUNT(*)
    -> FROM `test_logs`;
+----------+
| COUNT(*) |
+----------+
|    10010 |
+----------+
1 row in set (0.01 sec)

mysql> 
mysql> 
mysql> ALTER TABLE `test_logs` DROP PARTITION p201612;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT COUNT(*)
    -> FROM `test_logs`;
+----------+
| COUNT(*) |
+----------+
|     9596 |
+----------+
1 row in set (0.01 sec)

パーティション化を解除

mysql> ALTER TABLE `test_logs` REMOVE PARTITIONING;
Query OK, 9596 rows affected (0.44 sec)
Records: 9596  Duplicates: 0  Warnings: 0

注意点

パーティションのカラムはキーである必要がある

パーティショニング式で使用されるすべてのカラムは、テーブルが持つことができるすべての一意キーの一部である必要があります。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 19.6.1 パーティショニングキー、主キー、および一意キーに上記記載されています。

試しに、キーでないカラムをパーティショニング式で使用したとき、以下エラーとなりました。

A PRIMARY KEY must include all columns in the table's partitioning function

パーティションが作られてない範囲にレコード挿入

mysql> INSERT INTO `test_logs`
    -> (`message`,`created_at`)
    -> VALUES
    -> ("xxxxxxx", '2017-01-10');
ERROR 1526 (HY000): Table has no partition for value from column_list

2017-01-10 を格納できるパーティションがまだ作成されてないため、エラーになっています。