mysql 在线DDL原理是什么？详细说明一下。

### 1. **在线 DDL 的核心概念：临时表（影子表）** 当你在 MySQL 中执行某些需要修改表结构的操作（例如添加索引或删除列）时，MySQL 不会直接对原表进行修改。取而代之的是，它会创建一个临时的“影子表”（shadow table）。这个临时表的结构是你想要的最终表结构。 #### 举例： 假设你有一个表 `users`，其中有如下数据： ```sql CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50) ); INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie'); ``` 你现在希望在线添加一个索引到 `name` 列上： ```sql ALTER TABLE users ADD INDEX idx_name (name); ``` **在线 DDL 是如何工作的？** 1. **获取元数据锁 (MDL)**： - 在 MySQL 执行 `ALTER TABLE` 时，首先会获取一个 **MDL 锁**，用于锁定表的元数据。这不会阻塞现有的查询或插入操作，而是确保表的结构在变更期间是稳定的，防止其他事务同时修改表结构。 - 例如，其他事务可以继续插入或更新 `users` 表的数据，但不能修改 `users` 表的表结构。 2. **创建影子表**： - MySQL 会根据你提供的修改指令，创建一个和原表结构相同、但包含你想要的更改的新表（临时表/影子表）。在本例中，它会创建一个新的 `users` 表，并带有你想要添加的索引。 ```sql CREATE TABLE temp_users ( id INT PRIMARY KEY, name VARCHAR(50), INDEX idx_name (name) ); ``` 3. **数据复制过程**： - MySQL 会逐行从原表 `users` 中读取数据，然后将这些数据插入到新创建的影子表 `temp_users` 中。这个过程是在后台进行的，因此即使表中有大量数据，原表仍然可以接受新的读写操作。 - 比如，如果此时有插入操作 `INSERT INTO users (id, name) VALUES (4, 'David');`，MySQL 会在后台处理：插入新数据到原表 `users` 的同时，也将该数据插入到影子表 `temp_users`。 4. **同步新数据**： - 在数据复制过程中，如果有新的写操作（比如插入或更新），MySQL 会确保这些数据不仅会写入到原表，还会同步写入到影子表中。这是通过内部的日志机制来实现的。 - 这样，两个表的数据始终保持一致。 5. **替换旧表**： - 当所有数据从旧表复制到影子表后，MySQL 会短暂锁定表，将原表 `users` 替换为新表 `temp_users`。 - 这个替换操作非常快，因为只是修改了一下表的指针和元数据。替换完成后，新表 `users` 就包含了你新添加的索引 `idx_name`，而这个过程对用户几乎是无感的。 ### 2. **在线 DDL 的具体操作过程** 我们再以具体操作步骤来详细描述整个过程： #### 举例： 假设你在进行一个 **添加索引** 的操作： ```sql ALTER TABLE orders ADD INDEX idx_customer (customer_id); ``` #### 在线 DDL 执行步骤： 1. **准备阶段**： - MySQL 开始执行 ALTER 操作，获取一个元数据锁（MDL）来确保在 DDL 执行过程中没有其他 DDL 并发修改表结构。 - MDL 锁不会阻塞对表的读写操作，除非有其他 DDL 请求。现有的 SELECT、INSERT、UPDATE 等查询都不会受到影响。 2. **创建影子表**： - MySQL 为表 `orders` 创建一个影子表（临时表），这个表除了结构上的改变（新添加的索引）外，其它方面和原表完全一致。 - 在这个阶段，新的数据写入将同时被写入到旧表和影子表。 3. **数据复制**： - MySQL 在后台逐行将旧表的数据复制到影子表。在复制过程中，原表 `orders` 可以继续接受读写操作，数据的一致性通过重做日志（Redo Log）和回滚段（Undo Log）来保证。 - 比如你有一个订单插入操作，`INSERT INTO orders (order_id, customer_id) VALUES (1, 1001)`，MySQL 会确保数据同步到两个表中。 4. **日志同步**： - 在线 DDL 会利用 `Change Buffer` 等机制来处理变更过程中产生的 DML 操作，保证插入、更新操作同时应用到旧表和影子表中。 5. **完成阶段**： - 当所有旧表数据复制完毕后，MySQL 会临时锁定表，并用影子表替换旧表的指针，然后释放锁。 - 新表结构立即生效，整个过程结束。 ### 3. **在线 DDL 的具体应用场景** 在线 DDL 适用于以下场景： - **添加索引**：你希望对已有大表添加索引，在线 DDL 可以确保在索引创建过程中表的数据仍然可以读写。 - **添加列**：对大表添加列时，可以在线完成，避免锁表导致停机。 - **删除列/索引**：删除列或索引时，数据不需要重新组织，可以直接完成。 ### 4. **限制与注意事项** - 在线 DDL 操作虽然不会阻塞 DML 操作，但需要消耗大量的 CPU 和 I/O 资源。如果表非常大或者有大量并发操作，可能会导致数据库性能下降。 - 并非所有 DDL 操作都可以在线执行。例如，有些复杂的表结构修改（如修改列的数据类型）仍然需要对表进行独占锁处理。 ### 总结 MySQL 在线 DDL 通过创建影子表、后台数据复制、日志同步和表指针替换等机制，使得 DDL 操作可以在不阻塞表读写的情况下进行。这个过程对于用户来说是透明的，减少了停机时间，并确保在大规模表修改时业务能够正常运行。