优化关联子查询：根据库ID获取库名的查询优化方案

原查询问题分析 #

原查询使用了三个关联子查询，每遍历t_db_database_mapping的一行就会触发三次独立查询：

• 两次查询t_database获取库名（虽用到索引，但多次执行仍有额外开销）
• 一次对t_db_data_type_map做全表扫描统计行数，这是核心性能瓶颈（执行计划显示每次子查询都要执行全表扫描）

优化思路 #

1. 用JOIN替换关联子查询：将t_database表分别通过src_db_id和tgt_db_id关联到主表，一次性获取源库和目标库名称，避免每行触发子查询。
2. 预聚合计数：先对t_db_data_type_map按db_mapping_id分组统计行数，再关联到主表，只需要一次扫描统计，而非每行重复计算。

优化后的SQL语句 #

SELECT
    src_db.database_name AS source_db,
    tgt_db.database_name AS target_db,
    COALESCE(dt_count.types_count, 0) AS db_types_count
FROM t_db_database_mapping dm
LEFT JOIN t_database src_db ON dm.src_db_id = src_db.id
LEFT JOIN t_database tgt_db ON dm.tgt_db_id = tgt_db.id
LEFT JOIN (
    SELECT db_mapping_id, COUNT(*) AS types_count
    FROM t_db_data_type_map
    GROUP BY db_mapping_id
) dt_count ON dm.db_mapping_id = dt_count.db_mapping_id
-- 保留原查询的排序逻辑（如果需要）
-- ORDER BY dm.updated_dt DESC

优化后执行计划的预期变化 #

• 原计划中的三个子查询（SubPlan1/2/3）会被替换为：
  1. 对t_database的两次索引扫描（或索引仅扫描），仅执行一次而非每行触发
  2. 对t_db_data_type_map的一次全表扫描+分组聚合，之后与主表关联，避免重复扫描
• 整体查询成本会显著降低，尤其是当t_db_database_mapping行数较多时，性能提升明显

额外优化建议 #

• 给t_db_data_type_map的db_mapping_id字段创建索引，进一步提升预聚合的速度
• 如果src_db_id/tgt_db_id不会为NULL，可以将LEFT JOIN改为INNER JOIN，减少关联开销

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