[Support Bulletin 11] - 10M row 테스트로 본 DENSE_RANK() 성능의 진짜 변수

안녕하세요! 😊

에이플랫폼 Support Bulletin의 열한 번째 이야기로 찾아뵙게 되었습니다.

이번 편에서는 많은 분들이 자주 활용하시는 dense_rank 함수가 기대만큼 빠르지 않을 때 생기는 고민에 대해 이야기해보려 합니다.

순위 계산을 위해 dense_rank를 적용했지만, 성능 저하로 인해 처리 시간이 길어지고, 결국 전체 쿼리 성능까지 영향을 받는 경험... 혹시 한 번쯤 겪어보셨나요?

이번 글에서는 저희가 실제 프로젝트 중 겪었던 문제 사례와 함께, 이를 해결할 수 있었던 실전 팁을 소개드릴 예정입니다.

 

---

순위 함수란

SQL에서 데이터를 정렬하거나 순위를 매길 때 자주 사용하는 윈도우 함수들이 있습니다.

그 중 가장 대표적인 순위 함수는 ROW_NUMBER(), RANK(), DENSE_RANK()입니다.

이 함수들은 모두 순위를 계산하는 역할을 하지만, _동일한 값에 대한 처리 방식_에서는 차이가 있습니다.

다음은 예제를 통해 차이를 살펴보겠습니다

SELECT name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC)     AS row_num,
       RANK()      OVER (ORDER BY score DESC)     AS rank_val,
       DENSE_RANK() OVER (ORDER BY score DESC)    AS dense_rank_val
FROM   sample_table;

sample_table 에 담긴 데이터는 아래와 같습니다

name	score
Alice	95
Bob	95
Carol	90
Dave	85

위 쿼리 결과

+-------+-------+---------+----------+----------------+
| name  | score | row_num | rank_val | dense_rank_val |
+-------+-------+---------+----------+----------------+
| Bob   |    95 |       1 |        1 |              1 |
| Alice |    95 |       2 |        1 |              1 |
| Carol |    90 |       3 |        3 |              2 |
| Dave  |    85 |       4 |        4 |              3 |
+-------+-------+---------+----------+----------------+
4 rows in set (0.09 sec)

🔍 함수별 특징

• ROW_NUMBER()는 각 행마다 고유한 번호를 부여하며, 동점은 고려하지 않습니다.
• RANK()는 동점자에게 같은 순위를 부여하며, 다음 순위는 건너뜁니다.
• DENSE_RANK()는 동점자에게 같은 순위를 부여하고, 다음 순위는 건너뛰지 않습니다.

이처럼 세 함수는 결과가 유사해 보일 수 있지만, 실제 로직이나 처리 비용에서는 큰 차이를 유발할 수 있습니다.

특히 DENSE_RANK()는 대용량 데이터에서 성능에 민감할 수 있는 함수이기도 합니다.

그렇다면 DENSE_RANK()가 실제로 얼마나 영향을 줄 수 있는지 예제를 통해 보여드리고, 대안에 대해 다뤄보겠습니다.

 

---

대체 구현 쿼리 소개

SingleStore에서 DENSE_RANK 함수는 일부 상황에서 성능 저하를 유발할 수 있습니다. 이런 경우, CTE(Common Table Expression)와 ROW_NUMBER를 활용한 방식으로 동일한 결과를 얻을 수 있습니다.

다음은 실제 쿼리 예시입니다

WITH ordered_keys AS (
  SELECT DISTINCT c1, c2, c3
  FROM t1
),
ranked_keys AS (
  SELECT c1, c2, c3,
         ROW_NUMBER() OVER (ORDER BY c1, c2, c3) AS rk
  FROM ordered_keys
)
SELECT t.*, r.rk AS dense_rank
FROM t1 t
LEFT JOIN ranked_keys r
  ON t.c1 <=> r.c1 AND t.c2 <=> r.c2 AND t.c3 <=> r.c3

 

이 방식이 DENSE_RANK를 대체할 수 있는 이유

1. 중복 그룹 기반 순위 계산DENSE_RANK는 동일한 값 그룹에 같은 순위를 부여하는 특징이 있는데, 이 쿼리에서는 먼저 DISTINCT로 고유 그룹을 추출 (ordered_keys)하고, 그 그룹에 ROW_NUMBER()를 적용하여 고유 번호를 부여함으로써 같은 효과를 만듭니다.
2. Join을 통한 순위 확산 이후 원본 테이블과 고유 그룹 순위 정보를 LEFT JOIN하면서 각 행이 속한 그룹의 순위(rk)를 가져오는 방식입니다. 즉, DENSE_RANK()에서 자동으로 처리되는 그룹 기반 순위 부여를 명시적이고 효율적인 방식으로 구현한 것입니다.
3. 성능 측면의 이점

• DISTINCT + ROW_NUMBER() 방식은 불필요한 전체 데이터 정렬을 피하고, 윈도우 함수가 전체 데이터셋이 아닌 소수의 고유 키셋에만 작동하게 하여 CPU 부하를 줄입니다.
• 원래 DENSE_RANK()는 PARTITION BY 없을 시 Aggregator 노드에서 전체 데이터를 처리하는 구조인데, 이 방식은 그런 병목을 미리 제거할 수 있습니다.

결과적으로 이 커스텀 구현 방식은 DENSE_RANK와 논리적으로 동일한 순위 결과를 제공하면서, 중복된 그룹이 많고 전체 데이터를 집약 처리하는 환경에서는 성능 이점을 가질 수 있습니다.

반면, 데이터에 중복이 거의 없거나 순위 그룹화가 단순할 경우에는 오히려 DENSE_RANK() 함수가 더 빠르게 작동할 수도 있으므로, 데이터 특성과 실행 계획에 따라 전략적으로 선택하는 것이 중요합니다.

 

---

비교 테스트

DENSE_RANK()와 대체 구현 방식의 성능 차이를 중복 조합이 있을 경우와 없는 경우로 나누어 비교해보았습니다.

테스트 환경

• 노드 구성: 1 MasterAggregator + 1 LeaF
• 각 노드 사양 (MA, LF 동일 사양): 4 vCPU, 8 GB RAM
• SingleStore Version: 8.9.27
• Test Dataset: 10,000,000 rows

Test Table Schema

# 중복 조합 존재
CREATE TABLE t1 (
  id INT PRIMARY KEY,
  c1 VARCHAR(100),
  c2 VARCHAR(100),
  c3 INT);

# 중복 조합 없음
CREATE TABLE t1_unique (
  id INT PRIMARY KEY,
  c1 VARCHAR(20),
  c2 VARCHAR(20),
  c3 VARCHAR(20)
 );

 

 

---

테스트 데이터 예시

• 중복 조합이 존재하는 경우
• c1, c2: 알파벳 대/소문자 1글자 랜덤
• c3: 1~9 사이의 랜덤 정수
• 중복이 자주 발생하는 조합 (예: A, a, 1이 수천 건 존재 가능)

실 데이터 예시

SELECT * FROM t1 LIMIT 3;
>>>
+---------+------+------+------+
| id      | c1   | c2   | c3   |
+---------+------+------+------+
| 3760007 | L    | e    | 2    |
| 3760015 | Y    | x    | 5    |
| 3760027 | g    | z    | 4    |
+---------+------+------+------+

 

• 중복 조합이 없는 경우
• 1천만 건의 row에 대해 (c1, c2, c3) 조합이 모두 고유

실 데이터 예시

SELECT * FROM t1_unique LIMIT 3;
>>>
+---------+------------+------------+------------+
| id      | c1         | c2         | c3         |
+---------+------------+------------+------------+
| 9300005 | C1_9300005 | C2_9300006 | C3_9300007 |
| 9300009 | C1_9300009 | C2_9300010 | C3_9300011 |
| 9300038 | C1_9300038 | C2_9300039 | C3_9300040 |
+---------+------------+------------+------------+

 

---

테스트 쿼리

DENSE_RANK() 함수를 사용한 쿼리

SELECT *,
DENSE_RANK() OVER (ORDER BY c1, c2, c3) AS dense_rank
FROM {t1, t1_unique}
limit 10;

 

CTE + Rownumber 대체 구현 쿼리

WITH ordered_keys AS (
SELECT DISTINCT c1, c2, c3
FROM {t1, t1_unique}
),
ranked_keys AS (
SELECT c1, c2, c3,
ROW_NUMBER() OVER (ORDER BY c1, c2, c3) AS rk
FROM ordered_keys
)
SELECT t.*, r.rk AS dense_rank
FROM {t1, t1_unique} t
LEFT JOIN ranked_keys r
ON t.c1 <=> r.c1 AND t.c2 <=> r.c2 AND t.c3 <=> r.c3
limit 10;

 

테스트 결과

먼저 DENSE_RANK() 함수를 사용한 쿼리는 11.50 초, 14.22 초가 걸렸습니다.

# 중복 조합 O
+---------+------+------+------+------------+
| id      | c1   | c2   | c3   | dense_rank |
+---------+------+------+------+------------+
| 2341717 | A    | A    | 1    |          1 |
| 3835499 | A    | a    | 1    |          1 |
| 8545678 | a    | A    | 1    |          1 |
| 2269542 | a    | A    | 1    |          1 |
| 3975399 | A    | a    | 1    |          1 |
| 8631827 | a    | a    | 1    |          1 |
| 2549855 | a    | a    | 1    |          1 |
| 2595927 | A    | A    | 1    |          1 |
| 3982119 | A    | A    | 1    |          1 |
| 4073662 | a    | a    | 1    |          1 |
+---------+------+------+------+------------+
10 rows in set (11.50 sec)

# 중복 조합 X
+----+------------+------------+------------+------------+
| id | c1         | c2         | c3         | dense_rank |
+----+------------+------------+------------+------------+
|  1 | C1_0000001 | C2_0000002 | C3_0000003 |          1 |
|  2 | C1_0000002 | C2_0000003 | C3_0000004 |          2 |
|  3 | C1_0000003 | C2_0000004 | C3_0000005 |          3 |
|  4 | C1_0000004 | C2_0000005 | C3_0000006 |          4 |
|  5 | C1_0000005 | C2_0000006 | C3_0000007 |          5 |
|  6 | C1_0000006 | C2_0000007 | C3_0000008 |          6 |
|  7 | C1_0000007 | C2_0000008 | C3_0000009 |          7 |
|  8 | C1_0000008 | C2_0000009 | C3_0000010 |          8 |
|  9 | C1_0000009 | C2_0000010 | C3_0000011 |          9 |
| 10 | C1_0000010 | C2_0000011 | C3_0000012 |         10 |
+----+------------+------------+------------+------------+
10 rows in set (14.22 sec) 

 

대체 구현 쿼리는 0.55 초, 38.38초가 걸렸습니다.

# 중복 조합 O
+---------+------+------+------+------------+
| id      | c1   | c2   | c3   | dense_rank |
+---------+------+------+------+------------+
| 7331631 | K    | P    | 3    |       2478 |
| 7331650 | C    | q    | 4    |        616 |
| 7331658 | J    | v    | 9    |       2304 |
| 7331671 | g    | p    | 5    |       1544 |
| 7331686 | G    | x    | 2    |       1613 |
| 7331687 | H    | x    | 6    |       1851 |
| 7331689 | W    | m    | 4    |       5260 |
| 7331696 | u    | u    | 9    |       4869 |
| 7331700 | D    | B    | 1    |        712 |
| 7331707 | w    | H    | 8    |       5219 |
+---------+------+------+------+------------+
10 rows in set (0.55 sec)

# 중복 조합 X
+---------+------------+------------+------------+------------+
| id      | c1         | c2         | c3         | dense_rank |
+---------+------------+------------+------------+------------+
| 9300005 | C1_9300005 | C2_9300006 | C3_9300007 |    9300006 |
| 9300100 | C1_9300100 | C2_9300101 | C3_9300102 |    9300101 |
| 9300194 | C1_9300194 | C2_9300195 | C3_9300196 |    9300195 |
| 9300196 | C1_9300196 | C2_9300197 | C3_9300198 |    9300197 |
| 9300222 | C1_9300222 | C2_9300223 | C3_9300224 |    9300223 |
| 9300294 | C1_9300294 | C2_9300295 | C3_9300296 |    9300295 |
| 9300332 | C1_9300332 | C2_9300333 | C3_9300334 |    9300333 |
| 9300357 | C1_9300357 | C2_9300358 | C3_9300359 |    9300358 |
| 9300461 | C1_9300461 | C2_9300462 | C3_9300463 |    9300462 |
| 9300482 | C1_9300482 | C2_9300483 | C3_9300484 |    9300483 |
+---------+------------+------------+------------+------------+
10 rows in set (38.38 sec)

---

테스트 결과 요약

조합 유형	방식	실행 시간	특징
중복 있음	DENSE_RANK()	11.50초	그룹화 부담으로 성능 저하 발생
	CTE + ROW_NUMBER() 구현	0.55초	고유 그룹만 처리 → 빠르게 작동
중복 없음	DENSE_RANK()	14.22초	내부 최적화 덕분에 안정적 성능
	CTE + ROW_NUMBER() 구현	38.38초	불필요한 오버헤드로 처리 시간 증가

이번 테스트를 통해 DENSE_RANK()와 그 대체 구현 방식의 성능은 데이터의 중복 여부에 따라 극명하게 달라질 수 있음을 확인했습니다.

그렇다면, 성능 차이를 만들어낸 그 핵심 요인은 무엇일까요?

 

---

성능 차이 이유

DENSE_RANK() 함수는 내부적으로 다음과 같은 순서로 동작합니다.

1. 전체 테이블 정렬 (ORDER BY)
2. 동일한 값 그룹 식별
3. 각 그룹에 순위 부여

이 작업은 원본 테이블 전체를 대상으로 수행되며, 특히 PARTITION BY 절이 없을 경우 SingleStore의 Aggregator 노드에서 중앙 집중식 처리가 발생합니다.

이 구조에서는 모든 row를 Aggregator 노드로 이동시켜 정렬하고 그룹화해야 하므로, 네트워크 부하, 메모리 소비, 병목 현상이 심화될 수 있습니다.

 

반면, CTE를 활용한 대체 쿼리는 다음과 같은 방식으로 동작합니다

1. DISTINCT로 순위 기준의 중복 조합을 제거
2. ROW_NUMBER()로 고유 조합에만 순위 부여
3. 원본 테이블과 조인하여 순위 매핑

이 방식은 윈도우 함수가 처리해야 할 row 수를 대폭 줄이는 구조로 설계되었기 때문에,

정렬 및 계산 대상이 작아지고, 병렬 처리도 보다 효율적으로 이루어질 수 있습니다.

 

하지만 주의할 점도 있습니다.

대체 구현 방식의 핵심은 DISTINCT로 중복 조합을 먼저 줄이는 것에 있는데, 만약 데이터셋 자체가 이미 모든 (c1, c2, c3) 조합이 고유하거나 중복이 많지 않다면, DISTINCT로 줄어드는 row 수가 거의 없게 됩니다.

이 경우에는 오히려 다음과 같은 오버헤드가 발생할 수 있습니다

• 불필요하게 고유 조합을 추출하고
• 그에 대해 ROW_NUMBER()를 부여한 뒤
• 다시 원본 테이블과 JOIN을 수행

즉, 처리 단계가 늘어나며 전체 쿼리 복잡도가 상승하고, 성능이 DENSE_RANK()보다 더 낮아질 수 있습니다.

실제로 테스트 결과에서도 중복이 거의 없는 경우엔 DENSE_RANK()가 더 빠르게 실행되었음을 확인할 수 있습니다.

 

---

DENSE_RANK()와 그 대체 구현 방식 모두, 각자의 강점과 한계가 분명히 존재합니다.

중요한 건 어느 방식이 항상 빠르냐가 아니라, 내가 다루는 데이터셋과 서비스의 특성을 정확히 이해하고 상황에 맞는 전략을 선택하는 것입니다.

• 대량의 중복 그룹이 존재하고 성능이 중요한 환경이라면 ROW_NUMBER() 기반의 커스텀 구현이 유리할 수 있고,
• 반대로 고유한 조합이 많거나 쿼리 구조가 단순해야 할 경우엔 DENSE_RANK()가 더 적합할 수도 있습니다.

결국 성능 최적화란 단순히 함수를 바꾸는 문제가 아니라,

실행 계획을 이해하고, 처리 구조를 설계하는 일이 더 중요한 것 같습니다.