DB

DB 인덱스, 샤딩, 복제 등의 기법을 활용해 성능을 개선한 경험이 있다면, 문제 상황과 적용 방식, 결과를 함께 설명해 주세요.

쿼리 성능 문제를 어떤 방식으로 진단했고, 그에 따라 어떤 인덱스를 어떻게 설계했는지가 중요합니다. 단순히 "인덱스를 추가했다"보다는, 실행 계획(EXPLAIN) 분석이나 파티셔닝·샤딩을 통한 구조적 접근이 담기면 훨씬 설득력 있는 답변이 됩니다.

 

 

## 디비 인덱스

디비 인덱스는 검색이 필요한 곳에서는 빠르게 검색하기 위해 인덱스를 설정한다.

가령 유저라고하면, 아이디, 핸드폰번호, 이메일로 각각 인덱스를 잡는 경우가 많다

조회에서는 생성일 순으로 정릴하는 경우가 많아서 생성일도 인덱스를 개별적으로 사용하기도 한다.

 

 

 

단일 인덱스

1. 아이디

2. 핸드폰 번호

3. 이메일

4. 생성일 순

5. 소속

 

복합 인덱스

6. 소속 + 생성일 순 을 자주 조회한다고 하면 추가된다.

 

그러면 유저라는 테이블에 똑같은 데이터가 정렬된 데이터로 6개가 생성될것 같지만 아니다.

 

각 인덱스(아이디, 핸드폰번호, 이메일, 생성일, 소속, 소속+생성일)는 별도의 자료구조(주로 B-Tree) 로 만들어져,

해당 컬럼의 값과 해당 레코드로 가는 포인터(주소)만 저장한다.

 

## 슬로우 쿼리 문제

조회를 할때, 슬로우 쿼리가 발생하면(조회하는데 시간이 너무 걸리는 경우)

1. 쿼리문 최적회: 쿼리문 조회가 맞는지 체크

2. 인덱스 확인: 테이블의 인덱스가 제대로 설정되어있는지 확인

3. 하드웨어 환경: 서버 리소스가 부족하면 안됨

4. 캐시 설정: 캐시 설정이 안되어 있는 경우

 

 

## 파티셔닝

단일 서버의 논리적 분할

하나의 서버 안에서 논리적으로 똑같은 테이블을 2개로 만드는 것과 같다.

같은 테이블 구조를 유지한다.

 

유저 테이블에서 생성일을 기준으로 파티션을 나눌 수 있다.

202501, 202502, 이런식으로.. 이미 데이터가 분할 됨

기존에는 index로만 적용하면 created_at BETWEEN 20250101 AND 20250228로 찾아야함.

 

효과: 필요없는 파티션을 검색하지 않아서 쿼리 최적화

데이터 양이 많을 때 파티션 단위로 관리할 수 있다(백업, 보관, 삭제)

 

## 샤딩

다중 서버의 물리적 분할

하나의 서버에서 데이터가 많아져서 데이터 서버를 나누는 원리이다.

효과: 단일 서버의 부하를 줄일 수 있다.

 

유저 아이디를 숫자라고하면

1-100 까지는 127.0.0.1/DB
101-200 까지는 127.0.0.2/DB

201-30까지는 127.0.0.3/DB
으로 유저아이디의 범위에 따라 다른 서버의 DB에서 조회한다.

 

 

---

문제 상황

한 온라인 서비스에서는 사용자 활동 로그와 프로필 정보가 저장된 단일 테이블이 있었습니다. 이 테이블은 매일 수백만 건의 업데이트와 조회가 발생했는데, 특히 아래와 같은 쿼리에서 응답 지연이 잦았습니다.

• 상황 1: 관리자가 특정 소속(Organization) 내에서 최근 가입한 회원을 조회할 때
• 상황 2: 사용자 로그인 기록과 프로필을 조인하여 실시간 대시보드를 구성할 때

문제의 원인은 단일 테이블에 모든 데이터를 저장하면서, 조건에 맞는 레코드를 찾기 위해 전체 테이블 스캔(full table scan)이 발생했기 때문입니다. 그리고 동시에 데이터의 양이 점점 증가하여 I/O 부담과 DB 서버 부하가 심화되었습니다.

 

 

진단 과정

1. 실행 계획(EXPLAIN) 분석:
   • 해당 쿼리에 대해 EXPLAIN 명령어를 실행하여, 쿼리 실행 계획과 각 단계의 비용(cost)을 확인했습니다.
   • 분석 결과, WHERE 절에 사용된 소속(organization)이나 생성일(created_at) 관련 컬럼에 인덱스가 없어서 full table scan이 이루어지고 있다는 사실을 확인했습니다.
2. 데이터 분포 및 사용 패턴 확인:
   • 어느 정도의 데이터가 특정 기간, 특정 소속에 집중되어 있는지 파악하였고, 시간의 흐름에 따라 데이터가 기하급수적으로 증가하는 것을 확인했습니다.
3. 주요 병목 구간 파악:
   • 쿼리 응답 시간이 길어지는 원인이 단순한 인덱스 부재뿐만 아니라, 데이터의 물리적 저장 위치와 분산 관리가 미흡했음을 파악했습니다.

적용 방식

1. 인덱스 재설계:
   • 단일 컬럼 인덱스 최적화:
     • 사용자 아이디, 이메일, 핸드폰 번호 등 자주 검색되는 컬럼에 대해 개별 인덱스를 추가했습니다.
   • 복합 인덱스 설계:
     • 관리자가 소속과 생성일을 기준으로 회원을 조회하는 패턴을 고려하여 (소속, 생성일) 순서의 복합 인덱스를 추가했습니다.
     • 이 인덱스를 통해, 특정 소속에 속한 회원을 생성일 순으로 정렬하여 빠르게 조회할 수 있었습니다.
   • 실행 계획 재분석:
     • 인덱스 추가 후 EXPLAIN 명령어를 다시 실행하여, 인덱스 사용 여부와 비용 절감 효과를 확인했습니다.
2. 파티셔닝 도입:
   • 데이터의 규모가 매우 커지면서 최근 데이터만 집중적으로 조회되는 패턴이 있었으므로, 생성일(created_at)을 기준으로 파티셔닝을 적용했습니다.
   • 이를 통해, 특정 기간에 한정된 파티션만 스캔하도록 하여 전체 스캔 부담을 크게 줄였습니다.
3. 샤딩 및 복제 환경 구성:
   • 장기적으로 사용자 데이터와 로그가 급증함에 따라, 수평 샤딩을 고려했습니다.
     • 예를 들어, 사용자 ID 범위나 해시값을 기반으로 데이터를 여러 샤드로 분산 저장하여, 단일 DB 서버의 부하를 분산했습니다.
   • 또한, 읽기 부하가 높은 쿼리들을 위해 읽기 복제(replication) 환경을 구성하여, 읽기 요청이 여러 읽기 전용 복제본으로 분산되도록 했습니다.

 

 

결과

• 쿼리 성능 개선:
  • 복합 인덱스 추가와 파티셔닝 도입 후, 특정 소속의 회원 조회 쿼리 실행 시간이 평균 70% 이상 단축되었습니다.
  • EXPLAIN 결과에서도 인덱스 스캔(Index Scan)으로 변경되어 비용이 크게 줄었습니다.
• 시스템 확장성 향상:
  • 샤딩 및 복제 구성을 통해 단일 DB 서버의 부하를 분산시키고, 읽기 요청 처리 능력을 대폭 개선했습니다.
  • 서비스 확장에 따른 추가 샤드와 읽기 복제본을 손쉽게 추가할 수 있게 되어, 향후 데이터 증가에 유연하게 대응할 수 있게 되었습니다.
• 운영 안정성 증가:
  • 파티셔닝으로 인해 특정 기간 데이터에 문제가 생겨도 전체 테이블이 아닌 해당 파티션만 영향을 받게 되어, 유지 보수 및 백업에서 유리했습니다.