DB 설계와 쿼리 최적화

 

주의: 본 내용은 DB 내용에 대한 기초 지식이 어느정도 있음(DB 스캔의 작동 방식)을 가정하고 작성되었습니다.

DB는 백엔드에서 가장 중요한 역할을 수행한다.
DB 성능은 연동하는 모든 서버 성능에 영향을 주기 때문에, DB에 존재하는 기능을 전문가 수준으로 깊이 이해할 수 있다면 이상적이겠지만 조금만 신경 써도 DB 성능 문제를 충분히 줄이거나 없앨 수 있다.

---

인덱스 설계

예상하지 못한 테이블 풀 스캔은 DB 성능에 좋지 않은 영향을 끼친다.
이를 피하기 위해서 보통 인덱스를 사용하는데, 이때 인덱스를 제대로 알고 써야 원하는 성능 효율을 얻을 수 있다.

• 인덱스는 조회 패턴을 기준으로 설계해라.
• 단일 인덱스 vs 복합 인덱스의 차이를 고려해라
• 선택도(Selectivity)를 고려해라
• 가능하다면, 커버링 인덱스를 활용해라.
• 인덱스를 남용하지 말아라.

---

인덱스는 조회 패턴을 기준으로 설계하라.

• 10년 동안 쌓인 게시글 수가 520건에 불과하고, 최대 TPS가 10 미만인 상황에서 조회 성능을 올리기 위해 인덱스를 추가할 필요는 없다.
• 인기 커뮤니티 사이트에서 특정 카테고리에 속한 게시글 목록을 찾기 위해 article 테이블을 풀 스캔하면 조회 성능에 심각한 문제가 발생한다.
  • 이 경우, "카테고리별로 게시글 목록을 조회한다"는 패턴을 파악하고, 해당 카테고리 칼럼에 인덱스를 추가해야 한다.

---

단일 인덱스 vs 복합 인덱스

사용자의 모든 활동 내역을 보기 위해 활동 기록 테이블을 만들었다고 하자.

또한 고객센터에서 특정 사용자의 일자별 활동 내역을 조회하는 목적으로 해당 테이블을 사용한다고 가정해보자.
아마 다음 쿼리를 사용해서 활동 내역을 조회할 것이다.

select * from activityLog where userId = 123 and activityDate = '2024-07-31' order by activityDatetime desc;

성능 문제가 없으려면 userId를 포함한 인덱스가 필요하다.

 

여기서 고민할 점은 activityDate를 포함하느냐 하지 않느냐에 대한 것이다.

• 단일 인덱스: userId만 인덱스로 사용
• 복합 인덱스: (userId, activityDate)를 인덱스로 사용

위에서 다뤘던 인덱스는 조회 패턴을 기준으로 설계하라 는 말과 일맥상통하는데, 사용자당 가질 수 있는 데이터가 얼마나 될지 가늠해보면 어떤 인덱스를 사용해야 할지 판단하는 데 도움이 된다.

• 개별 사용자 기준으로 일주일에 하루 정도 방문
• 평균 활동 데이터는 5건

이 경우, 1년을 활동해야 260건이 된다.
이렇게 5년을 꾸준히 활동해야 1,500 건이 쌓인다.
이정도 데이터 건수면 userId 칼럼만 사용하는 단일 인덱스만 사용해도 심각한 문제는 발생하지 않는다.

회원들의 활동성이 좋아진다면, 복합 인덱스 사용을 고려할 것이다.

---

선택도(Selectivity)를 고려한 인덱스 설계

인덱스를 생성할 때는 선택도(selectivity)가 높은 칼럼을 골라야 한다.

선택도는 인덱스에서 특정 칼럼의 고유한 값 비율을 나타낸다.
수식으로는 (고유 값의 수) / (전채 행 수) 로 나타낸다.
예를 들어 성별에 M, F, N 값만을 가질 수 있다면, 선택도는 3/(count)가 된다.

• 되도록이면 선택도가 높은 칼럼을 인덱스로 선택해라.
• 되도록이면 선택도가 높은 칼럼을 복합 인덱스의 앞에 배치해라.
  • DB는 인덱스의 앞부터 순서대로 순회하고, 반드시 앞을 우선 순회한다(찾을 수 없다면, 풀 스캔이라도 수행한다).
  • 하지만 조회 패턴 상 선택도가 낮은 칼럼이 앞에 있어야 한다면, 선택도가 낮더라도 앞쪽에 배치하라.
• 비즈니스 로직 상 선택도가 낮은 칼럼 기준으로 조회가 자주 발생한다면, 이때는 선택도가 낮은 칼럼도 인덱스로 선택해라.

---

커버링 인덱스 활용하기

• 커버링 인덱스는 특정 쿼리를 실행하는 데 필요한 칼럼을 모두 포함하는 인덱스를 말한다.
  • 이 경우, 인덱스만으로 필요한 데이터를 모두 수집할 수 있어, DB 엔진이 디스크 I/O를 덜 수행할 수 있게 된다.

참고로 휴리스틱하게 디스크 I/O와 메모리I/O의 성능 차이는 500~10000배 정도 발생한다.

아래 인덱스가 있다고 가정해보자.

create index activityDate_and_type
on activityLog(activityDate, activityType);

다음 쿼리를 생각해보자.

select * from activityLog 
where activityDate = '2024-07-31' and activityType = 'VISIT';

이 경우, 인덱스를 통해 primary key를 획득하고, 클러스터드 인덱스로 접근하여 전체 데이터를 획득해야 한다.

 

하지만 만약 필요한 데이터가 해당 날짜에 존재하는 방문 타입의 수라면, 다음과 같이 쿼리를 작성할 수 있다.

select count(*) from activityLog
where activityDate = '2024-07-31' and activityType = 'VISIT';

이 경우, 필요한 데이터가 해당 인덱스(논클러스터링 인덱스)에 모두 존재하여, DB 엔진은 클러스터드 인덱스에 굳이 접근하지 않고 인덱스만으로 쿼리를 완수할 수 있게 된다. 따라서 쿼리 실행 시간이 빨라진다.

참고: 인덱스의 종류

인덱스는 크게 클러스터링 인덱스와 논클러스터링 인덱스로 나뉜다.

• 클러스터링 인덱스
  • 물리적 정렬: 테이블 내 데이터가 실제로 물리적으로 정렬되어 저장된다.
  • 유일성: 한 테이블 당 하나만 존재하며, 보통 기본키(PK)에 사용된다.
  • 연속적 데이터 접근 유리: 범위 검색 등 연속적인 데이터 접근 시 성능이 우수하다.
• 논클러스터링 인덱스
  • 논리적 구조: 데이터와는 별도로 관리되는 인덱스 구조이다.
  • 다중 존재 가능: 한 테이블에 여러 개의 논클러스터링 인덱스를 생성할 수 있다.
  • 검색 성능 향상: 특정 컬럼에 대한 검색 속도를 개선하지만, 범위 검색의 경우 추가적인 디스크 I/O가 발생할 수 있다.

---

너무 위에 있는 것 같아 다시 올렸다.

인덱스 남용 금지

아래 두 인덱스를 비교해보자.

• (userId, activityDate)
• (userId, activityDate, activityType)

참고로 둘 중 하나만 만들어야 한다. 이는 인덱스의 작동 방식을 참고해보면 이해하기 쉽다.
다른 사람이 이미 만들어둔 인덱스를 놓치지 말자.

여기서 두 번째 인덱스가 효과를 발휘하려면 한 사용자가 하루에 만들어 내는 데이터 개수가 조회 성능에 영향을 줄 만큼 많아야 한다.
하루에 고작 수백 개 미만일 때에는 인덱스 추가 효과를 체감하기 어렵다.

 

효과가 적은 인덱스를 추가하면 오히려 성능이 나빠질 수 있다. 인덱스는 조회 속도를 빠르게 해주지만 데이터 추가, 변경, 삭제 시에는 인덱스 관리에 따른 비용(시간)이 추가되기 때문이다.
또한 인덱스 자체도 데이터이기 때문에 인덱스가 많아질수록 메모리와 디스크 사용량도 함께 증가한다.

---

인덱스 外 조회 성능 개선 방법

인덱스가 DB 성능 최적화에 가장 많이 적용되지만, 조회 성능을 개선할 방법이 인덱스만 존재하는 것은 아니다.
어렵지 않으면서도 쉽게 사용할 수 있는 개선 방법들에 대해 알아보자.

• 미리 집계해 두어라(비정규화)
• 가능하다면 No-Offset을 사용해라.
• 조회 범위를 시간 기준으로 제한해보아라.
• 전체 개수를 세지 마라.
• 사용되지 않는 오래된 데이터는 삭제 및 분리 보관해라.
• 급하다면, DB 장비를 Scale-up 하는 것이 더 효율적이다.
• 필요하다면, 별도의 캐시 서버를 구성해라.

---

미리 집계하기

다음 기능을 제공하는 간단한 설문 조사 기능을 만든다고 하자.

• 각 설문은 질문이 4개로 고정되어 있다.
• 회원은 각 설문 조사마다 '좋아요'를 할 수 있다.
• 설문 조사 목록을 보여줄 때 답변 수와 '좋아요' 수를 표시한다.

이 기능을 구현하기 위해 다음과 같이 3개의 테이블을 만들었다.

목록을 표시할 때 설문에 답변한 회원 수와 '좋아요'를 한 회원 수를 표시한다는 요건이 있다.
이 요건을 충족하기 위해 설문 목록 조회 쿼리의 select절에 서브 쿼리를 사용할 수 있다.

select s.id, s.subject,
  (select count(*) from answer a where a.surveyId = s.id) as answerCnt,
  (select count(*) from liked l where l.surveyId = s.id) as likeCnt
from survey s
order by id desc
limit 30;

이 쿼리는 논리적으로는 정화하지만 성능에는 문제가 발생할 수 있다.

현재 상황이 다음과 같다고 가정해보자.

• 설문은 30개가 존재한다
• 설문마다 평균 답변자 수는 10만 명이다.
• 설문마다 '좋아요'를 한 회원 수는 1만 명이다.

그렇다면 이 쿼리는 다음과 같은 과정을 거칠 것이다.

1. 설문조사 목록 조회 1번
2. 설문조사 별 답변 조회 30번 (각 쿼리 내에서 10만 개의 데이터를 셈)
3. 설문조사 별 좋아요 조회 30번 (각 쿼리에서 1만 개의 데이터를 셈)

각 쿼리가 세는 데이터 수를 참고해서, 다음과 같이 쿼리 시간을 측정했다.

• 1번: 0.01초
• 2번: 0.1초
• 3번: 0.05초

이렇게 쿼리가 수행되는 경우, 전체 목록을 조회하는 데 걸리는 시간은 다음과 같다.

• 쿼리 시간 = 0.01 + 0.1 * 30 + 0.05*30 = 4.51

사용자가 많은 온라인 서비스에서 4.51초는 매우 긴 시간이다.
이 경우 비정규화를 이용해 답변 수/좋아요 수를 survey 내 컬럼으로 추가할 수 있을 것이다.

이 경우 Cnt 컬럼의 동시성 문제는 필히 고려되어야 할 것이다.

---

페이지 기준 목록 조회 대신 id기준 목록 조회 방식 사용하기(No-Offset)

No-Offset으로도 잘 알려진 이 방식은 모바일 스크롤 방식에 매우 유용하다.
id가 1부터 순서대로 생성된다고 가정하면, 99990번째 게시글을 검색할 때, 다음과 같은 방식을 떠올릴 수 있다.

1. 페이징 성능 개선하기 - No Offset 사용하기

 

select * from article
order by id desc
limit 10 offset 99990;

DB가 99991번째 id부터 바로 조회하면 좋겠지만, DB는 어떤 id값이 99,991번째인지 알지 못한다.

 

이를 다음과 같은 쿼리로 변환할 수 있다.

select * from article
where id < 99990
order by id desc
limit 10

해당 방식을 사용하면 클러스터링 인덱스를 활용하여 즉시 99991번째 게시글을 가져올 수 있다.

 

여기에 살짝 꼬아서 삭제된 게시글이 있다고 가정해보자.
이 경우 위와 같이 즉시 해당 페이지 이동으로는 어렵겠지만, 모바일 스크롤 방식이라면 다음과 같은 방식을 고려할 수 있다.

select * from article
where id < 1001 and deleted = false
order by id desc
limit 11;

• 10개의 게시글을 가져온다.
• 추가로 가져온 1개의 게시글은 다음 페이지의 존재 여부를 확인하기 위한 조회로, 만약 존재하지 않는다면 해당 페이지가 마지막임을 나타낸다.

즉, 1번째 게시글부터 순서대로 다음 페이지 게시글의 id를 저장해두며, 연속적으로 페이지를 가져올 수 있게 된다.

---

조회 범위를 시간 기준으로 제한하기

조회 성능을 개선하는 방법 중 하나는 조회 범위를 시간 기준으로 제한하는 것이다.

• 국내 주요 포털은 기사 목록을 제공할 때 일자별로 분류해서 제공하고 있다.
• 30일 이전 공지사항은 주로 확인되지 않기 때문에, 구글의 경우 최근 보안 활동 화면은 최근 28일 데이터만 보여준다.

이와 같은 방법을 이용하면 DB 성능 또한 향상된다.

---

전체 개수 세지 않기

조회 속도가 조금씩 느려지고 있는데, 그 원인 중 하나가 전체 개수를 세는 쿼리에 있다면 해당 쿼리를 실행하지 않는 방법을 고려해야 한다.
서비스 담당자에게 앞으로 성능 문제가 발생할 수 있다는 점을 알리고, 전체 개수를 화면에 표시하지 않는 방향으로 협의할 필요가 있다.

 

이와 같은 재밌는 실제 사례로는 구글의 검색 페이징이 있다.

 

구글의 경우 검색창에 검색을 수행하면 12페이지가 존재하는 것처럼 보여준다.

하지만, 실제로 해당 페이지에 들어가면 다음과 같은 결과를 볼 수 있다.

즉, 구글은 페이지의 갯수를 정확히 보여주는 것이 구글의 핵심 비즈니스 요구사항이 아님 이라 결정하고, 다음과 같은 페이지를 보여주는 것이다.

---

오래된 데이터 삭제 및 분리 보관하기

데이터 개수가 늘어나면 늘어날수록 쿼리 실행 시간은 증가한다.
즉, 데이터 개수가 증가하지 않으면 실행 시간을 일정 수준으로 유지할 수 있다.
또는 데이터 증가 속도를 늦추면, 실행 시간 증가 폭을 줄일 수 있다.

 

대표적인 예시로는 로그인 시도 내역이 있다.
로그인 시도 내역의 경우 계속 저장해둘 필요가 없다. 따라서 주기적으로 삭제하는 작업을 수행하는 것이 좋다.

---

DB 장비 확장하기

만약 빠르게 성능을 개선할 방법을 찾지 못했거나, 개선에 시간이 필요한 경우라면 일단 DB를 임시로 scale-up하는 것이 도움된다.
이와 같이 서비스를 가능한 상태로 유지하고, 개선할 시간을 벌어야 한다.

 

또는 조회 트래픽 비중이 높은 서비스의 경우, Primary-Replica 구조(Master-Slave) 를 고려하는 것이 도움이 된다.

일시적으로 급증하는 조회 트래픽에 대비해 DB 장비를 수평 확장하면 고정 비용도 함께 증가한다.
한번 증가한 고정 비용은 지속적으로 부담이 되기 때문에 DB 서버를 확장할 때는 비용 대비 얻는 이점이 확실해야 한다.

---

별도의 캐시 서버 구성하기

여러 이유로 DB가 트래픽을 처리하는 데 어려움이 있다면, 별도의 캐시 서버를 구성하는 것을 고려하는 것이 좋다.

 

꼭 대규모 트래픽이 아니더라도, 캐시 서버를 잘 활용하면 DB 확장 대비 적은 비용으로 더 많은 트래픽을 처리할 수 있다.
서버 개발자나 인프라 엔지니어 입장에서도, DB를 확장하는 것보다 레디스와 같은 캐시 서버를 구성하는 것이 상대적으로 부담이 적다.

 

물론 캐시를 도입하면 코드를 수정해야 한다.
하지만 코드 수정에 드는 비용 대비 캐시로 증가시킬 수 있는 처리량이 크다면, 코드를 수정하는 것이 더 합리적인 선택이다.

---

DB 설계와 쿼리 시 알아둬야 할 것들

• 쿼리 타임아웃
  • 응답 지연으로 인한 재시도는 서버 부하를 더욱 가중시킨다.
  • 따라서 쿼리에 타임아웃을 지정하여, 서버 입장에서의 동시 요청 폭증을 막는 것이다.
  • 타임아웃의 길이는 처리의 중요도(기능의 특성)을 고려해 설정하자.
• 상태 변경 시에는 복제 DB에서 조회하지 말아라.
  • select for update의 존재 이유를 생각해보자.
• 배치 쿼리는 실행시간이 증가될 수 있다.
  • 큰 데이터를 처리하기 까다롭다면, 나눠서 처리하는 것이 도움이 된다.
  • 이때, 트랜잭션이 쪼개짐으로써 동일 버전을 보장하지 않음을 유의하고, 동일 버전을 만족할 수 있는 방법을 고려하는 것이 중요하다.
• 타입이 다른 컬럼끼리 조인할 때 특히 조심해라.
  • 비교 타입이 다르다면, 인덱스를 활용하지 못할 가능성이 있다.
• 테이블 변경은 신중하게 해야 한다.
  • 예를 들어 MySQL은 테이블을 변경할 때 새 테이블을 생성하고 원본 테이블의 데이터를 복사한 뒤, 복사가 완료되면 새 테이블로 대체한다.
  • 이 복사 과정에서는 DML 작업을 허용하지 않기 때문에 복사 시간만큼 서비스가 멈춘다.
  • DML을 허용하면서 테이블을 변경하는 기능도 있지만 항상 가능한 것은 아니다.
• DB의 최대 연결 개수에 주의하자.
  • 다음과 같은 시나리오를 상정해보자.
    • API 서버는 세 대다.
    • 각 서버는 30개의 커넥션을 사용한다.
    • DB 서버는 최대 100개의 커넥션을 연결할 수 있다.
    • DB 서버의 CPU 사용률은 20% 수준으로 여유가 있다.
    • 트래픽이 증가하고 있어 수평 확장이 필요하다.
  • 이와 같은 시나리오에선 만약 API 서버를 1개 더 늘리면 20개의 커넥션이 DB와 연결되지 못할 것이다.
  • DB 서버의 최대 연결 개수를 가능한 늘리되, DB 서버의 CPU 사용률이 70% 이상으로 높다면 연결 개수를 늘리면 안된다.