一背景和架构
本文基于MySQL8.0.25源码进行分析和总结。这里MySQLServer层指的是MySQL的优化器、执行器部分。我们对MySQL的理解还建立在5.6和5.7版本的理解之上，更多的是对比PostgreSQL或者传统数据库。然而从MySQL8.0开始，持续每三个月的迭代和重构工作，使得MySQLServer层的整体架构有了质的飞越。下面来看下MySQL最新的架构。
我们可以看到最新的MySQL的分层架构和其他数据库并没有太大的区别，另外值得一提的是从图中可以看出MySQL现在更多的加强InnoDB、NDB集群和RAPID(HeatWaveclusters)内存集群架构的演进。下面我们就看下具体细节，我们这次不随着官方的Feature实现和重构顺序进行理解，本文更偏向于从优化器、执行器的流程角度来演进。
二MySQL解析器Parser
首先从Parser开始，官方MySQL8.0使用Bison进行了重写，生成ParserTree，同时ParserTree会contextualize生成MySQL抽象语法树（AbstractSyntaxTree）。
MySQL抽象语法树和其他数据库有些不同，是由比较让人拗口的SELECT_LEX_UNIT/SELECT_LEX类交替构成的，然而这两个结构在最新的版本中已经重命名成标准的SELECT_LEX-Query_block和SELECT_LEX_UNIT-Query_expression。Query_block是代表查询块，而Query_expression是包含多个查询块的查询表达式，包括UNIONAND/OR的查询块（如SELECT*FROMt1unionSELECT*FROMt2）或者有多Level的ORDERBY/LIMIT(如SELECT*FROMt1ORDERBYaLIMIT10)ORDERBYbLIMIT5。
例如，来看一个复杂的嵌套查询：
(SELECT*FROMttt1)UNIONALL(SELECT*FROM(SELECT*FROMttt2)ASa,(SELECT*FROMttt3UNIONALLSELECT*FROMttt4)ASb)
在MySQL中就可以用下面方式表达：
经过解析和转换后的语法树仍然建立在Query_block和Query_expression的框架下，只不过有些LEVEL的queryblock被消除或者合并了，这里不再详细展开。
三MySQLprepare/rewrite阶段
接下来我们要经过resolve和transformation过程Query_expression::prepare-Query_block::prepare，这个过程包括（按功能分而非完全按照执行顺序）：
1SetupandFix
setup_tables：Setuptableleavesinthequeryblockbasedonlistoftables.
resolve_placeholder_tables/merge_derived/setup_table_function/setup_materialized_derived：Resolvederivedtable,viewortablefunctionreferencesinqueryblock.
setup_natural_join_row_types：Computeandstoretherowtypesofthetop-mostNATURAL/USINGjoins.
setup_wild：Expandall*inlistofexpressionswiththematchingcolumnreferences.
setup_base_ref_items：Setquery_blocksbase_ref_items.
setup_fields：Checkthatallgivenfieldsexistsandfillstructwithcurrentdata.
setup_conds：ResolveWHEREconditionandjoinconditions.
setup_group：ResolveandsetuptheGROUPBYlist.
m_having_cond-fix_fields：SetuptheHAVINGclause.
resolve_rollup：ResolveitemsinSELECTlistandORDERBYlistforrollupprocessing.
resolve_rollup_item：Resolveanitem(anditstree)forrollupprocessingbyreplacingitemsmatchinggroupedexpressionswithItem_rollup_group_itemsandupdatingproperties(m_nullable,PROP_ROLLUP_FIELD).AlsocheckanyGROUPINGfunctionforincorrectcolumn.
setup_order：SetuptheORDERBYclause.
resolve_limits：ResolveOFFSETandLIMITclauses.
Window::setup_windows1：Setupwindowsaftersetup_order()andbeforesetup_order_final().
setup_order_final：DofinalsetupofORDERBYclause,afterthequeryblockisfullyresolved.
setup_ftfuncs：Setupfull-textfunctionsafterresolvingHAVING.
resolve_rollup_wfs:ReplacegroupbyfieldreferencesinsidewindowfunctionswithreferencesinthepresenceofROLLUP.
2Transformation
remove_redundant_subquery_clause:Permanentlyremoveredundantpartsfromthequeryif1)Thisisasubquery2)Notnormalizingaview.Removalshouldtakeplacewhenaqueryinvolvingaviewisoptimized,notwhentheviewiscreated.
remove_base_options：RemoveSELECT_DISTINCToptionsfromaqueryblockifcanskipdistinct.
resolve_subquery:Resolvepredicateinvolvingsubquery,performearlyunconditionalsubquerytransformations.
Convertsubquerypredicateintosemi-join,or
Markthesubqueryforexecutionusingmaterialization,or
PerformIN-EXISTStransformation,or
Performmore/lessALL/ANY-MIN/MAXrewrite
Substitutetrivialscalar-contextsubquerywithitsvalue
transform_scalar_subqueries_to_join_with_derived：Transformeligiblescalarsubqueriestoderivedtables.
flatten_subqueries：Convertsemi-joinsubquerypredicatesintosemi-joinjoinnests.Convertcandidatesubquerypredicatesintosemi-joinjoinnests.Thistransformationisperformedonceinquerylifetimeandisirreversible.
apply_local_transforms:
delete_unused_merged_columns:Ifqueryblockcontainsoneormoremergedderivedtables/views,walkthroughlistsofcolumnsinselectlistsandremoveunusedcolumns.
simplify_joins：Convertallouterjoinstoinnerjoinsifpossible
prune_partitions：Performpartitionpruningforagiventableandcondition.
push_conditions_to_derived_tables：Pushingconditionsdowntoderivedtablesmustbedoneaftervaliditychecksofgroupedqueriesdonebyapply_local_transforms();
Window::eliminate_unused_objects：Eliminateunusedwindowdefinitions,redundantsortsetc.
这里，节省篇幅，我们只举例