SQL 쿼리 성능 최적화와 정확성 검증

SQL은 데이터를 다루는 거의 모든 분야에서 사용되는 언어이다. 특히 데이터 분석, 백엔드 개발 등에서 SQL 쿼리의 성능과 정확성은 전체 시스템의 효율성에 직결된다. 이전에는 데이터 분석가도 쿼리 최적화에 대해 알아야하나? 라는 생각이 있었지만, 쿼리 한줄한줄이 DW, 레이크하우스에 치명적인 부하를 줄 수도 있으며 무엇보다도 실행 한 번에 비용이 부과되는 경우에는 많은 고민을 해야한다. 

때문에, 앞으로 쿼리 효율성에 대해 차근차근 정리를 해보고자 한다. 

 

1. WITH 문의 장단점

WITH문이란?

WITH문은 서브쿼리에 이름을 붙여 임시 테이블처럼 사용할 수 있게 해주는 SQL 구문이다. CTE를 표현하기 위해 사용한다. 복잡한 쿼리를 논리적 단계로 나누거나, 반복되는 서브쿼리를 재사용할 수 있도록 도와준다.

 

- 장점

 

• 가독성 향상
  쿼리 로직을 명확하게 단계별로 구분할 수 있어 이해와 유지보수가 쉽다.
• 중복 서브쿼리 재사용 가능
  동일한 하위 쿼리를 여러 번 사용할 때, 한 번만 정의하면 재사용할 수 있다.
• 재귀 쿼리 가능
  계층 구조나 순차 데이터(경로 탐색, 트리구조 등)에 매우 유용하다.

- 단점

 

• 일부 DBMS에서는 매번 실행되어 비효율적
  PostgreSQL, BigQuery 등에서는 CTE를 참조할 때마다 실제로 다시 실행된다.
• 읽기 쉬워도 느릴 수 있음
  CTE는 옵티마이저 최적화가 덜 일어나는 경우가 있다.
  (사실, 쿼리가 길어지다보면 읽기 쉬운게 맞는건가 .. 하는 스스로의 물음도 있다.)
• DBMS마다 동작 방식 다름
  MySQL, SQL Server, PostgreSQL, BigQuery에서 성능과 최적화 처리 방식이 다르다.

-- 최근 30일 내 주문 중 10만원 이상 주문만 조회
WITH recent_orders AS (
  SELECT *
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL 30 DAY
)

SELECT *
FROM recent_orders
WHERE total_amount >= 100000

 

 

 

2. SQL 쿼리 성능 최적화를 위한 기초

1) 인덱스 사용
인덱스는 테이블 내 특정 컬럼의 값을 빠르게 찾을 수 있도록 도와주는 자료 구조이다. 인덱스를 잘 활용하면 쿼리의 속도를 수 배~수십 배까지 향상시킬 수 있다. 

 

- 인덱스는 언제 필요할까?

• WHERE 절에 자주 등장하는 컬럼
• ORDER BY나 GROUP BY에 사용되는 컬럼
• JOIN 조건으로 자주 사용되는 키 컬럼

-- 특정 지역에서 카드 결제로 진행된 주문만 조회
SELECT order_id, payment_method
FROM orders
WHERE region = 'Seoul'
  AND payment_method = 'card';

 

위 쿼리는 region과 payment_method를 동시에 조건으로 사용하고 있다. 이 두 컬럼에 인덱스를 걸면 성능이 향상된다.

-- 성능 개선을 위한 인덱스 생성
CREATE INDEX idx_region_payment ON orders(region, payment_method);

복합 인덱스를 통해 해당 조건을 한 번에 검색할 수 있어 쿼리 속도가 크게 향상될 수 있다.

 

 

2) JOIN과 서브쿼리 최적화

JOIN은 여러 테이블의 데이터를 조합할 때 필수적인 기능이다. 그러나 잘못된 JOIN 사용은 심각한 성능 저하를 유발할 수 있으므로 주의가 필요하다. INNER JOIN은 모든 행이 일치하는 경우에만 검색해 보통 성능이 뛰어나다. JOIN할 테이블, 필터링 조건, JOIN 조건 등을 고려해 선택해야한다.

 

서브쿼리는 SELECT 문 안에 또 다른 SELECT 문이 있다고 생각하면 된다. 서브쿼리는 복잡한 쿼리를 작성할 때 유용하지만, 성능 저하의 원인이 될 수 있다. 따라서 서브쿼리를 사용할 때는 최소화해서 사용하는 것이 좋다. 아니면 디비가 뻥 ~

-- 사용자 정보가 필요하지 않은데도 JOIN한 경우
SELECT o.order_id
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE o.delivery_type = 'standard'

→ 위 쿼리에서 users 테이블 정보를 실제로 사용하지 않으므로 JOIN은 불필요

 

SELECT *
FROM orders
WHERE user_id IN (
  SELECT user_id
  FROM orders
  WHERE payment_method = 'card'
);

→ 동일 테이블을 다시 조회하고 있으므로 비효율적

-- 비효율적인 서브쿼리 예시
SELECT *
FROM orders
WHERE customer_id IN (
  SELECT customer_id
  FROM customers
  WHERE region = 'SEOUL'
);

-- 개선된 JOIN 방식
SELECT o.*
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.region = 'SEOUL';

3. 내가 작성한 쿼리가 정확한지 확인하는 방법

SQL은 문법만 맞다고 해서 정확한 결과를 보장하지 않는다 .. 스스로 정합성 체크를 해야한다.

 

1) 샘플 데이터를 기반으로 예상값 비교

raw data를 직접 열어서 확인하라는 거고 가장 빠르고 편한 길이지만, 쿼리가 복잡해졌을 때는 사용하기 좀 어려웠다.

 

2) COUNT, SUM 등의 집계함수로 검증

-- 전체 주문 수
SELECT COUNT(*) FROM orders;

-- 특정 조건 하의 주문 수
SELECT COUNT(*) FROM orders WHERE total_amount >= 100000;

→ 필터 전후 차이를 보고 결과 범위가 예상과 일치하는지 확인

 

3) 중간 단계로 쿼리 쪼개서 확인

쿼리가 길어지고 복잡해질 때 가장 많이 사용했던 방식이다.

-- 중간 결과를 WITH로 나눠 검토
WITH high_value_orders AS (
  SELECT * FROM orders WHERE total_amount >= 100000
)
SELECT COUNT(*) FROM high_value_orders;

→ 하위 쿼리 결과만 먼저 확인하면 전체 흐름에 대한 신뢰도가 높아진다.

 

4) 다른 방식으로 교차 검증해보기

JOIN 방식 vs 서브쿼리 방식, CTE 방식 등 다른 쿼리 방식으로 동일 로직을 구현해 결과를 비교해본다.