第52回　MySQLのパーティショニング機能

パーティショニングとは

パーティショニングとは、1つのテーブルデータを分割して管理する仕組みのことです。MySQLでは、バージョン5.1から標準でパーティショニングの機能が提供されています。

パーティショニングを行うことで巨大なデータを複数に分割でき、分割されたデータをまるごと削除したり、SELECT時に特定のパーティションを参照を指定することで、特定のパーティションのみをSELECTの対象として絞り込むことができます。一般的に、データベースのパーティションには、行をベースに分割する水平パーティションと、カラムを分割する垂直パーティションの2種類ありますが、現在MySQL5.7でサポートしているのは水平パーティションのみとなっています。

今回は、このパーティショニングの機能について紹介していきたいと思います。なお今回の例では、InnoDBストレージエンジンによるパーティションを見ていきます。

パーティショニングの種類

パーティショニングには複数の種類があります。

RANGEパーティショニング

範囲で指定されるパーティショニング方法になります。数値型を用いて、事前に定義した範囲に収まる位置にデータが格納されます。データの格納位置は、VALUES LESS THAN 演算子を用いて評価されます。

作成例

REATE TABLE t1 (
    id int NOT NULL,
    days DATE NOT NULL
    )
    PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (3),
    PARTITION p1 VALUES LESS THAN (5),
    PARTITION p2 VALUES LESS THAN (10),
    PARTITION p3 VALUES LESS THAN (15)
);

LISTパーティショニング

事前に定義したデータのリストの情報をもとにパーティショニングする方法になります。パーティショニングを定義する際に、特定のカラムのデータのリストをセットし、その情報をもとにVALUES IN (データの値) で評価されます。

作成例

CREATE TABLE t2 (
   id int(2),
   days date
 ) ENGINE=InnoDB
    PARTITION BY LIST(id)(
    PARTITION p1 VALUES IN (1,2),
    PARTITION p2 VALUES IN (3,4,5),
    PARTITION p3 VALUES IN (6,7,8,9,10),
    PARTITION p4 VALUES IN (11,15)
    );

HASHパーティショニング

HASHパーティショニングは整数型のカラムをもとにHASH値を定め、MySQLが自動でデータを均等にパーティショニングします。

作成例

CREATE TABLE t3 (
   id int(2),
   days date
 ) ENGINE=InnoDB
    PARTITION BY HASH(id)
    PARTITIONS 10;
;

HASH値の計算式は以下の通りです。

N = MOD(<整数型のカラム>, <分割数>)

KEYパーティショニング

KEYパーティショニングは主キーまたはユニークキーを内部でハッシュ化してパーティショニングします。

作成例

CREATE TABLE t4 (
   id int(2) PRIMARY KEY,
   days date
 ) ENGINE=InnoDB
    PARTITION BY KEY()
    PARTITIONS 10;
;

パーティションテーブルの操作方法

では実際にパーティショニングされたテーブルを作成、操作していきたいと思います。今回は、RANGE COLUMNSパーティショニングを用いて日付を分割する例を見ていきます。

パーティショニングされたテーブルを定義する場合、以下のようにPARTITION BYを用いてステートメントにパーティショニングする条件と分割するテーブルを記述していきます。

CREATE TABLE `t1` (
  `id` int(11),
  `days` date
) ENGINE=InnoDB
    PARTITION BY RANGE (year(days))
PARTITION p0 VALUES LESS THAN (1900) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1950) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2050) ENGINE = InnoDB)
;

生成されたテーブルを確認してみます。

show create table t1\G
*************************** 1. row ***************************
      Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `days` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
/*!50100 PARTITION BY RANGE (year(days))
(PARTITION p0 VALUES LESS THAN (1900) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1950) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2050) ENGINE = InnoDB) */
1 row in set (0.00 sec)

さらに、上記テーブルにデータを挿入してみましょう。

mysql > BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql > INSERT INTO t1(id, days) VALUES (1,'1969-12-13'),(2,'1973-5-5'),(3,'1834-4-18'),(4,'2000-1-1'),(5,'1923-6-8'),(6,'2012-7-19'),(7,'1917-2-1'),(8,'1999-12-31'),(9,'2017-8-15'),(10,'2005-8-10');
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql > COMMIT;
Query OK, 0 rows affected (0.01 sec)

mysql > SELECT COUNT(*) FROM t1;
+----------+
| count(1) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

データを挿入することができました。

これらのデータは、実際は各パーティショニングされたテーブルに保持されています。実際に今これらのデータが各パーティショニングにどれくらい保持されているかは、INFORMATION_SCHEMAのPARTITIONSテーブルを参照することによって確認できます。

mysql >SELECT
    TABLE_NAME,
    PARTITION_NAME,
    TABLE_ROWS,
    AVG_ROW_LENGTH,
    DATA_LENGTH
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_NAME = 't1'
;+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1         | p0             |          1 |          16384 |       16384 |
| t1         | p1             |          2 |           8192 |       16384 |
| t1         | p2             |          3 |           5461 |       16384 |
| t1         | p3             |          4 |           4096 |       16384 |
+------------+----------------+------------+----------------+-------------+
4 rows in set (0.00 sec)

上記のようにPARTITION_NAMEにTABLE_ROWSの件数だけデータが挿入されていることが確認できます。

ここで対象のdatadirの対象のdatabaseからファイルを確認してみます。

$  ls -l t1#P#*.ibd
-rw-r----- 1 mysql mysql 98304  8月 15 12:52 2017 t1#P#p0.ibd
-rw-r----- 1 mysql mysql 98304  8月 15 12:52 2017 t1#P#p1.ibd
-rw-r----- 1 mysql mysql 98304  8月 15 12:53 2017 t1#P#p2.ibd
-rw-r----- 1 mysql mysql 98304  8月 15 12:52 2017 t1#P#p3.ibd

idbファイルが4つ生成されています。パーティショニングを用いるとデータは各ファイルに格納されていますが、それをまとめて1つのテーブルのように見せかけています。

NULL値やパーティションの条件にそぐわない値をどう扱うのか

パーティショニングのKEYとして、NULL値やパーティションの条件にそぐわない値を扱う場合を考えてみます。下記のようなRANGEパーティショニングに対して、NULL値とパーティショニングの条件にそぐわないid=16を挿入してみます。

CREATE TABLE `t2` (
  `id` int(11),
  `days` date
) ENGINE=InnoDB
PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (3) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (15) ENGINE = InnoDB)
 ;

mysql > INSERT INTO t2(id, days) VALUES(null,'2017-08-13');
Query OK, 1 row affected (0.01 sec)

mysql > INSERT INTO t2(id, days) VALUES(16,'2017-08-13');
ERROR 1526 (HY000): Table has no partition for value 16

上記のようにNULL値を挿入することはできましたが、id=16を挿入することはできませんでした。

MySQLのパーティショニングは、厳密には各パーティショニングの種類によってパーティショニングするKEYのNULL値の扱い方が違います。詳細はMySQLのリファレンスマニュアル「MySQL パーティショニングによる NULL の扱い」をご確認ください。

パーティションの選択について

パーティションを選択してSQLを実行

パーティショニングされたテーブルに対して特定のパーティションのみにSQLを実行する場合は、PARTITIONオプションを追記します。t1テーブルで試してみましょう。

mysql > SELECT * FROM t1 PARTITION (p1);
+----+------------+
| id | days       |
+----+------------+
|  5 | 1923-06-08 |
|  7 | 1917-02-01 |
+----+------------+
2 rows in set (0.00 sec)

p1のパーティションに入っているデータのみを取得することができました。これは、SELECT * FROM t1 WHERE between days '1900-01-01' and '1949-12-31'と同じ意味になります。

実際に実行計画をとってみると、パーティション句なしの場合は全パーティションをスキャンしますが、PARTITION句をつけるとp1パーティションのみにアクセスしていることがわかります。

mysql >  EXPLAIN SELECT * FROM t1 PARTITION (p1);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | p1         | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql >  EXPLAIN SELECT * FROM t1;
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions  | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | p0,p1,p2,p3 | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | NULL  |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

パーティションプルーニング

パーティションプルーニングとは、WHERE句の条件によって不必要なパーティションをスキャンしない機能です。この機能はデータが膨大になった場合などに速度が大幅に向上することがあります。

パーティションプルーニングはWHERE句の条件が下記の条件のとき実行されます。

パーティションのKEYとなるカラム = （⁠または<、>、<=、>=,<>⁠）⁠ 値
パーティションのKEYとなるカラム in (値1,値2, ...)

先ほど作成したテーブルt1に対して、1930年以前のデータを取得する場合を想定します。1930年以前のデータはp0またはp1にのみ入っていることがパーティションの条件からわかります。なので、オプティマイザはp0、p1のみスキャンすればよいと判断するわけです。

実際に実行計画を取ってみます。

mysql > EXPLAIN SELECT * FROM t1 WHERE days < '1930-01-01';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | p0,p1      | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

partitionsがp0、p1となっていることが確認できました。

EXCHANGE PARTITIONについて

EXCHANGE PARTITIONは5.6で追加された機能で、パーティション分割されていないテーブルとパーティションテーブルを交換することができます。例として、t1テーブルと同じ構造をもち、パーティショニングされていないテーブルt3を作成します。

mysql >  CREATE TABLE t3 (
    ->      id int(11),
    ->      days date
    ->    ) ENGINE=InnoDB
    -> ;
Query OK, 0 rows affected (0.03 sec)

t1テーブルのp2パーティションを単独のテーブルとしてt3にコピーします。

EXCHANGE PARTITIONを実行する時は、ALTER TABLEにEXCHANGE PARTITION <パーティション名> WITH TABLE <交換するテーブル名>を用います。

mysql > SELECT COUNT(1) FROM t1;
+----------+
| COUNT(1) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

mysql > SELECT COUNT(*) FROM t1;
+----------+
| COUNT(1) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

mysql > SELECT COUNT(1) FROM t3;
+----------+
| COUNT(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql > SELECT
    ->     TABLE_NAME,
    ->     PARTITION_NAME,
    ->     TABLE_ROWS,
    ->     AVG_ROW_LENGTH,
    ->     DATA_LENGTH
    -> FROM
    ->     INFORMATION_SCHEMA.PARTITIONS
    -> WHERE
    ->     TABLE_NAME = 't1'
    -> ;
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1         | p0             |          1 |          16384 |       16384 |
| t1         | p1             |          2 |           8192 |       16384 |
| t1         | p2             |          3 |           5461 |       16384 |
| t1         | p3             |          4 |           4096 |       16384 |
+------------+----------------+------------+----------------+-------------+
4 rows in set (0.00 sec)

mysql > ALTER TABLE t1 EXCHANGE PARTITION p2 WITH TABLE t3;
Query OK, 0 rows affected (0.05 sec)

mysql > SELECT COUNT(*) FROM t1;
+----------+
| COUNT(1) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

mysql > SELECT COUNT(*) FROM t3;
+----------+
| COUNT(1) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql > SELECT * FROM t3;
+------+------------+
| id   | days       |
+------+------------+
|    1 | 1969-12-13 |
|    2 | 1973-05-05 |
|    8 | 1999-12-31 |
+------+------------+
3 rows in set (0.00 sec)

t1テーブルのp3パーティションに入っているデータがt3テーブルのデータと交換されていることが確認できました。

まとめ

今回はパーティショニングについて簡単に説明していきました。パーティショニングはデータの管理等には便利ですが、実際に使用する際には制約や制限があります。公式ドキュメントを参考に、パーティショニングを用いたデータ管理のしやすいテーブル設計、運用を行ってみてください。