Referenzhandbuch: MySQL & MariaDB Online DDL

• ALGORITHMUS – Steuert, wie DDL-Operationen ausgeführt werden und welcher Algorithmus verwendet wird
• LOCK – Steuert den Grad der zulässigen Tabellensperren bei der Ausführung von DDL

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]复制代码

ALGORITHMUS unterstützte Algorithmen

NOCOPY-Algorithmusunterstützung: NOCOPY 算法支持：MariaDB 10.3.2+，MySQL 不支持该算法。

INSTANT 算法支持：MariaDB 10.3.2+，MySQL 8.0.12+。

算法使用规则：

• 如果用户指定的算法为 COPY，则 InnoDB 使用 COPY 算法。
• 如果用户指定的是 COPY 之外的其它算法，则 InnoDB 会按照算法效率，选择最高效的算法，最差的情况下采用用户指定的算法。比如用户指定了 ALOGRITHM = NOCOPYMariaDB 10.3.2
+,

.

MariaDB 10.3.2+, MySQL 8.0.12+.

• Wenn der vom Benutzer angegebene Algorithmus COPY ist, verwendet InnoDB den COPY-Algorithmus.
• Wenn der Benutzer einen anderen Algorithmus als COPY angibt, wählt InnoDB den effizientesten Algorithmus basierend auf der Algorithmuseffizienz aus. Im schlimmsten Fall wird der vom Benutzer angegebene Algorithmus verwendet. Wenn der Benutzer beispielsweise ALOGRITHM = NOCOPY angibt, wählt InnoDB den effizientesten unterstützten Algorithmus aus (NOCOPY, INSTANT).



Der MySQL-Dienst besteht hauptsächlich aus der Serverschicht und der

Storage-Engine-Schicht

. Die Serverschicht enthält die meisten Kernfunktionen von MySQL, alle integrierten Funktionen und speichermotorübergreifende Funktionen wie gespeicherte Prozeduren und Auslöser, Ansicht usw. Die Speicher-Engine-Schicht ist für die Datenspeicherung und das Lesen verantwortlich und verwendet ein Plug-in-Architekturmodell. Der

COPY-Algorithmus

wirkt auf der Serverebene und sein Ausführungsprozess findet auf der Serverebene statt, daher unterstützen alle Speicher-Engines die Verwendung dieses Algorithmus. Der Ausführungsprozess ist wie unten gezeigt:
INPLACE-Algorithmus Auf der Ebene der Speicher-Engine handelt es sich um den einzigartigen DDL-Algorithmus der InnoDB-Speicher-Engine. Der Ausführungsprozess ist in der folgenden Abbildung dargestellt. Bei Bedarf können Sie die LOCK-Klausel übergeben. Steuert den Grad der zulässigen Tabellensperre bei der Ausführung von DDL. Wenn die für den angegebenen Vorgang erforderliche Einschränkungsstufe nicht erfüllt ist (), schlägt die Ausführung der Anweisung fehl und es wird ein Fehler gemeldet. Strategie

EXCLUSIVE > SHARED > NONE
Beschreibung
STANDARD	Verwenden Sie die Sperrstrategie mit der kleinsten Granularität, die von der aktuellen Operation unterstützt wird alle DML-Operationen
SHARED	Fügen Sie der Tabelle eine gemeinsame Sperre (Lesesperre) hinzu. Es sind nur schreibgeschützte DML-Vorgänge zulässig.
EXKLUSIV	Fügen Sie der Tabelle eine exklusive Sperre (Schreibsperre) hinzu erlaubt

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

Online DDL 执行过程

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



• 阶段 1：初始化

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

• 阶段 2：执行

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

• 阶段 3：提交表定义

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

元数据锁（Referenzhandbuch: MySQL & MariaDB Online DDL，Metadata Lock）主要用于 DDL 和 DML 操作之间的并发访问控制，保护表结构（表定义）的一致，保证读写的正确性。Referenzhandbuch: MySQL & MariaDB Online 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]复制代码

Nach dem Login kopieren

Nach dem Login kopieren

评估 Online DDL 操作的性能

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

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

比如

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

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

  Nach dem Login kopieren

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

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

  Nach dem Login kopieren

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

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

  Nach dem Login kopieren

由于在执行  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 最佳。



Sekundärer Index
❌✅✅❌❌❌ ①❌

❌	✅	❌		Index löschen	❌
✅	✅		Index umbenennen (⚠️MySQL 5.7+, MariaDB 10.5.2+)	❌	✅
✅	✅		Index FULLTEXT hinzufügen	❌	✅ ①
❌	❌		Fügen Sie SPATIAL hinzu Code> Index (⚠️MySQL 5.7+, MariaDB 10.2.2+)	❌	✅
❌FULLTEXT 索引	❌	✅ ①	❌ ①	❌	❌
添加 SPATIAL 索引（⚠️MySQL 5.7+，MariaDB 10.2.2+）	❌	✅	❌	❌	❌
修改索引类型	✅	✅	❌	✅	✅

说明：

• ① 第一次添加全文索引字段时需要重建表，之后就不需要了

主键

操作	INSTANT	INPLACE	重建表	并发 DML	只修改元数据
添加主键	❌	✅ ②	✅ ②	✅	❌
删除主键	❌	❌	✅	❌	❌
删除一个主键同时添加一个新的	❌	✅	✅	✅	❌

说明：

• 重建聚簇索引总是需要拷贝表数据（InnoDB 是“索引组织表”），所以最好是在创建表的时候就定义好主键
• 如果创建表是没有指定主键，InnoDB 会选择第一个 NOT NULL 的 UNIQUE 索引作为主键，或者使用系统生成的 KEY
• ② 对聚簇索引来说，使用 INPLACE 模式比 COPY 模式要高效一些：不会产生 undo log 和 redo log，二级索引是有序的，所以可以按顺序加载，不需要使用变更缓冲区

普通列

❌Indextyp ändern✅ ✅❌ ✅

操作	INSTANT	INPLACE	重建表	并发 DML	只修改元数据
列添加	✅ ③	✅	❌ ③	✅ ③	❌
列删除	❌ ④	✅	✅	✅	❌
列重命名	❌	✅	❌	✅ ⑤	✅
改变列的顺序	❌ ⑫	✅	✅	✅	❌
设置默认值	✅	✅	❌	✅	✅
修改数据类型	❌	❌	✅	❌	❌
扩展 VARCHAR 长度（⚠️MySQL 5.7+, MariaDB 10.2.2+）	❌ ⑬	✅	❌ ⑥	✅	✅
删除列的默认值	✅	✅	❌	✅	✅
改变自增值	❌	✅	❌	✅	❌ ⑦
设置列为 NULL	❌	✅	✅ ⑧	✅	❌
设置列为 NOT NULL	❌	✅ ⑨	✅ ⑨	✅	❌
修改 ENUM 和 SET

🎜✅🎜🎜🎜🎜

Anleitung:

• ① Sie müssen die Tabelle neu erstellen, wenn Sie zum ersten Mal ein Volltextindexfeld hinzufügen, danach jedoch nicht mehr

Primärschlüssel🎜🎜🎜🎜🎜Vorgang🎜🎜INSTANT🎜🎜INPLACE🎜🎜Tabelle neu erstellen🎜🎜Gleichzeitiges DML🎜🎜Nur Metadaten ändern🎜🎜🎜🎜🎜 🎜Hinzufügen Primärschlüssel🎜 🎜 ❌🎜🎜✅ ②🎜🎜✅ ②🎜🎜✅🎜🎜❌🎜🎜🎜🎜Primärschlüssel löschen🎜🎜❌🎜🎜❌🎜🎜✅ 🎜🎜❌🎜🎜 ❌🎜🎜🎜🎜Löschen Sie einen Primärschlüssel und einen neuen hinzufügen🎜🎜 ❌🎜🎜✅🎜🎜✅🎜🎜✅🎜🎜❌🎜🎜🎜🎜

Hinweis:

• Das Neuerstellen eines Clustered-Index erfordert immer das Kopieren von Tabellendaten (InnoDB ist). eine „indexorganisierte Tabelle“), daher ist es am besten, den Primärschlüssel beim Erstellen der Tabelle zu definieren
• Wenn die Tabelle ohne Angabe eines Primärschlüssels erstellt wird, wählt InnoDB den ersten NOT NULL aus UNIQUE Index als Primärschlüssel oder verwenden Sie den vom System generierten SCHLÜSSEL
• ② Für Clustered-Indizes ist es günstiger, den INPLACEzu verwenden > Modus als der COPY Modus. Effizienter: Undo Log und Redo Log werden nicht generiert. Der Sekundärindex ist geordnet in der richtigen Reihenfolge geladen werden und es ist nicht erforderlich, einen Änderungspuffer zu verwenden

Normale Spalte 🎜🎜🎜🎜🎜 Operation 🎜🎜INSTANT🎜🎜INPLACE🎜 🎜Tabelle neu erstellen🎜🎜Concurrent DML🎜🎜Nur Metadaten ändern🎜🎜🎜🎜🎜🎜 Spalte hinzugefügt 🎜🎜✅ ③🎜🎜✅🎜🎜❌ ③🎜🎜✅ ③🎜 🎜❌🎜🎜🎜🎜 Spalte gelöscht 🎜🎜❌ ④🎜🎜 ✅🎜🎜✅🎜🎜 ✅🎜🎜❌🎜🎜🎜🎜Spaltenumbenennung🎜 🎜❌🎜🎜✅🎜🎜❌🎜🎜✅ ⑤🎜🎜✅ 🎜🎜🎜🎜Ändern Sie die Reihenfolge der Spalten🎜🎜❌ ⑫🎜🎜✅🎜🎜 ✅🎜🎜✅🎜🎜❌ 🎜🎜🎜🎜Standardwert festlegen🎜🎜✅🎜 🎜✅🎜🎜❌🎜🎜✅🎜🎜✅🎜🎜🎜🎜Daten ändern Typ🎜🎜❌🎜🎜❌🎜🎜✅🎜🎜❌🎜🎜 ❌🎜🎜🎜 🎜Erweiterte VARCHAR-Länge (⚠️MySQL 5.7+, MariaDB 10.2.2+)🎜🎜❌ ⑬🎜🎜✅🎜🎜❌ ⑥🎜🎜✅🎜🎜✅ 🎜🎜🎜🎜Entfernen Sie die Standardwert der Spalte🎜🎜✅🎜🎜✅🎜🎜✅ ❌🎜🎜✅🎜🎜✅ 🎜🎜🎜🎜Ändern Sie den Wert für die automatische Erhöhung 🎜🎜✅🎜🎜❌ ⑦🎜🎜🎜 🎜Setzen Sie die Spalte auf NULL🎜🎜❌🎜🎜✅🎜 🎜✅ ⑧🎜🎜✅🎜🎜❌🎜🎜🎜 🎜Setzen Sie die Spalten auf NOT NULL🎜🎜❌🎜🎜✅ ⑨ 🎜🎜✅ ⑨🎜🎜✅🎜🎜❌🎜🎜 🎜🎜Ändern Sie die Definitionen der Spalten ENUM und SET🎜🎜 ✅🎜🎜✅🎜🎜❌ ⑩🎜🎜✅🎜🎜✅🎜🎜🎜🎜

Hinweis:

• ③ Gleichzeitiges DML: Beim Einfügen einer Auto-Inkrement-Spalte werden gleichzeitige DML-Vorgänge nicht unterstützt. Beim Hinzufügen einer Auto-Inkrement-Spalte wird eine große Datenmenge neu organisiert, was kostspielig ist ③ Erstellen Sie die Tabelle neu: Beim Hinzufügen von Spalten müssen MySQL 5.7 und frühere Versionen die Tabelle neu erstellen, wenn ALGORITHM=INPLACE, und nicht neu erstellen, wenn ALGORITHM =INSTANT

• ③ INSTANT-Algorithmus: Beim Hinzufügen von Spalten gelten für die Verwendung des INSTANT-Algorithmus die folgenden EinschränkungenALGORITHM=INPLACE 时，需要重建表，ALGORITHM=INSTANT 时不需要重建

• ③ INSTANT算法：添加列时，使用 INSTANT 算法有下面这些限制

  • 添加列操作不能和其它不支持 INSTANT 算法的操作合并为一条 ALTER TABLE 语句
  • 新增的列只能添加到表的最后，不能放到其它列的前面，在 MariaDB 10.4 之后，支持在任意位置添加
  • 不能将列添加到 ROW_FORMAT=COMPRESSED 的表中
  • 不能将列添加到包含 FULLTEXT 的表中
  • 不能将列添加到临时表中，临时表只支持 ALGORITHM=COPY
  • 不能将列添加到驻留在数据字典表空间中的表中
  • 在添加列的时候不会计算行的大小限制，该限制在执行 DML 操作插入或者更新表时才会被检查

• ④ 删除列时，大量的数据需要被重新组织，代价高昂，在 MariaDB 10.4 之后，删除列支持 INSTANT 算法

• ⑤ 重命名列时，确保只改变列名，不改变数据类型，这样才能支持并发的 DML 操作

• ⑥ 扩展 VARCHAR 长度时，INPLACE 是有条件的，必须保证用于标识字符串长度的长度字节不变（这里说的都是字节，不是 VARCHAR 的字符长度，字节占用与采用的字符集有关，utf8 字符集下，一个字符占 3 个字节， utf8mb4 则 4 个字节）

  • 当 VARCHAR 列长度在 0-255 个字节时，长度标识占用一个字节
  • 当 VARCHAR 列长度大于 255 个字节时，长度标识占用两个字节

  因此，INPLACE 只支持 0-255 个字节之间或者 256 个字节到更大的长度之间的变更。VARCHAR 列长度减小是不支持 INPLACE 的。

• ⑦ 自增列值变更是修改的内存中的值，不是数据文件

• ⑧ ⑨ 设置列为 [NOT] NULL 时，大量的数据被重新组织，代价高昂

• ⑩ 修改 ENUM 和 SET 类型的列定义时，是否需要表拷贝取决于已有元素的个数和插入成员的位置

• ⑫ 在 MariaDB 10.4 之后，列排序支持 INSTANT 算法

• ⑬ 在 MariaDB 10.4.3  之后，InnoDB 支持使用 INSTANT 算法增加列的长度，但是也有一些限制，具体参考 Changing the Data Type of a Column

生成列

Es können keine Spalten zu Tabellen hinzugefügt werden, die sich in Datenwörterbuch-Tabellenbereichen befinden.

操作	INSTANT	INPLACE	重建表	并发 DML	只修改元数据
添加 STORED 列	❌	❌	✅	❌	❌
修改 STORED 列的排序	❌	❌	✅	❌	❌
删除 STORED 列	❌	✅	✅	✅	❌
添加 VIRTUAL 列	✅	✅	❌	✅	✅
修改 VIRTUAL 列的排序	✅	❌	✅	❌	❌
删除 VIRTUAL	Der Vorgang zum Hinzufügen von Spalten kann nicht mit anderen Vorgängen kombiniert werden, die dies nicht unterstützen den INSTANT-Algorithmus in einer ALTER TABLE-Anweisung	Neue Spalten können nur am Ende der Tabelle hinzugefügt und nicht vor anderen Spalten platziert werden. Das Hinzufügen an jeder Position wird unterstützt.	Spalten können nicht zu ROW_FORMAT =COMPRESSED hinzugefügt werden.	Es können keine Spalten zu Tabellen hinzugefügt werden, die FULLTEXT enthalten.	Es können keine Spalten zu temporären Tabellen hinzugefügt werden , temporäre Tabellen unterstützen nur ALGORITHM=COPY

Die Zeilengrößenbeschränkung wird beim Hinzufügen von Spalten nicht berechnet. Diese Beschränkung wird beim Ausführen von DML-Vorgängen entfernt um die Tabelle einzufügen oder zu aktualisieren.

Hinweis:

• ⑭ Beim Hinzufügen von Fremdschlüsseln wird der INPLACE-Algorithmus nur unterstützt, wenn die Option foreign_key_checks deaktiviert istforeign_key_checks 选项被禁用的时候才支持 INPLACE 算法

表

说明：

• ⑮⑯ 当字符集不同时，需要重建表
• ⑰⑱⑲ 如果表中包含 FULLTEXT 的字段，则不支持 INPLACE

表空间

限制

• 在临时表 TEMPORARY TABLE 上创建索引时会发生表拷贝
• 如果表上有 ON...CASCADE 或者 ON...SET NULL 约束，则 ALERT TABLE 不支持字句 LOCK=NONE

Tabelle

❌ ✅ ⑮ ❌ ✅

Vorgang	INSTANT	INPLACE	Tabelle neu erstellen	Gleichzeitiges DML	Nur Metadaten ändern
ROW_FORMAT	❌	✅	✅	✅	❌
ModifyKEY_BLOCK_SIZE	❌	✅	✅	✅
Persistente Tabellenstatistiken festlegen	❌	✅	❌	✅	✅
Zeichensatz angeben	❌	✅	❌	❌
Zeichensatz konvertieren	❌	✅ ⑯	❌	❌
Tabelle optimieren	❌	✅ ⑰	✅	✅	❌
Verwenden Sie FORCE-Option zum Neuaufbau der Tabelle	❌	✅ ⑱	✅	✅	❌
Führen Sie eine leere Neuerstellung durch	❌	✅ ⑲	✅	❌
Tabelle umbenennen	✅	✅	❌	✅	✅

• Hinweis:
• ⑮⑯ Wenn die Zeichensätze unterschiedlich sind, muss dies in der Tabelle der Fall sein neu erstellt
  • ⑰⑱⑲ Wenn die Tabelle Felder von FULLTEXT enthält, wird INPLACE nicht unterstützt

  • Tabellenbereich

    Nur Metadaten ändern❌

    Vorgang	INSTANT	INPLACE	Tabelle neu erstellen	Gleichzeitiges DML
    Regulären Tablespace umbenennen	❌	✅	✅	✅
    Aktivieren oder deaktivieren Sie die reguläre Tablespace-Verschlüsselung	❌	✅	❌	✅	❌
    Dateiaktivieren oder deaktivieren -per-table Tabellenbereichsverschlüsselung	❌	❌	✅	❌	❌

    Einschränkungen

  • In temporärer Tabelle TEMPORARY TABLE Tabellenkopie tritt auf, wenn ein Index erstellt wird für
Wenn es eine ON...CASCADE- oder ON...SET NULL-Einschränkung für die Tabelle gibt, dann ALERT TABLE Der Ausdruck <code>LOCK=NONE wird nicht unterstützt, bevor der Online-DDL-Vorgang abgeschlossen wird, bis die Transaktion, die bereits die Metadatensperre für die zugehörige Tabelle hält, festgeschrieben oder zurückgesetzt wird Bei diesem Vorgang wird die zugehörige Tabelle blockiert. Neue Transaktionen werden blockiert und können nicht ausgeführt werden

Bei der Ausführung von DDL mit Tabellenrekonstruktion für eine große Tabelle gelten die folgenden Einschränkungen:

Es gibt keinen Mechanismus, um Online-DDL-Vorgänge anzuhalten oder die I/O-Operationen zu begrenzen. O oder CPU von Online-DDL-Vorgängen. Auslastung

Das Zurücksetzen eines Online-DDL-Vorgangs ist sehr teuer, wenn der Vorgang fehlschlägt.

Lange laufende Online-DDL kann zu Replikationsverzögerungen führen. Online-DDL-Vorgänge müssen auf dem Master abgeschlossen sein, bevor sie auf dem Slave ausgeführt werden können. Bei diesem Prozess müssen gleichzeitig verarbeitete DML warten, bis die DDL-Vorgänge auf dem Slave abgeschlossen sind, bevor sie ausgeführt werden. Am Ende geschriebenDieser Artikel wird weiterhin überarbeitet und aktualisiert. Bitte folgen Sie mir für weitere spannende Inhalte.