[UMC/시니어 미션 #2]

DB 설계 팁 3가지: 커서 페이징, 트랜잭션 전파, 인덱스 전략

이번 포스트에서는 프로젝트에서 많이 사용하는 데이터베이스 설계 및 성능 개선 기법을 정리해보았습니다.

• 미션 목록 조회 시 커서 기반 페이지네이션
• 트랜잭션의 상태 및 전파
• 함수 기반 인덱스와 복합 인덱스의 차이점

 

✏️ 미션 목록 커서 기반 페이지네이션 (정렬: 포인트 → 최신순)

사용자가 수행 중이거나 완료한 미션을 정렬 기준에 따라 페이징 처리해야 할 경우, OFFSET 방식보다 Cursor 기반 페이지네이션이 더 나은 선택입니다. 특히 데이터가 많을수록 성능 차이가 큽니다.

 

요구사항

• 내가 참여한 모든 미션을 조회
• 정렬 기준: 1순위 point (내림차순), 2순위 updated_at (내림차순)
• 페이징 처리: Cursor 기반으로 안정성과 성능 확보

쿼리 예시

SELECT
    m.id AS missionId,
    m.content AS missionContent,
    m.restaurant_id AS restaurantId,
    m.point AS missionPoint,
    m.updated_at AS missionUpdatedAt,
    um.is_completed AS isMissionCompleted,
    r.name AS restaurantName,
    r.id AS restaurantId
FROM Mission AS m
JOIN UserMission AS um ON um.mission_id = m.id
JOIN Restaurant AS r ON r.id = m.restaurant_id
WHERE um.user_id = :userId
  AND (
        m.point < :cursorPoint
        OR (m.point = :cursorPoint AND m.updated_at < :cursorUpdatedAt)
      )
ORDER BY m.point DESC, m.updated_at DESC
LIMIT :pageSize;

 

핵심 설명

• 커서 기준은 point, updated_at입니다.
• point가 더 낮거나, 같은 point일 경우 updated_at이 더 이전인 데이터를 조건으로 잡습니다.
• 이 방식은 OFFSET 방식보다 성능이 우수하고 중복/누락 없는 페이징이 가능합니다.

 

✏️ 트랜잭션 상태와 전파 

트랜잭션은 하나의 작업 단위를 정의하고, 데이터의 일관성을 보장합니다.
Spring 기반 프로젝트에서는 메서드 간 트랜잭션을 어떻게 "전파"할지도 매우 중요합니다.

 

트랜잭션 상태

Active	트랜잭션이 시작되어 작업 중
Partially Committed	마지막 명령 실행 후 커밋 직전 상태
Committed	성공적으로 DB에 반영된 상태
Failed	오류 발생으로 롤백이 필요한 상태
Aborted	롤백이 완료된 상태 (트랜잭션 취소)

 

트랜잭션 전파 옵션

REQUIRED	기본값. 트랜잭션이 있으면 참여, 없으면 생성
REQUIRES_NEW	기존 트랜잭션 일시 중단 후 새로운 트랜잭션 생성
NESTED	중첩 트랜잭션 (savepoint 사용)
MANDATORY	반드시 기존 트랜잭션 있어야 함, 없으면 예외 발생
NEVER	트랜잭션이 있으면 예외 발생
SUPPORTS	트랜잭션 있으면 참여, 없으면 그냥 실행
NOT_SUPPORTED	기존 트랜잭션 무시하고 새 트랜잭션 없이 실행

⚠️ REQUIRES_NEW는 별도의 DB 커넥션을 사용하므로 잘못 사용하면 커넥션 고갈 등의 문제가 발생할 수 있습니다.

 

✏️ 함수 기반 인덱스 vs 복합 인덱스

 

함수 기반 인덱스 (Function-based Index)

함수나 계산된 값을 기준으로 인덱스를 생성하는 방식입니다.

CREATE INDEX idx_lower_name ON users (LOWER(name));

 

장점

• LOWER(), TRIM() 등 문자열 변환을 빠르게 처리
• 파생 컬럼 계산 비용 감소

단점

• 일부 DB(MySQL 등)는 기본적으로 미지원, MySQL에서는 가상 컬럼 + 인덱스로 구현 가능
• 함수 복잡도에 따라 성능 저하 가능
• 쓰기 작업 시 부하 증가

복합 인덱스 (Composite Index)

여러 컬럼을 묶어 하나의 인덱스로 생성합니다.

CREATE INDEX idx_user_name_age ON users (name, age);

 

장점

• 조건절에서 두 개 이상의 컬럼을 조합한 검색에 최적화
• 정렬 조건과 일치할 경우 성능 매우 뛰어남

단점

• 순서 중요: name, age로 만들면 age만 조건으로는 인덱스 미사용 가능성 있음
• 인덱스가 많아지면 쓰기 성능 저하

✏️ 인덱스 요약표

목적	계산값 검색 최적화	다중 컬럼 조합 검색
장점	대소문자 무시 등 처리 가능	WHERE + ORDER BY 성능 향상
단점	DB 호환성 문제, 쓰기 부하	순서 의존성, 쓰기 성능 저하

✏️ 마무리

이번 게시글에서는 데이터베이스 관련 주제를 정리해봤습니다.

• Cursor 기반 페이지네이션은 대량 데이터에서 안정적인 페이징을 가능하게 하고
• 트랜잭션 전파는 비즈니스 로직의 안정성과 일관성을 확보하며
• 적절한 인덱스 전략은 성능의 핵심이 됩니다.