MySQL 8.0 에서만 특정 쿼리가 2초만에 끝나는 use index 힌트를 무시하고 자꾸 600초 가량이 걸리는 Full Table Scan 을 선택해서 부하 테스트가 잘 안됐다.

기존 원본 버전인 MariaDB 10.0 버전에서는 원하는대로 use index 만으로도 원하는 인덱스를 잘 사용했는데,

타겟 버전인 MySQL 8.0 에서만 이슈가 있던 부분이다. ( force index 는 잘 됨 )

그래서 MySQL 8.0 에서는 optimizer 부분이 어떻게 바뀌었는지 Optimizer Trace 를 살펴보면서 정리했다.

Cost-based Query Optimization

1. 기본 컨셉

Assign Cost to Operations
Assign Cost to partial or alternative plans
Search for plan with lowest cost

2. 주요 요소

Index and Access Method
- Table Scan
- Index Scan
- Range Scan
- Index lookup
Join order
Join buffering strategy
Subquery strategy

3. Cost Estimates

Cost Unit : read a random data page from disk
Main Factors
- IO Cost
  - pages read from table
  - pages read from index
- CPU Cost
  - Evaluating query conditions
  - Comparing Keys/ Records
  - Sorting keys

Main Cost Constants

cost	MySQl 5.7	MySQl 8.0
Read a random disk page	1.0	1.0
Read a data page from Memory buffer	1.0	0.25
Evaluate query condition	0.2	0.1
Compare keys/records	0.1	0.05

Optimizer Trace

아래 옵션들을 설정한 후에, Explain 을 실행하면 optimizer_trace 를 남길 수 있다.

https://dev.mysql.com/doc/internals/en/tracing-example.html

// Optimizer_trace 를 활성화 시킨다.
SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;

// Optimizer_trace Output 의 Max Length 를 증가 시킨다.
SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000; 

// 해당 테이블에서 저장된 Optimizer_trace 를 확인할 수 있다.
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

옵티마이저의 실행 단계

1. Logical Transformations (Negation elimination / Equality and constant propagation / Evaluation of constant expressions / Substitution of generated columns)

부정 조건 제거 같이 집합들을 재조립? 하는 과정이라고 보면 될 듯
Execution Plan 을 실제 정하기 전에 불필요한 조건들을 발라내는 과정

실제 해당 과정이 실행되는 trace 부분은 아래와 같다.

"join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`t1`.`id` = `t2`.`id`) and (`t2`.`id` = 9) and (((`t1`.`id` <= 10) and (`t2`.`d1` <= 3)) or ((`t1`.`d1` = (`t2`.`d1` + 7)) and (`t2`.`d1` = 5))))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((((9 <= 10) and (`t2`.`d1` <= 3)) or ((`t1`.`d1` = (`t2`.`d1` + 7)) and (`t2`.`d1` = 5))) and multiple equal(9, `t1`.`id`, `t2`.`id`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((((9 <= 10) and (`t2`.`d1` <= 3)) or ((`t1`.`d1` = (`t2`.`d1` + 7)) and (`t2`.`d1` = 5))) and multiple equal(9, `t1`.`id`, `t2`.`id`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(((`t2`.`d1` <= 3) or ((`t1`.`d1` = (`t2`.`d1` + 7)) and (`t2`.`d1` = 5))) and multiple equal(9, `t1`.`id`, `t2`.`id`))"
                }
              ] /* steps */
            } /* condition_processing */
          },

ex)
SELECT *
FROM t1 t1, t1 t2
WHERE t1.id = t2.id
  AND t2.id = 9
  AND (NOT (t1.id > 10 OR t2.d1 > 3)
        OR (t1.d1 = t2.d1 + 7 AND t2.d1=5))

WHERE t1.a = t2.a
  AND t2.a = 9
  AND (NOT (t1.a > 10 OR t2.b > 3)
        OR (t1.b = t2.b + 7
          AND t2.b=5))

WHERE t1.a = 9
  AND t2.a = 9
  AND ((t1.a <= 10 AND t2.b <= 3)
      OR (t1.b = t2.b + 7
          AND t2.b = 5))

WHERE t1.a = 9
  AND t2.a = 9
  AND ((9 <= 10 AND t2.b <= 3)
      OR (t1.b = 5 + 7
          AND t2.b = 5))

* finally
WHERE t1.a = 9
  AND t2.a = 9
  AND (t2.b <= 3
       OR (t1.b = 12
           AND t2.b = 5))

2. Prepare for cost-based Optimization (Ref access analysis / Range access analysis / Estimation of condition fan out / Constant table detection)

액세스 분석 / range 액세스 분석 / 조건 평가 / 상수 테이블 감지

1) Ref Access Analysis

Determine which indexes that can be used for index lookup in a join
즉 조인 구문에서 인덱스 조회에 사용할 수 있는 인덱스를 결정한다.

트레이스 상 결정하는 부분은 "ref_optimizer_key_uses" 의 value 들이다.

          {
            "ref_optimizer_key_uses": [
              {
                "table": "`t1`",
                "field": "id",
                "equals": "9",
                "null_rejecting": false
              },
              {
                "table": "`t1` `t2`",
                "field": "id",
                "equals": "9",
                "null_rejecting": false
              }
            ] /* ref_optimizer_key_uses */
          },

2) Range Access Analysis

table scan 의 cost
- 읽어야하는 page 개수에 기반
index scan alternatives 의 cost
- Covering Index scan
- Index range scan
- Index merge
- Index for grouping
- Skip scan (new in 8.0)
- Loose-index scan

트레이스 Range Access Analysis 하는 부분

"rows_estimation": [
{
"table": "`t1` ,
"const_keys_added": {
"keys": [
"PRIMARY",
"mrPayEnd",
...
] /* keys */,
"cause": "group_by"
} /* const_keys_added */,
"range_analysis": {
"table_scan": {
"rows": 34401680,
"cost": 4.32e6
} /* table_scan */,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "mrAppointmentDate",
"usable": true,
"key_parts": [
"mrAppointmentDate",
"mrIdx"
] /* key_parts */
},
...
...
...
{
"index": "memReservation",
"usable": false,
"cause": "not_applicable"
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"potential_group_range_indexes": [
{
"index": "mrAppointmentDate",
"usable": false,
"cause": "not_covering"
}
] /* potential_group_range_indexes */
} /* group_index_range */,
"skip_scan_range": {
"chosen": false,
"cause": "has_group_by"
} /* skip_scan_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "mrAppointmentDate",
"ranges": [
"2019-12-23 <= mrAppointmentDate <= 2019-12-26"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 583430,
"cost": 369674,
"chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,

"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "mrAppointmentDate",
"rows": 583430,
"ranges": [
"2019-12-23 <= mrAppointmentDate <= 2019-12-26"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 583430,
"cost_for_plan": 369674,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */

Table Scan 과 Alternatives 들의 Cost 를 계산한다.

※ Table Scan
 "range_analysis": {
                  "table_scan": {
                    "rows": 34401680,
                    "cost": 4.32e6
                  } /* table_scan */,


※ Index Scan Alternatives

                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "mrAppointmentDate",
                        "ranges": [
                          "2019-12-23 <= mrAppointmentDate <= 2019-12-26"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 583430,
                        "cost": 369674,
                        "chosen": true
                      }
                    ] /* range_scan_alternatives */,

3. Join order and access methods (Join order / Access method selection)

Goal : Find the best JOIN ordering
Strategy
- Start with all 1-table plans
  - size 와 key dependency 에 따라 sorting
  - 추후 sorting 된 순으로 cost 를 계산하게 됨
- Expand each plan with remaining tables
  - Depth-first
- If ("cost of partial plan" > "cost of best plan") 이면 plan 을 프루닝 처리
- Heuristic pruning
  - 선택될 가능성이 낮은 plan 을 프루닝 처리
  - 대신, 드물게 최적의 플랜을 놓칠 수도 있음.
    ※ 만약 끄고 싶다면 Heuristic pruniung 을 끄고 싶다면 "set optimizer_prune_level = 0" 옵션을 적용하면 된다.

트레이스 의 Join ordering 하는 부분

"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`pensionRevInfo` `PRI`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "INDEX",
"usable": false,
"chosen": false
},
{
"access_type": "range",
"rows": 1.89e6,
"cost": 758201,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 758201,
"rows_for_plan": 1.89e6,
"rest_of_plan": [
{
"plan_prefix": [
"`pensionRevInfo` `PRI`"
] /* plan_prefix */,
"table": "`pensionPurchase` `PP`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIIDX",
"rows": 1,
"cost": 1.89e6,
"chosen": true
},
{
"access_type": "scan",
"cause": "covering_index_better_than_full_scan",
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 3.02e6,
"rows_for_plan": 1.89e6,
"rest_of_plan": [
{
"plan_prefix": [
"`pensionRevInfo` `PRI`",
"`pensionPurchase` `PP`"
] /* plan_prefix */,
"table": "`pensionRev` `R` IGNORE INDEX (`INDEX_8`)",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 1,
"cost": 1.89e6,
"chosen": true
},
{
"access_type": "scan",
"rows": 2.68e6,
"cost": 1.5e12,
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"added_to_eq_ref_extension": true,
"cost_for_plan": 5.29e6,
"rows_for_plan": 1.89e6,
"chosen": true
}
] /* rest_of_plan */
}
] /* rest_of_plan */
},
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`pensionRev` `R` IGNORE INDEX (`INDEX_8`)",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"access_type": "scan",
"rows": 3.58e6,
"cost": 819297,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 819297,
"rows_for_plan": 3.58e6,
"rest_of_plan": [
{
"plan_prefix": [
"`pensionRev` `R` IGNORE INDEX (`INDEX_8`)"
] /* plan_prefix */,
"table": "`pensionRevInfo` `PRI`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "INDEX",
"rows": 1,
"cost": 3.58e6,
"chosen": true
},
{
"access_type": "range",
"rows": 1.42e6,
"cost": 2.7e12,
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 5.11e6,
"rows_for_plan": 3.58e6,
"rest_of_plan": [
{
"plan_prefix": [
"`pensionRev` `R` IGNORE INDEX (`INDEX_8`)",
"`pensionRevInfo` `PRI`"
] /* plan_prefix */,
"table": "`pensionPurchase` `PP`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIIDX",
"rows": 1,
"cost": 3.58e6,
"chosen": true
},
{
"access_type": "scan",
"cause": "covering_index_better_than_full_scan",
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"added_to_eq_ref_extension": true,
"cost_for_plan": 9.41e6,
"rows_for_plan": 3.58e6,
"pruned_by_cost": true
},
{
"plan_prefix": [
"`pensionRev` `R` IGNORE INDEX (`INDEX_8`)",
"`pensionRevInfo` `PRI`"
] /* plan_prefix */,
"table": "`pensionPurchase` `PP`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIIDX",
"rows": 1,
"cost": 3.58e6,
"chosen": true
},
{
"access_type": "scan",
"cause": "covering_index_better_than_full_scan",
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 9.41e6,
"rows_for_plan": 3.58e6,
"pruned_by_cost": true
}
] /* rest_of_plan */
}
] /* rest_of_plan */
}
] /* considered_execution_plans */

Best Path 와 Rest Path

※ Best Path 선정

                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "INDEX",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "range",
                      "rows": 1.89e6,
                      "cost": 758201,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "cost_for_plan": 758201,
                "rows_for_plan": 1.89e6,

※ Rest Path

                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`pensionRevInfo` `PRI`"
                    ] /* plan_prefix */,
                    "table": "`pensionPurchase` `PP`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "PRIIDX",
                          "rows": 1,
                          "cost": 1.89e6,
                          "chosen": true
                        },
                        {
                          "access_type": "scan",
                          "cause": "covering_index_better_than_full_scan",
                          "chosen": false
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "cost_for_plan": 3.02e6,
                    "rows_for_plan": 1.89e6,
                    "rest_of_plan": [
                      {
                        "plan_prefix": [
                          "`pensionRevInfo` `PRI`",
                          "`pensionPurchase` `PP`"
                        ] /* plan_prefix */,
                        "table": "`pensionRev` `R` IGNORE INDEX (`INDEX_8`)",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "ref",
                              "index": "PRIMARY",
                              "rows": 1,
                              "cost": 1.89e6,
                              "chosen": true
                            },
                            {
                              "access_type": "scan",
                              "rows": 2.68e6,
                              "cost": 1.5e12,
                              "chosen": false
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "added_to_eq_ref_extension": true,
                        "cost_for_plan": 5.29e6,
                        "rows_for_plan": 1.89e6,
                        "chosen": true
                      }
                    ] /* rest_of_plan */
                  }
                ] /* rest_of_plan */

Ref Acess vs Table/Index Scan

Ref Acess

인덱스를 사용하여 지정된 키 값으로 모든 레코드 읽음
ex1) WHERE t1.key = 7;
ex2) WHERE t1.key = t2.key;
"eq_ref" / "ref"

Table/Index Scan

이전에 수행된 "range_analysis" 에서 winner 가 된, 즉 cost 가 적은 경우

ETC

Join buffering (BNL/BKA)
Filtering effects of conditions

트레이스 상 나타나는 부분

※ Acess Type 

"plan_prefix": [
                ] /* plan_prefix */,
                "table": "`pensionRev` `R` IGNORE INDEX (`INDEX_8`)",
                "best_access_path": {
                  "considered_access_paths": [
                    {

                      "access_type": "ref",

                      "index": "PRIMARY",
                      "usable": false,
                      "chosen": false
                    },
                    {

                      "access_type": "scan",

                      "rows": 3.58e6,
                      "cost": 819297,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,


※ Filtering Effect
"considered_access_paths": [
                    {
                      "rows_to_scan": 582012,

                      "filtering_effect": [
                      ] /* filtering_effect */,
                      "final_filtering_effect": 3.3e-4,

                      "access_type": "range",
                      "range_details": {
                        "used_index": "mrAppointmentDate"
                      } /* range_details */,
                      "resulting_rows": 193.98,
                      "cost": 423330,
                      "chosen": true
                    }
                  ] /* considered_access_paths */

4. Plan refinement (Table condition pushdown / Access method adjustments / Sort avoidance / Index condition pushdown / Prepare temporary tables)

1) 조인 순서에 따른 condition summary

              "attached_conditions_summary": [
                {
                  "table": "`motelReservation` `mr` FORCE INDEX (`mrAppointmentDate`) IGNORE INDEX (`mrMyRoom_Check`)",
                  "attached": "((`mr`.`channelNo` = 0) and (`mr`.`mrMyRoom` = 'Y') and (`mr`.`mrCheck` = 'ONLINE') and (`mr`.`mrAppointmentDate` between '2019-12-23' and '2019-12-26') and (`mr`.`mrStatus` <> 'b') and (`mr`.`mpsName` like '%a%'))"
                }
              ] /* attached_conditions_summary */

2) Remove conditions satisfied by ref access

            "finalizing_table_conditions": [
              {
                "table": "`motelReservation` `mr` FORCE INDEX (`mrAppointmentDate`) IGNORE INDEX (`mrMyRoom_Check`)",
                "original_table_condition": "((`mr`.`channelNo` = 0) and (`mr`.`mrMyRoom` = 'Y') and (`mr`.`mrCheck` = 'ONLINE') and (`mr`.`mrAppointmentDate` between '2019-12-23' and '2019-12-26') and (`mr`.`mrStatus` <> 'b') and (`mr`.`mpsName` like '%a%'))",
                "final_table_condition   ": "((`mr`.`channelNo` = 0) and (`mr`.`mrMyRoom` = 'Y') and (`mr`.`mrCheck` = 'ONLINE') and (`mr`.`mrAppointmentDate` between '2019-12-23' and '2019-12-26') and (`mr`.`mrStatus` <> 'b') and (`mr`.`mpsName` like '%a%'))"
              }
            ] /* finalizing_table_conditions */

3) ORDER BY optimization

Avoid sorting, if possible

가능한 sort 를 피할 수 있는 다른 index 가 있다면 바꾼다

mysql> explain select * from t1 where c1='a' order by id desc limit 1;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+--------+----------+---------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows   | filtered | Extra               |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+--------+----------+---------------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | ix_c1         | ix_c1 | 47      | const | 227120 |   100.00 | Backward index scan |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+--------+----------+---------------------+
1 row in set, 1 warning (0.00 sec)


mysql> explain select * from t1 where c1='a' order by c3 desc limit 1;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | ix_c1         | ix_c3 | 6       | NULL |    2 |    48.33 | Using where; Backward index scan |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+----------------------------------+

실제로 c1 에 equal 조건으로 사용할 인덱스가 있더라도, Sorting 을 피하기 위해 c3 인덱스를 사용한다.

이 부분에 의해 원하는대로 Query Plan 이 안풀렸던 것이다.

4) Index Condition Pushdown

인덱스의 condition 들을 Storage Engine Level 로 push

5) Prepare temporary tables

            "considering_tmp_tables": [
              {
                "adding_sort_to_table_in_plan_at_position": 0
              } /* filesort */
            ] /* considering_tmp_tables */

Query Execution Trace 의 요약

1. temp 테이블 생성에 대한 정보

2. Sort parameter 들과 요약

3. Subquery Execution

4. Dynamic Range Optimization

5. 추가적인 큰 트레이스 정보 추적 가능

세션 변수를 통해 볼륨 사이즈 컨트롤 가능
SET optimizer_trace_features = "dynamic_range=off, repeated_subselect=off"

Optimizer 는 여러한 조건들을 통해 Execution Plan 을 최초 작성하고, 마지막으로 refine 을 한번 더 하는 과정을 지닌다.

결국 최초 index hint 로 유도한 best plan 을 작성했지만,

paln refinement 의 sort optimize 과정에서 sort 를 피할 수 있는 query plan 으로 refinement 된 것이다.(결국 엄청 비효율적인 ㅡ.ㅡ..)

이 때 sort + limit 이 들어간 쿼리 형태라면, sort 를 피하고 limit 을 통한 부분 범위 처리를 위해 ORDER BY 컬럼에 걸려 있는 인덱스를 사용하게 된다.

문제는 ORDER BY 를 하는 컬럼이 primary key 일 때이다.

WHERE 절에 적절한 인덱스가 있음에도 불구하고, 대부분의 레코드를 fetch 하고 WHERE 조건들을 filtering 을 하게 된다.

당황스러웠지만 어느정도 정리는 된 듯..

'MySQL > 8.0 New Feature' 카테고리의 다른 글

MySQL 8.0 binlog_row_value_options (JSON Type option) (0)	2020.02.10
MySQL 8.0 Atomic DDL (data definition statement) (0)	2020.02.03
MySQL 8.0 binlog_row_metadata (1)	2020.01.29
Descending Index (0)	2019.02.18

uzi's

MySQL 8.0 Optimizer Trace

Cost-based Query Optimization

1. 기본 컨셉

2. 주요 요소

3. Cost Estimates

Main Cost Constants

Optimizer Trace

옵티마이저의 실행 단계

1. Logical Transformations (Negation elimination / Equality and constant propagation / Evaluation of constant expressions / Substitution of generated columns)

2. Prepare for cost-based Optimization (Ref access analysis / Range access analysis / Estimation of condition fan out / Constant table detection)

1) Ref Access Analysis

2) Range Access Analysis

3. Join order and access methods (Join order / Access method selection)

Ref Acess vs Table/Index Scan

4. Plan refinement (Table condition pushdown / Access method adjustments / Sort avoidance / Index condition pushdown / Prepare temporary tables)

1) 조인 순서에 따른 condition summary

2) Remove conditions satisfied by ref access

3) ORDER BY optimization

4) Index Condition Pushdown

5) Prepare temporary tables

Query Execution Trace 의 요약

1. temp 테이블 생성에 대한 정보

2. Sort parameter 들과 요약

3. Subquery Execution

4. Dynamic Range Optimization

5. 추가적인 큰 트레이스 정보 추적 가능

'MySQL > 8.0 New Feature' 카테고리의 다른 글

티스토리툴바

MySQL 8.0 Optimizer Trace

Cost-based Query Optimization

1. 기본 컨셉

2. 주요 요소

3. Cost Estimates

Main Cost Constants

Optimizer Trace

옵티마이저의 실행 단계

1. Logical Transformations (Negation elimination / Equality and constant propagation / Evaluation of constant expressions / Substitution of generated columns)

2. Prepare for cost-based Optimization (Ref access analysis / Range access analysis / Estimation of condition fan out / Constant table detection)

1) Ref Access Analysis

2) Range Access Analysis

3. Join order and access methods (Join order / Access method selection)

Ref Acess vs Table/Index Scan

4. Plan refinement (Table condition pushdown / Access method adjustments / Sort avoidance / Index condition pushdown / Prepare temporary tables)

1) 조인 순서에 따른 condition summary

2) Remove conditions satisfied by ref access

3) ORDER BY optimization

4) Index Condition Pushdown

5) Prepare temporary tables

Query Execution Trace 의 요약

1. temp 테이블 생성에 대한 정보

2. Sort parameter 들과 요약

3. Subquery Execution

4. Dynamic Range Optimization

5. 추가적인 큰 트레이스 정보 추적 가능

'MySQL > 8.0 New Feature' 카테고리의 다른 글

'MySQL/8.0 New Feature' Related Articles

티스토리툴바