라벨로 필터링

소개

GitLab에는 이슈, Merge Request 및 에픽에 할당할 수있는 라벨이 있습니다. 이러한 객체에있는 라벨은 다대다 관계입니다. 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

특히 다음을 유의하십시오.

  1. 우리가 GROUP BY issues.id를 한 이유는 …
  2. 모든 일치하는 이슈에 두 라벨이 모두 있는지 확인하기 위해 HAVING (COUNT(DISTINCT labels.title) = 2) 조건을 사용합니다.

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

시도 A: WHERE EXISTS

시도 A1: WHERE EXISTS와 여러 서브쿼리 사용

이슈 #37137 및 관련된 Merge Request에서 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 사용

Merge Request #34503에서 A1과 유사한 접근 방식을 취했습니다. 하지만 이번에는 필터에 사용 된 라벨 ID에 대한 JOIN을 피하고 직접적으로 label_links.label_id로 필터링하기위한 별도의 쿼리를 수행했습니다. 또한이 쿼리를 가속화하기위해 label_links에 대한 새로운 인덱스를 추가했습니다​.

라벨 ID를 찾는 것은 명확하지 않았습니다. 왜냐하면 단일 루트 네임 스페이스 내에서 동일한 제목의 여러 라벨이있을 수 있기 때문입니다. 우리는 라벨 ID를 제목별로 그룹화 한 다음 EXISTS 절에서 ID 배열을 사용하여 해결했습니다.

결과적으로 이렇게 최적화되어 성능이 크게 향상되었습니다. 그러나이 최적화는 프로젝트나 그룹 컨텍스트가없는 대시 보드 페이지에는 적용할 수 없었습니다. 여기에서 라벨 ID를 쉽게 검색할 수 없었기 때문에 사용자가 액세스 할 수있는 모든 프로젝트 및 그룹 전체를 검색해야 했습니다.

시도 B: 배열 열을 사용하여 비정규화

GitLab 12.1에서 MySQL 지원 제거를 통해 PostgreSQL의 배열 사용이 더 쉬워졌습니다​. 이를 통해 label_links 테이블을 쿼리하기위한 배열 열의 비정규화를 논의했습니다. label ID 및 제목의 두 가지 옵션이 있습니다.

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

시도 B1: 각 객체에 대한 라벨 ID 저장

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

  1. 라벨이 삭제되거나 프로젝트가 이동 될 경우 데 정규화 된 열을 대량으로 업데이트 할 필요가 없습니다.
  2. 제목보다 적은 리포지터리를 사용합니다.

불행히도 우리의 응용 프로그램 설계는이를 어렵게 만듭니다. 라벨 ID로 쉽게 쿼리 할 수있다면 초기 쿼리에서 INNER JOIN labels가 필요 없을 것입니다. GitLab은 사용자가 프로젝트 및 그룹을 거쳐 라벨 제목으로 필터링할 수 있으므로, 라벨 ~Plan으로 필터링하는 경우 여러 가지 다른 ID로 라벨이 포함될 수 있습니다.

사용자가 다른 ID에 대해 알 필요가 없도록하여 다음 데이터 세트가있는 경우:

프로젝트 ~Plan 라벨 ID ~backend 라벨 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 절을 제거할 수 있었습니다.

이로써 가장 일반적인 경우에 대한 쿼리가 간소화되고 성능이 향상되었습니다.