《MySQL 技術内幕 InnoDB ストレージエンジン (第 2 版)》 読書ノート。
バッファプール#
データベースにとって、クエリと読み取りの効率は非常に重要です。一般的にデータベースはデータをディスクに保存します。しかし、相対的にディスクの読み書き速度は遅く、データベースの高速な読み書きの要求を満たすことができません。したがって、一般的なストレージエンジンはバッファプール技術を利用して、ディスクの一部のデータをメモリに読み込んでキャッシュします。これをバッファプールと呼びます。
- 読み取り要求の場合、まずバッファプール内に対応するデータがあるか確認します。あれば、メモリから直接読み取って返します。なければ、ディスクから読み出してメモリに置いてから返します。
- 書き込み要求の場合、まずバッファプール内に対応するデータがあるか確認します。あれば、メモリデータを直接変更して返します。なければ、ディスクから対応するデータを読み出してメモリに置き、メモリデータを変更して返します。ストレージエンジンは、その後の特定の時間に、汚れたデータを再びディスクに書き戻します。
バッファプールの最も重要なパラメータはバッファプールのサイズであり、この値はデータベースの性能に直接影響します。バッファプールが十分に大きくない場合、キャッシュのヒット率が非常に低く、頻繁にディスクを読み取る必要があり、その効率は非常に低くなります。InnoDB もバッファプール技術を使用しており、そのサイズは innodb_buffer_pool_size で設定されています。InnoDB のバッファプールにキャッシュされるのはデータだけでなく、インデックス、undo バッファ、change バッファ、ハッシュインデックスなども含まれます。これらのメモリデータはメモリ内でページ (page) の形式で組織されており、ページのサイズは innodb_page_size で設定されており、デフォルトは 16K です。
InnoDB は複数のバッファプールインスタンスをサポートしており、各ページはハッシュによって異なるインスタンスに割り当てられます。これは innodb_buffer_pool_instances で設定されており、デフォルトは 1 です。複数のバッファプールは、内部のリソース（ロックの競合など）を減少させ、並行性能を向上させることができます。
LRU リスト#
バッファプールの仕事は、メモリから要求されたデータページを読み取ることです。メモリから読み取ることができれば、ヒットと呼ばれ、応答速度は非常に速くなります。読み取れない場合、ヒットしなければ、ディスクを読み取る必要があり、速度は非常に遅くなります。バッファプールの性能を観察する際の最も重要なパラメータはバッファヒット率です。
バッファヒット率を向上させるために、一般的には LRU(latest recent used) アルゴリズムが採用されます。このアルゴリズムは、バッファリストを維持し、最も頻繁にアクセスされるページをリストの最前面に置き、最も少ない使用のものを最後に置きます。キャッシュがヒットしなかった場合、ディスクから新しいページを読み取り、リストの最後のページを破棄し、新しく読み取ったページをリストに追加します。新しく読み取ったページをリストのどの位置に置くかは、具体的な実装によります。
InnoDB はこのアルゴリズムを採用していますが、いくつかの最適化を行っています。InnoDB の LRU リストでは、新しく読み取ったページはリストの midpoint の位置に置かれます。midpoint はパーセンテージであり、このパーセンテージ以降のリストは old リストと呼ばれ、それ以前のものは new リストと呼ばれます。modpoint を境界として、InnoDB の LRU は実際にはこの 2 つの独立したリストで構成されています。modpoint の値は innodb_old_blocks_pct で設定されており、デフォルトは 37 です。つまり、新しく読み取ったページはリストの末尾から 37% の位置に置かれます。
なぜ最前端に置かないのでしょうか？想像してみてください。もし最前端に置かれた場合、そのページは最も使用されるページと見なされます。しかし、インデックスやデータのスキャンなどの特定の SQL 操作では、大量のデータに頻繁にアクセスすることがありますが、これらのデータはおそらくこのスキャン中に一度だけ使用される可能性があります。このようなデータを毎回最前端に置くと、本当にホットなデータが LRU リストから押し出され、ヒット率が低下する可能性があります。しかし、新しく読み取ったデータページが本当にホットなデータである可能性もあります。どうやって判断するのでしょうか？
まず知っておくべきことは、new リスト内でヒットしたページは new リストの最前端に昇格されます。old リスト内でヒットしたページも old リストの最前端に昇格されます。
では、新しく読み取ったこのデータページがホットデータであると仮定しましょう。しばらくの間、このページは頻繁にアクセスされ、毎回アクセスされるたびに old リストの先頭に昇格されるため、このページは押し出されることはありません。しかし、ホットデータでなく一時的なデータであれば、すぐに下に押し出され、old リストから押し出される可能性があります。このように、データページ自身が十分に長く生存できることを保証することによって、基本的にこれはホットデータページであることを証明できます。したがって、InnoDB は innodb_old_blocks_time という別のパラメータを追加し、新しく読み取ったページが old リストにどれくらいの時間存在した後に new リストに移動されるかを示します。ページが old から new に移動されることを「ページが若くなる」と呼び、old リストで十分に長く生存しなかった場合に押し出されることを「ページが若くならなかった」と呼びます。
unzip LRU#
バッファプール内には、サイズが 16KB でないデータページがある場合があります。このようなページが LRU の 16KB ページを占有すると、いくつかの無駄が生じるため、InnoDB はこのような非 16KB の圧縮ページを管理するために unzip_LRU リストを追加しました。
現在、2KB のページが必要だと仮定します。unzip_LRU はまず 2KB のページを探します：
- 見つからなければ、次に 4KB のページを探します。見つかれば、2 つの 2KB のページに分割して割り当てを完了します。
- 見つからなければ、次に 8KB のページを探します。見つかれば、1 つの 4KB と 2 つの 2KB のページに分割して割り当てを完了します。
- 見つからなければ、次に 16KB のページを探します。1 つの 8KB、1 つの 4KB、2 つの 2KB のページに分割して割り当てを完了します。
フリーリスト#
上記の LRU リストの議論は、LRU がすでに満杯であるという仮定の下で行われましたが、実際にはデータベースが起動したばかりのときは LRU は空です。このとき、バッファプール内のすべてのページはフリーリストに置かれます。ディスクから新しいページを読み出すたびに、まずフリーリストから空いているページを探し、空いているページがあれば、それを LRU リストに移動します。空いているページがなければ、上記の LRU の議論に従って、特定のページを破棄します。
フラッシュリスト#
上記の 2 つのリストは読み取りの状況を議論していますが、通常のデータベース要求には多くの書き込み操作もあります。書き込み操作の場合、まず LRU のルールに従ってメモリ内のデータページを読み取り、そのデータページを変更します。この変更されたデータページは汚れたページ (dirty page) と呼ばれますが、汚れたページは LRU から削除されることはなく、同時に別のフラッシュリストに追加されます。これにより、メモリ内でデータの変更が完了し、その後この汚れたページの読み取りは LRU が担当し、最新のデータを読み取ることができます。一方、ストレージエンジンは一定のメカニズムを通じてフラッシュリスト内のデータをすべてディスクに書き戻します。このフラッシュは即時ではなく、大量のディスク書き込みがデータベースの性能に大きな影響を与えることになります。もちろん、データベースがダウンした場合、汚れたページがまだ書き戻されていない可能性があり、この場合、ディスクデータの永続性はトランザクションの実装によって保証されます。
チェックポイントメカニズム#
チェックポイント技術を議論する前に、上記で述べたフラッシュリストが汚れたページをフラッシュできなかった問題について議論します。InnoDB のトランザクション実装では、各トランザクションがコミットされる前に、まず redo log（リドーログ）を書き込みます。これはメモリの redo log buffer に書き込まれ、その後一定の頻度でディスクにフラッシュされ、このトランザクションによるデータの完全な変更を記録します。その後、メモリ内のページを変更します。これにより、データベースがダウンした場合、再起動時に redo log を通じてデータを回復できます。この方法により、理想的にはデータベースは汚れたページをディスクにフラッシュする必要がなく、毎回再起動時に redo log を読み込むだけで済みます。もちろん、前提としてメモリとディスクが十分に大きい必要がありますが、この状況は現実的ではありません。したがって、汚れたページは依然としてディスクにフラッシュされる必要があります。データベースがダウンして再起動する際には、前回ディスクにフラッシュされた後の redo log を回復すればよいのです。
データの変更に加えて、LRU old リスト内で破棄されたページが汚れたページである場合、その時点でもディスクにフラッシュする必要があります。そうしないと、次回ディスクから取得するのは古いデータになります。メモリ内の redo log buffer のサイズは innodb_log_buffer_size で設定されており、この上限を超えるとトランザクションを保証できなくなるため、汚れたページを即時にディスクにフラッシュして空の redo log buffer を確保する必要があります。
InnoDB は Checkpoint メカニズムを採用して汚れたページをディスクにフラッシュします。チェックポイントは内部で 2 種類に分かれます：
1 つは Sharp Checkpoint で、データベースが正常にシャットダウンする際にすべての汚れたページをディスクにフラッシュします。ここでの正常なシャットダウンとは、innodb_fast_shutdown がデフォルト値 1 に設定されていることを指し、バッファプール内の汚れたページをフラッシュし、他は気にしないというものです。この値が 0 の場合、データベースはシャットダウン前に完全なページ回収と change buffer のマージなどを行い、これを slow shutdown と呼びます。値が 2 に設定されている場合は、ログファイルをディスクのログファイルに書き込むだけで停止します。表データの完全性は次回の起動時の回復を待つ必要があります。このパラメータの具体的な動作については 公式ドキュメント を参照してください。これに関連する設定として innodb_force_recovery があり、InnoDB の回復を制御します。
もう 1 つは、実行中に継続的に実行される Fuzzy Checkpoint で、特定の条件下で一部の汚れたページをディスクにフラッシュします。これらの条件は以下の通りです：
- マスタースレッドは一定の時間ごとに非同期でバッファプール内の汚れたページをディスクにフラッシュします。
- LRU リスト内でデータページが淘汰された場合、汚れたページであればディスクにフラッシュされます。
- redo log のログ可能な空間が少なくなった場合、redo log ファイルの最大容量の 75% 以上に達した場合、非同期で汚れたページをディスクにフラッシュする必要があります。そうしないと、後続の redo log が不足し、トランザクションのコミットに影響を与えます。90% 以上に達した場合は、同期でディスクにフラッシュする必要があります。
- 汚れたページの数が多すぎる場合もディスクにフラッシュする必要があります。設定
innodb_max_dirty_pages_pct_lwmは、バッファプール内の汚れたページの割合を示し、この割合に達すると事前フラッシュが開始されます (TODO: 事前フラッシュとは何か？)。設定innodb_max_dirty_pages_pctの割合に達すると、通常のフラッシュが行われます。
バッファプールの状態を確認する#
InnoDB ストレージエンジンの状態を確認するコマンドを使用して、InnoDB の実行状態を確認できます。ここでは、バッファプールとメモリに関連するいくつかの重要なパラメータを示します。基本的に名前からもわかるでしょう。ここでの Free と LRU を足しても、バッファプールの総サイズにはなりません。なぜなら、バッファプール内のページはハッシュや change buffer など他の用途にも割り当てられるからです。
> show engine innodb status;
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456 // InnoDBに割り当てられた総メモリサイズ
Buffer pool size 8192 // メモリプールのページ数、サイズはpage_sizeを掛ける
Free buffers 7146 // フラッシュリストのページ数
Database pages 1042 // LRUリストのページ数
Old database pages 402 // LRUのoldリストのページ数
Buffer pool hit rate 1000 / 1000 // キャッシュヒット率
InnoDB エンジンの特性#
MySQL と InnoDB の紹介#
MySQL は比較的よく使用されるリレーショナルデータベースであり、他のデータベースとの違いはプラグイン式のストレージエンジンを提供している点です。公式の説明によれば、このプラグイン式は、自分でストレージエンジンを作成し、実行中の MySQL インスタンスに追加できることを意味します。再コンパイルや再起動は必要ありません。
InnoDB は MySQL5.5.8 以降のデフォルトストレージエンジンです。設計目標は主に OLTP を実現することで、行ロック設計を特徴とし、外部キーをサポートし、デフォルトで読み取り操作はロックを生成しません。各テーブルのデータを個別の .idb ファイルに保存できます。
InnoDB は MVCC(multiversion concurrency control) を通じて高い並行性を実現しています。4 つの隔離レベルを実装しており、デフォルトは REPEATABLE です。
InnoDB の表データの保存は主キーの順序に従って行われます。明示的に主キーを指定しない場合、InnoDB は自動的に 6 バイトの ROWID を主キーとして生成します。データ量がこの ROWID の範囲 (281 兆行) を超えると、最も古いデータが上書きされます。
Change buffer#
通常、データベースの各テーブルには集約インデックス（いわゆる主キー）があり、複数の補助インデックスが存在することもあります。テーブルに新しいレコードを挿入する必要がある場合、このときインデックスツリーを更新する必要があります。集約インデックスが 1 つだけの場合は簡単です。集約インデックスは一般的に順次増加しているため、新しい行を追加する際には順次書き込みを行い、新しいインデックスをインデックスツリーの最後に追加すればよいのです。しかし、テーブルに補助インデックスがある場合、新しいインデックスがどの位置に追加されるべきかを補助インデックスツリー内で見つける必要があります。このインデックスページがバッファプールにない場合、ディスクのランダムな読み書きを行う必要があります。大量の書き込み要求に対して、このような何度もランダムなディスク IO を行うことは性能に非常に影響を与えます。
したがって、InnoDB は change buffer のソリューションを導入しました。補助インデックスに対して挿入、更新、削除操作を行う際に、このインデックスページがバッファプールにある場合は、直接バッファプールを変更します。バッファプールにない場合は、まず変更を change buffer に置き、他の操作（たとえば、現在のインデックスページの読み取り要求がある場合）を待ちます。このとき、必ずディスクからバッファプールに読み込む必要があります。その後、change buffer は自分の変更を統合します。これにより、毎回 insert/update/delete のたびにランダムなディスク IO を行う必要がなくなります。
さらに、一定の時間内にインデックスページがバッファプールに読み込まれなかった場合でも、そのインデックスページに対して複数回アクセスまたは変更が行われた場合、複数の操作も change buffer の対応するインデックスページで統合され、後続のディスクへの書き込みは 1 回で済むようになります。
また、change buffer には一定の制限があります。まず、このインデックスは補助インデックスでなければならず、さらにこのインデックスはユニークであってはなりません。なぜなら、ユニークである必要がある場合、insert/update の際には必ずすべてのインデックスページをスキャンして同じインデックスが存在しないか確認する必要があり、change buffer を使用する意味がなくなります。
change buffer は 3 つのタイプに分かれます：
- insert buffer は挿入操作のバッファを示します。
- delete buffer はレコードを削除としてマークします。
- purge buffer はレコードを実際に削除します。
InnoDB 内部では、update は delete と purge の 2 つの操作によって共同で完了します。設定 innodb_change_buffering で change buffer のバッファタイプを指定できます。デフォルトは all で、すべてのタイプをバッファします。
InnoDB 内部では、change buffer の構造は B + ツリーです。このツリーにもサイズ制限があり、設定 innodb_change_buffer_max_size で change buffer が総バッファプールサイズの最大パーセンテージを示します。デフォルトは 25 です。change buffer の量が一定のサイズに達すると、内部でもインデックスページを強制的に読み取って統合し、change buffer の空間不足を回避します。
ダブル書き込み#
データベースが実行されている間、常にバッファプール内のデータをディスクに同期させて、データの永続性を保証します。このとき、たとえば 16KB のデータページがディスクに書き込まれる過程で、4KB を書き終えたところでデータベースがダウンした場合、このときに redo log があっても、ディスク上のページはすでに 4KB が書き込まれており、redo log が保存する物理ログではこのページを完全に復元することはできません（詳細な理由は以下の redo log のセクションを参照）。したがって、redo log を使用する前に、このページをバックアップする必要があります。書き込みが失敗した場合、バックアップページを使用してディスクページを復元し、その後 redo log を使用して復元を試みます。
InnoDB はこの問題を解決するためにダブル書き込み技術を使用します。メモリ内にはダブル書き込みバッファと呼ばれる 2MB の領域があり、ディスクの共有表空間にも 2MB のスペースがあります。バッファプールの汚れたページがフラッシュされる過程で、まず汚れたページをディスクの共有表空間に書き込み、fsync を実行した後、各テーブルのデータファイルに書き込みます。これにより、データファイルの書き込み中にエラーが発生しても、共有表空間から汚れたページのコピーを取得できます。（TODO: 共有表空間にフラッシュする際に、なぜ 2 回に分けて 1MB ずつ書き込むのか？）
設定 innodb_doublewrite でダブル書き込み機能を無効にできます。書き込み失敗保護を提供するファイルシステムでは、この機能は必要ありません。
非同期 IO#
InnoDB は非同期 IO の方法を採用してデータをディスクにフラッシュします。これにより、スレッドのブロッキングが発生せず、AIO は IO マージを行い、ディスク IO の回数を減少させることができます。Linux での AIO については man ドキュメント を参照できます。
設定 innodb_use_native_aio で AIO を有効または無効にできます。
隣接ページのフラッシュ#
InnoDB は汚れたページをディスクにフラッシュする際、そのページが存在する領域のすべてのページを検出し、他の汚れたページがあれば一緒にディスクにフラッシュします。これにより、IO マージを利用して IO の回数を減少させることができます。設定 innodb_flush_neighbors でこれを有効または無効にできます。
InnoDB ワーカースレッド#
上記では InnoDB の実行時の状態を見てきましたが、次に InnoDB がどのように機能しているかを紹介します。
マスタースレッド#
マスタースレッドは InnoDB の主スレッドであり、大部分の作業を担当します。主な作業は以下の通りです：
- 汚れたページをディスクにフラッシュする
- ログファイルをディスクにフラッシュする。トランザクションがまだコミットされていなくても、トランザクションのコミット速度を向上させる
- change buffer を統合する
- undo ページを回収する
IO スレッド#
IO スレッドは主に AIO のリクエストコールバックを担当します。設定 innodb_read_io_threads と innodb_write_io_threads で読み取りおよび書き込みスレッドの数を変更できます。
パージスレッド#
トランザクションの実行中に、トランザクションが失敗した場合にデータをロールバックするための undo log が記録されます。パージスレッドの仕事は、トランザクションの実行が完了した後に不要になった undo ページを回収することです。設定 innodb_purge_threads でこのスレッドの数を変更できます。
ログファイル#
ここで述べるログは実際には MySQL のログであり、InnoDB とは関係ありません。頻繁に確認および処理が必要なため、ここに含めました。
エラーログ#
エラーログは MySQL の起動、実行、シャットダウンプロセス中のエラーや警告を記録します。log_error 変数で確認できます。
スロークエリログ#
スロークエリログは、設定された long_query_time を超える実行時間の SQL 文を記録します。デフォルトは 10 秒です。slow_query_log を有効にすることで、デフォルトは無効です。ログの記録位置は設定 slow_query_log_file で指定します。
一般クエリログ#
一般クエリログは、すべてのデータベース要求を記録し、general_log_file ファイルに記録されます。general_log を設定することで有効または無効にできます。通常は無効にされており、常に有効にすると性能に一定の影響があります。通常はデバッグ時にのみ有効にします。
バイナリログ#
バイナリログは、データベースに対して実行されたすべての変更（select や show などを除く）を記録します。このログを使用してバックアップ復元やマスタースレーブ複製などを行うことができます。このログは datadir 下の bin_log.xxxx ファイルに記録されます。その中で bin_log.index はバイナリインデックスファイルで、以前の bin log シーケンス番号を保存します。
InnoDB はトランザクション実行中にバイナリログをバッファに記録し、トランザクションがコミットされた後にバッファ内の bin log をディスクの bin log に書き込みます。設定 sync_binlog でバッファを書き込んだ後にディスクファイルに同期する回数を調整できます。デフォルトは 1 で、つまりバッファを書き終えたらすぐにディスクにフラッシュします。このパラメータが大きく設定されている場合、ダウンした際に bin log がディスクにフラッシュされていない可能性があり、マスタースレーブデータの不一致が発生することがあります。
ストレージ構造#
InnoDB 内のすべてのデータは 1 つの空間に保存され、これを共有表空間と呼びます。表空間の組織単位は大きい順に：セグメント (segment)、エクステンド (extend)、ページ (page) であり、各レベルは複数の下位単位で構成されています。
表構造ファイル#
MySQL の任意のストレージエンジンの下には、テーブルの構造とユーザーが作成したビューの構造を保存する .frm テキストファイルがあります。
表空間ファイル#
InnoDB はデフォルトで、すべてのテーブルに共有の表空間があります。設定 innodb_file_per_table で各テーブルのデータを個別の表空間に保存できます。しかし、この場合、各テーブルの表空間はデータのみを保存し、インデックスや change buffer のビットマップページは他のデータは元の共有表空間に保存されます。表空間ファイルの拡張子は .ibd で、デフォルトの共有表空間ファイルは ibddata1 です。
表空間ファイルは、データセグメント、インデックスセグメント、undo セグメントなどのさまざまなセグメントで構成されています。セグメントは複数のエクステンドで構成され、エクステンドは複数のページで構成され、エクステンド内のページは連続しています。ページは InnoDB 内の最小単位です。ページにはデータページ、undo ページ、トランザクションデータページなど、さまざまな種類があります。
行レコード形式#
InnoDB 内のデータは行の形式でデータページに保存され、B + ツリーのノードに保存されます。もしある列のデータ型の長さが可変（たとえば text）であれば、データはオーバーフローページに保存される可能性があります。元のデータ行はオーバーフロー行のオフセットを保持します。定長データの場合、データの長さが大きすぎて、1 ページに 1 行のデータしか保存できず、B + ツリーの構造を満たさない場合、InnoDB は内部で自動的に行データをオーバーフローページに保存します。
データパーティション#
データパーティションは MySQL がサポートする機能であり、テーブルまたはインデックスデータを物理的に複数の部分に分解できます。現在の MySQL は水平パーティションのみをサポートしており、1 つのテーブルの異なる行を異なる物理ファイルに割り当てます。また、局部分割インデックスであり、各物理ファイルにはデータとインデックスの両方が保存されます。
MySQL のパーティションは以下の種類をサポートしています：
- range パーティション：指定された連続した行データの範囲に基づき、各範囲を 1 つのパーティションに保存します (1, 2, 3, 4) (5, 6, 7, 8)
- list パーティション：range に似ていますが、パーティションデータは離散的であることができます (1, 3, 5, 7) (2, 4, 6, 8)
- hash パーティション：ユーザー定義の式の戻り値に基づいて、異なる範囲にハッシュ分割します。たとえば、hash (id % 1000)
- key パーティション：データベースが提供するハッシュ関数に基づいてパーティションを行います。ハッシュされるフィールドを指定するだけで済みます。
パーティションの基本に基づいて、MySQL はさらにパーティションを行うことができ、これをサブパーティションと呼びます。MySQL は range および list パーティションの上に hash または key パーティションを行うことを許可します。具体的なパーティション構文については 公式ドキュメント を参照してください。
インデックス#
インデックスはデータベースのクエリ性能を向上させるための最も重要な技術です。InnoDB 内部では、さまざまなインデックスをサポートしています：B + ツリーインデックス、全文インデックス、ハッシュインデックスなど。一般的に開発者は B + ツリーインデックスを多く使用するため、ここでは B + ツリーインデックスについてのみ議論します。B + ツリーインデックスも多くの種類に分かれます。
集約インデックス#
各 InnoDB のデータテーブルには主キーがあり、集約インデックスはテーブルの主キーに従って構築された B + ツリーです。このツリーの葉ノードはテーブルのデータページであり、完全な行データを保存します。各データページは双方向リストで接続されており、テーブルデータの主キーに従ってデータが保存される順序と同じです。すべてのテーブルデータのクエリは、バッファヒットや特別な状況を除いて、必ずこの集約インデックスツリーを通じて対応するデータページを検索します。
集約インデックスもディスク上の物理データを持っていますが、集約インデックスはディスク上では物理的に連続しておらず、論理的に連続しています。たとえば、各インデックスノードの最後の項目が次のインデックスノードのファイル内のオフセットを指します。そうでなければ、集約インデックスを維持するコストが非常に高くなります。
補助インデックス#
各補助インデックスも独自の B + ツリーですが、このツリーの葉ノードは完全な行データを保存せず、補助インデックスのキーとテーブルの主キーのみを保存します。補助インデックスを通じて行をクエリすると、まずテーブルの主キーを取得し、その後主キーを使用して集約インデックスツリー上で完全な行データを検索します。もちろん、補助インデックスのキーにデータが含まれている特別な状況もあり、この場合は集約インデックスツリーを検索する必要はありません。このようなインデックスを「カバリングインデックス」と呼びます。
組み合わせインデックス#
組み合わせインデックスは、テーブルの複数の列に対してインデックスを作成することを指します。使用法は単一キーの補助インデックスと同じです。インデックスの並べ替え順序は、インデックス宣言の順序に従って並べられます。たとえば、(a, b) の組み合わせインデックスの場合、データの並べ替えは最初に a を並べ替え、その後 b を並べ替えます。たとえば、(1, 1) (1, 2) (2, 1) (2, 2) のように、さらに多くのキーの状況が続きます。
複数のキーの値をクエリする場合、たとえば where a = xx and b = xx のように、組み合わせインデックスを使用できます。また、組み合わせインデックスでは最初のキーに基づいて並べ替えが行われるため、where a = xx のような単一キーのクエリでも組み合わせインデックスを使用できます。
もう 1 つの使用法は、たとえば where a = x group by b limit n のようなクエリの場合、単一の補助インデックスでは、クエリ後に b を再度並べ替える必要があります。しかし、組み合わせインデックスでは、最初のフィールド a が定値であることが確定している場合、2 番目のフィールド b はすでに並べ替えられているため、並べ替え操作を減少させることができます。
自適応ハッシュインデックス#
InnoDB は B + ツリーを使用してインデックスを構築します。インデックスの検索回数は B + ツリーの深さに依存します。たとえば、深さが 3 のツリーの場合、インデックスの検索には 3 回のクエリが必要です。もし私たちがデータに頻繁にアクセスする場合、毎回 3 回のインデックスクエリを経るのは無駄です。InnoDB 内部では、各テーブルに自適応ハッシュインデックス（adaptive hash index）を構築し、特定のホットデータをキャッシュしてデータクエリを加速します。
たとえば、私たちが select a from t where id = 1; を何度も連続して要求した場合、AHI は where id = 1 というパターンにインデックスを作成します。その後、同じ要求を行う際には、B + ツリーインデックスを検索する必要がなく、自適応ハッシュインデックスからデータページを取得できます。また、ハッシュインデックスは等値クエリのみを行うことができ、範囲クエリをキャッシュすることはできません。
ハッシュインデックスを構築するには一定の条件制限がありますが、ハッシュインデックスは InnoDB 内部の最適化であるため、詳細には述べません。ユーザーは設定 innodb_adaptive_hash_index で AHI を有効または無効にできます。
ロックとトランザクション#
データベースへのアクセスは必ず並行して行われます。このとき、データの一貫性を保証するためにロックが必要です。
InnoDB には 2 種類のロックがあります：
- latch：軽量のロックで、ロックの時間は非常に短く、一般的にはスレッドデータを保護するために使用されます。latch はさらに 2 種類に分かれます：
mutexとrwlockです。 - lock：このロックはトランザクションに作用し、データベース内のテーブルや行をロックします。また、ロックの時間は比較的長く、トランザクションがコミットまたはロールバックされるまで保持されます。
ここでは、トランザクションに関連するロックに主に焦点を当てます。トランザクション内で、InnoDB は行レベルでテーブルデータにロックをかけます。
行ロックと意図ロック#
InnoDB は 2 種類の行ロックをサポートしています：
- 共有ロック（Shared Lock）：トランザクションが 1 行のデータを読み取ることを許可します。共有ロックは他のロックと互換性があります。
- 排他ロック（eXclusive Lock）：トランザクションが 1 行のデータを削除または更新することを許可します。排他ロックは他のロックとは互換性がありません。
たとえば、トランザクション T1 が特定の行のデータを取得したい場合、その行の S ロックを取得する必要があります。このとき、別のトランザクション T2 もその行のデータを取得したい場合、同じく S ロックが必要です。2 つの S ロックは互換性があるため、問題ありません。仮にトランザクション T3 がその行のデータを変更したい場合、T3 はその行の X ロックを取得する必要がありますが、X ロックは S ロックとは互換性がないため、T3 は T1 と T2 が S ロックを解放するまでブロックされます。
行ロックの他に、InnoDB はテーブルレベルで Intention Lock（意図ロック）を提供しています。
- 特定の行のデータを変更する必要がある場合、行の X ロックを取得する前に、まずそのテーブルの意図排他ロック（IX）を取得して、今後特定の行の排他ロックを要求することを示します。
- 特定の行のデータを読み取る場合、行の S ロックを取得する前に、そのテーブルの意図共有ロック（IS）を取得して、今後特定の行データの共有ロックを要求することを示します。
意図ロックは、希望する行のロックタイプを事前に示すものであり、テーブルレベルのロックとは衝突しません。意図ロックは主に他のテーブルロックとの比較やテストに使用されます。たとえば、トランザクション T1 が特定の行データの X ロックを取得した場合、必ず行の IX ロックも取得しています。このとき、全テーブル更新のトランザクション T2 がある場合、T2 はテーブル全体の X ロックを取得する必要があります。T2 は現在、どの行が X ロックを保持しているかを知る必要があります。行ごとにスキャンするのは非常に遅いため、テーブル上の意図ロックを確認することで、IX ロックが存在することを確認し、現在どのトランザクションが特定の行の X ロックを保持しているかを知ることができます。そうすれば、T2 はブロックされ、IX ロックが解放されるのを待つことになります。
以下の表は互換性マトリックスであり、すべての IS, IX および IS, IX, S, X の比較はテーブルレベルのロックの比較です。たとえば、IS と X が互換性がないというのは、テーブルの意図共有ロックとテーブルの排他ロックが互換性がないことを意味します。意図ロックと行レベルのロックを比較するわけではありません。
| IS | IX | S | X | |
|---|---|---|---|---|
| IS | 互換性あり | 互換性あり | 互換性あり | 衝突 |
| IX | 互換性あり | 互換性あり | 衝突 | 衝突 |
| S | 互換性あり | 衝突 | 互換性あり | 衝突 |
| X | 衝突 | 衝突 | 衝突 | 衝突 |
一貫性のある非ロック読み取り#
一貫性のある非ロック読み取りとは、InnoDB が MVCC を通じてデータベース内の行を読み取ることを指します。現在の行が X ロックされている場合、行のスナップショットを読み取ることでブロックを回避できます。読み取りスナップショットはトランザクションの undo log を使用して実現されます。この方法は、データベースの並行性能を大幅に向上させることができます。
RC および RR の隔離レベルでは、InnoDB は一貫性のある非ロック読み取りを使用します。しかし、RC では読み取るスナップショットデータは現在の最新データであり、RR ではスナップショットデータはトランザクション開始時のデータです。
特定の状況下で、ユーザーがデータの一貫性を保証する必要がある場合、一貫性のあるロック読み取りを使用することができます。InnoDB は 2 つの select ロックモードをサポートしています：
select ... for updateは行データに X ロックをかけます。select ... lock in share modeはデータに S ロックをかけます。
これらの 2 つの文は必ずトランザクション内で使用する必要があり、トランザクションがコミットまたはロールバックされると、自動的にこれらのロックが解放されます。
自動増分ロック#
自動増分列に関しては、並行書き込みの状況でデータの一貫性を保証するためにロックメカニズムが必要です。各自動増分列はメモリ内に新しく挿入された行の自動増分列の値を割り当てるためのカウンタを持っています。
InnoDB 内部では、自動増分ロック（AUTO-INC Locking）という特別なロックがこの値を維持します。各トランザクションが行を追加するたびに、自動増分ロックは select max(auto_inc) from t for update という文を通じて新しい自動増分列の値を取得します。取得後、この自動増分ロックはすぐに解放され、トランザクションが終了するのを待ちません。しかし、この方法ではテーブルをロックするため、高い並行性の下では効率が非常に低く、トランザクションは他のトランザクションが自動増分ロックを使用するのを待たなければなりません。また、トランザクションがロールバックされると、自動増分値が破棄され、自動増分列に不連続なギャップが生じることになります。
InnoDB は innodb_autoinc_lock_mode の値を設定して自動増分列のロック戦略を制御できます：
- 0：自動増分ロックを使用し、効率が非常に低い。
- 1：デフォルト値で、insert および replace 文に対してメモリ内のカウンタに mutex ロックをかけます。トランザクションロックはなく、相対的に速くなります。他のタイプの挿入には自動増分ロックが使用されます。
- 2：すべての挿入が mutex ロックを使用し、効率が非常に高くなりますが、自動増分値が不連続になる可能性があります。
自動増分列に関する詳細な内容は 公式ドキュメント を参照してください。
ロックアルゴリズム#
行ロックには 3 つのアルゴリズムがあります：
- Record Lock：単一の行をロックします。たとえば、特定の行データを変更する場合。
- Gap Lock：範囲をロックしますが、記録自体は含まれません。たとえば、現在のテーブルに 1、3、5、7 という 4 つのデータがある場合、トランザクションが 4 を追加しようとすると、(3, 5) の範囲をロックする必要があります。他のトランザクションがこの間にデータを挿入するのを防ぐためです。さらに 8 を挿入する場合は、(7, +∞) の範囲にロックを追加する必要があります。Gap Lock の主な目的は、複数のトランザクションが同じ範囲に挿入するのを防ぐことです。隔離レベルを RC に設定すると、Gap Lock を無効にできます。
- Next-Key Lock：前の 2 つのロックを組み合わせたもので、記録自体と範囲の両方をロックします。このロックは、ファントムリードの問題を解決するために主に使用されます。
クエリのインデックスに一意の属性が含まれている場合、InnoDB は Next-Key Lock を Record Lock にダウングレードします。たとえば、トランザクションがテーブルに行を挿入する必要がある場合、id=7 ... で、id が一意のインデックスである場合、7 の行だけをロックすれば十分であり、範囲をロックする必要はありません。
ファントムリードとは、同じトランザクション内で同じ SQL を 2 回連続して実行すると、異なる結果が得られることを指します。2 回目の SQL は、以前には存在しなかった行を返す可能性があります。
たとえば、トランザクション T1 が最初に select id from t where id > 10; という文を実行した場合、この時点ではデータが読み取られませんでした。しかし、同時に別のトランザクション T2 が insert into t (id) values (11); という文を実行し、T2 が先にコミットされました。その後、T1 が再度 select id from t where id > 10; を実行すると、突然データが存在することがわかります。このような動作はトランザクションの隔離性に違反し、1 つのトランザクションが別のトランザクションの結果を感知できることになります。
Next-Key Lock を使用すると、select id from t where id > 10; を実行する際に、(10, +∞) に X ロックを追加します。これにより、T2 が値を挿入しようとするとブロックされます。T1 が同じ文を再度実行する際、結果は確実に変わらないことが保証されます。なぜなら、この範囲はすでに X ロックされており、他のトランザクションがこの範囲内のデータを変更できないからです。
InnoDB のデフォルトのトランザクション隔離レベルである RR では、Next-Key Lock を使用してトランザクションを保護し、ファントムリードを回避します。
ロックを使用してトランザクションの隔離性の問題を解決する#
- ダーティリード：あるトランザクションが他のトランザクションがまだコミットしていない変更を読み取ることができる状態です。ダーティリードは RU 隔離レベルでのみ発生します。少なくとも RC では、トランザクションが未コミットのデータは他のトランザクションには感知されません。
- 不可逆読み取り / ファントムリード：解決策は上記で述べた通りです。繰り返しません。
- 更新の喪失：2 つのトランザクションが同じ行を更新し、トランザクションの隔離性により、最初にコミットされたトランザクションの結果が後にコミットされたトランザクションの結果に上書きされることを指します。これはデータベース自体の問題ではなく、並行トランザクションが本来生じる可能性のある結果です。解決策の 1 つは、上記で述べた一貫性のあるロック読み取りを使用してトランザクションを直列化することです。つまり、SERIALIZABLE 隔離レベルを使用することです。
トランザクションの ACID と隔離レベル#
強力なロック保護により、InnoDB が実装するトランザクションは完全に ACID に準拠できます：
- Atomicity（原子性）：トランザクションは不可分の単位であり、トランザクション内のすべての操作が成功裏に実行される場合にのみ、トランザクションは成功と見なされます。トランザクション内のいずれかの操作が失敗した場合、すでに実行されたすべての操作も撤回される必要があります。
- Consistency（一貫性）：トランザクション開始前と終了後に、データベースのすべての制約が破壊されないことを保証します。たとえば、一意性制約などです。
- Isolation（隔離性）：各トランザクションの読み書きは、コミット前に他のトランザクションには見えません。
- Durability（永続性）：トランザクションが一度コミットされると、その結果は永続的であり、たとえダウンしても再び復元可能です。
SQL 標準では、トランザクションの 4 つの隔離レベルが定義されています：
- READ UNCOMMITTED（未コミット読み取り）：あるトランザクションが実行中に、他のトランザクションがまだコミットしていない変更を読み取ることができます。つまり、ダーティリードが発生します。
- READ COMMITTED（コミット済み読み取り）：あるトランザクションが実行中に、他のトランザクションがすでにコミットした変更を読み取ることができますが、ファントムリードの問題があります。
- REPEATABLE READ（可再現読み取り）：あるトランザクションが他のトランザクションがすでにコミットした新しく挿入されたレコードを読み取ることができますが、他のトランザクションがすでにコミットした変更は読み取れません。InnoDB がデフォルトで使用する隔離レベルであり、このレベルでは Next-Key Lock を使用してファントムリードの問題を解決します。
- SERIALIZABLE（直列化トランザクション）：トランザクション内の各読み書きは、テーブルレベルの共有ロックを取得する必要があります。
一般的に、隔離レベルが低いほど、トランザクションのロック保護が少なく、ロックを保持する時間が短くなります。
InnoDB トランザクションの redo log と undo log#
トランザクションがコミットされる前に、すべてのログを redo log ファイルに書き込む必要があります。これにより、ダウンが発生しても redo log から復元でき、データの永続性が保証されます。また、redo log がファイルに書き込まれることを保証するため、各回のディスク書き込み時に redo log は fsync を実行します。設定 innodb_flush_log_at_trx_commit で redo log をディスクにフラッシュする戦略を調整できます。デフォルトは 1 で、つまり毎回 fsync を実行して書き込みを保証します。0 に設定すると、トランザクションコミット時に redo log をファイルに書き込みません。2 に設定すると、redo log をファイルシステムに書き込みますが、fsync を実行しないため、ログがディスクにフラッシュされないままダウンするとデータが失われる可能性があります。redo log のフラッシュ戦略は、トランザクションのコミット速度に大きな影響を与えます。
トランザクションが実行される過程で、redo log の他に undo log も生成されます。トランザクションがロールバックされると、これらの undo log を実行してデータを元の状態に戻します。undo log は論理ログであり、トランザクション開始時のデータに戻すわけではありません。なぜなら、同時に複数のトランザクションが元のデータを変更している可能性があるからです。
トランザクション制御文#
MySQL コマンドラインのデフォルト設定では、トランザクションは自動的にコミットされます。各 SQL 文が実行されると、すぐに COMMIT が実行されます。SET AUTOCOMMIT=0 を使用して自動コミットを無効にできます。また、BEGIN コマンドを使用して明示的にトランザクションを開始することもできます。ここで、トランザクション制御文を整理します：
- BEGIN：トランザクションを開始します。
- COMMIT：トランザクションをコミットします。
- ROLLBACK：トランザクションをロールバックします。
- SAVEPOINT id：チェックポイント id を作成します。
- RELEASE SAVEPOINT id：チェックポイントを削除します。
- ROLLBACK TO id：チェックポイント id までロールバックします。このチェックポイント以前に実行されたものだけがロールバックされます。
- SET TRANSCATION：トランザクションの隔離レベルを設定します。
参考と引用
MySQL 技術内幕 InnoDB ストレージエンジン (第 2 版)
Mysql InnoDB 公式ドキュメント