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

目录

1. 直接在表上新增字段

优点：

缺点：

扩展：

示例：

2. 使用在线DDL工具

优点：

缺点：

扩展：

示例：

3. 创建新表并迁移数据

优点：

缺点：

扩展：

示例：

4. 使用特定数据库功能

优点：

缺点：

扩展：

示例：

5. 分区表

优点：

缺点：

深度：

示例：

总结与最佳实践

---

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

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;
-- 重复该过程直到所有分区都更新完毕

总结与最佳实践

选择哪种方法取决于多个因素，包括数据库类型、系统负载、业务需求以及维护窗口。在进行此类操作时，强烈建议在测试环境中先进行测试，评估操作对性能的影响，并确保有完整的数据备份和回滚计划。此外，最好在业务低峰时段进行此类操作，以减少对业务的影响。

当你需要在MySQL大表上添加字段时，以下是一些最佳实践：

1. 评估影响：在生产环境中执行之前，应该在测试环境中模拟操作，评估其对性能的影响和所需时间。

2. 备份数据：在进行结构变更之前，确保有完整的数据备份，以便在操作失败时能够恢复数据。

3. 监控：在执行操作时，实时监控数据库性能，包括CPU、内存、磁盘I/O等指标，以便在出现问题时及时响应。

4. 低峰时段操作：尽可能在系统负载较低的时段执行DDL操作，以减少对业务的影响。

5. 通知相关人员：确保所有相关人员（如开发人员、系统管理员、业务负责人）都知道即将进行的变更和可能的影响。

6. 渐进式部署：如果使用在线DDL工具，可以设定节奏，避免对主库性能造成显著影响。

7. 使用专业工具：考虑使用`pt-online-schema-change`或`gh-ost`等专业工具来减少影响。

8. 版本兼容性：确保你使用的方法与MySQL的版本兼容。例如，`INSTANT ADD COLUMN`功能仅在MySQL 8.0及以上版本中可用。

9. 事后验证：操作完成后，验证数据的一致性和完整性，并确保新添加的字段正常工作。

10. 回滚计划：准备好回滚计划，在操作失败或出现未预料的情况时能够迅速恢复到变更前的状态。

通过遵循这些最佳实践，你可以最大限度地减少在MySQL大表上添加字段时的风险。记住，每种方法都有其适用场景，没有一劳永逸的解决方案，因此选择最合适的方法需要根据实际情况和业务需求来决定。

有用请点赞，养成良好习惯！

疑问、交流、鼓励请留言！