MySQLパーティションを使う
MySQLで大量のデータを扱う上で重宝するパーティションの具体的な導入手順をまとめました。
Contents
パーティションとは？
テーブルを一定のルールにしたがって分割することで、パフォーマンスを上げる手法です。
今回はログテーブルなどでよく使われるRANGEによるパーティションをやってみます。
環境
- macOS 10.13.6
- MySQL 5.6
PRIMARY KEYを設定する
MySQLでパーティションを使用するには、RANGEとなるカラムがPRIMARY KEYである必要があります。
PRIMARY KEYを設定すると、そのカラムでは重複とNULLが許容されなくなります。
したがって、既存のテーブルでPRIMARY KEYを設定する場合、指定カラムで重複・NULLのレコードが存在しないことが条件になります。
今回、MySQLとMongoDBの比較記事でも使用したデータを使用します。
ただ、重複データが混じっていて、このままではPRIMARY KEYが設定できなかったので、少し加工し、新たに作成したテーブルにユニークなデータを入れてテストデータにします。
テーブル作成
CREATE TABLE IF NOT EXISTS login_p ( uid int NOT NULL, device VARCHAR(20) NOT NULL, time int NOT NULL, PRIMARY KEY(uid,time))
インデックスがはられていることを確認
show index from login_p\G
*************************** 1. row ***************************
Table: login_p
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: uid
Collation: A
Cardinality: 99445
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: login_p
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: time
Collation: A
Cardinality: 99445
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
サンプルデータを作成する(SELECT INSERT)
すでにデータが入っているテーブル(login)から、指定カラムがユニークなデータだけを取得し、結果をパーティション用テーブルに挿入します。
INSERT INTO login_p SELECT DISTINCT uid, device, time FROM login;
※PRIMARY KEYはuidとtimeのみなので、厳密にはdeviceはユニークでなくてもいいのですが、ほとんど内容が変わらないのと、「複数カラムをDISTINCTかつ、特定のカラムはDISTINCTの必要なしでSELECT」の方法がわからなかったので、全カラムDISTINCTで取得しています。
データが挿入できていることを確認。ついでにtimeの最小値と最大値を把握しておきます。
SELECT COUNT(*) FROM login_p; SELECT MIN(from_unixtime(time)), MAX(from_unixtime(time)) FROM login_p; +--------------------------+--------------------------+ | MIN(from_unixtime(time)) | MAX(from_unixtime(time)) | +--------------------------+--------------------------+ | 2011-11-29 19:53:47 | 2012-12-27 04:59:57 | +--------------------------+--------------------------+
[補足]UNIXTIMEからDATETIMEへ(from_unixtime)
timeカラムには時刻データがUNIXTIME型で格納されているので、日付でパーティションを区切るにあたり、両者を適切に変換する必要があります。
以下の2つを使いました。
from_unixtime(): UNIXTIMEをDATETIMEに変換unix_timestamp(): DATETIMEをUNIXTIMEに変換
パーティションを設定する
いよいよパーティション設定。
ALTER TABLE login_p
PARTITION BY RANGE COLUMNS(time) (
PARTITION p201111 VALUES LESS THAN (unix_timestamp('2011-12-01 00:00:00')),
PARTITION p201112_01 VALUES LESS THAN (unix_timestamp('2011-12-08 00:00:00')),
PARTITION p201112_02 VALUES LESS THAN (unix_timestamp('2011-12-15 00:00:00')),
PARTITION p201112_03 VALUES LESS THAN (unix_timestamp('2011-12-22 00:00:00')),
PARTITION p201112_04 VALUES LESS THAN (unix_timestamp('2011-12-29 00:00:00')),
PARTITION p201112_05 VALUES LESS THAN (unix_timestamp('2012-01-01 00:00:00')),
PARTITION p201201_01 VALUES LESS THAN (unix_timestamp('2012-01-08 00:00:00')),
PARTITION p201201_02 VALUES LESS THAN (unix_timestamp('2012-01-15 00:00:00')),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
作成するパーティションのRANGEから漏れているレコードがあるとエラーになります。
上の例でいうとMAX(time)が 2012-12-27 04:59:57 なので、最後の一行( PARTITION pmax VALUES LESS THAN MAXVALUE が必要です。
パーティションを確認する
うまくできたか確認してみます。
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='login_p'; +--------------+----------------+----------------+----------------------------+------------+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS | +--------------+----------------+----------------+----------------------------+------------+ | sample_db | login_p | p201111 | 1 | 543 | | sample_db | login_p | p201112_01 | 2 | 0 | | sample_db | login_p | p201112_02 | 3 | 0 | | sample_db | login_p | p201112_03 | 4 | 0 | | sample_db | login_p | p201112_04 | 5 | 0 | | sample_db | login_p | p201112_05 | 6 | 0 | | sample_db | login_p | p201201_01 | 7 | 0 | | sample_db | login_p | p201201_02 | 8 | 0 | | sample_db | login_p | pmax | 9 | 99910 | +--------------+----------------+----------------+----------------------------+------------+
一応分割できてます。ただ、偏りがすごいのでpmaxのパーティションをもう少し分割します。
パーティションを再設定する(REORGANIZE)
パーティションを再設定するにはREORGANIZEを使います。
ALTER TABLE login_p REORGANIZE PARTITION pmax INTO (
PARTITION p201201_03 VALUES LESS THAN (unix_timestamp('2012-01-22 00:00:00')),
PARTITION p201201_04 VALUES LESS THAN (unix_timestamp('2012-01-29 00:00:00')),
PARTITION p201201_05 VALUES LESS THAN (unix_timestamp('2012-02-01 00:00:00')),
PARTITION p201202 VALUES LESS THAN (unix_timestamp('2012-03-01 00:00:00')),
PARTITION p201203 VALUES LESS THAN (unix_timestamp('2012-04-01 00:00:00')),
PARTITION p201204 VALUES LESS THAN (unix_timestamp('2012-05-01 00:00:00')),
PARTITION p201205 VALUES LESS THAN (unix_timestamp('2012-06-01 00:00:00')),
PARTITION p201206 VALUES LESS THAN (unix_timestamp('2012-07-01 00:00:00')),
PARTITION p201207 VALUES LESS THAN (unix_timestamp('2012-08-01 00:00:00')),
PARTITION p201208 VALUES LESS THAN (unix_timestamp('2012-09-01 00:00:00')),
PARTITION p201209 VALUES LESS THAN (unix_timestamp('2012-10-01 00:00:00')),
PARTITION p201210 VALUES LESS THAN (unix_timestamp('2012-11-01 00:00:00')),
PARTITION p201211 VALUES LESS THAN (unix_timestamp('2012-12-01 00:00:00')),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
再度確認
+--------------+----------------+----------------+----------------------------+------------+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS | +--------------+----------------+----------------+----------------------------+------------+ | sample_db | login_p | p201111 | 1 | 543 | | sample_db | login_p | p201112_01 | 2 | 0 | | sample_db | login_p | p201112_02 | 3 | 0 | | sample_db | login_p | p201112_03 | 4 | 0 | | sample_db | login_p | p201112_04 | 5 | 0 | | sample_db | login_p | p201112_05 | 6 | 0 | | sample_db | login_p | p201201_01 | 7 | 0 | | sample_db | login_p | p201201_02 | 8 | 0 | | sample_db | login_p | p201201_03 | 9 | 0 | | sample_db | login_p | p201201_04 | 10 | 0 | | sample_db | login_p | p201201_05 | 11 | 0 | | sample_db | login_p | p201202 | 12 | 0 | | sample_db | login_p | p201203 | 13 | 0 | | sample_db | login_p | p201204 | 14 | 0 | | sample_db | login_p | p201205 | 15 | 0 | | sample_db | login_p | p201206 | 16 | 2024 | | sample_db | login_p | p201207 | 17 | 2030 | | sample_db | login_p | p201208 | 18 | 4701 | | sample_db | login_p | p201209 | 19 | 29 | | sample_db | login_p | p201210 | 20 | 19027 | | sample_db | login_p | p201211 | 21 | 29121 | | sample_db | login_p | pmax | 22 | 29623 | +--------------+----------------+----------------+----------------------------+------------+
うまくできました。
今回は週で分割したり月で分割したりしてますが、実運用ではルールを決めた方が良いでしょう。
パーティションを使ってSELECTする
パーティションを指定してSELECTします。
特定期間中のユニークなuidのリストと、その件数を集計するというクエリです。
パーティション指定してSELECT
SELECT DISTINCT uid, COUNT(*) FROM login_p PARTITION(p201205) WHERE time BETWEEN unix_timestamp('2012-05-01 00:00:00') AND unix_timestamp('2012-05-31 23:59:59') GROUP BY uid;
複数のパーティションにまたがってSELECT
SELECT DISTINCT uid, COUNT(*) FROM login_p PARTITION(p201205,p201206,p201207,p201208,p201209,p201210,p201211) WHERE time BETWEEN unix_timestamp('2012-05-01 00:00:00') AND unix_timestamp('2012-11-30 23:59:59') GROUP BY uid;
全件SELECTもできます
SELECT DISTINCT uid,COUNT(*) FROM login_p GROUP BY uid;
まとめ
以上、パーティションを使ってみるところまで。
今回の構成ではパフォーマンスのメリットを感じられていないので、規模を大きくしてベンチマーク取ってみたいと思います。
また、パーティションではパーティションごとに削除ができたり、パーティションの設定を戻したりもできるようです。
削除は戻せないのでお気をつけください。