About Database Kernel | PolarDB Optimizer Query Transformation - Window Functions

By Bo Li (Boge)

Introduction

The database query optimizer is like the brain of the entire system. The efficiency of executing an SQL statement can vary significantly based on different optimization decisions. Therefore, the optimizer is one of the core components and competitive advantages of a database system. Alibaba Cloud PolarDB for MySQL is a cloud-native database that can handles various user scenarios and loads, helping enterprises go online and increase their data value. To enhance its capabilities, we have focused on improving the query transformation functionality of PolarDB for MySQL.

This article series will focus on the query transformation capability of PolarDB for MySQL and introduce some of our accumulated work in this direction.

Overview

Correlated subqueries are commonly supported in modern relational databases. They offer strong semantic expression capabilities and allow users to directly reference columns from the outer query in order to calculate subquery results. Correlated subqueries are widely used in decision support systems and online analytics processing scenarios. For instance, one-third of the queries in the TPC-H decision support benchmark include correlated subqueries. Efficient execution of correlated subqueries is therefore a key criterion for determining the advancement of a database.

Decorrelation is a common method used to optimize queries with subqueries. Without decorrelation, the subquery is executed iteratively based on semantics. This means that the subquery is triggered and executed once for each piece of data queried by the outer query. Consequently, the correlated subquery is executed multiple times when the entire query is completed. If the outer query contains a large amount of data and the subquery cannot be efficiently executed, the overall query efficiency will be low.

This article introduces a method that utilizes the capabilities of window aggregate functions to decorrelate subqueries. It allows subqueries to be executed only once and avoids multiple accesses to the same common table in both the outer query and subquery. This greatly accelerates the entire query from two aspects.

The author has implemented this decorrelation method in MySQL, which significantly improves the performance of TPC-H Q2 and Q17 in the benchmark. By default, this method can be enabled online, providing stable and guaranteed performance improvement for eligible user queries.

Øystein, a MySQL industry expert, has also blogged Use Window Functions to Speed Up Correlated Subqueries to introduce this implementation, as he mentioned in his blog: At Alibaba, we have implemented the query transformation to window functions in our Innovative Version of POLARDB for MySQL. Unlike the subquery transformation introduced in MySQL 8.0.24, which is off by default since it may often significantly reduce the query performance, our transformation can be applied automatically. As long as the query satisfies the conditions listed above, it should not be any slower than the original query. In other words, with POLARDB you will no longer have to manually rewrite your query to get the benefits discussed in this blog post.

What I Think Really Matters for Personal Growth in Software Development | From Junior Engineer to Expert

Correlated Subquery Form

A common form of correlated subquery is when the subquery includes an aggregate function, and the outer query uses the aggregated results for further operations. A typical example is Query 2 in TPC-H:

SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr, 
 s_address, s_phone, s_comment
FROM part, supplier, partsupp, nation, region
WHERE p_partkey = ps_partkey
   AND s_suppkey = ps_suppkey
   AND p_size = 30
   AND p_type LIKE '%STEEL'
   AND s_nationkey = n_nationkey
   AND n_regionkey = r_regionkey
   AND r_name = 'ASIA'
   AND ps_supplycost = (
       SELECT MIN(ps_supplycost)
       FROM partsupp, supplier, nation, region
       WHERE p_partkey = ps_partkey
           AND s_suppkey = ps_suppkey
           AND s_nationkey = n_nationkey
           AND n_regionkey = r_regionkey
           AND r_name = 'ASIA'
   )
ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
LIMIT 100;

The goal of this query is to find the supplier that can provide the minimum wholesale price for each part of a specific type and size in a particular area. In the query above, the main query first connects the eligible parts with the corresponding suppliers and related information. For each combination of parts and suppliers, the correlated subquery calculates the minimum wholesale price provided by all suppliers of the corresponding parts. Then, it compares whether the current combination is the same as the minimum wholesale price. Finally, it selects the supplier with the same wholesale price and the minimum value.

Semantically Understanding the Process of Decorrelation

Using the above query as an example, in order to find the supplier that provides the minimum wholesale price for each part, in addition to the iterative method mentioned earlier, another approach is to calculate the minimum wholesale price of all parts corresponding to the supplier in advance, store the results, and then compare them with the combination of the main query part and the supplier. This avoids multiple executions of the subquery.

Window Aggregate Functions

A window aggregate function is a way to calculate the aggregation result. Compared to common aggregate functions, window aggregate functions are more flexible and do not aggregate data. They allow you to add aggregation results to each row without affecting the original output columns. The expression form for window aggregate functions is as follows:

function(arg) OVER ([partition_clause] [order_clause] [frame_clause])

The OVER syntax is used to define a window for function computation. The partition_clause is similar to the commonly used GROUP BY syntax and specifies the columns that are aggregated by the aggregate function. This article only covers the usage of the partition_clause syntax.

Optimization Methods

Step 1: Introducing window aggregate functions to transform correlated subqueries into non-correlated derived tables

Upon examining the main query body and subquery body of the previous query, we can observe that the tables, join conditions, and filter conditions involved in both bodies are quite similar. For instance, the tables present in the subquery are also included in the outer query, and the join conditions and filter conditions between these tables are also replicated in the outer query. Now, if we consider these tables and the join results obtained, we can conclude that the join results are identical in both the outer query and the subquery. Given that window aggregate functions can output additional aggregation results without affecting the original query results, could we perform the join operation only once in the subquery and output these join results? The main query would then only need to select the necessary columns from the output results of the subquery, eliminating the need for repetitive joins with these common tables.

Based on these ideas, we can rewrite the original query into the following form 1:

SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr,
  s_address, s_phone, s_comment
FROM part, (
    SELECT MIN(ps_supplycost) OVER(PARTITION BY ps_partkey) as win_min, 
      ps_partkey, ps_supplycost, s_acctbal, n_name, s_name, s_address,
      s_phone, s_comment
    FROM partsupp, supplier, nation, region
    WHERE s_suppkey = ps_suppkey
      AND s_nationkey = n_nationkey
      AND n_regionkey = r_regionkey
      AND r_name = 'ASIA') as derived
WHERE p_partkey = ps_partkey
  AND p_size = 30
  AND p_type LIKE '%STEEL'
  AND ps_supplycost = derived.win_min
ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
LIMIT 100;

By rewriting form 1, we have solved the issue of correlation in subqueries that requires iterative execution. Now, executing it once is sufficient. We have also avoided repeated access to common tables in both the main query and subqueries.

In addition to the rewriting mentioned above, it is important that the join results of the main query and the subquery are consistent for the common table. In fact, this consistency is not strictly required and can be expanded using other methods, as mentioned later. Another crucial condition is that the two tables involved in the correlated condition in the subquery must have a join condition that is semantically equivalent to the join condition between the two tables in the main query, and it must be an equivalent join.

How can we understand this? Let's consider the example of the original query mentioned above. The correlated condition in the subquery is p_partkey = ps_partkey. For each p_partkey in the main query, the subquery finds all records of ps_partkey that are identical to p_partkey and aggregates them. The subquery does not aggregate the ps_partkey, but instead aggregates the p_partkey value from the outer query. After decorrelating the subquery, only the ps_partkey column can be used for aggregation. Therefore, the correlated condition must be an equivalence condition. If p_partkey is equal to ps_partkey, the aggregation result of the ps_partkey column will be the same as that of the p_partkey column. If the correlated condition is non-equivalent, such as p_partkey being greater than ps_partkey, the aggregation result of ps_partkey cannot be correlated with the original subquery execution.

Why do we need a join condition that is equivalent to the correlated condition in the main query? This is because the output of the derived table needs to be joined with the associated table in the original main query. If the join condition is not equivalent to the correlated condition, the joined row will not correspond to the data that satisfies the associated condition. Using the aggregation result of the current row will result in an error.

Step 2: If the join relationship between the association table and the subquery is 1:N, the association table can be moved to the subquery.

Let's examine the query in the aforementioned form 1. In the original query, the join condition between the associated table "part" and the derived table is p_partkey = ps_partkey. The p_partkey is the primary key of the "part" table, and there is only one occurrence of the same p_partkey or ps_partkey value in the "part" table. However, there may be multiple occurrences in the "partsupp" table. Hence, the join relationship between the associated table and the derived table is 1:N. Joining them will not cause data expansion or affect the calculation of window aggregate values in the derived table. Therefore, the "part" table, join condition, and filter condition can be moved into the derived table together. The join operation is performed first, followed by the calculation of the window aggregate value. This is rewritten as form 2.

SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr,
  s_address, s_phone, s_comment
FROM (
    SELECT MIN(ps_supplycost) OVER(PARTITION BY ps_partkey) as win_min, 
      ps_partkey, ps_supplycost, s_acctbal, n_name, s_name, s_address,
      s_phone, s_comment
    FROM part, partsupp, supplier, nation, region
    WHERE p_partkey = ps_partkey 
      AND s_suppkey = ps_suppkey
      AND s_nationkey = n_nationkey
      AND n_regionkey = r_regionkey
      AND p_size = 30
      AND p_type LIKE '%STEEL'
      AND r_name = 'ASIA') as derived
WHERE ps_supplycost = derived.win_min
ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
LIMIT 100;

The benefit of this rewriting is that it allows more tables to be moved into the subquery that represents the derived table, providing the optimizer with more choices for the order of joins. Additionally, filter conditions and join conditions can be pushed down to the subquery, allowing for the pre-filtering of data that doesn't meet the conditions and reducing unnecessary calculations..

Universal Expression Form

Original Query Form

Now, we have learned the overall concept of decorrelation optimization by using a representative query example. Next, let's provide a general expression for decorrelation of subqueries using window aggregate functions. Assuming that the original query has the following form:

Original query correlated form

Among them:

T1, T2, and T3 are collections of one or more tables and views.
The dotted line between T2 and T3 indicates that T2 in the subquery is correlated with T3 in the main query.
T1 is in the main query, but is not correlated with T2 in the subquery.

Converted Form

Then under certain conditions, we can transform the query into the following two forms.

T2 is correlated with T3 in one or more common columns (not a primary key or unique key)

In this case, we can transform the original query into the following form, corresponding to form 1 in the above example:

The association columns of T2 and T3 form the primary key or unique key of T3 (forming a join relationship is 1:N ).

In this case, we can further optimize the original query and transform it to form 2:

Conditions for Decorrelation

In addition to the conditions mentioned above, there are some additional conditions that queries must meet in order to apply this optimization. These conditions are summarized below. Due to factors such as limitations in user scenarios, the risk of performance regression, and the difficulty of implementation, we support most usage scenarios, and a small number of expandable scenarios are also described in the following conditions.

Condition 1: The table in the main query must contain the table in the subquery.

To eliminate multiple accesses to common tables in both the main query and subquery, it is necessary to ensure that the join results of these common tables are consistent. One prerequisite is that the accessed tables are consistent. Therefore, the tables in the main query must include the tables in the subquery.

Expansion limits: Subqueries can include additional tables that are not in the main query. However, these additional tables must be connected to the common tables in a lossless manner. The purpose of a lossless join is also to ensure consistency in the output of the main query and the subquery on the common tables.

Condition 2: The join condition and filter condition of the common table in the main query must contain the condition in the subquery.

Similarly, in order to ensure consistency in the join results of the common tables in the main query and subquery, the join conditions and filter conditions of the common tables must include the conditions in the subquery. Additional conditions can exist in the main query, which can be further filtered at the outer layer after the output of the subquery representing the derived table.

Expansion limits: If a subquery contains additional conditions that are not in the main query, the conditions in the main query must be maintained in order to generate all the results required by the main query. However, the calculation result of the window aggregate function will be different from the original one due to the relaxed conditions. In this case, additional conditions need to be added to the window function computation using the CASE...WHEN statement. This transformation needs to be evaluated based on cost.

Condition 3: The correlated conditions in the subquery must be equivalent joins, and the main query must contain join conditions with the same semantics.

The specific reasons have been described in step 1 of optimization ideas, and will not be repeated here.

Condition 4: Aggregate functions do not contain the DISTINCT syntax and are compatible with window functions.

Window functions do not support the DISTINCT syntax. Therefore, aggregate functions cannot include the DISTINCT modifier. Additionally, aggregate functions must be compatible with window functions and can be transformed into window aggregate functions.

Condition 5: The join conditions of the main query and subquery must be established between the same two tables.

The main query and subquery can have multiple correlated conditions, but these conditions must be established between the same two tables. No other correlated conditions can exist except for those between these two tables.

Expansion limits: A subquery can be correlated with multiple different tables in the main query. Make sure that all correlated conditions are equivalent.

Condition 6: The main query and subquery do not contain expressions with marginal effects or uncertainty.

If a subquery is decorrelated using a window aggregate function, the execution times and execution order of the original query are changed. If the query contains user-defined functions, random functions, or other expressions with marginal effects or uncertainty, the execution result or the state of the database after execution may be inconsistent with the original query. Therefore, it is necessary to avoid transformation in these situations.

How it Works

The process of implementing the preceding condition check and query decorrelation transformation in MySQL is as follows:

Decorrelation implementation process

For convenience, Outer Query is used to refer to the main query and Inner Query is used to refer to the subquery. The transformation process consists of the following steps:

Check the prerequisites that must be met by the main query and subqueries.
Check whether the aggregate function in the subquery can be transformed to a window aggregate function.
Check whether the tables, join conditions and filter conditions between tables of the main query are contained in the subquery.
If the preceding conditions are met, the entire query can be transformed.
Eliminate the common join condition of the common table in the main query. If the condition is met, move the filter conditions of the association table and the join conditions between the association table and the common table to the subquery.
Eliminate common tables from the main query. If the condition is met, move the association table to the subquery;
Collect the columns of common tables and association tables that need to be accessed by external queries and add them to the output columns of subqueries.
Create a window consisting of association columns in a subquery and transform the aggregate function into a window aggregate function.
Transform correlated subqueries to non-correlated derived tables.
Replace the columns to be accessed in the main query with the output columns of the derived table, and replace the subquery with the output columns of the window aggregate function in the derived table.

Result

In the TPC-H query, Query 2 and Query 17 meet the optimization conditions. Benchmark the optimization effect of these two queries.

The CPU specification is 2 Intel(R) Xeon(R) CPU E5-2682 v4 @ 2.50GHz, 16 physical cores each.

Serial Performance

Data is TPC-H 10G with index.

Parameters are innodb_adaptive_hash_index=off, innodb_buffer_pool_size=32G.

The results show that the acceleration ratio of Q2 is 1.54 and that of Q17 is 4.91.

Parallel Performance

Data is TPC-H 100G with index.

Parameters are innodb_adaptive_hash_index=off, innodb_buffer_pool_size=170G.

Check the Free Tier & Special Offers of Alibaba Cloud Database Products: https://www.alibabacloud.com/product/databases

Community