[CS/DB] 스터디 week5

2rlokr·2025년 4월 13일

cs-knowledge

목록 보기

5/12

JOIN의 기법

✏️ Nested Loop Join, Merge Join, Hash Join에 대해 설명해주세요

JOIN 연산은 두 개 이상의 테이블을 하나의 테이블로 만드는 연산이다.
JOIN에는 크게 3가지 기법이 있다.

Nested Loop Join

선행 테이블에서 조건에 일치하는 레코드를 후행 테이블에서 반복적으로 탐색하며 조인하는 방식
중첩된 반복문과 유사한 방식으로 조인을 수행한다.

구조

반복문 외부에 있는 테이블은 선행 테이블 (외부 테이블), 반복문 내부에 있는 테이블은 후행 테이블 (내부 테이블)이라고 한다. 선행 테이블의 조건을 만족하는 행을 추출하여 후행 테이블을 읽으면서 조인을 수행하기 때문에 선행 테이블의 조건을 만족하는 모든 행의 수만큼 반복을 수행한다. 그러므로, 결과 행 수가 적은 테이블을 선행 테이블로 선택하는 것이 좋다.

JOIN 과정

선행 테이블에서 조건을 만족하는 행을 스캔한다.
이 때 선행 테이블에서 주어진 조건을 만족하지 않는 경우, 해당 데이터는 필터링
선행 테이블의 조인 키를 가지고 후행 테이블에 조인키가 존재하는지 스캔한다.
-> 조인을 시도 (논리적 판단)
후행 테이블의 인덱스에 선행 테이블의 조인키가 존재하는지 물리적으로 확인한다.
이 때 후행 테이블에 조인키가 존재하지 않으면 해당 선행 테이블 데이터는 필터링
인덱스에서 추출한 레코드 식별자를 이용하여 후행 테이블을 접근한다.
-> 인덱스 스캔을 통해 테이블에 접근하고, 후행 테이블에 주어진 조건까지 모두 만족하면 해당 행을 JOIN하여 추출 버퍼에 넣는다.

코드 예시

SELECT   /*+ USE_NL (B) */
		A.*
        , B.*
FROM    ITEM A, UITEM B
WHERE A.ITEM_ID=B.ITEM_ID --- 1
	AND A.ITEM_TYPE_CD = '100100' --- 2
    AND A.SALE_YN = 'Y'  --- 3
    AND B.SALE_YN = 'Y'  --- 4

▶️ 선행 테이블: ITEM A
A.ITEM_TYPE_CD = '100100'
A.SALE_YN = 'Y'
선행 테이블에서 위의 조건들을 만족하는 row들을 하나씩 스캔 (선행 조건 필터링)

▶️ 2번 단계 (조인 시도)
A.ITEM_ID 가지고 B 테이블과 조인을 시도한다.

▶️ 3번 단계 (후행 인덱스 확인)
B.ITEM_ID에 인덱스가 있다고 가정할 때,
A.ITEM_ID 값으로 B 테이블의 인덱스를 탐색하여 조인키가 있는지 확인하고, 존재하지 않으면 해당 A row 버린다.

▶️ 4번 단계 (테이블 접근 + 조건 확인)
인덱스를 통해 B의 ROW ID 얻고 테이블에서 실제 row 읽는다.
B.SALE_YN = 'Y'라는 후행 테이블의 조건까지 만족하면 최종 결과에 포함한다.

특징

절차적이며, 프로그래밍에서 FOR, WHILE문과 같은 구조로 수행된다. (반복문)
선행 테이블은 Full Scan이 이뤄지기 때문에, 선행 테이블의 크기가 작을수록 유리하다.
후행 테이블에 대해서는 반드시 인덱스가 존재해야 NL 조인이 가능하다.
그렇지 않을 경우, 선행 테이블의 row 마다 후행 테이블을 Full Scan 해야 하기 때문에 성능이 많이 저하될 수 있다.
랜덤 액세스 방식으로 데이터를 읽는다. -> 많은 I/O가 발생할 수 있다.

랜덤 액세스 방식

테이블의 레코드를 순차적으로 읽는 것이 아니라, 레코드 위치와 관계없이 필요한 레코드에 직접 액세스 방식이다.

조건을 만족하는 선행 테이블의 조인키로 후행 테이블의 조인키를 탐색할 때 매번 디스크에서 다른 위치로 점프해서 읽게 된다. HDD 같은 디스크는 점프할 때마다 물리적인 움직임이 필요하기 때문에 랜덤 액세스는 디스크 입장에서 비효율적이다.

Sort Merge Join

조인 컬럼을 기준으로 데이터를 정렬하여 조인을 수행한 후, 두 개의 테이블에서 정렬된 조인 키를 스캔하면서 조인하는 방식

JOIN 과정

선행 테이블에서 주어진 조건을 만족하는 행을 찾는다.
선행 테이블의 조인키를 기준으로 정렬 작업을 수행한다.
후행 테이블에서 주어진 조건을 만족하는 행을 찾는다.
후행 테이블의 조인키를 기준으로 정렬 작업을 수행한다.
정렬된 결과를 이용하여 조인을 수행하며 조인에 성공하면 추출 버퍼에 넣는다.

코드 예시

SELECT  /*+ ORDERED USE_MERGE(B) */
		A.*
        , B.*
FROM  ITEM A, UITEM B
WHERE A.ITEM_ID = B.ITEM_ID   -- 1
  AND A.ITEM_TYPE_CD = '100101'   -- 2
  AND A.SALE_YN = 'Y'  -- 3
  AND B.SALE_YN = 'Y'  -- 4

▶️ 선행 테이블: ITEM A
A.ITEM_TYPE_CD = '100100', A.SALE_YN = 'Y' 의 조건에 맞는 행들을 하나씩 스캔한다.

▶️ 2번 단계
선행 테이블의 조인키를 기준으로 정렬 작업을 수행한다.
선행 테이블인 ITEM A의 조인키인 A.ITEM_ID를 기준으로 정렬 작업을 수행한다.

▶️ 3번 단계
후행 테이블에서 주어진 조건 B.SALE_YN = 'Y'을 만족하는 행을 스캔한다.

▶️ 4번 단계
후행 테이블인 ITEM B의 조인키인 B.ITEM_ID를 기준으로 정렬 작업을 수행한다.

▶️ 5번 단계
정렬된 두 테이블을 병합(Merge)하여 ITEM_ID가 일치하는 행들을 매칭한다.
조인에 성공하면 해당 행들을 추출 버퍼에 넣어 최종 결과에 포함시킨다.

특징

1. NL Join 보완

랜덤 액세스 방식으로 데이터를 읽는 NL Join과 달리 Sort Merge Join은 주로 스캔 방식으로 데이터를 읽는다.
정렬을 진행한 테이블들을 순차적으로 스캔하기 때문에 인덱스를 사용하지 않는다.
각 테이블을 1회씩만 읽기 때문에 테이블의 크기가 클 경우 더 효율적이다.

2. 정렬 비용 발생

정렬할 데이터가 많아 메모리에서 모든 정렬 작업을 수행하기 어려운 경우에는 임시 영역(디스크)을 사용하기 때문에 성능이 떨어질 수 있다.
대용량의 테이블이라면 정렬 작업에 비용 부담이 크기 때문에 조인 컬럼에 인덱스를 생성하여 정렬 작업을 생략하는 것이 좋다.
대량의 조인 작업일 경우, CPU 위주의 작업을 수행하는 Hash Join이 성능상 유리하다.

3. 중복 데이터 제거 필요

중복 값이 아주 많은 경우, 중복 테이블을 제거하기 위해 메모리에서 임시 테이블을 생성하게 되는데 이는 많은 비용이 발생한다.
이를 방지하기 위해 UNIQUE INDEX를 생성하거나, GROUP BY로 묶는 방법 등 중복 데이터 처리가 중요하다.

4. Sort-Merge 조인이 유리한 경우

대부분 해시조인보다 느린 성능을 보이지만, 몇 가지 상황에서는 소트머지 조인이 유용할 수 있다.
- First 테이블에 소트연산을 대체할 인덱스가 있을 때
- 조인할 First 집합이 이미 정렬되어 있을 때
- 조인 조건식이 등차(=)조건이 아닐 때

Hash Join

조인될 두 테이블 중 한 테이블의 조인 컬럼에 Hash Function(해시 함수)를 이용해서 Hash Key(테이블의 인덱스 역할)을 생성한 후, Hash Table(해시 맵)을 생성한다. 조인 키 값을 해시 함수로 비교하여 매치되는 결과값을 얻는 방식

구조

조인 대상의 두 테이블 중 데이터가 더 작은 테이블을 Build Input(빌드 입력)이라 하고, 큰 테이블을 Probe Input(프로브 입력)이라고 한다.

Build Input 테이블의 조인 컬럼에 Hash Function를 이용해서 Hash Key를 생성하고, 해시 테이블을 생성한다. 그리고, Probe Input 테이블의 조인 조건에 맞는 해시 테이블을 탐색하며, 해시 함수를 적용하여 해시 키를 생성하고, 해시 테이블에서 같은 해시 키를 찾아서 조인을 진행하는 방식이다.

JOIN 과정

선행 테이블에서 주어진 조건을 만족하는 행을 찾는다.
선행 테이블의 조인키를 기준으로 해시함수를 적용하여 해시 테이블을 생성한다.
후행 테이블에서 주어진 조건을 만족하는 행을 찾는다.
후행 테이블의 조인 키를 기준으로 해시 함수를 적용하여 해당 버킷을 찾는다.
조인 키를 이용해서 실제 조인될 데이터를 찾는다.
조인에 성공하면 추출 버퍼에 넣는다.

특징

1. NL Join, SM Join 보완

조인 대상 테이블에 인덱스가 없고, 결과를 정렬할 필요가 없을 때 효율적으로 사용할 수 있다.
Merge Join을 하기에는 데이터의 양이 많거나, 중복된 값이 있을 때 해시 조인을 사용하면 속도가 더 빠르다.

2. 해시 조인이 사용될 수 있는 조건

Hash Join은 통계 정보가 있어야 성능을 예측할 수 있기 때문에, 비용 기반 옵티마이저를 사용할 때만 사용될 수 있다.
해시 함수를 이용하여 조인을 수행하기 때문에 동등 조인(=)에서만 사용될 수 있다.

3. Hash Table 사용

해시 테이블은 조인 성능이 빠른 구조로, 데이터를 해시 키로 빠르게 접근할 수 있는 장점이 있다.
하지만, 테이블 하나를 메모리에 적재해서 해시 테이블을 생성해야 하기 때문에 메모리 사용량이 크다.
메모리가 부족할 경우에는 디스크 스필(spill)이 발생할 수 있지만, PGA 메모리(프로세스 전용 메모리)를 활용하여 처리 속도를 보완할 수 있다.
또, 해시 테이블은 생성 후 곧바로 소멸되기 때문에 재활용이 불가능하다.

Index (인덱스)

✏️ 인덱스에 대해 설명해주세요(정의, 사용 목적, 장점, 단점, 구현 방법, 유의점 등)

인덱스란?

인덱스란 데이터베이스 테이블에 대한 검색 성능을 높이기 위해 사용하는 자료구조이다. 특정 컬럼에 인덱스를 생성하면, 해당 컬럼의 데이터를 정렬하여 별도의 메모리에 해당 컬럼의 값과 물리적 주소를 저장한다. 데이터베이스에서 모든 데이터를 탐색하며 원하는 데이터를 찾기에는 시간이 오래 걸릴 수 있기 때문에 인덱스를 사용한다.

📖 비유하자면..

책의 목차 : Index
책의 내용 : 데이터 값
책 페이지 : 물리적 주소

✔️ 원하는 내용을 빠르게 찾기 위해 책의 목차를 보는 것처럼, 데이터베이스에서도 데이터를 찾을 때 인덱스를 사용하여 데이터 검색 속도를 향상시키는 것이다.

인덱스의 장점과 단점

장점

데이터가 정렬되어 있기 때문에 테이블에서 검색과 정렬 속도를 향상시킨다.

조건 검색 WHERE 절 : 보통은 WHERE 절 조건에 맞는 데이터를 찾기 위해 데이터를 처음부터 끝까지 다 비교해야 하는데, 인덱스를 통해 데이터가 정렬되어 있으면 빠르게 찾아낼 수 있다.
정렬 ORDER BY 절 : 인덱스를 사용하면 ORDER BY 의 정렬(sort)과정을 피할 수 있다.
MIN, MAX : 인덱스를 통해 데이터가 정렬되어 있기 때문에 정렬된 데이터에서 효율적으로 최소값, 최대값을 찾을 수 있다.

시스템의 전반적인 부하를 줄일 수 있다.

단점

인덱스를 관리하기 위한 추가 작업이 필요하다.
인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 추가로 필요하다.
인덱스를 잘못 사용할 경우 오히려 성능이 저하된다.

인덱스 관리

DBMS는 인덱스를 항상 최신의 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다. 그렇기 때문에 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행될 때 다음과 같은 연산을 추가적으로 수행해줘야 한다.

INSERT : 새로운 데이터에 대한 인덱스를 추가한다.
DELETE : 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행한다.
UPDATE : 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대한 인덱스를 추가한다.

데이터의 인덱스를 제거하는 것이 아니라 '사용하지 않음'로 처리하고 남겨두기 때문에 수정 작업이 많은 경우 데이터에 비해 인덱스가 과도하게 커져 성능이 오히려 저하될 수 있다.

👍🏼 인덱스를 사용하면 좋은 경우

규모가 큰 테이블
INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼
JOIN이나 WHERE, ORDER BY에 자주 사용되는 컬럼
데이터 중복도가 낮은 컬럼

구현 방법

1. 해시 테이블

인덱스를 구현하기 위해 해시 테이블 자료구조를 사용할 수 있다. 해시 테이블은 (Key, Value) 쌍으로 데이터를 저장하는 자료구조이다. 여기서 (데이터의 실제 값, 물리적 주소)를 key와 value로 저장하고 있다.

데이터의 실제 값이 해싱 함수를 거치고, 이렇게 생성된 것 값이 인덱스가 된다. 이 인덱스의 값에는 그 데이터의 물리적 주소를 가지고 있는 것이다.
해시 테이블의 검색 시간 복잡도는 O(1) 로 아주 빠른 검색을 지원한다.

하지만, 인덱스에서 해시 테이블은 잘 이용되지 않는다.

해싱 함수로 생성된 Key 값으로 매칭되는 인덱스를 찾기 때문에 해시 테이블에서는 등호연산(=)에 특화되었기 때문이다. 값이 조금이라도 달라지면 아예 다른 인덱스 값을 반환한다. 또, 해시 테이블의 데이터는 정렬되어 있지 않다.

부등호 연산(<,>)이 더 많이 일어나는 데이터베이스에서는 적합하지 않다.

2. B-Tree

균형 트리의 일종으로 이진 트리를 확장하여 하나의 노드가 가질 수 있는 자식 노드가 최대 2 이상인 트리 자료구조이다.

B-Tree의 Key 검색

모든 노드에 데이터가 저장되어있다.
Root Node부터 검색을 시작하며, node의 key를 순회하며 일치하는 key가 있다면 검색을 종료한다.
k가 없다면 k 값을 비교하여 자식 Node로 내려간다.

장점

항상 균형 잡힌 트리 구조를 유지하므로, 검색, 삽입, 삭제 연산의 시간 복잡도가 O(log n) 로 일정하다.
트리 내 데이터가 항상 정렬된 상태를 유지하기 때문에 등호 연산, 부등호 연산 모두 처리할 수 있다.

단점

삽입/삭제 등 구조 변경이 자주 일어나는 컬럼에 적용되었다면 균형 트리를 유지하기 위해 구조를 자주 바꾸어야 하기 때문에 성능 저하가 일어날 수 있다.

3. B+Tree

대부분의 DBMS(오라클)에서 사용하고 있는 보편적인 인덱스 자료구조이다. Root Node(기준 노드), Branch Node(중간 노드), Leaf Node(말단 노드) 로 이루어져있으며, 계층 구조를 이루고 있다.

B+Tree의 특징

데이터는 Leaf Node에만 저장되어있다.
나머지 노드에는 데이터를 위한 인덱스(Key)만 갖는다.
Leaf Node들끼리 LinkedList로 연결되어 있어 선형 시간이 소모된다.
Root Node에서 경로를 확인한 후, 그에 맞는 Node들로 이동하고 최종적으로 원하는 데이터가 있는 Leaf Node를 찾게 된다.

장점

Full Scan 시 모든 Node를 탐색해야 하는 B-Tree에 비해, B+Tree는 Leaf Node 들이 LinkedList로 연결되어 있어 선형 시간이 소모된다.
Leaf Node에만 데이터를 저장하기 때문에 메모리 부분에서 효율적이다. B-Tree 노드에서는 Key+데이터를 저장하는 것에 비해, B+Tree는 내부 노드에서는 Key만 저장하고 실제 데이터는 Leaf Node에 저장하기 때문에 더 많은 포인터를 저장할 수 있다. 즉, 트리의 높이가 낮아져 루프에서 리프까지 가는 경로가 짧아진다.

단점

루트 노드에 key가 있을 때, B-Tree는 바로 찾을 수 있지만, B+Tree는 무조건 Leaf Node까지 내려가서 찾아야 한다.

Clustered & Non-Clustered Index

✏️ Clustered Index와 Non-Clustered Index에 대해 설명해주세요

1️⃣ Clustered Index

Clustered Index는 데이터가 테이블에 물리적으로 저장되는 순서를 정의한다. 즉, 클러스터형 인덱스는 특정 컬럼을 기준으로 데이터를 정렬시킨다.

오직 한 가지의 방법으로만 정렬되기 때문에 테이블 당 하나씩만 존재할 수 있다.

primary key 제약조건은 클러스터된 인덱스를 자동으로 생성하기 때문에 일반적으로 특정 컬럼에 PK를 지정했다면, 자료가 자동으로 정렬되는 것이다.

데이터가 삽입되는 순서에 상관없이 Index로 지정된 컬럼을 기준으로 정렬되어 삽입된다.

예를 들어, 인덱스를 기준으로 정렬된 테이블에서 중간에 새로운 데이터가 삽입된다면, 이후 모든 컬럼을 한 칸씩 재배열해줘야 한다.

장점

물리적으로 정렬되어 있어 검색 속도가 Non-Clustered Index보다 더 빠르다.

단점

데이터의 입력/수정/삭제 시에도 정렬을 수행해야 하기 때문에 많은 비용이 소모되고 속도가 느리다는 단점이 있다.

Clustered Index 방식

클러스터형 인덱스는 아래 그림처럼 B+Tree의 형태로 구성되어 있다. 여기서 B+Tree의 노드를 데이터베이스에서는 페이지라고 부른다.

Index Page를 키값과 데이터 페이지 번호로 구성하고, 검색하고자 하는 데이터의 키 값으로 페이지 번호를 검색하여 데이터를 찾는다. 클러스터형 인덱스는 루트 페이지와 리프 페이지로 구성되며, 리프 페이지가 데이터 페이지와 같다.

각 페이지는 고유의 페이지 번호를 가지고 있다. 위 그림은 RollNo 컬럼을 PK로 설정해 클러스터형 인덱스를 생성한 모습이다.

루트 페이지를 보면 Key로는 PK를 가지고 있고, 포인터로는 다른 페이지의 페이지 번호를 가지고 있다. 또, 리프 페이지는 Key로 PK를 가지고 있고, 데이터를 직접 가지고 있는 것을 확인할 수 있다.

Index Page를 키값과 데이터 페이지 번호로 구성하고, 검색하고자 하는 데이터의 키 값으로 페이지 번호를 검색하여 데이터를 찾는다.

어떤 경우에 Clustered Index를 사용할까?

테이블 데이터가 자주 업데이트 되지 않는 경우
항상 정렬된 방식으로 데이터를 반환해야 하는 경우
- ORDER BY 에 자주 사용된다면 이미 정렬되어있기 때문에 모든 데이터를 스캔하지 않고도 빠르게 원하는 데이터를 조회할 수 있다.
읽기 작업이 많은 경우, 빠르다.

2️⃣ Non-Clustered Index

Non-Clustered Index는 테이블에 저장된 물리적인 순서에 따라 데이터를 정렬하지 않는다. 즉, 순서대로 정렬되어 있지 않다.

하나의 테이블에 여러 개의 비클러스터 형 인덱스가 존재할 수 있다.

함부로 남용하면 시스템 성능을 떨어뜨린다.

비클러스터형 인덱스는 테이블 데이터와 함께 테이블에 저장되는 것이 아니라 별도의 장소에 저장된다.

클러스터형 인덱스와 다르게 물리적으로 테이블을 정렬하지 않는다. 그 대신 정렬된 별도의 인덱스 페이지를 생성하고 관리한다.

장점

인덱스 페이지는 정렬되어 있지만, 실제 데이터 페이지는 정렬되지 않으므로 클러스터형 인덱스에 비해 삽입, 수정, 삭제 작업이 비교적 빠르다. 데이터 페이지에는 정렬 순서 상관없이 빈 곳에 데이터를 삽입하면 되기 때문이다.

단점

정렬되어 있지 않기 때문에 Clustered Index보다 검색 속도가 느리다.

Non-Clustered Index 방식

Non-Clustered Index는 데이터 페이지를 건드리지 않고, 별도의 장소에 인덱스 페이지를 생성한다.

출처 : https://hudi.blog/db-clustered-and-non-clustered-index

앞서 말한 것처럼 비클러스터형 인덱스는 인덱스 페이지와 데이터 페이지가 구분되어있다. (Data page != Index Page)

루트 페이지는 클러스터형 인덱스와 비슷하게 인덱스에 대한 컬럼과 페이지 번호를 가지고 있다.

하지만, 리프 노드에서는 인덱스 컬럼과 데이터 페이지 번호 + #오프셋 을 가지고 있어 데이터 페이지의 특정 행을 가리킨다. 즉, 실제 데이터는 저장되어 있지 않고 데이터에 접근하기 위해서는 인덱스 페이지에서 데이터 페이지로 이동해야 한다.

DB Connection Pool

✏️ DB connection pool에 대해 설명해주세요

🔗 Connection

DB Connection 이란 애플리케이션과 데이터베이스 서버가 통신할 수 있도록 하는 기능이다.

Connection 객체 생성 과정

애플리케이션에서 DB 드라이버를 통해 커넥션을 조회한다.

DriverManagr.getConnection(...) 같은 코드로 DB 연결을 요청한다.

DB 드라이버는 DB와 TCP/IP 커넥션을 연결한다.

DB 서버 IP, 포트로 접속을 시도한다.(EX. MySQL 포트번호 : 3306)

DB 드라이버는 TCP/IP 커넥션이 연결되면 아이디와 패스워드 및 초기 접속 설정 정보를 DB에 전달한다. ( CHARSET, TIMEZONE, autocommit 설정 등)
DB는 아이디, 패스워드를 통해 내부 인증을 거친 후 클라이언트 세션을 위한 서버 내부 리소스를 할당해준다.
DB는 커넥션 생성이 완료되었다는 응답을 보낸다.
DB 드라이버는 커넥션 객체를 생성해서 클라이언트에 반환한다.

Connection 객체 생성 이후

Connection 객체 사용
반환된 Connection 객체를 사용하여 DB 관련 작업 (쿼리 실행, 트랜잭션 관리 등)을 수행한다.

연결 종료
DB 작업을 마치면 Connection 객체를 명시적으로 닫아줘야 한다. 리소스 누수를 방지하고 DB 연결을 제대로 해제하기 위함이다.

⛓️‍💥 매번 Connection 객체를 만드는 것의 문제점

🚨 DB 연결을 할 때마다 Connection 객체를 새로 만드는 것은 비용이 많이 들며, 아주 비효율적이다 !

네트워크 비용

새로운 Connection 객체를 생성할 때마다 DB 서버와의 네트워크 연결을 해야 하며, 이 과정은 일정 시간이 소요된다.

리소스 사용

각 Connection 객체는 DB 서버의 연결 세션을 나타낸다. DB 서버는 동시에 처리할 수 있는 연결 세션 수에 제한이 있으며, 무분별한 Connection 생성은 서버 리소스를 소모할 수 있다.

비용적 측면

Connection 객체 생성은 시간과 메모리를 소모한다.

✅ 해결책

애플리케이션 로딩 시점에 Connection 객체를 미리 생성하고, 애플리케이션에서 데이터베이스에 연결이 필요할 경우 미리 준비된 Connection 객체를 사용하여 애플리케이션의 성능을 향상하는 커넥션 풀 (Connection Pool)이 등장하게 된다 !

Connection Pool이란?

데이터베이스와의 연결을 미리 생성해 두고, 애플리케이션에서 필요할 때마다 이를 할당해 사용한 후 다시 풀에 반환하는 방식으로, 데이터베이스 연결의 생성 및 폐기에 따른 오버헤드를 줄여준다. 이 기법은 데이터베이스 서버와의 빈번한 연결 및 해제를 방지하여 시스템의 성능을 높이고, 연결 수에 대한 제어를 통해 자원 고갈을 방지함으로써 안정성을 제공할 수 있다.

Connection Pool 동작 구조

애플리케이션을 시작하는 시점에 필요한만큼 커넥션을 미리 생성하여 보관한다. (default = 10)

커넥션 풀에 들어있는 Connection 객체는 TCP/IP로 DB와 연결되어 있는 상태이기 때문에 바로 SQL을 DB에 날릴 수 있다.

커넥션 풀에 있는 커넥션을 요청하면 커넥션 풀은 자신이 가지고 있는 커넥션 객체 중 하나를 반환한다.

즉, DB 드라이버를 통해 새로운 커넥션을 획득하는 것이 아닌 이미 생성되어 있는 커넥션을 참조하여 사용하게 된다.

Connection Pool 구현

대표적인 커넥션 풀 구현체로는 HikariCP, Apache Commons DBCP 등이 있다. SpringBoot에서는 기본 커넥션 풀로 HikariCP가 사용된다.

Java에서 데이터베이스와 연결하는 방법을 추상화한 DataSource 인터페이스를 구현한 구현체들을 이용할 수 있다.

DataSource

DriverManager

void driverManager() throws SQLException {
	Connection conn1 = DriverManager.getConnection(URL, USERNAME, PASSWORD);
	Connection conn2 = DriverManager.getConnection(URL, USERNAME, PASSWORD);

DriverManager로 새 커넥션을 생성하는 코드는 DataSource를 사용하지 않는다.
DriverManager는 커넥션을 획득할 때마다 DB의 URL, USERNAME, PASSWORD와 같은 접속 정보를 계속 전달해야 한다.

DataSource

void dataSourceConnectionPool() throws SQLException{
	HikariDataSource hikariDataSource = new HikariDataSource();

    hikariDataSource.setJdbcUrl(MysqlDbConnectionConstant.URL);
    hikariDataSource.setUsername(MysqlDbConnectionConstant.USERNAME);
    hikariDataSource.setPassword(MysqlDbConnectionConstant.PASSWORD);

    hikariDataSource.setMaximumPoolSize(5);

    Connection conn1 = hikariDataSource.getConnection();
    Connection conn2 = hikariDataSource.getConnection();
    Connection conn3 = hikariDataSource.getConnection();
}

DataSource를 구현한 구현체들로 커넥션을 만들 경우, 처음 객체를 생성할 때만 연결 정보를 파라미터로 넘겨주고, 커넥션을 획득할 때는 단순히 dataSource.getConnection()만 호출하면 된다.
즉, dataSource를 사용하는 방법은 설정과 사용이 분리되어 있어 향후 변경에 더 유연하게 대처할 수 있다.
이후, Repository에서 private final DataSource dataSource;로 선언해둔 후, 의존관계를 주입 받아서 사용할 수 있다.

➕ 장점

성능 향상
: 미리 연결된 Connection을 Pool에 유지하고, 요청이 들어올 때마다 해당 연결을 재사용함으로써 응답 시간을 단축하고 애플리케이션의 성능을 향상시킨다.

자원 관리
: 연결을 생성하고 유지하는 데 필요한 자원을 최적화한다. 불필요한 연결을 만들지 않고, 연결을 재사용함으로써 메모리와 CPU 등의 자원을 효율적으로 관리할 수 있다.

동시성 관리
: 동시에 여러 요청을 처리할 수 있는 연결을 제공하므로, 다수의 사용자가 동시에 애플리케이션에 접속해도 안정적으로 처리할 수 있다.

연결 풀링
: 연결의 개수를 제한하고, 초과하는 요청이 들어올 경우 대기하도록 함으로써 과부하를 방지한다.

커넥션 오버헤드 감소
: 반복적인 데이터베이스 연결/해제 작업에 따른 오버헤드를 감소시킨다.

➖ 단점

리소스 사용
: 일정 수의 연결을 미리 생성하고 유지하는 데에 메모리 등의 리소스를 일정 부분 소비한다.

커넥션 누수
: 애플리케이션에서 연결을 올바르게 반환하지 않거나 예외가 있는 경우, 커넥션 풀에서 연결이 제대로 반환되지 않아 커넥션 누수가 발생할 수 있다.

트랜잭션

✏️ 트랜잭션에 대해 설명해주세요(정의, 특징, 상태 등)

트랜잭션이란? 🤝

트랜잭션이란 DB의 상태를 변화시키는 하나의 논리적 기능을 수행하기 위한 작업의 단위 또는 한꺼번에 모두 수행되어야 할 일련의 연산들을 의미한다.

트랜잭션 예시 : A가 B에게 10만 원을 송금하려고 한다. (하나의 논리적 기능)
------------------------------------
1. A의 계좌에서 10만 원을 차감한다. (차감 UPDATE)
2. B의 계좌에 10만 원을 입금한다. (입금 UPDATE)
------------------------------------
작업 단위 : 차감 UPDATE + 입금 UPDATE
=> 이 과정 하나를 트랜잭션이라고 한다. 
- 두 개의 쿼리가 모두 성공적으로 끝나야만 하나의 작업이 완료된다.

트랜잭션의 특징

트랜잭션은 ACID의 특징을 가진다.

1️⃣ Atomicity (원자성)

트랜잭션 내의 모든 연산은 하나의 원자적인 단위로 처리되어야 한다. 트랜잭션이 데이터베이스에 모두 반영되거나, 아니면 모두 반영되지 않아야 한다 일부 성공, 일부 취소라는 개념은 없다.

중간에 연산이 실패하더라도, 그 연산만 재시도하는 것이 아니라 트랜잭션 자체를 다시 재실행하거나 트랜잭션 전체를 중단하게 된다.

2️⃣ Consistency (일관성)

트랜잭션이 완료되었을 때, 데이터베이스의 상태는 일관성 있게 유지되어야 한다. 트랜잭션 전후의 데이터베이스는 정의된 모든 규칙들과 제약조건을 만족해야 한다.

3️⃣ Isolation (고립성)

트랜잭션이 동시에 실행되는 상황에서, 트랜잭션이 서로의 작업에 영향을 미치지 않도록 보장하는 원칙으로, 각 트랜잭션은 독립적으로 실행된다. 어떤 트랜잭션이, 다른 트랜잭션 내의 연산에 끼어들 수 없다.

하나의 트랜잭션이 완료될 때까지, 다른 트랜잭션이 그 트랜잭션의 결과를 참조할 수 없다.

4️⃣ Durability (영구성)

트랜잭션이 성공적으로 완료되었을 때, 그 결과는 데이터베이스에 영구적으로 반영되어야 한다는 원칙이다.

트랜잭션의 연산

✅ Commit 연산

Commit 연산은 한 개의 논리적 단위에 대한 작업이 성공적으로 끝났고, 데이터베이스가 다시 일관된 상태에 있을 때, 모든 작업들을 정상 처리하겠다고 확정하는 명령어로서 해당 처리 과정을 데이터베이스에 영구적으로 저장하겠다는 의미이다. Commit을 수행하면 하나의 트랜잭션 과정이 종료된다.

🔁 Rollback 연산

Rollback 연산은 하나의 트랜잭션 처리가 비정상적으로 종료되어 데이터베이스의 일관성을 깨뜨렸을 때, 이 트랜잭션의 일부가 정상적으로 처리되었더라도 트랜잭션의 원자성을 구현하기 위해 이 트랜잭션이 행한 모든 연산을 취소(Undo)하는 연산이다. Rollback 시 트랜잭션은 시작되기 이전의 상태로 되돌아간다.
Rollback 시에는 해당 트랜잭션을 재시작하거나 폐기한다.

🏷 Savepoint 연산

트랜잭션 중간에 되돌릴 수 있는 지점(Rollback할 포인트)를 지정하는 연산이다.
트랜잭션 도중 특정 시점에 이름을 붙이고, 이후 ROLLBACK TO SAVEPOINT를 사용하면 전체가 아닌, 해당 지점까지만 부분 취소가 가능하다.

트랜잭션의 상태

활성(Active)
트랜잭션이 작업을 시작하여 실행 중인 상태

부분 완료(Partially committed)
트랜잭션의 마지막 연산까지 실행하고 commit 요청이 들어온 직후의 상태
최종 결과를 데이터베이스에 아직 반영하지 않은 상태로, Commit이 있을 때까지 기다리는 상태이다.

완료(Committed)
트랜잭션이 성공적으로 종료되어 Commit 연산을 실행한 후의 상태로, 데이터베이스에 영구적으로 변경사항을 저장한 상태이다.

실패(Failed)
트랜잭션에 오류가 발생하여 실행이 중단된 상태

철회(Aborted)
트랜잭션이 비정상적으로 종료되어 Rollback 연산을 수행한 상태

세이브 포인트
ROLLBACK할 포인트를 지정하는 것

트랜잭션을 사용할 때 주의할 점

모든 명령어에 대해 트랜잭션의 롤백 명령이 적용되는 것은 아니다.
DDL문 (CREATE, DROP, ALTER, RENAME, TRUNCATE)은 트랜잭션의 Rollback 대상이 아니다.
트랜잭션은 꼭 필요한 작업만 묶어서 짧게 유지하는 것이 좋다.
데이터베이스 커넥션의 개수가 제한되어 있기 때문에, 각 단위 프로그램이 커넥션을 소유하는 시간이 길어지면 다른 단위 프로그램들이 커넥션을 가져가기 위해 기다려야 하는 상황이 발생할 수도 있다.
=> 따라서 트랜잭션 범위를 최소화해 커넥션 사용 시간을 줄이는 것이 효율적이다.

트랜잭션 격리 수준

✏️ 트랜잭션 격리 수준과 격리 수준에 따라 발생할 수 있는 문제에 대해 설명해주세요

트랜잭션 격리 수준이란?

여러 트랜잭션이 동시에 처리될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회되는 데이터를 볼 수 있게 허용할지 여부를 결정하는 것이다. READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE 이 있다.

READ UNCOMMITTED ➡️ SERIALIZABLE 로 갈수록 격리 수준이 높아진다.

1️⃣ READ UNCOMMITTED

트랜잭션의 변경 내용의 COMMIT과 ROLLBACK 연산 여부와 상관없이 다른 트랜잭션의 값을 읽을 수 있다.

RDBMS 표준에서 인정하지 않을 정도로 데이터의 정합성에 문제가 많은 격리 수준이기 때문에 사용하지 않는 것을 권장한다.

🚨 DIRTY READ

트랜잭션의 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있게 되는 현상

예시 설명

사용자 A가 id=51의 데이터에 Martin 데이터를 추가한다. (아직 COMMIT 이전)
사용자 B가 id=51의 데이터를 조회한다. -> 결과 Martin
☑️ 아직 COMMIT되지 않은 (DB에 영구적으로 저장되지 않은) 데이터를 읽었다.
사용자 A가 변경사항을 ROLLBACK한다.
사용자 B가 다시 id=51의 데이터를 조회한다. -> 결과 없음
사용자 B의 트랜잭션은 id=51의 데이터를 계속 처리하고 있을 텐데, 다시 데이터를 조회하니 결과가 존재하지 않는 상황이 생긴다.

2️⃣ READ COMMITTED

COMMIT된 데이터만 읽을 수 있다.

Oracle, SQL Server, PostgreSQL 등에서 기본적으로 사용하고 있는 격리 수준이다.
Dirty Read가 발생하지 않는다.

🚨 NON-REPEATABLE READ

하나의 트랜잭션에서 동일한 SELECT 문을 실행했을 때 항상 같은 결과를 보장해야 한다는 REPEATABLE READ의 정합성에 어긋나는 것이다. 즉, 기존에 조회한 동일한 레코드의 값이 바뀌는 경우를 말한다.

예시 설명

사용자 B가 Minkyu라는 이름의 데이터를 조회한다. --> 결과 X
사용자 A가 Mangkyu → Minkyu 로 데이터를 수정하고 COMMIT 한다.
다시 사용자 B가 Minkyu 라는 이름의 데이터를 조회한다. --> 결과 O

READ COMMITTED 격리조건에서는 커밋된 데이터는 읽을 수 있기 때문에 수정된 데이터를 읽는다.

🫨 READ COMMITTED에서 반복 읽기를 수행하면 다른 트랜잭션의 커밋 여부에 따라 조회 결과가 달라질 수 있다. 이러한 데이터 부정합 문제를 NON-REPEATABLE READ 라고 한다.

3️⃣ REPEATABLE READ

해당 트랜잭션이 생성되기 이전의 트랜잭션에서 COMMIT된 데이터만 읽는다.

MySQL과 MariaDB에서 기본으로 사용하는 격리 수준이다.
MVCC를 이용해 한 트랜잭션 내에서 동일한 결과를 보장하지만, 새로운 레코드가 추가되는 경우에 부정합이 생길 수 있다.

MVCC (Multi-Version Concurrency Control)

일반적으로 RDBMS는 변경 전의 레코드를 UNDO 공간에 백업해둔다. 그러면 변경 전/후의 데이터가 모두 존재하므로, 동일한 레코드에 대한 여러 버전이 존재한다고 하여 이를 MVCC(Multi-Version Concurrency Control)라고 한다.

MVCC를 통해 트랜잭션이 롤백이 된 경우 이전 데이터를 복원할 수 있고, 서로 다른 트랜잭션 간에 접근할 수 있는 데이터를 세밀하게 제어할 수 있다.

작동 방식

스냅샷을 통해 하나의 레코드에 대해 여러 버전을 관리한다. 각각의 트랜잭션은 순차 증가하는 고유한 번호를 가지며, UNDO 로그에는 어느 트랜잭션에 의해 백업되었는지 트랜잭션 번호가 함께 저장한다. 해당 데이터가 불필요하다고 판단되면 주기적으로 백그라운드 쓰레드를 통해 삭제된다.

예시 설명

사용자 B (트랜잭션 10) 가 id>=50의 데이터를 조회한다. -> 결과 Mangkyu
사용자 A (트랜잭션 12) 가 id=50의 데이터를 Mangkyu → Minkyu 로 수정하고 COMMIT 한다.

UNDO 로그에 이전 값인 Mangkyu 가 트랜잭션 번호와 함께 백업된다.

사용자 B (트랜잭션 10) 가 다시 id>=50의 데이터를 조회한다. -> 결과 Mangkyu

트랜잭션 10이 트랜잭션 12보다 먼저 생성된 트랜잭션이기 때문에 조회 결과로 기존과 동일한 데이터를 얻게 되는 것이다.

✅ REPEATABLE READ는 트랜잭션 번호를 참고하여 자신보다 먼저 생성된 트랜잭션의 데이터만을 조회한다. 만약, 테이블에 자신보다 이후에 생성된 트랜잭션의 데이터가 존재한다면 UNDO 로그를 참고해서 데이터를 조회한다.

🚨 PHANTOM READ

다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 안 보였다가 보였다가 하는 현상이다. 즉, 처음에는 없었던 새로운 레코드가 생겨 조회 결과에 추가되는 현상이다. (같은 조건으로 조회했는데 행의 개수 자체가 달라진다.)

MVCC 덕분에 일반적인 조회에서 유령 읽기(Phantom Read)는 발생하지 않는다. 하지만, 잠금(Lock)이 사용되는 경우 Phantom Read가 발생한다.

🔒 잠금 있는 SELECT (SELECT ... FOR UPDATE/SHARE)

SELECT ... FOR UPDATE : 배타적 잠금 (쓰기 잠금)을 걸고 조회
SELECT ... FOR SHARE : 읽기 잠금을 걸고 조회

락은 트랜잭션이 COMMIT 또는 ROLLBACK 될 때 해제된다.

일반적인 RDBMS

예시 설명

사용자 B(T-ID=10)가 id>=50의 데이터를 조회한다. -> 결과 1건 (Mangkyu)
🔒 하지만, 이번에는 SELECT FOR UPDATE 를 이용해 쓰기 잠금을 걸었다.
사용자 A(T-ID=12)가 id=51의 Martin 데이터를 추가하고 COMMIT 한다.

일반적인 DBMS에는 갭락이 존재하지 않으므로 id=50인 레코드에만 잠금이 걸린 상태이다.
즉, 사용자 A의 요청은 잠금 없이 즉시 실행된다.

다시 사용자 B(T-ID=10)가 id>=50의 데이터를 쓰기 잠금 쿼리로 조회한다. -> 결과 2건(Mangkyu, Martin)

❓ 왜 이후에 생성된 트랜잭션의 변경 사항을 읽은 걸까?

위의 경우도 MVCC를 통해 해결될 것 같지만, 두 번째 SELECT ... FOR UPDATE 문 때문에 그럴 수 없다.

잠금있는 읽기는 테이블에 변경이 일어나지 않도록 테이블에 잠금을 걸고 테이블에서 데이터를 조회한다. 하지만, UNDO 로그는 append-only 형태로, 잠금을 걸 수 없다. 따라서 잠금 없는 경우처럼 UNDO 로그를 바라보고 UNDO 로그에 잠금을 거는 것은 불가능하다.

따라서 잠금 있는 읽기로 레코드를 조회할 때는 UNDO 영역이 아니라 테이블에서 데이터를 읽어오게 되고, 이로 인해 Phantom Read가 발생하는 것이다.

위의 경우 두 번째 SELECT ... FOR UPDATE 문에서도 잠금 있는 읽기이기 때문에 id=50까지 존재하는 UNDO 로그가 아니라 변경사항이 반영된 테이블에서 데이터를 읽어온 것이다.

👻 동일한 트랜잭션 내에서도 새로운 레코드가 추가되는 경우에 조회 결과가 달라지는데, 이렇듯 다른 트랜잭션에서 수행한 작업에 의해 레코드가 안 보였다 보였다 하는 현상을 Phantom Read라고 한다. 이는 다른 트랜잭션에서 새로운 레코드를 추가하거나 삭제하는 경우 발생할 수 있다.

MySQL

MySQL에서는 갭 락이 존재하기 때문에 위의 상황에서 문제가 발생하지 않는다.

🔒 레코드 락? 갭 락? 넥스트 키 락?

레코드 락 (Record Lock) : 이미 존재하는 특정 레코드(Row)에 걸리는 잠금

갭 락 (Gap Lock) : 레코드 사이의 빈 공간(갭)에 걸리는 잠금으로, 레코드 사이에 새로운 레코드의 삽입을 방지하기 위한 락이다.

넥스트 키 락 (Next-Key Lock) : 레코드 락 + 갭 락을 합친 형태로 특정 레코드와 그 다음 레코드와 그 사이의 갭 락을 의미한다.

예시 설명

사용자 B(T-ID=10)가 id>=50의 데이터를 쓰기 잠금으로 조회한다. -> 결과 1건 (Mangkyu)

🔒 MySQL은 id=50인 레코드에는 레코드 락, id>50의 범위에는 갭 락으로 넥스트 키 락을 건다.

사용자 A(T-ID=12)가 id=51의 Martin 데이터를 추가하려고 한다.

하지만, id>50의 범위에 락이 걸려있으므로, A는 B의 트랜잭션이 종료될 때까지 기다려야 한다.

다시 사용자 B가 id>=50의 데이터를 쓰기 잠금 쿼리로 조회한다. -> 결과 1건 (Mangkyu)
사용자 B가 COMMIT 한다.
사용자 B의 트랜잭션이 종료되었기 때문에 A의 트랜잭션도 COMMIT된다.

트랜잭션이 종료되기를 기다리는 대기 시간이 지나치게 길어지면 락 타임아웃이 발생할 수 있다.

👻❌ MySQL에는 갭 락이 존재하기 때문에 일반적으로 Phantom Read가 발생하지 않는다.
다만, 아래의 상황에서는 MySQL에서도 Phantom Read가 발생할 수 있다.

예시 설명

사용자 B(T-ID=10)가 id>=50의 데이터를 조회한다. -> 결과 1건 (Mangkyu)
사용자 A(T-ID=12)가 id=51의 Martin 데이터를 추가하고 COMMIT 한다.

id>=50의 범위에 넥스트 키 락이 걸려있지 않기 때문에 COMMIT이 가능하다.

이번엔 사용자 B가 id>=50의 데이터를 쓰기 잠금 쿼리로 조회한다. -> 결과 2건 (Mangkyu, Martin)

UNDO 로그가 아닌 테이블에서 레코드를 조회하기 때문에 Phantom Read가 발생한다.

하지만, 이런 상황은 거의 존재하지 않기 때문에 MySQL의 REPEATABLE READ에서는 Phantom Read가 발생하지 않는다고 봐도 된다.

4️⃣ SERIALIZABLE

가장 높은 수준의 격리 수준으로, 트랜잭션을 순차적으로 진행한다. 여러 트랜잭션이 동일한 레코드에 동시 접근할 수 없으므로, 어떠한 데이터 부정합 문제도 발생하지 않는다.

SERIALIZABLE은 순수한 SELECT 작업에서도 대상 레코드에 넥스트 키 락을 읽기 잠금으로 걸어 다른 트랜잭션에서 절대 추가/수정/삭제할 수 없게 한다.

🚨 성능 문제

가장 안전하지만 트랜잭션이 순차적으로 처리되어야 하므로 가장 성능이 떨어진다. 따라서 극단적으로 안전한 작업이 필요한 경우가 아니면 사용해서는 안된다.

트랜잭션 격리 수준에 따른 동시성과 일관성의 상관관계

격리 수준이 높아질수록 데이터의 일관성은 잘 유지되지만, 동시에 처리가능한 트랜잭션의 양은 줄어들게 된다. 반대로, 격리 수준이 낮아지면 데이터의 일관성은 유지되기 어렵지만, 동시에 처리할 수 있는 트랜잭션의 양은 늘어난다.

2rlokr

이전 포스트

[CS/DB] 스터디 week4

다음 포스트