• 보고서 목적
• 조회가 오래걸릴 수 있는 기능 리스트업
• 주요기능별 동시성 테스트 결과
• 인덱스 적용전과 적용후의 성능비교
• 결론
• 회고

이 보고서는 실제 운영환경처럼 대규모트래픽이 일어난다는 가정하에 읽기성능에 부하를 가져올 수 있는 기능을 리스트업하고, 어떤 기능에 대해서 동시성 이슈가 예민할지를 미리 리스트업을 해서 RDBMS DB의 특성을 기반으로 데이터를 효율적으로 관리하고 성능병목을 개선할 방법을 찾고자 한다.

• concert_id, progress_date를 기준으로 복합조건에 사용된다.
• deleted, is_available은 Partial Index로 처리한다.

SELECT cd
FROM ConcertDate cd
    JOIN FETCH cd.concert c
    JOIN FETCH cd.seats cs
WHERE cd.deleted = false                # 아직삭제되지 않음
    AND cd.isAvailable = true           # 예약가능한 상태
    AND cd.progressDate >= :currentDate # 콘서트진행일정이 오늘이후인 것만
    AND c.id = :concertId               # concertId의 콘서트일정

concert_seats는 50개로 제한되어있지만 concert_dates는 N개를 가질 수있기 때문이다. 만일 풀스캐닝이라면 콘서트일정(concert_dates)의 데이터로우개수만큼 조인을 한후에 조건에 부합되는 데이터로 필터링하기 때문이다.

• 콘서트 더미 데이터 : 총 3건
• 콘서트 일정 더미데이터 개수: 총 10만건
• 콘서트 좌석 더미데이터 개수: 총 50만건

• CTE를 사용한 더미 데이터 생성 쿼리문

# 콘서트 데이터

INSERT INTO concerts (name, artist_name, created_at, updated_at, deleted)
VALUES
  ('Daft Funk Concert', 'Daft Funk', NOW(), NOW(), 0),
  ('Common Concert', 'Common', NOW(), NOW(), 0),
  ('Cold Play Concert', 'Cold Play', NOW(), NOW(), 0)
;


# 콘서트 일정 10만건 더미데이터 생성
SET max_execution_time = 0;
SET SESSION cte_max_recursion_depth = 10000000;

INSERT INTO concert_dates (concert_id, is_available, place, progress_date, created_at, updated_at, deleted)
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 100000
)
SELECT
    CASE
        WHEN n <= 20000 THEN 1      -- Daft Funk: 2만 건
        WHEN n <= 50000 THEN 2      -- Common: 3만 건
        ELSE 3                      -- Cold Play: 5만 건
    END AS concert_id,
    true AS is_available,
    CONCAT('Place-', n) AS place,
    DATE_ADD('2024-01-01', INTERVAL n DAY) AS progress_date,
    NOW(), NOW(), false
FROM seq;


# 콘서트 좌석 500만건 데이터 생성 (1분 8초 소요)

INSERT INTO concert_seats (
    concert_date_id,
    concert_id,
    number,
    price,
    is_available,
    created_at,
    updated_at,
    deleted
)
WITH RECURSIVE seat_num AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1 FROM seat_num WHERE num < 50
),
date_ids AS (
    SELECT id AS concert_date_id, concert_id
    FROM concert_dates
    WHERE deleted = false
)
SELECT
    d.concert_date_id,
    d.concert_id,
    s.num,
    10000 + s.num * 10,
    true,
    NOW(), NOW(), false
FROM date_ids d
JOIN seat_num s;

• 적용 인덱스

CREATE INDEX idx_concert_date_available
ON concert_date (concert_id, progress_date)
WHERE deleted = false AND is_available = true;

• EXPLAIN / EXPLAIN ANALYZE 분석 쿼리문

EXPLAIN
SELECT cd.*
FROM concert_dates cd
    JOIN concerts c ON cd.concert_id = c.id
    JOIN concert_seats cs ON cs.concert_date_id = cd.id
WHERE cd.deleted = false
    AND cd.is_available = true
    AND cd.progress_date >= '2025-04-21'
    AND c.id = 1
;

EXPLAIN ANALYZE
SELECT cd.*
FROM concert_dates cd
    JOIN concerts c ON cd.concert_id = c.id
    JOIN concert_seats cs ON cs.concert_date_id = cd.id
WHERE cd.deleted = false
    AND cd.is_available = true
    AND cd.progress_date >= '2025-04-21'
    AND c.id = 1
;

explain 결과

table: c(concerts)

• type = const - ✅ concert_id = 1 한개만을 찾는 쿼리를 수행
• Extra = Using Index - 인덱스를 통해서 데이터를 직접 읽었으며 Covering Index가 가능하다

table: cs(concert_seats)

• type = ALL : ❌ 풀 테이블 스캔을 하였음.
• 인덱스가 없어서 4,850,524건의 데이터를 전부 읽고 필터링을 적용함.
• Extra = Using Where : WHERE 조건으로는 필터링을 하지만 인덱스가 아니다.

table: cd(concert_dates)

• type = eq_ref : 조인조건(cs.concert_date_id = cd.id)를 기반으로 단일 ROW를 찾았음.
• Extra = Using Where : WHERE 조건으로 is_available, deleted, progress_date 를 이용해서 필터링을 했다.

explain analyze 결과

-> Nested loop inner join
        (cost=5.83e+6 rows=242526) (actual time=15.6..3480 rows=976250 loops=1)

    -> Filter: (cs.concert_date_id is not null)
            (cost=504359 rows=4.85e+6) (actual time=0.108..1821 rows=5e+6 loops=1)
        -> Table scan on cs
            (cost=504359 rows=4.85e+6) (actual time=0.108..1616 rows=5e+6 loops=1)

    -> Filter:
        ((cd.concert_id = 1)
            and (cd.is_available = true)
            and (cd.deleted = false)
            and (cd.progress_date >= DATE'2025-04-21'))
        (cost=0.998 rows=0.05) (actual time=254e-6..264e-6 rows=0.195 loops=5e+6)
        -> Single-row index lookup on cd using PRIMARY (id=cs.concert_date_id)
            (cost=0.998 rows=1) (actual time=122e-6..140e-6 rows=1 loops=5e+6)

Explain Analyze 에서도 도출되는 정보가 무엇인지 한줄씩 분석을 해보았다.

Nested loop inner join
    (cost=5.83e+6 rows=242526)
    (actual time=15.6..3480 rows=976250 loops=1)

• Nested loop join 을 사용하고 있다.
• 실제 수행시간은 약 3.48초 소요됐다.
• 총 976,250 개의 데이터 로우를 반환했다.
• 실제 루프횟수는 concert_seats 1번 스캔할때 그안에서 concert_dates를 5백만번을 조회한 것이다.

Table scan on cs
    (cost=504359 rows=4.85e+6)
    (actual time=0.108..1616 rows=5e+6 loops=1)

• 좌석 485만건을 전부 읽고 있다.
• 평균 수행시간은 약 1.6초 소요하고 있으며 심각한 병목현상이 일어나고 있다.
• 필터조건은 전부다 읽은 뒤에 적용되고 있다.

Single-row index lookup on cd using PRIMARY
    (id=cs.concert_date_id)
    (cost=0.998 rows=1)
    (actual time=122e-6..140e-6 rows=1 loops=5e+6)

• PK로 Lookup을 함으로써, 좌석마다 연결된 공연일정을 개별적으로 단건 조회하고 있다.
• 총 5백만번 루프를 돌면서 500만건의 조인을 수행하고 있으며, Nested Loop Join의 원인인 매우 비효율적인 패턴이다.

여기서 발생하는 문제점은?

concert_seats(cs) 테이블 풀스캔이 원인이다. 이 원인 인한 부작용은 concert_dates(cd) 테이블이 5백만번 조인되면서 시간이 지연되고 있다. concert_date_id, concert_id, deleted, is_available, progress_date 에 index가 존재하지 않는다.

이 쿼리문은 '현재기준으로 특정공연의 공연일정 목록조회' 의 요구사항이기에 concert_dates 조건에 걸리는 row만 선별적으로 읽고, concert_seats는 관련있는 것만 join 대상으로 삼는 방향으로 인덱스를 작성했다.

• 인덱스 생성 쿼리문

CREATE INDEX idx_cs_concert_date ON concert_seats (concert_date_id);

인덱스를 생성 및 적용후에 explain을 걸어보자.

explain 결과

explain analyze 결과

-> Nested loop inner join
    (cost=15045 rows=40306) (actual time=0.353..314 rows=976250 loops=1)
    -> Filter: (
            (cd.concert_id = 1)
                and (cd.is_available = true)
                and (cd.deleted = false)
                and (cd.progress_date >= DATE'2025-04-21'))
            (cost=10101 rows=832) (actual time=0.332..52.7 rows=19525 loops=1)
        -> Table scan on cd
            (cost=10101 rows=99806) (actual time=0.0408..44 rows=100000 loops=1)
    -> Covering index lookup on cs using idx_cs_concert_date (concert_date_id=cd.id)
        (cost=1.1 rows=48.5) (actual time=0.00778..0.0113 rows=50 loops=19525)

• 인덱스 idx_cs_concert_date 인덱스를 사용함으로써, 풀스캔으로 모든 concert_seats 를 조회했었는데 인덱스 루프를 조회하는 방안으로 변경됐다. 즉 각 공연일정에 해당하는 좌석만 인덱스를 통해서 바로 접근이 가능하며 약 48건 내외만 읽어서 I/O를 감소시킨다.

• 루프횟수는 줄지 않았지만 Loop당 읽는 row수가 50개로 대폭감소되어 속도가 향상되었다.

• 실제시간도 인덱스를 적용하기 전에는 3.5초이상 소요해왔으나, 인덱스를 적용한 후에는 0.3초 수준으로 감소했다.

• 쿼리문을 실행하는 시간이 3.5초 -> 0.353초 로 대폭줄고 성능이 향상 되었다.

WHERE문의 조건 순서대로 복합인덱스를 추가를 시도했고 분석쿼리를 다시한번더 수행시켜봤다.

CREATE INDEX idx_cd_filtering ON concert_dates (
 deleted,
 is_available,
 concert_id,
 progress_date
);

explain 결과: concert_dates(cd) 테이블의 filtered의 값과 테이블에 인덱스가 적용됨을 확인할 수 있다.

테이블에 읽은 row들중 WHERE 조건을 만족하는 비율이 0.83% -> 38.18% 로 대폭 증가했다는 의미이다. 즉 이를 통해 더많은 row가 조건을 통과하며, 정확한 조건 필터링을 인덱스를 통해서 진행됨을 의미한다. 그래서 실행속도의 차이점을 확인하기위해서 EXPLAIN ANALYZE 문을 실행해봤으며 전체 쿼리수행시간은 0.353초에서 0.248~49.5ms 정도로 빠른 조회성능을 보이고 있다. 루프 1.95만번 반복하면서도 평균조회시간이 0.01ms 내외로 Nested Loop Join이 매우 가볍게 동작된다. 따라서 976,250건 결과를 불러오는데도 불구하고 0.3초 미만의 실행속도를 가진다. 복합인덱스를 적용함으로써 조건필터링속도를 개선했고, 조인범위를 50으로 최소화시킴으로써 concert_seats 인덱스를 통해서 필요한것만 빠르게 조회하도록 조회성능속도를 개선시켰다.

인덱스를 추가함으로써 테이블 풀스캐닝으로 전체 데이터로우를 조인하게되어 실행속도가 느려지는 현상을 봤다. 데이터개수가 2만개일때 실행시간이 3.5초가량됐는데 만일 5만개였다면 concert_seats 데이터는 750만개의 데이터를 조인해야되는 문제가 있다. 데이터가 더 커질수록 느려진다.

인덱스를 생성함으로써 실행시간을 0.3초로 10배가량 대폭줄었는데 그 이유는 단일기반 Row Lookup에서 Index 기반 배치 접근으로 개선됐기 때문이다.

concert_dates 테이블은 아직 풀스캐닝으로 나오는건 어쩔 수 없지만 500만번조인에서 50번만 조인하는 식으로 조인의 횟수를 대폭 줄였다. 또한 옵티마이저가 무시하지 않도록 where 조건의 순서에 맞춰서 복합 인덱스를 하나 더 추가했다.

따라서 이번 인덱스적용을 통해서 테이블조인의 속도를 향상시킬 수 있었으며, 복합인덱스 추가로 조건필터링 성능을 향상시켜서 조회성능을 높였다.

인덱스는 이론적으로 들어봤지만 실무로 경험을 하지못해서 그런지 생소했다. 동일한 주제의 프로젝트를 만들더라도 사람마다 설계가 다르기때문에 나의 설계에 맞춰서 리스트업을 하는게 쉽지 않았다. 인덱스를 적용해야되는 기능이 무엇인지 예상을 해서 리스트업을 해야되는지 시간이 소요됐다.

또한 문제상황발생만을 알리지말고 내가 직접 문제상황을 만들어서 논리정연하게 어떤문제가 발생했고, 이 문제에 대한 해결방안이 무엇이고 이 문제를 해결함으로써 얻게되는 결과가 무엇인지 상대방을 설득시키는 글쓰기가 말로는 쉬워보여도 실제로 행동으로 옮기다보니 쉽지 않았다. 정답이 없기도하고, 내가 생각한게 정답이 아닐까봐 많이 고민을 했다.

이번에 데이터베이스 기술에 집중을 함으로써 데이터베이스의 이론을 공부할때 실제 프렉티스를 직접 만들어가면서 하는게 용어에 대한 이해를 더 깊이 할 수 있을거 같단 생각을 했다.

현재는 시간관계상 예약가능한 일정목록조회에만 신경을 썼다. 좀더 나아가서는 예약가능한 좌석목록 조회, 예약과, 토큰에도 인덱스를 태우는 과정도 추가함으로써 인덱스와 친숙해질 필요가 있다.