多关联查询优化咨询：项目进度查询性能瓶颈解决

原方案使用多表LEFT JOIN+CASE判断的方式，在频繁调用时性能低下，核心原因是LEFT JOIN会触发大量关联计算与数据加载，20多张表的关联更是会极大拉高IO开销。以下是几种更优的实现方案：

方案一：用EXISTS替代LEFT JOIN #

我们仅需判断项目是否存在某阶段的记录，无需获取关联表的具体数据。EXISTS作为半连接查询，找到匹配记录后会立即终止扫描，效率远高于LEFT JOIN。

示例代码：

SELECT
    p.id_project,
    CASE
        WHEN EXISTS (SELECT 1 FROM payment_order po WHERE po.id_project = p.id_project) THEN 'payment completed'
        WHEN EXISTS (SELECT 1 FROM bill b WHERE b.id_project = p.id_project) THEN 'bill received'
        WHEN EXISTS (SELECT 1 FROM engagement e WHERE e.id_project = p.id_project) THEN 'project engaged'
        -- 按阶段优先级依次添加其他EXISTS判断
        ELSE 'start of the project'
    END AS progress
FROM project p;

方案二：预聚合阶段状态（适配高频查询场景） #

把项目阶段的计算逻辑转移到数据写入环节，维护一个专门的进度汇总表，查询时直接读取该表，彻底规避关联计算开销。

1. 创建进度汇总表：

CREATE TABLE project_progress (
    id_project INT PRIMARY KEY COMMENT '项目ID',
    progress VARCHAR(100) COMMENT '当前阶段',
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
);

2. 初始化基础数据：

INSERT INTO project_progress (id_project, progress)
SELECT id_project, 'start of the project' FROM project;

3. 通过触发器或定时任务维护数据：
   比如在payment_order表插入数据时，自动更新对应项目的进度：

DELIMITER //
CREATE TRIGGER trg_update_payment_progress
AFTER INSERT ON payment_order
FOR EACH ROW
BEGIN
    UPDATE project_progress
    SET progress = 'payment completed'
    WHERE id_project = NEW.id_project;
END //
DELIMITER ;

同理给其他阶段表创建对应触发器，或用定时任务（如MySQL事件、后台脚本）定期同步最新状态。

4. 查询时直接读取汇总表：

SELECT id_project, progress FROM project_progress;

该方案将计算成本分摊到数据写入阶段，查询性能接近单表查询，完美适配频繁调用的视图场景。

方案三：UNION ALL聚合阶段 + 窗口函数取最高优先级 #

将所有阶段的存在情况通过UNION ALL聚合，再按阶段优先级排序，取每个项目的最高优先级阶段，避免多表JOIN的开销。

示例代码：

WITH project_stages AS (
    -- 优先级数字越小，阶段越靠后（优先级越高）
    SELECT id_project, 'payment completed' AS progress, 1 AS priority FROM payment_order
    UNION ALL
    SELECT id_project, 'bill received' AS progress, 2 AS priority FROM bill
    UNION ALL
    SELECT id_project, 'project engaged' AS progress, 3 AS priority FROM engagement
    -- 按实际阶段顺序添加其他表，调整对应priority值
    UNION ALL
    SELECT id_project, 'start of the project' AS progress, 99 AS priority FROM project
)
SELECT id_project, progress
FROM (
    SELECT 
        id_project, 
        progress,
        ROW_NUMBER() OVER (PARTITION BY id_project ORDER BY priority) AS rn
    FROM project_stages
) t
WHERE rn = 1;

通用优化建议 #

无论采用哪种方案，都要给所有关联表的id_project字段创建索引，比如：

CREATE INDEX idx_payment_order_id_project ON payment_order(id_project);
CREATE INDEX idx_bill_id_project ON bill(id_project);
CREATE INDEX idx_engagement_id_project ON engagement(id_project);
-- 其他阶段表同理创建索引

索引能大幅提升存在性判断或关联查询的效率。

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