前言

在一些互联网项目中，由于早期的数据库架构缺乏有效合理的设计，后期随着表数据量的增加就不可避免的会进行表结构变更。在修改表结构时，如果我们操作不当会引起重大事故。

影响如下：
1、在线修改大表的表结构执行时间往往不可预估，一般时间较长。
2、由于修改表结构是表级锁，因此在修改表结构时，影响表写入操作。
3、如果长时间的修改表结构，中途修改失败，由于修改表结构是一个事务，因此失败后会还原表结构，在这个过程中表都是锁着不可写入。
4、修改大表结构容易导致数据库CPU、IO等性能消耗，使MySQL服务器性能降低。
5、在线修改大表结构容易导致主从延迟，从而影响业务读取。

错误操作方式

1、准备user 表

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `nick_name` varchar(32) NOT NULL COMMENT '用户昵称',
  `gender` tinyint(2) unsigned NOT NULL COMMENT '性别',
  `type` tinyint(2) unsigned NOT NULL COMMENT '用户类型',
  `is_vip` tinyint(2) unsigned NOT NULL COMMENT '是否VIP',
  `grade` tinyint(2) unsigned NOT NULL COMMENT '等级',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4  COMMENT='用户表';

2、准备数据

3、修改表

请勿使用以下方式修改表结构

ALTER TABLE `test`.`user` 
ADD COLUMN `mobile` VARCHAR(32) NULL COMMENT '手机号码' AFTER `create_time`,
ADD INDEX `idx_nickname` (`nick_name` ASC);

4、引起主从延迟报警

■【腾讯云可观测平台告警】
您好！您账号（账号ID: ******，昵称: *******）的腾讯云可观测平台告警已触发
告警内容: 云数据库-MySQL-主机监控|主从延迟时间 > 10 秒
当前数据: 318 秒 (主从延迟时间)
告警对象: cdbro-5stjtw5g(实例名:******,IP地址:127.0.0.1:3306),实例类型:ro
项目|地域: 默认项目 | 北京
告警策略: mysql数据库监控报警
触发时间: 2024-01-18 00:14:00 (UTC+08:00)
您可以登录腾讯云可观测平台控制台查看告警详情，或在腾讯云助手小程序查看告警详情

生产环境下，变更 MySQL 表结构正确姿态

1.确认表的元数据信息；
2.对需求里的改表语句进行审核，如果存在不规范的地方进行修正。
3.确认当前节点是主节点还是从节点。
4.确认主从状态是否正常。
5.根据数据量、业务场景、业务容忍度，选择变更的方案以及预估需要的时间。
6.确定操作时间点，如果数据量大，建议在低峰进行。
7.发布变更通知，告知应用观察对应业务。
8.变更过程中，留意 MySQL 监控和机器监控，观察主从状态、主从连接数、主从机器负载。
9.变更过程出现问题，及时 KILL 相关操作。
10.变更顺利完成，进行数据校验。

最佳实践

1、创建一个新表 user_temp

use test;
create table user_temp like user;

2、修改 user_temp 表

ALTER TABLE `test`.`user_temp`
ADD COLUMN `mobile` VARCHAR(32) NULL COMMENT '手机号码' AFTER `create_time`,
ADD INDEX `idx_nickname` (`nick_name` ASC);

3、执行insert into 语句

use test;
insert into user_temp select * from user;

4、检查执行时间段是否有新数据未同步

在数据拷贝-新表改名使用期间，会存在少部分的数据写入存储到旧表中，这里事后需要根据时间节点做一次手动数据同步

5、删除 user 表

方法一：使用DELETE语句

DELETE FROM table_name;

这种方法是直接使用DELETE语句删除整个表的数据。但是，这种方法在删除大表时可能非常耗时，因为DELETE语句会逐行扫描表并删除记录，同时会产生大量的日志和行锁。

方法二：使用TRUNCATE TABLE语句

TRUNCATE TABLE table_name;

这种方法会删除整个表的数据，并且不会对每一行进行逐行扫描。它将表截断为一个空表，
并将所有空间归还给操作系统。在大表删除时，这种方法通常比DELETE语句更快。
但是需要注意的是，TRUNCATE语句不会触发触发器，
也不会执行相关操作，比如删除约束或者触发级联删除。

方法三：使用DROP TABLE语句

这种方法会彻底删除整个表，包括表的结构信息和数据。在删除大表时，
这种方法可能是最快的，因为它不需要逐行扫描数据或者产生日志。但是需要谨慎使用，
因为一旦执行了DROP TABLE语句，表和数据将无法恢复。

需要根据具体情况选择适合的删除方法。如果只是删除表中的数据而保留表结构，可以使用DELETE或TRUNCATE TABLE语句。如果需要完全删除表，包括结构和数据，可以使用DROP TABLE语句。注意在执行任何删除操作之前，务必备份数据以防止误操作造成数据丢失。

6、重命名 user_temp 为 user

rename table user_temp to user;

7、监控主从是否延迟，业务是否受影响

8、注意：

1、这种方式在数据拷贝-新表改名使用期间，会存在少部分的数据写入存储到旧表中，这里事后需要根据时间节点做一次手动数据同步
2、这种方法仍然会影响业务运行，建议使用在线DDL工具：pt-online-schema-change，且在访问量少时进行
3、大多数的alter table操作都会涉及lock–>copy to new table–>rename–>unlock的过程，锁表时间会很长，而且alter table 的process不可被kill，一旦执行就不可回退。

玖叶教程网

前端编程开发入门

MySQL进阶一之海量数据下修改表结构的正确姿势

前言

错误操作方式

生产环境下，变更 MySQL 表结构正确姿态

最佳实践