HTAP database query optimization method and device and storage medium

Abstract

The invention discloses an HTAP database query optimization method and device and a storage medium. The query performance is remarkably improved by combining the advantages of row storage and column storage. Comprising the steps of analyzing SQL query to generate a logic query plan; evaluating the execution cost of each logic operator in different storage modes by using a mixed cost model, wherein hyper-parameters of the mixed cost model are optimized through Bayesian search; selectively adopting a dynamic programming algorithm or a greedy algorithm to generate a hybrid physical query plan with the minimum execution cost according to the table number; and executing the plan to obtain a query result. In addition, an MCTS algorithm is further utilized, the optimal column subset is selected in the preset memory budget limit based on the query historical record and loaded into the memory column storage, and the subsequent query performance is further optimized. The problem that row storage and column storage in an existing HTAP system cannot work cooperatively is solved, operator-level mixed execution is achieved, and meanwhile memory resource utilization is optimized.

Description

Technical Field

[0001] The present disclosure relates to the field of database query optimization, and particularly to a method, device and storage medium for optimizing queries in an HTAP database. Background Art

[0002] Hybrid Transactional Analytical Processing (HTAP) database technology processes concurrent transactions and analytical queries simultaneously based on a one-stop architecture, and is mainly targeted at applications with high requirements for both transaction processing performance and query processing performance, such as transactional e-commerce websites, banking and financial systems, etc. Early HTAP databases were targeted at analytical ERP applications, mainly using columnar storage engines to handle a large number of analytical queries and a small number of update transactions simultaneously. Representative databases include SAP HANA, Hyrise, HyPer, etc. Since the concept of HTAP was officially proposed in 2014, many database systems have developed columnar storage engines by expanding on the original row-based storage engines, mainly targeting transactional banking operations with real-time analysis requirements. Their main advantage lies in the ability to use row-based and columnar storage engines in a single system to process transactions and queries respectively. Representative databases include MemSQL, Oracle, SQL Server, etc. However, existing HTAP systems still have many limitations in effectively leveraging the advantages of row-based and columnar storage.

[0003] The hybrid execution technology relies on an HTAP database. By analyzing the physical plan of the database, a part (or all) of the database query is processed by an analytical database, and the remaining part is processed by a transactional database. A direct hybrid execution technology is to calculate the query cost of each query in different databases and select the database with a lower cost for processing according to the query cost. However, this method usually cannot achieve fine-grained query optimization because some operators in the database can be optimized by another database. Another hybrid execution technology is to calculate the cost of each base table scan of the query and select the database with a lower cost for query when scanning the base table. Currently, the main methods are to execute the hybrid execution technology by selecting the query method for different databases and the database for each base table of the query.

[0004] Existing systems can only generate sub-optimal plans based on row storage or column storage, and cannot generate (optimal) hybrid plans by combining row storage and column storage. For example, in the PolarDB-IMCI database, a query can only be routed to either row storage or column storage, while the execution of the entire SQL can be accelerated by combining row-based index scans and column-based joins. At the same time, the cost estimation of existing systems for SQL queries is not accurate, and existing systems rely heavily on row-based cost models during the plan generation process. However, due to the lack of a global cost model that can quantify row storage operators and column storage operators in a unified framework, the generated results may usually not be optimal.

[0005] In HTAP databases, there is a mainstream database architecture that is a hybrid architecture of primary row storage and in-memory column storage. In the primary row storage and in-memory column storage (IMCS) architecture, a key task is to select columns with higher efficiency to load into the IMCS to improve memory utilization and reduce costs. Existing methods rely on rule-based strategies for column selection. However, it is difficult for this method to reasonably determine the optimal columns for the workload for wide tables in large datasets. In the primary row storage and in-memory column storage architecture, column storage requires a large amount of memory resources to accelerate queries. Therefore, another important limitation lies in memory utilization. Since not all columns can be maintained, the system needs to select the optimal in-memory columns to achieve the maximum improvement in analytical performance. However, existing methods rely on database administrators or rule-based strategies and cannot carefully determine the optimal columns. To accelerate queries, existing methods usually store all columns in column storage, which leads to an increase in storage costs and may limit the amount of data that can be processed. Specifically, when faced with limited memory resources, existing methods lack efficient means to select the columns that are most beneficial for accelerating analytical queries, thus hindering the overall performance improvement.

[0006] Generally speaking, there are multiple limitations in existing HTAP systems for effectively leveraging the advantages of row storage and column storage: the generated query plans often can only be executed in row storage or column storage, and cannot generate hybrid plans that combine the advantages of row storage and column storage; the cost model mainly relies on the cost model of row storage, lacking a global cost model that can uniformly quantify row storage operators and column storage operators; selecting the most beneficial columns in in-memory column storage (IMCS) is also an important task, but existing methods cannot accurately identify the best columns in large datasets and wide tables. Therefore, how to efficiently generate high-quality hybrid query plans, design a cost model that can simultaneously quantify row storage operators and column storage operators, and select the most beneficial columns within the memory budget limit are the main technical problems to be solved. Summary of the Invention

[0007] The objective of the present disclosure is to provide a technical solution for optimizing the query performance of an HTAP database, which constructs a hybrid cost model and a query execution framework on a hybrid row-column storage architecture based on a hybrid plan, aiming to maximize query performance by combining row storage and column storage.

[0008] According to an embodiment of the present disclosure, a query optimization method for an HTAP database is proposed, including:

[0009] Receiving and parsing an SQL query request to generate a logical query plan, where the logical query plan includes multiple logical operators;

[0010] Evaluating the execution costs of each logical operator in the logical query plan in row storage mode and column storage mode respectively using a hybrid cost model, where the hybrid cost model includes sub-models for evaluating the execution costs of logical operators in row storage mode and column storage mode respectively and a sub-model for evaluating the execution cost of a conversion operator, the conversion operator being used to convert data between row storage mode and column storage mode, and the hyperparameters of the hybrid cost model being determined via Bayesian search to uniformly quantify the execution costs of logical operators in different storage modes and the execution cost of the conversion operator;

[0011] Selectively adopting a dynamic programming algorithm or a greedy algorithm according to the number of tables involved in the logical query plan, based on the evaluated execution costs of logical operators, introducing the execution cost of the conversion operator, and gradually evaluating intermediate results and pruning using the hybrid cost model to generate a hybrid physical query plan with the minimum execution cost;

[0012] Executing the hybrid physical query plan to obtain a query result.

[0013] In some embodiments, the method further includes optimizing the R2C operator that converts row storage data to column storage data in the conversion operator as follows:

[0014] Using multi-threading technology to parallelize the sequential scan in row storage;

[0015] Adopting multi-threading technology to parallelize the format conversion tasks of multiple data blocks;

[0016] Pushing down filtering operations and projection operations to a position closer to the data source to reduce the amount of data that needs to be format-converted;

[0017] According to query requirement analysis, only loading the columns required for the query into column storage.

[0018] In some embodiments, the implementation of the C2R operator that converts column - stored data to row - stored data in the conversion operator includes an implementation based on materialization and an implementation based on RowID. The hybrid cost model dynamically evaluates the execution costs of the two implementation methods and selects the one with the lower execution cost.

[0019] In some embodiments, the hybrid cost model includes:

[0020] A row - stored scan cost sub - model for evaluating the execution costs of sequential scan, non - clustered index scan, clustered index scan, and filtering in the row - stored mode;

[0021] A column - stored scan cost sub - model for evaluating the execution costs of scan and filtering in the column - stored mode;

[0022] A row - stored join cost sub - model for evaluating the execution costs of hash join, index join, and merge join in the row - stored mode;

[0023] A column - stored join cost sub - model for evaluating the execution costs of hash join and merge join in the column - stored mode;

[0024] A conversion cost model for evaluating the execution cost of the R2C operator that converts row - stored data to column - stored data and the execution cost of the C2R operator that converts column - stored data to row - stored data.

[0025] In some embodiments, the execution cost Cost R2C (T, S) of the R2C operator is evaluated according to the following formula:

[0026] Cost R2C (T, S)=(γ R2C +γ t )*w(S)*N o ,

[0027] where T represents the input data source in the row - stored mode to be converted, S represents the set of materialized columns in T, γ R2C represents the cost coefficient for creating materialized columns, γ t represents the cost coefficient for data transmission, w(S) represents the width of the materialized columns, N o represents the number of rows in the input T that pass through the filter and are to be converted to the column - stored mode, and γ R2C and γ t are hyperparameters optimized by Bayesian search.

[0028] In some embodiments, the execution cost Cost C2R (T, S) of the C2R operator is evaluated according to the following formula:

[0029] Cost C2R(T, S) = min{(γ C2R + γ t ) * w(S) * N o , (γ C2R + γ t ) * w(rid(T)) * N o + γ i * w(S) * N_o},

[0030] where T represents the data source of the input column storage mode to be converted, S represents the set of columns to be converted, the first part in min{} is the execution cost of the implementation method based on materialization, the second part is the execution cost of the implementation method based on RowID, γ C2R represents the conversion cost coefficient, γ t represents the data transmission cost coefficient, γ i represents the coefficient for locating each tuple, w(S) represents the width of the column set, w(rid(T)) represents the width of the RowID, N o represents the number of rows to be converted to the row storage mode after filtering in the input T, γ C2R , γ t and γ i are hyperparameters optimized by Bayesian search.

[0031] In some embodiments, a dynamic programming algorithm or a greedy algorithm is selectively adopted according to the number of tables involved in the logical query plan, including:

[0032] When the number of tables involved in the logical query plan is less than a preset threshold, a dynamic programming algorithm is adopted;

[0033] When the number of tables involved in the logical query plan is greater than or equal to the preset threshold, a greedy algorithm is adopted.

[0034] In some embodiments, a dynamic programming algorithm is adopted to generate a hybrid physical query plan with the minimum execution cost, including:

[0035] Evaluate and separately record the minimum execution cost of each table in the row storage mode and the column storage mode as the intermediate result of the single table in the two storage modes;

[0036] For the two storage modes of each table subset for which the minimum execution cost has been calculated and stored, enumerate the combinations of the next table to be joined, the join order, and the storage mode after joining, and evaluate the execution cost of each combination, record the minimum execution cost of the new table subset obtained after joining the next table in the two storage modes as the intermediate result to incrementally construct a larger table set, where if the next join includes a conversion operator, the execution cost of the corresponding conversion operator is introduced when calculating the execution cost of the corresponding combination;

[0037] Until a join including all tables with the minimum execution cost is obtained, and use this join as the hybrid physical query plan.

[0038] In some embodiments, a greedy algorithm is used to generate a hybrid physical query plan with the minimum execution cost, including:

[0039] Traverse and evaluate the execution costs of all pairs of tables under all possible join methods and storage modes, and select the join of the two tables with the minimum execution cost as the initial join;

[0040] Among the remaining tables, traverse and evaluate the execution cost of each table joining the current join under different storage modes, and select the join with the minimum execution cost as the next join. Among them, if the next join includes a transformation operator, the execution cost of the corresponding transformation operator is introduced when calculating the execution cost of this join;

[0041] Until a join including all tables with the minimum execution cost is obtained as the hybrid physical query plan.

[0042] In some embodiments, the method further includes:

[0043] Based on the query history record, and using a transformation operator, filter out the columns with high access frequency from the table as candidate columns;

[0044] Using the Monte Carlo Tree Search (MCTS) algorithm, define the currently selected column subset as the search state. Each search tree node represents a specific column subset, and the tree search is cyclically executed through the following four stages:

[0045] Selection stage, use the UCT value to balance exploration and exploitation. Starting from the root node, select the child node with the largest UCT value until an unfully expanded node is reached;

[0046] Expansion stage, on an unfully expanded node, select a candidate column that has not been tried yet, add it to the current column subset to form a new search state, and create a corresponding child node;

[0047] Simulation stage, starting from the newly created node, randomly select candidate columns to add to the column subset using a fast simulation strategy until the memory budget limit is met or there are no more candidate columns to select, and evaluate the reward value of the final column subset;

[0048] Backpropagation stage, backpropagate the reward value obtained in the simulation stage to update the statistical information of all nodes on the path;

[0049] Repeat the above four stages until the search end condition is reached;

[0050] After the search is completed, a subset of columns with the maximum reward value is selected and loaded into the in-memory column store to optimize subsequent query performance, where the total size of the column subset does not exceed the preset memory budget.

[0051] In some embodiments, the reward function of the MCTS algorithm is:

[0052]

[0053] where CS is the currently selected set of columns, D' is the set of all columns, n represents the total number of queries used to train the MCTS algorithm, i represents the query number, B is a benefit function negatively correlated with the query cost, and B(Q i , CS) represents the benefit of the i-th query Q i when using the set of columns CS, and B(Q i , D') represents the benefit of the i-th query Q i when using all columns.

[0054] The present disclosure proposes a solution based on a hybrid execution plan for the query optimization problem of HTAP databases, significantly improving query performance and resource utilization. By introducing R2C and C2R transformation operators, the present disclosure overcomes the limitation that traditional query optimizers can only select a single storage format (row store or column store), achieving complementary advantages of row storage and column storage; by adopting a hybrid execution plan, it can make full use of the high efficiency of row store index scanning and the advantages of column store vectorized computing, and dynamically select the optimal execution path according to different query loads and data characteristics; by using Bayesian search to determine the hyperparameters of the hybrid cost model, it can uniformly quantify the execution costs of row store operators and column store operators and achieve automatic optimization of hyperparameters; according to the number of tables involved, using the dynamic programming algorithm (Dynamic Programming, abbreviated as DP) or the greedy algorithm (Greedy) to generate a hybrid execution plan can efficiently search for an execution plan with a lower execution cost in a huge plan space.

[0055] In addition, some embodiments of the present disclosure also introduce a column selection mechanism based on Monte Carlo tree search (MCTS) to achieve intelligent management of in-memory column stores. By selectively loading the columns that most significantly improve query performance under the constraint of memory budget, unnecessary memory overhead is avoided, and the utilization rate of memory resources is significantly improved.

[0056] According to the present disclosure, the query performance of HTAP databases can be significantly improved, and resource utilization can be increased. This solution has strong generality and scalability.

[0057] Other features and advantages of the technical solution proposed by the present disclosure are described in detail below. BRIEF DESCRIPTION OF THE DRAWINGS

[0058] The accompanying drawings here are incorporated into the specification and form a part of this specification, showing embodiments consistent with this specification, and are used together with the specification to explain the principles of this specification.

[0059] Figure 1 It is an overall architecture diagram of a system according to an exemplary embodiment of the present disclosure.

[0060] Figure 2 It shows a flowchart of an optimized query method for an HTAP database according to an embodiment of the present disclosure.

[0061] Figure 3 It shows a schematic diagram of a system workflow according to an exemplary embodiment of the present disclosure.

[0062] Figure 4 It shows a schematic diagram of generating a hybrid physical query plan using a dynamic programming algorithm according to an exemplary embodiment of the present disclosure.

[0063] Figure 5 It is a reference flowchart diagram of a column selection module according to an exemplary embodiment of the present disclosure.

[0064] Figure 6 It is a schematic diagram of the structure of an electronic device shown in at least one embodiment of the present disclosure. Detailed implementation manners

[0065] Here, exemplary embodiments will be described in detail, and the examples are shown in the accompanying drawings. When the following description refers to the accompanying drawings, unless otherwise indicated, the same numbers in different drawings represent the same or similar elements. The implementation manners described in the following exemplary embodiments do not represent all implementation manners consistent with the present disclosure. On the contrary, they are merely examples of devices and methods consistent with some aspects of the present disclosure as detailed in the appended claims.

[0066] Figure 1 It shows an overall architecture diagram of an optimized query system for an HTAP database according to an embodiment of the present disclosure. As Figure 1 shown, the system is divided into several main modules from top to bottom: the parsed SQL query, the optimizer, the buffer, the hybrid execution environment, the row storage, and the column selector.

[0067] The parsed SQL query represents a preliminarily processed SQL query statement.

[0068] The optimizer consists of three core components: a hybrid cost model for evaluating the costs of different execution paths; a hybrid plan generator, including two sub-modules of dynamic programming and greedy algorithms; and a hybrid plan representation for generating the optimal hybrid physical query plan.

[0069] The buffer is divided into three types of data storage: row storage data for storing data in row storage mode; incremental data for storing incremental updates; and column storage data for storing data in column storage mode.

[0070] The hybrid execution environment includes the following components: a hybrid execution model for overall management of the execution process; transformation operators, including two types of transformation operators, R2C (row to column) and C2R (column to row); row storage operators for handling various operations on row storage data; and column storage operators for handling various operations on column storage data.

[0071] Row storage includes Index and Disk components.

[0072] The column selector includes a column selection module based on MCTS, a candidate column generation module, and a query log input.

[0073] This architecture diagram clearly shows the complete process of query from parsing to execution, as well as the data flow relationship between components, presenting the innovative design of this disclosure in terms of hybrid execution and column selection from the overall architecture.

[0074] Figure 2 The flowchart of a query optimization method for an HTAP database according to an embodiment of this disclosure is shown. This method can be built on the Figure 1 shown architecture to maximize query performance by combining the advantages of row storage and column storage. As shown, this method includes steps 22, 24, 26, and 28.

[0075] Step 22: Receive and parse the SQL query request to generate a logical query plan, where the logical query plan includes multiple logical operators.

[0076] In this step, the system receives the SQL query request submitted by the user through the SQL parser and parses it into a logical query plan. The logical query plan is the core structure for the database system to understand and process queries, and is also the basis for subsequent optimization processes.

[0077] The SQL parsing process mainly includes lexical analysis, syntax analysis, semantic analysis, and finally creates a logical query plan representing query operations and structures based on the syntax tree.

[0078] The logical query plan is a tree structure, where each node can represent a logical operator, such as Scan, Filter, Join, Aggregate, Sort, etc. The leaf nodes of the tree usually represent table scan operations, which represent obtaining data from the data source; the intermediate nodes represent various data processing operations; and the root node represents the operation that finally generates the query result.

[0079] The logical query plan generated in this step is the basis for subsequent evaluation of the execution cost and generation of the hybrid physical query plan.

[0080] Step 24, use the hybrid cost model to evaluate the execution cost of each logical operator in the logical query plan in the row storage mode and the column storage mode respectively. The hybrid cost model includes sub-models for evaluating the execution cost of logical operators in the row storage mode and the column storage mode respectively, and a sub-model for evaluating the execution cost of the conversion operator. The conversion operator is used to convert data between the row storage mode and the column storage mode. The hyperparameters of the hybrid cost model are determined via Bayesian search to uniformly quantify the execution cost of logical operators in different storage modes and the execution cost of the conversion operator.

[0081] In this step, the system uses the hybrid cost model to evaluate the execution cost of each logical operator in the logical query plan in the row storage mode and the column storage mode respectively. The hybrid cost model uniformly quantifies the execution cost of operators in different storage modes and the cost of storage mode conversion, providing a basis for subsequent generation of the optimal hybrid physical query plan.

[0082] In some embodiments, the hybrid cost model includes:

[0083] Row storage scan cost sub-model: Evaluate the execution cost of sequential scan, non-clustered index scan, clustered index scan, and filtering operations in the row storage mode;

[0084] Column storage scan cost sub-model: Evaluate the execution cost of scan and filtering operations in the column storage mode;

[0085] Row storage join cost sub-model: Evaluate the execution cost of hash join, index join, and merge join in the row storage mode; Column storage join cost sub-model: Evaluate the execution cost of hash join and merge join in the column storage mode;

[0086] Conversion cost sub-model: Evaluate the execution cost of the R2C (Row-to-Column) operator for converting row storage data to column storage data and the C2R (Column-to-Row) operator for converting column storage data to row storage data.

[0087] The row-store scan cost sub-model calculates the execution cost for different scan types (sequential scan, unclustered index scan, and clustered index scan) respectively, and selects the scan method with the minimum execution cost as the optimal access method for the table in the row-store mode.

[0088] In some embodiments, the row-store scan cost sub-model can calculate the execution cost Cost Rs (T) of the scan operator in the row-store mode according to the following formula:

[0089]

[0090] where Seq Scan represents sequential scan, Unclustered represents unclustered index, Clustered represents clustered index, T represents the data source in the row-store mode to be scanned, and α f represents the coefficient of the filtering cost in the sequential scan, N t (T) represents the number of all tuples in T, N p (T) represents the total number of pages of T, and α i represents the cost coefficient for processing one tuple through the index, and α r represents the cost coefficient for random access, N it (T) represents the number of tuples through the index, and N ip (T) represents the number of pages corresponding to the index entries. α f , α i , α r are hyperparameters optimized by Bayesian search. In actual evaluation, the system will calculate the costs of these three scan methods and select the method with the minimum cost as the optimal access method in the row-store mode.

[0091] In some embodiments, the column-store scan cost sub-model can calculate the execution cost Cost Cs (T,S) of the scan operator in the column-store mode according to the following formula:

[0092]

[0093] where T represents the data source in the column-store mode to be scanned, S represents the set of columns to be scanned, and β f represents the cost coefficient for filtering, N b represents the number of blocks in the storage, b represents the size of each block, and β p is the cost coefficient related to column s, w(s) represents the width of column s, N o represents the number of rows passing through the filter in the input T, represents the cost of incremental storage scan. β f , β pare hyperparameters optimized by Bayesian search. Among them, the incremental storage scan cost is as follows:

[0094]

[0095] Among them, represents the cost of scanning the incremental storage of input T and selecting the column set S. δ f represents the cost coefficient for filtering, N Δ represents the number of tuples in the incremental storage, s represents a column in the column set S, δ p represents the cost coefficient of the projection operation, w(s) represents the width of column s, usually in bytes, N o represents the number of rows passed through the filter in the incremental storage. δ f , δ p are hyperparameters optimized by Bayesian search.

[0096] The row store join cost submodel calculates the execution cost for different join methods (hash join, index join, and merge join) respectively, and selects the join method with the minimum cost as the optimal join method in the row store mode.

[0097] In some embodiments, the execution cost of the join operator in the row store mode can be calculated according to the following formula in the row store join cost submodel

[0098]

[0099] Among them, Hash represents hash join, Index Join represents index join, Merge Join represents merge join, T L represents the row store data source to be joined on the left, T R represents the row store data source to be joined on the right, Cost build (T L ) represents the cost of constructing a hash table using T L Cost probe (T R ) represents the cost of probing the hash table using T R N represents o (T L ) represents T L The number of rows passed through the filter in, Cost index (T R ) represents the cost of finding a row through the row store index of T R Cost s (T L ,T R ) represents for T L and TR The cost of sorting, α m represents the cost coefficient of the merge operation in the row storage mode, |L| represents the size (number of rows) of T L and |R| represents the size (number of rows) of T R . α m is a hyperparameter optimized by Bayesian search. During the actual evaluation process, the system calculates the costs of these three join methods and selects the method with the minimum cost as the optimal join method in the row storage mode. This cost-based selection mechanism is the core of query optimization, ensuring the selection of the most efficient execution strategy under different data characteristics and query patterns.

[0100] The column storage mode generally does not support index joins. Therefore, the column storage join cost submodel can calculate the execution costs of hash joins and combinations respectively and select the method with the smaller execution cost as the optimal join method in the column storage mode.

[0101] In some embodiments, the execution cost of the join operator in the column storage mode can be calculated according to the following formula in the column storage join cost submodel

[0102]

[0103] where Hash represents a hash join, Merge Join represents a merge join, and T L represents the data source in column storage mode of the left side to be joined, and T R represents the data source in column storage mode of the right side to be joined, Cost build (T L ) represents the cost of building a hash table using T L , and Cost probe (T R ) represents the cost of probing the hash table using T R . β h represents the cost coefficient of the hash join operation in the column storage mode, considering the acceleration effect of SIMD instructions, and Cost s (T L , T R ) represents the cost of sorting the join columns in T L and T R . β s represents the cost coefficient of the sorting operation in column storage, and β m represents the cost coefficient of the merge operation in the column storage mode, |L| represents the size (number of rows) of the left data source T L and |R| represents the size (number of rows) of the right data source T R . β h , β s , βm is a hyperparameter optimized by Bayesian search.

[0104] The main advantage of column-store joins is the ability to utilize vectorized processing and SIMD instructions for acceleration, which can be reflected by coefficients such as β h The system calculates the costs of these two join methods and selects the method with the minimum cost as the optimal join method in the column-store mode.

[0105] During the hybrid execution according to this embodiment, two conversion operators, R2C (row-to-column) and C2R (column-to-row), are specifically introduced to solve the problem that row-store and column-store in existing HTAP systems cannot work efficiently together. The present disclosure realizes the hybrid execution ability at the operator level through the conversion operators, enabling the system to flexibly switch the storage mode at different stages of a single query, breaking through the limitation that traditional HTAP systems can only generate a single-mode execution plan of row-store or column-store.

[0106] The R2C operator is used to convert row-store data into column-store data. In some embodiments according to the present disclosure, the following optimizations are made to R2C to improve performance and efficiency:

[0107] 1. Parallel scanning: Optimize the sequential scanning operation in row-store through parallel technology, significantly reducing the scanning time. This technology makes full use of the advantages of multi-core processors and is particularly efficient in scenarios with large amounts of data;

[0108] 2. Multi-thread acceleration: Utilize multi-thread technology to accelerate the data conversion process between row-store and column-store. Multi-thread processing can execute the conversion tasks of multiple data blocks in parallel, significantly improving the overall processing speed;

[0109] 3. Push-down filtering and projection: Push down the filtering operation and projection operation to a position closer to the data source, significantly reducing the amount of data that needs to be transmitted and converted, and improving query performance. For example, perform the conversion only on the data rows that meet the conditions;

[0110] 4. Load necessary columns on demand: By analyzing the projection and filtering operators in the logical plan, only load the necessary columns into the column-store, rather than the data of the entire table. For example, for a query that only needs to project column "A" and the join filter condition depends on column "B", the system will only load columns "A" and "B" and not load irrelevant data. This on-demand loading strategy significantly reduces the overhead of data transmission and conversion.

[0111] Through the above optimizations in this embodiment, the performance of the R2C operator is significantly improved, reducing both the resource consumption of data processing and enhancing the execution efficiency of complex queries.

[0112] In some embodiments, the implementation of the C2R operator for converting column - stored data to row - stored data includes a materialization - based implementation and a RowID - based implementation. The hybrid cost model dynamically evaluates the execution costs of the two implementation methods and selects the one with the lower execution cost.

[0113] In one example, the materialization - based implementation includes the following three steps.

[0114] 1. Vectorized execution: Apply the filtering condition in the column store and generate a bitmap for relevant columns to mark the data that meets the condition.

[0115] 2. Bitmap merging and data localization: According to the results of vectorized execution, merge the bitmaps to determine the column data that meets the condition, and reconstruct the tuples based on this data.

[0116] 3. Data materialization: Temporarily store the reconstructed tuples as row - formatted data for subsequent operations.

[0117] This implementation is efficient when dealing with a small number of columns because the amount of materialized data is small and the processing overhead is limited. However, when the query needs to retrieve a large number of columns, the materialization implementation will result in significant conversion and transmission overhead, thus reducing efficiency.

[0118] In one example, the C2R operator adopts a RowID - based implementation. The C2R operator implemented by RowID does not directly obtain the complete data from the column store but relies on RowIDs for data localization, including the following three steps.

[0119] 1. Retrieve RowIDs: Through vectorized execution of the column store, find the RowIDs that meet the query conditions. These RowIDs uniquely identify each row in the table.

[0120] 2. Obtain row - store data: Provide the obtained RowIDs to the row store and directly obtain the required data from the row store.

[0121] 3. Join and sort support: In the join operation, the C2R operator can process the join results by passing RowID pairs; in the sort operation, combine the data of other columns through the sorted RowIDs of a certain column.

[0122] To find a better balance between the materialization implementation and the RowID implementation, as described above, the hybrid cost model will dynamically evaluate the performance of the two implementations in different scenarios and select the method with the lowest cost to perform data conversion. For example, when dealing with a small number of columns, the materialization implementation is usually better; while when dealing with a large number of columns, the RowID implementation is more efficient.

[0123] In some embodiments, the hybrid cost model evaluates the execution cost Cost of the R2C operator according to the following formula R2C (T, S):

[0124] Cost R2C (T, S) = (γ R2C + γ t ) * w(S) * N o ,

[0125] where T represents the data source of the input row storage mode to be converted, S represents the set of materialized columns in T, γ R2C represents the cost coefficient for creating materialized columns, γ t represents the cost coefficient for data transmission, w(S) represents the width of the materialized columns, N o represents the number of rows in the input T to be converted to column storage mode after filtering, and γ R2C and γ t are hyperparameters optimized by Bayesian search.

[0126] In some embodiments, the hybrid cost model evaluates the execution cost Cost of the C2R operator according to the following formula C2R (T, S):

[0127] Cost C2R (T, S) = min{(γ C2R + γ t ) * w(S) * N o , (γ C2R + γ t ) * w(rid(T)) * N o + γi * w(S) * N_o},

[0128] where T represents the data source of the input column storage mode to be converted, S represents the set of columns to be converted, the first part in min{} is the execution cost of the materialization-based implementation method, the second part is the execution cost of the RowID-based implementation method, γ C2R represents the cost coefficient for conversion, γ t represents the cost coefficient for data transmission, γ i represents the coefficient for locating each tuple, w(S) represents the width of the column set, w(rid(T)) represents the width of RowID, N o represents the number of rows in the input T to be converted to row storage mode after filtering, and γ C2R , γ t and γ i are hyperparameters optimized by Bayesian search.

[0129] The goal of Bayesian search is to search for all hyperparameters X, taking the execution time Y of each operator as the target value, and search for appropriate hyperparameters so that the execution cost evaluated according to the hybrid cost model is approximately equal to the target value. The execution time of each operator is obtained from the query log, and the total query time is additionally added for the adjustment of the overall hyperparameters.

[0130] For example, assume that the execution cost calculation formula for a certain type of operator O is c*|T|, where c is a hyperparameter. For this formula, we can get X = c, Y' = c*|T|; each query and operator can contribute samples of {X, Y'}, and training is performed based on the samples. The target value of Y' is the running time of operator O.

[0131] In this embodiment, the hyperparameter search and tuning tool of Bayesian search is selected, and the cost calculations of row storage and column storage are unified in the hybrid cost model, so that the execution costs calculated by the operator in the row storage mode and the execution costs calculated in the row storage mode can be compared uniformly, thereby generating the optimal hybrid physical query plan. Specifically, Bayesian optimization uses the existing load observation data to train a Gaussian process model. Subsequently, Bayesian optimization uses the Gaussian process model to predict the target function values of unobserved points. Finally, an acquisition function is selected according to the observation results to determine the next sampling point. Repeat the iteration for several rounds to search for the optimal solution. The advantage of Bayesian search is that it can reuse the historical information generated by the previous sampling points, which makes Bayesian search stand out among many hyperparameter search algorithms.

[0132] Step 26, selectively adopt the dynamic programming algorithm or the greedy algorithm according to the number of tables involved in the logical query plan, based on the evaluated execution costs of the logical operators, and introduce the execution costs of the conversion operators, and gradually evaluate the intermediate results and perform pruning using the hybrid cost model to generate a hybrid physical query plan with the minimum execution cost.

[0133] Since the query plan space grows exponentially with the increase in the number of tables, in order to balance efficiency and optimization quality, the present disclosure dynamically selects different algorithms according to the number of tables involved in the query. In some embodiments, when the number of tables involved in the logical query plan is less than a preset threshold, the dynamic programming algorithm is adopted; when the number of tables involved in the logical query plan is greater than or equal to the preset threshold, the greedy algorithm is adopted.

[0134] According to this embodiment, for simple queries involving a small number of tables, using the dynamic programming algorithm can ensure finding the global optimal solution. For complex queries involving a large number of tables, using the greedy algorithm can find an approximate optimal solution within an acceptable time. In some examples, the preset threshold can be set within the range of 3 to 7, and the specific value can be adjusted according to system performance and optimization requirements. For example, if the database system has high efficiency in processing join operations, the preset threshold can be appropriately increased. If the hardware resources are limited (such as small memory), the preset threshold can be appropriately decreased.

[0135] In some embodiments, using the dynamic programming algorithm to generate a hybrid physical query plan with the minimum execution cost includes:

[0136] Evaluate and separately record the minimum execution cost of each table in the row storage mode and the column storage mode as the intermediate result of the single table in the two storage modes.

[0137] For the two storage modes of each table subset for which the minimum execution cost has been calculated and stored, enumerate the combinations of the next table to be joined, the join order, and the storage mode after joining, and evaluate the execution cost of each combination. Record the minimum execution cost of the new table subset obtained after joining the next table in the two storage modes respectively as the intermediate result to incrementally construct a larger table set. Among them, if the next join includes a transformation operator, introduce the execution cost of the corresponding transformation operator when calculating the execution cost of the corresponding combination.

[0138] Until a join including all tables with the minimum execution cost is obtained, and use this join as the hybrid physical query plan.

[0139] The dynamic programming algorithm constructs the optimal solution from bottom to top.

[0140] For complex queries involving a large number of tables, the computational complexity of the dynamic programming algorithm is too high. At this time, the greedy algorithm can be used to find an approximate optimal solution within an acceptable time. In some embodiments, using the greedy algorithm to generate a hybrid physical query plan with the minimum execution cost includes:

[0141] Traverse and evaluate the execution costs of all pairs of tables in all possible join methods and storage modes, and select the pair of tables with the minimum execution cost as the initial join.

[0142] Among the remaining tables, traverse and evaluate the execution cost of each table joining the current join in different storage modes, and select the join with the minimum execution cost as the next join. Among them, if the next join includes a transformation operator, introduce the execution cost of the corresponding transformation operator when calculating the execution cost of this join.

[0143] Until a join including all tables with the minimum execution cost is obtained as the hybrid physical query plan.

[0144] According to this embodiment, a greedy algorithm is used to generate an optimal join, and the calculation efficiency is significantly higher than that of the dynamic programming algorithm, and near-optimal results can usually be obtained in practice.

[0145] Through this step, an optimal query plan can be generated according to the intermediate result evaluated by the hybrid cost model. The generated plan may simultaneously include operations of row storage and column storage. By introducing R2C or C2R conversion operators at appropriate positions, the conversion cost and performance gain are balanced to make full use of the advantages of the two storage modes, significantly improving the query performance of the HTAP database. Moreover, the exploration process considers the overall cost of the entire query process, rather than just local optimization.

[0146] Step 28, execute the hybrid physical query plan to obtain the query result.

[0147] After generating the optimal hybrid physical query plan, the system will execute the plan to obtain the query result. This step involves the specific implementation of the hybrid execution environment, including data flow conversion between row storage and column storage to efficiently execute various operator operations.

[0148] As described above Figure 1 As shown, the hybrid execution environment of this embodiment may include main components such as a Hybrid Execution Model, R2C operator, C2R operator, Row Operators, and Column Operators. These components work together and interact with the storage layer through a Buffer to achieve efficient hybrid execution.

[0149] In addition to the above query optimization, the hybrid transaction and analysis processing (HTAP) database query optimization method proposed in this embodiment also proposes a reinforcement learning-based in-memory column selection mechanism for selecting the optimal column subset to be loaded into the in-memory column storage under the memory budget constraint to optimize the subsequent query performance, especially applicable to the situation where memory is limited and the most efficient column combination needs to be selected from a huge column combination space. This mechanism further improves the overall performance of the HTAP database.

[0150] In an HTAP database, especially in a system with a hybrid architecture of main row storage and in-memory column storage (IMCS), due to limited memory resources, not all columns can be loaded into the in-memory column storage. Therefore, which columns to select for loading into memory becomes a key factor in improving system performance. This selection faces the following challenges:

[0151] 1. Huge combinatorial space: For a table with n columns, there are 2^n - 1 possible column combinations, which is a problem of combinatorial explosion.

[0152] 2. Memory budget limitation: The memory available for column storage in the system is limited, and the optimal column set needs to be selected within the budget.

[0153] 3. Dynamic workload: The query pattern may change over time, and the column selection strategy needs to be adjusted adaptively.

[0154] 4. Evaluation complexity: Evaluating the impact of different column combinations on query performance is complex and difficult to calculate directly.

[0155] To address these challenges, according to some embodiments of the present disclosure, the following column selection mechanism based on Monte Carlo Tree Search (MCTS) is proposed:

[0156] Based on the query history and using a transformation operator, columns with high access frequencies are filtered out from the table as candidate columns;

[0157] Using the Monte Carlo Tree Search (MCTS) algorithm, the currently selected column subset is defined as the search state, and each search tree node represents a specific column subset. The tree search is performed iteratively through the following four stages:

[0158] Selection stage: Using the UCT value to balance exploration and exploitation, starting from the root node, select the child node with the largest UCT value until an unexpanded node is reached;

[0159] Expansion stage: On an unexpanded node, select a candidate column that has not been tried yet, add it to the current column subset to form a new search state, and create a corresponding child node;

[0160] Simulation stage: Starting from the newly created node, randomly select candidate columns to add to the column subset using a fast simulation strategy until the memory budget limit is met or there are no more candidate columns available, and evaluate the reward value of the final column subset;

[0161] Backpropagation stage: Backpropagate the reward value obtained in the simulation stage to update the statistical information of all nodes on the path;

[0162] Repeat the above four stages until the search end condition is reached;

[0163] After the search ends, select the column subset with the largest reward value to be loaded into the in-memory column store to optimize subsequent query performance, where the total size of the column subset does not exceed the preset memory budget.

[0164] Specifically, in an example, the specific implementation process of the MCTS algorithm search is as follows:

[0165] 1. Selection Phase: The UCT (Upper Confidence Bound applied to Trees) value is used to balance exploration and exploitation. Starting from the current node in this phase, the algorithm initially starts at the root node and continuously searches for the child node with the maximum UCT value, while checking if there are unexpanded child nodes for the current node. If there are, this phase terminates; otherwise, it continues to select the child node with the maximum UCT value downward. The formula for calculating the UCT of node u is as follows:

[0166]

[0167] Where Q(u) represents the average reward of node u, N(u) represents the cumulative number of visits to node u, and c is a hyperparameter used to adjust how to balance the relationship between exploration and exploitation in the search process.

[0168] 2. Expansion Phase: When the algorithm finds an unexpanded node, it will search for possible actions in the current state. By selecting each possible action, it expands the successors of the current node to obtain a state representing a new set of candidate columns. For a new action, the current set of candidate columns is combined with the corresponding set of actions to obtain a new set of candidate columns and the corresponding state.

[0169] 3. Simulation Phase: Starting from the current node, a large number of simulations are carried out using the Rollout algorithm. The Rollout algorithm is: randomly execute actions until no query template can be added to the current set of candidate columns. This node is marked as a terminal node. After the Rollout process terminates, the algorithm will call the learning model to evaluate the set of candidate columns to obtain the corresponding reward value, which is used to update the reward value in the backpropagation phase.

[0170] 4. Backpropagation Phase: Starting from the node that obtained the reward value from the simulation, the Q-value, N-value, and related information of each node are updated bottom-up according to the reward value information obtained by the node, which is beneficial for further search.

[0171] In some embodiments, the reward function of the MCTS algorithm is:

[0172]

[0173] Where CS is the currently selected set of columns, D' is the set of all columns, n represents the total number of queries used to train the MCTS algorithm, i represents the query number, B is a reward function negatively correlated with the query cost, B(Q i , CS) represents the reward when using the set of columns CS for the i-th query Q i When using the set of columns CS, B(Q i , D') represents the reward when using the set of columns D' for the i-th query Q iBenefits when using all columns.

[0174] The reward function according to this embodiment takes into account the comprehensive effects of all queries and represents the overall performance ratio of selecting a specific column subset compared to using all columns. The larger the reward value, the closer the performance of the column subset is to the ideal situation of using all columns.

[0175] The hybrid transaction and analytical processing (HTAP) database query optimization method proposed in this embodiment significantly improves query performance and resource utilization efficiency by integrating the advantages of row storage and column storage. According to this embodiment, first, the SQL query is parsed to generate a query plan tree containing multiple logical operators; then, a hybrid cost model with hyperparameters optimized by Bayesian search is used to accurately evaluate the execution cost of each logical operator under different storage modes; next, according to the query complexity, dynamic programming or greedy algorithm is intelligently selected to generate an optimal hybrid physical query plan containing transformation operators; finally, the plan is executed to obtain the query result. In addition, this method also uses the Monte Carlo tree search algorithm to asynchronously select the optimal column subset to be loaded into memory to further optimize subsequent queries. Through this innovative hybrid execution framework, the system fully utilizes the index advantages and cache friendliness of row storage, as well as the vectorized processing ability of column storage, significantly accelerating analytical queries while maintaining high transaction throughput, and is applicable to application scenarios such as finance and e-commerce that require simultaneous transaction processing and analysis.

[0176] Figure 3 Shows a schematic diagram of the system workflow according to an exemplary embodiment of the present disclosure. The workflow includes the following steps:

[0177] Step 1 (Parse query): When an SQL statement enters the database, the SQL parser first parses the SQL and obtains the logical query plan, generating each required operator. Figure 3 The upper left corner in shows an input query example for retrieving data from the Review and Product tables based on the conditions P.ID < 100 and PR.Rating < 5.

[0178] Step 2 (Analyze the cost of each operator): The parsed logical operators are input into the hybrid cost model, which can effectively identify and evaluate various operations, including row scan, column scan, hash join, and the newly added transformation operators R2C and C2R. The identification of these operations lays the foundation for subsequent cost quantification and plan generation.

[0179] Step 3 (Cost evaluation of each candidate plan): With the support of the hybrid cost model, the system evaluates the cost of each candidate query plan and selects the optimal plan according to the principle of cost minimization. This process fully utilizes the computing power of the hybrid cost model to ensure that the selected plan is efficient and reliable.

[0180] Step 4 (Introducing a heuristic algorithm for plan search): Since the time complexity of generating an optimal hybrid physical query plan is relatively high, the present disclosure proposes a heuristic algorithm combining dynamic programming (DP) and greedy strategy to handle different scenarios of a small number of joins and a large number of joins respectively. The DP algorithm is applicable to small-scale joins, while the greedy strategy provides a fast solution for large-scale joins. Figure 3 The optimized hybrid plan is shown on the right, which includes operations such as projection, join, and filter, as well as the inserted R2C and C2R transformation operators at appropriate positions.

[0181] Step 5 (Using the R2C operator for query plan optimization): In the example workflow, the Product table performs an index scan on the ID column in row storage, and at the same time, the predicate ID < 100 is applied to complete the preliminary filtering operation. Then, the filtered results are converted to columnar storage format through the R2C operator, and two target columns are extracted: ID and Name.

[0182] Step 6 (Using the C2R operator for query optimization): This operator is responsible for converting the columnar storage back to row storage. In this example, two execution modes are designed for narrow tables and wide tables respectively, and the applicable execution method is dynamically determined through the cost model of the database. The optimized C2R operator can significantly improve the flexibility and overall performance of plan execution.

[0183] After the data conversion is completed, the hybrid plan makes full use of the characteristics of materialized columns to perform efficient vectorized join operations. This process significantly improves the computational efficiency of the join and provides strong support for the optimization of query performance.

[0184] Steps 7 - 9 (Column selection optimization): Figure 3 The process of extracting historical records from the query log, combining the memory budget for column selection, and the column selection optimization based on MCTS is also shown. These operations are all completed in the storage layer asynchronously to maximize efficiency.

[0185] Through this hybrid execution scheme, the system can simultaneously utilize the index advantage of row storage and the vectorized processing ability of column storage to achieve a significant improvement in query performance, especially suitable for complex query processing in HTAP environments.

[0186] Figure 4 The specific process of generating a hybrid physical query plan using the dynamic programming algorithm according to an exemplary embodiment of the present disclosure is shown. The red arrow marks the globally optimal path, which corresponds to a select-project-join (SPJ) query, including the chained join of three tables T1, T2, and T3. The system marks row storage and column storage respectively: light blue represents row storage, and pink represents column storage.

[0187] In the first step, the system selects the optimal scan method for each table in each store according to the scanning cost. For example, the better scan methods for T1 in row store and column store are Index Scan and SeqScan respectively. In the second step, the system uses the dynamic programming algorithm (DP) to evaluate all candidate hybrid plans, that is, enumerate each combination and evaluate its execution cost. For example, index join IJ<T1,T2> in row store r , hash join HJ<T1,T2> in column store c , sort-merge join SMJ<T1,T3> in row store r . In the third step, by comparing the costs of each candidate plan, the optimal hybrid physical query plan is output:

[0188] Figure 5 FIG. shows the workflow of the column selection mechanism based on Monte Carlo tree search (MCTS) according to an exemplary embodiment of the present disclosure, which is divided into two main parts: upper and lower.

[0189] Figure 5 The upper half of describes the candidate column generation process, which mainly includes:

[0190] 1. SQL to column conversion (SQL2Cols): Extract various SQL operations (such as Select, Filter, Join, etc.) from the query log, and identify the columns involved in each query (Q1, Q2, Q3...);

[0191] 2. Use R2C pruning: Analyze the applicability of R2C operations to different column sets, filter candidate columns based on the conversion characteristics from row store mode to column store mode, and mark special cases such as "No R2C" ( "No R2C");

[0192] 3. Use C2R pruning: Analyze the applicability of C2R operations to different column sets, further filter candidate columns based on the conversion characteristics from column store to row store, and identify column combinations that require C2R operations;

[0193] 4. Merge candidate columns: Merge the column sets after R2C and C2R evaluations to generate the final candidate column set.

[0194] Figure 5 The lower half of describes the MCTS column selection algorithm, which mainly includes:

[0195] 1. Input: Budget limit (Budget: 10GB), historical load, candidate columns, database (Database);

[0196] 2. Four-stage loop search of MCTS:

[0197] (1) Selection: Starting from the root node, select a child node based on the UCT value;

[0198] (2) Expansion: Add new candidate columns and create a new search state;

[0199] (3) Simulation: Randomly select columns starting from the new node until reaching a terminal node;

[0200] (4) Update: Backpropagate the reward value and update the node statistics;

[0201] 3. Feasible solution: Select the best column, load the selected column into the database, and optimize subsequent queries.

[0202] Figure 5 clearly shows how the entire column selection process filters candidate columns by analyzing query features and leveraging the characteristics of transformation operators, and then efficiently finds the optimal column subset in a huge search space through the MCTS algorithm. Figure 5 The search tree nodes in represent different column subset states, the blue path shows the path explored during the search, and the terminal node marks the end condition of the search.

[0203] Figure 6 For the electronic device provided by at least one embodiment of the present disclosure, the device includes a memory and a processor. The memory is used to store computer instructions that can run on the processor, and the processor is used to implement the query optimization method of the HTAP database according to any embodiment or implementation manner of the present disclosure when executing the computer instructions.

[0204] At least one embodiment of the present disclosure also provides a computer-readable storage medium, on which a computer program is stored. When the program is executed by a processor, it implements the query optimization method of the HTAP database according to any embodiment or implementation manner of the present disclosure.

[0205] Those skilled in the art should understand that one or more embodiments of this specification can be provided as a method, a system, or a computer program product. Therefore, one or more embodiments of this specification can take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware aspects. Moreover, one or more embodiments of this specification can take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) that contain computer-usable program code.

[0206] Each embodiment in this specification is described in a progressive manner. For the identical or similar parts among the embodiments, they can be referred to each other. Each embodiment focuses on the differences from other embodiments. In particular, for the embodiments of the data processing device, since it is basically similar to the method embodiments, the description is relatively simple. For the relevant parts, reference can be made to the partial description of the method embodiments.

[0207] The above describes specific embodiments of this specification. Other embodiments are within the scope of the appended claims. In some cases, the acts or steps recited in the claims can be performed in a different order than in the embodiments and still achieve the desired result. Additionally, the processes depicted in the figures do not necessarily require the particular order or sequential order shown to achieve the desired result. In certain implementations, multitasking and parallel processing are also possible or may be advantageous.

[0208] The embodiments of the subject matter and the functional operations described in this specification can be implemented in the following: digital electronic circuits, tangible computer software or firmware, computer hardware including the structures disclosed in this specification and their structural equivalents, or a combination of one or more of them. The embodiments of the subject matter described in this specification can be implemented as one or more computer programs, i.e., one or more modules of computer program instructions encoded on a tangible non-transitory program carrier to be executed by a data processing apparatus or to control the operation of a data processing apparatus. Alternatively or additionally, the program instructions can be encoded on a machine-generated propagated signal, such as a machine-generated electrical, optical, or electromagnetic signal, which is generated to encode and transmit information to a suitable receiver apparatus for execution by a data processing apparatus. A computer storage medium can be a machine-readable storage device, a machine-readable storage substrate, a random or serial access memory device, or a combination of one or more of them.

[0209] The processes and logical flows described in this specification can be executed by one or more programmable computers executing one or more computer programs to perform corresponding functions by operating on input data and generating output. The processes and logical flows can also be executed by dedicated logic circuits, such as FPGAs (Field Programmable Gate Arrays) or ASICs (Application Specific Integrated Circuits), and the apparatus can also be implemented as dedicated logic circuits.

[0210] Although this specification contains many specific implementation details, these should not be construed as limiting the scope of any invention or the scope of what is claimed, but rather as mainly for describing the features of specific embodiments of a particular invention. Certain features described in multiple embodiments in this specification can also be implemented in combination in a single embodiment. On the other hand, the various features described in a single embodiment can also be implemented separately in multiple embodiments or in any suitable sub-combination. In addition, although features may operate in certain combinations as described above and even initially claimed as such, one or more features from the claimed combination can in some cases be removed from that combination, and the claimed combination can be directed to a sub-combination or a variation of a sub-combination.

[0211] Similarly, although operations are depicted in the drawings in a particular order, this should not be construed as requiring that the operations be performed in the particular order shown or sequentially, or that all illustrated operations be performed to achieve the desired result. In some cases, multitasking and parallel processing may be advantageous. In addition, the separation of the various system modules and components in the above embodiments should not be construed as requiring such separation in all embodiments, and it should be understood that the described program components and systems can generally be integrated together in a single software product or packaged into multiple software products.

[0212] Thus, specific embodiments of the subject matter have been described. Other embodiments are within the scope of the appended claims. In some cases, the acts recited in the claims can be performed in a different order and still achieve the desired result. In addition, the processes depicted in the drawings are not necessarily in the particular order or sequential order shown to achieve the desired result. In some implementations, multitasking and parallel processing may be advantageous.

[0213] The above description is only the preferred embodiment of one or more embodiments of this specification and is not intended to limit one or more embodiments of this specification. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of one or more embodiments of this specification shall be included within the scope protected by one or more embodiments of this specification.

Claims

1. A query optimization method for HTAP databases, characterized in that, Including: Receiving and parsing an SQL query request to generate a logical query plan, where the logical query plan includes multiple logical operators; Evaluating the execution cost of each logical operator in the logical query plan in a row storage mode and a column storage mode respectively by using a hybrid cost model, where the hybrid cost model includes sub-models for evaluating the execution cost of logical operators in a row storage mode and a column storage mode respectively and a sub-model for evaluating the execution cost of a conversion operator, the conversion operator is used to convert data between a row storage mode and a column storage mode, and the hyperparameters of the hybrid cost model are determined by Bayesian search to uniformly quantify the execution cost of logical operators in different storage modes and the execution cost of the conversion operator; Selectively adopting a dynamic programming algorithm or a greedy algorithm according to the number of tables involved in the logical query plan, based on the evaluated execution cost of logical operators, introducing the execution cost of the conversion operator, and gradually evaluating intermediate results and pruning by using the hybrid cost model to generate a hybrid physical query plan with the minimum execution cost; Executing the hybrid physical query plan to obtain a query result.

2. The query optimization method according to claim 1, wherein The method further includes optimizing an R2C operator that converts row storage data into column storage data in the conversion operator as follows: Using multi-threaded technology to parallelize the sequential scan in row storage; Adopting multi-threaded technology to parallelize the format conversion tasks of multiple data blocks; Pushing down filtering operations and projection operations to a position closer to the data source to reduce the amount of data that needs to be format-converted; According to query requirement analysis, only loading columns required by the query into column storage.

3. The query optimization method according to claim 1, characterized in that The implementation methods of a C2R operator that converts column storage data into row storage data in the conversion operator include a materialization-based implementation method and a RowID-based implementation method, and the hybrid cost model dynamically evaluates the execution costs of the two implementation methods and selects the implementation method with the smaller execution cost.

4. The query optimization method according to claim 1, wherein The hybrid cost model includes: A row storage scan cost sub-model for evaluating the execution cost of sequential scan, non-clustered index scan, clustered index scan, and filtering in a row storage mode; A column storage scan cost sub-model for evaluating the execution cost of scan and filtering in a column storage mode; A row storage join cost sub-model for evaluating the execution cost of hash join, index join, and merge join in a row storage mode; A column storage join cost sub-model for evaluating the execution cost of hash join and merge join in a column storage mode; A conversion cost model for evaluating the execution cost of an R2C operator that converts row storage data into column storage data and the execution cost of a C2R operator that converts column storage data into row storage data.

5. The query optimization method according to claim 4, wherein Evaluate the execution cost Cost of the R2C operator according to the following formula R2C (T, S): Cost R2C (T,S) = (γ R2C + γ t ) * w(S) * N o , Among them, T represents the data source of the input row storage mode to be converted, S represents the set of materialized columns in T, and γ R2C represents the cost coefficient for creating materialized columns, and γ t represents the cost coefficient for data transmission, w(S) represents the width of the materialized columns, and N o represents the number of rows to be converted to column storage mode after filtering in the input T, and γ R2C and γ t are hyperparameters optimized by Bayesian search.

6. The query optimization method according to claim 4, wherein Evaluate the execution cost Cost of the C2R operator according to the following formula C2R (T, S): Cost C2R (T,S) = min{(γ C2R + γ t ) * w(S) * N o , (γ C2R + γ t ) * w(rid(T)) * N o + γi * w(S) * N_o}, Among them, T represents the data source in columnar storage mode to be converted, S represents the set of columns to be converted, the first part in min{} is the execution cost of the materialization-based implementation method, the second part is the execution cost of the RowID-based implementation method, γ C2R represents the conversion cost coefficient, γ t represents the data transmission cost coefficient, γ i represents the coefficient for locating each tuple, w(S) represents the width of the column set, w(rid(T)) represents the width of the RowID, N o represents the number of rows to be converted to row storage mode by filtering in the input T, γ C2R 、γ t and γ i are hyperparameters optimized by Bayesian search.

7. The query optimization method according to claim 1, wherein Selectively adopting a dynamic programming algorithm or a greedy algorithm according to the number of tables involved in the logical query plan, including: When the number of tables involved in the logical query plan is less than a preset threshold, adopting a dynamic programming algorithm; When the number of tables involved in the logical query plan is greater than or equal to the preset threshold, adopting a greedy algorithm.

8. The query optimization method according to claim 1, wherein Generating a hybrid physical query plan with the minimum execution cost by using a dynamic programming algorithm, including: Evaluate and record separately the minimum execution cost of each table in the row storage mode and the column storage mode as the intermediate result of the single table in the two storage modes; For the two storage modes of each table subset for which the minimum execution cost has been calculated and stored, enumerate the combinations of the next table to be joined, the join order, and the storage mode after joining, evaluate the execution cost of each combination, and record the minimum execution cost of the new table subset obtained after joining the next table in the two storage modes as the intermediate result to incrementally construct a larger table set. Among them, if a transformation operator is included in the next join, the execution cost of the corresponding transformation operator is introduced when calculating the execution cost of the corresponding combination; Until a join including all tables with the minimum execution cost is obtained, and use this join as the hybrid physical query plan.

9. The query optimization method according to claim 1, wherein Use a greedy algorithm to generate a hybrid physical query plan with the minimum execution cost, including: Traverse and evaluate the execution costs of all pairs of tables in all possible join methods and storage modes, and select the pair of tables with the minimum execution cost as the initial join; Among the remaining tables, traverse and evaluate the execution costs of each table in different storage modes when joining the current join, and select the join with the minimum execution cost as the next join. Among them, if a transformation operator is included in the next join, the execution cost of the corresponding transformation operator is introduced when calculating the execution cost of this join; Until a join including all tables with the minimum execution cost is obtained as the hybrid physical query plan.

10. The query optimization method according to claim 1, wherein The method further includes: Based on the query history record, and using a transformation operator, screen out the columns with high access frequency from the table as candidate columns; Use the Monte Carlo Tree Search (MCTS) algorithm. Define the currently selected column subset as the search state. Each search tree node represents a specific column subset, and execute the tree search in a loop through the following four stages: Selection stage: Use the UCT value to balance exploration and exploitation. Start from the root node and select the child node with the largest UCT value until an unfully expanded node is reached; Expansion stage: On the unfully expanded node, select a candidate column that has not been tried, add it to the current column subset to form a new search state, and create a corresponding child node; Simulation stage: Start from the newly created node, randomly select candidate columns to add to the column subset using a fast simulation strategy until the memory budget limit is met or there are no more candidate columns to select, and evaluate the reward value of the final column subset; Backpropagation stage: Backpropagate the reward value obtained in the simulation stage to update the statistical information of all nodes on the path; Repeat the above four stages until the search end condition is reached; After the search ends, select the column subset with the largest reward value and load it into the in-memory column store to optimize the subsequent query performance, where the total size of the column subset does not exceed the preset memory budget.

11. The query optimization method according to claim 10, wherein The reward function of the MCTS algorithm is: Among them, CS is the set of currently selected columns, D' is the set of all columns, n represents the total number of queries used to train the MCTS algorithm, i represents the query number, B represents the benefit function negatively correlated with the query cost, B(Q i , CS) represents the benefit of the i-th query Q i when using the set of columns CS, B(Q i , D') represents the benefit of the i-th query Q i when using all columns.

12. An electronic device, characterized in that, The device includes a memory and a processor. The memory is used to store computer instructions that can run on the processor, and the processor is used to implement the method according to any one of claims 1 to 11 when executing the computer instructions.

13. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the method according to any one of claims 1 to 11.

Images