RDB Index

참고 자료

[MYSQL] 📚 인덱스(index) 핵심 설계 & 사용 문법 💯 총정리

[MYSQL] 📚 인덱스(index) 핵심 설계 & 사용 문법 💯 총정리

https://www.youtube.com/watch?v=IMDH4iAQ6zM

 

Index 유무에 따른 성능 차이

• where first_name = ‘Minsoo’; 쿼리를 날렸을 때 성능을 비교해보자.
• first_name에 Index가 걸려 있지 않다면, 테이블의 로우를 순차 탐색하므로 O(N)
• first_name에 B tree 기반 Index가 걸려 있다면, O(logN)
• 튜플 검색의 기초가 되는 PK는 기본적으로 Index가 걸려있다.

 

Index를 사용하는 이유

• 조건을 만족하는 튜플(들)을 빠르게 조회하기 위해
• 빠르게 정렬(order by)하거나 그룹핑(group by)하기 위해

 

Index 사용 예시1

• select * from player where name = ‘Sonny’;
• 위의 쿼리를 수행할 때, name에 Index를 걸면 더 빠르게 동작한다.
• CREATE INDEX player_name_idx ON player(name);

 

Index 사용 예시2

• select * from player where team_id = 105 and backnumber = 7;
• player 테이블에서 team_id와 backnumber는 둘의 조합으로 player별 고유한 값을 가진다. 둘을 같은 Index에 걸면 더 빠르게 동작한다.
• CREATE UNIQUE INDEX team_id_backnumber_idx ON player(team_id, backnumber);

 

B-tree 기반의 index 동작 방식

 

• a 컬럼에 건 Index의 구성 : 정렬된 a 컬럼과 a에 연결된 튜플의 포인터
• where a = 9;을 실행할 때 Index(a)에서 이진 탐색으로 9를 빠르게 찾고, 9의 ptr가 가리키는 튜플을 찾아서 조회함

 

• Index(a)만 있을 때, a와 b를 함께 조건 검색하는 경우 성능이 안 나온다. full scan과 별 차이가 없다.
• Index에서 조건에 맞는 a를 찾으면 포인터로 튜플의 b를 추가 탐색한다. 만약 조건에 맞는 a로 찾은 포인터가 수천건이 넘는다면, 이 데이터에 대해 매번 조회해서 b를 비교해야한다.

 

• 위의 상황에서 성능을 내려면 Multiple Index를 걸어야 한다. 먼저 선언한 컬럼 위주로 정렬된다.
• 이진 탐색으로 a 조건에 맞는 Index 튜플을 찾으면 b 조건을 추가로 비교해서 맞으면 포인터로 연결된 튜플을 선택한다.
• 튜플의 앞뒤로 비교하여 a 조건을 비교하고, a 조건이 일치하는 범위에서 b 조건을 비교한다. 이때 b 조건도 이진 탐색한다.

• Multiple Index를 건 상태에서 주요 키가 아닌 키로 검색을 시도하면 성능이 안 나온다.
• Index에서 선언한 키 순서대로 정렬되므로, b는 정렬된 상태가 아니다. 그냥 풀 스캔한다. 이럴 때는 b 전용 인덱스를 걸어야 한다.
• where a = 7 or b = 95도 마찬가지로 성능이 안나온다.

 

INDEX 선택

• 어떤 select 절을 실행할 때 어떤 Index를 탈지 궁금하다면 select 절 앞에 EXPLAIN 키워드를 사용한다.
• select 절을 실행할 때 Index를 선택하는 것은 보통 Optimizer이다. 간혹 Index를 걸었는데도 성능이 안 나오는 문제가 있다면 Optimizer가 잘못된 인덱스를 선택했을 수도 있다.
• 수동으로 인덱스를 지정할 수 있는데, select 절 실행 시 where 절 앞에 USE INDEX(인덱스)나 FORCE INDEX(인덱스)를 사용한다.

 

INDEX를 막 만들어도 될까?

• INDEX를 만들면 실제 테이블에 있는 컬럼 데이터가 복사되어 중복으로 존재한다. 저장 공간이 더 필요하다.
• 만약 원본 테이블에 write하면 Index에도 변경이 발생한다. 이진 탐색 트리로 정렬하는 연산도 필요하다. 오버헤드 발생.
• 불필요한 인덱스는 만들지 않는 것이 중요하다!

 

Covering Index

• Index(team_id, backnumber)가 존재하는데, team_id와 backnumber를 조회하는 select 실행
• 조회하는 속성을 index가 모두 커버하고 있으므로 포인터로 조회할 필요가 없다. 조회 성능이 더 빠르다.

 

Hash Index

• 해시 함수 기반의 인덱스 구현 방식이다.
• 조회의 시간 복잡도가 O(1)이다.
• 단점 : rehash에 대한 부담, 동등 비교만 가능하고 범위 비교가 불가능하다. multicolumn index의 경우 컬럼 일부만 사용할 수 없다.

 

Full Scan이 더 좋은 경우

• 테이블에 데이터가 조금 있는 경우 (몇 십, 몇 백 건) → 데이터가 많이 쌓여서 조회 성능에 이슈가 나면 인덱스 지정한다.
• 조회하려는 데이터가 테이블의 상당 부분을 차지할 때. 즉, 타입 정보 같이 카디널리티가 낮은 컬럼으로 검색할 때.

 

Index 설계 TIP

• WHERE, JOIN 절에 자주 사용되는 열에는 인덱스의 효율이 좋다.
• ORDER BY 절에 사용되는 열은 데이터 페이지가 자동 정렬되는 클러스터형 인덱스가 유리하다.
• 데이터 중복도가 높은 컬럼은 인덱스 효과가 없다.
• 데이터 Insert가 조회보다 잦은 테이블은 성능 저하가 발생할 수 있다.
• 대용량 데이터가 자주 입력되는 경우 클러스터 대신 보조 인덱스를 사용하는 것이 낫다.