しがないエンジニアのブログ

技術的な内容をメモ代わりにつらつら

mysqlのストレージエンジンの種類について

MySQL

mysqlのengineってなんだろうと思ったのでまとめてみた。
現在使っているのはInnoDBというものだが、実はいろいろ種類があるらしい。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

公式リファレンスはこちら
MySQL :: MySQL 5.6 リファレンスマニュアル :: 15 代替ストレージエンジン

それらを1つずつざっくりと説明する。

InnoDB

mysql5.5以降はデフォルトでこれが指定される。
とりあえずこちらを利用しておけばいいと思う。
以下のような特徴がある。

MyISAM

InnoDBと比較されるもう一つのストレージエンジン。
InnoDBを使う理由がない場合はMyISAMを使う。
以下のような特徴がある。

  • テーブルロック
  • アクセスが早い
  • トランザクション機能がない
  • InnoDBよりテーブルサイズが小さい

参考

MEMORY

名前の通り、データをメモリ上に展開して保存する。
そのため、mysqlを再起動するとデータが消えてしまう。
メモリ上にあるため、アクセスが非常に高速。
インデックスの種類として、b treeとhashの2種類が存在する（基本的にはb tree）

参考

CSV

sqlのデータをcsvファイルとして扱うことができる。
my.cnfに設定したdatadir以下に、.frmの他に、.CSM、.CSVの計3種類のファイルが作られる。
その中のcsvファイルを変更することで、DBのデータも一緒に変更することができる。
変更したあとはFLUSH TABLE;を忘れずに。

参考

ARCHIVE

データの登録、参照のみ可能で、変更ができない。
つまり、SELECT, INSERTのみの操作しけ受け付けず、UPDATE, DELETE, REPLACEなどは利用できない。
データは圧縮して保存されているため、容量が小さく済む。
また、インデックスを付与することができない。(AUTO INCREMENTのカラムは除く)

参考

BLACKHOLE

SELECTやINSERTなどのSQLを発行しても、すべてemptyが返ってくるような形式。
主に試運転時や、マスター-スレーブ構成のときに使うらしい（よくわかってないです）。
これについては参考サイトを確認してください。

参考

MERGE

複数のテーブルを結合し、あたかも1つのテーブルで処理しているかのようにできる。
INSERTやUPDATE,DELETEをした場合、merge元とmerge先のテーブルの両方のデータが更新される。
merge先のテーブルがインデックスをもつ場合、merge元のテーブルも必ず同じインデックスを有している必要がある。
merge先のテーブルにINSERTする場合、INSERT_METHODオプションでどのテーブルに挿入するかを決定する。

例

CREATE TABLE t1 (
  a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  message CHAR(20)) ENGINE=MyISAM;

CREATE TABLE t2 (
  a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  message CHAR(20)) ENGINE=MyISAM;

INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');

CREATE TABLE total (
  a INT NOT NULL AUTO_INCREMENT,
  message CHAR(20), INDEX(a))
  ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

参考

FEDERATED

別のネットワークのMySQLデータにアクセスし、あたかもローカルがDBを持っているかのように振る舞うことができる。
これによって、簡易的なマスター-スレーブ構成が作れる。

参考

EXAMPLE

自作のストレージエンジンを作成するための例として存在する。
クエリを発行できるが、SELECTやINSERTはできない。

参考