优化LEFT JOIN与EXISTS结合的SQL查询：避免重复访问表

业务背景 #

• 百万级t_customer表需按created_date排序，每次取500条分批处理
• 处理结果插入t_summary_log表（两者为可选一对一关联），处理完的客户记录会排至队尾，全表处理完成后循环重新处理
• 每次迭代更新t_summary_log.last_updated_date，仅当t_summary_log中对应记录的status_flag=1时，该客户记录退出循环

原查询的问题 #

原SQL需要两次访问百万级的t_summary_log表，存在数据库资源浪费：

SELECT 
    a.id,
    a.username,
    IFNULL(b.last_updated_date, a.created_date) AS last_updated_date
FROM
    t_customer a
        LEFT JOIN
    t_summary_log b ON b.customer_id = a.id
WHERE
    NOT EXISTS( SELECT 
            1
        FROM
            t_summary_log b0
        WHERE
            b0.customer_id = a.id
                -- 0=处理中, 1=已完成，无需修改
                AND b0.status_flag = 1)
ORDER BY 3 ASC
LIMIT 500;

优化后的SQL #

通过在LEFT JOIN的连接条件中直接过滤status_flag != 1，同时兼容无对应日志的情况，只需访问一次t_summary_log即可完成逻辑：

SELECT 
    a.id,
    a.username,
    IFNULL(b.last_updated_date, a.created_date) AS last_updated_date
FROM
    t_customer a
LEFT JOIN t_summary_log b 
    ON b.customer_id = a.id 
    AND b.status_flag != 1  -- 连接阶段直接排除已完成的记录
WHERE
    b.customer_id IS NULL OR b.status_flag = 0  -- 保留未处理过或处理中的记录
ORDER BY last_updated_date ASC
LIMIT 500;

优化逻辑说明 #

1. 合并过滤条件：把原NOT EXISTS的判断逻辑整合到LEFT JOIN的连接条件中，避免二次查询t_summary_log
2. 过滤已完成记录：连接条件b.status_flag != 1会直接排除已完成（status_flag=1）的关联记录，这类客户对应的b.customer_id会变为NULL
3. 保留待处理记录：WHERE子句筛选出两种符合要求的记录：
   • 从未处理过的客户（无对应t_summary_log记录，b.customer_id IS NULL）
   • 正在处理但未完成的客户（b.status_flag=0）
4. 明确排序字段：将原ORDER BY 3改为ORDER BY last_updated_date ASC，既逻辑清晰，也便于数据库利用索引优化排序

可选索引建议 #

若需进一步提升查询效率，建议添加以下索引：

• t_customer(created_date)：支持按创建日期排序的需求
• t_summary_log(customer_id, status_flag, last_updated_date)：覆盖连接、过滤和排序所需的全部字段，避免回表查询

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