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

조회 트래픽을 고려하여 인덱스 설계할 것

1. 테이블의 모든 데이터를 순차적으로 읽는 것을 풀스캔이라고 한다.
2. 보통 데이터가 많을 때에 풀스캔이 발생하면 조회가 느려지므로 인덱싱을 잘 해둬야한다.
3. 일반적인 시스템의 경우에는 조회 기능의 실행 비율이 높다.
4. (그러므로) 조회 패턴을 기준으로 인덱스를 설계하는 것이 중요하다.
5. (예를 들면) 게시판의 경우 카테고리별로 게시글을 조회하는 패턴이 존재하므로 category 컬럼에 인덱스를 걸면 성능 개선이 일어날 수 있다.
6. (내가 작성한 글 보기 기능이 있는 경우에도) 풀스캔 방지를 위해 writerId 컬럼에 인덱스가 필요할 것이다.

FULLTEXT 인덱스

• 게시글 제목 검색 등의 기능을 위한 쿼리는 다음과 같다.

  SELECT id, category, writerId, title, content FROM article
  WHERE title like '%검색어%' order by id DESC limit 10

• 허나 이러한 like 조건은 풀스캔을 유발한다.
• (이를 사용하지 않기 위해) 엘라스틱 서치 와 같은 검색엔진을 사용하면 DB 를 사용하지 않고 검색 기능을 구현할 수 있다.
• (만약 이와 같은 도구를 사용하는 것이 힘들다면) DB 가 제공하는 FULLTEXT 검색 기능을 활용하자.
• FULLTEXT 인덱스를 고려하면 풀스캔 없이 문자열 검색 쿼리를 실행할 수 있다

단일 인덱스와 복합 인덱스 결정은 데이터 기반으로

1. 인덱스를 단일로 할지, 복합으로 할지는 해당 테이블에 얼마나 많은 데이터가 쌓이는지를 기준으로 정해야한다.
2. 하루에 50만건의 데이터가 쌓이는 경우에는 (요즘 기준으로) 많은 데이터는 아니지만 인덱스가 없는 경우 문제가 발생할 수 있는 규모다.
3. 만약 사용자 활동 로그를 저장하는 log 테이블이 있고 userId 와 activitiDate 를 기준으로 조회하는 기능이 있다고 하면 둘다 인덱싱 할지 userId 만 인덱싱 할지 고민될 수 있다.
4. 이럴때에는 사용자당 가질 수 있는 데이터가 얼마나 될지 가늠해보면 어떤 인덱스를 사용해야 할지 판단하는 데 도움이 된다.
5. 개별 사용자가 일주일에 하루정도 방문하고 평균 활동 데이터가 5건이면 1년 활동해야 260건이다.
6. 하지만 매일 방문하고 30번 이상의 활동을 하는 회원은 1년이면 1만 건이 넘는 활동 로그 데이터를 생성한다.
7. 이럴때에는 userID 와 activityDate 칼럼을 복합인덱스로 생성해야 조회 성능 문제가 발생하지 않는다.

선택도를 고려한 인덱싱을 하라. (항상은 아니고)

1. 선택도란 인덱스에서 특정 칼럼의 고유한 값의 비율을 뜻한다.
2. 고유한 값이 적어지면 그만큼 조회할때에 스캔할 내용이 많아짐으로 인덱스 효율이 낮아진다.
3. (하지만) 항상 선택도가 높아야하는 것은 아니다.
4. 만약 실제 사용하는 기능의 쿼리가 하나의 컬럼을 항상 조회한다면 선택도에 상관없이 인덱싱하는게 좋다.

커버링 인덱스를 활용해라

커버링 인덱스는 특정 쿼리를 실행하는데 필요한 모든 쿼리를 포함하는 인덱스를 뜻한다.

실제 데이터를 읽어오는 과정이 생략되므로 쿼리 실행 시간이 빨라진다.

과다 사용은 금물 아래글을 보라.

인덱스는 필요한 만큼만 만들 것.

1. 효과가 적은 인덱스를 만들면 오히려 데이터 추가, 변경, 삭제 시 인덱스 관리에 따른 비용이 발생하기에 안좋을 수 있다.
2. (또한) 인데스가 많아질수록 메모리와 디스크 사용량도 함께 증가한다.
3. (그러므로) 새로 추가할 쿼리가 기존에 존재하는 인덱스를 사용하지 않을 때에는 요구사항을 일부 변경할 수 있는지 검토해보자

성능 개선 방법

1. 미리 집계하기

1. 조회수 같은 기능이 필요하다면 sum 이나 count 같은 집계 쿼리를 조회시점에 실행하면 문제가 있을 수 있다.
2. 이러한 집계 데이터는 미리 계산해서 별도 컬럼에 저장하자.

2. 페이지 기준 목록 조회 대신 ID 기준 목록 조회 사용하기

1. 만약 id 가 AI라면 offset 기준으로 조회하는 것이 아닌 ID 기준으로 조회하는 것이 좋다.

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

2. 만약 위와 같은 쿼리가 있다면 DB 는 99,991 부터 10개 세면 좋겠지만 어떤 값이 99,990인지 모르므로 데이터를 세는 시간만큼 실행 시간이 증가한다.

3. 그러므로 특정 ID 를 기준으로 조회하면 좋다.

꿀팁

만약 프론트 개발자가 다음에 읽어올 데이터가 존재하는지 알려주는 속성을 응답 결과에 포함시켜 달라고 요청한다면 1개만 더 읽어 판단하면 된다.

예를 들어 10개를 조회하는 페이징이 있으면 11개를 조회하고 만약 조회한 데이터가 11개이면 다음에 읽을 데이터가 존재하므로 추가 데이터 존재 여부를 true 로 응답하면 된다.

JPA 의 pagenation 도 그렇게 동작하나? NO

🧩 책에서 제안한 방식

• 1페이지에 10개씩 조회
• 실제 쿼리: limit 11 offset 0
• 결과가 11개면 → 다음 페이지 존재 (hasNext = true)
• 결과가 10개 미만이면 → 마지막 페이지

➡ 장점: count 쿼리를 안 쳐서 빠름

➡ 단점: 전체 개수를 알 수 없음(total count 불명)

---

🧩 JPA의 기본 방식

• 1페이지에 10개씩 조회
• 실제 쿼리: limit 10 offset 0
• 별도로 select count(*) 실행
• count 결과와 현재 offset으로 다음 페이지 존재 여부 계산

➡ 장점: 전체 페이지 수(total count) 를 알 수 있음

➡ 단점: count 쿼리 비용이 큼 (특히 큰 테이블에서)

---

⚙️ 3. 정리

구분	방식	장점	단점
JPA 기본 Page	limit + count(*)	전체 개수, 페이지 수 알 수 있음	count 쿼리 부담
limit+1 방식 (프론트 제안)	limit + 1	빠름, 단순함	전체 개수 불명

---

💡 4. 실무 팁

성능 이슈로 count(*) 쿼리를 피하고 싶다면

Slice<T> 를 사용하는 방법이 있습니다.

Slice<Post> posts = postRepository.findAllByOrderByCreatedAtDesc(PageRequest.of(0, 10));

➡ Slice 는 count 쿼리를 날리지 않고,

내부적으로 limit+1 방식으로 다음 페이지 존재 여부(hasNext())를 판단합니다.

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

1. 뉴스를 보면 월별, 일별로 데이터를 분류해서 보여준다.
2. 즉, 기사 조회 범위를 일자 기준으로 제한하는 것이다.
3. 조회 범위를 제한하는 또 다른 방법은 최신 데이터만 조회하는 것이다.
4. 특히 공지사항의 경우에는 며칠 또는 몇 달 이내의 공지는 읽을 수 있지만 3년 전 공지사항은 (거의) 아무도 읽지 않는다. (구글의 경우 보안활동 데이터는 최근 28일 데이터만 보여준다.)

전체 개수 세지 않기

1. 목록을 표시하거나 조건에 해당하는 데이터 개수를 구하기 위해서는 count 함수를 사용해야한다.
2. 데이터가 적을때에는 count 함수를 사용해도 된다.
3. (하지만) 데이터가 많을때에는 count 함수를 실행할 경우 문제가 된다.
4. 커버링 인덱스를 사용하더라도 (조회된) 전체 인덱스를 스캔해야하고 만약 인덱싱이 되어있지 않은 경우 실제 데이터를 전부 읽어야한다.

APM 프로그램 항상 쉽게 볼 수 있는 곳에 띄워두기

1. 서비스를 진행할 경우 서비스가 정상일 때 으답시간 분포가 어떤 형태를 띠는지 감을 잡을 수 있다.

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

1. 과거 데이터를 삭제하면 데이터 개수를 일정하게 유지할 수 있어 성능 또한 일정 수준으로 유지된다.
2. 하지만 과거 데이터를 삭제할 수 있는 케이스는 많지는 않다.
   1. 로그인 시도 내역
      • 로그인 시도 내역은 장기간 보관할 필요 없기에 최근 180일 치 이상 된 데이터들만 분석에 사용한다면 이후 데이터들은 삭제하여 성능을 유지시킨다.
      • 만약 더 많은 일자가 내부 관리 시스템에서 필요할 경우 서비스 DB 와 분석 DB 를 분리하여 180일 이전 데이터는 별도 DB 로 분리 보관하면 된다.

단편화, 최적화

• DELETE 쿼리를 사용해도 실제 사용하는 디스크 양은 같다.
• 하지만 데이터가 반복적으로 추가되고 변경되고 삭제되는 과정에서 데이터가 흩어져 저장되ㅗㄱ 빈 공간이 생기는 단편화 현상이 발생할 수 있다.
• 이를 방지하기 위해 최적화 작업을 한다.
• 질문답변.

  질문 1.

  서적에서 추가 데이터 페이징 처리를 구현할때에

  만약 프론트 개발자가 다음에 읽어올 데이터가 존재하는지 알려주는 속성을 응답 결과에 포함시켜 달라고 요청한다면 1개만 더 읽어 판단하면 된다.
  
  예를 들어 10개를 조회하는 페이징이 있으면 11개를 조회하고 만약 조회한 데이터가 11개이면 다음에 읽을 데이터가 존재하므로 추가 데이터 존재 여부를 true 로 응답하면 된다.
  

  라고 했는데 JPA 의 pagenatnion 또한 count 쿼리를 활용하지 않고 동일하게 동작할까요?

  질문 2

  테이블 단편화를 해결하기 위해 OPTIMIZE TABLE 명령어(서적에서 언급한 최적화)를 사용할 수 있다고 알고 있습니다.
  하지만 운영 중인 서비스의 테이블에 이 명령어를 바로 실행하기 어려운 이유는 무엇이며, 어떤 대안을 고려할 수 있을까요? 힌트 : 서적에서 DML 을 운영중인 테이블에 사용하면 안된다고 한 이유와 같다.
  

DB 장비 확장하기

• 수직 확장의 경우 스케일 업을 통해 DB 장비의 성능을 빠르게 보충할 수 있는 방법도 있다. 클라우드를 사용하면 빠른 시간안에 성능을 높일 수 있다.
  
• 수평 확장의 경우
  1. DB 를 스케일 아웃하는 경우에는 DB 가 처리할 수 있는 트래픽을 늘릴 수 있다.
  2. 조회 트래픽의 비중이 높은 서비스의 경우 Primary-Replica 구조를 사용해 처리량을 증가시킬 수 있다.

별도의 캐시 서버 구성하기

1. 트래픽이 급격히 증가할 경우 DB 만으로 트래픽을 처리하기 어려움으로 캐시 서버를 기본적으로 사용한다.
2. 다만 캐시를 도입하면 코드를 수정해야할 수 있다. 하지만 코드수정에 드는 비용이 더 작다

주의사항

쿼리 타임아웃

1. 응답 시간은 처리량에 큰 영향을 준다.
2. 응답 시간이 길어지면 처리량은 반비례해 감소한다.
3. 하지만 응답지연으로 인해 사용자는 재시도 하기에 서버 부하를 더욱 가중시킨다.
4. 그러므로 쿼리 실행 시간을 제한하는 것이 합리적이다. (timeout)
5. Time out 시간은 서비스의 기능의 특성에 따라 다르게 설정해야 한다. (결제 처리 등의 경우에는 특히)

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

1. 주 DB 는 상태 변경을 복제 DB 는 상태 조회 기능을 담당한다.
2. 하지만 그렇다고 해서 모든 SELECT 쿼리를 복제 DB 에서 실행하면 안된다.
3. 주 DB 와 복제 DB의 데이터가 일치하지 않을 수 있기 때문이다. (복제 지연으로 인한 실시간 일관성이 깨질 수 있기 때문에)
4. 트랜잭션 문제가 일어날 수도 있다.
5. 그러므로 변경 대상 데이터를 조회해야한다면 복제 DB가 아닌 주 DB 에서 조회하자.

배치 쿼리 실행 시간 증가

1. 배치 프로그램은 데이터를 일괄로 조회, 집계, 생성하는 작업을 수행한다.
2. 한번에 처리하는 데이터가 많아질수록 배치 쿼리의 실행 시간도 함께 증가한다.
3. 이 문제를 예방하려면 배치 쿼리의 실행 시간을 지속적으로 모니터링해야 한다. 이를 통해 병목 지점을 찾아 해결할 수 있다.
4. DB 사양을 증설하여 처리 성능을 높일 수 있다.
5. 커버링 인덱스를 활용하여 쿼리 성능을 개선할 수 있다.
6. 데이터를 일정 크기로 나누어 처리하는 방법도 효과적이다. 쿼리를 시간대별로 분할 실행하면 결과를 안정적으로 얻을 수 있다.

타입이 다른 컬럼 조인 주의

1. 타입이 다른 경우 인덱스를 활용할 수 없다.
2. 타입 변환이 이루어지기 때문이다.
3. 그러므로 비교 대상 칼럼의 타입을 맞추면 쿼리 실행 중 발생하는 불필요한 타입변환을 줄일 수 있고 인덱스도 활용할 수 있다.

테이블 변경은 신중하게

1. MySQL은 타입을 테이블을 변경할 때 새 테이블을 생성하고 원본 데이터를 복사한 뒤 복사가 완료되면 새 테이블로 대체한다.
2. 이 과정에서는 DML 을 허용되지 않는다.

DB 최대 연결 개수

• 다음과 같은 상황을 상정해보자
  • api 서버는 3대
  • 트래픽이 증가하고 있고 수평확장이 필요하다.
  • DB 서버의 CPU 사용률은 20% 수준으로 여유가 있다.
• 트래픽 증가를 감당하기 위해 API 서버를 추가할 수 있다.
• 그런데 새로 추가한 API 서버에서 DB 커넥션 생성에 실패한다면? → DB 의 최대 연결개수를 확인해보자. 하지만 DB 서버의 CPU 사용률이 70% 이상으로 높다면 연결 개수를 늘리면 안된다.