[DB 기초]CLUSTERED 와 NONCLUSTERED 인덱스의 차이

Programming/DB_Oracle

[DB 기초]CLUSTERED 와 NONCLUSTERED 인덱스의 차이

아이바 2025. 6. 19. 14:24

CONSTRAINT [PK_BR_MEMBER] PRIMARY KEY CLUSTERED ([MEM_IDX]), CONSTRAINT [UK_BR_MEMBER_MEM_ID] UNIQUE NONCLUSTERED ([MEM_ID]) 여기서 CLUSTERED 와 NONCLUSTERED 차이는 뭐임

ChatGPT의 말:

좋은 질문입니다, 김태훈님.
CLUSTERED와 NONCLUSTERED의 차이는 인덱스의 물리적인 정렬 방식과 저장 방식에 있습니다.

✅ 요약부터

구분CLUSTERED INDEX (클러스터형)NONCLUSTERED INDEX (비클러스터형)

저장 구조	실제 데이터 정렬 순서와 동일	별도의 인덱스 구조로 존재 (데이터와 분리됨)
데이터 위치	인덱스 자체가 데이터를 포함	인덱스는 포인터(참조)만 가지고 있음
개수	테이블당 1개만 가능	여러 개 생성 가능
성능	범위 검색, 정렬 시 빠름	특정 컬럼 조회에 최적화
예시	PK (MEM_IDX)	유니크한 사용자 ID (MEM_ID)

✅ 실제 예제에서 설명

sql

복사편집

CONSTRAINT [PK_BR_MEMBER] PRIMARY KEY CLUSTERED ([MEM_IDX])

MEM_IDX는 IDENTITY로 자동 증가되는 기본키니까
이 컬럼 기준으로 데이터 자체가 정렬되어 저장됨
즉, 이 인덱스 자체가 데이터 페이지

sql

복사편집

CONSTRAINT [UK_BR_MEMBER_MEM_ID] UNIQUE NONCLUSTERED ([MEM_ID])

MEM_ID는 사용자 아이디로 유일해야 하지만, 데이터 정렬의 기준은 아님
그래서 별도의 인덱스 구조만 만들고,
이 인덱스에는 실제 데이터의 주소 포인터만 있음 (예: RID나 PK 참조)

✅ 그림으로 보면

sql

복사편집

✅ 언제 NONCLUSTERED를 쓰냐?

보통 조회가 자주 되는 컬럼, 예: 아이디, 이메일, 전화번호 등
또는 자주 검색 조건이나 정렬 기준이 되는 컬럼
클러스터 인덱스를 바꾸기 어려운 상황에서 보조 인덱스로 사용

필요하시면 실제 실행 계획 (Query Plan) 분석도 도와드릴게요.
이해를 위해 정리해드릴까요?

🔹 클러스터 인덱스 = 데이터 자체 정렬
🔹 논클러스터 인덱스 = 보조 목차 (색인처럼)

궁금한 부분 더 알려주시면 더 깊게 설명해드릴게요!

나의 말:

| 1 | userA | ... | 이값 자체가인덱스 하나라는거임?

ChatGPT의 말:

정확히 짚으셨습니다, 김태훈님.

✅ 네, | 1 | userA | ... | 이 하나의 인덱스 엔트리(=행) 입니다.

그리고 클러스터드 인덱스(예: MEM_IDX)일 경우에는:

"이 인덱스가 곧 데이터 자체입니다."

🔷 쉽게 설명해볼게요

📌 클러스터드 인덱스 (예: PRIMARY KEY CLUSTERED (MEM_IDX))

데이터는 기본키 순서대로 실제 디스크에 저장됩니다.

text

복사편집

이걸 인덱스로 보면:

text

복사편집

CLUSTERED INDEX = 데이터 자체 (1) -> [userA, 홍길동, ...] (2) -> [userB, 이몽룡, ...] (3) -> [userC, 성춘향, ...]

🔶 반면, 논클러스터드 인덱스는?

논클러스터드 인덱스는 이렇게 별도의 구조로 관리됩니다.

예: NONCLUSTERED UNIQUE ([MEM_ID])

text

복사편집

NONCLUSTERED INDEX: (MEM_ID 기준 정렬) [userA] -> pointer to row with MEM_IDX=1 [userB] -> pointer to row with MEM_IDX=2 [userC] -> pointer to row with MEM_IDX=3

이때 인덱스 자체는 실제 데이터를 포함하지 않고,
해당 데이터를 찾기 위한 주소(PK나 RID 등) 를 갖고 있어요.

🔸 요약하면

구분클러스터드 인덱스논클러스터드 인덱스

데이터와의 관계	인덱스 = 데이터 자체	인덱스는 주소만 가짐
정렬 기준	인덱스 컬럼 기준으로 물리 정렬	별도 정렬 구조
예시	MEM_IDX PK	MEM_ID UNIQUE KEY

추가로 실제 인덱스 페이지 구조나 sys.indexes, sys.index_columns 조회로 확인도 가능해요.
필요하면 해당 쿼리도 드릴게요.

다른 인덱스 관련 질문도 언제든지 환영입니다.

나의 말:

클러스터드 인덱스 는 하나의 행이라 보면됨?

ChatGPT의 말:

좋은 질문입니다, 김태훈님. 간단하고 정확하게 말씀드리면:

✅ 클러스터드 인덱스는 "행 자체가 인덱스다" 라고 보면 됩니다.

🔹 좀 더 구체하게 설명하면?

클러스터드 인덱스(Clustered Index) 는
데이터 자체가 인덱스 구조에 정렬되어 저장된 것입니다.
그래서 인덱스 엔트리 1개 = 테이블의 한 행(Row) 이 됩니다.

text

복사편집

예: PRIMARY KEY CLUSTERED (MEM_IDX) -- 인덱스 구조 = 실제 테이블 구조 MEM_IDX | MEM_ID | MEM_NAME | ... --------|--------|----------|----- 1 | userA | 홍길동 2 | userB | 이몽룡 3 | userC | 성춘향

이 전체가 클러스터드 인덱스에 저장되어 있음.
즉, 행 하나 = 인덱스 하나.

📌 그래서 클러스터드 인덱스는?

하나의 테이블에 하나만 존재 가능함 (정렬 기준이 하나니까)
인덱스를 따라가면 바로 데이터가 나옴
SELECT 시 별도 lookup 필요 없음 → 빠름

🔸 참고 비교: 논클러스터드 인덱스는?

MEM_ID 기준으로 만든 논클러스터드 인덱스는 이렇게 생김:

text

복사편집

[userA] → pointer to MEM_IDX=1 [userB] → pointer to MEM_IDX=2

→ 논클러스터드는 실제 데이터 주소만 저장, 따로 찾아가야 함

🔻 요약

구분클러스터드 인덱스

인덱스 엔트리 1개	= 행(Row) 1개
인덱스 + 데이터	동일함
검색 속도	매우 빠름 (중간에 lookup 없음)
개수	테이블당 1개만 가능

필요하시면 sys.indexes 조회 쿼리로 확인하는 방법도 알려드릴게요.

나의 말:

인덱스라는건 저장된 데이터의 집합인가?

ChatGPT의 말:

좋은 질문입니다, 김태훈님.

✅ 인덱스란?

**“저장된 데이터의 복사본(요약본)” + “빠르게 찾기 위한 주소록”**입니다.

🔹 쉽게 비유하면?

📖 책의 목차라고 생각하면 가장 직관적입니다.

책 전체(테이블)는 수백 페이지.
목차(인덱스)는 "챕터 제목 + 페이지 번호"만 모아놓은 구조.

즉,

실제 데이터(본문)는 테이블에 있고,
인덱스는 찾기 편하게 정리된 요약 목록입니다.

🔸 인덱스 = 데이터의 구조화된 정렬/복사본

용어설명

클러스터드 인덱스	테이블 그 자체 = 정렬된 데이터 저장 방식
논클러스터드 인덱스	특정 컬럼값 + 해당 행을 가리키는 주소(포인터) 저장

text

복사편집

예: MEM_ID 컬럼에 논클러스터드 인덱스 생성 인덱스 구조: MEM_ID → MEM_IDX(기본키 참조) -------- -------------- userA → 1 userB → 2 userC → 3

🔹 그럼 “인덱스 = 저장된 데이터인가?”라는 질문에 대한 정리:

항목설명

❓ 인덱스는 데이터인가요?	✅ 데이터 일부의 복사본입니다.
❓ 인덱스는 데이터 전부인가요?	❌ 아닙니다. 전부 저장하지 않고, 검색에 필요한 컬럼만 복사해 둠
❓ 인덱스만 보면 모든 데이터 알 수 있나요?	❌ 기본 인덱스는 주소만 저장함. 데이터는 테이블에 있음.

✅ 마무리 요약

인덱스는 저장된 데이터의 일부 컬럼을 정렬/요약/주소화한 복사본
인덱스를 통해 빠르게 데이터 찾기 가능
하지만 인덱스 자체는 전체 데이터는 아님, 성능 도구임

필요하시면 include 컬럼, covering index, heap vs clustered 도 설명해드릴게요.

나의 말:

아닙니다. 전부 저장하지 않고, 검색에 필요한 컬럼만 복사해 둠 이라고 했는데 평소에는 저장하지않잖아 select * from br_member 할때만 인덱스가 생성된다는거임?

ChatGPT의 말:

아니요, 그 반대입니다.
👉 **인덱스는 "select 하기 전에 미리 만들어서 저장해두는 것"**입니다.

🔍 정리해서 설명드리면:

✅ 인덱스는 항상 존재합니다 (생성 시점에).

CREATE INDEX 또는 PRIMARY KEY, UNIQUE 제약 조건을 만들 때 자동으로 인덱스 생성됨.
그 인덱스는 DB 내부에 따로 저장돼 있습니다.
그래서 SELECT 하기 전부터 이미 존재하고, 조회할 때 그걸 활용합니다.

📦 그럼 select * 할 때는?

SELECT * FROM BR_MEMBER는 모든 컬럼을 다 조회하니까,
- 인덱스만으로는 불가능하면 → 실제 테이블 데이터(=클러스터드 인덱스 or 힙) 에서 데이터를 읽습니다.
- 대신, WHERE MEM_ID = 'userA' 같이 조건이 있을 경우:
  - SQL Server는 먼저 MEM_ID 인덱스를 검색해서 행 주소 찾고
  - 그걸로 실제 테이블에서 데이터를 로딩합니다.

🔧 예를 들어

sql

복사편집

CREATE NONCLUSTERED INDEX IX_BR_MEMBER_MEM_ID ON BR_MEMBER(MEM_ID)

이걸 만들면 IX_BR_MEMBER_MEM_ID라는 인덱스 객체가 디스크에 저장됩니다.
이 인덱스에는 아래처럼 정렬된 키-포인터 구조가 저장돼 있음:

diff

복사편집

MEM_ID → ROW_ADDRESS -------- ------------ userA → 0x00000123 userB → 0x00000130

💡 요약

질문답변

select 할 때 인덱스가 만들어지나요?	❌ 아닙니다. 미리 만들어진 인덱스를 사용하는 것입니다.
인덱스는 언제 만들어지나요?	✅ CREATE INDEX, PRIMARY KEY, UNIQUE 등을 통해 수동/자동으로 생성됨
인덱스는 어떤 데이터인가요?	✅ 검색용으로 정렬된 복사본(일부 컬럼 + 주소 정보 등)

필요하시면 실제 인덱스 내부구조(B-Tree), 인덱스 스캔/시크 등도 설명드릴게요.

나의 말:

그러면 테이블 생성시점에 보통 cluster인덱스도 만드는데 그러면 안에 데이터가 없잖아 인덱스값들이 어떻게 만들어진다는거지?

ChatGPT의 말:

아주 좋은 질문입니다, 태훈님. 핵심을 짚으셨습니다.
요약부터 말씀드리면:

✅ 테이블 생성 시점에 클러스터드 인덱스(예: PK)를 만들면

"인덱스 구조"만 먼저 만들어지고,
"실제 인덱스 데이터는 나중에 레코드가 insert될 때 같이 채워집니다."

🔍 구체적으로 설명하면

예를 들어 다음과 같은 테이블을 만들 때:

sql

복사편집

CREATE TABLE BR_MEMBER ( MEM_IDX BIGINT PRIMARY KEY, MEM_ID VARCHAR(20) )

이때 MEM_IDX에 대해 클러스터드 인덱스가 자동 생성됩니다. 하지만…

❓ 그 시점엔 아직 데이터가 없죠?

맞습니다. 그래서 이 시점에 하는 건:

✅ "인덱스 트리 구조의 틀만 만들어 놓고,
나중에 데이터가 insert될 때마다 자동으로 그 인덱스 구조에 반영"됩니다.

📦 insert가 들어오면?

sql

복사편집

INSERT INTO BR_MEMBER (MEM_IDX, MEM_ID) VALUES (1001, 'userA')

이 순간:

MEM_IDX가 PK니까 → 클러스터드 인덱스에 이 값이 추가됨.
인덱스는 정렬 구조(B-tree)니까 → 자동으로 정렬된 상태로 반영됨.
물리적으로도 이 인덱스 순서에 따라 테이블 자체가 정렬되어 저장됨.

💡 핵심 요약

시점설명

테이블 생성 시	인덱스 구조(틀)만 생성됨. 데이터는 없음.
INSERT 시	인덱스 구조에 자동 반영됨. B-Tree 정렬 유지.
SELECT 시	인덱스를 활용해 빠르게 조회 가능.

원하시면 DBCC IND, DBCC SHOWCONTIG, 실제 인덱스 페이지 구조 보는 것도 알려드릴 수 있어요.
궁금한 포인트 더 알려주세요!

나의 말:

그럼 기본 테이블 값 외에도 인덱스가 저장공간을 차지하고있는거임? 그런데 pk 는 클러스터인덱스고 사실상 테이블마다 인덱스가 저장공간을 점유하고있는거아님?

ChatGPT의 말:

정확하게 보셨습니다, 태훈님.
질문 요지 정리부터 해보면 다음과 같습니다:

❓ "기본 테이블 외에 인덱스도 따로 저장공간을 차지하느냐?"

👉 Yes. 차지합니다.

✅ 설명 정리

1. 클러스터드 인덱스 (Clustered Index)

테이블 그 자체입니다.
클러스터드 인덱스를 만들면 테이블의 데이터는 인덱스 순서대로 저장됩니다.
따라서 클러스터드 인덱스 = 테이블의 물리적 저장 구조.

📦 즉, 클러스터드 인덱스는 따로 저장 공간을 차지한다기보다는
➡️ "테이블 자체가 클러스터드 인덱스의 형태로 저장된다" 고 이해하는 게 맞습니다.

2. 비클러스터드 인덱스 (Non-Clustered Index)

이건 테이블과는 별도로 생성되는 구조물입니다.
내부적으로는 [인덱스 키 컬럼 + RID(Row Identifier 또는 클러스터드 인덱스 키)] 형태로 구성됩니다.
따로 페이지 공간을 차지하고,
→ 인덱스 페이지가 B-Tree 구조로 정렬되어 저장됩니다.

📦 예를 들어 MEM_ID에 유니크 인덱스 걸면:
→ MEM_ID 값들만 따로 저장된 인덱스 구조가 생성됨.
→ 이 구조는 테이블과 별도로 디스크 공간을 사용합니다.

📊 요약 표

인덱스 종류저장공간 차지?어디에 저장?특징

클러스터드 인덱스	✅ 차지함	테이블 자체가 인덱스	1개만 가능
비클러스터드 인덱스	✅ 차지함	테이블 외부에 따로 존재	여러 개 가능

💡 그래서 MSSQL에서 인덱스가 많아지면:

디스크 사용량이 늘고,
데이터 입력/수정/삭제 속도도 느려질 수 있음 (인덱스도 같이 갱신해야 하므로).

필요하시면:

특정 테이블의 인덱스별 디스크 사용량 확인 쿼리도 드릴 수 있어요.
또는 실제 인덱스 페이지 구조 확인 방법도 알려드릴 수 있고요.

계속 궁금한 거 있으시면 편하게 이어가세요!

나의 말:

예를 들어 MEM_ID에 유니크 인덱스 걸면: → MEM_ID 값들만 따로 저장된 인덱스 구조가 생성됨. → 이 구조는 테이블과 별도로 디스크 공간을 사용합니다. 이거 insert할때마다 늘어나는거임

ChatGPT의 말:

네, 정확히 맞습니다.
MEM_ID에 유니크 인덱스가 걸려 있다면,

👉 INSERT가 발생할 때마다 인덱스 구조도 함께 업데이트(증가) 됩니다.

✅ 예시 상황 정리

1. BR_MEMBER 테이블에 다음과 같은 비클러스터드 유니크 인덱스가 있다고 가정:

sql

복사편집

CREATE UNIQUE NONCLUSTERED INDEX UK_BR_MEMBER_MEM_ID ON BR_MEMBER(MEM_ID);

2. 이제 다음 쿼리를 실행한다고 해봅시다:

sql

복사편집

INSERT INTO BR_MEMBER (MEM_ID, MEM_NAME, ...) VALUES ('userA', '홍길동', ...);

이 순간 SQL Server는 아래 2가지 작업을 동시에 수행합니다:

✅ 테이블(클러스터드 인덱스) 데이터에 새로운 row 저장
✅ 비클러스터드 인덱스 구조에 MEM_ID = 'userA' 엔트리 추가
- 이 엔트리는 내부적으로 [MEM_ID, RID(행 위치 정보)] 형식입니다.

📦 즉, insert가 발생할 때:

데이터 저장 공간 +
비클러스터드 인덱스 저장 공간

두 군데 모두에 새로운 정보가 추가됩니다.

💡 추가 포인트

UPDATE도 인덱싱된 컬럼이 바뀌면 인덱스 구조까지 갱신됩니다.
DELETE는 인덱스에서도 엔트리가 제거됩니다.
유니크 제약 조건은 인덱스 삽입 전에 중복 여부를 먼저 검사하므로 성능에도 영향을 줍니다.

원하시면 실제 인덱스 페이지에 어떻게 기록되는지,
실제 디스크 사용량까지 보는 쿼리도 알려드릴게요.

나의 말:

그러면 테이블 내 데이터는 볼수있고 인덱스 값들을 본다는게아니라 인덱스형태로 저장이되고 unique 인덱스는 별도의 저장공간에 값들이 따로저장되어있는건가

ChatGPT의 말:

네, 정확히 잘 이해하셨습니다.
아래에 핵심을 직관적으로 요약해드릴게요.

✅ 1. 테이블의 실제 데이터

테이블의 데이터는 기본적으로 클러스터드 인덱스 구조로 저장됩니다.
→ 즉, PK(예: MEM_IDX)가 있다면 해당 컬럼 기준으로 정렬된 실제 데이터가 디스크에 저장됨.

plaintext

복사편집

클러스터드 인덱스 = 테이블 데이터 자체

✅ 2. 비클러스터드 인덱스 (예: UNIQUE INDEX)

예를 들어, MEM_ID에 유니크 인덱스를 걸면:

sql

복사편집

CREATE UNIQUE NONCLUSTERED INDEX UK_BR_MEMBER_MEM_ID ON BR_MEMBER(MEM_ID);

→ SQL Server는 MEM_ID 값들만 모은 별도의 B-Tree 구조를 생성합니다.
→ 이 구조는 테이블과 완전히 별도의 저장 공간입니다.

plaintext

복사편집

비클러스터드 인덱스 = MEM_ID 값 + RID 또는 PK 참조 = 디스크에 따로 저장됨

✅ 3. 왜 따로 저장하느냐?

인덱스는 빠른 검색, 중복 체크, 정렬 등을 위해 별도로 최적화된 구조를 가집니다.
테이블 전체를 훑지 않고도, 예를 들어 WHERE MEM_ID = 'userA' 같은 조건을 빠르게 처리할 수 있게 합니다.

✅ 그림으로 비유

plaintext

복사편집

[BR_MEMBER 테이블] ┌────────┬──────────┬───────────────┐ | MEM_IDX| MEM_ID | ... | ├────────┼──────────┼───────────────┤ | 1 | userA | ... | | 2 | userB | ... | └────────┴──────────┴───────────────┘ [비클러스터드 인덱스: MEM_ID] (MEM_ID 기준으로 정렬된 별도 구조) ┌────────┬─────────────┐ | MEM_ID | RID(행 주소)| ├────────┼─────────────┤ | userA | → Row 1 | | userB | → Row 2 | └────────┴─────────────┘

※ RID는 클러스터드 인덱스 키나 실제 행 주소를 나타냄.

✅ 요약 정리

구분저장 위치포함 내용

클러스터드 인덱스	테이블 그 자체	전체 Row 데이터 (정렬됨)
비클러스터드 인덱스	별도의 저장 공간 (B-Tree)	인덱스 컬럼 + 참조 정보 (RID 등)

필요하시면 sys.dm_db_index_physical_stats 같은 DMV로 실제 인덱스 구조 확인하는 법도 알려드릴게요.

나의 말:

클러스터인덱스는 따로 저장공간이 있는게아니네 데이터가 그냥 그 클러스터링형태로 저장된거뿐

ChatGPT의 말:

네, 맞습니다! 김태훈님 이해 정확합니다. 👏

✅ 다시 말해 정리하면:

🔹 클러스터드 인덱스는 "별도 저장공간이 아니라"

→ 테이블 자체가 인덱스 구조로 저장된 것입니다.

즉, 테이블 = 클러스터드 인덱스 구조
데이터를 저장할 때 PK나 지정된 컬럼 기준으로 정렬된 B-Tree 구조에 따라 디스크에 기록됩니다.
그래서 따로 "클러스터 인덱스 공간"이 필요한 게 아니라, 그냥 테이블 자체가 그 구조인 것이에요.

✅ 반대로 비클러스터드 인덱스는?

→ 진짜로 별도의 공간에
→ (인덱스 컬럼 + 참조 키(PK 또는 RID)) 구조로 저장됩니다.

🔍 예시

sql

복사편집

CREATE TABLE Member ( Id INT PRIMARY KEY, -- 클러스터드 인덱스 UserId VARCHAR(20) UNIQUE -- 비클러스터드 인덱스 );

Id는 클러스터드 인덱스 → 데이터가 Id 기준으로 테이블 자체가 정렬됨
UserId는 비클러스터드 인덱스 → 별도 공간에 UserId + Id 형태로 저장

필요하시면 dbcc showcontig 또는 sys.dm_db_index_physical_stats 뷰로 실제 인덱스 구조를 확인하는 방법도 도와드릴게요.

나의 말:

그럼 클러스터드 인덱스는 테이블당 하나만 있을수밖에없는거네

ChatGPT의 말:

네, 정확합니다! 💯
클러스터드 인덱스(Clustered Index)는 테이블당 하나만 존재할 수 있습니다.

✅ 왜 하나만 가능한가?

클러스터드 인덱스 = 테이블의 실제 데이터 저장 순서이기 때문입니다.
테이블의 물리적 저장 순서는 하나밖에 없기 때문에,
→ 하나의 클러스터드 인덱스만 가능합니다.

✅ 예를 들어:

sql

복사편집

CREATE TABLE Orders ( OrderId INT PRIMARY KEY, -- 클러스터드 인덱스 CustomerId INT, OrderDate DATETIME );

여기서 OrderId가 PK이므로 → 기본적으로 클러스터드 인덱스가 생성됩니다.
OrderDate나 CustomerId에는 비클러스터드 인덱스를 추가로 만들 수는 있지만,
클러스터드 인덱스로는 설정 못합니다 (이미 하나 존재하므로).

✅ 그럼 바꾸고 싶다면?

기존 클러스터드 인덱스를 삭제하고,
다른 컬럼에 클러스터드 인덱스를 지정해야 합니다:

sql

복사편집

-- 기존 PK 제거 후 ALTER TABLE Orders DROP CONSTRAINT PK_Orders; -- 새로 클러스터드 인덱스 생성 CREATE CLUSTERED INDEX IX_Orders_OrderDate ON Orders(OrderDate);

필요하시면 클러스터드 인덱스를 바꾸는 절차나, 비클러스터드와 혼합 사용하는 전략도 알려드릴게요!

728x90

저작자표시 비영리 (새창열림)