Covering Index
Covering Index란
mysql 공식문서에 기재된 Covering Index의 정의는 다음과 같습니다.
An index that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, saving disk I/O.
쿼리에 사용된 모든 컬럼을 다 가지고 있는 인덱스를 Covering Index라고 합니다. 이는 단순히 WHERE절에 사용되는 컬럼만을 의미하는 게 아니라 SELECT절, ORDER BY절, GROUP BY절 등에 사용된 모든 컬럼을 의미합니다.- 보통의 인덱스는 실제 데이터가 저장된 페이지 블록에 빠르게 접근하기 위한 포인터로 사용됩니다. 하지만 Covering Index는
인덱스에 있는 데이터 자체를 반환합니다. 페이지 블록에 접근해 데이터를 가져오지 않기 때문에디스크 I/O비용을 절약할 수 있습니다.
실행계획
Extra 컬럼에 'Using index'값이 적혀있다면 이는 Covering Index로 실행된다는 의미입니다.
cf) Extra 컬럼에 'Using index'가 없고 단순히 type 컬럼만 index라면 이는 index full scan으로 실행된다는 의미입니다. index full scan은 인덱스 테이블을 모두 탐색한다는 의미로 table full scan보다는 속도가 빠르지만 전체적으로 봤을 때 빠른 동작방법은 아닙니다.
예제
테이블 생성
CREATE TABLE test (
id int NOT NULL AUTO_INCREMENT,
col1 int,
col2 int,
col3 varchar(255),
col4 varchar(255),
PRIMARY KEY (id),
KEY ix1 (col1,col2,col3)
);1. 기본
1-1. col1과 col2모두 ix1 인덱스에 포함된 값으로 커버링 인덱스로 동작한다
EXPLAIN SELECT col1 FROM test WHERE col2 > 10;
1-2. col4는 ix1 인덱스에 포함되지 않은 값이므로 커버링 인덱스로 동작하지 못한다
EXPLAIN SELECT col1, col4 FROM test WHERE col2 > 10;
1-3. InnoDB의 secondary index는 PK를 컬럼으로 가지고 있으므로 커버링 인덱스로 동작한다
EXPLAIN SELECT id, col1 FROM test WHERE col2 > 10;
2. GROUP BY와 ORDER BY
인덱스 테이블의 데이터들이 왼쪽 컬럼을 기준으로 정렬되어 있기 때문에 column의 순서가 중요합니다.
인덱스 테이블 예시
| col1 | col2 | col3 |
|---|---|---|
| 1 | 1 | 3 |
| 1 | 1 | 4 |
| 1 | 2 | 1 |
| 1 | 2 | 2 |
| 2 | 1 | 1 |
| 2 | 1 | 1 |
| 2 | 2 | 1 |
| 2 | 2 | 2 |
- 왼쪽 컬럼을 기준으로 값들이 정렬되어 있다
2-1. 커버링 인덱스 불가능 - 순서가 불일치하는 경우
[ORDER BY | GROUP BY] col2;
[ORDER BY | GROUP BY] col1, col3;
[ORDER BY | GROUP BY] col2, col3, col1;
[ORDER BY | GROUP BY] col2, col3 DESC, col1;
[ORDER BY | GROUP BY] col1, col2, col3, col4;2-2. 커버링 인덱스 가능 - 순서가 일치하는 경우
[ORDER BY | GROUP BY] col1;
[ORDER BY | GROUP BY] col1, col2;
[ORDER BY | GROUP BY] col1, col2, col3;2-3. 커버링 인덱스 가능 - 순서에 맞는 컬럼을 WHERE절에서 동등비교하는 경우
WHERE col1 = '' [ORDER BY | GROUP BY] col2;
WHERE col1 = '' AND col2 = '' [ORDER BY | GROUP BY] col3;3. 활용 예제
EXPLAIN SELECT *
FROM test
ORDER BY col1;
- *에는 col4가 포함되어 있습니다. 이는 ix1 인덱스에 존재하지 않는 값이므로 커버링 인덱스를 활용하지 못합니다.
이때 아래와 같이 쿼리를 수정하면 Covering Index를 활용해 동일한 결과를 얻을 수 있습니다.
SELECT *
FROM (
SELECT id
FROM test
ORDER BY col1
) b INNER JOIN test a ON a.id = b.id;
- 동일한 정렬조건으로 id값만을 SELECT하는 b테이블은 Covering Index를 활용할 수 있습니다. 이후 해당 테이블과 test테이블을 JOIN해 나머지 원하는 컬럼(*)값을 가져오면 더 빠르게 조회를 실행할 수 있습니다.
References
기록하고 정리하는 걸 좋아하는 백엔드 개발자입니다.