签到提醒推送之MySQL分页优化

签到提醒推送之MySQL分页优化

需求背景

签到提醒功能，每天18点推送服务通知提醒用户签到。推送对象为昨日签到，截止推送前未签到的用户。

开发过程

数据库表结构：

CREATE TABLE `cultivate_game_signin` (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `uid` bigint(20) DEFAULT NULL COMMENT '用户id',
  `signin_time` bigint(20) DEFAULT NULL COMMENT '签到时间',
  `continue_times` int(11) DEFAULT NULL COMMENT '连续签到次数',
  `singnin_count` int(11) DEFAULT NULL COMMENT '签到次数',
  `activity_id` bigint(20) DEFAULT NULL COMMENT '活动id',
  `remind_status` tinyint(4) DEFAULT '0' COMMENT '0打开签到提醒，1关闭签到提醒',
  PRIMARY KEY (`id`),
  KEY `idx_uid` (`uid`),
  KEY `idx_time` (`signin_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='活动签到表' 

第一版开发：

select * from cultivate_game_signin where signin_time betweent '2018-1-13 00:00:00' and '2018-1-14 00:00:00' limit 0 , 100;

在定时任务中执行，直至遍历完所有数据。 第一版上线后基本实现了需求。但是在12月，一次观察job发现任务竟然执行了9个小时，执行到的翌日凌晨。在pushcode 防打扰策略中其实21点后的推送已经无效了，用户也不能查收。没有意义且浪费了资源。

第二版优化：

根据问题排查，由于用户量激增，目标推送用户达到了60W。为了增加用户留存，保住新增用户，所以推送优化箭在弦上。

优化过程

优化主要采取了两点：

• 线程池优化 设置合理的线程数，在保证服务稳定的情况下推送完毕。这里不多赘述。
• 推送接口优化 单单使用MySQL分页不必多说，偏移量持续增大势必造成接口性能下降，无法满足需求。

Limit分页优化：

推荐分页： 分页方式一： Select * from table WHERE id>=23423 limit 11; #10+1 (每页10条) Select * from table WHERE id>=23434 limit 11; 分页方式二： Select * from table WHERE id >= ( select id from table limit 10000,1 ) limit 10; 分页方式三： Select * from table INNER JOIN (SELECT id from table limit 10000,10) USING(id) 分页方式四： 程序取ID: Select id from table limit 10000,10; Select * from table WHERE ID in(123,456…);

不难看出，优化主要是在解决偏移量过大。

explain select * from cultivate_game_signin where signin_time between 0 and 1540443740869  limit 99,1;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	cultivate_game_signin	range	idx_time	idx_time	9	NULL	129940	Using where

扫描数量会越来越大造成性能衰减。所以，在推送优化中使用了id，前提是id有序。

改进后的SQL:

select * from cultivate_game_signin where id > id signin_time betweent begin_time and end_time limit 100;

将id作为参数，作为下个执行语句的查询条件。避免了大偏移量的产生。效果也是立竿见影，30分钟内可以推送完所有目标用户。

分页的指导思想，查询命中索引并且避免大的偏移量。

转载于:https://my.oschina.net/u/3418748/blog/3093825