MySQL
SQL
DB
プロシージャ
パフォーマンス

MEMORYストレージエンジンでMySQLのパフォーマンスをあげよう

はじめに

MySQLには複数のストレージエンジンが用意されています。
ストレージエンジンはそれぞれ、性質がことなりメリットデメリットがあるため用途によって使い分ける必要がありますが、通常MySQLでは主にInnoDBというストレージエンジンが使われています。

今回はこのストレージエンジンのひとつである「Memoryストレージエンジン」を使ってMySQLのSQLパフォーマンスの改善を行ってみました。

環境

  • MySQL5.6以上

ストレージエンジンの設定

テーブル作成の書式は以下のようになります。

CREATE TABLE 構文
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

結果

結果として、読み込みは約30倍、書き込みは約20倍の速度で処理することができました。MEMORYストレージエンジンはテーブルデータをメモリ上にもつためIOが速いのは明白です。

以下にMySQLのMEMORYストレージエンジンの特徴をまとめます。

MEMORYストレージエンジンの特徴

MEMORYストレージエンジンは、格納されたデータがすべてメモリ上に保持されます。
InnoDBでは、データはハードディスクへ書き込まれるため、読み書きのオーバーヘッドが少なくなく、IOが非常に高速になります。

ただし以下の注意点があります。

  1. DBサーバーが落とされると格納されたデータは消える。(空テーブルのみ残る)

  2. ロック制御がテーブルロックのため、並列処理でパフォーマンスが落ちやすい。(InnoDBは行ロック)

  3. カラムはすべて固定長に変換されるため、一レコードのバイト数が多くなる。

  4. データ型にBLOB、TEXTは使用できない。

  5. インデックスは「HASH」がデフォルト。InnoDBは「BTREE」がデフォルト。
    変更は可能。「HASH」は場合によって遅くなる可能性あり。

※テーブルロック(注意点2)に対する解決案

一時テーブル(CREATE TEMPORARY TEBLE構文)で作成すれば、クライアントごとに別々のテーブルになるため、並列処理の発生を抑えることができます。

CREATE TEMPORARY TABLE 構文
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 が内部一時テーブルを使用する仕組み
MySQL5.6リファレンス 13.1.17 CREATE TABLE 構文