Introduction to This article analyzes and summarizes the MySQL 8.0.25 source code. The MySQL Server layer here refers to the optimizer and executor of MySQL. Our understanding of MySQL is also based on the understanding of the 5.6 and 5.7 versions, and it is more about comparing PostgreSQL or traditional databases. However, starting from MySQL 8.0, iterative and refactoring work continued every three months, making the overall architecture of the MySQL Server layer a qualitative leap. Let's take a look at the latest architecture of MySQL.
Background and architecture
This article analyzes and summarizes the MySQL 8.0.25 source code. The MySQL Server layer here refers to the optimizer and executor of MySQL. Our understanding of MySQL is also based on the understanding of the 5.6 and 5.7 versions, and it is more about comparing PostgreSQL or traditional databases. However, starting from MySQL 8.0, iterative and refactoring work continued every three months, making the overall architecture of the MySQL Server layer a qualitative leap. Let's take a look at the latest architecture of MySQL.
We can see that the hierarchical architecture of the latest MySQL is not much different from other databases. It is also worth mentioning from the figure that MySQL is now more strengthening InnoDB, NDB clusters and RAPID (HeatWave clusters) memory The evolution of cluster architecture. Let's take a look at the specific details. We will not follow the official Feature implementation and reconstruction sequence to understand this time. This article is more inclined to evolve from the perspective of optimizer and executor process.
MySQL parser Parser
First, starting with Parser, the official MySQL 8.0 uses Bison to rewrite it to generate Parser Tree, and at the same time, Parser Tree will contextualize to generate MySQL Abstract Syntax Tree.
The MySQL abstract syntax tree is somewhat different from other databases. Refer to the article "MySQL 8.0 New Volcano Model Executor", which is composed of SELECT\_LEX\_UNIT/SELECT\_LEX classes that make people more confusing. However, these two structures are in The latest version has been renamed to the standard SELECT\_LEX -> Query\_block and ELECT\_LEX\_UNIT -> Query\_expression. Query\_block represents a query block, and Query\_expression is a query expression containing multiple query blocks, including UNION AND/OR query blocks (such as SELECT * FROM t1 union SELECT * FROM t2) or multiple levels of ORDER BY /LIMIT (e.g. SELECT * FROM t1 ORDER BY a LIMIT 10) ORDER BY b LIMIT 5
For example, consider 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)It can be expressed in the following way in MySQL:
The parsed and converted grammar tree is still built under the framework of Query\_block and Query\_expression, but some LEVEL query blocks have been eliminated or merged, so I won’t expand it in detail here.
MySQL prepare/rewrite phase
Next, we have to go through the resolve and transformation process Query\_expression::prepare->Query\_block::prepare, this process includes (by function rather than exactly in the order of execution):
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 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 exists 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 : Setup 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). Also check any GROUPING function for incorrect column.
- 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 final setup of ORDER BY clause, after the query block is fully resolved.
- setup\_ftfuncs : Setup full-text functions after resolving HAVING
- resolve\_rollup\_wfs : Replace group by field references inside window functions with references in the presence of ROLLUP.
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 can skip distinct
- resolve\_subquery : Resolve predicate involving subquery, perform early unconditional subquery transformations
- Convert subquery predicate into semi-join, or
- Mark the subquery for execution using materialization, or
- Perform IN->EXISTS transformation, or
- 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 : Pushing conditions down to derived tables must be done after validity checks of grouped queries done by apply\_local\_transforms();
- Window::eliminate\_unused\_objects: Eliminate unused window definitions, redundant sorts etc.
Here, to save space, we will only focus on the function of simple\_joins related to top\_join\_list, and the simplified process of nested joins in Query\_block.
Compare PostgreSQL
In order to have a clearer understanding of standard database practices, we have quoted these three processes of PostgreSQL here:
Parser
In the figure below, first Parser generates a parse tree from the SQL statement.
testdb=# SELECT id, data FROM tbl_a WHERE id < 300 ORDER BY data;
Analyzer/Analyser
The following figure shows how the PostgreSQL analyzer/analyser generates a query tree after semantic analysis of the parse tree.
Rewriter
Rewriter will convert and rewrite the query tree according to 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 example in the figure below is how a query tree containing views is expanded into a new query tree.
sampledb=# SELECT * FROM employees_list;
MySQL Optimize and Planning phase
Next, we entered the process of generating a physical plan from a logical plan. This article still focuses on the analysis of the structure instead of introducing the details of the generation. MySQL used to rely on JOIN and QEP\_TAB before 8.0.22. JOIN is each Query\_block corresponding to it, and each Query\_block corresponding to QEP\_TAB refers to the sequence, method and execution plan of the specific "table" involved. However, after 8.0.22, the new Hypergraph-based optimizer algorithm successfully abandoned the QEP\_TAB structure to express the execution plan of the left deep tree, and directly used the HyperNode/HyperEdge graph to express the execution plan.
For example, you can see the different plans corresponding to the left deep tree expressed by the data structure and the bushy tree expressed by 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)In order to better compatible with the two optimizers, MySQL8.0.2x introduced a new class AccessPath, which can be considered as a Plan Tree abstracted by MySQL for decoupling executors and different optimizers.
Entrance to the old optimizer
The old optimizer still uses JOIN::optimize to convert query block into query execution plan (QEP.)
At this stage, some logical rewriting is still being done. The conversion at this stage can be understood as preparation for cost-based optimization. The detailed steps are as follows:
- 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 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 : Removes const and eq items. Returns 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 : Setup 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.
Entrance to the new optimizer
The new optimizer is not turned on by default. It must be turned on by set optimizer\_switch="hypergraph\_optimizer=on";. The code flow is as follows:
FindBestQueryPlan
-> MakeJoinHypergraph 构建hypergraph
-> MakeRelationalExpressionFromJoinList 基于top_join_list构建operator tree
...
-> MakeJoinGraphFromRelationalExpression 基于operator tree => hypergraph
目前对non-inner join的约束比较粗暴:
1. Inner join，且左右子树都只有inner join，只约束SES
2. Inner join，但子树有非inner join: 将那颗子树整个固定在当前operator下,作为hypernode
3. 非Inner join，直接把左右子树都固定死，变成2个hypernode
...
-> EnumerateAllConnectedPartitions 开始算法流程 (Solve)
-> EnumerateComplementsTo 找互补对 (EmitCsg)
-> FoundSubgraphPair 找到csg-cmp-pair (EmitCsgCmp)
-> ExpandComplement 扩展互补对 (EnumerateCmpRec)
-> ExpandSubgraph 扩展连通子图 (EnumerateCsgRec)
辅助函数 FindNeighborhood 获取neighborsFor example, look at the relationship between Plan (AccessPath) and SQL
Finally, it generates the Iterator execution carrier required by the Iterator executor framework. AccessPath and Iterator are in a one-to-one relationship (Access paths are a query planning structure that correspond 1:1 to iterators).
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 ¶m = path->table_scan();
iterator = NewIterator<TableScanIterator>(
thd, param.table, path->num_output_rows, examined_rows);
break;
}
case AccessPath::INDEX_SCAN: {
const auto ¶m = 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: {
......
}Compare 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)
Summarize
The main focus of this article is on the official source code of the latest version of MySQL, focusing on the analysis of the changes and connections of the official refactoring in multiple stages and the structure of each stage, and it is more for everyone to understand the development of a new MySQL. Finally, here are a few official youtube introduction refactor work content.
Refactoring: Separating stages
- Started ~10 years ago
Finished a few years ago
- A clear separation between query processing stages
- Fixed a large number of bugs
Improved stability
- Faster feature devlopment
Fewer surprises and complications during development
Refactoring: Parsing and preparing
- Still ongoing
Implemented piece by piece
- Separating parsing and resolving stages
Elimiating semantic actions that do too much
Get a true bottom-up parser
- Makes it easier to extend with new SQL syntax
Parsing doesn't have unintented side effects
- Condistent name and type resolving
Names resolved top-down
Types resolved bottom-up
- Transformations done in the prepare stage
Bottom-up
Refactoring: Execution
- Volcano iterator model
- Possible because stages were separated
- Done silently in 8.0 oever the last two years
- Much more modular executor
Common iterator interface for all iterators
Each operation is contained within an iterator
- Able to put plans together in new ways
Immediate benefix: Remove some instances of teamporary tables
- Join is just an iterator
Nested loop join is just an interator
Hash join is just another iterator
Your favourite join method is just another iterator
Old vs current executor
| Old executor<ul><li>Nested loop focused</li><li>Hard to extend</li><li>COde for one operation spread out</li><li>Different interfaces for each operation</li><li>Combination of operations hard coded</li></ul><span class="lake-card-margin-top lake-card-margin-bottom"><img src="https://ucc.alicdn.com/pic/developer-ecology/5d7894314f7e4cc782ce41492736b275.png" class="image lake-drag-image" alt="image.png" title="image.png"></span> | New (current) executor<ul><li>Modular</li><li>Easy to extend</li><li>Each iterator encapsulates on operation</li><li>Same interface for all iterators</li><li>All operations can be connected</li></ul><span class="lake-card-margin-top lake-card-margin-bottom"><img src="https://ucc.alicdn.com/pic/developer-ecology/97e2132d6db84247a0f92c2884f047e2.png" class="image lake-drag-image" alt="image.png" title="image.png"></span> |
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。