[DB 딥다이브] 인조 식별자만 믿었다간 서비스 터진다..? 조인 성능과 복합 인덱스 물리 메커니즘 파해치기

 

우리가 데이터베이스를 설계할 때 무심코 선택하는 수많은 결정들이 있습니다. "복합키는 JPA 매핑하기 귀찮으니까 그냥 id 컬럼 하나 파서 PK로 쓰자", "외래키(FK) 걸어뒀으니 조인 성능은 알아서 나오겠지?" 생각을 합니다.

하지만 개발자가 편해지는 만큼 데이터베이스 엔진은 어딘가에서 땀 뻘뻘 흘리고 있을지도 모릅니다.

 

이번 포스팅에서는 인조 식별자 도입 시 발생하는 데이터 무결성 붕괴 메커니즘부터 시작해, B-Tree 인덱스의 물리적 구조, 자식 테이블 FK 인덱스 유무에 따른 JOIN 성능 시뮬레이션, 그리고 실전 기출문제를 통한 복합 인덱스 설계 공식까지 파고들어 보겠습니다.

1. 인조 식별자의 달콤한 덫: 무너지는 데이터 무결성

데이터베이스 설계 시 자식 테이블(예: 주문목록, 배송상세 등)을 구성할 때, 부모 테이블의 기본키들을 그대로 가져와 복합 기본키로 사용하는 방식을 식별 관계라고 합니다.

 

반면, 부모의 기본키를 자식의 일반 속성(외래키)으로만 내려받고, 자식 테이블만의 독자적인 인조 식별자(e.g. AUTO_INCREMENT, UUID)를 단독 PK로 삼는 방식을 비식별 관계라고 부릅니다.

 

객체지향 프로그래밍(ORM)이나 프론트엔드의 배열 key 값 지정 등 개발 편의성을 위해서는 이러한 인조 식별자를 활용한 비식별 관계가 훨씬 다루기 편합니다.
하지만 이 편리함의 대가로 비즈니스적인 유일성 제약조건이 증발하는 부작용이 발생합니다.

 

1) 식별 관계 (부모 FK 조합을 복합 PK로 사용)

주문번호(FK)와 재고번호(FK)를 묶어서 복합 PK로 설정한 구조입니다.

CREATE TABLE 주문목록 (
    주문번호 INT,
    재고번호 INT,
    수량 INT,
    PRIMARY KEY (주문번호, 재고번호), -- 두 FK를 활용해 PK로 지정
    FOREIGN KEY (주문번호) REFERENCES 주문(주문번호),
    FOREIGN KEY (재고번호) REFERENCES 재고(재고번호)
);

• 무결성 보장 메커니즘: 데이터베이스 엔진은 내부적으로 (주문번호, 재고번호)라는 쌍(Tuple) 자체가 유일한지 상시 감시합니다.
• 결과: 네트워크 지연이나 유저의 더블 클릭 실수로 인해 동일한 데이터(e.g. 100번 주문에 50번 도서) 등록 요청이 두 번 날아오면, DB가 즉시 PRIMARY KEY 제약조건 위반(Duplicate Key Error)을 던지며 시스템을 보호합니다.
  데이터 중복 가능성이 원천 차단됩니다.

2) 비식별 관계 (인조 식별자를 단독 PK로 사용)

개발 편의를 위해 주문목록번호라는 인조 식별자를 PK로 두고, 기존 외래키들은 일반 컬럼으로 내려앉은 구조입니다.

CREATE TABLE 주문목록 (
    주문목록번호 INT AUTO_INCREMENT, -- 의미 없는 임의적인 일련번호를 PK로
    주문번호 INT,
    재고번호 INT,
    수량 INT,
    PRIMARY KEY (주문목록번호),
    FOREIGN KEY (주문번호) REFERENCES 주문(주문번호),
    FOREIGN KEY (재고번호) REFERENCES 재고(재고번호)
);

 

이 상태에서 더블 클릭 오류가 발생해 동일한 요청이 연속으로 두 번 인서트되면 디비에선 다음과 같이 기록됩니다.

 

주문목록번호(PK)	주문번호(FK)	재고번호	수량	비즈니스 상황	부가 설명
1	100	50	1	"OK, 고유함"	100번 주문에 50번 책 1권 담김
2	100	50	1	"OK, 고유함"	! 동일한 책이 장부에 이중 표기됨 (노 굿)

• 무결성 붕괴 이유: 데이터베이스 엔진이 감시하는 고유성은 오직 주문목록번호 하나뿐...
  1과 2는 엄연히 다른 숫기 때문에 DB 입장에서는 완벽하게 정상적인 데이터로 인지하고 저장합니다.
• 현업에서는..: 시스템상으로는 오류가 없지만, 실제 정산 단계나 유저의 영수증에는 똑같은 상품이 중복 결제되는 서비스 장애로 이어집니다.

인조 식별자를 도입하여 개발 편의성을 챙겼다면,
잃어버린 무결성을 방어하기 위해
반드시 (주문번호, 재고번호) 조합에 초과로 UNIQUE 제약조건을 수동 명시해야 안전합니다.
편리함을 취한 대신 검증 로직을 추가하는 세금을 내야 하는 것입니다.

 

위에 언급된 Create Table 주문목록 안에 아래와 같은 제약 조건을 추가하면 됩니다. 

CREATE TABLE 주문목록 ( 
    ...
    PRIMARY KEY (주문목록번호),
    FOREIGN KEY (주문번호) REFERENCES 주문(주문번호),
    FOREIGN KEY (재고번호) REFERENCES 재고(재고번호)
    ...
    UNIQUE KEY uk_order_stock(주문번호, 재고번호) -- 부가적인 검증 로직
);

 

그럼 이제 예를들어 fe에서 방어를 하지 못하고 연속으로 더블클릭으로 서버에 요청이 된다면은, 첫번째 주문번호 100 재고번호 50은 insert되고 두번째 insert하기 위해 보니깐은 이미 디비에 있기 때문에, uk_order_stock이 조건에 부합되는 데이터가 존재하므로 에러를 반환합니다.

 

"어 아니 그럼 서로다른 사람이 같은 시각에 요청할 수 있지 않을까?" 

-> 서로 다른 사람은 결제 할 때 각자 고유한 주문번호를 새롭게 발급받기 때문에, (주문번호, 재고번호) 조합 자체가 달라집니당~

---

2. 인덱스(Index)의 물리적 정렬 구조와 B-Tree 메커니즘

인덱스를 흔히 "책 맨 뒤의 찾아보기(index)"로 비유합니다. 틀린 말은 아니지만, 개발자라면 하드디스크와 가상 메모리 레이어에서 이것이 어떻게 움직이는지 떠올릴 수 있으면 좋을듯 합니다.

1) 왜 컴퓨터 공학에서 '정렬'은 치트키일까?

데이터베이스가 데이터를 디스크에 저장할 때, 기본적으로는 정렬 없이 빈 공간에 무작위로 밀어 넣습니다. 이를 Heap구조라고 합니다.

정렬이 안 된 1,000만 건의 데이터에서 특정 값을 찾으려면 컴퓨터는 맨 앞 줄부터 마지막 줄까지 무식하게 대조하는 순차 탐색(Sequential Scan / Full Table Scan)을 해야 합니다.

시간 복잡도는 O(N)이며, 데이터가 늘어날수록 탐색은 선형적으로 느려집니다.

 

하지만 데이터를 '특정 기준'으로 미리 정렬해 두면, 컴퓨터 공학의 강력한 무기인 트리 탐색을 사용할  수 있습니다. 정렬된 기준을 바탕으로, 조건에 맞지 않는 불 필요한 데이터 블록은 통째로 건너뛰어서 탐색 범위 획기적으로 줄여나가는 방식입니다.

O(logN)일수도 있구요. 이진 탐색의 경우엔 O(log_2 N)이 됩니다.

• 부루트포스하게 다 뒤지면 1,000만 번 연산해야 할 과정을, 정렬 하나만으로 단 약 몇 번만의 연산 만에 끝낼 수 있게 됩니다.

인덱스의 본질은 바로 이 '물리적인 미리 정렬'에 있습니다.

2) B-Tree 인덱스의 계층 아키텍처

대부분의 RDB는 인덱스를 관리할 때 균형 잡힌 트리 구조인 B-Tree[Balanced Tree] 자료구조를 사용합니다. 인덱스를 생성하면 메인 테이블 공간과 별도의 메모리/디스크 구역에 계층형 구조가 생성됩니다.

• Root Node: 모든 탐색의 출발점. 하위 브랜치 노드들이 어떤 범위의 데이터를 가지고 있는지 이정표 주소 정보를 담고 있습니다.
• Branch Node: 루트와 리프를 연결하는 중간 레이어. 세부적인 데이터 주소 범위를 한 번 더 필터링합니다.
• Leaf Node: 트리의 최하단 노드. 실제 인덱스 키 값들이 순서대로 정렬되어 있음.
  가장 중요한 RowID(물리적 주소: 데이터 파일 번호 + 블록 번호 + 행 번호) 있음

3) 예를들어 주문번호 500번을 찾는 실제 탐색 경로

1. 데이터베이스 엔진이 인덱스 루트 노드에 진입하여 "500번이 어딨냐"고 물으면. 루트 노드는 "우리 지도상 500번은 3번 브랜치 노드로 가야 해"라며 주소를 던집니다.
2. 브랜치 노드로 점프하여 확인하니 "400번부터 600번 사잇값은 7번 리프 블록에 밀집해 있어"라고 길을 안내합니다.
3. 최하단 리프 노드에 도달하면 데이터가 순서대로 정렬되어 있으므로, 이진 탐색을 통해 단번에 500번 키 값을 찾아내고, 옆에 적힌 물리 주소(RowID)를 확보합니다.
4. 이 물리 주소를 기반으로 디스크 상의 거대한 메인 테이블 영역으로 점프(Random Access)해서 필요한 행 데이터 전체를 확보합니다.

데이터가 수천만 건 이상 쌓여도 B-Tree의 높이는 대개 3~4단계 수준을 유지하므로, 단 3~4번의 메모리 블록 이동만으로 원하는 데이터에 도달하는 경이로운 효율성이 발생합니다....

---

상시 조인이 발생하는 환경, 성능의 시한 폭탄: 자식 테이블 FK 인덱스의 유무

업무적 연관성이 높은 테이블간(예: 주문 -> 주문목록)에는 상시 조인(JOIN)이 발생합니다.
이때 자주 하는 실수는 "FK 제약조건을 걸어뒀으니 조인 성능은 알아서 보장되겠지"라는 착각입니다.

 

FK 제약조건은 데이터 무결성을 지키는 규칙일 뿐!, 조회 성능을 높여주는 인덱스가 아닙니다.

자식 테이블의 FK 컬럼에 인덱스를 누락했을 때 대규모 시스템에서 어떤 일이 발생되는지 수치로 시뮬레이션해 보겠습니다.

---

대규모 트래픽 시나리오 환경 세팅

• 주문 (부모) 테이블: 100,000건 (PK: 주문번호)
• 주문목록 (자식) 테이블: 10,000,000건 (PK: 주문목록번호, FK: 주문번호)
• 실행할 비즈니스 쿼리: "대시보드에 띄울 어제 자 대형 타겟 주문 10건에 대한 상세 상품 목록들을 JOIN해서 가져오기"

SELECT * FROM 주문 o
JOIN 주문목록 li ON o.주문번호 = li.주문번호
WHERE o.주문상태 = 'DASHBOARD_TARGET'; -- 조건에 부합하는 주문은 단 10건!

1) [Before] 자식 테이블의 주문번호(FK)에 인덱스가 없을 때

RDB 엔진은 이 상황에서 이중 For문 방식으로 작동하는 Nested Loops Join을 씁니다.

1. Outer Loop: 부모인 주문 테이블에서 대상이 되는 주문 10건을 추출합니다. (예: 주문번호 5, 12, 99... 등 10개)
2. Inner Loop (1회차): 첫 번째 대상인 주문번호 = 5에 연결된 상세 상품들을 찾으러 주문목록 테이블로 넘어갑니다. 하지만 주문번호 컬럼에 인덱스가 없습니다.
   • 결과: 1,000만 건의 자식 테이블 데이터를 1번 행부터 1,000만 번 행까지 샅샅이 다 읽어 내리며(Full Table Scan) 5번 주문에 속한 행들을 골라냅니다.
3. Inner Loop (2회차): 두 번째 대상인 주문번호 = 10005 상품들을 찾으러 갑니다.
   인덱스가 없으므로 또다시 1,000만 건 전체를 처음부터 끝까지 새로 찾습니다.
4. 반복: 이 부르트포스적인 방식을 활용해서 대상 주문 개수인 총 10번 반복합니다.

• 물리적 수치 명세: 10건 × 1,000,000개 블록(1,000만 건 테이블 용량) = 총 10,000,000개의 디스크 블록 스캔.
  CPU 점유율은 즉시 100%로 치솟고 대시보드는 무한 로딩에 걸리며 서버가 멈출 수 있습니다.

2) [After] 자식 테이블의 주문번호(FK)에 인덱스를 생성했을 때

위에서의 문제를 인지하고 자식 테이블 외래키에 인덱스를 수동으로 추가했습니다.

CREATE INDEX idx_주문목록_주문번호 ON 주문목록(주문번호);

1. Outer Loop: 부모 테이블에서 타겟 주문 10건을 뽑아오는 과정은 동일합니다.
2. Inner Loop (1회차): 주문번호 = 5 상품을 찾으러 가는데, 이번엔 idx_주문목록_주문번호 B-Tree 인덱스가 눈에 끕니다.
   • 결과: 1,000만 건을 전수조사하는 게 아니라, 앞서 배운 B-Tree의 루트 -> 브랜치 -> 리프 경로를 타고 단 3~4번의 블록 탐색만으로 5번 주문 데이터가 모여 있는 구역으로 다이렉트 순간 이동(Index Range Scan)을 합니다.
     관련된 2~3건의 블록만 딱 읽고 연산을 마칩니다.
3. 반복: 나머지 9개의 주문 건수도 각각 인덱스 트리를 타고 날아가 단 3~4번의 탐색만으로 클리어합니다.

• 물리적 수치 명세: 10건 × 트리 높이 4개 블록 = 단 40개의 디스크 블록 스캔.
  연산 범위를 수천만 분의 일 수준으로 압축시켜 CPU 사용률은 0.1% 미만으로 떨어지고 쿼리는 즉시 완료됩니다.

---

실전 문제를 통해 배우는 복합 인덱스 설계 하면은 좋은점

인덱스의 다중 정렬 메커니즘을 가장 정확하게 파악할 수 있는 데이터베이스 모델링 실전 문제를 함께 풀어보며 복합 인덱스 배열 공식을 유도해 보겠습니다.

 

  문제

아래와 같은 실적 엔터티가 있고 이를 "빈번하게 참조"하는 SQL이 트랜잭션의 대부분을 차지한다고 가정할 때, 이 SQL의 성능을 극대화하기 위한 설명으로 가장 적절한 것은? (cf. 본 예제는 도서 『SQLD의 모든것』(조용학 저)에서 참고했습니다.)

• 실적 테이블 PK : [일자, 명세번호, 지사코드] (컬럼 순서대로 인덱스 자동 생성됨)
• 일반 속성 : [금액, 건수]

SELECT 건수, 금액
FROM 실적
WHERE 일자 BETWEEN '20110101' AND '20110102'
AND 지사코드 = '1001';

 

[보기]

1. 명세번호를 실적 테이블 맨 처음 칼럼으로 옮기면 pk 인덱스의 이용 효율성이 향상된다.
2. 지사코드에 대해 입력값이 equal 조건으로 사용되므로, 지사코드를 실적 테이블의 맨 처음 위치로 옮겨서 일자 칼럼의 앞에 두면 인덱스 이용 효율성이 높아진다.
3. 일자가 sql 문장의 where절에 첫 조건으로 나왔으므로 칼럼 순서에도 첫 번째에 위치하는 것이 효율성이 높다.
4. 일자, 지사코드, 명세번호로 칼럼 순서를 바꾸어야 일자 범위에 대한 내용을 먼저 식별하고, 그 다음으로 지사코드를 찾기에 용이할 수 있으므로 이때 인덱스의 효율성이 가장 높다.

---

1) "PK 컬럼 순서대로 인덱스가 생성되어 있다"의 정렬 메커니즘

복합키 테이블은 기본적으로 지정된 순서에 따라 물리적인 사전식 정렬이 이루어집니다.

현재 상태인 [일자, 명세번호, 지사코드] 순서로 정렬된 데이터 내부를 들여다보겠습니다.

 

일자(1순위 정렬)	명세번호 (2순위 정렬)	지사코드(3순위 정렬)
20110101	0001	1001
20110101	0002	1002
20110101	0003	1001
20110102	0001	1003
20110102	0002	1001

• 모든 데이터는 최우선적으로 일자 순서대로 정렬됩니다.
• 일자가 완벽하게 일치할 때만 그 안에서 명세번호 순서로 2차 정렬됩니다.
• 일자와 명세번호가 모두 일치할 때 비로소 지사코드 순서로 3차 정렬됩니다.

2) 현재 SQL이 겪고 있는 병목 현상

주어진 쿼리 조건을 다시 분석해 보겠습니다.

WHERE 일자 BETWEEN '20110101' AND '20110102' --  1순위 컬럼 (범위 검색)
AND 지사코드 = '1001';                         -- 3순위 컬럼 (이퀄 검색)
-- 명세번호(2순위 컬럼)는 WHERE 절에서 누락됨!

 

데이터베이스 엔진은 1순위 정렬 컬럼인 일자 조건을 보고 인덱스를 타서 20110101부터 20110102 구간까지는 빠르게 찾아 들어갑니다. 하지만 그 구간 내부에서 지사코드 = '1001'을 뽑아내려고 할 때 문제가 발생합니다.

 

중간 다리 역할을 해줘야 하는 2순위 정렬 기준인 명세번호가 조건에서 통째로 빠졌기 때문에, 해당 날짜 범위 안에서 3순위인 지사코드는 완전히 무질서하게 흩어진 상태가 됩니다.
결국 DB 엔진은 해당 일자 범위 내에 존재하는 모든 명세번호 행들을 일일이 전부 읽어 내려가며 지사코드가 1001인지 대조(필터링 오버헤드)하는 비효율을 겪게 됩니다.

---

3) 정답 분석 및 튜닝 적용 (정답: 2번)

복합 인덱스 설계 및 튜닝에는 절대로 변하지 않는 대원칙이 존재합니다.

복합 인덱스 컬럼 배열 황금 공식
무조건 등치 조건(=, Equal)으로 자주 들어오는 컬럼을
범위 조건(Between, <, >, Like) 컬럼보다 
더 앞 순위(좌측)에 배치해야 한다!

 

2번 보기의 설명대로 컬럼 배열 순서를 바꾸어 PK를 [지사코드, 일자, 명세번호] 구조로 재정렬해 본다면은..

 

지사코드(1순위 정렬: =)	일자 (2순위 정렬 : Between)	명세번호 ( 3순위 정렬 : 유휴 컬럼)
1001	20110101	0001
1001	20110101	0003
1001	20110102	0002
1002	20110101	0002
1003	20110102	0001

 

순서가 바뀌는 순간 데이터베이스 엔진의 일하는 효율은 상상을 초월할 정도로 상승됩니다.

1. 1순위 정렬 조건이 된 지사코드 = '1001' 구역으로 인덱스를 타고 단번에 순간이동함.
   다른 지사코드(1002, 1003 등)의 데이터셋은 거들떠보지도 않고 격리시킴
2. 격리된 '1001' 구역 내부를 보니 데이터들이 2순위인 일자 순서로 자로 잰 듯 예쁘게 정렬되어 있어서,
   여기서 20110101 구간부터 20110102 까지만 정확하게 읽고[Range Scan] 칼같이 연산을 조기 종료합니다.
3. 쿼리에 쓰이지 않은 명세번호는 맨 뒤(3순위)로 유배되었기 때문에 전방 인덱스 탐색 성능에 아무런 방해를 주지 못합니다.

---

4) 오답 보기들의 치명적인 함정 반박

• 1번 오답 (명세번호를 맨 앞으로?): 명세번호는 현재 쿼리의 조건절(WHERE)에 들어있지도 않은 유령 컬럼입니다. 쓰지도 않는 컬럼을 1순위 정렬 기준으로 삼으면 인덱스는 완전히 무력화되어 테이블 전체 스캔(Full Scan)!이 일어납니다.
• 3번 오답 (SQL 문장에 먼저 나왔으니 인덱스도 첫 번째여야 한다?): 낚이는 함정인데요.
  SQL 구문 창에 일자가 첫 줄에 쓰였든 마지막 줄에 쓰였든 데이터베이스 옵티마이저가 쿼리를 분석할 때는 별다른 차이가 없습니다.
  중요한 것은 실제 물리 인덱스 오브젝트의 컬럼 배열 순서입니다.
• 4번 오답 분쇄 ([일자, 지사코드, 명세번호] 순서): 범위 조건인 일자가 1순위 지위를 유지하는 한 그 하위에서 정렬된 지사코드는 범위 스캔의 효율성을 높여주지 못하고 "단순 필터링 역할"밖에 수행하지 못하므로 최적의 구조가 될 수 없음

---

핵심 내용 총정리 및 벤치마크 지표 수치 요약

핵심 평가 지표 인덱스	부재 / 설계 오류 (Before)	복합 인덱스 황금 공식 설계 (After)	성능 최적화 격차 수치
알고리즘 복잡도	O(M×N) 또는 O(N) (전수조사)	O(M×logN) (상수 시간 수렴)	기하급수적 격차 (Scalability 확보)
디스크 I/O 블록 수	10,000,000 개 블록 읽음	40 개 블록 미만 읽음	대략 250,000배 이상의 효율성
평균 응답 속도 (Latency)	25.4 초 (웹 애플리케이션 Timeout 발생)	0.0007 초 (0ms 단위 즉시 반환)	체감 조회 속도 약 30,000배 향상
시스템 리소스 부하	CPU 점유율 100% (DB 교착 상태 유발)	CPU 점유율 0.1% 미만 (평온 유지)	인프라 비용 절감 및 무한 확장성

---

개발할 때 id를 인조 식별자 하나만 PK로 쓰면 편하긴 하지만은, DB가 버벅거리지 않고 잘 돌아가려면, DB의 입맛에 맞게 판을 깔아줘야 합니다.

• 방패 없는 편리함은 위험: id를 써서 개발 편의성은 좋지만, 데이터가 중복되거나 꼬이지 않게 복합 유니크 제약조건 생각해둬야 함
• 인덱스도 생각해보쟈: DB가 데이터를 조인하거나 찾을 때 헤매지 않도록, 조건이 딱 떨어지는(=) 컬럼을 맨 앞줄에! 세워주자
• DB 속마음을 알아야 서버가 안 터짐: 겉보기에만 돌아가는 코드가 아닌, DB가 진짜로 데이터를 어떻게 쌓고 찾는지 원리를 알고 짜면은 나중에 트래픽이 많아져도 뻗지 않는 서비스가 된다.

편한 길(id)로 가되, 사고 나지 않게 방지턱(유니크) 잘 깔고 신호등(인덱스) 잘 세우자!