Method, device and storage medium for optimizing database query of htap

Abstract

This disclosure presents an HTAP database query optimization method, device, and storage medium that significantly improves query performance by combining the advantages of row-based and column-based storage. The method includes: parsing SQL queries to generate logical query plans; evaluating the execution cost of each logical operator under different storage modes using a hybrid cost model, where the hyperparameters of the hybrid cost model are optimized using Bayesian search; selectively using dynamic programming or a greedy algorithm based on the number of tables to generate a hybrid physical query plan with the lowest execution cost; and executing the plan to obtain the query results. Furthermore, this disclosure utilizes the MCTS algorithm to select the optimal subset of columns within a preset memory budget limit based on query history, further optimizing subsequent query performance. This disclosure solves the problem of incompatibility between row-based and column-based storage in existing HTAP systems, achieving operator-level hybrid execution while optimizing memory resource utilization.

Description

TECHNICAL FIELD

[0001] The present disclosure relates to the field of database query optimization, and particularly relates to a HTAP database query optimization method, device and storage medium. BACKGROUND

[0002] Hybrid Transactional Analytical Processing (HTAP) database technology processes concurrent transactions and analytical queries based on a one-stop architecture, which mainly faces applications with high requirements for transaction processing performance and query processing performance, such as transactional e-commerce websites, banking and financial systems, etc. Early HTAP databases face analytical ERP applications, mainly using columnar engines to process a large number of analytical queries and a small number of update transactions, and representative databases include SAP HANA, Hyrise, HyPer, etc. Since the concept of HTAP was formally proposed in 2014, many database systems have developed columnar engines based on the original row storage engine, mainly facing transactional banking businesses with real-time analysis needs. Their main advantage is that they can use row storage engines and columnar engines to process transactions and queries in one system, and representative databases include MemSQL, Oracle, SQL Server, etc. However, the existing HTAP systems still have many limitations in effectively utilizing the advantages of row storage and column storage.

[0003] Hybrid execution technology relies on HTAP databases to analyze the physical plan of the database, so that part (or all) of the database query is processed by the analytical database, and the remaining part is processed by the transactional database. One direct hybrid execution technology is to calculate the query cost of each query in different databases, and select the database with lower cost for processing. 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 lower cost for scanning the base table. The current main method is to select the way of query for different databases and the way of database for each base table of the query to execute the hybrid execution technology.

[0004] Existing systems can only generate sub-optimal plans based on row-store or column-store, and cannot generate a (optimal) hybrid plan by combining row-store and column-store. For example, in a PolarDB-IMCI database, a query can only be routed to one of the row-store or column-store, while the execution of the whole SQL can be accelerated by combining row-based index scan and column-based join. Meanwhile, the cost estimation of SQL queries by existing systems is not accurate, and existing systems heavily rely on row-based cost models during plan generation. However, due to the lack of a global cost model that can quantify row-store operators and column-store operators in a unified framework, the generated results are often not optimal.

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

[0006] Overall, existing HTAP systems have multiple limitations in effectively utilizing the advantages of row storage and column storage: the generated query plan can only be executed in row storage or column storage, and cannot generate a hybrid plan that combines the advantages of row storage and column storage; the cost model mainly relies on the cost model of row storage, and lacks a global cost model that can quantify row-store operators and column-store 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 quantify row-store operators and column-store operators, and select the most beneficial columns within the memory budget are the main technical problems to be solved. SUMMARY

[0007] The present disclosure aims 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 the query performance by combining row storage and column storage.

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

[0009] receiving and parsing a SQL query request to generate a logical query plan, the logical query plan comprising a plurality of logical operators;

[0010] evaluating the execution cost of each logical operator in the logical query plan in row storage mode and column storage mode respectively using a hybrid cost model, the hybrid cost model comprising sub-models for evaluating the execution cost 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 for converting data between row storage mode and column storage mode, the hyperparameters of the hybrid cost model being 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;

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

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

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

[0014] using multi-threading technology to process sequential scanning in row storage in parallel;

[0015] using multi-threading technology to perform format conversion tasks for multiple data blocks in parallel;

[0016] pushing filtering operations and projection operations to a position close to the data source to reduce the amount of data that needs to be converted in format;

[0017] loading only the columns required by the query into column storage according to the query requirement analysis.

[0018] In some implementations, the C2R operator that converts column-stored data into row-stored data is implemented in two ways: a materialization-based implementation and a RowID-based implementation. The hybrid cost model dynamically evaluates the execution cost of the two implementations and selects the implementation with the lower execution cost.

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

[0020] A row storage scan cost sub-model is used to evaluate the execution cost of sequential scans, non-clustered index scans, clustered index scans, and filtering in row storage mode;

[0021] The column storage scan cost sub-model is used to evaluate the execution cost of scanning and filtering in column storage mode;

[0022] A row-store join cost sub-model is used to evaluate the execution cost of hash joins, index joins, and merge joins in row-store mode;

[0023] A column-store join cost sub-model is used to evaluate the execution cost of hash joins and merge joins in column-store mode;

[0024] The conversion cost model is used to evaluate 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 implementations, the execution cost of the R2C operator is evaluated according to the following formula. R2C (T, S):

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

[0027] Where T represents the data source of the row storage mode to be transformed, S represents the set of materialized columns in T, and γ R2C γ represents the cost coefficient for creating materialized columns. t The data transfer cost coefficient is represented by w(S), which represents the width of the materialized column, and N is the value of N. o γ represents the number of rows in input T that have passed the filter and are to be converted to columnar storage mode. R2C and γ t These are the hyperparameters optimized using Bayesian search.

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

[0029] CostC2R (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 column storage schema to be transformed, S represents the set of columns to be transformed, the first part of min{} is the execution cost of the materialization-based implementation, the second part is the execution cost of the RowID-based implementation, and γ C2R γ represents the cost coefficient of the conversion. t γ represents the cost coefficient for data transmission. i This 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, and N o γ represents the number of rows in input T that have passed the filter and are to be converted to row storage mode. C2R γ t and γ i These are the hyperparameters optimized using Bayesian search.

[0031] In some implementations, dynamic programming or greedy algorithms are selectively employed based on 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 used;

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

[0034] In some implementations, dynamic programming algorithms are used to generate hybrid physical query plans with minimum execution cost, including:

[0035] Evaluate and record the minimum execution cost for each table in row storage mode and column storage mode respectively, as an intermediate result for a single table in the two storage modes;

[0036] For each table subset whose minimum execution cost has been calculated and stored, two storage modes are enumerated, the next table to be joined, the join order, and the combination of storage modes after the join are enumerated, and the execution cost of each combination is evaluated. The minimum execution cost of the new table subset obtained after joining the next table in each of the two storage modes is recorded as an intermediate result to incrementally build a larger table set. If the next join includes a transformation operator, the execution cost of the corresponding transformation operator is introduced when calculating the execution cost of the corresponding combination.

[0037] Continue until a join containing all tables with the minimum execution cost is obtained, and then use that join as the hybrid physical query plan.

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

[0039] Iterate through and evaluate the execution cost of all two tables under all possible join methods and storage modes, and select the two tables with the lowest execution cost as the initial join;

[0040] In the remaining tables, the execution cost of adding the current connection to each table under different storage modes is evaluated traversally, and the connection with the lowest execution cost is selected as the next connection. If the next connection includes a transformation operator, the execution cost of the corresponding transformation operator is introduced when calculating the execution cost of the connection.

[0041] The process continues until a join of all tables with the minimum execution cost is obtained, which serves as the hybrid physical query plan.

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

[0043] Based on the query history and using transformation operators, the columns with high access frequency are selected from the table as candidate columns;

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

[0045] In the selection phase, the exploration and utilization are balanced by using the UCT value. Starting from the root node, the child node with the largest UCT value is selected until a node that has not been fully expanded is reached.

[0046] During the expansion phase, for nodes that have not been fully expanded, a candidate column that has not yet been tried is selected, added to the current column subset to form a new search state, and a corresponding child node is created.

[0047] During the simulation phase, starting from the newly created node, a fast simulation strategy is used to randomly select candidate columns to add to the column subset until the memory budget limit is met or there are no more candidate columns to choose from. The reward value of the final column subset is then evaluated.

[0048] During the backtracking phase, the reward values ​​obtained in the simulation phase are propagated back to update the statistical information of all nodes on the path;

[0049] Repeat the above four stages until the search termination condition is met;

[0050] After the search ends, select the column subset with the highest reward value and load it into the in-memory column store to optimize the performance of subsequent queries. The total size of the column subset shall not exceed the preset memory budget.

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

[0052]

[0053] Where CS is the currently selected set of columns. This is the set of all columns, where n represents the total number of queries used to train the MCTS algorithm, i represents the query number, and B represents the payoff function negatively correlated with query cost. This represents the i-th query Q. i Benefits of using CS column sets This represents the i-th query Q. i Benefits when using all columns.

[0054] This disclosure addresses the query optimization problem of HTAP databases by proposing a hybrid execution plan solution that significantly improves query performance and resource utilization. By introducing R2C and C2R transformation operators, this disclosure overcomes the limitation of traditional query optimizers that can only select a single storage format (row-based or column-based), achieving complementary advantages of row-based and column-based storage. The hybrid execution plan fully leverages the efficiency of row-based index scanning and the advantages of column-based vectorized computation, dynamically selecting the optimal execution path for different query loads and data characteristics. Bayesian search is used to determine the hyperparameters of the hybrid cost model, uniformly quantifying the execution costs of row-based and column-based operators and achieving automatic hyperparameter optimization. Depending on the number of tables involved, dynamic programming (DP) or a greedy algorithm is used to generate the hybrid execution plan, efficiently searching for execution schemes with lower execution costs within a large plan space.

[0055] Furthermore, some embodiments of this disclosure introduce a column selection mechanism based on Monte Carlo Tree Search (MCTS), enabling intelligent management of in-memory column storage. By selectively loading columns that most significantly improve query performance within the constraints of memory budget, unnecessary memory overhead is avoided, significantly improving the utilization of memory resources.

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

[0057] Other features and advantages of the technical solutions proposed in this disclosure are described below. Attached Figure Description

[0058] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this specification and, together with the description, serve to explain the principles of this specification.

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

[0060] Figure 2 A flowchart of an optimized query method for an HTAP database according to an embodiment of the present disclosure is shown.

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

[0062] Figure 4 A schematic diagram illustrating the generation of a hybrid physical query plan using a dynamic programming algorithm according to an exemplary embodiment of the present disclosure is shown.

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

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

[0065] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numerals in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with this disclosure. Rather, they are merely examples of apparatuses and methods consistent with some aspects of this disclosure as detailed in the appended claims.

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

[0067] The parsed SQL query represents a SQL query statement that has undergone preliminary processing.

[0068] The optimizer comprises three core components: a hybrid cost model, used to evaluate the cost of different execution paths; a hybrid plan generator, which includes two sub-modules: dynamic programming and a greedy algorithm; and a hybrid plan, representing the generated optimal hybrid physical query plan.

[0069] The buffer is divided into three types of data storage: row storage data, used to store data in row storage mode; incremental data, used to store incremental updates; and column storage data, used to store 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 R2C (row to column) and C2R (column to row) transformation operators; row storage operators for handling various operations on row-stored data; and column storage operators for handling various operations on column-stored data.

[0071] Row storage includes index and disk components.

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

[0073] This architecture diagram clearly illustrates the complete process of query parsing and execution, as well as the data flow relationships between the components. From the overall architecture perspective, it presents the innovative design of this disclosure in terms of hybrid execution and column selection.

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

[0075] Step 22: Receive and parse the SQL query request to generate a logical query plan, which 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 it is also the foundation for subsequent optimization processes.

[0077] The SQL parsing process mainly includes lexical analysis, syntax analysis, semantic analysis, and finally, a logical query plan representing the query operation and structure is created based on the syntax tree.

[0078] A logical query plan is a tree structure where each node represents a logical operator, such as Scan, Filter, Join, Aggregate, and Sort. Leaf nodes typically represent table scan operations, indicating data retrieval from the data source; intermediate nodes represent various data processing operations; and the root node represents the operation that ultimately produces the query results.

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

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

[0081] In this step, the system uses a hybrid cost model to evaluate the execution cost of each logical operator in the logical query plan under both row-based and column-based storage modes. The hybrid cost model uniformly quantifies the execution cost of operators under different storage modes, as well as the cost of storage mode conversion, providing a foundation for subsequently generating the optimal hybrid physical query plan.

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

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

[0084] Columnar storage scan cost sub-model: Evaluates the execution cost of scan and filter operations in columnar storage mode;

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

[0086] Transformation Cost Sub-model: Evaluate the execution cost of the R2C (Row-to-Column) operator for converting row-stored data to column-stored data and the C2R (Column-to-Row) operator for converting column-stored data to row-stored data.

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

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

[0089] ,

[0090] Where Seq Scan indicates sequential scan, Unclustered indicates a non-clustered index, Clustered indicates a clustered index, T represents the data source of the row storage mode to be scanned, and α f N represents the coefficient of filtering cost in sequential scanning. t (T) represents the number of all tuples in T, N p (T) represents the total number of pages in T, α i α represents the cost coefficient for processing a tuple through an index. r N represents the cost coefficient of random access. it (T) represents the number of tuples accessed via the index, N ip (T) represents the page number corresponding to the index entry. α f α i α r These are the hyperparameters optimized using Bayesian search. In actual evaluation, the system calculates the cost of these three scanning methods and selects the method with the lowest cost as the optimal access method in row storage mode.

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

[0092] ,

[0093] Where T represents the data source of the column storage schema to be scanned, S represents the set of columns to be scanned, and β f N represents the cost coefficient used for filtering. b β represents the number of blocks in storage, b represents the size of each block, and β represents the number of blocks in storage. p It is the cost coefficient related to column s. w (s) represents the width of column s, N o This indicates the number of rows in input T that have passed the filter. This represents the cost of incremental memory scans. β f β p These are the hyperparameters optimized using Bayesian search. Among them, the incremental storage scan cost... as follows:

[0094] ,

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

[0096] The row storage join cost sub-model calculates the execution cost for different join methods (hash join, index join, and merge join) and selects the join method with the lowest cost as the optimal join method in the row storage mode.

[0097] In some implementations, the execution cost of the join operator in row storage mode can be calculated in the row storage join cost sub-model according to the following formula. :

[0098] ,

[0099] Where Hash represents hash join, Index Join represents index join, Merge Join represents merge join, and T L T represents the row storage data source to be connected on the left. R This indicates the row storage data source to be connected on the right, Cost. build (T L ) indicates the use of T L Cost of building a hash tableprobe (T R ) indicates the use of T R The cost of probing a hash table, N o Indicates (T) L ) represents T L The number of rows filtered, Cost index (T R ) indicates that through T R The cost of looking up a row in a row-stored index. s (T L , T R ) indicates that for T L and T R The cost of sorting, α m |L| represents the cost coefficient of the merge operation in row storage mode, and |L| represents T. L Size (number of rows), |R| represents T R Size (number of rows). α m These are the hyperparameters optimized using Bayesian search. During the actual evaluation process, the system calculates the cost of these three join methods and selects the one with the lowest cost as the optimal join method in row-oriented storage. This cost-based selection mechanism is the core of query optimization, ensuring that the most efficient execution strategy is chosen under different data characteristics and query patterns.

[0100] Column-oriented storage typically does not support indexed joins. Therefore, the column-oriented join cost sub-model can calculate the execution costs of hash joins and joins separately, and select the method with the lower execution cost as the optimal join method in column-oriented storage.

[0101] In some implementations, the execution cost of the join operator in column-store mode can be calculated in the column-store join cost sub-model according to the following formula. :

[0102] ,

[0103] Where Hash represents hash join, Merge Join represents merge join, and T L T represents the data source of the column-store schema to be joined on the left. R Indicates the data source of the column-store mode to be joined on the right, Cost build (T L ) indicates the use of T L Cost of building a hash table probe (T R ) indicates that T R The cost of probing a hash table, β h This represents the cost coefficient of a hash join operation in columnar storage mode, taking into account the acceleration effect of SIMD instructions.s (T L , T R ) indicates that for T L and T R The cost of sorting the joined columns in the array, β s β represents the cost coefficient for sorting operations in column-oriented storage. m |L| represents the cost coefficient of the merge operation in columnar storage mode, and |L| represents the data source T on the left. L Size (number of rows), |R| represents the data source T on the right. R Size (number of rows). β h β s β m These are the hyperparameters optimized using Bayesian search.

[0104] The main advantage of column-oriented joins is their ability to accelerate processing using vectorized methods and SIMD instructions, which can be achieved through beta... h The system calculates the cost of these two join methods and selects the method with the lowest cost as the optimal join method in columnar storage mode.

[0105] In the hybrid execution process according to this embodiment, two transformation operators, R2C (row to column) and C2R (column to row), are specifically introduced to solve the problem that row storage and column storage cannot work together efficiently in existing HTAP systems. This disclosure achieves operator-level hybrid execution capability through transformation operators, enabling the system to flexibly switch storage modes at different stages of a single query, breaking through the limitation of traditional HTAP systems that can only generate single-mode execution plans of row storage or column storage.

[0106] The R2C operator is used to convert row-stored data into column-stored data. In some embodiments according to this disclosure, R2C is optimized as follows to improve performance and efficiency:

[0107] 1. Parallel Scan: By optimizing sequential scan operations in row memory through parallel technology, the scan time is significantly reduced. 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-threaded acceleration: Multi-threaded technology is used to accelerate the data conversion process between row storage and column storage. Multi-threaded processing can execute the conversion tasks of multiple data blocks in parallel, significantly improving the overall processing speed.

[0109] 3. Pushdown filtering and projection: Push filtering and projection operations down to a position closer to the data source, significantly reducing the amount of data that needs to be transferred and transformed, and improving query performance. For example, perform transformation only on 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 the necessary columns are loaded into the column store, rather than the entire table's data. For example, if a query only requires column "A" for projection, and the join filter condition depends on column "B", the system will only load columns "A" and "B", without loading irrelevant data. This on-demand loading strategy significantly reduces the overhead of data transfer and transformation.

[0111] This implementation method, through the above optimizations, significantly improves... The performance of the operator reduces the resource consumption of data processing and enhances the execution efficiency of complex queries.

[0112] In some implementations, the C2R operator that converts column-stored data into row-stored data is implemented in two ways: a materialization-based implementation and a RowID-based implementation. The hybrid cost model dynamically evaluates the execution cost of the two implementations and selects the implementation with the lower execution cost.

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

[0114] 1. Vectorized execution: Apply filtering conditions to columnar storage and generate bitmaps for relevant columns to mark data that meets the conditions.

[0115] 2. Bitmap merging and data location: Based on the results of vectorized execution, bitmaps are merged to determine the column data that meets the conditions, and tuples are reconstructed based on these data.

[0116] 3. Data materialization: The reconstructed tuples are temporarily stored as row-oriented data for use in 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 queries require retrieving a large number of columns, the materialized implementation can lead to significant transformation and transfer overhead, thus reducing efficiency.

[0118] In one example, the C2R operator is implemented based on RowID. The C2R operator implemented with RowID does not directly obtain complete data from the column store, but relies on RowIDs for data location, which includes the following three steps.

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

[0120] 2. Obtain RowIDs: Provide the obtained RowIDs to the row storage to directly retrieve the required data from the row storage.

[0121] 3. Join and sorting support: In join operations, Operators can process join results by passing RowID pairs; in sorting operations, data from other columns are combined using the sorted RowIDs of one column.

[0122] To find a better balance between the materialized implementation and the RowID implementation, as mentioned above, the hybrid cost model dynamically evaluates the performance of the two implementations in different scenarios and selects the method with the lowest cost to perform data transformation. For example, the materialized implementation is generally better when dealing with a small number of columns, while the RowID implementation is more efficient when dealing with a large number of columns.

[0123] In some implementations, the hybrid cost model evaluates the execution 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 row storage mode to be transformed, S represents the set of materialized columns in T, and γ R2C γ represents the cost coefficient for creating materialized columns. t The data transfer cost coefficient is represented by w(S), which represents the width of the materialized column, and N is the value of N. o γ represents the number of rows in input T that have passed the filter and are to be converted to columnar storage mode. R2C and γ t These are the hyperparameters optimized using Bayesian search.

[0126] In some implementations, the hybrid cost model evaluates the execution 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 column storage schema to be transformed, S represents the set of columns to be transformed, the first part of min{} is the execution cost of the materialization-based implementation, the second part is the execution cost of the RowID-based implementation, and γ C2R γ represents the cost coefficient of the conversion. t γ represents the cost coefficient for data transmission. i This 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, and N o γ represents the number of rows in input T that have passed the filter and are to be converted to row storage mode. C2R γ t and γ i These are the hyperparameters optimized using Bayesian search.

[0129] The goal of Bayesian search is to search for all hyperparameters X, using the execution time Y of each operator as the objective value, and to find suitable hyperparameters such that the execution cost evaluated according to the hybrid cost model approximates the objective value. The execution time of each operator is obtained from the query log, and the total query time is additionally added for overall hyperparameter tuning.

[0130] For example, suppose the execution cost of a certain type of operator O is calculated using the formula c * |T|, where c is a hyperparameter. From this formula, we can obtain X = c. = c*|T|; Each query and operator contributes {X, The samples are used for training. The target value is the running time of operator O.

[0131] This embodiment selects Bayesian search as a hyperparameter search and tuning tool, and unifies the cost calculation between row-based and column-based storage in the hybrid cost model. This allows for a unified comparison of the execution cost calculated by the operator in row-based storage mode and the execution cost calculated in column-based storage mode, thereby generating the optimal hybrid physical query plan. Specifically, Bayesian optimization uses existing load observation data to train a Gaussian process model. Subsequently, Bayesian optimization uses the Gaussian process model to predict the objective function value of unobserved points. Finally, a sampling function is selected based on the observation results to determine the next sampling point. This process is repeated several times to search for the optimal solution. The advantage of Bayesian search is that it can reuse historical information generated from previous sampling points, which makes Bayesian search stand out among many hyperparameter search algorithms.

[0132] Step 26: Selectively use dynamic programming or greedy algorithm based on the number of tables involved in the logical query plan. Based on the evaluated execution cost of logical operators and the execution cost of transformation operators, use the hybrid cost model to evaluate intermediate results step by step and prune to generate a hybrid physical query plan with the minimum execution cost.

[0133] Since the query plan space grows exponentially with the number of tables, in order to achieve a balance between efficiency and optimization quality, this disclosure dynamically selects different algorithms based on 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, a dynamic programming algorithm is used; when the number of tables involved in the logical query plan is greater than or equal to the preset threshold, a greedy algorithm is used.

[0134] According to this implementation, for simple queries involving a small number of tables, dynamic programming can guarantee finding the globally optimal solution, while for complex queries involving a large number of tables, a greedy algorithm can find an approximate optimal solution within an acceptable time. In some examples, the preset threshold can be set in 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 is efficient in handling join operations, the preset threshold can be appropriately increased; if hardware resources are limited (such as limited memory), the preset threshold can be appropriately decreased.

[0135] In some implementations, dynamic programming algorithms are used to generate hybrid physical query plans with minimum execution cost, including:

[0136] Evaluate and record the minimum execution cost for each table in row storage mode and column storage mode respectively, as an intermediate result for a single table in the two storage modes;

[0137] For each table subset whose minimum execution cost has been calculated and stored, two storage modes are enumerated, the next table to be joined, the join order, and the combination of storage modes after the join are enumerated, and the execution cost of each combination is evaluated. The minimum execution cost of the new table subset obtained after joining the next table in each of the two storage modes is recorded as an intermediate result to incrementally build a larger table set. If the next join includes a transformation operator, the execution cost of the corresponding transformation operator is introduced when calculating the execution cost of the corresponding combination.

[0138] Continue until a join containing all tables with the minimum execution cost is obtained, and then use that join as the hybrid physical query plan.

[0139] Dynamic programming algorithms construct the optimal solution from the bottom up.

[0140] For complex queries involving a large number of tables, dynamic programming algorithms have excessively high computational complexity. In such cases, greedy algorithms can be used to find an approximate optimal solution within an acceptable timeframe. In some implementations, greedy algorithms are used to generate hybrid physical query plans with minimal execution cost, including:

[0141] Iterate through and evaluate the execution cost of all two tables under all possible join methods and storage modes, and select the two tables with the lowest execution cost as the initial join;

[0142] In the remaining tables, the execution cost of adding the current connection to each table under different storage modes is evaluated traversally, and the connection with the lowest execution cost is selected as the next connection. If the next connection includes a transformation operator, the execution cost of the corresponding transformation operator is introduced when calculating the execution cost of the connection.

[0143] The process continues until a join of all tables with the minimum execution cost is obtained, which serves as the hybrid physical query plan.

[0144] According to this implementation method, the optimal connection is generated by a greedy algorithm, which has a significantly higher computational efficiency than the dynamic programming algorithm, and can usually obtain near-optimal results in practice.

[0145] This step generates an optimal query plan based on the intermediate results of the hybrid cost model evaluation. The generated plan may include both row-based and column-based storage operations. By introducing R2C or C2R transformation operators at appropriate locations, the transformation costs and performance gains are balanced to fully leverage the advantages of both storage models, significantly improving the query performance of the HTAP database. Furthermore, 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 results.

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

[0148] As described above Figure 1 As shown, the hybrid execution environment in this embodiment may include major components such as a hybrid execution model, R2C operators, C2R operators, row operators, and column operators. These components work together, interacting with the storage layer through buffers to achieve efficient hybrid execution.

[0149] In addition to the query optimizations described above, the Hybrid Transactional and Analytical Processing (HTAP) database query optimization method proposed in this embodiment also introduces a reinforcement learning-based in-memory column selection mechanism. This mechanism selects the optimal subset of columns to load into the in-memory column store under memory budget constraints to optimize subsequent query performance. It is particularly suitable for situations 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 HTAP databases, especially in systems employing a hybrid architecture of primary row-store and in-memory column-store (IMCS), limited memory resources prevent all columns from being loaded into the in-memory column-store. Therefore, selecting which columns to load into memory becomes a critical 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 combinatorial explosion problem.

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

[0153] 3. Dynamic workload: Query patterns may change over time, requiring adaptive adjustments to column selection strategies.

[0154] 4. Assessment complexity: The impact of different column combinations on query performance is complex to assess and difficult to calculate directly.

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

[0156] Based on the query history and using transformation operators, the columns with high access frequency are selected from the table as candidate columns;

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

[0158] In the selection phase, the exploration and utilization are balanced by using the UCT value. Starting from the root node, the child node with the largest UCT value is selected until a node that has not been fully expanded is reached.

[0159] During the expansion phase, for nodes that have not been fully expanded, a candidate column that has not yet been tried is selected, added to the current column subset to form a new search state, and a corresponding child node is created.

[0160] During the simulation phase, starting from the newly created node, a fast simulation strategy is used to randomly select candidate columns to add to the column subset until the memory budget limit is met or there are no more candidate columns to choose from. The reward value of the final column subset is then evaluated.

[0161] During the backtracking phase, the reward values ​​obtained in the simulation phase are propagated back to update the statistical information of all nodes on the path;

[0162] Repeat the above four stages until the search termination condition is met;

[0163] After the search ends, select the column subset with the highest reward value and load it into the in-memory column store to optimize the performance of subsequent queries. The total size of the column subset shall not exceed the preset memory budget.

[0164] Specifically, in one example, the MCTS algorithm search process is implemented as follows:

[0165] 1. Selection Phase: This phase utilizes the UCT (Upper Confidence Bound applied to Trees) value balancing method for exploration and utilization. Starting from the current node, the algorithm searches downwards from the root node, continuously looking for the child node with the largest UCT value, while simultaneously checking if the current node has any unexpanded child nodes. If so, this phase terminates; otherwise, it continues downwards to select the child node with the largest UCT value. The formula for calculating UCT is as follows:

[0166] ,

[0167] in Represents a node Average returns Represents a node Total number of visits, It is a hyperparameter used to adjust how to balance the relationship between exploration and exploitation during the search process.

[0168] 2. Expansion Phase: After finding a node that has not yet been expanded, the algorithm searches for possible actions of the current state. By selecting each possible action, it expands the successor of the current node, obtaining states representing a new set of candidate columns. For a new action, the algorithm takes the union of the current set of candidate columns and the corresponding action set 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 performed using the Rollout algorithm. The Rollout algorithm randomly executes actions until no candidate column set can be added to any query template. This node is then marked as the termination node. After the Rollout process terminates, the algorithm will call the learning model to evaluate the candidate column set and obtain the corresponding reward value, which is used to update the reward value in the backtracking phase.

[0170] 4. Backtracking Phase: Starting from the node where the reward value is obtained from the simulation, the Q value and N value of each node, as well as related information, are updated from bottom to top based on the reward value information obtained from the node, which is beneficial for further search.

[0171] In some implementations, 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, and B represents the payoff function negatively correlated with the query cost, B(Q i , CS) represents the i-th query Q i The benefit when using the CS column set, B(Q) i (, D') represents the i-th query Q i Benefits when using all columns.

[0174] The reward function in this implementation considers the combined effect of all queries, representing the overall performance ratio of selecting a specific subset of columns relative to using all columns. A larger reward value indicates that the performance of that subset of columns is closer 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 combining the advantages of row-based and column-based storage. According to this embodiment, the SQL query is first parsed to generate a query plan tree containing multiple logical operators. Then, a hybrid cost model with optimized hyperparameters using Bayesian search is used to accurately evaluate the execution cost of each logical operator under different storage modes. Next, dynamic programming or a greedy algorithm is intelligently selected based on query complexity to generate an optimal hybrid physical query plan including transformation operators. Finally, the plan is executed to obtain the query results. Furthermore, this method also employs a Monte Carlo tree search algorithm to asynchronously select the optimal column subset and load it into memory, further optimizing subsequent queries. Through this innovative hybrid execution framework, the system fully utilizes the indexing advantages and cache friendliness of row-based storage while leveraging the vectorized processing capabilities of column-based storage. It significantly accelerates analytical queries while maintaining high transaction throughput, making it suitable for application scenarios such as finance and e-commerce that require simultaneous transaction and analytical processing.

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

[0177] Step 1 (Query Parsing): When an SQL statement enters the database, the SQL parser first parses the SQL and obtains the logical query plan, generating the necessary operators. Figure 3 The top left corner shows an example of an input query used to retrieve data from the Review and Product tables based on the conditions P.ID<100 and PR.Rating<5.

[0178] Step 2 (Analyzing the Cost of Each Operator): The parsed logical operators are input into the hybrid cost model. The hybrid cost model can effectively identify and evaluate various operations, including row scans, column scans, hash joins, and the newly added transformation operators R2C and C2R. The identification of these operations lays the foundation for subsequent cost quantization and plan generation.

[0179] Step 3 (Cost Evaluation for Each Candidate Plan): Supported by the hybrid cost model, the system evaluates the cost of each candidate query plan and selects the optimal plan based on the principle of cost minimization. This process fully utilizes the computational 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): Due to the high time complexity of generating the optimal hybrid physical query plan, this disclosure proposes a heuristic algorithm combining dynamic programming (DP) and a greedy strategy to handle different scenarios with few and many connections, respectively. The DP algorithm is suitable for small-scale connections, while the greedy strategy provides a fast solution for large-scale connections.Figure 3 The right side shows the optimized hybrid plan, which includes operations such as projection, joining, and filtering, as well as R2C and C2R transformation operators inserted in appropriate places.

[0181] Step 5 (Query plan optimization using R2C operators): In the example workflow, the Product table performs an index scan on the ID column in row-based storage, while applying the predicate ID<100 to complete the initial filtering operation. Next, the filtered results are converted to column-based storage format using R2C operators, and two target columns are extracted: ID and Name.

[0182] Step 6 (Query Optimization using the C2R Operator): This operator is responsible for converting columnar storage back to row-based storage. This example designs two execution modes for narrow and wide tables respectively, and dynamically determines the applicable execution method based on the database's cost model. Optimized Operators can significantly improve the flexibility and overall performance of plan execution.

[0183] After data transformation, the hybrid plan fully leverages the characteristics of materialized columns to perform efficient vectorized join operations. This process significantly improves the computational efficiency of joins, providing strong support for query performance optimization.

[0184] Steps 7-9 (Column Selection Optimization): Figure 3 It also demonstrates the process of extracting historical data from query logs, selecting columns based on memory budget, and optimizing column selection using MCTS. All these operations are performed asynchronously within the storage layer to maximize efficiency.

[0185] This hybrid execution scheme allows the system to leverage both the indexing advantages of row storage and the vectorized processing capabilities of column storage, resulting in a significant improvement in query performance. It is particularly suitable for complex query processing in HTAP environments.

[0186] Figure 4 This illustration demonstrates a specific process for generating a hybrid physical query plan using a dynamic programming algorithm according to an exemplary embodiment of this disclosure. The red arrows mark the globally optimal path, which corresponds to a selection-projection-join (SPJ) query containing three tables. , and The system uses a chained connection. Row and column storage are labeled separately: 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 storage based on the scan cost. For example, in row-based and column-based storage... The preferred scanning methods are index scan and sequential scan (SeqScan). In the second step, the system uses a dynamic programming (DP) algorithm to evaluate all candidate hybrid plans, that is, enumerate each combination and evaluate its execution cost, such as index joins in row storage. Hash joins in column storage Sort-merge join in row storage In the third step, the optimal hybrid physical query plan is output by comparing the costs of each candidate plan: .

[0188] Figure 5 The workflow of a column selection mechanism based on Monte Carlo Tree Search (MCTS) according to an exemplary embodiment of this disclosure is shown in detail, and is divided into two main parts.

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

[0190] 1. SQL to Column Conversion (SQL2Cols): Extracts various SQL operations (Select, Filter, Join, etc.) from query logs and identifies 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 storage mode to column storage mode, and mark special cases such as "No R2C";

[0192] 3. Use C2R pruning: Analyze the applicability of C2R operations to different column sets, further filter candidate columns based on the transformation characteristics from column storage to row storage, and identify column combinations that need to use 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 second half describes the MCTS column selection algorithm, which mainly includes:

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

[0196] 2. MCTS Four-Stage Cyclic Search:

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

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

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

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

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

[0202] Figure 5 It clearly demonstrates how the entire column selection process involves analyzing query features, utilizing the characteristics of transformation operators to filter candidate columns, and then using the MCTS algorithm to efficiently find the optimal subset of columns in a huge search space. Figure 5 The search tree nodes represent different column subset states, the blue paths show the paths explored during the search process, and the terminal nodes mark the end conditions of the search.

[0203] Figure 6 An electronic device provided in at least one embodiment of the present disclosure includes a memory and a processor. The memory is used to store computer instructions that can be executed on the processor, and the processor is used to implement the query optimization method for the HTAP database as described in any embodiment or implementation of the present disclosure when executing the computer instructions.

[0204] At least one embodiment of this disclosure also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the query optimization method for the HTAP database described in any embodiment or implementation of this disclosure.

[0205] Those skilled in the art will understand that one or more embodiments of this specification can be provided as a method, system, or computer program product. Therefore, one or more embodiments of this specification may take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, one or more embodiments of this specification may 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.) containing computer-usable program code.

[0206] The various embodiments in this specification are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the data processing device embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0207] The foregoing has described specific embodiments of this specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps described in the claims may be performed in a different order than that shown in the embodiments and may still achieve the desired results. Furthermore, the processes depicted in the drawings do not necessarily require the specific or sequential order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0208] The embodiments of the subject matter and functional operation described in this specification can be implemented in the following ways: digital electronic circuits, tangibly embodied computer software or firmware, computer hardware including the structures disclosed in this specification and their structural equivalents, or combinations thereof. 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 for execution by a data processing apparatus or for controlling the operation of a data processing apparatus. Alternatively or additionally, the program instructions may be encoded on artificially generated propagation signals, such as machine-generated electrical, optical, or electromagnetic signals, which are generated to encode information and transmit it to a suitable receiving device for execution by the data processing apparatus. The computer storage medium may be a machine-readable storage device, a machine-readable storage substrate, a random or serial access memory device, or combinations thereof.

[0209] The processing and logic flow described in this specification can be executed by one or more programmable computers that execute one or more computer programs to perform corresponding functions by operating on input data and generating output. The processing and logic flow can also be executed by dedicated logic circuitry—such as FPGAs (Field-Programmable Gate Arrays) or ASICs (Application-Specific Integrated Circuits), and the device can also be implemented as dedicated logic circuitry.

[0210] While this specification contains numerous specific implementation details, these should not be construed as limiting the scope of any invention or the scope of the claims, but rather are primarily intended to describe features of specific embodiments of a particular invention. Certain features described in the various embodiments herein may also be implemented in combination in a single embodiment. Conversely, various features described in a single embodiment may also be implemented separately in various embodiments or in any suitable sub-combination. Furthermore, while features may function in certain combinations as described above and even initially claimed in this way, one or more features from a claimed combination may be removed from that combination in some cases, and a claimed combination may refer to a sub-combination or a variation thereof.

[0211] Similarly, although the operations are depicted in a specific order in the accompanying drawings, this should not be construed as requiring these operations to be performed in the specific order shown or sequentially, or requiring all illustrated operations to be performed to achieve the desired result. In some cases, multitasking and parallel processing may be advantageous. Furthermore, the separation of 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 actions recited in the claims may be performed in a different order and still achieve the desired result. Furthermore, the processes depicted in the drawings are not necessarily shown in a specific order or sequence to achieve the desired result. In some implementations, multitasking and parallel processing may be advantageous.

[0213] The above description is merely a preferred embodiment of one or more embodiments of this specification and is not intended to limit the scope of one or more embodiments of this specification. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of one or more embodiments of this specification should be included within the protection scope of one or more embodiments of this specification.

Claims

1. A method for optimizing HTAP database queries, characterized in that, include: Receive and parse SQL query requests to generate a logical query plan, which includes multiple logical operators; The execution cost of each logical operator in the logical query plan is evaluated using a hybrid cost model in both row-based and column-based storage modes. The hybrid cost model includes sub-models for evaluating the execution cost of logical operators in both row-based and column-based storage modes, and a sub-model for evaluating the execution cost of transformation operators. The transformation operators are used to transform data between row-based and column-based storage modes. The hyperparameters of the hybrid cost model are determined by Bayesian search to uniformly quantify the execution cost of logical operators and transformation operators in different storage modes. Based on the number of tables involved in the logical query plan, a dynamic programming algorithm or a greedy algorithm is selectively adopted. Based on the execution cost of the evaluated logical operators and the execution cost of the transformation operators are introduced, the intermediate results are evaluated step by step using the hybrid cost model and pruning is performed to generate a hybrid physical query plan with the minimum execution cost. Execute the hybrid physical query plan to obtain the query results.

2. The query optimization method according to claim 1, characterized in that, The method further includes optimizing the R2C operator in the conversion operator that converts row-stored data to column-stored data as follows: Utilize multithreading technology to process sequential scans in row storage in parallel; Multithreading technology is used to execute the format conversion task of multiple data blocks in parallel; Push filtering and projection operations closer to the data source to reduce the amount of data that needs to be formatted. Based on the query requirements analysis, only the columns required for the query are loaded into the column store.

3. The query optimization method according to claim 1, characterized in that, The C2R operator, which converts column-stored data into row-stored data, is implemented in two ways: a materialization-based implementation and a RowID-based implementation. The hybrid cost model dynamically evaluates the execution cost of the two implementations and selects the one with the lower execution cost.

4. The query optimization method according to claim 1, characterized in that, The hybrid cost model includes: A row storage scan cost sub-model is used to evaluate the execution cost of sequential scans, non-clustered index scans, clustered index scans, and filtering in row storage mode; The column storage scan cost sub-model is used to evaluate the execution cost of scanning and filtering in column storage mode; A row-store join cost sub-model is used to evaluate the execution cost of hash joins, index joins, and merge joins in row-store mode; A column-store join cost sub-model is used to evaluate the execution cost of hash joins and merge joins in column-store mode; The conversion cost model is used to evaluate 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.

5. The query optimization method according to claim 4, characterized in that, The execution cost (Cost) of the R2C operator is evaluated according to the following formula. R2C (T, S): Cost R2C (T, S) = (γ R2C + γ t ) * w(S) * N o , Where T represents the data source of the row storage mode to be transformed, S represents the set of materialized columns in T, and γ R2C γ represents the cost coefficient for creating materialized columns. t The data transfer cost coefficient is represented by w(S), which represents the width of the materialized column, and N is the value of N. o γ represents the number of rows in input T that have passed the filter and are to be converted to columnar storage mode. R2C and γ t These are the hyperparameters optimized using Bayesian search.

6. The query optimization method according to claim 4, characterized in that, The execution cost of the C2R operator is evaluated according to the following formula: C2R (T, S): Cost C2R (T, S) = min{(γ C2R + c t ) * w(S) * N o , (c C2R + c t ) * w(rid(T)) * N o +g i * w(S) * N o }, Where T represents the data source of the column storage schema to be transformed, S represents the set of columns to be transformed, the first part of min{} is the execution cost of the materialization-based implementation, the second part is the execution cost of the RowID-based implementation, and γ C2R γ represents the cost coefficient of the conversion. t γ represents the cost coefficient for data transmission. i This 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, and N o γ represents the number of rows in input T that have passed the filter and are to be converted to row storage mode. C2R γ t and γ i These are the hyperparameters optimized using Bayesian search.

7. The query optimization method according to claim 1, characterized in that, Depending on the number of tables involved in the logical query plan, a dynamic programming algorithm or a greedy algorithm may be selectively employed, including: When the number of tables involved in the logical query plan is less than a preset threshold, a dynamic programming algorithm is used; When the number of tables involved in the logical query plan is greater than or equal to a preset threshold, a greedy algorithm is used.

8. The query optimization method according to claim 1, characterized in that, A dynamic programming algorithm is used to generate a hybrid physical query plan with minimum execution cost, including: Evaluate and record the minimum execution cost for each table in row storage mode and column storage mode respectively, as an intermediate result for a single table in the two storage modes; For each table subset whose minimum execution cost has been calculated and stored, two storage modes are enumerated, the next table to be joined, the join order, and the combination of storage modes after the join are enumerated, and the execution cost of each combination is evaluated. The minimum execution cost of the new table subset obtained after joining the next table in each of the two storage modes is recorded as an intermediate result to incrementally build a larger table set. If the next join includes a transformation operator, the execution cost of the corresponding transformation operator is introduced when calculating the execution cost of the corresponding combination. Continue until a join containing all tables with the minimum execution cost is obtained, and then use that join as the hybrid physical query plan.

9. The query optimization method according to claim 1, characterized in that, A greedy algorithm is used to generate a hybrid physical query plan with the minimum execution cost, including: Iterate through and evaluate the execution cost of all two tables under all possible join methods and storage modes, and select the two tables with the lowest execution cost as the initial join; In the remaining tables, the execution cost of adding the current connection to each table under different storage modes is evaluated traversally, and the connection with the lowest execution cost is selected as the next connection. If the next connection includes a transformation operator, the execution cost of the corresponding transformation operator is introduced when calculating the execution cost of the connection. The process continues until a join of all tables with the minimum execution cost is obtained, which serves as the hybrid physical query plan.

10. The query optimization method according to claim 1, characterized in that, The method further includes: Based on the query history and using transformation operators, the columns with high access frequency are selected from the table as candidate columns; The Monte Carlo Tree Search (MCTS) algorithm is used, defining the currently selected subset of columns as the search state. Each search tree node represents a specific subset of columns, and the tree search is performed iteratively through the following four stages: In the selection phase, the exploration and utilization are balanced by using the UCT value. Starting from the root node, the child node with the largest UCT value is selected until a node that has not been fully expanded is reached. During the expansion phase, for nodes that have not been fully expanded, a candidate column that has not yet been tried is selected, added to the current column subset to form a new search state, and a corresponding child node is created. During the simulation phase, starting from the newly created node, a fast simulation strategy is used to randomly select candidate columns to add to the column subset until the memory budget limit is met or there are no more candidate columns to choose from. The reward value of the final column subset is then evaluated. During the backtracking phase, the reward values ​​obtained in the simulation phase are propagated back to update the statistical information of all nodes on the path; Repeat the above four stages until the search termination condition is met; After the search ends, select the column subset with the highest reward value and load it into the in-memory column store to optimize the performance of subsequent queries. The total size of the column subset shall not exceed the preset memory budget.

11. The query optimization method according to claim 10, characterized in that, The reward function of the MCTS algorithm is: ， 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, and B represents the payoff function negatively correlated with the query cost, B(Q i , CS) represents the i-th query Q i Benefits when using the CS column set, B(Q) i (, D') represents the i-th query Q i Benefits when using all columns.

12. An electronic device, characterized in that, The device includes a memory and a processor, the memory being used to store computer instructions executable on the processor, and the processor being used to implement the method of 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 a processor, it implements the method described in any one of claims 1 to 11.

Images