数据库cpu很高解决方案

问题


通过排查发现mysql的cpu和内存高是由于大量的性能差的垃圾sql导致的，由于签到查询量多
所以导致cpu高

修改方案
首先修改sql提高查询性能
修改前

select * from att_record where username='ding2' and address is not null and swipe_type = 1
            AND createtime BETWEEN '2022-09-02 00:00:00'
		AND '2022-09-02 23:59:59' 
       order by
        swipe_time limit 0,1

查询时间11秒
添加了address索引 和精简了查询列

select address from att_record where username='ding2' and address is not null and swipe_type = 1
            AND createtime BETWEEN '2022-09-02 00:00:00'
		AND '2022-09-02 23:59:59' 
        limit 0,1

耗时0.36

代码层面通过异步方式执行
由于子线程无法获取父线程的request对象中的参数
所以把参数读出来在传到线程池
线程池执行逻辑

        CompletableFuture voidCompletableFuture = CompletableFuture.runAsync(() -> {
        logger.info("线程号为***" + Thread.currentThread().getId()+"线程名称..."+Thread.currentThread().getName());
     //	签到逻辑
    }, threadPoolTaskExecutor);

@Configuration
public class PollConfig {
    @Bean
    public ThreadPoolTaskExecutor selfThreadPoolExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        //设置线程名称
        executor.setThreadNamePrefix("completableFuture--pool");
        //设置最大线程数
        executor.setMaxPoolSize(50);
        //设置核心线程数
        executor.setCorePoolSize(10);
        //设置线程空闲时间，默认60
        executor.setKeepAliveSeconds(60);
        //设置队列容量
        executor.setQueueCapacity(1000);

        // 设置拒绝策略
        executor.setRejectedExecutionHandler(new RejectedExecutionHandler() {
            @Override
            public void rejectedExecution(Runnable r, ThreadPoolExecutor executor) {
                // .....任务过多时，存入数据库，定时执行，还是...
            }
        });
        // 使用预定义的异常处理类
        // executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());

        return executor;
    }

}

//主类开启才可以
@EnableAsync

最后压测结果