SpringBoot+ MySQL多線程查詢與聯(lián)表查詢性能對(duì)比

更新時(shí)間：2025年05月16日 11:28:47 作者：sjsjsbbsbsn

本文主要介紹了SpringBoot+ MySQL多線程查詢與聯(lián)表查詢性能對(duì)比,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

背景

在現(xiàn)代 Web 應(yīng)用開(kāi)發(fā)中，數(shù)據(jù)庫(kù)性能是影響系統(tǒng)響應(yīng)時(shí)間和用戶體驗(yàn)的關(guān)鍵因素之一。隨著業(yè)務(wù)需求的不斷增長(zhǎng)，單表查詢和聯(lián)表查詢的效率問(wèn)題日益凸顯。特別是在 Spring Boot 項(xiàng)目中，結(jié)合 MySQL 數(shù)據(jù)庫(kù)進(jìn)行復(fù)雜查詢時(shí)，如何優(yōu)化查詢性能已成為開(kāi)發(fā)者必須面對(duì)的重要問(wèn)題。

在本實(shí)驗(yàn)中，我們使用了 Spring Boot 框架結(jié)合 MySQL 數(shù)據(jù)庫(kù)，進(jìn)行了兩種常見(jiàn)查詢方式的性能對(duì)比：多線程查詢 和 聯(lián)表查詢。通過(guò)對(duì)比這兩種查詢方式的響應(yīng)時(shí)間，本文旨在探討在實(shí)際業(yè)務(wù)場(chǎng)景中，選擇哪種方式能帶來(lái)更高的查詢效率，尤其是在面對(duì)大數(shù)據(jù)量和復(fù)雜查詢時(shí)的性能表現(xiàn)。

實(shí)驗(yàn)?zāi)康?/h2>
本實(shí)驗(yàn)的主要目的是通過(guò)對(duì)比以下兩種查詢方式的性能，幫助開(kāi)發(fā)者選擇在不同業(yè)務(wù)場(chǎng)景下的查詢方式：

聯(lián)表查詢（使用 SQL 語(yǔ)句中的 LEFT JOIN 等連接操作）
多線程查詢（通過(guò) Spring Boot 異步處理，分批查詢不同表的數(shù)據(jù)）

實(shí)驗(yàn)環(huán)境

開(kāi)發(fā)框架：Spring Boot
數(shù)據(jù)庫(kù)：MySQL
數(shù)據(jù)庫(kù)表結(jié)構(gòu)：
- test_a：主表，包含與其他表（test_b、test_c、test_d、test_e）的關(guān)聯(lián)字段。
- test_b、test_c、test_d、test_e：附表，分別包含不同的數(shù)據(jù)字段。
這些表通過(guò)外鍵(邏輯)關(guān)聯(lián)，test_a 表中的 test_b_id、test_c_id、test_d_id 和 test_e_id 字段指向各自的附表。
數(shù)據(jù)量：約 100,000 條數(shù)據(jù)，分別在主表和附表中填充數(shù)據(jù)。

一.建表語(yǔ)句

主表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`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_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`)
) ENGINE=InnoDB AUTO_INCREMENT=792843462 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_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`)
) ENGINE=InnoDB AUTO_INCREMENT=100096 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_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`)
) ENGINE=InnoDB AUTO_INCREMENT=100300 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_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`)
) ENGINE=InnoDB AUTO_INCREMENT=100444 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

二.填充數(shù)據(jù)

@SpringBootTest
class DemoTestQuerySpringbootApplicationTests {

    @Autowired
    private TestAMapper testAMapper;
    @Autowired
    private TestBMapper testBMapper;
    @Autowired
    private TestCMapper testCMapper;
    @Autowired
    private TestDMapper testDMapper;
    @Autowired
    private TestEMapper testEMapper;

    @Test
    void contextLoads() {
        // 隨機(jī)數(shù)生成器
        Random random = new Random();

        for (int i = 1; i <= 100000; i++) {
            // 插入 test_b 數(shù)據(jù)
            int testBId = insertTestB(random);

            // 插入 test_c 數(shù)據(jù)
            int testCId = insertTestC(random);

            // 插入 test_d 數(shù)據(jù)
            int testDId = insertTestD(random);

            // 插入 test_e 數(shù)據(jù)
            int testEId = insertTestE(random);

            // 插入 test_a 數(shù)據(jù)
            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);  // 插入數(shù)據(jù)
        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);  // 插入數(shù)據(jù)
        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);  // 插入數(shù)據(jù)
        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);  // 插入數(shù)據(jù)
        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);  // 插入數(shù)據(jù)
    }

}

三.配置線程池

3.1配置

/**
 * 實(shí)現(xiàn)AsyncConfigurer接口
 * 并重寫(xiě)了 getAsyncExecutor方法，
 * 這個(gè)方法返回 myExecutor()，
 * Spring 默認(rèn)會(huì)將 myExecutor 作為 @Async 方法的線程池。
 */
@Configuration
@EnableAsync
public class ThreadPoolConfig implements AsyncConfigurer {
    /**
     * 項(xiàng)目共用線程池
     */
    public static final String TEST_QUERY = "testQuery";


    @Override
    public Executor getAsyncExecutor() {
        return myExecutor();
    }

    @Bean(TEST_QUERY)
    @Primary
    public ThreadPoolTaskExecutor myExecutor() {
        //spring的線程池
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        //線程池優(yōu)雅停機(jī)的關(guān)鍵
        executor.setWaitForTasksToCompleteOnShutdown(true);
        executor.setCorePoolSize(10);
        executor.setMaxPoolSize(10);
        executor.setQueueCapacity(200);
        executor.setThreadNamePrefix("my-executor-");
        //拒絕策略->滿了調(diào)用線程執(zhí)行，認(rèn)為重要任務(wù)
        executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        //自己就是一個(gè)線程工程
        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);

    @Override
    public 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;

    @Override
    public Thread newThread(Runnable r) {
        //執(zhí)行Spring線程自己的創(chuàng)建邏輯
        Thread thread = original.newThread(r);
        //我們自己額外的邏輯
        thread.setUncaughtExceptionHandler(MyUncaughtExceptionHandler);
        return thread;
    }
}

四.Service查詢方法

4.1left join連接查詢

    @Override
    public IPage<TestAll> getTestAllPage_1(int current, int size) {
        // 創(chuàng)建 Page 對(duì)象，current 為當(dāng)前頁(yè)，size 為每頁(yè)大小
        Page<TestAll> page = new Page<>(current, size);
        return testAMapper.selectAllWithPage(page);
    }

對(duì)應(yīng)的xml 的sql語(yǔ)句

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.fth.demotestqueryspringboot.com.test.mapper.TestAMapper">

  <!-- 基本的 ResultMap 映射 -->
  <resultMap id="BaseResultMap" type="org.fth.demotestqueryspringboot.com.test.entity.vo.TestAll">
    <id column="test_a_id" jdbcType="INTEGER" property="testAId" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="description" jdbcType="VARCHAR" property="description" />
    <result column="test_b_id" jdbcType="INTEGER" property="testBId" />
    <result column="test_c_id" jdbcType="INTEGER" property="testCId" />
    <result column="test_d_id" jdbcType="INTEGER" property="testDId" />
    <result column="test_e_id" jdbcType="INTEGER" property="testEId" />
    <result column="created_at" jdbcType="TIMESTAMP" property="createdAt" />
    <result column="updated_at" jdbcType="TIMESTAMP" property="updatedAt" />
    <!-- TestB -->
    <result column="field_b1" jdbcType="VARCHAR" property="fieldB1" />
    <result column="field_b2" jdbcType="INTEGER" property="fieldB2" />
    <result column="test_b_created_at" jdbcType="TIMESTAMP" property="testBCreatedAt" />
    <!-- TestC -->
    <result column="field_c1" jdbcType="VARCHAR" property="fieldC1" />
    <result column="field_c2" jdbcType="TIMESTAMP" property="fieldC2" />
    <result column="test_c_created_at" jdbcType="TIMESTAMP" property="testCCreatedAt" />
    <!-- TestD -->
    <result column="field_d1" jdbcType="VARCHAR" property="fieldD1" />
    <result column="field_d2" jdbcType="BOOLEAN" property="fieldD2" />
    <result column="test_d_created_at" jdbcType="TIMESTAMP" property="testDCreatedAt" />
    <!-- TestE -->
    <result column="field_e1" jdbcType="INTEGER" property="fieldE1" />
    <result column="field_e2" jdbcType="VARCHAR" property="fieldE2" />
    <result column="test_e_created_at" jdbcType="TIMESTAMP" property="testECreatedAt" />
  </resultMap>

  <!-- 分頁(yè)查詢 TestA 和其他表的數(shù)據(jù) -->
  <select id="selectAllWithPage" resultMap="BaseResultMap">
    SELECT
    a.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,
    -- TestB
    b.field_b1,
    b.field_b2,
    b.created_at AS test_b_created_at,
    -- TestC
    c.field_c1,
    c.field_c2,
    c.created_at AS test_c_created_at,
    -- TestD
    d.field_d1,
    d.field_d2,
    d.created_at AS test_d_created_at,
    -- TestE
    e.field_e1,
    e.field_e2,
    e.created_at AS test_e_created_at
    FROM test_a a
    LEFT JOIN test_b b ON a.test_b_id = b.id
    LEFT JOIN test_c c ON a.test_c_id = c.id
    LEFT JOIN test_d d ON a.test_d_id = d.id
    LEFT JOIN test_e e ON a.test_e_id = e.id

  </select>

</mapper>

4.2多線程查詢

 @Override
    public IPage<TestAll> getTestAllPage_2(int current, int size) {
        IPage<TestA> testAPage = testAMapper.selectPage(new Page<>(current, size), null);
        List<TestA> testAS = testAPage.getRecords();
        CompletableFuture<List<TestB>> futureBs = selectTestBids(testAS.stream().map(TestA::getTestBId).collect(Collectors.toSet()));
        CompletableFuture<List<TestC>> futureCs = selectTestCids(testAS.stream().map(TestA::getTestCId).collect(Collectors.toSet()));
        CompletableFuture<List<TestD>> futureDs = selectTestDids(testAS.stream().map(TestA::getTestDId).collect(Collectors.toSet()));
        CompletableFuture<List<TestE>> futureEs = selectTestEids(testAS.stream().map(TestA::getTestEId).collect(Collectors.toSet()));
        // 等待所有異步任務(wù)完成并收集結(jié)果
        CompletableFuture<Void> allFutures = CompletableFuture.allOf(futureBs, futureCs, futureDs, futureEs);

        try {
            // 等待所有異步任務(wù)完成
            allFutures.get();
        } catch (InterruptedException | ExecutionException e) {
            e.printStackTrace();
            throw new RuntimeException("Failed to fetch data", e);
        }
        // 獲取異步查詢的結(jié)果
        List<TestB> bs = futureBs.join();
        List<TestC> cs = futureCs.join();
        List<TestD> ds = futureDs.join();
        List<TestE> es = futureEs.join();

        // 將結(jié)果映射到Map以便快速查找
        Map<Integer, TestB> bMap = bs.stream().collect(Collectors.toMap(TestB::getId, b -> b));
        Map<Integer, TestC> cMap = cs.stream().collect(Collectors.toMap(TestC::getId, c -> c));
        Map<Integer, TestD> dMap = ds.stream().collect(Collectors.toMap(TestD::getId, d -> d));
        Map<Integer, TestE> eMap = es.stream().collect(Collectors.toMap(TestE::getId, e -> e));
        List<TestAll> 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());

            // 根據(jù) 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());
                }
            }

            // 根據(jù) 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());
                }
            }

            // 根據(jù) 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());
                }
            }

            // 根據(jù) 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());

        // 創(chuàng)建并返回新的分頁(yè)對(duì)象
        IPage<TestAll> page = new Page<>(testAPage.getCurrent(), testAPage.getSize(), testAPage.getTotal());
        page.setRecords(testAllList);
        return page;
    }


    @Async
    public CompletableFuture<List<TestB>> selectTestBids(Set<Integer> bids) {
        return CompletableFuture.supplyAsync(() -> testBMapper.selectBatchIds(bids));
    }

    @Async
    public CompletableFuture<List<TestC>> selectTestCids(Set<Integer> cids) {
        return CompletableFuture.supplyAsync(() -> testCMapper.selectBatchIds(cids));
    }

    @Async
    public CompletableFuture<List<TestD>> selectTestDids(Set<Integer> dids) {
        return CompletableFuture.supplyAsync(() -> testDMapper.selectBatchIds(dids));
    }

    @Async
    public CompletableFuture<List<TestE>> selectTestEids(Set<Integer> eids) {
        return CompletableFuture.supplyAsync(() -> testEMapper.selectBatchIds(eids));
    }

五.結(jié)果測(cè)試

5.1連接查詢

在這里插入圖片描述

查詢結(jié)果表格

current	size	響應(yīng)時(shí)間
1	20	16ms
50	20	23ms
100	20	22ms
500	20	52ms
200	200	213ms
500	200	517ms

5.2多線程查詢

在這里插入圖片描述

查詢結(jié)果表格

current	size	響應(yīng)時(shí)間
1	20	18ms
50	20	17ms
100	20	17ms
500	20	21ms
200	200	56ms
500	200	80ms

總結(jié)與建議

選擇聯(lián)表查詢：當(dāng)數(shù)據(jù)量較小，或者查詢邏輯較為簡(jiǎn)單時(shí)，使用聯(lián)表查詢可以更簡(jiǎn)單直接，查詢性能也較為優(yōu)秀。
選擇多線程查詢：當(dāng)面對(duì)大數(shù)據(jù)量或者復(fù)雜查詢時(shí)，采用多線程查詢將帶來(lái)更顯著的性能提升。通過(guò)異步并行查詢，可以有效縮短響應(yīng)時(shí)間，提升系統(tǒng)的整體性能。

在實(shí)際開(kāi)發(fā)中，可以根據(jù)具體的業(yè)務(wù)需求和數(shù)據(jù)庫(kù)的規(guī)模，合理選擇查詢方式，從而提高數(shù)據(jù)庫(kù)查詢效率，優(yōu)化系統(tǒng)性能

到此這篇關(guān)于SpringBoot+ MySQL多線程查詢與聯(lián)表查詢性能對(duì)比的文章就介紹到這了,更多相關(guān)SpringBoot+ MySQL多線程查詢與聯(lián)表查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: