시스매

데이터베이스 성능 최적화의 비밀 병기 Top N 쿼리 완벽 정복 핸드북

25 min read

2025-08-24 13:28

데이터베이스 성능 최적화의 비밀 병기 Top N 쿼리 완벽 정복 핸드북

데이터의 바다에서 가장 빛나는 보석 몇 개를 찾아야 한다면 어떻게 하시겠습니까? 모든 보석을 하나하나 다 살펴본 후 순위를 매겨 상위 몇 개를 고르는 것은 너무나 비효율적입니다. 데이터베이스의 세계에서도 마찬가지입니다. 수백만, 수억 건의 데이터 중에서 가장 중요한 상위 N개의 결과를 빠르고 효율적으로 가져오는 기술, 바로 Top N 쿼리가 필요한 순간입니다.

이 핸드북은 게임 랭킹 보드부터 쇼핑몰의 베스트 상품 목록, 실시간 인기 뉴스까지, 우리 주변 모든 곳에서 사용되는 Top N 쿼리의 모든 것을 담고 있습니다. 개발자, 데이터 분석가, DBA(데이터베이스 관리자)라면 반드시 알아야 할 이 핵심 기술의 탄생 배경부터 내부 동작 원리, 다양한 데이터베이스별 사용법과 성능을 극한으로 끌어올리는 최적화 비법까지, A to Z를 완벽하게 마스터할 수 있도록 안내해 드리겠습니다.

1. Top N 쿼리는 왜 만들어졌을까? (탄생 배경과 필요성)

컴퓨터 과학의 많은 발전이 그랬듯, Top N 쿼리 역시 ‘불편함’과 ‘비효율’을 해결하기 위해 탄생했습니다.

전통적인 정렬 방식의 문제점

가장 단순한 방법을 생각해 봅시다. ‘연봉이 가장 높은 직원 10명’을 찾으려면 어떻게 해야 할까요?

  1. 모든 직원의 데이터를 데이터베이스에서 가져온다.

  2. 가져온 모든 데이터를 연봉 순서대로 정렬한다. (내림차순)

  3. 정렬된 결과에서 위에서부터 10개를 선택한다.

이 방식은 직원 수가 100명일 때는 문제가 없습니다. 하지만 100만 명, 1억 명이라면 어떨까요? 단 10명을 찾기 위해 1억 명의 데이터를 모두 메모리에 올리고, 정렬하는 과정은 상상만 해도 끔찍한 자원 낭비입니다. 이는 마치 ‘서울에서 가장 키 큰 사람 3명’을 찾기 위해 전 국민의 키를 종이에 적어 운동장에 펼쳐놓고 줄을 세우는 것과 같습니다.

이러한 ‘전체 정렬 후 일부 선택(Sort and Cut)’ 방식은 다음과 같은 심각한 성능 문제를 야기합니다.

  • 메모리 과부하: 전체 데이터를 정렬하기 위해 막대한 양의 메모리를 사용합니다. 데이터가 메모리 용량을 초과하면 디스크를 사용하게 되는데(External Sort), 이로 인해 I/O 병목이 발생하여 성능이 급격히 저하됩니다.

  • CPU 자원 낭비: 수백만 건의 데이터를 정렬하는 것은 CPU에 큰 부담을 줍니다.

  • 느린 응답 속도: 전체 과정이 오래 걸리므로, 사용자에게 결과를 보여주기까지의 시간이 길어집니다. 실시간 랭킹이나 빠른 응답이 필수적인 서비스에서는 치명적입니다.

Top N 쿼리의 등장: 효율성을 향한 진화

이러한 비효율을 해결하기 위해 데이터베이스 시스템은 더 똑똑한 방법을 고안했습니다. “어차피 상위 N개만 필요한데, 굳이 전체를 다 정렬할 필요가 있을까?”라는 질문에서 출발한 것이죠.

Top N 쿼리는 데이터베이스 **옵티마이저(Optimizer)**가 사용자의 의도를 파악하고, 전체를 정렬하는 대신 상위 N개의 후보군만 유지하면서 최소한의 데이터만 처리하는 방식으로 동작합니다. 이는 마치 심사위원이 오디션 참가자 전원을 한 줄로 세우지 않고, 현재까지의 최고점 10명만 기억해두고 새로운 참가자가 그 10명 안에 들 때만 순위를 갱신하는 것과 같습니다.

이러한 접근 방식 덕분에 우리는 필요한 만큼의 메모리와 CPU 자원만 사용하여 훨씬 빠르고 효율적으로 원하는 결과를 얻을 수 있게 되었습니다.

2. Top N 쿼리는 어떻게 동작할까? (구조 및 작동 원리)

Top N 쿼리는 사용자가 보기에는 간단한 SQL 구문이지만, 내부적으로는 데이터베이스 시스템의 정교한 최적화 과정이 숨어있습니다. 데이터베이스 종류마다 문법은 조금씩 다르지만, 목표는 ‘최소 비용으로 상위 N개 찾기’로 동일합니다.

데이터베이스별 구현 방식

1. Oracle: ROWNUM

오라클은 ROWNUM이라는 특별한 가상 컬럼(Pseudo Column)을 사용합니다. ROWNUM은 쿼리 결과로 반환되는 각 행에 대해 부여되는 1부터 시작하는 순번입니다.

-- 연봉 상위 10명 직원 조회 (잘못된 예)
SELECT *
FROM employees
WHERE ROWNUM <= 10
ORDER BY salary DESC;

위 쿼리는 원하는 대로 동작하지 않습니다. ROWNUM은 ORDER BY 절이 실행되기 전에 먼저 적용되기 때문입니다. 즉, 데이터베이스에서 임의의 순서로 가져온 10개의 행에 대해 정렬을 수행할 뿐입니다.

올바른 방법은 인라인 뷰(Inline View) 또는 **서브쿼리(Subquery)**를 사용하는 것입니다.

-- 연봉 상위 10명 직원 조회 (올바른 예)
SELECT *
FROM (
    SELECT *
    FROM employees
    ORDER BY salary DESC
)
WHERE ROWNUM <= 10;

이렇게 하면 먼저 employees 테이블을 연봉 순으로 정렬한 결과 집합을 만들고, 그 결과 집합에 대해 상위 10개의 행을 ROWNUM으로 필터링하므로 정확한 결과를 얻을 수 있습니다.

2. MySQL / PostgreSQL / SQLite: LIMIT

가장 직관적이고 널리 사용되는 방식입니다. ORDER BY로 정렬한 후, LIMIT 절을 사용해 가져올 행의 개수를 제한합니다.

-- 연봉 상위 10명 직원 조회
SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 10;

이 구문은 “연봉을 내림차순으로 정렬한 뒤, 그 결과에서 처음부터 10개만 주세요”라는 의미로 명확하게 해석됩니다.

3. SQL Server: TOP

SQL Server는 SELECT 절 바로 뒤에 TOP (N)을 명시하는 방식을 사용합니다.

-- 연봉 상위 10명 직원 조회
SELECT TOP (10) *
FROM employees
ORDER BY salary DESC;

WITH TIES 옵션을 추가하면, 10위와 동일한 연봉을 받는 직원이 더 있을 경우 그 직원들도 함께 포함하여 반환합니다.

-- 10위와 동점자 포함
SELECT TOP (10) WITH TIES *
FROM employees
ORDER BY salary DESC;

4. 표준 SQL: 윈도우 함수 (Window Functions)

최신 데이터베이스 시스템들은 대부분 SQL 표준인 윈도우 함수를 지원합니다. 윈도우 함수는 Top N 쿼리를 훨씬 더 유연하고 강력하게 만들어 줍니다. 특히 ROW_NUMBER(), RANK(), DENSE_RANK()가 주로 사용됩니다.

함수설명예시 (점수: 100, 90, 90, 80)
ROW_NUMBER()동점 여부와 상관없이 고유한 순위를 부여합니다.1, 2, 3, 4
RANK()동점자에게는 동일한 순위를 부여하고, 다음 순위는 동점자 수를 건너뛰어 계산합니다. (일반적인 순위)1, 2, 2, 4
DENSE_RANK()동점자에게 동일한 순위를 부여하고, 다음 순위를 건너뛰지 않고 바로 다음 번호로 부여합니다.1, 2, 2, 3
-- 윈도우 함수를 이용한 연봉 상위 10명 조회
SELECT *
FROM (
    SELECT
        e.*,
        ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees e
) AS ranked_employees
WHERE rn <= 10;

이 방식은 ‘그룹별 Top N’과 같이 더 복잡한 요구사항을 해결할 때 진정한 힘을 발휘합니다. (예: 각 부서별 연봉 상위 3명)

옵티마이저의 영리한 선택: 실행 계획

중요한 점은 데이터베이스 옵티마이저가 위 SQL 구문들을 보고 **최적의 실행 계획(Execution Plan)**을 수립한다는 것입니다. 만약 salary 컬럼에 인덱스(Index)가 잘 생성되어 있다면, 옵티마이저는 다음과 같이 동작할 가능성이 높습니다.

  1. employees 테이블 전체를 읽는 대신(Full Table Scan), salary 인덱스를 역순으로 스캔합니다.

  2. 인덱스를 통해 10개의 행을 찾으면, 즉시 스캔을 멈춥니다. (STOPKEY)

  3. 찾아낸 10개의 행에 해당하는 실제 테이블 데이터만 가져옵니다.

이 과정은 1억 명의 데이터를 모두 정렬하는 것보다 비교할 수 없을 정도로 빠릅니다. 이것이 바로 Top N 쿼리의 핵심 원리입니다.

3. Top N 쿼리 실전 사용법 (유형별 예제)

이제 실제 시나리오에 적용할 수 있는 다양한 Top N 쿼리 예제를 살펴보겠습니다.

예제 1: 가장 기본적인 Top N (상위 10개)

요구사항: 우리 쇼핑몰에서 가장 비싼 상품 10개를 찾아주세요.

-- MySQL / PostgreSQL
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 10;
 
-- SQL Server
SELECT TOP (10) product_name, price
FROM products
ORDER BY price DESC;
 
-- Oracle
SELECT product_name, price
FROM (
    SELECT product_name, price
    FROM products
    ORDER BY price DESC
)
WHERE ROWNUM <= 10;

예제 2: 페이징 처리 (11번째부터 20번째)

요구사항: 상품 목록의 두 번째 페이지를 보여주세요. (한 페이지에 10개씩)

OFFSET 키워드를 사용하면 지정된 수의 행을 건너뛴 후부터 결과를 가져올 수 있습니다.

-- MySQL / PostgreSQL
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 10 OFFSET 10; -- 10개를 건너뛰고 10개를 가져옴

주의: OFFSET 값이 매우 커지면 (예: OFFSET 1000000), 건너뛰는 행을 읽는 과정에서 성능이 저하될 수 있습니다. 이에 대한 최적화 방법은 심화 파트에서 다룹니다.

예제 3: 그룹별 Top N (가장 중요한 활용 사례)

요구사항: 각 상품 카테고리별로 가장 비싼 상품 3개를 찾아주세요.

이것이 바로 윈도우 함수가 빛을 발하는 순간입니다.

-- 표준 SQL (대부분의 최신 DB에서 지원)
SELECT category_name, product_name, price
FROM (
    SELECT
        c.category_name,
        p.product_name,
        p.price,
        ROW_NUMBER() OVER (PARTITION BY c.category_name ORDER BY p.price DESC) AS rn
    FROM products p
    JOIN categories c ON p.category_id = c.category_id
) AS ranked_products
WHERE rn <= 3;

PARTITION BY 구문이 마법을 부리는 부분입니다. category_name을 기준으로 파티션을 나누고, 각 파티션(그룹) 안에서 price를 기준으로 순위를 매깁니다. 그 후, 각 그룹에서 순위가 3위 안에 드는 결과만 필터링하는 것입니다.

네, 해당 SQL 쿼리가 어떻게 “각 카테고리별 가장 비싼 상품 3개”를 찾아내는지 단계별로 쉽게 설명해 드릴게요.

이 쿼리의 핵심은 **윈도우 함수(Window Function)**인 ROW_NUMBER()를 사용하는 것입니다.

1단계: 내부 쿼리 (FROM 절의 괄호 안) - 순위 매기기

먼저 안쪽에 있는 SELECT 문부터 실행됩니다. 이 부분의 목표는 각 상품에 카테고리 내 순위를 매기는 것입니다.

SQL

SELECT
    c.category_name,
    p.product_name,
    p.price,
    -- 이 부분이 핵심입니다!
    ROW_NUMBER() OVER (PARTITION BY c.category_name ORDER BY p.price DESC) AS rn
FROM products p
JOIN categories c ON p.category_id = c.category_id

  1. FROM ... JOIN ...: products 테이블과 categories 테이블을 category_id를 기준으로 합쳐서 상품 정보와 카테고리 이름을 한 번에 볼 수 있게 만듭니다.

  2. ROW_NUMBER() OVER (...): 여기서 마법이 일어납니다.

    • PARTITION BY c.category_name: 전체 상품 데이터를 카테고리 이름(category_name)을 기준으로 여러 그룹으로 나눕니다. ‘전자제품’, ‘의류’, ‘식품’처럼 각각의 그룹이 생긴다고 생각하시면 됩니다.

    • ORDER BY p.price DESC: 방금 나눈 각 그룹 안에서 상품들을 가격(price)이 비싼 순서대로 정렬합니다.

    • ROW_NUMBER(): 이렇게 정렬된 순서대로 1, 2, 3, … 와 같이 순번을 매깁니다. 이 순번은 PARTITION BY 때문에 각 카테고리 그룹이 시작될 때마다 1번부터 다시 시작됩니다.

    • AS rn: 이렇게 매겨진 순번을 rn이라는 새로운 컬럼 이름으로 저장합니다.

이 내부 쿼리가 실행되고 나면, 아래와 같은 형태의 임시 결과가 만들어집니다.

category_nameproduct_namepricern
전자제품8K TV50000001
전자제품게이밍 노트북25000002
전자제품스마트폰15000003
전자제품블루투스 스피커3000004
의류명품 코트30000001
의류가죽 자켓8000002
의류청바지1500003

2단계: 외부 쿼리 - 최종 필터링

이제 1단계에서 만든 임시 결과를 가지고 바깥쪽 SELECT 문이 실행됩니다.

SQL

SELECT category_name, product_name, price
FROM ( ... 1단계 결과 ... ) AS ranked_products
WHERE rn <= 3;

  1. FROM (...) AS ranked_products: 1단계에서 만든 순위가 매겨진 임시 결과 테이블을 ranked_products라는 이름으로 사용합니다.

  2. WHERE rn <= 3: rn 컬럼(카테고리 내 순위)의 값이 3 이하인 행들만 남깁니다. 즉, 각 카테고리 그룹에서 1, 2, 3위에 해당하는 상품들만 선택하는 것입니다.

  3. SELECT ...: 최종적으로 우리가 보고 싶은 category_name, product_name, price 컬럼만 화면에 보여줍니다.

요약

결론적으로 이 쿼리는 다음과 같은 순서로 동작합니다.

  1. 상품과 카테고리 정보를 합친다.

  2. 카테고리별로 그룹을 나눈 뒤, 각 그룹 안에서 가격 순으로 1, 2, 3… 순위를 매긴다.

  3. 전체 결과에서 순위가 3위 안에 드는 상품들만 골라낸다.

4. Top N 쿼리, 더 빠르게 만들기 (심화 및 최적화)

Top N 쿼리를 올바르게 사용하는 것만으로도 큰 성능 향상을 기대할 수 있지만, 데이터가 많아지면 추가적인 최적화가 필요합니다.

1. 인덱스, 인덱스, 인덱스!

Top N 쿼리 최적화의 90%는 인덱스에 달려있다고 해도 과언이 아닙니다. 옵티마이저가 전체 테이블을 읽지 않고 필요한 부분만 효율적으로 접근하게 해주는 이정표 역할을 합니다.

  • ORDER BY 컬럼에 인덱스 생성: 가장 기본적이고 중요한 최적화입니다. ORDER BY salary DESC 쿼리가 있다면 salary 컬럼에 인덱스가 반드시 필요합니다.
  • 복합 인덱스 활용: WHERE 절과 ORDER BY 절이 함께 사용된다면 복합 인덱스를 고려해야 합니다. 
    -- 쿼리 예시
SELECT *
FROM employees
WHERE department_id = 10
ORDER BY salary DESC
LIMIT 5;
    이 경우, (department_id, salary) 순서로 복합 인덱스를 생성하면 옵티마이저는 특정 부서(10번)의 직원들만 모여있는 인덱스 영역에서 연봉 순으로 5명을 매우 빠르게 찾을 수 있습니다.

2. OFFSET의 함정 피하기: 커서 기반 페이징

앞서 언급했듯이, OFFSET은 페이지 번호가 뒤로 갈수록 성능이 급격히 저하됩니다. 100만 번째 페이지를 보기 위해 1억 개의 데이터를 건너뛰는 작업을 상상해 보세요.

이를 해결하기 위한 더 나은 방법은 커서 기반 페이징(Cursor-based Pagination) 또는 **키셋 페이징(Keyset Pagination)**입니다. 마지막으로 조회한 데이터의 값을 기억해두고, 그 다음부터 N개를 가져오는 방식입니다.

-- 1페이지 (OFFSET 방식)
SELECT * FROM posts ORDER BY id DESC LIMIT 10;
 
-- 2페이지 (OFFSET 방식 - 느릴 수 있음)
SELECT * FROM posts ORDER BY id DESC LIMIT 10 OFFSET 10;
 
-- 2페이지 (커서 기반 페이징 - 빠름)
-- 1페이지의 마지막 게시물 id가 991이라고 가정
SELECT * FROM posts WHERE id < 991 ORDER BY id DESC LIMIT 10;

이 방식은 OFFSET 처럼 건너뛰는 과정 없이 인덱스를 바로 탐색(Index Seek)할 수 있으므로 훨씬 빠릅니다. 무한 스크롤 기능을 구현할 때 필수적인 기법입니다.

3. 실행 계획(Execution Plan) 분석 습관화

“느린 것 같은데, 왜 느릴까?”라는 의문이 들 때, 답은 항상 실행 계획에 있습니다. EXPLAIN, EXPLAIN ANALYZE 등의 명령어를 사용하여 데이터베이스가 여러분의 쿼리를 어떻게 처리하고 있는지 직접 확인하세요.

  • Full Table Scan이 보인다면 인덱스가 제대로 사용되지 않고 있다는 신호입니다.

  • Using filesort (MySQL)가 보인다면 인덱스를 사용하지 못해 디스크 기반의 느린 정렬이 발생하고 있다는 의미입니다.

실행 계획을 읽는 법을 익히는 것은 데이터베이스 성능 전문가로 가는 지름길입니다.

결론: Top N 쿼리, 단순한 기술을 넘어 데이터 활용의 핵심으로

지금까지 Top N 쿼리의 모든 것을 탐험했습니다. 단순히 데이터를 정렬하고 일부를 잘라내는 기능에서 시작했지만, 그 내부에는 데이터베이스 시스템의 깊은 고민과 효율성을 향한 노력이 담겨있습니다.

기억해야 할 핵심은 다음과 같습니다.

  1. 목적을 명확히 하라: 전체 데이터가 필요한지, 상위 N개만 필요한지 명확히 구분하여 쿼리를 작성해야 합니다.

  2. 데이터베이스에 맞는 문법을 사용하라: LIMIT, TOP, ROWNUM, 윈도우 함수 등 자신의 환경에 맞는 최적의 구문을 선택하세요.

  3. 그룹별 Top N은 윈도우 함수를 기억하라: 복잡한 분석 쿼리의 문을 열어주는 열쇠입니다.

  4. 인덱스는 선택이 아닌 필수다: ORDER BY 컬럼에 대한 인덱스 설계는 Top N 쿼리 성능의 핵심입니다.

  5. 실행 계획을 친구로 삼아라: 데이터베이스와의 대화 창구인 실행 계획을 통해 성능 병목의 원인을 찾아 해결하세요.

Top N 쿼리를 자유자재로 다룰 수 있게 되면, 대용량 데이터 속에서 비즈니스 가치를 빠르고 정확하게 찾아내는 강력한 무기를 손에 쥐게 되는 것입니다. 이 핸드북이 여러분의 데이터 여정에 든든한 나침반이 되기를 바랍니다.

레퍼런스(References)

Top N 쿼리

그래프 뷰

백링크