Guide de référence : DDL en ligne MySQL et MariaDB

Tutoriel MySQLLa colonne présente et guide MySQL & MariaDB Online DDL.

Vue d'ensemble

Dans les versions antérieures de MySQL, les opérations DDL (telles que la création d'index, etc.) nécessitaient généralement le verrouillage de la table de données DML. les opérations seront bloquées pendant le processus, affectant les activités normales. MySQL 5.6 et MariaDB 10.0 commencent à prendre en charge le DDL en ligne, qui peut effectuer des opérations DDL sans affecter l'exécution normale du DML. L'exécution directe d'opérations DDL en ligne est fondamentalement invisible pour les utilisateurs (certaines opérations ont un impact sur les performances).

Différentes versions de bases de données présentent certaines différences dans leur prise en charge de diverses instructions DDL. Cet article résume la prise en charge de MySQL et MariaDB pour le DDL en ligne. Lorsque vous devez effectuer des opérations DDL, vous pouvez vous référer à cet article. Section Support DDL en ligne.

Cet article continuera d'être révisé et mis à jour. Pour le contenu le plus récent, veuillez vous référer à mon projet de plan de croissance des programmeurs sur GITHUB. Les étoiles sont les bienvenues. Pour un contenu plus passionnant, veuillez me suivre.

Dans l'instruction ALTER TABLE, le DDL en ligne est pris en charge via les instructions ALGORITHM et LOCK :

• ALGORITHM - Contrôlez la manière dont les opérations DDL sont effectuées et quel algorithme est used
• LOCK - Contrôle le niveau de verrouillage de table autorisé lors de l'exécution de DDL

ALTER TABLE tab ADD COLUMN c varchar(50), ALGORITHM=INPLACE, LOCK=NONE;复制代码

ALGORITHME algorithmes pris en charge

ALGORITHM	说明
DEFAULT	默认算法，自动使用可用的最高效的算法
COPY	最原始的方式，所有的存储引擎都支持，不使用 Online DDL，操作时会创建临时表，执行全表拷贝和重建，过程中会写入 Redo Log 和大量的 Undo Log，需要添加读锁，非常低效
INPLACE	尽可能避免表拷贝和重建，更确切的名字应该是 ENGINE 算法，由存储引擎决定如何实现，有些操作是可以立即生效的（比如重命名列，改变列的默认值等），但有些操作依然需要全表或者部分表的拷贝和重建（比如添加删除列、添加主键、改变列为 NULL 等）
NOCOPY	该算法是 INPLACE 算法的子集，用于避免聚簇索引（主键索引）的重建造成全表重建，也就说用该算法会禁止任何引起聚簇索引重建的操作
INSTANT	用于避免 INPLACE 算法在需要修改数据文件时异常低效的问题，所有涉及到表拷贝和重建的操作都会被禁止

NOCOPY Prise en charge de l'algorithme : MariaDB 10.3.2+, MySQL ne prend pas en charge cet algorithme .

INSTANT Prise en charge de l'algorithme : MariaDB 10.3.2+, MySQL 8.0.12+.

Règles d'utilisation de l'algorithme :

• Si l'algorithme spécifié par l'utilisateur est COPY, InnoDB utilise l'algorithme COPY.
• Si l'utilisateur spécifie un algorithme autre que COPY, InnoDB sélectionnera l'algorithme le plus efficace en fonction de l'efficacité de l'algorithme. Dans le pire des cas, l'algorithme spécifié par l'utilisateur sera utilisé. Par exemple, si l'utilisateur spécifie ALOGRITHM = NOCOPY, InnoDB sélectionnera l'algorithme le plus efficace pris en charge parmi (NOCOPY, INSTANT).

Le service MySQL est principalement composé de la Couche serveur et de la Couche moteur de stockage La couche Serveur contient la base de données MySQL. . Certaines fonctions principales, toutes les fonctions intégrées, les fonctions du moteur de stockage croisé telles que les procédures stockées, les déclencheurs, les vues, etc. La couche moteur de stockage est responsable du stockage et de la lecture des données et adopte un modèle d'architecture de plug-in.

L'algorithme COPY agit sur la couche Serveur et son processus d'exécution se situe au niveau de la couche Serveur, donc tous les moteurs de stockage prennent en charge l'utilisation de cet algorithme. Le processus d'exécution est comme indiqué ci-dessous

Algorithme INPLACE agit sur la couche du moteur de stockage et est un algorithme DDL unique au moteur de stockage InnoDB. Le processus d'exécution est illustré dans la figure ci-dessous

Politique LOCK

Par défaut, MySQL/MariaDB utilisera le moins de verrous possible lors de l'exécution de DDL. Si nécessaire, vous pouvez utiliser la clause LOCK pour. contrôler le niveau de verrouillage de table autorisé lors de l'exécution de DDL. Si le niveau de restriction requis par l'opération spécifiée n'est pas satisfait (

EXCLUSIVE > SHARED > NONE

), l'exécution de l'instruction échoue et une erreur est signalée.

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

Online DDL 执行过程

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

• 阶段 1：初始化

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

• 阶段 2：执行

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

• 阶段 3：提交表定义

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

元数据锁（Guide de référence : DDL en ligne MySQL et MariaDB，Metadata Lock）主要用于 DDL 和 DML 操作之间的并发访问控制，保护表结构（表定义）的一致，保证读写的正确性。Guide de référence : DDL en ligne MySQL et MariaDB 不需要显式的使用，在访问表时会自动加上。

由于上面三个阶段中对元数据锁的独占，  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 最佳。

Index secondaire

操作	INSTANT	INPLACE	重建表	并发 DML	只修改元数据
创建或者添加二级索引	❌	✅	❌	✅	❌
删除索引	❌	✅	❌	✅	✅
重命名索引 （⚠️MySQL 5.7+，MariaDB 10.5.2+）	❌	✅	❌	✅	✅
添加 FULLTEXT 索引	❌	✅ ①	❌ ①	❌	❌
添加 SPATIAL 索引（⚠️MySQL 5.7+，MariaDB 10.2.2+）	❌	✅	❌	❌	❌
修改索引类型	✅	✅	❌	✅	✅

Remarque :

• ① Vous devez reconstruire la table lors de l'ajout d'un champ d'index de texte intégral pour la première fois, mais ce n'est plus le cas nécessaire après cela

Clé primaire

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

Remarque :

• La reconstruction d'un index clusterisé nécessite toujours de copier les données de la table (InnoDB est un "index- table organisée"), il est donc préférable de la créer avant de définir la clé primaire
• lors de la création de la table. Si la clé primaire n'est pas spécifiée lors de la création de la table, InnoDB sélectionnera la première NOT NULL UNIQUE index comme clé primaire, ou utilisez la CLÉ générée par le système
• ② Pour les index clusterisés, l'utilisation du mode INPLACE est plus efficace que le mode COPY : annuler le journal et redo log ne sera pas généré et l'index secondaire est ordonné, il peut donc être chargé en séquence sans utiliser le tampon de changement

colonnes ordinaires

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

Remarque :

• ③ DML simultané : lors de l'insertion d'une colonne à incrémentation automatique, les opérations DML simultanées ne sont pas prises en charge. Lors de l'ajout d'une colonne à incrémentation automatique, une grande quantité de données sera prise en charge. être réorganisé. Coûteux

• ③ Reconstruire la table : lors de l'ajout d'une colonne, MySQL 5.7 et les versions antérieures doivent reconstruire la table lorsque ALGORITHM=INPLACE et le fait. pas besoin de reconstruire lorsque ALGORITHM=INSTANT

• ③ Algorithme INSTANT : Lors de l'ajout d'une colonne, l'algorithme INSTANT a les limitations suivantes :

  • Ajouter une colonne L'opération ne peut pas être utilisée et les autres algorithmes ne prennent pas en charge INSTANT Les opérations sont combinées en une seule ALTER TABLE instruction
  • Les nouvelles colonnes ne peuvent être ajoutées qu'à la fin du tableau et ne peuvent pas être placées devant les autres. colonnes. Après MariaDB 10.4, il est pris en charge d'ajouter
  Impossible d'ajouter des colonnes à la table contenant
  • ROW_FORMAT=COMPRESSED
  Impossible d'ajouter des colonnes à la table contenant
  • FULLTEXT.
  Impossible d'ajouter des colonnes à la table temporaire, les tables temporaires sont uniquement prises en charge
  • ALGORITHM=COPY
  Impossible d'ajouter des colonnes à une table qui réside dans un espace table de dictionnaire de données
  • La limite de taille de ligne n'est pas calculée lors de l'ajout d'une colonne, qui n'est pas calculée lors de l'exécution d'une opération DML insérer Ou elle ne sera vérifiée que lorsque la table sera mise à jour
• ④ Lors de la suppression d'une colonne, une grande quantité des données doivent être réorganisées, ce qui est coûteux. Après MariaDB 10.4, la suppression de colonnes prend en charge l'algorithme INSTANT

• ⑤ Lorsque vous renommez une colonne, assurez-vous de modifier uniquement le nom de la colonne et non le nom de la colonne. type de données, afin que les opérations DML simultanées puissent être prises en charge

• ⑥ Longueur VARCHAR étendue, INPLACE est conditionnelle et il faut s'assurer que la longueur en octets utilisée pour identifier la longueur de la chaîne reste inchangé (voici tous les octets, pas la longueur des caractères de VARCHAR, et l'occupation des octets est liée au jeu de caractères utilisé,

  Sous le jeu de caractères, un caractère occupe 3 octets, utf8 puis 4 octets) utf8mb4

  Lorsque la longueur de la colonne VARCHAR est comprise entre 0 et 255 octets, l'identifiant de longueur occupe un octet
  • Lorsque la longueur de la colonne VARCHAR est supérieure à 255 octets, l'identifiant de longueur occupe deux octets
  Par conséquent, INPLACE ne prend en charge que 0 à 255 octets ou 256 mots. Changements entre les sections vers des longueurs plus grandes. La réduction de la longueur des colonnes VARCHAR n'est pas prise en charge avec INPLACE.

• ⑦ Le changement de la valeur de la colonne d'auto-incrémentation est la valeur modifiée dans la mémoire, pas le fichier de données

• ⑧ ⑨ Lorsque le est définie sur

  , une grande quantité de données est réorganisée, ce qui est coûteux [NOT] NULL

• ⑩ Lors de la modification des définitions de colonnes des types

  et ENUM, qu'il s'agisse d'un une copie de la table est requise en fonction du nombre d'éléments existants et de la position des membres insérés SET

• ⑫ Après MariaDB 10.4, le tri des colonnes prend en charge l'algorithme INSTANT

• ⑬ Après MariaDB 10.4.3, InnoDB prend en charge l'utilisation de l'algorithme INSTANT augmente la longueur de la colonne, mais il présente également certaines limitations. Pour plus de détails, reportez-vous à Modification du type de données d'une colonne

.

Génération de colonne

操作	INSTANT	INPLACE	重建表	并发 DML	只修改元数据
添加 STORED 列	❌	❌	✅	❌	❌
修改 STORED 列的排序	❌	❌	✅	❌	❌
删除 STORED 列	❌	✅	✅	✅	❌
添加 VIRTUAL 列	✅	✅	❌	✅	✅
修改 VIRTUAL 列的排序	✅	❌	✅	❌	❌
删除 VIRTUAL 列	✅	✅	❌	✅	✅

Clé étrangère

Remarque :

• ⑭ Lors de l'ajout de clés étrangères, l'algorithme foreign_key_checks n'est pris en charge que lorsque l'option INPLACE est désactivée

Table

.

操作	INSTANT	INPLACE	重建表	并发 DML	只修改元数据
修改 ROW_FORMAT	❌	✅	✅	✅	❌
修改 KEY_BLOCK_SIZE	❌	✅	✅	✅	❌
设置持久表统计信息	❌	✅	❌	✅	✅
指定字符集	❌	✅	✅ ⑮	❌	❌
转换字符集	❌	❌	✅ ⑯	❌	❌
优化表	❌	✅ ⑰	✅	✅	❌
使用 FORCE 选项重建表	❌	✅ ⑱	✅	✅	❌
执行空的重建	❌	✅ ⑲	✅	✅	❌
重命名表	✅	✅	❌	✅	✅

Remarque :

• ⑮⑯ Lorsque les jeux de caractères sont différents, la table doit être reconstruite
• ⑰⑱⑲ Si la table contient des FULLTEXT champs, INPLACE
n'est pas pris en charge.

Tablespace

操作	INSTANT	INPLACE	重建表	并发 DML	只修改元数据
重命名常规表空间	❌	✅	❌	✅	✅
启用或者禁用常规表空间加密	❌	✅	❌	✅	❌
启用或者禁用 file-per-table 表空间加密	❌	❌	✅	❌	❌

Limitations

• Une copie de table se produit lors de la création d'un index sur une table temporaire TEMPORARY TABLE
• Si la table S'il y a des contraintes ON...CASCADE ou ON...SET NULL, alors ALERT TABLE ne prend pas en charge les mots LOCK=NONE
• Avant que l'opération Onlne DDL ne soit terminée, elle doit attendre la transaction qui détient déjà le verrou des métadonnées sur la table associée à valider ou à restaurer, pendant ce processus, les nouvelles transactions des tables associées seront bloquées et ne pourront pas être exécutées
• Lors de l'exécution de DDL impliquant une reconstruction de table sur une grande table, les restrictions suivantes existera
  • Il n'existe aucun mécanisme permettant de suspendre les opérations DDL en ligne ou de limiter l'utilisation des E/S ou du processeur des opérations DDL en ligne
  • L'annulation d'une opération DDL en ligne est très coûteuse si l'opération échoue
  • DDL en ligne de longue durée Peut entraîner des retards de réplication. Les opérations DDL en ligne doivent être terminées sur le maître avant de pouvoir être exécutées sur l'esclave. Dans ce processus, les opérations DML traitées simultanément doivent attendre que les opérations DDL soient terminées sur l'esclave avant d'être exécutées.

Écrit à la fin

Cet article sera continuellement révisé et mis à jour, veuillez me suivre pour un contenu plus passionnant.

Plus de recommandations d'apprentissage gratuites connexes : tutoriel MySQL(vidéo)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!