リファレンス ガイド: MySQL および MariaDB オンライン DDL

MySQL チュートリアル このコラムでは、MySQL と MariaDB Online DDL を紹介し、ガイドします。

概要

初期の MySQL バージョンでは、DDL 操作 (インデックスの作成など) には通常、データ テーブルをロックする必要がありました。プロセス中にブロックされ、通常のビジネスに影響します。 MySQL 5.6 および MariaDB 10.0 は、DML の通常の実行に影響を与えることなく DDL 操作を実行できるオンライン DDL のサポートを開始します。オンラインでの DDL 操作の直接実行は、基本的にユーザーには見えません (一部の操作はパフォーマンスに影響します)。

データベースのバージョンが異なると、さまざまな DDL ステートメントのサポートに特定の違いがあります。この記事では、オンライン DDL に対する MySQL と MariaDB のサポートについて要約します。DDL 操作を実行する必要がある場合は、この記事を参照してください。 オンライン DDL サポートセクション。

この記事は今後も改訂および更新されます。最新の内容については、GITHUB 上の私のプログラマー成長計画プロジェクトを参照してください。スターは大歓迎です。さらにエキサイティングなコンテンツについては、フォローしてください。

ALTER TABLE ステートメントでは、オンライン DDL は ALGORITHM ステートメントと LOCK ステートメントを通じてサポートされています。

ALGORITHM

- DDL 操作の実行方法と使用するアルゴリズムを制御します。
• LOCK
- DDL
• <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">ALTER TABLE tab ADD COLUMN c varchar(50), ALGORITHM=INPLACE, LOCK=NONE;复制代码</pre><div class="contentsignin">ログイン後にコピー</div></div> の実行時に許可されるテーブル ロックのレベルを制御します。 ALGORITHM サポートされるアルゴリズム

ALGORITHM説明DEFAULTデフォルトのアルゴリズム。利用可能な最も効率的なアルゴリズムを自動的に使用します。COPYすべてのストレージ エンジンでサポートされている最もオリジナルの方法では、オンライン DDL を使用しません。操作時には、一時テーブルを作成し、テーブル全体のコピーと再構築を実行し、プロセス中に REDO ログと大量の Undo ログを書き込みます。これには読み取りロックの追加が必要で、非常に非効率です。 #INPLACE テーブルのコピーと再構築をできるだけ避けてください。より正確な名前は、 アルゴリズムである必要があります。## の実装方法はストレージ エンジンによって決まります。 #. 一部の操作はすぐに有効になりますが (列名の変更、列のデフォルト値の変更など)、一部の操作ではテーブル全体またはテーブルの一部のコピーと再構築が必要になります (列の追加と削除、プライマリの追加など)。 に使用されます。テーブル全体の再構築を引き起こすクラスター化インデックス (主キー インデックス) の再構築を回避します また、このアルゴリズムを使用すると、クラスター化インデックスの再構築を引き起こす操作が 禁止されると言われています テーブルのコピーと再構築を伴うすべての操作禁止されます#

NOCOPY アルゴリズムのサポート: MariaDB 10.3.2、MySQL はこのアルゴリズムをサポートしていません。

INSTANT アルゴリズムのサポート: MariaDB 10.3.2、MySQL 8.0.12。

アルゴリズム使用規則:

• ユーザー指定のアルゴリズムが COPY の場合、InnoDB は COPY アルゴリズムを使用します。
• ユーザーが COPY 以外のアルゴリズムを指定した場合、InnoDB はアルゴリズムの効率に基づいて最も効率的なアルゴリズムを選択します。最悪の場合、ユーザーが指定したアルゴリズムが使用されます。たとえば、ユーザーが ALOGRITHM = NOCOPY を指定すると、InnoDB は (NOCOPY, INSTANT) からサポートされている最も効率的なアルゴリズムを選択します。

MySQL サービスは、主に サーバー層 と ストレージ エンジン層 で構成されます。サーバー層には MySQL が含まれます。データベース: 一部のコア関数、すべての組み込み関数、ストアド プロシージャ、トリガー、ビューなどのクロスストレージ エンジン関数。ストレージ エンジン層はデータの保存と読み取りを担当し、プラグイン アーキテクチャ モデルを採用します。

COPY アルゴリズム はサーバー層で動作し、その実行プロセスはサーバー層で行われるため、すべてのストレージ エンジンがこのアルゴリズムの使用をサポートしています。実行プロセスは次のとおりです

#INPLACE アルゴリズム はストレージ エンジン層で動作し、InnoDB ストレージ エンジンの独自の DDL アルゴリズムです。実行プロセスは次の図に示されています

LOCK ポリシー

デフォルトでは、MySQL/MariaDB は DDL の実行中に使用するロックをできるだけ少なくします。必要に応じて、LOCK 句を使用して、 DDL の実行時に許可されるテーブル ロックのレベルを制御します。指定された操作に必要な制限レベルが満たされていない場合 (

EXCLUSIVE > SHARED > NONE)、ステートメントの実行は失敗し、エラーが報告されます。

	ENGINE
	NOCOPYこのアルゴリズムは、INPLACE アルゴリズムのサブセットであり、
	INSTANT は、INPLACE を回避するために使用されます。データ ファイルを変更する必要がある場合、このアルゴリズムは非常に非効率的です。

戦略説明##DEFAULTNONE SHAREDEXCLUSIVE

为了避免执行 DDL 时，由于锁表导致生产服务不可用，在执行表结构变更语句时，可以添加 LOCK=NONE 子句，如果语句需要获取共享锁或者排它锁，则会直接报错，这样就可以避免意外锁表，造成线上服务不可用了。

Online DDL 执行过程

Online  DDL 操作主要分为三个阶段：

• 阶段 1：初始化

  在初始化阶段，服务器会根据存储引擎的能力，操作的语句和用户指定的 ALGORITHM 和 LOCK 选项来决定允许多大程度的并发。在这个阶段会创建一个 可升级的元数据共享锁（SU）来保护表定义。

• 阶段 2：执行

  这个阶段会 准备 并 执行 DDL 语句，根据 阶段 1 评估的结果来决定是否将元数据锁升级为 排它锁 （X），如果需要升级为排它锁，则只在 DDL 的 准备阶段 短暂的添加排它锁。

• 阶段 3：提交表定义

  在表定义的提交阶段，元数据锁会升级为排它锁来更新表的定义。独占排它锁的持续时间非常短。

元数据锁（リファレンス ガイド: MySQL および MariaDB オンライン DDL，Metadata Lock）主要用于 DDL 和 DML 操作之间的并发访问控制，保护表结构（表定义）的一致，保证读写的正确性。リファレンス ガイド: MySQL および MariaDB オンライン DDL 不需要显式的使用，在访问表时会自动加上。

由于上面三个阶段中对元数据锁的独占，  Online  DDL 过程必须等待已经持有元数据锁的并发事务提交或者回滚才能继续执行。

注意：当  Online  DDL 操作正在等待元数据锁时，该元数据锁会处于挂起状态，后续的所有事务都会被阻塞。在 MariaDB 10.3 之后，可以通过添加 NO WAIT 或者 WAIT n 来控制等待所得超时时间，超时立即失败。

ALTER TABLE tbl_name [WAIT n|NOWAIT] ...CREATE ... INDEX ON tbl_name (index_col_name, ...) [WAIT n|NOWAIT] ...DROP INDEX ... [WAIT n|NOWAIT]DROP TABLE tbl_name [WAIT n|NOWAIT] ...LOCK TABLE ... [WAIT n|NOWAIT]OPTIMIZE TABLE tbl_name [WAIT n|NOWAIT]RENAME TABLE tbl_name [WAIT n|NOWAIT] ...SELECT ... FOR UPDATE [WAIT n|NOWAIT]SELECT ... LOCK IN SHARE MODE [WAIT n|NOWAIT]TRUNCATE TABLE tbl_name [WAIT n|NOWAIT]复制代码

评估 Online DDL 操作的性能

Online DDL 操作的性能取决于是否发生了表的重建。在对大表执行 DDL 操作之前，为了避免影响正常业务操作，最好是先评估一下 DDL 语句的性能再选择如何操作。

1. 复制表结构，创建一个新的表
2. 在新创建的表中插入少量数据
3. 在新表上面执行 DDL 操作
4. 检查执行操作后返回的 rows affected 是否是 0。如果该值非 0，则意味着需要拷贝表数据，此时对 DDL 的上线需要慎重考虑，周密计划

比如

• 修改某一列的默认值（快速，不会影响到表数据）

  Query OK, 0 rows affected (0.07 sec)复制代码

• 添加索引（需要花费一些时间，但是 0 rows affected 说明没有发生表拷贝）

  Query OK, 0 rows affected (21.42 sec)复制代码

• 修改列的数据类型（需要花费很长时间，并且重建表）

  Query OK, 1671168 rows affected (1 min 35.54 sec)复制代码

由于在执行  Online  DDL 过程中需要记录并发执行的 DML 操作发生的变更，然后在执行完 DDL 操作之后再应用这些变更，因此使用  Online  DDL 操作花费的时间比不使用 Online 模式执行要更长一些。

Online  DDL 支持情况

INSTANT 算法支持：MariaDB 10.3.2+，MySQL 8.0.12+。NOCOPY 只支持 MariaDB 10.3.2 以上版本，不支持 MySQL，这里就暂且忽略了。

重点关注是否 重建表 和 支持并发 DML：不需要重建表，支持并发 DML 最佳。

セカンダリ インデックス

現在の操作でサポートされる最小の粒度ロック ポリシー
テーブル ロックを取得せず、すべての DML 操作を許可します
共有ロック (読み取りロック) をテーブルに追加し、読み取り専用の DML 操作のみを許可します
排他ロックをテーブルに追加します。テーブル (書き込みロック)、DML 操作は許可されません

##✅✅インデックスの名前を変更 (⚠️MySQL 5.7、MariaDB 10.5.2)フルテキストSPATIAL を追加#インデックス タイプの変更#✅✅手順:

操作	INSTANT	INPLACE	テーブルの再構築	同時実行DML	メタデータのみを変更します
セカンダリ インデックスを作成または追加します	❌	✅	❌	✅	❌
インデックスを削除	❌	✅	❌
#❌	✅	❌	✅	✅
インデックスを追加❌	✅ ①	❌ ①	❌	❌
インデックス (⚠️MySQL 5.7、MariaDB 10.2.2)❌	✅	❌	❌	❌
	##❌	✅	✅

① フルテキスト インデックス フィールドを初めて追加するときはテーブルを再構築する必要がありますが、再構築する必要はありません。その後必要になります

主キー

操作INSTANT#メタデータのみの変更❌✅ ②#✅❌✅#主キーを削除し、新しいキーを追加します❌✅✅✅❌##手順: NOT NULLUNIQUE インデックスを主キーとして選択するか、システム生成の KEY

INPLACE	テーブルの再構築	同時 DML			#主キーの追加
✅ ②			#プライマリキーを削除	❌	❌
❌	❌
				クラスター化インデックスの再構築には常にテーブル データのコピーが必要です (InnoDB は「インデックス構成テーブル」です)。したがって、テーブルの作成時に主キーを定義することが最善です。主キーを指定せずにテーブルが作成された場合、InnoDB は最初の

② を使用します。クラスター化インデックスの場合は、

INPLACE

モードを使用します。

COPY
• モードよりも効率的です。
undo ログ
• および redo ログ は生成されず、セカンダリ インデックスは順序付けされているため、順番にロードできます。変更バッファを使用する必要はありません。
通常の列
• 操作 INSTANTINPLACE

テーブルの再構築

同時 DMLメタデータのみの変更# #列を追加 ✅ ③#✅ ③❌#列の削除列名の変更❌✅❌#列の順序を変更します❌ ⑫✅✅✅❌デフォルト値を設定します ✅✅❌✅✅データ型の変更 ❌❌✅❌❌拡張子 長さ (⚠️MySQL 5.7、MariaDB 10.2.2) ❌ ⑬✅❌ ⑥✅✅##✅✅✅自己インクリメント値の変更❌✅❌✅❌ ⑦列を NULL に設定します❌✅ ✅ ⑧ ✅❌ 列を NOT NULL❌✅ ⑨✅ ⑨✅❌#ENUMSET の定義を変更します。 ✅

##✅

❌ ③

❌ ④

✅

✅

✅

❌

#✅ ⑤

✅

VARCHAR

#列のデフォルト値を削除

##❌# #✅

および

列

✅

❌ ⑩

✅

✅

説明:

• ③ 同時 DML: 自動インクリメント列を挿入する場合、同時 DML 操作はサポートされていません。自動インクリメント列を追加すると、大量のデータが必要になります。コストがかかる

• ③ テーブルの再構築: カラムを追加するとき、MySQL 5.7 以前のバージョンではテーブルを再構築する必要があり、MySQL 8.0 では ALGORITHM=INPLACE の場合にテーブルを再構築する必要があります。 ,ALGORITHM=INSTANT

• ③ の場合は再構築する必要はありません。 INSTANT アルゴリズム: 列を追加するときは、次の INSTANT アルゴリズムを使用します。次の制限があります

  • 列の追加操作は、INSTANT アルゴリズムをサポートしていない他の操作と 1 つの ALTER TABLE ステートメント
  ## に組み合わせることができません。 #新しい列はテーブルにのみ追加できます 最後に、他の列の前に配置することはできません MariaDB 10.4 以降では、任意の位置に列を追加することがサポートされています
  • ## を使用してテーブルに列を追加することはできません#ROW_FORMAT=COMPRESSED
  FULLTEXT
  • を含むテーブルには列を追加できません。一時テーブルには列を追加できません。一時テーブルは
  ALGORITHM=COPY のみをサポートします。
  • データ ディクショナリ テーブルスペースにあるテーブルに列を追加できません
  • 列を追加するときに行サイズの制限は計算されません。制限は、挿入する DML 操作を実行するときにチェックされます。またはテーブルを更新します
④ 列を削除する場合、大量のデータを再編成する必要があり、コストがかかります。MariaDB 10.4 以降では、列の削除で INSTANT アルゴリズムがサポートされています
⑤ 列の名前を変更するときは、同時 DML 操作をサポートするために、データ型ではなく列名のみを変更するようにしてください。

• ##⑥ VARCHAR の長さを拡張する場合、INPLACE は条件付きです。文字列の長さを識別するために使用されるバイト長が変更されないことを確認する必要があります (ここでは、VARCHAR の文字長ではなく、すべてのバイトが示されています。バイト占有は文字セットに関連しています)
utf8
• 文字セットでは、1 文字が 3 バイト、

  utf8mb4 で 4 バイトを占めます)VARCHAR カラムの長さが 0 ～ 255 バイトの場合、長さ識別子は 1 バイトを占めます

  VARCHAR 列の長さが 255 バイトを超える場合、長さ識別子は 2 バイトを占めます
  • したがって、INPLACE は 0 ～ 255 バイトまたは 256 バイトのみをサポートします。バイトをより大きな長さに変更します。 VARCHAR 列の長さの短縮は、INPLACE ではサポートされていません。
  ⑦ 自動インクリメント列値の変更は、データ ファイルではなく、メモリ内の変更された値です。

• ⑧ ⑨ を設定します。列の値
[ NOT] NULL
• 、大量のデータが再編成され、コストがかかります
  ⑩
ENUM
• と列の定義を変更します。

  SET タイプ テーブルのコピーが必要かどうかは、既存の要素の数と挿入されたメンバーの位置によって異なります。

  ⑫ MariaDB 10.4 以降では、列の並べ替えがサポートされています。 INSTANT アルゴリズム

• ⑬ MariaDB 10.4.3 以降、InnoDB は列の長さを増やすために INSTANT アルゴリズムの使用をサポートしますが、いくつかの制限があります。詳細については、「データの変更」を参照してください。列のタイプ

• 列の生成

操作INSTANTINPLACEテーブルの再構築同時 DMLメタデータのみの変更#STORED 列の追加STORED 列中 STORED 列仮想 列を追加します 列の並べ替えを変更します✅✅ ❌❌列を削除 ✅❌#✅✅外部キー# #操作

❌❌	✅	❌	❌		並べ替えを変更する
#❌❌	✅	❌	❌		削除
❌✅	✅	✅	❌
✅✅	❌	✅	✅		##VIRTUAL
❌					仮想
✅

INSTANT

INPLACE##テーブルの再構築✅ ⑭❌##外部キー制約の削除❌✅❌✅✅# ＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃

注:

• ⑭ 外部キーを追加する場合、INPLACE アルゴリズム は、
foreign_key_checks

オプションが有効な場合にのみサポートされます。

テーブル

同時実行DML	メタデータのみの変更			外部キー制約の追加	❌
✅	✅

##✅✅✅❌永続テーブル統計の設定❌ ✅❌✅✅指定された文字セット❌ ✅✅ ⑮❌❌文字セットの変換 ❌❌✅ ⑯❌❌##テーブルの最適化FORCE空のリビルドを実行します❌✅ ⑲✅✅❌テーブル名の変更✅✅❌✅✅注:

操作	INSTANT	INPLACE	テーブルの再構築	同時 DML	メタデータのみ変更
変更ROW_FORMAT	❌	✅	✅	✅	❌
KEY_BLOCK_SIZE	❌# を変更します
❌	✅ ⑰	✅	✅	❌
オプションを使用してくださいテーブルを再構築❌	✅ ⑱	✅	✅	❌

⑮⑯ 文字セットが異なる場合、テーブルを再構築する必要があります。

⑰⑱⑲ テーブルに
• FULLTEXT
フィールドが含まれる場合、 INPLACE
• テーブルスペース

操作INSTANTINPLACE##通常のテーブルスペースの名前変更❌✅❌✅✅通常の表領域の暗号化を有効または無効にする❌ ✅❌✅❌ テーブルスペースの暗号化❌✅❌❌一時テーブルにインデックスを作成すると、テーブルのコピーが発生します。

	##はサポートされません#テーブルの再構築	同時 DML	メタデータのみの変更
					ファイルごとに有効または無効にしますtable
❌					制限事項

TEMPORARY TABLE

テーブル ##ON...CASCADE
• または ON...SET NULL 制約がある場合、
ALERT TABLE
• は単語 LOCK=NONE# をサポートしません ##Onlne DDL 操作が完了する前に、関連テーブルでメタデータ ロックを既に保持しているトランザクションがコミットまたはロールバックされるまで待機する必要があります。このプロセス中、関連テーブルの新しいトランザクションはブロックされ、実行できません。大きなテーブルでテーブルの再構築を伴う DDL を実行する場合、次の制限が存在します。オンライン DDL 操作を一時停止したり、I/O または CPU 使用率を制限したりするメカニズムはありません。オンライン DDL 操作の数
オンライン DDL 操作のロールバックは、操作が失敗した場合に非常にコストがかかります。長時間実行されるオンライン DDL は、レプリケーションの遅延を引き起こす可能性があります。オンライン DDL 操作は、スレーブで実行する前にマスターで実行する必要があります。このプロセスでは、同時に処理される DML は、実行前にスレーブで DDL 操作が完了するまで待機する必要があります。
• • 最後に書いてます
  • この記事は今後も改訂、更新していきますので、より面白い内容をご覧いただくためにフォローしてください。
  • #その他の関連する無料学習の推奨事項:

mysql チュートリアル

(ビデオ)

以上がリファレンス ガイド: MySQL および MariaDB オンライン DDLの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。