MySQL 8.0 Reference Manual（讀書筆記52節--Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions(2)）

4 Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization

The optimizer can handle derived table references using two strategies (which also apply to view references and common table expressions):

• Merge the derived table into the outer query block

• Materialize the derived table to an internal temporary table

Example 1:

SELECT * FROM (SELECT * FROM t1) AS derived_t1;

With merging of the derived table derived_t1, that query is executed similar to:

SELECT * FROM t1;

Example 2:

SELECT *
 FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1
 WHERE t1.f1 > 0;

With merging of the derived table derived_t2, that query is executed similar to:

SELECT t1.*, t2.f1
 FROM t1 JOIN t2 ON t1.f2=t2.f1
 WHERE t1.f1 > 0;

With materialization【實現；具體化；物質化;】, derived_t1 and derived_t2 are each treated as a separate table within their respective queries.

The optimizer handles derived tables, view references, and common table expressions the same way: It avoids unnecessary materialization whenever possible, which enables pushing down conditions from the outer query to derived tables and produces more efficient execution plans.

If merging would result in an outer query block that references more than 61 base tables, the optimizer chooses materialization instead.

The optimizer propagates【ˈprɑːpəɡeɪts 傳播;繁殖;宣傳;增殖;】 an ORDER BY clause in a derived table or view reference to the outer query block if these conditions are all true:

• The outer query is not grouped or aggregated.

• The outer query does not specify DISTINCT, HAVING, or ORDER BY.

• The outer query has this derived table or view reference as the only source in the FROM clause.

Otherwise, the optimizer ignores the ORDER BY clause.

The following means are available to influence whether the optimizer attempts to merge derived tables, view references, and common table expressions into the outer query block:

• The MERGE and NO_MERGE optimizer hints can be used. They apply assuming that no other rule prevents merging.

• Similarly, you can use the derived_merge flag of the optimizer_switch system variable.By default, the flag is enabled to permit merging. Disabling the flag prevents merging and avoids ER_UPDATE_TABLE_USED errors.

The derived_merge flag also applies to views that contain no ALGORITHM clause. Thus, if an ER_UPDATE_TABLE_USED error occurs for a view reference that uses an expression equivalent to the subquery, adding ALGORITHM=TEMPTABLE to the view definition prevents merging and takes precedence【ˈpresɪdəns 優先;優先權;】 over the derived_merge value.

• It is possible to disable merging by using in the subquery any constructs that prevent merging, although these are not as explicit in their effect on materialization. Constructs that prevent merging are the same for derived tables, common table expressions, and view references:

• Aggregate functions or window functions (SUM(), MIN(), MAX(), COUNT(), and so forth)

• DISTINCT

• GROUP BY

• HAVING

• LIMIT

• UNION or UNION ALL

• Subqueries in the select list

• Assignments to user variables

• References only to literal values (in this case, there is no underlying table)

If the optimizer chooses the materialization strategy rather than merging for a derived table, it handles the query as follows:

• The optimizer postpones derived table materialization until its contents are needed during query execution. This improves performance because delaying materialization may result in not having to do it at all. Consider a query that joins the result of a derived table to another table: If the optimizer processes that other table first and finds that it returns no rows, the join need not be carried out further and the optimizer can completely skip materializing the derived table.

• During query execution, the optimizer may add an index to a derived table to speed up row retrieval from it.

Consider the following EXPLAIN statement, for a SELECT query that contains a derived table:

EXPLAIN SELECT * FROM (SELECT * FROM t1) AS derived_t1;

The optimizer avoids materializing the derived table by delaying it until the result is needed during SELECT execution. In this case, the query is not executed (because it occurs in an EXPLAIN statement), so the result is never needed.

Even for queries that are executed, delay of derived table materialization may enable the optimizer to avoid materialization entirely. When this happens, query execution is quicker by the time needed to perform materialization. Consider the following query, which joins the result of a derived table to another table:

SELECT *
 FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2
 ON t1.f2=derived_t2.f1
 WHERE t1.f1 > 0;

If the optimization processes t1 first and the WHERE clause produces an empty result, the join must necessarily be empty and the derived table need not be materialized.

For cases when a derived table requires materialization, the optimizer may add an index to the materialized table to speed up access to it. If such an index enables ref access to the table, it can greatly reduce amount of data read during query execution. Consider the following query:

SELECT *
 FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2
 ON t1.f1=derived_t2.f1;

The optimizer constructs【kənˈstrʌkts 建築;建造;修建;(按照數學規則)編製，繪製;組成;創建;】 an index over column f1 from derived_t2 if doing so would enable use of ref access for the lowest cost execution plan. After adding the index, the optimizer can treat the materialized derived table the same as a regular table with an index, and it benefits similarly from the generated index. The overhead of index creation is negligible compared to the cost of query execution without the index. If ref access would result in higher cost than some other access method, the optimizer creates no index and loses nothing.

For optimizer trace output, a merged derived table or view reference is not shown as a node. Only its underlying tables appear in the top query's plan.

What is true for materialization of derived tables is also true for common table expressions (CTEs). In addition, the following considerations pertain specifically to CTEs.

If a CTE is materialized by a query, it is materialized once for the query, even if the query references it several times.

A recursive CTE is always materialized.

If a CTE is materialized, the optimizer automatically adds relevant indexes if it estimates that indexing can speed up access by the top-level statement to the CTE. This is similar to automatic indexing of derived tables, except that if the CTE is referenced multiple times, the optimizer may create multiple indexes, to speed up access by each reference in the most appropriate way.

The MERGE and NO_MERGE optimizer hints can be applied to CTEs. Each CTE reference in the top-level statement can have its own hint, permitting CTE references to be selectively merged or materialized. The following statement uses hints to indicate that cte1 should be merged and cte2 should be materialized:

WITH
 cte1 AS (SELECT a, b FROM table1),
 cte2 AS (SELECT c, d FROM table2)
SELECT /*+ MERGE(cte1) NO_MERGE(cte2) */ cte1.b, cte2.d
FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

The ALGORITHM clause for CREATE VIEW does not affect materialization for any WITH clause preceding the SELECT statement in the view definition. Consider this statement:

CREATE ALGORITHM={TEMPTABLE|MERGE} VIEW v1 AS WITH ... SELECT ...

The ALGORITHM value affects materialization only of the SELECT, not the WITH clause.

Prior to MySQL 8.0.16, if internal_tmp_disk_storage_engine=MYISAM, an error occurred for any attempt to materialize a CTE using an on-disk temporary table, since for CTEs, the storage engine used for on-disk internal temporary tables could not be MyISAM. Beginning with MySQL 8.0.16, this is no longer an issue, since TempTable now always uses InnoDB for on-disk internal temporary tables.

As mentioned previously, a CTE, if materialized, is materialized once, even if referenced multiple times. To indicate one-time materialization, optimizer trace output contains an occurrence of creating_tmp_table plus one or more occurrences of reusing_tmp_table.

CTEs are similar to derived tables, for which the materialized_from_subquery node follows the reference【ˈrefrəns 參考，參照，查閱;（方便查詢的）編號，標記，索引;關係，關聯;】. This is true for a CTE that is referenced multiple times, so there is no duplication of materialized_from_subquery nodes (which would give the impression that the subquery is executed multiple times, and produce unnecessarily verbose output). Only one reference to the CTE has a complete materialized_from_subquery node with the description of its subquery plan. Other references have a reduced materialized_from_subquery node. The same idea applies to EXPLAIN output in TRADITIONAL format: Subqueries for other references are not shown.

5 Derived Condition Pushdown Optimization

MySQL 8.0.22 and later supports derived condition pushdown for eligible【ˈelɪdʒəbl 有資格的;合格的;(指認為可做夫妻的男女)合意的，合適的，中意的;具備條件的;】 subqueries. For a query such as SELECT * FROM (SELECT i, j FROM t1) AS dt WHERE i > constant, it is possible in many cases to push the outer WHERE condition down to the derived table, in this case resulting in SELECT * FROM (SELECT i, j FROM t1 WHERE i > constant) AS dt. When a derived table cannot be merged into the outer query (for example, if the derived table uses aggregation), pushing the outer WHERE condition down to the derived table should decrease the number of rows that need to be processed and thus speed up execution of the query.

【Prior to MySQL 8.0.22, if a derived table was materialized but not merged, MySQL materialized the entire table, then qualified all of the resulting rows with the WHERE condition. This is still the case if derived condition pushdown is not enabled, or cannot be employed for some other reason.】

Outer WHERE conditions can be pushed down to derived materialized tables under the following circumstances:

• When the derived table uses no aggregate or window functions, the outer WHERE condition can be pushed down to it directly. This includes WHERE conditions having multiple predicates joined with AND, OR, or both.

For example, the query SELECT * FROM (SELECT f1, f2 FROM t1) AS dt WHERE f1 < 3 AND f2 > 11 is rewritten as SELECT f1, f2 FROM (SELECT f1, f2 FROM t1 WHERE f1 < 3 AND f2 > 11) AS dt.

• When the derived table has a GROUP BY and uses no window functions, an outer WHERE condition referencing one or more columns which are not part of the GROUP BY can be pushed down to the derived table as a HAVING condition.

For example, SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j) AS dt WHERE sum > 100 is rewritten following derived condition pushdown as SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j HAVING sum > 100) AS dt.

• When the derived table uses a GROUP BY and the columns in the outer WHERE condition are GROUP BY columns, the WHERE conditions referencing those columns can be pushed down directly to the derived table.

For example, the query SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i > 10 is rewritten as SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 WHERE i > 10 GROUP BY i,j) AS dt.

In the event that the outer WHERE condition has predicates【ˈpredɪkeɪts 使基於;斷言;表明;使以…為依據;闡明;】 referencing columns which are part of the GROUP BY as well as predicates referencing columns which are not, predicates of the former sort are pushed down as WHERE conditions, while those of the latter type are pushed down as HAVING conditions. For example, in the query SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i > 10 AND sum > 100, the predicate i > 10 in the outer WHERE clause references a GROUP BY column, whereas the predicate sum > 100 does not reference any GROUP BY column. Thus the derived table pushdown optimization causes the query to be rewritten in a manner similar to what is shown here:

SELECT * FROM (
 SELECT i, j, SUM(k) AS sum FROM t1
 WHERE i > 10
 GROUP BY i, j
 HAVING sum > 100
 ) AS dt;

 To enable derived condition pushdown, the optimizer_switch system variable's derived_condition_pushdown flag (added in this release) must be set to on, which is the default setting. If this optimization is disabled by optimizer_switch, you can enable it for a specific query using the DERIVED_CONDITION_PUSHDOWN optimizer hint. To disable the optimization for a given query, use the NO_DERIVED_CONDITION_PUSHDOWN optimizer hint.

The following restrictions and limitations apply to the derived table condition pushdown optimization:

• The optimization cannot be used if the derived table contains UNION. This restriction is lifted in MySQL 8.0.29. Consider two tables t1 and t2, and a view v containing their union, created as shown here:

CREATE TABLE t1 (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
 c1 INT, 
 KEY i1 (c1)
);
CREATE TABLE t2 (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
 c1 INT, 
 KEY i1 (c1)
);
CREATE OR REPLACE VIEW v AS
 SELECT id, c1 FROM t1
 UNION ALL
 SELECT id, c1 FROM t2;

As be seen in the output of EXPLAIN, a condition present in the top level of a query such as SELECT * FROM v WHERE c1 = 12 can now be pushed down to both query blocks in the derived table:

mysql> EXPLAIN FORMAT=TREE SELECT * FROM v WHERE c1 = 12\G
*************************** 1. row ***************************
EXPLAIN: -> Table scan on v (cost=1.26..2.52 rows=2)
 -> Union materialize (cost=2.16..3.42 rows=2)
 -> Covering index lookup on t1 using i1 (c1=12) (cost=0.35 rows=1)
 -> Covering index lookup on t2 using i1 (c1=12) (cost=0.35 rows=1)
1 row in set (0.00 sec)

In MySQL 8.0.29 and later, the derived table condition pushdown optimization can be employed with UNION queries, with the following exceptions【ɪkˈsɛpʃənz 例外;規則的例外;一般情況以外的人(或事物);例外的事物;】:

• Condition pushdown cannot be used with a UNION query if any materialized derived table that is part of the UNION is a recursive common table expression.
• Conditions containing nondeterministic expressions cannot be pushed down to a derived table.

• The derived table cannot use a LIMIT clause.

• Conditions containing subqueries cannot be pushed down.

• The optimization cannot be used if the derived table is an inner table of an outer join.

• If a materialized derived table is a common table expression, conditions are not pushed down to it if it is referenced multiple times.

• Conditions using parameters【pəˈræmətərz 範圍;規範;決定因素;】 can be pushed down if the condition is of the form derived_column > ?. If a derived column in an outer WHERE condition is an expression having a ? in the underlying derived table, this condition cannot be pushed down.

• For a query in which the condition is on the tables of a view created using ALGORITHM=TEMPTABLE instead of on the view itself, the multiple equality is not recognized at resolution【ˌrezəˈluːʃn 決議;解析度;(問題、分歧等的)解決，消除;堅決;決心;堅定;清晰度;有決心;正式決定;】, and thus the condition cannot be not pushed down. This because, when optimizing a query, condition pushdown takes place during resolution phase while multiple equality propagation【傳播;擴展;宣傳;培養;】 occurs during optimization.

This is not an issue in such cases for a view using ALGORITHM=MERGE, where the equality can be propagated and the condition pushed down.

• Beginning with MySQL 8.0.28, a condition cannot be pushed down if the derived table's SELECT list contain any assignments【əˈsaɪnmənts (分派的)工作，任務;(工作等的)分派，佈置;】 to user variables. (Bug #104918)