21. [MS-SQL] 인덱스부터 포괄열까지 이해하기

결론만 말하면,

포괄열은 Leaf 페이지에 해당 키를 추가해주는 것(단, 정렬은 안함 넣는대로 쌓임)

그렇다면..

논클러스터 인덱스 +  포괄열를 만들면

논클러스터 인덱스 키로 지정된 열은 Leaf 노드에 정렬되어있지만,

포괄열로 넣은 키들은 정렬되어 있지않다.

-> 인서트 했을 때 적어도 포괄열 때문에 정렬을 다시 하지않아도 된다.

-> Select 했을 때 Leaf 페이지에서 Seek 하고싶다면 포괄열에 키를 넣어주면 RID 나 Key Look Up을 줄일 수 있다.

 

클러스터 + 논클러스터 인덱스 + 포괄열 이라면 

Non-Clustered Index  / Clustered Index / Include Index

 

좀 더, 정리해서 말하자면

 

포괄열 (Included Columns)

포괄열은 비클러스터 인덱스의 리프 노드에 추가적으로 데이터를 포함시키는 기능입니다.

• 정렬되지 않음: 포괄열로 지정된 컬럼은 단순히 리프 노드에 추가될 뿐, 정렬되지 않습니다.
• RID 또는 Key Lookup 감소: 포괄열로 데이터를 포함하면, 추가적으로 테이블의 데이터 페이지를 참조하지 않아도 되므로 Key Lookup을 줄일 수 있습니다.
• 데이터 쓰기 비용 감소: 포괄열 데이터는 단순히 저장되기만 하기 때문에, 삽입/갱신 시 정렬 작업이 필요하지 않아 쓰기 비용이 줄어듭니다.

---

논클러스터 인덱스 + 포괄열

1. 리프 노드의 정렬:
   • 논클러스터 인덱스 키는 항상 정렬됩니다.
   • 하지만, 포괄열 키는 단순히 추가적으로 저장될 뿐 정렬되지 않습니다.
2. 삽입 시 동작:
   • 논클러스터 인덱스는 인덱스 키의 정렬을 유지해야 하기 때문에 키 정렬 작업이 발생합니다.
   • 그러나, 포괄열은 정렬 대상이 아니므로 추가적인 정렬 비용은 발생하지 않습니다.
3. 조회 시 동작:
   • Seek 작업은 논클러스터 인덱스 키로만 가능합니다.
   • 포괄열 컬럼은 리프 노드에 포함되어 있으므로 RID/Key Lookup 없이 데이터를 가져올 수 있습니다.

---

클러스터 인덱스 + 논클러스터 인덱스 + 포괄열

1. 구성 요소:
   • 클러스터 인덱스:
     • 데이터가 클러스터 인덱스 키를 기준으로 물리적으로 정렬됩니다.
     • 클러스터 인덱스 키가 논클러스터 인덱스에 포함됩니다.
   • 논클러스터 인덱스:
     • 논클러스터 인덱스 키는 정렬됩니다.
     • 리프 노드에 클러스터 인덱스 키를 포함합니다.
   • 포괄열 (Included Columns):
     • 논클러스터 인덱스의 리프 노드에 단순히 데이터를 포함합니다. 정렬되지 않음.
2. 삽입 시:
   • 논클러스터 인덱스는 키 정렬을 유지하기 위해 삽입 작업 시 약간의 오버헤드가 있지만, 포괄열 컬럼은 정렬 대상이 아니므로 추가적인 정렬 작업이 없습니다.
3. 조회 시:
   • Key Lookup 감소:
     • 논클러스터 인덱스 키와 포괄열에 포함된 컬럼만으로 쿼리가 처리될 경우, 테이블 페이지를 참조하지 않아도 됩니다.
   • Seek 성능 향상:
     • 논클러스터 인덱스 키로 정렬된 데이터를 효율적으로 탐색하고, 포괄열 컬럼을 함께 반환할 수 있습니다.

 

---

---

더 자세히 알아보기

힙 테이블

• 클러스터 인덱스가 없는 상태의 테이블.
• 데이터가 특정 순서 없이 저장되며, 검색 시 전체 테이블을 스캔해야 하는 경우가 많음.

---

클러스터 인덱스 (Clustered Index)

• 주요 특징:
  • 물리적으로 데이터가 인덱스 키(PK) 기준으로 정렬되어 저장됨.
  • 테이블당 하나만 생성 가능.
  • Leaf Node에 실제 데이터가 저장됨.
• 예시:
  • UserID 컬럼이 클러스터 인덱스이며, 데이터는 UserID 기준으로 정렬되어 저장됨.
• CREATE TABLE Users ( UserID INT PRIMARY KEY, Name NVARCHAR(100), Age INT );

---

논클러스터 인덱스 (Non-Clustered Index)

• 주요 특징:
  • 테이블당 최대 999개까지 생성 가능.
  • Leaf Node에 실제 데이터 대신, 해당 키가 위치하는 RID(Row Identifier)를 저장.
  • RID를 통해 힙 테이블 또는 클러스터 인덱스에서 실제 데이터를 가져옴.
• 예시:
  • Name 컬럼에 대해 논클러스터 인덱스 생성.
• CREATE NONCLUSTERED INDEX IX_Users_Name ON Users (Name);

---

클러스터 인덱스 + 논클러스터 인덱스

• 특징:
  • 논클러스터 인덱스는 RID 대신 클러스터 인덱스 키를 저장.
  • Leaf Node에 저장된 클러스터 인덱스 키를 통해 데이터를 검색함.
• 예시:
  • Age 컬럼에 대해 논클러스터 인덱스를 생성하면, Leaf Node에는 UserID 값이 저장됨.
• CREATE NONCLUSTERED INDEX IX_Users_Age ON Users (Age);

---

복합 인덱스 (Composite Index)

• 정의: 두 개 이상의 컬럼을 결합하여 만든 인덱스.
• 주요 특징:
  • 여러 컬럼 조합으로 검색 속도를 향상시킴.
  • 인덱스 정의 순서에 따라 성능 차이가 발생.
  • 첫 번째 컬럼을 기준으로 정렬되며, 이후 컬럼은 서브 정렬로 저장됨.
• Leaf Node:
  • 복합 인덱스의 Leaf Node는 인덱스 키 조합과 함께 RID 또는 클러스터 인덱스 키를 저장.
  • 복합 인덱스 키에 포함된 컬럼만 Leaf Node에서 직접 반환 가능.
• 포괄 열과의 조합:
  • 복합 인덱스의 키에 포함되지 않은 열을 Leaf Node에 저장하여 Look Up을 줄일 수 있음.
  • 동일한 열이 인덱스 키와 포괄 열 모두에 포함될 경우, 불필요한 중복 저장으로 비효율적일 수 있음.
• 예시:
  • Name과 Age는 인덱스 키, Email은 포괄 열.
  • SELECT Name, Age, Email FROM Users WHERE Name = 'John';에서 Look Up 없이 검색 가능.
• CREATE NONCLUSTERED INDEX IX_Users_Name_Age ON Users (Name, Age) INCLUDE (Email);
• 조건에 따른 동작:
  1. 첫 번째 키만 사용하는 경우:
     • Name 기준으로 효율적으로 검색.
     • Age는 사용되지 않음.
  2. SELECT * FROM Users WHERE Name = 'John';
  3. 첫 번째와 두 번째 키를 모두 사용하는 경우:
     • 두 키를 모두 사용하여 정확한 범위를 검색.
  4. SELECT * FROM Users WHERE Name = 'John' AND Age = 30;
  5. 두 번째 키만 사용하는 경우:
     • Age만 사용하면 인덱스를 활용하지 못하고 Table Scan 발생 가능.
  6. SELECT * FROM Users WHERE Age = 30;
  7. 포괄 열만 사용하는 경우:
     • Email은 포괄 열로 저장되어 있어 Look Up 없이 검색 가능.
  8. SELECT Email FROM Users WHERE Name = 'John';

---

복합 인덱스 상세 동작

• 정렬 및 순서 영향:
  • 복합 인덱스는 첫 번째 키를 기준으로 정렬되며, 이후 키는 첫 번째 키 내에서 정렬됨.
  • 조건절에 사용된 컬럼의 순서가 인덱스 정의 순서와 다르면, 인덱스를 사용할 수 없거나 일부만 활용 가능.
• 범위 조건 사용 시:
  • 복합 인덱스의 첫 번째 키에 범위 조건이 적용되면, 두 번째 키 이후는 인덱스를 활용할 수 없음.
• 예시:
  • Name과 Age는 인덱스 키, Email은 포괄 열.
• CREATE NONCLUSTERED INDEX IX_Users_Name_Age_Email ON Users (Name, Age) INCLUDE (Email);

1. 정확한 키 순서로 사용:
   • Name과 Age 모두 사용되어 인덱스를 완전히 활용.
2. SELECT * FROM Users WHERE Name = 'John' AND Age = 30;
3. 첫 번째 키에 범위 조건 사용:
   • Name의 범위 조건으로 인해 Age는 활용되지 않음. 테이블 스캔 발생 가능.
4. SELECT * FROM Users WHERE Name LIKE 'J%' AND Age = 30;
5. 순서가 다른 경우:
   • 쿼리 작성 순서와 상관없이 인덱스 정의 순서에 따라 작동하므로, Name 먼저 검색 후 Age를 필터링.
6. SELECT * FROM Users WHERE Age = 30 AND Name = 'John';
7. 첫 번째 키 없이 두 번째 키만 사용하는 경우:
   • Name 없이 Age만 사용하면 복합 인덱스를 활용하지 못하고 테이블 스캔 발생.
8. SELECT * FROM Users WHERE Age = 30;
9. 포괄 열만 사용하는 경우:
   • Email은 포괄 열로 저장되어 있으므로 Look Up 없이 검색 가능.
10. SELECT Email FROM Users WHERE Name = 'John';

• 정리:
  • 첫 번째 키가 조건에 반드시 포함되어야 인덱스를 효율적으로 활용 가능.
  • 범위 조건이 포함되면 이후 키는 인덱스에서 제외될 가능성이 높음.
  • 순서와 조건 사용 방식에 따라 인덱스의 활용도가 크게 달라짐.

---

데이터 스캔 방식

1. Table Scan

• 힙 테이블에서 전체 데이터를 순차적으로 스캔.
• 예시: 인덱스 없이 SELECT * FROM Users WHERE Age > 30.

2. Clustered Index Scan

• 클러스터 인덱스를 순차적으로 스캔.
• 예시: SELECT * FROM Users WHERE Name LIKE 'A%' (클러스터 인덱스 범위 검색).

3. Non-Clustered Index Scan

• 논클러스터 인덱스를 순차적으로 스캔.
• 예시: 조건이 인덱스 키에 적합하지 않을 때, SELECT * FROM Users WHERE Age > 50.

4. Clustered Index Seek

• 클러스터 인덱스에서 조건에 맞는 데이터를 빠르게 검색.
• 예시:
  • UserID가 클러스터 인덱스이므로 Seek 가능.
• SELECT * FROM Users WHERE UserID = 5;

5. Non-Clustered Index Seek

• 논클러스터 인덱스의 Leaf Node에서 조건에 맞는 값을 바로 검색.
• 예시:
  • Name에 논클러스터 인덱스가 있을 경우.
• SELECT Name FROM Users WHERE Name = 'John';

6. RID Look Up

• Leaf Node에 원하는 열이 없을 때, RID를 사용해 힙 테이블에서 데이터를 조회.
• 예시:
  • Name에 대한 논클러스터 인덱스만 존재할 경우.
• SELECT Age FROM Users WHERE Name = 'John';

7. Key Look Up

• 논클러스터 인덱스 + 클러스터 인덱스 조합에서, Leaf Node에 없는 열을 가져오기 위해 클러스터 인덱스를 사용해 데이터를 검색.
• 예시:
  • Name 논클러스터 인덱스가 있고, 데이터 조회 시 UserID를 통해 연결.
• SELECT Age FROM Users WHERE Name = 'John';

---

포괄 열 (Included Column)

• 정의: 논클러스터 인덱스에서 키가 아닌 열을 추가로 포함하여 Leaf Node에서 데이터를 반환 가능하도록 구성.
• 장점:
  • Look Up을 줄이고 Seek를 사용할 수 있음.
  • 정렬되지 않으므로 성능 부담 감소.

포괄 열의 활용 전략

1. 자주 조회되는 컬럼을 포괄 열로 추가.
   • SELECT 절에서 많이 호출되는 열을 리프 노드에 저장하면 추가 조회를 줄일 수 있음.
2. 키와 포괄 열 중복
   • 포괄 열에 이미 키로 포함된 열을 추가해도 데이터 중복이 발생하지 않음.
   • 중복 정의는 의미가 없으므로 피하는 것이 좋음.
3. 복합 키의 순서
   • 조건절에서 가장 자주 사용되는 컬럼을 왼쪽에 배치.
   • 왼쪽부터 순서대로 조건을 사용해야 인덱스 효율이 극대화됨.

주의점

• 포괄 열은 정렬되지 않음: 조건절에서 포괄 열을 사용하는 경우 인덱스를 사용하지 못하고 전체 스캔이 발생할 수 있음.
• 포괄 열 추가로 성능 저하 가능성: 리프 노드에 저장되는 데이터가 많아져 디스크 I/O가 증가할 수 있음.
• 복합 키와 포괄 열의 조합 활용: 조회 및 정렬 조건을 고려해 키와 포괄 열을 적절히 설정해야 성능을 극대화할 수 있음.

 

복합 인덱스 예시 (3개 키 + 2개 포괄 열)

 

 

• 키: Name, Age, Email (검색 시 조건에 따라 순차적으로 활용)
• 포괄 열: Address, PhoneNumber (Leaf Node에만 저장, 정렬은 없음)

---

Leaf Node 동작

1. Leaf Node 구성:
   • 키: (Name, Age, Email) 조합
   • 포괄 열: Address, PhoneNumber
   • Leaf Node에는 복합 인덱스 키가 정렬된 상태로 저장됩니다.
     포괄 열은 정렬되지 않으며, Look Up 없이 반환 가능합니다.
2. 동작 방식:
   • 인덱스 키에 포함된 컬럼은 Seek 또는 Range Scan으로 검색 가능.
   • 포괄 열에 포함된 데이터는 RID Look Up 없이 즉시 반환.

---

조건에 따른 동작 방식

1. 첫 번째 키만 사용하는 경우

sql

코드 복사

SELECT Name, Address FROM Users WHERE Name = 'John';

• Name이 첫 번째 키이므로 Seek 사용.
• Address는 포괄 열에 포함되어 있어 추가 Look Up 없이 반환.

2. 첫 번째와 두 번째 키를 사용하는 경우

sql

코드 복사

SELECT Name, Age FROM Users WHERE Name = 'John' AND Age = 30;

• Name과 Age가 인덱스 키에 모두 포함되어 있으므로 Seek 사용.
• 효율적으로 범위를 좁혀 검색.

3. 첫 번째, 두 번째, 세 번째 키를 모두 사용하는 경우

sql

코드 복사

SELECT Email FROM Users WHERE Name = 'John' AND Age = 30 AND Email = 'john@example.com';

• 모든 키가 검색 조건에 포함되므로 Seek 활용.
• 정확히 하나의 Leaf Node를 검색.

4. 두 번째 키만 사용하는 경우

sql

코드 복사

SELECT Age FROM Users WHERE Age = 30;

• Age는 첫 번째 키가 아니므로 Index Seek 불가능.
• 대신 Table Scan 또는 Clustered Index Scan 발생.

5. 포괄 열만 사용하는 경우

sql

코드 복사

SELECT Address FROM Users WHERE Name = 'John';

• Name이 인덱스 키로 검색되며, Address는 포괄 열로 Look Up 없이 반환.

6. 범위 조건 사용 시

sql

코드 복사

SELECT Name, Email FROM Users WHERE Name LIKE 'J%' AND Age > 25;

• 범위 조건(LIKE, >)이 포함되면 해당 키 이후의 컬럼은 인덱스 효율이 떨어질 수 있음.
• Name을 기준으로 Seek를 시작하지만, 이후 조건은 Range Scan으로 처리.

---

동일한 컬럼이 키와 포괄 열 모두에 포함된 경우

• 인덱스 정의 시 동일한 컬럼이 키와 포괄 열에 중복 등록되면, 데이터베이스는 중복 저장합니다.
• 이는 스토리지 낭비를 초래하며, 일반적으로 피해야 합니다.

예시:

sql

코드 복사

CREATE NONCLUSTERED INDEX IX_Users_Name_Age ON Users (Name, Age) INCLUDE (Name, Address);

• Name은 키와 포괄 열에 모두 포함되어 중복 저장됩니다.
• 해결 방법:
  • INCLUDE 절에서 중복 컬럼을 제거합니다:
    sql

    코드 복사

    CREATE NONCLUSTERED INDEX IX_Users_Name_Age ON Users (Name, Age) INCLUDE (Address);

---

복합 인덱스의 순서가 중요한 이유

• 복합 인덱스는 첫 번째 키를 기준으로 정렬되며, 이후 키는 서브 정렬로 저장됩니다.
• 첫 번째 키가 조건에 포함되지 않으면 인덱스는 비효율적이거나 사용되지 않을 수 있습니다.

순서 변경 시 차이

1. 인덱스: (Name, Age, Email)
   • Name을 기준으로 Seek 가능.
2. sql
   코드 복사
   SELECT * FROM Users WHERE Name = 'John';
3. 인덱스: (Age, Name, Email)
   • Name이 첫 번째 키가 아니므로 Table Scan 발생.
4. sql
   코드 복사
   SELECT * FROM Users WHERE Name = 'John';