[DB] Index ( Clustered & Non-Clustered )

자바니또 2022. 1. 16. 12:39

쿼리를 잘 짠다는 것의 척도가 되는 1순위는 성능일 것입니다. 이번 포스팅에서는 대다수의 성능의 원인이 되는 인덱스에 대해 공부한 내용을 적어보려합니다.
혹시나 잘못된 정보가 있다면 댓글로 알려 주시길 바랍니다.

Index란?

인덱스는 실생활에서 쉽게 볼 수 있습니다. 예를 하나 들면, 책의 찾아보기와 같습니다.
책의 찾아보기는 원하는 정보를 빠르게 접근할 수 있도록 도우면서 동시에 찾아보기 목록 또한 빠르게 찾을 수 있도록 사전순으로 정렬되어 있습니다.
DB의 인덱스도 다르지 않습니다. Index는 원하는 데이터를 빠르게 조회하기 위해 정렬된 찾아보기와 같습니다.

만약 인덱스가 없다면 조금만 복잡한 SQL을 실행해도 몇번이고 Full Table Scan을 반복할 것이고 대용량 데이터베이스의 경우 굉장히 느릴 것입니다.
인덱스는 Range Scan을 가능하게 하여 검색 성능을 향상시킬 수 있습니다.
특히 대용량 데이터베이스에서 인덱스만 잘 설정해도 굉장히 큰 성능 향상을 얻을 수 있습니다.

Full Table Scan은 디스크에 존재하는 테이블 레코드들을 모두 탐색하는 것이고, Range Scan은 일부만 탐색하는 것입니다.

Index 저장 구조(B-Tree)와 특징

인덱스는 메모리 상에서 Tree 구조로 존재합니다. 이번 포스팅에서는 DB 인덱스 구조 중 흔하게 사용되는 B-Tree 기준으로 알아봅니다.

위의 그림은 알파벳 오름차순으로 정렬된 B-Tree 구조 인덱스를 간단하게 표현한 것입니다.
B-Tree는 이진 트리와는 다르게 분기가 2개 이상 가능하며 균형 트리로서 어떤 상황에서도 평균적인 검색 성능을 보입니다.
또한 하나의 노드는 1개 이상의 키를 가지며 각 키는 자식 노드의 분리 값으로 작동합니다.

B-Tree의 노드를 DB 인덱스에서는 페이지라고 부르며 각 페이지의 여러 키 값들은 자식 페이지 번호를 가지고 있어서 참조가 가능합니다.

`GGG`를 검색한다면 어떤 순서로 검색할까?

루트 페이지부터 검색을 시작한다. 찾고자 하는 GGG를 선형 탐색한다. GGG보다 순서가 뒤인 LLL을 만나서 탐색을 멈추고 바로 직전 키인 AAA 키가 가진 자식 페이지 번호를 참조한다.
GGG를 선형 탐색한다. III를 만나면 탐색을 멈추고 그 직전 키인 FFF의 자식 페이지를 참조한다.
GGG를 찾았다. 만약 없었다면 더이상 자식 페이지가 없기 때문에 GGG가 없다고 판단한다.

Insert, Update, Delete SQL을 실행하면 인덱스는 어떻게 동작할까?

인덱스로 설정된 컬럼에 데이터가 Insert 될 때 들어가야 할 페이지에 자리가 없다면 페이지를 분할해서 데이터를 추가합니다. 이때 페이지 분할은 트리구조를 유지하기 위해 생각보다 복잡한 과정을 거치기 때문에 오버헤드가 발생합니다.
인덱스로 설정된 컬럼에 데이터가 Delete 되면 해당 키 값을 사용하지 않는다는 표시를 합니다. 만약 키 값을 제거한다면 페이지의 합성을 하는 오버헤드가 발생할 수 있습니다.
인덱스로 설정된 컬럼에 데이터가 Update 되면 해당 키 값을 사용하지 않는다고 표시를 하고 새롭게 키 값을 추가합니다.

인덱스 특징

트리 구조를 만들기 위해서 추가적인 메모리가 필요합니다. 보통 10% 정도의 추가적인 메모리가 필요하다고 합니다.
한 페이지의 크기는 MySQL에서는 16KB 이고, SQL Server 에서는 8KB 라고 합니다. 설정 파일에서 변경 가능합니다.
한 개 이상의 컬럼을 인덱스로 설정한다면 첫 번째 컬럼으로 페이지를 나누고 페이지 안에서 나머지 컬럼으로 정렬을 합니다.
인덱스 컬럼을 무엇으로 설정하냐에 따라 성능차이가 많이 날 수 있습니다.

Clustered Index & Non-Clustered Index

테이블의 레코드들은 DB서버 디스크에 저장됩니다. 인덱스의 종류에 따라 DB서버 디스크에 레코드가 저장되는 방식이 다릅니다. 이해를 돕기 위해 그림을 같이 보겠습니다.

Clustered Index

위의 테이블은 (id, name)을 컬럼으로 가지는 테이블에 id를 Clustered Index로 설정한 그림입니다.
주의 깊게 볼 점은 인덱스 트리의 리프 페이지가 디스크에 존재 한다는 점입니다.
즉 Clustered Index는 디스크에 저장하는 데이터 또한 정렬하여 페이지에 저장합니다.
이 페이지를 테이블의 레코드(데이터)가 들어있다 하여 데이터 페이지라고 합니다.

정리하면 특징은 다음과 같습니다.

디스크에 저장된 데이터들도 페이지(데이터 페이지)로 만들어서 트리 구조를 만든다.
새로운 레코드가 삽입되면 디스크에 있는 데이터 페이지들 까지 재정렬이 이루어진다.
디스크의 레코드가 정렬된 상태기 때문에 Range Scan이 가능하다.

Clustered Index는 다음과 같은 방법으로 만들 수 있습니다. (MySQL)

Primary Key를 설정하면 자동으로 생성된다.
PK가 없을 때 not null 컬럼을 인덱스로 설정하면 Clustered Index가 된다.

그럼 실제로 Clustered Index를 설정하면 디스크의 데이터 또한 재정렬 되는지 확인해 보겠습니다.
테이블은 USER(ID, NAME, INSERT_SEQ)로 만들고 PK와 다른 인덱스는 만들지 않았습니다.

데이터 삽입 후 조회

    insert into user values('BBK', '바비킴', 1);
    insert into user values('KBS', '김범수', 2);
    insert into user values('LJB', '임재범', 3);
    insert into user values('SSK', '성시경', 4);
    insert into user values('EJW', '은지원', 5); 
    insert into user values('KKH', '김경호', 6); 
    insert into user values('YJS', '윤정수', 7); 

    select * from user -- Full Table Scan(디스크에 저장된 순서대로 꺼낸다.)

저장한 순서대로 디스크에 저장되었을테고 예상대로 결과가 나왔습니다.

PK 생성 (Clustered Index 생성) 후 조회

    alter table user add Primary key (id);

    select * from user; -- Full Table Scan(디스크에 저장된 순서대로 꺼낸다.)

Clustered Index로 설정한 id를 기준으로 디스크에 있는 레코드들을 오름차순으로 정렬되었다.

Non-Clustered Index

Non-Clustered Index 구조를 간단하게 그려봤습니다. 위의 그림은 Clustered Index가 없는 상태를 가정한 그림입니다.
주의 깊게 볼 점은 메모리 안에 리프 페이지가 있다는 것과 힙 파일이라는 처음 보는 단어의 등장입니다.
리프 페이지는 데이터를 가지고 있지 않고 힙 파일의 레코드 주소를 가지고 있습니다..

힙 파일(또는 힙 테이블)이란 Clustered Index가 없을 때 디스크에 저장되는 레코드 형식입니다. (흔히 아는 메모리 힙이 아닙니다.)
정렬되지 않은 레코드들이며 삽입된 시간 순서대로 파일 끝에 나열됩니다.

정리하면 특징은 다음과 같습니다.

인덱스 트리가 메모리에만 존재한다.
리프 페이지가 데이터를 가지고 있지 않고 디스크의 레코드 주소를 가지고 있다.
Clustered Index가 없는 상태일 땐 디스크에 힙 파일 형태로 레코드들이 저장되며, 데이터의 삽입이 발생해도 메모리 상의 인덱스들만 재정렬된다.
디스크의 데이터가 정렬된 상태가 아니기 때문에 Range Scan 이 불가능하다.

Non-Clustered Index는 다음과 같은 방법으로 만들 수 있습니다.

Unique Key를 생성하면 자동으로 생성된다.
인덱스 생성 구문을 실행하면 기본적으로 Non-Clustered Index가 생성된다.

그럼 Non-Clustered Index의 생성이 디스크의 레코드와 무관한지 확인해 보겠습니다.
테이블은 USER(ID, NAME, INSERT_SEQ)로 만들고 PK와 다른 인덱스는 만들지 않았습니다.

데이터 삽입 후 조회

    insert into user values('BBK', '바비킴', 1); 
    insert into user values('KBS', '김범수', 2); 
    insert into user values('LJB', '임재범', 3); 
    insert into user values('SSK', '성시경', 4); 
    insert into user values('EJW', '은지원', 5); 
    insert into user values('KKH', '김경호', 6); 
    insert into user values('YJS', '윤정수', 7); 

    select * from user; -- Full Table Scan(디스크에 저장된 순서대로 꺼낸다.)

저장한 순서대로 디스크에 저장되었을테고 예상대로 결과가 나왔습니다.

Non-Clustered Index 생성 후 조회

    create index idx_idcol on user(id); 

    select * from user;

id 컬럼으로 인덱스 생성을 했지만 생성 전과 결과는 같습니다. 메모리 상의 인덱스 트리만 정렬이 되고 디스크는 정렬되지 않기 때문입니다.

Clustered + Non-Clustered

우리는 PK와 UK를 같이 사용하는 경우가 흔합니다.
같이 사용하면 어떤 모습일지 그림으로 확인해 보겠습니다. 테이블 user(id, name) 에서 id는 Clustered 로 name은 Non-Clustered로 하겠습니다.

Non-Clustered Index의 리프 페이지가 디스크의 레코드 주소가 아니라 Clustered Index 키 값을 주소 값으로 사용하여 Clustered Index의 루트 페이지를 참조하는 구조입니다.
만약 Non-Clustererd Index가 다른 컬럼으로 하나 더 있다고 해도 모든 Non-Clustered Index는 Clustered Index의 루트 페이지를 참조하도록 되어있습니다.

왜 이런 구조를 가질까?

만약 Non-Clustered Index와 Clustered Index가 서로 독립적이라고 생각 해 보겠습니다. 주의할 점은 Non-Clustered Index의 리프 페이지가 힙 파일을 참조하는 것이 아닙니다. Clustered Index가 있기 때문에 디스크는 정렬된 데이터 페이지이고 그 레코드 주소를 참조할 것입니다.

만약 새로운 레코드의 삽입이 발생하여 Clustered Index 트리가 재정렬 된다면 어떻게 될까요?

데이터 페이지도 물론 재정렬 될 것이고 모든 Non-Clustered Index 트리가 재정렬 될 것입니다. 삽입 한번에 이러한 일이 발생한다면 재앙일 것입니다.

Index 사용시 주의사항

인덱스를 남용하면 안된다.

인덱스는 읽기 작업의 성능은 올려주지만 쓰기 작업(Insert, Update)에서는 B-Tree 구조를 유지하기 위해 페이지 분할이 일어나서 성능이 떨어집니다.
또 인덱스도 메모리를 사용하기 때문에 남용하면 메모리 낭비가 발생합니다.
주기적으로 조회에 사용되지 않는 Index는 제거해 주는게 좋습니다.
수정 빈도와 읽기 빈도를 고려해서 인덱스를 설정하는 것이 바람직합니다.

Clustered Index를 제거할 때는 신중해야 한다.

Non-Clustered Index를 같이 사용하고 있을 경우, Clustered Index 부터 삭제하면 Non-Clustered Index의 리프 페이지가 가진 주소 값을 디스크의 레코드 주소 값으로 모두 변경해야 합니다.
이는 굉장히 많은 시간이 걸릴 수 있으므로 충분히 고민할 필요가 있습니다.

인덱스 컬럼을 선택할 때는 카디널리티가 높은 것을 선택해야 한다.

카디널리티란 해당 컬럼 값의 개수를 의미합니다.
이때 중복을 제거한 개수이며 카디널리티를 알아보는 SQL문은 SELECT COUNT(distinct {column명})입니다.
예를 들어 성별의 카디널리티는 남과 여로 2개이지만, 주민번호의 카디널리티는 사람마다 고유하기 때문에 굉장히 높은 카디널리티를 갖고 있습니다.
만약 카디널리티가 낮은 성별을 인덱스로 한다면 인덱스로 필터링 되는 데이터들이 굉장히 적어 검색에서 좋은 성능을 낼 수 없습니다. 즉 인덱스는 최대한 많이 걸러지도록 카디널리티가 높은 것을 선택해야 합니다.

Clustered Index 컬럼 테이블과 무관한 일련 번호를 선택하는 것이 좋다.

회사 입사자의 데이터를 관리해야 하는 상황에서 주민번호를 Clustered Index로 설정했다고 가정 해 보겠습니다.
입사 순서대로 데이터가 추가될 것이고 추가 될 때마다 디스크에 있는 데이터 페이지가 재정렬 될 것입니다.
데이터 페이지 재정렬은 디스크 I/O를 여러번 해야 하기 때문에 더 비용이 큽니다.
이 예시에서는 그렇게 자주 추가되지 않을 것이기 때문에 크게 문제가 없을 수 있지만, OLTP 시스템에서는 데이터 수정이 빈번하여 성능 저하의 원인이 될 수 있습니다.
가장 중요한 것은 상황에 따라 이런 것을 고려해서 선택하는 것입니다.

마무리

결과만 나오면 되는 쿼리만 짜오다가 실무를 경험하면서 인덱스 설정을 어떻게 하느냐가 성능에 중요한 영향을 미친다는 사실을 알게 되었습니다.
알게 된 이상 공부의 필요성을 느꼈고 잘못된 지식을 전달하고 싶지 않아서 여러 자료를 참고하여 공부했습니다. 이번 공부가 SQL 성능 이슈가 발생했을 때 돌파구를 만들어 줄 무기가 되리라 생각합니다. 끝까지 읽어주셔서 감사합니다.

티스토리