MySQLのストレージエンジン(InnoDB)の基本

#ストレージエンジンとは
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      | *バッファプールのサイズ
+-------------------------------------+----------------+
```