옵티마이저, 힌트

해당 게시물은 'Real MySQL 8.0'라는 책을 참고해서 작성했습니다.

👣 개요

특정 쿼리를 실행하는 방법은 매우 다양한 방법이 있을 수 있다. 
테이블 자체를 풀 스캔 하던지 인덱스를 이용해서 검색하던지 등등의 방법이 많다.
하지만 최적의 방법은 존재하기 때문에 해당 방법을 찾는 장치가 필요하다.
그러한 역할을 옵티마이저가 담당한다.

👣 용어 정리

실행 계획
쿼리를 이용해서 데이터를 어찌 가져올까에 대한 계획.

옵티마이저
실행 계획 중 가장 최적화된 것을 찾는 주체.

👣 쿼리 실행 절차

쿼리가 실행되는 절차는 3 단계로 나뉜다.

1. SQL 문장을 잘게 쪼게서 서버가 이해할 수 있는 수준으로 분리(Parse Tree)한다.
2. Parse Tree를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용할지를 선택한다.
3. 결정된 읽기 순서를 이용해서 스토리지 엔진으로부터 데이터를 가져온다.

👣 옵티마이저 종류

옵티마이저는 2가지로 크게 나눌 수 있다.

1. CBO[Cost-based Optimizer] - 비용 기반 최적화
각 단위 작업의 비용 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획을 세우는 방법.
대부분의 DBMS에서 사용하는 방법.

2. RBO[Rule-based Optimizer] - 규칙 기반 최적화 방법
미리 DBMS에 규정해놓은 우선순위를 이용해서 실행 계획을 수립하는 방법

👣 기본 데이터 처리

👣 Full Table Scan & Full Index Scan

대부분의 경우 Full Index Scan을 먼저 고려할 확률이 크다.
왜냐하면 Index가 실제 Table보다 크기가 더 작기 때문에
메모리를 로드하는 시간이 더 오래 걸려 연산시간이 더 걸린다.
하지만 특수한 몇몇 경우엔 Full Table Scan이 더 효율적이라서 해당 방법을 사용한다.

• Table의 레코드 건수가 너무 작아서 Full Table Scan이 더 빠른 경우.
• Where 절이나 On 절에 인덱스를 이용할 수 있는 조건이 없는 경우.
• 옵티마이저 판단하에 조건에 부합하는 레코드 건수가 너무 많은 경우.

예를 들면 다음과 같다.

아래 경우는 2 번째 경우와 부합해서 Full Table Scan을 실행.

SELECT * FROM employees;

하지만, 아래 방법은 인덱스의 용량이 실제 테이블의 용량보다 작은 경우이므로 Full Index Scan을 함.

SELECT COUNT(*) FROM employees;

👣 Order By 처리 

정렬 쿼리에 대한 대처는 2 가지로 추릴 수 있다.

인덱스 이용
이미 정렬된 인덱스를 그대로 사용하는 방법. 
굉장히 빠르게 처리 가능.
하지만 모든 정렬에 대해 인덱스를 사용할 수는 없기 때문에 적용이 어렵다.

Filesort 이용
직접 정렬을 수행해서 결과를 내놓는다.
당연히 응답 속도가 느리고 자원을 많이 잡아 먹는다.
하지만 정렬할 레코드가 많지 않으면 충분히 빠른 속도를 가지고
모든 정렬에 대처 가능하다.

👣 소트 버퍼

정렬을 수행하기 위한 메모리 공간.
버퍼 크기 만큼 로드하고 정렬하고 디스크에 임시로 기록한다.
때문에 너무 작은 소트 버퍼는 빈법한 I/O를 유발한다.

👣 정렬 알고리즘

레코드 정렬 시, 정렬 모드를 2 가지로 나눌 수 있다.1. 싱글 패스
레코드 전체를 소트 버퍼에 담음.
주된 정렬 방식. 성능이 좋지만 많은 소트 버퍼를 요구함.2. 투 패스
정렬 기준 칼럼만 소트 버퍼에 담음.
우선 PK 키 값만을 소트 버퍼에 가져오고 정렬된 순서대로
다시 테이블을 읽어 SELECT할 칼럼을 가져오는 방식.

👣 정렬 처리 방법

쿼리에 ORDER BY 가 포함되면 3 가지 처리 방법 중 하나를 선택해서 정렬한다.

아래로 갈수록 처리속도가 느리다.

정렬 처리 방법	실행 계획의 Extra 칼럼 내용
인덱스를 사용한 정렬	별도 표기 없음
조인에서 드라이빙 테이블만 정렬	"Using filesort" 메시지가표시됨
조인에서 조인 결과를 임시 테이블로 저장 후 정렬	"Using temporary; Using filesort" 메시지가 표시됨.

👣 Group By 처리 

마찬가지로 인덱스를 이용하는 방법과 아닌 방법이 있다.

인덱스 스캔을 이용하는 Group By
이미 정렬되어 있고 그룹으로 엮여 있는 상태라서 별도의 임시 테이블이 필요하지 않다.

루스 인덱스 스캔을 이용하는 Group By
불필요한 인덱스의 레코드는 건너뛰는 방식.

임시 테이블을 사용하는 Group By
인덱스를 전형 사용하지 못할 때 사용하는 방식
임시 테이블을 사용"하는 것은 쿼리 실행 중에 임시 결과를 저장하고 처리하기 위해 임시 테이블을 생성하거나 사용한다는 것을 의미한다. 임시 테이블은 결과 집합을 저장하고 조작하기 위한 일시적인 저장소로 사용된다.

👣 Distinct 처리 

해당 처리는 2가지 경우로 나눌 수 있다.
1. 집계 함수와 같이 사용하는 Distinct
2. 집계 함수 없이 사용되는 Distinct
위와 같이 2가지로 나뉘는 경우는 Distinct가 영향을 미치는 범위가 다르기 때문이다.

집계 함수 없이 사용되는 Distinct

select dictinct first_name, last_name from employees;

위와 같이 그저 튜플 전체에 수행되는 쿼리는 Order By문이 없다면 정렬을 전혀 사용하지 않는다.

집계 함수와 같이 사용하는 Distinct

select count(distinct s.salary) from employees e, salaries s;

위와 같은 경우, count 함수를 위해 조인을 한 후에 
임시 테이블을 distinct 당 1개씩 만들어서 중복 처리를 한다.
물론 인덱스가 존재한다면 굳이 임시 테이블을 사용하지 않고 
인덱스 풀스캔으로 중복처리를 할 수 있다.

👣 내부 임시 테이블 활용

MySQL 엔진은 스토리지 엔진으로부터 받아온 레코드들을 조작할 때,
인덱스를 사용할 수 없는 상태라면 내부적으로 임시 테이블을 만들어서 계산한다.

임시 테이블은 처음엔 메모리 위에서만 조작되다가 용량이 커지면
디스크로 옮겨 작업한다.

당연하지만 계산이 모두 끝나면 자동으로 삭제된다.

MySQL 8.0부터는 메모리 임시 테이블은 TempTable라는 스토리지 엔진을 사용하고
디스크 임시 테이블은 InnoDB라는 스토리지를 사용한다.

 

👣 고급 최적화

MySQL에서 옵티마이저가 실행 계획을 수립할 때,
그동안의 통계 정보와 옵티마이저 옵션을 결합해서 최적의 실행 계획을 수립한다.

통계 정보는 자체적으로 조절하는 경우라고 해도
옵티마이저 옵션은 사용자에 의해 커스터마이징 될 수 있고
MySQL은 이것을 조절할 수 있는 옵티마이저 스위치 옵션을 제공한다.

👣 옵티마이저 스위치 옵션

해당 스위치는 'on', 'off', 'default'와 같은 3가지 중 하나로 설정할 수 있다.
해당 옵션들은 여러가지가 있지만 이러한 것들이 존재한다.

옵션들이 너무 많아 각각을 모두 정리하기는 어렵고 필요에 따라 찾아서 사용하면 된다.

• MRR과 배치 키 액세스
• Block Nested Loop Join
• Index Condition Pushdown
• 인덱스 확장
• 인덱스 머지 - 교집합
• 인덱스 머지 - 합집합
• 인덱스 머지 - 정렬 후 합집합
• 세미 조인
• 테이블 풀-아웃
• 패스트 매치
• 루스 스캔
• 구체화
• 중복 제거
• 컨디션 팬아웃
• 파생 테이블 머지
• 인비저블 인덱스
• 스킵 스캔
• 해시 조인
• 인덱스 정렬 선호

 

👣 쿼리 힌트

통계 정보와 옵티마이저의 다양한 최적화 방법을 이용한다고 한들
개발자가 만든 Applicaiton에 대해 최적화된 실행 계획에 부합하지 않아
불필요한 Cost를 소모할 수도 있다.
때문에 개발자는 자신의 앱 환경에 맞는 실행 계획을 MySQL에게 제공할 수 있다.
그것을 옵티마이저 힌트라고 한다.

힌트는 2가지가 존재한다.

1. 인덱스 힌트
2. 옵티마이저 힌트

👣 인덱스 힌트

인덱스 힌트는 MySQL 5.6 버전 이전에 사용되던 힌트로서
ANSI-SQL 표준 문법을 준수하지 못하는 특징을 가지고 있다.
때문에 되도록 옵티마이저 힌트를 사용하는 것이 좋다.

STRAIGHT_JOIN
여러 개의 테이블이 조인될 경우 조인 순서를 고정시키는 힌트.

select /*! STRAIGHT_JOIN */ e.first_name, e.last_name, d.dept_name
from employees e, dept_emp de, departments d
where e.emp_no=de.emp_no and d.dept_no=de.dept_no;

USE INDEX, FORCE INDEX, IGNORE INDEX
해당 힌트는 옵티마이저가 사용하려는 인덱스를
개발자가 원하는 인덱스로 유도하는 것을 의미한다.
해당 힌트는 사용하고 싶은 테이블 뒤에 명시해야 한다.
"{테이블명} USE INDEX({인덱스명})" 과 같은 형식으로 사용된다.
오해할 수 있는 것이 강제로 선택을 하도록 하는 것이 아니기 때문에
적용을 해도 해당 인덱스를 사용하지 않을 수도 있다.

select * from employees USE INDEX(primary) where emp_no=10001;

SQL_CALC_FOUND_ROWS
보통 limit에 명시된 수만큼 레코드를 탐색하면 더이상 탐색하지 않는다.
하지만 해당 힌트를 사용하면 의도적으로 limit을 무시하고
끝까지 탐색해 레코드가 총 몇 개가 있는지 탐색한다.
이것을 통해 페이징 처리 시, 전체 페이지 갯수를 알 수 있다.

하지만 해당 힌트는 성능을 위한 쿼리가 아니다.
어차피 쿼리는 2번 보낼 뿐만 아니라 실제 레코드를 이용해서 갯수를 찾는 것보다
차라리 갯수만 찾는 쿼리를 따로 보내 인덱스로만 갯수를 찾는 것이 더 빠르다.
때문에 되도록 해당 힌트는 사용하지 말아야 한다.

select SQL_CALC_FOUND_ROWS 
* from employees 
where first_name='Georgi' limit 0, 20;

select found_rows() as total_record_count;

👣 옵티마이저 힌트

4가지 종류가 존재한다.

1. 인덱스: 특정 인덱스의 이름을 사용하는 힌트
2. 테이블: 특정 테이블의 이름을 사용하는 힌트
3. 쿼리 블록: 특정 쿼리에 사용하는 힌트.
4. 글로벌 : 모든 쿼리에 영향을 미치는 힌트.

해당 힌트는 종류가 너무 많아 이것도 그때그때마다 찾는 형식으로 공부해야 한다.

select /*+ INDEX(exployees ix_firstname) */ e.name
from employees e
where first_name = 'Matt';

• MAX_EXECUTION_TIME
• SET_VAR
• SEMIJOIN & NO_SEMIJOIN
• SUBQUERY
• BNL & NO_BNL & HASHJOIN & NO_HASHJOIN
• JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX
• MERGE & NO_MERGE
• INDEX_MERGE & NO_INDEX_MERGE
• NO_ICP
• SKIP_SCAN & NO_SKIP_SCAN
• INDEX & NO_INDEX