MySQL 8.0에서 추가된 모든 기능 – 옵티마이저

1. 히스토그램

[원본제공링크 1 2 3]

히스토그램(Histogram)은 테이블의 컬럼 값 분포를 통계적으로 표현하여 옵티마이저가 보다 정확한 실행 계획을 수립할 수 있게 합니다. 히스토그램을 사용하면 데이터 분포에 대한 정보를 제공하여, 옵티마이저가 쿼리 실행 시 더 나은 인덱스 선택과 조인 순서 결정을 할 수 있습니다.

예시:

-- 특정 컬럼에 히스토그램 생성
ANALYZE TABLE employees UPDATE HISTOGRAM ON salary WITH 10 BUCKETS;
+-------------------+-----------+----------+---------------------------------------------------+
| Table             | Op        | Msg_type | Msg_text                                          |
+-------------------+-----------+----------+---------------------------------------------------+
| minhang.employees | histogram | status   | Histogram statistics created for column 'salary'. |
+-------------------+-----------+----------+---------------------------------------------------+
1 row in set (0.10 sec)

이 예시는 employees 테이블의 salary 컬럼에 대해 10개의 버킷을 가진 히스토그램을 생성합니다.

2. 적응형 스캔 버퍼 크기

[원본제공링크 1]

※ 이 주제는 제목만 봐선 무슨 이야기인지 모르겠고, 링크된 문서까지 읽어봐도 무슨 소리인지 이해가 조금 어렵습니다.

우선 레퍼런스 매뉴얼을 참고해봐도 ‘Adaptive Scan Buffer Size’나 ‘Adaptive Scan Buffer’라는 기능 자체가 언급되지 않습니다. 그렇다고 MySQL에 ‘Scan Buffer’라는 개념이 있는 것도 아니라.. 이 문서에서만 한정되어 사용된 개념으로 보입니다.

링크된 글에도 ‘adaptive’라는 단어가 나오지도 않고, ‘scan buffer’라는 개념이 나오지도 않습니다. 내용도 버퍼사이즈가 커진다면 스캔 범위가 큰 쿼리들이 이득을 볼 것이라는 것입니다. 옵티마이저가 스캔범위에 따라 버퍼 사이즈를 adaptive하게 조절한다는 내용이 전혀 아니므로, 이 주제는 제목을 잘못 붙혀놓은 것 같습니다.

링크된 글 내용만 아주 짧게 요약해보자면,
InnoDB가 사용할 버퍼 크기를 키우겠다. 그러면 대부분의 상황에서 이득을 본다.
// 내용이 이게 다고, 옵티마이저에 대한 내용도 아닙니다.

3. 메모리 IO와 디스크 IO 간의 비용 분리 계산

[원본제공링크 1]

이전 버전의 MySQL에서는 옵티마이저가 데이터 접근 시 모든 I/O 비용을 동일하게 간주하여, 메모리와 디스크 간의 성능 차이를 반영하지 못했습니다. 그러나 MySQL 8.0부터는 비용 모델이 개선되어, 메모리와 디스크 접근에 대한 비용을 별도로 계산합니다. 이를 통해 옵티마이저는 데이터가 버퍼 풀에 캐시되어 있는 경우와 디스크에서 직접 읽어야 하는 경우를 구분하여, 더 효율적인 실행 계획을 수립할 수 있습니다.

4. 비용 테이블의 기본값

[원본제공링크 1]

mysql.server_cost, mysql.engine_cost 테이블의 기본값이 업데이트되어, 현대적인 하드웨어 환경에 맞게 비용 모델이 조정되었습니다. 이를 통해 옵티마이저는 보다 현실적인 비용 추정치를 사용하여 실행 계획을 수립합니다.

예시:

mysql> SELECT * FROM mysql.server_cost WHERE cost_name = 'disk_temptable_create_cost';
+----------------------------+------------+---------------------+---------+---------------+
| cost_name                  | cost_value | last_update         | comment | default_value |
+----------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost |       NULL | 2022-09-28 01:40:53 | NULL    |            20 |
+----------------------------+------------+---------------------+---------+---------------+
1 row in set (0.00 sec)

5. 스토리지 엔진 API의 샘플링 인터페이스

[원본제공링크 1 2 3 4]

※ 위의 링크는 원본문서에서도 깨져있는데, 소스 코드쪽 문서라 대체된 문서의 정확한 링크를 찾기 어렵습니다..
헌데, “Sampling Interface” “Storage Engine API”를 검색해서 나오는 내용도 없어서 무슨 이야기를 하려던 것인지 추측이 어렵습니다..

6. NOWAIT 및 SKIP LOCKED

[원본제공링크 1 2]

데이터베이스에서 동시성 제어를 위해 행 수준 잠금이 사용되며, 이는 여러 트랜잭션이 동일한 데이터를 동시에 수정하지 못하도록 합니다. 그러나 이러한 잠금은 다른 트랜잭션이 해당 행에 접근하려 할 때 지연을 초래할 수 있습니다. MySQL 8.0에서는 이러한 문제를 해결하기 위해 NOWAIT와 SKIP LOCKED 옵션을 제공합니다.

SKIP LOCKED: 이 옵션을 사용하면, 잠긴 행을 결과 집합에서 제외하고 나머지 행만을 반환합니다. 이를 통해 잠금으로 인한 지연 없이 작업을 계속할 수 있으며, 주로 대기열과 같은 테이블에서 여러 세션이 동시에 작업할 때 유용합니다.

NOWAIT: 이 옵션을 사용하면, 트랜잭션이 잠긴 행에 접근하려 할 때 대기하지 않고 즉시 오류를 반환합니다. 이를 통해 잠금으로 인한 지연을 방지하고, 애플리케이션이 신속하게 대체 작업을 수행할 수 있게 합니다.

예시:

-- 잠금 대기 없이 즉시 오류 반환
SELECT * FROM orders FOR UPDATE NOWAIT;

-- 잠긴 행을 건너뛰고 작업 진행
SELECT * FROM orders FOR UPDATE SKIP LOCKED;

7. FORCE INDEX로 불필요한 인덱스 다이빙 방지

[원본제공링크 1 2 3]

FORCE INDEX 힌트를 사용할 때 옵티마이저가 불필요한 인덱스 다이브를 수행하지 않도록 개선되었습니다. 여기서 인덱스 다이브란, 쿼리에 제일 적합한 인덱스가 무엇인지 살펴보는 과정입니다. FORCE INDEX를 사용하면 지정된 인덱스를 강제로 사용하여 실행 계획 수립 시간을 단축합니다.

예시:

SELECT * FROM employees FORCE INDEX (idx_last_name) WHERE last_name = 'Smith';

8. 비가시성 인덱스 사용을 위한 옵티마이저 스위치

[원본제공링크 1 2 3]

옵티마이저가 비가시성 인덱스(Invisible Index)를 사용할지 여부를 제어하는 옵티마이저 스위치가 추가되었습니다. 비가시성 인덱스 또한 8.0에서 추가된 기능으로, 옵티마이저가 사용하지 않도록 설정해 놓은 인덱스입니다. 헌데 이걸 또 그냥 무시하고 사용하도록 설정할 수 있는 optimizer_switch 값이 추가된 것입니다. 이는 테스트나 마이그레이션 시 비가시성 인덱스의 영향을 분석하는 데 도움이 됩니다.

사용 방법:

-- 보이지 않는 인덱스 사용 허용
SET optimizer_switch = 'use_invisible_indexes=on';

9. 최적화 추적의 기본 버퍼 크기 증가

[원본제공링크 1]

최적화 추적(Optimizer Trace) 버퍼 크기의 기본값이 증가하였습니다. 기존 16KB에서 1MB로 변경되었습니다. 이는 옵티마이저가 쿼리 실행 계획을 생성하는 과정을 디버깅하거나 분석할 때 더 많은 정보를 기록할 수 있도록 설계된 변경사항입니다. 복잡한 쿼리의 실행 계획을 더 완전하게 추적할 수 있게 되었습니다. 해당 값은 optimizer_trace_max_mem_size 파라메터를 수정하여 변경해도 됩니다.

10. 새 힌트 – MERGE

[원본제공링크 1]

MERGE 힌트는 옵티마이저에게 뷰나 서브쿼리를 병합하여 실행하도록 지시합니다. 여기서 MERGE란, 뷰나 서브쿼리를 풀어서, 하나의 쿼리처럼 만들어주는 것입니다. 이를 통해 더욱 정확한 최적화 과정을 얻을 수 있고, 결과적으로 쿼리 성능을 향상시킬 수 있습니다.

예시:

SELECT /*+ MERGE(v) */ * FROM (SELECT * FROM orders) v WHERE order_date > '2021-01-01';

11. 새 힌트 – INDEX_MERGE

[원본제공링크 1]

INDEX_MERGE 힌트는 옵티마이저에게 INDEX_MERGE 알고리즘을 사용하도록 지시하여, 여러개의 인덱스를 병합하여 사용하는 방식으로 쿼리를 최적화합니다. 예를 들어, OR 조건이 포함된 쿼리에서 두 개 이상의 인덱스를 동시에 사용해 각각의 결과를 병합할 수 있습니다.

예시:

SELECT /*+ INDEX_MERGE(employees idx_age idx_salary) */ * 
FROM employees 
WHERE age > 30 OR salary > 60000;

12. 새 힌트 – NO_INDEX_MERGE

[원본제공링크 1]

NO_INDEX_MERGE 힌트는 옵티마이저에게 INDEX_MERGE 알고리즘을 사용하지 않도록 지시합니다. 특정 상황에서 단일 인덱스를 사용하는 것이 더 효율적일 때 사용합니다.

예시:

SELECT /*+ NO_INDEX_MERGE(orders) */ * FROM orders WHERE customer_id = 1 OR order_date > '2021-01-01';

13. 새 힌트 – JOIN_FIXED_ORDER

[원본제공링크 1]

JOIN_FIXED_ORDER 힌트는 옵티마이저에게 작성된 조인 순서를 그대로 사용하도록 지시합니다. 이는 개발자가 조인 순서를 명시적으로 제어하고자 할 때 유용합니다. 이 경우 FROM절에 테이블이 사용된 순서대로 조인이 발생합니다. 기존의 STRAIGHT_JOIN과 같습니다.

예시:

SELECT /*+ JOIN_FIXED_ORDER */ * FROM customers c JOIN orders o ON c.id = o.customer_id;

14. 새 힌트 – JOIN_ORDER

[원본제공링크 1]

JOIN_ORDER 힌트는 옵티마이저에게 지정된 순서대로 조인을 수행하도록 지시합니다. 이때 순서에 언급할 수 있는 대상은, 테이블을 대상으로 할 수도 있지만, 특정 쿼리 블록을 대상으로 할 수도 있습니다.

예시:

SELECT /*+ JOIN_ORDER(c, o) */ * FROM customers c JOIN orders o ON c.id = o.customer_id;

15. 새 힌트 – JOIN_PREFIX

[원본제공링크 1]

JOIN_PREFIX 힌트는 옵티마이저에게 지정된 테이블들을 조인의 앞부분에 배치하도록 지시합니다. 나머지 테이블들은 옵티마이저에 의해 최적화된 순서로 배치됩니다.

예시:

SELECT /*+ JOIN_PREFIX(c, o) */ * FROM customers c JOIN orders o ON c.id = o.customer_id JOIN payments p ON o.id = p.order_id;

16. 새 힌트 – JOIN_SUFFIX

[원본제공링크 1]

JOIN_SUFFIX 힌트는 옵티마이저에게 지정된 테이블들을 조인의 뒷부분에 배치하도록 지시합니다. 나머지 테이블들은 앞쪽에 옵티마이저가 최적화된 순서로 배치됩니다.

예시:

SELECT /*+ JOIN_SUFFIX(p) */ * FROM customers c JOIN orders o ON c.id = o.customer_id JOIN payments p ON o.id = p.order_id;

17. 새 힌트 – SET_VAR

[원본제공링크 1]

SET_VAR 힌트는 특정 쿼리에서만 세션 변수를 일시적으로 설정할 수 있게 합니다. 이를 통해 쿼리 단위로 설정을 조정할 수 있습니다. 예를 들어, sort_buffer_size나 max_execution_time과 같은 변수 값을 조정하여 특정 쿼리의 성능을 최적화할 수 있습니다.

예시:

SELECT /*+ SET_VAR(sort_buffer_size=16M) */ * FROM large_table ORDER BY column1;

18. LIKE 연산자 사용시, 프리픽스 인덱스를 커버링 인덱스로 고려

[원본제공링크 1]

MySQL 8.0에서는 Covering Prefix Index를 사용하여 특정 조건에서 LIKE 연산자를 효율적으로 처리할 수 있습니다. 전방 매칭(예: LIKE 'abc%')이 포함된 조건에서 프리픽스 인덱스를 활용해 성능을 최적화합니다. 이는 문자열 검색 시 풀 테이블 스캔을 방지하고, 인덱스 범위를 통해 데이터를 빠르게 검색할 수 있게 합니다.

예시:

-- 'name' 컬럼에 인덱스가 있을 때
SELECT name FROM employees WHERE name LIKE 'A%';

19. INSERT/UPDATE/REPLACE/DELETE의 EXPLAIN에 대해 변환된 문장 확인

[원본제공링크 1]

EXPLAIN 명령어를 사용하여 INSERT, UPDATE, REPLACE, DELETE 문에 대한 실행 계획을 조회할 때 변환된 내부 문장을 볼 수 있게 되었습니다. 이는 옵티마이저가 쿼리를 어떻게 변환하여 실행하는지 이해하는 데 도움이 됩니다.

-- 실행 계획 확인
mysql> explain update test1 set data=data+1 where id =1;            
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | test1 | NULL       | range | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- 변환된 문장 확인
mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                   |
+-------+------+---------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | update `minhang`.`test1` set `minhang`.`test1`.`data` = (`minhang`.`test1`.`data` + 1) where (`minhang`.`test1`.`id` = 1) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

EXPLAIN 이 후에 SHOW WARNINGS를 통해 추가적인 메세지를 확인하는 것은 원래 SELECT 구문에 대해서만 지원되던 기능입니다. 8.0.12버전 이후로 INSERT/UPDATWE/REPLACE/DELETE에 대해서도 확인 가능하게 되었습니다.

20. EXPLAIN ANALYZE

[원본제공링크 1]

EXPLAIN ANALYZE는 실제 쿼리를 실행하고 실행 계획과 함께 실제 실행 시간과 행 수를 제공합니다. 이를 통해 예상된 실행 계획과 실제 실행 결과를 비교하여 쿼리 성능을 최적화할 수 있습니다.

예시:

EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 10;

21. HASH JOIN(이너, 아우터, 안티, 세미 조인)

[원본제공링크 1]

옵티마이저가 해시 조인(Hash Join)을 지원하여, 대용량 테이블 간의 조인 성능을 향상시켰습니다. 해시 조인은 메모리에서 해시 테이블을 생성하여 조인하므로, 특정 상황에서 중첩 루프 조인보다 빠른 성능을 제공합니다.

예시:

mysql> explain select a.id from aaa a join bbb b on a.filename=b.filename;            
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 106652 |   100.00 | NULL                                       |
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 981274 |    10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

equi 조인인 경우에만 적용 가능하며, 사용가능한 인덱스가 없을 때 효과가 좋습니다.

22. 이터레이터 실행기에서 배치 키 접근

[원본제공링크 1 2]

이터레이터 실행기(Iterator Executor)에서 배치 키 접근(Batched Key Access, BKA) 지원하여, 조인 시 랜덤 I/O를 줄이고 성능을 향상시켰습니다. 이는 드라이빙 테이블의 키를 배치로 모아서 한 번에 접근합니다.
MySQL의 Batch Key Access (BKA)는 대량 데이터를 처리할 때 랜덤 I/O 비용을 줄이고, 성능을 최적화하는 쿼리 실행 방식입니다. BKA는 조인 작업에서 여러 키를 한 번에 읽어들여 디스크 접근을 최소화하며, 특히 InnoDB 스토리지 엔진과 잘 통합됩니다. MySQL 8.0의 Iterator Executor는 BKA를 활용해 조인 실행 계획을 효율적으로 처리합니다.

23. 그룹-바이 루스 인덱스 스캔 개선

[원본제공링크 1]

GROUP BY 절에서 루스 인덱스 스캔(Loose Index Scan)이 개선되어, 인덱스를 효과적으로 활용하여 그룹화 작업의 성능을 향상시켰습니다.

예시:

SELECT COUNT(*), category FROM products GROUP BY category;

인덱스가 category 컬럼에 있을 경우, 루스 인덱스 스캔을 통해 성능이 개선됩니다.
(다만 loose index scan의 기능 중 어느부분이 더 좋아졌다는 것인지는 레퍼런스 매뉴얼을 봐도 확실하지가 않습니다. 해당 페이지는 5.7과 8.0의 내용이 같습니다.)

24. LIMIT 최적화를 비활성화하기 위한 새 옵티마이저 스위치

[원본제공링크 1]

ORDER BY 또는 GROUP BY와 함께 LIMIT 절이 있는 쿼리에 대해, MySQL 옵티마이저는 기본적으로 정렬된 인덱스를 사용하는 것이 쿼리 실행을 더 빠르게 할 것으로 판단되면 이를 선택하려고 시도합니다.
MySQL 8.0.21 이전에는, 옵티마이저가 이러한 동작을 수행하지 않도록 설정할 방법이 없었습니다.
하지만 MySQL 8.0.21부터는 optimizer_switch 시스템 변수의 prefer_ordering_index 플래그를 off로 설정하여 이 최적화를 비활성화할 수 있습니다.

사용 방법:

SET optimizer_switch = "prefer_ordering_index=off";

25. 단일 테이블에서의 UPDATE/DELETE에 대해 세미조인 작동

[원본제공링크 1 2]

MySQL 8.0에서는 Semijoin 전략이 이제 단일 테이블에 대한 UPDATE 및 DELETE 쿼리에서도 동작하도록 확장되었습니다.
이는 특정 조건에서 서브쿼리를 포함하는 UPDATE 및 DELETE 쿼리의 성능을 최적화하며, 불필요한 데이터 접근을 줄이고 실행 속도를 개선할 수 있습니다.

예시:

DELETE FROM orders WHERE customer_id NOT IN (SELECT id FROM customers);

옵티마이저가 이 쿼리에 세미조인을 사용하여 효율적으로 실행할 수 있습니다.