一次优化记录

背景

业务：新人优惠券生效当天/快过期（3天）时给用户做提醒，
时间：每天十点
定时：个人更加熟悉xxl-job。但目前项目用的quartz。所以用quartz实现。
基本思路：先查所有用户优惠券实体。是否时生效当天或者是否快过期判断放到java业务里而不是sql里
业务表：
优惠券用户表数据量：1200W数据且持续增长。暂未作分区表和数据归档（后面会做）。未记录优惠券生效等状态 至于生效时间，失效时间，领取时间等。索引:优惠券id,创建时间
优惠券表：数量较少 

备注：由于隐私 部分使用了伪代码，伪SQL

初始方案

直接查
十点查全部 select * from ‘用户优惠券表’ where 优惠券id in (select id from 优惠券表 where 限制=新用户 and 90天内)  总时间40+秒

这里用exlpain分析 优惠券id是有索引的，但是实际上没有走索引。是因为数据量过大。mysql会认为全表扫描比走索引效率高，就放弃索引走了全表扫描。第一次优化主要从这方面入手

第一次优化

先查询新人优惠券ids，并对ids进行分组，再到优惠券用户表查数据

1：select id from 优惠券表 where 领取限制=新用户 取ids

2：for(int i=1;;i++){
    res=select * from 用户优惠券表 where 优惠券id in idlist i and 创建时间<90天;
    baseRes.add(res)
}
符合条件的结果list=baseRes.stream.map().filter()..collect(Collects.toList)取userId的list

这里的思路是减少每次查询的数据量，希望可以命中索引。多次跑这块代码实际结果用70-100多秒不等。比第一次还要慢。事后分析结果如下：

优惠每个优惠券的分布是不均的。可能有的多有的少。所以循环过程中。如果此次的优惠券组数量较少则会走索引。若数据量较大，还是会全表扫描。甚至可能出现全表扫描多次的情况。所以时间会更长。 于是第二次优化希望去找稳定的索引。

第二次优化

第二次优化从执行时间上进行优化，既然SQL执行太久 可以用延时来做。让业务低峰期去跑慢SQL。于是在凌晨5点执行任务。跑完SQL后 丢到ScheduledExecutorSercice里延时5小时跑。伪代码：

set.forEach(o->{
    ThreadUtil.PUSH_SERVICE.schedule(new Runnable() {
        @Override
        public void run() {
             User user=selectUserByUserId(o);
             kafka.send(topic,user)
        }
    },5, TimeUnit.HOURS);
});

这样做基本可以避免业务对高峰期线上环境的影响。凌晨去跑一个几十秒的SQL也是可以接受的方案。但是总感觉还有不少可以优化的空间。思考一晚后，第二天有了第三次优化。

第三次优化

因为业务只需要查进90天的优惠券。而数据库的主键是自增的。所以可以通过主键来确定范围。

1：select min(id) from 优惠券用户表 where 时间>DATEADD(CURRDATE,INTERVAL -90 DAY) 得到minId

2: select id from 优惠券表 where 领取限制=新用户 取ids

3：select * from '用户优惠券表' where id >minId and 优惠券id in (ids)

explain 分析：

possible_keys:主键id,优惠券id

key:主键id

这里显示可能会走的索引用到了主键id，以及优惠券id两个。但是实际用到的索引是主键索引。说明走优惠券id索引的话 。数据量过大。不如走全表扫描 返回数据100W左右。查询时间45ms。数据传输时间5秒+。

第四次优化

1：对第三次结果还算满意，本地运行的时候把五小时调成了5秒钟。发现定时任务到执行时间时。控制台连续打印sql。发现

set.forEach(o->{
    ThreadUtil.PUSH_SERVICE.schedule(new Runnable() {
        @Override
        public void run() {
            User user=selectUserByUserId(o);
            kafka.send(topic,user)
        }
    },5, TimeUnit.HOURS);
});

中 把用户查询也放到了线程池里。导致在十点时，数据库访问量也会很大。就把用户查询提到了线程池外，凌晨去执行 如下：

set.forEach(o->{

    User user=selectUserByUserId(o);
    ThreadUtil.PUSH_SERVICE.schedule(new Runnable() {
        @Override
        public void run() {
            kafka.send(topic,user)
        }
    },5, TimeUnit.HOURS);
});

这样的话，十点执行的任务就只有一个消息推送了。