목차는 다음과 같다.
- 커서 기반 페이지네이션 수정
- 트랜잭션
- 인덱스 연구하기
커서 기반 페이지네이션
내가 진행중, 진행 완료한 미션 모아서 보는 쿼리(페이징 포함)에서
정렬 기준을 1순위는 포인트로 2순위는 최신순으로 하여 Cursor기반 페이지네이션을 구현해볼 것이다.
기존 구현 방식은 최신순, id 조합으로 구현을 했었다.
SELECT
m.id, m.points, m.content, s.name AS mission_name, s.store_id, um.user_mission_status
FROM (
SELECT user_mission_id, mission_id, user_mission_status
FROM user_missions
WHERE um.user_id = 1
AND um.user_mission_status IN ('PROGRESS', 'SUCCESS')
AND (created_at < '2025-09-18 12:00:00.000000' OR
(um.user_mission_id < 123 AND created_at = '2025-09-18 12:00:00.000000'))
ORDER BY created_at, user_mission_id DESC
LIMIT 10;
)
JOIN missions AS um ON m.mission_id = um.mission_id
JOIN stores s ON m.store_id = s.store_id;포인트,날짜,id 조합으로 구현하면 된다. WHERE절을 다음과 같이 수정해야 한다.
m.points < 100
OR (m.points < 100 AND m.created_at '2025-09-18 12:00:00.000000')
OR (m.points < 100 AND m.created_at '2025-09-18 12:00:00.000000' AND m.mission_id < 123)전체 코드는 다음과 같이 된다.
SELECT
m.mission_id,
m.points,
m.content,
s.name AS store_name,
s.store_id,
um.user_mission_status
FROM (
SELECT um.user_mission_id, um.mission_id, um.user_mission_status
FROM user_missions um
WHERE um.user_id = 1
AND um.user_mission_status IN ('PROGRESS', 'SUCCESS')
AND (
m.points < 100
OR (m.points = 100 AND um.created_at < '2025-09-18 12:00:00.000000')
OR (m.points = 100 AND um.created_at = '2025-09-18 12:00:00.000000' AND um.user_mission_id < 123)
)
ORDER BY m.points DESC, um.created_at DESC, um.user_mission_id DESC
LIMIT 10
) um
JOIN missions m ON um.mission_id = m.mission_id
JOIN stores s ON m.store_id = s.store_id;
트랜잭션
트랜잭션은 데이터베이스의 상태를 변화시키는 한 개의 논리적 작업 단위이다. SQL문을 사용해서 데이터베이스에 접근하는 것이다. 여러 SQL문이더라도 한번 실행하는 단위라고 볼 수 있다. 데이터의 일관성을 보장하기 위해서 사용된다.
다양한 트랜젝션 상태
- Active : 트랜잭션이 시작되어 쿼리 작업을 수행 중인 상태
- partially Committed : 연산, 작업은 끝났는데 commit은 되지 않은 상태
- committed: 트랜잭션 정상 종료, 변경 사항 DB에 반영된 상태
- Falied : 실행 도중 오류 발생해서, 트랜잭션이 진행이 되지 않은 상태
- Aborted/rolled back : 오류, 취소로 인해 변경 사항이 반영되지 않은 상태
트랜잭션 관리를 비즈니스 로직과 분리하는 것이 권장되는데, 스프링에서는 @Transactional 어노테이션을 통해, AOP를 사용하여 트랜잭션 관리를 쉽게 분리할 수 있도록 한다.
트랜젝션 전파
스프링에서 메서드 실행 시, 트랜잭션에 대해 하나의 트랜잭션이 다른 트랜잭션 내에서 호출될 때 해당 트랜잭션을 어떻게 처리할 지, 기존 트랜잭션이 있는지, 없는 채로 실행해도 되는지, 새로 만들어야만 하는지를 결정하는 규칙을 전파라고 한다.
Spring에서는 @Transactional의 propagation 옵션으로 설정 가능하다.
- Propagation.REQUIRED : 트랜잭션이 필요하고, 기존 트랜잭션이 있으면 사용하고 없으면 새로 생성한다.
- Propagation.REQUIRES_NEW : 항상 새로운 트랜잭션이 필요하다. 기존 트랜잭션이 있어도 일시 중지하고, 새로 생성하여 실행한다. 하위 메서드와 상위 메서드는 독립적으로, 하위 메서드에서 예외 발생되어서 롤백된다고 해도, 상위 트랜잭션은 계속 실행된다.
- Propagation.SUPPORTS : 기존 트랜잭션이 있으면 사용하고, 없으면 없이 진행한다.
- Propagation.MANDATORY : 트랜잭션이 필수이다. 메서드 호출 시 트랜잭션이 설정되어 있어야 한다.
- Propagation.NOT_SUPPORTED : 트랜잭션을 지원하지 않고 기존에 트랜잭션이 있었어도 없이 진행한다.
- Propagation.NEVER : 트랜잭션을 지원하지 않고 상위 스코프에도 트랜잭션이 설정되있으면 안 된다.
- Propagation.NESTED : 중첩 트랜잭션을 만든다.
인덱스
인덱스는 데이터 검색 성능 향상을 위해서 사용하는 자료구조로, 주로 B-Tree 또는 Hash이다.
종류는 다음과 같은 게 있다.
- unique index: unique 제약 조건으로 생성되며, 값이 중복될 수 없다. 즉 연관된 테이블의 하나의 행만 가리킬 수 있다.
- primary index: primary key 제약 조건을 걸면 생성되며, 식별자 역할을 한다.
- non-unique index: 검색 속도를 높이기 위해 만든 인덱스로, 중복 가능하다. 즉 연관된 테이블의 여러 행을 가리킬 수가 있다. 주로 자주 where 조건에 들어가는 컬럼에 설정된다.
- function-based index: 함수나 표현식의 계산값으로 생성된다.
- composite / multi-column index: 여러 컬럼을 묶어서 생성되며, 컬럼 순서가 중요하다.
function based vs composite
함수 기반 인덱스와 복합 인덱스를, 위의 커서 기반 페이징 쿼리 기반으로 생성해보고, 성능 비교를 해 볼 것이다.
함수 기반 인덱스 만들어보기
커서가 문자열(title)일 때 대소문자 구분을 없애고 싶다면 다음과 같이 생성할 수 있다.
CREATE INDEX idx_mission_lower_title
ON mission ((LOWER(title)));장점
- 표현식이나 함수를 적용해서 WHERE 절로 결과를 자주 조회할 경우 유리하다.
- 날짜를 가공해서 검색한다거나, 특정 문자열로 매핑을 해서 저장해야 할 때 유리하다.
단점
- 인덱스는 해당 테이블에 생성이 되는 것이기 때문에, INSERT, UPDATE 할 때마다 자주 적용되는 함수의 결과도 계산을 해서 저장을 해야 한다. 성능이 약간 저하될 수 있다.
- DBMS별로 지원 차이가 있을 수 있다.
복합 인덱스 만들어보기
복합 인덱스는 여러 컬럼을 묶어서, 특정 조합을 조회할 때 유리하다. 사용자의 상태별로 미션을 조회할 때,
사용자id, 사용자의 미션 진행 상태, 미션 id로 조회를 하게 된다. 조건이 많을 경우에 인덱스를 사용하면 성능이 약간 향상될 수 있다. 즉 자주 조회하는 컬럼들을 묶어서, 인덱스를 생성하는 것이다.
CREATE INDEX idx_user_status_mission
ON user_mission(user_id, status, mission_id);장점
- 커서 페이징을 할 때, 여러 컬럼을 묶어서 조회하는 경우가 많으므로 성능이 향상된다.
단점
- 컬럼 순서가 중요하다.
