何かしらのアクションの度に発生するログデータをMysqlで運用する機会があります。
しかし、保存期間を決めておかないといずれ、ディスク容量がいっぱいになりDBの書き込みができなくなってしまいます。
保存期間を決めたとしても、不必要なデータのみをDelete文で削除していく方法は、時間がかかり非常に面倒です。
Mysqlの機能であるパーティションニングを使用すると、設定したルールに従って、
データを参照したり、区分化されたデータを一瞬で(物理的に)削除することが可能です。
パーティションニングはログ系テーブルには必須の機能と言えます。
この記事では、最初に時間がない人のためにパーティションの作り方、中盤以降はパーティションニングについての解説していきます。解説するパーティショニングのタイプは、ログ系テーブルでよく使われるRANGEを使います。
時間がない人のためにパーティションの作り方だけ知りたい方はここだけでも読め！
パーティションの作り方だけ解説します。
まず、CREATE TABLEでテーブルを作成します。
PARTITIONを使ってパーティションを区切ります。
以下の例は、t_diary_logsテーブルに年単位(createdカラム)でパーティションを区切る例です。ここでのポイントは、プライマリをidとcreatedを設定しています。その理由は、
パーティションで区切る基準になるカラムはプライマリに含める必要があるからです。
以下の操作でパーティションを区切ることができます。
CREATE TABLE `t_diary_logs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(45) DEFAULT NULL COMMENT 'タイトル',
`data` varchar(45) DEFAULT NULL COMMENT 'データ',
`created` datetime NOT NULL,
`modified` datetime DEFAULT NULL,
`del` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`,`created`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE ( YEAR(created))
(PARTITION p0 VALUES LESS THAN (2014) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (2015) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2016) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
パーティショニングとは
Mysqlのマニュアルをみるとパーティショニングについて以下のように書いてあります。
SQL 標準では、データ保存の物理的な仕様に関するガイダンスはあまり提供されていません。SQL 言語自体が、それが動作するスキーマ、テーブル、行、またはカラムの基盤となるデータ構造やメディアと独立して動作するように意図されています。それにもかかわらず、ほとんどの高度なデータベース管理システムでは、ファイルシステム、ハードウェア、またはその両方について、特定のデータを格納するために使用される物理的な場所を判別する方法が開発されてきました。MySQL では、InnoDB ストレージエンジンによってテーブルスペースの認識がサポートされてきており、MySQL サーバーは、パーティショニングが導入される前から、異なるデータベースの格納に異なる物理的なディレクトリを使用するように構成できました (これを行う方法については、セクション8.11.3.1「シンボリックリンクの使用」を参照してください)。
パーティショニングはこの認識をさらに一歩進めて、必要に応じて多くの部分を設定できるルールに従って、個々のテーブルの部分をファイルシステムに配分できるようにしています。それにより、テーブルの異なる部分が別個のテーブルとして別個の場所に格納されます。データを分割するためにユーザーが選択するルールはパーティショニング関数と呼ばれ、MySQL では法、範囲セットまたは値リストに対する単純な照合、内部ハッシュ関数、または線形ハッシュ関数が使用されます。関数は、ユーザーが指定したパーティショニングタイプに従って選択され、ユーザーが指定した式の値をパラメータとして取ります。この式には、使用されるパーティショニングのタイプに応じて、カラム値、1 つ以上のカラム値を操作する関数、または 1 つ以上のカラム値のセットを指定できます。
19.1 MySQL のパーティショニングの概要
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-overview.html
パーティションニングは、１つのテーブルに対して更に、保存領域を設定できます。
パーティションニングを適用すると、設定したルールに従って、データを参照したり、区分化されたデータを一瞬で(物理的に)削除することが可能です。
パーティションが使えるかどうか確認する
Mysqlのバージョンによっては、パーティションニングが使えない場合があります。
まずは、本当にパーティションニングが使えるか以下のSQLを打って、プラグインの中に「partition」があり、「ACTIVE」になっていることを確認します。
SHOW PLUGINS;
partition | 1.0 | ACTIVE
InnoDBテーブルスペースをテーブル単位で作成できるようになっているかの確認。
「my.cnf」を開き、「innodb_file_per_table」が設定されているか確認します。
パーティショニングタイプについて
パーティショニングのタイプには、RANGE、LIST、COLUMNS、HASH、KEYがありますが、この記事ではログ系テーブルでよく使われるRANGEタイプで解説しています。
パーティショニングタイプについて詳しく知りたい方は以下が参考になります。
19.2 パーティショニングタイプ
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-types.html
パーティションテーブルの作成、確認、追加、削除
パーティションを作る
では、実際にパーティションを考慮したテーブルを作ります。
CREATE TABLEでテーブル文をかいて、その後にPARTITIONでパーティションを区分けします。パーティションは、作成後に追加は可能ですが、追加の際、データの再配置を実行するため待ち時間が発生します。運用しているシステムだとメンテンナンス中に行わなければならず、意外に面倒です。よって、初期の段階で、あとで変更が無いようにどのようなパーティションにするのか検討しておく必要があります。
以下の例は、t_diary_logsテーブルに年単位(createdカラム)でパーティションを区切る例です。ここでのポイントは、プライマリをidとcreatedを設定しています。その理由は、
パーティションで区切る基準になるカラムはプライマリに含める必要があるからです。
以下の操作でパーティションを区切ることができます。
CREATE TABLE `t_diary_logs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(45) DEFAULT NULL COMMENT 'タイトル',
`data` varchar(45) DEFAULT NULL COMMENT 'データ',
`created` datetime NOT NULL,
`modified` datetime DEFAULT NULL,
`del` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`,`created`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE ( YEAR(created))
(PARTITION p0 VALUES LESS THAN (2014) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (2015) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2016) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
この操作によって、
p0パーティションには、2014以前に発生したデータ
p1パーティションには、2015以前(2014年のデータ)に発生したデータ
p2パーティションには、2016以前(2015年のデータ)に発生したデータ
p3パーティションには、2017以前(2016年のデータ)に発生したデータ
pmaxパーティションには、2017年以降のデータに発生したデータ
とそれぞれ格納されるようになります。
パーティションの確認
Select文で以下のようにパーティションを選択すれば、該当するパーティションのデータのみ参照することができます。
SELECT * FROM t_diary_logs PARTITION (p1)
パーティションの追加
最初に作ったパーティションに対してさらに追加したいことがあります。
このような場合は、REORGANIZEを使ってパーティションを追加します。
以下の例は、新たにパーティションp4を追加して2018年以前(2017年のデータ)を区分けできるようにしたものです。
尚、再配置はt_diary_logsテーブルのデータを置き換えるので、時間がかかります。
すでにシステムを運用していたら、メンテナンスをかけるなどして一度止める必要があります。
ALTER TABLE t_diary_logs REORGANIZE PARTITION pmax into (
PARTITION p4 VALUES LESS THAN (2018),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
19.3 パーティション管理
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-management.html
パーティションを消してみる
パーティションを消すときは、以下のようなSQLを実行します。
以下の例は、パーティションp1を物理的に削除する例です。
ALTER TABLE t_diary_logs DROP PARTITION p1
その結果、p1パーティションの内容だけが物理的に削除されます。
実行速度は0.1秒もかからないほどの一瞬です。
上記は、実際の物理データです。
パーティション削除すると、p1テーブルのデータが消えます。
また、パーティションを消さず、パーティションのデータを空にする方法もあります。
以下の例は、パーティションp2のデータを空にします。
ALTER TABLE t_diary_logs TRUNCATE PARTITION p2
実際にp2のデータ量が209Mから115kbになっていることを確認できます。
まとめ
いかがでしょうか？
ログ系テーブルのように常にデータが溜まるテーブルに対しては、パーティションを設定することによりデータの管理が楽になります。
もしデータの管理で悩んでいたらパーティションを使ってみてはいかがでしょうか？
