Mysql 如何修改大数据表

请允许我先吐槽一下， 我在v2ex上至少花了两个小时，在一个亲爱的伙伴提醒下，才找到发帖按钮。 我以为发帖要钱， 我以为我没有权限， 我觉得这一点绝对可以吐槽一下。 你们不能把发帖按钮放在一个能看得见的地方？

吐槽完毕。


在mysql 里修改一个数据表， 包括： 修改字段的定义，删除一个字段， 增加一个字段， 广泛意义上来说， 要批量替换或者重新计算一个字段的值也算。 但本文以增加一个字段为例， 适用于修改字段定义， 不使用于删除字段，我有解释， 也不适合批量替换数据，这个很容易。

比如增加一个字段， SQL 为：

ALTER TABLE table ADD COLUMN field INT DEFAULT 0;

这样一条 SQL 在执行的时候， 如果数据表读写不是很忙，并且数据量在10万这个级别的情况下， 如果内存允许， 一般大概不需要1秒中

但如果上述的条件不成立， 即读写很忙， 数据量很大， 其中任何一个出来的时候， 想增加一个字段直接执行上面的SQL 就会看到数据库不动， 然后前端的网页也可能不动了。

这个事情在扇贝的历史上发生过几次。


第一次是数据量比较大， 那个时候我们的一个表数据量首次达到了1000万。 当时我们需要增加一个字段， 直接执行了SQL, 发现那条SQL 根本不动， 然后就停掉了， 发布公告，凌晨停机维护，停机时间是0点到5点。 停机后， 再次执行sql, 等了5分钟没有反应， 10分钟没有反应，一个小时还是没有反应， 最后我们就没办法，睡觉去了， 早上5点起来， 抱歉，还在执行， 这个时候就相当紧张了， 立即关掉了这条sql. 重启数据库，查了一下那张表， 1000万多一点。但天亮了， 服务继续。

还有一次， 一个数据表， 大概是300万条数据， 数据量不算特别大， 但这个表比较麻烦的是， 访问频繁，读写都很多， 我们当时觉得300万可以试试看， 然后就执行了上面的sql, 1分钟， 2分钟， 5分钟，掐断。

经过这两次事情后， 我们才真正开始寻找并实施大数据表在线迁移。

先列一些基本原则：


1. 能不加字段就不要加， 能不修改字段就不要修改， 能不删除字段就不要删除， 等等为什么要删除字段呢？ 如果没事，不要蛋疼的找事。 实际上，我们那次更新失败后， 我们并没有增加那个字段， 然后我们一直运行到今天， 但是后来还是增加了其他字段

2. 增加字段的情况下， 如果可以通过增加一个新的表来增加这个字段， 那么就增加一个新的表， 通过cache 或 程序来实现join 的效果

3. 如果能停机， 并且停机的时间在你容忍范围之内， 数据备份之后停机来做。 如果是主从备份，做这样大的操作的时候，停掉主从备份， 万一你挂了， 备份数据库可以救你。 等到一切安全了， 重启主从备份；

4. 如果上面都不行， 这个字段还是要改，要加， 需要用到下面的方法， 也是扇贝网正在使用的方法；


修改大数据表的方法：


1. 被修改的表 Table A 需要有一个记录时间戳的字段， 这个时间戳就是每次数据更新，都会更新的字段， 这个字段需要有索引，在django里可以使用 auto_now=True

2. 创建一个新的临时表 Table B， 不是tmp_table, 是一个新的表，但是是临时使用的。 这个表和要修改的表拥有一模一样的数据结构， 加上你要修改的部分， 比如增加的字段；

3. 记录下Table A 的索引

4. 删除 Table B 的全部索引

5. 把Table A 的数据全部复制到Table B, 是不是执行 INSERT INTO B(field1, field2) SELECT field1, field2 FROM A？ 当然不是， 这么做不还是锁死了Table A 么， 这里的迁移就是一个需要细分的地方，需要写一个脚本， 让程序每次读取比如5000条数据出来， 插入到Table B里面， 因为Table B 是没有索引的， 所以要当心不要使用多进程来做； 如果是多进程， 要确保插入到B的时候是不会有重复数据的； 如果是1000万的数据，每次5000条， 假设这个操作需要500ms， 那么 2000*200ms = 16 分钟。 这只是一个估值， 具体情况和服务器当时的情况有关， 不好细说。 另外， 我们要记录这个迁移开始的时间点，记为t1;

6. 那么这个时候Table A 的数据是不是都进入了Table B 呢， 应当说差不多大部分都进入了， 但5中说， 这大概需要16分钟， 这么长的时间里， 可能有新的数据进入了， 也有可能已有的数据发生了更新， 所以我们要把Table A 中在t1 之后发生变化的数据查找出来， 然后更新到Table B 中， 我们的做法是：

记录这个操作对应的时间点 t2
BEGIN;
DELETE FROM B WHERE updated_time > t1;
INSERT INTO B(field1, field2) SELECT field1, field2 FROM A WHERE updated_time >t1;
COMMIT;

7. 现在A 和 B 差不多該同步了吧？ 差不多了， 但是6 执行完之后， A仍然在写， 子子孙孙无穷尽也 ... , 但这个时候 A 和 B 的差异已经非常非常小了， 所以在下一步，我们在一个transaction 里执行下面的操作：

BEGIN;
DELETE FROM B WHERE updated_time > t2;
INSERT INTO B(field1, field2) SELECT field1, field2 FROM A WHERE updated_time >t2;

ALTER TABLE A RENAME TO C;
ALTER TABLE B RENAME TO A;
COMMIT;

8. Done

PS: 如果A表没有时间戳， 实际上没有先见之明的人大概不会想到要预留一个时间戳的字段， 那么几乎是没有办法快速比较两个表的差异的， 这个时候我选择的做法就是放弃修改的数据， 只管新的数据了， 所以只要把t1， t2 换成id 就可以了， 这样delete 语句也省略了， 因为没啥好delete的；

千万不要想着根据ID 来JOIN 然后更新B表的字段来补齐新的数据， 如果能把两个千万级别的表JOIN起来， 内存有多大呢？



9. 上面的解决方案是我们第二次尝试之后犯下了一个巨大的错误，这个错误导致网站瘫痪了大概20分钟， 如果你和我一样没有发现问题，那么这就是悲剧的地方。 问题就在于我是根据上面的PS来操作的， 然后B就华丽地变成了A. B 表至今身上是没有索引的， 立即悲剧。 所以应当在第5步之后按照A的索引为B建立索引， 待索引全部好了之后， 再继续6。 如果不是走PS这条路， 而是有时间戳的字段的话， 在6的时候会发现这个问题， 因为那条Delete 慢的超出想像， 会明白这里是有问题的

10. 新手， 请在本地练习之后， 再实际操作； 可以多操作几次， 写一个脚本，服务器上直接执行脚本.

最后， 扇贝网的数据已经不是这个千万这个级别了， 我们遇到的那个问题是我们在linode上的时候遇到了的， 数据搬迁回来之后，我们华丽地进入了亿级啦， 再变成亿之前，我又华丽地将它一分为多， 所以我们下下篇来跟大家分享django 下如何做sharding.

预告一下下篇：DB 的Table Schema 发生改变的情况下， 如何管理过期的缓存？