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

SQL 데이터베이스 쿼리 최적화와
복잡한 데이터 분석 기법 완전 정복 🚀
느린 쿼리는 이제 그만! 실전에서 바로 써먹는 최적화 전략과 분석 기법을 친구처럼 알려드릴게요 😎
야, 솔직히 말해봐. SQL 쿼리 짜다가 "왜 이렇게 느려?" 하면서 멘붕 온 적 있지? 😅
수십만 건, 수백만 건 데이터를 다루다 보면 쿼리 하나가 몇 초, 심하면 몇 분씩 걸리는 경우가 생기거든.
그리고 단순한 SELECT 쿼리를 넘어서 복잡한 데이터 분석을 해야 할 때는 어디서부터 시작해야 할지 막막하기도 하고.
이 글에서는 SQL 쿼리 최적화의 핵심 원리부터 시작해서, 윈도우 함수, CTE, 서브쿼리 최적화, 실행 계획 분석, 파티셔닝, 그리고 복잡한 데이터 분석 기법까지 — 실무에서 진짜 쓰이는 내용들을 싹 다 정리해줄게.
커피 한 잔 들고 편하게 읽어봐! ☕
쿼리 최적화의 첫 번째 단계는 실행 계획을 읽는 것이야.
실행 계획이란 데이터베이스 엔진이 쿼리를 어떻게 처리할지 미리 보여주는 로드맵 같은 거야.
이걸 모르고 최적화를 논하는 건 지도 없이 등산하는 것과 같아. 😤
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) | 예상 비용 | 높을수록 느린 쿼리 |
EXPLAIN FORMAT=JSON을 쓰면 더 상세한 정보를 JSON 형태로 볼 수 있어. PostgreSQL에서는 EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)으로 버퍼 히트율까지 확인 가능!
MySQL 기준으로 type 컬럼의 성능 순서를 알아두면 정말 유용해:
const: PRIMARY KEY나 UNIQUE 인덱스로 단 1건 조회 (최고!)
eq_ref: JOIN 시 PRIMARY KEY 또는 UNIQUE NOT NULL 인덱스 사용
ref: 비유니크 인덱스 사용
range: 인덱스 범위 스캔 (BETWEEN, >, < 등)
index: 인덱스 풀 스캔 (테이블 풀 스캔보다는 낫지만 여전히 느림)
ALL: 테이블 풀 스캔 (최악! 반드시 개선 필요)
인덱스는 책의 목차와 같아. 목차가 없으면 책 전체를 다 뒤져야 하지만, 목차가 있으면 바로 원하는 페이지로 갈 수 있잖아.
근데 목차가 너무 많으면? 책이 두꺼워지고 목차 업데이트하는 데 시간이 걸리지. 인덱스도 마찬가지야. 균형이 중요해!
복합 인덱스는 여러 컬럼을 묶어서 만드는 인덱스야. 순서가 엄청 중요해!
-- 예시: 주문 테이블에서 자주 쓰는 쿼리
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' 이렇게 써야 해!
쿼리에서 필요한 모든 컬럼이 인덱스에 포함되어 있으면, 테이블 자체에 접근하지 않아도 돼.
이걸 커버링 인덱스라고 하는데, 성능 향상이 엄청나!
-- 이 쿼리를 자주 실행한다면:
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" 가 뜨면 성공!
-- 테이블 접근 없이 인덱스만으로 결과 반환
인덱스를 만들기 전에 선택도를 먼저 확인해봐. 선택도가 낮으면 인덱스 효과가 별로야.
-- 선택도 계산: 고유값 수 / 전체 행 수
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 → 단독 인덱스 비효율적
JOIN은 SQL에서 가장 강력하면서도 가장 성능 문제를 많이 일으키는 녀석이야.
JOIN 방식에는 여러 가지가 있고, 옵티마이저가 자동으로 선택하지만 우리가 힌트를 줄 수도 있어.
| 알고리즘 | 동작 방식 | 적합한 상황 |
|---|---|---|
| Nested Loop Join | 외부 테이블 각 행마다 내부 테이블 스캔 | 소규모 테이블, 인덱스 있을 때 |
| Hash Join | 해시 테이블 생성 후 매칭 | 대규모 테이블, 인덱스 없을 때 |
| Merge 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;
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 있으면? → 깔끔하고 성능도 좋음!
서브쿼리와 CTE(Common Table Expression)는 비슷해 보이지만 성능과 가독성에서 차이가 있어.
언제 뭘 써야 할지 제대로 알아두자!
-- 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는 정말 강력해!
-- 조직도 계층 탐색 (직원 → 상위 관리자 체인)
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;
MySQL 8.0 이전에서는 CTE가 항상 임시 테이블로 구체화(materialize)되어 오히려 느릴 수 있었어.
MySQL 8.0+, PostgreSQL에서는 옵티마이저가 CTE를 인라인으로 처리할 수 있어서 성능이 개선됐어.
PostgreSQL에서 CTE를 반드시 구체화하고 싶다면
WITH cte AS MATERIALIZED (...), 인라인 처리하고 싶다면 NOT MATERIALIZED를 명시할 수 있어.
윈도우 함수(Window Function)는 SQL 데이터 분석에서 게임 체인저야.
GROUP BY처럼 행을 집계하지만, 원래 행을 유지하면서 집계 결과를 추가로 보여줘.
이걸 제대로 쓸 줄 알면 복잡한 분석 쿼리가 훨씬 간결해져!
-- 윈도우 함수 기본 구조
함수명() OVER (
[PARTITION BY 컬럼] -- 그룹 나누기 (선택)
[ORDER BY 컬럼] -- 정렬 기준 (선택)
[ROWS/RANGE BETWEEN ...] -- 프레임 지정 (선택)
)
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;
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% 고객
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;
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW라서 현재 행까지만 봐. 파티션 전체를 보려면 반드시 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING을 명시해야 해!
수억 건의 데이터가 있는 테이블에서 특정 기간 데이터만 조회할 때, 파티셔닝이 없으면 전체를 다 뒤져야 해.
파티셔닝은 테이블을 논리적으로 여러 조각으로 나눠서 필요한 파티션만 스캔하게 해줘.
이걸 파티션 프루닝(Partition Pruning)이라고 해!
-- 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 파티셔닝
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 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);
파티션 수가 너무 많으면 (수천 개 이상) 파티션 관리 오버헤드가 생겨.
일반적으로 파티션당 수백만 건 정도가 적당해.
이제 진짜 재미있는 부분이야! 실무에서 자주 쓰이는 복잡한 데이터 분석 패턴들을 알아보자.
이런 분석 기법들은 재능넷 같은 플랫폼에서 사용자 행동 분석, 매출 분석 등에 실제로 활용돼.
코호트 분석은 특정 기간에 가입한 사용자 그룹의 행동을 시간에 따라 추적하는 분석이야. 리텐션 분석에 필수!
-- 월별 가입 코호트의 리텐션 분석
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;
사용자가 특정 단계를 거쳐 전환되는 과정을 분석하는 기법이야. 이커머스에서 필수!
-- 구매 퍼널: 방문 → 상품조회 → 장바구니 → 결제완료
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;
고객을 최근성, 빈도, 금액 기준으로 세분화하는 마케팅 분석 기법이야!
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;
-- 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, ...)
이 세 가지는 비슷한 결과를 내지만 성능 차이가 있어. 상황에 따라 골라 써야 해!
-- 주문이 있는 고객 조회하기
-- 방법 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: 중복 제거 (정렬 + 비교 작업 발생 → 느림)
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
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;
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;
-- 슬로우 쿼리 로그 활성화
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;
-- 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;
쿼리 최적화할 때 이 순서대로 점검해봐. 마치 의사가 진단하듯이!
type=ALL이 있는지, 인덱스가 제대로 사용되는지, rows 수가 적절한지 확인
자주 쓰이는 필터 조건에 인덱스가 있는지, 함수로 감싸서 인덱스를 무효화하고 있지 않은지 확인
불필요한 데이터 전송 줄이기, 커버링 인덱스 활용 가능성 높이기
작은 테이블이 드라이빙 테이블이 되도록, JOIN 컬럼에 인덱스 존재 여부 확인
상관 서브쿼리(Correlated Subquery)는 특히 위험, 가능하면 JOIN으로 변환
페이지네이션 시 OFFSET 대신 커서 기반 페이지네이션 고려 (대용량에서 OFFSET은 느림)
MySQL:
ANALYZE TABLE, PostgreSQL: ANALYZE 실행으로 옵티마이저 통계 갱신
자주 실행되는 무거운 쿼리는 Redis 등 캐시 레이어 도입 검토
OFFSET 방식은 뒤로 갈수록 느려져. 100만 번째 페이지라면 100만 건을 스캔하고 버려야 하거든!대신 마지막으로 본 ID를 기준으로 다음 페이지를 가져오는 방식을 써봐:
WHERE id > :last_seen_id ORDER BY id LIMIT 20 — 이렇게 하면 항상 빠르게 동작해!
요즘 데이터베이스에 JSON을 저장하는 경우가 많아졌어.
MySQL 5.7+, PostgreSQL 9.2+에서 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 = '서울';
-- 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 쿼리 최적화와 데이터 분석은 한 번에 다 익히는 게 아니라, 실제 문제를 만나고 해결하면서 조금씩 쌓이는 거야.
오늘 배운 내용을 정리하면:
인덱스 복합 인덱스, 커버링 인덱스, 선택도 고려
JOIN 드라이빙 테이블, 알고리즘 이해
CTE 재귀 CTE로 계층 구조 처리
윈도우 함수 순위, 집계, 이동 평균, 오프셋
파티셔닝 대용량 테이블 파티션 프루닝
분석 기법 코호트, 퍼널, RFM, 피벗
통계 Z-Score, IQR 이상치 탐지
모니터링 슬로우 쿼리 로그, pg_stat_statements
JSON 반정형 데이터 처리와 인덱싱
SQL 실력을 더 키우고 싶다면, 실제 프로젝트에서 부딪혀보는 게 최고야.
만약 혼자 공부하기 어렵다면 재능넷에서 데이터베이스 전문가에게 1:1 멘토링을 받아보는 것도 좋은 방법이야.
실무 경험이 풍부한 개발자들이 직접 코드 리뷰와 쿼리 튜닝을 도와줄 수 있거든! 🙌
느린 쿼리는 이제 두렵지 않지? 😎
EXPLAIN 켜고, 인덱스 확인하고, 윈도우 함수 써보고, 파티션 나눠보고!
그렇게 하나씩 해결하다 보면 어느새 SQL 고수가 되어 있을 거야. 화이팅! 🚀
댓글 작성
이 글에 대한 여러분의 생각을 들려주세요
로그인이 필요합니다
댓글을 작성하려면 먼저 로그인해주세요.
댓글 0
지식인의 숲 - 지적 재산권 보호 고지
지적 재산권 보호 고지
- 저작권 및 소유권: 본 컨텐츠는 재능넷의 독점 AI 기술로 생성되었으며, 대한민국 저작권법 및 국제 저작권 협약에 의해 보호됩니다.
- AI 생성 컨텐츠의 법적 지위: 본 AI 생성 컨텐츠는 재능넷의 지적 창작물로 인정되며, 관련 법규에 따라 저작권 보호를 받습니다.
- 사용 제한: 재능넷의 명시적 서면 동의 없이 본 컨텐츠를 복제, 수정, 배포, 또는 상업적으로 활용하는 행위는 엄격히 금지됩니다.
- 데이터 수집 금지: 본 컨텐츠에 대한 무단 스크래핑, 크롤링, 및 자동화된 데이터 수집은 법적 제재의 대상이 됩니다.
- AI 학습 제한: 재능넷의 AI 생성 컨텐츠를 타 AI 모델 학습에 무단 사용하는 행위는 금지되며, 이는 지적 재산권 침해로 간주됩니다.
아직 댓글이 없습니다.