1.5 MySQL Query Optimizer

1. What Is the Query Optimizer

• It transforms the syntax analysis tree into query trees. There are many possible query trees, and each query tree corresponds to one way of executing the query.
• The role of the query optimizer is to find the best execution method among them.

2. How the Query Optimizer Optimizes SQL

• It involves two kinds of optimization: logical query optimization and physical query optimization.

2.1. Logical Query Optimization

• Find equivalent transformations of SQL statements that make SQL execution more efficient.

2.1.1. Rule-Based Optimization

MySQL converts some poor statements into efficient statements. This is called query rewriting.

2.1.1.1. Condition Simplification

2.1.1.2. Outer Join Elimination

2.1.1.3. Subquery Optimization

2.2. Physical Query Optimization

• Among the available single-table scan methods, which single-table scan method is optimal?
• When joining two tables, how should the optimal method be selected?
• For joins involving multiple tables, there are many combinations of join order. Should every combination be explored? If not all combinations are explored, how can the optimal combination be found?

2.3. Cost-Based Optimization

2.3.1. Table Access Methods

• MySQL Table Access Methods.md

2.3.2. Cost of a Single-Table Query

1. Based on the search conditions, find all indexes that may be used.
2. Calculate the cost of a full table scan.
3. Calculate the cost of executing the query with different indexes.
   • Each index maintains a set of statistics: MySQL Statistics.md
4. Compare the costs of the various execution plans and find the one with the lowest cost.

2.3.3. Cost of Join Queries

2.3.3.1. Two Tables

• There are 2*1=2 join orders.
• Total join-query cost = cost of one access to the driving table + driving-table fan-out × cost of one access to the driven table.
  • The number of records obtained after querying the driving table is called the fan-out of the driving table.

2.3.3.2. Multiple Tables

• There are n! join orders.

3. Decision Process of the Query Optimizer

3.1. optimizer trace Table

• EXPLAIN can only show the execution plan used by the optimizer, while the optimizer trace table can be used to analyze how MySQL makes its decisions.

3.2. Usage

• It is disabled by default: SHOW VARIABLES LIKE 'optimizer_trace';
• Enable it: SET optimizer_trace="enabled=on";
• Enter and execute the query statement: select xxx
• View the optimization process in the OPTIMIZER_TRACE table: SELECT * FROM information_schema.OPTIMIZER_TRACE;
• Disable it: SET optimizer_trace="enabled=off";
• Analysis:
  • The optimization process is roughly divided into three stages: the prepare stage, the optimize stage, and the execute stage.
  • Cost-based optimization is mainly concentrated in the optimize stage.
    • For single-table queries, we mainly focus on the rows_estimation process in the optimize stage. This process deeply analyzes the cost of the various execution plans for a single-table query.
    • For multi-table join queries, we focus more on the considered_execution_plans process. This process records the cost corresponding to different join methods.

4. Optimization Result of the Query Optimizer

4.1. Execution Plan

The MySQL query optimizer finds all possible plans that can be used to execute a statement, compares them, and selects the plan with the lowest cost. This lowest-cost plan is the execution plan.

• MySQL explain.md

5. References

• Technical Sharing | MySQL Query Optimization - Zhihu
• [Playing with MySQL, Part 6] MySQL Query Optimizer - Zhihu