MySQL8.0 横向LATERAL derived派生表的支持

介绍

MySQL当中派生表通常不能在同一个FROM子句中引用(依赖于)前面表的列。在MySQL 8.0.14中，派生表可以被定义为侧向派生表，以指定这种引用是允许的。用于优化查询语句，提高查询效率。
MySQL Lateral是一种子查询语法，用于在子查询的结果集中引用外层表达式的列。横向派生表受以下限制:

• 横向派生表只能出现在FROM子句中，可以出现在用逗号分隔的表列表中，也可以出现在联接规范（join、INNER join、CROSS join、LEFT[OUTER]join或RIGHT[OUTER]join）中。
• 如果横向派生表位于联接子句的右操作数中，并且包含对左操作数的引用，则联接操作必须是INNER join、CROSS join或left[OUTER]join。
  如果表在左操作数中并且包含对右操作数的引用，则联接操作必须是INNER join、CROSS join或right[OUTER]join。
• 如果横向派生表引用聚合函数，则函数的聚合查询不能是拥有发生横向派生表的FROM子句的查询。
• 根据SQL标准，MySQL总是将带有表函数（如JSON_table（））的联接视为使用了LATERAL。

在于使用方式方面，表之间JOIN场景下的子查询方式嵌套：

table_reference: {
table_factor
| joined_table
}
table_factor: {
tbl_name [PARTITION (partition_names)]
[[AS] alias] [index_hint_list]
| [LATERAL] table_subquery [AS] alias [(col_list)]
| ( table_references )
}


如果添加了LATERAL关键字，t2表与外层表格t1通过col字段进行关联查询。在包含派生表的FROM子句中进行额外搜索。如下：

mysql> SELECT ... FROM t1, LATERAL (SELECT ... FROM t2
^ WHERE t2.col=t1.col ... ) AS derived;
| |
+---------------------------+


使用场景

1.横向派生表使用场景

那什么场景下使用横向派生表，下面通过例子解析：

#1）先准备20行数据。
mysql> CRRETE TABLE nodes(id int);
mysql> INSERT INTO nodes
with recursive cte(n) as
(
SELECT 1
union all
SELECT n+1 FROM cte where n EXPLAIN SELECT origin_nodes.id, target_nodes.id
FROM nodes as origin_nodes,
(
SELECT id
FROM nodes
order by rand()
LIMIT 2
) as target_nodes;


实现方式 就是结果集for each循环：

select two random target nodes, store into target_nodes
for each row R in origin_nodes:
join R with target_nodes


在看下执行计划：

1）DERIVED派生表里Extra字段显示Using temporary，表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作。，并且需要在临时表上排序。随机排序取前 2 个。
2）通两张表hash join 返回结果集。

当为了计算一些其他元素，把原表的一些内容代入到子查询需求。语句如下：

mysql> EXPLAIN SELECT origin_nodes.id, target_nodes.id
FROM nodes as origin_nodes,
(
SELECT id
FROM nodes
order by rand()+ 0 * origin_nodes.id
LIMIT 2
) as target_nodes;
ERROR 1054 (42S22): Unknown column 'origin_nodes.id' in 'order clause'


在上述语句当中0*origin_nodes.id不会更改ORDER BY子句的值，但它会依赖于origin_nodes表中的值。但是普通派生表不允许依赖于前表的FROM子句中，因此当运行上面的查询时，得到错误提示。

因此，将其设为LATERAL派生表，根据定义，它是：派生表允许依赖于FROM子句以前的表。如下：

mysql> SELECT origin_nodes.id, target_nodes.id
FROM nodes as origin_nodes,
LATERAL (
SELECT id
FROM nodes
order by rand()+0*origin_nodes.id
LIMIT 2
) as target_nodes;


下面在看看执行计划：

注意新的指示：

• DEPENDENT DERIVED：派生表依赖于另一个表。Extra信息里中MATERIALIZED（derived2）派生表物化子查询。
• 在origin_nodes行重新实体化：每次从origin_node读取一行时，MySQL都会重新实体化派生表derived2（这是派生表target_nodes的MySQL内部名称）

备注：
派生表是从SELECT语句返回的虚拟表。派生表类似于临时表，但是在SELECT语句中使用派生表比临时表简单得多，因为它不需要创建临时表的步骤。当SELECT语句的FROM子句中使用独立子查询时，将其称为派生表。与子查询不同，派生表必须具有别名，以便稍后在查询中引用其名称。 如果派生表没有别名，MySQL将发出错误。

2.不是所用场景使用适合横向LATERAL。

没必要的环境就不需要使用横向LATERAL。性能影响会越来越大，可能起到反效果。因为是子查询，所以必然会生成中间表。主要点是在于这个中间表是否体量大，要是体量大，性能影响会越来越大，可能起到反效果。

假设需要统计 销售人员完成的最高单笔金额：
这个问题有两种解决方法。

CREATE TABLE `employees` (
`emp_no` int NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) ,
`last_name` varchar(16) ,
`gender` enum('M','F') ,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB;
CREATE TABLE `salaries` (
`emp_no` int NOT NULL,
`salary` int NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL
) ENGINE=InnoDB;

Lateral也不是所有方式高效的。比如获取销售人员的最大消费记录：
1）通过两张表join ，group 销售人员 和 max销售额度：使用temporary 表，+ 共扫描6rows

mysql> EXPLAIN SELECT e.emp_no, max(s.salary)
FROM employees e inner join salaries s
ON e.emp_no = s.emp_no GROUP BY e.emp_no
ORDER BY max(s.salary) DESC LIMIT 2;


2)通过Lateral方式：Materialize临时表 + 共扫描17rows

mysql> EXPLAIN SELECT e.emp_no, max_sale.salary
FROM employees e, LATERAL (
SELECT s.emp_no, s.salary FROM salaries s
WHERE e.emp_no = s.emp_no
ORDER BY salary DESC LIMIT 1) max_sale;


使用Lateral的SQL语句过程（执行计划）更复杂。效率更差。

总结

MySQL Lateral是一种非常实用的查询优化技术,可以避免嵌套循环查询（Nested Loop Join）和递归查询（Recursive Query）。可以帮助简化查询语句并提高查询效率。