레이블별 필터링

소개

GitLab에는 이슈, 병합 요청 및 이야기에 할당할 수 있는 레이블이 있습니다. 이러한 객체의 레이블은 다대다 관계로 다형성 label_links 테이블을 통해 연결됩니다.

다중 레이블로 이러한 객체를 필터링하려면, 예를 들어 ‘레이블 ~계획과 레이블 ~백엔드가 있는 모든 오픈 이슈’와 같이 쿼리를 생성합니다. 간단한 형식으로는 다음과 같습니다: 

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를 사용하여 …
  2. HAVING (COUNT(DISTINCT labels.title) = 2) 조건을 사용하여 모든 일치하는 이슈가 두 개의 레이블을 가지도록합니다.

이것은 이상적인 것보다는 더 복잡합니다. 쿼리 구성이 오류(예: 이슈 #15557)에 민감해집니다.

시도 A: WHERE 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과 유사한 방법을 채용했습니다. 그러나 이번에는 필터에 사용된 레이블의 ID를 직접 label_links.label_id로 필터링하기 위해 별도의 쿼리를 수행했습니다. 또한이 쿼리를 가속화하기 위해 label_links에 대한 target_id, label_id 및 target_type 열에 새로운 인덱스를 추가했습니다.

레이블 ID를 찾는 것은 간단하지 않았습니다. 하나의 루트 네임스페이스 내에서 동일한 제목의 여러 레이블이 있을 수 있기 때문에 레이블 ID를 찾는 것이었습니다. 이 문제를 해결하기 위해 레이블 ID를 제목별로 그룹화하고 EXISTS 절에서 ID 배열을 사용했습니다.

이로 인해 상당한 성능 향상이 이뤄졌습니다. 그러나이 최적화는 프로젝트나 그룹 컨텍스트가 없는 대시보드 페이지에는 적용할 수 없었습니다. 여기서 레이블 ID를 쉽게 검색할 수 없었기 때문에 사용자가 액세스 할 수있는 모든 프로젝트 및 그룹을 대상으로 검색하는 것을 의미했습니다.

시도 B: 배열 열 사용을 통한 정규화

GitLab 12.1에서 MySQL 지원 중단을 완료한 후, PostgreSQL 배열의 사용이 더 많아졌습니다. 우리는 label_links 테이블을 질의하기 위해 정규화 옵션 두 가지인 레이블 ID 및 제목과 함께 이슈 #49651에서 논의했습니다.

이러한 배열 열은 일치를 개선하기 위해 GIN 인덱스와 함께 사용할 수 있습니다.

시도 B1: 객체별 레이블 ID 저장

이것은 제목보다 강력한 장점이 있습니다.

  1. 레이블이 삭제되거나 프로젝트가 이동되지 않는 한, 정규화된 열을 대량으로 업데이트 할 필요가 없습니다.
  2. 제목보다 적은 저장소를 사용합니다.

유감스럽게도 애플리케이션 설계로 인해이 것은 어렵습니다. 레이블 ID로 쉽게 질의할 수 있다면 초기 문서의INNER JOIN labels가 필요하지 않았을 것입니다. GitLab은 사용자가 프로젝트 및 그룹을 모두 거쳐 레이블 제목별로 필터링할 수 있으므로 레이블 ~계획에 의한 필터링은 여러 고유한 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로- 있는 경우, 조합의 수는 더욱 늘어날 수 있습니다.

시도 B2: 각 객체에 대한 저장소 레이블 제목

객체를 업데이트하는 관점에서 이것은 최악의 옵션입니다. 다음 경우에 객체를 대량으로 업데이트해야 합니다:

  1. 객체가 한 프로젝트에서 다른 프로젝트로 이동될 때
  2. 프로젝트가 한 그룹에서 다른 그룹으로 이동될 때
  3. 레이블이 이름이 변경될 때
  4. 레이블이 삭제될 때

이 방법은 훨씬 더 많은 저장소를 사용합니다. 하지만 쿼리는 간단합니다: 

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

또한, 우리의 이슈 #49651의 테스트에서 이 방법이 빠를 수 있다는 것을 보여줬습니다.

하지만 현재로서는 단점이 장점을 상쇄시키는 상황입니다.

결론

우리는 정규화가 필요없고 쿼리 성능을 크게 향상시키는 A2 방법을 찾았습니다. 이것은 모든 경우에 적용되는 것은 아니지만, 나머지 경우에는 A1 방법을 적용하여 모든 시나리오에서 GROUP BY 및 HAVING 절을 제거하도록 했습니다.

이로써 가장 흔한 경우에 쿼리를 간소화시키고 성능을 향상시켰습니다.