MySQL(RDBMS)でビッグデータを扱う
ビッグデータというのは、概ね通常単一のPCでは扱いきれないデータ量のレコード数のデータのことを言います。
一般的にはDBなどに蓄積されているデータよりも蓄積はされても参照されることのないログファイルを指してビッグデータと呼ばれるようですが、通常参照されることのないデータをわざわざ検索することにそれほど価値や意味を見出すことには疑問が残ります。
また、ログファイルといえどもそうそう１億行にも達するようなデータというものはwebサービスを運営していたとしても早々お目にかかれるものでもありません。
なので、「ワイもビッグデータを扱ってみたい！」とふと思った少年少女そのほかおじさんなどに向けた「手軽に作れるビッグデータ」の方法を記述していこうかと思います。
本記事を試行するにあたって必要なシステム
- 本記事ではMySQL5.5を利用していますが、5.1以上であればおそらく問題ないとは思います。
- Raspberry PiなどのLinuxが動作する環境であれば大体何とかなります
- MySQLを使用してビッグデータを作成します。apt-get install mysql-serverなどでインストールしてください
- １億レコード以上のデータを扱うため、比較的ディスクに余裕を持たせてください。でも大体10GBくらいあればお釣りが来ます
- MySQL+PHPがよく扱われるテーマとはなりますが、本記事では特にPHPに関しては扱いません。逆に、長大なSQLを頻繁に実行することになるので、MySQLのクライアントツールなりを適宜インストールしておくことをお勧めします。NetBeansなんかがお勧め。
1億レコードを入れる器を用意する
ユーザ作成については省略します。データベースはBIG_DATAとでもします。
なお、これは私ルールとなりますが
- 予約語は大文字
- テーブルのカラムも大文字
- 区切り文字はアンダーバー
- アクサン等のテーブル文字列エスケープは使わない
となっているのでご注意ください。
データベース作成スクリプト
CREATE DATABASE BIG_DATA
DEFAULT CHARACTER SET=UTF8;
1億レコードを用意するのが目的ですが、何を1億レコード蓄積するべきでしょうか？
ログデータ的なものもよいかもしれません。しかし、ここはビッグデータを扱ってみようとしてみる手前、もしかしたらすでにあるかもしれないログデータではなく、「マスタデータ」的なデータを用意することにしましょう。
約１億レコード存在するマスタデータとは？ そうです。日本国民です。
せっかくなので、PC上に仮想日本国民全員の擬似マスタデータを作成してみることにしましょう。
今回は、便宜的に「性別」と「誕生日」のカラムだけ存在するテーブルを国民テーブルとして作成します。（これには深い理由があるのですが、今回はその理由については触れません）
DROP TABLE IF EXISTS M_PEOPLE;
CREATE TABLE IF NOT EXISTS M_PEOPLE(
ID SERIAL, /*一意のID、通番*/
SEX ENUM('F','M') NULL /*性別。F=女性 M=男性*/,
BIRTH_DATE DATE NULL /*誕生日*/
)ENGINE=MyISAM,AUTO_INCREMENT=1;
本クエリではM_PEOPLEという名前のテーブルを削除し、作成するという処理になっています。
若干冗長になっていますが、これは何回もやりなおしなどをするときにテーブルの構造を確実に変更するためと、その都度データを空にする為です。
また、ENGINE=MyISAMではストレージエンジンにデフォルトではないものを設定しています。
これは、MySQLのデフォルトストレージエンジンであるInnoDBは、高機能であることと引き換えにレコード数が多くなることによるINSERT性能の低下があるためです。
今回のビッグデータ生成では、最初に述べたとおり1億レコード以上のINSERTが発生します。
マシンスペックにもよりますが、この規模のデータはInnoDBでは手軽に挿入不可能です。
反対に、MyISAMはシンプルなストレージエンジンであり、INSERT性能はレコード数に関係なく一定です。ほぼストレージの書き込み速度と同じくらいのINSERTができます。
COUNT()等も高速であるため、MySQLでビッグデータを扱う際は基本MyISAMを利用します。
どうやって１億レコードのデータを入れるか
シェルスクリプトでも書いて1億回INSERTを繰り返せば1億レコードを生成できるわけではありません。
それではデータの生成に時間がかかりすぎてしまいます。おそらく単純にループしてINSERT()を発行するだけでは１億レコードの作成に一日二日かかってしまうこともあり得るでしょう。
SQL文だけで、しかも1文だけで１億レコードを挿入することができます。
数字テーブル（連番テーブル）の作成
１億レコード挿入SQLを作成する前に、まず数字テーブルを作成します。
これは、MySQLには「連番を生成する」という機能が存在しないため、マニュアルで連番テーブルを作成する必要があるからです。以下のSQL文を実行します。
DROP TABLE IF EXISTS C_NUM;
CREATE TABLE IF NOT EXISTS C_NUM(
N TINYINT NOT NULL
)ENGINE=MEMORY;
INSERT INTO C_NUM(N)VALUES
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),
(10),(11),(12),(13),(14),(15);
MySQLに限らずSQLには比較的予約語が多いため、テーブル名にはプレフィクスなどを付けて予約語制限に引っかからないようにします。
先ほどM_PEOPLEというテーブルを作成いたしました。今回はC_NUMというテーブルです。
テーブルの頭一文字はテーブル自体の種別を現すようにしています。
あくまで勝手な分類ですが、自分は
- M=マスタテーブル
- C=共通テーブル（システムとは特に関係のないもの）
- U=ユーザに関連するテーブル
- V=ビュー
等のように分類分けをしています。
また、今回テーブルはMEMORYストレージエンジンを利用しています。
参照回数がかなり多くなるためこうしていますが、MyISAMやInnoDBにしたところでたいした違いはないので好きにしてください。
INSERTで0～15の数字を入れているのは、早い話が0x00～0x0Fまでの値ということです。
この16個の数字を使うことで、1億の連番を作り出すことができるようになります。
連番ビューを作成する
連番テーブルを作成する、といっても別に0～99999999のデータが入ったテーブルを作る必要はありません。先ほど作成した0～15の数字が入ったテーブルだけで連番テーブルを完成させることが可能です。
以下のSQLを実行してください。
CREATE OR REPLACE VIEW V_NUM AS
SELECT
(N1.N << 24)
+(N2.N << 20)
+(N3.N << 16)
+(N4.N << 12)
+(N5.N << 8)
+(N6.N << 4)
+(N7.N)
as N
FROM
C_NUM as N1,
C_NUM as N2,
C_NUM as N3,
C_NUM as N4,
C_NUM as N5,
C_NUM as N6,
C_NUM as N7
;
C_NUMのテーブルを合計7つ結合しています。１つのテーブルには16個の数字が入っているので、1つ結合するごとにレコード数が16倍になります。
7つ結合しているのでつまり28bit分、2億6千万の連番レコードがこれにより取得できることになります。
ためしに実行してみる
まだ失敗してもすぐに取り返しはつくので、ためしにSELECTしてみましょう。
SELECT * FROM V_NUM ;
…すぐに結果が表示されましたか？ それならばいいですが、Raspberry Piなどの非力なシステムではおそらく固まったような状態になるはずです。固まったと思ったらすぐクエリの実行を停止してください。
このクエリが固まってしまう原因は、mysqlのjoin_buffer_sizeによるものです。
my.cnfを弄ってバッファを増やすことによって解決もできますが、ここはSQLだけで解決しましょう。
join_bufferのサイズが足らないならば、joinを減らせばよいのです。
4bitの数列を6回joinするのではなく、16bitの数列を1回joinするようにすればいいのです。
DROP TABLE IF EXISTS C_NUM16;
CREATE TABLE IF NOT EXISTS C_NUM16(
N INTEGER UNSIGNED NOT NULL
)ENGINE=MEMORY;
INSERT INTO C_NUM16(N)
SELECT
(N1.N << 12)
+ (N2.N << 8)
+ (N3.N << 4)
+ N2.N
FROM
C_NUM as N1,
C_NUM as N2,
C_NUM as N3,
C_NUM as N4
;
便宜的にC_NUM16という16bitの連番テーブルを作成いたしましたが、これは別にC_NUMに0～65535の数字を入れることでも達成できます。
ただ、今回のSQLでは先ほどのjoinを利用してINSERTをすることにより、SQL文１個で65536個の連番を生成できているというところに注目しておいてください。
おおよそ勘のいい人ならお分かりでしょうが、これと同じことをM_PEOPLEテーブルでも実行すれば「SQL文１個で１億レコード」が達成できるのです。
C_NUM16テーブルを利用して32bitの連番ビューを作成するSQLが以下です。
CREATE OR REPLACE VIEW V_NUM32 AS
SELECT
(N1.N << 16) + N2.N as N
FROM
C_NUM16 as N1,
C_NUM16 as N2
;
先ほどは28bitの連番を生成しようとしていましたが、今回は16bit x 16bitのため32bitの連番（0～42億)になります。多すぎ？ いや、多くても大丈夫です。
実行してみよう（２回目）
V_NUM32でSELECTする場合はクエリにLIMITを付けます。
流石に42億レコードは素直にSELECT出来ない筈なので、気をつけてください。
SELECT * FROM V_NUM32 LIMIT 100000000;
今回のビューは、比較的低スペックのマシンでもすぐに結果が出てくると思います。
私の利用しているRaspberry Pi 3 Model Bではたった180秒程度で結果が返ってきました。
実際にINSERTする
あとはINSERTすればいいだけです。
ですが、失敗するとSSDやHDDを傷めることになるので、くれぐれも桁数などには注意してください。1/10のオーダーで実行してから本番実行してみるのをお勧めします。
1/10のオーダーで完了した時間の丁度10倍くらいが実際に掛かる時間です。
今回使用したM_PEOPLEテーブルの場合、以下のクエリは 大体30分程度で挿入完了します。
処理時間はディスクの書き込み速度に比例するため、遅いディスクを利用している人は注意してください。
なお、１億件で大体2GB位の容量になるはずです。
INSERT INTO M_PEOPLE(SEX,BIRTH_DATE)
SELECT
NULL,NULL
FROM
V_NUM32
LIMIT
100000000 /*件数注意！*/
これで、目出度く１億人の擬似国民データが出来上がりました。
え？ 性別と誕生日がNULLじゃないかって？ 名前とかどうするのかって？
記事が長くなりそうなので、それはまた今度！