[IT] 주니어 백엔드 개발자가 반드시 알아야 할 실무지식 - 3장: 성능을 좌우하는 DB 설계와 쿼리

 

DB 성능 개선

• 풀 스캔(full scan)
  • 테이블의 모든 데이터를 순차적으로 읽는 것
  • where 절을 쓸 때 조건에 대응하는 인덱스가 없으면 발생함
  • 데이터 개수가 많아지면 성능이 기하급수적으로 떨어짐
• 인덱스(Index)
  • 인덱스는 B-Tree 구조로, O(logN) 이기 때문에 데이터가 아무리 커져도 탐색 시간은 극적으로 줄어듦.
  • 예를 들어 100개 중 35라는 데이터를 찾는다면 1~50, 51~100 이렇게 반으로 나누고, 그 안에서 또 반씩 나누면서 검색
  • 다음 노드가 디스크의 몇 번째에 있는지 표 형태로 기록해두기 때문에 이 구조가 가능
• 조회 트래픽을 고려한 인덱스 설계
  • 게시판에서 카테고리별로 검색하는 기능
    • category라는 컬럼에 인덱스를 건다.
    • 사내 공지용 게시판 등 조회가 적은 게시판은 굳이 인덱스가 필요 없다. (ex: 매주 1건)
    • 그러나 1000만 건의 데이터가 있는 커뮤니티 사이트에서는 매번 풀스캔 시 성능 문제 발생.
  • 내가 작성한 글 목록 보기 기능
    • writerId 컬럼을 포함한 인덱스 생성
  • 문자열 검색
    • like 조건은 풀 스캔을 유발함
    • 엘라스틱서치를 사용하면 DB를 통하지 않고서도 검색 기능을 구현 가능.
      • 그러나 매번 혹은 이벤트 큐로 한번에 동기화시켜줘야 함
    • 검색 엔진을 사용하기 어려우면
      • 데이터 양이 수백만 건 이하면 DB에서 제공하는 Full-Text Search를 사용하면 성능 개선 가능
  • 복합 인덱스
    • 순서가 중요함. 선택도(카디널리티)가 높은 컬럼을 앞에 둠.
    • 특정 사용자의 일자별 활동 내역 검색
      • 활동을 하면 activityLogs에 1행씩 쌓임
      • 모든 회원들의 활동성이 좋다면 (userId, activityDate)로 복합 인덱스 걸기
      • 특정 일자의 활동 타입별 개수를 구해야 한다면 (activityDate, activityType) 으로 걸기
• 선택도를 고려한 인덱스 컬럼
  • 인덱스를 걸더라도 조건에 해당하는 데이터가 전체의 15%~25%를 넘어가면 DB 옵티마이저에 의해 풀 스캔으로 전환
  • gender에 인덱스가 걸려 있는데 where 조건이 gender = 'M' 이고 그게 80%면 선택도가 나쁜 경우에 해당.
• 커버링 인덱스
  • 특정 쿼리를 실행하는 데 필요한 컬럼을 모두 포함하는 인덱스
  • 실제 데이터에 접근하지 않기 때문에 쿼리 실행 시간이 빨라짐
  • select * from activityLog where date = '2024-07-31' and type = 'VISIT;
  • select date, type from activityLog where date = '2024-07-31' and type = 'VISIT;
    • date와 type이 모두 인덱스에 있기 때문에 조회가 빨라짐.
  • 너무 많은 컬럼을 넣은 복합 인덱스를 커버링을 위해 쓰거나 인덱스를 너무 많이 추가하면 CUD 성능이 매우 떨어지게 됨
    
    • CUD 시마다 존재하는 인덱스들을 추가하고 재정렬해야 하기 때문
• 인덱스는 필요한 만큼 만들기
  • 효과가 적은 인덱스를 추가하면 오히려 성능이 나빠질 수 있음
  • 인덱스는 조회 속도를 빠르게 해주지만 데이터 CUD 시에는 시간이 늘어나기 때문
  • 같은 컬럼에 대한 인덱스를 또 추가하면 관리 비용만 늘어남
    • (A, B, C)와 (A, B) 처럼 선두 컬럼부터 순서가 겹치면 두 번째 인덱스는 삭제 대상.
    • (A, B)와 (B, A)는 다르기 때문에 둘 다 필요. where A = 1은 (A, B)만 타기 때문.
• 인덱스 이외의 성능 개선 방법
  • 서브쿼리 대신 조인으로 N+1 방지
  • count나 sum 같은 집계 쿼리 대신 컬럼 추가(ex: 조회수)
  • 비정규화
  • 목록 검색 시 페이지 번호 기준 조회 대신 id 값 기준 조회 사용
    • offset 방식 대신 커서 방식을 쓰라는 것
    • limit 20 offset 200000; 은 앞의 200000개를 읽은 뒤 버리고 그 뒤의 20개만 가져오라는 뜻
    • 커서로 바꾸면 where id<5000 limit 20; 이 됨. 마지막으로 읽은 id 이후부터 가져오라는 뜻
    • id 인덱스를 쓰기 때문에 빠른 것.
  • 조회 범위를 시간 기준으로 제한하기
    • 캐시 효율이 높아짐
  • count(*) 로 전체 개수 조회 지양
  • 오래된 데이터 삭제 및 분리 보관
    • delete해도 디스크 용량이 줄지는 않음. 인덱스 성능과 RAM 효율 때문에 정리하는 것.
    • 오래된 데이터는 연도별 파티셔닝으로 분리. 기간별 조회 성능이 더 좋아짐.
    • 보통 로그성 데이터 같은 경우에는 파티셔닝을 해서 drop partition으로 날림. 그럼 디스크 용량 줄어듦
  • 별도로 캐시 서버 구성
    • 대규모 트래픽이 발생하는 서비스들은 캐시 서버를 기본적으로 사용.
  • 쿼리 타임아웃
    • 응답 지연으로 인한 재시도는 서버 부하를 가중시킴
    • 5초로 타임아웃을 걸면 바로 에러를 띄워버려서 해당 요청을 종료시켜 폭증을 막음
  • 상태 변경 기능은 복제 DB에서 조회하지 않기
    • 복제 DB(Replication)는 트래픽이 몰릴 경우 조회가 과부하되는 것을 막기 위해 만들며, 보통 조회 전용으로만 사횽됨
    • 그러나 모든 select 쿼리를 무조건 복제 DB에서 실행하면 안 됨.
      • 주 DB와 복제 DB는 순간적으로 데이터가 일치하지 않는 '불일치' 문제가 생기기 때문
  • 배치 쿼리의 실행 시간이 증가했다면?
    • 같은 쿼리인데 갑자기 성능이 떨어졌다는 것.
    • 가장 빠른 해결책은 DB 장비 사양을 높이는 것
    • 현실적으로 어렵다면 커버링 인덱스, 데이터 파티셔닝 이용
  • 타입이 다른 컬럼 조인
    • 테이블 2개 조인 시 where절에 u.userId = p.recieverId 라는 조건이 있는데 두 컬럼의 타입이 다를 경우, 불필요한 타입 변환이 일어남
  • DB 최대 연결 개수
    • 트래픽이 증가해 커넥션 생성에 실패하는 경우
    • API 서버가 4개고 각각 커넥션 풀 개수가 30개라면, DB의 커넥션 풀 개수도 120개로 최대 연결 개수를 늘리면 문제가 해결될 수 있다
    • 그러나 DB 서버의 CPU 사용률이 70% 이상으로 높다면 연결 개수를 높이면 안 된다
• DB 트랜잭션
  • 트랜잭션 없이 여러 데이터를 수정하는 경우가 많음
  • 시작과 종료 경계를 명확히 설정했는지 확인하기
  • ex) 환영메일 발송에 실패했다고 회원가입이 롤백되는 경우

 

---

실무에서 커버링 인덱스를 사용한 적이 있음.

이번 기회를 통해 더 정확히 알게 되었음