콘텐츠 대표 이미지 - SQL 데이터베이스 쿼리 최적화와복잡한 데이터 분석 기법 완전 정복 🚀
📊 프로그램 개발 · 데이터베이스

SQL 데이터베이스 쿼리 최적화와
복잡한 데이터 분석 기법 완전 정복 🚀

느린 쿼리는 이제 그만! 실전에서 바로 써먹는 최적화 전략과 분석 기법을 친구처럼 알려드릴게요 😎

야, 솔직히 말해봐. SQL 쿼리 짜다가 "왜 이렇게 느려?" 하면서 멘붕 온 적 있지? 😅
수십만 건, 수백만 건 데이터를 다루다 보면 쿼리 하나가 몇 초, 심하면 몇 분씩 걸리는 경우가 생기거든.
그리고 단순한 SELECT 쿼리를 넘어서 복잡한 데이터 분석을 해야 할 때는 어디서부터 시작해야 할지 막막하기도 하고.

이 글에서는 SQL 쿼리 최적화의 핵심 원리부터 시작해서, 윈도우 함수, CTE, 서브쿼리 최적화, 실행 계획 분석, 파티셔닝, 그리고 복잡한 데이터 분석 기법까지 — 실무에서 진짜 쓰이는 내용들을 싹 다 정리해줄게.
커피 한 잔 들고 편하게 읽어봐! ☕

쿼리 최적화 전후 성능 비교 최적화 전 8.4초 인덱스 추가 3.1초 쿼리 재작성 1.8초 실행계획 분석 0.9초 파티셔닝 0.3초 성능 향상 ↑ 최적화 전 단계별 최적화 적용 성능 향상 추세
1. 실행 계획(Execution Plan) — 쿼리의 속마음 읽기 🔍

쿼리 최적화의 첫 번째 단계는 실행 계획을 읽는 것이야.
실행 계획이란 데이터베이스 엔진이 쿼리를 어떻게 처리할지 미리 보여주는 로드맵 같은 거야.
이걸 모르고 최적화를 논하는 건 지도 없이 등산하는 것과 같아. 😤

📌 EXPLAIN / EXPLAIN ANALYZE 사용법

MySQL, PostgreSQL 모두 EXPLAIN 키워드를 지원해. 사용법은 간단해:

-- MySQL / PostgreSQL 공통
EXPLAIN SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= '2024-01-01'
  AND c.country = 'KR';

-- PostgreSQL: 실제 실행 시간까지 보고 싶다면
EXPLAIN ANALYZE SELECT ...;

실행 계획에서 꼭 확인해야 할 핵심 항목들이 있어:

항목 의미 주의 신호
type (MySQL) 접근 방식 (ALL, index, range, ref, eq_ref, const) ALL이면 풀 스캔 → 위험 ⚠️
rows 예상 처리 행 수 수백만 이상이면 인덱스 점검
key 실제 사용된 인덱스 NULL이면 인덱스 미사용
Extra 추가 정보 Using filesort, Using temporary 주의
cost (PostgreSQL) 예상 비용 높을수록 느린 쿼리
💡 꿀팁! MySQL에서 EXPLAIN FORMAT=JSON을 쓰면 더 상세한 정보를 JSON 형태로 볼 수 있어. PostgreSQL에서는 EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)으로 버퍼 히트율까지 확인 가능!
📌 실행 계획 해석 — type 컬럼의 중요성

MySQL 기준으로 type 컬럼의 성능 순서를 알아두면 정말 유용해:

성능 좋음 → 나쁨 순서: const → eq_ref → ref → range → index → ALL ⚠️

const: PRIMARY KEY나 UNIQUE 인덱스로 단 1건 조회 (최고!)
eq_ref: JOIN 시 PRIMARY KEY 또는 UNIQUE NOT NULL 인덱스 사용
ref: 비유니크 인덱스 사용
range: 인덱스 범위 스캔 (BETWEEN, >, < 등)
index: 인덱스 풀 스캔 (테이블 풀 스캔보다는 낫지만 여전히 느림)
ALL: 테이블 풀 스캔 (최악! 반드시 개선 필요)

2. 인덱스 전략 — 데이터베이스의 목차 만들기 📚

인덱스는 책의 목차와 같아. 목차가 없으면 책 전체를 다 뒤져야 하지만, 목차가 있으면 바로 원하는 페이지로 갈 수 있잖아.
근데 목차가 너무 많으면? 책이 두꺼워지고 목차 업데이트하는 데 시간이 걸리지. 인덱스도 마찬가지야. 균형이 중요해!

🎯 복합 인덱스(Composite Index) 설계 원칙

복합 인덱스는 여러 컬럼을 묶어서 만드는 인덱스야. 순서가 엄청 중요해!

-- 예시: 주문 테이블에서 자주 쓰는 쿼리
SELECT * FROM orders
WHERE customer_id = 100
  AND status = 'COMPLETED'
  AND created_at >= '2024-01-01';

-- 좋은 복합 인덱스 설계
CREATE INDEX idx_orders_customer_status_date
ON orders (customer_id, status, created_at);

-- 왜 이 순서? → 카디널리티(선택도) 높은 것부터!
-- customer_id: 고유값 많음 (높은 카디널리티)
-- status: 몇 가지 값만 존재 (낮은 카디널리티)
-- created_at: 범위 조건이므로 마지막에
⚠️ 인덱스 컬럼에 함수 쓰면 인덱스 무효화!
WHERE YEAR(created_at) = 2024 이렇게 쓰면 인덱스 못 써.
대신
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
이렇게 써야 해!
🎯 커버링 인덱스(Covering Index)

쿼리에서 필요한 모든 컬럼이 인덱스에 포함되어 있으면, 테이블 자체에 접근하지 않아도 돼.
이걸 커버링 인덱스라고 하는데, 성능 향상이 엄청나!

-- 이 쿼리를 자주 실행한다면:
SELECT customer_id, status, total_amount
FROM orders
WHERE customer_id = 100 AND status = 'COMPLETED';

-- 커버링 인덱스: 쿼리에 필요한 모든 컬럼 포함
CREATE INDEX idx_covering
ON orders (customer_id, status, total_amount);

-- EXPLAIN에서 Extra: "Using index" 가 뜨면 성공!
-- 테이블 접근 없이 인덱스만으로 결과 반환
🎯 인덱스 선택도(Selectivity) 계산

인덱스를 만들기 전에 선택도를 먼저 확인해봐. 선택도가 낮으면 인덱스 효과가 별로야.

-- 선택도 계산: 고유값 수 / 전체 행 수
SELECT
  COUNT(DISTINCT customer_id) / COUNT(*) AS customer_selectivity,
  COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
  COUNT(DISTINCT country) / COUNT(*) AS country_selectivity
FROM orders;

-- 결과 예시:
-- customer_selectivity: 0.8500  → 인덱스 효과 좋음 ✅
-- status_selectivity:   0.0003  → 인덱스 효과 낮음 ❌
-- country_selectivity:  0.0150  → 단독 인덱스 비효율적
💡 일반적으로 선택도가 0.1(10%) 이상이면 인덱스 효과가 좋아. 그 이하면 단독 인덱스보다는 복합 인덱스의 일부로 활용하는 게 나아.
B-Tree 인덱스 구조 시각화 루트 노드 50 | 150 | 300 브랜치 노드 1 브랜치 노드 2 브랜치 노드 3 리프: 1~49 리프: 50~149 리프: 150~299 리프: 300~499 리프: 500~699 리프: 700~ 루트 노드 브랜치 노드 리프 노드 (실제 데이터 포인터) 리프 노드는 이중 연결 리스트로 연결 → 범위 검색에 최적화
3. JOIN 최적화 — 테이블 결합의 기술 🔗

JOIN은 SQL에서 가장 강력하면서도 가장 성능 문제를 많이 일으키는 녀석이야.
JOIN 방식에는 여러 가지가 있고, 옵티마이저가 자동으로 선택하지만 우리가 힌트를 줄 수도 있어.

🔄 JOIN 알고리즘 종류
알고리즘 동작 방식 적합한 상황
Nested Loop Join 외부 테이블 각 행마다 내부 테이블 스캔 소규모 테이블, 인덱스 있을 때
Hash Join 해시 테이블 생성 후 매칭 대규모 테이블, 인덱스 없을 때
Merge Join 정렬된 두 테이블을 병합 이미 정렬된 데이터, 대규모 JOIN
🔄 JOIN 최적화 실전 팁
-- ❌ 나쁜 예: 서브쿼리로 JOIN 대체 (매 행마다 실행됨)
SELECT o.id, o.total,
  (SELECT c.name FROM customers c WHERE c.id = o.customer_id) AS customer_name
FROM orders o;

-- ✅ 좋은 예: 명시적 JOIN 사용
SELECT o.id, o.total, c.name AS customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

-- ❌ 나쁜 예: 작은 테이블을 드라이빙 테이블로 쓰지 않음
SELECT * FROM orders o  -- orders: 100만 건
JOIN order_status s ON o.status_id = s.id;  -- status: 10건

-- ✅ 좋은 예: 작은 테이블을 먼저 (MySQL은 STRAIGHT_JOIN 힌트)
SELECT STRAIGHT_JOIN * FROM order_status s
JOIN orders o ON o.status_id = s.id;
🔄 LATERAL JOIN / CROSS APPLY (PostgreSQL)

PostgreSQL에서 LATERAL JOIN은 서브쿼리에서 외부 쿼리의 컬럼을 참조할 수 있게 해줘. 강력한 기능이야!

-- 각 고객의 최근 주문 3건 가져오기
SELECT c.id, c.name, recent.order_id, recent.created_at
FROM customers c
CROSS JOIN LATERAL (
  SELECT o.id AS order_id, o.created_at
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.created_at DESC
  LIMIT 3
) AS recent;

-- LATERAL 없이 하면? → 윈도우 함수나 복잡한 서브쿼리 필요
-- LATERAL 있으면? → 깔끔하고 성능도 좋음!

4. 서브쿼리 vs CTE — 언제 뭘 써야 해? 🤔

서브쿼리와 CTE(Common Table Expression)는 비슷해 보이지만 성능과 가독성에서 차이가 있어.
언제 뭘 써야 할지 제대로 알아두자!

📝 CTE (WITH 절) 기본 사용법
-- CTE 기본 구조
WITH cte_name AS (
  SELECT ...
  FROM ...
  WHERE ...
),
another_cte AS (
  SELECT ...
  FROM cte_name
  WHERE ...
)
SELECT * FROM another_cte;

-- 실전 예시: 월별 매출 상위 고객 분석
WITH monthly_sales AS (
  SELECT
    customer_id,
    DATE_FORMAT(created_at, '%Y-%m') AS month,
    SUM(total_amount) AS monthly_total
  FROM orders
  WHERE created_at >= '2024-01-01'
  GROUP BY customer_id, DATE_FORMAT(created_at, '%Y-%m')
),
ranked_customers AS (
  SELECT
    customer_id,
    month,
    monthly_total,
    RANK() OVER (PARTITION BY month ORDER BY monthly_total DESC) AS rnk
  FROM monthly_sales
)
SELECT c.name, rc.month, rc.monthly_total, rc.rnk
FROM ranked_customers rc
JOIN customers c ON rc.customer_id = c.id
WHERE rc.rnk <= 10
ORDER BY rc.month, rc.rnk;
📝 재귀 CTE (Recursive CTE) — 계층 구조 탐색

조직도, 카테고리 트리, 댓글 계층 같은 계층형 데이터를 처리할 때 재귀 CTE는 정말 강력해!

-- 조직도 계층 탐색 (직원 → 상위 관리자 체인)
WITH RECURSIVE org_hierarchy AS (
  -- 앵커 멤버: 시작점 (최상위 관리자)
  SELECT
    id,
    name,
    manager_id,
    0 AS depth,
    CAST(name AS CHAR(1000)) AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- 재귀 멤버: 하위 직원 탐색
  SELECT
    e.id,
    e.name,
    e.manager_id,
    oh.depth + 1,
    CONCAT(oh.path, ' → ', e.name)
  FROM employees e
  INNER JOIN org_hierarchy oh ON e.manager_id = oh.id
  WHERE oh.depth < 10  -- 무한 루프 방지!
)
SELECT
  REPEAT('  ', depth) || name AS indented_name,
  depth,
  path
FROM org_hierarchy
ORDER BY path;
ℹ️ CTE vs 서브쿼리 성능 차이
MySQL 8.0 이전에서는 CTE가 항상 임시 테이블로 구체화(materialize)되어 오히려 느릴 수 있었어.
MySQL 8.0+, PostgreSQL에서는 옵티마이저가 CTE를 인라인으로 처리할 수 있어서 성능이 개선됐어.
PostgreSQL에서 CTE를 반드시 구체화하고 싶다면 WITH cte AS MATERIALIZED (...), 인라인 처리하고 싶다면 NOT MATERIALIZED를 명시할 수 있어.

5. 윈도우 함수 — 데이터 분석의 핵심 무기 🪟

윈도우 함수(Window Function)는 SQL 데이터 분석에서 게임 체인저야.
GROUP BY처럼 행을 집계하지만, 원래 행을 유지하면서 집계 결과를 추가로 보여줘.
이걸 제대로 쓸 줄 알면 복잡한 분석 쿼리가 훨씬 간결해져!

🪟 기본 구조
-- 윈도우 함수 기본 구조
함수명() OVER (
  [PARTITION BY 컬럼]   -- 그룹 나누기 (선택)
  [ORDER BY 컬럼]       -- 정렬 기준 (선택)
  [ROWS/RANGE BETWEEN ...] -- 프레임 지정 (선택)
)
🪟 순위 함수 — ROW_NUMBER, RANK, DENSE_RANK
SELECT
  product_name,
  category,
  sales_amount,
  -- 전체 순위 (동점 없음, 연속 번호)
  ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS row_num,
  -- 카테고리 내 순위 (동점 있으면 같은 순위, 다음 순위 건너뜀)
  RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS rank_in_cat,
  -- 카테고리 내 순위 (동점 있어도 다음 순위 안 건너뜀)
  DENSE_RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS dense_rank_in_cat
FROM products;

-- 결과 예시:
-- product_name | category | sales | row_num | rank | dense_rank
-- 상품A        | 전자     | 5000  | 1       | 1    | 1
-- 상품B        | 전자     | 5000  | 2       | 1    | 1  ← 동점!
-- 상품C        | 전자     | 3000  | 3       | 3    | 2  ← RANK는 3, DENSE_RANK는 2
🪟 집계 윈도우 함수 — 누적 합계, 이동 평균
SELECT
  order_date,
  daily_sales,
  -- 누적 합계
  SUM(daily_sales) OVER (ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales,
  -- 7일 이동 평균
  AVG(daily_sales) OVER (ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d,
  -- 전일 대비 증감
  daily_sales - LAG(daily_sales, 1) OVER (ORDER BY order_date) AS day_over_day,
  -- 전주 동일 요일 대비
  daily_sales - LAG(daily_sales, 7) OVER (ORDER BY order_date) AS week_over_week
FROM daily_sales_summary
ORDER BY order_date;
🪟 NTILE, PERCENT_RANK, CUME_DIST
SELECT
  customer_id,
  total_purchase,
  -- 고객을 구매액 기준으로 4분위로 나누기
  NTILE(4) OVER (ORDER BY total_purchase DESC) AS quartile,
  -- 백분위 순위 (0~1 사이 값)
  PERCENT_RANK() OVER (ORDER BY total_purchase) AS pct_rank,
  -- 누적 분포 (해당 값 이하인 비율)
  CUME_DIST() OVER (ORDER BY total_purchase) AS cume_dist
FROM customer_summary;

-- NTILE(4) 결과:
-- 1분위: 상위 25% 고객 (VIP)
-- 2분위: 상위 25~50% 고객
-- 3분위: 상위 50~75% 고객
-- 4분위: 하위 25% 고객
🪟 FIRST_VALUE, LAST_VALUE, NTH_VALUE
SELECT
  customer_id,
  order_id,
  order_amount,
  order_date,
  -- 각 고객의 첫 번째 주문 금액
  FIRST_VALUE(order_amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS first_order_amount,
  -- 각 고객의 가장 최근 주문 금액
  LAST_VALUE(order_amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_order_amount
FROM orders;
💡 LAST_VALUE 주의! LAST_VALUE는 기본 프레임이 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW라서 현재 행까지만 봐. 파티션 전체를 보려면 반드시
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
을 명시해야 해!
윈도우 함수 동작 원리 GROUP BY (행 축소) WINDOW FUNCTION (행 유지) 카테고리 | 합계 전자 | 15,000 의류 | 8,000 식품 | 5,500 3행으로 축소됨 ↑ VS 상품 | 카테고리 | 판매 | 카테고리합계 상품A | 전자 | 5,000 | 15,000 상품B | 전자 | 7,000 | 15,000 상품C | 전자 | 3,000 | 15,000 상품D | 의류 | 4,000 | 8,000 상품E | 의류 | 4,000 | 8,000 원래 행 유지하면서 집계값 추가 ✅ PARTITION: 전자 PARTITION: 의류 주요 윈도우 함수 한눈에 보기 순위 함수 ROW_NUMBER() RANK() DENSE_RANK() NTILE(n) 집계 함수 SUM() OVER() AVG() OVER() COUNT() OVER() MAX/MIN() OVER() 이동/오프셋 LAG(col, n) LEAD(col, n) FIRST_VALUE() LAST_VALUE() 분포 함수 PERCENT_RANK() CUME_DIST() PERCENTILE_CONT() PERCENTILE_DISC()
6. 테이블 파티셔닝 — 대용량 데이터 처리의 비밀 🗂️

수억 건의 데이터가 있는 테이블에서 특정 기간 데이터만 조회할 때, 파티셔닝이 없으면 전체를 다 뒤져야 해.
파티셔닝은 테이블을 논리적으로 여러 조각으로 나눠서 필요한 파티션만 스캔하게 해줘.
이걸 파티션 프루닝(Partition Pruning)이라고 해!

📦 RANGE 파티셔닝 (날짜 기반)
-- MySQL: 연도별 RANGE 파티셔닝
CREATE TABLE orders (
  id BIGINT NOT NULL AUTO_INCREMENT,
  customer_id INT NOT NULL,
  total_amount DECIMAL(10,2),
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id, created_at)  -- 파티션 키는 PK에 포함 필수!
)
PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 파티션 프루닝 확인
EXPLAIN SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- partitions: p2024 → 해당 파티션만 스캔!
📦 LIST 파티셔닝 (카테고리 기반)
-- 지역별 LIST 파티셔닝
CREATE TABLE sales (
  id BIGINT NOT NULL,
  region_code VARCHAR(10) NOT NULL,
  amount DECIMAL(10,2),
  PRIMARY KEY (id, region_code)
)
PARTITION BY LIST COLUMNS (region_code) (
  PARTITION p_seoul VALUES IN ('SEO', 'GGI'),
  PARTITION p_busan VALUES IN ('PUS', 'ULS'),
  PARTITION p_daegu VALUES IN ('TAE', 'GYB'),
  PARTITION p_others VALUES IN ('JEJ', 'GWJ', 'CHN')
);
📦 PostgreSQL 파티셔닝 (선언적 파티셔닝)
-- PostgreSQL 10+ 선언적 파티셔닝
CREATE TABLE orders (
  id BIGSERIAL,
  customer_id INT NOT NULL,
  total_amount NUMERIC(10,2),
  created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- 월별 파티션 생성
CREATE TABLE orders_2024_01
  PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE orders_2024_02
  PARTITION OF orders
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- 각 파티션에 인덱스 생성 (자동으로 파티션별 적용)
CREATE INDEX ON orders (customer_id, created_at);
💡 파티셔닝 주의사항! 파티션 키가 WHERE 절에 없으면 파티션 프루닝이 안 돼서 오히려 느려질 수 있어.
파티션 수가 너무 많으면 (수천 개 이상) 파티션 관리 오버헤드가 생겨.
일반적으로 파티션당 수백만 건 정도가 적당해.

7. 복잡한 데이터 분석 기법 — 실전 SQL 분석 레시피 📊

이제 진짜 재미있는 부분이야! 실무에서 자주 쓰이는 복잡한 데이터 분석 패턴들을 알아보자.
이런 분석 기법들은 재능넷 같은 플랫폼에서 사용자 행동 분석, 매출 분석 등에 실제로 활용돼.

📈 코호트 분석 (Cohort Analysis)

코호트 분석은 특정 기간에 가입한 사용자 그룹의 행동을 시간에 따라 추적하는 분석이야. 리텐션 분석에 필수!

-- 월별 가입 코호트의 리텐션 분석
WITH cohort_base AS (
  -- 각 사용자의 첫 주문 월 (코호트 정의)
  SELECT
    customer_id,
    DATE_FORMAT(MIN(created_at), '%Y-%m') AS cohort_month
  FROM orders
  GROUP BY customer_id
),
cohort_activity AS (
  -- 각 주문의 코호트 기준 경과 월 수 계산
  SELECT
    cb.cohort_month,
    TIMESTAMPDIFF(MONTH,
      STR_TO_DATE(CONCAT(cb.cohort_month, '-01'), '%Y-%m-%d'),
      o.created_at
    ) AS months_since_first,
    COUNT(DISTINCT o.customer_id) AS active_customers
  FROM orders o
  JOIN cohort_base cb ON o.customer_id = cb.customer_id
  GROUP BY cb.cohort_month, months_since_first
),
cohort_size AS (
  -- 코호트별 초기 사용자 수
  SELECT cohort_month, COUNT(*) AS total_customers
  FROM cohort_base
  GROUP BY cohort_month
)
SELECT
  ca.cohort_month,
  cs.total_customers,
  ca.months_since_first AS month_number,
  ca.active_customers,
  ROUND(ca.active_customers / cs.total_customers * 100, 1) AS retention_rate
FROM cohort_activity ca
JOIN cohort_size cs ON ca.cohort_month = cs.cohort_month
ORDER BY ca.cohort_month, ca.months_since_first;
📈 퍼널 분석 (Funnel Analysis)

사용자가 특정 단계를 거쳐 전환되는 과정을 분석하는 기법이야. 이커머스에서 필수!

-- 구매 퍼널: 방문 → 상품조회 → 장바구니 → 결제완료
WITH funnel_steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_type = 'page_view' THEN 1 ELSE 0 END) AS step1_visit,
    MAX(CASE WHEN event_type = 'product_view' THEN 1 ELSE 0 END) AS step2_product,
    MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) AS step3_cart,
    MAX(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) AS step4_purchase
  FROM user_events
  WHERE event_date >= '2024-01-01'
  GROUP BY user_id
)
SELECT
  SUM(step1_visit) AS 방문자수,
  SUM(step2_product) AS 상품조회수,
  SUM(step3_cart) AS 장바구니추가,
  SUM(step4_purchase) AS 구매완료,
  ROUND(SUM(step2_product) / SUM(step1_visit) * 100, 1) AS 방문_상품조회_전환율,
  ROUND(SUM(step3_cart) / SUM(step2_product) * 100, 1) AS 상품조회_장바구니_전환율,
  ROUND(SUM(step4_purchase) / SUM(step3_cart) * 100, 1) AS 장바구니_구매_전환율,
  ROUND(SUM(step4_purchase) / SUM(step1_visit) * 100, 1) AS 전체_전환율
FROM funnel_steps;
📈 RFM 분석 (Recency, Frequency, Monetary)

고객을 최근성, 빈도, 금액 기준으로 세분화하는 마케팅 분석 기법이야!

WITH rfm_raw AS (
  SELECT
    customer_id,
    -- Recency: 마지막 구매로부터 경과 일수
    DATEDIFF(CURDATE(), MAX(created_at)) AS recency_days,
    -- Frequency: 총 주문 횟수
    COUNT(DISTINCT id) AS frequency,
    -- Monetary: 총 구매 금액
    SUM(total_amount) AS monetary
  FROM orders
  WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
  GROUP BY customer_id
),
rfm_scores AS (
  SELECT
    customer_id,
    recency_days,
    frequency,
    monetary,
    -- 각 지표를 1~5점으로 스코어링 (NTILE 활용)
    NTILE(5) OVER (ORDER BY recency_days ASC) AS r_score,  -- 최근일수록 높은 점수
    NTILE(5) OVER (ORDER BY frequency DESC) AS f_score,
    NTILE(5) OVER (ORDER BY monetary DESC) AS m_score
  FROM rfm_raw
)
SELECT
  customer_id,
  r_score, f_score, m_score,
  (r_score + f_score + m_score) AS total_rfm_score,
  CASE
    WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'VIP 고객'
    WHEN r_score >= 3 AND f_score >= 3 THEN '충성 고객'
    WHEN r_score >= 4 AND f_score <= 2 THEN '신규 고객'
    WHEN r_score <= 2 AND f_score >= 3 THEN '이탈 위험 고객'
    WHEN r_score <= 2 AND f_score <= 2 THEN '휴면 고객'
    ELSE '일반 고객'
  END AS customer_segment
FROM rfm_scores
ORDER BY total_rfm_score DESC;
📈 피벗 테이블 (PIVOT) — 행을 열로 변환
-- MySQL: CASE WHEN으로 피벗 구현
SELECT
  product_name,
  SUM(CASE WHEN MONTH(order_date) = 1 THEN amount ELSE 0 END) AS '1월',
  SUM(CASE WHEN MONTH(order_date) = 2 THEN amount ELSE 0 END) AS '2월',
  SUM(CASE WHEN MONTH(order_date) = 3 THEN amount ELSE 0 END) AS '3월',
  SUM(CASE WHEN MONTH(order_date) = 4 THEN amount ELSE 0 END) AS '4월',
  SUM(CASE WHEN MONTH(order_date) = 5 THEN amount ELSE 0 END) AS '5월',
  SUM(CASE WHEN MONTH(order_date) = 6 THEN amount ELSE 0 END) AS '6월',
  SUM(amount) AS '합계'
FROM sales
WHERE YEAR(order_date) = 2024
GROUP BY product_name
ORDER BY 합계 DESC;

-- PostgreSQL: crosstab 함수 사용 (tablefunc 확장 필요)
SELECT * FROM crosstab(
  'SELECT product_name, month, amount FROM sales ORDER BY 1,2',
  'SELECT generate_series(1,12)'
) AS ct(product_name TEXT, m1 NUMERIC, m2 NUMERIC, ...)

8. 쿼리 최적화 고급 기법 — 프로처럼 튜닝하기 ⚡
⚡ EXISTS vs IN vs JOIN 성능 비교

이 세 가지는 비슷한 결과를 내지만 성능 차이가 있어. 상황에 따라 골라 써야 해!

-- 주문이 있는 고객 조회하기

-- 방법 1: IN 서브쿼리
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'COMPLETED');
-- 단점: 서브쿼리 결과를 메모리에 올림, 대용량에서 느릴 수 있음

-- 방법 2: EXISTS (대부분의 경우 가장 빠름)
SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'COMPLETED'
);
-- 장점: 첫 번째 매칭 발견 시 즉시 중단 (Short-circuit)

-- 방법 3: JOIN
SELECT DISTINCT c.*
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'COMPLETED';
-- 주의: DISTINCT 없으면 중복 발생, 대용량에서 해시 조인 활용

-- 방법 4: NOT EXISTS (주문 없는 고객)
SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
-- NOT IN보다 NOT EXISTS가 훨씬 빠름 (NULL 처리 문제도 없음)
⚡ UNION vs UNION ALL
-- UNION: 중복 제거 (정렬 + 비교 작업 발생 → 느림)
SELECT customer_id FROM orders_2023
UNION
SELECT customer_id FROM orders_2024;

-- UNION ALL: 중복 허용 (단순 합치기 → 빠름)
SELECT customer_id FROM orders_2023
UNION ALL
SELECT customer_id FROM orders_2024;

-- 중복이 없다는 걸 알고 있다면 항상 UNION ALL 사용!
-- 중복 제거가 필요하다면 UNION 대신:
SELECT DISTINCT customer_id FROM (
  SELECT customer_id FROM orders_2023
  UNION ALL
  SELECT customer_id FROM orders_2024
) combined;
-- 이 방식이 UNION보다 옵티마이저 최적화 여지가 더 많음
⚡ 배치 처리 — 대용량 UPDATE/DELETE 최적화
-- ❌ 나쁜 예: 한 번에 수백만 건 UPDATE
UPDATE orders SET status = 'ARCHIVED'
WHERE created_at < '2022-01-01';
-- 문제: 락 경합, 언두 로그 폭발, 리플리케이션 지연

-- ✅ 좋은 예: 배치로 나눠서 처리
SET @batch_size = 10000;
SET @total_updated = 1;

WHILE @total_updated > 0 DO
  UPDATE orders SET status = 'ARCHIVED'
  WHERE created_at < '2022-01-01'
    AND status != 'ARCHIVED'
  LIMIT 10000;

  SET @total_updated = ROW_COUNT();
  -- 잠깐 쉬어가기 (다른 쿼리에 기회 주기)
  DO SLEEP(0.1);
END WHILE;

-- PostgreSQL 버전
DO $$
DECLARE
  updated_count INT;
BEGIN
  LOOP
    UPDATE orders SET status = 'ARCHIVED'
    WHERE id IN (
      SELECT id FROM orders
      WHERE created_at < '2022-01-01'
        AND status != 'ARCHIVED'
      LIMIT 10000
    );
    GET DIAGNOSTICS updated_count = ROW_COUNT;
    EXIT WHEN updated_count = 0;
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;
⚡ 옵티마이저 힌트 활용
-- MySQL 8.0+ 옵티마이저 힌트
SELECT /*+ INDEX(o idx_orders_customer_date) */
  o.id, o.total_amount
FROM orders o
WHERE o.customer_id = 100
  AND o.created_at >= '2024-01-01';

-- JOIN 순서 강제
SELECT /*+ JOIN_ORDER(c, o) */
  c.name, o.total_amount
FROM customers c
JOIN orders o ON c.id = o.customer_id;

-- 해시 조인 강제 (MySQL 8.0.18+)
SELECT /*+ HASH_JOIN(o c) */
  o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

-- PostgreSQL 힌트 (pg_hint_plan 확장 필요)
/*+ SeqScan(orders) IndexScan(customers) */
SELECT c.name, o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.id;

9. SQL로 하는 통계 분석 — 데이터 과학자처럼 🧮

SQL만으로도 꽤 수준 높은 통계 분석이 가능해. Python이나 R 없이도 데이터베이스 안에서 처리하면 데이터 이동 비용도 없고 훨씬 빠르거든!

🧮 이동 평균과 지수 이동 평균
-- 단순 이동 평균 (SMA) - 7일
SELECT
  date,
  daily_value,
  AVG(daily_value) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS sma_7d,
  -- 가중 이동 평균 (WMA) - 최근 데이터에 더 높은 가중치
  SUM(daily_value * weight_factor) OVER (
    ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) / SUM(weight_factor) OVER (
    ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS wma_7d
FROM (
  SELECT
    date,
    daily_value,
    ROW_NUMBER() OVER (ORDER BY date) AS rn,
    -- 가중치: 최근 7일 중 가장 최근이 7, 가장 오래된 게 1
    ROW_NUMBER() OVER (ORDER BY date) % 7 + 1 AS weight_factor
  FROM daily_metrics
) t;
🧮 표준편차와 이상치 탐지
-- Z-Score 기반 이상치 탐지
WITH stats AS (
  SELECT
    AVG(order_amount) AS mean_amount,
    STDDEV(order_amount) AS std_amount
  FROM orders
  WHERE created_at >= '2024-01-01'
),
z_scores AS (
  SELECT
    o.id,
    o.customer_id,
    o.order_amount,
    (o.order_amount - s.mean_amount) / s.std_amount AS z_score
  FROM orders o
  CROSS JOIN stats s
  WHERE o.created_at >= '2024-01-01'
)
SELECT *,
  CASE
    WHEN ABS(z_score) > 3 THEN '극단적 이상치'
    WHEN ABS(z_score) > 2 THEN '이상치 의심'
    ELSE '정상'
  END AS anomaly_flag
FROM z_scores
WHERE ABS(z_score) > 2
ORDER BY ABS(z_score) DESC;

-- IQR 기반 이상치 탐지 (더 강건한 방법)
WITH quartiles AS (
  SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY order_amount) AS q1,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY order_amount) AS q3
  FROM orders
)
SELECT o.*,
  q3 - q1 AS iqr,
  q1 - 1.5 * (q3 - q1) AS lower_fence,
  q3 + 1.5 * (q3 - q1) AS upper_fence
FROM orders o
CROSS JOIN quartiles q
WHERE o.order_amount < q.q1 - 1.5 * (q.q3 - q.q1)
   OR o.order_amount > q.q3 + 1.5 * (q.q3 - q.q1);
🧮 시계열 분석 — 계절성 분해
-- 요일별, 시간대별 패턴 분석
SELECT
  DAYOFWEEK(created_at) AS day_of_week,
  DAYNAME(created_at) AS day_name,
  HOUR(created_at) AS hour_of_day,
  COUNT(*) AS order_count,
  AVG(total_amount) AS avg_amount,
  -- 전체 평균 대비 비율 (계절성 지수)
  COUNT(*) / AVG(COUNT(*)) OVER () AS seasonality_index
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY DAYOFWEEK(created_at), DAYNAME(created_at), HOUR(created_at)
ORDER BY day_of_week, hour_of_day;

10. 성능 모니터링 — 느린 쿼리 잡아내기 🕵️
🕵️ MySQL 슬로우 쿼리 로그
-- 슬로우 쿼리 로그 활성화
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 1초 이상 걸리는 쿼리 기록
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Performance Schema로 상위 느린 쿼리 확인
SELECT
  DIGEST_TEXT AS query_pattern,
  COUNT_STAR AS execution_count,
  ROUND(AVG_TIMER_WAIT / 1e9, 3) AS avg_seconds,
  ROUND(MAX_TIMER_WAIT / 1e9, 3) AS max_seconds,
  ROUND(SUM_TIMER_WAIT / 1e9, 3) AS total_seconds,
  SUM_ROWS_EXAMINED AS total_rows_examined,
  SUM_ROWS_SENT AS total_rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;
🕵️ PostgreSQL pg_stat_statements
-- pg_stat_statements 확장 활성화 (postgresql.conf)
-- shared_preload_libraries = 'pg_stat_statements'

-- 상위 느린 쿼리 확인
SELECT
  query,
  calls,
  ROUND(total_exec_time::numeric / 1000, 2) AS total_seconds,
  ROUND(mean_exec_time::numeric / 1000, 4) AS avg_seconds,
  ROUND(stddev_exec_time::numeric / 1000, 4) AS stddev_seconds,
  rows,
  ROUND(100.0 * shared_blks_hit /
    NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_ratio
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 인덱스 사용 현황 확인
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan AS index_scans,
  idx_tup_read AS tuples_read,
  idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
🕵️ 인덱스 사용 현황 점검
-- MySQL: 사용되지 않는 인덱스 찾기
SELECT
  t.TABLE_SCHEMA,
  t.TABLE_NAME,
  s.INDEX_NAME,
  s.COLUMN_NAME,
  s.SEQ_IN_INDEX
FROM information_schema.STATISTICS s
JOIN information_schema.TABLES t
  ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
  AND s.TABLE_NAME = t.TABLE_NAME
WHERE s.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema')
  AND s.INDEX_NAME != 'PRIMARY'
ORDER BY t.TABLE_NAME, s.INDEX_NAME;

-- 중복 인덱스 찾기 (앞부분이 같은 인덱스)
-- 예: (a, b)와 (a) 인덱스가 동시에 있으면 (a)는 불필요
SELECT
  TABLE_NAME,
  GROUP_CONCAT(INDEX_NAME ORDER BY INDEX_NAME) AS indexes,
  GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
GROUP BY TABLE_NAME, INDEX_NAME
HAVING COUNT(*) > 1;

11. 실전 최적화 체크리스트 ✅

쿼리 최적화할 때 이 순서대로 점검해봐. 마치 의사가 진단하듯이!

1
EXPLAIN으로 실행 계획 확인
type=ALL이 있는지, 인덱스가 제대로 사용되는지, rows 수가 적절한지 확인
2
WHERE 절 컬럼에 인덱스 존재 여부 확인
자주 쓰이는 필터 조건에 인덱스가 있는지, 함수로 감싸서 인덱스를 무효화하고 있지 않은지 확인
3
SELECT * 대신 필요한 컬럼만 명시
불필요한 데이터 전송 줄이기, 커버링 인덱스 활용 가능성 높이기
4
JOIN 순서와 드라이빙 테이블 확인
작은 테이블이 드라이빙 테이블이 되도록, JOIN 컬럼에 인덱스 존재 여부 확인
5
서브쿼리를 JOIN이나 CTE로 리팩토링
상관 서브쿼리(Correlated Subquery)는 특히 위험, 가능하면 JOIN으로 변환
6
LIMIT 절 활용
페이지네이션 시 OFFSET 대신 커서 기반 페이지네이션 고려 (대용량에서 OFFSET은 느림)
7
통계 정보 최신화
MySQL: ANALYZE TABLE, PostgreSQL: ANALYZE 실행으로 옵티마이저 통계 갱신
8
캐싱 레이어 고려
자주 실행되는 무거운 쿼리는 Redis 등 캐시 레이어 도입 검토
ℹ️ 커서 기반 페이지네이션 예시
OFFSET 방식은 뒤로 갈수록 느려져. 100만 번째 페이지라면 100만 건을 스캔하고 버려야 하거든!
대신 마지막으로 본 ID를 기준으로 다음 페이지를 가져오는 방식을 써봐:
WHERE id > :last_seen_id ORDER BY id LIMIT 20 — 이렇게 하면 항상 빠르게 동작해!

12. JSON 데이터 처리 — 반정형 데이터 다루기 📋

요즘 데이터베이스에 JSON을 저장하는 경우가 많아졌어.
MySQL 5.7+, PostgreSQL 9.2+에서 JSON 타입을 지원하고, 인덱스도 걸 수 있어!

📋 MySQL JSON 함수
-- JSON 데이터 예시
-- {"name": "홍길동", "tags": ["VIP", "신규"], "address": {"city": "서울"}}

-- JSON 값 추출
SELECT
  JSON_EXTRACT(profile, '$.name') AS name,
  JSON_UNQUOTE(JSON_EXTRACT(profile, '$.address.city')) AS city,
  profile->>'$.name' AS name_shorthand,  -- MySQL 5.7.13+
  JSON_LENGTH(profile->'$.tags') AS tag_count
FROM customers;

-- JSON 배열 검색
SELECT * FROM customers
WHERE JSON_CONTAINS(profile->'$.tags', '"VIP"');

-- JSON 인덱스 (Generated Column 활용)
ALTER TABLE customers
ADD COLUMN city VARCHAR(100)
  GENERATED ALWAYS AS (JSON_UNQUOTE(profile->>'$.address.city')) STORED;

CREATE INDEX idx_customers_city ON customers(city);

-- 이제 city 컬럼으로 빠른 검색 가능!
SELECT * FROM customers WHERE city = '서울';
📋 PostgreSQL JSONB
-- JSONB는 JSON보다 저장 시 파싱하여 바이너리로 저장 → 조회 빠름
CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  data JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- JSONB 인덱스 (GIN 인덱스)
CREATE INDEX idx_events_data ON events USING GIN (data);

-- JSONB 연산자
SELECT
  data->>'user_id' AS user_id,           -- 텍스트로 추출
  data->'metadata'->>'source' AS source, -- 중첩 접근
  data @> '{"event_type": "click"}'      -- 포함 여부 확인
FROM events
WHERE data @> '{"event_type": "purchase"}'  -- GIN 인덱스 활용!
  AND (data->>'amount')::numeric > 10000;

-- JSONB 집계
SELECT
  data->>'event_type' AS event_type,
  COUNT(*) AS count,
  AVG((data->>'amount')::numeric) AS avg_amount
FROM events
GROUP BY data->>'event_type';

마무리 — SQL 최적화는 여정이야 🏁

와, 여기까지 읽어줬다면 진짜 대단한 거야! 👏
SQL 쿼리 최적화와 데이터 분석은 한 번에 다 익히는 게 아니라, 실제 문제를 만나고 해결하면서 조금씩 쌓이는 거야.

오늘 배운 내용을 정리하면:

📌 핵심 요약 실행 계획 EXPLAIN으로 쿼리 속마음 읽기
인덱스 복합 인덱스, 커버링 인덱스, 선택도 고려
JOIN 드라이빙 테이블, 알고리즘 이해
CTE 재귀 CTE로 계층 구조 처리
윈도우 함수 순위, 집계, 이동 평균, 오프셋
파티셔닝 대용량 테이블 파티션 프루닝
분석 기법 코호트, 퍼널, RFM, 피벗
통계 Z-Score, IQR 이상치 탐지
모니터링 슬로우 쿼리 로그, pg_stat_statements
JSON 반정형 데이터 처리와 인덱싱

SQL 실력을 더 키우고 싶다면, 실제 프로젝트에서 부딪혀보는 게 최고야.
만약 혼자 공부하기 어렵다면 재능넷에서 데이터베이스 전문가에게 1:1 멘토링을 받아보는 것도 좋은 방법이야.
실무 경험이 풍부한 개발자들이 직접 코드 리뷰와 쿼리 튜닝을 도와줄 수 있거든! 🙌

느린 쿼리는 이제 두렵지 않지? 😎
EXPLAIN 켜고, 인덱스 확인하고, 윈도우 함수 써보고, 파티션 나눠보고!
그렇게 하나씩 해결하다 보면 어느새 SQL 고수가 되어 있을 거야. 화이팅! 🚀

댓글 작성

이 글에 대한 여러분의 생각을 들려주세요

댓글 0
댓글을 불러오는 중입니다.

아직 댓글이 없습니다.