php开奖数据千万级如何更新,千万级表数据更新的需求

点击标题下「蓝色微信名」可快速关注

预计阅读时间：4分钟

今天的兄弟部门同事，和我讨论了一个问题，有关于数据更新的方案。

需求是：

一张含LOB字段的表，由于应用的需要，必须将表中某个字段，更新为统一的值，表数据量为3000万，大约200G的占用空间，平时这张表，有非常频繁的增删改操作。为了配合此操作，应用可以停2小时，如何做？

为了便于说明，创建一张测试表，包含两个NUMBER类型，一个CLOB类型，含有100万数据，其中A是主键，需要将字段C的值，都更新为1，

SQL> create table tbl_a(a number primary key, b clob, c number);

Table created.

SQL> select count(*) from tbl_a;

COUNT(*)

----------

1000000

方案1：

写一个java程序，多线程操作，使用update tbl_a set c=1 where a between ? and ?进行批量更新操作，?可以选择1和10000，一次更新10000条记录，commit提交一次。

针对这种方案，尽管update使用了主键字段，看似应该用索引，但由于需要回表，加上增删改频繁，有可能聚簇因子值很高，即索引组织顺序，和表中数据的顺序不一致，导致回表成本高，如下图，进而导致SQL消耗更高的成本，不会选择索引。而且由于多线程，很可能导致并行操作下，一些资源的争用，进而影响效率。

方案2：

写一个java程序，使用update tbl_a set c=1 where a=?，循环执行10000次，也就是执行10000条记录的update操作，commit一次。

单看一条语句，按主键进行更新，效率很高，对于需求来说，就是执行的次数问题，需要执行3000万次。

如果采用DML语句，实现需求，这种方案，算是一种可行的，可以尝试1万条、5万条、10万条，一次commit的效率，选择最合适的批量次数。

方案3：

上面两种方案，都是采用DML，针对这一需求，需要将3000万记录的C，统一改为1，是否有其他方法？

想到一种DDL的方法，使用CTAS创建备份表，但是需要注意，备份表只含有A和B，不包含C，然后采用alter table的方法，为备份表增加C，并且增加default 1属性，即默认值设为1，

SQL> create table tbl_a_k as select a, b from tbl_a;

Table created.

Elapsed: 00:00:02.18

SQL> alter table tbl_a_k add c number default 1 not null;

Table altered.

Elapsed: 00:00:00.15

由于这种DDL操作，不涉及回滚，不需要记录UNDO，所以执行速度上，非常快，上面100万数据，只需要2秒多，当然实际表比测试要复杂，但至少应该一个数量级，相比方案1和2，不同的是，要占据两倍的存储空间，毕竟新建了一张原表，另外，就是需要评估，C设置默认值，业务上的影响，如果逻辑操作，都会指定具体的字段，用的比较标准，就没什么问题。

针对以上方案，朋友们是否有其他的建议？对这几种方案，是否有自己看法？欢迎各位发言、指教！