[DB] 인덱스 , 쿼리 최적화, 트랜잭션

1. 인덱스(Index)

인덱스 구조: Balanced Tree (B-Tree)

• 노드가 가득 차면 중앙값 기준으로 분할하여 균형 유지
• 탐색 속도를 빠르게 유지

인덱스 종류

종류	설명
클러스터형 인덱스	PK 또는 UNIQUE + NOT NULL 컬럼에 자동 생성. 테이블에 한 개만 존재. 데이터 정렬 기준이 됨.
보조 인덱스	추가적으로 생성되는 사용자 인덱스. 여러 개 가능. 별도 공간에 컬럼 정렬.

인덱스 관리

-- 인덱스 조회
SHOW INDEX FROM 테이블명;

-- 인덱스 생성
CREATE UNIQUE INDEX 인덱스명 ON 테이블명(컬럼1, 컬럼2, ...);

-- 인덱스 삭제
DROP INDEX 인덱스명 ON 테이블명;

2. 인덱스 활용

인덱스가 영향을 주는 구문

• WHERE
• ORDER BY
• JOIN
• GROUP BY

-- 인덱스가 없으면 → 전체 테이블 순차 검색 (Full Table Scan)
-- 인덱스가 있으면 → 인덱스를 통해 빠르게 탐색

인덱스 장단점

장점	단점
조회 속도 향상	공간 사용 증가
정렬·조인 성능 향상	INSERT/UPDATE/DELETE 성능 저하
WHERE 조건 최적화	과도한 인덱스는 오히려 성능 저하

3. 복합 인덱스

• 복합 인덱스 (A, B, C) → 반드시 왼쪽부터 순차 조건 필요
  • 예: WHERE A=1 AND B=2 → 사용됨
  • 예: WHERE B=2 → 인덱스 비사용

4. EXPLAIN (쿼리 실행 계획 분석)

사용 예시

EXPLAIN SELECT * FROM 테이블 WHERE 조건;

주요 항목

항목	설명
id	실행 순서
select_type	쿼리 유형 (SIMPLE, PRIMARY, SUBQUERY, UNION)
table	사용된 테이블
type	테이블 접근 방식 (중요)
possible_keys	사용할 수 있는 인덱스
key	실제 사용된 인덱스
rows	예측 읽기 행 수 (작을수록 좋음)
Extra	추가 정보 (Using index, Using filesort 등)

type 종류 요약 (성능 좋은 순서)

1. const : 상수 비교 (최고 성능)
2. ref : 인덱스를 통한 특정 값 검색
3. range : 범위 검색 (ex: >, <)
4. index : 인덱스 전체 순회
5. ALL : 전체 테이블 스캔 (최악)

5. 트랜잭션 (Transaction)

ACID 속성

속성	설명
Atomocity (원자성)	모두 수행 or 전혀 수행 안 함
Consistency (일관성)	전후 상태의 데이터 일관성 유지
Isolation (고립성)	트랜잭션 간 간섭 없음
Durability (지속성)	완료된 작업은 영구 저장

MySQL엔진 중 InnoDB 엔진만 트랜잭션 지원

트랜잭션 사용

SET autocommit = 0;  -- 자동 커밋 해제
START TRANSACTION;

INSERT ...;
SAVEPOINT sp1;

ROLLBACK TO sp1;
COMMIT;

• 주의: DML만 수동 커밋 필요.
• DDL, SELECT는 자동 커밋됨.

읽기 일관성 (Read Consistency)

• 읽는 사람은 쓰는 사람을 기다릴 필요 없음
• 쓰는 사람은 다른 쓰는 사람의 작업이 끝나길 기다림 (충돌 방지)
• InnoDB는 Redo Log, Doublewrite Buffer를 이용해 장애 복구 보장