概念描述

在做MySQL的SQL优化时，经常会遇到下面这两情况：

• 谓词无法推入子查询
• 取分组内最小/最大值记录

在MySQL8.0.14版本之后，这个问题可以使用横向派生表（lateral-derived-tables）来解决，lateral功能类似于Oracle的APPLY语法。虽然lateral不是适用所有场景，但它确实为上述两种情景提供了一种优化思路。下面我们看一下lateral的基本用法、性能、及其适用场景。

测试验证

创建测试数据：

-- 创建两张表(结构一样)
CREATE TABLE table1 (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(10) DEFAULT NULL,
  age int DEFAULT NULL,
  PRIMARY KEY (id),
  KEY ix_name (name)
) ENGINE=InnoDB;

CREATE TABLE table2 (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(10) DEFAULT NULL,
  age int DEFAULT NULL,
  PRIMARY KEY (id),
  KEY ix_name (name)
) ENGINE=InnoDB;

-- 表1插入10条数据
insert into table1
select rn, concat('name', rn), rn*10 from
(select row_number() over() as rn from information_schema.columns limit 10) tmp;
-- 表2插入100条数据
insert into table2
select rn, concat('name', (rn-1)%50+1), rn from
(select row_number() over() as rn from information_schema.columns limit 100) tmp;

lateral基本用法：

mysql> explain
    -> select a.id, a.name, b.age
    -> from table1 a
    -> inner join lateral-- 在原来SQL的基础上加lateral关键字
    -> (
    -> select m.name,m.age
    -> from table2 m
    -> where m.name = a.name  -- 子查询中可以引用外部a表的字段
    -> ) b
    -> -- on 1=1   -- on中的条件可以不写，也可以写成1=1
    -> -- on a.name=b.name   -- on中的条件存在时，a、b表join lateral后会应用此条件
    -> where a.id = 1;
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys   | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | a     | NULL       | const | PRIMARY,ix_name | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | m     | NULL       | ref   | ix_name         | ix_name | 43      | const |    2 |   100.00 | NULL  |