简介:本文重点介绍了优化器的基于规则的其中一部分优化,更多的偏重于SQL中的基本操作符
我们都知道,利用编写程序来动态实现我们应用所需要的逻辑,从而程序执行时得到我们需要的结果。那么数据库就是一种通过输入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的解析转换过程,第二部分介绍更为复杂的子查询、分区表和连接的复杂转换过程,大纲如下:
转换的整个框架是由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等。
SELECT t1.c1 FROM t1, (SELECT t2.c1 FROM t2, (SELECT t3.c1 FROM t3 UNION SELECT t4.c1 FROM t4) AS t3a) AS t2a;
未在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中。
过程简化为:
merge_derived 图解过程
看起来官方的derived merge还是不够完美,无法自底向上的递归merge包含的opt trace:
trace_derived.add_utf8_table(derived_table) .add("select#", derived_query_block->select_number) .add("merged", true); trace_derived.add_alnum("transformations_to_derived_table", "removed_ordering");
该优化可以通过set optimizer_switch="derived_merge=on/off"来控制。
对于剩下不能采用 merge 算法的 derived table ,会转为materialize 物化方式去处理。但此时只是做一些变量设置等预处理,实际的物化执行是在executor阶段执行。
trace_derived.add_utf8_table(this) .add("select#", derived->first_query_block()->select_number) .add("materialized", true);
如果 query block 中有 table function,整个过程会处理两遍。第一遍会跳过 table function 的 table ,第二遍才专门再对table function 的 table 执行一遍上述逻辑。这里的考虑应该是先 resolve 了外部环境(相对于table function),因为有可能函数参数会有依赖外部的 derived table。
trace_derived.add_utf8_table(this) .add_utf8("function_name", func_name, func_name_len) .add("materialized", true);
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 语句,可以使得通过单个查询语句来实现对数据进行不同层级上的分析与统计。
SELECT YEAR, country, product, SUM(profit) AS profit FROM sales GROUP BY YEAR, country, product WITH ROLLUP; +------+---------+------------+--------+ | year | country | product | profit | +------+---------+------------+--------+ | 2000 | Finland | Computer | 1500 | | 2000 | Finland | Phone | 100 | | 2000 | Finland | NULL | 1600 | | 2000 | India | Calculator | 150 | | 2000 | India | Computer | 1200 | | 2000 | India | NULL | 1350 | | 2000 | USA | Calculator | 75 | | 2000 | USA | Computer | 1500 | | 2000 | USA | NULL | 1575 | | 2000 | NULL | NULL | 4525 | | 2001 | Finland | Phone | 10 | | 2001 | Finland | NULL | 10 | | 2001 | USA | Calculator | 50 | | 2001 | USA | Computer | 2700 | | 2001 | USA | TV | 250 | | 2001 | USA | NULL | 3000 | | 2001 | NULL | NULL | 3010 | | NULL | NULL | NULL | 7535 | +------+---------+------------+--------+ 相当于做了下面的查询: SELECT * FROM (SELECT YEAR, country, product, SUM(profit) AS profit FROM sales GROUP BY YEAR, country, product UNION ALL SELECT YEAR, country, NULL, SUM(profit) AS profit FROM sales GROUP BY YEAR, country UNION ALL SELECT YEAR, NULL, NULL, SUM(profit) AS profit FROM sales GROUP BY YEAR UNION ALL SELECT NULL, NULL, NULL, SUM(profit) AS profit FROM sales) AS sum_table ORDER BY YEAR, country, product; +------+---------+------------+--------+ | YEAR | country | product | profit | +------+---------+------------+--------+ | NULL | NULL | NULL | 7535 | | 2000 | NULL | NULL | 4525 | | 2000 | Finland | NULL | 1600 | | 2000 | Finland | Computer | 1500 | | 2000 | Finland | Phone | 100 | | 2000 | India | NULL | 1350 | | 2000 | India | Calculator | 150 | | 2000 | India | Computer | 1200 | | 2000 | USA | NULL | 1575 | | 2000 | USA | Calculator | 75 | | 2000 | USA | Computer | 1500 | | 2001 | NULL | NULL | 3010 | | 2001 | Finland | NULL | 10 | | 2001 | Finland | Phone | 10 | | 2001 | USA | NULL | 3000 | | 2001 | USA | Calculator | 50 | | 2001 | USA | Computer | 2700 | | 2001 | USA | TV | 250 | +------+---------+------------+--------+
排序由于有NULL的问题,所以分级汇总的效果非常难弄,而且group 列不同改变,SQL复杂度来回变化,而ROLLUP很简单就可以实现效果,下面看下rollup在解析过程做了什么样的转换达到了意想不到的效果。
其中一个函数find_order_in_list(): 尝试在select fields里去寻找可以映射的列,否则就得在最后投影的all fields里加上当前列,同时也做fix_fields。
select c1 from t1 where t1.c2 in (select distinct c1 from t2 group by c1, c2 order by c1); 转化为 => select c1 from t1 where t1.c2 in (select c1 from t2);
is_grouped() && hidden_group_field_count == 0 && olap == UNSPECIFIED_OLAP_TYPE
例如场景:
SELECT DISTINCT c1, max(c2) from t1 group by c1;
SELECT id, release_year, rating, avg(rating) over(PARTITION BY release_year) AS year_avg FROM tw; +------+--------------+--------+-------------------+ | id | release_year | rating | year_avg | +------+--------------+--------+-------------------+ | 1 | 2015 | 8 | 8.5 | | 3 | 2015 | 9 | 8.5 | | 2 | 2015 | 8.5 | 8.5 | | 4 | 2016 | 8.2 | 8.3 | | 5 | 2016 | 8.4 | 8.3 | | 6 | 2017 | 7 | 7 | +------+--------------+--------+-------------------+
执行的过程和结果类似于下图:
我们看下它在开始Query_block::prepare解析过程做了哪些事情:
select_lex->m_windows 不为空,就调用 Window::setup_windows1
本文重点介绍了下优化器的基于规则的其中一部分优化,更多的偏重于SQL中的基本操作符,如表、列、函数、聚合、分组、排序等元素的解析和设置以及一些显而易见的结构变化。下一篇文章我们将继续介绍子查询、分区表和JOIN操作的转换部分,敬请期待。
PolarDB 是阿里巴巴自主研发的云原生分布式关系型数据库,于2020年进入Gartner全球数据库Leader象限,并获得了2020年中国电子学会颁发的科技进步一等奖。PolarDB 基于云原生分布式数据库架构,提供大规模在线事务处理能力,兼具对复杂查询的并行处理能力,在云原生分布式数据库领域整体达到了国际领先水平,并且得到了广泛的市场认可。在阿里巴巴集团内部的最佳实践中,PolarDB还全面支撑了2020年天猫双十一,并刷新了数据库处理峰值记录,高达1.4亿TPS。欢迎有志之士加入我们,简历请投递到daoke.wangc@alibaba-inc.com,期待与您共同打造世界一流的下一代云原生分布式关系型数据库。
原文链接:https://developer.aliyun.com/article/790379?
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。