MySQL Online DDL原理剖析

2024-11-3 22:28| 发布者: 2ae29| 查看: 100| 评论: 0

摘要: 目次一、配景与意义二、工作机制1. 预备阶段2. 执行DDL操纵3. 完成与清理三、实现原理与优化四、使用场景与上风五、使用束缚与留意事项六、锁在Online DDL中的作用留意事项一、配景与意义在传统的数据库体系中，执

一、配景与意义

在传统的数据库体系中，执行DDL操纵时通常必要锁定表，以防止数据不一致。然而，这种锁定会导致表在DDL操纵期间不可用，从而影响数据库的可用性。MySQL原生Online DDL办理了这个题目，它允许DDL操纵在表仍旧可用时执行，大大提高了数据库的可用性，特别是对于必要24/7高可用性的应用来说，这是一个重要的特性。

二、工作机制

MySQL原生Online DDL的工作机制涉及多个步调和内部原理：

1. 预备阶段

检查与评估：在执行DDL操纵之前，MySQL会举行一系列的检查和评估工作。这包括验证DDL操纵的语法精确性、检查用户权限以及评估所需资源等。
选择执行策略：根据DDL操纵的类型和表的布局，MySQL会选择一个合适的执行策略。这通常涉及决定是使用COPY算法、INPLACE算法还是INSTANT算法。

2. 执行DDL操纵

COPY算法：

创建暂时表：首先，MySQL会创建一个与原始表布局相似的新暂时表。
数据拷贝：接着，原始表中的数据会被逐行拷贝到新的暂时表中。这个过程大概必要一些时间，具体取决于表的巨细和体系的性能。
重定名与替换：数据拷贝完成后，暂时表会被重定名为原始表的名字，从而替换掉原始表。在这个过程中，原始表会被加上写锁，以阻止对数据举行修改，确保数据的一致性。

INPLACE算法：

直接修改：与COPY算法差别，INPLACE算法直接在原始表上举行修改，无需创建暂时表和拷贝数据。
纪录DML操纵：在DDL操纵执行期间，如果有DML操纵（如INSERT、UPDATE、DELETE）实验修改表，这些操纵会被纪录下来。
应用DML更改：DDL操纵完成后，之前纪录的DML更改会被应用到表上，确保数据的完备性和一致性。

INSTANT算法：

元数据修改：对于某些简朴的DDL操纵（如修改表的默认字符集），INSTANT算法可以直接修改数据字典中的元数据，而无需对表数据举行任何更改。
无锁操纵：由于只修改元数据，因此这种算法可以在不锁定表的情况下完成，实现了真正的“刹时”完成DDL操纵。

3. 完成与清理

开释资源：DDL操纵完成后，体系会开释全部在操纵过程中分配的资源，如暂时表、内存等。
更新统计信息：MySQL会更新与表干系的统计信息，以便优化器可以大概更好地制定查询筹划。
通知与日志纪录：操纵完成后，体系会生成相应的日志纪录，以便在必要时举行恢复或审计。同时，也大概通过某种机制（如触发器）通知应用步伐DDL操纵的完成。

MySQL原生Online DDL通过差别的算法和策略来实现在线修改数据库布局的目标，从而提高了数据库的可用性和灵活性。这些工作原理确保了即使在执行DDL操纵时，数据库仍旧可以大概处理正常的DML操纵，淘汰了停机时间和维护成本。

三、实现原理与优化

在线DDL的焦点实现原理涉及几个关键环节，同时也有一些关键的优化策略：

构建暂时表：为了不影响原表的正常读写，体系会创建一个具备新布局的暂时表。这一步调为后续的DDL操纵提供了基础。
数据迁徙与实时同步：旧表中的数据会被高效地迁徙到暂时表中，同时确保数据的实时同步。这种迁徙策略旨在保障DDL过程中数据的完备性和一致性，制止数据丢失或损坏。
变更追踪与重播：利用日志机制，体系会追踪DDL执行期间旧表上的数据变更，并将这些变更实时重播到暂时表中。这一优化确保了数据在DDL操纵完成后的准确性。
无缝切换：当DDL操纵完成且数据完全同步后，数据库引擎会在合适的机遇将暂时表提拔为新表，从而实现无缝切换。此后，全部的读写操纵都将基于新表举行。

四、使用场景与上风

MySQL原生Online DDL适用于多种场景，如添加或删除列、修改数据类型、添加或删除索引等。这些操纵都可以在不绝止服务的情况下完成，大大提高了数据库的灵活性和可用性。别的，由于Online DDL淘汰了停机时间，因此也降低了维护成本和数据丢失的风险。

五、使用束缚与留意事项

尽管MySQL 5.7的在线DDL带来了诸多便利，但在现实使用中仍需留意以下几点：

操纵支持范围：并非全部类型的DDL操纵都支持在线执行。某些特定操纵大概仍必要锁定整张表，因此在执行前需确认操纵类型。
资源占用：DDL操纵期间大概会显着增长体系资源的消耗，特别是在数据迁徙和同步阶段。因此，在高负载环境下应谨慎规划并执行此类操纵。
测试与验证：为确保数据的完备性和业务的连续性，执行在线DDL之前应举行充分的测试和验证。这包括但不限于数据的备份、恢复以及一致性检查等步调。

六、锁在Online DDL中的作用

在Online DDL过程中，锁主要用于确保数据的一致性。差别类型的锁对表的可访问性有差别的影响：

共享锁（S锁）：允许多个事件读取同一资源，但不允许写入。在Online DDL中，这大概用于允许读取操纵继续举行，同时阻止写入操纵。
排他锁（X锁）：阻止其他事件读取或写入资源。在DDL操纵中，如果必要修改表的布局或数据，则大概必要使用排他锁。

ALGORITHM和LOCK选项

在MySQL中，你可以通过[code]ALGORITHM[/code]和[code]LOCK[/code]关键字来控制DDL操纵的举动。

ALGORITHM选项

INPLACE：这个选项指示MySQL直接在原表上举行修改，而不是创建一个新表。这通常可以淘汰锁的使用和时间，从而提高并发性。但是，并非全部的DDL操纵都支持INPLACE算法。
COPY：这个选项告诉MySQL创建一个新表，将原表的数据复制到新表中，然后在新表上执行DDL操纵。完成后，新表会替换原表。这个过程中，原表通常会被锁定，以防止数据不一致。COPY算法通常必要更多的时间和资源。
DEFAULT：如果不指定ALGORITHM选项，MySQL将选择默认的举动。这通常是实验使用INPLACE算法，如果不大概，则回退到COPY算法。 LOCK选项
NONE：这个选项指示MySQL在执行DDL操纵时不要对表加锁（如果大概的话）。但是，如果DDL操纵必要保证数据的一致性，MySQL大概会忽略这个选项并加锁。
SHARED：允许读取操纵继续举行，但阻止写入操纵。
EXCLUSIVE：阻止读取和写入操纵。这是最强的锁类型，用于确保DDL操纵期间数据的一致性。

如何使用这些选项

当你想要执行一个DDL操纵时，你可以通过添加[code]ALGORITHM[/code]和[code]LOCK[/code]选项来控制操纵的举动。例如：

[code]ALTER TABLE tbl_name ADD COLUMN col_name col_type, ALGORITHM=INPLACE, LOCK=NONE;[/code]

这条下令实验在原地（INPLACE）添加一个新列，并且只管不使用锁（LOCK=NONE）。但是，必要留意的是，如果MySQL判断无法保证数据的一致性而不使用锁，它大概会忽略这些选项。

可以通过如下的SQL语句检察是否有事件和锁等信息。

[code]select * from information_schema.innodb_locks;[/code] [code]select * from information_schema.innodb_trx;[/code] [code]select * from information_schema.innodb_lock_waits;[/code] [code]select * from information_schema.processlist;[/code]