はじめに
MySQLには複数のストレージエンジンが用意されています。
ストレージエンジンはそれぞれ、性質が異なり、メリット・デメリットがあるため用途によって使い分ける必要がありますが、MySQLでは主にInnoDBというストレージエンジンが使われています。
今回はこのストレージエンジンのひとつである「Memoryストレージエンジン」を使ってMySQLのSQLパフォーマンスの改善を行ってみました。
「Memoryストレージエンジン」は、メモリ上にテーブルを展開する形となるため、劇的にパフォーマンスを改善する可能性があります。
環境
- MySQL5.6以上
ストレージエンジンの設定
テーブル作成の書式は以下のようになります。
CREATE TABLE db_name.tbl_name
(col_name1 data_type1, col_name2 data_type2, ...
) ENGINE=engine_name;
最後のストレージエンジンの指定で、「ENGINE=MEMORY」とすれば、MEMORYストレージエンジンでテーブルが生成されます。
SELECTによる読み込み速度の比較
10000000件のデータに対して、
全件検索（WHERE句なし）で集計（SELECT COUNT(*) FROM 〜）を実施。
| ストレージエンジン | 実行速度(sec) |
|---|---|
| InnoDB | 62.31 |
| MEMORY | 2.33 |
INSERTによる書き込み速度の比較
10000000件のデータをINSERT文で登録。
| ストレージエンジン | 実行速度(sec) |
|---|---|
| InnoDB | 146.24 |
| MEMORY | 7.10 |
結果
結果として、読み込みは約３０倍、書き込みは約２０倍の速度で処理することができました。MEMORYストレージエンジンはテーブルデータをメモリ上にもつためIOが速いのは明白です。
以下にMySQLのMEMORYストレージエンジンの特徴をまとめます。
MEMORYストレージエンジンの特徴
MEMORYストレージエンジンは、格納されたデータがすべてメモリ上に保持されます。
InnoDBでは、データはハードディスクへ書き込まれるため、読み書きのオーバーヘッドが少なくなく、IOが非常に高速になります。
ただし以下の注意点があります。
-
DBサーバーが落とされると格納されたデータは消える。（空テーブルのみ残る）
-
ロック制御がテーブルロックのため、並列処理でパフォーマンスが落ちやすい。（InnoDBは行ロック）
-
カラムはすべて固定長に変換されるため、一レコードのバイト数が多くなる。
-
データ型にBLOB、TEXTは使用できない。
-
インデックスは「HASH」がデフォルト。InnoDBは「BTREE」がデフォルト。
変更は可能。「HASH」は場合によって遅くなる可能性あり。
※テーブルロック（注意点2）に対する解決案
一時テーブル（CREATE TEMPORARY TEBLE構文）で作成すれば、クライアントごとに別々のテーブルになるため、並列処理の発生を抑えることができます。
CREATE TEMPORARY TABLE db_name.tbl_name
(col_name1 data_type1, col_name2 data_type2, ...
) ENGINE=MEMORY;
まとめ
MySQLのストレージエンジンは、テーブルの使用目的に合わせて選択することで、SQLのパフォーマンスを劇的に上げられる可能性があります。
MEMORYストレージエンジンはDBを落としたら消えるなど、デメリットもありますが、プログラム上の構造が変えられないような状況の時の選択肢として、うまく使えば、パフォーマンスチューニングを行う手助けになりそうです。
参考
MySQL5.6リファレンス 第 15 章 代替ストレージエンジン
MySQL5.6リファレンス 15.3 MEMORY ストレージエンジン
[MySQL5.6リファレンス 8.4.4 MySQL が内部一時テーブルを使用する仕組み]
(https://dev.mysql.com/doc/refman/5.6/ja/internal-temporary-tables.html)
[MySQL5.6リファレンス 13.1.17 CREATE TABLE 構文]
(https://dev.mysql.com/doc/refman/5.6/ja/create-table.html)