MySQL道普請便り

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

この記事を読むのに必要な時間:およそ 6.5 分

パーティショニングとは

パーティショニングとは,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つのテーブルのように見せかけています。

著者プロフィール

深町日出海(ふかまちひでみ)

GMOメディア株式会社のデータベースエンジニア。主にOracleとMySQLを担当。得意なプログラム言語はJava。MySQLの好きなところはTABLEやINDEXの識別子に64byteまで使えるところ。

Twitter:@fuki190989

コメント

コメントの記入