데이터베이스에서 트랜잭션은 하나의 거래를 안전하게 처리하도록 보장해주는 것을 의미한다. 작업의 단위
A → B 5000원 계좌이체
A의 잔고 5000원 감소
B의 잔고 5000원 증가
→ 위 두 작업은 2가지 단계로 나뉘기는 하지만, 계좌이체라는 하나의 작업에서 모두 수행되어야 할 로직이다. 이를 트랜잭션을 이용해 하나로 묶는다면 A의 잔고는 정상적으로 감소하고, B의 잔고는 정상적으로 증가할 것이다.
트랜잭션 용어
commit : 모든 작업이 성공해 DB에 정상 반영되는 것
rollback : 작업 중 하나라도 실패해 거래 이전으로 되돌리는 것
트랜잭션의 ACID
트랜잭션은 ACID라는 특징을 보장해야 하는데, 각각의 특징은 다음과 같다.
Atomicity(원자성)
트랜잭션 내에서 실행한 작업들은 마치 하나의 작업인 것처럼 모두 성공하거나 모두 실패해야 한다
여러 쿼리를 마치 하나의 작업인 것처럼 처리할 수 있다
Consistency(일관성)
모든 트랜잭션은 일관성 있는 DB 상태를 유지해야 한다. 예를 들어 DB에서 정한 무결성 제약 조건을 항상 만족해야 한다
Isolation(격리성)
동시에 실행되는 트랜잭션들이 서로에게 영향을 미치지 않도록 격리한다
예를 들어 동시에 같은 데이터를 수정하지 못하도록 해야한다
격리성은 동시성과 관련된 성능 이슈로 인해 트랜잭션 격리 수준을 선택할 수 있다
Durability(지속성)
트랜잭션을 성공적으로 끝내면 그 결과가 항상 영구 보존되어야 한다
중간에 시스템 문제가 발생해도 DB 로그 등을 사용해 성공한 트랜잭션 내용을 복구해야 한다
트랜잭션 격리 수준 - Isolation Level
격리 수준은 4가지가 있고, 보통 READ COMMITTED를 기본으로 사용한다
READ UNCOMMITTED - 커밋되지 않은 읽기
READ COMMITTED - 커밋된 읽기
REPEATABLE READ - 반복 가능한 읽기
SERIALIZABLE - 직렬화 가능
데이터베이스 연결 구조와 DB 세션
사용자가 WAS, DB 접근 툴로 DB에 연결 요청 후 커넥션을 맺게 된다.
이때 DB 내부에는 세션을 만드는데, 커넥션을 통한 모든 요청은 이 세션을 통해 실행된다
즉, 전체 과정은 사용자 → 클라이언트(커넥션) → 서버(커넥션, 세션) 이렇게 된다.
사용자가 커넥션을 닫거나 DBA(DB 관리자)가 세션을 강제로 종료하면 세션이 종료된다
트랜잭션 - DB
트랜잭션 사용법
데이터 변경 실행 후 DB에 결과를 반영하려면 commit을, 결과를 반영하고 싶지 않으면 rollback을 호출하면 된다
commit을 호출하기 전까지는 임시로 데이터를 저장한다. commit을 호출해야 DB에 반영된다.
따라서 트랜잭션을 시작한 세션(사용자)에게만 변경 데이터가 보이고, 다른 세션(사용자)에게는 변경 데이터가 보이지 않는다
예제
격리 수준이 READ COMMITTED라는 가정하에 진행한다. 이 예제는 사용자(세션)이 두 명이 있다고 생각하고, 사용자1에서는 트랜잭션 내에서 데이터 변경(생성, 수정, 삭제)를 수행하고 사용자2에서는 commit 된 데이터를 확인하는 용도로 진행한다. 만약 사용자1이 변경내역을 commit 하지 않는다면 사용자2는 해당 내역을 확인할 수 없는 것을 확인할 수 있다.
위 상황도 트랜잭션의 격리 수준에 따라 달라질 수 있는데, 만약 격리 수준이 READ UNCOMMITTED라면 다른 세션에서도 커밋되지 않은 데이터를 조회할 수 있다. 하지만 이는 그리 좋은 방법은 아니기에(뒤에서 설명하겠다) 격리 수준이 READ COMMITTED라는 가정하에 진행하겠다.
1. 데이터 변경 전
2. 세션1에서 새로운 회원 추가
사용자1이 새로운 회원을 추가한다. 이때 추가한 정보는 세션1에서만 확인이 가능하고, 아직 커밋을 하지 않았기에 세션2에서는 확인이 불가능하다.
READ UNCOMMITTED일 때 발생하는 문제
READ UNCOMMITTED인 경우 커밋되지 않은 데이터를 조회할 수 있는데, 이 경우 데이터 정합성이 맞지 않는 문제가 발생할 수 있고, 이는 심각한 문제이다.
예를들어 세션1이 회원을 추가하는 중에 세션2에서 해당 회원 정보를 조회한 후 관련 작업을 진행한다. 만약 세션1이 갑자기 회원 정보를 롤백하면 세션2에서 있다고 생각한 회원 정보가 갑자기 사라지는 문제가 발생하고, 이는 결국 데이터가 정확하지 않은 문제로 이어질 수 있다.
따라서 트랜잭션 격리 수준은 최소 READ COMMITTED가 되어야 한다!!
3. 세션1에서 commit
세션1에서 commit 한 뒤 반영된 데이터는 이제 세션2에서도 조회가 가능하다
4. 세션1에서 rollback
만약 세션1에서 rollback을 호출한다면 이전 상태로 돌아가게 된다. 다시 1번의 상태로 돌아가게 된다.
자동 커밋
자동 커밋으로 설정하면 각각의 쿼리 실행 직후에 자동으로 커밋을 호출해서 데이터 변경 내역을 DB에 바로 반영한다.
장점
커밋이나 롤백을 직접 호출 하지 않아도 되는 편리함
단점
쿼리 하나 실행할 때마다 자동으로 커밋 되어버리기 때문에 우리가 원하는 트랜잭션 기능을 제대로 수행할 수 없다
→ 따라서 우리가 원하는 로직을 실행하기 위해서 & 데이터 정합성을 보장해야하는 경우에는 수동 커밋(자동 커밋 끈 상태)으로 설정 해야 한다.
트랜잭션 시작
자동 커밋 모드에서 수동 커밋 모드로 전환하는 것을 트랜잭션을 시작한다고 표현한다.
DB 락 개념
동일한 row에 변경 작업이 동시에 들어올 경우를 생각해보자. 이를 제어하지 않으면 사용자 입장에서는 내가 변경한 데이터가 제대로 반영되지 않을 수 있다. 세션1은 money를 10000에서 500으로 바꾸고 싶은데 중간에 세션2에서 money를 10000에서 1000으로 바꾼다면 세션1이 의도한 money는 500이 아닌 1000이 될 수 있다.
DB는 이런 문제를 해결하기 위해 락(Lock) 개념을 제공한다.
락 - 데이터 변경
락을 획득해야 로우의 데이터를 변경할 수 있다.
락을 얻고, row를 변경하기 위해서는 다음 과정을 거쳐야 한다.
트랜잭션을 시작한다
남아있는 락이 있을 경우 락을 획득한다(남아있는 락이 없다면 timeout 시간만큼 대기해야 한다)
timeout 시간 동안 대기해도 락을 얻지 못하면 lock timeout 오류가 발생한다
획득한 락으로 row 데이터를 변경한다
작업을 마치고 commit하면 락이 반환되고, 다른 세션이 락을 획득할 수 있게 된다
락 - 데이터 조회
일반적으로 조회에는 락을 사용하지 않는다. 즉, 세션1에서 락을 획득하고 데이터를 변경하고 있어도 세션2에서 데이터 조회는 가능하다.
select for update
데이터 조회 시에도 락을 획득해 다른 세션의 접근을 막고 싶을 때가 있을 수 있는데, 이때 select for update 구문을 사용하면 된다
select for update를 사용하면 조회 시점에 락을 가져가기 때문에 다른 세션에서 락을 얻지 못해 데이터를 변경할 수 없다
코드 예시
set autocommit false;
select *
from member
where member_id = 'memberA' for update;
트랜잭션 - 적용
트랜잭션을 통해 계좌이체와 같이 원자성이 중요한 작업의 데이터 정합성을 보장할 수 있다는 것을 배웠다. 그럼 이 트랜잭션을 애플리케이션에 적용해야하는데, 다음과 같은 질문이 생긴다.
이에 대한 답은 트랜잭션은 비즈니스 로직이 있는 서비스 계층에서 시작되어야 한다. 비즈니스 로직이 잘못되면 해당 비즈니스 로직으로 인해 문제가 되는 부분을 롤백해야 하기 때문이다.
트랜잭션 동기화와 동일한 커넥션
트랜잭션을 시작하려면 커넥션이 필요하고, 서비스 계층에서 커넥션을 만들고 커밋과 커넥션을 종료해야한다
여러 쿼리를 실행해 하나의 작업을 할 때는 하나의 트랜잭션에서 실행해야하기 때문에 동일한 커넥션으로 진행해야 한다. 그래야 같은 세션을 사용할 수 있다.
여기서 생각하는 방법은 커넥션을 파라미터로 전달해서 같은 커넥션이 사용되도록 유지하는 것이다
트랜잭션을 고려한 서비스 로직
/**
* 트랜잭션 - 파라미터 연동, 풀을 고려한 종료 */
@Slf4j
@RequiredArgsConstructor
public class MemberServiceV2 {
private final DataSource dataSource;
private final MemberRepositoryV2 memberRepository;
public void accountTransfer(String fromId, String toId, int money) throws SQLException {
Connection con = dataSource.getConnection();
try {
con.setAutoCommit(false); //트랜잭션 시작 //비즈니스 로직
bizLogic(con, fromId, toId, money);
con.commit(); //성공시 커밋
} catch (Exception e) {
con.rollback(); //실패시 롤백
throw new IllegalStateException(e);
} finally {
release(con);
}
}
private void bizLogic(Connection con, String fromId, String toId, int money) throws SQLException {
Member fromMember = memberRepository.findById(con, fromId);
Member toMember = memberRepository.findById(con, toId);
memberRepository.update(con, fromId, fromMember.getMoney() - money);
validation(toMember);
memberRepository.update(con, toId, toMember.getMoney() + money);
}
private void validation(Member toMember) {
if (toMember.getMemberId().equals("ex")) {
throw new IllegalStateException("이체중 예외 발생");
}
}
private void release(Connection con) {
if (con != null) {
try {
con.setAutoCommit(true); //커넥션 풀 고려 con.close();
} catch (Exception e) {
log.info("error", e);
}
}
}
정리
데이터 정합성을 위해 트랜잭션을 공부하고 적용해보았다. 트랜잭션으로 데이터를 정확하게 처리할 수 있게 되었지만, 서비스 계층에 트랜잭션 로직이 녹아있어 지저분하고 복잡한 코드가 되었다. 그리고 커넥션을 유지하도록 코드를 변경하는 것도 쉽지 않다. 이 문제를 스프링에서 해결해주는데 다음 포스트에서 이어가도록 하자.
몇 번의 면접을 진행하면서 쿼리 최적화에 대한 질문이 들어와 그 부분을 공부한 내용입니다. 본 포스팅은 왓챠의 쿼리 최적화 포스트를 참고해 작성한 내용입니다.
개요
데이터가 100만개인 테이블에서 10개의 데이터 조회 시 속도가 느릴 경우 쿼리 최적화를 통해 속도 개선을 할 수 있습니다.
예시 데이터 셋
# movie (약 100개의 레코드)
-- id(int): primary key
-- title(varchar): 영화 제목
# rating (약 10,000,000개의 레코드)
-- id(int): primary key
-- movie_id(int): 별점의 대상이 되는 movie id
-- value(int): 별점의 값
# genre (약 100,000개의 레코드)
-- id(int): primary key
-- movie_id(int): 별점의 대상이 되는 movie id
-- genre(varchar): 장르명
1. Select 시에는 꼭 필요한 컬럼만 불러오기
-- Inefficient (not 최적화 쿼리)
SELECT * FROM movie;
-- Improved (최적화한 쿼리)
SELECT id FROM movie;
많은 필드 값을 불러올 수록 DB에 더 많은 로드로 인한 부담이 발생됩니다. 컬럼 중에 불필요한 값을 가진 필드가 있다면 과감히 제외하고, 꼭 필요한 열만 불러오는 것이 좋습니다.
2. Where 절 사용 시, 가급적이면 기존 DB 값에 별도의 연산 걸지 않기
-- Inefficient
SELECT m.id, ANY_VALUE(m.title) title, COUNT(r.id) r_count
FROM movie m
INNER JOIN rating r
ON m.id = r.movie_id
WHERE FLOOR(r.value/2) = 2
GROUP BY m.id;
-- Improved
SELECT m.id, ANY_VALUE(m.title) title, COUNT(r.id) r_count
FROM movie m
INNER JOIN rating r
ON m.id = r.movie_id
WHERE r.value BETWEEN 4 AND 5
GROUP BY m.id;
Inefficient 쿼리의 경우는 Full Table Scan을 하면서 모든 cell 값을 탐색하고, 수식을 건 뒤, 조건 충족 여부를 판단해야 합니다.
반면 Improved 쿼리의 경우 기존에 r.value가 가지고 있는 index를 그대로 활용할 수 있기 때문에 모든 필드 값을 탐색할 필요가 없어 Inefficient 쿼리 대비 더 짧은 Running Time을 가질 수 있습니다.
3. Like 사용 시 와일드카드 문자열(%)을 String 앞부분에는 배치하지 않기
value In (…), value = “…”, value LIKE “…%”는 Index를 활용하는 반면, value Like “%…”는 Full Table Scan을 활용합니다. 따라서 같은 결과를 낼 수 있다면, value Like “%…” 보다는 다른 형태의 조건을 적용하는 것이 바람직합니다.
-- Inefficient
SELECT g.value genre, COUNT(r.movie_id) r_cnt
FROM rating r
INNER JOIN genre g
ON r.movie_id = g.movie_id
WHERE g.value LIKE "%Comedy"
GROUP BY g.value;
-- Improved(1) : value IN (...)
SELECT g.value genre, COUNT(r.movie_id) r_cnt
FROM rating r
INNER JOIN genre g
ON r.movie_id = g.movie_id
WHERE g.value IN ("Romantic Comedy", "Comedy")
GROUP BY g.value;
-- Improved(2):value = "..."
SELECT g.value genre, COUNT(r.movie_id) r_cnt
FROM rating r
INNER JOIN genre g
ON r.movie_id = g.movie_id
WHERE g.value = "Romantic Comedy" OR g.value = "Comedy"
GROUP BY g.value;
-- Improved(3):value LIKE "...%"
-- 모든 문자열을 탐색할 필요가 없어, 가장 좋은 성능을 내었습니다
SELECT g.value genre, COUNT(r.movie_id) r_cnt
FROM rating r
INNER JOIN genre g
ON r.movie_id = g.movie_id
WHERE g.value LIKE "Romantic%" OR g.value LIKE "Comed%"
GROUP BY g.value;
4. SELECT DISTINCT, UNION DISINCT와 같이 중복 값을 제거하는 연산 사용 최대한 지양하기
-- Inefficient
SELECT DISTINCT m.id, title
FROM movie m
INNER JOIN genre g
ON m.id = g.movie_id;
-- Improved
SELECT m.id, title
FROM movie m
WHERE EXISTS (SELECT 'X' FROM rating r WHERE m.id = r.movie_id);
중복 값을 제거하는 연산은 많은 시간이 걸리기 때문에 되도록이면 사용을 지양해야 합니다.
만약 불가피하게 사용해야 하는 상황이라면 DISTINCT 연산을 대체하거나, 연산의 대상이 되는 테이블의 크기를 최소화하는 방법을 고민할 수 있습니다.
가장 대표적인 대체 방법으로는 EXISTS를 활용하는 방법이 있습니다.
5. 같은 조건일 때 Group By 연산 시 Having 보다는 Where 절 사용하기
-- Inefficient
SELECT m.id, COUNT(r.id) AS rating_cnt, AVG(r.value) AS avg_rating
FROM movie m
INNER JOIN rating r
ON m.id = r.movie_id
GROUP BY id
HAVING m.id > 1000;
-- Improved
SELECT m.id, COUNT(r.id) AS rating_cnt, AVG(r.value) AS avg_rating
FROM movie m
INNER JOIN rating r
ON m.id = r.movie_id
WHERE m.id > 1000
GROUP BY id ;
쿼리 실행 순서에서 Where 절이 Having 절보다 먼저 실행됩니다. Where 절로 미리 데이터 크기를 작게 만들면, Group by에서 다뤄야 하는 데이터 크기가 작아지기 때문에 보다 효율적인 연산이 가능합니다.
6. 3개 이상의 테이블을 Inner Join 할 때는, 크기가 가장 큰 테이블을 From에, 나머지 테이블을 Inner Join 절에 배치하기
Inner Join 과정에서 최소한의 Combination을 탐색하도록 From & Inner Join의 순서를 배열하면 좋다는 이야기지만 항상 통용되는 건 아닙니다.
간단한 Inner Join의 경우 대부분의 Query Planner에서 가장 효과적인 순서를 탐색해 Inner Join의 순서를 바꾸기 때문입니다.
예를들어 아래의 Query (A)와 (B)에서 inner join 순서가 다름에도 결과적으로 실행 시간에는 차이가 없습니다. (순서대로 Inner Join 된다면, Query(B)가 (A)보다 훨씬 더 적은 시간이 소요될 것 입니다)
-- Query (A)
SELECT m.title, r.value rating, g.value genre
FROM rating r
INNER JOIN genre g
ON g.movie_id = r.movie_id
INNER JOIN movie m
ON g.id = r.movie_id;
-- Query (B)
SELECT m.title, r.value rating, g.value genre
FROM rating r
INNER JOIN movie m
ON r.movie_id = m.id
INNER JOIN genre g
ON r.movie_id = g.movie_id;
예시의 테이블만으로는 Query Planner의 성능을 넘을 수 있을 정도의 복잡한 쿼리를 만들 수 없습니다.
하지만 테이블 개수가 늘어난다면 탐색해야 할 Inner Join 순서의 경우의 수가 늘어나고, 이는 결국 Planning 비용의 증가로 이어집니다.
그러면 결국 Inner Join 순서로 Planning 비용 줄이기 > 비싼 Planning 비용으로 가장 최적의 순서 찾기 가 되는 상황이 발생할 수 있습니다.
복잡한 쿼리에서는 완전하게 최적화되지 않은 Inner Join 연산이 실행될 때가 있기에 이를 사전 방지해야 합니다. 그때 최적화된 Inner Join 순서를 입력 단계에서 조정해 두는 것은 분명 도움이 될 것 입니다. (Inner Join의 최적화 여부가 연산량에 미치는 영향력은 상당히 크기 때문입니다)
7. 자주 사용하는 데이터 형식에 대해서는 미리 전처리 된 테이블을 따로 보관/관리 하기
RDBMS의 원칙에 어긋나는 측면이 있고, DB의 실시간성을 반영하지 못할 가능성이 높기 때문에 대부분 운영계보다는 분석계에서 더 많이 사용되곤 합니다.
가장 대표적인 사례로는 1) 사용자에 의해 발생한 Log 데이터 중에서 필요한 Event만 모아서 따로 적재해 두는것, 2) 핵심 서비스 지표를 주기적으로 계산해 따로 모아두는 것 등이 있습니다.
ETC
위에 설명한 내용 외에도 Order by는 연산 중간에 사용하지 말기, Limit 활용하기와 같은 습관을 들이는 것도 중요합니다.
