일단 리프노드에서 시작 지점을 찾기만 하면 그 이후로는 이미 정렬되어있는 리프노드를 따라 쭉 읽기만 하면 된다.
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';
