はじめに
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
参考文献