SQL_23) index 넌 못지 나간다.

Topic (오늘의 주제)

트랜잭션(Transaction)과 인덱스(Index)는 데이터베이스에서 서로 밀접한 관계를 가집니다. 트랜잭션이 데이터를 변경할 때 인덱스도 함께 업데이트되어야 하며, 인덱스의 존재는 트랜잭션의 성능과 일관성에 직접적인 영향을 미칩니다. 트랜잭션의 ACID 속성을 보장하면서 인덱스를 효율적으로 관리하는 것이 데이터베이스 성능 최적화의 핵심입니다.

Why (왜 사용하는가? 왜 중요한가?)

트랜잭션이 데이터를 INSERT, UPDATE, DELETE할 때마다 해당 컬럼에 인덱스가 있다면 인덱스도 함께 업데이트되어야 합니다. 이 과정에서 인덱스의 정렬 상태를 유지하기 위한 추가 작업이 발생하며, 트랜잭션의 성능과 일관성에 영향을 줍니다.
인덱스는 트랜잭션의 격리성(Isolation)과 밀접한 관련이 있습니다. 트랜잭션이 인덱스를 사용하여 데이터를 검색할 때, 다른 트랜잭션의 커밋되지 않은 변경사항이 인덱스에 반영되면 Dirty Read나 Phantom Read 같은 동시성 문제가 발생할 수 있습니다.
트랜잭션의 롤백 시 인덱스도 함께 롤백되어야 하며, 인덱스의 락(Lock) 메커니즘은 트랜잭션의 동시성 제어에 중요한 역할을 합니다. 인덱스가 많을수록 트랜잭션의 쓰기 성능이 저하되지만, 읽기 성능은 향상되는 트레이드오프를 이해해야 합니다.

1. 트랜잭션과 인덱스의 기본 관계

트랜잭션에서 인덱스의 역할

트랜잭션이 데이터를 변경할 때, 해당 컬럼에 인덱스가 있다면 인덱스도 함께 업데이트되어야 합니다.

예시:

-- 인덱스가 있는 컬럼에 데이터 삽입
CREATE INDEX idx_name ON users(name);

BEGIN TRANSACTION;
    INSERT INTO users (id, name, email)
    VALUES (1, '홍길동', 'hong@example.com');
    -- 인덱스 idx_name도 함께 업데이트됨
COMMIT;

동작 과정:

트랜잭션이 시작됨
users 테이블에 데이터 삽입
idx_name 인덱스에 ('홍길동', 레코드 주소) 추가
인덱스의 B+Tree 구조 유지를 위해 재정렬 작업 수행
트랜잭션 커밋 시 인덱스 변경사항도 함께 커밋

인덱스 업데이트의 원자성 보장

트랜잭션의 원자성(Atomicity)에 따라 인덱스 업데이트도 All or Nothing 원칙을 따릅니다.

예시:

BEGIN TRANSACTION;
    INSERT INTO users (id, name, email)
    VALUES (1, '홍길동', 'hong@example.com');
    -- 인덱스 업데이트 시작

    -- 오류 발생
    INSERT INTO orders (user_id, amount)
    VALUES (999, 10000);  -- 외래 키 제약조건 위반

    -- 원자성 보장: 인덱스 업데이트도 함께 롤백됨
ROLLBACK;

핵심:

테이블 데이터 변경 실패 → 인덱스 업데이트도 롤백
인덱스 업데이트 실패 → 전체 트랜잭션 롤백
중간 상태는 존재하지 않음

2. 트랜잭션에서 인덱스 업데이트 비용

인덱스 업데이트 오버헤드

트랜잭션이 데이터를 변경할 때마다 인덱스도 함께 업데이트되어야 하므로 추가 비용이 발생합니다.

INSERT 시 인덱스 업데이트:

-- 인덱스: idx_name, idx_email
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_email ON users(email);

BEGIN TRANSACTION;
    INSERT INTO users (id, name, email)
    VALUES (1, '홍길동', 'hong@example.com');
    -- 1. 테이블에 데이터 삽입
    -- 2. idx_name 인덱스 업데이트 (B+Tree 재정렬)
    -- 3. idx_email 인덱스 업데이트 (B+Tree 재정렬)
COMMIT;

비용 분석:

인덱스 1개: 테이블 삽입 + 인덱스 업데이트 1회
인덱스 2개: 테이블 삽입 + 인덱스 업데이트 2회
인덱스 N개: 테이블 삽입 + 인덱스 업데이트 N회

UPDATE 시 인덱스 업데이트:

BEGIN TRANSACTION;
    UPDATE users
    SET name = '김철수'  -- 인덱스 컬럼 변경
    WHERE id = 1;
    -- 1. 기존 인덱스 항목 삭제 ('홍길동')
    -- 2. 새 인덱스 항목 추가 ('김철수')
    -- 3. B+Tree 재정렬
COMMIT;

DELETE 시 인덱스 업데이트:

BEGIN TRANSACTION;
    DELETE FROM users WHERE id = 1;
    -- 1. 테이블에서 데이터 삭제
    -- 2. 모든 인덱스에서 해당 항목 삭제
    -- 3. B+Tree 재정렬
COMMIT;

인덱스 개수와 트랜잭션 성능

인덱스가 많을수록 트랜잭션의 쓰기 성능이 저하됩니다.

성능 비교:

인덱스 0개: INSERT 시간 = T
인덱스 1개: INSERT 시간 = T + I (인덱스 업데이트 시간)
인덱스 3개: INSERT 시간 = T + 3I
인덱스 5개: INSERT 시간 = T + 5I

트레이드오프:

인덱스 많음: SELECT 빠름, INSERT/UPDATE/DELETE 느림
인덱스 적음: SELECT 느림, INSERT/UPDATE/DELETE 빠름

3. 트랜잭션 격리성과 인덱스

인덱스를 통한 데이터 검색과 격리성

트랜잭션이 인덱스를 사용하여 데이터를 검색할 때, 격리 수준에 따라 다른 트랜잭션의 변경사항을 볼 수 있는지가 결정됩니다.

READ UNCOMMITTED와 인덱스:

-- 트랜잭션 A
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM users WHERE name = '홍길동';
-- 인덱스 idx_name을 사용하여 검색

-- 트랜잭션 B (동시 실행)
BEGIN TRANSACTION;
UPDATE users SET name = '김철수' WHERE name = '홍길동';
-- 인덱스 업데이트 시작 (아직 커밋 안 함)

-- 트랜잭션 A
SELECT * FROM users WHERE name = '홍길동';
-- Dirty Read 가능: 커밋되지 않은 인덱스 변경사항을 볼 수 있음

READ COMMITTED와 인덱스:

-- 트랜잭션 A
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM users WHERE name = '홍길동';
-- 커밋된 데이터만 인덱스에서 읽음

-- 트랜잭션 B
BEGIN TRANSACTION;
UPDATE users SET name = '김철수' WHERE name = '홍길동';
COMMIT;  -- 인덱스 업데이트 커밋

-- 트랜잭션 A
SELECT * FROM users WHERE name = '홍길동';
-- 커밋된 변경사항 반영 (Non-Repeatable Read 가능)

인덱스 락과 트랜잭션 격리

인덱스도 락 메커니즘을 사용하여 트랜잭션의 격리성을 보장합니다.

인덱스 락의 종류:

인덱스 키 락 (Key Lock)
- 특정 인덱스 키에 대한 락
- 해당 키를 사용하는 트랜잭션만 접근 가능
갭 락 (Gap Lock)
- 인덱스 키 사이의 간격에 대한 락
- Phantom Read 방지
넥스트 키 락 (Next-Key Lock)
- 인덱스 키 락 + 갭 락
- REPEATABLE READ 격리 수준에서 사용

예시:

-- 트랜잭션 A (REPEATABLE READ)
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM users WHERE name BETWEEN '가' AND '나';
-- 인덱스에 넥스트 키 락 설정

-- 트랜잭션 B
BEGIN TRANSACTION;
INSERT INTO users (name) VALUES ('김철수');
-- '가'와 '나' 사이에 삽입 시도 → 대기 (갭 락으로 인해)

-- 트랜잭션 A
COMMIT;  -- 락 해제

-- 트랜잭션 B 실행 가능

4. 트랜잭션 롤백과 인덱스 복구

롤백 시 인덱스 복구

트랜잭션이 롤백되면 인덱스도 이전 상태로 복구되어야 합니다.

롤백 메커니즘:

BEGIN TRANSACTION;
    INSERT INTO users (id, name, email)
    VALUES (1, '홍길동', 'hong@example.com');
    -- 인덱스 업데이트 (아직 커밋 안 됨)

    -- 오류 발생
    ROLLBACK;
    -- 1. 테이블 데이터 롤백
    -- 2. 인덱스 변경사항도 롤백
    -- 3. 인덱스가 트랜잭션 시작 전 상태로 복구

인덱스 복구 방법:

언두 로그 (Undo Log) 사용
- 인덱스 변경 전 상태를 언두 로그에 저장
- 롤백 시 언두 로그를 사용하여 복구
인덱스 변경 지연
- 트랜잭션 커밋 전까지 인덱스 변경을 지연
- 커밋 시에만 인덱스 업데이트

롤백 비용과 인덱스

인덱스가 많을수록 롤백 비용도 증가합니다.

롤백 비용:

인덱스 0개: 롤백 시간 = R
인덱스 1개: 롤백 시간 = R + I (인덱스 복구 시간)
인덱스 3개: 롤백 시간 = R + 3I

최적화 전략:

불필요한 인덱스 제거로 롤백 비용 감소
트랜잭션 범위 최소화로 롤백 가능성 감소

5. 인덱스와 트랜잭션 데드락

인덱스로 인한 데드락

인덱스가 많을수록 데드락 발생 가능성이 증가합니다.

데드락 발생 예시:

-- 인덱스: idx_name, idx_email
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_email ON users(email);

-- 트랜잭션 A
BEGIN TRANSACTION;
UPDATE users SET name = '김철수' WHERE email = 'hong@example.com';
-- idx_email 인덱스로 검색 → idx_name 인덱스 업데이트 대기

-- 트랜잭션 B (동시 실행)
BEGIN TRANSACTION;
UPDATE users SET email = 'new@example.com' WHERE name = '홍길동';
-- idx_name 인덱스로 검색 → idx_email 인덱스 업데이트 대기

-- 데드락 발생!
-- A: idx_name 락 대기 (B가 가지고 있음)
-- B: idx_email 락 대기 (A가 가지고 있음)

데드락 방지 전략:

인덱스 순서 일관성 유지
- 항상 같은 순서로 인덱스 접근
- 예: 항상 name → email 순서
불필요한 인덱스 제거
- 사용하지 않는 인덱스는 제거
- 데드락 가능성 감소
트랜잭션 범위 최소화
- 트랜잭션 시간을 짧게 유지
- 락 보유 시간 감소

6. 트랜잭션에서 인덱스 사용 최적화

인덱스 선택과 트랜잭션 성능

트랜잭션이 인덱스를 효율적으로 사용하면 성능이 향상됩니다.

인덱스 사용 예시:

-- 인덱스: idx_user_id
CREATE INDEX idx_user_id ON orders(user_id);

BEGIN TRANSACTION;
    -- 인덱스를 사용한 빠른 검색
    SELECT * FROM orders
    WHERE user_id = 123;  -- Index Scan 사용

    -- 인덱스 없으면 Full Table Scan (느림)
COMMIT;

인덱스 설계 원칙

트랜잭션 성능을 고려한 인덱스 설계:

자주 조회되는 컬럼에 인덱스 생성
- SELECT 성능 향상
- 트랜잭션의 읽기 성능 개선
자주 변경되지 않는 컬럼에 인덱스 생성
- UPDATE/DELETE 시 인덱스 업데이트 비용 감소
- 트랜잭션 쓰기 성능 유지
복합 인덱스 활용
- 여러 컬럼을 함께 조회하는 경우
- 단일 인덱스보다 효율적

예시:

-- 복합 인덱스
CREATE INDEX idx_user_date ON orders(user_id, order_date);

BEGIN TRANSACTION;
    -- 복합 인덱스 사용
    SELECT * FROM orders
    WHERE user_id = 123 AND order_date >= '2024-01-01';
    -- idx_user_date 인덱스 사용 (빠름)
COMMIT;

7. 트랜잭션과 인덱스의 실무 활용

대용량 데이터 삽입 시 인덱스 전략

대량의 데이터를 삽입할 때는 인덱스를 일시적으로 비활성화하는 것이 효율적일 수 있습니다.

인덱스 비활성화 전략:

-- 1. 인덱스 비활성화
ALTER INDEX idx_name ON users DISABLE;

-- 2. 대량 데이터 삽입 (인덱스 업데이트 없음)
BEGIN TRANSACTION;
    INSERT INTO users SELECT * FROM temp_users;
COMMIT;

-- 3. 인덱스 재활성화 및 재구성
ALTER INDEX idx_name ON users REBUILD;

주의사항:

인덱스 비활성화 중에는 해당 인덱스를 사용한 검색 불가
데이터 삽입 후 반드시 인덱스 재구성 필요

트랜잭션 로그와 인덱스

트랜잭션 로그는 인덱스 변경사항도 기록합니다.

트랜잭션 로그 구조:

트랜잭션 로그:
1. 테이블 데이터 변경 기록
2. 인덱스 변경 기록
3. 커밋/롤백 정보

복구 시나리오:

-- 시스템 장애 발생
-- 트랜잭션 로그를 사용하여 복구

-- 1. 커밋된 트랜잭션: 인덱스 변경사항도 복구
-- 2. 롤백된 트랜잭션: 인덱스 변경사항도 롤백
-- 3. 진행 중이던 트랜잭션: 롤백하여 인덱스 일관성 유지

요약

트랜잭션과 인덱스의 관계: 트랜잭션이 데이터를 변경할 때 인덱스도 함께 업데이트되며, 트랜잭션의 원자성에 따라 인덱스 변경사항도 All or Nothing 원칙을 따릅니다.
인덱스 업데이트 비용: 인덱스가 많을수록 INSERT/UPDATE/DELETE 시 인덱스 업데이트 오버헤드가 증가하여 트랜잭션의 쓰기 성능이 저하됩니다. 반면 SELECT 성능은 향상되는 트레이드오프가 있습니다.
격리성과 인덱스: 트랜잭션의 격리 수준에 따라 인덱스를 통한 데이터 검색 시 다른 트랜잭션의 변경사항을 볼 수 있는지가 결정됩니다. 인덱스 락(Key Lock, Gap Lock, Next-Key Lock)을 통해 격리성을 보장합니다.
롤백과 인덱스: 트랜잭션이 롤백되면 인덱스도 이전 상태로 복구되어야 하며, 인덱스가 많을수록 롤백 비용이 증가합니다.
데드락과 인덱스: 인덱스가 많을수록 데드락 발생 가능성이 증가하므로, 인덱스 접근 순서를 일관되게 유지하고 불필요한 인덱스를 제거하는 것이 중요합니다.
최적화 전략: 자주 조회되는 컬럼에 인덱스를 생성하고, 자주 변경되지 않는 컬럼에 인덱스를 생성하며, 대량 데이터 삽입 시에는 인덱스를 일시적으로 비활성화하는 등의 전략을 사용할 수 있습니다.

참고 자료

예상 꼬리질문 정리

1. 트랜잭션 중 인덱스가 손상되면 어떻게 되나?

인덱스 무결성 보장:

트랜잭션 중 인덱스가 손상되면 전체 트랜잭션이 롤백됩니다.

BEGIN TRANSACTION;
    INSERT INTO users (id, name) VALUES (1, '홍길동');
    -- 인덱스 업데이트 중 오류 발생

    -- 인덱스 손상 감지
    -- 전체 트랜잭션 롤백
ROLLBACK;

복구 메커니즘:

트랜잭션 로그를 사용하여 인덱스 복구
인덱스 재구성(REBUILD) 필요 시 자동 실행

2. 인덱스가 많으면 트랜잭션이 왜 느려지나?

인덱스 업데이트 비용:

인덱스가 많을수록 각 인덱스를 업데이트하는 비용이 증가합니다.

인덱스 1개: INSERT 시간 = T + I
인덱스 5개: INSERT 시간 = T + 5I

B+Tree 재정렬 비용:

각 인덱스마다 B+Tree 재정렬 필요
디스크 I/O 발생
락 경합 증가

최적화:

불필요한 인덱스 제거
자주 변경되지 않는 컬럼에만 인덱스 생성

3. 트랜잭션 격리 수준이 인덱스 사용에 영향을 주나?

격리 수준에 따른 인덱스 락:

READ COMMITTED:

인덱스 키 락만 사용
커밋된 데이터만 읽음

REPEATABLE READ:

넥스트 키 락 사용
갭 락으로 Phantom Read 방지

SERIALIZABLE:

범위 락 사용
인덱스 전체에 락 설정 가능

성능 영향:

격리 수준이 높을수록 인덱스 락 범위 증가
동시성 저하, 데드락 가능성 증가

4. 롤백 시 인덱스 복구는 어떻게 이루어지나?

언두 로그(Undo Log) 사용:

BEGIN TRANSACTION;
    INSERT INTO users (id, name) VALUES (1, '홍길동');
    -- 인덱스 업데이트 전 상태를 언두 로그에 저장

    ROLLBACK;
    -- 언두 로그를 사용하여 인덱스 복구
    -- 인덱스가 트랜잭션 시작 전 상태로 복구됨

복구 과정:

언두 로그에서 인덱스 변경 전 상태 확인
인덱스에서 변경사항 제거
인덱스 B+Tree 재정렬

비용:

인덱스가 많을수록 복구 비용 증가
롤백 시간이 길어짐

5. 인덱스 없이 트랜잭션을 실행하면 어떻게 되나?

Full Table Scan 발생:

-- 인덱스 없음
BEGIN TRANSACTION;
    SELECT * FROM users WHERE name = '홍길동';
    -- Full Table Scan 발생 (느림)
    -- 전체 테이블을 스캔하여 검색
COMMIT;

영향:

SELECT 성능 저하
트랜잭션 시간 증가
락 보유 시간 증가 → 동시성 저하

권장:

자주 조회되는 컬럼에 인덱스 생성
트랜잭션 성능 향상

'SQL' 카테고리의 다른 글

SQL_24) 동시라는 것은 SQL에 좋지 않다. (1)	2026.01.30
SQL_22) 건초더미에서 바늘 찾기 싫다면 인덱스를 쓰세요 (0)	2026.01.28
SQL_21) 그럼 여기가 inner이지 outer이냐 ? (0)	2026.01.23
SQL_20) 트랜잭션, 님아 그 COMMIT을 누르지마오 (1)	2025.12.19
SQL_19) DML, DDL, DCL (1)	2025.12.19

Topic (오늘의 주제)

Why (왜 사용하는가? 왜 중요한가?)

1. 트랜잭션과 인덱스의 기본 관계

트랜잭션에서 인덱스의 역할

인덱스 업데이트의 원자성 보장

2. 트랜잭션에서 인덱스 업데이트 비용

인덱스 업데이트 오버헤드

인덱스 개수와 트랜잭션 성능

3. 트랜잭션 격리성과 인덱스

인덱스를 통한 데이터 검색과 격리성

인덱스 락과 트랜잭션 격리

4. 트랜잭션 롤백과 인덱스 복구

롤백 시 인덱스 복구

롤백 비용과 인덱스

5. 인덱스와 트랜잭션 데드락

인덱스로 인한 데드락

6. 트랜잭션에서 인덱스 사용 최적화

인덱스 선택과 트랜잭션 성능

인덱스 설계 원칙

7. 트랜잭션과 인덱스의 실무 활용

대용량 데이터 삽입 시 인덱스 전략

트랜잭션 로그와 인덱스

요약

참고 자료

예상 꼬리질문 정리

1. 트랜잭션 중 인덱스가 손상되면 어떻게 되나?

2. 인덱스가 많으면 트랜잭션이 왜 느려지나?

3. 트랜잭션 격리 수준이 인덱스 사용에 영향을 주나?

4. 롤백 시 인덱스 복구는 어떻게 이루어지나?

5. 인덱스 없이 트랜잭션을 실행하면 어떻게 되나?

'SQL' 카테고리의 다른 글

티스토리툴바