对大量数据的update优化操作

3/7/2017来源:SQL技巧人气:4838

最近做的项目,上线后出了不少问题,所以我就经常去查数据库数据,翻各种日志等,但是在查询的时候发现好蛋疼,有些地方的时间是写 “2016-08-11 20:13:02”这种格式,有些地方的时间是写 “1470917582000”这种格式,然后多张表数据进行对比的时候就很蛋疼,我得不停进行时间转换,恶心得不行。最后实在忍不了,我要统一时间格式 (由于时间都是单独一个字段记录,所以比较好处理),两种格式,很明显,对于查数据来说,第一种格式更直观,所以就决定了,将数据库日志类、邮件类的表的 时间,统一成”2016-08-11 20:13:02”这种格式。

想了一下,没法单纯用MySQL语句直转换,于是就自己写了一个脚本来转换时间格式(新增的很好处理,主要问题是要将之前记录的数据转换一次,在服务器维护的时候,进行处理)。

一开始想法很简单(很少用mysql,使用的时候也都是简单使用而已),就是把数据库数据全部select出来,然后一条条update,本地测试,没啥 问题。后面就把外网的玩家部分数据导入进来,进行测试。不导不知道,一导吓一跳,内存爆了,哈哈,外网数据量太大,根本就没法让我一下子全select出 来。那咋办呢,想了一下,其实我并不需要那么多数据,对每条记录,我只要select出key和time就行了,说干就干,试了一下,好像不会爆了,但是 发现他的执行速度超级慢,一百万条记录,跑了五六个小时,这还只是部分数据啊,要是是全部数据,那得跑多久啊,停服维护这么久,还不被骂死。没办法,那就 优化吧。

首先想到的是,一条一条更新的速度太慢了,然后就想批量更新,一次更新N条数据。实践是检验真理的唯一标准,不一会儿,代码就敲完了,重新试了一下,效果 依旧不理想。啊哦,真是要崩溃!后面又想到了利用异步,我一下子开多个mysql连接,同时处理,可是依旧慢的一笔。然后就放弃了,更新的效率肯定是满足 不了了。然后就想着绕弯子了,我新建一张表,把旧表数据取出来,处理完后,直接插入到新表,然后在把旧表删除,把新表重新命名成旧表的名字。想 想,insert into的速度应该会比update快不少。但是要把旧表数据全部取出来,内存会爆掉,所以我就用慢慢取的方式, select+limit。试了一下,速度快了不少,但是感觉也得跑个一个多小时。虽然快了很多,但是这速度肯定还是不行。

然后试了下,select的时候用select + where + order by + limit的方法,where和order by都是用time去处理,跑了一下,十来分钟就搞定了。后面想想,where和order by的时候用表的主键去处理,应该会快点,然后就试了一下,哇塞,效果很明显,select + where + order by + limit的方法(用主键去处理where 和order by) ,一分钟就处理完了所有数据。取一次数据,处理完数据后,插入一次数据,可以根据自己的数据量大小,适当调整一次要select多少条数据出来。记住,select出来后,处理完,一次性插入新表,不要一条条插入!

最终结果,用select + where + order by + limit的方法(用主键去处理where 和order by) ,一分钟左右就处理完了五百万条左右的数据,有好几张表,其中有两张表都各有一两百万条的数据,而最初的那种逐条update的方法,预计得十个小时以 上。虽然几经波折,最后终于放心地交差了。

小插曲: 创建新表的时候,我是用复制旧表结构的方式去创建的,复制旧表结构的时候,记得使用create table newTableName like oldTableName的方式,不然会没有把旧表的索引复制过来