Details of the Architecture of MySQL 8.0 Server Layer

By Daoke

1. Background and Architecture

This article analyzes and summarizes the source code of MySQL 8.0.25. The MySQL Server layer refers to the optimizer and executor of MySQL. Our understanding of MySQL is based on versions 5.6 and 5.7. We mainly compare it PostgreSQL or traditional databases. However, starting from MySQL 8.0, the continuous iteration and refactoring efforts every three months have brought a qualitative leap to the overall architecture of MySQL Server layer. Let's look at the architecture of MySQL 8.0.25.

The MySQL hierarchical architecture is not very different from other databases. It is worth noting that MySQL is now focusing more on enhancing the evolution of InnoDB, NDB clusters, and the memory cluster architecture of RAPID (HeatWave clusters), as shown in the figure. Let's take a look at the details. We do not follow the sequence of the official Feature implementation and refactoring. Instead, this article takes the perspective of the process of optimizer and executor.

2. MySQL Parser

The official MySQL 8.0 uses Bison to rewrite the parser and generate Parser Tree. At the same time, Parser Tree will contextualize to generate a MySQL abstract syntax tree (AST).

MySQL AST is somewhat different from other databases. It is composed of complex SELECT_LEX_UNIT/SELECT_LEX classes alternately. However, these two structures have been renamed to standard Query_expression and Query_block in this version, respectively. Query_block represents a query block, while Query_expression is a query expression that contains multiple query blocks, including the UNION AND/OR query block (such as SELECT FROM t1 union SELECT FROM t2) or multi-level ORDER BY/LIMIT (such as SELECT * FROM t1 ORDER BY a LIMIT 10) and ORDER BY b LIMIT 5.

For example, let's look at a complex nested query:

(SELECT *
   FROM ttt1)
UNION ALL
  (SELECT *
   FROM
     (SELECT *
      FROM ttt2) AS a,
     (SELECT *
      FROM ttt3
      UNION ALL SELECT *
      FROM ttt4) AS b)

MySQL can be express it in the following way:

The parsed and converted syntax tree is still built under the framework of Query_block and Query_expression, but some levels of query blocks have been eliminated or merged. We will not discuss it in detail here.

3. prepare/rewrite Phase in MySQL

Next, we will go through the resolve and transform process: Query_expression::prepare->Query_block::prepare. This process includes (by function instead of completely following the execution order):

3.1 Setup and Fix

• setup_tables: Set up table leaves in the query block based on list of tables

• resolve_placeholder_tables/merge_derived/setup_table_function/setup_materialized_derived: Resolve derived table, view, or table function references in query block

• setup_natural_join_row_types: Compute and store the row types of the top-most NATURAL/USING joins

• setup_wild: Expand all '*' in the list of expressions with the matching column references

• setup_base_ref_items: Set query_block's base_ref_items

• setup_fields: Check that all given fields exist and fill struct with current data

• setup_conds: Resolve WHERE condition and join conditions

• setup_group: Resolve and set up the GROUP BY list

• m_having_cond->fix_fields: Set up the HAVING clause

• resolve_rollup: Resolve items in SELECT list and ORDER BY list for rollup processing

• resolve_rollup_item: Resolve an item (and its tree) for rollup processing by replacing items matching grouped expressions with Item_rollup_group_items and updating properties (m_nullable, PROP_ROLLUP_FIELD). Check any GROUPING function for incorrect columnsas well.

• setup_order: Set up the ORDER BY clause

• resolve_limits: Resolve OFFSET and LIMIT clauses

• Window::setup_windows1: Set up windows after setup_order() and before setup_order_final()

• setup_order_final: Do the final setup of the ORDER BY clause after the query block is fully resolved

• setup_ftfuncs: Set up full-text functions after resolving HAVING

• resolve_rollup_wfs: Replace group by field references inside window functions with references in the presence of ROLLUP

3.2 Transformation

• remove_redundant_subquery_clause: Permanently remove redundant parts from the query if:

1) This is a subquery.

2) Not normalizing a view.

Removal should take place when a query involving a view is optimized, not when the view is created.

• remove_base_options: Remove SELECT_DISTINCT options from a query block if distinct can be skipped.

• resolve_subquery: Resolve predicate involving subquery and perform early unconditional subquery transformations using one of the options below:

⚬ Convert subquery predicate into semi-join
⚬ Mark the subquery for execution using materialization
⚬ Perform IN->EXISTS transformation
⚬ Perform more/less ALL/ANY -> MIN/MAX rewrite
⚬ Substitute trivial scalar-context subquery with its value

• transform_scalar_subqueries_to_join_with_derived: Transform eligible scalar subqueries to derived tables.

• flatten_subqueries: Convert semi-join subquery predicates into semi-join join nests. Convert candidate subquery predicates into semi-join join nests. This transformation is performed once in query lifetime and is irreversible.

• apply_local_transforms:

⚬ delete_unused_merged_columns: If query block contains one or more merged derived tables/views, walk through lists of columns in select lists and remove unused columns.
⚬ simplify_joins: Convert all outer joins to inner joins if possible
⚬ prune_partitions: Perform partition pruning for a given table and condition

• push_conditions_to_derived_tables: Push-down of conditions to derived tables must be done after validity checks of grouped queries done by apply_local_transforms().
• Window::eliminate_unused_objects: Eliminate objects like unused window definitions and redundant sorts.

Due to space limitations, we will focus on the simple_joins function, which is related to top_join_list, and the simplifying process of nested joins in Query_block.

3.3 Comparison with PostgreSQL

We have quoted the three procedures of PostgreSQL below to have a clearer understanding of the standard database approach:

Parser

In the following figure, Parser generates a parse tree based on the SQL statement:

testdb=# SELECT id, data FROM tbl_a WHERE id < 300 ORDER BY data;

Analyzer/Analyser

The following figure shows how the analyzer/analyser of PostgreSQL uses the parse tree to generate a query tree after semantic analysis.

Rewriter

Rewriter transforms the query tree based on the rules in the rule system:

sampledb=# CREATE VIEW employees_list
sampledb-#      AS SELECT e.id, e.name, d.name AS department
sampledb-#            FROM employees AS e, departments AS d WHERE e.department_id = d.id;

The following example shows how a query tree containing view is expanded into a new query tree:

sampledb=# SELECT * FROM employees_list;

4. Optimize and Planning Phases in MySQL

Next, we entered the process of generating physical plans from logical plans. This article still focuses on the analysis of structures instead of introducing the details of generation. MySQL used to rely mainly on JOIN and QEP_TAB before version 8.0.22. JOIN corresponds to Query_block, while QEP_TAB corresponds to the sequence, method, and execution plan of the specific "table" involved in Query_block. However, after version 8.0.22, the new Hypergraph-based optimizer algorithm successfully abandoned the QEP_TAB structure of expressing the execution plan of the left deep tree. The algorithm directly used the HyperNode/HyperEdge graph to represent the execution plan.

In the following example, you can see the different plan presentations of left deep tree presented in data structure and the bushy tree presented in the hypergraph structure.

| -> Inner hash join (no condition)  (cost=1.40 rows=1)
    -> Table scan on R4  (cost=0.35 rows=1)
    -> Hash
        -> Inner hash join (no condition)  (cost=1.05 rows=1)
            -> Table scan on R3  (cost=0.35 rows=1)
            -> Hash
                -> Inner hash join (no condition)  (cost=0.70 rows=1)
                    -> Table scan on R2  (cost=0.35 rows=1)
                    -> Hash
                        -> Table scan on R1  (cost=0.35 rows=1)

| -> Nested loop inner join  (cost=0.55..0.55 rows=0)
    -> Nested loop inner join  (cost=0.50..0.50 rows=0)
        -> Table scan on R4  (cost=0.25..0.25 rows=1)
        -> Filter: (R4.c1 = R3.c1)  (cost=0.35..0.35 rows=0)
            -> Table scan on R3  (cost=0.25..0.25 rows=1)
    -> Nested loop inner join  (cost=0.50..0.50 rows=0)
        -> Table scan on R2  (cost=0.25..0.25 rows=1)
        -> Filter: (R2.c1 = R1.c1)  (cost=0.35..0.35 rows=0)
            -> Table scan on R1  (cost=0.25..0.25 rows=1)

MySQL 8.0.2x introduces a new class AccessPath to be more compatible with the two optimizers. It can be considered as a Plan Tree abstracted by MySQL to decouple the executor from different optimizers.

4.1 Entry to the Old Optimizer

The old optimizer still uses JOIN::optimize to convert query blocks to query execution plans (QEPs).

Some logic rewriting work is still done in this phase. The conversion in this phase can be understood as preparing for cost-based optimization. The detailed steps are listed below:

• Logical Transformations
• optimize_derived: Optimize the query expression representing a derived table/view
• optimize_cond: Equality/constant propagation
• prune_table_partitions: Partition pruning
• optimize_aggregated_query: COUNT(*), MIN(), MAX() constant substitution in case of implicit grouping
• substitute_gc: ORDER BY optimization. Substitute all expressions in the WHERE condition and ORDER/GROUP lists that match generated columns (GC) expressions with GC fields, if any.
• Perform cost-based optimization of table order and access path selection.
• JOIN::make_join_plan(): Set up join order and initial access paths
• Post-join order optimization
• substitute_for_best_equal_field: Create optimal table conditions from the where clause and the join conditions
• make_join_query_block: Inject outer-join guarding conditions
• Adjust data access methods after determining the table condition (several times)
• optimize_distinct_group_order: Optimize ORDER BY/DISTINCT
• optimize_fts_query: Perform FULLTEXT search before all regular searches
• remove_eq_conds: Remove const and eq items. Return the new item or nullptr if no condition
• replace_index_subquery/create_access_paths_for_index_subquery: See if this subquery can be evaluated with subselect_indexsubquery_engine
• setup_join_buffering: Check whether join cache could be used.
• Code Generation
• alloc_qep(tables): Create QEP_TAB array
• test_skip_sort: Try to optimize away sorting/distinct
• make_join_readinfo: Plan refinement stage: Do various setup things for the executor
• make_tmp_tables_info: Set up temporary table usage for grouping and/or sorting
• push_to_engines: Push (parts of) the query execution down to the storage engines if they can provide faster execution of the query or part of it
• create_access_paths: Generated ACCESS_PATH

4.2 Entry to the New Optimizer

The new optimizer is not enabled by default and must be enabled using set optimizer_switch="hypergraph_optimizer=on". This is mainly implemented by the FindBestQueryPlan function. The logic is listed below:

• First, determine whether it belongs to the Query syntax (CheckSupportedQuery) that the new optimizer can support. If it is not supported, it will return an error: ER_HYPERGRAPH_NOT_SUPPORTED_YET.
• Convert top_join_list to a JoinHypergraph structure. Since Hypergraph is relatively independent algorithm-level implementation, the JoinHypergraph structure is used to better encapsulate the structure of the database into the concepts of Hypergraph edges and nodes.
• The DPhyp algorithm in the paper is implemented by EnumerateAllConnectedPartitions.
• The CostingReceiver class contains the main logic of the previous JOIN planning, including selecting the corresponding access path based on cost, evaluating the sub-plan generated by DPhyp, and retaining the sub-plan with the smallest cost.
• After the root_path is obtained, handle group/agg/having/sort/limit. Hypergraph uses sorting first + streaming aggregation for the Group BY operation.

Here's an example to help us understand the relationship between Plan (AccessPath) and SQL:

Finally, the Iterator execution carrier required by the Iterator executor framework is generated. One AccessPath corresponds to one iterator.

Query_expression::m_root_iterator = CreateIteratorFromAccessPath(......)

unique_ptr_destroy_only<RowIterator> CreateIteratorFromAccessPath(
     THD *thd, AccessPath *path, JOIN *join, bool eligible_for_batch_mode) {
......
   switch (path->type) {
     case AccessPath::TABLE_SCAN: {
       const auto &param = path->table_scan();
       iterator = NewIterator<TableScanIterator>(
           thd, param.table, path->num_output_rows, examined_rows);
       break;
     }
     case AccessPath::INDEX_SCAN: {
       const auto &param = path->index_scan();
       if (param.reverse) {
         iterator = NewIterator<IndexScanIterator<true>>(
             thd, param.table, param.idx, param.use_order, path->num_output_rows,
             examined_rows);
       } else {
         iterator = NewIterator<IndexScanIterator<false>>(
             thd, param.table, param.idx, param.use_order, path->num_output_rows,
             examined_rows);
       }
       break;
     }
     case AccessPath::REF: {
......
}

4.3 Comparison with PostgreSQL

testdb=# EXPLAIN SELECT * FROM tbl_a WHERE id < 300 ORDER BY data;
                          QUERY PLAN                           
---------------------------------------------------------------
 Sort  (cost=182.34..183.09 rows=300 width=8)
   Sort Key: data
   ->  Seq Scan on tbl_a  (cost=0.00..170.00 rows=300 width=8)
         Filter: (id < 300)
(4 rows)

5. Summary

This article focuses on the official source code of MySQL 8.0.25, mainly analyzing the changes and connections in the structure of the official refactoring in each stage. We mainly intend to introduce the development of a brand-new MySQL.

Community

Details of the Architecture of MySQL 8.0 Server Layer

1. Background and Architecture

2. MySQL Parser

3. prepare/rewrite Phase in MySQL

3.1 Setup and Fix

3.2 Transformation

3.3 Comparison with PostgreSQL

Parser

Analyzer/Analyser

Rewriter

4. Optimize and Planning Phases in MySQL

4.1 Entry to the Old Optimizer

4.2 Entry to the New Optimizer

4.3 Comparison with PostgreSQL

5. Summary

Read previous post:

Read next post:

ApsaraDB

You may also like

Comments

ApsaraDB

Related Products

PolarDB for PostgreSQL

PolarDB for MySQL

AnalyticDB for PostgreSQL

AnalyticDB for MySQL