MySQL, SQL Server, PostgreSQL, Oracle의 Non Clustered Index의 Leaf 노드에는 어떤 것을 저장할까?

디비 인덱스에 포함하지 않는 데이터를 가져오기 위해 Leaf 노드에 저장된 key를 통해 실제 데이터를 가져오게 되는데

각 디비별로 살펴보자.

 

chatgpt를 활용해서 Non Clustered 의 Leaf노드에 저장하는 것에 대한 질문을 해봤다.

좋은 질문은 좋은 답변을 얻는다.

 

 

1. SQL Server

SQL Server에서 Non-Clustered Index의 리프 노드는 다음과 같은 데이터를 저장합니다.

• 클러스터형 테이블(Clustered Table, 즉 PK가 클러스터형 인덱스일 경우)
  • 리프 노드에 해당 행의 클러스터형 인덱스 키(PK 값) 를 저장 → 이를 통해 클러스터형 인덱스에서 데이터를 찾아감
• 힙 테이블(Heap, 즉 PK 없이 테이블이 구성된 경우)
  • 리프 노드에 RID(Row Identifier, = File ID + Page ID + Slot ID) 를 저장하여 실제 데이터가 있는 위치를 참조

✅ 즉, 클러스터형 인덱스가 있는 경우에는 리프 노드에 PK를 저장하고, 클러스터형 인덱스가 없는 경우에는 RID를 저장

---

2. MySQL (InnoDB 기준)

MySQL InnoDB에서 Non-Clustered Index(Secondary Index)의 리프 노드는 다음을 저장합니다.

• 항상 PK 값을 저장 (클러스터형 인덱스를 사용하므로 RID 개념이 없음)
• 이후, PK를 사용해 클러스터형 인덱스(B+트리)에서 데이터를 조회

✅ InnoDB에서는 무조건 리프 노드에 PK 값을 저장하고, 이를 이용해 클러스터형 인덱스에서 데이터를 찾아감

 

MySQL InnoDB는 기본적으로 클러스터형 인덱스(Clustered Index)를 기반으로 동작해.
그런데 테이블에 명시적인 PK가 없으면 어떻게 될까?

1. 명시적인 PK가 있으면 → Non-Clustered Index 리프 노드에 PK 저장
2. PK가 없고, 유니크(UNIQUE) NOT NULL 인덱스가 있으면 → 그걸 클러스터형 인덱스로 사용
3. PK도 없고, 적절한 유니크 인덱스도 없으면 → InnoDB가 내부적으로 숨겨진 RowID(6바이트) 생성

🛑 즉, PK가 없으면 InnoDB는 자동으로 내부 RowID를 생성하고, Non-Clustered Index의 리프 노드에는 이 RowID가 저장됨.

---

🏷 MySQL (InnoDB)에서 Non-Clustered Index 리프 노드 저장 값 정리

테이블 상태클러스터형 인덱스Non-Clustered Index 리프 노드 저장 값

PK가 있음	PK 컬럼을 기준으로 정렬	PK 값
PK가 없고, 유니크 NOT NULL 인덱스가 있음	해당 UNIQUE 인덱스를 클러스터형 인덱스로 사용	해당 UNIQUE 인덱스 값
PK도 없고, 유니크 인덱스도 없음	InnoDB가 내부 RowID(6바이트) 자동 생성	RowID

✅ 결론: MySQL에서 PK가 없으면 Non-Clustered Index의 리프 노드에는 내부적으로 생성된 RowID가 저장됨.
✅ 즉, MySQL도 SQL Server의 힙 테이블처럼 RID 개념이 존재하긴 하지만, 외부에서 직접 사용할 수 없음.

 

 

---

3. Oracle

Oracle에서는 비클러스터형 인덱스(Non-Clustered Index, 일반적인 B-Tree Index) 의 리프 노드는 다음을 저장합니다.

• 해당 행의 RowID (Data Block 주소 포함, = File ID + Block ID + Row Number)
• 즉, Non-Clustered Index의 리프 노드가 직접 데이터가 저장된 블록을 가리키므로 추가적인 PK 검색 과정 없이 바로 데이터에 접근 가능

✅ Oracle에서는 리프 노드에 RowID를 저장하여 테이블 블록을 직접 찾아감 → SQL Server와 다르게 PK가 필요 없음
✅ RID와 유사하지만, Oracle에서는 RowID가 더 세밀한 주소 정보 포함

 

4. PostgreSQL

PostgreSQL의 모든 인덱스(B-Tree, Hash, GIN, BRIN 등)는 테이블의 실제 데이터를 저장하지 않음.
리프 노드에는 해당 행을 찾을 수 있는 TID(Tuple ID) 가 저장됨.

✅ TID (Tuple ID)

• PostgreSQL은 테이블 데이터를 Heap에 저장하며, 각 행(row)은 TID라는 고유한 식별자를 가짐.
• TID = (Page Number, Row Number) 로 구성됨.
• 즉, Non-Clustered Index의 리프 노드에는 PK나 RowID가 아닌 TID가 저장됨.
• 이를 통해 테이블의 해당 페이지(Page)와 행(Row)에 바로 접근할 수 있음.

🔥 PostgreSQL의 특이한 점

1. PostgreSQL은 클러스터형 인덱스 개념이 없고, 테이블 데이터는 항상 Heap에 저장됨.
2. Non-Clustered Index의 리프 노드에는 PK 대신 TID(Tuple ID)가 저장됨.
3. TID는 페이지 번호(Page Number)와 행 번호(Row Number)로 구성되어, 테이블 데이터에 바로 접근 가능.
4. MVCC(다중 버전 동시성 제어)로 인해, 테이블이 업데이트되면 새로운 버전의 행이 다른 위치에 생성되므로, 인덱스 재작성(재색인, REINDEX)이 필요할 수도 있음.

✅ 즉, PostgreSQL에서 Non-Clustered Index를 통해 데이터를 찾을 때는 PK 대신 TID를 이용해 Heap에서 데이터를 조회한다!
✅ PK를 사용하든 안 하든, PostgreSQL의 Non-Clustered Index는 항상 TID를 저장하며, 이를 이용해 테이블 데이터를 검색함.

 

 

📌 요약

디비명	Non Cluster Leaf 노드 저장
SQL Server	PK (클러스터형 테이블) 또는 RID (힙 테이블)
MySQL (InnoDB)	PK (PK 없으면 내부 RowID)
Oracle	RowID (Data Block 주소 포함)
PostgreSQL	TID (Page Number + Row Number)

 

 

그렇다면 여기서 이런 의문점이 들 수 있다.

Oracle과 PostgreSQL의 RID혹은 TID 참조가 수십만번 일어나면 성능 이슈가 생기지 않을까?

🔍 문제: Index Look Up이 너무 많아지면 비효율적이다!

• 예제 시나리오:
  • 데이터가 10만 건이고,
  • 인덱스에는 검색할 컬럼(A)만 존재,
  • 실제 조회 시 추가적인 컬럼(B, C 등)이 필요하다면,
  • 인덱스 리프 노드에 없는 컬럼을 가져오기 위해 매번 테이블 Look Up이 발생
  • 즉, 10만 건의 Look Up(랜덤 I/O) → 성능 저하!

 

📌 Index Look Up 비용 문제와 해결 방법 (SQL Server / MySQL / PostgreSQL / Oracle 비교)

데이터베이스에서 Non-Clustered Index(보조 인덱스) 를 사용할 때,

• 인덱스에 없는 컬럼이 필요하면 테이블 Look Up이 발생
• Look Up이 많아지면 성능 저하 (예: 10만 건 조회 시 10만 번 Look Up)
• SQL Server는 INCLUDE INDEX로 해결하지만, 다른 DBMS들은 어떻게 해결할까?

아래에서 각 DBMS별로 Look Up을 줄이는 방법을 비교해볼게! 🔥

---

🔍 문제: Index Look Up이 많을 때 비효율적!

예제 상황설명

데이터 개수	100만 건
검색 조건	WHERE search_column = '값'
추가 조회 컬럼	SELECT additional_column (인덱스에 없음)
인덱스	CREATE INDEX idx_search ON my_table (search_column);

🚨 기본 인덱스(idx_search)에는 search_column만 포함됨.
➡ 따라서, additional_column을 가져오기 위해 매번 테이블 Look Up이 발생!
➡ Look Up이 10만 번 이상 발생하면 비효율적 → 해결책이 필요함!

---

1️⃣ SQL Server: INCLUDE INDEX 사용

SQL Server는 INCLUDE 옵션을 제공해서 인덱스 리프 노드에 추가 컬럼을 포함할 수 있어.

sql

복사편집

CREATE INDEX idx_covering ON my_table (search_column) INCLUDE (additional_column);

✅ additional_column이 인덱스 리프 노드에 저장됨
✅ Look Up 없이 인덱스만으로 데이터를 반환 가능 (Index Only Scan 가능!)
✅ SQL Server에서 가장 간단한 해결책! 🚀

---

2️⃣ MySQL (InnoDB): Secondary Index & Covering Index

MySQL(InnoDB)은 SQL Server처럼 INCLUDE 옵션이 없음.
하지만 Covering Index(커버링 인덱스)로 Look Up을 줄일 수 있음!

sql

복사편집

CREATE INDEX idx_covering ON my_table (search_column, additional_column);

✅ additional_column을 함께 저장하는 복합 인덱스(Composite Index)
✅ MySQL InnoDB의 보조 인덱스는 기본적으로 PK를 포함하므로 PK 조회도 가능
✅ Index Only Scan 가능 → 테이블 Look Up 없이 조회 가능!

🚨 하지만 복합 인덱스는 검색 순서에 영향을 받을 수 있음.
➡ 예: WHERE additional_column = '값'은 위 인덱스를 활용 못 할 수도 있음.

---

3️⃣ PostgreSQL: INCLUDE 옵션 사용

PostgreSQL은 SQL Server처럼 INCLUDE 옵션을 지원함! 🎉

sql

복사편집

CREATE INDEX idx_covering ON my_table (search_column) INCLUDE (additional_column);

✅ SQL Server와 동일한 방식!
✅ additional_column이 인덱스 리프 노드에 포함되므로 Look Up이 필요 없음
✅ Index Only Scan 가능 → 성능 향상!

💡 PostgreSQL 11부터 INCLUDE 지원 → 최신 버전 사용 권장!

---

4️⃣ Oracle: Composite Index 또는 IOT 사용

Oracle은 SQL Server처럼 INCLUDE를 지원하지 않음.
하지만 아래 2가지 방법으로 해결 가능!

🔹 방법 1: Composite Index 사용

sql

복사편집

CREATE INDEX idx_covering ON my_table (search_column, additional_column);

✅ MySQL과 동일하게 복합 인덱스(Composite Index) 사용
✅ 검색 순서에 영향 받을 수 있음 (WHERE 절 조건 주의)

🔹 방법 2: Index Organized Table (IOT) 사용

sql

복사편집

CREATE TABLE my_table ( search_column VARCHAR2(100), additional_column VARCHAR2(100), PRIMARY KEY (search_column) ) ORGANIZATION INDEX;

✅ IOT는 테이블을 아예 인덱스로 저장 (MySQL InnoDB의 클러스터형 인덱스와 유사)
✅ Look Up이 필요 없음 → 성능 최적화 가능!
🚨 하지만 일반적인 테이블과 다르게 설계해야 하므로 적용이 제한적.

---

🔥 DBMS별 해결 방법 비교 (SQL Server / MySQL / PostgreSQL / Oracle)

DBMSLook Up 해결 방법Index Only Scan 지원

SQL Server	INCLUDE 옵션 사용 (INCLUDE (additional_column))	✅
MySQL (InnoDB)	복합 인덱스 사용 ((search_column, additional_column))	✅
PostgreSQL	INCLUDE 옵션 사용 (INCLUDE (additional_column))	✅
Oracle	복합 인덱스 사용 또는 IOT (ORGANIZATION INDEX)	✅ (IOT 사용 시 완벽)

✅ SQL Server & PostgreSQL: INCLUDE로 가장 간단하게 해결 가능!
✅ MySQL & Oracle: Composite Index로 해결 가능, Oracle은 IOT도 가능

---

📌 결론: Look Up 최소화 방법

1️⃣ SQL Server → INCLUDE INDEX 사용
2️⃣ MySQL → Composite Index 사용 ((A, B))
3️⃣ PostgreSQL → INCLUDE INDEX 사용 (SQL Server와 동일)
4️⃣ Oracle → Composite Index 사용 ((A, B)) 또는 IOT 사용

✅ 결국, "Index Only Scan"을 만들면 Look Up 비용을 줄일 수 있음!
✅ SQL Server와 PostgreSQL은 INCLUDE, MySQL과 Oracle은 Composite Index로 해결 가능!
✅ Oracle에서는 IOT를 활용하면 Look Up이 완전히 없어짐!