[SQL] 대용량 데이터 환경에서 쿼리

대용량 데이터 환경에서 성능을 고려한 쿼리 설계나 아키텍처 구조를 이해하기 위해서는, '비용(cost)' 중심의 사고방식이 기본이 되어야 한다. 여기서 말하는 비용은 CPU, Memory, Disk I/O, Network I/O, Function Overhead, Scan Range 등 리소스 사용량을 의미함.

1. 성능 최적화 기준

1. 쿼리 비용 계산의 사고 방식

리소스 타입	비용 요소	설명	피해야 할 패턴
CPU	연산량 (계산식, 함수 호출)	집계 함수, 스칼라 서브쿼리, UDF 등	WHERE ABS(col1) = 10, SELECT SLEEP(5)
메모리	정렬, 해시 테이블, 중간 결과 저장	GROUP BY, ORDER BY, JOIN, WINDOW FUNCTION 등	과도한 DISTINCT, 중첩 GROUP BY
디스크 I/O	읽는 데이터 양 (row 수 × column 수)	Full Table Scan, Wide Column Fetching	SELECT *, 필터 조건 없는 조회
네트워크 I/O	분산 환경에서 노드 간 데이터 이동	분산 JOIN, SHUFFLE, BROADCAST	다른 노드에 있는 테이블과의 JOIN

2. 스캔 범위 추정 기준

필터 조건의 유무

• WHERE가 없으면 Full Scan 발생. 이건 대부분의 DB에서 가장 느린 쿼리 유형.

컬럼별 Selectivity

• WHERE gender = 'M' (선택률 50%)보다 WHERE age > 90 (선택률 1%)이 더 강한 필터 조건.

인덱스 적용 가능 여부

• 범위조건(>, <, BETWEEN)은 Index 사용 가능. LIKE '%abc%'는 불가.

컬럼 수 영향

• 행 수는 동일해도, 가져오는 컬럼이 많을수록 메모리/네트워크 사용량이 증가.

3. GROUP BY 관련 기준

GROUP BY는 레코드를 줄이지만, 메모리 사용량은 증가한다.

• 줄이는 건 맞지만, 내부적으로 정렬 또는 해시 테이블을 만들기 때문에 메모리 사용이 증가함.

KEY 수 (cardinality)가 높을수록 더 많은 메모리를 사용한다.

• GROUP BY user_id (수천만건) >> GROUP BY country (200개 이하)

중첩 GROUP BY vs ROLLUP/ CUBE

• 중첩 집계는 일반적으로 별도 쿼리로 나누거나 ROLLUP을 쓰는 것이 좋음.

4. 함수 호출 비용 기준

스칼라 함수, 사용자 정의 함수(UDF)는 반복 호출된다.

• SELECT id, my_udf(col1) FROM big_table → 수백만 번 실행됨.

함수를 WHERE나 JOIN 조건에 쓰는 것은 매우 비싸다.

• WHERE TO_CHAR(date_col, 'YYYY') = '2023' → 인덱스 무력화 + 전 행 평가

가능한 계산은 미리 처리해두는 것이 좋다.

• 전처리 또는 ETL 과정에서 컬럼을 만들어두는 것이 좋음 (예: 연도 컬럼 따로 생성)

5. 조인과 연산의 비용 기준

JOIN은 항상 두 테이블을 모두 스캔할 필요가 있다.

• 특히 분산 환경에서 SHUFFLE JOIN은 병목의 주요 원인.

HASH JOIN은 적은 메모리로 빠르지만, 큰 테이블끼리는 성능 저하

• 작은 테이블을 먼저 읽어야 빠른 성능 확보 가능.

Nested Loop JOIN은 소규모에만 적합

• SELECT ... FROM A, B WHERE A.key = B.key 구조에서 인덱스 없으면 전수비교 발생

6. 정렬 / 윈도우 함수 기준

ORDER BY는 매우 느릴 수 있다.

• 데이터가 메모리에서 정렬 불가능하면 디스크 정렬 발생 (external sort)

WINDOW 함수는 Partition 수와 정렬 범위에 따라 비용 폭발 가능

• PARTITION BY user_id ORDER BY timestamp 는 user 수만큼 정렬 작업 발생

7. 실행계획 기반 의사결정 기준

EXPLAIN (MySQL, PostgreSQL), EXPLAIN ANALYZE

• Table Scan인지, Index Scan인지, Nested Loop인지 확인 가능
• 예상 row 수 / 실제 row 수 / cost 추정치를 비교해서 병목 구간 추정

---

8. 쿼리 최적화 시 의사결정 순서

1. Scan 양이 많은가? → WHERE 조건이 적절한가?

2. Index 활용이 가능한가? → 함수나 연산으로 Index를 무력화하고 있지는 않은가?

3. 필요한 컬럼만 SELECT 하고 있는가?

4. JOIN의 순서, 방식은 적절한가?

5. GROUP BY/ORDER BY가 필수적인가, 아니면 사전 정렬이 가능한가?

6. 서브쿼리/CTE/함수는 반복 호출되고 있지 않은가?

7. 쿼리 결과의 크기 자체가 크지는 않은가?

---

2. 서브쿼리가 필요한 경우와 비용이 큰 경우 구분

서브쿼리(Subquery)는 복잡한 로직을 캡슐화하거나 중간 결과를 구성하는 데 유용하지만, 대용량 환경에서는 성능 이슈의 주요 원인이 되기도 한다.

1. 서브쿼리가 필요한 경우와 비용이 큰 경우 구분

유형	서브쿼리 예시	설명	비용 이슈 가능성
스칼라 서브쿼리	SELECT (SELECT MAX(age) FROM users)	단일 값 반환	매우 높은 반복 비용
인라인 뷰	SELECT * FROM (SELECT ... ) AS t	쿼리 결과를 테이블처럼 사용	인덱스 상실, 중간 정렬
WHERE절 서브쿼리	WHERE user_id IN (SELECT user_id FROM ...)	필터링 용도	서브쿼리 실행 횟수에 따라 비용 증가
EXISTS / NOT EXISTS	WHERE EXISTS (SELECT 1 FROM ...)	조건 존재 여부 확인	종속적 쿼리일 경우 비싸짐
CORRELATED 서브쿼리	SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE B.id = A.id)	외부 쿼리의 값을 내부에서 참조	반복 실행으로 매우 비쌈

2. 상황별 서브쿼리 최적화 전략

2.1 스칼라 서브쿼리 최적화 (SELECT 안에 SELECT)

SELECT name, (SELECT MAX(score) FROM scores WHERE scores.user_id = users.id) AS max_score
FROM users

• 문제점: users row 수만큼 scores 테이블을 다시 스캔함 → N회 반복 실행

해결: JOIN으로 리팩터링

SELECT u.name, s.max_score
FROM users u
LEFT JOIN (
    SELECT user_id, MAX(score) AS max_score
    FROM scores
    GROUP BY user_id
) s ON u.id = s.user_id

2.2 IN / NOT IN 서브쿼리 최적화

SELECT name
FROM users
WHERE id IN (SELECT user_id FROM orders WHERE order_date >= '2025-01-01')

• 문제점: 서브쿼리 결과가 크면 중첩 Loop or 임시 테이블로 느림. 특히 NOT IN은 NULL 존재 시 매우 위험

해결: JOIN 또는 EXISTS로 리팩터링

-- JOIN 방식 (데이터 크기가 작을 때 적합)
SELECT DISTINCT u.name
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.order_date >= '2025-01-01'

-- EXISTS 방식 (존재 여부만 따질 때 빠름)
SELECT u.name
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.order_date >= '2025-01-01'
)

IN과 EXISTS의 선택 기준

상황	추천 방식	이유
서브쿼리 결과가 작다	IN	빠르고 간단
메인 쿼리 결과가 작다	EXISTS	빠름 (메인 -> 서브 존재 여부 확인)
NULL 포함 가능성 있음	EXISTS	NOT IN은 NULL 때문에 결과 왜곡됨
조인 없이 존재 여부만 필요	EXISTS	비용 최소화

WITH절 (CTE)로 추출 + 최적화 힌트 활용

WITH active_users AS (
  SELECT user_id FROM logs WHERE activity = 'active'
)
SELECT name FROM users WHERE id IN (SELECT user_id FROM active_users)

최적화 방법

• CTE 결과가 반복적으로 쓰이면 → MATERIALIZED 힌트 고려 (DBMS에 따라 다름)
• 단일 사용시 IN → JOIN으로 전환이 유리

인라인 뷰로 인한 정렬, 중간 집계 비용 줄이기

SELECT *
FROM (
  SELECT user_id, COUNT(*) as cnt FROM logs GROUP BY user_id
) t
WHERE cnt > 100

• 문제점: 인라인 뷰는 인덱스 미사용, 중간 정렬 비용 발생

해결: CTE 또는 JOIN으로 리팩터링
(정확히 같은 성능은 아님 — 옵티마이저 상황 따라 다름)

JOIN으로 바꾸기 어려운 서브쿼리는 캐싱 처리 (ETL 또는 View화)

서브쿼리가 너무 복잡해서 리팩터링이 어려운 경우

• 미리 처리해놓은 결과 테이블 (ETL)
• Materialized View로 대체 (지원되는 DB일 경우)
• 사용자 정의 함수(UDF) 제거 후 파생 컬럼 생성

서브쿼리 최적화 체크리스트

체크 항목	내용
스칼라 서브쿼리 반복 실행인가?	JOIN으로 리팩터링 가능한가?
WHERE절에 IN 사용 중인가?	EXISTS / JOIN 대체 가능한가?
GROUP BY + IN 또는 중첩 SELECT인가?	미리 CTE로 정리 가능한가?
파생 데이터에 함수 호출 포함되어 있는가?	미리 처리하여 테이블화 가능한가?
서브쿼리의 결과 cardinality가 큰가?	인덱스 + 정렬 최적화 가능한가?