10. 인덱스

1) 인덱스의 정의

인덱스(Index)란

테이블의 특정 컬럼(또는 컬럼 조합)을 정렬된 구조(B-Tree 등)로 별도 저장해서

검색(WHERE), 정렬(ORDER BY), 조인(JOIN) 을 빠르게 만드는 자료구조다.

• 데이터(테이블) = 본문
• 인덱스 = 목차

   목차가 없으면 책을 처음부터 끝까지 훑어야 한다(Full Table Scan).

---

2) 인덱스가 필요한 쿼리 패턴 3가지

2-1) WHERE 조건 검색

SELECT * FROM customers WHERE email = 'user10@example.com';

2-2) ORDER BY + LIMIT (Top-N)

SELECT * FROM orders ORDER BY ordered_at DESC LIMIT 20;

2-3) JOIN 키 (FK ↔ PK)

SELECT ...
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

---

3) 현재 스키마에 이미 존재하는 인덱스 확인

SHOW INDEX FROM customers;
SHOW INDEX FROM products;
SHOW INDEX FROM orders;
SHOW INDEX FROM order_items;
SHOW INDEX FROM payments;

스키마상 핵심 인덱스

• customers : PRIMARY(customer_id), UNIQUE(email)
• products : PRIMARY(product_id), UNIQUE(sku)
• orders : PRIMARY(order_id), INDEX idx_orders_customer_ordered(customer_id, ordered_at)
• order_items : INDEX idx_order_items_order(order_id), INDEX idx_order_items_product(product_id)
• payments : UNIQUE(order_id) (주문당 결제 1개)

---

4) EXPLAIN으로 “인덱스가 쓰였는지” 확인하는 법

4-1) 고객 이메일 검색 (UNIQUE 인덱스)

EXPLAIN
SELECT customer_id, name, status
FROM customers
WHERE email = 'user10@example.com';

확인:

• key에 uk_customers_email이 잡히면 인덱스 사용
• type이 const 또는 ref면 매우 좋음
• rows가 작을수록 좋음

---

4-2) 주문: 고객별 최근 주문 10건 (복합 인덱스 사용)

EXPLAIN
SELECT order_id, ordered_at, total_amount
FROM orders
WHERE customer_id = 1
ORDER BY ordered_at DESC
LIMIT 10;

• idx_orders_customer_ordered(customer_id, ordered_at)가 딱 맞는 이유
  • WHERE에서 customer_id로 좁히고
  • ordered_at으로 정렬 + LIMIT

---

5) 인덱스 설계 원칙

5-1) “선행 컬럼” 규칙 (복합 인덱스의 핵심)

복합 인덱스 (A, B)는 아래는 빠름:

• WHERE A = ...
• WHERE A = ... AND B = ...
• WHERE A = ... ORDER BY B ...

하지만 아래는 보통 못 씀(인덱스 효율 낮음):

• WHERE B = ... 만

  예: 현재 존재하는 인덱스

• idx_orders_customer_ordered(customer_id, ordered_at)

  • 고객별 주문 조회/정렬에 최적

---

5-2) 선택도(Selectivity)가 높은 컬럼이 유리

• 값 종류가 많아야(중복이 적어야) 인덱스 효과 큼

예:

• email(거의 유일) → 인덱스 매우 유리
• status(종류 3~5개) → 인덱스 효율 낮을 수 있음

---

5-3) 인덱스는 “읽기”를 빠르게 하지만 “쓰기”는 느리게 한다

INSERT/UPDATE/DELETE 시:

• 테이블 변경 + 인덱스도 같이 갱신해야 함

따라서 인덱스를 무작정 많이 만들면:

• 쓰기 성능 하락
• 디스크/메모리 사용 증가

---

6) 실습: 인덱스 유무 비교

6-1) PK 조회 (인덱스 사용)

EXPLAIN
SELECT product_id, name
FROM products
WHERE product_id=1;

실행 계획 핵심

type = const
key  = PRIMARY
rows = 1

• DB가 딱 1건만 읽고 끝냄
• 가장 이상적인 접근 방식

---

6-2) 인덱스 없는 컬럼 조회 (Full Scan)

EXPLAIN
SELECT product_id, name
FROM products
WHERE name='USB-C Cable 1m';

실행 계획 예상

type = ALL
key  = NULL
rows = 30

---

7) JOIN에서 인덱스가 중요한 이유

7-1) 주문상세에서 주문별 아이템 조회

EXPLAIN
SELECT *
FROM order_items
WHERE order_id = 1;

• idx_order_items_order(order_id) 덕분에 빠름
• 없다면 order_items 전체 스캔 위험

---

JOIN 쿼리

SELECT*
FROM orders o
JOIN order_items oi
ON o.order_id= oi.order_id
WHERE o.order_id=1;

인덱스가 있을 때 vs 없을 때 (JOIN 관점)

🔹 인덱스 있음

order_items.idx_order_items_order(order_id)

• DB가:
  • order_id = 1 인 위치로 바로 점프
  • 해당 주문의 아이템 몇 건만 읽음

• JOIN 시:
  • orders 1건
  • order_items 2~3건
  • 총 읽는 row 수 매우 작음

---

🔹 인덱스 없음

WHERE order_id = 1

• DB가:
  • order_items 전체를 다 읽고
  • order_id = 1 인 row만 골라냄

👉 JOIN 시:

• orders 1건
• order_items 전체 N건 스캔

8) 운영 관점 체크리스트

• “느린 쿼리”가 발생하면
  1. EXPLAIN으로 실행계획 확인
  2. Full Scan인지 확인(type=ALL)
  3. WHERE/JOIN/ORDER BY 컬럼 기준으로 인덱스 검토
• 인덱스 추가는 성능 만능 해결책이 아님
  • 쓰기량 많은 테이블은 인덱스 최소화
• RDS 같은 Managed DB에서도
  • 인덱스 설계는 사용자가 책임짐

---

연습문제 : 인덱스

• payments에서 method 조건 조회를 빠르게 하기 위해 (method, paid_at) 복합 인덱스를 생성하시오.

인덱스명: idx_payments_method_paidat

create index idx_payments_method_paidat
on payments(method, paid_at)

-- 확인 --
show index from payments;