大表修改字段类型另类方法

     最近加字段,突然想起几年前一次大表加字段的事情。

     缘起:

       公司的订单表和配送单表,以前金额字段是:decimal(19,3)类型,正常肯定够用,但是财务有个需求,需要把字段类型从

decimal(19,3) 改成decimal(38,10)类型,支持10位小数点。

       当BI部门给我提这个需求时,也没觉得怎样,在SQL Server的大表加字段,不像MySQL 那么麻烦,执行个alter命令就可以。

      测试:

         为保险起见,找最近一天的备份文件,恢复到一台比较好的服务器,配置比较高(SSD硬盘,256G)

         先在订单主表,有3,4千万多条数据,执行alter命令后,想了会很快,没想到执行了1个多小时还在执行。

        因有4个多表:  订单主表, 订单明细表,配送单主表,配送单明细表,这些都是核心业务表,数据量主表有3,4千万多条,明细表6,7千万多条,如果按1个小时执行一个表完成,4个表要4个多小时,而且在执行alter时,表是不可访问的,一旦有问题,回滚执行又需要很长时间,风险很大。

      沟通:

         和BI部门,测试部门沟通,让他们找财务不要改这个字段类型,风险高,BI老大沟通下来,一定要改。怎么处理,明知该类型有不可控风险,而且是核心表,这么长的锁定时间,4个大表。

      方案

           肯定不能用alter直接改,那怎么处理,

       方案1: 可以建个改好字段的新表,把数据导进去: (方案不行

         但是这些表都很大,都好几千万,要导好久  ,后面一直思索有什么好的办法,解决该问题?

       方案2:否再加个字段,在把数据update过来,上线的时候修改新旧字段名(可行

      后来在测试环境测试一下,但是这表都几千万条,怎么update,如果切换的时候update ,update执行时间太长,肯定不行

      我就做个job,每次更新一定的条数,如 limit 10000条,每次update,4个表的都update,到切换的时候,不一样的再update就很快。   

    上线:

         最后选择方案2,提前加新字段,job定期update每次少量更新字段数据一致,在切换的字段名后,再update一下,保证2个字段数据一致

         新字段不能加not null,在切换后加上not null约束,以前的字段因为not null但是有default默认值,不影响。

         执行完后,表只比以前多了几个字段,需求完全满足。

    

posted @ 2021-09-02 17:06  zping  阅读(708)  评论(0编辑  收藏  举报