Java虚拟线程能否提升数据库查询效率？实测遇瓶颈求解析

我想在一个多任务的简单Java应用里测试虚拟线程的能力，每个任务执行一个耗时约10秒的数据库查询。原本以为任务主要耗时在等待响应，所有查询应该几乎同时执行，但实际并非如此，肯定是忽略了某些关键点。

执行任务的方式 #

ExecutorService executorService = Executors.newVirtualThreadPerTaskExecutor()

任务执行逻辑 #

StopWatch stopWatch = StopWatch.createStarted();
int numberOfTasks = 10;
List<? extends Future<String>> futures;
try(ExecutorService executorService = Executors.newVirtualThreadPerTaskExecutor()) {
     futures = IntStream.range(1, numberOfTasks + 1).mapToObj(i -> new Task(i)).map(executorService::submit).toList();
}
        
for(Future<String> future: futures) {
            future.get();
}
stopWatch.stop();
System.out.println(format("The total time of execution was: {0} ms", stopWatch.getTime(TimeUnit.MILLISECONDS)));

Task.call()方法实现 #

@Override
public String call() {
    System.out.println(format("Task: {0} started", taskId));
    StopWatch stopWatch = StopWatch.createStarted();
    Connection connection = null;
    String result = null;
    try {
        connection = DriverManager.getConnection("jdbc:mysql://localhost/sakila?user=sakila&password=sakila");
        System.out.println(format("Task: {0} connection established", taskId));
        var statement = connection.createStatement();
        System.out.println(format("Task: {0} executes SQL statement", taskId));
        ResultSet resultSet = statement.executeQuery("SELECT hello_world() AS output");
        while (resultSet.next()) {
            result = resultSet.getString("output");
        }
        statement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(format("Task: {0} connection closed", taskId));
    }
    stopWatch.stop();
    System.out.println(format("Task: {0} completed in {1} ms", taskId, stopWatch.getTime(TimeUnit.MILLISECONDS)));
    return result;
}

程序输出 #

Task: 1 started
Task: 5 started
Task: 9 started
Task: 7 started
Task: 3 started
Task: 6 started
Task: 8 started
Task: 2 started
Task: 4 started
Task: 10 started
Task: 1 connection established
Task: 6 connection established
Task: 7 connection established
Task: 9 connection established
Task: 8 connection established
Task: 5 connection established
Task: 7 executes SQL statement
Task: 2 connection established
Task: 1 executes SQL statement
Task: 6 executes SQL statement
Task: 3 connection established
Task: 8 executes SQL statement
Task: 2 executes SQL statement
Task: 5 executes SQL statement
Task: 4 connection established
Task: 4 executes SQL statement
Task: 10 connection established
Task: 10 executes SQL statement
Task: 10 connection closed
Task: 6 connection closed
Task: 10 completed in 10 319 ms
Task: 8 connection closed
Task: 2 connection closed
Task: 2 completed in 10 335 ms
Task: 1 connection closed
Task: 9 executes SQL statement
Task: 3 executes SQL statement
Task: 1 completed in 10 337 ms
Task: 4 connection closed
Task: 4 completed in 10 320 ms
Task: 5 connection closed
Task: 8 completed in 10 336 ms
Task: 6 completed in 10 336 ms
Task: 7 connection closed
Task: 5 completed in 10 338 ms
Task: 7 completed in 10 338 ms
Task: 9 connection closed
Task: 3 connection closed
Task: 9 completed in 20 345 ms
Task: 3 completed in 20 345 ms
The total time of execution was: 20 363 ms

现象总结 #

• 初始阶段所有任务均已启动
• 所有任务都建立了JDBC数据库连接
• 仅10个任务中的8个开始执行SELECT语句
• 最后2个任务在有两个任务完成后才开始执行SELECT语句

核心疑问：数据库通信属于I/O操作，虚拟线程应该让所有SELECT语句几乎同时执行，但实际并未实现。（我的CPU是8核）

内容的提问来源于stack exchange，提问作者CodeGrinder