MySQL双数据集按ID与日期高效关联的查询优化问题

当然可以通过单条优化后的查询实现你的需求，而且这种方式在180万行的大数据量下比分开两个子查询再关联更高效——关键是要利用窗口函数或精准的关联逻辑，配合合适的索引来避免全表扫描。

核心需求回顾 #

你需要：

• 从hours表聚合出每个人员、日期、岗位的总工时
• 为每个聚合结果匹配对应日期生效的最新有效费率，且同一人员同一日期有多个费率时（比如person3在2020-05-01的两条费率），要选择指定的费率（示例中取21.00，这里默认按费率降序选择最高值，可根据实际规则调整）

优化后的单查询实现（MySQL 8.0+） #

以下查询利用窗口函数和预聚合逻辑，同时处理费率匹配和性能优化：

WITH aggregated_hours AS (
    -- 第一步：预聚合工时数据，减少后续关联的数据量
    SELECT 
        hours_person_id,
        hours_date,
        hours_job,
        SUM(hours_value) AS total_hours
    FROM hours
    WHERE hours_status = 1
    GROUP BY hours_person_id, hours_date, hours_job
),
ranked_rates AS (
    -- 第二步：为每个人员的有效费率排序，优先取最新生效日期，同一日期取最高费率
    SELECT 
        rate_person_id,
        rate_date,
        rate_value,
        ROW_NUMBER() OVER (
            PARTITION BY rate_person_id
            ORDER BY rate_date DESC, rate_value DESC
        ) AS rate_priority
    FROM rates
    WHERE rate_active = 1
)
-- 第三步：关联聚合工时和排序后的费率
SELECT 
    ah.hours_person_id,
    ah.hours_date,
    ah.hours_job,
    ah.total_hours,
    rr.rate_value,
    ah.total_hours * rr.rate_value AS labor_cost
FROM aggregated_hours ah
LEFT JOIN ranked_rates rr
    ON rr.rate_person_id = ah.hours_person_id
    AND rr.rate_date <= ah.hours_date
-- 过滤出对应当前工时日期的最优费率
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY ah.hours_person_id, ah.hours_date, ah.hours_job
    ORDER BY rr.rate_date DESC, rr.rate_value DESC
) = 1;

大数据量下的性能优化关键 #

为了适配180万行的规模，必须添加复合索引来避免全表扫描：

• 给hours表创建索引：

  CREATE INDEX idx_hours_status_person_date_job 
  ON hours (hours_status, hours_person_id, hours_date, hours_job);
  

  这个索引可以让聚合查询直接利用索引完成分组和求和，无需扫描全表。

• 给rates表创建索引：

  CREATE INDEX idx_rates_active_person_date_value 
  ON rates (rate_active, rate_person_id, rate_date DESC, rate_value DESC);
  

  这个索引能加速窗口函数的排序逻辑，同时让关联查询快速定位到匹配的费率数据。

替代方案（兼容MySQL 8.0.13以下版本） #

如果你的MySQL版本不支持QUALIFY，可以将过滤逻辑放到子查询中：

SELECT *
FROM (
    SELECT 
        ah.hours_person_id,
        ah.hours_date,
        ah.hours_job,
        ah.total_hours,
        rr.rate_value,
        ah.total_hours * rr.rate_value AS labor_cost,
        ROW_NUMBER() OVER (
            PARTITION BY ah.hours_person_id, ah.hours_date, ah.hours_job
            ORDER BY rr.rate_date DESC, rr.rate_value DESC
        ) AS row_rank
    FROM (
        SELECT 
            hours_person_id,
            hours_date,
            hours_job,
            SUM(hours_value) AS total_hours
        FROM hours
        WHERE hours_status = 1
        GROUP BY hours_person_id, hours_date, hours_job
    ) ah
    LEFT JOIN (
        SELECT 
            rate_person_id,
            rate_date,
            rate_value
        FROM rates
        WHERE rate_active = 1
    ) rr
        ON rr.rate_person_id = ah.hours_person_id
        AND rr.rate_date <= ah.hours_date
) temp
WHERE temp.row_rank = 1;

为什么单查询更优？ #

分开两个子查询再关联会让数据库先生成两个临时结果集，再进行关联——这会增加内存消耗和IO操作。而单查询的CTE（公共表表达式）可以让查询优化器直接规划更高效的执行路径，结合索引后能大幅减少数据扫描量，更适合大数据场景。

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