효율적인 IN 연산자 쿼리

GitLab에서 IN SQL 연산자를 사용하는 효율적인 정렬 데이터베이스 쿼리 구축 기법과 이를 적용하는 유틸리티 모듈 사용 방법을 설명합니다.

이 문서는 IN SQL 연산자를 사용하여 효율적인 정렬 데이터베이스 쿼리를 구축하는 기법과, 해당 기법을 적용하는 데 도움이 되는 GitLab 유틸리티 모듈 사용 방법을 설명합니다. 설명된 기법은 keyset 페이지네이션 을 많이 활용합니다. 먼저 해당 주제에 익숙해지는 것을 권장합니다. 동기 # GitLab에서 Issue 와 같은 많은 도메인 객체는 프로젝트와 그룹의 중첩 계층 구조 아래에 존재합니다. 그룹 레벨에서 도메인 객체의 중첩 데이터베이스 레코드를 가져오기 위해 우리는 종종 IN SQL 연산자를 사용하여 쿼리를 수행합니다. 성능을 위해 일반적으로 ORDER BY 와 LIMIT 절을 사용하여 일부 속성으로 레코드를 정렬하고 레코드 수를 제한하는 데 관심이 있습니다. 페이지네이션은 이후 레코드를 가져오는 데 사용될 수 있습니다. 그룹 레벨에서 중첩 도메인 객체를 쿼리해야 하는 예시 작업: gitlab-org 그룹에서 생성일 또는 마감일 기준으로 처음 20개의 이슈 표시. gitlab-com 그룹에서 머지된 날짜 기준으로 처음 20개의 머지 리퀘스트 표시. 안타깝게도 정렬된 그룹 레벨 쿼리는 실행 시 무거운 I/O, 메모리, 연산이 필요하기 때문에 일반적으로 성능이 좋지 않습니다. 그러한 쿼리 하나를 심층적으로 살펴봅시다. IN 쿼리의 성능 문제 # 다음 쿼리로 gitlab-org 그룹에서 가장 오래된 이슈 20개를 가져오는 작업을 고려해 봅시다: SELECT "issues".* FROM "issues" WHERE "issues"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" IN (SELECT traversal_ids[array_length(traversal_ids, 1)] AS id FROM "namespaces" WHERE (traversal_ids @> ('{9970}')))) ORDER BY "issues"."created_at" ASC, "issues"."id" ASC LIMIT 20 페이지네이션을 위해 created_at 칼럼으로 정렬하는 것만으로는 충분하지 않으며, 타이 브레이커 로 id 칼럼을 추가해야 합니다. 쿼리 실행은 크게 세 단계로 나눌 수 있습니다: 데이터베이스는 namespaces 와 projects 테이블 모두에 접근하여 그룹 계층 구조의 모든 그룹에서 모든 프로젝트를 찾습니다. 데이터베이스는 각 프로젝트의 issues 레코드를 검색하여 무거운 디스크 I/O를 발생시킵니다. 이상적으로는 적절한 인덱스 구성이 이 프로세스를 최적화해야 합니다. 데이터베이스는 메모리에서 issues 행을 created_at 기준으로 정렬하고 최종 사용자에게 LIMIT 20 행을 반환합니다. 대규모 그룹의 경우, 이 마지막 단계에서 대용량 메모리와 CPU 리소스가 모두 필요합니다. 이 DB 쿼리의 실행 계획: Limit (cost=90170.07..90170.12 rows=20 width=1329) (actual ti