[Database] Clustered Index와 Non-clustered Index

앞선 포스트에서 Index(인덱스)에 대해 자세히 알아보았다. 인덱스는 크게 Clsutered Index와  Non-clustered Index로 나눌 수 있다. 이 두 종류의 인덱스에 대해 자세히 알아보자.

Clustered Index와 Non-clustered Index

Clustered Index

출처: https://velog.io/@gillog/SQL-Clustered-Index-Non-Clustered-Index

Clustered Index는 테이블의 레코드를 지정된 컬럼에 대해 물리적으로 재배열한다. Clustered Index는 테이블 당 한 개만 존재할 수 있고, primary key 제약조건을 지정하는 컬럼에 대해 자동으로 Clustered Index를 생성한다. 이렇기 때문에 우리가 일반적으로 테이블을 생성할 때 특정 컬럼에 primary key 제약조건을 지정했다면, 데이터가 자동으로 정렬되는 것이다.

Clustered Index를 생성한 컬럼을 기준으로 테이블의 데이터가 정렬되어 있기 때문에 속도면에서 우수한 성능을 보인다. 하지만 데이터의 추가/수정/삭제 시 매번 레코드를 정렬해야 하기 때문에 추가/수정/삭제의 성능이 저하된다.

Clustered Index의 문제점

출처: https://gwang920.github.io/database/clusterednonclustered/

만약 위 테이블에서 (ID=2, NAME=넬, Birth=1980)을 추가하면, 아래와 같이 추가될 것이다.

출처: https://gwang920.github.io/database/clusterednonclustered/

위에서 말한거와 같이 Clustered Index로 지정한 컬럼을 기준으로 데이터를 정렬하기 때문에 ID=2인 데이터를 추가할 경우 ID가 2보다 큰 데이터는 한 칸씩 아래로 이동하고, ID=2인 데이터가 해당 위치에 추가된다.

위의 테이블은 3개의 데이터만 이동하면 되지만 만약 ID가 2보다 큰 데이터가 10만, 100만 건 이상 있다고 가정한다면, Insert에 소모되는 비용이 굉장히 커질 것이다.

그렇기 때문에 Primary Key를 어떤 컬럼으로 선택하는가에 따라 데이터베이스의 성능이 좌우된다.

왜 우리는 ID를 PK로 설정하고 auto_increment 옵션을 설정하는가 ❔❔

우리는 보통 DB를 설계할 때 정수형의 ID를 PK로 설정하고 auto_increment 옵션을 설정한다. 우리가 왜 정수형의 ID를 PK로 설정하고 이를 auto_increment 옵션을 주는지 알아보자.

create table user (
    email varchar(100) primary key,
    password varchar(100) not null
);

위와 같이 email과 password 필드를 가지고 email을 PK로 하는 user 테이블이 있다고 가정해보자. 이 테이블을 만든 사람은 '이메일은 유저를 식별할 수 있는 필드이기 때문에 PK로 사용할 수 있어!'라고 생각했을 것이다. 하지만 email이 PK이기 때문에 email 컬럼을 통해 clustered index가 생성될 것이다. 이때 a로 시작하는 email을 가진 user를 추가하게 되면, 해당 email보다 사전상 뒤에 나타나는 email을 가진 레코드는 한 칸씩 아래로 이동하고 이 레코드가 해당 위치에 추가될 것이다. 대용량 테이블에서 이러한 작업은 극심한 성능 저하를 일으킬 것이다.

이러한 문제점은 추가할 레코드의 PK를 현재 테이블에 있는 레코드의 PK 보다 큰 값으로 설정한다면, 해결할 수 있을 것이다. 따라서 우리는 ID라는 별도의 필드를 PK로 설정하고 auto_increament 옵션을 주어 clustered index에서 발생할 수 있는 문제점을 해결한 것이다.

Clustered Index 구조

출처: https://junghn.tistory.com/entry/DB-%ED%81%B4%EB%9F%AC%EC%8A%A4%ED%84%B0-%EC%9D%B8%EB%8D%B1%EC%8A%A4%EC%99%80-%EB%84%8C%ED%81%B4%EB%9F%AC%EC%8A%A4%ED%84%B0-%EC%9D%B8%EB%8D%B1%EC%8A%A4-%EA%B0%9C%EB%85%90-%EC%B4%9D%EC%A0%95%EB%A6%AC

Clustered Index를 구성하기 위해 레코드를 해당 컬럼으로 정렬한 후에, 루트 페이지를 만들게 된다. Clustered Index는 루트 페이지와 리프 페이지로 구성되며, 리프 페이지는 데이터 그 자체이다. 즉, Index 자체에 데이터가 포함된다. Index Page를 키 값과 데이터 페이지 번호로 구성하고, 검색하고자하는 데이터의 키 값으로 페이지 번호를 검색하여 데이터를 찾는다.

Clustered Index 특징 정리

• 테이블 당 1개만 허용
• 물리적으로 레코드를 재배열한다.
• PK 설정 시 해당 컬럼에 대해 자동으로 Clustered Index가 생성된다.
• 인덱스의 리프 페이지가 곧 데이터이다. 즉, 테이블 자체가 인덱스이기 때문에 따로 인덱스 페이지를 만들지 않는다.
• Non-clustered Index보다 속도면에서 우수한 성능을 보이지만, 데이터 추가/수정/삭제 시 매번 정렬을 수행해야 하기 때문에 추가/수정/삭제의 성능이 떨어진다.

Non-clustered Index

출처: https://velog.io/@gillog/SQL-Clustered-Index-Non-Clustered-Index

Non-clustered Index는 물리적으로 레코드를 정렬하지 않은 상태로 데이터 페이지가 구성된다. 즉, 테이블의 레코드는 그대로두고 지정된 컬럼에 대해 정렬된 인덱스를 만든다. 물리적으로 레코드를 정렬하지 않기 때문에 Clustered Index보다 속도면에서 성능이 떨어지지만, 추가/수정/삭제의 성능은 더 뛰어나다.

Non-clustered Index는 unique 제약 조건을 설정한 컬럼에 대해 자동으로 Non-clustered Index를 생성한다. 따라서 테이블 당 여러개 존재 가능하다. 하지만 함부로 남용하면 오히려 시스템 성능이 저하될 수 있다. (성능 저하 문제는 Database Scan 포스트에서 설명)

Non-clustered Index 구조

출처: https://junghn.tistory.com/entry/DB-%ED%81%B4%EB%9F%AC%EC%8A%A4%ED%84%B0-%EC%9D%B8%EB%8D%B1%EC%8A%A4%EC%99%80-%EB%84%8C%ED%81%B4%EB%9F%AC%EC%8A%A4%ED%84%B0-%EC%9D%B8%EB%8D%B1%EC%8A%A4-%EA%B0%9C%EB%85%90-%EC%B4%9D%EC%A0%95%EB%A6%AC

Non-clustered Index는 데이터 페이지를 건들지 않고, 별도의 장소에 인덱스 페이지를 생성한다. Non-clustered Index의 인덱스 페이지는 키 값과 위치 포인터(ROWID)로 구성된다.

ROWID는 '파일그룹번호-데이터페이지번호-데이터페이지오프셋'으로 구성되는 포인팅 정보.

우선 인덱스 페이지의 리프 페이지에 인덱스로 구성한 컬럼을 정렬하고 위치 포인터(ROWID)를 생성한다. 위치 포인터는 Clustered Index와 달리 '페이지 번호 + #오프셋'이 기록되어 바로 데이터 위치를 가리킨다.

(2, indexTest2)를 찾는다고 가정해보면, 루트 페이지에서 101번 리프 페이지로 이동하고 101번 리프 페이지에 데이터 페이지가 102, 데이터 페이지 오프셋이 2으로 되어 있기 때문에 102번 페이지의 두 번째의 데이터를 가져온다.

Non-clustered Index 특징 정리

• 레코드는 정렬되지 않고, 인덱스 페이지만 정렬된다.
• 한 테이블에 여러 개의 Non-clustered Index를 생성할 수 있다.
• unique 제약조건 설정 시 자동으로 Non-clustered Index가 생성된다.
• 물리적으로 테이블이 정렬되어 있지 않기 때문에 Clustered Index보다 속도면에서 성능은 떨어지지만, 데이터의 입력, 수정, 삭제 성능은 더 뛰어나다.
• 인덱스를 생성할 때 데이터 페이지는 그냥 둔 상태에서 별도의 인덱스 페이지를 따로 만들기 때문에 추가적인 용량이 필요하다.

Clustered Index & Non-clustered Index

출처: https://velog.io/@gillog/SQL-Clustered-Index-Non-Clustered-Index

 

Multiple-column Index

출처: https://steady-coding.tistory.com/546

다중 컬럼 인덱스는 두 개 이상의 컬럼을 조합하여 생성한 인덱스이다.

다중 컬럼 인덱스에서 중요한 것은 인덱스의 두 번째 컬럼은 첫 번째 컬럼에 의존해서 정렬되어 있다는 것이다. 즉, 두 번째 컬럼은 첫 번째 컬럼의 값이 같은 레코드에서만 정렬되어 있다. 따라서 다중 컬럼 인덱스에서는 컬럼의 순서가 상당히 중요하다. '=' 조건과 같이 개수가 적은 데이터를 조회하는 컬럼을 앞에 설정하고, 범위 검색과 같이 개수가 많은 데이터를 조회하는 컬럼을 뒤쪽에 설정해야 효율적이다.

또한, 다중 컬럼 인덱스는 단일 컬럼 인덱스보다 추가/수정/삭제 시 더 비효율적이기 때문에 가급적으로 추가/수정/삭제를 하지 않는 컬럼을 선정하는 것이 좋다.

언제 Multiple-column Index를 사용해야 할까 ❔❔

데이터 조회 시 단일 컬럼 인덱스를 여러 개 사용해야 하는 경우가 많다면 다중 컬럼 인덱스를 고려해볼 수 있다.

에를 들어 A, B 컬럼을 조건절에 포함한 검색을 자주한다고 가정해보자,

A, B 컬럼 각각 인덱스를 설정할 경우 옵티마이저는 A 컬럼과 B 컬럼 중 어떤 컬럼이 더 빠르게 검색되는지(레코드 수가 적은지) 판단하고 더 빠른 컬럼의 인덱스를 통해 레코드를 탐색하고 이 레코드에서 B 컬럼을 탐색한다.

A, B 컬럼 복합 인덱스를 설정할 경우 인덱스에 A와 B 컬럼의 정보가 있기 때문에 바로 탐색이 가능하므로 위의 방식보다 빠르다.

하지만 where 절에 B 컬럼만 사용할 경우 이 복합 인덱스는 B가 A에 의존적으로 정렬되기 때문에 해당 인덱스를 탐색하지 않는다. (where 절에 A 컬럼만 사용할 경우는 인덱스를 탐색한다.)

 

어떤 컬럼으로 인덱스를 만들어야 할까 ❔❔

Cardinality(카디널리티)

카디널리티는 전체 행에 대한 특정 컬럼의 중복 수치를 나타내는 지표이다. 예를 들어, 테이블의 '성별' 컬럼이 '남자'와 '여자'로 구성된다면, 성별 컬럼의 카디널리티는 2가 된다. 반대로 주민등록번호 같이 개인별 고유한 값을 가지는 컬럼의 카디널리티는 테이블에 저장된 레코드의 개수가 된다.

✅ 즉, 중복도가 '낮으면' 카디널리티가 높고, 중복도가 '높으면' 카디널리티가 낮다.

 

오라클에서는 아래와 같은 쿼리로 특정 컬럼의 카디널리티를 구할 수 있다.

select count(distinct gender) from user;

Selectivity(선택도)

선택도는 데이터의 집합에서 특정 값을 얼마나 잘 골라낼 수 있는지에 대한 지표이다. 선택도가 1이라는 뜻은 모든 값이 고유하다는 의미이다. 선택도는 카디널리티로부터 계산할 수 있다. 선택도는 카디널리티에 비례한다.

✅ 즉, 선택도 또한 중복도가 '낮으면' 선택도가 높고, 중복도가 '높으면' 선택도가 낮다.

Selectivity = Cardinarlity / Total Number of Records

선택도는 어떤 컬럼으로 인덱스를 생성하면 좋을지 고를 때 자주 사용한다. 선택도가 높은 컬럼에 인덱스를 생성하면 인덱스가 특정 레코드를 잘 골라내서 인덱스의 효율이 높아진다. 따라서 Primary Key, Candidate Key, Unique Key는 거의 1에 가까운 선택도를 가지기 때문에 이러한 컬럼으로 인덱스를 생성할 경우 효율적이다. 반대로 선택도가 낮은 컬럼에 인덱스를 생성할 경우 인덱스 효율이 떨어지고, 최악의 경우 Table Full Scan이 될 수 있다.

 

✅ 데이터베이스에서 인덱스의 선택도를 높이는 방법은 간단하다. 두 개 이상의 컬럼을 조합해 인덱스를 생성하면, 조합된 인덱스 키의 카디널리티가 증가하고 선택도가 증가하여 효율적인 인덱스를 만들 수 있다.

---

Reference

https://gwang920.github.io/database/clusterednonclustered/

https://velog.io/@gillog/SQL-Clustered-Index-Non-Clustered-Index

https://www.youtube.com/watch?v=NkZ6r6z2pBg&t=646s 

https://junghn.tistory.com/entry/DB-%ED%81%B4%EB%9F%AC%EC%8A%A4%ED%84%B0-%EC%9D%B8%EB%8D%B1%EC%8A%A4%EC%99%80-%EB%84%8C%ED%81%B4%EB%9F%AC%EC%8A%A4%ED%84%B0-%EC%9D%B8%EB%8D%B1%EC%8A%A4-%EA%B0%9C%EB%85%90-%EC%B4%9D%EC%A0%95%EB%A6%AC

https://itholic.github.io/database-cardinality/

https://hbase.tistory.com/195

https://steady-coding.tistory.com/546

https://code-factory.tistory.com/24