同SQL在MySQL CLI快速执行，R2dbcTemplate/DBeaver却极慢的原因

同一主机上，执行以下SQL语句时，MySQL CLI耗时仅约41.9秒，但R2dbcTemplate代码和DBeaver客户端执行均耗时超5分钟，且三者执行计划结构看似一致，求差异原因。

执行的SQL语句 #

SELECT
  al.category,
  IFNULL(jt.name, 'Ungrouped Devices') AS device_group_name,
  IFNULL(jt.id, '00000000-0000-0000-0000-000000000000') AS device_group_id,
  jt.color_code AS color_code,
  COUNT(*) AS total
FROM
  alerts AS al,
  JSON_TABLE( CAST(al.device_groups AS JSON),
    '$[*]' COLUMNS ( name VARCHAR(255) PATH '$.device_group_name',
      id VARCHAR(255) PATH '$.device_group_id',
      color_code VARCHAR(255) PATH '$.color_code' ) ) AS jt
WHERE
  account_id = 1000000000
GROUP BY
  category,
  device_group_name,
  device_group_id,
  color_code

MySQL CLI执行情况 #

执行耗时约41.9秒，执行计划如下：

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on <temporary>  (actual time=41540.274..41554.003 rows=1956 loops=1)
    -> Aggregate using temporary table  (actual time=41540.265..41540.265 rows=1956 loops=1)
        -> Nested loop inner join  (actual time=0.192..15244.892 rows=676934 loops=1)
            -> Filter: ((al.alert_type_id <> 40) and (al.alert_type_id <> 90) and (al.event_time >= TIMESTAMP'2024-11-22 00:00:00') and (al.event_time < TIMESTAMP'2025-02-20 00:00:00'))  (cost=7946.96 rows=75045) (actual time=0.034..6171.038 rows=676934 loops=1)
                -> Index lookup on al using PRIMARY (account_id=1091000508)  (cost=7946.96 rows=216200) (actual time=0.031..4714.982 rows=676934 loops=1)
            -> Materialize table function  (actual time=0.012..0.013 rows=1 loops=676934)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (41.90 sec)

R2dbcTemplate与DBeaver执行情况 #

• R2dbcTemplate代码执行耗时超5分钟，代码片段：

template
    .getDatabaseClient()
    .sql(sql)
    .map(EVENT_CATEGORY_METRIC_ROW_MAPPER)
    .all();

• DBeaver客户端执行同样耗时超5分钟，执行计划如下：

-> Table scan on <temporary>  (actual time=355837.677..355838.448 rows=1956 loops=1)
    -> Aggregate using temporary table  (actual time=355837.668..355837.668 rows=1956 loops=1)
        -> Nested loop inner join  (actual time=0.120..7819.864 rows=676934 loops=1)
            -> Filter: ((al.alert_type_id <> 40) and (al.alert_type_id <> 90) and (al.event_time >= TIMESTAMP'2024-11-22 00:00:00') and (al.event_time < TIMESTAMP'2025-02-20 00:00:00'))  (cost=7207.37 rows=67649) (actual time=0.050..3442.970 rows=676934 loops=1)
                -> Index lookup on al using PRIMARY (account_id=1091000508)  (cost=7207.37 rows=216200) (actual time=0.043..2399.595 rows=676934 loops=1)
            -> Materialize table function  (actual time=0.006..0.006 rows=1 loops=676934)

执行计划只是宏观逻辑，实际执行的细节差异才是耗时天差地别的关键，主要集中在以下几点：

1. 结果处理与传输方式差异 #

MySQL CLI是本地进程，与数据库通过**IPC（进程间通信）**直接交互，数据传输开销几乎可以忽略；而DBeaver和R2dbcTemplate即使在同一主机，默认也可能使用TCP/IP连接，TCP的握手、数据包拆分重组会带来额外开销。此外：

• DBeaver需要对结果集做UI渲染、格式化、后台预取分页数据，这些前端处理会占用大量CPU和时间；
• R2dbcTemplate的all()方法会一次性拉取所有结果，再通过EVENT_CATEGORY_METRIC_ROW_MAPPER转换为Java对象，若映射逻辑包含复杂操作（如枚举转换、日期格式化），累积耗时会大幅增加。

2. 会话参数配置不一致 #

不同客户端的默认会话参数差异极大，直接影响执行效率：

• 自动提交状态：MySQL CLI默认开启autocommit，而JDBC/R2dbc驱动默认关闭，事务状态下临时表的创建、锁机制处理逻辑不同；
• 内存临时表阈值：CLI的tmp_table_size和max_heap_table_size可能设置得更大，聚合时临时表全程在内存中处理；其他客户端默认值较小，临时表达到阈值后转存到磁盘，磁盘IO速度比内存慢几十倍；
• 优化器开关：optimizer_switch等参数差异，可能导致执行计划看似相同，但实际执行路径有细微区别（如临时表的内存分配策略）。

3. 临时表存储介质差异 #

执行计划明确用到临时表做聚合，若CLI的临时表在内存中运行，而DBeaver/R2dbc的临时表落到磁盘，两者耗时差10倍以上都很正常——磁盘IO的延迟远高于内存操作。

4. 客户端/驱动的额外隐性操作 #

• R2dbc驱动虽为异步模型，但如果ROW_MAPPER中包含同步阻塞逻辑（如查询缓存、打印日志），会拖慢整体处理流程；
• DBeaver在执行SQL前后会自动查询元数据（如表结构、字段类型），这些隐性操作的耗时会累积；
• 部分客户端会开启结果集缓存、性能统计等后台任务，占用额外资源。

可以按以下步骤定位具体原因：

• 对比会话参数：在CLI和DBeaver/R2dbc中分别执行SHOW VARIABLES LIKE 'tmp_table_size';、SHOW VARIABLES LIKE 'autocommit';，查看参数差异；
• 切换连接方式：将JDBC/R2dbc的URL改为使用本地套接字（如jdbc:mysql://localhost:3306/db?socket=/var/run/mysqld/mysqld.sock），替代TCP连接，观察耗时变化；
• 简化结果处理：R2dbc中去掉map逻辑，直接拉取原始结果；DBeaver中关闭UI渲染（如直接导出结果），看耗时是否下降；
• 监控磁盘IO：执行SQL时用iostat -x 1查看磁盘读写情况，若其他客户端执行时磁盘IO显著升高，说明临时表已落到磁盘。

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