游标性能测试

package com.report.common.database;

import com.alibaba.druid.pool.DruidDataSource;
import com.report.common.utils.database.DbFactory;
import com.report.common.utils.database.DriverUtil;
import java.sql.*;
import java.util.concurrent.TimeUnit;

public class DruidDataSourcePerformanceTest {
    // 数据库配置（需修改为实际值）
    private static final String BASE_URL = "jdbc:mysql://localhost:3306/edu";
    private static final String USER = "root";
    private static final String PASSWORD = "123456";
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String TEST_SQL = "SELECT * FROM testbigdata";

    // 两个独立数据源
    private DruidDataSource normalDataSource;  // 普通查询
    private DruidDataSource cursorDataSource;  // 游标查询

    public void setup() {
        // 使用原有工厂方法创建两个独立数据源
        normalDataSource = createDataSource(false);  // 不启用游标
        cursorDataSource = createDataSource(true);   // 启用游标
    }

    private DruidDataSource createDataSource(boolean useCursor) {
        DriverUtil driverUtil = new DriverUtil();
        driverUtil.setDbId("testDb_" + (useCursor ? "cursor" : "normal"));
        driverUtil.setDriverName(DRIVER);

        // 动态构建URL
        String url = BASE_URL
                + "?useSSL=false"
                + "&serverTimezone=UTC"
                + "&rewriteBatchedStatements=true"
                + (useCursor ? "&useCursorFetch=true" : "");

        driverUtil.setUrl(url);
        driverUtil.setUserName(USER);
        driverUtil.setPwd(PASSWORD);

        return DbFactory.getDruidDataSource(driverUtil);
    }

    public void comparePerformance() {
        // 预热（消除JVM冷启动影响）
        warmup();

        // 正式测试（5轮取平均）
        final int TEST_ROUNDS = 5;
        long totalNormal = 0, totalCursor = 0;

        for (int i = 1; i <= TEST_ROUNDS; i++) {
            System.out.printf("\n=== 第 %d 轮测试 ===\n", i);

            long normalTime = testQuery(normalDataSource, "普通查询");
            totalNormal += normalTime;

            long cursorTime = testQuery(cursorDataSource, "游标查询");
            totalCursor += cursorTime;

            sleep(1);  // 测试间隔
        }

        // 输出结果
        printResult("普通查询", totalNormal, TEST_ROUNDS);
        printResult("游标查询", totalCursor, TEST_ROUNDS);
        printImprovement(totalNormal, totalCursor, TEST_ROUNDS);
    }

    private long testQuery(DruidDataSource dataSource, String testName) {
        long start = System.nanoTime();
        int rowCount = 0;

        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(TEST_SQL)) {

            conn.setAutoCommit(false);

            // 根据数据源类型设置fetchSize
            if (dataSource == normalDataSource) {
                //stmt.setFetchSize(Integer.MIN_VALUE);  // 流式读取
            } else {
                stmt.setFetchSize(Integer.MIN_VALUE);              // 游标分页
            }

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    // 仅读取数据不处理（保持测试公平性）
                    rowCount++;
                    rs.getInt(1);     // row_id
                    rs.getString(2);  // name
                    rs.getString(3);  // medicalRecordNumber
                    rs.getString(4);  // content
                    rs.getInt(5);     // id
                }
            }

        } catch (SQLException e) {
            handleError(testName, e);
            return -1;
        }

        long duration = TimeUnit.NANOSECONDS.toMillis(System.nanoTime() - start);
        System.out.printf("[%s] 读取 %d 行，耗时: %d ms\n", testName, rowCount, duration);
        return duration;
    }

    private void warmup() {
        System.out.println(">> 预热开始");
        for (int i = 0; i < 3; i++) {
            testQuery(normalDataSource, "预热-普通查询");
            testQuery(cursorDataSource, "预热-游标查询");
        }
        sleep(2);
        System.out.println(">> 预热完成\n");
    }

    private void sleep(int seconds) {
        try {
            TimeUnit.SECONDS.sleep(seconds);
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        }
    }

    private void handleError(String testName, SQLException e) {
        System.err.printf("[%s] 测试异常: %s\n", testName, e.getMessage());
        e.printStackTrace();
    }

    private void printResult(String testName, long totalTime, int rounds) {
        double avg = totalTime / (double) rounds;
        System.out.printf("%s => 总耗时: %dms | 平均: %.2fms\n",
                testName, totalTime, avg);
    }

    private void printImprovement(long totalNormal, long totalCursor, int rounds) {
        double avgNormal = totalNormal / (double) rounds;
        double avgCursor = totalCursor / (double) rounds;
        double improvement = ((avgNormal - avgCursor) / avgNormal) * 100;

        System.out.printf("\n性能对比: %s%.2f%% (%.2fms → %.2fms)\n",
                (improvement > 0 ? "提升" : "下降"),
                Math.abs(improvement),
                avgNormal,
                avgCursor);
    }

    public static void main(String[] args) {
        DruidDataSourcePerformanceTest test = new DruidDataSourcePerformanceTest();

        System.out.println("初始化数据源...");
        test.setup();

        System.out.println("\n开始性能测试...");
        test.comparePerformance();
    }
}

17:28:52.975 [main] INFO com.report.common.utils.database.DbFactory -
加载数据驱动 17:28:52.977 [main] INFO
com.report.common.utils.database.DbFactory -
url：jdbc:mysql://localhost:3306/edu?useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
17:28:52.978 [main] INFO com.report.common.utils.database.DbFactory -
userName：root 17:28:52.978 [main] INFO
com.report.common.utils.database.DbFactory - pwd： 123456 17:28:53.086
[main] INFO com.report.common.utils.database.DbFactory - 加载数据驱动
17:28:53.086 [main] INFO com.report.common.utils.database.DbFactory -
url：jdbc:mysql://localhost:3306/edu?useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true&useCursorFetch=true
17:28:53.087 [main] INFO com.report.common.utils.database.DbFactory -
userName：root 17:28:53.087 [main] INFO
com.report.common.utils.database.DbFactory - pwd： 123456

开始性能测试…

预热开始 17:28:53.089 [main] WARN com.alibaba.druid.pool.DruidDataSource - removeAbandoned is true, not
use in production. 17:28:53.236 [main] INFO
com.alibaba.druid.pool.DruidDataSource - {dataSource-1,testDb_normal}
inited 17:29:01.509 [main] ERROR
com.alibaba.druid.filter.stat.StatFilter - slow sql 8154 millis.
SELECT * FROM testbigdata[] [预热-普通查询] 读取 7000001 行，耗时: 11162 ms
17:29:04.251 [main] WARN com.alibaba.druid.pool.DruidDataSource -
removeAbandoned is true, not use in production. 17:29:04.263 [main]
INFO com.alibaba.druid.pool.DruidDataSource -
{dataSource-2,testDb_cursor} inited [预热-游标查询] 读取 7000001 行，耗时: 6024 ms
17:29:17.590 [main] ERROR com.alibaba.druid.filter.stat.StatFilter -
slow sql 7314 millis. SELECT * FROM testbigdata[] [预热-普通查询] 读取 7000001
行，耗时: 9584 ms [预热-游标查询] 读取 7000001 行，耗时: 5784 ms 17:29:31.735 [main]
ERROR com.alibaba.druid.filter.stat.StatFilter - slow sql 6090 millis.
SELECT * FROM testbigdata[] [预热-普通查询] 读取 7000001 行，耗时: 9020 ms
[预热-游标查询] 读取 7000001 行，耗时: 4986 ms
预热完成

=== 第 1 轮测试 === 17:29:46.466 [main] ERROR com.alibaba.druid.filter.stat.StatFilter - slow sql 4811 millis.
SELECT * FROM testbigdata[] [普通查询] 读取 7000001 行，耗时: 7616 ms [游标查询] 读取
7000001 行，耗时: 4850 ms

=== 第 2 轮测试 === 17:30:01.386 [main] ERROR com.alibaba.druid.filter.stat.StatFilter - slow sql 6261 millis.
SELECT * FROM testbigdata[] [普通查询] 读取 7000001 行，耗时: 9354 ms [游标查询] 读取
7000001 行，耗时: 4386 ms

=== 第 3 轮测试 === 17:30:15.870 [main] ERROR com.alibaba.druid.filter.stat.StatFilter - slow sql 5995 millis.
SELECT * FROM testbigdata[] [普通查询] 读取 7000001 行，耗时: 11189 ms [游标查询] 读取
7000001 行，耗时: 4647 ms

=== 第 4 轮测试 === 17:30:32.805 [main] ERROR com.alibaba.druid.filter.stat.StatFilter - slow sql 6080 millis.
SELECT * FROM testbigdata[] [普通查询] 读取 7000001 行，耗时: 8966 ms [游标查询] 读取
7000001 行，耗时: 4395 ms

=== 第 5 轮测试 === 17:30:47.165 [main] ERROR com.alibaba.druid.filter.stat.StatFilter - slow sql 6074 millis.
SELECT * FROM testbigdata[] [普通查询] 读取 7000001 行，耗时: 11530 ms [游标查询] 读取
7000001 行，耗时: 4761 ms 普通查询 => 总耗时: 48655ms | 平均: 9731.00ms 游标查询 =>
总耗时: 23039ms | 平均: 4607.80ms

性能对比: 提升52.65% (9731.00ms → 4607.80ms)