Database에서 자주 사용되는 용어인 Index는 DataBase에 데이터가 많으면 많을수록 검색 성능을 향상하는데 도움을 주는 기술이다.

Index의 목적은 RDMS의 검색 속도를 높이는 데 있다. Select 쿼리의 Where 절이나 Join 예약어를 사용했을 때 인덱스를 사용하여 쿼리의 검색 속도를 빠르게 한다. 즉, Insert, Update, Delete 성능은 다소 떨어진다.

흔히 책을 비유해서 Index를 설명하는데, 책에 있는 목차, 색인과 같은 역할을 한다. 적은 양의 데이터를 검색할 때는 양이 적기 때문에 성능 향상이 미비하다. 10쪽도 안 되는 책에서 목차와 색인이 있어봐야 공간만 차지한다. 하지만 1000쪽이 넘는 책에서는 목차와 색인이 없다면 원하는 내용을 찾기 매우 힘들다.

이와 마찬가지로 큰 데이터를 다룰 때 Index는 사용자가 검색을 요청할 때 빠르게 찾아 줄 수 있도록 기능을 제공한다. 아래는 Index 예시 테이블이다.

Index 설명 이미지
출처:http://knowtechstuffz.blogspot.com/2015/04/how-sql-indexes-work-internally.html

예시 테이블을 보면 특징이 보인다.

첫 번째는 Index 용 공간이 필요하다. 데이터를 찾기 위해 Index를 설정하려면 Index를 저장할 수 있는 공간이 필요하다. 그래서 과도한 Index 생성은 데이터베이스의 공간을 차지하기 때문에 Index 생성 시 필요한 곳에만 생성을 해야 한다.

두 번째는 Index는 데이터의 장소를 저장한다는 점이다. 데이터의 장소를 저장하기 때문에 Index를 활용하여 검색을 하면 훨씬 빠른 검색 능력을 보여줄 수 있다.

다른 특징으로는 필드 데이터 형식 중 인덱스가 될 수 없는 필드도 있다.

Index는 어떻게 동작할까?

Index가 동작하기 전 특정 컬럼에 Index를 설정했다고 가정한다. 설정한 컬럼 명은 A라고 한다. Index를 설정하면 A컬럼에 대한 Index Table이 생성된다. 컬럼 A가 포함된 Where 절 쿼리를 요청하면 Index Table에서 값을 찾게 된다. 이 상황에서 동작 순서는 다음과 같다.

Index Table에서 Where절에 포함된 값을 찾는다.
찾은 값의 Table Id(PK)를 가져온다.
Table Id 값으로 원본 테이블에서 값을 조회해서 반환한다.

Index Search에 사용되는 B+Tree 알고리즘

B+Tree 알고리즘은 Index를 관리하는 대표적인 알고리즘이다. 위와 같은 형식으로 관리되며, 이를 통해 검색을 효율적으로 할 수 있게 한다.

B+ Tree 구성

리프 노드 : 실제 데이터가 저장되는 노드. LinkedList로 연결되어 있다.
논리 노드 : 리프 노드까지의 경로 역할을 하는 노드.
루트 노드 : 경로의 출발점 노드.

BTree의 리프 노드들은 LinkedList로 연결하여 순차 검색을 용이하게 하는 등 BTree를 인덱스에 맞게 최적화했다. 물론 무조건 리프 노드까지 가야 한다는 단점도 있다.

Index의 장점

키 값을 통해 테이블에서 검색과 정렬 속도가 향상된다.
질의 및 보고서 생성 시 그룹화 작업의 속도가 향상된다.
인덱스를 통해 테이블 행의 고유성을 강화시킨다.
테이블 기본 키는 자동으로 인덱싱된다.

Index의 단점

Index를 생성하면 공간이 커진다. 즉, .mdb 파일 크기가 늘어난다.
여러 사용자(여러 응용프로그램)에서 한 페이지를 동시에 수정할 때 병행성이 떨어진다.
인덱스 된 필드에서 데이터 업데이트 및 추가, 삭제할 때 성능이 떨어진다.
인덱스 생성 시 시간이 많이 소요될 수 있다.

Index를 생성해야 하는 경우는 언제일까?

Where 절에서 사용되는 컬럼은 Index로 만들자.
데이터의 중복도가 높은 열은 Index 효용이 낮다.
외래 키가 사용 되는 열은 Index를 생성하자.
Index, Update, Delete가 자주 발생하면 생성하지 말자.
사용하지 않는 Index는 삭제한다.

주의사항

Select 쿼리를 실행할 때 데이터 블록 수, DB_FILE_MULTIBLOCK_READ_COUNT 값과 분포도 등에 따라 검색 속도가 다를 수 있다. 전체 테이블 검색보다 늦어지는 경우도 있다.

Index 종류

다양한 종류의 Index가 있으며, 상황과 목적에 맞게 Index를 사용하면 된다.

Cluster Index – 테이블 당 1개만 허용. 해당 컬럼을 기준으로 테이블이 물리적으로 정렬된다. 리프 노드가 필요 없고 추가적인 공간이 필요 없다. Primary Key(기본 키) 설정 시 자동 생성된다.
NonCluster Index – 테이블당 249개까지 생성 가능하다. 새로운 공간에 테이블 페이지를 정렬한다. 그래서 Cluster Index보다 많은 공간을 차지한다.
결합 인덱스 – 두 개 이상의 컬럼을 합쳐 Index를 생성한다. 주로 Where 조건절이 2개 이상의 쿼리로 작성된 경우 사용한다.
함수 기반 인덱스 – 함수 형태로 Index를 생성한다.
Bitmap 인덱스 – 데이터 값의 종류가 적고 동일한 데이터가 많을 경우에 많이 사용한다.
Unique Index – 인덱스 안에 있는 Key 값에 중복되는 데이터가 없다. 그래서 성능이 좋다.

결론적으로 인덱스는 검색에 최적화된 기능이다. 데이터 변경이 자주 일어나는 테이블에서는 사용을 자제해야 한다. 그래서 검색도 많고 데이터 변경도 자주 일어나는 테이블과 로직에서는 인덱스 사용을 신중하게 선택해야 한다. 어떤 기능이 주로 발생하는지에 따라 인덱스를 선택해서 사용하자.

카디널리티(cardinality), 선택도(selectivity), 인덱스(index)

참고

https://mangkyu.tistory.com/96

Database에서 Index란?