Help us understand the problem. What is going on with this article?

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      | *バッファプールのサイズ
+-------------------------------------+----------------+
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
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  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
ユーザーは見つかりませんでした