按标签过滤

简介

GitLab 有 labels 可以分配给 issues、merge requests 和 epics。这些对象上的标签通过多态的 label_links 表形成多对多关系。

要通过多个标签过滤这些对象 - 例如，“所有带有 ~Plan 和 ~backend 标签的开放 issues” - 我们会生成一个包含 GROUP BY 子句的查询。简单形式如下：

SELECT
    issues.*
FROM
    issues
    INNER JOIN label_links ON label_links.target_id = issues.id
        AND label_links.target_type = 'Issue'
    INNER JOIN labels ON labels.id = label_links.label_id
WHERE
    issues.project_id = 13083
    AND (issues.state IN ('opened'))
    AND labels.title IN ('Plan',
        'backend')
GROUP BY
    issues.id
HAVING (COUNT(DISTINCT labels.title) = 2)
ORDER BY
    issues.updated_at DESC,
    issues.id DESC
LIMIT 20 OFFSET 0

具体来说：

1. GROUP BY issues.id 将结果按 issues 分组。
2. HAVING (COUNT(DISTINCT labels.title) = 2) 确保所有匹配的 issues 都具有这两个标签。

这比理想情况更复杂。它使得查询构建更容易出错（例如 issue #15557）。

尝试 A: WHERE EXISTS

尝试 A1: 使用多个子查询与 WHERE EXISTS

在 issue #37137 及其相关的 merge request 中， 我们尝试用多次使用 WHERE EXISTS 来替换 GROUP BY。对于上面的例子，这会给出：

WHERE (EXISTS (
        SELECT
            TRUE
        FROM
            label_links
            INNER JOIN labels ON labels.id = label_links.label_id
        WHERE
            labels.title = 'Plan'
            AND target_type = 'Issue'
            AND target_id = issues.id))
AND (EXISTS (
        SELECT
            TRUE
        FROM
            label_links
            INNER JOIN labels ON labels.id = label_links.label_id
        WHERE
            labels.title = 'backend'
            AND target_type = 'Issue'
            AND target_id = issues.id))

虽然这无需更改模式就能工作，并且确实提高了可读性，但它没有改善查询性能。

尝试 A2: 在 WHERE EXISTS 子句中使用标签 ID

在 merge request #34503 中，我们采用了与 A1 类似的方法。但这次，我们执行了单独的查询来获取过滤中使用的标签 ID，这样我们就可以避免 EXISTS 子句中的 JOIN，并直接通过 label_links.label_id 进行过滤。我们还为 label_links 表添加了一个新的索引，包含 target_id、label_id 和 target_type 列，以加速此查询。

查找标签 ID 并不简单，因为在单个根命名空间中可能存在多个具有相同标题的标签。我们通过按标题对标签 ID 进行分组，然后在 EXISTS 子句中使用 ID 数组来解决这个问题。

这带来了显著的性能提升。然而，此优化无法应用于我们没有项目或组上下文的仪表板页面。我们无法轻松搜索标签 ID，因为这意味着要搜索用户有权访问的所有项目和组。

尝试 B: 使用数组列反规范化

我们在 issue #49651 中讨论了使用数组列反规范化 label_links 表进行查询的问题，有两个选项：标签 ID 和标题。

我们可以将这两者都视为 issues、merge_requests 和 epics 上的数组列：issues.label_ids 将是标签 ID 的数组列，而 issues.label_titles 将是标签标题的数组。

这些数组列可以配合 GIN 索引 来提高匹配效率。

尝试 B1: 为每个对象存储标签 ID

这比标题有一些显著优势：

1. 除非标签被删除，或者项目被移动，否则我们永远不需要批量更新反规范化列。
2. 它比标题使用的存储空间更少。

不幸的是，我们的应用程序设计使这变得困难。如果我们能够轻松地仅通过标签 ID 进行查询，就不需要本文档开头初始查询中的 INNER JOIN labels。GitLab 允许用户跨项目和跨组按标签标题进行过滤，因此按 ~Plan 标签的过滤器可能包含具有多个不同 ID 的标签。

我们不希望用户必须了解不同的 ID，这意味着给定以下数据集：

我们需要类似这样的东西：

WHERE
    label_ids @> ARRAY[11, 12]
    OR label_ids @> ARRAY[21, 22]
    OR label_ids @> ARRAY[31, 32]

当我们考虑到在某些情况下，可能有两个具有不同 ID 的 ~backend 标签可以应用于同一个对象时，情况会变得更加复杂，因此组合的数量会进一步膨胀。

尝试 B2: 为每个对象存储标签标题

从更新对象的角度来看，这是最差的选择。我们必须在以下情况下批量更新对象：

1. 对象从一个项目移动到另一个项目。
2. 项目从一个组移动到另一个组。
3. 标签被重命名。
4. 标签被删除。

它也使用更多的存储空间。不过查询很简单：

WHERE
    label_titles @> ARRAY['Plan', 'backend']

而且我们在 issue #49651 中的测试 表明这可能是快速的。

然而，目前来看，缺点大于优点。

结论

我们发现了一种方法 A2，它不需要反规范化，并且显著提高了查询性能。这 不适用于所有情况，但我们能够将方法 A1 应用于其他情况，从而在所有场景中 移除了 GROUP BY 和 HAVING 子句。

这简化了查询，并在最常见的情况下提高了性能。