优化基于GROUP BY结果的查询：获取全Done状态流水线任务列表

问题背景 #

我有一张存储流水线任务数据的表，一个流水线包含多个独立运行的任务，每个任务可按自身节奏完成。流水线完成后，会通过将archived列设为1进行归档。需要获取**所有任务状态均为"Done"**的流水线的任务列表。

示例数据 #

mysql> select id, pipeline, archived, state from jobs where archived=0 limit 4;
+---------+-----------+----------+-------+
| id      | pipeline  | archived | state |
+---------+-----------+----------+-------+
| 8572387 | pipeline1 |        0 | Done  |
| 8572388 | pipeline1 |        0 | Done  |
| 8572389 | pipeline2 |        0 | Done  |
| 8572390 | pipeline2 |        0 | Fail  |
+---------+-----------+----------+-------+
4 rows in set (0.00 sec)

现有查询及性能问题 #

已经能快速查询出存在失败任务的流水线列表（耗时0.01秒）：

mysql> select distinct(pipeline) from jobs where archived=0 group by pipeline, state having state!='Done';
+-----------+
| pipeline  |
+-----------+
| pipeline2 |
+-----------+
1 row in set (0.01 sec)

但将该子查询嵌套到最终查询后，耗时骤增至18.77秒（真实数据中4个流水线仅2个失败，每个流水线约500个任务）：

select j1.id 
from jobs j1 
where j1.archived=0 
and j1.pipeline not in ( select distinct(j2.pipeline) 
                         from jobs j2 
                         where j2.archived=0 
                         group by j2.pipeline, j2.state having j2.state!='Done'
                        );

+---------+
| id      |
+---------+
| 8583200 |
| 8583201 |
| 8583202 |
| 8583203 |
.
.
.
| 8584305 |
| 8584306 |
+---------+
1107 rows in set (18.77 sec)

查询计划显示子查询被标记为DEPENDENT SUBQUERY，意味着会对主查询的每一行都执行一次子查询，这是性能瓶颈：

mysql> describe select j1.id from jobs j1 where j1.archived=0 and j1.pipeline not in (select distinct(j2.pipeline) from jobs j2 where j2.archived=0 group by j2.pipeline, j2.state having j2.state!='Done');
+----+--------------------+-------+------+---------------+----------+---------+-------+------+----------------------------------------------+
| id | select_type        | table | type | possible_keys | key      | key_len | ref   | rows | Extra                                        |
+----+--------------------+-------+------+---------------+----------+---------+-------+------+----------------------------------------------+
|  1 | PRIMARY            | j1    | ref  | archived      | archived | 2       | const | 2306 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | j2    | ref  | archived      | archived | 2       | const | 2306 | Using where; Using temporary; Using filesort |
+----+--------------------+-------+------+---------------+----------+---------+-------+------+----------------------------------------------+
2 rows in set (0.00 sec)

优化方案 #

方案1：用JOIN替代NOT IN，消除相关子查询 #

先预计算出所有无失败任务的流水线，再关联主表获取任务ID：

SELECT j.id
FROM jobs j
INNER JOIN (
    -- 找出所有任务都为Done的流水线
    SELECT pipeline
    FROM jobs
    WHERE archived = 0
    GROUP BY pipeline
    HAVING COUNT(CASE WHEN state != 'Done' THEN 1 END) = 0
) valid_pipelines ON j.pipeline = valid_pipelines.pipeline
WHERE j.archived = 0;

方案2：临时表存储失败流水线，减少重复计算 #

如果失败流水线数量少，可以先将结果存入临时表，再做排除：

-- 创建临时表存储失败流水线
CREATE TEMPORARY TABLE failed_pipelines AS
SELECT DISTINCT pipeline
FROM jobs
WHERE archived = 0
GROUP BY pipeline, state
HAVING state != 'Done';

-- 查询排除失败流水线的任务
SELECT id
FROM jobs
WHERE archived = 0
AND pipeline NOT IN (SELECT pipeline FROM failed_pipelines);

方案3：添加复合索引，提升过滤与分组效率 #

创建针对(archived, pipeline, state)的复合索引，让数据库直接通过索引完成过滤、分组和状态检查，无需全表扫描：

CREATE INDEX idx_archived_pipeline_state ON jobs(archived, pipeline, state);

性能瓶颈原因 #

原查询中的子查询被识别为相关子查询，MySQL会对主查询的每一行（约2306行）重复执行一次子查询，相当于累计执行了2306次耗时0.01秒的查询，总耗时自然飙升。优化后的查询将子查询转为独立预计算，仅执行一次，再关联主表，性能会大幅提升。

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