Real Mysql 8.0 : 옵티마이저

옵티마이저

쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장돼 있는지 통계 정보를 참조하며, 그러한 기본 데이터를 비교해 최적의 실행 계획을 수립한다.

우리들은 실행 계획의 이해해야 실행 계획의 불합리한 부분, 더 최적화된 방법으로 실행 계획을 수립하도록 유도할 수 있습니다.

쿼리 실행 절차

1. SQL 파싱
사용자로부터 요청된 SQL문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리해서 파스 트리가 만들어짐

설명 : SQL문장이 문법적으로 잘못됐다면 이단계에서 걸러짐, MySQL서버는
SQL문장 그자체가 아니라 파스 트리를 이용해 쿼리를 실행

2. 최적화 및 실행 계획 수립
SQL의 파스 트리를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택

설명 : 첫 번째 단계의 파스트리를 참조하면서 불필요한 조건 제거, 연산 단순화, 테이블 조인이 존재할 때 순서 결정, 인덱스 통계 정보를 이용해 사용 인덱스 결정, 임시 테이블 사용 여부 결정등을 처리하는 단계이며 MySQL서버의 "옵티마이저"에서 처리

3. 데이터 수집
두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져옴

설명 : 두 번째 단계에서 수립된 실행 계획에 따라 스토리지 엔진에 레코드 읽기 요청, 서버 엔진과 스토리지 엔진인 함께 참여

옵티마이저 종류

옵티마이저는 데이터베이스 서버에서 두노외 같은 역할을 담당하며
크게 현재 대부분 DBMS가 선택하는 비용 기반 최적화(Cost-based optimizer, CBO)방법과 예전 초기 버전의 규칙 기반 최적화(Rule-based optimizer, RBO)로 크게 나눌 수 있음

규칙 기반 최적화는 테이블이나 인덱스의 통계 정보가 거의 없고 상대적으로
느린 CPU 연산으로 인한 비용 계산 과정때문에 과거에 사용
MySQL포함 현대의 RDBMS는 비용 기반 옵티마이저를 사용

기본 데이터 처리

풀 테이블 스캔, 풀 인덱스 스캔

풀 테이블 스캔 사용 조건

• 테이블 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우
• where 절이나 on절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
• 인덱스 레이지 스캔을 사용할 수 있는 쿼리라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우

select count(*) from employees

풀 테이블 스캔보다는 풀 인덱스 스캔을 할 가능성이 높은 쿼리
단순히 레코드의 건수만 필요로 하는 쿼리라면 용량이 작은 인덱스를 선택하는 것이 디스크 읽기 횟수를 줄일 수 있음
❓ clustered index의 1개 row보다 secandary index 1개 row 용량이 적어서 그런가?

select * from employees
풀 테이블 스캔을 활용하는 쿼리
secandary index table에는 레코드의 내용이 없다.

병렬 처리

MySQL 8.0부터 1개의 쿼리를 여러 스레드가 작업을 나누어서 처리가 가능(한정된 용도)
innodb_parallel_read_threads라는 시스템 변수를 이용해 병렬 처리 스레드의 수를 조정 가능

MySQL 8.0버전에서는 아무런 where 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬로 처리 가능

select count(*) from salaries

order by 처리(Using filesort)

정렬을 처리하는 방법은 index를 사용하는 방법과 Filesort라는 별도의 처리를 이용하는 방법으로 나눌 수 있음

index - 장, 단점은 index를 사용할 때 나오는 모든 것을 포함
filesort - 정렬를 쿼리 실행 시 처리함, 정렬을 할 레코드가 적다면 메모리에서 filesort가 충분히 빠르지만, 많아질수록 요청시 정렬이 진행되어서 느림

레코드 정렬을 위해서 항상 Filesort를 거쳐야 하는 것은 아니며 인덱스를 이용한 정렬을 사용할 수 있다.
하지만 모든 정렬을 인덱스를 이용하도록 튜닝하기는 불가능

• 정렬 기준이 너무 많다.
• Group by 결과 또는 Distinct같은 처리의 결과를 정렬해야하는 경우
• UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬하는 경우
• 랜덤하게 결과 레코드를 가져오는 경우

소트 버퍼

MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용
이 공간을 소트 버퍼(Sort Buffer)라고 하며 정렬이 필요한 경우에만 할당

소트 버퍼 공간은 정렬 레코드의 크기에 따라 가변적이며 쿼리 실행이 완료되면 해당 공간은 반납됩니다.

문제는 정렬해야 할 레코드가 너무 많을 때 입니다.
최대 소트 버퍼 공간보다 레코드가 크다면 디스크의 임시공간을 사용하여 소트 버퍼에서 정렬한 일부분을 적재하고 다시 또 읽어서 메모리에서 정렬하며 추가적인 I/O 요청이 생깁니다.

소트 버퍼의 크기는 설정이 가능하나 너무 크게 잡는 것은 성능적으로 오히려 마이너스이며 적절한 값 56KB - 1MB(시스템에 따라 상이)가 적절합니다.

정렬 알고리즘

싱글 패스 VS 투 패스

레코드 전체와 정렬 기준 컬럼에서 어떤 것을 소트 버퍼에 담을지에 따라 싱글 패스, 투패스 방식으로 나눌 수 있습니다.

이로 인해 레코드 전체를 담는 싱글 패스는 테이블을 한 번 읽지만 투 패스는 처음에는 정렬 키와 레코드의 로우아이디만 가져와 후에 나머지 레코드를 가져오므로 두 번 읽습니다.

"투 패스" 정렬 방식
<sort_key, rowid> : 정렬 키와 레코드의 로우 아이디만 가져와서 정렬

"싱글 패스" 정렬 방식
<sort_key, additional_fields> : 정렬 키와 레코드 전체를 가져와서 정렬, 레코드 컬럼은 고정 사이즈로 메모리 저장
<sort_key, packed_additional_fields> : 정렬 키와 레코드 전체를 가져와서 정렬, 레코드의 컬럼은 가변 사이즈로 메모리 저장

select query에서 꼭 필요한 컬러만 조회하지 않고 모든 컬럼(*)을 조회하는 경우 정렬 버퍼를 몇 배에서 몇십 배까지 비효율적으로 사용할 가능성이 큽니다.
특히 정렬이 필요한 select는 불필요한 컬럼은 조회하지 않는 것이 효율적입니다.
정렬 버퍼만의 이야기가 아니라 임시 테이블이 필요한 쿼리에도 영향을 미칩니다.

정렬 처리 방법

쿼리에 order by가 사용되면 다음 3가지 처리 방법 중 하나로 정렬이 됩니다.

아래로 갈수록 느린 방법

1. 인덱스를 사용한 정렬 -> 별도 표기 없음
2. 조인에서 드라이빙 테이블만 정렬 -> Using filesort
3. 조인에서 조인 결과를 임시 테이블로 저장 후 정렬 -> Using temporary; Uisng fileosrt

먼저 옵티마이저는 정렬 처리를 위해 인덱스를 사용할 수 있는지 검토를 합니다.
만약 인덱스를 사용할 수 없다면 where조건에 일치하는 레코드를 검색해 정렬 버퍼에 저장하면서 정렬(Filesort)을 처리합니다.

 

옵티마이저는 정렬 대상 레코드를 최소화를 위해 2가지 방법 중 하나를 선택

• 조인의 드라이빙 테이블만 정렬한 다음 조인을 수행
• 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬 수행

일반적으로 조인을 한다면 레코드 건수가 몇배로 증식합니다.
되도록이면 1번 방식을 통해 드라이빙 테이블을 먼저 정렬한 다음 조인을 수행하는 것이 효율적입니다.

인덱스를 이용한 정렬

인덱스를 이용한 정렬을 위해서는 반드시 order by에 명시된 컬럼이 제일 먼저 읽은 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고 order by의 순서대로 명시된 인덱스가 존재해야합니다.

또한 Where절에 첫 번째로 읽는 테이블의 컬럼에 대한 조건이 있다면 그 조건과 order by는 같은 인덱스를 사용할 수 있어야 합니다.

그리고 B-Tree 계열의 인덱스가 아닌 인덱스는 정렬을 사용할 수 없습니다.

여러 테이블이 조인되는 경우에는 nested-loop 방식의 조인에서만 이 방식을 사용 가능

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;

PK Index를 사용하는 상황에 명시적으로 pk 정렬을 한다고 부가적인 정렬 작업을 수행하지 않습니다.

❓order by를 명시하지 않으면 옵티마이저의 분석에 따라 정렬에 사용되는 인덱스가 변경될지도 모른다.

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

일반적으로 조인이 수행되면 결과 레코드의 건수가 몇 배로 불어나고,
레코드 하나하나의 크기도 늘어나 조인전에 미리 정렬을 하는 것이 좋습니다.

이 방법으로 정렬이 되려면 첫 번째로 읽히는 드라이빙 테이블의 컬럼만으로 order by 절을 작성해야 합니다.

select * 
from employees e, salaries s
where s.emp_no=e.emp_no
and e.emp_no between 100002 and 100010
order by e.last_name;

2가지 조건으로 인해 옵티마이저는 employees 테이블이 드라이빙 테이블로 선택할 것입니다.

• where절의 e.emp_no between 100002 and 100010은 employees 테이블의 프라이머리 키를 이용해 검색하면 작업량이 줄어듬
• 드리븐 테이블의 조인 컬럼에 emp_no이 들어감

검색은 PK(emp_no)로 레인지 스캔처리가 가능하지만 order by의 last_name은 프라이머리 키와 연관이 없으므로 인덱스를 이용한 정렬이 불가능합니다.
하지만 order by의 last_name이 employees에 포함된 컬럼이므로 드라이빙 테이블만 먼저 졍렬을 수행하고 그 결과를 드리븐 테이블과 조인합니다.

임시 테이블을 이용한 정렬

쿼리가 여러 테이블을 조인하지 않고, 하나의 테이블로부터 SELECT해서 정렬하는 경우라면 임시 테이블이 필요하지 않습니다. (예외 : 조인의 드라이빙 테이블만 정렬)
조인을 했다면 임시 테이블에서 조인한 결과를 정렬합니다.

당연하게도 정렬할 결과가 가장 많아지므로 임시 테이블을 이용한 정렬이 가장 느릴 것 입니다.

order by절에 드라이빙 테이블의 컬럼이 아닌 드리븐 컬럼이 포함되어 있다면
조인된 결과를 가지고 정렬할 수밖에 없습니다.

아래처럼 드리븐 테이블의 컬럼이 order by절에 사용되면
Using temporary; Using filesort; 키워드가 Extra column에 노출됩니다.
(아래는 옵티마이저에 의해 드라이빙 테이블이 변경된 것으로 추측)

쿼리
select * 
from m2 m2, m3 m3 
where m2.id = m3.m2_id 
order by m2.name;

실행 계획
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra                                        |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | m3    | NULL       | ALL    | m2_id         | NULL    | NULL    | NULL          |    2 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | m2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | test.m3.m2_id |    1 |   100.00 | NULL                                         |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------------------------------

정렬 처리 방법의 성능 비교

• 웹 서비스용 쿼리에서는 ORDER BY, LIMIT이 거의 필수로 사용되는 경향이 존재
• 일반적으로 LIMIT은 테이블이나 처리 결과의 일부만 가져오기 때문에 작업량을 줄이는 역할을 함
• ORDER BY, GROUP BY는 모든 컬럼이 기본적으로 사용을 하여 WHERE절을 LIMIT으로 일부만 가져오기가 불가능
• WHERE절이 인덱스를 잘 활용하도록 해도 GROUP, ORDER BY로 인해 쿼리가 느려지는 경우가 존재

쿼리에서 인덱스를 사용하지 못하는 정렬이나 그루핑 작업이 왜 느린지 2가지 방식으로 구분해 봅니다.

스트리밍 방식 vs 버퍼링 방식

스트리밍

데이터가 얼마인지에 관계없이 조건에 일치하면 레코드를 검색하면 바로바로 클라이언트에 전송, 스티리밍 방식의 쿼리는 LIMIT처럼 결과 건수를 제한하는 조건들은 쿼리의 전체 실행 시간을 상당히 줄여줍니다.
(매우 큰 테이블이라도 첫 번째 레코드는 매우 빠르게 조회)

버퍼링

ORDER BY, GROUP BY같은 처리는 스트리밍되는 것을 불가능하게 합니다.
(WHERE절의 결과를 정렬하거나 그루핑해야 하기 때문)

MySQL 서버는 스트리밍 방식으로 반환하지만 클라이언트의 JDBC 라이브러리는 버퍼링을 함, JDBC를 사용하지 않는 SQL 클라이언트 도구는 이러한 버퍼링을 하지 않기 때문에 아무리 큰 테이블이라도 빠르게 가져옴

JDBC가 자체적으로 버퍼링하는 이유는 이 방식이 전체 처리 시간이 짧고 통신 횟수가 적어 자원 소모가 줄기 때문

• ORDER BY의 3가지 처리 방법 가운데 인덱스를 사용한 정렬 방식만 스트리밍 형태의 처리, 나머지는 모두 버퍼링된 후에 정렬 (당연하게도 인덱스를 사용하지 못한 경우 모든 레코드를 디스크에서 읽고 정렬을 해야함)

• 인덱스를 사용한 정렬 방식을 사용하지 못해도 조인의 드라이빙 테이블만 정렬으로 유도해도 좋은 튜닝 방법

인덱스를 사용하지 못하고 별도로 Filesort를 거쳐야 하는 쿼리에 LIMIT이 아무런 도움이 안되는 것은 아니다. 1000건 결과에서 10건의 LIMIT 조건이 있다면 필요한 순서대로 정렬 LIMIT이 채워지면 작업을 멈추고 반환함.
하지만 MYSQL서버는 정렬 알고리즘을 퀵 소트, 힙 소트를 사용하므로 LIMIT의 값보다 더 많은 작업이 필요할 수도 있음

quick

heap

---

GROUP BY 처리

• GROUP BY도 ORDER BY와 같이 스트리밍하게 처리를 할 수 없는 작업
• ❓GROUP BY절에 사용된 조건은 인덱스를 사용해서 처리가 불가능, HAVING절 튜닝을 하려면 인덱스를 생성하거나 다른 방법이 필요
• GROUP BY 작업에서 인덱스를 사용하는 경우와 그렇지 못한 경우가 존재
  사용하는 경우 - 인덱스 스캔, 루스 인덱스 스캔 사용
  사용하지 못하는 경우 - GROUP BY작업을 임시 테이블에서 사용

인덱스 스캔을 사용(타이트 인덱스 스캔)
ORDER BY의 경우와 마찬가지로 조인의 드라이빙 테이블에 속한 컬럼만 이용해 그루핑할 때 GROUP BY칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그결과를 조인을 처리한다.
이러한 그루핑 방식은 실행 계획에서 Using index for group-by나 Using temporary, Using filesort가 사용되지 않음

루스 인덱스 스캔을 사용

• 인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 것
• 루스 인덱스 스캔을 사용하면 실행계획의 Extra컬럼에 Using index for group-by가 표시
• 단일 테이블에 대해 수행되는 GROUP BY 처리에만 사용할 수 있음, Prefix Index에는 사용 불가
• 별도의 임시 테이블이 필요없음
• 루스 인덱스 스캔이 사용될 수 있을지 판단하는 것은 WHERE, ORDER BY절의 인덱스 사용을 판단하는 것보다 어렵다.

Index Range Scan에서는 유니크한 값의 수가 많을수록 유리
Loose Index Scan에서는 유니크한 값의 수가 적을수록 유리

임시 테이블을 사용

• 인덱스를 전혀 사용하지 못하는 경우에 사용된다.
• Index를 사용하지 못한 Group by인 경우 Extra에 노출 : Using temporary
• 8.0 이전에는 Using Filesort도 함께 노출, Group By가 사용된 쿼리에 묵시적으로 정렬도 시켜줬기 때문

내부적으로 GROUP BY절의 컬럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 생성해서 중복 제거, 집합 함수 연산을 수행

조인된 결과를 한 건씩 가져와 임시 테이블에서 중복 체크를 하면서 INSERT, UPDATE를 진행
당연하게도 임시 테이블의 Index가 걸린 last_name을 기준으로 자동 정렬이 진행된다.

CREATE TEMPORARY TABEL ... (
	last_name VARCHAR(16),
    salary INT,
    UNIQUE INDEX ux_lastname (last_name)
);

8.0부터는 묵시적 정렬 수행 X, ORDER BY NULL이 필요없음

DISTINCT

• 특정 컬럼의 유니크 값만 조회하기 위해 사용
• MIN, MAX.. 같은 집합 함수와 함께 사용되는 경우와 아닌 경우의 과정은 매우 다르다.
• DISTINCT 처리가 인덱스를 사용하지 못 할때는 항상 임시 테이블이 필요(Using temporary가 노출되지는 않음)

SELECT DISTINCT ...

단순히 유니크한 레코드만 가져오고자 한다면 사용하며 GROUP BY와 동일한 방식으로 처리

두 쿼리는 동일한 방식으로 수행
SELECT DISTINCT emp_no FROM salaries;
SELECT emp_no FROM salaries GROUP BY emp_no;

 

☠️ Distinct 주의점

1. 특정 컬럼을 유니크하게 찾는 것이 아니라 레코드를 유니크하게 찾는 것
   예시 : first_name, last_name의 조합

1. DISTINCT() <- DISTINCT는 함수가 아님, ()는 의미가 없다.

집합 함수와 함께 사용된 DISTINCT

집합 함수와 함께 사용한 DISTINCT는 그 집합 함수의 인자로 전달된 컬럼값이 유니크 한것들을 가져옴

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;

위 쿼리에서 salary의 값만 저장하기 위한 임시 테이블을 생성(인덱스 사용 불가),
이때 salary 컬럼에는 유니크 인덱스가 생성되기 때문에 건수가 많아지면 상당히 느려짐

아래 쿼리에서는 2개 모두 인덱스를 사용이 불가능하여 임시 테이블이 사용

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

아래에서 인덱스된 컬럼에 대해 DISTINCT를 수행하면 인덱스를 풀 스캔 혹은 레인지 스캔을 한다.

SELECT COUNT(DISTINCT emp_no) FROM employees;

내부 임시 테이블 활용

• MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑할 때 는 내부적인 임시 테이블(internal temporary table)을 사용

내부적 임시 테이블은 CREATE TEMPORARY TABLE 명령어로 만든 임시 테이블과 다르다.

일반적으로 MySQL엔진이 사용하는 임시 테이블은 처음에는 메모리에 사용됐다가 테이블의 크기가 커지면 디스크로 이동(특정 예외 케이스에는 바로 디스크에 생성)

MySQL 엔진이 내부적인 가공을 위해 생성한 임시 테이블은 다른 세션이나 다른 쿼리에 보는것도 불가능하며 사용도 불가능

메모리 임시 테이블과 디스크 임시 테이블

MySQL8.0 이전에는 원본 테이블의 엔진과 상관없이 임시 테이블이 메모리를 사용을 할 때는 MEMORY 스토리지 엔진을 사용, 디스크에서는 MyISAM을 사용

MySQL8.0 부터는 MEMORY 스토리지 엔진 대신 가변 길이 타입을 지원하는 TempTable 스토리지 엔진이 도입(MEMORY 엔진은 가변 길이 타입을 지원하지 않음)

임시 테이블이 필요한 쿼리

MySQL 엔진에서 별도의 데이터 가공 작업이 필요로 한다면 임시 테이블을 생성
(+ 인덱스를 사용하지 못할 때도 내부 임시테이블을 생성해야 할 때가 많음)[Unique Index를 사용한 임시 테이블]

• ORDER BY와 GROUP BY에 명시된 컬럼이 다른 쿼리[Unique Index를 사용한 임시 테이블]
• ORDER BY나 GROUP BY에 명시된 컬럼이 조인의 순서상 첫 번째 테입블이 아닌 쿼리[Unique Index를 사용한 임시 테이블]
• DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우, DISTINCT가 인덱스로 처리되지 못하는 경우[Unique Index를 사용한 임시 테이블], [Extra 표시 X]
• UNION이나 UNION DISTINCT가 사용된 쿼리[Extra 표시 X]
• 쿼리의 실행 계획에서 select_type이 DERIVED인 경우[Extra 표시 X]

실행 계획에서 Extra에 Using temporary가 출력된다면 임시 테이블을 사용(없어도 사용하는 경우가 존재)

⭐️ Unique Index가 존재하면 처리가 상당히 느리다.

임시 테이블이 디스크에 생성되는 경우

내부 임시 테이블은 기본적으로 메모리에 만들어지지만 아래의 경우 디스크에 만들어짐

• UNION, UNION ALL에서 SELECT되는 컬럼 중에서 길이가 512Byte 이상인 경우가 존재
• GROUP BY나 DISTINCT 컬럼에 512Byte 이상인 크기의 컬럼이 존재하는 경우
• 메모리 임시 테이블 크기가 tmp_table_size 또는 max_heap_table_size 시스템 변수보다 크거나 temptable_max_ram보다 큰 경우