인덱스 이해하기
인덱스란 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조이다.
위 정의를 이해하기 위해 예시를 보자.
500 페이지 분량의 책이 있다. 여기서 특정 원하는 내용을 찾기 위해서는 어떻게 할까?
일반적으로는 직접 페이지를 넘겨 확인할 것이다. 책의 내용을 기억하고 않다면 최대 500페이지까지 확인해야 원하는 내용을 확인할 수 있다.
책에서는 이러한 불편함을 방지하기 위해 목차를 통해 독자가 찾는 내용을 쉽게 확인할 수 있다. DB의 인덱스는 책의 목차와 같은 역할을 한다. 즉, 인덱스는 특정 정보를 빠르게 찾기 위해서 사용된다.
DB에서 인덱스는 왜 필요할까?
테이블을 편의상 A라 가정해보자.
A 에서 이메일을 통해 행 정보를 찾게 되면 어떻게 될까?
select * from where email = 'exam@test.com'사진과 같이 10만개 행을 순차적으로 확인할 것이고 재수 없다면 10만 행까지 갈 수 있다.
데이터가 점차 많아질수록 이는 더욱 오래 걸릴 수 있다.
위 도서 예시와 같이 이러한 경우에 인덱스를 통해 빠르게 정보를 찾을 수 있다.
왼쪽 그림과 같이 이메일 컬럼에 대해 인덱스를 적용하면 특정 기준을 통해 정렬되어(이진 탐색과 유사) 빠르게 정보를 찾을 수 있다. 일반적으로 B-Tree (Balance-Tree) 알고리즘이 적용되며 이진 탐색 을 기반으로 구현된다.
(위 인덱스에 대한 그림은 예시이기에 실제와는 다를 수 있다. 이러한 형식이라는 정도로 이해하면 좋다.)
클러스터링 / 논-클러스터링 인덱스
클러스터링 인덱스 : 실제 데이터 페이지를 가르키며 실제 데이터와 같은 무리의 인덱스
논-클러스터링 인덱스 : 실제 데이터의 주소 페이지를 가르키며 다른 무리의 별도의 인덱스
일반적으로 클러스터링 인덱스는 항상 기본으로 사용된다. 우리는 @Id 를 통해 PK를 지정한다. DB에서는 자체적으로 PK를 기준으로 데이터를 인덱스를 통해 항상 정렬해서 관리한다.
여기서 흥미로운 점은 왜 PK인 id를 Auto Increment를 사용했는지 알 수 있다. 위에서 언급했듯 인덱스는 조회 성능은 빠르지만 추가/변경/삭제 기능은 느리다. 이러한 문제를 해결하기 위해 Auto Increment를 사용한 것이다.
추가, 삽입되는 데이터가 중간에 끼어들면 어떻게 될까? 데이터의 정렬을 위해 기존 페이지가 쪼개지고 변경되는 요소가 많아져 성능적인 문제가 발생할 수 있다.
이처럼 Auto Increment를 통해 순차적으로 데이터를 추가하고 정렬 방지, 페이지 쪼갬/분할 등을 최소화하여 최적화한 것이다.
위 클러스터링 인덱스와 실제 데이터 구조처럼 클러스터링 인덱스를 통해 빠르게 찾을 수 있다. (위 그림은 실제 구조와 정확하지 일치하지 않을 수 있다.)
클러스터링 인덱스의 특징은 다음과 같다.
- 실제 데이터 자체가 정렬된다.
- 테이블당 1개만 존재한다.
- 리프 페이지가 실제 데이터 페이지이다.
- 아래의 제약조건 시, 자동으로 생성된다.
- primary key (우선 순위)
- unique + not null
이번에는 논-클러스터링 인덱스 구조를 보자. 위 인덱스는 email 컬럼에 대해 적용했다.
편의상 리프 페이지의 이메일 요소를 ‘i@j.k’ 형식에서 i 부분만 표시했다.
논-클러스터링 인덱스의 리프 페이지는 실제 데이터 페이지 주소를 가진다. 이때, PK 정보도 포함한다.
email이 ‘b@xx.xx’인 값을 찾으면 인덱스를 통해 빠르게 찾을 수 있다. 위 사진에서는 리프 페이지에서 해당 값을 찾자마자 바로 실제 데이터의 접근하는 것으로 보이지만 클러스터링 인덱스(PK)를 기준으로 다시 수행된다.
논-클러스터링 인덱스의 특징은 다음과 같다.
- 실제 데이터는 정렬되지 않는다.
- 별도의 인덱스 페이지가 생성된다.
- 데이블당 여러 개 존재할 수 있다.
- 리프 페이지에는 실제 데이터 페이지 주소를 담는다.
- unique 제약 조건 적용 시, 자동 생성된다.
- 직접 index 생성하면 논-클러스터링 인덱스가 생성된다.
그럼 모든 테이블 컬럼에 인덱스를 걸면 효과적이지 않을까?
위 요소에 대해서 그렇지 않다.
- 인덱스도 하나의 데이터베이스 객체로 공간을 차지한다.
- 인덱스는 항상 정렬된다. 따라서 쓰기 작업 성능이 느려진다.
아쉽게도 테이블의 크기가 커진다면 인덱스 정보도 많아진다. 모든 요소에 인덱스를 적용한다면 DB 용량은 매우 커질 수 있다. 또한 인덱스가 적용된 테이블의 정보가 많아진다면 인덱스 정보도 같이 많아진다.
인덱스는 정렬을 통해 빠르게 데이터를 검색한다. 다만 데이터의 추가, 삭제, 변경 등 쓰기 작업이 일어나는 순간 인덱스에 대한 정보도 수정하고 정렬을 해야 한다. 이때, 페이지에 새로운 데이터를 추가할 여유공간이 없거나 트리의 높이 다르다면 페이지에 변화가 발생하여 이를 해결한다. 이를 페이지 분할이라 한다. 이러한 수행으로 DB가 느려지고 성능에 영향을 준다.
즉, 추가/변경/삭제에 대한 성능을 내어주고 읽기(조회)에 대한 성능을 높인 것이다.
이러한 Trade-Off를 고려하여 인덱스를 사용해야 한다. 그렇다면 어떻게 사용하는 것이 효율적일까?
인덱스는 어떻게 사용하는 게 효율적일까?
인덱스 또한 페이지 단위로 구성된다.
페이지 단위는 16KB 이다. 인덱스 키 크기가 커지면 커질수록 페이지에 담긴 정보의 수는 적어질 것이다.
예를 들어, 이메일을 인덱스로 설정했을 때 인덱스 키 크기가 48 Byte 이다. 자식 노드(Branch, Leaf), 추가 정보 등 담긴 크기가 12Byte 라고 하면 16 * 1024 / (48 + 12) = 약 273 으로 하나의 페이지에는 273개 저장된다.
반면에 닉네임을 인덱로 설정할 때 인덱스 키 크기가 12 Byte라면 어떨까?
16 * 1024 / (12 + 12) = 약 683 으로 하나의 페이지에는 683개 저장된다.
조회 결과로 500개 행을 읽는다면 닉네임(12Byte)일때는 1개 페이지에서 전부 조회가 되지만 이메일(48Byte)에 2개 페이지를 읽어야 하므로 더 낮은 성능을 보인다.
즉, 인덱스 키 크기가 클수록 성능 저하가 발생할 수 있다.
카디널리티가 높은 칼럼을 사용하자.
카디널리티 : 특정 데이터 집합의 중복되지 않는 값의 개수
성별과 주민등록번호를 보자. 성별은 남자/여자로 2가지로 나뉘고 주민등록번호는 고유값이므로 중복되지 않는 수는 매우 많다. 성별은 카디널리티가 낮고 주민등록번호는 카디널리티가 매우 높다.
인덱스로는 카디널리티가 높은 값을 사용해야 효율적이다. 인덱스는 Full Scan 대신 필요한 정보를 빠르게 찾기 위한 역할이다. 성별을 인덱스로 사용하며 남자/여자 중 하나를 선택하기에 반만 걸러낸다. 예시로 여성 500만 데이터, 500만 남성 데이터가 있다면 무조건 500만 데이터를 전부 확인해야 한다.
반면 주민등록번호는 고유값이기에 인덱스를 통해 대부분 데이터를 걸러낼 수 있어 빠르게 조회할 수 있다.
여러 칼럼으로 인덱스를 사용한다면 어떻게 해야할까?
- 자주 사용되는 컬럼부터 순차적으로 구성한다.
- 카디널리티가 높은 순으로 구성한다.
Index(이메일), index(성별) 경우를 생각하자. (이메일은 고유값이지만 중복 허용이라는 가정)
예로 100만 데이터에서 where 절로 찾는 이메일이 10건만 존재한다면 10건 내부에서 찾으면 된다.
반면 성별은 남/여 밖에 없기에 사실 상 100만 데이터 중 절반을 확인해야 한다.
이처럼 카티널리티가 높은 순으로 구성해야 효율적으로 인덱스를 사용할 수 있다.
여러 컬럼으로 인덱스(복합 인덱스)시 조건 누락
예로 index(a, b, c)가 있는 경우에 where c = ‘안녕’ 조건절로 조회하면 인덱스는 사용되지 않는다.
index(email, gender) 로 인덱스를 생성한 것을 알 수 있다.
위 쿼리를 각각 수행하면 where email 만 인덱스가 걸렸다. 이처럼 첫번째 인덱스 컬럼에 해당하지 않는다면 인덱스는 수행되지 않는다.
처음에 언급한 예시처럼 index(a, b, c)인 경우에 where a=1 and c=3 경우에는 index(a, b, c) 가 적용된다.
인덱스 조회시 주의사항
- between, like, <, > 등 범위 조건은 해당 컬럼은 인덱스를 타지만, 그 뒤 인덱스 컬럼들은 인덱스가 사용되지 않습니다
- 반대로 =, in 은 다음 컬럼도 인덱스를 사용합니다.
- AND연산자는 각 조건들이 읽어와야할 ROW수를 줄이는 역할을 하지만, or 연산자는 비교해야할 ROW가 더 늘어나기 때문에 풀 테이블 스캔이 발생할 확률이 높습니다.
- 인덱스로 사용된 컬럼값 그대로 사용해야만 인덱스가 사용됩니다.
- null 값의 경우 is null 조건으로 인덱스 레인지 스캔 가능
추가로 인덱스 적용 전, 인지해야 할 요소
- where, join, group by 등 조건 절이 필요한 경우와 자주 사용되는지
- insert / update / delete 가 자주 발생하지 않는 컬럼인지(일반적인 경우, 쓰기/읽기 비율은 2:8, 1:9 이다. 느린 쓰기를 감수하고 빠른 읽기를 선택하는 것도 하나의 방법이다.)
- 규모가 작지 않은 테이블인지