本文介绍了线上环境 MySql 更新大表(千万级别大表)结构-删除列操作。
一 解需要更新大表
1 清楚要操作更新的表情况
查看看当前表的数据,如下图:
1 数据记录条数
2 占用磁盘大小
3 表的列数
该表设计具然有122列,非常不合理,第一次看到该表时非常扎心觉得有1万头操泥马从心中奔过……
2 更新大表时注意事项
九百多万条数据都快进入千万级别层次了,这时变更表要保证不能影响线上环境,更不能直接使用删除列数据,变更时要注意以下几个点:
- 数据完整性: 确保要删除的列不包含关键数据或被其他表引用,否则可能导致数据丢失或引用失效。
- 应用程序兼容性: 检查应用程序是否使用了要删除的列,并做好相应的代码修改。
- 性能影响: 在线 DDL 操作会带来一定的性能开销,建议在非高峰时段执行。
- 大表风险: 在线删除列可能需要较长的时间,并占用大量 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个字段还是根据业务情况可以提取出多一张明细表,下次优化继续。