java查询数据库百万条数据,优化之:多线程+数据库

java百万查询语句优化

业务需求

今天去面试时hr问了个关于大量数据查询的问题。

面试官:“我们公司是做数据分析的,每次需要从数据库中查询100万条数据进行分析,该接口不能用分页(不限制具体怎么实现),请问怎么优化sql或者java代码呢??”

如果用普通查询需要5分多分钟才查询完毕,所以我们用索引加多线程来实现。

那我们就开始吧!GO!!GO!!

数据库设计

编写数据库字段

然后要生成100万条数据

image-20230606110030790

在数据库添加索引

image-20230606112107605

索引这个方面我还是不太了解,大家懂的可以优化索引

代码实现

java编写

controller类编写
package com.neu.controller;
 
import com.neu.mapper.UserMapper;
import com.neu.pojo.User;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import javax.annotation.Resource;
import java.util.*;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
 
/**
 * 用户查询多线程用户Controller
 * @author 薄荷蓝柠
 * @since 2023/6/6
 */
@Controller
public class ExecutorUtils {

   @Resource
   private UserMapper userMapper;

 
   // 一个线程最大处理数据量
   private static final int THREAD_COUNT_SIZE = 5000;



   @RequestMapping("Executor")
   public List<User> executeThreadPool() {

      //计算表总数
      Integer integer = userMapper.UserSum();

      //记录开始时间
      long start = System.currentTimeMillis();


      //new个和表总数一样长的ArrayList
      List<User> threadList=new ArrayList<>(integer);

      // 线程数,以5000条数据为一个线程,总数据大小除以5000,再加1
      int round = integer / THREAD_COUNT_SIZE + 1;

      //new一个临时储存List的Map,以线程名为k,用做list排序
      Map<Integer,ArrayList> temporaryMap = new HashMap<>(round);

      // 程序计数器
      final CountDownLatch count = new CountDownLatch(round);

      // 创建线程
      ExecutorService executor = Executors.newFixedThreadPool(round);

      // 分配数据
      for (int i = 0; i < round; i++) {
         //该线程的查询开始值
         int startLen = i * THREAD_COUNT_SIZE;
         int k = i + 1;
         executor.execute(new Runnable() {
            @Override
            public void run() {
               ArrayList<User> users = userMapper.subList(startLen);
               //把查出来的List放进临时Map
               temporaryMap.put(k,users);
               System.out.println("正在处理线程【" + k + "】的数据,数据大小为:" + users.size());
               // 计数器 -1(唤醒阻塞线程)
               count.countDown();
            }
         });
      }
      try {
         // 阻塞线程(主线程等待所有子线程 一起执行业务)
         count.await();
         //结束时间
         long end = System.currentTimeMillis();
         System.out.println("100万数据查询耗时:" + (end - start) + "ms");
         //通过循环遍历临时map,把map的值有序的放进List里
         temporaryMap.keySet().forEach(k->{
            threadList.addAll(temporaryMap.get(k));
         });
      } catch (Exception e) {
         e.printStackTrace();
      } finally {
         //清除临时map,释放内存
         temporaryMap.clear();
         // 终止线程池
         // 启动一次顺序关闭,执行以前提交的任务,但不接受新任务。若已经关闭,则调用没有其他作用。
         executor.shutdown();
      }
      //输出list的长度
      System.out.println("list长度为:"+threadList.size());
      return threadList;
   }
}
编写Mapper
package com.neu.mapper;

import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.annotations.*;

import com.neu.pojo.User;

/**
 * 用户查询多线程用户Controller
 * @author 薄荷蓝柠
 * @since 2023/6/6
 */
@Mapper
public interface UserMapper {
    
    /**
	 * 检索user表的长度
	 * @return 表长度
	 */
	@Select("SELECT count(id) as sum FROM sysuser")
	Integer UserSum();

     /**
	 * 检索user表的所有记录,
	 * SELECT * 无法使用 MySQL 优化器覆盖索引的优化(基于 MySQL 优化器的“覆盖索引”策略又是速度极快,效率极高,业界极为推荐的查询优化方式)
	 * @return 所有记录信息
	 */
	@Select("select id,account,password,name,job,rights,roleId,createUserId,createTime,updateUserId,updateTime,del,enable from sysuser LIMIT #{startLen},5000")
	ArrayList<User> subList(@Param("startLen") int startLen);
}

编写完成后我们测试一波–>

测试结果20秒内,比之前快了好多

模糊查询

模糊查询呢?

咱测试一下:

修改Mapper
package com.neu.mapper;

import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.annotations.*;

import com.neu.pojo.User;

/**
 * 用户查询多线程用户Controller
 * @author 薄荷蓝柠
 * @since 2023/6/6
 */
@Mapper
public interface UserMapper {
	
    
     /**
	 * 检索user表id包含有“0”的长度
	 * @return 表长度
	 */
	@Select("SELECT count(id) as sum FROM sysuser where id like concat('%',0,'%')")
	Integer UserSum();

     /**
	 * 检索user表id包含有“0”的所有记录
	 * SELECT * 无法使用 MySQL 优化器覆盖索引的优化(基于 MySQL 优化器的“覆盖索引”策略又是速度极快,效率极高,业界极为推荐的查询优化方式)
	 * @return 所有记录信息
	 */
	@Select("select id,account,password,name,job,rights,roleId,createUserId,createTime,updateUserId,updateTime,del,enable from sysuser  where id like concat('%',0,'%') LIMIT #{startLen},5000")
	ArrayList<User> subList(@Param("startLen") int startLen);
}

修改完成后我们再测试一波–>

image-20230606110806810

耗时5秒左右,可以满足业务需求

结束

目前基本的查询已经写完

看到这个文章的还可以对以下方面进行优化:

  1. 索引进行优化。
  2. 每个线程查询多少条数据最为合适??
  3. 如果配置有线程池可以使用:总条数/线程数==每个线程需要查询多少条数据。
  4. 进行代码优化,优化一些耗时的代码。
  • 8
    点赞
  • 78
    收藏
    觉得还不错? 一键收藏
  • 12
    评论
您好!对于查询数据库百万条数据的情况,使用多线程数据库分页查询是一种常见的优化策略。下面是一个基本的实现思路: 1. 多线程处理:将数据查询任务分配给多个线程并行执行,提高查询效率。可以使用Java的线程池来管理线程,例如使用`ExecutorService`和`Callable`接口。 2. 数据库分页查询:通过分页查询的方式减少单次查询的数据量,避免一次性查询大量数据导致性能问题。可以使用SQL语句中的`LIMIT`子句来实现分页查询,例如`SELECT * FROM table_name LIMIT offset, limit`,其中`offset`表示偏移量,`limit`表示每页查询的数据量。 下面是一个简单的示例代码: ```java import java.sql.*; import java.util.concurrent.*; public class DatabaseQuery { private static final int PAGE_SIZE = 100; // 每页查询的数据量 public static void main(String[] args) { ExecutorService threadPool = Executors.newFixedThreadPool(10); // 创建线程池 Connection connection = null; try { connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name", "username", "password"); int totalRows = getTotalRows(connection); // 获取数据总行数 int totalPages = (int) Math.ceil((double) totalRows / PAGE_SIZE); // 计算总页数 for (int page = 0; page < totalPages; page++) { int offset = page * PAGE_SIZE; threadPool.submit(new QueryTask(connection, offset)); } } catch (SQLException e) { e.printStackTrace(); } finally { if (connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } threadPool.shutdown(); } } private static int getTotalRows(Connection connection) throws SQLException { try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT COUNT(*) FROM table_name")) { resultSet.next(); return resultSet.getInt(1); } } private static class QueryTask implements Callable<Void> { private Connection connection; private int offset; public QueryTask(Connection connection, int offset) { this.connection = connection; this.offset = offset; } @Override public Void call() throws Exception { try (PreparedStatement statement = connection.prepareStatement("SELECT * FROM table_name LIMIT ?, ?")) { statement.setInt(1, offset); statement.setInt(2, PAGE_SIZE); ResultSet resultSet = statement.executeQuery(); // 处理查询结果 while (resultSet.next()) { // 处理每条数据 // ... } resultSet.close(); } return null; } } } ``` 以上示例代码仅供参考,具体的实现需要根据实际情况进行调整和优化。同时,请确保在使用多线程数据库查询时遵循相关的线程安全和数据库事务处理的规范。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 12
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值