부하 테스트로 발견한 SQL 안티패턴과 쿼리 개선기

tls·2026년 2월 19일
sql

Tech

목록 보기
1/8
post-thumbnail

프로젝트의 서버에 부하 테스트를 진행했는데 테스트 시나리오에 포함하지 않은 API에 문제가 생겼다. 원인을 찾아보다 보니 내가 작성한 쿼리가 SQL 안티패턴에 해당한다는 것을 알게 됐다.

본 글에서는 SQL 안티패턴에 대해 학습한 내용을 간략하게 정리함과 동시에 내가 어떤 쿼리를 작성하였고 어떻게 쿼리를 수정했는지 작성하고자 한다.

SQL 안티패턴이란?

안티패턴(Anti-pattern)이란 흔하게 사용되지만 실제로는 문제를 유발하는 설계나 코드 패턴을 말한다. SQL에서도 마찬가지로 일단 동작은 하지만 성능이나 유지보수에 악영향을 주는 쿼리 작성 방식들이 존재한다.

SQL 안티패턴이 나쁜 이유는?

  1. 옵티마이저가 최적화하기 어렵다.
    SQL은 선언형 언어로, 실행 방법은 데이터베이스 쿼리 옵티마이저가 결정한다. 그런데 안티패턴은 옵티마이저가 쿼리를 최적화할 수 있는 선택지 자체를 줄이는 구조를 가진 경우가 많다.
    예를 들면 JOIN의 경우, 옵티마이저가 실행 순서와 인덱스 활용 여부를 결정할 수 있다. 하지만 안티패턴은 옵티마이저가 이러한 실행 순서나 인덱스 활용 여부와 같은 것들을 선택할 수 없도록 한다.

  2. 성능이 데이터 특성(분포)에 종속된다.
    안티패턴 쿼리는 소량 데이터에서는 문제가 없다가, 데이터가 특정 방식으로 쌓이는 순간 갑자기 느려질 수 있다. 개발 환경에서는 잘 동작하다 운영 환경에서 성능이 떨어지거나 동작하지 않을 수 있는 것이다.

  3. 유지보수를 어렵게 한다.
    개발자들이 빠르게 개발을 진행하기 위해 기존 쿼리를 복사하고 붙여넣어 사용할 수 있다. 만약 처음에 작성된 SQL 쿼리가 안티패턴이라면 해당 쿼리가 전체 시스템에 산재되고 궁극적으로는 조직의 데이터 거버넌스를 유지하기 힘들어질 수 있다.

대표적인 SQL 안티패턴

상관 서브쿼리 (Correlated Subquery)

= 바깥 쿼리 값에 의존하는 서브쿼리
상관 서브쿼리를 사용하면 외부 쿼리의 각 행을 처리할 때마다 서브쿼리를 반복 실행하게 된다. 즉, 외부 쿼리 결과가 N행이면 서브쿼리도 N번 실행된다.

-- 각 직원마다 해당 부서의 평균 급여를 서브쿼리로 계산
SELECT name, salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id  -- 외부 쿼리의 e를 참조
);

옵티마이저 입장에서는 서브쿼리가 외부 행에 종속되어 있기 때문에 미리 계산해두기가 어렵다. 대안으로는 JOIN이나 윈도우 함수(AVG() OVER (PARTITION BY ...))를 사용하면 된다.

후술할 문제 쿼리도 상관 서브쿼리에 해당한다.

조회 시 함수, 연산 등을 사용

조회 시 컬럼에 함수나 연산을 사용한다면 인덱스를 사용할 수 없게 된다.

-- 함수와 연산을 사용하는 조회 컬럼
SELECT name, price
FROM orders
WHERE UPPER(name) = 'SHIRT'
  AND price + 10000 > 40000;

인덱스는 쉽게 말하자면 키를 기준으로 데이터를 정렬하여 저장해둔 것이다. 하지만 인덱스의 키에 함수나 연산을 적용하면, 인덱스를 그대로 활용할 수 없다고 판단하여 최악의 경우 풀스캔으로 쿼리를 실행할 수도 있다.

해결 방법

두 가지가 있다.
첫 번째 방법은 파라미터를 미리 변형하여 쿼리에 입력하는 방법이다. 위 예시와 같은 경우는 애플리케이션에서 미리 name을 대문자로 변경한 뒤, 쿼리를 실행하면 된다.
두 번째 방법은 함수 인덱스나 표현식 인덱스를 생성하는 방법이다. 이미 해당 함수가 적용된 데이터를 기반으로 인덱스를 생성하므로, 조회 쿼리에서 함수를 사용할 때 해당 인덱스를 사용하게 된다. 다만, 함수 인덱스 생성 등을 지원하지 않는 데이터베이스가 있으므로 지원 여부 확인이 필요하다.

CASE WHEN을 중복하여 사용하는 경우

CASE WHEN은 SQL에서 조건 분기를 표현하는 가장 직관적인 쿼리 작성법이다. 그러나 동일한 조건 로직이 여러 쿼리에서 반복해서 나타난다면 안티패턴이 된다.

-- 주문 상태를 여러 쿼리에서 각각 해석
SELECT
    order_id,
    CASE status
        WHEN 1 THEN '결제대기'
        WHEN 2 THEN '결제완료'
        WHEN 3 THEN '배송중'
        WHEN 4 THEN '배송완료'
    END AS status_name
FROM orders;

CASE WHEN 로직이 여러 쿼리에 복사되는 순간, 데이터는 하나인데 해석은 여럿이 되는 구조가 되기 쉽다. 수정이 필요할 때 모든 복사본을 동일하게 고친다는 보장이 없기 때문이다.

차원 테이블 (Status Dimension)

이 방법을 해결하기 위해서는 차원 테이블을 구현하는 방법이 있다. 상태 코드와 의미를 별도의 테이블로 분리하여 중앙에서 관리하는 방법이다.

-- 차원 테이블
CREATE TABLE order_status (
    status_code INT PRIMARY KEY,
    status_name VARCHAR(20) NOT NULL
);

INSERT INTO order_status VALUES
    (1, '결제대기'), (2, '결제완료'),
    (3, '배송중'),  (4, '배송완료');

-- 조회 시 JOIN으로 처리한다
SELECT o.order_id, s.status_name
FROM orders o
JOIN order_status s ON s.status_code = o.status;

상태 이름을 변경하거나 새 상태를 추가해야 한다면 차원 테이블 하나만 수정하면 된다. 데이터 무결성과 일관성이 자연스럽게 유지되고, 차원 테이블은 보통 행 수가 적어 데이터베이스 버퍼 캐시에 상주하는 경우가 많으므로 JOIN 연산이 추가되더라도 성능 부담이 작다. (그래도 데이터베이스 상황에 따라 다를 수 있으므로 EXPLAIN으로 확인이 필요하다!)

하지만 애플리케이션에서는 보통 ENUM으로 상태값을 정의해두는 경우가 많다. 따라서 차원 테이블과 ENUM 동기화가 깨질 위험이 있어 주의가 필요하다.

공용 뷰 생성

복잡한 JOIN, 계산 로직은 뷰 내부에 캡슐화하여 사용하는 쪽에서는 단순한 쿼리만 작성할 수 있도록 추상화하는 기법이다.

CREATE VIEW order_summary AS
SELECT
    o.order_id,
    o.user_id,
    s.status_name,
    o.total_amount
FROM orders o
JOIN order_status s ON s.status_code = o.status;

-- 사용하는 쪽은 간단하게 사용 가능!
SELECT * FROM order_summary WHERE user_id = 123;

뷰를 사용하면 비즈니스 로직이 한 곳에 모이므로 수정 시 범위가 명확해질 것이다.

다만, 주의해야 할 점이 존재한다. MySQL을 기준으로 했을 때, 단순한 뷰는 옵티마이저가 뷰를 원본 테이블 쿼리로 merge하고 최적화를 진행한다. 내부에 집계나 서브쿼리가 포함된 뷰라면 옵티마이저는 뷰를 파생 테이블(derived table)로 임시 구체화한 뒤 쿼리를 실행한다. 이 경우 원본 테이블의 인덱스를 활용하지 못해 성능이 오히려 나빠질 수 있다.

SELECT *

컬럼을 명시하지 않고 전체를 조회하는 방법이다. 개발 편의상 자주 쓰이지만 여러 문제를 일으킨다.

SELECT * FROM users;

필요하지 않은 컬럼까지 모두 읽어 I/O와 네트워크 비용이 늘어나고, 커버링 인덱스 활용을 힘들게 한다. 또한 테이블 스키마 변경 시 애플리케이션에 예상치 못한 영향을 줄 수 있다.

묵시적 형변환 (Implicit Type Casting)

WHERE 조건에서 컬럼 타입과 비교 값의 타입이 다를 때, 데이터베이스는 자동으로 형변환을 수행한다. 아주 편리하지만 이 과정에서 인덱스를 사용할 수 없게 되는 경우가 있다.

-- number 컬럼이 VARCHAR인데 숫자로 비교
SELECT * FROM users WHERE number = 12345678;

-- 인덱스가 있어도 전체 테이블 스캔이 발생할 수 있음

데이터베이스 입장에서는 모든 행의 number 값을 숫자로 변환한 뒤 비교해야 하므로, 인덱스 탐색 대신 풀스캔을 하게 된다.

문제 발견 경위

직접 만든 서버가 얼마나 부하를 견디는지 궁금하여 프로젝트에 부하 테스트를 진행하였다.

부하 테스트 중 채팅 시나리오의 내용은 다음과 같다.

  • 동시 접속 세션 : 320명
  • 메시지 전송 : 1인당 10초 간격 (전체 초당 약 32건)
  • 단일 채팅방에 부하 집중

테스트 결과 자체는 목표 지표를 모두 통과했다.

WEBSOCKET
ws_connecting......: avg=1396.98ms  p(95)=2081.34ms
ws_msgs_sent.......: 7252  34.527425/s   ← 목표 32건/s 초과 달성
ws_sessions........: 320   ← 전원 세션 점유 성공

좋은데!!

그런데 테스트 종료 후, 채팅방 목록 API에서 타임아웃이 발생했다.

stress_test_chatapi_timeout

애플리케이션을 재시작해도 여전히 채팅방 목록 API는 타임아웃이 발생했고, 명령어 SHOW FULL PROCESSLIST를 통해 원인을 파악할 수 있었다.

| Id    | Command | Time | State     | Info                                              |
| 22090 | Query   | 1198 | executing | select c1_0.id, p1_0.name, cm1_0.message ...     |
| 22091 | Query   | 1167 | executing | select c1_0.id, p1_0.name, cm1_0.message ...     |
| 22093 | Query   | 1092 | executing | select c1_0.id, p1_0.name, cm1_0.message ...     |
(이하 동일한 쿼리 반복)

동일한 쿼리가 여러 커넥션에서 동시에 15~20분째 executing 상태였다.

원인은 쿼리 자체의 구조와 부하 테스트 후 동시 생성된 데이터가 많이 쌓여서였다. 320명이 동시에 메시지를 전송하면서 동일한 created_at 타임스탬프를 가진 메시지가 대량으로 쌓였다. 그 상태에서 채팅방 목록 API가 호출되자, 2단계 서브쿼리가 동일 타임스탬프를 가진 수천 건 전체를 스캔하였고 타임아웃이 발생했던 것이다.

원인 쿼리 분석

erd

채팅방 목록 조회 API는 단순히 채팅방 목록을 조회하지 않는다. 각 채팅방의 가장 최근 메시지를 함께 보여줘야 한다.

내가 작성한 쿼리는 다음과 같다.

SELECT c.id, p.name, cm.message, cm.created_at
FROM position_chat_chatroom c
LEFT JOIN position_chat_message cm
    ON cm.chatroom_id = c.id
    AND cm.id = (
        SELECT MAX(cm2.id)
        FROM position_chat_message cm2
        WHERE cm2.chatroom_id = c.id         -- 외부 행 참조
          AND cm2.created_at = (
              SELECT MAX(cm3.created_at)
              FROM position_chat_message cm3
              WHERE cm3.chatroom_id = c.id   -- 외부 행 참조
          )
    )
JOIN positions p ON p.id = c.position_id
ORDER BY cm.created_at DESC, cm.id DESC;

채팅방마다 MAX(created_at)을 구하고, 그 타임스탬프와 일치하는 행 중에서 다시 MAX(id)를 구하는 중첩 구조이다.

작성 당시에는 두 가지를 근거로 문제없다고 판단했다.

  • 채팅방 수가 포지션 수와 동일하여 8개로 고정이니, N번 반복이라도 N=8이면 괜찮다
  • AUTO_INCREMENT를 사용하므로 MAX(id)가 곧 가장 최신 메시지다

하지만... 첫 번째는 서브쿼리 반복 횟수의 문제가 아니라, 각 서브쿼리 내부의 스캔 범위가 데이터 분포에 종속된다는 것을 파악하지 못한 근거였다. 두 번째 근거는 현재는 문제가 없지만, 만약 추후 UUID처럼 생성 순서를 보장하지 않는 ID로 전환하면 쿼리의 정확성 자체가 깨질 수 있다.

쿼리 개선 과정

방향 설정

처음에는 데이터베이스 테이블 비정규화도 고민해보았다.

ChatRoom 테이블에 마지막 메시지 데이터를 저장할 때 FK 대신 메시지 내용 자체를 컬럼으로 저장하는 방법이다. 하지만 ERD 설계 때도 기각했듯, 조회는 단순해지지만 채팅이라는 도메인 특성상 쓰기 빈도가 높아 정규화 구조 대비 쓰기 비용이 더 커질 것으로 판단하여 제외하였다.

따라서 쿼리 개선을 위해 아래 두 가지 방법을 병행하였다.

복합 인덱스 적용과 쿼리 구조 변경

(chatroom_id, created_at, id) 순서로 인덱스를 구성하면, 채팅방 기준 필터링과 날짜/ID 기준 정렬을 인덱스만으로 처리할 수 있다.

그리고 중첩 상관 서브쿼리를 제거하고, 채팅방별로 ORDER BY created_at DESC, id DESC LIMIT 1 쿼리를 실행하는 방식으로 전환하였다.

상관 서브쿼리는 외부 행의 값을 참조하기 때문에 외부 행이 바뀔 때마다 서브쿼리를 다시 실행해야 한다. ORDER BY ... LIMIT 1은 중첩 서브쿼리처럼 반복하지 않는다.

단, 채팅방별로 쿼리를 분리하면 쿼리 사이에 다른 트랜잭션이 끼어들어 채팅방 목록의 정렬 기준이 되는 스냅샷이 달라질 수 있다. 이를 방지하기 위해 REPEATABLE_READ 격리 수준을 명시하여 같은 트랜잭션 내 모든 쿼리가 동일한 스냅샷을 기준으로 실행되도록 하였다.

적용

CREATE INDEX idx_cm_room_created_id
ON position_chat_message (chatroom_id, created_at, id);
@Transactional(readOnly = true, isolation = Isolation.REPEATABLE_READ)
public class ChatroomQueryService {

    public List<ChatroomResponse> getAllChatroom() {
        List<Chatroom> chatrooms = chatroomRepository.findAllWithPosition();
        List<ChatroomWithLatest> merged =
                chatrooms.stream()
                        .map(chatroom ->
                                new ChatroomWithLatest(
                                        chatroom, findLatestMessage(chatroom.getId())))
                        .toList();
        return merged.stream()
                .sorted(chatroomLatestComparator())
                .map(this::toResponse)
                .toList();
    }

    private ChatMessage findLatestMessage(Long chatroomId) {
        List<ChatMessage> latest =
                chatMessageRepository.findLatestByChatroomId(chatroomId, PageRequest.of(0, 1));
        return latest.isEmpty() ? null : latest.getFirst();
    }

    private Comparator<ChatroomWithLatest> chatroomLatestComparator() {
        return Comparator.comparing(
                ChatroomWithLatest::latest,
                Comparator.nullsLast(
                        Comparator.comparing(ChatMessage::getCreatedAt).reversed()
                                .thenComparing(Comparator.comparing(ChatMessage::getId).reversed()))
        );
    }

    private record ChatroomWithLatest(Chatroom chatroom, ChatMessage latest) {}
}

EXPLAIN ANALYZE 결과 비교

(메시지 300개 기준)

개선 전 중첩 상관 서브쿼리 (인덱스 없음)

    -> Select #2 (dependent) → loops=4
        -> Aggregate: max(cm2.id)
            -> Table scan on cm2  (rows=300, loops=4)
                -> Select #3 (dependent) → loops=900
                    -> Aggregate: max(cm3.created_at)
                        -> Table scan on cm3  (rows=300, loops=900)

Select #3이 900회, Select #2가 4회 풀스캔을 반복 실행됐다. 서브쿼리가 외부 행의 값을 참조하는 구조이기 때문에 외부 행이 바뀔 때마다 다시 실행된 것이다.

개선 후 ORDER BY LIMIT 1

-> Limit: 1 row(s)  (cost=32.4 rows=1) (actual time=0.0785..0.0785 rows=1 loops=1)
    -> Index range scan on position_chat_message using idx_cm_room_created_id
       over (chatroom_id = 1) (reverse)
       (cost=32.4 rows=300) (actual time=0.0777..0.0777 rows=1 loops=1)
idtypekeyrowsExtra
1rangeidx_cm_room_created_id1Backward index scan

채팅방별 단독 실행 시 인덱스를 역방향으로 탐색하여 첫 번째 행에서 즉시 종료된다.

항목쿼리 1쿼리 2
실행 시간77.9ms (전체 목록)0.0785ms × 8 ≈ 0.628ms (단건 기준)
서브쿼리 반복최대 900회없음
데이터 증가 시반복 횟수 증가채팅방 수에만 비례

개선 후 수치는 채팅방 단건 기준이다. 채팅방이 8개이므로 대략 0.0785ms × 8 ≈ 0.628ms이며, 실제 API 실행 시에는 채팅방 간 쿼리 오버헤드가 추가되리라 예상된다.

개선 후 채팅방 목록 API의 타임아웃 문제는 해결되었다.

정리

이번 경험을 통해 SQL 쿼리의 대표적인 안티패턴들을 학습하고 데이터베이스의 동작에 대해서도 고민해볼 수 있었다.

profile
tls
다음 포스트

유의미한 테스트와 커버리지 사이의 균형

0개의 댓글