线上环境 MySql 千万级别大表结构更新

  本文介绍了线上环境 MySql 更新大表（千万级别大表）结构-删除列操作。

一 解需要更新大表

1 清楚要操作更新的表情况

查看看当前表的数据，如下图：

1 数据记录条数

2 占用磁盘大小 

3 表的列数

该表设计具然有122列，非常不合理，第一次看到该表时非常扎心觉得有1万头操泥马从心中奔过……

 

2 更新大表时注意事项

       九百多万条数据都快进入千万级别层次了，这时变更表要保证不能影响线上环境，更不能直接使用删除列数据，变更时要注意以下几个点：

1. 数据完整性: 确保要删除的列不包含关键数据或被其他表引用，否则可能导致数据丢失或引用失效。
2. 应用程序兼容性: 检查应用程序是否使用了要删除的列，并做好相应的代码修改。
3. 性能影响: 在线 DDL 操作会带来一定的性能开销，建议在非高峰时段执行。
4. 大表风险: 在线删除列可能需要较长的时间，并占用大量 I/O 资源。可先对大表进行拆分或分治操作。

      可考虑先检查要删除的列是索引或外键，如有先删除索引或外键，检测到当前要删除除的表

3 更新大表时要考虑关键点

在线删除表列时，需要谨慎评估数据完整性、应用程序兼容性、性能影响和大表风险等因素，并选择合适的解锁方法来尽量减少对表锁的影响

• ALGORITHM=COPY: 该算法会创建一个临时表，将原表数据复制到临时表并删除要删除的列，然后再将临时表重命名为原表。这种方法在数据量较大时比较有效，但会占用更多的 I/O 资源。
• INPLACE: 该算法直接在原表上进行修改，但可能导致表碎片增加。建议在数据量较小或表碎片较多时使用。

ALTER TABLE table_name
  DROP COLUMN column_name
  ALGORITHM= {COPY | INPLACE};

如果不指定 ALGORITHM 参数，**在线删除表列的默认算法为 UNDEFINED。

UNDEFINED 算法是一种基于统计信息的算法，它会根据表的具体情况选择最合适的删除方式，包括 COPY 和 INPLACE 两种算法。

 我们这里是线上的库所有要考虑到锁表问题决定采用INPLACE。

 别外本次更新是删除列数非常多，是一列一列删除，还是多列一起删除？

这里是使用多列一起删除的方式来在线变更表结构，而不是一列一列地删除。

原因如下：

• 减少锁的开销: 一次性删除多列只需要对表加锁一次，而一列一列删除则需要对表加锁多次，会增加锁的开销，降低并发性能。
• 提高执行效率: 多列一起删除操作可以由 MySQL 内部优化器进行整合，提高执行效率。
• 降低出错风险: 一次性删除操作不易出现因中途报错导致部分列未删除的情况。

二 具体更新千万级大表操作

不管做什么先备份数据库，由于采用的是阿里云数据库，所以直接云平台操作备份即可，另外同时备份也备份一下这张表（考虑后面sql操作数据）如下图：

1 先创建表结构。

2 插入数据副本。

删除列，

 删除时花了2分16秒，删除过程并没有锁表，应用正常，删除后还有78列，空间减少了3个G，未影响在线主要业务。

78个字段还是根据业务情况可以提取出多一张明细表，下次优化继续。