[개발] 성능을 좌우하는 DB 설계와 쿼리

 

 

 

데이터베이스는 최종 종착지

 

대부분 시스템은 사용자의 요청을 받고 데이터를 응답하게 되는데 데이터베이스가 끝 지점이라 생각한다.

그만큼 데이터베이스가 중요한 역할을 하게되는데  대규모 트래픽을 처리하는 서비스에서 성능 병목은

대부분 데이터베이스에서 시작된다.

 

서버나 네트워크 튜닝도 중요하지만,

결국 DB 쿼리가 느리면 전체 시스템이 병목을 겪게 되는데 이걸 해결할 수 있는 방법이 뭐가 있을까?

---

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

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

풀스캔이 발생하지 않도록 하려면 조회 패턴을 기준으로 인덱스를 설계해야된다.

풀스캔(full scan) : 테이블의 모든 데이터를 순차적으로 읽는 것을 말한다. 인덱스(index) :  이터베이스에서 검색 성능을 높이기 위한 자료구조이다.

 

우리는 생각보다 인덱스를 정말 많이 이용하고 있는데 아래와 같은 상황이 있다.

내 블로그 ㅎ

이렇게 검색하게 되면 순서대로가 아닌 포함된 문자열을 검색할 수 있게 되는데 이것 또한 인덱스에 포함되어있다.

이런식으로 검색하게 되면 내가 생각하기에는 쿼리문이 아래와 같을텐데

SELECT
	title
    , context
    , userId
    , regDate
FROM board 
WHERE title LIKE '%virtualbox 설치%'; 
// 이렇게 중간에 포함된 단어를 검색하기 위한 like 조건은 풀스캔을 유발

 

like 조건을 걸어서 풀스캔이 발생하면

구글같이 데이터가 많은 곳은 정말 병목현상이 심하게 발생될텐데 왜 금방 검색이 되는걸까?

 

전문 검색 인덱스

Oracle Text, MySQL- FULLTEXT 인덱스를 사용하면 풀 스캔없이도 문자열 검색 쿼리를 실행 할 수 있다.

MySQL- FULLTEXT  - 중간에 단어나 문장으로도 인덱스를 생성해주기 때문에 - 긴문장 전체를 대상으로 인덱싱을 하며, InnoDB 와 MYISAM table 만 지원한다. - char, varchar, text ㅌ입 문자만 인덱싱 가능하다.

 

이렇듯 우리 생활에는 인덱스가 이미 녹여져 있는 상태이다.

단일 인덱스 vs 복합 인덱스

단일 인덱스 :  하나의 컬럼에만 인덱스를 생성하는 방식

복합 인덱스  : 두 개 이상의 컬럼을 묶어서 하나의 인덱스로 만드는 방식

 

보통 위의 인덱스에 대한 선택 전략을 세울 때 사용자당 가질 수 있는 데이터가 얼마나 될지 가늠해보면 판단이 될 것이다.

(근데 보통은 단일 인덱스 사용하는 경우는 거의 없고, 복합 인덱스를 주로 사용하게 된다)

 

선택도가 높은 컬럼을 먼저

 

선택도(Selectivity) : 전체 데이터 중 특정 값의 고유 비율

(선택도가 높으면 해당 칼럼에 고유한 값이 많고, 선택도가 낮으면 고유한 값이 적다는 의미)

 

인덱스를 생성할 때는 선택도가 높은 칼럼을 고려야 되며 선택도가 높을수록 인덱스를 이용한 조회 효율이 높아지기 때문!

(복합 인덱스 설계 시 선택도가 높은 컬럼을 앞에 두는 것이 성능상 유리)

 

🤚 물론, 선택도가 낮아도 인덱스 칼럼으로 적합한 상황도 존재! 작업 큐를 구현한 테이블에 해당되며 status 컬럼이 존재하여 해당 데이터에 대해 status 기준으로 조회하는 경우  선택도에 상관없이 인덱스로 추가하는 게 맞다.

 

커버링 인덱스 활용

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

 

인덱스는 ‘필요한 만큼’만

- 효과가 적은 인덱스를 추가하면 오히려 성능이 나빠질 수 있다.

- 인덱스는 조회 속도를 빠르게 해주지만 데이터 추가, 변경, 삭제 시에는 인덱스 관리에 따른 비용이 추가

- 인덱스 자체도 데이터이기 때문에 많아질수록 메모리와 디스크 사용량도 함께 증가

 

새로 추가할 쿼리가 기존에 존재하는 인덱스를 사용하지 않을 때는 요구사항을 일부 변경 할 수 있는 지 검토 해보자!

([AS-IS]예약한 예약자 이름으로 조회 -> [TO-BE]  특정 일자에 예약한 예약자 이름으로 조회  )

 

조회 성능을 높이는 실전 전략

미리 집계하기

- 자주 사용되는 집계값(예: 유저 수, 게시물 수)은 실시간 쿼리 대신 사전 계산한 값을 저장하는 방식으로 성능 높일 수 있다.

ID 기준 목록 조회

- 지정한 오프셋으로 이동하기 위해 데이터를 세는 시간을 줄이는 방법은 특정 ID를 기준으로 조회

 

모바일 화면은 페이지 단위로 이동하는 방식보다는 스크롤을 통해 다음 데이터를 읽어오는 방식이 많음.

이런 조회 방식에서는 ID를 기준으로 조회하는 방식이 좋음

시간 기준 조회 제한

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

- 기사 조회 범위를 일자 기준으로 제한하듯이 시간 기준으로 제한

- 아님, 최신 데이터만 조회하여 단일 쿼리로 단순화

최신 데이터 위주 조회 시 DB 성능 향상 데이터 조회가 발생 시 메모리에 캐시하여 다음에 동일한 요청이 들어올 시  더 빠르게 응답할 수 있음.

 

COUNT(*) 피하기

- 데이터가 많을 수록 COUNT 실행 시간이 높아지는데, 모든 데이터를 탐색해야되기 때문이다.

- 캐싱하거나 비동기 집계하는 방식이나 전체 개수를 화면에 표시하지 않는 방향으로 협의할 필요가 있다.

 

오래된 데이터는 분리 보관

- 데이터 개수가 늘어나면 늘어날수록 쿼리 실행 시간은 증가하기 때문에 이력 데이터는 주기적으로 삭제하는 게 좋다.

- 보통 실무에서는 6개월 ~ 1년 정도 냅두고 CRON 으로 없애는 작업을 진행한다.

 

DELETE 쿼리를 이용해 테이블에서 데이터를 삭제하면, 실제 사용하는 디스크용량은 줄어들지 않고 향후 재사용한다. 하지만 데이터가 반복적으로 crud 가 된다면 빈공간이 생기는 단편화 현상이 발생할 수 있다. 최적화 작업으로 데이터를 재배피해 단편화를 줄이고, 물리적인 디스크 사용량까지 줄여주는 효과가 있다.

 

이렇게 delete 표시만 남겨두고 향후 재사용

 

DB 장비 확장과 캐시 도입

- 읽기 전용 트래픽은 Replica DB로 분산, 보통 master-slave 구조를 갖는다.

- Redis, Memcached 같은 캐시 서버를 활용해 반복 조회 부하를 줄인다.

 

쿼리 작성 시 꼭 알아야 할 주의사항

 

쿼리 타임아웃을 고려하자

응답 지연으로 인한 재시도가 발생 될 경우 서버 부하가 폭증하게 된다. 쿼리 타임아웃을 걸어놓으면 요청을 정상적으로 종료할 수 있기 때문에 동시 요청 수의 폭증을 막을 수 있다.

상태 변경은 Master에서만

Replication 환경에서 상태 변경 요청을 Replica에 보내면 정합성 문제가 발생할 수 있기 때문에 유의해야된다.

 

배치 쿼리 실행 시간 증가

배치 프로그램은 데이터를 일괄로 조회 및 집계 생성하는 작업을 수행한다.

한 번에 조회하고 집계하는 데이터가 많을질수록 일괄 처리용 쿼리의 실행 시간도 함께 증가한다.

[예방법]  - 쿼리 실행시간 확인 [해결책] - DB 장비 사양을 높임 --> 현실적으로 불가능함 - 커버링 인덱스 활용 - 데이터를 일정 크기로 나눠서 처리 -> 보통 bulk 형태로 배치 처리하는 경우가 많다.

 

타입이 다른 컬럼 간 조인 주의

INT ↔ VARCHAR 조인처럼 타입이 다르면 인덱스가 무효화되고, 풀스캔이 발생

테이블 변경은 신중하게

대용량 테이블의 ALTER TABLE은 장시간 락을 발생할 수 있기 떄문에 Online DDL or 백업-복사-스위칭 방식을 고려하자

DB 최대 연결 수

커넥션 풀을 지나치게 크게 잡으면 DB가 과부하될 수 있다는 점을 생각하자.

실패와 트랜잭션 고려

DB 작업 중간 실패 시 롤백 전략을 고려해야 한다.