[actual combat] query millions of data using multithreading optimization

preface

Pre preparation

Start test

First, make sure that there is data in the database. Due to the complexity of the actual business, the simulation of username here also makes it more complicated. It is not that the same data has been copied more than 6 million times

Single thread + basic SQL

The next step is the basic full table query method. The postman test is used here

@GetMapping("/sync")
public String getData() {
    List<User> list = userService.queryAllUseSync();
    return "查询成功！";
}


@Override
@RecordMethodSpendAnnotation //这个注解标记的方法会被SpringAOP管理起来，计算方法耗时
public List<User> queryAllUseSync() {

    //直接就采用Mybatis全查
    return userMapper.queryAll();
}

Let's take a look at the SQL of queryall. It can be found that it is a simple full table query

<select id="queryAll" resultMap="UserMap">
		select
		id,username,create_time
		from performance.user
</select>

Cause analysis

We directly grab the SQL and explain it. We can find that there is no index. The whole table has more than 600 W of data, and the local time-consuming (average for multiple tests): 67s