MySQL의 인덱스 스캔에 대해 알아보자 (feat. 커버링 인덱스)

어떤 경우에 인덱스를 사용하게 유도할지 말지를 판단하려면 어떻게 인덱스를 이용해서 실제 레코드를 읽는지 알 필요가 있다.

MySQL이 인덱스를 이용하는 대표적인 3가지 방법을 알아보자.

 

인덱스 레인지 스캔

가장 대표적인 인덱스 접근 방식으로 나머지 방법 중 가장 빠른 방법이다.

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

 

B-Tree 인덱스에서는 루트와 브랜치 노드를 이용해 스캔 시작 위치를 검색하고 그 지점부터 필요한 방향으로 인덱스를 읽어 나간다.

인덱스 자체가 정렬되어 있기 때문에 이런 범위 검색 또한 빠르고 정렬된 상태로 레코드를 가져온다.

 

인덱스 레인지 스캔의 순서를 살펴보면 다음과 같다.

1. 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다. (인덱스 탐색)
2. 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다. (인덱스 스캔)
3. 2번에서 읽은 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 데이터 페이지를 가져오고 최종 레코드를 읽는다.

커버링 인덱스 사용 유무에 따라 3번 과정이 필요 없을 수 있다.

커버링 인덱스로 처리되는 쿼리는 디스크의 레코드를 읽지 않아도 되기 때문에 랜덤 I/O가 상당히 줄고 그 만큼 성능은 빨라진다.

 

커버링 인덱스

커버링 인덱스란 쿼리의 select 절의 컬럼을 인덱스로 설정된 컬럼으로만 조회하는 것이다.

왜 커버링 인덱스를 사용하면 성능이 빨라지는지는 세컨더리 인덱스 쿼리가 처리되는 과정을 보면 이해가 빠르다.

세컨더리 인덱스 쿼리 처리 과정

1. WHERE 절의 조건으로 세컨더리 인덱스를 검색
2. 인덱스에서 조건에 맞는 레코드의 PK를 찾음.
3. SELECT 절 확인
4. SELECT 절에서 요청한 컬럼이 세컨더리 인덱스에 없으면, PK를 사용해 클러스터드 인덱스를 통해 데이터를 추가로 조회

이 과정에서 세컨더리 인덱스은 두 번의 디스크 I/O가 발생하게 된다.

 

커버링 인덱스 쿼리 처리 과정

하지만 커버링 인덱스의 경우, 다음과 같은 과정을 거친다.

1. WHERE 절의 조건으로 세컨더리 인덱스를 검색
2. 인덱스에서 조건에 맞는 레코드의 PK를 찾음.
3. SELECT 절 확인
4. SELECT 절에서 요청한 컬럼이 모두 세컨더리 인덱스에 있으므로, 추가 조회 없이 인덱스의 데이터를 바로 사용

인덱스를 검색한 후 추가적인 데이터 페이지 접근 없이도 필요한 모든 정보를 얻을 수 있다.

이는 디스크 I/O를 절반으로 줄여 더욱 성능이 빨라진다.

 

만약 아래 쿼리를 실행했다고 가정해보자. (age에 index가 걸려있다고 가정)

SELECT * FROM member WHERE age BETWEEN 20 AND 30;

위 범위에 해당하는 레코드가 3개라면

세컨더리 인덱스의 리프 노드에서 검색 조건에 일치하는 레코드를 찾고

SELECT * 이므로 모든 컬럼이 필요함을 확인하고 이 레코드가 참조하는 PK로 클러스터링 인덱스의 리프 노드에서 실제 데이터를 찾는다.

즉, 총 6번의 랜덤 I/O가 발생한다.

 

이 때 아래와 같이 커버링 인덱스를 적용한다면

SELECT age FROM member WHERE age BETWEEN 20 AND 30;

위 범위에 해당하는 레코드가 3개일 때

세컨더리 인덱스의 리프 노드에서 검색 조건에 일치하는 레코드를 찾는다.

SELECT 절에는 age만을 필요하고 이 데이터는 세컨더리 인덱스에 이미 포함되어 있어 실제 데이터를 찾아갈 필요가 없다.

즉, 총 3번의 랜덤 I/O가 발생하고 더욱 빠른 쿼리 성능을 만들어 낼 수 있다.

---

인덱스 풀 스캔

검색을 위한 최적의 인덱스가 없을 경우 차선책으로 선택되는 스캔이며, 인덱스의 처음부터 끝까지 모두 읽는 방식이다.

대표적으로 쿼리의 조건절에 사용된 컬럼이 복합 인덱스의 첫 번째 컬럼이 아닐 경우 인덱스 풀 스캔이 사용된다.

 

일반적으로 인덱스의 크기는 테이블의 크기보다 작아 직접 테이블을 처음부터 끝까지 읽는 것 보다는 인덱스만 읽는 것이 효율적이다.

즉, 테이블 풀 스캔보다는 인덱스 풀 스캔이 빠르다.

 

만약 아래 쿼리에서 (company, age)가 복합 인덱스로 설정되어있다면 인덱스 풀 스캔이 발생한다.

SELECT * FROM member WHERE age >= 20;

이 때 테이블의 총 레코드 수의 20 ~ 30% 이상을 읽어야하거나 

인덱스 크기(인덱스 페이지 수, 인덱스 깊이)가 크다면 테이블 풀 스캔이 일어날 수 있다고 한다.

---

루스 인덱스 스캔

말 그대로 느슨하게 듬성듬성하게 인덱스를 읽는 것이다.

인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요하지 않은 인덱스 키 값은 Skip하고 다음으로 넘어가는 형태로 처리한다.

일반적으로 GROUP BY 또는 집계 함수, DISTINCT에 대해 최적화를 하는 경우에 사용한다.

 

(company, age) 복합 인덱스가 설정되어 있는 아래 쿼리를 실행했다고 가정해보자

SELECT company, COUNT(company) 
FROM member 
WHERE age BETWEEN 20 AND 30;
GROUP BY company;

1. WHERE 절에서 age가 20~30 범위에 있는 첫 번째 레코드를 찾음
2. GROUP BY 절에서 age 범위 내의 첫 번째 그룹 처리
   
   • age 범위 내라면 COUNT 수행
   • age 범위 내가 아니라면 다음 그룹으로 건너뛰기
3. 이 과정 반복 

WHERE 조건을 만족하는 범위 전체를 다 스캔할 필요가 없다는 것을 옵티마이저는 알기 때문에

조건에 만족하지 않는 레코드는 무시하고 다음 레코드로 이동한다.

 

보통은 GROUP BY 또는 집계 함수, DISTINCT를 사용할 때 인덱스 루스 스캔이 발생하는데 항상은 아닌 것 같다.

정확한 조건을 확인을 찾지 못해서 이런 쿼리를 사용할 때 쿼리 실행 계획으로 어떻게 스캔하는지 확인할 필요가 있을 것 같다.

---

인덱스 스킵 스캔

(company, age) 복합 인덱스가 있다고 가정해보자.

이 복합 인덱스를 사용하려면 WHERE 조건절에 첫 번째 인덱스 요소인 company에 대한 비교 조건이 필수다.

SELECT * FROM member WHERE compnay = 'Checks';

SELECT * FROM member WHERE compnay = 'Checks' AND age >= 20;

만약 compnay 컬럼에 대한 비교 조건이 없다면 해당 복합 인덱스를 효율적으로 사용할 수 없었다. (인덱스 풀 스캔)

MySQL 8.0버전 부터는 company를 건너 뛰어서 age만으로도 복합 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔 기능이 도입되었다.

 

아래 쿼리를 실행했다고 가정해보자.

SELECT company, age FROM member WHERE age >= 20;

MySQL은 위 상황에서 인덱스 스킵을 사용해 company에 컬럼에서 유니크한 값을 모두 조회해

주어진 쿼리에 company 컬럼 조건을 추가해 다시 쿼리를 실행하는 형태로 처리한다.

 

위 작동방식에서 느낄 수 있듯이 모든 상황에서 인덱스 스킵 스캔이 적용되는 것은 아니다.

다음과 같은 조건이 있다.

• WHERE 조건절에 조건이 없는 인덱스의 선행 컬럼의 유니크 값 개수가 적어야함 (카디널리티가 높아야함)
• 커버링 인덱스를 적용해야함

---

B-Tree 인덱스의 스캔 방향

인덱스를 구성하는 컬럼의 정렬은 오름차순, 내림차순으로 설정할 수 있다.

또한 다음과 같이 정렬 순서를 혼합한 인덱스를 MySQL 8.0 부터 생성할 수 있게 됐다.

CREATE INDEX idx_member_composite ON member (company ASC, age DESC);

이처럼 인덱스를 생성할 때 설정한 정렬 규칙에 따라 오름차순, 내림차순으로 정렬되어 저장된다.

만약 인덱스가 오름차순으로 정렬되었다면 이 인덱스르 읽을 때 오름차순으로만 읽는 것은아니다.

인덱스를 어느 방향으로 읽을지는 쿼리에 따라 옵티마이저가 만들어 내는 실행 계획에 따라 결정된다.

 

아래 쿼리에 age가 인덱스(오름차순)가 적용되어 있다고 가정해보자.

SELECT * FROM member ORDER BY age ASC LIMIT 1;

SELECT * FROM member ORDER BY age DESC LIMIT 1;

첫 번째 쿼리는 age 인덱스가 이미 오름차순으로 정렬되어 있으므로, 첫 번째로 만나는 값이 바로 최솟값이 된다.

두 번째 쿼리는 age 인덱스를 역방향으로 읽어나가며 첫 번째로 만나는 값이 최댓값이 된다.

이 처럼 MYSQL 옵티마이저는 인덱스의 읽기 방향을 전환해 사용하도록 실행 계획을 만들어낸다.

---

인덱스 내림차순 스캔

최신 데이터를 자주 조회하는 경우(예: 최근 주문 내역, 최신 게시글 목록 등)에 내림차순 인덱스를 활용하면 성능을 최적화할 수 있다.

하지만 무조건 성능이 최적화 되는 것은 아니다.

 

오름차순 인덱스일 경우 인덱스 정순 스캔을 내림차순 인덱스일 경우엔 인덱스 역순 스캔을 한다.

중요한점은 B-Tree 인덱스는 페이지 간의 양방향 연결 링크를 가지고 있지만 페이지 내부의 레코드들은 단방향 링크만 가지고 있다.

InnoDB 스토리지 엔진에서는 내부적으로 페이지의 레코드를 접근할 때마다, 페이지에 대해서 잠금을 획득한다고 한다.

그리고 페이지 잠금은 데드락 방지를 위해 인덱스 정순 스캔에 적합한 구조로 되어 있다.

따라서 인덱스 정순 스캔 시에는 페이지 잠금 획득이 매우 간단하지만

역순 스캔 시에는 페이지 내에서 복잡한 과정을 거쳐 느릴 수 밖에 없다고 한다.

 

ORDER BY ~ DESC LIMIT ~ 를 사용하는 쿼리가 소량의 레코드에 실행되는 경우라면

굳이 내림차순 인덱스를 고려할 필요는 없을 것 같다.

 

자세한 내용은 아래 카카오 기술 블로그를 통해 확인할 수 있다.

https://tech.kakao.com/posts/351

---

참고자료

• Real MySQL 8.0 1권