MySQL 옵티마이저와 힌트

옵티마이저

쿼리를 실행하기전 어떻게 실행할 것인지, 최적의 실행 계획을 만들기 위한 논리를 만드는 역할

 

쿼리 실행 절차

1. 사용자의 SQL 문장를 MySQL 서버가 이해할 수 있는 수준으로 분리한다.

2. 분리된 파스 트리 정보를 보고 어떤 테이블, 인덱스를 읽을 것인지 확인한다.

3. 결정된 테이블 읽기 순서나 대상 인덱스를 사용하여 데이터를 읽는다.

 

- 파스 트리 정보으로부터 결정하는 것

불필요한 조건 제거, 복잡한 연산 단순화, 읽는 테이블 순서 결정, 인덱스 통계 정보를 이용하여 인덱스 결정, 레코드를 임시 테이블을 만들어 다시 가공해야하는 지 결정 등.

 

기본 데이터 처리

- 풀 테이블 스캔

인덱스를 사용하지 않고 전체 데이터를 처음부터 끝까지 읽어 요청된 작업을 처리하는 것

1. 레코드 건 수가 작아 인덱스 읽기가 불필요할 때

2. WHERE 또는 ON 절의 비교 컬럼에 인덱스가 없는 경우

3. 인덱스 레인지 스캔을 사용한다 하더라도 옵티마이저가 읽어야하는 레코드 건 수가 너무 많다고 판단할 때

 

- 풀 인덱스 스캔

풀 테이블 스캔과 같이 쿼리 요청을 처리하기 위해 전체 레코드를 읽어야 하나 전체 인덱스를 읽는 것이 전체 테이블 레코드를 읽는 것보다 메모리 읽기 비용을 고려했을 때 최적인 경우

 

ex) 전체 레코드 수를 가져오는 쿼리 -> 레코드 내용이 필요없으므로 인덱스를 읽어 그 전체 수를 반환하는 것이 효과적

SELECT COUNT(*) FROM employees;

 

- 병렬 처리

풀 스캔에서 병렬 처리를 이용하면 더 빨리 처리될 수 있다.

innodb_parallel_read_threads 변수로 스레드 수를 조절한다. 

SET SESSION innodb_parallel_read_threads=1;

 

- ORDER BY 처리 (Using filesort)

인덱스를 사용하지 않는 컬럼을 ORDER BY 기준으로 할 때 Filesort라는 전략을 이용하여 정렬한다.

 

다음은 인덱스를 만들기가 어려운 상황이다.

1. 정렬 기준이 너무 많음

2. GROUP BY 혹은 DISTINCT를 이용

3. UNION 결과 혹은 임시 테이블의 결과를 정렬해야함

4. 랜덤하게 결과 레코드를 가져옴

 

인덱스 혹은 Filesort를 이용하여 정렬 처리했을 때 장단점

	장점	단점
인덱스	DML 수행시 인덱스를 이용하는 경우 매우 빠름	인덱스를 위한 추가 메모리 필요 키 삽입, 삭제, 변경에 대한 추가 조정 시간 필요
Filesort	인덱스의 단점이 없음 정렬 레코드 수가 적으면 충분함.	정렬 레코드 건 수가 많아지면 오래걸림.

- 소트 버퍼

정렬을 위해 추가적으로 사용해야하는 메모리 공간. sort_buffer_size라는 시스템 변수로 설정가능.

정렬 레코드 건 수가 많아져서 sort_buffer_size보다 큰 경우 정렬 일부를 디스크에 임시에 저장하거나 다시 메모리에 여러 정렬 결과를 합치는 merge 과정이 이루어질 수 있음 (디스크 I/O).

 

- 정렬 알고리즘

어떤 정렬 방식을 사용하는 지 확인하는 쿼리

SET OPTIMIZER_TRACE='enabled=on', END_MARKERS_IN_JSON=on;
SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;

SELECT * FROM employees ORDER BY last_name LIMIT 10000, 1;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

 

정렬 방식은 싱글 패스와 투 패스 방식으로 나뉜다.

1) 싱글 패스

<sort_key, rowid> :정렬 키와 레코드 rowid로만 가져와서 정렬</sort_key,>

 

소트 버퍼에 SELECT 대상 컬럼을 모두 담아서 정렬을 하는 방식

SELECT emp_no, first_name, last_name FROM employees ORDER BY first_name;

emp_no, first_name, last_name 컬럼으로 이루어진 레코드를 모두 소트 버퍼에 올리고 정렬을 수행하여 결과를 머지하거나 하는 등의 절차를 거쳐 사용자에게 결과를 반환

 

2) 투 패스

<sort_key, additional_fields> 정렬키와 레코드 전체를 가져와 정렬. 레코드 컬럼은 고정된 사이즈로 메모리 저장.

<sort_key, packed_additional_fields> 정렬키와 레코드 전체를 가져와 정렬. 레코드 컬럼은 가변 사이즈로 메모리 저장.

 

MySQL 8.0 버전부터 도입된 방식

프라이머리 키 emp_no와 정렬 기준 키 first_name 만을 가지고와서 소트 버퍼에 담고 정렬을 수행한 후 결과 레코드에서 프라이머리 키로 last_name을 가져와서 사용자에게 반환

더 적은 소트 버퍼 메모리를 사용하므로 더 많은 레코드 정렬이 가능함.

 

- 정렬 처리 방법

정렬 처리 방법	실행 계획의 'Extra' 컬럼
인덱스 이용	표기 없음
조인에서 드라이빙 테이블만 정렬	"Using filesort"
조인에서 조인 결과를 임시 테이블로 저장 후 정렬	"Using temporary; Using filesort"

 

조인이 포함된 쿼리에서 정렬 처리 방식

1. 드라이빙 테이블 (먼저 오는 테이블)에서 정렬을 수행한 후 조인을 처리

2. 전체 레코드에 대해 조인을 먼저 처리한 이후 정렬을 수행

1의 경우가 2보다 대체적으로 더 빠르다.

 

1) 인덱스를 이용한 정렬

SELECT * FROM Employees e, Salaries s
WHERE s.emp_no = e.emp_no
AND e.emp_no BETWEEN 100002 AND 100020
ORDER BY e.emp_no;

emp_no는 인덱스가 생성되어 있으므로 MySQL 엔진은 정렬을 위해 별도의 작업을 수행하지 않음 (이미 정렬되어 있으므로). emp_no를 차례대로 읽으며 인덱스 레인지 스캔을 통해 일치하는 레코드를 Salaries 테이블에서 조인함.

2) 조인의 드라이빙 테이블만 정렬

SELECT * FROM Employees e, Salaries s
WHERE s.emp_no = e.emp_no
AND e.emp_no BETWEEN 100002 AND 100020
ORDER BY e.last_name;

옵티마이저는 emp_no를 보고 BETWEEN  AND 를 처리하여 레코드 건 수를 줄이고 드리븐 테이블 Sararies에 emp_no 인덱스가 있으므로 Employees를 드라이빙 테이블로 결정한다.

 

처리 절차

1. BETWEEN AND를 이용하여 인덱스 레인지 스캔 및 레코드 건 수 줄이기

2. last_name으로 정렬 (filesort)
3. 정렬 결과 레코드를 읽어 드리븐 테이블에 조인

EXPLAIN 확인

처리 절차대로 수행한 것을 확인 가능하다. last_name은 인덱스가 없으므로 filesort를 이용한다.

 

3) 임시 테이블을 이용한 정렬

SELECT * FROM Employees e, Salaries s
WHERE s.emp_no = e.emp_no
AND e.emp_no BETWEEN 100002 AND 100020
ORDER BY s.salary;

위 쿼리는 s.salary라는 컬럼으로 정렬되어야 한다. 2)와 마찬가지로 Employees가 드라이빙 테이블, Salaries가 드리븐 테이블로 선택되지만 정렬 기준 컬럼이 드리븐 테이블에 있기 때문에 항상 조인된 결과에서 최종 정렬을 수행할 수 있다.

 

처리 절차

1. BETWEEN AND (Using where)

2. JOIN : 임시 테이블을 만든다. (Using temporary)

3. 정렬 수행 (Using filesort)

 

EXPLAIN 확인

 

- 정렬 처리 방법 성능 비교

웹 서비스에서 자주 사용하는 LIMIT은 ORDER BY나 GROUP BY가 같이 사용될 경우 WHERE 절을 통한 결과에 LIMIT 건수 만큼 먼저 처리하지 못하게 되는 단점이 있다.

 

1) 스트리밍 방식

ORDER BY나 GROUP BY가 없다면 WHERE 절의 검색 결과를 읽는 순서대로 LIMIT 만큼만 즉각 가져와 바로 사용자에게 반환하면 된다.

 

2) 버퍼링 방식

ORDER BY나 GROUP BY를 사용한다면 중간 결과를 이용하여 LIMIT을 처리해야하므로 중간 결과를 버퍼링하는 수 밖에 없다.

대신 인덱스가 있는 컬럼을 ORDER BY 기준으로 한다면 이미 정렬이 되어 있으므로 버퍼링 없이 스트리밍 방식으로 바로 반환 가능하다.

 

ORDER BY와 LIMIT이 포함된 쿼리의 읽기 및 조인 횟수

SELECT *
FROM tb_test1 t1, tb_test2 t2
WHERE t1.col1 = t2.col1
ORDER BY t1.col2
LIMIT 10;

tb_test1은 100개의 레코드 tb_test2는 1000개의 레코드가 있다고 가정. 조인을 할 경우 tb_test1 레코드 하나당 tb_test2 레코드 10개가 조인됨.

 

tb_test1이 드라이빙 테이블

	읽어야할 건수	조인 횟수	정렬해야할 건수
인덱스 사용	tb_test1 : 1건 tb_test2 : 10건	1번 (tb_test1 레코드 하나에서 tb_test2으로부터 10개의 건수를 가져올 수 있음)	0건 (인덱스 사용으로)
조인의 드라이빙 테이블만 정렬	tb_test1 : 1건 tb_test2 : 10건	1번 (tb_test1 레코드 하나에서 tb_test2으로부터 10개의 건수를 가져올 수 있음)	100건 (tb_test1 레코드 수만큼 정렬)
임시 테이블 이용후 정렬	tb_test1 : 100건 tb_test2 : 1000건	100번 (tb_test1 레코드 수만큼 조인 필요)	1000건 전체 조인되어 만들어진 임시 테이블의 레코드 수만큼 정렬

 

tb_test2이 드라이빙 테이블

	읽어야할 건수	조인 횟수	정렬해야할 건수
인덱스 사용	tb_test1 : 10건 tb_test2 : 10건	10번 (최대 tb_test1을 10건을 읽어야함)	0건 (인덱스 사용으로)
조인의 드라이빙 테이블만 정렬	tb_test1 : 10건 tb_test2 : 1000건	10번 (최대 tb_test1을 10건을 읽어야함)	1000건 (tb_test2 레코드 수만큼 정렬)
임시 테이블 이용후 정렬	tb_test1 : 100건 tb_test2 : 1000건	1000번 (tb_test2 레코드 수만큼 조인 필요)	1000건 전체 조인되어 만들어진 임시 테이블의 레코드 수만큼 정렬

 

GROUP BY 처리

GROUP BY 조건 컬럼도 인덱스를 사용할 수 있는 경우와 없는 경우가 있다.

 

1) 인덱스 스캔을 이용

인덱스를 차례로 읽어 그루핑 작업을 수행하여 결과를 반환, 이미 정렬된 데이터를 읽어드리는 작업.

 

2) 루스 인덱스 스캔

EXPLAIN SELECT emp_no 
	FROM salaries
	WHERE from_date ='1985-03-01'
	GROUP BY emp_no;

Using index for group by가 나타나는 방식. salaries 테이블은 (emp_no, from_date) 인덱스가 있어 WHERE의 경우 인덱스를 사용할 수 없지만 type을 보면 인덱스 레인지 스캔을 사용한 것으로 볼 수 있다.

 

루스 인덱스 스캔 방식은 (emp_no, from_date) 인덱스를 순서대로 읽으면서 유니크한 emp_no 에 대하여 from_date 조건을 일치하는 레코드를 가져온다. 따라서 WHERE emp_no = ... AND from_date = '1985-03-01' 으로 가져오는 것과 유사하다.

 

3) 임시 테이블을 사용

EXPLAIN SELECT e.last_name, AVG(s.salary)
    FROM employees e, salaries s
    WHERE s.emp_no=e.emp_no
    GROUP BY e.last_name;

GROUP BY 절에 온 last_name 컬럼은 인덱스를 전혀 사용할 수 없기 때문에 "Using temporary" 결과로 임시 테이블을 만들어낸다는 것을 알 수 있다.

 

DISTINCT 처리

DISTINCT도 인덱스를 사용하지 못하는 경우에 임시 테이블을 만들어낸다. 하지만 EXPLAIN에서 Using temporary를 출력하지는 않는다.

 

- SELECT DISTINCT

SELECT DISTINCT first_name, last_name FROM employees;

위 쿼리는 (first_name, last_name) 조합이 유니크한 것을 가져온다. first_name만 유니크하게 만드는 것으로 착각하기 쉽다.

 

- 집합함수와 함께 사용하는 DISTINCT

EXPLAIN SELECT COUNT(DISTINCT s.salary)
FROM employees e, salaries s
WHERE e.emp_no=s.emp_no
AND e.emp_no BETWEEN 100001 AND 100100;

EXPLAIN에서 Using temporary를 출력하지는 않지만 결과를 처리하기 위해 최종 조인 테이블에서 salary 컬럼만 유니크하게 가져오는 임시 테이블을 만들어낸다. 이 때 salary 컬럼에 대해 인덱스를 생성하므로 매우 느릴 수 있다.

EXPLAIN SELECT COUNT(DISTINCT s.salary), COUNT(DISTINCT e.last_name)
FROM employees e, salaries s
WHERE e.emp_no=s.emp_no
AND e.emp_no BETWEEN 100001 AND 100100;

위 쿼리는 last_name에 대해 임시 테이블을 또한 만들어내므로 최종적으로 두 개의 임시 테이블을 만들어낸다.

 

내부 임시 테이블 활용

MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑을 할 때 내부 임시 테이블을 사용한다. 이는 "CREATE TEMPORARY TABLE" 명령으로 만든 임시 테이블과는 다르다. MySQL 엔진이 만들어내는 내부 임시 테이블은 쿼리 처리 완료 이후 삭제된다.

 

- 임시 테이블이 필요한 쿼리

 

1. ORDER BY와 GROUP BY에 명시된 컬럼이 다른 쿼리

2. ORDER BY와 GROUP BY에 명시된 컬럼이 조인의 순서상 첫 번째 테이블(드라이빙 테이블)이 아닌 쿼리

3. DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리

4. UNION이나 UNION DISTINCT가 사용된 쿼리 (select_type 컬럼이 UNION RESULT 인 경우)

4. 쿼리의 실행계획에서 select_type이 DERIVED인 경우

 

일반적으로 실행 계획에서 임시 테이블을 사용하는 경우 Extra 컬럼에 Using temporary가 나타난다.

 

고급 최적화

옵티마이저 스위치 옵션

optimizer_switch 시스템 변수에 묶음으로 설정하는 변수들이 있다.

SET SESSION optimizer_switch='index_condition_pushdown=on,index_merge=on...';

참고: https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html

MySQL :: MySQL 8.0 Reference Manual :: 8.9.2 Switchable Optimizations

 

- 인덱스 컨디션 푸시다운 (index_condition_pushdown)

ALTER TABLE employees add index ix_lastname_firstname (last_name, first_name);

EXPLAIN SELECT * FROM employees WHERE last_name='Action' AND first_name LIKE '%sal';

인덱스 레인지 스캔을 사용할 수 없는 first_name을 스토리지 엔진이 같이 인덱스로 고려하여 최적화하게끔 하는 옵션

 

- 인덱스 확장(use_index_extensions)

InnoDB 스토리지 엔진을 사용하는 테이블에서 세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 건지 여부

CREATE TABLE dept_emp(
emp_no INT NOT NULL,
dept_no CHAR(4) NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
PRIMARY KEY (dept_no, emp_no),
KEY ix_fromdate (from_date)
) ENGINE = innoDB;

dept_emp 테이블은 (dept_no, emp_no) 프라이머리 키와 from_date에 대한 세컨더리 인덱스를 가진다. 이 때, from_date 인덱스는 세컨더리 인덱스로 (dept_no, emp_no) 정보를 가지고 있으므로 (from_date, dept_no, emp_no) 인덱스를 사용한 효과를 가질 수 있다.

 

- 인덱스 머지(index_merge)

하나의 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리를 처리하는 옵션

 

- index_merge_intersection : 각각 하나로 생성된 두 개의 인덱스를 WHERE ... AND ... 에 같이 사용하는 것

- index_merge_union : 각각 하나로 생성된 두 개의 인덱스를 WHERE ... ON ... 에 같이 사용하는 것

- index_merge_sort_union : 인덱스 머지 최적화에서 두 결과 집합의 중복을 제거하기위해 정렬된 결과가 필요할 때

 

- 세미 조인(semijoin)
실제 조인을 수행하지 않고 다른 테이블에서 조건에 일치하는 레코드가 있는지 없는지만 체크하는 쿼리를 세미 조인

SELECT * FROM ... IN (SELECT 서브 쿼리) 형태

 

세미 조인관련 최적화 옵션 

- 테이블 풀-아웃(Table Pull-Out)

세미 조인의 서브 쿼리에 사용된 테이블을 바깥 쿼리로 끄집어내어 조인 쿼리로 재작성하는 옵션

 

- 퍼스트 매치(firstmatch)

IN 형태의 세미 조인을 EXISTS 전략으로 튜닝하는 옵션

 

- 루스 스캔(loosescan)
서브 쿼리 처리를 루스 인덱스 스캔으로 처리가능할 때 최적화 옵션

 

- 구체화(materialization)

세미 조인에 사용된 서브 쿼리를 통째로 구체화하여 쿼리를 최적화 옵션

 

- 중복 제거(duplicated weed-out)

세미 조인 쿼리를 일반적인 INNER JOIN 쿼리로 바꿔서 실행하고 중복된 레코드를 제거하는 최적화 옵션

 

- 컨디션 팬아웃(condition_fanout_filter)

레코드가 더 적은 테이블부터 조인을 실행하는 옵션

 

- 파생 테이블 머지(derived_merge)

EXPLAIN SELECT * FROM (SELECT * FROM employees WHERE first_name='Matt') derived_table
        WHERE derived_table.hire_date='1986-04-03';

FROM 절에서 서브 쿼리 결과의 임시 테이블을 Derived 테이블 이라고 한다. 임시 테이블이므로 테이블을 생성하는 오버헤드가 있다.

서브 쿼리를 바깥으로 꺼내어 두 개의 WHERE first_name='' AND hired_date=''로 다시 작성하여 최적화 할 수 있다.

 

- 인비지블 인덱스(invisible index)
INVISIBLE로 설정된 인덱스를 사용할 수 있게 하는 옵션이다.

 

- 스킵 스캔(skip scan)
인덱스가 (A,B,C) 컬럼에 대해 생성된 경우 (A), (A,B)에 대해서는 인덱스를 사용할 수 있지만 (B,C)에 대해서는 사용할 수 없다. 이를 가능하게하는 최적화 옵션이다.

 

- 해시 조인(hash join)

인덱스를 사용하여 조인을 수행할 수 없을 때 해시 조인을 사용한다. 해시 조인은 두 단계로 이루어진다.

1) 빌드 단계 Build phase

조인 대상 테이블에서 레코드 수가 적은 테이블 선택해 해시 테이블로 만든다.

2) 프로브 단계 Probe phase

나머지 테이블의 레코드를 읽어서 해시 테이블의 일치 레코드를 찾는 과정을 의미한다.

 

- 인덱스 정렬 선호(prefer_ordering_index)

ORDER BY 정렬시 어떤 인덱스를 어떻게 먼저 사용하냐에 따라 최적화하는 옵션

 

쿼리 힌트

 

인덱스 힌트

- STRAIGHT JOIN

EXPLAIN SELECT * FROM employees e, dept_emp de, departments d
WHERE e.emp_no=de.emp_no AND d.dept_no=de.dept_no;

옵티마이저는 레코드 건 수가 적은 것 부터 드라이빙 테이블이 되도록 조인 순서를 결정한다.

레코드 수가 적은 deparments -> dept_emp -> employees 순으로 조인이 되도록 최적화 하였다.

EXPLAIN SELECT /*! STRAIGHT_JOIN */ * FROM employees e, dept_emp de, departments d
WHERE e.emp_no=de.emp_no AND d.dept_no=de.dept_no;

해당 STRAIGHT_JOIN 힌트를 사용하게되면 항상 명시된 테이블 순서대로 (e->de->d) 조인이 이루어지도록 강제한다.

 

- USE INDEX / FORCE INDEX/ IGNORE INDEX

3~4개 이상의 컬럼을 포함하는 비슷한 인덱스가 여러개 존재하는 경우 옵티마이저가 실수 할 수 있는 경우가 생긴다.

 

USE INDEX: 가장 자주 사용되는 인덱스 힌트로, 특정 테이블의 인덱스를 사용하도록 권장한다. 권장의 의미로 항상 사용하는 것은 아닐 수 있다.

FORCE INDEX: USE INDEX 보다 더 강제성이 큰 힌트이다.

IGNORE INDEX: 인덱스 사용보다 풀 테이블 스캔을 유도하고 싶을 때 사용할 수 있는 힌트이다.

 

그 밖의 JOIN/ORDER BY/GROUP BY를 위한 인덱스 유도

USE INDEX FOR JOIN / USE INDEX FOR ORDER BY / USE INDEX FOR GROUP BY

 

예시)

SELECT * FROM employees FROM emp_no=100001;
SELECT * FROM employees FORCE INDEX(primary) WHERE emp_no=100001;
SELECT * FROM employees USE INDEX(primary) WHERE emp_no=100001;

SELECT * FROM employees INGRNOE INDEX(primary) WHERE emp_no=100001;
SELECT * FROM employees FORCE INEDX(ix_firstname) WHERE emp_no=100001;

 

- SQL_CALC_FOUND_ROWS
LIMIT과 같이 전체 레코드 결과가 필요하지 않을 때 검색 프로세스가 중간에 끝날 수 있다. 이를 멈추지 않고 조건에 맞는 모든 레코드를 검색하도록 강제하는 힌트이다.

SELECT SQL_CALC_FOUND_ROWS * FROM employees WHERE first_name='Georgi' LIMIT 0,20;
SELECT FOUND_ROWS() AS total_record_count;

 

옵티마이저 힌트

옵티마이저의 행동은 optimizer_switch로 설정할 수 있지만 statement 단위로 힌트를 설정하여 optimizer 행동을 변경할 수 있다. 

 

인덱스용/테이블용/ 쿼리블록/전체 쿼리 블록등 다양한 종류의 옵티마이저 힌트가 존재한다.

 

참고 : https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html

MySQL :: MySQL 8.0 Reference Manual :: 8.9.3 Optimizer Hints