开发易忽视的问题：MySQL Alter操作系统性能问题

三年之约
776 阅读8分钟

在InnoDB引擎下，ALTER TABLE操作的处理机制随着MySQL版本的升级而不断优化。以下是一些关键版本中的变化和优化：

  1. MySQL 5.5及之前：

    • 大多数ALTER TABLE操作（修改字段长度、字段重命名、增删字段）都需要创建一个临时表。
    • 数据会从原表复制到新表。这一过程可能非常耗时，尤其是在大表上，需要锁表操作。

  2. MySQL 5.6：

    • 引入了“在线DDL”功能，使某些ALTER操作可以在不完全锁定表的情况下进行复制操作。
    • 支持在添加或删除索引时进行非阻塞操作，即写操作可能被允许而不会锁定整个表。
    • 对于某些操作，可以减少表锁定时间，允许对表的读取操作继续进行。

  3. MySQL 5.7：

    • 进一步增强了在线DDL支持，包括更多类型的操作可以以更少的锁定时间执行。
    • 改善了对并行线程的管理和资源使用，从而提高了复杂DDL操作的性能。

  4. MySQL 8.0：

    • 支持Instant 模式操作，通过修改元数据信息，不需要重建表或移动数据。
    • 以下是支持 Instant 模式的一些操作：

        1. 增加列（ADD COLUMN） ：

        • 在表的末尾添加新的列。
        • 新增列必须允许 NULL 或有默认值，不涉及复杂的数据类型计算。

        1. 重命名列（RENAME COLUMN） ：

        • 改变现有列的名称，而不改变其数据类型或位置。

        1. 修改列默认值（MODIFY DEFAULT VALUE） ：

        • 改变现有列的默认值属性。

MySQL 5.5执行流程

在MySQL 5.5中，Alter操作更改VARCHAR列的长度（例如，从128改为256）通常会涉及到表的重建。以下是该过程的具体实现步骤：

  1. 获取表锁：

    • 在执行ALTER TABLE操作时，MySQL会对整个表加上一个写锁。这意味着其他事务在此期间无法对该表进行读或写操作。

  2. 创建临时表：

    • 数据库会创建一个新的临时表，其结构与原始表相同，但将指定的VARCHAR列修改为新的长度。

  3. 复制数据：

    • 将数据从原始表逐行复制到临时表。在这个过程中，VARCHAR列的数据会被适配到新的长度。

  4. 应用变更：

    • 一旦数据复制完成，MySQL会删除旧表，并将临时表重命名为原表的名字。

  5. 更新索引和约束：

    • 重建表时，所有相关的索引和约束也需要被重新创建在新的表结构上。

  6. 释放表锁：

    • 完成所有操作后，释放表上的锁，使得其他事务可以再次访问该表。

在MySQL 5.5中，由于需要对表进行完整的重建，这个过程可能会导致较长时间的表锁定，特别是在大型表上进行操作时。这种方式会对数据库的可用性造成一定影响，所以在线DDL特性在较新版本中被引入以改善这一问题。

在线DDL执行流程

在MySQL 5.6中，引入了在线DDL特性，使得一些ALTER TABLE操作可以在不完全锁定表的情况下进行。对于添加列（ADD COLUMN）操作，在线DDL的流程大致如下：

  1. 元数据锁定：

    • 操作开始时，会获取一个短暂的元数据锁，以防止其他并发DDL修改同一张表。

  2. 准备阶段：

    • MySQL会检查ALTER TABLE请求以确定其兼容性，并评估是否可以在线执行。
    • 确认该操作可以通过在线DDL方式来执行，减少对读写操作的干扰。

  3. 表拷贝：

    • 创建一个新的临时表，包含旧表中的所有列以及新添加的列。
    • 在此过程中，MySQL使用“快速拷贝”（Fast Copy）技术，在后台将数据从原始表复制到新表，同时允许对原始表进行读取和修改。

  4. 应用变更日志：

    • 在数据拷贝期间，任何对原始表的更新都会被记录到变更缓冲区中。
    • 一旦数据拷贝完成，这些变更会被应用到新表中以保持一致性。

  5. 切换表：

    • 数据拷贝和日志应用完成后，原表会被替换为新表。
    • 更新系统元数据以反映新的表结构。

  6. 释放锁定：

    • 最终释放所有锁定，表操作完成。

在整个过程中，MySQL尽量减少对表的全局锁定时间，只在必要时（如修改元数据或切换表时）才进行锁定。这种方式显著提高了对大型生产环境的支持能力，减少了因为表结构变更导致的服务中断时间。

Instant ADD操作执行流程

在MySQL 8.0中，"Instant ADD"操作是通过一种优化机制来实现的，这使得添加列的操作非常快速且高效。以下是Instant ADD执行流程的概述：

  1. 获取元数据锁：

    • 操作开始时，需要一个短暂的元数据锁以防止其他DDL操作同时进行。

  2. 更新数据字典：

    • MySQL只需修改系统数据字典中的表定义信息，以记录新列的存在和默认值。这一步骤不涉及对实际数据文件的修改。

  3. 无需重建表：

    • 不同于传统的表重建方法，Instant ADD不需要创建临时表或复制数据，因此也不会有长时间的表锁定。

  4. 无物理数据变更：

    • 表的物理存储结构保持不变。这意味着不需要调整现有的行，也不需要立即为新列分配存储空间。

  5. 动态读取默认值：

    • 对于新列，在读取数据时会自动返回默认值，而不必在磁盘上存储这些默认值。

  6. 释放锁：

    • 完成元数据的更新后，立即释放锁，使得表可以继续被其他事务访问。

这种优化大大加快了添加列的速度，并且最小化了数据库的停机时间，提高了MySQL在处理大型表结构变更时的效率。然而，这种即时添加列的特性仅适用于某些简单的场景，例如没有复杂的约束、索引或默认值表达式的列添加。

思考题：8.0为什么不需要重建表操作

在 MySQL 8.0 以下版本中，执行 ALTER TABLE 操作时，通常需要重建表，这主要是由于以下几个原因：

  1. 存储引擎限制：大多数 MySQL 数据库在 8.0 之前默认使用 InnoDB 作为存储引擎。InnoDB 的表结构信息并不完全存储在独立的元数据文件中，而是与数据文件紧密结合。因此，要修改表结构，就需要重新组织数据文件。
  2. 数据一致性和完整性：直接修改元数据可能导致数据不一致的问题。通过重建表，可以确保新的表结构与数据的一致性和完整性，因为所有的数据都会被重新写入到新的表结构中。
  3. 实现复杂：在旧版本中，实现直接修改元数据的功能会增加代码的复杂性，并且容易引入错误和潜在的数据库损坏风险。通过重建表可以简化实现，同时也能保持不同 SQL 操作的一致行为。
  4. 历史原因：由于早期设计和实现的限制，MySQL 在处理 ALTER TABLE 时选择了比较保守的方法，确保不会因为直接修改元数据而影响数据库的其他部分。

MySQL 8.0 引入了一种全新的数据字典架构，以及改进的存储机制，以提高性能和灵活性。

  1. 统一的数据字典：

    • 在 MySQL 8.0 之前，元数据存储在多个地方（如系统表、文件和内存结构）中。而 MySQL 8.0 引入了一个统一的数据字典，将所有元数据信息集中存储在 InnoDB 表空间中。这使得管理和访问更为高效，并支持事务特性。

  2. 事务型元数据操作：

    • 新的数据字典存储在 InnoDB 中，因此所有元数据的变更都可以利用 InnoDB 的事务特性。这意味着对元数据的修改可以被原子地提交或回滚，提升了系统的可靠性和一致性。

  3. 减少锁争用：

    • 由于使用了统一的数据字典，许多涉及元数据的操作（例如，DDL 操作）不再需要长时间锁定整个数据库，从而减少了锁争用，提高了并发度。

  4. 即刻模式更改：

    • MySQL 8.0 对于很多 ALTER TABLE 操作能够直接更新数据字典，而不必重建整个表。例如，增加虚拟列或重新命名列等操作在某些情况下可以即时完成，而无需复制表数据。

  5. 改善的持久性和恢复能力：

    • 因为数据字典存储在 InnoDB 表中，所以它享有与用户数据相同的持久性和崩溃恢复能力，当数据库发生故障时，数据字典能够保持一致且完整。

总结

  • 5.6版本之前，Alter操作都需要复制表、锁旧表；
  • 8.0以下版本，需要复制表，但是不需要锁旧表（引入变更日志，同步复制过程中变化的数据）
  • 8.0以上版本，因为将表结构信息从数据文件中抽离出来，由独立的元数据文件管理；所以针对部分操作，直接修改元数据即可。
avatar
文章
阅读
粉丝