(cache)MySQL パーティショニングで高速クエリーを実現！！

f:id:hit10231023:20180911180143p:plain

MySQL(MariaDB)には、レンジパーティションってものがありまして、うーなんでしょ？ある規則にしがったデータをおのおののデータファイルに振り分けてくれるストレージエンジン的なものです。

データ領域が分割されるため、大量のデータを処理することによる性能上のボトルネックの発生を抑えられる
MyISAMなど、テーブルサイズに上限がある場合でもそれ以上のデータを格納することが可能になる

といった点です、少ないデータですと大きな利点はないかと思いますが、数百万規模のデータですとパーティションの条件にマッチすれば、劇的にクエリパフォーマンスが向上します！！

サンプルテーブル

こんなテーブルを作ってみました。非パーティショニングテーブルですね。つまり普通のテーブルです。これをベースにパーティショニング化を検証したいとおもいます

| sample_tables | CREATE TABLE `sample_tables` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`detail1` varchar(26) DEFAULT NULL,
`detail2` enum('cate','com','blog') NOT NULL DEFAULT 'cate',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=271124 DEFAULT CHARSET=utf8 |

mysql> select count(*) from sample_tables;
+----------+
| count(*) |
+----------+
|   271123 |
+----------+
1 row in set (0.00 sec)

既存のテーブルを、パーティション表に変換できるか検証

正常終了したことを確認しております

mysql> alter table sample_tables
-> PARTITION BY RANGE (id) (
->  PARTITION p000000 VALUES LESS THAN (100000),
->  PARTITION p100000 VALUES LESS THAN (200000),
->  PARTITION p200000 VALUES LESS THAN (300000),
->  PARTITION pover VALUES LESS THAN MAXVALUE
-> );
Query OK, 271123 rows affected (4.97 sec)
Records: 271123  Duplicates: 0  Warnings: 0

mysql> select count(*) from sample_tables;
+----------+
| count(*) |
+----------+
|   271123 |
+----------+
1 row in set (0.11 sec)

データの分布状況を確認

なんとなく分かれているように見える

select TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,TABLE_ROWS from INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='sample_tables';

+--------------+-----------------+----------------+----------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME      | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
+--------------+-----------------+----------------+----------------------------+------------+
| say          | sample_tables   | p000000        |                          1 |      99972 |
| say          | sample_tables   | p100000        |                          2 |     102819 |
| say          | sample_tables   | p200000        |                          3 |      74263 |
| say          | sample_tables   | pover          |                          4 |          0 |
+--------------+-----------------+----------------+----------------------------+------------+

mysql> select count(*) from sample_tables;
+----------+
| count(*) |
+----------+
|   271123 |
+----------+
1 row in set (0.00 sec)

上記にて作成したテーブルに、新規パーティションを追加する

ALTER TABLE sample_tables ADD PARTITION (
PARTITION p300000 VALUES LESS THAN (400000)
);

エラーになりました！

RANGE パーティショニングされているテーブルにおいて、ADD PARTITION は既存パーティションの「後」にしかパーティションの追加ができない（「前」や「間」はNGです）。そのためMAXVALUEが既に存在する場合はMAXVALUE より後の値は存在し得ないため、新規パーティションが作成できないということになります。

ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

代替案

上記の場合は、新規でパーティションを作るより、データを再配置（REORGANIZE）する方法で実現することができます。

mysql> ALTER TABLE sample_tables REORGANIZE PARTITION pover INTO (
->     PARTITION p300000 VALUES LESS THAN (400000),
->     PARTITION pover VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(*) from sample_tables;
+----------+
| count(*) |
+----------+
|   271123 |
+----------+
1 row in set (0.11 sec)

以上を踏まえて・・

MAXVALUEはあえて指定しない

ある程度、バッファを持たせた形でレンジを振っておく等の設計を行っておいたほうが良いかと思います。

既存のパーティションをさらに細分化する

作成したパーティションをさらに細分化したい場合、たとえば、sample_tablesの例ですと100000レンジで、パーティションを分けておりますが、さらにこれを、10000単位で分割しなおしたいという場合は、REORGANIZE PARTITIONを利用して、すべて再配置することが可能になります。ただし上記5同様再配置に時間がかかります。

細分化

ALTER TABLE sample_tables REORGANIZE PARTITION p000000, p100000, p200000, p300000,pover INTO (
PARTITION p00000 VALUES LESS THAN (10000),
PARTITION p10000 VALUES LESS THAN (20000),
PARTITION p20000 VALUES LESS THAN (30000),
PARTITION p30000 VALUES LESS THAN (40000),
PARTITION p40000 VALUES LESS THAN (50000),
PARTITION p50000 VALUES LESS THAN (60000),
PARTITION p60000 VALUES LESS THAN (70000),
PARTITION p70000 VALUES LESS THAN (80000),
PARTITION p80000 VALUES LESS THAN (90000),
PARTITION p90000 VALUES LESS THAN (100000),
PARTITION pover VALUES LESS THAN MAXVALUE
);

Query OK, 271123 rows affected (5.00 sec)
Records: 271123  Duplicates: 0  Warnings: 0

mysql> select count(*) from sample_tables;

+----------+
| count(*) |
+----------+
|   271123 |
+----------+
1 row in set (0.10 sec)

細分化結果

+--------------+-----------------+----------------+----------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME      | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
+--------------+-----------------+----------------+----------------------------+------------+
| say          | sample_tables   | p00000         |                          1 |       9975 |
| say          | sample_tables   | p10000         |                          2 |       9851 |
| say          | sample_tables   | p20000         |                          3 |      10421 |
| say          | sample_tables   | p30000         |                          4 |      10236 |
| say          | sample_tables   | p40000         |                          5 |      10222 |
| say          | sample_tables   | p50000         |                          6 |       9761 |
| say          | sample_tables   | p60000         |                          7 |       9992 |
| say          | sample_tables   | p70000         |                          8 |      10222 |
| say          | sample_tables   | p80000         |                          9 |      10166 |
| say          | sample_tables   | p90000         |                         10 |       9984 |
| say          | sample_tables   | pover          |                         11 |     173134 |
+--------------+-----------------+----------------+----------------------------+------------+
11 rows in set (0.00 sec)

パーティションの一部を消したい場合

！！！！この作業を行うと、パーティション内に格納されていたデータはすべてなくなりますが、逆に部分的にtruncateできるという意味ではかなり使えます！

ALTER TABLE sample_tables DROP PARTITION p00000;
ALTER TABLE sample_tables DROP PARTITION p10000;
ALTER TABLE sample_tables DROP PARTITION p20000;
ALTER TABLE sample_tables DROP PARTITION p30000;

DROP PARTITION 結果

データ件数

減っているのがわかるかと思います

mysql> select count(*) from sample_tables;
+----------+
| count(*) |
+----------+
|   231124 |
+----------+
1 row in set (0.08 sec)

-- p00000、p10000、p20000、p30000 がなくなっております。

+--------------+-----------------+----------------+----------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME      | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
+--------------+-----------------+----------------+----------------------------+------------+
| say          | sample_tables   | p40000         |                          1 |       9819 |
| say          | sample_tables   | p50000         |                          2 |       9779 |
| say          | sample_tables   | p60000         |                          3 |      10513 |
| say          | sample_tables   | p70000         |                          4 |      10082 |
| say          | sample_tables   | p80000         |                          5 |      10215 |
| say          | sample_tables   | p90000         |                          6 |       9968 |
| say          | sample_tables   | pover          |                          7 |     170286 |
+--------------+-----------------+----------------+----------------------------+------------+
7 rows in set (0.00 sec)

定義

sample_tables | CREATE TABLE `sample_tables` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`detail1` varchar(26) DEFAULT NULL,
`detail2` enum('cate','com','blog') NOT NULL DEFAULT 'cate',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=271124 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (id)
(PARTITION p40000 VALUES LESS THAN (50000) ENGINE = InnoDB,
PARTITION p50000 VALUES LESS THAN (60000) ENGINE = InnoDB,
PARTITION p60000 VALUES LESS THAN (70000) ENGINE = InnoDB,
PARTITION p70000 VALUES LESS THAN (80000) ENGINE = InnoDB,
PARTITION p80000 VALUES LESS THAN (90000) ENGINE = InnoDB,
PARTITION p90000 VALUES LESS THAN (100000) ENGINE = InnoDB,
PARTITION pover VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |

非パーティションのテーブルに戻す

こちらは、データは消えません！

mysql> ALTER TABLE sample_tables REMOVE PARTITIONING;

パーティションがなくなっていることを確認

+--------------+-----------------+----------------+----------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME      | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
+--------------+-----------------+----------------+----------------------------+------------+
| say          | sample_tables   | NULL           |                       NULL |     248834 |
+--------------+-----------------+----------------+----------------------------+------------+

データは消えていないことを確認

mysql> select count(*) from sample_tables;
+----------+
| count(*) |
+----------+
|   271123 |
+----------+

f:id:hit10231023:20180309104332j:plain

Database JUNKY

MySQL,MariaDBを中心としたブログです

MySQL パーティショニングで高速クエリーを実現！！