こんにちは！ルーターの伊崎です。

今回は、データの効率的な消し方と、SQLの機能であるストアドプロシージャをご紹介します。なお、今回データベースをMySQLとしていますが、細かい書き方は違えどデータベースの種類によらずに成り立つ内容です。それではどうぞ。

データの削除

私たち株式会社ルーターは、日々大量のデータを処理しています。

そこで扱うデータは1日だけ見て捨てるものもあれば、1ヶ月、1年と保管し続けるものもあります。大事なのは、いらなくなったデータはすぐに捨てることです。日々増えるデータはサーバーの容量を圧迫し、いずれ新たなデータの入る余地をなくしてしまいます。そうなる前に対処する必要があります。

データを捨てる、とひとことで言っても、それがいつも簡単であるとは限りません。次の表は、弊社で実際に扱っているデータを集計したもの（の一部）です。

テーブル名	レコード数	容量（MB）
price_logs	21075062	5069
prices	185629	213

price_logsテーブルが大きいですね。2000万件超のレコードによって、5GBを占有しています。

このテーブルは、prices（価格）テーブルに集めたデータのバックアップ的な存在で、長期に保管することを前提としています。したがって、どうしても容量は大きくなります。放っておけば、すぐに10GB、20GBと大きくなってしまうでしょう。気づけば20GBになっていた。そんな場合を考えてみます。

このテーブルはバックアップなので、最近1週間のものについては残しておいて、それより古いものを削除したいとしましょう。その場合のSQL文は以下のようになるはずです。

DELETE FROM price_logs WHERE created_at > NOW() - INTERVAL 1 WEEK;

ちなみに、NOW() - INTERVAL 1 WEEKは現在から1週間前を表します。

このSQL文、実は危険です。なぜでしょうか。

大量のデータを消すということ

実際に実行してみればわかりますが、このSQL、とても時間がかかります。

試しに、今回削除したいレコード数の1/10、200万件のレコードを消してみました。かかった時間はおよそ2分です。ということは、直近1週間以外のすべてのレコード2000万件を消すには、単純に考えてこの10倍、20分がかかります。今回は5Gでしたが、これが10G、20Gであったとするなら、さらに数倍の時間がかかります。そして、時間がかかると同時に、サーバーへ負荷がかかります。削除だけを行っている場合は問題ありませんが、複数人で、それほどスペックの高くないサーバーを同時に使用している場合、他の人のクエリを止めてしまったり、場合によってはサーバーを落としてしまうおそれもあります。

そういった事態を避けるために、ちょっとしたテクニックがあります。とその前に、truncateコマンドについて触れておきましょう。これは、テーブル内の全データを一瞬のうちに消し去ってしまう強力なコマンドです。そして今回のように、一部のデータを残しておきたい場合には使えません。残念！さて、サーバーに負荷をかけることなく、削除を行うためにやるべきことは2つだけです。

• 削除したいテーブルと同じ構造を持つテーブルを作る
• 複製したテーブルに、残したいレコードのみインサートする

あとは元のテーブルを残しておくもよし、さっさとdropしてもよしです。

これをSQLに落とし込むと以下のようになります。

DROP TABLE IF EXISTS price_logs_new;
DROP TABLE IF EXISTS price_logs_old;
CREATE TABLE price_logs_new like price_logs;
INSERT INTO price_logs_new ( select * from price_logs WHERE created_at > (NOW() - INTERVAL 1 WEEK));
RENAME TABLE price_logs TO price_logs_old, price_logs_new TO price_logs;

やっていることは単純ですが、行数も多いし、コピペするのも面倒です。何か間違って大事なデータを消してしまうのではないかという心配もあります。そこで登場するのが、もう一つのテーマ、ストアドプロシージャです。

ストアドプロシージャ

複数のSQL文をまとめて実行したいときに使えるのが、ストアドプロシージャです（親戚にストアドファンクションがありますが、今回は触れません）。特別な準備は必要ありません。MySQLの中でコマンドを打つだけです。

先ほどのクエリ群をストアドプロシージャでまとめてみます。delete_price_logsという名前のプロシージャを作成します。

DELIMITER //
CREATE PROCEDURE delete_price_logs()
BEGIN
DROP TABLE IF EXISTS price_logs_new;
DROP TABLE IF EXISTS price_logs_old;
CREATE TABLE price_logs_new like price_logs;
INSERT INTO price_logs_new ( select * from price_logs WHERE created_at > (NOW() - INTERVAL 1 WEEK));
RENAME TABLE price_logs TO price_logs_old, price_logs_new TO price_logs;
END//
DELIMITER ;

順を追って説明します。

まず、冒頭のDELIMITER //ですが、ふだんSQLを利用するときに末尾につけるセミコロン;を//に変更するというものです。なぜそんなことをするかというと、CREATE PROCEDURE、BEGINときて実際に見慣れたSQL文を書くわけですが、DELIMITER //を行っていないとDROP TABLE IF EXISTS price_logs_new;の時点で全体のSQL文が終わったと判断されてしまいます。プロシージャの中の個々のSQL文をまとめるために、末尾の区切り文字を予め変えておくのです。CREATE PROCEDURE、BEGINのあとにSQL文を並べ、最後にEND//で締めます。

そして、デリミタを忘れずに変えておきますDELIMITER ;。

早速実行してみましょう。

mysql> DELIMITER //
mysql> CREATE PROCEDURE delete_price_logs()
-> BEGIN
-> DROP TABLE IF EXISTS price_logs_new;
-> DROP TABLE IF EXISTS price_logs_old;
-> CREATE TABLE price_logs_new like price_logs;
-> INSERT INTO price_logs_new ( select * from price_logs WHERE created_at > (NOW() - INTERVAL 1 WEEK));
-> RENAME TABLE price_logs TO price_logs_old, price_logs_new TO price_logs;
-> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql>

これでdelete_price_logsという名前のストアドプロシージャができました。作成したストアドプロシージャの内容はSHOW CREATE PROCEDURE delete_price_logs;コマンドで確認できます。

あとは、実行を残すのみです。

mysql> CALL delete_price_logs();
Query OK, 0 rows affected (1 min 51.95 sec)
mysql>

2分弱で終わりました！結果を確認してみましょう。

テーブル名	レコード数	容量（MB）
price_logs_old	21075062	5069
price_logs	1176611	283
prices	185629	213

price_logs_oldが以前のprice_logsです。price_logsが随分小さくなったことがわかります。

これがたったの2分でできてしまい、実行したコードはたった1行です。2000万件のレコードは無事消去できました。めでたしめでたし。

作成したストアドプロシージャは、データベース・サーバーの中に保存され、いつでも呼び出すことができます。以下のコマンドで、保存されているプロシージャ一覧が確認できます。

SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';

ROUTINE_NAME	ROUTINE_TYPE
delete_price_logs	PROCEDURE

容量が増えがちなログテーブルごとに削除プロシージャを用意しておけば、データ容量管理がぐっと楽になるでしょう。

まとめ

大量のデータを扱うことがある人は、世の中にまだそう多くはないと思います。しかしこれからはビッグデータの時代です。パンパンに膨れ上がったデータベースをなんとかしたいという人も増えてくるのではないでしょうか。そんな問題に直面したとき、この記事のことを思い出していただけると幸いです。

それでは、よいデータライフを！