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

原创 CuiHulong 2023-10-16

1064

介绍

MySQL当中使用派生表通常不能在同一个FROM子句中引用(依赖于)前面表的列。在MySQL 8.0.14中，派生表可以被定义为横向LATERAL派生表，以指定这种引用是允许的。其实就是通过关键字LATERAL，在子查询的结果集中引用外层表达式的列。这种使用方式用于简化查询语句，提高查询效率。
如下，添加了LATERAL关键字，t2表与外层表格t1通过col字段进行关联查询。在包含派生表的FROM子句中进行额外搜索。

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

横向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。

使用场景

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<20
)
SELECT * from cte;

#2）生成node表为基础的笛卡尔积 产生重复数据。
mysql> 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 返回结果集。

下面为了使用类似横向LATERAL功能，把外表的的字段带入到子查询里：
当为了计算一些其他元素，把原表的一些内容代入到子查询需求。语句如下：

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;

INSERT INTO `employees` VALUES (10001,'1953-09-02','Georgi','Facello','M','1986-06-26'),(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'),
(10003,'1959-12-03','Parto','Bamford','M','1986-08-28'),
(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01'),
(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12'),
(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02'),
(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10'),
(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15'),
(10009,'1952-04-19','Sumant','Peac','F','1985-02-18'),
(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');

INSERT INTO `salaries` VALUES (10001,10,'2023-06-26','2023-07-01'),
     (10001,30,'2023-06-26','2023-07-01'),(10001,50,'2023-06-25','2023-07-01'),
     (10002,20,'2023-06-25','2023-07-01'),(10002,30,'2023-06-25','2023-07-01');

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;