背景
由于项目的新版本有大改动,需求是将一些旧表的数据转移到新表来,于是使用PHP写了数据脚本,对MySQL的数据进行读取,计算,转移,插入等处理,实现千万级别数据的计算和转移。这里面也遇到一些问题,这里做下总结:
需求
- 将几个旧表的数据拷到新表来,有些字段发生了变化,有些字段的值需要计算;
- 单表数据量到达千万级,而且线上有多个库,要对脚本进行必要的优化;
- 脚本执行时间控制在两个小时之内;
实现过程
1、分析表数据的关联性,整合脚本。
分析了几个表数据之间的联系,可以将一些有关联的表的数据放在一个数据脚本中,比如user表和user_detail表,这两个表的数据是有一些关联的,一些数据值的计算不用重复读取,减少脚本的计算操作,节约时间;
2、数据读取时,减少配置数据的加载操作,减少数据查询操作。
开始数据转移时,必要的配置数据必须在脚本开始时全部加载进来,不能在转移时用到再去进行查询,因为每次查询都是意味着消耗更多时间。当然这里有个前提是你的机器内存要够大,PHP的这种加载是比较消耗内存的。一个脚本运行起来,内存都要占了很多G,这种其实就是用空间换时间的做法。当然,当机器内存不够大,或者性能不够强时,还是先保证脚本的健壮性,再来考虑性能。
PHP可以使用set_time_limit ( 0 ); @ini_set(\'memory_limit\',\'2048M\');来指定脚本执行的最长时间和使用内存的最大值;
3、脚本处理数据时,需要分段分批处理。
我们在处理数据时,需要先读取出用户id,在根据id查询表的数据再做处理。就是我们的处理逻辑都是以用户id为基准,按照用户id做key,数据做value的方式,遍历数据。为了减少机器的负载,充分利用机器的性能,我们使用一个while循环,每次处理3000个用户的数据,转移完后再查询出3000个用户的数据,如此类推,直到所有数据处理完,也就是跳出while循环。
同时必须要保证数据的有效性,不然insert不进去。这里还遇到过一个问题,就是在使用PHP的函数批量insert数据时,有时一个sql语句数据量太多,会超过MySQL最大的insert限制,所以在insert之前,需要将需要插入的数据进行分段,也就是对数据进行隔断处理,分批插入。PHP中可以使用array_slice()对数组数据进行分段。
4、将多次MySQL处理集合在一次的commit上。
我们在一次循环中是使用了一次try-catch来监控一次操作,当某个数据处理有异常时,就直接抛出异常,保证每次处理数据的完整性。我们每次处理开始前,先开启一个事务,处理完成后会进行一次提交事务。为了节省时间,可以优化成:开启一个事务,在遍历了多个用户数据后,再进行一次提交,或者在遇到异常后也提交一次,这样可以保证数据完整,也可以减少多次commit db的操作,节约时间。
5、使用shell脚本整合每个PHP脚本,开启多个PHP进程。
因为我们处理一个库的数据要涉及到多个PHP脚本,可以使用shell来整合多个脚本,并且让其顺序执行。使用nohub命令不挂断地运行命令(后面再单独介绍这个linux命令)。根据机器的核数来开启多少个PHP进程,充分发挥机器的性能。
例子
比如执行一个PHP脚本,可以这样子:
进程1:php move_user.php a 0 10000 进程2:php move_user.php a 10000 20000 进程3:php move_user.php b 0 10000 进程4:php move_user.php b 10000 20000
这样表示使用PHP cli模式(命令模式)执行一个PHP脚本,对于进程1,a 表示是数据库a,0 和10000表示从用户id 0开始,执行到用户id 10000 结束,一个进程处理10000个用户数据。
进程2表示执行数据库a 10000 到20000的用户数据。
这样多个进程,可以同时执行多个库的数据,多个区段的用户数据,节省时间。
当然,每次处理多少个用户数据,每次开多少个进程,每次遍历多少数据,都是根据项目的业务数据,和机器的最大负载来综合处理,达到一个平衡状态。
总结
- 此次数据处理原本预期要一个小时,结果由于其他原因,后面花费了两个多小时,但整体都是在计划之内,所以是正常的;
- PHP和MySQL做数据交互,充分利用了PHP的性能后,瓶颈就在与MySQL更新和插入数据了,我们就是通过分段循环处理,分段提交事务来平衡了MySQL的瓶颈;
- MySQL单表数据太大,后面需要单独对这块进行优化,不然以后对数据进行更新和备份时,都要浪费大量的时间;
- 必须保证脚本逻辑没有问题,不然后面重跑就很蛋疼了。
相关阅读
PHP+MySQL实现海量数据导入导出的一些总结
---------------------------------------------------END----------------------------------------------------
欢迎关注我的公众号【phper的进阶之路】
不断更新各种技术心得,免费提供各种学习资源!