'DB 인덱스'에 대해 알아보자

 

DB 쿼리 속도 최적화를 위한 '인덱스'에 대해서 많이 들어보았을 것이다.
오늘은 이 인덱스란 녀석에 대해서 알아보자.

 

Index, 왜 중요할까?

100만 명이 등록되어 있는 user 테이블이 있다.

이때 이름을 기반으로 유저를 조회하려고 한다.

select * from user where user_name = "hyunjun"

 

hyunjun이라는 이름을 가진 계정을 찾기 위해서는 100만 개의 row를 모두 scan 해야 한다. (full scan, O(N))

만약 user_name에 index를 걸어두었다면? B-tree index 기반으로 O(logN)의 시간 복잡도로도 충분히 찾을 수 있다.

정리하자면 index를 사용하는 이유는 특정 조건을 만족하는 데이터들을 빠르게 찾기 위함이다!
상황에 따라서는 빠르게 정렬(order by)하거나 그룹핑(group by)하기 위해서도 사용한다.

 

 

Index, 어떻게 걸면 될까?

이번에는 아래와 같은 컬럼들로 구성되어 있는 player 테이블이 있다.

id, name, team_id, back_number

이때 백넘버는 서로 다른 팀에 있을 경우 겹칠 수 있는 값이다.

select * from player where name = "Sonny";
select * from player where team_id = "1" and back_number=7;

create index player_name_idx on player (name);
create unique index team_id_back_number_idx on player (team_id, back_number);

 

name에 대한 index와 달리 team_id와 back_number 컬럼에 대한 index의 경우 unique index를 걸어주었다. 이유가 무엇일지 잠시 생각해 보자.

name의 경우 동명이인이 존재할 수 있는 반면에 team_id와 back_number를 사용하면 유니크한 결과를 알아낼 수 있다.

추가로 team_id, back_number를 이용한 인덱스처럼 2개 이상의 컬럼을 이용한 인덱스를 multicolumn index, composite index 등으로 부른다.

 

 

아래와 같은 쿼리로 인덱스 확인이 가능하다.

show index from player;

 

index, 동작 방식

 

B-tree 기반의 index 동작 방식에 대해 알아보자.

특정 컬럼에 인덱스를 걸면 해당 컬럼을 기준으로 정렬한 데이터를 가지고 있는다.

name 컬럼에 인덱스를 걸었다면 name 컬럼과 원본 테이블의 row를 가리키는 참조 컬럼으로 구성된 데이터를 name 기준으로 정렬하여 저장하고 있다고 생각하면 된다.

where 문을 통한 조회 시에는 정렬된 데이터를 활용하여 binary search를 통해 더 빠르게 데이터를 조회할 수 있다.

 

create unique index team_id_back_number_idx on player (team_id, back_number);
create unique index team_id_back_number_idx on player (back_number, team_id);

앞서 player를 조회하기 위해 team_id와 back_number로 구성된 multicolumn index를 알아보았다.

이때 순서를 달리하여 back_number가 먼저 오게 한다면 어떻게 될까?

 

인덱스와 관련된 데이터는 해당 인덱스를 구성하는 컬럼들의 순서에 맞춰서 정렬을 진행한다. 따라서 중복 데이터가 존재하지 않는 team_id를 우선적으로 하는 multicolum 인덱스를 구성하는 것이 올바른 구성이다.

 

create unique index team_id_back_number_idx on player (team_id, back_number);
select * from player where back_number=7;

만약 위와 같이 인덱스를 걸어두고 쿼리를 실행했을 때 어떻게 동작할까?

[정답]

team_id를 우선적으로 하는 multicolumn 인덱스 테이블은 back_number만을 기준으로 한다면 정렬된 데이터를 보장하지 않는다. (team_id를 기준으로 먼저 정렬, 그다음으로 back_number를 기준으로 정렬한 데이터로 구성)

따라서 위와 같이 where 조건문으로 back_number만을 전달한다면 인덱스를 통한 성능 향상은 기대할 수 없게 된다.

위와 같이 back_number만을 조건으로 조회할 때 성능 향상이 되기를 원한다면 back_number 하나로 구성된 인덱스를 추가해 주어야 한다.

사용되는 쿼리에 맞춰 적절한 index를 걸어주자!

 

index 많이 만들어도 괜찮을까?

index를 위한 실제 테이블과 별도로 index를 위한 데이터를 만든다는 것을 고려해야 한다.

• table에 write를 할 때마다 index 데이터에도 변경이 발생한다. 데이터가 많아질수록 write 과정에서 오버헤드가 많이 발생하게 된다.
• 또한 index를 위한 추가적인 저장 공간을 차지하게 된다.따라서 불필요한 index는 만들지 않도록 하자.

 

실제 index가 원하는 것처럼 동작하는지는 어떻게 확인하면 좋을까?

실행하려는 쿼리 앞에 explian을 추가해 줌으로써 실행 계획 확인이 가능하다.

 

Covering index란?

조회하기를 원하는 컬럼들이 모두 index 안에 포함이 되어 있다면 (cover가 된다면) index 데이터로부터 원본 테이블로 갈 필요 없이 원하는 데이터들을 가지고 있을 것이다.

이러한 index를 Covering index라고 부르며 Covering index는 일반적인 index보다 조회 성능이 더욱 빠르다.

 

index 적용 시 참고 사항

카디널리티가 낮은 경우(데이터의 중복도가 높은 경우)에는 index를 사용하지 않고 full scan의 성능이 나을 때가 있다.

인덱스를 쓰는 것이 나은지 full scan이 나은지에 대해서는 DB 옵티마이저가 판단한다.

이미 데이터가 굉장히 많은 테이블에 대해 인덱스를 생성하는 경우에는 오랜 시간이 소요될 수 있으며 인덱스를 생성하는 동안 DB 성능에 악영향을 줄 수 있다. 만약 해당 작업을 진행한다면 고객 사용이 적은 시간을 선정하여 진행하는 것이 좋을 것이다.