Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
16
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

updated at

Organization

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

はじめに

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

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

「Memoryストレージエンジン」はテーブルをメモリ上に展開する形になるため劇的にパフォーマンスが改善する可能性があります。

環境

  • 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 構文

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
16
Help us understand the problem. What are the problem?