일반적인 시스템에서는 조회 기능의 실행 비율이 높다. 게시판을 생각해보면, 다수의 사용자는 글을 읽기만 하고 글을 작성하거나 삭제하는 사용자는 소수에 불과하다. 물론 같은 게시판이라도 인기 커뮤니티 게시판과 사내 공지용 게시판을 생각해보면 트래픽 규모나 성격이 다를 수 있다.
DB 테이블을 설계할 때는 트래픽 규모를 고려해야 한다.
데이터양이 적고 동시 접속자가 많지 않은 시스템이라면, 높은 확률로 풀스캔으로도 충분히 빠른 응답 시간을 낼 수 있을 것이다. 반면에 데이터양이 많고 동시 접속자가 많은 시스템이라면, 인덱스를 적절히 설계하여 조회 성능을 높여야 한다. 그렇지 않는다면 높은 확률로 풀스캔으로 인한 DB CPU 부하가 발생하고, 이는 곧 서비스 응답 시간 저하 및 장애로 이어질 수 있다.
조회 트래픽이 많다고 해서 무조건 모든 컬럼에 인덱스를 생성하는 것은 바람직하지 않다. 인덱스는 조회 성능을 높여주지만, 반대로 데이터 변경 성능을 저하시킨다. 따라서 자주 조회되는 컬럼에만 인덱스를 생성하는 것이 좋다.
CREATE TABLE articles (
id BIGINT PRIMARY KEY,
title VARCHAR(255),
content TEXT,
category_id BIGINT,
author_id BIGINT,
created_at TIMESTAMP
);위와 같은 articles 테이블이 있다고 하자. 만약 특정 카테고리에 속한 글 목록을 자주 조회한다면, category_id 컬럼에 인덱스를 생성하는 것이 좋을 것이다.
LIKE를 사용하여 특정 단어가 포함된 글을 조회하는 경우에는 일반적인 인덱스를 만들더라도 사용하지 못할 수 있다. 앞에 와일드카드(%)가 붙어있는 경우가 그렇다.
%keyword%처럼 검색하는 경우에는 풀스캔을 수행할 수밖에 없다.
사용자의 모든 활동 내역을 보관하기 위한 테이블 activity_logs가 있다고 하자.
CREATE TABLE activity_logs (
id BIGINT PRIMARY KEY,
user_id BIGINT,
activity_type VARCHAR(50),
activity_date DATE,
activity_date_time TIMESTAMP,
memo VARCHAR(200)
);하루 약 50만 건의 활동 로그가 기록된다. (한달이면 약 1,500만 건)
- 일평균 방문 사용자는 10만 명
- 평균 5번의 활동
요즘 기준으로 대규모 데이터는 아니지만 그렇다고 작은 규모도 아니다. 적절한 인덱스가 없으면 조회 성능에 문제가 발생할 수 있다.
원활한 CS 대응을 위해 특정 사용자의 일자별 활동 내역을 조회하는 기능을 만들어보자.
SELECT *
FROM activity_logs
WHERE user_id = 123 AND activity_date = '2025-12-25'
ORDER BY activity_date_time DESC;여기서 성능 문제를 방지하려면 userId를 포함한 인덱스가 필요하다. 고민해야 하는 부분은 activity_date도 인덱스에 포함시켜야 하는지 여부다.
- 단일 인덱스: (user_id)
- 복합 인덱스: (user_id, activity_date)
사용자당 가질 수 있는 데이터가 얼마나 될지 가늠해보면 어떤 인덱스를 사용해야 할지 판단하기 수월하다.
1년 활동 시 260건의 로그가 기록된다. (5년이면 약 1,500건)
- 1주일에 하루 방문
- 하루 5번의 활동
이정도면 단일 인덱스(user_id)로도 충분하다. 몇천 건이 안 되는 데이터를 비교하는 시간은 매우 짧은 편이기 때문이다. 반면에 사용자들의 활동성이 좋다면 복합 인덱스(user_id, activity_date)를 사용하는 것이 나을 것이다.
activity_logs 테이블을 활용하여 통계를 낼 수도 있을 것이다.
SELECT activity_date, activity_type, COUNT(activity_type) FROM activity_logs
WHERE activity_date = '2025-12-25'
GROUP BY activity_type;전체 데이터를 풀스캔하지 않으려면 activity_date 컬럼을 포함한 인덱스가 필요하다. 여기서도 고민할 부분은 activity_type도 인덱스에 포함시켜야 하는지 여부다.
- 단일 인덱스: (activity_date)
- 복합 인덱스: (activity_date, activity_type)
쿼리 실행 빈도와 실행 시간을 고려하여 적절한 인덱스를 선택해야 한다. activity_date만 포함된 인덱스라면 특정 일자에 해당하는 모든 데이터를 읽어와야 한다.
그런데 이 쿼리를 하루에 한번 실행하고 별도 테이블이나 캐시에 결과를 보관한다면, 큰 문제가 되지 않을 수 있다.
급격하게 서비스가 성장하면서 하루에 쌓이는 데이터가 수백만 건이 된다면, 복합 인덱스(activity_date, activity_type)를 사용하는 것이 나을 것이다. 특히 이 쿼리에서는 커버링 인덱스를 활용할 수 있기 때문에 더욱 효과적이다.
인덱스를 생성할 때는 선택도(selectivity)가 높은 컬럼을 고르자. 선택도는 인덱스에서 특정 컬럼의 고유한 값 비율이다.
- 선택도가 높다: 고유한 값이 많다 (예: 이메일 주소, 책 제목)
- 선택도가 낮다: 고유한 값이 적다 (예: 성별, 상태)
성별처럼 선택도가 낮은 컬럼에 인덱스를 생성하면, 인덱스를 사용하더라도 많은 데이터를 읽어와야 하기 때문에 성능 향상 효과가 크지 않을 수 있다.
선택도가 낮아도 인덱스 컬럼으로 적합한 상황이 있다.
대기 중인 주문 목록을 조회하는 기능이 있고, 주문 상태는 '대기', '처리 중', '완료', '취소' 4가지가 있다고 하자. 고유한 값이 4개밖에 없으므로 선택도는 낮다.
하지만 대기 중인 주문 목록을 자주 조회한다면, 상태 컬럼에 인덱스를 생성하는 것이 좋다.
SELECT *
FROM orders
WHERE status = '대기'
ORDER BY created_at DESC;만약 상태 컬럼에 인덱스가 없다면, orders 테이블을 풀스캔해야 한다. 선택도에 상관없이 인덱스를 사용하면 풀스캔을 피할 수 있으므로, 상태 컬럼에 인덱스를 생성하는 것이 유리할 것이다.
커버링 인덱스(Covering Index)는 쿼리에서 필요한 모든 컬럼을 포함하는 인덱스다.
실제 데이터가 저장된 테이블을 조회하지 않고, 인덱스 접근만으로 쿼리를 처리할 수 있기 때문에 성능이 크게 향상된다.
인덱스를 추가하면 조회 성능이 향상되지만, 반대로 데이터 변경 성능은 저하된다. 따라서 인덱스를 무작정 많이 생성하는 것은 바람직하지 않다.
인덱스를 생성할 때는 다음 사항을 고려하자.
- 조회 성능 향상 및 데이터 변경 성능 저하 균형
- 인덱스가 차지하는 저장 공간
- 순서가 중요한 복합 인덱스 설계
- 같은 컬럼에 중복된 인덱스가 없는지 여부
- 자주 사용되지 않는 인덱스는 없는지 여부
- 인덱스 통계 최신성 유지 여부
인덱스가 아니어도 조회 성능을 개선할 수 있는 방법이 있다.
- 미리 집계하기
- 오프셋 대신 커서 기반 페이징
- 조회 범위를 시간 기준으로 제한하기
- 전체 개수 세지 않기
- 오래된 데이터 삭제 및 분리 보관하기
- DB 장비 확장하기
- 별도 캐시 서버 구성하기
비정규화를 활용하여 자주 조회되는 통계 데이터를 미리 집계해두면, 조회 성능을 크게 향상시킬 수 있다.
surveys, answers, likes 테이블이 있다고 하자.
설문 조사 목록을 표시할 때 각 설문에 답변한 회원 수와 좋아요를 누른 회원 수를 함께 표시해야 한다면 다음과 같은 쿼리를 작성할 수 있다.
SELECT s.id, s.subject,
(SELECT COUNT(*) FROM answers a WHERE a.survey_id = s.id) AS answer_count,
(SELECT COUNT(*) FROM likes l WHERE l.survey_id = s.id) AS like_count
FROM surveys s
ORDER BY s.id DESC
LIMIT 30;논리적으로 문제가 없는 쿼리이지만, 성능 측면에서는 좋지 않다.
- 목록 조회 1번
- 답변자 수를 세는 서브쿼리 30번
- 좋아요 수를 세는 서브쿼리 30번
총 61번의 쿼리가 실행된다.
대신 surveys 테이블에 answer_count, like_count 컬럼을 추가하고, 답변이 등록되거나 좋아요가 눌릴 때마다 해당 컬럼을 업데이트하도록 변경할 수 있다.
-- 답변 추가
INSERT INTO answers values (...);
-- 답변 수 증가
UPDATE surveys
SET answer_count = answer_count + 1
WHERE id = ?;
-- 좋아요 추가
UPDATE surveys
SET like_count = like_count + 1
WHERE id = ?;이렇게 하면 설문 조사 목록을 조회할 때는 surveys 테이블만 조회하면 되므로, 쿼리 실행 횟수를 1번으로 줄일 수 있다. 답변이 삭제되거나 좋아요가 취소될 때는 answer_count, like_count 컬럼을 각각 1씩 감소시키면 된다.
우선 정규화는 관계형 데이터베이스의 설계에서 데이터 중복을 줄이고 데이터 무결성을 개선하기 위해 사용되는 방법론이다. 위 정의에 따르면 answer_count, like_count 컬럼 추가는 정규화 원칙에 어긋난다. 특히 무결성이 깨지는 상황이 발생할 수도 있다.
그러나 설문 조사 목록 조회 기능에서는 약간의 불일치를 감수하더라도 실시간 집계용 컬럼을 추가하는 것이 더 나은 선택일 수 있다. 답변 수나 좋아요 수가 실제보다 약간 적거나 많더라도 심각한 문제가 발생하지 않기 때문이다. (사용자가 느끼는 불편함도 크지 않다.)
나아가 정확한 값은 언제나 answers, likes 테이블을 조회하여 구할 수 있다.
UPDATE surveys
SET answer_count = answer_count + 1
WHERE id = ?;사용하는 DBMS가 이 쿼리를 원자적 연산으로 처리해주는지 확인해야 한다. 트랜잭션 격리 수준이 낮거나, DBMS가 해당 쿼리를 원자적 연산으로 처리하지 않는다면 동시성 문제가 발생할 수 있다.
오프셋 방식은 특정 페이지로 바로 이동할 수 있다는 장점이 있지만, 페이지가 뒤로 갈수록 성능이 저하된다는 단점이 있다. 대신 커서 기반 페이징(Cursor-based Paging)을 사용하면 성능 저하 문제를 완화할 수 있다.
다만 커서 기반 페이징은 특정 페이지로 바로 이동하는 기능을 제공하기 어렵고, 정렬 기준이 되는 컬럼에 인덱스가 반드시 필요하다는 단점이 있다.
대부분의 서비스에서는 최신 데이터에 대한 조회가 많다.
- 자동차 점검 결과 조회 (6개월 전 점검 결과는 크게 궁금하지 않을 것이다.)
- 사내 공지사항 조회 (1년 전 공지는 대부분 큰 의미가 없을 것이다.)
시간 기준으로 조회 범위를 제한하면, 대부분 시간 컬럼에만 인덱스를 생성해도 좋은 성능을 낼 수 있다. 나아가 여러 페이징 쿼리에 비해 쿼리가 단순해진다.
최신 데이터 위주로 조회하도록 기능을 구현하면 DB 성능 또한 향상될 수 있다. DB는 성능을 높이기 위해 메모리에 자주 사용하는 데이터를 캐싱하는데, 최신 데이터일수록 캐시 적중률이 높기 때문이다.
목록을 표시하는 기능에서 전체 개수를 함께 제공하는 경우가 많다. 이때 COUNT 키워드를 사용하여 전체 개수를 세는 쿼리를 실행하는데, 이 쿼리는 성능에 악영향을 미칠 수 있다.
데이터가 많아질수록 COUNT 쿼리의 실행 시간이 길어지기 때문이다. 따라서 전체 개수를 세지 않는 방향으로 기능을 설계하는 것이 좋다.
조회 속도가 조금씩 느려지고 있는데, 그 원인 중 하나가 COUNT 쿼리에 있다면 담당자에게 앞으로 성능 문제가 발생할 수 있음을 알리고, 전체 개수를 세지 않는 방향으로 협의할 필요가 있다.
평소 그래프 패턴을 익혀두면, 이상 징후가 발생했을 때 빠르게 감지할 수 있다.
데이터양이 많아질수록 쿼리 실행 시간은 증가한다. 반대로 데이터양의 변화가 없다면 쿼리 실행 시간은 일정하게 유지된다.
로그인 시도 내역을 보관하는 테이블이 있다고 가정하자.
- 로그인 시도 내역은 장기간 보관할 필요가 없다. (예: 6개월)
- 이상 징후를 탐지하기 위한 용도로만 사용된다.
- 즉 몇 년 전 로그인 시도 내역은 사용되지 않는다.
과거 데이터를 삭제하거나 별도 테이블로 분리 보관하면, 현재 데이터를 조회하는 쿼리의 성능을 일정하게 유지할 수 있다.
DB 부하로 인해 성능 문제가 발생하고 있는데, 성능 개선 방법을 찾지 못했거나 개선에 시간이 필요한 경우라면 일단 DB 장비를 수직으로 확장하는 방법을 고려하자.
클라우드를 사용하면 손쉽게 서버 자원을 확장할 수 있다. 그렇게 시간을 벌고 성능 문제의 근본 원인을 찾아 해결하는 것이 좋다.
조회 트랠픽 비중이 높은 서비스의 경우 수평 확장도 고려할 수 있다. (Primary-Replica 구성) 물론 복잡도도 증가하고, 고정 비용도 증가하므로 확실하게 이득이 있을 때 도입하는 것이 좋다.
서버 개발자나 인프라 엔지니어 입장에서도, DB를 확장하는 것보다 별도 캐시 서버를 구성하는 것이 상대적으로 부담이 적다.
동시 사용자가 증가할 때 응답시간이 길어지면 처리량은 감소할 것이다. 이때 사용자는 몇 초만 지나도 서비스가 느리다고 생각하며 재시도를 하게 된다. 재시도가 반복되면 DB 부하는 더욱 커지고, 결국 서비스에 장애가 발생할 수 있다.
이런 상황을 방지하기 위해 쿼리 타임아웃을 설정할 수 있다. 설정한 시간 내에 쿼리가 완료되지 않으면 에러를 응답하게 되면서 이전 요청이 여전히 처리 중이 아닌 상태가 된다. (동시 처리 수 폭증을 막을 수 있다.)
서비스 특성에 맞게 적절한 타임아웃 시간을 설정하는 것도 중요하다. 예를 들어, 결제 처리 도중 타임아웃으로 에러가 발생한다면 후속 처리와 데이터 정합성에 문제가 발생할 수 있다.
Primary-Replica 구조에서 상태 변경 기능은 Primary DB에서 조회하도록 하자. 복제 지연으로 인해 최신 상태가 반영되지 않은 데이터를 조회할 수 있기 때문이다.
회원 가입, 변경, 등록, 삭제와 같이 상태를 변경하는 기능에서 대상 데이터를 조회해야 한다면 Replica DB가 아닌 Primary DB에서 조회하도록 구현한다면 데이터 불일치로 인한 문제를 방지할 수 있다.
배치 작업에는 데이터를 그룹화하고 집계하는 쿼리가 자주 사용되고, 조건을 충족하는 데이터의 컬럼 값을 일괄로 변경하는 쿼리도 자주 사용된다.
데이터양이 많아질수록 이런 쿼리의 실행 시간이 길어지므로, 배치 작업이 제시간에 완료되지 않을 수 있다. 이런 문제를 예방하기 위해선 지속적으로 쿼리의 실행 시간을 추적해야 한다. 실행 시간이 길어지는 쿼리가 발견되면, 조치를 취해야 한다.
- 인덱스 추가 및 쿼리 튜닝
- 일정 단위로 나누어 처리
일정 단위로 나누어 처리하는 것이 효율적인 이유는 다음과 같다.
- 메모리 관점
- 정렬/집계 대상이 작아짐
- 임시 테이블이 메모리에 머물 확률 증가
- I/O 관점
- 버퍼 풀에 재사용 가능한 데이터가 남음
- 디스크 thrashing 감소
- 트랜잭션 관점
- 락 점유 시간 감소
- 롤백 범위 축소
- 장애 복구 용이
비교 대상 컬럼의 타입을 맞추면 쿼리 실행 중 발생하는 불필요한 타입 변환을 줄일 수 있고 실행 시간이 길어지는 문제도 방지할 수 있다.
문자열 타입을 비교할 때는 컬럼의 캐릭터셋이 같은지 확인하자. 서로 다른 캐릭터셋이라면 쿼리 실행 중에 불필요한 변환이 발생할 수 있다.
데이터가 많은 테이블에 새로운 컬럼을 추가하거나 기존 열거 타입 컬럼을 변경할 때는 매우 주의해야 한다. 무심코 컬럼을 변경했다가 서비스가 아주 오랫동안 중단되는 사태가 발생할 수 있다.
MySQL은 테이블을 변경할 때 새 테이블을 생성하고 원본 테이블의 데이터를 복사한 뒤, 복사가 완료되면 새 테이블로 대체한다. 이 과정에서 UPDATE, DELETE, INSERT 같은 DML 작업을 수행할 수 없다.
DML을 허용하면서 테이블을 변경하는 기능도 있지만 항상 가능한 것은 아니다. 그래서 대규모 테이블에 변경이 필요한 경우에는 점검 시간을 미리 공지하고 진행하는 경우가 많다.
서버를 수평 확장하거나 블루-그린 배포 같이 여러 대의 서버가 동시에 DB에 연결되는 환경에서는 DB 최대 연결 개수를 신경 써야 한다. DB 최대 연결 개수를 초과하면 새로운 연결 요청이 실패하기 때문이다.
설정으로 DB 최대 연결 개수를 늘릴 수 있지만, 무턱대고 늘리는 것은 바람직하지 않다. 만약 DB CPU 사용률이 높다면, 부하가 더 커질 수 있기 때문이다. 쿼리 개선이나 캐시 서버 구성을 통해 부하를 줄이고 DB 최대 연결 개수를 적절히 설정하자.
- 트랜잭션 없이 여러 데이터를 수정하지 말자.
- 외부 연동 기능이 실패하는 경우 트랜잭션을 어떻게 처리할지 고민하자.
@Transactional은 런타임 예외가 발생하는 경우 트랜잭션을 롤백한다.
조회 성능 개선은 기술 선택의 문제가 아니라 상황 판단의 문제라는 점이 인상 깊었다. 데이터 규모와 조회 빈도, 정합성 요구 수준에 따라 인덱스, 비정규화, 배치 전략의 선택이 달라진다는 점이 흥미로웠다.