[DB] 인덱스 확장 (Index Extension)

0. 학습 목적

1. 인덱스 확장이 무엇인지 이해한다.
2. 인덱스 확장 전략의 장점을 이해한다.

1. 인덱스 확장 이란?

Index Extension은 InnoDB를 사용하는 테이블에서 보조 인덱스를 활용할 때, 그 보조 인덱스에 자동 추가된 PK도 함께 활용할 수 있게 할지 말지를 정하는 옵션이다.

해당 옵션을 활성화하면, 보조인덱스를 (보조 인덱스, PK) 로 된 복합 인덱스를 활용하는 것처럼 사용할 수 있다.

다음과 같이 테이블이 있다고 해보자.

-- 테이블 생성
CREATE TABLE dept_emp (
    dept_no INT NOT NULL,
    emp_no CHAR(4) NOT NULL,
    salary INT,
    from_date DATE NOT NULL,
    PRIMARY KEY (dept_no, emp_no)
    INDEX ix_fromdate (from_date)
)engine='innodb';

보조인덱스인 ix_fromdate는 오직 from_date만 컬럼 키로 가지고 있다. 하지만 Index Extension을 켜두면 보조인덱스를 (from_date, dept_no, emp_no) 형태의 복합 인덱스인 것처럼 사용할 수 있다. 이는 다음과 같은 장점이 있다.

2. 장점

1. 커버링 인덱스가 가능해짐
2. 보조인덱스의 리프노드 속 PK로 정렬 작업도 가능해짐.

   (1) 커버링 인덱스가 가능해짐.

   explain
   select count(*) 
   from dept_emp 
   where from_date = '1987-02-25' 
   AND dept_no = 'd001' 

다음과 같은 쿼리문의 경우 , 위에서 만든 ix_fromdate의 키 컬럼과 PK로 처리가 가능해진다. 따라서 본 테이블 읽기 없이 보조 인덱스 컬럼만 읽고 데이터를 반환한다.
실행 계획을 보면 이를 알 수 있다.

id	select_type	table	tpye	key	key_len	ref
1	SIMPLE	dept_emp	ref	ix_fromdate	19	const, const

위와 같이 실행 계획이 나오는데, 본 테이블을 읽는 행동을 하지 않았음을 알 수 있다. 또한 인덱스 구성 컬럼 중 어느 컬럼까지 사용했는지를 바이트 수를 보여주는 key_len이 19임이 보인다. 이는 from_date가 DATE 타입임으로 3 바이트, dept_no 가 CHAR(4)로 16바이트 즉 정확하게 두 개의 컬럼을 이용했음을 알 수 있다.

(2) 보조 인덱스의 PK로 정렬 작업도 가능해짐

explain
select * 
from dept_emp 
where from_date = '1987-02-25' 
order by dept_no 

ix_fromdate가 (from_date, dept_no, emp_no) 복합 인덱스처럼 되어 있으므로, 위의 쿼리문은 from_date의 range-scan으로 찾은 유효한 범위를 바로 반환하면 된다. 왜냐하면 이미 dept_no로 정렬되어 있기 때문이다.

3. 핵심 요약

1. 인덱스 확장은 옵티마이저가 보조 인덱스를 (보조인덱스, PK) 형태의 복합 인덱스인 것으로 인식하고 활용할 수 있도록 해주는 옵션이다.
2. 해당 옵션을 통해 커버링 인덱스가 가능해졌고, 보조 인덱스의 PK를 정렬 작업에도 활용할 수 있게 되었다.

---

부록

A. 모르는 단어 정리

B. 참고 문서

• MySQL document 10.3.10 Use Of Index Extension