MySQL使用INNER JOIN和子查询执行耗时过长如何优化

一、索引优化（最核心的优化手段） #

• 为in_client表创建联合覆盖索引：INDEX idx_agent_idnum_modified (agent_id, id_number, modified)
  这个索引完全覆盖子查询的过滤条件（agent_id = 1234、时间筛选）、分组逻辑（GROUP BY id_number）、最大值计算（MAX(modified)）的所有字段需求，子查询可以直接通过索引返回结果，无需回表扫全表数据，性能提升最明显。
• 为in_policy表创建单列索引：INDEX idx_client_id (client_id)
  用于快速校验客户是否存在关联保单，避免全表扫描in_policy表。

二、SQL语句改写优化 #

改写点说明： #

1. 把原HAVING层的时间过滤条件提前到子查询的WHERE中，先剔除所有早于指定时间的记录，大幅减少分组计算的数据量。
2. 将原LEFT JOIN + IS NULL的无保单筛选逻辑替换为NOT EXISTS，匹配到对应记录就终止扫描，执行效率远高于左连接后再过滤。
3. 原查询中in_agent关联只需要获取agent_id=1234的手机号，无需逐行关联，可直接改为常量查询（如果你的业务中agent_id是动态参数，保留关联也可以）。

优化后兼容低版本数据库的SQL： #

SELECT 
    ic.id, 
    ic.id_number, 
    (SELECT phone_number FROM in_agent WHERE id = 1234) AS phone_number
FROM 
    in_client AS ic 
INNER JOIN (
    SELECT id_number, MAX(modified) AS modified 
    FROM in_client 
    WHERE 
        agent_id = 1234
        AND id_number IS NOT NULL 
        -- 时间过滤提前到WHERE层
        AND modified >= '2021-mm-dd hh:mm:ss'
    GROUP BY id_number 
) AS max USING (id_number, modified) 
-- 替换LEFT JOIN为NOT EXISTS
WHERE NOT EXISTS (
    SELECT 1 FROM in_policy AS ip WHERE ip.client_id = ic.id
)

如果你使用的数据库支持窗口函数（MySQL 8.0+、PostgreSQL等），可以进一步简化为以下写法，减少一次表关联： #

WITH client_latest AS (
    SELECT 
        id, id_number, modified,
        ROW_NUMBER() OVER (PARTITION BY id_number ORDER BY modified DESC) AS rn
    FROM in_client
    WHERE 
        agent_id = 1234
        AND id_number IS NOT NULL
        AND modified >= '2021-mm-dd hh:mm:ss'
)
SELECT 
    id, 
    id_number,
    (SELECT phone_number FROM in_agent WHERE id = 1234) AS phone_number
FROM client_latest
WHERE 
    rn = 1
    AND NOT EXISTS (
        SELECT 1 FROM in_policy AS ip WHERE ip.client_id = client_latest.id
    )

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