ラベルによるフィルタリング
導入
GitLab には、イシューやマージリクエスト、エピックに割り当てられるラベルがあります。これらのオブジェクトのラベルは、多相テーブルlabel_links を通して多対多の関係になっています。
これらのオブジェクトを複数のラベルでフィルタリングするために、例えば’~Planというラベルと~backendというラベルを持つすべてのオープンなイシュー’というように、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
特に
- 我々は、
GROUP BY issues.id。 -
HAVING (COUNT(DISTINCT labels.title) = 2)条件を使用して、一致したすべてのイシューが両方のラベルを持つことを確認します。
これは理想的な方法よりも複雑です。クエリ構築にエラーが発生しやすくなります(イシュー#15557など)。
試みAWHERE EXISTS
A1:複数のサブクエリを使用します。WHERE EXISTS
イシュー#37137とそれに関連するマージリクエストでは、GROUP BY をWHERE EXISTS の複数の使用で置き換えることを試みました。 上記の例では、これは次のようになります：
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 を使用します。
マージリクエスト#34503では、A1と同様のアプローチをとりました。しかし今回は、EXISTS 節のJOIN を避け、label_links.label_id で直接フィルタリングするために、フィルタで使用されるラベルの ID を取得する別のクエリを実行しました。また、このクエリを高速化するために、target_id、label_id、target_type 列に対して、label_links に新しいインデックスを追加しました。
ラベル ID の検索は、1 つのルート名前空間内に同じタイトルのラベルが複数存在する可能性があるため、簡単ではありませんでした。ラベル ID をタイトルでグループ化し、EXISTS 節で ID の配列を使用することで、これを解決しました。
その結果、パフォーマンスが大幅に向上しました。しかし、この最適化はプロジェクトやグループのコンテキストがないダッシュボードページには適用できませんでした。なぜなら、ユーザーがアクセスできるすべてのプロジェクトとグループを検索することになるからです。
試み B：配列カラムを使用した非正規化
GitLab 12.1ではMySQLのサポートがなくなったので、PostgreSQLの配列を使うことにしました。label_links テーブルをクエリ用に非正規化する方法については、イシュー#49651で説明しました。選択肢はラベル ID とタイトルの二つです。
issues,merge_requests,epics の配列カラムとして考えることができます。issues.label_ids はラベルIDの配列カラム、issues.label_titles はラベルタイトルの配列となります。
これらの配列カラムをGINインデックスで補完することで、マッチングを向上させることができます。
試みB1：各オブジェクトのラベルIDを格納
これは、タイトルよりも強力な利点があります：
- ラベルが削除されたり、プロジェクトが移動したりしない限り、非正規化されたカラムを一括更新する必要はありません。
- タイトルよりも少ないストレージしか使用しません。
残念なことに、私たちのアプリケーションの設計ではこれが難しいのです。もしラベル ID だけで簡単にクエリできたら、冒頭のクエリINNER JOIN labels は必要なかったでしょう。GitLab では、ユーザーはプロジェクトやグループを超えてラベルのタイトルでフィルタをかけることができます。そのため、~Plan というラベルでフィルタをかけると、複数の異なる ID を持つラベルが含まれる可能性があります。
そのため、~Plan というラベルでフィルタをかけると、複数の異なる ID を持つラベルが含まれる可能性があります。私たちはユーザーに異なる ID を知ってほしくないので、このデータセットでは
| プロジェクト | ~プランラベルID | ~バックエンドラベルID |
|---|---|---|
| A | 11 | 12 |
| B | 21 | 22 |
| C | 31 | 32 |
のようなものが必要でしょう：
WHERE
label_ids @> ARRAY[11, 12]
OR label_ids @> ARRAY[21, 22]
OR label_ids @> ARRAY[31, 32]
場合によっては、同じオブジェクトに異なるIDを持つ2つの〜バックエンドラベルが適用されるかもしれないので、組み合わせの数はさらに増えることになります。
試みB2：各オブジェクトにラベルのタイトルを保存
オブジェクトの更新という観点から見ると、これは最悪の選択肢です。オブジェクトを一括更新しなければなりません：
- プロジェクト間でオブジェクトが移動した場合。
- プロジェクトがグループから別のグループに移動されます。
- ラベルの名前が変更されました。
- ラベルが削除されました。
また、より多くのストレージを使用します。クエリは簡単です：
WHERE
label_titles @> ARRAY['Plan', 'backend']
イシュー#49651で行ったテストでは、これが高速であることがわかりました。
しかし現時点では、デメリットがメリットを上回っています。
結論
非正規化を必要とせず、クエリのパフォーマンスを大幅に改善する方法A2を発見しました。これは全てのケースに適用できるわけではありませんが、残りのケースにA1の方法を適用することで、全てのシナリオでGROUP BY 、HAVING 節を削除することができました。
これにより、クエリが簡素化され、最も一般的なケースでパフォーマンスが向上しました。