ストレージエンジンとは
RDBMSにおいてデータやトランザクションの管理を行うメイン機能のこと。
MySQLではテーブルごとにストレージエンジンを指定できる。
- データフォーマットの定義
- データ永続化
- インデックス管理
- トランザクション管理
- ロック・排他的制御 などを行う
主なストレージエンジン
| ストレージエンジン | 説明 |
|---|---|
| InnoDB | デフォルト。行ロック、トランザクション、クラッシュリカバリなどに対応 |
| MyISAM | 旧デフォルト。テーブルレベルのロック |
| MEMORY | メモリ上にテーブルを配置。アクセス頻度の高いデータ用 |
| Archive | データを自動的に圧縮。データの追加、参照のみ。ログや監査用 |
| NDB | MySQL Cluster構成にて使用される |
| CSV | CSV形式でデータを保存。を使用する |
| Blackhole | /dev/nullのように書き込んだデータを破棄 |
ストレージエンジンの操作
利用可能なストレージエンジンの確認
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | 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 |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
ストレージエンジンを指定してテーブル作成
mysql> use mysql
mysql> CREATE TABLE test_table (i int) ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)
既存テーブルのストレージエンジン確認
mysql> use mysql
mysql> SHOW TABLE STATUS where name = "test_table" \G;
*************************** 1. row ***************************
Name: test_table
Engine: MEMORY
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 8
Data_length: 0
Max_data_length: 16777216
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-30 16:57:18
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
--------------------------
mysql> use information_schema;
mysql> select table_schema, table_name, engine from tables where table_name = "test_table";
+--------------+------------+--------+
| table_schema | table_name | engine |
+--------------+------------+--------+
| mysql | test_table | InnoDB |
+--------------+------------+--------+
1 row in set (0.00 sec)
既存テーブルのストレージエンジン変更
mysql> use mysql
mysql> ALTER TABLE test_table ENGINE = "MEMORY";
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
InnoDB
InnoDBのデータファイル
システム変数innodb_file_per_tableにより表ごとにデータファイルを作るか否かを設定できる。
テーブルごとにファイルを作ることで、テーブルのDROP、TRUNCATEなどの際にディスク使用量を減らすことができる。
mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
ONの場合、データディレクトリ配下に、テーブルごとの***.indというファイルが作成される
# ll /var/lib/mysql/mysql | grep test
-rw-r-----. 1 mysql mysql 98304 Oct 30 18:53 test_table.ibd
InnoDBのトランザクションログ
トランザクションが実行されると、InnoDBログバッファ（メモリ上の領域）に書き込み
↓
コミットされると、InnoDBログファイル（WAL - Write Ahead Logと呼ばれる）＆バッファプール（メモリ上のキャッシュ）にログバッファの内容を書き込み
↓
適当なタイミングでバッファプールの内容をデータファイルへ反映
上記の流れでデータが更新される。バッファプールの内容をデータファイルに反映する前に、データが消えてしまった場合などに、InnoDBログファイルがリカバリに利用される。
ログファイルの設定
mysql> SHOW VARIABLES LIKE 'innodb_log%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| innodb_log_buffer_size | 16777216 | *ログファイルに書き込む際に InnoDB で使用されるバッファーのサイズ
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 | *ログファイルのサイズ
| innodb_log_files_in_group | 2 | *ログファイルの数
| innodb_log_group_home_dir | ./ | *ログファイルのディレクトリパス
| innodb_log_write_ahead_size | 8192 | *read-on-write問題に対するオプション
+-----------------------------+----------+
*ディスクのブロックサイズよりも小さなデータをログファイルの途中にwriteするときに、対象ブロックを読み取ってから書き戻すという非効率な処理を避けるために、ブロックサイズ分だけデータを補完してwriteだけ行われるようにした
上記設定でのログファイルは以下の通り。
# ll /var/lib/mysql/ | grep log
-rw-r-----. 1 mysql mysql 50331648 Oct 30 18:53 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Oct 30 15:57 ib_logfile1
バッファプールの設定
mysql> SHOW VARIABLES LIKE 'innodb_buffer%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON | *MySQLのシャットダウン時にバッファプールにキャッシュされるページを記録するかどうか
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool | *バッファプールのキャッシュを記録する場合のファイル名
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON | *記録したバッファプールの内容をロードするかどうか
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 | *バッファプールのサイズ
+-------------------------------------+----------------+