Notes

윈도우함수

6 min read

https://jira.mariadb.org/browse/MDEV-9780?jql=text%20~%20%22window%22

ROW_NUMBER()와 LIMIT의 성능 차이: 결론부터 말씀드립니다.

결론적으로, ROW_NUMBER()를 사용한 쿼리는 테이블의 거의 모든 행을 스캔(Full Index Scan)하게 됩니다. 반면, ORDER BY … LIMIT 5는 인덱스를 사용하여 정확히 5개 행만 효율적으로 찾아오므로 훨씬 빠릅니다.

자세한 설명: 왜 ROW_NUMBER()는 전체를 스캔할까요?

질문하신 쿼리는 다음과 같은 형태일 것입니다.

Generated sql

  `-- ROW_NUMBER()를 사용하는 방식 (비효율적) SELECT * FROM (     SELECT         *,         ROW_NUMBER() OVER (ORDER BY created DESC) as rnum     FROM your_table ) AS t WHERE t.rnum BETWEEN 1 AND 5;`

이 쿼리의 실행 과정을 MariaDB 옵티마이저 입장에서 살펴보겠습니다.

  1. 서브쿼리(Derived Table) 실행: SQL은 쿼리의 논리적 실행 순서가 있습니다. WHERE 절의 rnum 조건을 처리하기 전에, FROM 절에 있는 서브쿼리가 먼저 실행되어야 합니다.

  2. ROW_NUMBER() 계산: 서브쿼리는 your_table의 모든 행에 대해 순위(rnum)를 매겨야 합니다.

    • ORDER BY created DESC: created 컬럼에 인덱스가 있으므로, MariaDB는 이 인덱스를 사용해 데이터를 정렬된 순서로 읽어옵니다. 이 덕분에 별도의 정렬(filesort) 작업은 피할 수 있어 그나마 다행입니다.

    • 하지만 ROW_NUMBER() 함수는 1번부터 100만 번까지 순위를 모두 매겨야 합니다. 이 함수는 “전체 데이터 창(window)“을 기준으로 순번을 붙이는 함수이기 때문에, 5개만 찾고 멈출 수가 없습니다. 100만 개의 모든 행을 순서대로 읽고, 임시로 rnum 컬럼을 추가하여 1, 2, 3, …, 1,000,000 값을 모두 할당합니다.

  3. 임시 테이블 생성: 이 과정에서 100만 건의 데이터와 rnum 컬럼을 가진 거대한 임시 테이블(파생 테이블)이 메모리나 디스크에 생성됩니다.

  4. 외부 WHERE 절 필터링: 이제서야 외부 쿼리가 이 거대한 임시 테이블을 대상으로 WHERE rnum BETWEEN 1 AND 5 조건을 실행하여 최종 5개의 행을 필터링합니다.

결과적으로, 최종 5개를 얻기 위해 100만 건의 데이터를 모두 읽고 순번을 매기는 비효율적인 작업을 수행하게 됩니다. 이는 테이블 풀 스캔은 아니지만, 인덱스 풀 스캔(Full Index Scan)에 가까운 동작으로 매우 느립니다.

가장 효율적인 방법: ORDER BY … LIMIT

최신 5개를 가져오는 가장 표준적이고 효율적인 방법은 LIMIT를 사용하는 것입니다.

Generated sql

  `-- LIMIT를 사용하는 방식 (매우 효율적) SELECT * FROM your_table ORDER BY created DESC LIMIT 5;`

IGNORE_WHEN_COPYING_START

content_copy download

Use code with caution. SQL

IGNORE_WHEN_COPYING_END

이 쿼리의 실행 과정은 다음과 같습니다.

  1. 인덱스 사용: 옵티마이저는 ORDER BY created DESC와 LIMIT 5를 함께 인지합니다.

  2. created 컬럼의 인덱스를 역순(DESC)으로 탐색 시작합니다.

  3. 인덱스를 통해 가장 최신 데이터의 위치를 바로 찾고, 첫 번째 행을 가져옵니다.

  4. 같은 방식으로 인덱스를 따라 4개의 행을 추가로 가져옵니다.

  5. 총 5개의 행을 찾았으므로, LIMIT 5 조건에 따라 즉시 실행을 멈추고 결과를 반환합니다. 100만 개의 나머지 데이터는 전혀 읽지 않습니다.

핵심 차이점 요약

항목ROW_NUMBER() + WHERE rnumORDER BY + LIMIT
작동 방식전체 정렬 → 전체 순번 매기기 → 필터링인덱스로 상위 N개만 찾고 중지
스캔 범위인덱스 전체 또는 테이블 전체인덱스의 일부 (필요한 5개)
성능데이터가 많을수록 매우 느려짐데이터 양과 무관하게 항상 빠름
메모리 사용높음 (임시 테이블 생성)매우 낮음

언제 ROW_NUMBER()를 사용해야 할까요?

ROW_NUMBER()는 LIMIT로 해결할 수 없는, 그룹 내 순위를 매길 때 강력한 힘을 발휘합니다. 예를 들어 “각 사용자별로 가장 최근에 작성한 게시글 3개씩 가져오기”와 같은 복잡한 요구사항에 적합합니다.

Generated sql

  `-- 각 user_id 별로 최신 게시글 3개 가져오기 SELECT * FROM (     SELECT         *,         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created DESC) as rnum     FROM posts ) AS t WHERE t.rnum <= 3;`

IGNORE_WHEN_COPYING_START

content_copy download

Use code with caution. SQL

IGNORE_WHEN_COPYING_END

이 경우에는 LIMIT로 간단히 처리할 수 없으므로 ROW_NUMBER()를 사용하는 것이 올바른 접근 방식입니다.

Graph View