Mysql大表修改字段

背景

        由于平台币需要从之前的整形类型变为支持小数的decimal类型,所以需要对订单表的字段类型进行修改.

       

        订单表: 目前数据已经有 2000多万数据了,数据量还是比较大的, 需要修改的字段有3个,订单表名为 order_info

解决方案一

直接用sql原生语句

alter table order_info MODIFY column coin_count decimal(6, 1);

耗时 500多秒都没执行完, 撤回了,因为锁表用户下不了单影响可能还是比较大的,所以该方案废弃了

解决方案二

用新旧表逻辑进行处理,主要步骤有以下几步

1: 创建新的表,名字为 order_info_new, 结构从 order_info 复制, 但是那三个字段已经从int改为decimal了, 这个步骤是不会锁表的

2: 把原有的order_info表名改为 order_info_old, 同时把order_info_new改为 order_info即可,这个步骤也很快,同时也不会锁表

3: 使用mysql自带的命令行语句把之前的数据迁移到新表中,语句如下,只需要等待完成即可:

INSERT INTO order_info SELECT * FROM order_info_old

注: 第1步建立表时需要注解id的问题,如果是自增的话那么新表的自增id要大一些,防止切换过程中id发生冲突问题

        第三步执行时mysql默认是开启事务的,所以执行过程数据会查询不到,需要等到执行完成,看有没有这方面需要考虑的问题

选择

        最终自然是使用了第二种方案咯,整个过程也不算太慢,2000多万数据迁移也就用了半个多小时,阿里的RDS感觉还是有点东西的应该

其他优化

在处理这个问题时,同时一并处理了订单表设计不合理的地方,主要处理了两个问题

1: 数据字段类型不合理设计

2: 历史数据进行部分迁移

类型优化

 `delivery_status` int DEFAULT '1' COMMENT '发货状态 1:未发货 2:已发货' 订单表存在很多这种不合适的字段类型,不合理是因为这个字段只会有2个值,所以应该改为

 `delivery_status` tinyint DEFAULT '1' COMMENT '发货状态 1:未发货 2:已发货',这样比较合理

改完效果还是比较明显的,一条数据就可以节省3个字节,2000多万条大概节省了12G空间出来,这是一件很有意义的事来的

历史数据迁移

order_info的数据从 2020年一直到2024年的数据都是在同一张表,但其实很多历史数据也不会查了,所以把2020-2021年的数据迁移到新的表中去了,做法也很简单

1: 原来的 order_info 只保留了 2022年以后至今的数据

2: 再创建一张 order_info_history 数据表,结构一摸一样的

3: 把 order_info 2022年前的数据全部转到 order_info_history 中

具体的操作命令如下:

INSERT INTO order_info SELECT * FROM order_info_old where create_time >= 1640966400000;

INSERT INTO order_info_history SELECT * FROM order_info_old where create_time < 1640966400000;

### 修改 MySQL Decimal 字段精度的性能与优化 在修改 MySQL `DECIMAL` 字段的精度可能会导致显著的性能开销,因为此类操作通常涉及的重构[^1]。具体来说,当执行 `ALTER TABLE` 语句以修改字段类型或精度时,MySQL 需要创建一个临时并将原始数据复制到新中。这一过程可能导致长时间的锁定和高 I/O 消耗。 #### 性能影响因素 以下是影响性能的主要因素: - **小**:中的行数越多,重构所需的时间越长。 - **存储引擎**:InnoDB 支持在线 DDL 操作(通过设置 `ALGORITHM=INPLACE` 和 `LOCK=NONE`),而 MyISAM 则需要完全锁定[^2]。 - **硬件资源**:磁盘 I/O、CPU 和内存都会直接影响操作的速度。 - **并发性**:如果存在高并发写入,锁定可能引发阻塞问题。 #### 优化方法 为了减少修改字段精度时的性能开销,可以采用以下策略: 1. **使用在线 DDL** 对于 InnoDB ,推荐使用 `ALGORITHM=INPLACE` 和 `LOCK=NONE` 参数来执行 `ALTER TABLE` 操作,从而避免长时间的锁定[^3]。例如: ```sql ALTER TABLE your_table MODIFY COLUMN your_column DECIMAL(10, 2), ALGORITHM=INPLACE, LOCK=NONE; ``` 2. **分批处理** 如果无法使用在线 DDL 或非常,可以考虑分批更新数据。创建一个新的结构,并逐步将数据从旧迁移到新中。完成迁移后,重命名以替换原。示例代码如下: ```sql -- 创建新 CREATE TABLE new_table LIKE old_table; ALTER TABLE new_table MODIFY COLUMN your_column DECIMAL(10, 2); -- 分批插入数据 INSERT INTO new_table SELECT * FROM old_table WHERE id BETWEEN 1 AND 100000; -- 继续分批插入直到完成 ``` 3. **调整系统参数** 调整 MySQL 的配置参数可以提升操作效率。例如,增加 `innodb_buffer_pool_size` 和 `tmp_table_size` 的值,以减少磁盘 I/O 并提高临时的性能[^4]。 4. **使用第三方工具** 工具如 Percona Toolkit 提供了 `pt-online-schema-change`,可以在不锁定的情况下修改结构。它通过创建触发器和影子的方式实现无停机变更[^5]。使用方法如下: ```bash pt-online-schema-change --alter "MODIFY COLUMN your_column DECIMAL(10, 2)" D=your_database,t=your_table,u=root,p=password ``` #### 注意事项 - 在执行任何修改操作之前,请确保备份数据以防止意外丢失。 - 测试环境下的性能评估是必要的,尤其是对于生产环境的操作。 ```sql -- 示例:测试在线 DDL 修改字段精度 ALTER TABLE test_table MODIFY COLUMN amount DECIMAL(15, 4), ALGORITHM=INPLACE, LOCK=NONE; ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值