클러스터드 인덱스와 비클러스터형 인덱스

인덱스는 테이블이나 뷰에서 행의 검색 속도를 높일 수 있다. 

클러스터드 인덱스

• 클러스터형 인덱스는 해당 키 값을 기반으로 테이블이나 뷰의 데이터 로우를 정렬하고 저정한다.이러한 키 값은 인덱스 정의에 포함된 컬럼이다. 데이터 로우 자체는 한 가지 순서로만 저장될 수 있으므로 테이블당 클러스터형 인덱스는 하나만 존재할 수 있다. 때문에 일반적으로는 PK 또는 unique not null 을 사용한다.
• 테이블의 데이터 로우가 정렬된 순서로 저장될 때만 테이블에 클러스터형 인덱스가 포함된다. 테이블에 클러스터형 인덱스가 있는 경우 테이블을 클러스터형 테이블이라고 한다. 테이블에 클러스터형 인덱스가 없는 경우 해당 데이터 로우는 힙이라는 순서가 지정되지 않은 구조에 저장된다.

비클러스터형 인덱스

• 비클러스터형 인덱스에는 데이터 로우와 별개의 구조가 있다. 비클러스터형 인덱스에는 비클러스터형 인덱스 키 값이 포함되며 각 키 값 항목에는 키 값이 포함된 데이터 로우에 대한 포인터가 있다.
• 비클러스터형 인덱스에서 데이터 로우에 대한 인덱스 로우의 포인터를 로우 로케이터라고 한다. 로우 로케이터의 구조는 데이터 페이지가 힙 또는 클러스터형 테이블에 저장되는지 여부에 따라 달라진다. 힙의 경우 로우 로케이터는 로우에 대한 포인터이다. 클러스터형 테이블의 경우 로우 로케이터는 클러스터형 인덱스 키이다.
• 비클러스터드 인덱스는 인덱스와 데이터가 분리되어서 관리되는 형태이다. 즉 데이터 행으로부터 동립적이며 데이터를 간접적으로 두고 참조하기 때문에, 중간에 참조용 테이블 하나가 반드시 필요해진다.
• 키가 아닌 컬럼을 비클러스터형 인덱스의 리프 수준에 추가하여 기존 인덱스 키 제한을 바이패스하고 완전히 적용된 인덱싱된 쿼리를 실행할 수 있다. 때문에 인덱스가 정렬된 상태를 유지할 필요가 없어데이터가 중간에 추가/수정 되어도 리스크가 덜하다. 하지만 인덱스용 페이지를 위한 매핑과정이 있기 때문에 추가적인 공간이 필요할 수 있고 오버헤드가 증가하며 여러 열에 인덱스를 걸어둘 수 있습니다.

비교

두개의 설명을 통해 정렬된 상태를 유지하는지, 추가 공간이 필요한지, 몇개의 인덱스를 추가할 수 있는지 알 수 있다.

각 인덱스이 특징으로 인해 상황에 따라 무엇이 좋다 라고 특정하기가 어렵다. 

클러스터드 인덱스	클러스터드 인덱스	비클러스터형 인덱스
차이	물리적으로 행을 재배열	물리적으로 재배열 하지 않는다
크기	인덱스 페이지 용량이 넌 클러스터드 인덱스 페이지 용량보다 작다	클러스터드 인덱스 페이지 용량보다 크다
선택도	30% 이내에서 사용해야 좋은 선택도	3% 이내에서 사용해야 좋은 선택도
최대 갯수	테이블당 1개	테이블당 249개

데이터 예시

클러스터드의 흐름 예시

• [데이터베이스 구성] 데이터베이스에는 학생들의 정보를 저장하는 테이블이 있으며 학생의 학번과 성적으로 컬럼이 구성되어 있다.
• [클러스터드 인덱스 생성] 성적 테이블의 학번 컬럼에 클러스터드 인덱스(Primary Key)를 생성한다.
• [인덱스 정렬] 클러스터드 인덱스가 생성되면 테이블은 학번 기준으로 레코드가 물리적으로 정렬된다.
• [데이터 추가] 새로운 학생의 성적 정보가 추가된다. 이 데이터는 테이블의 끝에 추가되며 새로운 학번이 추가되면 클러스터드 인덱스의 정렬된 위치에 맞게 입력된다.
• [데이터 업데이트] 특정 학생의 성적 정보가 업데이트 되어 해당 레코드가 클러스터드 인덱스에서의 위치가 변경될 수 있다. 데이터베이스는 변경된 레코드를 인덱스의 위치에 맞게 재배치한다.
• [데이터 검색] 특정 학번을 조건으로 학생의 성적을 검색할 때 클러스터드 인덱스를 사용하여 데이터를 찾는다.

-- 테이블 생성
CREATE TABLE student_scores (
    -- MySQL 에서 PRIMARY KEY 또는 Unique 을 이용하면 클러스터드 인덱스를 생성할 수 있다.
    student_id INT PRIMARY KEY, -- 학번 
    grade FLOAT -- 성적
);

-- 데이터 추가
INSERT INTO student_scores (student_id, grade) VALUES (201803060, 300);

-- 데이터 업데이트
UPDATE student_scores SET grade = 400 WHERE student_id = 201803060;

-- 데이터 검색
SELECT * FROM student_scores WHERE student_id = 201803060;

 

비클러스터의 흐름 예시

흐름을 통해 데이터의 물리적인 순서와 인덱스의 논리적인 순서가 독립적으로 유지되기에 데이터 수정 작업이 성능에 미치는 영향이 작다.

• [데이터베이스 구성] 데이터베이스에는 학생들의 정보를 저장하는 테이블이 있으며 학생의 학번과 성적으로 컬럼이 구성되어 있다.
• [비클러스터드 인덱스 생성] 성적 테이블의 학번 컬럼에 비클러스터드 인덱스를 생성한다. 
• [인덱스 생성] 비클러스터드 인덱스가 생성되면 인덱스를 별도의 구조로 유지한다. 이 구조는 데이터의 물리적인 순서와는 독립적으로 관리된다. 
• [데이터 삽입] 새로운 학생의 성적 정보가 추가된다. 이 데이터는 테이블의 끝에 추가되며 새로운 학번을 비클러스터드 인덱스에 추가한다.
• [데이터 갱신] 특정 학생의 성적 정보가 업데이트 되며 이 경우 해당 레코드가 인덱스에서의 위치는 변경되지 않는다.
• [데이터 검색] 특정 학번을 기반으로 학생의 성적을 검색하는 경우, 데이터베이스는 비클러스터드 인덱스를 사용하여 빠르게 데이터를  찾는다.

-- 테이블 생성
CREATE TABLE student_scores_1 (
    student_id INT, -- 학번 컬럼
    grade FLOAT -- 성적
);

-- [비클러스터드 인덱스 생성] 학번 컬럼에 비클러스터드 인덱스 생성
CREATE INDEX idx_student_id
ON student_scores_1 (student_id);

-- [데이터 삽입] 새로운 학생의 성적 정보 추가
INSERT INTO student_scores_1 (student_id, grade) VALUES (201803060, 300); -- 예시: 학번 1001의 성적 85.5 추가

-- [데이터 갱신] 특정 학생의 성적 정보 업데이트
UPDATE student_scores_1 SET grade = 400 WHERE student_id = 201803060;

-- [데이터 검색] 특정 학번을 기반으로 학생의 성적 검색
SELECT * FROM student_scores_1 WHERE student_id = 201803060;