今天一个同事问我,对一张1亿以上的在线读写事务表删除一个字段该怎么做。
当时的伪结论是:用select into 新表 from 事务表的方式,去新建一张一样的表,然后对新表的字段的属性进行设置,然后把业务转移到新表上,然后通过判断旧业务的某个时间字段,去对新表这段时间内的数据做一个更新。
因为从来没有做过这样的操作,我之后做了一系列的测试,得出了更精确的结论。
1.在 删除 或 添加一个字段(添加的话用null属性,并且不加默认值)时,该表会加一个叫sch-m的构架修改锁,通俗的理解为任何别的会话都无法访问这个表的任何资源,任何锁请求都是会被阻塞住的。
2.对一张2亿记录表删除 或 添加一个字段(null属性,无默认值)的时间是瞬间,如果在没有锁资源被别的会话占用的情况下。
3.select into from 语句新建表的开销是非常大的,尤其是在数据库“完全”故障恢复模型的情况下,2个字段2亿记录的时间是3-4个小时,并且生成大量的事务日志(和数据文件的大小差不多的,可能是十几G甚至更多)。如果是一个事务很高的表,那么那么几个小时的更新也会需要不少的时间去同步这些数据。
所以我的结论是:在发生这样操作时,性价比最高的操作方式是在非繁忙时段(比如通常的凌晨),通过应用程序把业务停止一小会时间(当然这需要你的应用程序在起初的设计上就具有这样的功能)。然后执行这个删除或则添加的瞬间操作,然后打开应用程序的功能。这样对于系统来说应该是开销最小的:)