MySQL 8.0 Server层最新架构详解

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 本文基于MySQL 8.0.25源码进行分析和总结。这里MySQL Server层指的是MySQL的优化器、执行器部分。我们对MySQL的理解还建立在5.6和5.7版本的理解之上,更多的是对比PostgreSQL或者传统数据库。然而从MySQL 8.0开始,持续每三个月的迭代和重构工作,使得MySQL Server层的整体架构有了质的飞越。下面来看下MySQL最新的架构。

背景和架构

本文基于MySQL 8.0.25源码进行分析和总结。这里MySQL Server层指的是MySQL的优化器、执行器部分。我们对MySQL的理解还建立在5.6和5.7版本的理解之上,更多的是对比PostgreSQL或者传统数据库。然而从MySQL 8.0开始,持续每三个月的迭代和重构工作,使得MySQL Server层的整体架构有了质的飞越。下面来看下MySQL最新的架构。

image.png

我们可以看到最新的MySQL的分层架构和其他数据库并没有太大的区别,另外值得一提的是从图中可以看出MySQL现在更多的加强InnoDB、NDB集群和RAPID(HeatWave clusters)内存集群架构的演进。下面我们就看下具体细节,我们这次不随着官方的Feature实现和重构顺序进行理解,本文更偏向于从优化器、执行器的流程角度来演进。

MySQL 解析器Parser

首先从Parser开始,官方MySQL 8.0使用Bison进行了重写,生成Parser Tree,同时Parser Tree会contextualize生成MySQL抽象语法树(Abstract Syntax Tree)。

image.png

MySQL抽象语法树和其他数据库有些不同,参考《MySQL 8.0 新的火山模型执行器》文章,是由让比较让人拗口SELECT_LEX_UNIT/SELECT_LEX类交替构成的,然而这两个结构在最新的版本中已经重命名成标准的SELECT_LEX -> Query_block和ELECT_LEX_UNIT -> Query_expression。Query_block是代表查询块,而Query_expression是包含多个查询块的查询表达式,包括UNION AND/OR的查询块(如SELECT * FROM t1 union SELECT * FROM t2)或者有多Level的ORDER BY/LIMIT (如SELECT * FROM t1 ORDER BY a LIMIT 10) ORDER BY b LIMIT 5

例如,来看一个复杂的嵌套查询:

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

在MySQL中就可以用下面方式表达:

image.png

经过解析和转换后的语法树仍然建立在Query_block和Query_expression的框架下,只不过有些LEVEL的query block被消除或者合并了,这里不在详细展开。

MySQL prepare/rewrite阶段

接下来我们要经过resolve和transformation过程Query_expression::prepare->Query_block::prepare,这个过程包括(按功能分而非完全按照执行顺序):

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.

这里,节省篇幅,我们只举例关注下和top_join_list相关的simple_joins这个函数的作用,对于Query_block中嵌套join的简化过程。

image.png

对比PostgreSQL

为了更清晰的理解标准数据库的做法,我们这里引用了PostgreSQL的这三个过程:

Parser

下图首先Parser把SQL语句生成parse tree。

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

image.png

Analyzer/Analyser

下图展示了PostgreSQL的analyzer/analyser如何将parse tree通过语义分析后生成query tree。

image.png

Rewriter

Rewriter会根据规则系统中的规则把query tree进行转换改写。

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;

下图的例子就是一个包含view的query tree如何展开成新的query tree。

sampledb=# SELECT * FROM employees_list;

image.png

MySQL Optimize和Planning阶段

接下来我们进入了逻辑计划生成物理计划的过程,本文还是注重于结构的解析,而不去介绍生成的细节,MySQL过去在8.0.22之前,主要依赖的结构就是JOIN和QEP_TAB。JOIN是与之对应的每个Query_block,而QEP_TAB对应的每个Query_block涉及到的具体“表”的顺序、方法和执行计划。然而在8.0.22之后,新的基于Hypergraph的优化器算法成功的抛弃了QEP_TAB结构来表达左深树的执行计划,而直接使用HyperNode/HyperEdge的图来表示执行计划。

image.png

举例可以看到数据结构表达的left deep tree和超图结构表达的bushy tree对应的不同计划展现:

| -> 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)

MySQL8.0.2x为了更好的兼容两种优化器,引入了新的类AccessPath,可以认为这是MySQL为了解耦执行器和不同优化器抽象出来的Plan Tree。

image.png

老优化器的入口

老优化器仍然走JOIN::optimize来把query block转换成query execution plan (QEP.)

这个阶段仍然做一些逻辑的重写工作,这个阶段的转换可以理解为基于cost-based优化前做准备,详细步骤如下:

  • 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.

新优化器的入口

新优化器默认不打开,必须通过set optimizer_switch="hypergraph_optimizer=on"; 来打开,其代码流程如下:

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 获取neighbors

举例看下Plan(AccessPath)和SQL的关系

image.png

最后生成Iterator执行器框架需要的Iterator执行载体,AccessPath和Iterator是一对一的关系(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 &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: {
......
}

对比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)

image.png

总结

本文主要focus在MySQL最新版本官方的源码上,重点分析了官方的重构在多阶段和各阶段结构上的变化和联系,更多的是为了让大家了解一个全新的MySQL的发展。最后这里贴几张官方youtube介绍的refactor的工作内容。

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

  • Nested loop focused
  • Hard to extend
  • COde for one operation spread out
  • Different interfaces for each operation
  • Combination of operations hard coded

image.png

New (current) executor

  • Modular
  • Easy to extend
  • Each iterator encapsulates on operation
  • Same interface for all iterators
  • All operations can be connected

image.png

参考资料

《Refactoring Query Processing in MySQL (Norvald H. Ryeng, Oracle)》

《Dynamic Programming Strikes Back - MySQL8.0的新优化器》

《The Internals of PostgreSQL Chapter 3 Query Processing》

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
存储 SQL 关系型数据库
Mysql高可用架构方案
本文阐述了Mysql高可用架构方案,介绍了 主从模式,MHA模式,MMM模式,MGR模式 方案的实现方式,没有哪个方案是完美的,开发人员在选择何种方案应用到项目中也没有标准答案,合适的才是最好的。
129 3
Mysql高可用架构方案
|
2月前
|
监控 关系型数据库 MySQL
深入了解MySQL主从复制:构建高效稳定的数据同步架构
深入了解MySQL主从复制:构建高效稳定的数据同步架构
130 1
|
3月前
|
NoSQL 关系型数据库 MySQL
微服务架构下的数据库选择:MySQL、PostgreSQL 还是 NoSQL?
在微服务架构中,数据库的选择至关重要。不同类型的数据库适用于不同的需求和场景。在本文章中,我们将深入探讨传统的关系型数据库(如 MySQL 和 PostgreSQL)与现代 NoSQL 数据库的优劣势,并分析在微服务架构下的最佳实践。
|
28天前
|
SQL 存储 缓存
【赵渝强老师】MySQL的体系架构
本文介绍了MySQL的体系架构,包括Server层的7个主要组件(Connectors、Connection Pool、Management Service & Utilities、SQL Interface、Parser、Optimizer、Query Caches & Buffers)及其作用,以及存储引擎层的支持情况,重点介绍了InnoDB存储引擎。文中还提供了相关图片和视频讲解。
【赵渝强老师】MySQL的体系架构
|
9天前
|
SQL 存储 关系型数据库
MySQL进阶突击系列(01)一条简单SQL搞懂MySQL架构原理 | 含实用命令参数集
本文从MySQL的架构原理出发,详细介绍其SQL查询的全过程,涵盖客户端发起SQL查询、服务端SQL接口、解析器、优化器、存储引擎及日志数据等内容。同时提供了MySQL常用的管理命令参数集,帮助读者深入了解MySQL的技术细节和优化方法。
|
3月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
3月前
|
关系型数据库 MySQL 数据库
docker启动mysql多实例连接报错Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’
docker启动mysql多实例连接报错Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’
215 0
|
7天前
|
弹性计算 API 持续交付
后端服务架构的微服务化转型
本文旨在探讨后端服务从单体架构向微服务架构转型的过程,分析微服务架构的优势和面临的挑战。文章首先介绍单体架构的局限性,然后详细阐述微服务架构的核心概念及其在现代软件开发中的应用。通过对比两种架构,指出微服务化转型的必要性和实施策略。最后,讨论了微服务架构实施过程中可能遇到的问题及解决方案。
|
16天前
|
Cloud Native Devops 云计算
云计算的未来:云原生架构与微服务的革命####
【10月更文挑战第21天】 随着企业数字化转型的加速,云原生技术正迅速成为IT行业的新宠。本文深入探讨了云原生架构的核心理念、关键技术如容器化和微服务的优势,以及如何通过这些技术实现高效、灵活且可扩展的现代应用开发。我们将揭示云原生如何重塑软件开发流程,提升业务敏捷性,并探索其对企业IT架构的深远影响。 ####
30 3
|
24天前
|
Cloud Native 安全 数据安全/隐私保护
云原生架构下的微服务治理与挑战####
随着云计算技术的飞速发展,云原生架构以其高效、灵活、可扩展的特性成为现代企业IT架构的首选。本文聚焦于云原生环境下的微服务治理问题,探讨其在促进业务敏捷性的同时所面临的挑战及应对策略。通过分析微服务拆分、服务间通信、故障隔离与恢复等关键环节,本文旨在为读者提供一个关于如何在云原生环境中有效实施微服务治理的全面视角,助力企业在数字化转型的道路上稳健前行。 ####

相关产品

  • 云数据库 RDS MySQL 版