고급 최적화

• MySQL 서버의 옵티마이저가 실행 계획을 수립할 때 통계 정보와 옵티마이저 옵션을 결합해서 최적의 실행 계획을 수립하게 된다.
• 옵티마이저 옵션은 크게 다음 두 가지로 구분할 수 있다.
  • 조인 관련된 옵티마이저 옵션
  • 옵티마이저 스위치
• MySQL 서버 초기 버전부터 조인 관련된 옵티마이저 옵션을 제공했다.
  • 조인이 많이 사용되는 서비스에서는 알아야 한다.
• MySQL 5.5 버전부터 옵티마이저 스위치를 지원했다.
  • MySQL 서버의 고급 최적화 기능들을 활성화할지 제어하는 용도로 사용한다.

옵티마이저 스위치 옵션

• 옵티마이저 스위치 옵션은 optimizer_switch 시스템 변수를 이용해서 제어한다.
• optimizer_switch 시스템 변수에는 여러 개의 옵션을 세트로 묶어서 설정하는 방식으로 사용한다.

최적화 옵션

옵션에 대한 자세한 설명은 다음 글에서 각각 설명한다.

• 시스템 변수에 설정할 수 있는 최적화 옵션은 다음과 같다.

옵티마이저 스위치 이름	기본값	설명
batched_key_access	off	BKA 조인 알고리즘을 사용할지 여부 설정
block_nested_loop	on	Block Nested Loop 조인 알고리즘을 사용할지 여부 설정
engine_condition_pushdown	on	Engine Condition Pushdown 기능을 사용할지 여부 설정
index_condition_pushdown	on	Index Condition Pushdown 기능을 사용할지 여부 설정
use_index_extensions	on	Index Extension 최적화를 사용할지 여부 설정
index_merge	on	Index Merge 최적화를 사용할지 여부 설정
index_merge_intersection	on	Index Merge Intersection 최적화를 사용할지 여부 설정
index_merge_sort_union	on	Index Merge Sort Union 최적화를 사용할지 여부 설정
index_merge_union	on	Index Merge Union 최적화를 사용할지 여부 설정
mrr	on	MRR 최적화를 사용할지 여부 설정
mrr_cost_based	on	비용 기반의 MRR 최적화를 사용할지 여부 설정
semijoin	on	세미 조인 최적화를 사용할지 여부 설정
firstmatch	on	FirstMatch 세미 조인 최적화를 사용할지 여부 설정
loosescan	on	LooseScan 세미 조인 최적화를 사용할지 여부 설정
materialization	on	Materialization 최적화를 사용할지 여부 설정 (Materialization 세미 조인 최적화 포함)
subquery_materialization_cost_based	on	비용 기반의 Materialization 최적화를 사용할지 여부 설정

스위치 옵션 설정

• 스위치 옵션은 “default”, “on”, “off” 중 하나를 설정할 수 있다.
  • default: 기본값으로 적용
  • on: 옵션 활성화
  • off: 옵션 비활성화
• 옵티마이저 스위치 옵션은 글로벌과 세션별 모두 설정할 수 있는 시스템 변수이다.

-- MySQL 서버 전체적으로 옵티마이저 스위치 설정
SET GLOBAL optimizer_switch='index_merge=on,index_merge_union=on,...';

-- 현재 커넥션의 옵티마이저 스위치만 설정
SET SESSION optimizer_switch='index_merge=on,index_merge_union=on,...';

-- 현재 쿼리에만 옵티마이저 스위치 설정
SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off') */ 1;

💡 SET_VAR 사용법

SET_VAR는 (단일) 명령문이 실행되는 동안 시스템 변수의 세션 값을 일시적으로 설정하는 힌트 구문이다.

• var_name : 세션 값이 있는 시스템 변수의 이름
• value : 할당할 값

SET_VAR(var_name = value)

MySQL에서 /*+*/는 쿼리 힌트를 지정하는 데 사용한다. 따라서, 다음과 같이 사용하면 된다.

SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ name FROM people ORDER BY name;
INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);
SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off') */ 1;

출처: https://dev.mysql.com/doc/refman/8.3/en/optimizer-hints.html#optimizer-hints-set-var, AI Gemini

조인 최적화 알고리즘

• MySQL에는 조인 쿼리의 실행 계획 최적화를 위한 알고리즘이 2개 있다.
  • Exhaustive 검색 알고리즘
  • Greedy 검색 알고리즘
• MySQL의 조인 최적화는 나름 많이 개선되었다고 이야기하지만, 다음과 같은 현상이 생길 수 있다.
  • 테이블의 개수가 많아지면 최적화된 실행 계획을 찾는 것이 상당히 어려워진다.
  • 하나의 쿼리에서 조인되는 테이블의 개수가 많아지면 실행 계획을 수립하는 데만 몇 분이 걸릴 수 있다.
  • 테이블의 개수가 특정 한계를 넘어서면 실행 계획 수립에 소요되는 시간만 몇 시간이나 며칠로 늘어날 수 있다.

Exhaustive 검색 알고리즘

• MySQL 5.0과 그 이전 버전에서 사용되던 조인 최적화 기법이다.
• FROM 절에 명시된 모든 테이블의 조합에 대해 실행 계획의 비용을 계산해서 최적의 조합 1개를 찾는 방법이다.
• 4개의 테이블이 Exhaustive 검색 알고리즘으로 처리된다면 $4!(Factorial, 64)$개가 된다.
• 이전 버전에서 사용되던 Exhaustive 검색 알고리즘에서는 테이블이 10개만 넘어도 실행 계획을 수립하는데 몇 분이 걸리며, 테이블이 10개에서 1개만 늘어나도 11배의 시간이 더 걸린다.

💡 오타?
서적에서, 또 다른 예시를 들면서 테이블이 20개라면 $20!(3628800)$개가 된다고 설명했다.

그런데 $20! = 2,432,902,008,176,640,000$이이며, 3628800개가 되는 건 $10!$이다.

뒤에서 테이블이 10개일 때를 말하며 실행 계획 시간을 설명하는 걸 보면 단순 오타인 듯하다.

Greedy 검색 알고리즘

• MySQL 5.0부터 도입된 조인 최적화 기법이다.
• Exhaustive 검색 알고리즘의 시간 소모적인 문제점을 해결하기 위해 도입되었다.
• optimizer_search_depth 시스템 설정 변수에 설정된 값에 따라 조인 최적화의 비용이 상당히 줄어들 수 있다.
  • 기본값은 62다.

Greedy 검색 알고리즘의 동작

1. 전체 N개의 테이블 중에서 optimizer_search_depth 시스템 설정 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성한다.
2. (1)번에서 생성된 조인 조합 중에서 최소 비용의 실행 계획 하나를 선정한다.
3. (2)번에서 선정된 실행 계획의 첫 번째 테이블을 “부분 실행 계획(실행 계획 완료 대상)”의 첫 번째 테이블로 선정한다.
4. (3)번에서 선택된 테이블을 제외한, 전체 N-1개의 테이블에서 다시 optimizer_search_depth 시스템 설정 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성한다.
5. (4)번에서 생성된 조인 조합들을 하나씩 (3)번에서 생성된 “부분 실행 계획”에 대입해 실행 비용을 계산한다.
6. (5)번의 비용 계산 결과, 최적의 실행 계획에서 두 번째 테이블을 (3)번에서 생성된 “부분 실행 계획”의 두 번째 테이블로 선정한다.
7. 남은 테이블이 모두 없어질 때까지 (4)~(6)번까지의 과정을 반복 실행하면서 “부분 실행 계획”에 테이블의 조인 순서를 기록한다.
8. 최종적으로 “부분 실행 계획”이 테이블의 조인 순서로 결정된다.

Greedy 검색 알고리즘의 예

전체 4개의 테이블($N=4$)이고, optimizer_search_depth=2 라고 가정한다.

SELECT * FROM t1, t2, t3, t4;

1. 전체 4개의 테이블 중에서 2개의 테이블로 가능한 조인 조합을 생성한다.

   • optimizer_search_depth 시스템 설정 변수에 정의된 개수는 2개

   

2. (1)번에서 생성된 조인 조합 중에서 최소 비용의 실행 계획 하나를 선정한다.

   • T3 → T2 선택

   

3. (2)번에서 선정된 실행 계획의 첫 번째 테이블을 “부분 실행 계획(실행 계획 완료 대상)”의 첫 번째 테이블로 선정한다.

   

4. (3)번에서 선택된 T3 테이블을 제외한, 전체 3개의 테이블에서 다시 2개의 테이블로 가능한 조인 조합을 생성한다.

   

5. 생성된 조인 조합들을 하나씩 “부분 실행 계획”에 대입해 실행 비용을 계산한다.

6. (5)번의 비용 계산 결과, 최적의 실행 계획에서 두 번째 테이블을 “부분 실행 계획”의 두 번째 테이블로 선정한다.

   • T4 → T2 선택

   

7. 앞서 선택된 T3, T4 테이블을 제외한 전체 2개의 테이블에서 다시 2개의 테이블로 가능한 조인 조합을 생성한다.

   

8. 생성된 조인 조합들을 하나씩 “부분 실행 계획”에 대입해 실행 비용을 계산한다.

9. (8)번의 비용 계산 결과, 최적의 실행 계획에서 두 번째 테이블을 “부분 실행 계획”의 세 번째 테이블로 선정한다.

   • T2 → T1 선택

   

10. 남은 테이블이 T1 하나이므로, “부분 실행 계획”에 테이블의 조인 순서를 기록한다.

    

11. 최종적으로 “부분 실행 계획”이 테이블의 조인 순서로 결정된다.

조인 최적화를 위한 시스템 변수

• MySQL에서는 조인 최적화를 시스템 변수로 optimizer_prune_lebel 과 optimizer_search_depth 가 제공된다.
• optimizer_search_depth
  • Greedy 검색 알고리즘과 Exhastive 검색 알고리즘 중에서 어떤 알고리즘을 사용할지 결정하는 시스템 변수
  • 0~62까지의 정숫값을 설정할 수 있다.
  • 기본값은 62다.
  • 0이면 MySQL 옵티마이저가 Greedy 검색을 위한 최적의 조인 검색 테이블의 개수를 자동으로 결정한다.
  • 1~62까지의 정숫값이 설정되면 Greedy 검색 대상을 지정된 개수로 한정해서 최적의 실행 계획을 산출한다.
  • 4~5정도의 값으로 설정하는 것이 좋다.
  • 조인에 사용된 테이블의 개수가 이 설정값보다 크다면 설정값만큼의 테이블은 Exhaustive 검색이 사용되고, 나머지 테이블은 Greedy 검색이 사용된다.
  • 조인에 사용된 테이블의 개수가 이 설정값보다 작다면 Exhaustive 검색만 사용된다.
• optimizer_prune_lebel
  • MySQL 5.0부터 추가된 Heuristic 검색이 작동하는 방식을 제어한다.
  • 기본값은 1이다.
  • Heuristic 검색의 가장 핵심적인 내용은 다양한 조인 순서의 비용을 계산하는 도중 이미 계산했던 조인 순서의 비용보다 큰 경우에는 언제든지 중간에 포기할 수 있다는 것이다.
  • Heuristic 검색 최적화에는 아우터 조인으로 연결되는 테이블은 우선순위에서 제거하는 등 경험 기반의 최적화도 포함되어 있다.
  • 이 시스템 변수가 1로 설정되면 옵티마이저는 조인 순서 최적화에 경험 기반의 Heuristic 알고리즘을 사용한다.
  • 이 시스템 변수가 0으로 설정되면 경험 기반의 Heuristic 최적화가 적용되지 않는다.
  • Heuristic 조인 최적화는 조인 대상 테이블이 몇 개 되지 않더라도 상낭한 성능 차이를 내므로, 특별한 요건이 없다면 이 시스템 변수의 값을 0으로 설정하지 말도록 하자.

Reference

참고 서적

📔 Real MySQL 8.0

참고 사이트

MySQL 옵티마이저 힌트 문서
AI Gemini