[MySQL] Index - 인덱스 사용법

 

MySQL 인덱스를 공부하고 알기 쉽게 정리하기 위해 글을 작성하게 되었습니다.

 

1. 인덱스를 왜 사용할까?

 

다음 두 개의 순서가 적힌 표가 있습니다.

1.

3

1

2

6

4

 

2.

1

2

3

4

6

 

 

1번 표는 3, 1, 2, 6, 4

2번 표는 1, 2, 3, 4, 6

 

1번과 2번 중에 5가 없다는 것을 알기 쉬운 것은 정렬되어 있는 2번 표라고 생각합니다.

 

인덱스는 정렬된 자료구조로써 이를 통해 탐색범위를 최소화하게 됩니다.

MySQL에서 인덱스 또한 테이블이며 인덱스를 생성시 정렬된 데이터를 가진 테이블을 확인할 수 있습니다.

 

데이터 주소	이름	종류	가격
1	햄버거	음식	100
2	피자	음식	200
3	콜라	음료	50
4	치킨	음식	300

 

위와 같은 데이터가 있다고 가정합니다.

 

가격이 가장 싼 데이터를 찾고 싶을때 데이터베이스는 어떻게 동작할까요?

 

1. 인덱스가 없을때

 

1번 데이터를 확인합니다. -> 가격 100원을 기록

2번 데이터를 확인... 4번 데이터를 확인.

이렇게 전체 데이터를 다 확인합니다.

왜냐하면 어떤 위치의 데이터가 가장 작은 데이터인지 모르기 때문입니다. 

 

2. 인덱스가 있을 때

인덱스는 정렬된 자료구조이기 때문에 가격으로 인덱스를 생성하게 되면 새로운 데이터가 생성됩니다.

 

가격	데이터주소
50	3
100	1
200	2
300	4

 

가격이 키 값으로 정렬된 데이터주소를 가지고 있는 테이블에서 검색을 하게 됩니다.

가격으로 정렬이 되어 있기 때문에  첫 번째가 가장 낮은 가격임이 보장됩니다. 그래서 데이터 주소 3번으로 바로 조회할 수 있게 됩니다.

 

만약 위의 표에서 인덱스를 종류로 잡게 된다면 음식이 3개 음료가 1개이므로 음식인 데이터를 조회하게 되면 4개에서 3개로 탐색범위가 줄어들긴 헀지만 크게 이점을 보지 못하게 됩니다.

 

인덱스의 핵심은 탐색(검색)의 범위를 최소화하는 것으로 이해하면 될 것 같습니다. 

 

 

2. 인덱스의 구조

 

1. MySQL의 InnoDB는  B + Tree 자료구조를 사용합니다.

그 이유는 다음과 같습니다.

• 삽입 / 삭제 시 항상 균형을 이룸 - 트리가 한쪽 노드로만 쌓이게 되면 결국 리스트와 탐색 속도가 같아짐
• 하나의 노드가 여러 개의 자식 노드를 가질 수 있음
• 리프 노동만 데이터 존재 - 연속 데이터 접근 시 유리

 

2. 클러스터 인덱스

• 클러스터 인덱스는 데이터 위치를 결정하는 키 값이다.
• MySQL의 PK는 클러스터 인덱스이다.
• MySQL에서 PK를 제외한 모든 인덱스는 PK를 가지고 있다.

위와 같은 이유 때문에 PK로 Auto Increment를 사용하는 이유는

인덱스는 정렬된 자료구조이기 때문에 예를 들어 PK 가 1,2,4가 저장되어 있는 테이블에 3을 삽입시 2와 4 사이에 저장되게 됩니다. 그렇기 때문에 순차적으로 증가하는 Auto Increment를 사용하여 데이터가 밀리는 현상을 방지하게 됩니다.

 

 

 

3. 인덱스 생성 및 실행

 

더미 데이터로 백만 건 정도 MySQL 데이터베이스에 저장해 놓았습니다.

 

3.1 인덱스 사용하기 전

 

 

실행하면 1초가 넘게 걸리는 것을 확인할 수 있습니다.

 

explain 명령어로 실행 계획을 보면

 

 

type ALL, rows 99만 건이 넘으므로 테이블의 모든 데이터를 읽은 것을 확인할 수 있습니다.

 

 

3.2 인덱스 사용

 

create index POST__index_member_id
    on POST (memberId);

create index POST__index_created_date
    on POST (createdDate);

create index POST__index_member_id_created_date
    on POST (memberId, createdDate);

 

3개의 인덱스를 만들어 줍니다.

 

3.2.1 memberId 인덱스를 사용했을 때

 

오히려 인덱스를 사용하기 전 보다 느려진 것을 확인할 수 있습니다.

 

실행 계획 또한 인덱스를 사용한 것으로 나옵니다.

 

왜 이런 결과가 나올까요?

현재 백만 건의 데이터는 memberId가 1인 게시글만 백만 건입니다.

그러므로 memberId = 1의 조건문으로 통한 인덱스 조회는 인덱스의 핵심인 탐색의 범위를 최소화하지 못하고 인덱스 테이블 + 원본 테이블까지 두배로 읽게 되는 경우가 발생합니다.

 

 

 

3.2.2  createdDate 인덱스를 사용했을 때

 

 

143ms로 훨씬 빨라졌습니다.

 

 

이유는 createdDate는 식별할 수 있는 값이 1번과 달리 백만 건이 아닌 19024로 훨씬 적어졌기 때문입니다.

인덱스의 핵심은 탐색의 범위를 최소화하는 것이기 때문에 빠른 조회가 가능하게 되었습니다.

 

 

3.2.3  memberId, createdDate 인덱스를 사용했을 때

두 개의 값을 가지고 인덱스를 만들 수 있는데 이것을 복합 인덱스라고 합니다.

복합 인덱스는 첫 번째 값이 정렬되고 만약 첫번째 값이 같을 때 두 번째 값으로 정렬이 됩니다.

 

 

조회를 하면 거의 2배 이상 차이는 것을 확인할 수 있습니다. 

memberId 인덱스를 통해 탐색범위를 줄이고 탐색범위를 줄인 데이터중에서 createdDate를 통해 다시 한번 탐색범위를 줄이기 때문에 훨씬 빠른 데이터 조회를 할 수 있게 됩니다.

또한 group by 조차도 인덱스와 같은 값을 사용하기 때문에 group by도 인덱스를 타게 되어서 더 빠른 조회가 됩니다.

 

위의 3가지 경우를 봤을 때 인덱스를 만들어 사용한다고 해도 데이터 분포도에 따른 1초 -> 10초도 걸릴 수 있고

10초 -> 1초로 줄일 수도 있다는 것을 알게 되었습니다.

 

4. 인덱스 사용 시 생각할 점

인덱스 사용 시에는 데이터 분포도 생각해야 된다.

그러므로 카디널리티 높은 (중복도가 낮은 데이터, distinct 사용시 출력 값이 높은) 값을 선정하는 것이 좋다.

또한 인덱스는 기본적으로 하나의 쿼리에서 하나의 인덱스만 사용할 수 있게 되어 있으므로 group by, order by, where

을 함께 사용 시 주의해서 사용해야 한다.

 

인덱스를 사용할 때는 꼭 explain으로 실행 계획을 확인하는 것을 추천합니다!.