MySQL이 인덱스를 이용하는 방법

인덱스를 잘 이용하려면 당연히 인덱스가 어떻게 실제 레코드를 읽어내는 지를 알아야한다.

이번 포스팅에서는 MySQL이 인덱스를 이용하는 대표적인 방법을 살펴보겠습니다.

 

---

인덱스 레인지 스캔

인덱스 레인지 스캔은 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식이다.

 

 

SELECT * FROM employees WHERE first_name BETWEEN 'Ebbe' AND 'Gad';

 

위 쿼리를 실행하면, first_name이 세컨더리 인덱스이기때문에 스캔해야할 위치 검색을 위한 비교작업을 수월하게 수행할 수 있다.

(아래 그림의 두꺼운 선)

현재 아래 그림은 세컨더리 인덱스와 클러스터링 인덱스를 동시에 적용한 상태이다.

세컨더리 인덱스를 이용해 스캔 시작 위치를 결정하고, 그 지점부터 필요한 방향으로 인덱스를 읽어나가고 있다.

 

위 사진은 리프 노드에 저장된 (인덱스 키, PK)를 통해 데이터 파일에서 레코드를 읽어오고 있다.

 

이때, 한 건 한 건단위로 랜덤 I/O가 한 번씩 일어나게 되는 데 이는 비용이 상당히 많이 든다.
따라서, 인덱스를 통해 읽어야 할 데이터 레코드가 20 ~ 25%가 넘는다면 인덱스를 통한 읽기를 사용하는 것보다 테이블의 데이터를 직접 읽는 것이 더 효율적일 수 있다.

 

인덱스 풀 스캔

 

인덱스 레인지 스캔과 마찬가지로 인덱스를 사용하지만, 인덱스 레인지 스캔과는 달리 인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다.

위 방식은 인덱스가 (A, B, C) 칼럼의 순서로 만들어져 있지만 쿼리 조건절은 B 칼럼이나 C 칼럼으로 검색되는 경우이다.

 

일반적으로, 인덱스의 크기는 테이블의 크기보다 작으므로 인덱스만 읽는 것이 전체 테이블을 다 읽는 것보다 효율적이다.

쿼리가 인덱스에 명시된 컬럼으로 조건을 처리할 수 있는 경우에 이 방식을 사용한다.

다만, 인덱스뿐만 아니라 데이터 레코드까지 읽어야 한다면 이 방식으로 처리되지 않는다.

인덱스 풀 스캔

MySQL에서의 인덱스 자료구조는 B+ Tree를 사용하기 때문에 리프 노드들끼리 Linked List 형식으로 연결되어 있다.

따라서, 이 방식은 인덱스 레인지 스캔 보다는 빠르지 않지만 테이블 풀 스캔보다는 효율적이다.

 

이 방식이 인덱스를 이용하기는 하지만 효율적인 방식은 아니며, 일반적으로 이 방식을 사용하는 경우에 "인덱스를 효율적으로 사용하지 못 한다."라고 한다.

루스 인덱스 스캔

 

루스 인덱스이란 듬성듬성하게 인덱스를 읽는 것이다.

이는 인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요하지 않는 인덱스 키 값은 무시하고 다음으로 넘어간다.

일반적으로 GROUP BY 또는 MAX(), MIN() 함수에 대해 최적화할 때 사용된다.

 

SELECT dept_no, MIN(emp_no) FROM dept_emp WHERE dep_no BETWEEN 'd002' AND 'd004' GROUP BY dept_no;

 

위 테이블의 인덱스는 (dept_no, emp_no) 조합으로 되어 있어 정렬되어 있다.

 

인덱스 스킵 스캔

 

employees 테이블에 아래와 같은 인덱스가 있다고 하자.

 

ALTER TABLE employees ADD INDEX ix_gender_birthdate (gender, birth_date);

 

위 인덱스를 사용하려면, WHERE 조건절에 gender에 대한 비교 조건이 필수이다.

 

 

예를 들어서, 다음과 같은 SQL문은 인덱스를 사용하지 못 한다. -> 인덱스 풀 스캔을 사용한 것

 

SELECT * FROM employees WHERE birth_date>='1965-02-01';

 

이 경우에는 birth_date 칼럼부터 시작하는 인덱스를 새로 만들어야한다.

 

하지만, MySQL 8.0 부터는 옵티마이저가 gender 칼럼을 건너띄고 birth_date칼람만으로 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔 최적화 기능이 도입됐다.

 

따라서 아래와 같은 실행 계획이 만들어진다.

인덱스 스킵 스캔

 

이는 아래와 같이 gender 컬럼 조건을 추가해서 쿼리를 다시 실행하는 형태로 처리된다.

 

 

하지만, 이 방식은 아래와 같은 단점이 있다.

 

만약, gender 컬럼의 유니크한 갯수가 많다면 효율적이지 못 하게 된다.

 

물론, 아래와 같이 쿼리가 인덱스 컬럼 외에 다른 컬럼들도 필요하게 된다면 풀 스캔 해야한다.

 

 

지금까지 MySQL이 인덱스를 사용하는 방법에 대해서 알아보았습니다.