데이터베이스 성능 최적화

인덱스 최적화

데이터베이스의 성능을 높이기위한 방법 중 한가지로 인덱스를 사용해 쿼리 튜닝을 하는 방법이 있다.

인덱스의 종류

인덱스는 단일 인덱스와 복합 인덱스가 있다. 단일 인덱스는 하나의 칼럼에 인덱스를 거는 것이고, 복합 인덱스는 여러칼럼을 묶어서 하나의 인덱스를 구성하는 것이다. 복합 인덱스는 선두 칼럼이 반드시 조건에 사용되어야 인덱스를 제대로 탐색할 수 있다.

복합 인덱스를 사용하면 좋은 경우

복합 인덱스는 다음과 같이 두 개 이상의 조건이 결합된 쿼리에서 성능을 크게 향상시킨다

• WHERE 필터링 + 정렬
  • where 절의 칼럼에만 인덱스가 있으면 정렬할 때 filesort(인덱스 없이 정렬하는 것)가 발생
• WHERE 필터링 + 페이징(LIMIT)
  • DB는 조건을 만족하는 모든 데이터를 스캔하고 정렬하고 OFFSET/페이징 함 → 불필요한 연산 많음
• 자주 같이 쓰는 조건들
  • gender, age, university 각각 인덱스가 있어도 → DB는 하나만 선택하거나 Index Merge 전략을 써야 함
  • Index Merge는 느리고 비효율적인 경우가 많음

Index Merge란?
MySQL에서 제공하는 특수한 인덱스 사용 전략으로, 하나의 쿼리에 여러 개의 단일 인덱스를 병합해서 사용하는 방식
Postgresql에서는 Bitmap Index 등이 있음.

테스트

현재 사용중인 프로젝트에서 진행중인 장학금을 조회수순으로 보여주는 기능이 있었다. 여기에 인덱스를 적용하면 응답속도가 빨라질 것 같아 테스트해봤다. 

SELECT PROGRESS_STATUS, VIEW_COUNT
FROM SCHOLARSHIP
WHERE PROGRESS_STATUS = 'ONGOING'
ORDER BY VIEW_COUNT DESC
LIMIT 20;

위 쿼리를 사용하여 진행중인 장학금을 조회수 순으로 상위 20개만 불러오는 쿼리를 사용했다.

인덱스 사용 전

인덱스 사용 전 속도

인덱스를 사용하기 전에는 120ms의 속도로 응답받을 수 있었다.

인덱스 사용 후 

CREATE INDEX IDX_STATUS_VIEW ON SCHOLARSHIP(PROGRESS_STATUS, VIEW_COUNT DESC);

다음과 같은 SQL을 통해 장학금의 상태와 조회수를 복합 인덱스로 설정했다.

인덱스 사용 후 속도

인덱스를 사용하고 나서는 43ms의 속도로 응답 받았다.

결과적으로 120ms -> 43ms 약 70%의 속도 향상이 있었다.

파티셔닝

데이터베이스의 레코드가 너무 많으면 성능에 저하가 올 수 밖에 없는데, 이것을 방지하기 위해서 테이블을 물리적으로 분할하는 것이 파티셔닝이다. 테이블의 행을 기준으로 분할하면 수평적 분할, 테이블의 열을 기준으로 분할하면 수직적 분할이다.

수평적 분할

수평적 분할은 테이블의 레코드가 너무 많은 상황에 사용한다.테이블의 레코드를 참조할 때마다 모든 레코드를 한 번에 불러들일 필요가 없는 경우 테이블을 수평적으로 분할한다.

수직적 분할

수직적 분할은 다음과 같은 이유로 한다.

• 테이블에 발생하는 트랜잭션에 비해 테이블 내의 열이 과도하게 많은 경우
• 테이블의 특정 열이 다른 열보다 과도하게 데이터 크기가 큰 경우
• 보안 상의 이유로 특정 열을 별개의 테이블로 나누어 저장해야 하는 경우

일반적으로 말하는 파티셔닝은 수평적 분할을 의미한다.

파티셔닝의 종류

레인지 파티셔닝

레인지 파티셔닝은 레코드가 가질 수 있는 범위를 정의하고, 해당 범위를 기준으로 테이블을 분할하는 방식이다.

예를 들면, 1900년대에 생성된 레코드는 p0 파티션에 저장하고 2000년대에 생성된 레코드는 p1 파티션에 저장하는 방식이다.

주로 로그 테이블, 주문 테이블, 결제 테이블 등 시간 기반 데이터에 가장 많이 사용된다. 월별, 일별, 또는 연도별로 나누는 경우가 흔함.

리스트 파티셔닝

리스트 파티셔닝은 레코드의 특정 칼럼이 특정 목록(리스트)에 포함된 값을 가질 경우 해당 레코드를 별도의 테이블로 분할하는 방식이다.

예를 들면, address가 Seoul인 레코드는 p0, Busan인 레코드는 p1에 저장하는 방식이다.

주로 특정 코드 값(예: 국가 코드, 지역 코드, 상태 코드)에 따라 데이터를 명확히 분리해야 할 때 사용된다.

해시 파티셔닝

특정 칼럼에 해시함수를 적용하여 나온 결과 값을 기준으로 별도의 테이블로 분할하는 방식이다.

해시를 사용하기 때문에 데이터가 각 파티션에 균등하게 저장된다.

키 파티셔닝

키 파티셔닝은 키를 기준으로 별도의 테이블로 분할하는 방식이다.

키를 기준으로 분할하기 때문에 각 파티션에 균등하게 저장된다.

예를 들면, student의 pk인 student_id의 값에 따라 각 파티션에 저장된다.

파티셔닝 적용 시 고려사항

• 데이터 접근 패턴: 어떤 기준으로 데이터를 조회하고 관리하는지에 따라 적절한 파티셔닝 전략을 선택해야 함
• 복잡성 증가: 파티셔닝을 적용하면 데이터베이스 구조가 더 복잡해지고 관리할 요소가 늘어날 수 있다.
• 파티션 키 선택: 파티션 키를 잘못 선택하면 오히려 성능이 저하되거나 데이터 불균형이 발생할 수 있다.
• 크로스 파티션 쿼리: 여러 파티션에 걸쳐 데이터를 조회해야 하는 쿼리의 경우, 파티셔닝을 하지 않았을 때보다 성능이 저하될 수도 있다.

샤딩

수평적 분할을 통해서 분리한 파티션들이 모두 같은 서버에 있으면 한번에 데이터 요청이 몰렸을 때 분산의 효과를 얻기 힘들다.

이를 해결하기 위해서 분할된 테이블을 독립적인 물리적 서버로 분할하는 것이 샤딩이다.

샤딩은 주로 데이터베이스의 스케일 아웃을 위해서 사용된다.

샤딩의 종류

레인지 샤딩

레인지 샤딩은 특정 범위를 기준으로 데이터를 나눠서 저장하는 방식이다.

특정 범위에 데이터가 몰리거나(핫스팟), 데이터가 불균형하게 분포될 위험이 있다.

해시 샤딩

해시 샤딩은 특정 칼럼에 해시함수를 적용하여 나온 값을 기준으로 데이터를 나눠서 저장하는 방식이다.

특정 범위의 데이터를 조회할 때 모든 샤드를 조회해야 할 수도 있어 비효율적이다.

디렉토리 기반 샤딩

디렉토리 기반 샤딩은 샤딩 키와 해당 데이터가 저장된 샤드 정보를 매핑하는 별도의 조회 테이블(Directory Table)을 중앙에 관리하는 방식이다.

address가 Seoul이면 shard 1에, Busan이면 shard 2에 저장하되, 이 정보를 디렉토리 테이블에서 관리하는 방식

샤드의 추가/제거 및 데이터 재분배에 용이하다.

중앙 디렉토리 테이블이 SPOF(Single Point of Failure)가 될 수 있으며, 조회 시 오버헤드가 발생할 수 있다.

지리적 샤딩

지리적 샤딩은 사용자의 물리적 위치(지리적 위치)를 기준으로 데이터를 가장 가까운 서버에 저장하는 방식이다.

한국 사용자는 한국 리전의 데이터베이스에, 미국 사용자는 미국 리전의 데이터베이스에 데이터를 저장.

사용자에게 낮은 레이턴시를 제공하고, 데이터 주권 규제를 준수하는 데 유리하다.

사용자 이동 시 데이터 마이그레이션이 복잡해질 수 있다.