丹阳网站建设咨询,百度品牌广告多少钱一个月,长春财经学院宿舍图片,鄂州第一官方网站Spring Boot MySQL: 多线程查询与联表查询性能对比分析
背景
在现代 Web 应用开发中#xff0c;数据库性能是影响系统响应时间和用户体验的关键因素之一。随着业务需求的不断增长#xff0c;单表查询和联表查询的效率问题日益凸显。特别是在 Spring Boot 项目中#xff0…Spring Boot MySQL: 多线程查询与联表查询性能对比分析
背景
在现代 Web 应用开发中数据库性能是影响系统响应时间和用户体验的关键因素之一。随着业务需求的不断增长单表查询和联表查询的效率问题日益凸显。特别是在 Spring Boot 项目中结合 MySQL 数据库进行复杂查询时如何优化查询性能已成为开发者必须面对的重要问题。
在本实验中我们使用了 Spring Boot 框架结合 MySQL 数据库进行了两种常见查询方式的性能对比多线程查询 和 联表查询。通过对比这两种查询方式的响应时间本文旨在探讨在实际业务场景中选择哪种方式能带来更高的查询效率尤其是在面对大数据量和复杂查询时的性能表现。 实验目的
本实验的主要目的是通过对比以下两种查询方式的性能帮助开发者选择在不同业务场景下的查询方式
联表查询使用 SQL 语句中的 LEFT JOIN 等连接操作多线程查询通过 Spring Boot 异步处理分批查询不同表的数据 实验环境 开发框架Spring Boot 数据库MySQL 数据库表结构 test_a主表包含与其他表test_b、test_c、test_d、test_e的关联字段。test_b、test_c、test_d、test_e附表分别包含不同的数据字段。 这些表通过外键(逻辑)关联test_a 表中的 test_b_id、test_c_id、test_d_id 和 test_e_id 字段指向各自的附表。 数据量约 100,000 条数据分别在主表和附表中填充数据。
一.建表语句
主表A
CREATE TABLE test_a (id int NOT NULL AUTO_INCREMENT,name varchar(255) NOT NULL,description varchar(255) DEFAULT NULL,test_b_id int DEFAULT NULL,test_c_id int DEFAULT NULL,test_d_id int DEFAULT NULL,test_e_id int DEFAULT NULL,created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT6 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;附表b,c,d,e
CREATE TABLE test_b (id int NOT NULL AUTO_INCREMENT,field_b1 varchar(255) DEFAULT NULL,field_b2 int DEFAULT NULL,created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT792843462 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;CREATE TABLE test_c (id int NOT NULL AUTO_INCREMENT,field_c1 varchar(255) DEFAULT NULL,field_c2 datetime DEFAULT NULL,created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT100096 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;CREATE TABLE test_d (id int NOT NULL AUTO_INCREMENT,field_d1 text,field_d2 tinyint(1) DEFAULT NULL,created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT100300 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;CREATE TABLE test_e (id int NOT NULL AUTO_INCREMENT,field_e1 int DEFAULT NULL,field_e2 varchar(255) DEFAULT NULL,created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT100444 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;二.填充数据
SpringBootTest
class DemoTestQuerySpringbootApplicationTests {Autowiredprivate TestAMapper testAMapper;Autowiredprivate TestBMapper testBMapper;Autowiredprivate TestCMapper testCMapper;Autowiredprivate TestDMapper testDMapper;Autowiredprivate TestEMapper testEMapper;Testvoid contextLoads() {// 随机数生成器Random random new Random();for (int i 1; i 100000; i) {// 插入 test_b 数据int testBId insertTestB(random);// 插入 test_c 数据int testCId insertTestC(random);// 插入 test_d 数据int testDId insertTestD(random);// 插入 test_e 数据int testEId insertTestE(random);// 插入 test_a 数据insertTestA(testBId, testCId, testDId, testEId, random);}}private int insertTestB(Random random) {TestB testB new TestB();testB.setFieldB1(B Field random.nextInt(1000));testB.setFieldB2(random.nextInt(1000));testBMapper.insert(testB); // 插入数据return testB.getId(); }private int insertTestC(Random random) {TestC testC new TestC();testC.setFieldC1(C Field random.nextInt(1000));testC.setFieldC2(new java.sql.Timestamp(System.currentTimeMillis()));testCMapper.insert(testC); // 插入数据return testC.getId(); }private int insertTestD(Random random) {TestD testD new TestD();testD.setFieldD1(D Field random.nextInt(1000));testD.setFieldD2(random.nextBoolean());testDMapper.insert(testD); // 插入数据return testD.getId(); }private int insertTestE(Random random) {TestE testE new TestE();testE.setFieldE1(random.nextInt(1000));testE.setFieldE2(E Field random.nextInt(1000));testEMapper.insert(testE); // 插入数据return testE.getId(); }private void insertTestA(int testBId, int testCId, int testDId, int testEId, Random random) {TestA testA new TestA();testA.setName(Test A Name random.nextInt(1000));testA.setDescription(Test A Description random.nextInt(1000));testA.setTestBId(testBId);testA.setTestCId(testCId);testA.setTestDId(testDId);testA.setTestEId(testEId);testAMapper.insert(testA); // 插入数据}}三.配置线程池
3.1配置
/*** 实现AsyncConfigurer接口* 并重写了 getAsyncExecutor方法* 这个方法返回 myExecutor()* Spring 默认会将 myExecutor 作为 Async 方法的线程池。*/
Configuration
EnableAsync
public class ThreadPoolConfig implements AsyncConfigurer {/*** 项目共用线程池*/public static final String TEST_QUERY testQuery;Overridepublic Executor getAsyncExecutor() {return myExecutor();}Bean(TEST_QUERY)Primarypublic ThreadPoolTaskExecutor myExecutor() {//spring的线程池ThreadPoolTaskExecutor executor new ThreadPoolTaskExecutor();//线程池优雅停机的关键executor.setWaitForTasksToCompleteOnShutdown(true);executor.setCorePoolSize(10);executor.setMaxPoolSize(10);executor.setQueueCapacity(200);executor.setThreadNamePrefix(my-executor-);//拒绝策略-满了调用线程执行认为重要任务executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());//自己就是一个线程工程executor.setThreadFactory(new MyThreadFactory(executor));executor.initialize();return executor;}}3.2异常处理
public class MyUncaughtExceptionHandler implements Thread.UncaughtExceptionHandler {private static final Logger log LoggerFactory.getLogger(MyUncaughtExceptionHandler.class);Overridepublic void uncaughtException(Thread t, Throwable e) {log.error(Exception in thread,e);}
}3.3线程工厂
AllArgsConstructor
public class MyThreadFactory implements ThreadFactory {private static final MyUncaughtExceptionHandler MyUncaughtExceptionHandler new MyUncaughtExceptionHandler();private ThreadFactory original;Overridepublic Thread newThread(Runnable r) {//执行Spring线程自己的创建逻辑Thread thread original.newThread(r);//我们自己额外的逻辑thread.setUncaughtExceptionHandler(MyUncaughtExceptionHandler);return thread;}
}四.Service查询方法
4.1left join连接查询 Overridepublic IPageTestAll getTestAllPage_1(int current, int size) {// 创建 Page 对象current 为当前页size 为每页大小PageTestAll page new Page(current, size);return testAMapper.selectAllWithPage(page);}对应的xml 的sql语句
?xml version1.0 encodingUTF-8?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespaceorg.fth.demotestqueryspringboot.com.test.mapper.TestAMapper!-- 基本的 ResultMap 映射 --resultMap idBaseResultMap typeorg.fth.demotestqueryspringboot.com.test.entity.vo.TestAllid columntest_a_id jdbcTypeINTEGER propertytestAId /result columnname jdbcTypeVARCHAR propertyname /result columndescription jdbcTypeVARCHAR propertydescription /result columntest_b_id jdbcTypeINTEGER propertytestBId /result columntest_c_id jdbcTypeINTEGER propertytestCId /result columntest_d_id jdbcTypeINTEGER propertytestDId /result columntest_e_id jdbcTypeINTEGER propertytestEId /result columncreated_at jdbcTypeTIMESTAMP propertycreatedAt /result columnupdated_at jdbcTypeTIMESTAMP propertyupdatedAt /!-- TestB --result columnfield_b1 jdbcTypeVARCHAR propertyfieldB1 /result columnfield_b2 jdbcTypeINTEGER propertyfieldB2 /result columntest_b_created_at jdbcTypeTIMESTAMP propertytestBCreatedAt /!-- TestC --result columnfield_c1 jdbcTypeVARCHAR propertyfieldC1 /result columnfield_c2 jdbcTypeTIMESTAMP propertyfieldC2 /result columntest_c_created_at jdbcTypeTIMESTAMP propertytestCCreatedAt /!-- TestD --result columnfield_d1 jdbcTypeVARCHAR propertyfieldD1 /result columnfield_d2 jdbcTypeBOOLEAN propertyfieldD2 /result columntest_d_created_at jdbcTypeTIMESTAMP propertytestDCreatedAt /!-- TestE --result columnfield_e1 jdbcTypeINTEGER propertyfieldE1 /result columnfield_e2 jdbcTypeVARCHAR propertyfieldE2 /result columntest_e_created_at jdbcTypeTIMESTAMP propertytestECreatedAt //resultMap!-- 分页查询 TestA 和其他表的数据 --select idselectAllWithPage resultMapBaseResultMapSELECTa.id AS test_a_id,a.name,a.description,a.test_b_id,a.test_c_id,a.test_d_id,a.test_e_id,a.created_at,a.updated_at,-- TestBb.field_b1,b.field_b2,b.created_at AS test_b_created_at,-- TestCc.field_c1,c.field_c2,c.created_at AS test_c_created_at,-- TestDd.field_d1,d.field_d2,d.created_at AS test_d_created_at,-- TestEe.field_e1,e.field_e2,e.created_at AS test_e_created_atFROM test_a aLEFT JOIN test_b b ON a.test_b_id b.idLEFT JOIN test_c c ON a.test_c_id c.idLEFT JOIN test_d d ON a.test_d_id d.idLEFT JOIN test_e e ON a.test_e_id e.id/select/mapper
4.2多线程查询 Overridepublic IPageTestAll getTestAllPage_2(int current, int size) {IPageTestA testAPage testAMapper.selectPage(new Page(current, size), null);ListTestA testAS testAPage.getRecords();CompletableFutureListTestB futureBs selectTestBids(testAS.stream().map(TestA::getTestBId).collect(Collectors.toSet()));CompletableFutureListTestC futureCs selectTestCids(testAS.stream().map(TestA::getTestCId).collect(Collectors.toSet()));CompletableFutureListTestD futureDs selectTestDids(testAS.stream().map(TestA::getTestDId).collect(Collectors.toSet()));CompletableFutureListTestE futureEs selectTestEids(testAS.stream().map(TestA::getTestEId).collect(Collectors.toSet()));// 等待所有异步任务完成并收集结果CompletableFutureVoid allFutures CompletableFuture.allOf(futureBs, futureCs, futureDs, futureEs);try {// 等待所有异步任务完成allFutures.get();} catch (InterruptedException | ExecutionException e) {e.printStackTrace();throw new RuntimeException(Failed to fetch data, e);}// 获取异步查询的结果ListTestB bs futureBs.join();ListTestC cs futureCs.join();ListTestD ds futureDs.join();ListTestE es futureEs.join();// 将结果映射到Map以便快速查找MapInteger, TestB bMap bs.stream().collect(Collectors.toMap(TestB::getId, b - b));MapInteger, TestC cMap cs.stream().collect(Collectors.toMap(TestC::getId, c - c));MapInteger, TestD dMap ds.stream().collect(Collectors.toMap(TestD::getId, d - d));MapInteger, TestE eMap es.stream().collect(Collectors.toMap(TestE::getId, e - e));ListTestAll testAllList testAS.stream().map(testA - {TestAll testAll new TestAll();testAll.setTestAId(testA.getId());testAll.setName(testA.getName());testAll.setDescription(testA.getDescription());testAll.setCreatedAt(testA.getCreatedAt());// 根据 testBId 填充 TestB 的字段if (testA.getTestBId() ! null) {TestB testB bMap.get(testA.getTestBId());if (testB ! null) {testAll.setFieldB1(testB.getFieldB1());testAll.setFieldB2(testB.getFieldB2());testAll.setTestBCreatedAt(testB.getCreatedAt());}}// 根据 testCId 填充 TestC 的字段if (testA.getTestCId() ! null) {TestC testC cMap.get(testA.getTestCId());if (testC ! null) {testAll.setFieldC1(testC.getFieldC1());testAll.setFieldC2(testC.getFieldC2());testAll.setTestCCreatedAt(testC.getCreatedAt());}}// 根据 testDId 填充 TestD 的字段if (testA.getTestDId() ! null) {TestD testD dMap.get(testA.getTestDId());if (testD ! null) {testAll.setFieldD1(testD.getFieldD1());testAll.setFieldD2(testD.getFieldD2());testAll.setTestDCreatedAt(testD.getCreatedAt());}}// 根据 testEId 填充 TestE 的字段if (testA.getTestEId() ! null) {TestE testE eMap.get(testA.getTestEId());if (testE ! null) {testAll.setFieldE1(testE.getFieldE1());testAll.setFieldE2(testE.getFieldE2());testAll.setTestECreatedAt(testE.getCreatedAt());}}return testAll;}).collect(Collectors.toList());// 创建并返回新的分页对象IPageTestAll page new Page(testAPage.getCurrent(), testAPage.getSize(), testAPage.getTotal());page.setRecords(testAllList);return page;}Asyncpublic CompletableFutureListTestB selectTestBids(SetInteger bids) {return CompletableFuture.supplyAsync(() - testBMapper.selectBatchIds(bids));}Asyncpublic CompletableFutureListTestC selectTestCids(SetInteger cids) {return CompletableFuture.supplyAsync(() - testCMapper.selectBatchIds(cids));}Asyncpublic CompletableFutureListTestD selectTestDids(SetInteger dids) {return CompletableFuture.supplyAsync(() - testDMapper.selectBatchIds(dids));}Asyncpublic CompletableFutureListTestE selectTestEids(SetInteger eids) {return CompletableFuture.supplyAsync(() - testEMapper.selectBatchIds(eids));}五.结果测试
5.1连接查询 查询结果表格
currentsize响应时间12016ms502023ms1002022ms5002052ms200200213ms500200517ms
5.2多线程查询 查询结果表格
currentsize响应时间12018ms502017ms1002017ms5002021ms20020056ms50020080ms
总结与建议
选择联表查询当数据量较小或者查询逻辑较为简单时使用联表查询可以更简单直接查询性能也较为优秀。选择多线程查询当面对大数据量或者复杂查询时采用多线程查询将带来更显著的性能提升。通过异步并行查询可以有效缩短响应时间提升系统的整体性能。
在实际开发中可以根据具体的业务需求和数据库的规模合理选择查询方式从而提高数据库查询效率优化系统性能
