如何优化缺失ACK记录的查询速度？排查条件致慢原因

问题2：WHERE子句致慢的原因 #

1. 丢失LEFT JOIN的索引优化：原本LEFT JOIN可通过关联键TYPE的索引快速匹配，但加上<>（不等于）过滤后，数据库无法提前筛选不匹配的记录，必须先完成两个聚合子查询结果的全量关联，再逐行比较计数，等价于全量扫描后再过滤，开销陡增。
2. 聚合结果无索引可用：两个子查询的聚合结果集没有索引，WHERE子句的数值比较只能逐行校验，当Type数量超过100后，逐行比较的开销会被放大。
3. 无法提前终止查询：无过滤条件时，数据库可直接返回聚合后的关联结果；加上不等于过滤后，必须遍历所有关联行才能筛选出目标数据，无法提前返回部分结果。

问题1：查询优化方案 #

针对大数据量表场景，从索引、SQL逻辑、执行计划三个维度优化：

1. 新增复合索引 #

• 在PENDING_RECORDS_TABLE上创建复合索引：(STATUS, TYPE)。快速过滤STATUS='PENDING'的记录，并按TYPE分组，避免全表扫描。
• 在ACKNOWLEDGE_RECORDS_TABLE上创建索引：(TYPE)。加速按TYPE分组计数的操作，减少全表扫描开销。

2. 简化SQL逻辑，移除冗余代码 #

原SQL中WHERE TYPE IN (SELECT DISTINCT TYPE FROM PENDING_RECORDS_TABLE)属于冗余逻辑，优化后的SQL如下：

SELECT 
    rs.TYPE,
    rs.NUMBER_OF_PENDING,
    COALESCE(ar.NUMBER_OF_RECEIVED, 0) AS NUMBER_OF_RECEIVED
FROM (
    SELECT TYPE, COUNT(1) AS NUMBER_OF_PENDING 
    FROM PENDING_RECORDS_TABLE
    WHERE STATUS = 'PENDING'
    GROUP BY TYPE
) rs
LEFT JOIN (
    SELECT TYPE, COUNT(1) AS NUMBER_OF_RECEIVED 
    FROM ACKNOWLEDGE_RECORDS_TABLE ar
    JOIN (SELECT DISTINCT TYPE FROM PENDING_RECORDS_TABLE WHERE STATUS='PENDING') pt
        ON ar.TYPE = pt.TYPE
    GROUP BY TYPE
) ar ON rs.TYPE = ar.TYPE
WHERE rs.NUMBER_OF_PENDING <> COALESCE(ar.NUMBER_OF_RECEIVED, 0)
ORDER BY rs.TYPE;

• 用COALESCE处理未收到ACK的情况（将NULL转为0，确保比较逻辑正确）；
• 用JOIN替代IN，让数据库更高效地执行关联过滤。

3. 下推过滤条件 #

如果业务上有时间范围限制，一定要在子查询中加入时间过滤，减少聚合的数据量。比如：

-- 在PENDING_RECORDS_TABLE的子查询中加入时间过滤
WHERE STATUS = 'PENDING' AND CREATE_TIME >= '2024-01-01 00:00:00'

4. 分批次查询（可选） #

如果Type数量极多，可按Type的范围（如首字母、ID区间）分批次查询，避免一次处理大量聚合结果。

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