大きな MySQL テーブルに列を追加する方法

質問は https://www.zhihu.com/question/440231149 から参照されています。MySQL では、テーブルに 3 億のデータがあり、テーブルに分割されていません。この大きなテーブルにデータの列を追加する必要があります。データベースを停止することはできず、追加、削除、変更の操作がまだ残っています。どうすればいいですか?答えはオリジナルです

MySQL の古いバージョンでは、列を追加するために次の操作を行っていました。

ALTER TABLE テーブルに COLUMN 新しい列 char(128); を追加します。

テーブルロックが発生します。簡単なプロセスは次のとおりです。

• Table1と完全に同型の新しいTable2を作成します。
• Table1に書き込みロックを追加する
• ALTER TABLE 你的表ADD COLUMN 新列char(128)
• Table1のデータをTable2にコピーする
• Table2の名前をTable1に変更し、Table1を削除して、関連するすべてのロックを解除します。

データ量が非常に多い場合、テーブルロック時間が非常に長くなり、その間はすべてのテーブル更新がブロックされ、オンラインビジネスを正常に実行できなくなります。

MySQL 5.6 より前のバージョン (含まれません)では、トリガーを使用して、あるテーブルへの更新を別のテーブルに繰り返し、データを同期します。データの同期が完了すると、ビジネスはテーブル名を新しいテーブルに変更して公開します。営業停止はございません。トリガーの設定は次のようになります。

各行の元のテーブルに更新後にトリガー person_trigger_update を作成します。 
begin set @x = "トリガーUPDATE";
新しいテーブルに置き換えます。SELECT * from original table where new table.id = original table.id;
終了の場合;
終わり;

MySQL 5.6 以降のバージョンでは、オンライン DDL 機能が導入されています。

テーブルを変更します。ALGORITHM [=] {DEFAULT|INSTANT|INPLACE|COPY}、LOCK [=] {DEFAULT| NONE| SHARED| EXCLUSIVE }

パラメータは次のとおりです。

アルゴリズム：

• DEFAULT : デフォルト モード。MySQL 8.0 では、ALGORITHM が指定されていない場合、最初に INSTANT アルゴリズムが選択されます。これが失敗した場合は、INPLACE アルゴリズムが使用されます。INPLACE アルゴリズムがサポートされていない場合は、COPY メソッドが使用されます。
• INSTANT : 8.0 で追加された新しいアルゴリズム。追加された列はすぐに返されます。ただし、仮想列にすることはできません。原理は非常に単純です。新しい列が作成されても、テーブル内の元のデータはすべてすぐに変更されるわけではありません。列とデフォルト値のみがテーブル ディクショナリに記録されます。デフォルトの動的行形式 (実際には圧縮のバリアント) の場合、列が更新されると、元のデータは削除済みとしてマークされ、更新されたレコードが最後に追加されます。そうすると、事前にキュー スペースが予約されていないことになり、その後の更新によって行レコード スペースが変更される可能性が高くなります。しかし、ほとんどの企業では最新の記録のみが変更されるため、大きな問題にはなりません。
• INPLACE : 一時テーブルをコピーせずに元のテーブルを直接変更します。大量の undolog と redolog を生成したり、多くのバッファを占有したりすることなく、レコードを 1 つずつ変更します。これにより、テーブルの再構築によって発生する IO と CPU の消費を回避でき、期間中の良好なパフォーマンスと同時実行性が保証されます。
• COPY : 変更のために一時的な新しいテーブルにコピーします。レコードのコピーにより、大量のundologとredologが生成され、多くのバッファが占有されるため、業務パフォーマンスに影響を及ぼします。

ロック：

• DEFAULT : ALGORITHMのDEFAULTと同様
• NONE : ロックなし、テーブルの同時読み取りと更新が可能
• SHARED : 共有ロック。読み取りは許可するが更新は許可しない。
• 排他的: 読み取りと更新は許可されていません

さまざまなバージョンでサポートされているオンライン DDL 変更アルゴリズムの比較:

参考資料:

MySQL 5.6: https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.htmlMySQL

5.7: https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.htmlMySQL

8.0: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

これは次の方法で実行できます。

テーブルにALTER TABLEを実行し、新しい列 char(128)、ALGORITHM=INSTANT、LOCK=NONE を追加します。

同様のステートメントは、オンラインでフィールドを追加する場合にも使用されます。 DDL を実行する際に、オンライン業務にどの程度の影響を与えるかを明確に把握できるように、 ALGORITHM と LOCK を指定するのが最適です。

同時に、オンライン DDL を実行するプロセスは、おおよそ次のようになります。

初期段階ではメタデータ ロックが必要であることがわかります。メタデータ ロックは MySQL 5.5 で導入されました。以前にもメタデータを保護するための同様のメカニズムはありましたが、メタデータ ロックの概念は明確に提案されていませんでした。ただし、メタデータ保護に関しては、5.5 より前のバージョン (5.1 など) と 5.5 以降のバージョンの間には大きな違いがあります。5.1 はステートメント レベルでメタデータを保護しますが、5.5 はトランザクション レベルでメタデータを保護します。いわゆるステートメント レベルとは、ステートメントが実行された後、トランザクションがコミットされたかロールバックされたかに関係なく、他のセッションによってテーブル構造を更新できることを意味します。一方、トランザクション レベルとは、トランザクションが終了した後にのみメタデータ ロックが解除されることを意味します。

メタデータ ロックの導入により、主に 2 つの問題が解決されます。1 つはトランザクション分離の問題です。たとえば、反復可能分離レベルでは、セッション A が 2 つのクエリ中にテーブル構造を変更すると、2 つのクエリ結果に矛盾が生じ、反復可能読み取りの要件を満たすことができません。もう 1 つはデータ複製の問題です。たとえば、セッション A が複数の更新ステートメントを実行し、別のセッション B がテーブル構造を変更して最初にコミットすると、スレーブは最初に変更をやり直し、次に更新をやり直すため、複製エラーが発生します。

現在実行中のトランザクションが多数あり、次のような大きなクエリを含むトランザクションがある場合:

トランザクションを開始します。
テーブルからcount(*)を選択します

これにより、トランザクションの実行に長い時間がかかり、ブロックされることになります。

したがって、原則として、

• 大規模な取引を避ける
• 営業時間外にテーブル構造を変更する

大規模な MySQL テーブルに列を追加する方法についての記事はこれで終わりです。大規模な MySQL テーブルに列を追加する方法の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。