쿼리가 느린가요? 혹시 조인 조건에 인덱스는 걸었나요?

in Blog on Db, Sql 
SELECT o.order_id, u.username
FROM orders o
LEFT JOIN users u
ON u.order_id = o.order_id
   AND u.status = 'active'
   AND u.region = 'Seoul';

위 쿼리에서 u.order_id와 o.order_id에는 인덱스가 걸려있다고 가정합시다.
id만으로도 충분한데 거기다 status와 region 조건까지 추가했으니, 이 쿼리는 빠를 것이라고 생각하는 사람들만 이 글을 읽으세요.

저는 그런 생각을 했었고, 조인문에 조건 쿼리는 인덱스가 반드시 있어야 합니다.라는 코드리뷰를 받고나서 더 알아본 것들을 적었습니다.



관계형 데이터베이스에서 테이블은 주로 다음 원칙들을 기준으로 나뉩니다.

  1. 정규화(Normalization): 데이터 중복을 최소화하고 데이터 무결성을 보장하기 위한 과정입니다.
  2. 엔티티(Entity): 현실 세계의 개체나 개념을 표현합니다. 예를 들어, ‘사용자’, ‘상품’, ‘주문’ 등이 각각 별도의 테이블이 될 수 있습니다.
  3. 관계(Relationship): 엔티티 간의 연관성을 나타냅니다. 일대일(1:1), 일대다(1), 다대다(N) 관계가 있으며, 다대다 관계는 보통 중간 테이블로 분리합니다.
  4. 업무 도메인(Business Domain): 업무 프로세스나 기능에 따라 테이블을 분리합니다. 예를 들어, ‘회원관리’, ‘주문관리’, ‘재고관리’ 등으로 구분할 수 있습니다.
  5. 데이터 접근 패턴: 데이터를 어떻게 조회하고 사용할지에 따라 테이블 설계가 달라질 수 있습니다.
  6. 성능 최적화: 쿼리 성능을 향상시키기 위해 테이블을 분할하거나 통합할 수 있습니다.
  7. 확장성(Scalability): 데이터 증가에 대비해 테이블을 분할(파티셔닝, 샤딩)할 수 있습니다.

이렇게 나뉘어진 테이블에 대하여 분산된 관련 정보를 함께 조회할 때면 JOIN을 사용합니다.
‘관련’되어 있음을 시스템적으로 나타낼 때 외래키(Foreign Key)를 사용해 관계 설정을 합니다.
이러한 외래키를 생성할 때 MySQL이나 MariaDB에서는 인덱스를 자동으로 생성해해주고, 그 인덱스를 통해 JOIN을 사용할 때 데이터를 비교적 빨리 조회할 수 있어요.

인덱스는 뭐길래 검색을 빠르게 해주며, 조인에서 왜 중요한 역할을 할까요?

인덱스가 뭐야?

사전
영어 사전에서 Dictionary라는 단어를 찾는다면, D에 해당하는 페이지를 펼칠 거예요.
이때 A, B, C, D와 같이 나뉘어진 이걸 인덱스라고 합니다. 목차같은 거예요.
데이터베이스에서의 인덱스도 그런 역할을 합니다.
인덱스가 없으면 책 전체를 뒤져야 하듯이, 데이터베이스도 테이블 전체를 스캔해야 해요.

예를 들어, customers 테이블에 10만 명의 고객 데이터가 있다고 해볼게요.
customer_id에 인덱스가 있으면, customer_id = 123인 데이터를 찾을 때 바로 그 위치로 점프할 수 있어요.
인덱스가 없으면? 10만 행을 하나씩 확인해야죠. 이게 풀 테이블 스캔(Full Table Scan)이에요.

조인에서 인덱스가 중요한 이유

조인은 두 테이블을 연결하는 작업이라서, 한쪽 테이블의 각 행마다 다른 테이블에서 조건에 맞는 행을 찾아야 해요.
주문 테이블에 1,000건이 있다고 하면, 고객 ID를 기준으로 고객 테이블에서 매칭되는 데이터를 1,000번 찾아야 하는 거죠.
고객 테이블의 고객 ID에 인덱스가 없으면 매번 10만 행을 다 확인해야 하니까 엄청 느려져요. 🥲
그런데 덱스가 있으면 찾는 속도가 빨라져서 조인도 순식간에 끝날 수 있어요.
조인 알고리즘(예: Nested Loop, Hash Join)이 인덱스(예: B-트리, 해시)를 만나면 훨씬 효율적으로 움직인다고 생각하면 돼요.

조인 조건과 인덱스의 관계

조인할 때 조건이 하나만 있는 게 아니라 여러 개일 때가 있을 수 있습니다. 이러한 조인 조건에 여러 컬럼이 들어가면 이야기가 좀 복잡해져요.
예를 들어, 주문과 사용자를 연결하는데, 주문 ID가 같아야 하고, 사용자가 활성 상태여야 하며, 특정 지역에 속해야 한다고 해볼게요.
이런 조건들이 다 들어가면 인덱스를 어디다 둬야할까요?

SELECT o.order_id, u.username
FROM orders o
LEFT JOIN users u
ON u.order_id = o.order_id
   AND u.status = 'active'
   AND u.region = 'Seoul';

이 쿼리는 orders와 users를 조인하는데, 조건이 세 개예요:

  1. u.order_id = o.order_id
  2. u.status = 'active'
  3. u.region = 'Seoul'

문제 상황: order_id에만 인덱스가 있다면?

o.order_id는 기본 키라 인덱스가 있다고 치고, u.order_id에도 인덱스가 있다고 해볼게요.

근데 status와 region에는 인덱스가 없다면?

데이터베이스는 u.order_id로 일단 매칭되는 행들을 찾은 뒤, 그중에서 status = 'active'와 region = 'Seoul'을 만족하는 행을 다시 확인해야 해요.

만약 users 테이블에 50만 행이 있고, order_id로 필터링해도 1만 행이 남는다면, 그 1만 행을 또 다 뒤져야 하는 거죠.

해결책: 복합 인덱스

이런 경우엔 users 테이블에 (order_id, status, region) 순으로 복합 인덱스(Composite Index)를 만들면 좋아요:

복합 인덱스는 여러 컬럼을 한 번에 커버해서, 세 조건을 모두 효율적으로 필터링할 수 있어요.
이렇게 하면 풀 스캔 없이 필요한 데이터만 바로 가져올 수 있죠.

🧐 복합 인덱스의 순서가 중요한가요?
네, 중요해요. 자주 사용되는 컬럼이나 값 분포가 다양한 컬럼을 앞에 두는 게 좋다고 해요.
여기선 주문 ID가 조인의 핵심이니까 첫 번째로 오는 게 맞아요.

그럼 인덱스를 싹 다 걸어버릴까요?!

지금까지의 이야기를 바탕으로 한다면, 왠지 모든 쿼리의 조건문에 해당하는 컬럼들에 인덱스를 걸어야만 할 것 같죠.
데이터를 빠르게 찾게 해주는 인덱스를 모든 곳에 추가하게 되면 어떤 일이 일어날까요?

1. 디스크 공간이 부족해질 수 있어요.

인덱스는 다음과 같은 추가 데이터를 저장합니다.

  • 키 값: 인덱싱하는 컬럼의 실제 값들입니다.
  • 포인터/참조: 해당 키 값이 실제로 저장된 테이블 내 데이터 행의 위치를 가리키는 참조 정보입니다. 이는 보통 디스크의 물리적 주소나 행 식별자(row identifier)로 구성됩니다.
  • 인덱스 구조 데이터: 인덱스 유형에 따라 다르지만, 예를 들어 B-Tree 인덱스의 경우 트리 구조를 유지하기 위한 노드 정보, 부모-자식 관계 정보 등을 저장합니다.
  • 메타데이터: 인덱스에 대한 통계 정보, 인덱스 깊이, 밀도 등 쿼리 최적화에 사용되는 정보입니다.
users 테이블:
id | name      | email              | created_at
---+-----------+--------------------+------------
1  | 김철수     | kim@example.com    | 2023-01-01
2  | 이영희     | lee@example.com    | 2023-01-02
3  | 박지민     | park@example.com   | 2023-01-03

위 테이블에 name 컬럼에 인덱스를 설정한다면, 데이터베이스는 다음과 같은 정보를 별도의 인덱스 구조로 저장합니다:

name 인덱스:
키 값    | 데이터 위치(포인터)
---------+----------------
김철수   | 1번 행의 주소
박지민   | 3번 행의 주소
이영희   | 2번 행의 주소

디스크 공간이 부족할 경우 데이터베이스 관련해서 다양한 문제가 발생할 수 있습니다:

  1. 데이터 삽입 실패: 새로운 레코드를 추가하거나 기존 레코드를 업데이트할 때 디스크 공간이 부족하면 작업이 실패합니다. 이는 애플리케이션에서 오류로 나타날 수 있으며, 사용자 경험에 직접적인 영향을 미칩니다.
  2. 데이터베이스 성능 저하: 디스크 공간이 부족해지면 데이터베이스는 내부적으로 공간을 효율적으로 관리하기 위해 추가 작업(압축, 재구성, 확장, …)을 수행하게 되어 전반적인 성능이 저하됩니다.
  3. 트랜잭션 실패: 여유 공간이 부족하면 트랜잭션 로그를 기록할 공간이 없어 트랜잭션이 실패할 수 있습니다.
  4. 백업 실패: 백업 작업은 추가 공간을 필요로 하므로, 디스크 공간이 부족하면 백업이 실패할 수 있습니다.
  5. 인덱스 재구성 불가능: 인덱스 재구성 작업이 필요할 때 디스크 공간이 부족하면 이 작업이 실패합니다.
  6. 서비스 중단: 심각한 경우 데이터베이스 서비스 자체가 중단될 수 있습니다.

2. 쓰기(INSERT/UPDATE/DELETE) 성능이 저하될 수 있어요.

데이터를 추가/수정/삭제할 때마다 인덱스도 함께 업데이트가 되야 합니다.
업데이트 해야 하는 인덱스가 많아진다면, 그만큼 추가/수정/삭제가 느려지겠죠?

그래서 꼭 필요한 컬럼에만 인덱스를 만드는 게 좋습니다.

🧐 그럼 언제 인덱스를 쓰면 좋을까?

  • 자주 검색이나 조인에 사용되는 컬럼.
  • 데이터가 많고, 읽기 작업이 쓰기보다 많은 경우.

실무에서 인덱스의 필요성을 어떻게 확인할까?

“인덱스가 필요하다”는 걸 판단하기 위해 실제로 쿼리가 느린지, 어디서 문제가 되는지 어떻게 알 수 있을까요?

이때 유용한 게 쿼리 실행 계획(EXPLAIN)입니다.

MySQL/PostgreSQL에서는 쿼리 앞에 EXPLAIN을 붙여 실행 계획을 확인할 수 있습니다:

EXPLAIN SELECT o.order_id, u.username
FROM orders o
LEFT JOIN users u
ON u.order_id = o.order_id
   AND u.status = 'active'
   AND u.region = 'Seoul';

실행 계획에서 주목해야 할 핵심 요소들:

  1. 접근 타입 (type) - 성능에 직접적인 영향을 미치는 요소
    • type: ALL → 풀 테이블 스캔, 인덱스 없이 전체 테이블을 읽음 (최악) 👎
    • type: INDEX → 인덱스 풀 스캔, 인덱스를 모두 읽지만 ALL보다는 빠름
    • type: RANGE → 인덱스 범위 스캔, 조건에 맞는 범위만 스캔
    • type: REF → 인덱스를 활용한 참조, 비고유 인덱스 매칭 (좋음)
    • type: eq_ref → PRIMARY/UNIQUE 인덱스 매칭 (매우 좋음)
    • type: const → 단 하나의 매칭 행, 최고 성능 (최상) 👍
  2. 쿼리 코스트 (cost) - DBMS가 계산한 쿼리 실행 비용
    • MySQL에서는 단일 코스트 값으로 표시 (예: cost: 125.10)
    • 낮을수록 좋음
    • rows 값과 함께 고려해야 함 (많은 행을 처리할수록 실제 부하 증가)
    • 실무 가이드라인:
    • 소규모 테이블: 코스트 > 50 검토 필요
    • 중간 규모 테이블: 코스트 > 200 검토 필요
    • 대규모 테이블: 코스트 > 500 검토 필요
  3. rows - DBMS가 처리할 것으로 예상하는 행 수
    • 실제 결과 행 수와 크게 차이나면 통계 업데이트 필요
    • 값이 클수록 많은 데이터를 처리한다는 의미 → 인덱스 검토 대상
  4. Extra 정보 - 추가 실행 정보
    • Using index → 커버링 인덱스 사용 중 (매우 좋음)
    • Using filesort → 정렬을 위한 추가 작업 필요 (개선 필요)
    • Using temporary → 임시 테이블 사용 (매우 나쁨, 개선 필요)

실무에선 이렇게 실행 계획을 보고, 풀 스캔이 발생하는 부분에 인덱스를 추가하거나 쿼리를 튜닝합니다.

예시로 보는 문제 상황

이제 실무에서 흔히 볼 법한 예시를 하나 들어볼게요.

주문과 결제 정보를 연결하는 쿼리가 있습니다.

SELECT o.order_id, p.payment_amount
FROM orders o
LEFT JOIN payments p
ON p.order_id = o.order_id
   AND p.payment_status = 'completed'
   AND p.payment_method = 'card';
  • orders: 10만 행
  • payments: 50만 행
  • o.order_id에만 인덱스가 있음

위와 같은 상황을 가정해볼게요.

문제점

이 쿼리에서 발생하는 총 스캔 수는 얼마정도 될까요?

  1. orders 테이블 스캔: 10만 행 (전체 테이블 스캔)
  2. payments 테이블 접근:
    • 각 orders 행(10만 개)에 대해 p.order_id = o.order_id 조건으로 인덱스를 사용하여 매칭되는 행을 찾습니다.
    • 인덱스가 있으므로 테이블 전체를 스캔하지는 않고, 인덱스를 통해 필요한 행만 접근합니다.
    • 평균적으로 각 order_id당 약 5개의 payment 레코드가 있다고 가정하면, 약 50만 행을 인덱스를 통해 접근합니다.
      • 일반적으로는 하나의 order에 하나의 payment만 있겠지만, 예외적인 상황은 있을 거고 일단 가정을 해보아요ㅎㅎ..
  3. 인덱스로 찾은 payments 행에 대한 추가 필터링:
    • payment_status = 'completed'와 payment_method = 'card' 조건은 인덱스가 없으므로, 인덱스로 찾은 각 행에 대해 이 조건을 확인해야 합니다.

따라서 총 접근/스캔 행 수는:

  • orders 테이블: 10만 행
  • payments 테이블(인덱스 접근): 약 50만 행

총합 약 60만 행의 데이터에 접근하게 되겠네요.
3번의 경우 이미 접근한 행에 대한 필터링으로 추가 스캔이 되지는 않지만, 메모리 내에서 CPU 리소스를 사용하게 됩니다.
쿼리 실행 시간은 디스크 I/O 시간과 CPU 처리 시간의 합이니, 대용량 데이터에서는 필터링의 CPU 부하도 중요한 성능 요소입니다.

개선 방법

아까 말했던 해결책인 복합 인덱스를 걸어보겠습니다.

CREATE INDEX idx_payments_order_status_method 
ON payments (order_id, payment_status, payment_method);

이 인덱스가 존재할 경우 쿼리 실행 과정은 다음과 같이 변경됩니다:

  1. orders 테이블 스캔: 10만 행 (여전히 전체 테이블 스캔)
  2. payments 테이블 접근:
    • 각 orders 행에 대해 복합 인덱스를 사용하여 p.order_id = o.order_id AND p.payment_status = 'completed' AND p.payment_method = 'card' 조건을 모두 만족하는 행을 직접 찾습니다.
    • 이는 인덱스만으로 모든 조건을 평가할 수 있기 때문에 매우 효율적입니다(커버링 인덱스).
    • 실제 payments 테이블 데이터에 접근하지 않고도 인덱스 내에서 모든 조건을 확인할 수 있습니다.

이 경우 총 접근/스캔 행 수는:

  • orders 테이블: 10만 행
  • payments 인덱스 접근: 10만 번의 인덱스 조회 (각 order_id에 대해 한 번씩)

따라서 총 데이터 접근량이 이전의 110만 행에서 약 20만 행 정도로 크게 줄어듭니다. 또한 추가 필터링 작업이 필요 없어지므로 CPU 사용량도 감소합니다.

마치며

인덱스가 중요한 건 알았어도, “왜 조인에서 특히 중요한가”를 몰랐다면 알아가는 시간이 되셨길.

여기까지. 안녕!

© 2023. All rights reserved.

Powered by Hydejack v9.1.6