メインコンテンツまでスキップ

MySQL の実行チェーン

· 約15分
tanzhuo

1 本の SQL がクライアントから送信され、データが実際にディスクへ書き込まれるまでには、コネクタ、パーサ、オプティマイザ、エグゼキュータを経由し、さらに Buffer Pool と 3 つのログの連携が欠かせません。この記事では、その実行チェーン全体を一本の流れとしてつなげて解説します。

コネクタからエグゼキュータまで​

クライアントの SQL 文が MySQL サーバーに送信されると、まず接続を確立する必要があります。コネクタはクライアントと接続を行い、ユーザーの認証情報と権限情報を検証します。権限チェックを通過すると、SQL パーサが SQL 文の意味解析を行います（SQL 文が正しいか、パースできるかをチェックします）。解析が完了すると、SQL の実行過程に対する最適化が行われます。たとえば不要な検索条件 1=1 を取り除いたり、最適なインデックスの選択や Where 条件のフィールドの並び替えを行ったりします。

その後、エグゼキュータがこの SQL 文を実行します。エグゼキュータは、データベースが使用しているストレージエンジンに応じて SQL を実行します。MySQL は設計上 2 つの層、すなわち Server 層とストレージエンジン層に分かれています。こうすることの利点は疎結合であることで、シーンに応じて適切なストレージエンジンを選択できます。

Buffer Pool​

よく使われる InnoDB ストレージエンジンは、ページ（16KB）単位でストレージ空間を管理しています。あらゆる CRUD 操作は最終的にページ全体を操作することになり、ページ全体を Buffer Pool にロードし、すべてのデータ操作は Buffer Pool 内で完結します。Buffer Pool は通常、サーバーメモリの 70%〜80% 程度に設定します。こうすることでデータ操作がメモリ上で直接実行され、データ操作の効率が向上します（もし DML 文のたびに IO 操作が必要になると、データベースのディスクはすぐにボトルネックになってしまいます。1 件のデータを変更するには、まずディスクから読み込み、それからディスクへフラッシュするので 2 回の IO、つまり 1 回の読み取りと 1 回の書き込みが発生します）。

例を挙げて説明しましょう。まず Buffer Pool がないと仮定します。user テーブルには 1 件のレコードだけがあり、そのレコードの age = 1 で、以下の 3 本の SQL を実行する必要があるとします。

トランザクション A：update user set age = 2（1 読み 1 書きで 2 回の IO）
トランザクション B：update user set age = 3（1 読み 1 書きで 2 回の IO）
トランザクション C：update user set age = 4（1 読み 1 書きで 2 回の IO）

毎回ディスクからデータをメモリに読み込んで変更し、それからディスクへフラッシュする必要があるため、合計で 6 回の IO が発生します。

Buffer Pool を導入すると、次のようになります。

トランザクション A：update user set age = 2（Buffer Pool に読み込んで変更、読み取り IO 1 回）
トランザクション B：update user set age = 3（Buffer Pool 内のデータを変更、IO 0 回）
トランザクション C：update user set age = 4（Buffer Pool 内のデータを変更しディスクへフラッシュ、書き込み IO 1 回）

データページのロードが必要なのは最初の 1 回だけで、その後の操作はすべてメモリ上で直接完結します。Buffer Pool を導入すると、この一連の操作は合計 2 回の IO で済みます。（ここでは基本的なロジックを簡単に示しただけで、実際の最適化ロジックはこれほど単純ではありません。とはいえ全体として、Buffer Pool はディスク上のデータをメモリにマッピングして操作することで、ディスク IO の回数を減らす仕組みです。ただし Buffer Pool はメモリ上でデータ変更操作を行うため、クラッシュや停電が発生するとデータが簡単に失われてしまいます。）

Buffer Pool の情報を確認するには：

-- 出力には Buffer Pool のサイズ、ヒット率、ダーティページ数などの情報が含まれる
SHOW ENGINE INNODB STATUS

そこで、いくつかの非常に重要なログが補助として必要になります。Redo Log（リドゥログ）、Bin Log（バイナリログ）、Undo Log（アンドゥログ）です。

Redo Log（リドゥログ）​

データを変更した後、変更後の値を先にディスク上の Redo Log に記録しておくことで、突然停電して Buffer Pool 内のデータがすべて失われても、電源復旧時に Redo Log をもとに Buffer Pool を復元できます。これにより、Buffer Pool によるメモリの高効率性を活かしつつ、データが失われないことも保証されます。Redo Log のフラッシュには 3 つの戦略があります。

  • 0 に設定：トランザクションのコミットごとにはフラッシュ操作を行わない（システムデフォルトでは Master Thread が 1 秒ごとにリドゥログの同期を行う）
  • 1 に設定：トランザクションをコミットするたびに同期フラッシュ操作を行う。これが最も安全な設定で、このタイミングでクラッシュした場合はトランザクションの commit が成功していないことを意味するため、復元すべきデータもない（デフォルト）
  • 2 に設定：トランザクションのコミットごとに Redo Log Buffer の内容を Page Cache に書き込むだけで同期は行わず、いつディスクへ同期するかはファイルシステム（OS）に任せる

ここでの Redo Log のフラッシュはシーケンシャル書き込みの WAL（Write-ahead logging）で、日本語では「先行書き込みログ」と訳されます。ランダムなディスク書き込みに比べて効率が大幅に向上しており、Kafka や RocketMQ などの MQ もシーケンシャルなログ書き込みを採用してディスクの書き込み性能を高めています。ディスクのシーケンシャル書き込みは十分高効率とはいえ、メモリ操作とはまだ差があります。Redo Log の効率をさらに高めるため、メモリ上に Change Buffer（デフォルトサイズは 16MB、Buffer Pool 内に配置され、占有割合をパーセンテージで設定可能）を設けてメモリ上で記録を行い、トランザクションがコミットされたときにのみディスクへのシーケンシャル書き込みを行うようになっています。

Change Buffer は多数の Block に分かれており、各 Block のサイズは 512KB です。1 つのトランザクションが生成するすべての Redo Log は 1 つの Group と呼ばれます。

Bin Log（バイナリログ）​

Bin Log は、データベースのすべてのテーブル構造変更（CREATE、ALTER TABLE など）およびテーブルデータの変更（INSERT、UPDATE、DELETE など）を記録するバイナリログです。SELECT や SHOW といった操作は記録されません。これらの操作はデータ自体を変更しないためです。ただし、汎用クエリログ（general log）を参照すれば、MySQL が実行したすべての文を確認できます。

注意すべき点として、update 操作がデータの変化を引き起こさなかった場合でも、Bin Log には記録されます。

Redo Log と同様に、Bin Log にも独自のフラッシュ戦略があり、sync_binlog パラメータで制御します。

  • 0 に設定：トランザクションのコミット前に Bin Log を OS Cache へ書き込み、いつディスクへフラッシュするかはオペレーティングシステムに任せる
  • 1 に設定：同期的なディスク書き込み方式で Bin Log を書き込み、OS Cache を使って Bin Log を書き込まない
  • n に設定：n 回のトランザクションコミットが行われるたびに Fsync を 1 回呼び出し、OS Cache 内の Bin Log を強制的にディスクへフラッシュする

Bin Log にはよく使われるユースケースが 2 つあります。

  • レプリケーション：MySQL Replication では Master 側で Bin Log を有効にし、Master が自身のバイナリログを Slave に伝達することで、Master-Slave 間のデータ一貫性を実現します。
  • データ復旧：mysqlbinlog ツールを使ってデータを復旧します。

さて、ここで疑問が生まれます。Bin Log も Redo Log も変更後の値を記録するものですが、両者は何が違うのでしょうか？Redo Log があるのに、なぜ Bin Log も必要なのでしょうか？

Bin Log と Redo Log の違い​

  • Bin Log は MySQL Server 層に属し、Redo Log は Engine 層に属する
  • Bin Log はすべてのエンジンで使用できるが、Redo Log は InnoDB 固有のもの
  • Bin Log は論理的な操作を記録し、Redo Log は更新された内容を記録する
  • Bin Log は追記書き込みで複数のファイルを形成し、Redo Log は固定サイズの数ファイルを循環的に書き込む
  • トランザクション実行中は複数の操作が継続的に Redo Log に書き込まれ、最後のコミット時にはじめて Bin Log に書き込まれる

Undo Log（アンドゥログ）​

データを変更するとき、Redo Log を記録するだけでなく、対応する Undo Log も記録されます。何らかの理由でトランザクションが失敗したり、ロールバックされたりした場合、この Undo Log を使ってロールバックできます。Undo Log はセグメント（segment）方式で記録されており、各 Undo Log 操作は記録時に 1 つの Undo Log Segment を占有します。役割は、トランザクション発生前のデータのバージョンを保存することで、ロールバックに使えるだけでなく、多版型同時実行制御（MVCC）における読み取りも提供できます。注意すべき点として、Undo Log はデフォルトでグローバルテーブルスペースに格納されています。Undo Log も MySQL のテーブルに記録されていると簡単に理解してよく、Undo Log を 1 件挿入するのは通常のデータを 1 件挿入するのと似たようなものです。つまり、Undo Log を書き込む過程でも同様に Redo Log への書き込みが必要になるということです。

実行チェーン​

以上の基礎知識を踏まえて、SQL 全体の実行チェーンを見てみましょう。

0、ここではコネクタ、パーサ、オプティマイザのステップは省略します（すでに上で説明しました）。

1、まず Undo Log がこのトランザクションのロールバック情報（トランザクション ID、ロールバックポインタ）を記録します。これはトランザクションのロールバックと、それに対応するトランザクション分離の MVCC のためです。

2、次に Buffer Pool 内のデータを操作します。操作と同時に、データ操作情報を Change Buffer にも追加する必要があります。もしこのデータページがまだメモリ上にない場合、データの一貫性に影響しないことを前提に、InnoDB はこれらの更新操作を先に Change Buffer にキャッシュします。こうすることで、ディスクからこのデータページを読み込む必要がなくなります。次回のクエリでこのデータページへのアクセスが必要になったとき、データページをメモリに読み込み、それから Change Buffer 内のこのページに関する操作を実行します。（ただし、状況によっては先にデータページをメモリに読み込んでから操作する必要がある場合もあります）

3、データ変更の操作情報を Change Buffer に追加した後、操作情報は同期的に Redo Log へフラッシュして永続化する必要があります。これはクラッシュによる操作データの喪失を防ぐためです。（ここでいう同期とは、トランザクションがコミットされた後にはじめてディスクへのフラッシュを考えるという意味です。トランザクションがコミットされていなければフラッシュする意味がありません。MySQL のデフォルト設定では Redo Log は同期フラッシュです。非同期フラッシュに変更すると、突然のクラッシュや停電時にデータ喪失が発生し、メモリ内の Change Buffer に記録された情報はすべて失われます）

4、具体的な SQL 操作は Bin Log Cache にも追加する必要があります。ただし Bin Log Cache に記録されるのは論理的な操作情報（実際に実行された DML・DDL・DQL 文）だけで、データ変更情報は記録されません。Bin Log Cache は各スレッドがプライベートに持つメモリ空間（デフォルト 32K）で、トランザクション全体が生成する Bin Log Event を格納するメモリサイズを指定します。大きなトランザクションの場合、このパラメータの設定値を超える可能性が高く、その場合は Bin Log 一時ファイルを使って格納する必要があります。

5、クライアントがトランザクションをコミットすると、2 フェーズコミットが行われます。まず Redo Log のログレコードをフラッシュし、Redo Log に記録された状態を prepare に変更します。次に Bin Log のログレコードをフラッシュし、Bin Log のフラッシュが成功したら、対応する Redo Log の状態を prepare から commit に変更します。両者のログの一貫性を必ず保たなければならないためで、2 つのログのデータが一致しないと、マスター・スレーブ間のデータ不整合をはじめとするさまざまな問題が発生します。この時点でクライアントにトランザクションのコミット成功を応答できますが、このときデータ（ダーティページ）はまだディスクに書き込まれておらず、依然として Buffer Pool に保存されています。対応するログの記録がすべて成功しただけの状態です。

6、MySQL サーバーは、メモリ内の Buffer Pool のダーティページデータを非同期でフラッシュします（適切なフラッシュのタイミング）。ダーティページのデータがディスクに書き込まれてはじめて、データが本当にディスクに永続化されたことになります。非同期フラッシュ中にクラッシュや停電が発生しても、起動後に InnoDB は Redo Log をもとに、以前の Buffer Pool のデータをやり直す（redo する）ことができます。

適切なフラッシュのタイミング​

1、Redo Log に空きがなくなったとき。Redo Log は永続化の保証として、更新操作のたびに必ず記録する必要があります。Redo Log に書き込めなくなったら、ページのフラッシュを行ってデータをディスクへ同期し、Redo Log の CheckPoint ポインタを移動させて新しい領域を空けなければなりません。このときメモリに余裕があれば、フラッシュし終えたページをメモリから追い出す必要はありません。

2、メモリにデータページが収まらなくなったとき。クエリ操作はディスク上のデータページをメモリに読み込んでから結果を返します。メモリにデータページが収まらなくなったら、最も長く使われていないデータページを追い出さなければなりません。そのデータページがダーティページであれば、ディスクにフラッシュしてから追い出す必要があります。

3、データベースがアイドル状態のとき。データベースが現在アイドル状態だと判断した場合、ダーティページのフラッシュを行います。このときメモリに余裕があれば、フラッシュ後に追い出しは行いません。

4、データベースが正常にシャットダウンされるとき。データベースのシャットダウン時にもページのフラッシュが行われ、それまでの変更がハードディスクに永続化されます。

まとめ​

MySQL は一連のログのシーケンシャル書き込みによって永続化の性能を高め、さらに Buffer Pool コンポーネントでディスク上のデータをメモリにマッピングして DML 操作を行うことで、スループット性能を大幅に向上させています。同時に非同期フラッシュを採用することで、異なるテーブルや行データの変更によるディスクランダム書き込みの低性能問題を回避しています。Redo Log はクラッシュ後のデータ復旧を保証し、Bin Log はレプリケーションとデータ復旧を支え、Undo Log はロールバックと MVCC を担います。この 3 つが 2 フェーズコミットと連携することで、性能とデータ一貫性のバランスを共に保証しているのです。

COMMENTS