MySQL 表 DDL 操作全攻略

我们在运维MySQL数据库的时候，时常会对数据表进行DDL操作，比如修改字段、添加索引等。对于MySQL，DDL操作时会锁表。如果表比较小，比如小于1w时，影响还是比较小的。当遇到千万级别的表，就会影响对表的读写操作了，甚至导致整个数据库发生异常，服务崩溃！

如何在Mysql运维的道路上稳步前行，有效化解潜在风险，确保数据库系统的稳定与高效呢？让我们一起了解几个常见的工具的原理和实践步骤吧。

一、问题案例

以下是一些常见的问题案例：

1. 定义主键问题：创建表时忘记设置主键的 AUTO_INCREMENT 属性，导致新订单插入时发生主键重复错误。例如，在创建订单表时，若未对订单号字段设置 AUTO_INCREMENT，后续插入订单数据时，可能会因手动指定的订单号重复而出现错误。
2. 外键约束问题：未正确设置外键约束，导致插入无效数据。比如在关联用户表和订单表时，如果外键设置错误，可能会插入不存在用户的订单数据。
3. 索引选择问题：在频繁更新的字段上创建索引，导致插入和更新性能下降；未评估索引的选择性，导致索引效果不佳。例如对一个经常更新状态的字段创建索引，每次更新都要维护索引，严重影响性能；或者创建的索引区分度低，不能有效提升查询效率。
4. 删除操作问题：误删了生产环境中的重要表，导致数据丢失；删除列后，未更新相关应用程序逻辑，导致系统出现故障。例如在数据库管理中，误操作删除了核心业务表，数据无法恢复；或者删除了某列后，应用程序中仍有对该列的引用，导致程序出错。
5. 分区策略问题：分区策略不合理，导致查询性能下降。例如按照错误的字段或方式进行分区，使得查询时仍需扫描大量不必要的数据分区。
6. 索引过多问题：错误地使用了过多的索引，增加了写操作的开销。比如对一些很少用于查询的字段也创建了索引，导致数据插入、更新时需要额外维护这些索引，降低性能。

MySQL 千万级表因为上述相关的一些问题，需要进行 DDL 操作时，由于其锁表特性可能引发诸多严重问题：

1. 长时间锁表导致业务中断：当执行 DDL 操作修改千万级表的字段或索引时，例如添加一个新字段，MySQL 会对表加锁。在这个过程中，所有对该表的读写操作都会被阻塞。对于一个高并发的业务系统，这意味着大量的前端请求会处于等待状态，甚至超时失败。比如电商平台在促销活动期间，对订单表进行 DDL 操作，可能导致用户无法下单、查询订单状态或进行支付等操作，直接影响业务的正常运转，造成巨大的经济损失。
2. 主从复制延迟加剧：在主从架构的 MySQL 环境中，主库执行 DDL 操作锁表后，从库的复制线程会因为无法获取主库表的变更而延迟。当表数据量达到千万级时，这种延迟会更加明显。例如，在一个内容分发网络（CDN）服务中，主库对存储海量日志信息的表进行 DDL 操作，从库的延迟可能导致数据分析和报表生成的不准确与不及时，进而影响基于数据的决策制定，无法及时对网络服务质量进行优化和调整。
3. 锁表期间资源竞争与系统负载飙升：DDL 锁表期间，等待锁释放的事务会不断堆积，占用大量系统资源如 CPU、内存和磁盘 I/O。以社交网络平台为例，对用户关系表进行 DDL 操作时，由于锁表导致大量的好友查询、消息发送等操作等待，系统资源被这些等待事务耗尽，不仅使数据库服务器性能急剧下降，还可能影响同一服务器上其他服务的正常运行，甚至引发整个系统的崩溃。
4. 回滚风险与数据不一致：如果在 DDL 操作过程中出现意外情况，如服务器宕机或执行超时，可能导致操作回滚。对于千万级表，回滚操作同样需要耗费大量资源和时间，并且可能使数据处于不一致的状态。例如金融交易系统中对账户表进行 DDL 操作失败回滚后，可能出现账户余额与交易记录不匹配的情况，严重影响金融数据的准确性和可靠性，引发用户信任危机。

二、解决方案工具

pt-online-schema-change

工作原理

通过创建表的空副本来进行更改，在原表上创建触发器，将原表中的行复制到新表中，复制完成后，用新表替换原表。在复制期间，原表上的更新操作会通过触发器同步到新表，保证数据的一致性。

使用步骤

1. 首先进入 /root/ 目录

cd /root/

1. 下载 Percona Toolkit：

# For Debian or Ubuntu:
sudo apt-get install percona-toolkit

# For RHEL or CentOS:
sudo yum install percona-toolkit

#  Alternative Install Methods
#  You can also download the packages # from the Percona web site and install it using tools like dpkg and rpm

wget https://www.percona.com/downloads/percona-toolkit/-3.6.1/tarball/percona-toolkit--3.6.1.tar.gz
- 解压下载的文件：
tar -zxvf percona-toolkit-3.6.1.tar.gz
- 检查配置、编译并安装：
perl Makefile.PL 
make 
make install
- 将相关工具加入环境变量：
cd./percona-toolkit-3.6.1/bin/ cp * /usr/bin/

1. 假设要对名为 employees 的表添加一个名为 email 的 VARCHAR (255) 类型且唯一约束的字段，执行命令：

pt-online-schema-change --alter "ADD COLUMN email VARCHAR(255) COMMENT '邮箱'" D=your_database,t=employees,A=utf8 --execute

# 其中，`--alter` 后面指定要进行的 DDL 变更语句，
`D` 表示数据库名，`t` 表示表名，`A` 表示字符集
`--execute` 表示执行操作。若不想直接执行，可先使用 `--dry-run` 查看操作计划。

gh-ost

工作原理

采用消费 binlog 的方式来代替触发器方式，将同步信息存储到临时表中，经历校验、初始化、迁移、cut-over 四个阶段完成表结构变更。它通过读取 binlog 中的数据变更，将这些变更应用到新表上，从而实现数据的同步，避免了触发器可能带来的性能问题和复杂性.

使用步骤

• 确保 MySQL 版本为 5.7 及以上，并且 binlog_format 设置为 row，log_slave_updates 设置为 on，同时执行操作的用户具有相应权限.
• 例如对 abtest 数据库中的 abtest1 表进行引擎变更为 InnoDB 的操作，执行命令：

gh-ost -max-load=Threads_running=20 -critical-load=Threads_running=100 -chunk-size=2000 -user="temp" -password="test" -host=10.10.1.10 -allow-on-master -database="abtest" -table="abtest1" -alter="engine=innodb" -cut-over=default -exact-rowcount -concurrent-rowcount -default-retries=120 -timestamp-old-table -assume-rbr -panic-flag-file=/tmp/ghost.panic.flag -execute

这里的各个参数含义如下：

• -max-load 和 -critical-load 用于控制操作过程中的负载阈值，当 MySQL 的 Threads_running 指标达到设定值时，会采取相应的暂停或停止操作。
• -chunk-size 指定每次处理的数据块大小。
• -user、-password、-host 用于指定连接 MySQL 的用户、密码和主机地址。
• -allow-on-master 表示允许在主库上进行操作（需谨慎使用）。
• -database 和 -table 分别指定数据库名和表名。
• -alter 为要执行的 DDL 变更语句。
• -cut-over 等其他参数用于控制操作的具体行为和特性，如精确行计数、并发行计数、重试次数等设置，-panic-flag-file 用于指定 panic 标志文件的路径.

MySQL Online DDL 最新特性及使用

MySQL 8.0 中的 Online DDL 有了更多的优化和特性.

工作原理

在 MySQL 5.6 开始支持，通过不同的锁级别控制 DDL 期间的读写操作，在 MySQL 8.0 有了更多优化。它根据不同的 DDL 操作和表的情况，选择合适的算法和锁策略，尽量减少对表读写操作的影响。例如对于一些不涉及数据格式变更的操作，可以采用更轻量级的锁，允许并发读写；而对于一些复杂的变更，可能需要更严格的锁控制，但也会在一定程度上允许并发.

• 例如创建一个名为 users 的示例表：

CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

• 然后在线添加新列 email，执行命令：

ALTER TABLE users ADD COLUMN email VARCHAR(100) ALGORITHM=INPLACE,LOCK=NONE;

其中，ALGORITHM=INPLACE 表示尽量采用原地修改的算法（如果支持），LOCK=NONE 表示在操作过程中不锁定表，允许并发读写。但需要注意的是，并非所有的 DDL 操作和表结构都支持这种无锁或低锁级别的操作，具体要根据实际情况和 MySQL 版本进行调整.

三、总结

在处理 MySQL 千万级表的 DDL 操作时，我们需要充分认识到锁表可能引发的一系列严峻问题，并根据实际情况巧妙选择合适的解决方案工具。pt-online-schema-change、gh-ost 和 MySQL Online DDL 各有其独特的优势与适用场景，在使用过程中务必严格遵循操作步骤，密切留意操作过程中的各项关键指标以及可能突发的异常状况，确保 DDL 操作顺利推进，最大程度降低对数据库读写操作的不良影响，为业务系统的稳定、高效运行筑牢坚实的数据基石 。