SQL 동작 원리와 최적화

1. JOIN / GROUP BY / HAVING

1-1. JOIN

현실 세계의 데이터는 보통 테이블 하나에 다 넣지 않고 여러 테이블로 나누어 저장합니다.

예를 들어,

• user 테이블: 사용자 정보 (id, name, email…)
• orders 테이블: 주문 정보 (id, user_id, total_price…)

이렇게 나눠 놓으면, 어떤 사용자가 어떤 주문을 했는지를 보려면 두 테이블을 묶어서 조회해야 합니다.

이때 사용하는 것이 JOIN입니다.

가장 많이 사용되는 것은 INNER JOIN, LEFT JOIN입니다.

 

1. INNER JOIN

두 테이블에서 조건에 일치하는 행만 가져오는 조인입니다.

SELECT u.id, u.name, o.id AS order_id, o.total_price
FROM users u
JOIN orders o ON u.id = o.user_id;

• users와 orders에서 u.id = o.user_id가 같은 행들만 결과에 나옵니다.
• 주문을 한 적이 없는 사용자(orders에 데이터가 없는 사용자)는 결과에 나오지 않습니다.

2. LEFT (OUTER) JOIN

왼쪽 테이블을 기준으로 왼쪽은 모두 가져오고, 오른쪽은 매칭되는 것만 가져오는 조인입니다.

SELECT u.id, u.name, o.id AS order_id, o.total_price
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

• 사용자는 모두 가져옵니다.
• 주문이 없는 사용자는 order_id, total_price가 NULL로 나옵니다.
• 주문을 했든 안 했든 전체 사용자 목록과 주문정보를 보고 싶을 때 사용합니다.

3. FULL (OUTER) JOIN

두 테이블의 모든 데이터를 가져오고, 매칭되지 않는 부분은 NULL로 채워서 합치는 조인입니다.

SELECT *
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

• 사용자와 주문 정보를 모두 가져옵니다.
• MySQL은 FULL OUTER JOIN을 공식 지원하지 않습니다. (PostgreSQL, Oracle 등에서는 지원합니다)
  • MySQL에서 비슷한 효과를 내려면 LEFT JOIN + UNION + RIGHT JOIN 방식을 사용합니다.

1-2. GROUP BY

GROUP BY는 데이터를 특정 기준으로 묶어서 집계할 때 사용합니다.

예를 들어 사용자별 주문 총액, 일자별 가입자 수, 카테고리별 상품 개수 같은 것을 구할 때 사용합니다.

SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;

• orders 테이블에서 user_id를 기준으로 묶고,
• 각 사용자별로 주문 건수를 세어줍니다.

SELECT user_id, SUM(total_price) AS total_spent
FROM orders
GROUP BY user_id;

• 사용자별로 얼마나 결제했는지 총액을 구할 수 있습니다.

중요한 점은 SELECT 절에 있는 칼럼 중, 집계 함수가 아닌 칼럼은 모두 GROUP BY에 포함되어야 합니다.

• 집계 함수: COUNT(), SUM(), AVG(), MAX(), MIN() 등

// 잘못된 예시 - total_price는 GROUP BY에도 없고, 집계 함수도 아님
SELECT user_id, total_price, SUM(total_price)
FROM orders
GROUP BY user_id;

// 정상 예시1 - 모든 비집계 컬럼을 GROUP BY에 포함
SELECT user_id, total_price, SUM(total_price)
FROM orders
GROUP BY user_id, total_price;

// 정상 예시2 - total_price를 집계 함수로 묶기
SELECT user_id, SUM(total_price) AS total_spent
FROM orders
GROUP BY user_id;

1-3. HAVING

HAVING은 GROUP BY 후에 만들어진 그룹에 조건을 거는 것입니다. WHERE과 비교하자면, WHERE는 GROUP BY 전에 각 행(row)에 조건을 거는 것입니다.

예를 들어 사용자별 주문 총액이 10만 원 이상인 사용자만 보고 싶다고 한다면

SELECT user_id, SUM(total_price) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(total_price) >= 100000;

• SUM(total_price)는 집계 결과이므로 WHERE이 아닌 HAVING에서 필터링해야 합니다.

정리하면:

• WHERE: 일반 행 필터링
• HAVING: 집계 결과 필터링

SQL 실행 순서 FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY

---

2. 인덱스와 B-Tree 동작 원리

2-1. 인덱스란?

인덱스는 테이블의 데이터를 일일이 훑지 않고도, B-Tree 같은 정렬된 구조를 이용해 원하는 값을 빠르게 찾기 위한 ‘검색용 목차’입니다.

DB에서 인덱스가 없으면 테이블의 모든 행을 처음부터 끝까지 검사(Full Scan) 해야 합니다.

하지만 인덱스가 있으면, 인덱스를 보고 원하는 값이 있는 위치를 빠르게 찾을 수 있습니다.

그래서 인덱스는 주로 WHERE 조건, JOIN 조건, ORDER BY 칼럼에 설정합니다.

// 단일 인덱스
CREATE INDEX idx_users_age ON users(age);

// 복합 인덱스
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at);

2-2. B-Tree 인덱스의 기본 동작

관계형 DB에서 가장 흔한 인덱스 구조는 B-Tree입니다.

핵심 개념은 다음과 같습니다.

1. 인덱스는 특정 칼럼 값들을 정렬된 트리 구조로 저장합니다.
2. 트리의 노드에는 “키 값”과 “다음 노트 또는 실제 데이터 위치”가 들어 있습니다.
3. 데이터를 찾을 때는 트리를 타고 내려가며 비교합니다. → 이진 탐색과 비슷하게 O(log N) 시간에 원하는 값을 찾을 수 있습니다.

---

3. 인덱스가 사용되지 않는(안 먹히는 ) 경우

인덱스를 만들어도, 항상 사용되는 것은 아닙니다.

DB가 인덱스를 써도 이득이 없다고 판단하는 경우도 있고, 개발자가 작성한 쿼리 구조 때문에 인덱스를 못 쓰는 경우도 있습니다.

3-1. 함수/연산을 칼럼에 직접 사용한 경우

SELECT * FROM users
WHERE DATE(created_at) = '2025-01-01';

• created_at에 인덱스가 있어도 DATE(created_at)처럼 칼럼에 함수를 씌우면 인덱스를 잘 활용하지 못합니다.

SELECT * FROM users
WHERE created_at >= '2025-01-01'
  AND created_at <  '2025-01-02';

• 가능하면 이렇게 사용해야 합니다.

3-2. LIKE ‘% 값’ 형태 (앞에 %가 붙는 경우)

SELECT * FROM users
WHERE name LIKE '%bin';

• 문자열 앞에 %가 붙어 있으면, 인덱스 입장에서는 앞부분이 뭔지 모르기 때문에 정렬된 인덱스를 제대로 사용하기 어렵습니다.
• 결과적으로 Full Scan이 일어날 가능성이 큽니다.

WHERE name LIKE 'hyebin%'

• 반면 앞이 고정되어 있고 뒤에 %가 붙는 경우에는 인덱스를 사용할 수 있는 경우가 많습니다.

3-3. 복합 인덱스에서 선두 칼럼을 사용하지 않는 경우

INDEX idx_user (user_id, created_at)

// 사용되는 경우
WHERE user_id = 10
WHERE user_id = 10 AND created_at >= '2025-01-01'

//사용되지 않을 수 있는 경우
WHERE created_at >= '2025-01-01'

• user_id → created_at 순서로 정렬되어 있는 복합 인덱스에서는 왼쪽(선두) 칼럼부터 순서대로 사용할 때 효과가 있습니다.

3-4. 조건에 해당하는 데이터가 너무 많을 때 (선택도가 낮을 때)

SELECT * FROM users
WHERE gender = 'F';

• 전체의 90%가 ‘F’라면, 인덱스를 사용해 여기저기 점프하는 것보다 그냥 Full Scan을 하는 게 더 빠를 수도 있습니다.
• DB는 내부적으로 대략 몇 건이 나올지 통계를 보고 인덱스를 쓸지, Full Scan을 할지 판단합니다.

3-5. 타입이 맞지 않는 비교, 암시적 형변환

SELECT * FROM users
WHERE id = '100';  -- id는 INT인데 문자열 리터럴 사용

• DB에 따라 다르지만, 타입이 맞지 않아 내부에서 형변환이 발생하면 인덱스를 제대로 활용하지 못할 수 있습니다.
• 가능하면 타입을 정확히 맞춰서 비교하는 것이 좋습니다.

---

4. 실행 계획(EXPLAIN) 기본 읽는 법

실무에서는 쿼리가 느린데 왜 느린지를 확인하기 위해 실행 계획을 자주 확인합니다.

EXPLAIN
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;

DB마다 출력형식은 조금씩 다르지만, 중요하게 보든 칼럼은 비슷합니다.

현재 글에서는 MySQL 기준으로 설명하겠습니다.

4-1. 자주 보는 칼럼

• type
  • 접근 방식
  • ALL 이면 Full Table Scan (가장 안 좋음)
  • range, ref, eq_ref, const 등으로 갈수록 더 좋은 방식
    • range: 인덱스를 이용해 특정 범위만 조회 (BETWEEN, IN, >, < ….)
    • ref: 인덱스로 특정 값을 가진 row 조회 (=)
    • eq_ref: 조인에서 탁 한 개의 row를 찾을 때
    • const: 딱 1개의 row만 읽으면 되는 상황이라 상수처럼 취급할 수 있을 때
• key
  • 실제로 사용된 인덱스 이름
  • NULL이면 인덱스를 사용하지 않는 것
• rows
  • 이 쿼리를 위해 대략 몇 개의 행을 읽어야 하는지 추정하는 값
  • 숫자가 작을수록 성능에 유기
• Extra
  • Using index: 인덱스만 보고 데이터를 해결했다는 의미 (커버링 인덱스)
  • Using where: WHERE 조건으로 필터링함
  • Using temporary, Using filesort: GROUP BY / ORDER BY 때문에 임시 테이블/정렬이 발생했음을 의미 (주의할 필요가 있습니다)

4-2. 어떻게 활용하면 좋을까요?

• type = ALL, rows가 아주 크다 → 인덱스 고려
• key = NULL → 인덱스가 없거나, 있어도 사용하지 않음
• Using temporary, Using filesort → GROUP BY / ORDER BY 최적화 필요 가능성

즉, EXPLAIN은 이 쿼리를 DB가 어떤 경로로 처리하고 있는지를 보여주는 지도입니다.

이를 보고, 인덱스 추가나 쿼리 구조 변경을 결정합니다.

---

5. 정규화와 반정규화

5-1. 정규화란?

정규화는 데이터를 중복 없이, 이상 현상이 없도록 여러 테이블로 잘게 나누어 설계하는 과정입니다.

예를 들어, 하나의 테이블에 회원정보 + 주문정보 + 상품정보가 다 들어있다면, 회원 이름을 바꾸면 그 회원의 모든 주문 행을 수정해야 합니다.

이런 구조는 중복도 많고, 수정 시 실수와 불일치가 생기기 쉽습니다. 이러한 문제를 이상(Anomaly)라고 부릅니다.

그래서 회원은 members 테이블, 주문은 orders 테이블, 상품은 products 테이블처럼 나누고, 관계(외래키)로 연결하는 설계를 합니다.

이 과정을 단계적으로 이론화해 놓을 것이 1정규형, 2정규형, 3정규형 등입니다.

5-2. 정규화의 장점과 단점

장점

• 데이터 중복 감소
• 한 곳만 수정하면 되므로 일관성 유지 가능
• 이상 형상(삽입/갱신/삭제 이상) 감소

단점

• 너무 잘게 쪼개면, 데이터 조회할 때 여러 테이블을 JOIN 해야 함
  • JOIN이 많을수록 쿼리가 복잡해지고, 성능 감소

⇒ 완벽한 정규화보다는 적당한 정규화 + 필요시 반정규화를 선택해야 합니다.

5-3. 반정규화란?

반정규화는 조회 성능을 올리기 위해 의도적으로 중복을 허용하면서 데이터를 합치는 작업입니다.

예를 들어, 주문 내역을 조회할 때마다 orders + members + products를 항상 JOIN 해야 해 조회가 너무 잦고, 성능이 문제가 된다면 주문 테이블에 “회원 이름, 상품 이름, 가격”을 넣어둘 수도 있습니다.

이렇게 하면, 한 테이블만 조회해도 필요한 정보를 한 번에 볼 수 있지만 이름이 바뀌거나 가격이 바뀔 때 데이터가 일관되지 않을 수 있습니다. (대신, “주문 시점의 스냅샷”이라고 보고 허용하는 경우가 많습니다.)