##DEFAULT
現在の操作でサポートされる最小の粒度ロック ポリシー |
|
NONE
テーブル ロックを取得せず、すべての DML 操作を許可します |
|
SHARED
共有ロック (読み取りロック) をテーブルに追加し、読み取り専用の DML 操作のみを許可します |
|
EXCLUSIVE
排他ロックをテーブルに追加します。テーブル (書き込みロック)、DML 操作は許可されません |
|
为了避免执行 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 语句的性能再选择如何操作。
- 复制表结构,创建一个新的表
- 在新创建的表中插入少量数据
- 在新表上面执行 DDL 操作
- 检查执行操作后返回的
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 最佳。
セカンダリ インデックス
操作 |
INSTANT |
INPLACE |
テーブルの再構築 |
同時実行DML |
メタデータのみを変更します |
セカンダリ インデックスを作成または追加します |
❌ |
✅ | ❌ |
✅ |
❌ |
インデックスを削除 |
❌ |
✅ |
❌ | ##✅ | ✅ |
インデックスの名前を変更 (⚠️MySQL 5.7、MariaDB 10.5.2) #❌ | ✅ |
❌ |
✅ |
✅ |
|
フルテキストインデックスを追加 ❌ |
✅ ① |
❌ ① |
❌ |
❌ |
|
SPATIAL を追加 インデックス (⚠️MySQL 5.7、MariaDB 10.2.2) ❌ |
✅ |
❌ |
❌ | ❌ |
| #インデックス タイプの変更
#✅ | ✅##❌ |
✅ | ✅ |
|
| 手順:
① フルテキスト インデックス フィールドを初めて追加するときはテーブルを再構築する必要がありますが、再構築する必要はありません。その後必要になります
操作
INSTANT
INPLACE |
テーブルの再構築 |
同時 DML | #メタデータのみの変更 |
|
#主キーの追加 |
❌
✅ ②
✅ ② | #✅ | ❌ |
#プライマリキーを削除 |
❌ |
❌ |
✅
❌ |
❌ |
| #主キーを削除し、新しいキーを追加します | ❌ | ✅ | ✅
✅ | ❌ |
| ##手順: |
クラスター化インデックスの再構築には常にテーブル データのコピーが必要です (InnoDB は「インデックス構成テーブル」です)。したがって、テーブルの作成時に主キーを定義することが最善です。主キーを指定せずにテーブルが作成された場合、InnoDB は最初の | NOT NULL | UNIQUE
インデックスを主キーとして選択するか、システム生成の KEY
② を使用します。クラスター化インデックスの場合は、INPLACE
モードを使用します。COPY- モードよりも効率的です。
undo ログ- および
redo ログ
は生成されず、セカンダリ インデックスは順序付けされているため、順番にロードできます。変更バッファを使用する必要はありません。
通常の列-
操作 INSTANTINPLACE
テーブルの再構築同時 DML
メタデータのみの変更
|
| # #列を追加 | ✅ ③##✅ |
❌ ③ | #✅ ③ | ❌
#列の削除
❌ ④ |
✅ |
✅ |
✅ |
❌ |
| 列名の変更
❌ | ✅ | ❌#✅ ⑤ |
✅ |
| #列の順序を変更します | ❌ ⑫
✅ | ✅ | ✅ | ❌ |
| デフォルト値を設定します | ✅
✅ | ❌ | ✅ | ✅ |
| データ型の変更 | ❌
❌ | ✅ | ❌ | ❌ |
| 拡張子VARCHAR | 長さ (⚠️MySQL 5.7、MariaDB 10.2.2)
❌ ⑬ | ✅ | ❌ ⑥ | ✅ | ✅ |
#列のデフォルト値を削除 | ##✅
✅##❌# #✅
| ✅ |
| 自己インクリメント値の変更 | ❌ | ✅ | ❌
✅ | ❌ ⑦ |
| 列を NULL に設定します | ❌ | ✅ | ✅ ⑧
✅ | ❌ |
| 列を NOT NULL | ❌ | ✅ ⑨ | ✅ ⑨
✅ | ❌ |
| #ENUM および | SET の定義を変更します。 列 |
✅ |
✅
❌ ⑩ |
✅ |
✅ |
|
説明:
③ 同時 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 アルゴリズムの使用をサポートしますが、いくつかの制限があります。詳細については、「データの変更」を参照してください。列のタイプ
-
列の生成
操作INSTANT | INPLACE | テーブルの再構築 | 同時 DML | メタデータのみの変更 |
|
#STORED 列の追加
❌ ❌ | ✅ |
❌ |
❌ |
|
並べ替えを変更する | STORED 列中
#❌ ❌ |
✅ |
❌ |
❌ |
|
削除 | STORED 列 ❌ ✅ |
✅ |
✅ |
❌ |
|
| 仮想 列を追加します
✅ ✅ |
❌ |
✅ |
✅ |
|
##VIRTUAL | 列の並べ替えを変更します
✅❌ | ✅ | ❌ | ❌ |
|
仮想 | 列を削除
✅✅ | ❌ | #✅ | ✅ |
| 外部キー |
# #操作
INSTANTINPLACE
##テーブルの再構築
同時実行DML |
メタデータのみの変更 |
| |
外部キー制約の追加 | ❌ |
✅ ⑭
❌
✅ |
✅ |
| ##外部キー制約の削除 | ❌ | ✅ | ❌
✅ | ✅# ##################################注:
- ⑭ 外部キーを追加する場合、
INPLACE アルゴリズム は、 foreign_key_checks オプションが有効な場合にのみサポートされます。 テーブル
操作 |
INSTANT |
INPLACE |
テーブルの再構築 |
同時 DML |
メタデータのみ変更 |
変更ROW_FORMAT
|
❌ |
✅ |
✅ |
✅ |
❌ |
KEY_BLOCK_SIZE |
❌# を変更します | ##✅ | ✅ | ✅ | ❌ |
永続テーブル統計の設定 | ❌ | ✅ | ❌ | ✅ | ✅ |
指定された文字セット | ❌ | ✅ | ✅ ⑮ | ❌ | ❌ |
文字セットの変換 | ❌ | ❌ | ✅ ⑯ | ❌ | ❌ |
##テーブルの最適化
❌ |
✅ ⑰ |
✅ |
✅ |
❌ |
|
FORCE オプションを使用してくださいテーブルを再構築 ❌ |
✅ ⑱ |
✅ |
✅ |
❌ |
| 空のリビルドを実行します
❌ | ✅ ⑲ | ✅ | ✅ | ❌ |
| テーブル名の変更
✅ | ✅ | ❌ | ✅ | ✅ |
|
注:
⑮⑯ 文字セットが異なる場合、テーブルを再構築する必要があります。
⑰⑱⑲ テーブルに - FULLTEXT
フィールドが含まれる場合、 INPLACE-
テーブルスペース
操作INSTANT | INPLACE##はサポートされません#テーブルの再構築 |
同時 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 チュートリアル
(ビデオ)
|