使用LEFT JOIN根据其他列值筛选MySQL行的查询需求

需求说明 #

为temp_employee表中的每个employee_id，从employee_logs（超4000万行）中筛选出满足以下条件的最新单条记录：

• status != 3
• updated_time <= application_date
  最终返回employee_id、type、status、updated_time字段，结果行数需与temp_employee完全一致。

方案一：窗口函数（MySQL 8.0+ 推荐） #

利用ROW_NUMBER()窗口函数按员工分组排序，直接筛选出每组的最新记录，大数据量下性能更优，前提是配置合适索引。

SELECT t.employee_id, l.type, l.status, l.updated_time
FROM temp_employee t
LEFT JOIN (
    SELECT 
        employee_id,
        type,
        status,
        updated_time,
        ROW_NUMBER() OVER (
            PARTITION BY employee_id 
            ORDER BY updated_time DESC
        ) AS rn
    FROM employee_logs
    WHERE status != 3
) l ON t.employee_id = l.employee_id 
    AND l.updated_time <= t.application_date
    AND l.rn = 1;

性能优化索引 #

为employee_logs创建复合索引，覆盖筛选、分组和返回字段，避免全表扫描：

CREATE INDEX idx_emp_logs_status_empid_updated ON employee_logs(status, employee_id, updated_time DESC, type);

方案二：关联子查询（兼容低版本MySQL） #

通过子查询定位每个员工符合条件的最大updated_time，再关联回日志表获取完整记录，适合不支持窗口函数的老版本MySQL。

SELECT t.employee_id, l.type, l.status, l.updated_time
FROM temp_employee t
LEFT JOIN employee_logs l 
    ON t.employee_id = l.employee_id 
    AND l.status != 3
    AND l.updated_time <= t.application_date
    AND l.updated_time = (
        SELECT MAX(updated_time)
        FROM employee_logs l2
        WHERE l2.employee_id = t.employee_id
            AND l2.status != 3
            AND l2.updated_time <= t.application_date
    );

性能优化索引 #

为employee_logs创建适配子查询的复合索引：

CREATE INDEX idx_emp_logs_empid_status_updated ON employee_logs(employee_id, status, updated_time DESC, type);

关键注意事项 #

• 必须使用LEFT JOIN，确保无符合条件日志的员工也能保留在结果中，保证行数与temp_employee一致。
• 索引是处理4000万行数据的核心，没有合适索引会导致查询超时。

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