【互联网之常见】-大表新增字段有哪些方法

选择哪种方法取决于多个因素，包括数据库类型、系统负载、业务需求以及维护窗口。在进行此类操作时，强烈建议在测试环境中先进行测试，评估操作对性能的影响，并确保有完整的数据备份和回滚计划。此外，最好在业务低峰时段进行此类操作，以减少对业务的影响。当你需要在MySQL大表上添加字段时，以下是一些最佳实践：1. **评估影响**：在生产环境中执行之前，应该在测试环境中模拟操作，评估其对性能的影响和所需时间。

Java托尼

2923人浏览 · 2019-10-10 18:16:02

Java托尼 · 2019-10-10 18:16:02 发布

对于包含几亿条数据的大表来说，新增字段是一个需要谨慎处理的操作，因为它可能会对数据库的性能和可用性产生显著影响。对大表添加字段是一个特别敏感的操作，因为它可能会导致长时间的锁表和性能下降。以下是一些常见的方法以及它们的优缺点：

1. 直接在表上新增字段

在MySQL中，直接使用`ALTER TABLE`添加新字段可能会导致表重建，这在大表上可能需要很长时间，并且会锁定表，阻止写入操作。

优点：

操作简单，只需一条SQL命令。
直接修改，无需额外的数据迁移步骤。

缺点：

对于大表来说，这个操作可能会锁表很长时间，导致应用无法访问该表，影响业务。
可能导致事务日志迅速增长，消耗大量磁盘空间。
在某些数据库系统中，如MySQL，添加列可能会导致表的全表复制，这将消耗大量时间和计算资源。

扩展：

- 为了减少锁表时间，可以考虑在MySQL 5.6及以上版本使用`ALGORITHM=INPLACE`，这样可以尽可能减少对表的锁定时间。
- 对于不支持`INPLACE`操作的列类型变更，可以考虑将其拆分成多个小操作，例如先添加列，然后再逐步更新数据。

示例：

ALTER TABLE my_large_table ADD COLUMN new_column INT DEFAULT 0, ALGORITHM=INPLACE, LOCK=NONE;

2. 使用在线DDL工具

`pt-online-schema-change`和`gh-ost`是两个常用的在线DDL工具，它们可以在不锁定原始表的情况下进行表结构变更。

优点：

减少了对业务的影响，因为它们不会锁住原始表。
提供了更细粒度的控制，如可以控制复制操作的速度，减少对主数据库的影响。

缺点：

操作相对复杂，需要安装和配置额外的工具。
变更期间，会创建一个临时表来复制数据，这需要额外的存储空间。
可能会有性能开销，因为数据复制和同步需要额外的计算资源。

扩展：

- `pt-online-schema-change`工作原理是创建一个新表，将旧表的数据复制到新表中，并在此过程中通过触发器捕获对旧表的更改，最后将旧表切换为新表。
- `gh-ost`利用MySQL的复制日志（binary log）来捕获数据变更，这样可以减少对数据库性能的影响，同时也支持在复制过程中暂停和恢复。

示例：

使用`pt-online-schema-change`：

pt-online-schema-change --alter "ADD COLUMN new_column INT DEFAULT 0" D=mydatabase,t=my_large_table --execute

使用`gh-ost`：

gh-ost \
  --host=localhost \
  --user=myuser \
  --password=mypassword \
  --database=mydatabase \
  --table=my_large_table \
  --alter="ADD COLUMN new_column INT DEFAULT 0" \
  --execute

3. 创建新表并迁移数据

在MySQL中，可以创建一个新的表结构，包括新的字段，然后将数据从旧表复制到新表。

优点：

可以在不影响旧表的情况下进行操作，迁移可以逐步进行。
新表可以优化结构，如重新定义索引、调整字段顺序等。

缺点：

数据迁移可能会很耗时，尤其是在数据量大的情况下。
需要维护复杂的数据同步逻辑，确保迁移期间的数据一致性。
在迁移完成切换到新表时，可能需要短暂的停机时间。

扩展：

- 创建与原表结构相同的新表，并添加新字段。
- 使用`INSERT INTO new_table SELECT * FROM old_table`来复制数据。
- 在数据复制过程中可能需要同步新的写操作，这可以通过设置写锁或使用触发器来实现。
- 一旦数据迁移完成，可以通过重命名表来切换旧表和新表。

示例：

CREATE TABLE my_large_table_new LIKE my_large_table;
ALTER TABLE my_large_table_new ADD COLUMN new_column INT DEFAULT 0;
INSERT INTO my_large_table_new (col1, col2, ..., colN, new_column) SELECT col1, col2, ..., colN, 'default_value' FROM my_large_table;
RENAME TABLE my_large_table TO my_large_table_old, my_large_table_new TO my_large_table;

4. 使用特定数据库功能

MySQL 8.0引入了`INSTANT ADD COLUMN`特性，允许在不重建表的情况下即时添加列，这适用于某些类型的列添加。一些数据库管理系统提供了特殊的功能来处理大表结构变更，例如：

PostgreSQL: 使用ALTER TABLE命令添加字段，如果默认值是NULL，通常不会重写整个表。
Oracle: 可以使用DBMS_REDEFINITION包来在线重定义表。
SQL Server: 使用ALTER TABLE命令并结合在线索引操作（Online Index Operations）。

优点：

利用数据库本身的高级功能，通常更安全可靠。
可能无需额外的工具或复杂的迁移过程。

缺点：

需要对数据库的特定功能有深入了解。
不同的数据库有不同的限制和要求。

扩展：

- 这个特性只适用于InnoDB引擎，并且有一些限制，例如不能添加有默认值的列（除非是NULL）。
- 如果条件允许，这是最快的添加列方法，几乎不会对现有的操作产生影响。

示例：

ALTER TABLE my_large_table ADD COLUMN new_column INT, ALGORITHM=INSTANT;

5. 分区表

如果原表是一个分区表，可以单独对各个分区进行`ALTER TABLE`操作，从而减少对整个表的影响。

优点：

减少了对整个表的影响，操作可以更加灵活。
可以逐个分区地进行操作，降低风险。

缺点：

需要表已经预先进行了分区。
对于没有分区的表，实现分区会很复杂。

深度：

- 对分区表进行操作时，可以对各个分区逐一添加字段，减少每次操作的数据量和时间。
- 分区表的操作需要考虑分区键和分区策略，确保添加字段后仍然满足分区要求。

示例：

针对分区表单独添加字段，你需要对每个分区逐一进行操作，如下所示：

ALTER TABLE my_large_partitioned_table PARTITION p0 ADD COLUMN new_column INT DEFAULT 0;
ALTER TABLE my_large_partitioned_table PARTITION p1 ADD COLUMN new_column INT DEFAULT 0;
-- 重复该过程直到所有分区都更新完毕