Post

3장. 성능을 좌우하는 DB 설계와 쿼리

주니어 백엔드 개발자가 반드시 알아야 할 실무 지식

31
3장. 성능을 좌우하는 DB 설계와 쿼리
topic/java type/guide

성능에 핵심인 DB

H 서비스는 교사와 학생을 대상으로 하는 서비스

-> 코로나 시기, 재택 수업이 시작되며 서비스 가입자가 증가.
-> 10초가 넘도록 응답을 받지 못해 에러가 발생 & 사용자 불만이 극에 달했음

=> 문제는 DB!

사용자가 조금만 증가해도, DB 장비의 CPU 사용률이 90%를 넘김.
CPU 사용률이 높아지면 전체 쿼리 실행 시간이 느려지고, 서버 응답 시간도 느려진다!

발생한 이유는 Full Scan 때문이였다.
데이터가 적을 때는 문제가 되지 않지만, 데이터 개수가 증가하며 쿼리 실행 시간이 느려지기 시작했다.

Full Scan?

모든 데이터를 순차적으로 읽는 것

  • where 절 조건에 대응하는 인덱스 없을때 발생
  • 인덱스를 사용하는 것보다 전체 데이터 탐색이 더 빠를 때도 발생
    데이터가 늘어나면 응답 시간이 기하급수적으로 증가하게 된다

DB 성능은 연동하는 모든 서버 성능에 영향을 준다.

조회 트래픽 고려한 인덱스 설계

일반적으로 대부분의 시스템은 조회 기능의 실행 비율이 높다. - 파레토 법칙
EX) 다수의 사용자는 게시판에서 글을 읽는다. 소수 사용자만 새로운 게시글 등록 & 수정 & 삭제를 한다.

그리고, 같은 게시판이어도 트래픽 규모는 다를 수 있다.
EX) 인기 있는 커뮤니티, 회사 내부 사용하는 공지 게시판 - 네이버 카페, 에펨코리아, 레딧 등 모두 특징이 미세하게 다르다

DB 테이블을 설계할 때는 조회 기능 과 트래픽 규모 를 고려해야 한다.

1
2
3

select id, category, writerId, title, content
from article
where category = 10 order by id desc limit 20, 10

category 에 인덱스가 없다면??
-> 상황을 봐야한다.

  1. 사내 공지용 게시판이라면.
  • 매주 1건씩 공지 사항을 올린다고 가정 - 1년에 52개, 10년간 520개
  • 직원 수 1,000 명이고 모든 직원이 5분 내 읽는다고 가정 - 초당 트래픽은 6.67 TPS

데이터양이 적고, 동시 접속자도 매우 적기 때문에 테이블 풀 스캔하면 성능 문제가 발생하지 않는다.

  1. 인기 커뮤니티 사이트라면.
  • 현재 등록된 전체 게시글 1,000만건 - 하루 5,000개 게시글 등록 + 약 6년

사용자가 게시글 목록 조회할 때마다 1,000만 건의 데이터를 비교해야 한다.
많은 사용자가 동시에 조회하면, 다수 풀 스캔 발생 -> CPU 사용률 100%에 도달하며 DB 가 뻗게된다.

=> 조회 패턴을 기준으로 인덱스를 설계해야 한다.

%검색어% 와 같은 LIKE 연산자는 풀 스캔을 유발한다.
-> ElasticSearch 같은 검색 엔진 or FULL TEXT 인덱스등을 통해 풀 스캔을 피할 수 있다.

단일 인덱스와 복합 인덱스

활동 내역을 보관하기 위해 activityLog 테이블을 만들었다고 가정하자.

  • 일평균 방문 회원 10만 명
  • 평균 5번의 활동

-> 하루 50만건, 한 달 1,500만건

  1. 고객 센터에서 특정 사용자의 일자별 활동 내역 조회 목적으로 사용한다면?
1
2
3
4

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

userId 가 123이고, activityDate 가 2024-07-31 데이터를 검색한다.
userId 를 포함한 인덱스는 필수적이다.

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

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

사용자당 가질 수 있는 데이터가 얼마나 될지 가늠해보면 판단하는데 도움이 된다!

  • 개별 사용자 기준 : 1주일 하루 정도, 평균 활동 데이터 5건이면 -> 1년 활동해야 260건!

이정도 데이터 건수면 단일 인덱스를 사용해도 심각한 문제는 발생하지 않는다.

활동성이 좋다면, 복합 인덱스 사용을 고려해야 한다.
사용자당 수만 건이 넘는 데이터가 쌓인다면 조회 속도가 느려질 수 있다.

  1. activityLog 테이블은 통계를 추출하기 위한 목적이 있다면?
1
2
3
4

select activityDate, activityType, count(activtyType)
from activtyLog
where activtyDate = '2024-07-28'
group by activtyType;

데이터를 Full Scan 하지 않으려면? activityDate 에 인덱스를 걸어야한다.
쿼리 실행 빈도 와 실행 시간 을 검토해서 포함 여부를 결정할 수 있다.

하루 평균 쌓이는 데이터가 50만 개면 적지 않은 숫자다. 실행 시간이 10초 이상 걸릴 수 있다.

-> 하루에 한 번만 실행하고 그 결과를 별도 테이블에 저장한다면? 30초가 걸려도 문제 될 건 없다!

물론, 하루 쌓이는 데이터가 더 늘어나 수백만 개가 된다면 상황이 달라진다.
이때는 복합 인덱스 + 커버링 인덱스를 통해 조회 시간을 단축시켜 나갈수 있다.

선택도를 고려한 인덱스 칼럼 선택

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

  • 선택도(Cardinality) : 인덱스 특정 칼럼의 고유한 값의 비율, 높으면 고유한 값이 많다 - 낮으면 고유한 값이 적다
  • 선택도가 높을수록 인덱스 이용한 조회 효율이 높아진다.
1
2
3
4
5

create table member (
	memberId int not null primary key,
	gender char(1),
	...
)

gender 가 M, F, N 3개 중 하나를 가지고 인덱스를 사용한다.
M이 50만 개, F가 50만 개, N이 천 개라면 다음 쿼리 실행하면 여전히 50만 개 데이터를 확인해야 한다.

But, 인덱스를 사용한 칼럼 고를 때 항상 높아야 하는 건 아니다.

1
2
3
4
5

create table jobqueue (
	jobid varchar(16) not null primary key,
	status char(1) not null,
	...
)

status 칼럼은 W, P, C 를 가진다.
대부분 칼럼은 C이고, 적은 수의 데이터만 W와 P를 가진다.

이 역시도, 인덱스를 사용하기 좋은 칼럼일 수 있다.

1
2
3

select * from jobqueue
where status = 'W'
order by jobid asc;

작업 실행기는 이 쿼리를 반복해서 실행하므로, 오래 걸리면 모든 작업 실행이 지연되는 문제가 발생한다.
선택도 상관없이 status 칼럼을 인덱스로 추가해야 한다. - 인덱스 걸려 있지 않으면 full scan 발생시킴

커버링 인덱스 활용하기

특정 쿼리 실행하는 데 필요한 칼럼을 모두 포함하는 인덱스

1
2

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

activityDate, activityType 칼럼 사용하는 인덱스가 있다면 읽을 데이터를 빠르게 선택할 수 있다.
조회할 데이터를 선택하는 과정은 빠르지만, 결국 실제 데이터 자체는 읽어와야 한다.

1
2
3

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

쿼리가 이렇게 바뀌면? 실제 데이터에 접근하지 않는다.
모두 인덱스에 포함되어 있기 때문에, 실제 데이터 읽어오는 과정이 생략되므로 쿼리 실행 시간이 빨라진다.

인덱스는 필요한 만큼만 만들기

효과가 적은 인덱스는 오히려 성능을 나쁘게 만들수 있다.
-> 조회 속도는 빠르게 해주지만, 추가 & 변경 & 삭제시 인덱스 관리의 비용이 추가된다.

요구사항을 일부 변경해서 인덱스를 추가하지 않을수도 있다.

몇 가지 조회 성능 개선 방법

인덱스가 아니어도 성능을 개선할 방법은 존재한다.

미리 집계

설문에 답변한 회원 수, ‘좋아요’ 한 회원 수를 표시한다면?

1
2
3
4
5
6

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;

해당 쿼리는 성능에 문제가 발생할 수 있다.

count 는 필연적으로 인덱스를 타기 어렵다.
각 설문마다 답변지 수를 구하기 위한 서브 쿼리가 나간다. - 30번 반복

=> 집계 데이터를 미리 계산해서 별도 칼럼에 저장할 수 있다.

1

update survey set answerCnt = answerCnt + 1 where surveyId = 아이디;

비정규화는 괜찮은가?

사실, 위 방법은 데이터를 중복하는 것이다. ( answer 테이블로 개수를 구할 수 있었음 )
+ 무결성도 깨진다.
그럼에도, 위 경우에는 실시간 집계용 칼럼이 더 좋은 선택일 수 있다.
answer 테이블의 데이터 개수가 10,150 개일때 answerCnt 값이 10,149 라고 문제 될 건 없다.
-> 어차피, 최종 보고서 및 백오피스에서는 실제 개수를 구하면 된다.

동시성 문제는?

원자적 연산이라면 5만큼 증가한다.
원자적 연산이 아니라면 값이 어떻게 될지 예측할 수 없다.
사용하는 RDBMS 를 확인 및 검증해야 한다.

페이징 대신 ID 조회 (커서) 방식 사용하기

1
2
3
4

select id, subject, writer, regdt
from article
order by id desc
limit 10 offset 99990;

DB 는 어떤 값이 99,991 번째인지 알지 못한다.
DB 는 역순으로 99,990 개를 세고 나서야 10개의 데이터를 조회할 수 있다.

인덱스를 타지 못한다면 최악일 것

1
2
3
4

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

id 는 인덱스를 타므로 9985 보다 작은 값을 바로 찾을 수 있다.
마지막 조회한 ID 기준으로 다음 데이터 요청해 조회 속도를 높일 수 있다.

다음 데이터 여부가 (hasNext) 필요하다면 1개만 더 일어서 판단하면 된다.
-> 11개 조회하고, 10개를 반환해주되 1개는 탐지용으로 사용

조회 범위 시간 기준 제한하기

뉴스 기사 목록을 제공한다고 가정하면?

1
2
3
4
5

select title
from news
where regdt >= '2024-08-09 00:00:00' and regdt >= '2024-08-10 00:00:00'
order by regdt desc
limit 100;

하루 등록되는 기사에 따라 인덱스를 추가하지 않고도, 다른 조건들을 더해도 성능이 유지된다.
(즉, 날짜 순으로 빠르게 조회 후 나머지는 Full Scan 도 가능)

1
2
3
4

select * from checkResult
where cust_id = ?
	and checkMonth >= ?
order by checkMonth desc;

최신 데이터만 조회하는 것도 방법이다.
고객은 최근 받은 점검 결과만 정상인지 관심이 있으며, 3년 전 받은 결과에는 관심이 없다.

최신 데이터로 제한하면 캐시 효율도 높일 수 있다.

전체 개수 세지 않기

조건에 해당하는 데이터 개수를 구하기 위해선 count 함수를 사용해야만 한다.
count 는 모든 데이터를 탐색해야 한다. 커버링 인덱스를 사용해도 전체 인덱스 스캔을 해야한다.

적절히, PM 과 얘기해서 협의를 해나가야 한다.

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

데이터 개수가 늘어나면 쿼리 실행 시간은 증가한다.
-> 즉, 데이터 개수가 증가하지 않으면 실행 시간을 일정 수준으로 유지할 수 있다.

로그인 시도 내역등은 장기간 보관 필요 없다.
ISMS 등에서 요구하는 보안 기한이나, 이상탐지 등이 목적이지 충분히 언제든 삭제할 수 있다.

또는, 기한이 지난 데이터는 별도 저장소로 분리 보관하는 것도 방법이다.

단편화

테이블에서 데이터 삭제하면, 실제 사용하는 디스크 용량도 줄어드는가?
DELETE 쿼리를 날려도, DB 가 사용하는 디스크 용량은 줄어들지 않는다.
삭제되었다는 표시만 남기고, 향후 재사용되는 방식이다.
-> 데이터가 흩어져 저장되면 빈 공간이 생기는게 단편화!

최적화

데이터를 재배치해 단편화를 줄이고, 물리적 디스크 사용량을 줄여준다.

DB 장비 확장하기

DB 에 부하가 증가해 성능 문제가 발생한다면?
개선 방법을 찾지 못한 채 헤매면, 며칠동안도 장애 상황이 지속되는 대참사가 발생할 수 있다.

과감하게 DB 장비를 수직으로 확장하는 것도 방법이다.

수평 확장 역시도 고려할 수 있다.
수평으로 확장하면, 처리할 수 있는 트래픽을 늘릴 수 있다.

조회 트래픽 비중이 높은 서비스라면, 주 DB - 복제 DB 구조를 사용해 처리량을 효과적으로 늘릴 수 있다.

데이터 변경 쿼리는 주 DB, 조회 쿼리는 복제 DB 를 통해 실행

별도 캐시 서버

트래픽이 급격히 증가하면, DB 만으로는 트래픽 처리가 어려워질 수 있다.
다양한 개선 방법을 적용해도 한계가 있꼬, 비용 문제로 더 이상 확장을 못하는 상황도 발생할 수 있다.

꼭 대규모 트래픽이 아니더라도, 캐시 서버를 통해 DB 를 확장하는 것보다 적은 비용으로 더 많은 트래픽을 처리할 수 있다.

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

알아두면 좋은 몇 가지 주의 사항

쿼리 타임아웃

동시 사용자가 증가할 때 응답 시간이 길어지면, 처리량은 감소한다. - 반비례
하지만, 단순히 처리량만 떨어지는 것에서 끝나지 않는다..!

사용자는 몇 초만 지나도 서비스가 느리다고 느껴 몇 초 후에 재시도를 하게 될 것이다!
(응답 지연으로 인한 재시도가 서버 부하를 더욱 가중시키는 형태)

그렇기에, 쿼리 실행 시간을 제한하는 것이다.

트래픽이 증가할 때, 실행 시간이 5초를 넘기면 제한 시간 초과로 에러가 발생한다.
사용자는 에러 화면을 보게 되지만, 서버 입장에서는 해당 요청을 정상적으로 처리시킨 것이다.
( 이전 요청이 처리 중인 상태가 아니므로, 동시 요청 수가 폭증하는 것도 방지 )

물론, 서비스의 기능 & 특성에 따라 다르게 설정해야 한다.
블로그 글 조회 같은 기능은 타임아웃을 몇 초 이내 짧게 설정해도 되지만, 상품 결제 같은 기능은 긴 타임아웃이 필요하다.
(타임아웃 에러가 발생하면, 후속 처리 & 데이터 정합성 등 오히려 더 복잡해질 수 있기 때문)

상태 변경 기능은 복제 DB 에서 조회 X

모든 SELECT 쿼리를 무조건 복제 DB 에서 실행하는 경우가 있다.
이는 2가지 측면에서 문제를 일으킬 수 있다.

주 DB - 복제 DB 는 순간적으로 데이터가 일치하지 않을 수 있다.
주 DB 에서 변경된 데이터는 두 단계를 거쳐 복제 DB 에 반영된다.

  • 네트워크 통해 복제 DB 에 전달
  • 복제 DB 는 자체 데이터에 변경 내용 반영

이 과정은 시간이 걸린다. 일시적으로 서로 다른 값을 가질 수 있다.

물론, RDS 를 사용하면 엄청 빠르고 엄청 정확하게 반동기를 해준다.

이 경우, 잘못된 데이터를 조회 ( 즉, 변경이 반영되지 않은 ) 하면 사용자 요청을 제대로 처리할 수 없게 된다.

그리고, 트랜잭션 문제가 발생할 수 있다.
주 DB - 복제 DB 간 데이터 복제는 트랜잭션 커밋 시점에 이뤄진다.

주 DB 트랜잭션 범위 내 데이터를 변경, 복제 DB 에서 변경 대상 데이터를 조회 하면 데이터 불일치가 발생할 수 있다.

-> 회원 가입, 변경, 등록, 삭제 등과 실행하는 기능은 주 DB 에서 SELECT 쿼리를 실행하자.

배치 쿼리 실행 시간 증가

배치 프로그램은 데이터를 일괄 조회, 집계, 생성하는 작업을 수행한다.
데이터가 많아질수록 쿼리의 실행 시간도 함께 증가한다. ( 증가에 따라 몇 분 ~ 몇 십분 까지도 걸린다. )

이 특성상 많은 양의 메모리를 사용하고, 특정 임계점을 넘기면 예측할 수 없을만큼 길어질 수 있다!!

두 가지를 적용해볼 수 있다.

  • 커버링 인덱스 활용
  • 데이터 일정 크기로 나눠서 처리 - 청크

하루 데이터, 1시간 데이터, 10분 데이터 등등 짧은 간격으로 나눠 실행할 수 있다.

테이블 변경은 신중하게

데이터가 많은 테이블은 새로운 칼럼 추가 및 기존 열거 타입 칼럼을 변경할 때 매우 주의해야 한다.
❗정말 주의해야 한다고 한다❗

DB 의 테이블 변경 방식 때문이다.

EX) MySQL 은 테이블 변경 시

  1. 새 테이블 생성
  2. 원본 테이블 데이터 복사
  3. 복사 완료 시, 새 테이블로 대체

이 복사 과정에서 잠금 상태에 따라, DML 작업도 멈출 수 있다.

-> 복사 시간만큼 서비스가 멈출 수 있다!

무중단 배포가 가능한 경우도 있지만, 필연적으로 점검 시간을 잡고 변경해야 하는 경우도 존재한다.

⭐ 서비스를 멈추게 한 테이블 변경
( ㅋㅋ 우리도 이런 경험 있다… 7000만건 + 계속 쌓이는 테이블을 파티셔닝 시도하다가 실패했음)

저자 범균님의 경험 역시도 비슷하다.
수천만 건 데이터가 있는 테이블에서 열거 타입 칼럼 변경 과정에서 문제가 발생함.
데이터가 많았기 때문에 십 분이 지나도 테이블 변경이 끝나지 않음.
-> 여러 기능에서 사용되고 있었으므로, 이미 서비스는 정상적 제공을 할 수 없는 상태였다.

바로 점검 모드로 전환하고, 테이블 변경이 끝날 때 까지 기다릴 수 밖에 없었다.

문제를 일으킨 원인은 단 한줄의 간단한 테이블 변경 쿼리였다고 한다.
한 줄의 쿼리가 8시간 가까이 서비스를 중단 시키고, 며칠간 데이터 보정 & 고객 보상등 후처리에 많은 시간을 들이게 했다고 한다.

DB 최대 연결 개수

DB 서버 자원에 여유가 있으나, API 서버에서 DB에 연결되지 않는다면 DB 에 설정된 최대 연결 개수를 확인해야 한다.

EX) API 서버당 커넥션 30개를 사용할 때, DB 최대 연결 개수가 100개라면?
4대가 될 때 20대의 커넥션을 얻지 못하고 연결 실패가 발생하게 된다.
-> DB 의 최대 연결 개수를 늘려서 문제를 해결할 수 있다.

단, DB 서버의 CPU 사용률이 70% 이상으로 높다면 연결 개수를 늘리면 안된다고 한다.
연결 수가 많아질수록 DB 부하는 증가하고, 성능 저하가 발생할 수 있다.

=> 캐시 서버 구성 & 쿼리 튜닝 같은 조치를 통해 DB 부하를 낮추고 최후에 연결 개수를 늘리자.

실패와 트랜잭션 고려

모든 코드가 항상 정상적인 해피 케이스로만 동작하지 않는다.
그렇기에, 비정상 상황에서 트랜잭션 처리를 반드시 고민해야만 한다.

트랜잭션 시작과 종료 경계를 명확히 설정했는지 반드시 확인 해야만 한다.
( 실수로 빠뜨리거나, 외부 API 때문에 못하거나 등등 )

  1. DB 연결
  2. 계약 데이터 조회
  3. 계약 생성
  4. 사용자 계약 상태 변경

과 같을때, 4개는 하나의 트랜잭션에 처리되어야만 한다.

경우에 따라 일부 기능에서 오류가 나도 트랜잭션을 커밋해야 하는 상황도 존재한다.

  1. 멤버 추가
  2. 회원가입 축하 메일 발송

메일 발송 중 예외가 발생할 때, 회원 가입 전체가 실패해야 하는가?
-> 메일 발송 오류는 별도 처리해서 무시해야 한다.
( Spring 의 Runtime Exception Catch 를 조심 - 의도치않게 롤백될 수 있음 )