주니어 백엔드 개발자가 반드시 알아야 할 실무 지식 ‐ 성능을 좌우하는 DB 설계와 쿼리 - thought-corner/Backend-PlayGround GitHub Wiki

풀 스캔(Full Scan)

풀 스캔은 테이블의 모든 데이터를 순차적으로 읽는 것을 말한다. 보통 쿼리에서 where 절에 있는 조건에 대응하는 인덱스가 없을 때 발생한다.
인덱스를 사용하는 것보다 전체 데이터를 탐색하는 것이 더 빠를 때에도 풀 스캔이 발생한다.
데이터 개수가 적을 때에는 풀 스캔을 해도 성능 문제가 겉으로는 드러나지 않지만 데이터 개수가 늘어나면 어느 순간 응답 시간이 기하급수적으로 증가하게 된다.
따라서 DB를 설계할 때 풀 스캔 발생 가능성을 항상 염두에 두어야 한다.
DB 성능은 연동하는 모든 서버 성능에 영향을 준다.

❗ DB에 부하가 생기면 DB에 연결된 모든 서버의 응답 시간을 증가시키고 이는 다시 클라이언트에 영향을 준다.

조회 트래픽을 고려한 인덱스 설계 방법 - 선택도를 고려한 인덱스 칼럼

예를 들어, 카테고리별로 나눠 게시글 목록을 조회하는 API가 있다고 가정하자.
게시글 테이블은 카테고리를 저장하기 위해 category 컬럼을 가지게 될 것이고 카테고리변로 분류하는 쿼리는 다음과 같을 것이다.

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

where절에 들어가는 컬럼에 대해서 무조건 인덱스를 만들라는 의도가 아니다. 서비스의 사용 빈도와 선택도를 고려해야함을 강조하고 싶은 것이다.
인덱스를 생성할 때는 선택도(selectivity)가 높은 컬럼을 골라야 한다.
여기서 선택도란, 인덱스에서 특정 칼럼의 고유한 값 비율을 말한다. 선택도가 높으면 해당 컬럼에 고유한 값이 많다는 뜻이며 선택도가 낮으면 고유한 값이 적다는 뜻이다.
선택도가 높을수록(=고유한 값이 많을수록) 인덱스를 이용한 조회 효율이 높아진다.

전문 검색 인덱스

문자열을 이용한 검색 기능은 일반적인 서비스에서 흔히 볼 수 있는 기능이다.
제목에 특정 단어가 포함된 게시글을 검색하는 기능이 이에 해당된다. 다음 쿼리처럼 like를 사용해서 조건을 지정하면 된다.

select id, category, writerId, title, content
from article
where title like '%검색어%' order by id desc limit 10

그런데 중간에 포함된 단어를 검색하기 위해 넣는 like 조건은 풀 스캔을 유발한다.
Elastic Search같은 검색 엔진을 사용하는 환경이라면 DB를 사용하지 않고도 검색 기능을 구현할 수 있다.
하지만 별도의 검색 엔진을 구성하기 힘든 상황이라면 DB가 제공하는 전문 검색 기능 사용을 고려해볼 수 있다.
MySQL 엔진의 경우 FULL TEXT 인덱스를 사용하면 풀 스캔 없이도 문자열 검색 쿼리를 실행할 수 있다.

❗ 무조건 인덱스를 사용하라는 것이 아니다. "데이터 규모"를 보고 능동적으로 판단할 수 있는 시각을 기르는 것이 중요하다.

조회 트래픽을 고려한 인덱스 설계 방법 - 커버링 인덱스

특정 쿼리를 실행하는데 필요한 칼럼을 모두 포함하는 인덱스를 말한다.
예를 들어, 다음 쿼리를 보자.

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

activityDate 컬럼과 activityType 컬럼을 사용하는 인덱스가 있다면 인덱스를 사용해서 읽을 데이터를 빠르게 선택할 수 있다.
데이터를 선택한 뒤에는 컬럼값을 조회하기 위해 각 데이터를 읽어 온다.
인덱스를 사용해서 조회할 데이터를 선택하는 과정은 빠르지만 실제 데이터 자체는 테이블로부터 읽어와야 한다.

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

이 쿼리는 실제 데이터에 접근하지 않는다.
왜냐하면 쿼리를 실행하는데 필요한 activityDate, activityType 컬럼이 모두 인덱스에 포함되어 있기 때문이다.
실제 데이터를 읽어오는 과정이 생략되므로 쿼리 실행 시간이 빨라진다.

인덱스는 필요한 만큼만 만들어라

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

인덱스가 아니어도 조회 성능을 개선할 수 있는 방법 1 : 미리 집계하기

다음과 같은 예시 쿼리가 있다고 가정하자.

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개의 설문이 있고 설문마다 평균 답변자 수가 100,000명이고 '좋아요'를 한 회원 수가 10,000명이라고 가정하면 다음과 같이 쿼리가 실행된다.

1. survey 테이블: 1회 스캔 (30개 행)
2. 서브쿼리 실행:
  answer 카운트: 30회 (각 설문마다)
  liked 카운트: 30회 (각 설문마다)
  총 서브쿼리: 60회
3. 데이터 스캔량:
  answer 테이블: 30 × 100,000 = 3,000,000행
  liked 테이블: 30 × 10,000 = 300,000행
  총: 약 330만 행 스캔

count나 sum같은 집계 쿼리를 조회 시점에 실행하면서 발생하는 성능 문제를 제거하는 방법이 있다.
집계 데이터를 미리 계산해서 별도 칼럼에 저장하면 된다.
이 방법을 무조건 적용하라는 것이 아니다. 무결성이 깨지는 문제가 발생할 가능성이 다분하다.
약간의 불일치를 감수하더라도 그렇게 제공이 되어도 된다고 판단이 된다면 미리 집계용 컬럼을 놔두는 것이 좋다는 것을 말하고 싶다.

인덱스가 아니어도 조회 성능을 개선할 수 있는 방법 2 : 페이지 기준 목록 조회 대신 ID 기준 목록 조회 방식을 사용하기

게시글 데이터가 100,000개가 있다고 가정하고 화면에 10개의 게시글을 보여주면 전체 페이지 개수는 10,000개가 된다.
첫 페이지를 읽어오기 위한 쿼리는 다음과 같을 것이다.

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

다음은 마지막 10,000번째 페이지에 해당하는 게시글을 읽어오는 쿼리다.

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

이 쿼리를 실행할 때, 99,991번째 id부터 바로 조회가 되면 좋겠지만, DB는 어떤 id값이 99,991번째인지를 알지 못한다.
다음과 같이 id를 활용하면 개선이 가능하다. 처음 10개를 조회하는 쿼리는 다음과 같다.

select *
from article
whrer deleted = false
order by id desc
limit 10;

이 쿼리 결과로 조회한 마지막 데이터의 id가 있을 것이다. 그 다음 10개 데이터를 읽을 때는 앞서 읽어온 마지막 id를 사용해서 조회하면 된다.

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

기본적으로 id는 인덱스이기 때문에 풀스캔을 하지 않고도 찾을 수가 있다.
만약 다음에 읽어올 데이터가 존재하는지 여부를 알려주는 속성을 응답 결과에 포함시켜달라고 요청하면 추가 데이터 존재 여부를 boolean으로 응답해주면 된다.

인덱스가 아니어도 조회 성능을 개선할 수 있는 방법 3 : 조회 범위를 시간 기준으로 제한하기

조회 성능을 개선하는 방법 중 하나는 조회 범위를 시간 기준으로 제한하는 것이다.
최신 데이터 위주로 조회하게 기능을 변경하면 DB 성능 또한 향상된다.
DB는 성능을 높이기 위해 메모리 캐시를 사용한다.

인덱스가 아니어도 조회 성능을 개선할 수 있는 방법 4 : 전체 개수 세지 않기

목록을 표시하는 기능은 전체 개수를 함께 표시하는 경우가 많다.
조건에 해당하는 개수를 구하기 위해서는 count 함수를 사용해야 한다.
데이터가 적을 때는 count 쿼리를 실행해도 큰 문제가 되진 않는다. 문제는 데이터가 급격히 증가하기 시작할 때, 발생한다.
데이터가 많아질수록 count 쿼리 실행 시간도 증가하게 되는데 그 이유는 조건에 해당하는 모든 데이터를 탐색하기 때문이다.
커버링 인덱스를 사용하더라도 전체 인덱스를 스캔해야 하며, 커버링 인덱스가 아닌 경우에는 실제 데이터를 전부 읽어야만 한다.

인덱스가 아니어도 조회 성능을 개선할 수 있는 방법 5 : 오래된 데이터 삭제 및 분리 보관하기

데이터 개수가 늘어나면 늘어날수록 쿼리 실행 시간은 증가한다.
데이터 증가 폭을 낮추기 위해서는 과거 데이터를 삭제하는 방법이 있다.

단편화와 최적화의 개념

DELETE 쿼리를 이용해 테이블에서 데이터를 삭제하면 실제 사용되는 디스크 용량은 줄어들지 않는다.

DB는 해당 데이터가 삭제되었다는 마크를 남기고 삭제된 공간은 향후 재사용한다.

하지만 데이터가 반복적으로 추가되고, 변경되고, 삭제되는 과정에서 데이터가 흩어져 저장되고 빈 공간이 생기는 단편화 현상이 발생할 수 있다.

단편화가 심해지면 디스크 I/O가 증가하면서 쿼리 성능이 저하될 수 있다. 또한 테이블에 실제로 보관된 데이터 크기보다 더 많은 디스크 공간을 사용하게 되어, 디스크 낭비도 발생한다.

단편화로 인한 성능 저하를 해결하는 방법 중 하나는 최적화 작업이다. 최적화는 데이터를 재배치해 단편화를 줄이고 물리적인 디스크 사용량까지 줄여주는 효과가 있다.

DB 장비 확장하기

DB에 부하가 증가해 성능 문제가 발생하고 있다면 어떻게 해야할까?
빠르게 성능 개선 방법을 찾지 못했다면 DB 장비를 수직으로 확장해 유지하고 개선할 수 있는 시간을 벌어야 한다.
DB를 수평으로 확장하면 DB가 처리할 수 있는 트래픽을 늘릴 수 있다.
조회 트래픽 비중이 높은 서비스의 경우 주 DB - 복제 DB 구조를 사용해 처리량을 효과적으로 늘릴 수 있다.
서버는 데이터 변경 쿼리는 주 DB를 통해, 조회 쿼리는 복제 DB를 통해 실행한다. 조회 기능에 대한 트래픽이 증가하면 복제 DB를 추가해 조회 처리량을 확장할 수 있다.

별도 캐시 서버 구성하기

트래픽이 급격히 증가하면 DB만으로 모든 트래픽을 처리하기 어려워질 수 있다.
캐시를 도입하면 코드를 수정해야 한다. 하지만 코드 수정에 드는 비용 대비 캐시로 증가시킬 수 있는 처리량이 크다면 코드를 수정하는 것이 더 합리적인 선택이다.

쿼리 타임아웃

응답 지연으로 인한 재시도는 서버 부하를 가중시킨다.
이런 식으로 재시도가 반복되면 동시에 처리해야 하는 요청 수가 기하급수적으로 증가하게 되고 서버 부하는 폭증하게 된다.
이런 상황을 방지하는 방법 중 하나로 쿼리 실행 시간을 제한하는 것이다.
예를 들어 쿼리 실행 시간을 5초로 제한하면 트래픽이 증가해 쿼리 실행 시간이 5초를 넘기면 제한 시간 초과로 에러가 발생한다.
사용자는 에러 화면을 보게 되지만 서버 입장에서는 해당 요청을 정상적으로 종료한 셈이 된다.
사용자가 재시도를 하더라도 이전 요청이 여전히 처리 중인 상태가 아니므로 동시 요청 수의 폭증을 막을 수 있다.

상태 변경 기능은 복제 DB에서 조회하지 않기

주 DB - 복제 DB를 구성할 때 변경은 주 DB를 사용하고 조회는 복제 DB를 사용한다.
그러나 이 구성을 잘못 이해해서 조회 자체를 무조건 복제 DB에서 실행하는 경우가 있다. 이는 2가지 측면에서 문제를 일으킬 소지가 있다.

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

1-1. 네트워크를 통해 복제 DB에 전달
1-2. 복제 DB는 자체 데이터에 변경 내용을 반영

이 과정에는 시간이 걸린다.
주 DB에서 복제 DB로의 데이터 복제에는 지연이 발생한다.
이 지연 시간만큼 주 DB와 복제 DB는 일시적으로 서로 다른 값을 갖게 된다.

2. 트랜잭션 문제가 발생할 수 있다.

주 DB와 복제 DB 간 데이터 복제는 트랜잭션 커밋 시점에 이루어진다.
주 DB 트랜잭션 범위 내에서 데이터를 변경하고 복제 DB에서 변경 대상이 될 수 있는 데이터를 조회하게 되면 데이터 불일치로 인해 문제가 생긴다.
GET을 제외한 나머지 HTTP Method 쿼리를 실행하는 기능에서 변경 대상 데이터를 조회해야 한다면 복제 DB가 아닌 주 DB에서 SELECT 쿼리를 실행하도록 해야 한다.
그래야 데이터 불일치로 인해 발생할 수 있는 오류를 방지할 수 있다.

배치 쿼리 실행 시간 증가

배치 프로그램은 데이터를 조회하거나 집계하거나 생성하는 작업을 수행한다.
집계 쿼리는 특성상 많은 양의 메모리를 사용하게 되며, 특정 임계점을 넘기면 실행 시간이 예측할 수 없을 만큼 길어질 수 있다.
이런 문제를 예방하려면 배치에서 사용되는 쿼리의 실행 시간을 지속적으로 추적해야 한다.
집계 쿼리는 특성상 많은 데이터를 스캔해야 한다. 이 때, 집계 대상 칼럼이 인덱스에 포함된 상태라면 데이터를 직접 읽지 않고 인덱스만 스캔해 집계를 수행할 수 있다.
데이터를 일정 크기로 나눠 처리하는 것도 해결책이다.

타입이 다른 칼럼 조인 시 주의

두 칼럼의 값을 비교하는 과정에서 DB는 타입 변환을 수행한다.
receiverId 칼럼은 varchar 타입이므로 userId와 비교하려면 DB는 receiverId 값을 integer 타입으로 변환해야 한다.
이 변환은 각 행마다 발생하며 결과적으로 receiverId 인덱스를 온전히 활용하지 못하게 된다.
전체 데이터를 스캔하지 않고 인덱스만 스캔하는 경우에도 이런 변환 작업으로 인해 쿼리 실행 시간이 길어질 수 밖에 없다.
비교하는 칼럼의 타입이 달라서 인덱스를 활용하지 못하는 문제를 해결하려면 두 칼럼의 타입을 맞춰서 비교해야 한다.

select u.userId, u.name, p.*
from user u, push p
where u.userId = 145
  and cast(u.userId as char character set utf8mb4) collate 'utf8mb4_unicode_ci` = p.receiverId
  and p.receiverType = 'MEMBER'
order by p.id desc
limit 100;

실패와 트랜잭션을 고려하기

일부 기능에서 오류가 발생하면 전체 트랜잭션을 롤백한다.
하지만 경우에 따라 일부 기능에서 오류가 나도 트랜잭션을 커밋해야 할 상황도 존재한다.

// Bad 
@Transactional // 선언적 트랜잭션(AOP로 비즈니스 로직과 트랜잭션을 분리해 처리한다.)
public void join(JoinRequest join) {
    

    // ...
    memberDao.insert(member);
    mailClient.sendMail(...);
}

위의 코드에서 만약 메일 발송에 실패하게 된다면 회원가입 부분에서 회원 데이터 역시 같이 롤백이 된다.
메일 발송에 실패하더라도 회원 가입은 정상 처리되길 원한다면, 메일 발송 오류는 별도로 처리해서 무시해야 한다.

// Good
@Transactional // 선언적 트랜잭션(AOP로 비즈니스 로직과 트랜잭션을 분리해 처리한다.)
public void join(JoinRequest join) {
    

    // ...
    memberDao.insert(member);
    try {
        mailClient.sendMail(...);
    } catch (Exception ex) {
        // 메일 발송 오류 무시
        // 로그로 기록해 이슈에 대응할 수 있도록 모니터링
    }
}