はじめに

MySQLのストレージエンジンであるInnoDBについて学習したことを簡単にまとめます。

検証に使用した環境

DB：MySQL8.0

#ストレージエンジンとは
MySQLの中で実際にデータを格納する部分で、簡単にいうとファイルシステムにあたります。

InnoDBとは

MySQLで使用できるストレージエンジンの一つで、信頼性とパフォーマンスを両立した汎用性があります。
MySQL8.0では、デフォルトのストレージエンジンに採用されているおり、別のストレージエンジンを構成していなければ、 ENGINE=句を指定しなくてもInnoDBを使用してテーブルが作成されます。

2001年のMySQL3.23.34aから導入されました。

InnoDB の主要な利点

ACIDモデルに準拠

RDBMSによくあるトランザクション管理が実装されています。
そのため、コミット、ロールバック、クラッシュリカバリ機能でユーザーデータを保護します。

MySQLのストレージエンジンで唯一トランザクション管理を実装しています。

行ロックと一貫性読み取り

行レベルのロックと一貫性読み取りを使用すると、複数ユーザーの並列性およびパフォーマンスが向上します。

一貫性読み取りとは

マルチバージョンストレージエンジンであるInnoDBが、ある時点でのスナップショットを使用してクエリーを対処する仕組み

主キーインデックス

主キーに基づいてクエリーが最適化されるように、ディスク上のデータは整列されます。
InnoDBで作成された各テーブルには、主キー検索用のI/Oを最小限に抑えるためにデータを編成するclustered indexと呼ばれる主キーインデックスがあります。

clustered indexとは

主キーのシノニム

外部キー制約

データインテグリティ（データ完全性）のために、外部キー制約がサポートされています。
外部キーでは、挿入、更新および削除がチェックされ、異なるテーブル間で不整合が発生しないことが確認されます。

InnoDBテーブルのベストプラクティス

主キー検索を主体にする

最も頻繁にクエリーするカラムを使用してすべてのテーブルにprimary keyを指定するか、
明確な主キーがない場合はauto-increment値を指定するようにします。

複数のテーブルでデータを管理する場合は外部キーを定義する

複数のテーブルに分けてデータを管理する場合、データを参照する時にjoinを使用することになります。
結合に使うカラムに外部キーを定義し、各テーブルでそのカラムを同じデータ型で宣言しておきます。
そうすると、参照カラムが確実にインデックス付けされてパフォーマンスを向上させることができます。

autocommitをオフにしてコミットタイミングを任意で指定する

1秒間に何百回もコミットした場合、ストレージデバイスの書き込み速度で制限されてパフォーマンスに上限が設定されてしまいます。
そのため、autocommitをオフにしておきます。

しかし、頻繁にコミットしたくない一方で、コミットなしでINSERT、UPDATE、またはDELETE文を何時間も実行する処理も良くないです。
そのため、DML操作のセットを、START TRANSACTION、COMMITで囲みtransactionsにグループ化します。

データのロックは行ロックを使用する

LOCK TABLESステートメントを使用しないようにします。
InnoDBは、一度に同じテーブルへのすべての読み取りや書き込みを行い、信頼性や高パフォーマンスを犠牲にせずに、複数のセッションを処理できます。
行のセットへの排他的な書き込みアクセス権を取得するには、SELECT ... FOR UPDATE という構文を使用して、更新対象の行のみをロックします。

テーブルデータ、インデックスをsystem tablespaceに格納しない

innodb_file_per_tableオプションを有効にするか、一般的なテーブルスペースを使用して管理します。

innodb_file_per_tableはデフォルトで有効になっています。
しかし、もし無効にしていた場合、テーブルのデータ、インデックスをibdataファイル(system tablespace)に格納してしまい、ファイルを肥大化させる要因になります。

システムテーブルスペース

1つ以上のデータファイル(ibdataファイル)。
メタデータ、変更バッファの記憶域や二重書込みバッファを含みます。

sh-4.2# find /var/lib/mysql -name *ibdata*
/var/lib/mysql/ibdata1

圧縮

InnoDBでは、テーブルレベルとページレベルの両方の圧縮がサポートされます。
読み取りおよび書き込みの機能を犠牲にせずに、テーブルを圧縮できます。

※ページ圧縮は、透過的ページ圧縮とも呼ばれます。

ページとは

テーブルスペースを構成するもので、データベースページと呼ばれます。
すべてのテーブルスペースは同じページサイズで構成されています。

テーブルを別ストレージエンジンで作成されないようにする

オプションで「--sql_mode=NO_ENGINE_SUBSTITUTION」を指定してサーバーを実行し、CREATE TABLEの
ENGINE= 句で指定されたエンジンに問題がある場合に、別のストレージエンジンでテーブルが作成されないようにします。

主要な設定項目

innodb_buffer_pool_size

InnoDBバッファプールのサイズ。
通常は利用可能なメモリの7割程度を指定します。

デフォルト：128MB

innodb_log_file_size

InnnoDBログファイルのサイズ。
増やすことでページのフラッシュを遅らせることができ、更新処理性能が期待できます。

デフォルト：50M

innodb_io_capacity

バックグラウンドスレッドが実行可能なI/O操作の回数(IOPS)の上限。
使用するストレージのIOPSと同程度の値を指定します。

デフォルト：200

innodb_flush_neighbors

SSDや大容量キャッシュ付きストレージの場合はOFFにすると性能が上がる場合があります。

デフォルト：ON

innodb_print_all_deadlocks

デッドロックが生じた際にその情報をエラーログに出力する

デフォルト：OFF

参考文献