[Mysql] 효율적인 Index 사용 예제

효율적인 Index 사용법

Real My SQL을 읽다가 Index를 효율적으로 사용하는 방법을 정리하면 도움이 될거 같아 글을 남긴다.

---

[MYSQL 환경]

Server version: 8.0.29 Homebrew

Storage engine = InnoDB;

transaction isolation = Repeatable read

---

들어가기에 앞서 이 글에 사용될 테이블이다.

인덱싱은 각 테스트마다 만들어 줄것이다.

1. Index full scan

Mysql은 쿼리에 첫번째 인덱싱된 열이 명시되어있지 않으며, 인덱싱된 열로 쿼리를 처리할 수 있을때, index full scan을 사용한다.

예를들어, 위 테이블에 name, age순으로 인덱싱이 걸려있고 아래와 같은 쿼리를 날린다고 가정을 해보자.

select name, age from indexTest where age > 20;

age > 20 이상인 사람은 2명이다. 이때, 몇번의 I/O가 발생할까? age가 20이상인 사람은 2명이며, age 칼럼또한 인덱싱 되어있으므로, 2번의 I/O가 발생하는것이 타당해보인다. 과연 그럴까? 실제로 쿼리를 날려보자.

위 사진에서 (row column)5개의 row에 대해 I/O가 발생했고, 인덱스 사용으로 33.33퍼센트가 필터링 되었음을 알 수 있다. 또한, (type column)index 타입으로 쿼리가 날라 갔다. (type이 index인 경우 index full scan을 의미한다.)

 

예상과는 다르게 인덱싱된 모든 row에 대해 I/O후 우리가 원하는 조건 (age > 20)을 만족하는 row만 필터링해서 가져오는 모습이 보인다.

이런 현상이 일어나는 이유는 2개이상의 칼럼이 함께 인덱싱된 경우에 인덱싱되는 형태와 관련이 있다.

InnoDB의 경우 인덱스가 '왼쪽값을 기준으로 정렬된 형태'로 생성이 된다. 예를들어, 위 테이블에서 name,age의 경우 name이 우선적으로 오름차순 정렬 인덱싱되고, name이 같은 값 이라면 age가 오름차순 정렬 인덱싱 된다. 즉, name이 없는 age칼럼의 인덱싱 정보는 '작업 범위'를 결정하는데 어떠한 도움도 주지 못한다. (반면 name이 있다면 age또한 작업 범위를 결정하는데 도움을 준다.)

위 원리가 굉장히 중요하다. 이러한 구조를 알지 못하고 인덱싱을 사용하면 불필요한 I/O가 계속해서 발생하게 된다.

 

2. Index range scan

이번에는 1번의 Index full scan을 이상적인 인덱스 사용방법인 index range scan으로 변경해보자.

테스트를 위해 우선, 기존에 걸었던 (name, age)인덱스를 삭제하고 (age, name)순서로 인덱스를 새롭게 걸어줬다.

alter table indexTest drop index ixNameAge;
alter table indexTest add index ixAgeName(age, name);

이제, 1번과 똑같이 쿼리를 날려보자.

select name, age from indexTest where age > 20;

1번의 경험을 미루어 봤을때, 이번에는 I/O가 정확히 2번 발생할것으로 예상된다.

row칼럼을 보면 2번의 I/O가 발생했고, 인덱스 사용으로 100% 필터링되었으며 type이 range로 바뀌었음을 알 수 있다.

즉, index range scan방법으로 age > 20 이상인 2개의 데이터를 I/O했다.

마찬가지로 인덱스가 age -> name 순서로 정렬되어 있기때문에, age값이 '작업 범위'를 결정하는데 도움이 되어 2개의 row만 읽을수 있는것이다.

 

3. >, = 비교 효율성

2번과 마찬가지로 (age, name)순으로 인덱스가 만들어져 있을때, 다음 쿼리를 날려보자.

select * from indexTest where age >= 0 and name like '첫째'

몇번의 I/O가 발생할까?

4번의 I/O가 발생했고 (5번을 예상했는데 왜 4번인지는 모르겠다.) 인덱스 사용으로 필터링된 행이 20퍼센트밖에 되지 않는다.

분명 age, name순서로 인덱싱 되어있으며, where 절에 인덱싱된 첫번째 열인 age를 태워서 보냈다. 하지만, I/O는 1번이 아닌 4번이 일어났다. 

이번에는 인덱싱을 (age -> name)에서 (name -> age)로 변경 후에 쿼리를 날려보자.

alter table indexTest drop index ixAgeName;
alter table indexTest add index ixNameAge(name, age);

쿼리는 동일하다.

select * from indexTest where age >= 0 and name like '첫째'

이번에는 위 와 다르게 1번의 I/O가 발생했음을 알 수 있다.

 

이는 >= 와 like (혹은 =)의 특징에 있는데, >= 를 기준으로 인덱스 테이블을 탐색 할 경우, age >= 0을 만족하는 모든 인덱싱된 행에 대해서  name like '첫째'를 만족하는지 비교를 한다.

하지만, like 를 기준으로 인덱스 테이블을 탐색 할 경우, like를 만족하는 행에 대해서 age >= 0보다 큰지 비교를한다.

따라서, 비교구문또한 상황에 맞게 선택하는것 효율적인 쿼리를 만드는것에 중요하다.