Notes

옵티마이저는 항상 옳지 않음

6 min read

https://mariadb.com/docs/release-notes/mariadb-community-server-release-notes/old-releases/release-notes-mariadb-10-2-series/what-is-mariadb-102#explain 윈도우함수가 10.2에 처음등장햇음;;

아주 좋은 질문입니다! 결론부터 말씀드리면, LIMIT를 사용하는 것이 비교할 수 없을 정도로 훨씬 더 빠르고 효율적입니다.

100만 건 데이터가 있고 등록일(예: created_at)에 인덱스가 걸려있는 상황은 이 차이를 극명하게 보여주는 아주 좋은 예시입니다.

왜 LIMIT가 훨씬 빠를까요?

ORDER BY … LIMIT 구문은 이와 같은 “상위 N개 레코드 조회”에 완벽하게 최적화되어 있습니다.

LIMIT 사용 시 동작 방식 (가장 빠른 시나리오):

Generated sql

  `SELECT *  FROM your_table  ORDER BY created_at DESC  LIMIT 5;`

  1. MariaDB 옵티마이저는 ORDER BY created_at DESC 구문을 봅니다.

  2. created_at 컬럼에 인덱스가 있다는 것을 확인합니다.

  3. 전체 100만 건의 데이터를 스캔하거나 정렬하지 않습니다. 대신, 인덱스를 역순으로 읽기 시작합니다. (Index Reverse Scan)

  4. 인덱스에서 가장 큰 값(가장 최신 날짜)을 가진 첫 번째 항목을 찾고, 해당 데이터 행을 가져옵니다.

  5. 이 과정을 4번 더 반복합니다.

  6. LIMIT 5에 도달했으므로, 5개의 행만 읽고 즉시 쿼리를 종료합니다.

핵심: 이 방식은 전체 100만 건 중 단 5건의 데이터에만 접근하므로 I/O와 CPU 사용량이 극도로 적습니다.

ROW_NUMBER()는 왜 느릴까요?

ROW_NUMBER()는 윈도우 함수(Window Function)로, 행의 순위를 매기는 데 사용됩니다. 이 작업은 본질적으로 더 많은 연산을 필요로 합니다.

ROW_NUMBER() 사용 시 동작 방식:

Generated sql

  `SELECT * FROM (     SELECT          *,         ROW_NUMBER() OVER (ORDER BY created_at DESC) as rn     FROM your_table ) AS ranked_table WHERE rn <= 5;`

IGNORE_WHEN_COPYING_START

content_copy download

Use code with caution. SQL

IGNORE_WHEN_COPYING_END

  1. MariaDB는 서브쿼리(FROM (…)) 부분을 먼저 실행해야 합니다.

  2. ROW_NUMBER() OVER (ORDER BY created_at DESC)는 your_table의 모든 행에 대해 created_at을 기준으로 순위를 매겨야 합니다.

  3. 이를 위해 옵티마이저는 인덱스를 사용하더라도, 1번부터 100만 번까지의 순번을 모두 매기기 위해 전체 100만 건의 데이터를 (논리적으로) 정렬하고 순번을 부여하는 과정을 거칩니다. 이는 막대한 양의 작업입니다.

  4. 이렇게 생성된 100만 건의 결과셋(원본 컬럼 + rn 컬럼)에서 WHERE rn ⇐ 5 조건을 통해 상위 5개를 필터링합니다.

핵심: 단 5건을 얻기 위해 100만 건 전체를 처리해야 하므로, LIMIT에 비해 엄청나게 비효율적입니다. 데이터가 많을수록 성능 저하가 기하급수적으로 커집니다.

※ 최신 버전의 DB 옵티마이저는 ROW_NUMBER() … WHERE rn ⇐ N 패턴을 ORDER BY … LIMIT N으로 자동 최적화해주는 경우도 있지만, 이를 보장할 수 없으며 명시적으로 LIMIT를 쓰는 것이 항상 가장 안전하고 확실한 방법입니다.

성능 비교 요약

항목LIMITROW_NUMBER()
동작 방식인덱스를 이용해 필요한 5건만 접근전체 데이터를 정렬하고 순번 부여 후 필터링
성능 (100만건)매우 빠름 (최적)매우 느림 (데이터가 많을수록 급격히 저하)
자원 사용량최소 (I/O, CPU, 메모리)높음 (임시 데이터셋 생성으로 인한 메모리/CPU 사용)
주요 용도단순 상위/하위 N건 조회그룹 내 순위 매기기 등 복잡한 분석 쿼리

ROW_NUMBER()는 언제 사용하나요?

ROW_NUMBER()는 LIMIT로 해결하기 어려운, 더 복잡한 순위 분석에 사용될 때 강력한 힘을 발휘합니다. 예를 들어, “각 부서별로 가장 최근에 입사한 직원 2명씩 조회” 와 같은 경우입니다.

Generated sql

  `-- 각 department_id 별로 hire_date가 최신인 2명씩 조회 SELECT * FROM (     SELECT         *,         ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY hire_date DESC) as rn     FROM employees ) AS ranked_employees WHERE rn <= 2;`

IGNORE_WHEN_COPYING_START

content_copy download

Use code with caution. SQL

IGNORE_WHEN_COPYING_END

이 쿼리는 PARTITION BY를 사용하여 그룹(department_id) 내에서 순위를 매기므로 LIMIT로는 간단하게 구현할 수 없습니다. 이럴 때 ROW_NUMBER()가 제 역할을 하는 것입니다.

결론

“최신 등록된 5건 조회” 와 같이 단순한 상위 N건을 조회할 때는 고민 없이 ORDER BY … LIMIT 구문을 사용하세요. 이것이 가장 빠르고, 가장 효율적이며, 해당 목적을 위해 만들어진 표준적인 방법입니다.

Graph View