LATERAL Derived Tables in MySQL 8.0

自8.0.14版本以来,MySQL支持横向派生表,有时称为为每次循环的SQL。他们是什么?它们是如何工作的?你为什么需要它们?他们能做什么?你如何使用它们?你应该用它们吗?对于每个循环,这都是关于什么的呢?
在本课程中,我们将解释这些概念,查看如何和何时使用这个新特性的示例,并讨论如何通过MySQL优化和执行横向派生表。

展开查看详情

1.LATERAL Derived Tables in MySQL 8.0 Norvald H. Ryeng Software Development Senior Manager MySQL Optimizer Team May 29, 2019 Copyright © 2019 Oracle and/or its affiliates. All rights reserved.

2.LATERAL what? Copyright © 2019 Oracle and/or its affiliates. All rights reserved. 2

3.Derived Tables ● Derived tables are subqueries in FROM clauses SELECT … FROM t1, (subquery) AS derived, t2 … ● Two execution methods – Materialized – Merged into the outer query Copyright © 2019 Oracle and/or its affiliates. All rights reserved. 3

4.Copyright © 2019 Oracle and/or its affiliates. All rights reserved. 4

5.LATERAL Derived Tables ● Can refer to other tables in the same FROM clause SELECT … FROM t1, LATERAL (SELECT … FROM … WHERE … = t1.col) AS derived, t2 … – Only to tables that appear before it in the FROM clause – Including other derived tables ● Two execution methods – Materialized – Merged into the outer query ● SQL feature T491 Copyright © 2019 Oracle and/or its affiliates. All rights reserved. 5

6.LATERAL Derived Tables ✓ ● Can refer to other tables in the same FROM clause SELECT … FROM t1, LATERAL (SELECT … FROM … WHERE … = t1.col) AS derived, t2 … – Only to tables that appear before it in the FROM clause – Including other derived tables ● Two execution methods – Materialized – Merged into the outer query ● SQL feature T491 Copyright © 2019 Oracle and/or its affiliates. All rights reserved. 6

7.Implicitly LATERAL Table Functions ● Table functions are implicitly LATERAL – Not allowed to explicitly specify LATERAL ● MySQL has one table function: JSON_TABLE SELECT people.* FROM t1, JSON_TABLE(t1.json_col, '$.people[*]' COLUMNS ( name VARCHAR(40) PATH '$.name', address VARCHAR(100) PATH '$.address')) AS people; Copyright © 2019 Oracle and/or its affiliates. All rights reserved. 7

8.LATERAL Without Lateral References ● Declared as LATERAL, but contains no lateral references SELECT … FROM t1, LATERAL (SELECT 1) AS derived, t2 … – Optimized by MySQL as if LATERAL was not present – No performance penalty ● Don't add LATERAL to all your derived tables! – Won't get any warning when accidentaly using a lateral reference – Confusing the next person to read your query Copyright © 2019 Oracle and/or its affiliates. All rights reserved. 8

9.Examples, please! Copyright © 2019 Oracle and/or its affiliates. All rights reserved. 9

10.Example Setup CREATE TABLE cities ( city_name VARCHAR(40), population BIGINT, country_name VARCHAR(40) ); INSERT INTO cities VALUES ('Shanghai', 24183300, 'China'), ('Beijing', 20794000, 'China'), … ; Copyright © 2019 Oracle and/or its affiliates. All rights reserved. 10

11.The Largest City of Each Country, Option 1 SELECT dt.population, dt.city_name, c.country_name FROM (SELECT DISTINCT country_name FROM cities) AS c, LATERAL ( SELECT city_name, population FROM cities WHERE cities.country_name = c.country_name ORDER BY population DESC LIMIT 1 ) AS dt; Copyright © 2019 Oracle and/or its affiliates. All rights reserved. 11

12.The Largest City of Each Country, Option 2 SELECT dt.pop, dt2.city_name, dt.country_name FROM ( SELECT country_name, MAX(population) AS pop FROM cities GROUP BY country_name ) AS dt, LATERAL ( SELECT city_name FROM cities WHERE cities.country_name = dt.country_name AND cities.population = dt.pop ) AS dt2; Copyright © 2019 Oracle and/or its affiliates. All rights reserved. 12

13.The Largest City of Each Country, No LATERAL! SELECT dt.pop, cities.city_name, dt.country_name FROM ( SELECT country_name, MAX(population) AS pop FROM cities GROUP BY country_name ) AS dt JOIN cities ON cities.country_name = dt.country_name AND cities.population = dt.pop; Copyright © 2019 Oracle and/or its affiliates. All rights reserved. 13

14.The Largest City of Each Country, No LATERAL! SELECT dt.pop, cities.city_name, dt.country_name FROM ( SELECT country_name, MAX(population) AS pop FROM cities GROUP BY country_name ) AS dt JOIN cities ON cities.country_name = dt.country_name AND cities.population = dt.pop; This is how MySQL rewrites the previous query after merging the lateral derived table into the outer query. Copyright © 2019 Oracle and/or its affiliates. All rights reserved. 14

15.Feature descriptions and design details directly from the source. http://mysqlserverteam.com/ Copyright © 2019 Oracle and/or its affiliates. All rights reserved. 15

16.Copyright © 2019 Oracle and/or its affiliates. All rights reserved. 16

17.Safe Harbor Statement The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle. Copyright © 2019 Oracle and/or its affiliates. All rights reserved. 17

18.