[MySQL] Query Optimizer 제어
- Databases/MySQL
- 2020. 8. 14.
■ 쿼리 계획 평가 제어
쿼리 옵티마이저의 작업은 SQL 쿼리를 실행하기위한 최적의 계획을 찾는 것입니다. "좋은"계획과 "나쁜"계획 간의 성능 차이는 수십 배 (몇 초에서 몇 시간 또는 며칠)에 달할 수 있으므로 MySQL 쿼리를 포함한 대부분의 쿼리 옵티마이저는 최적의 검색을 위해 다소 철저한 검색을 수행합니다. 가능한 모든 쿼리 평가 계획 중에서 실행 계획을 만듭니다. 조인 쿼리의 경우 MySQL 옵티마이저가 조사한 가능한 계획수가 쿼리에서 참조되는 테이블 수에 따라 기하 급수적으로 증가합니다. 적은 수의 테이블 (일반적으로 7-10 미만)의 경우 문제가되지 않습니다. 그러나 더 큰 쿼리를 제출하면 쿼리 최적화에 소요되는 시간이 서버 성능의 주요 병목이 될 수 있습니다.
보다 유연한 쿼리 최적화 방법을 통해 사용자는 옵티마이저가 최적의 쿼리 평가 계획을 찾는데 얼마나 철저하게 계획을 세울지에 대해 제어 할 수 있습니다. 일반적으로 옵티마이저가 조사하는 계획이 적을수록 쿼리 컴파일에 소요되는 시간이 줄어 듭니다. 반면, 옵티마이 저가 일부 계획을 건너 뛰므로 최적의 계획을 찾지 못할 수 있습니다.
평가하는 계획수와 관련하여 옵티마이저의 동작은 두 가지 시스템 변수를 사용하여 제어 할 수 있습니다.
+ optimizer_prune_level 변수는 옵티마이저가 각 테이블에 액세스한 행(row) 수의 추정치에 따라 특정 계획을 건너 뛰도록 지시합니다. 이런 종류의 "훈련된 혹은 교육받은 가상 추정"에서 최적의 계획을 놓치지 않으며 쿼리 컴파일 시간을 크게 단축시킬 수 있습니다. 따라서 이 옵션은 기본적으로 켜져 있습니다 (optimizer_prune_level = 1). 그러나 옵티마이 저가 더 나은 쿼리 계획을 놓쳤다고 생각하면 쿼리 컴파일 시간이 더 오래 걸릴 수 있으므로 이 옵션을 끌 수 있습니다 (optimizer_prune_level = 0). 이 휴리스틱을 사용하더라도 옵티마이저는 여전히 대략적으로 많은 수의 계획을 탐색합니다.
+ optimizer_search_depth 변수는 옵티마이저가 추가 확장 여부를 평가하기 위해 불완전한 각 계획의 "미래"까지의 거리를 나타냅니다. optimizer_search_depth의 값이 작을수록 쿼리 컴파일 시간이 훨씬 작아질 수 있습니다. 예를 들어 12, 13 개 이상의 테이블이 있는 쿼리는 optimizer_search_depth가 쿼리의 테이블 수에 가까운 경우 컴파일하는데 몇 시간, 심지어 며칠이 걸릴 수 있습니다. 동시에, optimizer_search_depth를 3 또는 4로 컴파일하면 옵티마이저가 동일한 쿼리에 대해 1분 이내에 컴파일 할 수 있습니다. optimizer_search_depth에 대한 합리적인 값이 확실하지 않은 경우이 변수를 0으로 설정하여 옵티마이저가 값을 자동으로 결정하도록 지시할 수 있습니다.
■ Switchable Optimizations
optimizer_switch 시스템 변수를 사용하면 옵티 마이저 동작을 제어할 수 있습니다. 해당 값은 플래그 세트이며 각 플래그는 해당 옵티마이저 동작이 사용 가능한지 여부를 표시하기 위해 on 또는 off 값을 갖습니다. 이 변수는 글로벌 및 세션 값을 가지며 런타임시 변경 될 수 있습니다. 서버 시작시 전역 기본값을 설정할 수 있습니다.
현재 옵티마이저 플래그 세트를 보려면 변수 값을 선택합니다.
mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,mrr_cost_based=on,
block_nested_loop=on,batched_key_access=off,
materialization=on,semijoin=on,loosescan=on,
firstmatch=on,duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,derived_merge=on
optimizer_switch의 값을 변경하려면 쉼표로 구분된 하나 이상의 명령 목록으로 구성된 값을 지정합니다.
SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';
각 명령값은 다음표에 표시된 형식중 하나를 가져야합니다.
| Command Syntax | Meaning |
| default | Reset every optimization to its default value |
| opt_name=default | Set the named optimization to its default value |
| opt_name=off | Disable the named optimization |
| opt_name=on | Enable the named optimization |
기본 명령이있는 경우 먼저 실행되지만 값의 명령 순서는 중요하지 않습니다. opt_name 플래그를 기본값으로 설정하면 기본값으로 설정하거나 해제 할 수 있습니다. 지정된 opt_name을 값에 두 번 이상 지정하면 허용되지 않으며 오류가 발생합니다. 값에 오류가 있으면 오류가 발생하여 할당이 실패하고 optimizer_switch의 값은 변경되지 않습니다.
The following list describes the permissible opt_name flag names, grouped by optimization strategy:
다음 목록은 허용 가능한 opt_name 플래그 이름을 최적화 전략별로 그룹화하여 설명합니다.
+ Batched Key Access Flags
- batched_key_access (default off)
BKA 결합 알고리즘의 사용을 제어합니다.
on으로 설정된 경우 batched_key_access가 영향을 미치려면 mrr 플래그도 켜져 있어야합니다. 현재 MRR의 비용 산정은 너무 비관적입니다. 따라서 BKA를 사용하려면 mrr_cost_based를 해제해야합니다.
+ Block Nested-Loop Flags
- block_nested_loop (default on)
BNL 결합 알고리즘의 사용을 제어합니다.
+ Condition Filtering Flags
- condition_fanout_filter (default on)
조건 필터링 사용을 제어합니다.
+ Derived Table Merging Flags
- derived_merge (default on)
파생 테이블과 뷰를 외부 쿼리 블록으로 병합하는 것을 제어합니다.
derived_merge 플래그는 다른 규칙이 병합을 방해하지 않는다고 가정할 때 옵티마이저가 파생 테이블을 병합하고 참조를 외부 쿼리 블록으로 보려고하는지 여부를 제어합니다. 예를 들어, 보기에 대한 ALGORITHM 지시문은 derived_merge 설정보다 우선합니다. 기본적으로 플래그는 병합을 사용하도록 설정되어 있습니다.
+ Engine Condition Pushdown Flags
- engine_condition_pushdown (default on)
엔진 상태 푸시 다운을 제어합니다.
자세한 내용은 아래 링크를 참고합니다.
https://myinfrabox.tistory.com/83?category=829169
[MySQL] Engine Condition Push Down 최적화
이 최적화는 인덱싱되지 않은 열과 상수간 직접 비교의 효율성을 향상시킵니다. 이러한 경우 조건은 평가를 위해 스토리지 엔진에 "밀어 넣어집니다(pushed down)". 이 최적화는 NDB 스토리지 엔진에
myinfrabox.tistory.com
+ Index Condition Pushdown Flags
- index_condition_pushdown (default on)
인덱스 조건 푸시 다운을 제어합니다.
자세한 내용은 아래 링크를 참고합니다.
https://myinfrabox.tistory.com/84?category=829169
[MySQL] Index Condition Pushdown 최적화
ICP(Index Condition Pushdown)는 MySQL이 인덱스를 사용하여 테이블에서 행을 검색하는 경우를 위한 최적화입니다. ICP가 없으면 스토리지 엔진은 인덱스를 탐색하여 기본 테이블에서 행을 찾아 행의 WHERE
myinfrabox.tistory.com
+ Index Extensions Flags
- use_index_extensions (default on)
인덱스 확장 사용을 제어합니다.
자세한 내용은 아래 링크를 참고합니다.
https://myinfrabox.tistory.com/88?category=829169
[MySQL] 인덱스 확장 사용
InnoDB는 기본키 컬럼을 추가하여 각 보조 인덱스를 자동으로 확장합니다. 예를 들어 다음과 같은 테이블이 있습니다. CREATE TABLE t1 ( i1 INT NOT NULL DEFAULT 0, i2 INT NOT NULL DEFAULT 0, d DATE DEFAULT..
myinfrabox.tistory.com
+ Index Merge Flags
- index_merge (default on)
모든 인덱스 병합 최적화를 제어합니다.
- index_merge_intersection (default on)
인덱스 병합 교차 액세스 최적화를 제어합니다.
- index_merge_sort_union (default on)
인덱스 병합 정렬 조합 액세스 최적화를 제어합니다.
- index_merge_union (default on)
인덱스 병합 연합 액세스 최적화를 제어합니다.
자세한 내용은 아래 링크를 참고합니다.
https://myinfrabox.tistory.com/89?category=829169
[MySQL] 인덱스 병합 최적화
인덱스 병합 액세스 방법은 여러 범위 스캔으로 행을 검색하고 결과를 하나로 병합합니다. 이 액세스 방법은 단일 테이블의 인덱스 스캔만 병합하고 여러 테이블의 스캔은 병합하지 않습니다. �
myinfrabox.tistory.com
+ Multi-Range Read Flags
- mrr (default on)
다중 범위 읽기 전략을 제어합니다.
- mrr_cost_based (default on)
mrr = on 인 경우 비용 기반 MRR 사용을 제어합니다.
자세한 내용은 8.2.1.10 절“다중 범위 읽기 최적화”를 참조하십시오. - 링크 걸것(포스팅 작성중)
+ Semijoin Flags
- semijoin (default on)
모든 semijoin 전략을 제어합니다.
- duplicateweedout (default on)
semijoin 중복 위드 아웃 전략을 제어합니다.
- firstmatch (default on)
semijoin FirstMatch 전략을 제어합니다.
- loosescan (default on)
semijoin LooseScan 전략을 제어합니다 (GROUP BY의 Loose Index Scan과 혼동하지 마십시오).
semijoin, firstmatch, loosescan 및 duplicateweedout 플래그를 사용하면 semijoin 전략을 제어 할 수 있습니다. semijoin 플래그는 semijoin 사용 여부를 제어합니다. 설정하면 firstmatch 및 loosescan 플래그를 사용하여 허용된 semijoin 전략을보다 세밀하게 제어 할 수 있습니다.
duplicateweedout semijoin 전략을 사용하지 않으면 적용 가능한 다른 모든 전략도 사용하지 않으면 사용되지 않습니다.
semijoin 및 materialization이 모두 설정된 경우 semijoin은 해당되는 경우 materialization도 사용합니다. 이 플래그는 기본적으로 켜져 있습니다.
자세한 내용은 8.2.2.1 절“세미 조인 변환을 사용하여 하위 쿼리, 파생 테이블 및 뷰 참조 최적화”를 참조하십시오. - 링크 걸것(포스팅 작성중)
+ Subquery Materialization Flags
- materialization (default on)
구체화를 제어합니다 (반조 인 구체화 포함).
- subquery_materialization_cost_based (default on)
비용 기반 구체화 선택을 사용하십시오.
구체화 플래그는 서브쿼리 구체화(materialization)의 사용 여부를 제어합니다. semijoin 및 materialization이 모두 설정된 경우 semijoin은 해당되는 경우 materialization도 사용합니다. 이 플래그는 기본적으로 켜져 있습니다.
subquery_materialization_cost_based 플래그를 사용하면 하위 쿼리 구체화와 IN-to-EXISTS 하위 쿼리 변환 중에서 선택을 제어 할 수 있습니다. 플래그가 켜져 있으면 (기본값) 옵티마이저는 두 가지 방법 중 하나를 사용할 수 있는 경우 하위 쿼리 구체화와 IN-to-EXISTS 하위 쿼리 변환 중에서 비용 기반 선택을 수행합니다. 플래그가 꺼져 있으면 옵티마이 저는 IN-to-EXISTS 서브 쿼리 변환에서 서브 쿼리 구체화를 선택합니다.
자세한 내용은 8.2.2 절“하위 쿼리, 파생 테이블 및 뷰 참조 최적화”를 참조합니다. - 링크 걸것(포스팅 작성중)
optimizer_switch에 값을 할당 할 때 언급되지 않은 플래그는 현재 값을 유지합니다. 이를 통해 다른 동작에 영향을주지 않고 단일 명령문에서 특정 옵티 마이저 동작을 활성화하거나 비활성화 할 수 있습니다. 명령문은 다른 옵티 마이저 플래그가 존재하고 값이 무엇인지에 의존하지 않습니다. 모든 인덱스 병합 최적화가 활성화되었다고 가정합니다.
mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,mrr_cost_based=on,
block_nested_loop=on,batched_key_access=off,
materialization=on,semijoin=on,loosescan=on,
firstmatch=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on
서버가 특정 쿼리에 대해 Index Merge Union 또는 Index Merge Sort-Union 액세스 방법을 사용하고 있으며 옵티마이저가 없는지 더 잘 수행 할 것인지 확인하려면 변수 값을 다음과 같이 설정합니다.
■ 옵티마이저 힌트
옵티 마이저 전략을 제어하는 한가지 방법은 optimizer_switch 시스템 변수를 설정하는 것입니다 (8.9.2 절.“스위칭 가능한 최적화”참조). 이 변수를 변경하면 모든 후속 쿼리 실행에 영향을줍니다. 한 쿼리가 다른 쿼리와 다르게 영향을 미치려면 각 쿼리전에 optimizer_switch를 변경해야합니다.
옵티마이저를 제어하는 다른 방법은 개별 명령문 내에 지정할 수 있는 옵티 마이저 힌트를 사용하는 것입니다. 옵티 마이저 힌트는 명령문별로 적용되므로 optimizer_switch를 사용하여 얻을 수 있는 것보다 명령문 실행 계획을 보다 세밀하게 제어할 수 있습니다. 예를 들어, 명령문에서 한 테이블에 대한 최적화를 활성화하고 다른 테이블에 대한 최적화를 비활성화 할 수 있습니다. 명령문 내의 힌트는 optimizer_switch 플래그보다 우선합니다.
예제 :
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
FROM t3 WHERE f1 > 30 AND f1 < 33;
SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;
EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;
노트
기본적으로 mysql 클라이언트는 서버에 최적화 힌트를 전달하도록 변경될 때 MySQL 5.7.7까지 서버에 전송된 SQL문(최적화 힌트 포함)의 주석을 제거합니다. 옵티마이저 힌트를 이해하는 서버 버전과 함께 이전 버전의 mysql 클라이언트를 사용하는 경우 옵티마이저 힌트가 제거되지 않도록하려면 --comments 옵션을 사용하여 mysql을 호출합니다.
여기에 설명 된 옵티 마이저 힌트는 아래에 나올 인덱스 힌트와는 다름니다. 최적화 및 인덱스 힌트는 개별적으로 또는 함께 사용될 수 있습니다.
+ 옵티마이저 힌트 개요
+ 옵티마이저 힌트 힌트 구문
+ 테이블 레벨 옵티마이저 힌트
+ 인덱스 레벨 옵티마이저 힌트
+ 서브쿼리 옵티마이저 힌트
+ 명령문 실행 시간 옵티마이저 힌트
+ 명명 쿼리 블록에 대한 옵티마이저 힌트
▶︎ 옵티 마이저 힌트 개요
옵티마이저 힌트는 다른 범위 레벨에서 적용됩니다.
+ Global : 힌트는 전체 문장에 영향을줍니다
+ Query block : 힌트는 명령문 내의 특정 쿼리 블록에 영향을줍니다.
+ Table-level : 힌트는 쿼리 블록 내의 특정 테이블에 영향을줍니다.
+ Index-level : 힌트는 테이블 내의 특정 인덱스에 영향을줍니다.
다음표는 사용 가능한 옵티마이저 힌트, 영향을 주는 옵티마이저 전략 및 적용되는 범위를 요약합니다. 자세한 내용은 나중에 제공됩니다.
Table 8.2 Optimizer Hints Available
| Hint Name | Description | Applicable Scopes |
| BKA, NO_BKA | 일괄 키 액세스 조인 처리에 영향을줍니다. | Query block, table |
| BNL, NO_BNL | MySQL 8.0.20 이전 : Block Nested-Loop 조인 처리에 영향을줍니다. MySQL 8.0.18 이상 : 해시 조인 최적화에도 영향을줍니다. MySQL 8.0.20 이상 : 해시 조인 최적화에만 영향을 미침 | Query block, table |
| DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN | 구체화 된 파생 테이블에 대한 파생 조건 푸시 다운 최적화 사용 또는 무시 (MySQL 8.0.22에 추가됨) | Query block, table |
| GROUP_INDEX, NO_GROUP_INDEX | GROUP BY 작업에서 색인 스캔에 지정된 색인을 사용하거나 무시합니다 (MySQL 8.0.20에 추가됨). | Index |
| HASH_JOIN, NO_HASH_JOIN | 해시 조인 최적화에 영향을줍니다 (MySQL 8.0.18 만 해당). | Query block, table |
| INDEX, NO_INDEX | JOIN_INDEX, GROUP_INDEX 및 ORDER_INDEX의 조합 또는 NO_JOIN_INDEX, NO_GROUP_INDEX 및 NO_ORDER_INDEX의 조합으로 작동합니다 (MySQL 8.0.20에 추가됨). | Index |
| INDEX_MERGE, NO_INDEX_MERGE | 인덱스 병합 최적화에 영향 | Table, index |
| JOIN_FIXED_ORDER | 조인 순서를 위해 FROM 절에 지정된 테이블 순서 사용 | Query block |
| JOIN_INDEX, NO_JOIN_INDEX | 모든 액세스 방법에 대해 지정된 인덱스를 사용하거나 무시합니다 (MySQL 8.0.20에 추가됨). | Index |
| JOIN_ORDER | 조인 순서에 대한 힌트에 지정된 테이블 순서 사용 | Query block |
| JOIN_PREFIX | 조인 순서의 첫 번째 테이블에 힌트에 지정된 테이블 순서 사용 | Query block |
| JOIN_SUFFIX | 조인 순서의 첫 번째 테이블에 힌트에 지정된 테이블 순서 사용 | Query block |
| MAX_EXECUTION_TIME | 문 실행 시간 제한 | Global |
| MERGE, NO_MERGE | 파생 된 테이블 / 뷰가 외부 쿼리 블록으로 병합되는 데 영향을줍니다. | Table |
| MRR, NO_MRR | 다중 범위 읽기 최적화에 영향 | Table, index |
| NO_ICP | 인덱스 조건 푸시 다운 최적화에 영향 | Table, index |
| NO_RANGE_OPTIMIZATION | 범위 최적화에 영향 | Table, index |
| ORDER_INDEX, NO_ORDER_INDEX | 행 정렬을 위해 지정된 인덱스를 사용하거나 무시합니다 (MySQL 8.0.20에 추가됨). | Index |
| QB_NAME | 쿼리 블록에 이름 지정 | Query block |
| RESOURCE_GROUP | 문 실행 중 리소스 그룹 설정 | Global |
| SEMIJOIN, NO_SEMIJOIN | semijoin 전략에 영향을줍니다. MySQL 8.0.17부터는 안티 조인에도 적용됩니다. | Query block |
| SKIP_SCAN, NO_SKIP_SCAN | 스킵 스캔 최적화에 영향 | Table, index |
| SET_VAR | 문 실행 중 변수 설정 | Global |
| SUBQUERY | 구체화, IN-to-EXISTS 하위 쿼리 전략에 미치는 영향 | Query block |
최적화를 비활성화하면 옵티마이저가 최적화를 사용하지 못하게됩니다. 최적화를 사용한다는 것은 옵티마이저가 명령문 실행에 적용되는 경우 전략을 자유롭게 사용할 수 있다는 것을 의미하며, 옵티마이저가 반드시 이를 사용한다는 것이 아닙니다.
▶︎ 옵티 마이저 힌트 구문
MySQL은 SQL 문의 주석을 지원합니다. 옵티 마이저 힌트는 / * + ... * / 주석 내에 지정해야합니다. 즉, 옵티 마이저 힌트는 / *(주석 열기 순서) 뒤에 + 문자가있는 / * ... * / C 스타일 주석 구문의 변형을 사용합니다.
예제 :
/*+ BKA(t1) */
/*+ BNL(t1, t2) */
/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */
/*+ QB_NAME(qb2) */
+(플러스) 문자 다음에 공백이 허용됩니다.
구문 분석기는 SELECT, UPDATE, INSERT, REPLACE 및 DELETE 문의 초기 키워드 다음에 옵티마이저 힌트 주석을 인식합니다. 다음과 같은 상황에서 힌트가 허용됩니다.
+ 쿼리 및 데이터 변경 문의 시작시 :
SELECT /*+ ... */ ...
INSERT /*+ ... */ ...
REPLACE /*+ ... */ ...
UPDATE /*+ ... */ ...
DELETE /*+ ... */ ...
+ 쿼리 블록 시작시 :
(SELECT /*+ ... */ ... )
(SELECT ... ) UNION (SELECT /*+ ... */ ... )
(SELECT /*+ ... */ ... ) UNION (SELECT /*+ ... */ ... )
UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)
INSERT ... SELECT /*+ ... */ ...
+ EXPLAIN에서 머리말을 붙인 힌트가 가능한 문장에서. 예를 들면 다음과 같습니다.
EXPLAIN SELECT /*+ ... */ ...
EXPLAIN UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)
이는 EXPLAIN을 사용하여 옵티마이저 힌트가 실행 계획에 미치는 영향을 확인할 수 있다는 의미입니다. EXPLAIN 직후에 SHOW WARNINGS를 사용하여 힌트 사용 방법을 확인합니다. 다음 SHOW WARNINGS으로 표시되는 확장 EXPLAIN 출력은 사용 된 힌트를 나타냅니다. 무시된 힌트는 표시되지 않습니다.
힌트 설명에는 여러 힌트가 포함될 수 있지만 쿼리 블록에는 여러 힌트 설명이 포함될 수 없습니다. 다음의 쿼리는 유효합니다 :
SELECT /*+ BNL(t1) BKA(t2) */ ...
그러나 이 쿼리는 유효하지 않습니다.
SELECT /*+ BNL(t1) */ /* BKA(t2) */ ...
힌트 주석에 여러 힌트가 포함된 경우 중복 및 충돌 가능성이 있습니다. 다음과 같은 일반적인 지침이 적용됩니다. 특정힌트 유형의 경우 힌트 설명에 표시된대로 추가 규칙이 적용될 수 있습니다.
+ 중복 힌트 : /*+ MRR (idx1) MRR (idx1) */와 같은 힌트의 경우 MySQL은 첫번째 힌트를 사용하고 중복 힌트에 대한 경고를 표시합니다.
+ 충돌 힌트 : /*+ MRR (idx1) NO_MRR (idx1) */와 같은 힌트의 경우 MySQL은 첫 번째 힌트를 사용하고 두 번째 충돌하는 힌트에 대한 경고를 표시합니다.
쿼리 블록 이름은 식별자이며 유효한 이름과 인용 방법에 대한 일반적인 규칙을 따릅니다.
힌트 이름, 쿼리 블록 이름 및 전략 이름은 대소 문자를 구분하지 않습니다. 테이블 및 인덱스 이름에 대한 참조는 일반적인 식별자인 대/소문자 구분 규칙을 따릅니다.
▶︎ 테이블 레벨 옵티마이저 힌트
테이블 수준 힌트는 BNL (Best Nested-Loop) 및 BKA (Batched Key Access) 조인 처리 알고리즘의 사용에 영향을줍 니다. 이러한 힌트 유형은 특정 테이블 또는 쿼리 블록의 모든 테이블에 적용됩니다.
테이블 수준 힌트의 구문 :
hint_name([@query_block_name] [tbl_name [, tbl_name] ...])
hint_name([tbl_name@query_block_name [, tbl_name@query_block_name] ...])
구문은 다음 용어를 나타냅니다.
+ hint_name : 다음과 같은 힌트 이름들은 허용됩니다.
-BKA, NO_BKA : 지정된 테이블에 대해 BKA를 활성화 또는 비활성화합니다.
-BNL, NO_BNL : 지정된 테이블에 대해 BNL을 활성화 또는 비활성화합니다.
노트
BNL 또는 BKA 힌트를 사용하여 외부 조인의 내부 테이블에 대한 조인 버퍼링을 활성화하려면 외부 조인의 모든 내부 테이블에 대해 조인 버퍼링을 활성화해야합니다.
-tbl_name : 명령문에 사용된 테이블의 이름입니다. 힌트는 이름이 지정된 모든 테이블에 적용됩니다. 힌트가 테이블 이름을 지정하지 않는 경우 힌트가 발생하는 쿼리 블록의 모든 테이블에 적용됩니다.
테이블에 별칭이 있는 경우 힌트는 테이블 이름이 아닌 별칭을 참조해야합니다.
힌트의 테이블 이름은 스키마 이름으로 규정될 수 없습니다.
- query_block_name : 힌트가 적용되는 쿼리 블록입니다. 힌트에 선행 @query_block_name이 없으면 힌트가 발생하는 쿼리 블록에 적용됩니다. tbl_name@query_block_name 구문의 경우 힌트는 쿼리 블록 이름의 테이블 이름에 적용됩니다. 쿼리 블록에 이름을 할당하려면 쿼리 블록 이름 지정을 위한 최적화 힌트를 참조합니다.
예제:
SELECT /*+ NO_BKA(t1, t2) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3;
SELECT /*+ NO_BNL() BKA(t1) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3;
테이블 레벨 힌트는 보내는 테이블이 아닌 이전 테이블에서 레코드를 받는 테이블에 적용됩니다. 이 문법을 확인해봅니다 :
SELECT /*+ BNL(t2) */ FROM t1, t2;
옵티마이저가 t1을 먼저 처리하기로 선택한 경우 t2에서 읽기를 시작하기 전에 t1의 행을 버퍼링하여 블록 중첩 루프 조인을 t2에 적용합니다. 옵티마이저가 대신 t2를 먼저 처리하도록 선택하면 t2가 송신자 테이블이므로 힌트가 적용되지 않습니다.
▶︎ 인덱스 수준 옵티마이저 힌트
인덱스 레벨 힌트는 옵티마이저가 특정 테이블 또는 인덱스에 사용하는 인덱스 처리 전략에 영향을 줍니다. 이러한 힌트 유형은 ICP (Index Condition Pushdown), MRR (Multi-Range Read) 및 범위 최적화 사용에 영향을줍니다.
인덱스 수준 힌트의 구문 :
hint_name([@query_block_name] tbl_name [index_name [, index_name] ...])
hint_name(tbl_name@query_block_name [index_name [, index_name] ...])
구문은 다음 용어를 나타냅니다.
+ hint_name : 다음 힌트 이름이 허용됩니다.
-MRR, NO_MRR : 지정된 테이블에 대한 MRR을 활성화 또는 비활성화합니다. MRR 힌트는 InnoDB 및 MyISAM 테이블에만 적용됩니다.
-NO_ICP : 지정된 테이블에 대해 ICP를 비활성화합니다. 기본적으로 ICP는 후보 최적화 전략이므로 이를 활성화하기 위한 힌트가 없습니다.
-NO_RANGE_OPTIMIZATION : 지정된 테이블에 대한 인덱스 범위 액세스를 비활성화 합니다. 이 힌트는 또한 테이블에 대한 인덱스 병합 및 느슨한 인덱스 스캔을 비활성화 합니다. 기본적으로 범위 액세스는 후보 최적화 전략이므로 이를 활성화하기 위한 힌트는 없습니다.
이 힌트는 범위 수가 많고 범위 최적화에 많은 리소스가 필요한 경우에 유용 할 수 있습니다.
+ tbl_name : 힌트가 적용되는 테이블입니다.
+ index_name : 명명된 테이블의 인덱스 이름. 힌트는 이름이 지정된 모든 인덱스에 적용됩니다. 힌트에 인덱스 이름이 없으면 테이블의 모든 인덱스에 적용됩니다.
기본 키를 참조하려면 이름 PRIMARY를 사용합니다. 테이블의 인덱스 이름을 보려면 SHOW INDEX를 사용하십시오.
+ query_block_name : 힌트가 적용되는 쿼리 블록입니다. 힌트에 선행 @query_block_name이 없으면 힌트가 발생하는 쿼리 블록에 적용됩니다. tbl_name@query_block_name 구문의 경우 힌트는 쿼리 블록 이름의 테이블 이름에 적용됩니다. 쿼리 블록에 이름을 할당하려면 쿼리 블록 이름 지정을 위한 최적화 힌트를 참조하십시오.
예제 :
SELECT /*+ MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
FROM t3 WHERE f1 > 30 AND f1 < 33;
INSERT INTO t3(f1, f2, f3)
(SELECT /*+ NO_ICP(t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1
AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
▶︎ 서브 쿼리 옵티마이저 힌트
서브 쿼리 힌트는 세미조인 변환 사용여부 및 허용할 세미조인 전략, 세미조인을 사용하지 않는 경우, 서브 쿼리 구체화 또는 IN-to-EXISTS 변환 사용 여부에 영향을 줍니다.
semijoin 전략에 영향을주는 힌트 구문 :
hint_name([@query_block_name] [strategy [, strategy] ...])
구문은 다음 용어를 나타냅니다.
+ hint_name : 다음 힌트 이름이 허용됩니다.
- SEMIJOIN, NO_SEMIJOIN : 명명 된 semijoin 전략을 활성화 또는 비활성화합니다.
+ strategy : 활성화 또는 비활성화 할 semijoin 전략. DUPSWEEDOUT, FIRSTMATCH, LOOSESCAN, MATERIALIZATION과 같은 전략 이름이 허용됩니다.
SEMIJOIN 힌트의 경우 이름을 지정하지 않은 전략은 가능한 경우 optimizer_switch 시스템 변수에 따라 활성화된 전략을 기반으로 semijoin이 사용됩니다. 전략의 이름이 지정되었지만 명령문에 적용할 수 없는 경우 DUPSWEEDOUT이 사용됩니다.
NO_SEMIJOIN 힌트의 경우 이름이 지정된 전략이 없으면 semijoin이 사용되지 않습니다. 명령문에 적용 가능한 모든 전략을 배제하는 전략의 이름이 지정된 경우 DUPSWEEDOUT이 사용됩니다.
DUPSWEEDOUT을 사용하지 않으면 경우에 따라 옵티마이저가 최적이 아닌 쿼리 계획을 생성 할 수 있습니다. 이는 그리디(탐욕) 검색중 휴리스틱 정리로 인해 발생하며, optimizer_prune_level = 0을 설정하면 피할 수 있습니다.
예제 :
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
서브쿼리 구체화(MATERIALIZATION) 또는 IN-to-EXISTS 변환 사용 여부에 영향을 주는 힌트 구문 :
SUBQUERY([@query_block_name] strategy)
힌트 이름은 항상 SUBQUERY입니다.
SUBQUERY 힌트의 경우 다음 전략 값이 허용됩니다 : INTOEXISTS, MATERIALIZATION.
예제 :
SELECT id, a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ a FROM t1) FROM t2;
SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ a FROM t1);semijoin 및 SUBQUERY 힌트의 경우 선행 @query_block_name은 힌트가 적용되는 쿼리 블록을 지정합니다. 힌트에 선행 @query_block_name이 없으면 힌트가 발생하는 쿼리 블록에 적용됩니다. 쿼리 블록에 이름을 할당하려면 쿼리 블록 이름 지정을 위한 최적화 힌트를 참조합니다.
힌트 주석에 여러 개의 하위 쿼리 힌트가 포함된 경우 첫 번째 힌트가 사용됩니다. 해당 유형에 대한 다음과 같은 힌트가 있으면 경고가 표시됩니다. 다른 유형의 다음 힌트는 자동으로 무시됩니다.
▶︎ 명령문 실행 시간 최적화 힌트
MAX_EXECUTION_TIME 힌트는 SELECT 문에만 허용됩니다. 서버가 종료하기전에 명령문을 실행할 수있는 시간에 제한 N (밀리 초 단위의 제한 시간 값)을 설정합니다.
MAX_EXECUTION_TIME (N)
시간이 1 초 (1000 밀리 초) 인 예 :
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ...
MAX_EXECUTION_TIME (N)힌트는 명령문 실행 시간 초과를 N밀리 초로 설정합니다. 이 옵션이 없거나 N이 0이면 max_execution_time 시스템 변수에 의해 설정된 명령문 시간 종료가 적용됩니다.
MAX_EXECUTION_TIME 힌트는 다음과 같이 적용됩니다.
-결합 또는 서브 쿼리가 있는 명령문과 같이 여러 개의 SELECT 키워드가 있는 명령문의 경우 MAX_EXECUTION_TIME은 전체 명령문에 적용되며 첫번째 SELECT 다음에 나타나야합니다.
-읽기 전용 SELECT 문에 적용됩니다. 읽기 전용이 아닌 명령문은 데이터를 부작용으로 수정하는 저장 함수를 호출하는 명령문입니다.
-저장된 프로그램의 SELECT 문에는 적용되지 않으며 무시됩니다.
▶︎ 네이밍(Naming) 쿼리 블록에 대한 옵티마이저 힌트
테이블 레벨, 인덱스 레벨 및 서브쿼리 옵티마이저 힌트를 사용하면 특정 쿼리 블록의 이름을 인수 구문의 일부로 지정할 수 있습니다. 이러한 이름을 작성하려면 QB_NAME 힌트를 사용합니다. 이 힌트는 이름이 발생하는 쿼리 블록에 이름을 지정합니다.
QB_NAME(name)
QB_NAME 힌트를 사용하여 다른 힌트가 적용되는 쿼리 블록을 명확하게 표현할 수 있습니다. 또한 복잡한 명령문을보다 쉽게 이해할 수 있도록 모든 non-query(비 질의) 블록 이름 힌트를 단일 힌트 주석 내에 지정할 수 있습니다. 다음과 같은 명령문이 있습니다.
SELECT ...
FROM (SELECT ...
FROM (SELECT ... FROM ...)) ...
QB_NAME 힌트는 명령문의 쿼리 블록에 이름을 지정합니다.
SELECT /*+ QB_NAME(qb1) */ ...
FROM (SELECT /*+ QB_NAME(qb2) */ ...
FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...
그런 다음 다른 힌트에서 해당 이름을 사용하여 적절한 쿼리 블록을 참조 할 수 있습니다.
SELECT /*+ QB_NAME(qb1) MRR(@qb1 t1) BKA(@qb2) NO_MRR(@qb3t1 idx1, id2) */ ...
FROM (SELECT /*+ QB_NAME(qb2) */ ...
FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...
결과 효과는 다음과 같습니다.
+ MRR(@ qb1 t1)은 쿼리 블록 qb1의 테이블 t1에 적용됩니다.
+ BKA(@ qb2)는 쿼리 블록 qb2에 적용됩니다.
+ NO_MRR (@ qb3 t1 idx1, id2)은 쿼리 블록 qb3에서 테이블 t1의 인덱스 idx1 및 idx2에 적용됩니다.
쿼리 블록 이름은 식별자이며 유효한 이름과 인용 방법에 대한 일반적인 규칙을 따릅니다. 예를 들어, 공백이 포함된 쿼리 블록 이름은 따옴표로 묶어야합니다.
SELECT /*+ BKA(@`my hint name`) */ ...
FROM (SELECT /*+ QB_NAME(`my hint name`) */ ...) ...
ANSI_QUOTES SQL 모드가 사용 가능한 경우 큰 따옴표 안에 쿼리 블록 이름을 인용 할 수도 있습니다.
SELECT /*+ BKA(@"my hint name") */ ...
FROM (SELECT /*+ QB_NAME("my hint name") */ ...) ...
■ 인덱스 힌트
인덱스 힌트는 쿼리 처리 중에 인덱스를 선택하는 방법에 대한 옵티마이저 정보를 제공합니다. 여기에 설명 된 인덱스 힌트는 위에 설명된 옵티 마이저 힌트와 다릅니다. 인덱스 및 최적화 힌트는 개별적으로 또는 함께 사용될 수 있습니다.
인덱스 힌트는 SELECT 및 UPDATE 문에만 적용됩니다.
인덱스 힌트는 테이블 이름 뒤에 지정됩니다. 인덱스 힌트를 포함하여 개별 테이블을 참조하는 구문은 다음과 같습니다.
tbl_name [[AS] alias] [index_hint_list]
index_hint_list:
index_hint [index_hint] ...
index_hint:
USE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
| {IGNORE|FORCE} {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
index_list:
index_name [, index_name] ...
USE INDEX (index_list) 힌트는 MySQL에게 인덱스 이름중 하나만 사용하여 테이블에서 행을 찾도록 지시합니다. 대체 구문 IGNORE INDEX (index_list)는 MySQL에게 특정 인덱스를 사용하지 않도록 지시합니다. 이 힌트는 EXPLAIN이 MySQL이 가능한 인덱스 목록에서 잘못된 인덱스를 사용하고 있음을 나타내는 경우에 유용합니다.
FORCE INDEX 힌트는 USE INDEX (index_list)와 같은 역할을 하며 테이블 스캔이 매우 비싸다고(비용이 높다고) 가정합니다. 다시 말해, 테이블 스캔은 인덱스 이름중 하나를 사용하여 테이블에서 행을 찾는 방법이 없는 경우에만 사용됩니다.
각 힌트에는 열 이름이 아닌 인덱스 이름이 필요합니다. 기본키를 참조하려면 이름 PRIMARY를 사용합니다. 테이블의 인덱스 이름을 보려면 SHOW INDEX 문 또는 INFORMATION_SCHEMA.STATISTICS 테이블을 사용합니다.
index_name 값은 전체 인덱스 이름일 필요는 없습니다. 인덱스 이름의 명확한 접두사일 수 있습니다. 접두사가 모호하면 오류가 발생합니다.
예제 :
SELECT * FROM table1 USE INDEX (col1_index,col2_index)
WHERE col1=1 AND col2=2 AND col3=3;
SELECT * FROM table1 IGNORE INDEX (col3_index)
WHERE col1=1 AND col2=2 AND col3=3;
인덱스 힌트의 구문에는 다음과 같은 특징이 있습니다.
+ USE INDEX에 대해 index_list를 생략하는 것이 구문상 유효합니다. 이는 "인덱스 사용 안함"을 의미합니다. FORCE INDEX 또는 IGNORE INDEX에 대해 index_list를 생략하면 구문오류가 발생합니다.
+ 힌트에 FOR절을 추가하여 인덱스 힌트의 범위를 지정할 수 있습니다. 이를 통해 다양한 쿼리 처리 단계에 대한 실행 계획의 옵티마이저 선택을 보다 세밀하게 제어 할 수 있습니다. MySQL이 테이블에서 행을 찾는 방법과 조인을 처리하는 방법을 결정할때 사용되는 인덱스에만 영향을 주려면 FOR JOIN을 사용합니다. 행 정렬 또는 그룹화에 대한 인덱스 사용에 영향을 주려면 FOR ORDER BY 또는 FOR GROUP BY를 사용합니다.
+ 여러 인덱스 힌트를 지정할 수 있습니다.
SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;
여러 힌트에서 동일한 인덱스의 이름을 지정하는 것은 오류가 아닙니다 (같은 힌트 내에서도).
SELECT * FROM t1 USE INDEX (i1) USE INDEX (i1,i1);
그러나 동일한 테이블에 USE INDEX와 FORCE INDEX를 혼합하면 오류가 발생합니다.
SELECT * FROM t1 USE INDEX FOR JOIN (i1) FORCE INDEX FOR JOIN (i2);
인덱스 힌트에 FOR 절이 없으면 힌트의 범위는 명령문의 모든 부분에 적용됩니다. 예를 들면 다음과 같습니다.
IGNORE INDEX (i1)
이 힌트 조합과 같습니다.
IGNORE INDEX FOR JOIN (i1)
IGNORE INDEX FOR ORDER BY (i1)
IGNORE INDEX FOR GROUP BY (i1)
MySQL 5.0에서 FOR 절이 없는 힌트 범위는 행 검색에만 적용되었습니다. FOR 절이 없을 때 서버가 이전 동작을 사용하게 하려면 서버 시작시 이전 시스템 변수를 활성화합니다. 복제 설정에서 이 변수를 활성화하는 데 주의합니다. 명령문 기반 바이너리 로깅의 경우 마스터 및 슬레이브에 대해 다른 모드를 사용하면 복제 오류가 발생할 수 있습니다.
인덱스 힌트가 처리되면 유형 (USE, FORCE, IGNORE) 및 범위 (FOR JOIN, FOR ORDER BY, FOR GROUP BY)별로 단일 목록으로 수집됩니다. 예를 들면 다음과 같습니다.
SELECT * FROM t1
USE INDEX () IGNORE INDEX (i2) USE INDEX (i1) USE INDEX (i2);
다음과 같습니다.
SELECT * FROM t1
USE INDEX (i1,i2) IGNORE INDEX (i2);
그러면 인덱스 힌트가 다음 순서로 각 범위에 적용됩니다.
1. {USE | FORCE} INDEX가 있으면 적용됩니다. 그렇지 않은 경우, 옵티마이저가 결정한 색인 세트가 사용됩니다.
2. IGNORE INDEX는 이전 단계의 결과에 적용됩니다. 예를 들어 다음 두 쿼리는 동일합니다.
SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX (i2) USE INDEX (i2);
SELECT * FROM t1 USE INDEX (i1);
FULLTEXT 검색의 경우 인덱스 힌트는 다음과 같이 작동합니다.
+ 자연어 모드 검색의 경우 색인 힌트가 자동으로 무시됩니다. 예를 들어 경고없이 IGNORE INDEX (i1)가 무시되고 인덱스가 계속 사용됩니다.
+ 부울 모드 검색의 경우 FOR ORDER BY 또는 FOR GROUP BY가 포함된 인덱스 힌트는 자동으로 무시됩니다. FOR JOIN이 있거나 FOR 한정자가 없는 인덱스 힌트가 적용됩니다. FULLTEXT가 아닌 검색에 힌트를 적용하는 방법과 달리 힌트는 쿼리 실행의 모든 단계 (행 찾기 및 검색, 그룹화 및 순서 지정)에 사용됩니다. FULLTEXT가 아닌 인덱스에 대한 힌트가 제공되는 경우에도 마찬가지입니다.
예를 들어 다음 두 쿼리는 동일합니다.
SELECT * FROM t
USE INDEX (index1)
IGNORE INDEX (index1) FOR ORDER BY
IGNORE INDEX (index1) FOR GROUP BY
WHERE ... IN BOOLEAN MODE ... ;
SELECT * FROM t
USE INDEX (index1)
WHERE ... IN BOOLEAN MODE ... ;
■ 옵티마이저 비용 모델
실행 계획을 생성하기 위해 옵티마이저는 쿼리 실행 중에 발생하는 다양한 작업의 비용 추정치를 기반으로 하는 비용 모델을 사용합니다. 옵티 마이저에는 실행 계획에 대한 결정을 내리기 위해 컴파일된 기본 "비용 상수"세트가 있습니다.
옵티마이저는 또한 실행 계획 구성 중에 사용할 비용 추정 데이터베이스를 가지고 있습니다. 이러한 추정치는 mysql 시스템 데이터베이스의 server_cost 및 engine_cost 테이블에 저장되며 언제든지 구성할 수 있습니다. 이러한 테이블의 목적은 쿼리 실행 계획에 도달하려고 할 때 옵티마이저가 사용하는 비용 추정을 쉽게 조정할 수 있도록하는 것입니다.
+ 비용 모델 일반 운영
+ 비용 모델 데이터베이스
+ 비용 모델 데이터베이스 변경
▶︎ 비용 모델 일반 운영
구성 가능한 옵티마이저 비용 모델은 다음과 같이 작동합니다.
+ 서버는 시작시 비용 모델 테이블을 메모리로 읽고 런타임시 인 메모리 값을 사용합니다. 표에 지정된 NULL이 아닌 비용 견적은 해당 컴파일된 기본 비용 상수보다 우선합니다. NULL 추정값은 옵티마이저가 컴파일된 기본값을 사용하도록 지시합니다.
+ 런타임에 서버는 비용 테이블을 다시 읽을 수 있습니다. 스토리지 엔진이 동적으로 로드되거나 FLUSH OPTIMIZER_COSTS 문이 실행될 때 발생합니다.
+ 비용 테이블을 통해 서버 관리자는 테이블의 항목을 변경하여 비용 견적을 쉽게 조정할 수 있습니다. 항목 비용을 NULL로 설정하여 기본값으로 쉽게 되돌릴 수도 있습니다. 옵티마이저는 인 메모리 비용 값을 사용하므로 테이블에 대한 변경 사항 뒤에 FLUSH OPTIMIZER_COSTS가 적용되어야 합니다.
+ 클라이언트 세션이 시작될때 현재의 메모리 내 비용 추정치는 해당 세션이 끝날 때까지 해당 세션에 적용됩니다. 특히, 서버가 비용 테이블을 다시 읽는 경우 변경된 추정값은 이후에 시작된 세션에만 적용됩니다. 기존 세션은 영향을 받지 않습니다.
+ 비용 테이블은 주어진 서버 인스턴스에 따라 다릅니다. 서버는 비용 테이블 변경 사항을 복제 슬레이브에 복제하지 않습니다.
▶︎ 비용 모델 데이터베이스
옵티마이저 비용 모델 데이터베이스는 mysql 시스템 데이터베이스에서 쿼리 실행 중에 발생하는 작업에 대한 비용 추정 정보를 포함하는 두 개의 테이블로 구성됩니다.
+ server_cost : 일반 서버 운영을위한 최적화 비용 추정
+ engine_cost : 특정 스토리지 엔진과 관련된 작업에 대한 옵티마이저 비용 추정
server_cost 테이블에는 다음 열이 포함되어 있습니다.
+ cost_name
비용 모델에 사용된 비용 견적의 이름입니다. 이름은 대소 문자를 구분하지 않습니다. 서버가 이 테이블을 읽을 때 비용 이름을 인식하지 못하면 오류 로그에 경고를 기록합니다.
+ cost_value
비용 추정치입니다. 값이 NULL이 아닌 경우 서버는 이 값을 비용으로 사용합니다. 그렇지 않으면 기본 추정값 (컴파일 된 값)을 사용합니다. DBA는이 열을 업데이트하여 비용 견적을 변경할 수 있습니다. 서버가 이 테이블을 읽을때 비용 값이 유효하지 않음 (양이 아님)을 발견하면 오류 로그에 경고를 기록합니다.
NULL을 지정하는 항목에 대한 기본 비용 추정을 대체하려면 비용을 NULL이 아닌 값으로 설정합니다. 기본값으로 되돌리려면 값을 NULL로 설정합니다. 그런 다음 FLUSH OPTIMIZER_COSTS를 실행하여 비용 테이블을 다시 읽도록 서버에 지시합니다.
+ last_update
마지막 행 업데이트 시간
+ comment
비용 추정과 관련된 설명 주석입니다. DBA는이 열을 사용하여 비용 추정 행이 특정값을 저장하는 이유에 대한 정보를 제공 할 수 있습니다.
server_cost 테이블의 기본키는 cost_name 열이므로 비용 산정에 대해 여러 항목을 작성할 수 없습니다.
서버는 server_cost 테이블에 대한 다음 cost_name 값을 인식합니다.
+ disk_temptable_create_cost (기본값 40.0), disk_temptable_row_cost (기본 1.0)
디스크 기반 스토리지 엔진 (InnoDB 또는 MyISAM)에 저장된 내부적으로 생성된 임시 테이블의 비용 추정치입니다. 이 값을 늘리면 내부 임시 테이블 사용에 대한 비용 추정치가 증가하고 옵티마이저가 더 적은 쿼리 계획을 선호합니다.
해당 메모리 매개 변수의 기본값(memory_temptable_create_cost, memory_temptable_row_cost)에 비해 이러한 디스크 매개 변수의 기본값이 클수록 디스크 기반 테이블 처리 비용이 더 많이 든다는 것을 나타냅니다.
+ key_compare_cost (default 0.1)
레코드 키를 비교하는 비용. 이 값을 늘리면 많은 키를 비교하는 쿼리 계획이 더 비싸집니다. 예를 들어, 파일 정렬을 수행하는 쿼리 계획은 인덱스를 사용하여 정렬을 피하는 쿼리 계획에 비해 상대적으로 비쌉니다.
+ memory_temptable_create_cost (default 2.0), memory_temptable_row_cost (default 0.2)
MEMORY 스토리지 엔진에 저장된 내부적으로 작성된 임시 테이블의 예상 비용입니다. 이 값을 늘리면 내부 임시 테이블 사용에 대한 비용 추정치가 증가하고 옵티마이저가 더 적은 쿼리 계획을 선호합니다.
해당 디스크 파라미터(disk_temptable_create_cost, disk_temptable_row_cost)의 기본값에 비해 이러한 메모리 파라미터의 기본값이 작을수록 메모리 기반 테이블 처리 비용이 적게 드는 것을 반영합니다.
+ row_evaluate_cost (default 0.2)
기록 조건 평가 비용. 이 값을 늘리면 적은 수의 행을 검사하는 쿼리 계획에 비해 많은 행을 검사하는 쿼리 계획이 더 비싸게됩니다. 예를 들어, 테이블 스캔은 더 적은 행을 읽는 범위 스캔에 비해 상대적으로 더 비쌉니다.
engine_cost 테이블에는 다음 열이 포함됩니다.
+ engine_name
이 비용 견적이 적용되는 스토리지 엔진의 이름입니다. 이름은 대소 문자를 구분하지 않습니다. 이 값이 기본값인 경우 자체 이름이 없는 항목이 모든 스토리지 엔진에 적용됩니다. 서버가 이 테이블을 읽을 때 엔진 이름을 인식하지 못하면 오류 로그에 경고를 기록합니다.
+ device_type
이 비용 견적이 적용되는 장치 유형입니다. 이 열은 하드 디스크 드라이브 대 솔리드 스테이트 드라이브와 같은 다른 스토리지 장치 유형에 대해 다른 비용 견적을 지정하기 위한 것입니다. 현재 이 정보는 사용되지 않으며 0이 유일하게 허용되는 값입니다.
+ cost_name
비용 모델에 사용된 비용 견적의 이름입니다. 이름은 대소 문자를 구분하지 않습니다. 서버가 이 테이블을 읽을 때 비용 이름을 인식하지 못하면 오류 로그에 경고를 기록합니다.
+ cost_value
비용 추정치입니다. 값이 NULL이 아닌 경우 서버는 이 값을 비용으로 사용합니다. 그렇지 않으면 기본 추정값 (컴파일 된 값)을 사용합니다. DBA는이 열을 업데이트하여 비용 견적을 변경할 수 있습니다. 서버가 이 테이블을 읽을때 비용 값이 유효하지 않음 (양이 아님)을 발견하면 오류 로그에 경고를 기록합니다.
NULL을 지정하는 항목에 대한 기본 비용 추정을 대체하려면 비용을 NULL이 아닌 값으로 설정합니다. 기본값으로 되돌리려면 값을 NULL로 설정합니다. 그런 다음 FLUSH OPTIMIZER_COSTS를 실행하여 비용 테이블을 다시 읽도록 서버에 지시합니다.
+ last_update
마지막 행 업데이트 시간
+ comment
비용 추정과 관련된 설명 주석입니다. DBA는이 열을 사용하여 비용 추정 행이 특정값을 저장하는 이유에 대한 정보를 제공 할 수 있습니다.
engine_cost 테이블의 기본 키는 (cost_name, engine_name, device_type) 열을 포함하는 튜플이므로 해당 열의 값 조합에 대해 여러 항목을 만들 수 없습니다.
서버는 engine_cost 테이블에 대한 다음 cost_name 값을 인식합니다.
+ io_block_read_cost (default 1.0)
디스크에서 인덱스 또는 데이터 블록을 읽는 비용. 이 값을 늘리면 디스크 블록을 적게 읽는 쿼리 계획에 비해 많은 디스크 블록을 읽는 쿼리 계획이 더 비싸게 됩니다. 예를 들어, 테이블 스캔은 더 적은 블록을 읽는 범위 스캔에 비해 상대적으로 더 비쌉니다.
+ memory_block_read_cost (default 1.0)
io_block_read_cost와 유사하지만 인 메모리 데이터베이스 버퍼에서 인덱스 또는 데이터 블록을 읽는 비용을 나타냅니다.
io_block_read_cost 및 memory_block_read_cost 값이 다른 경우 실행 계획은 동일한 쿼리의 두 실행간에 변경될 수 있습니다. 메모리 액세스 비용이 디스크 액세스 비용보다 저렴하다고 가정합니다. 이 경우, 데이터를 버퍼 풀로 읽기 전에 서버를 시작할 때 데이터가 메모리에 있기 때문에 쿼리가 실행된 후와 다른 계획을 얻을 수 있습니다.
▶︎ 비용 모델 데이터베이스 변경
비용 모델 매개 변수를 기본값에서 변경하려는 DBA의 경우 값을 두 배로 늘리거나 반으로 줄이고 효과를 측정해야 합니다.
io_block_read_cost 및 memory_block_read_cost 매개 변수를 변경하면 가치있는 결과를 얻을 수 있습니다. 이 매개 변수 값을 사용하면 데이터 액세스 방법에 대한 비용 모델이 다른 소스에서 정보를 읽는 비용을 고려할 수 있습니다. 즉, 디스크에서 정보를 읽는 비용과 이미 메모리 버퍼에 있는 정보를 읽는 비용. 예를 들어 io_block_read_cost를 memory_block_read_cost보다 큰 값으로 설정하면 다른 모든 항목이 동일하므로 옵티마이저는 메모리에 이미 보유된 정보를 읽는 쿼리 계획을 디스크에서 읽어야하는 계획보다 선호합니다.
이 예는 io_block_read_cost의 기본값을 변경하는 방법을 보여줍니다.
UPDATE mysql.engine_cost
SET cost_value = 2.0
WHERE cost_name = 'io_block_read_cost';
FLUSH OPTIMIZER_COSTS;
이 예는 InnoDB 스토리지 엔진에 대해서만 io_block_read_cost 값을 변경하는 방법을 보여줍니다.
INSERT INTO mysql.engine_cost
VALUES ('InnoDB', 0, 'io_block_read_cost', 3.0,
CURRENT_TIMESTAMP, 'Using a slower disk for InnoDB');
FLUSH OPTIMIZER_COSTS;
'Databases > MySQL' 카테고리의 다른 글
| [MySQL] Table Partitioning-파티셔닝 타입 (0) | 2020.08.20 |
|---|---|
| [MySQL] Table Partitioning - 소개 (0) | 2020.08.17 |
| [MySQL] 서브 쿼리, 파생(Derived) 테이블 및 뷰 참조 최적화 (0) | 2020.08.13 |
| [MySQL] 인덱스 병합 최적화 (0) | 2020.08.10 |
| [MySQL] 인덱스 확장 사용 (0) | 2020.08.10 |