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

f:id:hit10231023:20180911180143p:plain

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

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

といった点です。

MySQLでもMariaDBでも、古いバージョンからある機能ではありますが、数百万規模のデータですとSQLの条件によって、読み込む分母のレコード数が少なくなるため、パフォーマンスは向上するわけですね。

サンプルテーブル

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

| 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)

SQLのイメージ

今回の例では、idのレンジパーティションをしましたので、当然のことながらIdのレンジでSQLが効果があります。

p000000のパーティションしか見に行かない例

もっとも効果のあるSQLです。

SELECT
  id.
  detail1
FROM
   sample_tables
WHERE
  id < 100000 ;

p000000,p100000のパーティションを見に行く例

このケースでも、２つのパーティションをまたがって検索している状態ですが、それでも全てのパーティションをまたがっているわけではないので、高速化が期待できます

SELECT
  id.
  detail1
FROM
   sample_tables
WHERE
  id < 200000 ;

全てのパーティションをまたぐ例

まあ、これは、パーティショニングのメリットが全くない例です（汗）むしろ、非パーティショニングテーブルのほうが速いと思う。。

idの指定条件が、全てのパーティションを参照する条件になっている

SELECT
  id.
  detail1
FROM
   sample_tables
WHERE
  id > 5 ;

そもそも、パーティショニングのキーに関係ない

SELECT
  id.
  detail1
FROM
   sample_tables
WHERE
  detail2 = 'cate';

。。とこんな感じです。パーティショニングで高速クエリーを実現！！と大々的に書いてしまいましたが、大事な部分は、そのパーティショニングのキーを主体とした検索・集計を多くある場合というのが大前提になります

id列という、すこしあまり意味のない列を例にして書いてしまったため（汗）どんなメリットあるの？状態になってしまいましたが

たとえば、これが日付を主体とした、例えば、検索、集計の条件で、日付を元にした集計などは、高速化の期待がもてます

管理/運用

もちろん、日々の運用なので、そのまま放置して良いわけでもなく管理が必要になってきます。

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

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 |
+----------+

おまけ

パーティションを限定した検索

どこで活用できるかはわかりませんが、MySQL5.6以降では、パーティションを限定した検索が可能です。

パーティションp000000, p100000に絞った検索

SELECT
  id.
  detail1
FROM
   sample_tables
PARTITION (p000000, p100000)
WHERE
  detail2 = 'cate';

id < 100000までを別テーブルにコピー

CREATE TABLE sample_tables_99999
SELECT * FROM
   sample_tables
PARTITION (p000000) ;

f:id:hit10231023:20180309104332j:plain

Database JUNKY

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