Inside MySQL 8.0: Deep Dive into Parser, Optimizer, and Execution Engine

1 Background and Architecture

This article is based on the MySQL 8.0.25 source code and focuses on the server layer (optimizer and executor). The overall architecture of MySQL Server has significantly evolved since version 8.0, with stronger emphasis on InnoDB, NDB clusters, and the RAPID (HeatWave) memory‑cluster architecture.

2 MySQL Parser

The parser in MySQL 8.0 is rewritten with Bison, producing a parser tree that is contextualized into an abstract syntax tree (AST). The former SELECT_LEX_UNIT/SELECT_LEX structures have been renamed to Query_expression and Query_block respectively.

Example of a complex nested query and its AST representation:

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

MySQL rewrites this into a simplified AST based on Query_block and Query_expression.

3 MySQL Prepare/Rewrite Phase

The resolve and transformation process (Query_expression::prepare → Query_block::prepare) performs a series of setup and fix steps, including:

setup_tables

resolve_placeholder_tables / merge_derived / setup_table_function / setup_materialized_derived

setup_natural_join_row_types

setup_wild

setup_base_ref_items

setup_fields

setup_conds

setup_group

m_having_cond‑>fix_fields

resolve_rollup / resolve_rollup_item

setup_order / setup_order_final

setup_ftfuncs

resolve_rollup_wfs

2 Transformation

Key transformation functions applied after the prepare phase include:

remove_redundant_subquery_clause

remove_base_options

resolve_subquery (including IN‑>EXISTS, materialization, scalar‑subquery substitution)

flatten_subqueries

apply_local_transforms (delete_unused_merged_columns, simplify_joins, prune_partitions, push_conditions_to_derived_tables, Window::eliminate_unused_objects)

3 Comparison with PostgreSQL

PostgreSQL processes a query through three stages:

Parser : Generates a parse tree.

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

Analyzer : Performs semantic analysis to produce a query tree.

Rewriter : Applies rewrite rules to the query tree, e.g., view expansion.

4 MySQL Optimize and Planning

The logical plan is transformed into a physical plan. Prior to 8.0.22 the optimizer relied on JOIN and QEP_TAB structures; newer versions use a hypergraph‑based optimizer with AccessPath, HyperNode, and HyperEdge.

1 Legacy Optimizer Entry

The legacy optimizer follows JOIN::optimize, performing logical transformations such as optimize_derived, optimize_cond, prune_table_partitions, optimize_aggregated_query, substitute_gc, cost‑based join order selection, and various join‑buffering and code‑generation steps.

2 New Optimizer Entry

The hypergraph optimizer is disabled by default and must be enabled with set optimizer_switch="hypergraph_optimizer=on". It works via FindBestQueryPlan, which checks query support, converts the top_join_list into a JoinHypergraph, runs the DPhyp algorithm, and uses CostingReceiver to select the lowest‑cost plan. Group/agg/having/sort/limit are handled with streaming aggregation, and each AccessPath maps one‑to‑one to an iterator.

Query_expression::m_root_iterator = CreateIteratorFromAccessPath(...)

case AccessPath::TABLE_SCAN: { const auto &param = path->table_scan(); iterator = NewIterator<TableScanIterator>(thd, param.table, path->num_output_rows, examined_rows); break; }

5 Summary

The article focuses on the latest MySQL source, analyzing the multi‑stage refactoring of the server layer and highlighting the architectural evolution from the traditional JOIN/QEP_TAB model to the hypergraph‑based optimizer.