[MySQL Index] 2. 커버링 인덱스 (Covering Index)란 무엇인가?

Damongsanga·2024년 7월 1일
IndexRDBMSdbmysql스터디

전 글에 이어서 두번째 DB Index 포스트이다. 이번엔 커버링 인덱스에 대해 알아보자. 사실 스터디에서 내용 공유할 때는 커버링 인덱스를 잘 이해하지 못해 간단하게 언급만 하고 넘어갔는데.. 뭔가 아쉬워서 혼자서 더 공부해보았다.

Covering Index란 무엇인가요?

  • 조회하려는 column이 index 내의 데이터만으로 모두 cover가 가능할 때 이 index를 covering index라고 한다
    • 쿼리에 사용되는 모든 열(컬럼)을 인덱스에 포함시킵니다.
  • 일반적으로는 인덱스 기반으로 실제 테이블을 조회하여 찾으나, 쿼리에 필요한 모든 열이 인덱싱 되어있다면, 인덱스만으로도 쿼리 결과를 얻을 수 있다.
  • 본 테이블까지 찾을 필요가 없기 때문에 조회 성능이 매우 빠르다
  • 따라서 성능 개선을 위해 의도적으로 만들기도 한다.

잘 이해가 되지 않는가? 나도 안됐다..

그러면 인덱스를 스캔하는 방법을 배워보자. 우선 아래 상황을 생각해보자.

인덱스 레인지 스캔

employees 테이블에 first_name 에는 인덱스가 걸려있다.

SELECT * FROM employees WHERE first_name BETWEEN "Harry" AND "Ron"; # -> 1
SELECT first_name FROM employees WHERE first_name BETWEEN "Harry" AND "Ron"; # -> 2

인덱스 레인지 스캔 과정은 아래와 같다

  1. 인덱스 탐색 : 인덱스에서 조건이 만족하는 값이 저장된 위치를 찾음

  2. 인덱스 스캔 : 1번에서 탐색한 위치부터 쭈욱 차례대로 인덱스를 읽는다 (B+Tree는 LinkedList로 연결되어있음으로 페이지가 넘어가더라도 가능하다!)

  3. 2번에서 읽어들인 인덱스 키와 레코드 주소를 이용하여 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다.

    만약 1번 SQL 문의 경우는 3번 과정까지 진행해야 한다. 그러나 2번 SQL문은 이미 인덱스 만으로 쿼리할 데이터를 모두 가져왔음으로 3번 과정이 불필요하다. 여기서 3번 과정은 Random I/O임으로 비용이 크다. 이 비용을 줄이는 과정에서 성능이 매우 높게 개선된다.

인덱스 풀 스캔

추가로 공부해보자. 인덱스를 사용하기는 하지만 인덱스의 처음부터 끝까지 모두 읽는 방식이다.

예시로 인덱스를 (A, B, C)로 만들어두었으나, B나 C 칼럼으로 조건절을 검색하는 경우가 있다.

SELECT 절에 인덱스가 걸린 칼럼만 있는 경우에 이 방식이 진행되며, 물론 인덱스 뿐만 아니라 데이터 레코드 모두 읽어야 한다면 테이블 풀 스캔을 할 것이다. 이 방식들은 “인덱스를 효율적으로 사용하지 못한” 경우일 것이다.

물론 인덱스의 전체크기가 테이블의 전체 크기보다 작음으로, 인덱스 풀 스캔이 테이블 풀 스캔보다는 적은 디스크 I/O로 쿼리를 처리할 수 있다.

루스 인덱스 스캔

추가로 공부해보자. 커버링 인덱스와는 별도로 인덱스 스캔의 방식이 보다 개선된 방법이다.

인덱스 레인지 스캔과 유사하게 동작하지만, 페이지를 건너갈 때마다 중간에 필요치 않는 인덱스는 스킵하고 넘어가는 형태로 처리된다.

일반적으로 GROUP BY 등의 집합함수에서의 MAX(), MIN() 함수의 최적화 경우에서 사용된다.

예시로 만약 인덱스가 (A, B) 칼럼을 기준으로 인덱싱되어있다면, A 그룹 별로 첫번째 레코드의 B 값만 읽어보면 된다. 만약 A 그룹 조건이 만족하지 않거나, 첫번째 B 값이 만족하지 않으면 그룹을 스킵해버리면 된다.

이를 실행하기 위해서는 여러 조건을 만족해야한다. 이 내용은 다음에 공부해보자.

인덱스 스킵 스캔

추가로 공부해보자. MySQL 8.0 버전부터 도입된 인덱스 스킵 스캔은 복합 인덱스 (A, B)에서 B를 기준으로 비교검색을 하는 경우에 적용될 수 있다. 

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

SELECT gender, birth_date FROM employees WHERE birth_date >= "2000-01-01";

인덱스 스킵 스캔이 적용되지 않는다면 인덱스 풀 스캔을 진행했을 진행했을 것이다. (일단 커버링 인덱스임으로)

그러나 8.0 부터는 옵티마이저가 gender 칼럼을 뛰어넘어 birth_date 칼럼만으로도 검색이 가능하다. 여기서 루스 인덱스 스캔과 혼동할 수 있는데, 이는 GROUP BY 작업을 처리하기 위해 Index를 사용하는 경우만 적용할 수 있었다. 이에 반해 인덱스 스킵 스캔은 WHERE 절에도 적용할 수 있어 더 폭넓은 활용이 가능해졌다.

EXPLAIN 명령어로 위 쿼리의 실행계획을 확인하면

인덱스 스킵 진행 X : type = index, Extra = Using where; Using index

mysql> SET optimizer_switch='skip_scan=off';
mysql> EXPLAIN SELECT gender, birth_date FROM employees WHERE birth_date >= "2000-01-01";
+----+-----------+------------+-------+---------------------+---------------------+--------------------------+
| id | table     | partitions | type  | possible_keys       | key                 | Extra                    |
+----+-----------+------------+-------+---------------------+---------------------+--------------------------+
|  1 | employees | NULL       | index | ix_gender_birthdate | ix_gender_birthdate | Using where; Using index |
+----+-----------+------------+-------+---------------------+---------------------+--------------------------+

인덱스 스킵 진행 O : type = range, Extra = Using where; Using index for skip scan

mysql> SET optimizer_switch='skip_scan=on';
mysql> EXPLAIN SELECT gender, birth_date FROM employees WHERE birth_date >= "2000-01-01";
+----+-----------+------------+-------+---------------------+---------------------+----------------------------------------+
| id | table     | partitions | type  | possible_keys       | key                 | Extra                                  |
+----+-----------+------------+-------+---------------------+---------------------+----------------------------------------+
|  1 | employees | NULL       | range | ix_gender_birthdate | ix_gender_birthdate | Using where; Using index for skip scan |
+----+-----------+------------+-------+---------------------+---------------------+----------------------------------------+

여기서 인덱스 스킵이 진행된 경우,

  1. MYSQL 옵티마이저는 gender 칼럼에서 유니크한 값을 모두 조회
  2. 주어진 쿼리에 gender 칼럼의 조건을 추가해서 쿼리를 다시 실행
    1. SELECT gender, birth_date FROM employees WHERE gender = “F” AND birth_date >= "2000-01-01";
    2. SELECT gender, birth_date FROM employees WHERE gender = “M” AND birth_date >= "2000-01-01";

이 과정에서 알 수 있듯, 인덱스 스킵 스캔에는 제한조건이 존재한다.

  1. WHERE 조건절에 조건이 없는 인덱스의 선행칼럼 (위 예시에서 gender)의 유니크한 값이 “적어야”한다

    • 이는 유니크한 값이 많다면 옵티마이저가 인덱스에서 스캔해야 되는 시작 지점을 검색하는 첫 작업이 많이 필요해진다. 따라서 유니크한 값이 많다면 오히려 성능이 떨어진다.

  2. 커버링 인덱스여야 : 쿼리가 인덱스에 존재하는 칼럼만으로 처리 가능해야 한다

    mysql> EXPLAIN SELECT * FROM employees WHERE birth_date >= "2000-01-01";
+----+-----------+------------+-------+----------------------------------------+
| id | table     | partitions | type  | Extra                                  |
+----+-----------+------------+-------+----------------------------------------+
|  1 | employees | NULL       | ALL   | Using where; Using index for skip scan |
+----+-----------+------------+-------+----------------------------------------+

드디어 마지막이다! 다음에는 인덱스가 어떠한 자료구조로 만들어져 있는지 알아보도록 하자!

참조

Real MySQL 1권 8장
https://www.youtube.com/watch?v=IMDH4iAQ6zM&t=510s
https://steady-coding.tistory.com/558
https://velog.io/@jewelrykim/Binary-Search-Tree에서-BTree까지Database-Index-추가
https://velog.io/@semi-cloud/MySQL-B-Tree-인덱스-구조와-인덱스-스캔
https://steady-coding.tistory.com/558
https://velog.io/@chosj1526/DB-Index-개념-장단점-자료구조
https://velog.io/@sweet_sumin/클러스터드-인덱스-Clustered-Index-넌-클러스터드-인덱스-Non-Clustered-Index
https://mangkyu.tistory.com/286 ⇒ 매우 자세히 설명되어있음
https://bo5mi.tistory.com/212

profile
Damongsanga
향유하는 개발자
이전 포스트

[MySQL Index] 1. 인덱스는 왜 사용하며, 언제 사용해야 좋은가?

다음 포스트

[MySQL Index] 3. 인덱스는 어떠한 자료구조로 만들어졌나요? (Hash, B-Tree, B+Tree, Red Black Tree)

0개의 댓글