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

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の他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • MySQL 8.0 で列を素早く追加する方法
  • MySQLオンラインDDLの使用に関する詳細な説明
  • MySQL DDL による同期遅延を解決する方法
  • MySQL 8.0 アトミック DDL 構文の詳細な説明
  • MySQL オンライン DDL ツール gh-ost 原理分析
  • MySQL DDLステートメントの使用
  • 一般的なMysql DDL操作の概要
  • MySQL 8.0 の新機能の分析 - トランザクション データ ディクショナリとアトミック DDL
  • MySQL データ定義言語 DDL の基本ステートメント
  • MySQL 8.0 DDLアトミック機能と実装原則
  • MySQLオンラインDDL gh-ostの使用の概要
  • MySQL 5.7 でブロックポジショニング DDL の問題を解決する
  • MySQL 8.0 の新機能: アトミック DDL ステートメントのサポート
  • MySQL がユーザー名とパスワードの漏洩を引き起こす可能性のある Riddle の脆弱性を公開

<<:  CSSで制御可能な点線を実装する方法

>>:  Vue3 テーブルコンポーネントの使用

推薦する

MySQL 5.7 のインストールと設定方法のグラフィックチュートリアル

このチュートリアルでは、MySQL 5.7のインストールと設定方法を参考までに紹介します。具体的な内...

Zabbixリモートコマンド実行の詳細な例

目次1つ。環境二。予防三つ。例Zabbix トリガーがしきい値に達すると、アラート メッセージの送信...

JavaScript インタビュー: 配列の平坦化メソッドを実装する方法

目次1 配列のフラット化とは何ですか? 2 JS標準ライブラリの配列フラット化メソッド3 フラットメ...

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

目次問題の説明MySQLオンラインDDLで列を追加する従来の方法01 コピー方法02 インプレースメ...

vue-cli の紹介とインストール

目次1. はじめに2. vue-cli の紹介2.1 コマンドライン2.2 CLI サービス2.3 ...

Dockerでmongodbデータベースを使用するための実装コード

mongoイメージを取得する sudo docker pull mongo mongodbサービスを...

MySQL 8.0.22 winx64 のインストールと設定方法のグラフィックチュートリアル

MySQL-8.0.22-winx64のデータベースインストールチュートリアルは参考になります。具体...

CSS マスクを使用して PNG 画像のサイズを大幅に最適化します (推奨)

この記事は共有および集約することを歓迎します。全文を転載する必要はありません。著作権を尊重してくださ...

Vue 開発プロジェクトで Font Awesome 5 を使用する方法

目次依存関係をインストールする構成使用1.アイコン検索ページに入る2. 使用したいアイコンの英語名を...

IE6では画像要素imgに余分な空白スペースがある

ページの DIV+CSS レイアウトを行う際、IE6 で画像要素 img の下に余分なスペースができ...

JavaScript BOM ロケーション オブジェクト + ナビゲーター オブジェクト + 履歴オブジェクト

目次1. 場所オブジェクト1. URL 2. 場所オブジェクトのプロパティ3. ロケーションオブジェ...

Firefox または IE でスパン幅が決定されない場合の解決策

コードをコピーコードは次のとおりです。 <html xmlns="http://ww...

Webpack でよく使われる 12 個の Loader を共有する (要約)

目次序文スタイルローダーCSSローダーsassローダーpostcssローダーバベルローダーtsローダ...

sqlite3 から mysql に移行するときに起こりうる問題のコレクション

簡単な説明適切な読者: モバイル開発sqlite3 データを mysql に移行する場合、多くの構文...