如何更新7000万的数据，你会了吗？

点击蓝色“有关SQL”关注我哟

加个“星标”，天天与10000人一起快乐成长

前两天，我们微信群的小姐姐提出一个非常好的SQL问题。

更新7000万数据表的一个字段，最好的方法是什么？

这个问题，是我的面试必考题，占总分值的90%. 从 128位历史面试者的回答来看，足够筛掉很多上来就要30K的5年+程序员，甚至很多10年+。

非常简单的一个需求，考查的并不是一个简单的SQL功底，而是对整个数据库体系的理解。注意，如果是老读者，你们对体系两字，印象应该非常深刻。

围绕这个话题，微信群炸了。似乎，每次只要小姐姐提问，讨论都异常热烈，比我发红包还燥。我百思不得其解，谁来回答我？

一个人，可以走得很快。一群人，才可以走得更远。

同样解决一个问题，你想到的方法是A和B。其他积极思考的人，一定还能想到C,D，甚至E,F。人越多，方法就越多。你就不想知道，谁的方法更好用？

所以，我积极组织与参加社群，要的就是与这些肯积极思考的人，一起想办法解决更多的问题，从而磨炼自己的思考方式。同时也让自己的错题本，越来越厚，越来越抗打。

于是，好玩的事情，层出不穷。就好比上面这个题，如果没有你们这些可爱水友的智慧，就不会讨论的那么深入。

一开始，大家想的方法，都很随意。简直就是在做入门的SQL题，直接update,merge, 甚至调用cursor. 给出这样方法的朋友，我猜大部分是刚入行的帅哥靓妹，还停留在正确使用SQL命令语法的阶段。

那么回答 update, merge, cursor 为什么不对呢？归根结底，还是对体系的掌握不够到位，缺乏对数据库日志，事务管理，存储引擎等的理解。

大量的数据更新，会造成对表的锁定，这样其他人就不能用表了。数据库的操作，会先记录日志，一个大事务，能将日志撑爆，然后整个数据库服务器就炸了。

试想，你家就一台电视机，世界杯上赛的时候，你老婆非霸占着看某凡的演唱会，你有多郁闷？！而且这场演唱会竟然耗时2个半小时，想死的心都有了，是不是？

更优化的方法，群里也讨论到了。我们可以批量跑，一次跑1万条或者5万条，在每次跑批结束时，稍加停顿，停1-2秒钟，然后继续下一轮。

嗯，听上去，很人性化。自己憋屈点，每次小批量的跑，不占用太多服务器资源，也不给日志文件带来短时间的膨胀，似乎已经为每个人，每类压力资源都考虑到了。

简直完美，那还有没有提高的空间呢？当然有。这里的缺点，也很显然，自己委屈了。7000万条数据，1万一次，跑7000次，每次就算1秒，加上停顿了1秒，这样就需要4个小时，才能处理完。

那实际跑下来，是不是能如期跑完呢，实际上，随着越来越多的数据被更新，维护的索引也会越来越复杂，越到后面，更新1万条数据就不再是1秒一次了。要等更新完数据，遥遥无期。

那做DBA的朋友，会怎么看待这个问题？

首先，肯定是表结构设计的问题。对于这类容易暴涨数据的表，设计前第一个事情，是做好分区管理。按照年，月，日去做分区，分区粒度把握好。分区的目的，可以提供多线程处理。这个特性很重要，分区是可以秒切的，无论多少数据，即使2亿数据，也就1秒。这样100个分区，切出100个小表来，用多线程处理完了，再切回来。

关于秒切分区，看这里：谈谈表分区

更新前， 先 checkpoint 完所有新鲜的日志，完成差异备份或日志备份。

预处理步骤完毕，接着把数据库备份模式改成simple模式，使其日志文件，可以循环利用。这样解决了日志暴涨带来的空间不够用的后顾之忧。接着丢弃其他暂时无用的表索引，只保留更新用到的条件判断索引。最后执行批量更新操作。

数据全部更新完毕之后，立即执行差异备份或日志备份，以保证其他更新都安全保存。重建其他索引，恢复数据库的Full模式。

至此，完成更新。

当然，代码怎么写呢，是不是简简单单像下面这样呢：

WHILE(True)
BEGIN 
    UDPATE TOP(10000) TAB_A 
        SET COL_A = 'XXX'
    WHERE COL_A IS NULL 
    
    WAIT FOR 1
    IF @@ROWCOUNT = 0 
        BREAK 
END 

@@ROWCOUNT代表上条语句影响的记录行数 本例用的是伪代码

我100%的告诉大家，肯定不是。这样的设计，逻辑是没错的，但程序是死的。不够灵活。假如服务器足够空闲，你会满意一次处理1万条吗。答案是，显然不会。这就体现你SQL功底了。

真正的功底，考察灵活而巧妙的设计，洞察你对业务世界的数据，抽象建模的能力。

我一直认为，程序精妙处，在于迭代，在于你不断去反思，如何让你的程序跑得更好，更快，更稳。而大部分的程序员，仅仅满足于，把数据跑完，就拉倒。这样的10年经验，真的和一年没啥区别，无非你copy和paste的手速，会比其他人快点。

看到这里的同学，肯定被我吊足了胃口，那么正确答案是什么呢？抱歉，我不能给。

情景总是在不断的进化，每个特殊案例都有自己的特殊处理技巧，这世界没有银弹，一劳永逸地解决你将面临的所有问题。

解决眼前的问题，不要仅局限于一种方法。想出一个暂时的解决方法是急事，但拼命去想出，第二种，第三种方法是要事，以后能走多远，往往取决于要事，这些需要沉淀你思考的要事，才能给你更多力量。

--完--

往期精彩：

本号精华合集（三）

如何写好 5000 行的 SQL 代码

如何提高阅读 SQL 源代码的快感

我在面试数据库工程师候选人时，常问的一些题

零基础 SQL 数据库小白，从入门到精通的学习路线与书单