mysql update文の実行プロセスの詳細な説明

以前、MySQL クエリ文の実行プロセスについての記事がありました。ここでは、更新文の実行プロセスをまとめます。更新にはデータの変更が伴うため、更新ステートメントは選択ステートメントよりも複雑であると容易に推測できます。

1. 準備

テストテーブルを作成する

テーブル「test」を作成します（
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `c` int(11) NOT NULL デフォルト '0' コメント '値',
 主キー (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='テストテーブル';

3つのデータを挿入する

`test` (`c`) に値 (1)、(2)、(3) を挿入します。

2. テスト

最初のデータのc値に1を加えたい場合、

`test` を更新し、`c` = `c` + 1 を設定します。WHERE `id` = 1;

通常の考え方では、このレコードを見つけて、その値を変更し、保存するだけです。

しかし、詳細を見てみましょう。データの変更が関係するため、ログが関係します。

3 操作手順

3.1 レコードの検索: 実行者はまずエンジンで id=1 の行を検索します。 ID は主キーであり、エンジンはツリー検索を直接使用してこの行を検索します。 id=1 の行があるデータ ページがすでにメモリ内にある場合は、そのデータ ページは直接エグゼキュータに返されます。そうでない場合は、まずディスクからメモリに読み込んでから返す必要があります。

3.2実行者はエンジンから返された行データを取得し、num を 2 に変更し、新しいデータ行を取得してから、エンジン インターフェイスを呼び出してこの新しいデータ行を書き込みます。

3.3 エンジンは新しいデータ行をメモリに更新し、更新操作をREDOログに記録します。この時点で、REDOログは準備状態にあります。

3.4 エンジンは実行が完了したことをエグゼキュータに通知し、いつでもインターフェイスを呼び出してトランザクションを送信できます。

3.5 実行者はこの操作のバイナリログを生成し、そのバイナリログをディスクに書き込みます。

3.6エグゼキュータはエンジンのコミットトランザクションインターフェースを呼び出し、エンジンは書き込まれたばかりのREDOログをコミット状態に変更し、更新が完了します。

binlog MySQLの2フェーズコミット実装のコーディネーターです。各トランザクションにトランザクションIDを割り当てます: XID
フェーズ1 :
トランザクションを開始します。REDO ログと UNDO ログには対応するログが記録されています。この時点で、トランザクションの状態はprepare
フェーズ2 :

binlog write和fsyncを完了した後、トランザクションは正常にコミットされる必要があります。そうでない場合、トランザクションはロールバックされ、コミットが送信され、元に戻す情報がクリアされ、やり直しがフラッシュされ、トランザクション ステータスが完了に設定されます。
4. 2種類のログ 4.1再実行ログ

REDO ログは通常、特定の行がどのように変更されたかではなく、データ ページへの物理的な変更を記録する物理ログです。送信後に物理データ ページを復元するために使用されます (データ ページを復元し、最後に送信された位置にのみ復元できます)。

一般的な更新プロセスは次のとおりです。

元のデータを直接照会してすぐに更新します。まず一時的なノートブックを見つけて記録し、忙しくないときや決済中に計算と更新を実行します。

最初のアプローチは、同時 IO が多い状況ではあまり楽観的ではありません。したがって、一般的には 2 番目の方法が採用されます。

MySQL にも同様の問題が発生します。すべての更新操作をディスクに書き込む必要があり、ディスクも対応するレコードを見つけて更新する必要がある場合、プロセス全体の IO コストと検索コストが非常に高くなります。この問題を解決するために、MySQL の設計者は、REDO ログの考え方を使用して更新効率を改善しました。

一時ノートブックと元のデータを操作するプロセス全体は、MySQL でよく言及されるWAL (Write-Ahead Logging) テクノロジにも対応しています。 WAL の正式名称は Write-Ahead Logging です。 その重要なポイントは、最初にログを書き込んでからディスクに書き込むことです。具体的には、レコードを更新する必要がある場合、InnoDB エンジンはまずレコードを REDO ログ (メモ帳) に書き込み、メモリを更新します。この時点で更新が完了します。同時に、InnoDB エンジンは、適切な場合にこのノートブック内の操作レコードをディスクに更新します。この更新は、システムが比較的アイドル状態のときに実行されることが多いです。

質問: REDO ログ (メモ帳) がいっぱいになる問題を解決するにはどうすればよいですか?
InnoDB の redo ログは固定サイズです。たとえば、各ファイルが 1 GB の 4 つのファイルのグループとして構成できます。この場合、この「メモ帳」は合計 4 GB の操作を記録できます。下の図のように、先頭から書き始め、最後まで書き終えたら先頭に戻ってループ状に書きます。

書き込み位置は現在のレコードの位置です。書き込み中は後方に移動します。ファイル番号3の末尾まで書き込んだ後は、ファイル番号0の先頭に戻ります。チェックポイントは消去される現在の位置であり、後方に移動され、循環的に実行されます。レコードを消去する前に、レコードをデータ ファイルに更新する必要があります。

書き込み位置とチェックポイントの間のスペースは、新しい操作を記録するために使用できる「メモ帳」上の空きスペースです。書き込み位置がチェックポイントに追いついた場合、それは「ピンクボード」がいっぱいであることを意味します。この時点では、新しい更新は実行できません。チェックポイントを進めるには、まず停止していくつかのレコードを消去する必要があります。

InnoDB では、REDO ログを使用することで、データベースが異常に再起動した場合でも、以前に送信されたレコードが失われないことを保証できます。この機能はクラッシュセーフと呼ばれます。

4.2 アーカイブログ binlog

MySQL 全体は、実際には 2 つの部分で構成されています。1 つは主に MySQL の機能面を処理するサーバー層、もう 1 つは特定のストレージ関連の問題を担当するエンジン層です。上記の redo ログは InnoDB エンジン固有のログであり、サーバー層にも binlog (アーカイブ ログ) と呼ばれる独自のログがあります。

なぜログが2つあるのですか?

当初、MySQL には InnoDB エンジンはありませんでした。 MySQL に付属するエンジンは MyISAM ですが、MyISAM にはクラッシュセーフ機能がなく、binlog ログはアーカイブにのみ使用できます。 InnoDB は、別の会社によってプラグインの形で MySQL に導入されました。binlog のみに依存するとクラッシュセーフ機能が提供されないため、InnoDB は別のログ システム、つまり redo ログを使用してクラッシュセーフ機能を実現します。

これら 2 つのログには 3 つの違いがあります。

redo ログは InnoDB エンジンに固有のものですが、binlog は MySQL のサーバー層で実装されており、すべてのエンジンで使用できます。

Redo ログは、「特定のデータ ページにどのような変更が行われたか」を記録する物理ログです。一方、binlog は、「ID=2 の行の c フィールドに 1 を追加する」などのステートメントの元のロジックを記録する論理ログです。

Redo ログはサイクルで書き込まれ、そのスペースは最終的に不足します。一方、binlog は追加形式で書き込むことができます。 「追加書き込み」とは、binlog ファイルが一定のサイズに達すると、次のファイルに切り替わり、以前のログを上書きしないことを意味します。

5 2フェーズコミット

REDO ログの書き込みは準備とコミットの 2 つのステップに分かれており、これを「2 フェーズ コミット」と呼びます。

5.1 「2 フェーズコミット」はなぜ必要なのか?これは、2 つのログ間のロジックを一貫させるためです。

Binlog はすべての論理操作を「追加書き込み」形式で記録します。 DBA が半月以内にリカバリを実行できると約束した場合、バックアップ システムは過去半月のすべてのバイナリ ログを確実に保存し、データベース全体を定期的にバックアップします。ここでの「定期的」は、システムの重要度に応じて異なり、1 日に 1 回または 1 週間に 1 回になる場合があります。

指定した秒数、たとえばある日の午後 2 時に復元する必要がある場合、午後 12 時にテーブルが誤って削除されたことがわかり、データを回復する必要がある場合は、次のようにします。

まず、最新の完全バックアップを見つけます。運が良ければ、昨夜のバックアップである可能性があります。このバックアップから一時データベースに復元します。

次に、バックアップ時刻から始めて、バックアップされたバイナリログを 1 つずつ取り出し、正午にテーブルが誤って削除される前の時刻まで再生します。

この方法では、一時データベースは誤って削除される前のオンライン データベースと同じになります。その後、必要に応じて一時データベースからテーブル データを取得し、オンライン データベースに復元できます。

データ復旧プロセスについて説明した後、ログに「2 フェーズ コミット」が必要な理由について説明します。これを説明するために、背理法を使ってみましょう。

5.2 2相コミットを使用しない場合

redo ログと binlog は 2 つの独立したロジックであるため、2 フェーズ コミットを使用しない場合は、最初に redo ログが書き込まれ、次に binlog が書き込まれるか、順序が逆になります。

これら 2 つのアプローチでどのような問題が発生するかを見てみましょう。

引き続き前の更新ステートメントを例として使用すると、ID=1 の現在の行のフィールド c の値は 1 です。

5.2.1 最初にREDOログを書き、次にバイナリログを書き込む

REDO ログが書き込まれているが、バイナリログが書き込まれていないときに、MySQL プロセスが異常に再起動するとします。前述したように、REDO ログが書き込まれた後は、システムがクラッシュしてもデータを回復できるため、回復後のこの行の c の値は 2 になります。ただし、書き込みが完了する前にバイナリログがクラッシュしたため、このステートメントはバイナリログに記録されませんでした。このバイナリログを使用して一時データベースを復元する必要がある場合、このステートメントのバイナリログが失われているため、一時データベースではこの更新が失われます。復元された行の c の値は 1 であり、元のデータベースの値とは異なります。

5.2.2 最初にバイナリログを書き込み、次にログを再実行

バイナリログが書き込まれた後にトランザクションがクラッシュした場合、REDO ログはまだ書き込まれておらず、クラッシュ回復後のトランザクションは無効であるため、この行の c の値は 1 になります。ただし、binlog には「c を 1 から 2 に変更しました」というログが記録されています。したがって、後で binlog をリカバリに使用すると、復元された一時データベースのこの行の c 値は 2 になり、元のデータベースの値とは異なります。

「2 フェーズ コミット」を使用しない場合、データベースの状態は、ログを使用して復元されたデータベースの状態と一致しない可能性があることがわかります。

実際、このプロセスは、誤った操作の後にデータを回復するためにのみ必要なわけではありません。容量拡張が必要な​​場合、つまりシステムの読み取り機能を高めるためにバックアップ データベースをさらに必要とする場合、現在では、フル バックアップとアプリケーション binlog を使用してこれを実現するのが一般的な方法です。この「不整合」により、マスター スレーブ データベースにオンラインの不整合が発生します。

簡単に言えば、REDO ログと binlog の両方を使用してトランザクションのコミット ステータスを示すことができ、2 フェーズ コミットによってこれら 2 つの状態が論理的に一貫した状態に保たれます。

再実行ログはクラッシュセーフ機能を保証するために使用されます。 innodb_flush_log_at_trx_commit パラメータが 1 に設定されている場合、各トランザクションの REDO ログがディスクに直接保存されることを意味します。 MySQL の異常な再起動後にデータが失われないようにするには、このパラメータを 1 に設定することをお勧めします。

sync_binlog パラメータが 1 に設定されている場合、各トランザクションの binlog がディスクに永続化されることを意味します。 MySQL の異常な再起動後に binlog が失われないように、このパラメータを 1 に設定することもお勧めします。

上記はMySQLのupdate文の実行プロセスの詳細な説明の詳細な内容です。update文の実行プロセスの詳細については、123WORDPRESS.COMの他の関連記事に注目してください。