【MySQL学習】innoDB

# はじめに
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ファイル)。
メタデータ、変更バッファの記憶域や二重書込みバッファを含みます。

```shell
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

# 参考文献

https://dev.mysql.com/doc/refman/8.0/ja/innodb-introduction.html

https://dev.mysql.com/doc/refman/8.0/ja/innodb-best-practices.html

https://www.amazon.co.jp/%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E5%BE%B9%E5%BA%95%E6%94%BB%E7%95%A5-WEB-DB-PRESS-plus-ebook/dp/B07JJFKWRY/ref=sr_1_2?__mk_ja_JP=%E3%82%AB%E3%82%BF%E3%82%AB%E3%83%8A&crid=1K9INPJQXBEWP&keywords=db%E5%BE%B9%E5%BA%95%E6%94%BB%E7%95%A5&qid=1658022283&sprefix=db%E5%BE%B9%E5%BA%95%E6%94%BB%E7%95%A5%2Caps%2C247&sr=8-2