MySQL - ストレージエンジンのソースを表示

== 概要 ==
ストレージエンジンとは、RDBMSにおいてデータやトランザクションの管理を行う機能のことである。<br>
MySQLでは、テーブルごとにストレージエンジンを指定することができる。<br>
<br>
* データフォーマットの定義
* データ永続化
* インデックス管理
* トランザクション管理
* ロックおよび排他的制御
<br>
<center>
{| class="wikitable" | style="background-color:#fefefe;"
|+ 主なストレージエンジン
|-
! style="background-color:#66CCFF;" | ストレージエンジン 
! style="background-color:#66CCFF;" | 意味
|-
| InnoDB || デフォルトのストレージエンジン。<br>行ロック、トランザクション、クラッシュリカバリ等に対応している。
|-
| MyISAM || 古いMySQLのデフォルトのストレージエンジン。<br>テーブルレベルのロックに対応している。
|-
| MEMORY || メモリ上にテーブルを配置する。<br>アクセス頻度の高いデータ向け。
|-
| Archive || データを自動的に圧縮する。<br>データの追加、参照のみ行うことができる。<br>主に、ログや監査向けで使用する。
|-
| NDB || MySQL Cluster構成にて使用される。
|-
| CSV || CSV形式でデータを保存および使用する。
|-
| Blackhole || /dev/nullのように書き込んだデータを破棄する。
|}
</center>
<br><br>

== ストレージエンジンの操作 ==
==== 利用可能なストレージエンジンの確認 ====
 <syntaxhighlight lang="mysql">
 SHOW ENGINES;
 </syntaxhighlight>
<br>
==== ストレージエンジンを指定してテーブルの作成 ====
 <syntaxhighlight lang="mysql">
 USE <データベース名>;
 
 CREATE TABLE <テーブル名> (
    <カラム名 1> <型名>
    <カラム名 2> <型名>
    <カラム名 3> <型名>
    -- ...略
 )
 ENGINE = <ストレージエンジン名>;
 </syntaxhighlight>
<br>
==== 既存テーブルのストレージエンジン確認 ====
 <syntaxhighlight lang="mysql">
 USE <データベース名>;
 
 SHOW TABLE STATUS
    where name = '<テーブル名>' \G;
 </syntaxhighlight>
<br>
==== 既存テーブルのストレージエンジンの変更 ====
 <syntaxhighlight lang="mysql">
 USE <データベース名>;
 
 ALTER TABLE <テーブル名> ENGINE = '<ストレージエンジン名>';
 
 -- 例: sample_tableテーブルのストレージエンジンをMEMORYに変更する
 ALTER TABLE sample_table ENGINE = 'MEMORY';
 </syntaxhighlight>
<br><br>

== InnoDB ==
InnoDBで設定できるシステム変数を知りたい場合は、[https://dev.mysql.com/doc/refman/8.0/ja/innodb-parameters.html MySQLの公式ドキュメント]を参照すること。<br>
<br>
==== InnoDBのデータファイル ====
システム変数<code>innodb_file_per_table</code>により、テーブルごとにデータファイルを作成するかどうかを設定することができる。<br>
テーブルごとにファイルを作成することにより、テーブルの<code>DROP</code>文、<code>TRUNCATE</code>文等の実行時において、ディスク使用量を減らすことができる。<br>
 <syntaxhighlight lang="mysql">
 SHOW VARIABLES LIKE 'innodb_file_per_table';
 </syntaxhighlight>
<br>
システム変数<code>innodb_file_per_table</code>が<code>ON</code>の場合、データディレクトリ配下にテーブルごとの***.indファイルが作成される。<br>
なお、デフォルトのデータディレクトリは/var/lib/mysql/mysqlディレクトリである。<br>
<br>
==== InnoDBのトランザクションログ ====
データの更新は、以下に示すような流れで処理される。<br>
# トランザクションの実行直後に、InnoDBログバッファ (メモリ上の領域) に書き込む。
# コミット時に、InnoDBログファイル（WAL : Write Ahead Log）、および、バッファプール (メモリ上のキャッシュ) にログバッファの内容を書き込む。
# 任意のタイミングでバッファプールの内容をデータファイルへ反映する。
<br>
バッファプールの内容をデータファイルに反映する前にデータが消えた場合等に、InnoDBログファイルがリカバリに利用される。<br>
<br>
==== I/Oの設定 ====
 SHOW VARIABLES LIKE 'innodb_io%';
<br>
* innodb_io_capacity
*: バッファプールからのページのフラッシュや変更バッファからのデータのマージ等、InnoDBバックグラウンドタスクが実行可能なI/O操作の回数(IOPS)の上限である。
*: ワークロードに対して低い値では不十分であることを証明した場合を除き、20000以上のような極端な値は使用しないこと。
*: 値が大きすぎる場合、早期フラッシュでI/O処理が無駄になることに注意する。
*: デフォルト値は、200である。
* innodb_io_capacity_max
*: InnoDBは、システム変数<code>innodb_io_capacity</code>で定義されているよりも高い速度のIOPSでフラッシュできる。
*: InnoDBバックグラウンドタスクにより実行されるIOPSの最大数を定義する。
*: デフォルト値は、2000である。
<br>
==== ログファイルの設定 ====
 <syntaxhighlight lang="mysql">
 SHOW VARIABLES LIKE 'innodb_log%';
 </syntaxhighlight>
<br>
* innodb_log_buffer_size
*: ログファイルの書き込み時において、InnoDBで使用されるバッファのサイズ。
*: 大規模なログバッファにおいては、トランザクションおよびコミットの前にログをディスクに書き込むことなく大規模なトランザクションが実行できる。
*: したがって、多数のレコードの更新、挿入、削除するトランザクションの場合、ログバッファを大きくするとディスクI/Oを節約できる。 
*: デフォルト値は、16[MB]である。
*: <br>
* innodb_log_checksums
*: redoログページのチェックサムを有効または無効を指定する。
*: redoログページのCRC-32Cチェックサムアルゴリズムを有効にする。
*: このシステム変数が無効な時は、redoログページのチェックサムフィールドの内容は無視される。
*: <br>
*: redoログヘッダページおよびredoログチェックポイントページのチェックサムは無効化されない。
* innodb_log_compressed_pages
*: 再圧縮ページのイメージをredoログに書き込むかどうかを指定する。
*: 圧縮されたデータが変更されると、再圧縮が発生する場合がある。
*: このシステム変数は、リカバリ時に異なるバージョンのzlib圧縮アルゴリズムが使用された場合に発生する可能性がある破損を防ぐため、デフォルトで有効になっている。
*: zlibのバージョンが変更されないことが確実な場合は、このシステム変数を無効にすることにより、圧縮データを変更するワークロードのredoログ生成を減らすことができる。
*: <br>
*: このシステム変数の有効化または無効化の影響を測定するには、同じワークロードで両方の設定のredoログ生成を比較する。
*: redoログ生成の測定オプションには、<code>SHOW ENGINE INNODB STATUS</code>文の結果において、
*: LOGカラムでのLog sequence number (LSN) の監視、または、redoログファイルに書き込まれたバイト数の<code>Innodb_os_log_written</code>ステータスの監視が含まれる。
*: <br>
* innodb_log_file_size
*: InnoDBログファイルのサイズ。
*: サイズを増加させることにより、ページのフラッシュを遅延させることができ、更新時のスループットが上がる。
*: デフォルトは、50[MB]である。
*: <br>
* innodb_log_files_in_group
*: ログファイルの数。
*: ログファイルを結合したサイズ (innodb_log_file_size * innodb_log_files_in_group) は、最大で512[GB]にすることができる。
*: デフォルト値(推奨値)は、2である。
*: <br>
* innodb_log_group_home_dir
*: ログファイルのディレクトリパス。
*: <br>
* innodb_log_write_ahead_size
*: Read on Write問題に関するオプション
<br>
ディスクのブロックサイズよりも小さなデータをログファイルの途中に書き込む時、<br>
対象ブロックの読み取り後に書き戻すという非効率な処理を避けるため、ブロックサイズ分のみデータを補完して書き込みのみ行われるようにした。<br>
<br>

==== バッファプールの設定 ====
 <syntaxhighlight lang="mysql">
 SHOW VARIABLES LIKE 'innodb_buffer%';
 </syntaxhighlight>
<br>
* innodb_buffer_pool_chunk_size
*: <br>
* innodb_buffer_pool_dump_at_shutdown
*: MySQLのシャットダウン時にバッファプールにキャッシュされるページを記録するかどうか。
* innodb_buffer_pool_dump_now
*: <br>
* innodb_buffer_pool_dump_pct
*: <br>
* innodb_buffer_pool_filename
*: バッファプールのキャッシュを記録する場合のファイル名。
* innodb_buffer_pool_instances
*: <br>
* innodb_buffer_pool_load_abort
*: <br>
* innodb_buffer_pool_load_at_startup
*: 記録したバッファプールの内容をロードするかどうか。
* innodb_buffer_pool_load_now
*: <br>
* innodb_buffer_pool_size
*: InnoDBバッファプールのサイズ
*: 使用可能なRAMの50[%]〜70[%]程度を指定する。
*: デフォルトは、128[MB]である。
<br><br>


__FORCETOC__
[[カテゴリ:MySQL]]