记一次数据库DDL与DML,ONLINE DDL

DDL与DML

DDL（Data Definition Language，数据定义语言）和DML（Data Manipulation Language，数据操纵语言）是SQL语言中的两个子集，它们用于执行不同类型的数据库操作。

DDL（数据定义语言）：

• DDL涉及到数据库模式定义和修改。
• DDL语句包括CREATE、ALTER、DROP、TRUNCATE、RENAME等。
• DDL操作不涉及数据本身，而是数据库的结构。
• DDL语句执行后，通常会导致事务的隐式提交，即不能回滚。

DML（数据操纵语言）：

• DML涉及到数据的操纵和处理。
• DML语句包括INSERT、UPDATE、DELETE、SELECT等。
• DML操作用于添加、修改、删除和查询数据记录。
• DML语句可以在事务中执行，通常可以回滚（除了SELECT，它不改变数据）。

 Online DDL

Online DDL 是 MySQL 5.6 及更高版本中引入的一个特性，允许对表进行某些DDL操作而不完全阻塞对该表的读取和写入。这提高了数据库的可用性，因为在执行诸如ADD COLUMN、DROP COLUMN、INDEX等操作时，表仍然可以对用户部分可用。

下面是一个使用Online DDL 修改表结构的例子：

ALTER TABLE table_name
ADD COLUMN new_column INT,
ALGORITHM=INPLACE, LOCK=NONE;

这里的ALGORITHM=INPLACE告诉MySQL尽可能在原地（in place）进行修改，而不是创建一个新表然后拷贝数据。LOCK=NONE指示MySQL在执行此操作时不要锁定表，使得在此操作进行时，表仍然可以进行查询和更新操作。

Online DDL的执行流程在MySQL中是由InnoDB存储引擎处理的，其允许在表上执行DDL操作而不完全锁定表，从而减少对数据库的影响。下面是一个简化的流程，说明了Online DDL如何工作：

1. 准备阶段：

   • MySQL解析ALTER TABLE语句，确定需要执行的操作类型。
   • MySQL检查操作是否支持Online DDL。如果不支持，则回退到传统的DDL方式。

2. 元数据锁定：

   • MySQL对表加上S锁（共享锁），允许其他事务读取表但不允许修改结构。

3. 拷贝和更改表结构（如果需要）：

   • 对于某些操作，MySQL可能创建一个新的临时表来包含更改后的结构。
   • 数据从原始表复制到新表中。
   • 在复制过程中，原始表对读操作仍然可用。

4. 应用日志：

   • 在复制数据时，对原表进行的修改会记录在一个特殊的日志中（DDL日志）。
   • 一旦数据复制完成，将这些修改应用到新表上以确保新旧表的数据一致。

5. 原子替换和重命名：

   • MySQL将原表替换为新表，通常是通过原子重命名操作完成的。
   • 在这一步，可能会有一个短暂的锁定时间，但这比传统的DDL操作短得多。

6. 清理：

   • 删除临时表（如果有）。
   • 清除DDL日志和其他临时数据结构。

7. 完成：

   • 释放元数据锁。
   • 操作完成，表现在包含了修改后的结构，并且在整个过程中，表的可用性最大化。

不同的DDL操作（如添加索引、删除列、修改列类型等）可能会有不同的执行细节，但总体上遵循上述流程。具体的执行细节也可能取决于MySQL的版本和配置。

当执行DDL操作如ALTER TABLE时，InnoDB存储引擎提供了不同的算法来处理这些更改。这些算法决定了MySQL如何进行表结构的修改。截至目前为止，主要的算法有以下几种：

1. INPLACE：

   • INPLACE算法允许更改表结构而不需要重新复制表的全部数据。
   • 在更改过程中，原始数据通常保持在原位，尽量减少对现有数据的移动。
   • 这种方式通常允许对表进行并发的DML操作（如INSERT、UPDATE、DELETE），但是并发操作的程度取决于具体的DDL操作。
   • 例如，添加索引通常可以使用INPLACE算法，而且允许并发的读写操作。

2. COPY：

   • COPY算法需要创建表的一个完整副本，并在该副本上应用更改。
   • 一旦更改完成，MySQL将新表替换为原表，并删除原始表。
   • 这个过程中，表通常会被锁定，以避免在复制过程中发生数据不一致的问题。
   • 因为COPY算法涉及到复制整个表，所以它通常比INPLACE算法要慢，并且在执行期间对表的锁定时间更长。

3. NOCOPY（不常用）：

   • NOCOPY算法是一个特殊情况，它通常用于那些不需要移动表数据的操作，比如重命名表。
   • 这种算法几乎不会影响到表的数据，因此执行速度很快，并且几乎不会锁定表。

4. INSTANT（MySQL 8.0引入的新特性）：

   • INSTANT算法允许对表进行某些更改而不需要重建表。
   • 例如，向表中添加一个新的列，如果该列有默认值，则可以瞬间完成，不需要修改现有的行。
   • 这种算法的优势在于其对性能几乎没有影响，并且对并发操作的兼容性最好。

每种算法都有其适用场景和限制，选择哪种算法取决于你想执行的具体DDL操作、表的大小、以及你能容忍的维护窗口大小。在MySQL 5.6及以后版本，可以在执行ALTER TABLE语句时通过ALGORITHM子句指定使用的算法，例如：

哪些情况下可能使用COPY算法

1. 更改存储引擎：当你需要将表的存储引擎从一个类型更改为另一个类型时（如从MyISAM更改为InnoDB）。

2. 添加/删除主键或唯一索引：如果你在没有主键的表上添加主键，或者更改或删除现有的主键或唯一索引。

3. 更改列类型：如果你需要改变一个列的数据类型，特别是当新的数据类型需要更多或更少的存储空间时。

4. 重新排序列：当你通过ALTER TABLE语句更改列的顺序时。

5. 删除列：删除表中的列通常需要重建表，因为这会改变表中数据的物理布局。

6. 添加列：在某些情况下，尤其是当新列不是追加到表的最后，并且有一个非默认值时。

7. 更改字符集或排序规则：更改表的字符集或排序规则通常需要重建表，以确保所有的文本数据都符合新的设置。

8. 列的重新定义：如使用ALTER TABLE ... MODIFY COLUMN或ALTER TABLE ... CHANGE COLUMN语句。

9. 分区操作：添加、删除、合并或重新定义分区通常需要重建表。

需要注意的是，这些操作是否需要重建表取决于MySQL的版本和具体的操作细节。在MySQL 5.6及更高版本中，通过InnoDB存储引擎提供的Online DDL功能，许多这类操作可以在不锁定表的情况下执行，尽管内部可能仍然涉及到重建表的过程。始终建议在执行这些操作之前，先检查MySQL的文档，以了解你的特定版本和配置下这些操作的行为。