这个定时任务，从3min优化到200ms。老板，我尽力了！

 

近期，数据中心系统负荷大，mysql服务器的CPU动辄高达90%以上。代码和数据表存在很大优化空间。

 

这里分享一个定时任务批量处理数据的优化过程。

 

先介绍定时任务

先介绍下面2张数据表

	字段	数据量
platform_order 平台交易订单表	包括 主键自增id、客户id、客户名称（冗余字段）、服务商id（levy_id）、服务商名称（levy_name，冗余字段）、 付款方式、付款状态、收款人、收款人收款账号（卡号/支付宝/微信）、项目id、付款金额、渠道商、销售代表、 创建时间、最近更新时间、付款完成时间，等等。	550w，每天增量3w 宽表，有多达54个字段。
levy_info 服务商信息表	字段包括 服务商id（levy_id）、服务商名称（levy_name），等若干字段	50条，基础信息表，很少新增

 

 

项目程序里有一个定时任务，每间隔5分钟，定期为platform_order的冗余字段levy_name赋值。也就是，根据levy表里的信息来更新platform_order表。

 

最原始的程序实现

我相信这是绝大多数程序员的实现方式。

 

【第一步】求count：  select count(1) from platform_order where levy_name is null 

【第二步】分页从数据表获取 levy_name is null 的记录，例如每页1000条，放到List集合里。

【第三步】遍历List集合里的元素，根据记录的levy_id去查levy_info表，得到levy_name，执行SQL： update platform_order set levy_name=#{levy_name} where id=#{id} 

 

这个定时任务启动后，不停刷日志，耗时≈3min

改进后的程序实现

【第一步】求count：SQL同上

【第二步】如果count>0，则执行一条update语句： update platform_order a join levy_info b on a.levy_id=b.levy_id set a.levy_name=b.levy_name where a.levy_name is null 

 

这个实现方式，java着实少了许多行代码，不过，数据库倒是出现慢sql了。 count耗时≈2s  +  update语句耗时10~12s  =  整个job耗时≈15s 。

 

洪荒之力，优化到200ms以内

【第一步】

不再是傻瓜式地一个  levy_name is null  条件了。而是再加一个 id>#{maxId} 条件。 maxId 值从哪里来？每次定时任务执行完后将最大记录id缓存起来。当然，服务启动后第一次是没有缓存的，就让maxId=0。

再者，执行的sql不是简单的count，而是 select levy_id, min(id) as minId，max(id) as maxId from platform_order where id>#{maxId} and levy_name is null group by levy_id

【第二步】

上面的分组查询得到一个List集合，遍历集合元素，同样根据levy_id查levy_info表得到levy_info记录。

然后，如果你跟得上我的节奏，你应该能猜到，执行这样一个SQL：

update platform_order set levy_name=#{levy_name} where levy_id=#{levy_id} and id between #{minId} and #{maxId} and levy_name is null

顺便说一嘴，根据levy_id获取levy_info记录，我使用了缓存，缓存24h，豪横吧~

【第三步】 

缓存最大id  ---> maxId

 

经过这个性能优化之后，job的耗时在100ms~200ms之间，这个耗时足可以令伙伴们尖叫！

 

related MySql团队开发规范

11）单表字段数不要太多，建议最多不要大于50个。过度的宽表对性能也是很大的影响。
12）MySQL在处理大表时，性能就开始明显降低，所以建议单表物理大小限制在16GB，表中数据行数控制在2000W内。
业内的规则是超过2000W性能开始明显降低。但是这个值是灵活的，你可以根据实际情况进行测试来判断，比如阿里的标准就是500W，百度的确是2000W。实际上是否宽表，单行数据所占用的空间都有起到作用的。
13）如果数据量或数据增长在前期规划时就较大，那么在设计评审时就应加入分表策略，后续会有专门的文章来分析数据拆分的做法：垂直拆分（垂直分库和垂直分表）、水平拆分（分库分表和库内分表）。