Innodb中那些DDL不支持Online操作-MySQL必会核心问题

当前位置:　首页>> 技术小册>> MySQL必会核心问题

### 章节标题：InnoDB中那些DDL不支持Online操作

在MySQL数据库中，InnoDB作为默认的存储引擎，以其事务支持、行级锁定和外键约束等功能深受开发者喜爱。InnoDB引擎的一个重要特性是能够支持在线DDL（Data Definition Language）操作，这意味着在执行结构更改（如表结构修改、索引添加或删除等）时，数据库能够继续处理查询和更新操作，从而最小化对业务连续性的影响。然而，并非所有的DDL操作都支持在线进行，某些操作在执行时会阻塞表或数据库的其他部分，导致性能下降或服务中断。本章节将深入探讨InnoDB中不支持在线操作的DDL类型及其背后的原因，同时提供一些应对策略。

#### 一、引言

在线DDL操作是MySQL 5.6及以后版本中InnoDB引擎的一大亮点，它通过引入`ALGORITHM=INPLACE`和`LOCK=NONE`（或`LOCK=SHARED`、`LOCK=EXCLUSIVE`）等选项，使得大部分DDL操作能够在不阻塞表访问的情况下执行。尽管如此，仍有部分DDL操作由于技术限制或设计考虑，无法在线执行。

#### 二、不支持在线操作的DDL类型

##### 1. **修改表的存储引擎**

当尝试将表的存储引擎从非InnoDB更改为InnoDB（或反之），特别是当表包含大量数据时，这个过程可能不是完全在线的。虽然从MySQL 5.6开始，InnoDB支持在线更改表的存储引擎，但在某些极端情况下（如表非常大、系统资源有限等），操作可能需要较长时间并可能暂时阻塞表访问。此外，如果目标存储引擎不支持表的某些特性（如外键），则操作可能需要更复杂的转换步骤，这些步骤可能不是完全在线的。

##### 2. **重建表**

在某些情况下，如执行`OPTIMIZE TABLE`命令或某些复杂的`ALTER TABLE`操作（如改变行格式、大量调整列类型等），MySQL可能会选择通过创建一个新表、复制数据、然后替换旧表的方式来“重建”表。这种操作通常不是在线的，因为它需要锁表并在整个过程中阻止对表的访问。

##### 3. **修改主键**

直接修改表的主键（PRIMARY KEY）通常是一个复杂的操作，因为它涉及到索引的重建和数据的重新排序。虽然从MySQL 5.6开始，InnoDB尝试通过在线DDL支持这一操作，但在某些情况下（如主键列的数据类型发生显著变化、表非常大等），操作可能需要较长时间且可能不是完全在线的。此外，如果主键是外键的引用目标，更改主键还可能影响到相关的外键约束，这进一步增加了操作的复杂性。

##### 4. **添加或删除外键约束**

虽然添加或删除外键约束通常可以通过在线DDL完成，但在某些特定情况下（如外键涉及大量数据、外键约束本身很复杂等），操作可能需要更长的时间，并可能在执行过程中暂时影响表的性能。此外，如果外键约束的添加或删除导致了数据完整性问题（如违反了外键约束的数据存在），则操作可能无法成功完成，需要额外的数据清理步骤。

##### 5. **更改表的字符集**

更改表的字符集（CHARACTER SET）可能涉及转换表中所有数据的字符编码，这是一个资源密集型的操作。虽然InnoDB支持在线更改表的字符集，但如果表中包含大量数据或字符集转换过程复杂（如从单字节字符集转换到多字节字符集），则操作可能需要较长时间，并可能在执行过程中影响表的性能。

#### 三、应对策略

面对上述不支持在线操作的DDL类型，可以采取以下策略来减轻对业务的影响：

1. **计划维护窗口**：在业务低峰期执行这些操作，以减少对业务的影响。

2. **使用pt-online-schema-change等工具**：Percona Toolkit中的`pt-online-schema-change`是一个强大的工具，它可以在不锁定原表的情况下执行复杂的`ALTER TABLE`操作。该工具通过创建一个与原表结构相同的新表，逐步复制数据，并在适当时机切换读写到新表来完成操作。

3. **分批处理**：对于大规模的数据更改（如更改表中大量行的数据类型），可以考虑将操作分批进行，每次处理一小部分数据，以减少对系统资源的占用和对业务的影响。

4. **监控和评估**：在执行这些操作之前，使用性能监控工具评估系统当前的状态和预计的操作影响。在操作过程中持续监控，以便在出现性能问题时及时调整策略。

5. **测试验证**：在生产环境之前，在测试环境中模拟这些操作，以验证其效果和潜在的影响。

#### 四、结论

虽然InnoDB引擎通过在线DDL操作极大地提高了数据库维护的灵活性和业务连续性，但仍有一部分DDL操作由于技术或设计上的限制，无法完全在线执行。了解这些不支持在线操作的DDL类型及其背后的原因，对于制定合适的数据库维护策略至关重要。通过采用合适的应对策略，可以最大限度地减少对业务的影响，确保数据库的稳定运行。

该分类下的相关小册推荐：

MySQL从入门到精通(一)

MySQL从入门到精通(五)

MySQL8.0入门与实践

MySQL从入门到精通(四)

MySQL 实战 45 讲

MySQL从入门到精通(二)

MySQL从入门到精通(三)

细说MySQL(零基础到高级应用)

SQL零基础到熟练应用(增删改查)