데이터베이스 스터디를 하며 인덱스를 내용을 공부하고 발표한 뒤, covering index 에 대한 질문을 받았다.
스터디 책에서는 data file 을 구현하는 방법 중에 모든 데이터 레코드가 인덱스 파일에 들어있는 index-organized tables (IOT) 방식을 소개하고 있었는데, IOT 와 covering index 가 같은 개념인지 묻는 질문이었다.
covering index 개념을 처음 들어봐서 다른 분의 답변을 듣고 공부할 수 있었는데, 새로 알게된 개념인만큼 이번 기회에 정리해보려고 한다.
Covering Index
covering 은 포괄한다는 의미를 갖고 있다.
인덱스는 테이블 데이터를 찾을 때 사용할 일부 컬럼에 대한 데이터를 갖고 있는데, 이 데이터만으로 쿼리를 커버할 수 있는 인덱스를 가리켜 covering index 라고 한다.
단일 키에 대한 인덱스
create table student (
sid bigint,
name varchar(30),
age int,
grade int,
primary key (sid));
예를 들어 위와 같이 테이블을 정의했을 때, primary key 인 sid 에 대해서 기본 인덱스가 생성된다.
DataGrip을 통해 기본으로 만들어진 인덱스 정보를 조회할 수 있다.
기본 인덱스의 이름은 PRIMARY 이며, sid 컬럼 하나에 대한 인덱스이고, 그 타입은 btree 임을 알 수 있다.
B-Tree 의 구조를 간단하게 표현하면 위와 같이 표현할 수 있다.
하나의 data entry (인덱스 파일의 data record) 에는 4개의 데이터와 5개의 포인터가 있다.
(각 데이터 좌우로 다음 data entry 를 가리키는 포인터가 존재)
그리고 마지막 data entry 는 실제 data record 위치를 가리키고 있다.
explain
select *
from student;
마리아 DB를 사용하여 stduent 테이블에 450만 개의 데이터를 넣어두고 전체 데이터를 조회하는 쿼리에 대해 실행 계획을 출력해보았다.
참고로 expalin 의 실행 결과로는 위와 같은 정보를 보여준다.
실행 결과는 위와 같이 나왔다.
type 이 ALL 이라는 것은 적절한 인덱스를 찾지 못해서 테이블을 풀 스캔했다는 것을 의미한다.
어차피 모든 row의 모든 컬럼을 다 조회해야 하므로, 인덱스를 타서 왔다갔다 하는 것보다, 직접 풀스캔하는 것이 더 효율적이다.
explain
select sid
from student;
이번엔 기본 인덱스가 걸려있는 sid 항목만 집어서 조회해보자.
실행 결과는 위와 같다.
type 이 index 로 바뀌었는데, 공식문서를 확인해보니 index 를 사용해서 full scan 을 진행했다는 것을 의미한다.
그리고 Extra 항목을 보면 Using index 라고 적혀있다.
공식문서를 보면, Using index 라는 말은 테이블에서 필요한 정보를 가져오기 위해 오직 index 만 활용했으며, 실제 레코드를 가져오는 추가적인 작업이 필요없다는 의미라고 한다.
바로 이것이 Covering index 에 해당한다.
위 그림에서 녹색은 index file 에 저장된 데이터를, 빨간색은 data file 에 저장된 데이터를 나타낸다.
sid 기반으로 만들어진 index file 에 sid 값이 들어있으므로, 만약 sid = 3 인 데이터를 찾고자 한다면 data record 를 가리키고 있는 3 이라는 데이터를 갖고 있는 data entry 까지만 도달하면 굳이 실제 data record 를 읽어보지 않아도 원하는 sid 값 3을 찾을 수 있다.
따라서 인덱스만 가지고도 원하는 데이터를 조회할 수 있다.
실제 실행시간을 측정해보면, 풀스캔을 진행하는 경우, 400만개 레코드를 조회하는데 24초의 시간이 소요되었고
sid 만 조회하는 경우에는 13초의 시간이 걸린 것을 알 수 있다.
실제 데이터 레코드가 아니라, 데이터 파일보다 크기가 작은 인덱스 파일을 풀스캔하므로 시간이 더 적게 걸린다.
explain select name from student;
이번에는 인덱스가 걸려있지 않은 name 컬럼 데이터를 조회하는 실행 계획을 살펴보자.
type 이 ALL 로 테이블 풀스캔을 하는 것으로 나타난다.
실제 실행 시간은 13.5 초 정도 소요되었다.
create index name_idx on student(name);
이번엔 name 컬럼에 인덱스를 생성하고, 같은 쿼리를 사용해보자.
인덱스를 생성하는데에도 꽤 시간이 걸린다.
쿼리플랜을 보면 type 이 index 로 바뀌면서 인덱스를 사용한 테이블 풀스캔을 진행한다는 것을 알 수 있고,
Extra 항목을 보면 이 방법도 Covering index 를 사용하고 있음을 알 수 있다.
쿼리에 등장하는 컬럼이 name 밖에 없는데, 이 name 컬럼을 완전히 포함하는 인덱스가 존재하기 때문이다.
하지만 실제 실행시간에 큰 차이는 없었다.
인덱스 파일에 대한 풀 스캔이냐, 데이터 파일에 대한 풀 스캔이냐 차이만 존재해서 큰 차이가 안나는 건가 싶다.
복합 키에 대한 인덱스
explain
select name
from student
where age < 20;
이번에는 복합키에 대한 인덱스를 테스트해보기 위해 위와 같이 쿼리 플랜을 작성해서 실행해보았다.
type 이 ALL 이므로 테이블 풀스캔을 수행한다는 것을 알 수 있다.
age 에 대해 인덱스를 걸긴 했지만, 어차피 student table 에서 name 을 알아오려면 테이블 데이터를 다 읽어와야 한다.
extra 에는 Using where 라는 항목으로 나온다.
공식문서를 보니 where 절을 사용했다면 표시된다고 한다.
이제 한번 쿼리를 직접 실행해보았다.
실행 결과 약 42.5초의 시간이 걸린 것을 확인할 수 있다.
이제 이 쿼리에 대한 covering index 를 만들어보자.
covering index 는 쿼리에 등장하는 모든 컬럼을 포함하는 인덱스를 말한다.
create index name_age_idx on student(age, name);
이렇게 인덱스를 만들었다.
역시 인덱스를 만드는 데에도 시간이 50초로 꽤 걸린다.
이제 쿼리 플랜을 다시 실행해보면
이렇게 나온다.
type 을 보면 range 라고 나와있는데, 공식문서 설명을 보면, 테이블의 하나 이상의 값 범위에 대해 키를 통해 접근하는 것을 나타낸다고 한다.
여기에서 key 는 위 표에서 볼 수 있듯 name_age_idx 라는 인덱스를 가리킨다.
즉, 테이블의 일정 range 를 위 표에 나온 key 를 통해서 접근한다는 것을 말한다.
거기에 더해 Extra 필드를 보면 Using index 가 새로 추가된 것을 볼 수 있다.
즉, 오직 index 만 가지고 데이터를 얻어오며, 실제 데이터 레코드의 값을 읽어오지 않는다는 뜻이다.
쿼리를 실제로 실행해본 결과, 같은 데이터를 조회하는데 13초의 시간이 걸린 것을 확인할 수 있다.
커버링 인덱스를 걸기 전에 42.5 초가 걸렸던 점을 비교해보면 무려 70% 정도 소요 시간이 줄어든 셈이다.
복합 키에 대해 b-tree 를 구성하는 경우 위 그림과 같이 왼쪽 키부터 정렬하는 방식으로 인덱스를 구성한다고 한다.
따라서 age < 20 이라는 조건을 걸게 되면 위 그림에서 오른쪽에 있는 (20, c) 오른쪽 영역과 (21,d) 오른쪽 포인터 밑에 연결된 인덱스에 대해서는 전혀 탐색하지 않으므로 탐색 범위를 획기적으로 줄일 수 있다.
거기에 더해 데이터 레코드를 가리키는 최종 데이터 엔트리까지 도달하면 (18, a) 와 같은 (age, name) 쌍의 데이터를 얻게 된다.
이 데이터 만으로 주어진 쿼리를 해결할 수 있으므로, 데이터 파일에 대한 추가적인 접근 없이 인덱스 파일의 내용만으로 데이터를 얻어올 수 있게 된다.
이것이 커버링 인덱스의 장점이다.
그리고 궁금해서 복합키 인덱스의 순서를 <age, name> 에서 <name, age> 로도 바꿔보았다.
possible key 항목에서 name_age_idx 가 빠졌지만 여전히 Using index 로 실제 데이터 레코드 조회 없이 데이터를 조회할 수 있다는 걸 알 수 있다.
그리고 type 항목이 range 에서 index 로 바뀌었다.
index 는 index 를 사용해서 풀 스캔을 한다는 뜻이다.
이 내용을 조합해보면, age_idx 라는 age 에 걸어둔 인덱스를 가지고 풀 스캔을 돌린 뒤,
그 age 값을 가지고 <name, age> 에 대한 복합키 인덱스에서 name 을 찾는다고 이해하였다.
그리고 이렇게 인덱스를 구성한 다음 쿼리를 실행했더니 놀랍게도 9.5 초만에 모든 데이터를 읽어오면서 오히려 성능이 향상되었다.
<name, age> 기준이면 <name> 을 기준으로 먼저 정렬하고, 다음으로 <age> 기준으로 정렬하기 때문에 성능이 더 나빠질 것이라고 생각한 것과 달라서 놀랍다.
혹시 age_idx 가 존재해서 그런걸까 싶어 age_idx 를 제거하고 다시 테스트해봤다.
explain 실행 결과 possible keys 리스트가 빈 것 말고는 동일하다.
즉, Using index 가 활성화 되어있으므로 여전히 covering index 를 사용한다는 것을 알 수 있다.
실행 결과는 7.5초로 오히려 더 줄어들었다.
마지막으로 <age, name> <name, age> 두 인덱스를 모두 만들고 나서 실행을 해보았다.
explain 에서는 age_name_idx 를 사용한 range 타입의 실행을 한다고 나왔다.
그리고 실행성능은 5초로 줄어들었다. (점점 빨라지는 것 같은데..)
그래서 뭔가 이상하다 싶어 다시 <name, age> 인덱스를 지우고 다시 돌려보았더니 여전히 5초 대가 나왔다.
그냥 실행할 때마다 뭔가 캐싱같은 걸 해서 빨라지는 걸까 싶어서 다시 <age, name> 인덱스를 지우고, <name, age> 인덱스만 만들어서 쿼리를 실행해보았다.
다시 7초대가 나온다.
따라서 <age, name> 인덱스를 사용하는 것이 <name, age> 인덱스를 사용하는 것보다 대략 33% 정도 더 빠르다.
그리고 두 인덱스 모두 covering index 이기 때문에 풀스캔을 하는 것보다는 매우매우 빠르다는 것을 알 수 있었다.
결론
이제 'IOT 와 covering index 가 같은 개념인지 묻는 질문'에 대답을 할 수 있을 것 같다.
IOT 는 인덱스 자체 안에 모든 데이터를 저장하는 방식으로, 데이터 파일과 인덱스 파일을 하나로 통합해서 관리하는 파일 구성의 한 방법이다.
반면 covering index 는 인덱스와 데이터 레코드를 같은 파일에 관리하나 다른 파일에 관리하나 상관이 없다.
(인덱스 안에 데이터를 저장한다는 것과 인덱스와 데이터 레코드가 같은 파일에 있다는 것은 다르다.)
covering index는 '기본적으로 인덱스를 통해 데이터 레코드의 위치를 알아내고, 데이터 레코드 위치로 추가 접근을 통해 데이터를 알아오지만, 인덱스가 조회할 데이터를 모두 커버하는 경우, 데이터 레코드에 추가 접근을 하지 않는 개념' 이고
IOT 는 인덱스 안에 모든 데이터가 들어있기 때문에 항상 인덱스만으로 모든 데이터를 조회하는 개념이다.
즉, 커버링 인덱스가 추가적인 데이터 파일에 대한 접근이 없어도 된다는 특징은 IOT 와 동일하지만,
커버링 인덱스는 인덱스와 데이터 레코드가 서로 분리되어 있기 때문에 성능상 이점을 얻는 기법이고,
IOT 는 모든 데이터 레코드가 인덱스에 들어있어서 애초부터 데이터 레코드를 추가로 조회할 필요가 없는 기법이다.
