- ubuntu12.04环境下使用kvm ioctl接口实现最简单的虚拟机
- Ubuntu 通过无线网络安装Ubuntu Server启动系统后连接无线网络的方法
- 在Ubuntu上搭建网桥的方法
- ubuntu 虚拟机上网方式及相关配置详解
CFSDN坚持开源创造价值,我们致力于搭建一个资源共享平台,让每一个IT人在这里找到属于你的精彩世界.
这篇CFSDN的博客文章图解MySQL 8.0优化器查询解析篇由作者收集整理,如果你对这篇文章有兴趣,记得点赞哟.
。
我们都知道,利用编写程序来动态实现我们应用所需要的逻辑,从而程序执行时得到我们需要的结果。那么数据库就是一种通过输入SQL字符串来快速获取数据的应用。当然,假设没有数据库这种系统应用,用程序如何实现呢?我们可能会发现,即使不管数据如何存储、数据是否并发访问,仍然需要不断通过修改程序处理不同应用对数据的不同请求。比如大数据领域,我们通常通过非关系型数据库的API,实现对数据的获取。然而这种方式虽然入门简单,但是维护极难,而且通用性不强,即使不断进行软件架构设计或者抽象重构,仍然需要不断地变换应用,这也是为何非关系型数据库回头拥抱数据库SQL优化器的原因.
SQL优化器本质上是一种高度抽象化的数据接口的实现,经过该设计,客户可以使用更通用且易于理解的SQL语言,对数据进行操作和处理,而不需要关注和抽象自己的数据接口,极大地解放了客户的应用程序.
本文就来通过图形解说的方式介绍下MySQL 8.0 SQL优化器如何把一个简单的字符串(SQL),变成数据库执行器可以理解的执行序列,最终将数据返还给客户。强大的优化器是不需要客户关注SQL如何写的更好来更快获得需要的数据,因此优化器对原始SQL一定会做一些等价的变化。在 《MySQL 8.0 Server层最新架构详解》 一文中我们重点介绍了MySQL最新版本关于Server层解析器、优化器和执行器的总体介绍,包括一些代码结构和变化的详细展示,并且通过simple_joins函数抛砖引玉展示了MySQL优化器在逻辑变换中如何简化嵌套Join的优化。本文我们会一步一步带你进入神奇的优化器细节,详细了解优化器优化部分的每个步骤如何改变着一个SQL最终的执行.
本文基于最新MySQL8.0.25版本,因为优化器转换部分篇幅比较长,我们分成两篇文章来介绍,第一部分介绍基于基本结构的Setup和Resolve的解析转换过程,第二部分介绍更为复杂的子查询、分区表和连接的复杂转换过程,大纲如下:
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. 。
转换的整个框架是由Query_expression到Query_block调用prepare函数(sql/sql_resolver.cc)并且根据不同转换规则的要求自顶向下或者自底向上的过程.
prepare开始先要处理nullable table,它指的是table可能包含全为null的row,根据JOIN关系(top_join_list)null row可以被传播。如果能确定一个table为nullable会使得一些优化退化,比如access method不能为EQ_REF、outer join不能优化为inner join等.
未在setup_table调用之前,每个Query_block的leaf_tables是为0的.
该函数的作用就是构建leaf_tables,包括base tables和derived tables列表,用于后续的优化。setup_tables并不会递归调用,而是只解决本层的tables,并统计出本层derived table的个数。但是随后会调用resolve_placeholder_tables()->resolve_derived()->derived(Query_expression)::prepare->Query_block::prepare来专门递归处理derived table对应的Query_expression.
接下来我们根据prepare的调用顺序,继续看下针对于derived table处理的函数resolve_placeholder_tables.
这个函数用于对derived table、view和table function的处理,如果该table已经merged过了,或者是由于使用transform_grouped_to_derived()被调用到,已经决定使用materialized table方式,则直接忽略.
前面已经介绍过resolve_derived()的作用,我们重点介绍merge_derived()函数,merge_derived是改变Query_expression/Query_block框架结构,将derived table或者view合并到到query block中.
1)merge_derived transformation的先决条件 。
外层query block是否允许merge(allow_merge_derived) 。
外层query block为nullptr 。
外层query expression的子查询为nullptr,derived table是第一层子查询 。
外层的外层query block可以allow_merge_derived=true,或者不包括外层的外层query block话是否为SELECT/SET 。
外层lex是否可以支持merge(lex->can_use_merged()+lex->can_no_use_merged()) 。
derived table是否已经被标记为需要物化materialize,比如创建视图的方法是CREATE ALGORITHM=TEMPTABLE VIEW(derived_table->algorithm == VIEW_ALGORITHM_TEMPTABLE) 。
整个dervived table所在的查询表达式单元中,不能是(Query_expression::is_mergeable() ):
Union查询 。
包含聚集、HAVING、DISTINCT、WINDOWS或者LIMIT 。
没有任何table list 。
HINT或者optimizer_switch没有禁止derived_merge; 。
heuristic建议合并(derived_query_expressionmerge_heuristic()); 。
如果derived table包含的子查询SELECT list依赖于自己的列时,不支持; 。
如果是dependant subquery需要多次执行时,不支持; 。
derived table中如果查询块包含SEMI/ANTI-JOIN,并指定STRAIGHT_JOIN时,不支持; 。
如果合并的derived table和现有query block的leaf table count大约 MAX_TABLES时,不支持; 。
2)merge_derived transformation的转换过程 。
利用derived_table->nested_join结构来辅助处理OUTER JOIN的情况.
把derived table中的表merge到NESTED_JOIN结构体(derived_table->merge_underlying_tables()).
将derived table中的所有表连接到父查询的table_list列表中,同时把derived table从父查询中删除.
对父查询的所有相关数据结构进行重新计算(leaf_table_count、derived_table_count、table_func_count、materialized_derived_table_count、has_sj_nests、has_aj_nests、partitioned_table_count、cond_count、between_count、select_n_having_items).
传播设置父查询OPTION_SCHEMA_TABLE(add_base_options())和如果是外查询JOIN的内表,传播设置nullable属性(propagate_nullability()).
合并derived table的where条件到外查询中(merge_where()).
建立对derived table需要获取的列的引用(create_field_translation()).
将Derived table的结构从父查询中删除(exclude_level()).
将derived table中的列或者表的重命名合并到父查询(fix_tables_after_pullout()/repoint_contexts_of_join_nests()).
因为已经把derived table中包含的表merge到了父查询,所以需要对TABLE_LIST中的表所在的位置进行重新定位(remap_tables()).
将derived table合并到父查询之后,需要重新修改原来derived table中所有对derived table中所有列的引用(fix_tables_after_pullout()).
如果derived table中包含ORDER BY语句,如果满足下列条件,derived table将会保留ORDER BY并合并到父查询中,其他情况ORDER BY将会被忽略掉:
如果父查询允许排序并且正好是只有derived table 。
不是一个UNION 。
可以有WHERE条件,但是不能有group by或聚合函数 。
本身并不是有序的 。
过程简化为:
merge_derived 图解过程 。
看起来官方的derived merge还是不够完美,无法自底向上的递归merge 。
包含的opt trace:
该优化可以通过set optimizer_switch="derived_merge=on/off"来控制.
对于剩下不能采用 merge 算法的 derived table ,会转为materialize 物化方式去处理。但此时只是做一些变量设置等预处理,实际的物化执行是在executor阶段执行.
setup_materialized_derived_tmp_table(): 设置一个临时表包含物化Derived Table的所有行数据.
如果 query block 中有 table function,整个过程会处理两遍。第一遍会跳过 table function 的 table ,第二遍才专门再对table function 的 table 执行一遍上述逻辑。这里的考虑应该是先 resolve 了外部环境(相对于table function),因为有可能函数参数会有依赖外部的 derived table.
base_ref_items记录了所有Item的位置,方便查询块的其他Item可以进行引用,或者通过Item_ref及其Item_ref子类进行直接引用,例如子查询的引用(Item_view_ref)、聚合函数引用(Item_aggregate_ref)、外查询列的引用(Item_outer_ref)、subquery 子查询产生NULL value的引用辅助(Item_ref_null_helper).
举例说明比较复杂的Item_outer_ref:
下图是比较复杂的带子查询的fixed field过程。有些field和表关联,有的要添加相应的Item_xxx_ref引用.
setup_join_cond如果有nested_join会递归调用setup_join_cond进行解析和设置。这里也顺带介绍下simplify_const_condition函数的作用,如果发现可以删除的const Item,则会用Item_func_true/Item_func_false来替代整个的条件,如图.
在数据库查询语句中,在 GROUP BY 表达式之后加上 WITH ROLLUP 语句,可以使得通过单个查询语句来实现对数据进行不同层级上的分析与统计.
排序由于有NULL的问题,所以分级汇总的效果非常难弄,而且group 列不同改变,SQL复杂度来回变化,而ROLLUP很简单就可以实现效果,下面看下rollup在解析过程做了什么样的转换达到了意想不到的效果.
其中一个函数find_order_in_list(): 尝试在select fields里去寻找可以映射的列,否则就得在最后投影的all fields里加上当前列,同时也做fix_fields.
m_having_cond->fix_fields : 对having条件进行fixed_fields.
resolve_limits : 处理OFFSET和LIMIT子句(offset_limit和select_limit的Items).
setup_ftfuncs : 如果有full-text的函数,对相关Item进行fix_fields.
remove_redundant_subquery_clause : 对于Table Subquery的表达式,通常是IN/ANY/ALL/EXISTS/etc,如果没有聚合函数和Having子句,通常可以考虑删除不必要的ORDER/DISTINCT/GROUP BY。该函数支持三种REMOVE_ORDER | REMOVE_DISTINCT | REMOVE_GROUP,如果是SINGLEROW_SUBS的子查询,只考虑删除REMOVE_ORDER.
处理是否可以删除不必要的distinct语句,删除的条件就是GROUP BY的列都在SELECT列表中,并且没有ROLLUP和Window函数.
例如场景:
执行的过程和结果类似于下图:
我们看下它在开始Query_block::prepare解析过程做了哪些事情:
select_lex->m_windows 不为空,就调用 Window::setup_windows1 。
遍历window函数列表,调用resolve_window_ordering来解析m_partition_by和m_order_by 。
处理inter-window的引用关系(如WINDOW w1 AS (w2), w2 AS (), w3 AS (w1)),但必须是一个有向无环图(DAG) 。
重新遍历检查是否唯一名字check_unique_name、创建window partition by和window order by的引用items 。
检查窗口函数特征(Window::check_window_functions1(THD *thd, _block *select)) 。
首先判断的是当前是静态窗口还是动态窗口;静态窗口即判断了 frame 的定义是否有定义上下边界。 m_static_aggregates 为 true, 意味着是静态窗口,同时对每一个分区都可以进行一次评估。如果 ma_static_aggregates 为 false, 则进一步判断其滑动窗口使用的是基于范围还是基于行。 m_row_optimizable 基于行 m_range_optimizable 基于范围 。
获取聚合函数作为窗口函数时候窗口的特殊规格要求 wfs->check_wf_semantics1(thd, select, &reqs) 这个方法其实就是判断是不是需要row_buffer作为评估,如果我们只看当前分区的行无法进行正确的计算不需要,而需要看之后的或者之前的行,就需要使用row_buffer.
本文重点介绍了下优化器的基于规则的其中一部分优化,更多的偏重于SQL中的基本操作符,如表、列、函数、聚合、分组、排序等元素的解析和设置以及一些显而易见的结构变化。下一篇文章我们将继续介绍子查询、分区表和JOIN操作的转换部分,敬请期待.
原文链接:https://mp.weixin.qq.com/s?__biz=MzIzOTU0NTQ0MA==&mid=2247504986&idx=1&sn=7e288e29957f96b9d92b25b5b4946b8a&utm_source=tuicool&utm_medium=referral 。
最后此篇关于图解MySQL 8.0优化器查询解析篇的文章就讲到这里了,如果你想了解更多关于图解MySQL 8.0优化器查询解析篇的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。
比较代码: const char x = 'a'; std::cout > (0C310B0h) 00C3100B add esp,4 和 const i
您好,我正在使用 Matlab 优化求解器,但程序有问题。我收到此消息 fmincon 已停止,因为目标函数值小于目标函数限制的默认值,并且约束满足在约束容差的默认值范围内。我也收到以下消息。警告:矩
处理Visual Studio optimizations的问题为我节省了大量启动和使用它的时间 当我必须进行 J2EE 开发时,我很难回到 Eclipse。因此,我还想知道人们是否有任何提示或技巧可
情况如下:在我的 Excel 工作表中,有一列包含 1-name 形式的条目。考虑到数字也可以是两位数,我想删除这些数字。这本身不是问题,我让它工作了,只是性能太糟糕了。现在我的程序每个单元格输入大约
这样做有什么区别吗: $(".topHorzNavLink").click(function() { var theHoverContainer = $("#hoverContainer");
这个问题已经有答案了: 已关闭11 年前。 Possible Duplicate: What is the cost of '$(this)'? 我经常在一些开发人员代码中看到$(this)引用同一个
我刚刚结束了一个大型开发项目。我们的时间紧迫,因此很多优化被“推迟”。既然我们已经达到了最后期限,我们将回去尝试优化事情。 我的问题是:优化 jQuery 网站时您要寻找的最重要的东西是什么。或者,我
所以我一直在用 JavaScript 编写游戏(不是网络游戏,而是使用 JavaScript 恰好是脚本语言的游戏引擎)。不幸的是,游戏引擎的 JavaScript 引擎是 SpiderMonkey
这是我在正在构建的页面中使用的 SQL 查询。它目前运行大约 8 秒并返回 12000 条记录,这是正确的,但我想知道您是否可以就如何使其更快提出可能的建议? SELECT DISTINCT Adve
如何优化这个? SELECT e.attr_id, e.sku, a.value FROM product_attr AS e, product_attr_text AS a WHERE e.attr
我正在使用这样的结构来测试是否按下了所需的键: def eventFilter(self, tableView, event): if event.type() == QtCore.QEven
我正在使用 JavaScript 从给定的球员列表中计算出羽毛球 double 比赛的所有组合。每个玩家都与其他人组队。 EG。如果我有以下球员a、b、c、d。它们的组合可以是: a & b V c
我似乎无法弄清楚如何让这个 JS 工作。 scroll function 起作用但不能隐藏。还有没有办法用更少的代码行来做到这一点?我希望 .down-arrow 在 50px 之后 fade out
我的问题是关于用于生产的高级优化级联样式表 (CSS) 文件。 多么最新和最完整(准备在实时元素中使用)的 css 优化器/最小化器,它们不仅提供删除空格和换行符,还提供高级功能,如删除过多的属性、合
我读过这个: 浏览器检索在 中请求的所有资源开始呈现 之前的 HTML 部分.如果您将请求放在 中section 而不是,那么页面呈现和下载资源可以并行发生。您应该从 移动尽可能多的资源请求。
我正在处理一些现有的 C++ 代码,这些代码看起来写得不好,而且调用频率很高。我想知道我是否应该花时间更改它,或者编译器是否已经在优化问题。 我正在使用 Visual Studio 2008。 这是一
我正在尝试使用 OpenGL 渲染 3 个四边形(1 个背景图,2 个 Sprite )。我有以下代码: void GLRenderer::onDrawObjects(long p_dt) {
我确实有以下声明: isEnabled = false; if(foo(arg) && isEnabled) { .... } public boolean foo(arg) { some re
(一)深入浅出理解索引结构 实际上,您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(no
一、写在前面 css的优化方案,之前没有提及,所以接下来进行总结一下。 二、具体优化方案 2.1、加载性能 1、css压缩:将写好的css进行打包,可以减少很多的体积。 2、css单一样式:在需要下边
我是一名优秀的程序员,十分优秀!