[Real MySQL] 인덱스 (3) - B-Tree 인덱스를 통한 스캔 방식들

인덱스 테이블을 활용한 데이터 스캔 방식을 알아보자

 

• 인덱스 레인지 스캔
• 인덱스 풀 스캔
• 루즈 인덱스 스캔
• 인덱스 스킵 스캔(v8.0 이상)

 

1. 인덱스 레인지 스캔

아래 쿼리와 같이 검색해야 할 범위가 결정되었을 때 사용되어지는 방식

일단 리프노드에서 시작 지점을 찾기만 하면 그 이후로는 이미 정렬되어있는 리프노드를 따라 쭉 읽기만 하면 된다.

SELECT * FROM employees WHERE first_name BETWEEN 'Ebbe' AND 'Gad'

 

 

(1) 리프노드까지 들어가 시작지점을 찾는다.

(2) 시작지점부터 리프노드만 순차적으로 읽는다.(이때 정렬된 결과가 반환 됨)

(3) 리프노드에서 실제 데이터 파일 읽어올때는 랜덤 I/O가 개별적으로 발생함.😓

 

(1)번과 (2)번 과정은 얼마나 일어났는지 아래 쿼리로  확인하는것도 가능! 😮

SHOW STATUS LIKE 'Handler_%';​

Handler_read_key 가 (1) 리프노드까지 들어가 시작 지점을 찾은 횟수를 나타내고
Handler_read_next, Handler_read_prev 가 (2)번 단계에서 인덱스를 정순으로 읽었는지 역순으로 읽었는지를 나타낸다고 볼 수 있다.

커버링 인덱스
쿼리가 어떤 데이터를 필요로 하는지에 따라 (3)번의 랜덤 I/O 과정이 안 발생 할 수도있다. ⇒ 성능 UP!

 

2. 인덱스 풀 스캔

실제 데이터 레코드 읽을 필요없이 인덱스 테이블만으로 충분히 풀 스캔이 가능한 경우 😏

WHERE 절에 걸린 컬럼이 모두 인덱스 컬럼인 경우

 

3. 루즈(=루스?) 인덱스 스캔

인덱스는 정렬된 형태로 저장 되어있으므로 GROP BY 로 MAX(), MIN() 구할 때 전체 인덱스 테이블을 스캔 할 필요가 없다. 듬성듬성 띄엄띄엄 읽으면서 가장 앞, 가장 뒤 데이터만 가져오면 된다.

 

SELECT dept_no, MIN(emp_no)
FROM dept_emp
WHERE dep_no BETWEEN 'd002' AND 'd004'
GROUP BY dept_no;

 

4. 인덱스 스킵 스캔

(🆕 MySQL 8.0부터 도입된 기능)

 

다중 컬럼 인덱스가 설정된 테이블에서 인덱스를 활용하지 못하는 쿼리도 인덱스가 적용된 것 처럼 스킵을 지원해 주는 기능

 

다중 컬럼 인덱스(복합 컬럼 인덱스)는 순서가 중요하다!

// 다중 컬럼 인덱스 만들기 
ALTER TABLE employees
ADD INDEX ix_gender_birthdate (gender, birth_date);

 

이렇게 여러개의 컬럼을 복합으로 묶어서 인덱스를 지정 할 경우 위 그림 처럼 gender, birth_date 순으로 정렬이 들어가기 때문에 birth_date만 가지고 쿼리를 작성할 경우 인덱스를 사용하지 못한다.

// 인덱스를 활용하지 못하는 쿼리
// 이렇게 쿼리 칠려면 birth_date부터 시작하는 인덱스를 새로 생성해야 함.
SELECT * FROM employees WHERE birth_date >= '1965-02-01';

// 첫번째 인덱스 컬럼에 대한 조건을 지정해주어야 한다.
SELECT * FROM employees WHERE gender = 'M' AND birth_date >= '1965-02-01';

 

그런데 8.0 버전 부터는 인덱스 스킵 스캔기능을 켜면 위의 인덱스를 활용하지 못하는 쿼리도 다음과 같이 내부적으로 변환하여 실행한다.

// 인덱스를 활용하지 못하는 쿼리
// 이렇게 쿼리 칠려면 birth_date부터 시작하는 인덱스를 새로 생성해야 함.
SELECT * FROM employees WHERE birth_date >= '1965-02-01';

// but 8.0 버전 부터는 인덱스 스킵 스캔 기능을 켜면 됨.
SET optimizer_switch='skip_scan=on';

// 그러면 내부적으로 아래 2개 쿼리를 실행하는것과 비슷하게 최적화 되어 실행 됨
SELECT gender, birth_date FROM employees WHERE gender='M' AND birth_date >= '1965-02-01';
SELECT gender, birth_date FROM employees WHERE gender='F' AND birth_date >= '1965-02-01';