MySQL 8.0 オンライン DDL クイック列追加の概要

問題の説明

数日前、同僚から次のような質問を受けました。「ビジネス A が MySQL から MongoDB に移行する理由は何ですか?」

正直に言うと、この質問に答えるのは簡単ではありません。なぜ移行する必要があるのでしょうか。データの量やデータの種類など、何らかのボトルネックが発生したに違いありません。そこで私はビジネスに相談し、最終的に答えを得ました。このビジネスの一部のテーブルには、頻繁にフィールドを追加する必要があるのです。 MongoDB でフィールドを追加するコストはほぼゼロですが、MySQL の下位バージョンでフィールドを追加するコストは依然としてかなり高くなります。

では、MySQL フィールドを追加するためによく使用される方法は何でしょうか?ここで簡単に列挙します:

1. Perconaのpt-oscツール

2. GitHubオープンソースプロジェクトgh-ostツール

3. MySQLネイティブオンラインDDL

MySQLオンラインDDLで列を追加する従来の方法

01 コピー方法

MySQLバージョン5.5以前で列を追加する方法: コピー

実行図は以下のとおりです。

元のテーブル A には、レコード 1、2、4、6 を含む 1 つのフィールドのみが含まれています。コピー アルゴリズムを使用して列を追加すると、次のようになります。

1. 2 つのフィールドを含む新しいテーブル tmp-A を作成します。

2. 次に、テーブル A のすべてのデータを行ごとに新しいテーブル tmp-A にコピーします。

3. 次にtmp-AテーブルとAテーブルを使用して交換します。

このようにして、新しいテーブルには 2 つのフィールドが含まれます。また、新しいテーブルのデータ レコードは元のテーブルよりもコンパクトになっていることにも注意してください。レコード 3 と 5 が削除されたため、元のテーブルの中央に穴が開いているか、スペースが断片化している可能性があります。

ご覧のとおり、コピー アルゴリズムではデータを 1 回コピーする必要があり、一時テーブル tmp-A を保存するための追加のストレージ スペースが必要になります。さらに、データのコピー プロセス中は、テーブル A の書き込み操作が失われます。つまり、テーブルの変更プロセス中にテーブル A のデータを更新することはできません。これは致命的な欠陥となる可能性があります。

02 インプレースメソッド

MySQL 5.6 ではオンライン DDL が導入され、上記のプロセスが次のように変更されました。

そのプロセスは、上記のコピー アルゴリズムとは多少異なります。

1. オンラインDDLプロセス中に、B+ツリーがテーブルAから抽出され、中間テーブルtmp-Aではなく中間ファイルtmp-fileに保存されます。

2. ステップ1の実行中、テーブルAへのすべての書き込みは行ログに記録されます。

3. 手順 1 が完了したら、すべての行ログを tmp ファイルに適用して、テーブル A と同じデータを含むデータ ファイルを取得します。

4. データ ファイル tmp-file を使用して、表 A のデータ ファイルを置き換えます。

このプロセス中、行ログが存在するため、これらの操作は失われないため、プロセス全体を通してテーブル A を追加、削除、変更、およびチェックできます。このため、このプロセスはオンライン DDL と呼ばれます。

また、ここで説明しておく必要があるのは、Copy アルゴリズムで生成される tmp-A 一時テーブルはサーバー レベルで作成されるのに対し、上記の Online DDL 操作の tmp-file はプラグイン ストレージ エンジン Innodb 内で生成されるということです。この変更操作は Innodb 内で完了することを Inplace (中国語で in place の意味) と呼びます。つまり、データを「サーバー レベルの一時テーブル」に移動する必要がないということです。

MySQL 8.0.12で導入されたインスタントメソッド

MySQL バージョン 8.0.12 では、列の追加を容易にする Instant メソッドが導入されました。インスタント アルゴリズムが列を追加する場合、テーブル全体を再構築する必要はなくなり、新しく追加された列の基本情報をテーブル メタデータに記録するだけで済みます。

その利点を見てみましょう。まず、テーブル t1 を作成し、260,000 件のレコードを挿入します。次に、列 col_1、col_2、col_3 をそれぞれ追加し、列を追加するアルゴリズムを copy、inplace、instant として指定します。結果は次のようになります。

 [テスト] 23:42:45> t1からcount(1)を選択します。
 +----------+
 | カウント(1) |
 +----------+
 |262144|
 +----------+
 セット内の1行（0.06秒）
 
解決策1: コピー
[テスト] 23:43:29> alter table t1 add col_1 int,algorithm=copy;  
クエリは正常、262144 行が影響を受けました (1.48 秒)
レコード: 262144 重複: 0 警告: 0

解決策2: インプレース
[テスト] 23:43:46> alter table t1 add col_2 int,algorithm=inplace; 
クエリは正常、影響を受けた行は 0 行 (0.58 秒)
レコード: 0 重複: 0 警告: 0

解決策3: インスタント
[テスト] 23:44:08> alter table t1 add col_3 int,algorithm=instant; 
クエリは正常、影響を受けた行は 0 行 (0.01 秒)
レコード: 0 重複: 0 警告: 0

m5480:[email protected] [テスト] 23:44:14> show create table t1\G
************************** 1. 行 ****************************
       表: t1
テーブルの作成: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT、
  `name` varchar(10) COLLATE utf8mb4_general_ci デフォルト NULL,
  `age` int デフォルト NULL,
  `score` int デフォルト NULL,
  `col_1` int デフォルト NULL,
  `col_2` int デフォルト NULL,
  `col_3` int デフォルト NULL,
  主キー (`id`)、
  キー `idx_sco` (`スコア`)
) エンジン=InnoDB AUTO_INCREMENT=458730 デフォルト文字セット=utf8mb4 COLLATE=utf8mb4_general_ci
セット内の1行（0.01秒）

結果から実行時間は次のようになることが容易にわかります。

コピー > インプレース > インスタント

同時に、コピー アルゴリズムによって影響を受ける行数はテーブル全体ですが、インプレース アルゴリズムとインスタント アルゴリズムによって影響を受ける行数は 0 であり、これらはオンライン DDL 操作であることを示しています。

最後に、次の方法でインスタント列の情報を表示することもできます。

[テスト] 23:53:01> SELECT * FROM information_schema.innodb_tables where name like 'test/t1'\G
 ************************** 1. 行 ****************************
      テーブルID: 1079
          名前: test/t1
          フラグ: 33
        列数: 10
         スペース: 22
    ROW_FORMAT: 動的
 ZIP_ページサイズ: 0
   スペースタイプ: シングル
 インスタント列: 6
セット内の 1 行 (0.00 秒)

ご覧のとおり、test.t1 テーブルの現在の列番号は 6 です。これは、テーブルの 7 番目の列であることを意味します (列番号は 0 から始まります)。

もちろん、インスタントアルゴリズムは通常の列の削除をサポートしておらず、列の順序を設定することもできず、その他の制限もあります。詳細については、公式ドキュメントを参照してください: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

しかし、これらの制限は、それが優れた DDL 関数であることを妨げるものではありません。 MySQL のバージョンが継続的に繰り返されることにより、以降のバージョンではより多くの変更操作で、instant などの効率的なアルゴリズムを使用できるようになると考えています。

以上がMySQL 8.0オンラインDDL高速列追加の詳細な概要です。MySQL DDL高速列追加の詳細については、123WORDPRESS.COMの他の関連記事に注目してください。