《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 に移動することを page made young と呼び、old リストに十分な時間生存しなかった場合に押し出されることを page not made young と呼びます。
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 つのクラスタ化インデックス（いわゆる主キー）があり、複数の補助インデックスが存在する可能性があります。テーブルに新しいレコードを挿入する必要があると仮定すると、このときインデックスツリーを更新する必要があります。クラスタ化インデックスが 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 内のすべてのデータは、共有表空間と呼ばれる空間に保存されています。表空間は、大から小の組織単位で構成されており、段 (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 がサポートする機能で、1 つのテーブルまたはインデックスデータを物理的に複数の部分に分解できます。現在の 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 を行い、異なる区に分けます。例えば hash (id % 1000)
- key パーティション：データベースが提供する hash 関数に基づいてパーティションを行います。hash されるフィールドを指定するだけで済みます。
パーティションの基礎の上に、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 種類に分かれます。1 つは
mutex、もう 1 つは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 ロックを保持していることがわかります。そのため、ブロックされて IX ロックが解放されるのを待つことになります。
以下の表は互換性マトリックスであり、すべての IS, IX と IS, IX, S, X の比較はテーブルレベルのロックの比較です。たとえば、IS と X が互換性がないということは、テーブルの意図共有ロックとテーブルの排他ロックが互換性がないことを示しており、意図ロックと行レベルのロックを比較しているわけではありません。
| IS | IX | S | X | |
|---|---|---|---|---|
| IS | 互換性あり | 互換性あり | 互換性あり | 衝突 |
| IX | 互換性あり | 互換性あり | 衝突 | 衝突 |
| S | 互換性あり | 衝突 | 互換性あり | 衝突 |
| S | 衝突 | 衝突 | 衝突 | 衝突 |
一貫性のある非ロック読み取り#
一貫性のある非ロック読み取りは、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, +∞) の範囲にもう 1 つの範囲ロックを追加する必要があります。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 が再度同じ SQL を実行する際には、結果が変わることはありません。なぜなら、この範囲はすでに 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 公式ドキュメント