MySQL5.7 Performance Optimization Series (ii)--SQL statement Optimization (3)--optimizing subqueries with materialized policies

The optimizer uses a materialized policy (materialization) to achieve more efficient subquery processing. The query execution is accelerated by generating a subquery result as a temporary table, usually in memory. MySQL needs the subquery result for the first time and implements the result as a temporary table. Any subsequent results are required, and MySQL points to the staging table again. The optimizer can index a table using a hash index to make the lookup faster and cheaper. The index is unique, it eliminates duplicates, and makes the table smaller.

The subquery implementation may use an in-memory temporal table, and if the table becomes too large, return to the disk storage.

If you do not use a materialized policy, the optimizer sometimes overrides a non-correlated subquery as a correlated subquery. For example, the following in subquery is irrelevant (where_condition only refers to columns from T2 instead of T1):

1.SELECT * FROM t12.WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

The optimizer may override it as a exists-related subquery:

1.SELECT * FROM t12.WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);

A subquery implementation that uses a temporal table avoids this override and makes it possible to execute subqueries only once, rather than once per row of external queries.

The optimizer_switch system variable materialization flag must be enabled for materialized subqueries to be used in MySQL. When the implementation flag is enabled, the implementation applies to the predicates that appear anywhere (in the select list, Where,on,group by,having or order by), for any of these use cases:

• Predicates have this form when an outer expression oe_i or an inner expression ie_i is empty. N is more than 1.

..., oe_N) [NOT] IN (SELECT ie_1, i_2, ..., ie_N ...)

• Predicates have this form when there is a single external expression oe and inner expression ie. An expression can be empty.

...)

• The predicate is in or not In,unknown (NULL) and the result of false has the same meaning.

The following example illustrates how the equivalence of the unknown and false predicate evaluations affects whether a subquery implementation can be used. Suppose where_condition contains only columns from T2 instead of T1, so that subqueries are irrelevant.

This query may be implemented:

1.SELECT * FROM t12.WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

The following restrictions apply to materialized using subqueries:

• The types of internal and external expressions must match. For example, if two expressions are integers, or both are decimal, the optimizer may be able to use the implementation, but if one expression is an integer and the other is decimal, the implementation cannot be used.
• An internal expression cannot be a blob.

Use the explain query to provide an indication of whether the optimizer uses subqueries materialized. Select_type may change from dependent subquery to subquery when compared to query execution that does not use implementations. This indicates that for subqueries that will be executed once per outer row, the implementation causes the subquery to execute only once. In addition, for extended explain output, the text displayed by the following show warnings includes materialized and materialized subqueries.

MySQL5.7 Performance Optimization Series (ii)--SQL statement Optimization (3)--optimizing subqueries with materialized policies