클러스터형 인덱스(Clustered Index) 구조

 클러스터 인덱스 (Clustered Index)

use testdb;

create database if not exists testdb;
USE testdb;
 
 
DROP TABLE IF EXISTS clustertbl;

CREATE TABLE clustertbl ( -- 클러스터 테이블
    userID CHAR(8),
    name VARCHAR(30)
);
alter table clustertbl modify column name varchar(30);

-- 데이터 삽입
INSERT INTO clustertbl VALUES ('HK123456', 'Harry Kane');
INSERT INTO clustertbl VALUES ('SR123456', 'Son Heung-Min');
INSERT INTO clustertbl VALUES ('KD123456', 'Kevin De Bruyne');
INSERT INTO clustertbl VALUES ('MS123456', 'Mohamed Salah');
INSERT INTO clustertbl VALUES ('BF123456', 'Bruno Fernandes');
INSERT INTO clustertbl VALUES ('VM123456', 'Virgil van Dijk');
INSERT INTO clustertbl VALUES ('RL123456', 'Raheem Sterling');
INSERT INTO clustertbl VALUES ('AM123456', 'Antonio Martial');
INSERT INTO clustertbl VALUES ('SM123456', 'Sadio Mane');
INSERT INTO clustertbl VALUES ('JR123456', 'James Rodriguez');

-- 결과 확인
SELECT * FROM clustertbl;

ALTER TABLE clustertbl 
ADD CONSTRAINT pk_clustertbl_userID PRIMARY KEY (userID);

 

 

 

인덱스 순서로 자동 정렬이 된다.

 

 

 

 

 

 

 

 

 

 
- 클러스터 인덱스는 테이블의 데이터가 물리적으로 정렬된 순서에 따라 저장되는 인덱스이다. 즉, 클러스터 인덱스에 의해 결정된 순서대로 테이블의 실제 데이터가 배치된다. 하나의 테이블에는 하나의 클러스터 인덱스만 존재할 수 있는데 테이블의 물리적 데이터가 인덱스 순서에 따라 정렬되기 때문이다. 데이터 자체가 인덱스 키에 따라 정렬되어 저장되므로, 특정 범위 내에서 데이터를 검색할 때 매우 빠르다. 클러스터 인덱스의 리프 노드는 실제 데이터 페이지를 가리켜 인덱스를 조회하면 곧바로 데이터에 접근할 수 있다.

 

 

- 빠른 범위 검색: 데이터가 물리적으로 정렬되어 있어 범위 검색 시 성능이 뛰어난다.
- 높은 효율성: 테이블이 클러스터 인덱스 순서대로 정렬되어 있으므로, 데이터 검색 시 디스크 I/O가 최소화된다.

 

 단점:
- 데이터 삽입/삭제 시 성능 저하: 데이터가 정렬된 상태로 유지되어야 하므로, 중간 위치에 데이터를 삽입하거나 삭제할 때 성능 저하가 발생할 수 있다.
- 테이블 재정렬 필요: 클러스터 인덱스를 생성하면 테이블이 인덱스 키 순서로 정렬되기 때문에, 인덱스 생성 과정에서 시간이 오래 걸릴 수 있다.

 

 

 

 

 

 

새로운 데이터를 추가했을 때 마지막에 들어가지 않고 페이지 분할이 발생한다.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 보조 인덱스 (Non-Clustered Index)

- 보조 인덱스는 테이블의 데이터와 별도로 저장되는 인덱스로, 테이블의 데이터와는 독립적으로 인덱스가 생성된다. 보조 인덱스는 인덱스가 가리키는 키 값과 그에 해당하는 데이터의 위치를 저장한다. 하나의 테이블에 여러 개의 보조 인덱스를 생성할 수 있어 테이블의 다양한 컬럼에 대해 검색 성능을 최적화하는 데 유용하다. 리프 노드에 데이터 위치 정보 저장: 보조 인덱스의 리프 노드에는 실제 데이터가 저장되지 않고, 데이터의 위치를 가리키는 포인터가 저장된다. 이 포인터를 통해 테이블의 데이터를 조회한다.

- 다양한 쿼리 최적화: 여러 개의 보조 인덱스를 만들어 다양한 컬럼에 대해 쿼리 성능을 최적화할 수 있다.
- 데이터 정렬에 영향 없음: 테이블의 물리적 정렬과 독립적으로 인덱스를 생성할 수 있으므로, 테이블 자체의 데이터 삽입/삭제 성능에는 영향을 덜 준다.

 

 

 단점:
- 추가적인 디스크 I/O 필요: 보조 인덱스는 데이터의 위치를 가리키는 포인터를 통해 실제 데이터를 검색하므로, 클러스터 인덱스보다 더 많은 디스크 I/O가 필요할 수 있다.
- 추가적인 디스크 공간 필요: 인덱스와 실제 데이터가 별도로 저장되므로, 보조 인덱스를 많이 생성하면 더 많은 디스크 공간이 필요한다.

 

 

 

 보조 인덱스에 데이터 추가 시 인덱스 변경

1. 보조 인덱스의 구조:
   - 보조 인덱스는 일반적으로 B-트리(Balanced Tree) 구조로 구현된다.
   - 인덱스는 루트 노드, 중간 노드, 리프 노드로 구성되며, 리프 노드에는 인덱스 키 값과 해당 키 값에 대응하는 데이터의 위치를 가리키는 포인터가 저장된다.

2. 데이터 추가 시:
   - 새로운 데이터가 테이블에 삽입되면, 해당 데이터의 인덱스 키 값이 보조 인덱스에 추가된다.
   - B-트리 구조에서는 새로운 인덱스 엔트리를 올바른 위치에 삽입하기 위해, 트리의 노드들이 조정된다. 이 과정에서 노드의 분할이나 병합이 발생할 수 있다.

3. 리프 노드에 변경:
   - 리프 노드에는 새로운 인덱스 키 값과 함께 데이터 페이지를 가리키는 포인터(예: ROWID)가 삽입된다.
   - 이 포인터는 실제 데이터가 저장된 테이블의 위치를 가리킨다.

4. 실제 데이터 위치 변경:
   - 보조 인덱스는 물리적 데이터 위치와 독립적으로 존재하므로, 데이터 삽입에 따라 데이터의 실제 물리적 위치가 변경될 수 있다.
   - 하지만, 보조 인덱스는 이러한 물리적 위치 변경을 반영하여 인덱스 키와 새로 할당된 데이터 위치를 가리키는 포인터를 업데이트한다.

 

 

 

 
 삽입 후 인덱스 트리 변화

1. 노드 조정: 새로운 인덱스 키를 삽입하면, 트리의 특정 위치에 추가된다. 이 과정에서 노드가 가득 차면 분할이 발생할 수 있으며, 이는 트리의 균형을 유지하는 데 필요한다.
2. 리프 노드 갱신: 리프 노드가 업데이트되어, 새로운 인덱스 항목이 추가되고, 이 항목은 테이블의 새로운 행이 저장된 위치를 가리킨다.

 

 인덱스를 관리하는 방법

데이터베이스 시스템은 이러한 인덱스 변경 작업을 자동으로 처리한다. 따라서, 개발자는 인덱스가 데이터 삽입, 삭제, 업데이트에 따라 어떻게 변화하는지를 명시적으로 관리할 필요가 없다. 그러나 인덱스가 복잡해지거나 테이블의 크기가 커질수록, 인덱스 관리에 따른 성능 오버헤드가 발생할 수 있다. 이러한 경우, 정기적인 인덱스 재구성이나 최적화를 통해 성능을 유지하는 것이 좋다.
 
- 보조 인덱스에 데이터가 추가되면, B-트리 구조가 유지되도록 인덱스가 재조정된다.
- 새로운 데이터가 추가될 때, 보조 인덱스는 데이터의 실제 위치를 가리키는 포인터를 업데이트하여 데이터를 정확하게 조회할 수 있도록 한다.

 

 

 

 

 

 

 특징                           클러스터 인덱스 (Clustered Index)          보조 인덱스 (Non-Clustered Index)     
 
데이터 정렬                 물리적으로 정렬된 순서로 저장              물리적 데이터와는 별도로 저장   
 인덱스 개수                 테이블당 하나                                        여러 개 가능                          
 리프 노드                   실제 데이터 저장                                     데이터 위치 정보 저장                 
 검색 성능                   범위 검색 시 매우 빠름                            포인터를 통해 데이터 검색, 성능 다소 낮음 
 디스크 I/O                  디스크 I/O가 효율적                                추가적인 디스크 I/O 필요              
 삽입/삭제 성능              데이터 삽입/삭제 시 성능 저하 가능     데이터 정렬과 독립적, 영향 덜함       
 디스크 공간                 테이블의 데이터 저장 공간에 종속         추가적인 디스크 공간 필요             

  

 

 

 

 

  클러스터 인덱스와 보조 인덱스의 혼합 사용

 

CREATE TABLE mixedclustertbl (
    userID CHAR(8),                -- 클러스터 인덱스를 적용할 컬럼
    name VARCHAR(30),              -- 선수 이름
    position VARCHAR(20),          -- 선수 포지션
    team VARCHAR(30),              -- 소속 팀
    nationality VARCHAR(30)        -- 국적
);
-- 데이터 삽입
INSERT INTO mixedclustertbl VALUES ('HK123456', 'Harry Kane', 'Forward', 'Tottenham Hotspur', 'England');
INSERT INTO mixedclustertbl VALUES ('SR123456', 'Son Heung-Min', 'Forward', 'Tottenham Hotspur', 'South Korea');
INSERT INTO mixedclustertbl VALUES ('KD123456', 'Kevin De Bruyne', 'Midfielder', 'Manchester City', 'Belgium');
INSERT INTO mixedclustertbl VALUES ('MS123456', 'Mohamed Salah', 'Forward', 'Liverpool', 'Egypt');
INSERT INTO mixedclustertbl VALUES ('BF123456', 'Bruno Fernandes', 'Midfielder', 'Manchester United', 'Portugal');
INSERT INTO mixedclustertbl VALUES ('VM123456', 'Virgil van Dijk', 'Defender', 'Liverpool', 'Netherlands');
INSERT INTO mixedclustertbl VALUES ('RL123456', 'Raheem Sterling', 'Forward', 'Chelsea', 'England');
INSERT INTO mixedclustertbl VALUES ('AM123456', 'Antonio Martial', 'Forward', 'Manchester United', 'France');
INSERT INTO mixedclustertbl VALUES ('SM123456', 'Sadio Mane', 'Forward', 'Bayern Munich', 'Senegal');
INSERT INTO mixedclustertbl VALUES ('JR123456', 'James Rodriguez', 'Midfielder', 'Al-Rayyan', 'Colombia');

select * from mixedclustertbl;

 

 

 

 

 

 

 

■ cluster index 생성

■ non-cluster index 생성

 

 

 

 

 

 

 

■실행계획