MySQL · 新特性分析 · 5.7中Derived table变形记

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
简介: Derived table实际上是一种特殊的subquery,它位于SQL语句中FROM子句里面,可以看做是一个单独的表。MySQL5.7之前的处理都是对Derived table进行Materialize,生成一个临时表保存Derived table的结果,然后利用临时表来协助完成其他父查询的操作,比如JOIN等操作。MySQL5.7中对Derived table做了一个新特性。该特性允许将符合

Derived table实际上是一种特殊的subquery,它位于SQL语句中FROM子句里面,可以看做是一个单独的表。MySQL5.7之前的处理都是对Derived table进行Materialize,生成一个临时表保存Derived table的结果,然后利用临时表来协助完成其他父查询的操作,比如JOIN等操作。MySQL5.7中对Derived table做了一个新特性。该特性允许将符合条件的Derived table中的子表与父查询的表合并进行直接JOIN。下面我们看一下DBT-3中的一条被新特性优化过的执行计划:

SELECT t2.o_clerk, t1.price - t2.o_totalprice
FROM
    (SELECT l_orderkey, SUM( l_extendedprice * (1 - l_discount)) price
     FROM lineitem GROUP by l_orderkey) t1
JOIN
    (SELECT o_clerk, o_orderkey, o_totalprice
     FROM orders 
     WHERE o_orderdate BETWEEN '1995-01-01' AND '1995-12-31') t2
ON t1.l_orderkey = t2.o_orderkey WHERE t1.price > t2.o_totalprice;

MySQL5.6执行计划如下图所示(下图通过WorkBench的Visual Explain直观的对执行计划进行了展示):

MySQL5.6执行计划

对应的explain输出结果为:

ID SELECT_TYPE	  TABLE	    TYPE	POSSIBLE_KEYS	KEY	    KEY_LEN	REF	            ROWS	    EXTRA
1	PRIMARY  	<derived3>	ALL  	NULL	        NULL	NULL	NULL	        4812318	    NULL
1	PRIMARY	    <derived2>	ref	   <auto_key0>	 <auto_key0>   4	t2.o_orderkey	599860	    Using where; Using index
3	DERIVED       orders	ALL	   i_o_orderdate	NULL	NULL	NULL	        15000000    Using where
2	DERIVED	      lineitem	index	PRIMARY, i_l_shipdate, …	PRIMARY	8	NULL	 59986052	NULL   

MySQL5.7 Merge derived table特性应用之后,执行计划变成了如下所示:

derived-57.png

同样explain的输出结果为:

ID  SELECT_TYPE	TABLE	    PARTITIONS  TYPE	POSSIBLE_KEYS	        KEY	    KEY_LEN	REF    	ROWS	    FILTERED	EXTRA
1	PRIMARY	    <derived2>	NULL	    ALL	    NULL	                NULL	NULL	NULL	59986052	100.00	    NULL
1	PRIMARY	    orders 	    NULL	    eq_ref	PRIMARY, i_o_orderdate	PRIMARY	4	    t1.l_orderkey	1	10.69	    Using where
2	DERIVED	    lineitem	NULL	    index	PRIMARY, i_l_shipdate, …PRIMARY	8	    NULL	59986052	100.00	    NULL

可以看到orders已经从Derived table的子表里面merge到了父查询中,尽而简化了执行计划,同时也提高了执行效率。看一下MySQL5.6与MySQL5.7对于上面的DBT-3中的这条Query执行性能的对比图:

derived-tutorial.png

Merge Derived table有两种方式进行控制。第一种,通过开关optimizer_switch=’derived_merge=on|off’来进行控制。第二种,在CREATE VIEW的时候指定ALGORITHM=MERGE | TEMPTABLE, 默认是MERGE方式。如果指定是TEMPTABLE,将不会对VIEW进行Merge Derived table操作。只要Derived table里不包含如下条件就可以利用该特性进行优化:

  • UNION clause
  • GROUP BY
  • DISTINCT
  • Aggregation
  • LIMIT or OFFSET
  • Derived table里面包含用户变量的设置。

那么Merge Derived table在MySQL中是如何实现的呢?下面我们分析一下源码。
对于Derived table的merge过程是在MySQL的resolve阶段完成的,这意味着对于Merge操作是永久性的,经过resolve阶段之后就不会再对Derived table进行其他的变换。执行的简单流程如下:

SELECT_LEX::prepare

       |

TABLE_LIST::resolve_derived // 这里首先递归对每个Derived table自身进行变换,经过变换后的Derived table开始考虑和最外层的父查询进行Merge

       |

SELECT_LEX::merge_derived // 将Derived table与父查询进行Merge

下面我们重点研究一下merge_derived这个函数实现过程:

bool SELECT_LEX::merge_derived(THD *thd, TABLE_LIST *derived_table)
{   
  DBUG_ENTER("SELECT_LEX::merge_derived");
  
  // 这里首先会判断是不是Derived table(这里view看做是带有名字的Derived table),同时也会看该Derived table是否已经被合并过了
  if (!derived_table->is_view_or_derived() || derived_table->is_merged())
    DBUG_RETURN(false);

  SELECT_LEX_UNIT *const derived_unit= derived_table->derived_unit();
  
  // A derived table must be prepared before we can merge it
  DBUG_ASSERT(derived_unit->is_prepared());

  LEX *const lex= parent_lex;
  
  // Check whether the outer query allows merged views
  if ((master_unit() == lex->unit && // 只会在父查询进行merge Derived table操作。
 	   // 这里会查看当前命令是否需要进行merge操作,比如CREATE VIEW,SHOW CREATE VIEW等。如果需要再继续
       !lex->can_use_merged()) ||  
      lex->can_not_use_merged()) 
    DBUG_RETURN(false);

 // 查看当前的Derived table是否满足merge条件
  if (!derived_unit->is_mergeable() ||
      derived_table->algorithm == VIEW_ALGORITHM_TEMPTABLE ||
      (!thd->optimizer_switch_flag(OPTIMIZER_SWITCH_DERIVED_MERGE) &&
       derived_table->algorithm != VIEW_ALGORITHM_MERGE))
    DBUG_RETURN(false);

  SELECT_LEX *const derived_select= derived_unit->first_select();
  /*
	当前不会对包含 STRAIGHT_JOIN,且Derived table中包含semi-join的query进行merge操作。
	这是因为MySQL为了保证正确性,必须先做semi-join之后才可以与其他表继续做JOIN。
	例如:select straight_join * from tt , (select * from tt where a in (select a from t1)) 	as ttt;
  */
  if ((active_options() & SELECT_STRAIGHT_JOIN) && derived_select->has_sj_nests)
    DBUG_RETURN(false);

	...

  // 利用Nested_join结构来辅助处理OUTER-JOIN的情况。如果Derived table是OUTER-JOIN的内表,需要将Derived table中的每个表设置为JOIN的时候可以为空。具体请参考propagate_nullability。
  if (!(derived_table->nested_join=
       (NESTED_JOIN *) thd->mem_calloc(sizeof(NESTED_JOIN))))
    DBUG_RETURN(true);        /* purecov: inspected */
  // 这里确保NESTED_JOIN结构是空的,在构造函数处理比较合适
  derived_table->nested_join->join_list.empty();
  // 该函数会将所有Derived table中的表merge到NESTED_JOIN结构体中
  if (derived_table->merge_underlying_tables(derived_select))
    DBUG_RETURN(true);       /* purecov: inspected */

  // 接下来需要将Derived table中的所有表连接到父查询的table_list列表中,进而将Derived table从父查询中剔除。
  for (TABLE_LIST **tl= &leaf_tables; *tl; tl= &(*tl)->next_leaf)
  {
    if (*tl == derived_table)
    {
      for (TABLE_LIST *leaf= derived_select->leaf_tables; leaf;
           leaf= leaf->next_leaf)
      {
        if (leaf->next_leaf == NULL)
        {
          leaf->next_leaf= (*tl)->next_leaf;
          break;
        }
      }
      *tl= derived_select->leaf_tables;
      break;
    }
  }
  // 下面会对父查询的所有相关数据结构进行重新计算,进而包含所有从Derived table merge之后的表的相关信息。
  leaf_table_count+= (derived_select->leaf_table_count - 1);
  derived_table_count+= derived_select->derived_table_count;
  materialized_derived_table_count+=
    derived_select->materialized_derived_table_count;
  has_sj_nests|= derived_select->has_sj_nests;
  partitioned_table_count+= derived_select->partitioned_table_count;
  cond_count+= derived_select->cond_count;
  between_count+= derived_select->between_count;

  // Propagate schema table indication:
  // @todo: Add to BASE options instead
  if (derived_select->active_options() & OPTION_SCHEMA_TABLE)
    add_base_options(OPTION_SCHEMA_TABLE);

  // Propagate nullability for derived tables within outer joins:
  if (derived_table->is_inner_table_of_outer_join())
    propagate_nullability(&derived_table->nested_join->join_list, true);

  select_n_having_items+= derived_select->select_n_having_items;

  // 将Derived table的where条件合并到父查询
  if (derived_table->merge_where(thd))
    DBUG_RETURN(true);        /* purecov: inspected */
  // 将Derived table的结构从父查询中删除
  derived_unit->exclude_level();

  // 这里用来禁止对Derived table的继续访问
  derived_table->set_derived_unit((SELECT_LEX_UNIT *)1);

  // 建立对Derived table需要获取的列的引用。在后续函数中会对引用列进行相关处理,请参考函数setup_natural_join_row_types函数
  if (derived_table->create_field_translation(thd))  
    DBUG_RETURN(true); 

  // 将Derived table中的列或者表的重命名合并到父查询
  merge_contexts(derived_select);
  repoint_contexts_of_join_nests(derived_select->top_join_list);

  // 因为已经把Derived table中包含的表merge到了父查询,所以需要对TABLE_LIST中的表所在的位置进行重新定位。
  remap_tables(thd);

  // 将Derived table合并到父查询之后,需要重新修改原来Derived table中所有对Derived table中所有列的引用,
  fix_tables_after_pullout(this, derived_select, derived_table, table_adjust);

  // 如果Derived table中包含ORDER By语句,处理原则和正常SubQuery的处理方式类似:
  //  1. 如果Derived table只包含一个表
  //  2. 并且Derived table不包含聚集函数
  // 满足上述两个条件之后,Derived table将会保留ORDER BY。其他情况subquery中的ORDER BY将会被忽略掉,这也是MySQL5.7区别于MySQL5.6的一点。

  //  当Derived table保留了Order by,是否能合并到父查询,需要满足如下条件:
  // 	1. 父查询允许做Derived table中的ORDER BY。下面几种情况不允许做ORDER BY
  // 		a) 如果父查询包含有自己的ORDER BY
  // 		b) 如果父查询包含GROUP BY
  //         c) 如果父查询包含未被优化掉的DISTINCT
  // 	2. 父查询不能是UNION操作,因为UNION默认会做DISTINCT操作
  //     3. 为了简化操作,只有当父查询只包含Derived table的时候(即FROM子句里面只有Derived table一个表)才可以保留ORDER BY。这里有相当大的改进空间可以尽量的来按照Derived table定义的ORDER BY操作来进行父查询的操作。比如有两个表以上,如果父查询没有ORDER BY的要求,也可以按照Derived table来对结果进行排序。
  if (derived_select->is_ordered())
  {
    if ((lex->sql_command == SQLCOM_SELECT ||
         lex->sql_command == SQLCOM_UPDATE ||
         lex->sql_command == SQLCOM_DELETE) &&
        !(master_unit()->is_union() ||
          is_grouped() ||
          is_distinct() ||
          is_ordered() ||
          get_table_list()->next_local != NULL))
      order_list.push_back(&derived_select->order_list);
  }

  // 对于Derived table中包含的full-text functions需要添加到父查询的查询列表中。
  if (derived_select->ftfunc_list->elements &&
      add_ftfunc_list(derived_select->ftfunc_list))
    DBUG_RETURN(true);        /* purecov: inspected */

  DBUG_RETURN(false);
}



综上所述,本篇文章简要的分析了MySQL Merge Derived table的作用以及实现方式。Merge Derived table的引入可以有效的提升Subquery query的执行效率,更重要的是为以后应对复杂查询提供了新的优化手段。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现,而持久性则依赖于事务日志中的`Redo Log`。在MySQL中,`Redo Log`确保已提交事务的数据能持久保存,即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改,待事务提交时写入磁盘。此外,`Redo Log`采用简单的物理日志格式和高效的顺序IO,确保快速提交。通过不同的落盘策略,可在性能和安全性之间做出权衡。
1611 14
|
18天前
|
存储 关系型数据库 MySQL
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则,包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限,后者又优先于表级别权限。在权限冲突时,更严格的权限将被优先执行,确保数据库的安全性与资源合理分配。
|
2月前
|
JSON 关系型数据库 MySQL
MySQL 8.0 新特性
MySQL 8.0 新特性
138 10
MySQL 8.0 新特性
|
2月前
|
SQL 关系型数据库 MySQL
MySQL 8.0报错--1118-Row size too large. The maximum row size for the used table type, not counting BLOBs,is 8126,
MySQL 8.0报错--1118-Row size too large. The maximum row size for the used table type, not counting BLOBs,is 8126,
MySQL 8.0报错--1118-Row size too large. The maximum row size for the used table type, not counting BLOBs,is 8126,
|
1月前
|
SQL 安全 关系型数据库
MySQL8.2有哪些新特性?
【10月更文挑战第3天】MySQL8.2有哪些新特性?
34 2
|
1月前
|
SQL 关系型数据库 MySQL
MySQL 更新1000万条数据和DDL执行时间分析
MySQL 更新1000万条数据和DDL执行时间分析
80 4
|
1月前
|
SQL 自然语言处理 关系型数据库
Vanna使用ollama分析本地MySQL数据库
这篇文章详细介绍了如何使用Vanna结合Ollama框架来分析本地MySQL数据库,实现自然语言查询功能,包括环境搭建和配置流程。
162 0
|
2月前
|
Oracle NoSQL 关系型数据库
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
310 2
|
28天前
|
SQL 关系型数据库 MySQL
MySQL异常一之: You can‘t specify target table for update in FROM clause解决办法
这篇文章介绍了如何解决MySQL中“不能在FROM子句中指定更新的目标表”(You can't specify target table for update in FROM clause)的错误,提供了错误描述、需求说明、错误做法和正确的SQL写法。
286 0
|
2月前
|
存储 关系型数据库 MySQL
分析MySQL主从复制中AUTO_INCREMENT值不一致的问题
通过对 `AUTO_INCREMENT`不一致问题的深入分析和合理应对措施的实施,可以有效地维护MySQL主从复制环境中数据的一致性和完整性,确保数据库系统的稳定性和可靠性。
98 6

相关产品

  • 云数据库 RDS MySQL 版
  • 下一篇
    无影云桌面