【PHP面试题24】MySQL如何给一个1000万的表安全的加字段

文章目录

• 一、前言
• 二、MySQL表添加字段的方案
• • 2.1 ALTER TABLE 添加字段
  • 2.2 Online Alter Table
  • 2.3 Percona Toolkit
  • 2.4 给表新增字段
  • 2.5 使用触发器添加字段
• 总结

---

一、前言

本文已收录于PHP全栈系列专栏：PHP面试专区。-
计划将全覆盖PHP开发领域所有的面试题，对标资深工程师/架构师序列，欢迎大家提前关注锁定。

当一个MySQL表有大量数据的时候，需要给它添加新的字段，会带来很多问题，如数据重构、锁定表等。本文将介绍几种安全可靠的方式，让您在MySQL表中添加字段时更加愉悦。-

二、MySQL表添加字段的方案

2.1 ALTER TABLE 添加字段

ALTER TABLE 添加字段是最常用的方法，也是最不方便、最耗费时间的方法。假设要在一个有1000万行数据的表中添加一个字段remark，则可以使用以下命令：

ALTER TABLE table_name ADD remark VARCHAR(255);

这个命令会在表中添加一个新列remark，类型为VARCHAR(255)，并且默认值为NULL。然而，ALTER TABLE 添加新的字段可能会导致锁表，长时间卡住查询和更新操作，影响其他业务的正常运行。同时，如果在表中有多个索引的情况下，那么ALTER TABLE 添加新列时将会话费更长的时间。

2.2 Online Alter Table

Online Alter Table 是MySQL 5.6中新增的功能，可以在线修改MySQL表结构，包括添加、删除、修改列等。相较于ALTER TABLE 添加字段的方式，Online Alter Table 不需要锁定表，也不会对其他应用程序产生影响。

假设要在一个有1000万行数据的表中添加一个字段remark，则可以使用以下命令：

ALTER TABLE table_name ADD COLUMN remark VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;

这个命令使用了 Online Alter Table 的方式，添加一个新列remark，类型为VARCHAR(255)，并且默认值为NULL。使用INPLACE算法， 就能保证不会产生数据复制和重建索引的情况，而且在LOCK=NONE的情况下就不需要锁表，不会对其他业务产生影响。

Online Alter Table 是一种更加安全可靠的方式，但是在实际使用中还要注意以下几点：

1. 需要使用MySQL 5.6或者更高版本。
2. 需要把innodb_file_per_table配置为ON，否则可能会导致磁盘空间不足。
3. 一些特殊的数据类型不能使用Online Alter Table。（比如spatial类型，full text类型，等等）

2.3 Percona Toolkit

Percona Toolkit是Percona公司推出的MySQL实用工具包，其中有一个pt-online-schema-change工具，可以在线修改MySQL表结构，包括添加、删除、修改列等。相较于前面的两种方式，Percona Toolkit 的在线操作功能更加强大，支持对多个表进行合并、分裂、检查和校验等操作。

使用Percona Toolkit的方式，可以避免ALTER TABLE 添加字段的方式导致锁表，长时间卡住查询和更新操作，影响其他业务的正常运行。同时Percona Toolkit工具包还支持在线执行DDL语句，使到表结构的更新更加方便。

在实际使用Percona Toolkit工具包时，需要注意以下几点：

1. 需要把innodb_file_per_table配置为ON，否则可能会导致磁盘空间不足。
2. 安装Percona Toolkit的服务器和MySQL主机之间的网络延迟可能会影响到修改操作的速度。

2.4 给表新增字段

给表新增字段是一种非在线的方式，在不停止MySQL服务的情况下，通过重命名表和建立新表的方式，慢慢地完成表结构更改。其核心思路是：

1. 先创建一个新的表，包含了所有原有表的列，以及要新增的列；
2. 将旧表中的数据复制到新表中；
3. 在原有的业务中对新表进行测试，如果没有问题则可以删除旧表、重命名新表为旧表的名字；
4. 如果出现问题，可以还原到原来的旧表。

具体操作步骤如下：

# 1. 创建一个新表
CREATE TABLE new_table LIKE old_table;
ALTER TABLE new_table ADD remark VARCHAR(255);

# 2. 把旧表中的数据复制到新表中
INSERT INTO new_table (column1,column2,...)
  SELECT column1,column2,... FROM old_table;

# 3. 测试并且删除旧表
RENAME TABLE old_table TO backup_table;
RENAME TABLE new_table TO old_table;
DROP TABLE backup_table;

这种方式可以避免出现锁表、SQL语句阻塞等问题，但是在实际操作中需要注意以下几点：

1. 新建的表需要和旧表有相同的索引和数据约束；
2. 如果新表中存在外键，在复制数据之前需要先关闭外键约束，待复制结束后再开启；
3. 大数据量的表在拷贝数据时可能会导致磁盘空间不足，因此需要确保磁盘空间足够大。

2.5 使用触发器添加字段

与其他方法相比，这种方法的好处是，它可以确保数据一致性，并且可以追踪所有表更改。缺点是，它可能会影响数据库的性能，因此只适用于小型数据集。

在这种方法中，您需要创建一个触发器，以便在插入、更新或删除行时，在新的字段中添加备注。以下是一个示例触发器：

CREATE TRIGGER add_remark_trigger BEFORE INSERT ON mytable FOR EACH ROW SET NEW.remark = 'new remark';

此触发器将在插入新行时自动添加新字段。在此示例中，它会将字段设置为 “new remark”，但是您可以根据需要修改触发器以实现不同的行为。

总体来说，在不停机更新的情况下面，在数据实时变化的的数据表下面添加字段，为了维护数据一致性，用触发器是一个不错的选择。

总结

本文介绍了大表加字段的多种方式，大家如果遇到此类情况，请结合自己项目的实际情形作出选择。数据无价，请谨慎操作。