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

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

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

 

인덱스 레인지 스캔

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

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

 

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

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

 

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

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

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

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

 

커버링 인덱스

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

참고로 PK를 사용해 조회할 경우에는 큰 성능 최적화가 없다.

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

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

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

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

만약 select 절에 세컨더리 인덱스로만 이뤄져있으면 몰라도 아닌 컬럼에 대해서는

세컨더리 인덱스가 데이터 페이지에 접근해 원하는 컬럼을 읽어와야하기 때문이다.

 

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

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

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% 이상을 읽어야하거나 

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

---

루스 인덱스 스캔

루스 인덱스 스캔은 인덱스의 일부만 선택적으로 읽는 최적화 기법으로 B-Tree 인덱스의 정렬 특성을 활용해 

GROUP BY / DISTINCT 처리 시 인덱스에서 필요한 값만 읽고 중간의 불필요한 값들을 건너뛰어 I/O를 크게 감소시킨다.

 

간단히 예시를 보면 만약 카테고리와 브랜드가 복합 인덱스로 구성되어 있다고 가정해보자.

그럼 B-Tree 정렬 특성상 다음과 같이 정렬될 것이다.

전자제품, 삼성
전자제품, 삼성
전자제품, 애플
전자제품, 애플
전자제품, LG

 

만약 아래 쿼리를 실행하면

SELECT DISTINCT category, brand FROM products;

아래와 같이 읽을 필요가 없는 부분은 건너뛰게 된다.

1. "전자제품, 삼성" 읽기 → 결과에 추가
2. 다음 "전자제품, 삼성" 건너뛰기
3. 전자제품, 애플" 읽기 → 결과에 추가
4. 이런 식으로 계속...

 

루스 인덱스 스캔이 적용되면 쿼리 실행 계획의 "Extra" 컬럼에 "Using index for group-by"라고 표시된다.

루스 인덱스 스캔 적용에는 조건이 있다.

이 조건과 예시에 대해서는 아래 공식문서를 참고했다.

https://dev.mysql.com/doc/refman/8.4/en/group-by-optimization.html#loose-index-scan

 

우선 예시를 위한 테이블은 다음과 같다.

CREATE TABLE t1 (
    c1 VARCHAR(50),
    c2 VARCHAR(50),
    c3 INT,
    c4 VARCHAR(100)
);

CREATE INDEX composit_idx ON t1(c1, c2, c3);
CREATE INDEX idx_c4 ON t1(c4(50));

 

단일 테이블 쿼리여야 한다.

말 그대로 조인된 테이블에서는 루스 인덱스 스캔을 적용할 수 없다는 말이다.

 

쿼리에 사용된 컬럼에 인덱스가 있어야 한다.

당연하게도 인덱스가 없으면 테이블 풀 스캔을 하기 때문에 인덱스 스캔 자체가 발생하지 않는다.

 

복합 인덱스일 경우 GROUP BY / DISTINCT에 사용된 컬럼들이 복합 인덱스의 왼쪽 접두사와 일치해야 한다.

이건 복합 인덱스 자체의 조건이기도 하다.

-- 루스 인덱스 스캔 적용 O : 복합 인덱스의 왼쪽 컬럼 부터 시작함
EXPLAIN SELECT c1, c2 FROM t1 GROUP BY c1, c2;

 

-- 커버링 인덱스는 적용되지만 루스 인덱스 스캔 적용 X : 복합 인덱스의 왼쪽 컬럼 부터 시작하지 않기 때문
EXPLAIN SELECT c2, c3 FROM t1 GROUP BY c2, c3;

MySQL에서 기본적으로 "ONLY_FULL_GROUP_BY" 모드가 활성화 되어 있기 때문에

GROUP BY 절에 나열된 모든 컬럼이 SELECT 절에 있어야 한다. (이외의 컬럼은 집계 함수만 허용)

따라서 위 쿼리의 경우에는 루스 인덱스 스캔이 적용되지 않아 임시 테이블을 생성하지만

커버링 인덱스가 적용되어 인덱스 테이블에서 인덱스만 스캔하기 때문에 I/O는 줄 수 있으나

 

아래 쿼리의 경우에는 커버링 인덱스 마저 적용되지 않아 테이블 풀 스캔으로 더 비효율적인 쿼리가 된다.

EXPLAIN SELECT c2, c3, SUM(c4) FROM t1 GROUP BY c2, c3;

 

쿼리에서 참조하는 GROUP BY 이외의 인덱스의 모든 부분은 상수와 동등 비교여야 한다. (MIN() 또는 MAX() 함수의 인수는 예외)

 

아래 쿼리는 GROUP BY 이외의 인덱스의 구성 요소에 상수와 동등 비교를 하기 때문에 루스 인덱스 스캔이 적용된다.

EXPLAIN SELECT c1, c2 FROM t1 WHERE c3 = 100  GROUP BY c1, c2;

 

하지만 아래의 경우에는 적용되지 않는다.

루스 인덱스 스캔은 중복 값을 건너뛰는 것이 핵심이다.

하지만 동등 비교가 아닌 경우에는 여러 값 확인이 필요하기 때문에 건너뛰어선 안된다.

EXPLAIN SELECT c1, c2 FROM t1 WHERE c3 > 100  GROUP BY c1, c2;

 

예외 상황으로는 MIN() 또는 MAX() 함수를 사용할 때이다.

동등 비교를 하지 않더라도 어차피 인덱스의 정렬 특성을 활용해 첫 번째/마지막 값만 가져오면 되므로 건너뛰기가 가능기 때문이다.

EXPLAIN SELECT c1, c2, MAX(c3) FROM t1 GROUP BY c1, c2;

주의할점으로는 MIN(), MAX()가 GROUP BY 절에 사용된 복합 인덱스 컬럼의 바로 다음 위치에 있는 컬럼이여야한다.

위 복합 인덱스는 (c1, c2, c3)로 이뤄져있는데 GROUP BY절에는 c1, c2가 있고 그 다음 복합 인덱스 컬럼은 c3이다.

따라서 위 쿼리는 루스 인덱스 스캔이 정상 작동한다.

 

EXPLAIN SELECT c1, c2, MAX(c3) FROM t1 GROUP BY c1, c2, c3;

하지만 위 쿼리는 루스 인덱스 스캔이 작동하지 않는다.

(c1, c2, c3) 조합은 이미 고유하다고 판단되어 건너 뛰지 않는 것이다.

따라서 인덱스 풀 스캔으로 동작한다.

 

읽은 rows를 비교해보면 루스 인덱스 스캔이 무려 10배 가까이 덜 읽은 것을 볼 수 있다.

만약 이런 상황에서 모든 그룹화가 필요 없이 복합 인덱스 컬럼의 오른쪽 컬럼의 최대값, 최솟값 집계가 필요하다면

루스 인덱스 스캔을 적용하는게 훨씬 성능에 좋을 것이다.

 

집계 함수는 기본적으로 MIN(), MAX()로 제한된다.

EXPLAIN SELECT c1, MIN(c2) FROM t1 GROUP BY c1;

MIN(), MAX() 함수는 인덱스의 정렬 특성을 활용해 첫 번째/마지막 값만 가져오면 되므로 건너뛰기가 가능하다.

주의할 점 또한 바로 위 조건 설명과 같다.

 

반면 이외의 SUM()이나 COUNT() 같은 함수는 모든 값을 확인해야 하므로 건너뛰기가 불가능하다.

단, 다음과 같이 DISTINCT로 중복을 제거하여 함께 사용한다면 루스 인덱스 스캔 적용이 가능하다.

EXPLAIN SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;
EXPLAIN SELECT COUNT(DISTINCT c1) FROM t1;

---

인덱스 스킵 스캔

(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권