A query rewriting method and system for a database system

Abstract

This invention discloses a query rewriting method for database systems. First, the original query is converted into an extended relational algebra expression using Calcite's query parsing and validation tools. The advantage of using extended relational algebra is that it provides an abstract and general expression to represent the query statement, reducing the language complexity during query rewriting. Then, a language and rewriting rules for the extended relational algebra expression are established, and the extended relational algebra expression is converted into an E-Graph, thereby using an equivalent saturation graph to efficiently encode the rewriting results. A Monte Carlo tree search method is used to explore the optimal rewriting operation under the current E-Graph structure. At each step, an E-Graph copy of the current policy tree node is obtained, and the rewriting rule linked list of the current policy tree node is replayed on this copy. This invention solves the technical problem of existing classic query rewriting methods, which are prone to local optimizations due to the lack of exploration of the rewriting space.

Description

Technical Field

[0001] This invention belongs to the field of computer databases, and more specifically, relates to a query rewriting method and system for a database system. Background Technology

[0002] With the rapid growth of data volume, efficient query processing in large-scale data scenarios has become a key research topic in database performance optimization. Query rewriting, as an important means of database performance optimization, aims to transform the original query into an equivalent and more efficient form to reduce end-to-end latency in query processing. The query rewriting process involves parsing the original query to obtain a parse tree, and then traversing the parse tree, replacing some structures with equivalent and more efficient forms. In the query rewriting system, a rewriting rule table is used to define and store rewriteable structures and their corresponding rewriting actions. The parse tree is then searched and matched according to a predetermined order, and structures that satisfy the rewriting rules are modified.

[0003] Existing query rewriting methods mainly include classical query rewriting, learned query rewriting (LR) based on deep models, and query rewriting based on equivalence saturation graphs (Equality GraphGood, Egg). Classical query rewriting uses a predefined rewriting rule table to rewrite the original query parse tree. Specifically, it searches for rewriting rules at each node of the original query parse tree and modifies the structure in-situ if the rules are satisfied. LR uses a deep neural network to first encode the rewriting rules and data information, and then trains a cost prediction model based on graph attention to predict the benefits of the rewriting order. It then uses a Monte Carlo tree search method to obtain the rewriting order with the highest benefit, and finally uses this rewriting order to rewrite the original query. Egg transforms the original query into an equivalence saturation graph (E-Graph) for efficient encoding of the rewriting results. It continuously searches for nodes in the E-Graph and matches them with the rewriting rule table. When a match is successful, a node is added to the E-Graph until the node limit is reached or no new matching results are generated.

[0004] However, the aforementioned existing query rewriting methods all have some significant drawbacks:

[0005] First, the classic query rewriting method does not explore the rewrite space, which can easily lead to local optimizations.

[0006] Second, the LR method requires offline model training before rewriting, which means that when the data changes, it takes a long time to retrain the model. Therefore, it cannot be rewritten in real time under dynamic load.

[0007] Third, the Egg method adds nodes to the E-Graph in an unguided way, which results in a large number of inefficient rewrite results being encoded in the E-Graph when there are a limited number of nodes, which leads to poor overall optimization. Summary of the Invention

[0008] To address the aforementioned deficiencies or improvement needs of existing technologies, this invention provides a query rewriting method and system for database systems. Its purpose is to solve the technical problems of existing classic query rewriting methods, which are prone to local optimization due to the lack of exploration of the rewrite space; existing LR methods, which require offline model training before rewriting, resulting in lengthy retraining times when data changes, thus preventing real-time rewriting under dynamic loads; and existing Egg methods, which suffer from poor overall optimization due to encoding a large number of inefficient rewrite results in the E-Graph.

[0009] To achieve the above objectives, according to one aspect of the present invention, a query rewriting method for a database system is provided, comprising the following steps:

[0010] (1) Obtain the query rewrite request from the client;

[0011] (2) Convert the original query statement in the query rewrite request obtained in step (1) into an extended relational algebra expression;

[0012] (3) Establish the language and rewriting rules of the extended relational algebra expression obtained in step (2), and construct the extended relational algebra expression obtained in step (2) into an E-Graph.

[0013] (4) Use the Monte Carlo tree search method to rewrite the E-graph constructed in step (3) to obtain a new E-graph.

[0014] (5) Obtain the e-node sequence with the minimum total cost in the new E-Graph obtained in step (4), and take this sequence as the optimal node sequence;

[0015] (6) Convert the optimal node sequence obtained in step (5) into a query statement, input the query statement into the database system for querying, and obtain the final query result.

[0016] Preferably, the process of converting the original query statement in the query rewrite request into an extended relational algebra expression in step (2) is as follows: first, the original query statement is converted into a parse tree of type RelNode using Calcite's query parsing tool; then, the parse tree is converted into an extended relational algebra expression using Calcite's query validation tool.

[0017] Step (3) involves using the Egg application programming interface “define_language!” to establish the language of the extended relational algebra expression obtained in step (2), and using the Egg application programming interface “rewrite!” to establish a rewrite rule table consisting of multiple rewrite rules for the extended relational algebra expression.

[0018] Preferably, step (4) includes the following sub-steps:

[0019] (4-1) Initialize the E-Graph of the root node v0 in the policy tree (which is initially empty) as the E-Graph obtained in step (3), and initialize the rewrite rule chain L of the root node v0 to be empty;

[0020] (4-2) Set the counter cnt1 = 1;

[0021] (4-3) Determine whether the counter cnt1 is equal to the iteration threshold budget. If yes, proceed to step (4-12); otherwise, proceed to step (4-4).

[0022] (4-4) Determine whether the number of nodes included in the E-Graph of the current node in the current policy tree has reached the preset threshold node_num. If yes, proceed to step (4-12); otherwise, proceed to step (4-5).

[0023] (4-5) Select the node v to be expanded from the current strategy tree. e Then proceed to steps (4-6);

[0024] (4-6) Randomly select a rewrite rule r from the rewrite rule table constructed in step (2), and according to the extended node v selected in step (4-5) e Rewrite rule list L e Create a new linked list of rewrite rules L′ = L and rewrite rule r. e ∪r, create a new node v′, where the E-Graph of node v′ is related to v. e The E-Graph is the same, and node v′ is added to node v. e In the list of child nodes, initialize the number of visits to node v′ to 0, and then proceed to step (4-6);

[0025] (4-7) Create an E-Graph copy for node v′, and rewrite the rewrite rule list L′ of node v′ on this E-Graph copy to obtain the updated E-Graph copy;

[0026] (4-8) Randomly select a rewrite rule from the rewrite rule table created in step (2), add a node to the updated E-Graph replica according to the rewrite rule, repeat this process up to num times, and obtain the node utility value of node v′, and then proceed to step (4-9), where the value of num ranges from 1 to 100 times, preferably 10 times;

[0027] (4-9) Increment the number of times node v′ has been visited by 1;

[0028] (4-10) Calculate the modified reward value of the parent node v″ of node v′, and determine whether the parent node v″ is the root node v0. If so, proceed to step (4-11); otherwise, set node v′ as the parent node v″ and return to step (4-9).

[0029] Specifically, the modified reward value of the parent node v″ is calculated using the following formula:

[0030] adjustReward(v″)=max(adjustReward(v″),adjustReward(v′))

[0031] (4-11) Set the counter cnt1 = cnt1 + 1, and return to step (4-3);

[0032] (4-12) Select the child node v with the largest node utility value from all child nodes of the root node v0, and rewrite the child node v's rule linked list L. v Reload the E-Graph of that child node to obtain a new E-Graph.

[0033] Preferably, step (4-5) includes the following sub-steps:

[0034] (4-5-1) Determine whether the current node v in the current policy tree has not been expanded, or the current node v is the root node and has not been fully expanded, or the current node v is a non-root node and has not been fully expanded and the random probability is less than 0.5. If so, output the current node v and then the process ends; otherwise, proceed to step (4-5-2).

[0035] (4-5-2) Select the child node v′ with the largest node utility value from the current node v in the current policy tree and set it as the current node v, then return to step (4-5-1).

[0036] Preferably, for the root node v0 in the current strategy tree, its node utility value is fixed at 0;

[0037] For the non-root node v in the current policy tree non Its node utility value is calculated through the following steps:

[0038] (A1) Calculate the non-root node v using the following formula. non Reward value:

[0039]

[0040] Where E(v) non ) represents a non-root node v non The E-graph, where E(v0) represents the E-graph of the root node v0. Represents a non-root node v non The corresponding optimal e-node sequence This represents the optimal e-node sequence corresponding to the root node v0. Represents a non-root node v non The cost of the corresponding optimal e-node sequence, This represents the cost of the optimal e-node sequence corresponding to the root node v0.

[0041] (A2) Calculate the non-root node v using the following formula. non Penalty value:

[0042]

[0043] Among them, N(E(v) non )) represents a non-root node v non The number of e-nodes in the E-graph, N(E(v0)) represents the number of e-nodes in the E-graph of the root node v0.

[0044] (A3) Based on the non-root node v obtained in step (A1) non The reward value, and the non-root node v obtained in step (A2) non The penalty value is calculated for the non-root node v using the following formula. non Corrected reward value:

[0045] adjustReward(v non ) = reward(v non )-τ·penalty(v non )

[0046] Wherein, τ is a hyperparameter that balances the reward value and the penalty value. Preferably, the value of τ is between 0 and 1, and preferably 0.001.

[0047] (A4) Based on the non-root node v obtained in step (A3) non The corrected reward value is calculated using the following formula for non-root node v. non Node utility value:

[0048]

[0049] Among them, v Δ Is it a non-root node v? non One of the child nodes in the array, and Δ∈[1, non-root node v non The total number of child nodes in the [V], Visit(v non ) represents a non-root node v non The number of times it is visited, Visit(v Δ ) represents child node v Δ The number of times the data is accessed, C is a hyperparameter that balances exploration and utilization, and its value range is any real number, preferably .

[0050] Preferably, any node v in the strategy tree f The cost of the corresponding optimal e-node sequence It is obtained through the following sub-steps:

[0051] (a1) will node v f Rewrite rule list L f Reload the E-Graph on that node to obtain a new E-Graph, namely E(v f Then proceed to step (a2);

[0052] (a2) Obtain E(v) obtained in step (a1) f The cost of each e-node in the sequence is calculated, and the e-node sequence with the minimum total cost is obtained as the optimal e-node sequence. Then proceed to step (a3);

[0053] Specifically, this step involves first obtaining E(v) obtained in step (a1). fThe process involves calculating the cost of each e-node in the E-Graph, then starting from the root node of the new E-Graph, obtaining the costs of all e-nodes in the e-class to which the root node belongs, selecting the multiple e-nodes with the lowest costs, and randomly selecting one e-node from these multiple e-nodes as the optimal node; then, obtaining all child nodes of the selected optimal node in the new E-Graph, and for each selected child node, obtaining the costs of all e-nodes in the e-class to which the child node belongs, selecting the multiple child nodes with the lowest costs, and randomly selecting one child node from these multiple child nodes as the optimal child node, and so on, repeating this process until all child nodes in the E-Graph have been processed, finally obtaining the optimal node and all optimal child nodes to form the optimal e-node sequence.

[0054] (a3) Take any node v obtained in step (a2) f Optimal e-node sequence The sum of the costs of each e-node is used as the value for any node v. f The cost of the corresponding optimal e-node sequence.

[0055] Preferably, the process of calculating the cost of e-node x in step (a2) includes the following sub-steps:

[0056] (b1) Determine the type of e-node x: LogicTableScan, LogicFilter, LogicProject, Aggregate, HashAgg, SortAgg, Join, HashJoin, MergeJoin, or other types. If it is LogicTableScan, proceed to step (b2); if it is LogicFilter, proceed to step (b3); if it is LogicProject, proceed to step (b4); if it is Aggregate, proceed to step (b5); if it is HashAgg, proceed to step (b6); if it is SortAgg, proceed to step (b7); if it is Join, proceed to step (b8); if it is HashJoin, proceed to step (b9); if it is MergeJoin, proceed to step (b10); if it is other types, proceed to step (b11).

[0057] (b2) Calculate the cost of e-node x using the following formula:

[0058] cost(x) = rows(x) * cols(x)

[0059] Where rows(x) is the number of rows of data columns in the data table scanned by the LogicTableScan operation corresponding to this e-node;

[0060] For data table t1, the value is obtained by executing "SELECT count(*) FROM t1"; cols(x) represents the average width of the data columns scanned by the scan operation corresponding to the e-node, which is obtained by reading the avg_length field in the static statistics table corresponding to data table t1; the relationship between the data table and the pre-established static statistics table is that the fields in the static statistics table count the data distribution in each data column of the data table, and each data column corresponds to a static statistics table;

[0061] (b3) Calculate the cost of node x (e-node) using the following formula:

[0062] cost(x)=sel(x)*rows(x)+rows(x)*cols(x)

[0063] sel(x) is the selectivity of the predicate expression of the LogicFilter operation represented by this e-node;

[0064] (b4) Calculate the cost of node x (e-node) using the following formula:

[0065] cost(x) = sel(x) * rows(x)

[0066] (b5) Calculate the cost of node x (e-node) using the following formula:

[0067] cost(x)=sel(x)*rows(x)+rows(x)*cols(x)

[0068] (b6) Calculate the cost of node x (e-node) using the following formula:

[0069] cost(x) = 0.01 * log (sel(x)*rows(x)+cost(g)+1) +rows(x)*cols(x)

[0070] Where g is the e-node to which the aggregation key of the HashAgg operation corresponding to the e-node belongs, and cost(g) is the cost of the node.

[0071] (b7) Calculate the cost of node x (e-node) using the following formula:

[0072] cost(x)=sel(x)*rows(x)+cost(g)+rows(x)*cols(x)

[0073] (b8) Calculate the cost of node x (e-node) using the following formula:

[0074] cost(x)=sel(x)*rows(l)*rows(r)+cost(l)*cost(r)

[0075] Where l represents the e-node to which the left data table of the join operation belongs, rows(l) is the number of data rows in the data table corresponding to the node, cost(l) is the cost of the node, r represents the e-node to which the right data table of the join operation belongs, rows(r) is the number of data rows in the data table corresponding to the node, and cost(r) is the cost of the node.

[0076] (b9) Calculate the cost of node x (e-node) using the following formula:

[0077] cost(x) = 0.01 * log (rows(l) )*(rows(l)+rows(r))+cost(l)+cost(r)+rows(x)*cols(x)

[0078] (b10) Calculate the cost of node x (e-node) using the following formula:

[0079] cost(x)=rows(x)*cols(x)+cost(l)+cost(r)

[0080] (b11) Calculate the cost of e-node x using the following formula:

[0081] cost(x) = 1.

[0082] Preferably, the calculation process of the selectivity sel(x) of the predicate expression of the LogicFilter operation represented by the e-node includes the following sub-steps:

[0083] (c1) Initialize the selectivity sel to 1;

[0084] (c2) Determine whether the current predicate in the predicate expression contained in the e-node is "IS NULL". If so, modify the selectivity sel to the value of the null_frac field in the static statistics table, and then proceed to step (c9); otherwise, proceed to step (c3).

[0085] (c3) Determine whether the current predicate in the predicate expression contained in the e-node is ">" or ">=". If so, calculate the cumulative frequency cum_freq(Q) and modify sel to 1-cum-freq(Q), then proceed to step (c9); otherwise, proceed to step (c4).

[0086]

[0087] Where, bucket is the histogram bucket of the attribute column in the predicate expression contained in the e-node, index is the histogram bucket number of the attribute value Q in the predicate expression contained in the e-node, pos is the bucket offset value within the histogram bucket, bucket[index].max and bucket[index].min are the maximum and minimum values ​​of the data in the histogram bucket, respectively, and δ is the residual frequency, which is calculated according to the following formula:

[0088] δ=1-(null_frac+∑most_common_freqs)

[0089] Among them, null_frac and most_common_freqs are obtained from the corresponding fields with the same name in the static statistics table.

[0090] (c4) Determine whether the current predicate in the predicate expression contained in the e-node is "<" or "<=". If so, modify sel to cum_freq(Q) and then proceed to step (c9); otherwise, proceed to step (c5).

[0091] (c5) Determine whether the current predicate in the predicate expression contained in the e-node is “=". If so, determine whether the attribute value Q of the predicate expression is the value in the most_common_vals field of the static statistics table. If so, modify sel to the frequency corresponding to the value in the most_common_freq field of the static statistics table. Otherwise, modify sel to δ / n_distinct, where n_distinct is the value of the field with the same name in the static statistics table. Then proceed to step (c9). Otherwise, proceed to step (c6).

[0092] (c6) Determine if the current predicate in the predicate expression contained in the e-node is "IN". If so, first modify the sel value to 0, then, for the attribute value list Q1, Q2, ..., Q of the predicate expression... n Judge Q1, Q2, ..., Q in sequence. nIf the value is in the most_common_vals field of the static statistics table, then sel increments the frequency corresponding to that value in the most_common_freq field of the static statistics table. If not, sel increments δ / n_distinct, where n_distinct is the value of the field with the same name in the static statistics table. After the judgment is completed, proceed to step (c9); otherwise, proceed to step (c7).

[0093] (c7) Determine whether the current predicate in the predicate expression contained in the e-node is "LIKE". If so, modify sel to a fixed value to replace it. Preferably, the value is set to 0.05, and proceed to step (c9); otherwise, proceed to step (c8).

[0094] (c8) Determine whether the current predicate in the predicate expression contained in the e-node contains the "NOT" prefix. If so, modify sel to 1-sel. Then, proceed to step (c9);

[0095] (c9) Returns the selectivity sel.

[0096] Preferably, step (6) involves constructing a relational algebra tree of type RelNode based on the type of the optimal node sequence and using Calcite's RelBuilder tool, and then using the RelBuilder tool to convert the RelNode type relational algebra tree into a query statement.

[0097] According to another aspect of the present invention, a query rewriting system for a database system is provided, comprising:

[0098] The first module is used to obtain query rewrite requests from the client;

[0099] The second module is used to convert the original query statement in the query rewrite request obtained by the first module into an extended relational algebra expression.

[0100] The third module is used to establish the language and rewriting rules of the extended relational algebra expressions obtained in the second module, and to construct the extended relational algebra expressions obtained in the second module into an E-Graph.

[0101] The fourth module is used to rewrite the E-graph constructed in the third module using the Monte Carlo tree search method to obtain a new E-graph.

[0102] The fifth module is used to obtain the e-node sequence with the minimum total cost in the new E-Graph obtained from the fourth module, and this sequence is taken as the optimal node sequence;

[0103] The sixth module is used to convert the optimal node sequence obtained by the fifth module into a query statement, input the query statement into the database system for querying, and obtain the final query result.

[0104] In summary, compared with the prior art, the above-described technical solutions conceived by this invention can achieve the following beneficial effects:

[0105] (1) Since the present invention adopts steps (1) to (3), it converts the original query into an E-Graph representation and uses equivalent saturation to realize the query rewriting process, which can efficiently encode the huge rewriting results. Therefore, it can solve the technical problem that the existing classic query rewriting method is prone to local optimization due to the lack of exploration of the rewriting space.

[0106] (2) Since the present invention adopts steps (a1) to (a3), it uses the static statistical information of the database and the efficient function calculation method to evaluate the cost of nodes in E-Graph, and uses a greedy strategy to extract the optimal node sequence. It can efficiently perform node cost evaluation and node extraction, thereby reducing the time overhead of rewriting optimization. Therefore, it can solve the technical problem that the existing LR method cannot perform real-time rewriting under dynamic load.

[0107] (3) This invention employs step (4), which uses the Monte Carlo tree search method to select the current optimal rewriting rule each time an E-Graph is constructed, thereby constructing a new E-Graph. Compared to the egg method, it can encode more efficient rewriting results in the E-Graph within the same node limit, thus solving the technical problem that the existing egg method encodes a large number of inefficient rewriting results in the E-Graph, resulting in poor overall optimization performance. Attached Figure Description

[0108] Figure 1 This is a flowchart illustrating the query rewriting method of the database system of the present invention;

[0109] Figure 2 This is a schematic diagram illustrating how the present invention converts a query into an extended relational algebra expression;

[0110] Figure 3 This is a comparison of the rewrite optimization performance of this invention on the TPC-H dataset;

[0111] Figure 4 This is a comparison of the rewrite optimization performance of this invention on the JOB dataset. Detailed Implementation

[0112] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention. Furthermore, the technical features involved in the various embodiments of this invention described below can be combined with each other as long as they do not conflict with each other.

[0113] The technical terms used in this invention will be explained and described below:

[0114] Extended Relational Algebra Expressions: Extended relational algebra expressions are operator-driven tree-structured expressions, with operator types including mathematical operators, Boolean operators, relational algebra operators, and physical operators.

[0115] Equality Saturation: Equality saturation is a technique for solving staged ordering problems. Its core principle is to explore all possible rewrite branches while preserving the state before each rewrite step. When no more new branches are generated, saturation is considered achieved. Finally, the optimal branch is extracted from the preserved branches based on a cost metric.

[0116] Equality Saturation Graph (E-Graph): An E-Graph is a graph data structure used to represent and manipulate equivalence classes, efficiently encoding equivalence expressions. An E-Graph contains a set of equivalence classes (e-classes), and each e-class contains a set of equivalence nodes (e-nodes). Each e-node represents an operator or operand and is connected to an e-class by a directed edge. Initially, each e-class contains only one e-node. An E-Graph is considered saturated when no new nodes are added or when a predetermined number of termination steps are reached.

[0117] Calcite: Calcite is a foundational framework for optimizing query processing from heterogeneous data sources, comprising an SQL parser, an application programming interface for building relational algebra expressions, and a query planning engine.

[0118] RelNode: A node data structure in the Calcite framework that represents a relational algebra expression.

[0119] Egg: An open-source E-Graph building framework that allows you to define languages ​​and rewrite rules through application programming interfaces (APIs) and then rewrite them using E-Graph.

[0120] Monte Carlo Tree Search (MCTS) is a tree search algorithm based on the Monte Carlo method. Its core idea is to estimate the value of each action through random simulation and dynamically adjust the search strategy based on the action's reward, thereby continuously approaching the optimal solution. A single search process includes four steps: selection, expansion, simulation, and backpropagation.

[0121] The basic idea of ​​this invention is as follows: First, the original query is converted into an extended relational algebra expression using Calcite's query parsing and validation tools. The advantage of using extended relational algebra is that it provides an abstract and general expression to represent the query statement, reducing the language complexity handled during query rewriting. Then, a language and rewriting rules for the extended relational algebra expression are established, and the extended relational algebra expression is converted into an E-Graph, thereby using an equivalent saturation graph to efficiently encode the rewriting results. Next, a Monte Carlo tree search method is used to explore the optimal rewriting operation under the current E-Graph structure. Each step first obtains a copy of the E-Graph of the current policy tree node, replays the rewriting rule list of the current policy tree node on this copy, and then expands the policy tree node through a certain number of simulations. The cost of the optimal node sequence in the policy tree node's E-Graph is calculated, thereby obtaining the node utility value of the policy tree node. The child node with the maximum node utility value is selected from the policy tree of the root node, and the E-Graph of this child node is used as the E-Graph constructed by the Monte Carlo tree search method. Finally, the optimal node sequence is extracted from the new E-Graph and processed using Calcite. The RelBuilder tool converts the node sequence into a query and outputs the query to the database system for execution, obtaining the final query results.

[0122] like Figure 1 As shown, this invention provides a query rewriting method for a database system, comprising the following steps:

[0123] (1) Obtain the query rewrite request from the client;

[0124] (2) Convert the original query statement in the query rewrite request obtained in step (1) into an extended relational algebra expression;

[0125] Specifically, the process of converting the original query statement in the query rewrite request into an extended relational algebra expression in this step involves first using Calcite's query parsing tool to convert the original query statement into a parse tree of type RelNode, and then using Calcite's query validation tool to convert the parse tree into an extended relational algebra expression.

[0126] For example, as Figure 3 shown, the present invention converts the original query statement "SELECT SUM(l_extendedprice*l_discount) as revenue FROM lineitem WHERE l_shipdate >= date '1997-01-01' and l_shipdate < date '1997-01-01' + interval '1' year and l_discount between 0.03 - 0.01 and 0.03 + 0.01 and l_quantity < 25" into an extended relational algebra expression.

[0127] The advantage of this step is that it provides an abstract and general expression to represent the original query statement, reducing the language complexity processed during query rewriting.

[0128] (3) Establish the language and rewrite rules of the extended relational algebra expression obtained in step (2), and construct the extended relational algebra expression obtained in step (2) into an E-Graph.

[0129] Specifically, the present invention uses the application programming interface "define_language!" of Egg to establish the language of the extended relational algebra expression obtained in step (2), uses the application programming interface "rewrite!" of Egg to establish a rewrite rule table composed of multiple rewrite rules for this extended relational algebra expression (as shown in Table 1 below, the present invention uses 62 rewrite rules, which are classified into relational algebra (RA) rewrite, mathematical operation rewrite, boolean operation rewrite, and physical operation rewrite according to types. It should be noted that the number and rewrite rules shown in Table 1 are only for illustrative purposes. In specific implementation, the rewrite rule table can be defined according to the characteristics of the specific query load), and constructs an E-Graph using the Egg tool according to the established language and rewrite rule table of the extended relational algebra expression.

[0130] It should be understood that the process of using Egg in the present invention to define the grammar and rewrite rules of the extended relational algebra expression and construct the extended relational algebra expression into an E-Graph is only one implementation method for converting the extended relational algebra expression into an E-Graph. The present invention can use any program that implements equivalent saturation, such as Egg, egglog, etc.

[0131] Table 1 Rewrite Rule Table

[0132]

[0133]

[0134] The advantage of steps (1) to (3) above is that the original query is converted into an E-Graph, which can be used to efficiently rewrite the result encoding and explore more rewritten results.

[0135] (4) Use the Monte Carlo tree search method to rewrite the E-graph constructed in step (3) to obtain a new E-graph.

[0136] This step (4) includes the following sub-steps:

[0137] (4-1) Initialize the E-Graph of the root node v0 in the policy tree (which is initially empty) as the E-Graph obtained in step (3), and initialize the rewrite rule chain L of the root node v0 to be empty;

[0138] (4-2) Set the counter cnt1 = 1;

[0139] (4-3) Determine whether the counter cnt1 is equal to the iteration threshold budget. If yes, proceed to step (4-12); otherwise, proceed to step (4-4).

[0140] Preferably, the iteration threshold budget ranges from 1 to 30, and is preferably 10;

[0141] (4-4) Determine whether the number of nodes included in the E-Graph of the current node in the current policy tree has reached the preset threshold node_num. If yes, proceed to step (4-12); otherwise, proceed to step (4-5).

[0142] Preferably, the preset threshold node_num ranges from 0 to 5000, and is preferably 1000;

[0143] (4-5) Select the node v to be expanded from the current strategy tree. e Then proceed to steps (4-6);

[0144] Specifically, in this step, the node v that needs to be expanded is selected from the current policy tree. e This process includes the following sub-steps:

[0145] (4-5-1) Determine whether the current node v in the current policy tree has not been expanded, or the current node v is the root node and has not been fully expanded (i.e., the node cannot be expanded further), or the current node v is a non-root node and has not been fully expanded and the random probability is less than 0.5. If so, output the current node v and then the process ends; otherwise, proceed to step (4-5-2).

[0146] (4-5-2) Select the child node v′ with the largest node utility value from the current node v in the current policy tree and set it as the current node v, then return to step (4-5-1).

[0147] Specifically, for the root node v0 in the current policy tree, its node utility value is fixed at 0; for the non-root nodes v in the current policy tree... non Its node utility value is calculated through the following steps:

[0148] (A1) Calculate the non-root node v using the following formula. non Reward value:

[0149]

[0150] Where E(v) non ) represents a non-root node v non The E-graph, where E(v0) represents the E-graph of the root node v0. Represents a non-root node v non The corresponding optimal e-node sequence This represents the optimal e-node sequence corresponding to the root node v0. Represents a non-root node v non The cost of the corresponding optimal e-node sequence, This represents the cost of the optimal e-node sequence corresponding to the root node v0.

[0151] Furthermore, any node v in the policy tree f The cost c of the corresponding optimal e-node sequence It is obtained through the following sub-steps:

[0152] (a1) will node v f Rewrite rule list L f Reload the E-Graph on that node to obtain a new E-Graph, namely E(v f Then proceed to step (a2);

[0153] (a2) Obtain E(v) obtained in step (a1) f The cost of each e-node in the sequence is calculated, and the e-node sequence with the minimum total cost is obtained as the optimal e-node sequence. Then proceed to step (a3);

[0154] Specifically, this step involves first obtaining E(v) obtained in step (a1). fThe process involves calculating the cost of each e-node in the E-Graph, then starting from the root node of the new E-Graph, obtaining the costs of all e-nodes in the e-class to which the root node belongs, selecting the multiple e-nodes with the lowest costs, and randomly selecting one e-node from these multiple e-nodes as the optimal node; then, obtaining all child nodes of the selected optimal node in the new E-Graph, and for each selected child node, obtaining the costs of all e-nodes in the e-class to which the child node belongs, selecting the multiple child nodes with the lowest costs, and randomly selecting one child node from these multiple child nodes as the optimal child node, and so on, repeating this process until all child nodes in the E-Graph have been processed. The final optimal node and all optimal child nodes form the optimal e-node sequence.

[0155] The process of calculating the cost of e-node x in step (a2) includes the following sub-steps:

[0156] (b1) Determine the type of e-node x: LogicTableScan, LogicFilter, LogicProject, Aggregate, HashAgg, SortAgg, Join, HashJoin, MergeJoin, or other types. If it is LogicTableScan, proceed to step (b2); if it is LogicFilter, proceed to step (b3); if it is LogicProject, proceed to step (b4); if it is Aggregate, proceed to step (b5); if it is HashAgg, proceed to step (b6); if it is SortAgg, proceed to step (b7); if it is Join, proceed to step (b8); if it is HashJoin, proceed to step (b9); if it is MergeJoin, proceed to step (b10); if it is other types, proceed to step (b11).

[0157] Specifically, in this step (b1), the type of e-node x is determined by reading the node type field in the e-node.

[0158] (b2) Calculate the cost of e-node x using the following formula:

[0159] cost(x) = rows(x) * cols(x)

[0160] Here, rows(x) is the number of rows of data columns in the data table scanned by the LogicTableScan operation corresponding to the e-node; specifically, for data table t1, this value is obtained by executing "SELECT count(*) FROM t1"; cols(x) represents the average width of the data columns in the data table scanned by the scan operation corresponding to the e-node, which is obtained by reading the avg_length field in the static statistics information table (as shown in Table 1 below) corresponding to data table t1.

[0161] Specifically, the relationship between the data table and the pre-established static statistics table is as follows: the fields in the static statistics table summarize the data distribution in each data column of the data table, and each data column corresponds to a static statistics table. For example, for the data column col1 in data table t1, the field value corresponding to it in the static statistics table is obtained by executing "SELECT * FROM pg_stats WHERE tablename='t1' and attname='col1'".

[0162] Table 2 Static Statistical Information Table

[0163]

[0164] (b3) Calculate the cost of node x (e-node) using the following formula:

[0165] cost(x)=sel(x)*rows(x)+rows(x)*cols(x)

[0166] Where sel(x) is the selectivity of the predicate expression of the LogicFilter operation represented by the e-node, and its calculation process includes the following sub-steps:

[0167] (c1) Initialize the selectivity sel to 1;

[0168] (c2) Determine whether the current predicate in the predicate expression contained in the e-node is "ISNULL". If so, modify the selectivity sel to the value of the null_frac field in the static statistics table, and then proceed to step (c9); otherwise, proceed to step (c3).

[0169] (c3) Determine whether the current predicate in the predicate expression contained in the e-node is ">" or ">=". If so, calculate the cumulative frequency cum_freq(Q) and modify sel to 1-cum_freq(Q), then proceed to step (c9); otherwise, proceed to step (c4).

[0170]

[0171] Where, bucket is the histogram bucket of the attribute column in the predicate expression contained in the e-node, index is the histogram bucket number of the attribute value Q in the predicate expression contained in the e-node, pos is the bucket offset value within the histogram bucket, bucket[index].max and bucket[index].min are the maximum and minimum values ​​of the data in the histogram bucket, respectively, and δ is the residual frequency, which is calculated according to the following formula:

[0172] δ=1―(null_frac+∑most_common_freqs)

[0173] Among them, null_frac and most_common_freqs are obtained from the corresponding fields with the same name in the static statistics table.

[0174] (c4) Determine whether the current predicate in the predicate expression contained in the e-node is "<" or "<=". If so, modify sel to cum_freq(Q) and then proceed to step (c9); otherwise, proceed to step (c5).

[0175] (c5) Determine whether the current predicate in the predicate expression contained in the e-node is “=". If so, determine whether the attribute value Q of the predicate expression is the value in the most_common_vals field of the static statistics table. If so, modify sel to the frequency corresponding to the value in the most_common_freq field of the static statistics table. Otherwise, modify sel to δ / n_distinct, where n_distinct is the value of the field with the same name in the static statistics table. Then proceed to step (c9). Otherwise, proceed to step (c6).

[0176] (c6) Determine if the current predicate in the predicate expression contained in the e-node is "IN". If so, first modify the sel value to 0, and then, for the attribute value list Q1, Q2, ..., Q of the predicate expression... n Judge Q1, Q2, ..., Q in sequence. n If the value is in the most_common_vals field of the static statistics table, then sel increments the frequency corresponding to that value in the most_common_freq field of the static statistics table. If not, sel increments δ / n_distinct, where n_distinct is the value of the field with the same name in the static statistics table. After the judgment is completed, proceed to step (c9); otherwise, proceed to step (c7).

[0177] (c7) Determine whether the current predicate in the predicate expression contained in the e-node is "LIKE". If so, modify sel to a fixed value to replace it. Preferably, the value is set to 0.05, and proceed to step (c9); otherwise, proceed to step (c8).

[0178] (c8) Determine whether the current predicate in the predicate expression contained in the e-node contains the "NOT" prefix. If so, modify sel to 1-sel. Then, proceed to step (c9);

[0179] (c9) Returns the selectivity sel.

[0180] (b4) Calculate the cost of node x (e-node) using the following formula:

[0181] cost(x) = sel(x) * rows(x)

[0182] (b5) Calculate the cost of node x (e-node) using the following formula:

[0183] cost(x)=sel(x)*rows(x)+rows(x)*cols(x)

[0184] (b6) Calculate the cost of node x (e-node) using the following formula:

[0185] cost(x) = 0.01 * log (sel(x)*rows(x)+cost(g)+1) +rows(x)*cols(x)

[0186] Where g is the e-node to which the aggregation key of the HashAgg operation corresponding to the e-node belongs, and cost(g) is the cost of the node.

[0187] (b7) Calculate the cost of node x (e-node) using the following formula:

[0188] cost(x)=sel(x)*rows(x)+cost(g)+rows(x)*cols(x)

[0189] (b8) Calculate the cost of node x (e-node) using the following formula:

[0190] cost(x)=sel(x)*rows(l)*rows(r)+cost(l)*cost(r)

[0191] Where l represents the e-node to which the left data table of the join operation belongs, rows(l) is the number of data rows in the data table corresponding to the node, cost(l) is the cost of the node, r represents the e-node to which the right data table of the join operation belongs, rows(r) is the number of data rows in the data table corresponding to the node, and cost(r) is the cost of the node.

[0192] (b9) Calculate the cost of node x (e-node) using the following formula:

[0193] cost(x) = 0.01 * log (rows(l)) *(rows(l)+rows(r))+cost(l)+cost(r)+rows(x)*cols(x)

[0194] (b10) Calculate the cost of node x (e-node) using the following formula:

[0195] cost(x)=rows(x)*cols(x)+cost(l)+cost(r)

[0196] (b11) Calculate the cost of e-node x using the following formula:

[0197] cost(x) = 1

[0198] (a3) Take any node v obtained in step (a2) f Optimal e-node sequence The sum of the costs of each e-node is used as the value for any node v. f The cost of the corresponding optimal e-node sequence.

[0199] The advantage of the above sub-steps (a1) to (a3) ​​is that they use an efficient method for calculating node cost and node sequence cost, which enables real-time query rewriting with low query rewriting overhead.

[0200] (A2) Calculate the non-root node v using the following formula. non Penalty value:

[0201]

[0202] Among them, N(E(v) non )) represents a non-root node v non The number of e-nodes in the E-graph, N(E(v0)) represents the number of e-nodes in the E-graph of the root node v0.

[0203] (A3) Based on the non-root node v obtained in step (A1) non The reward value, and the non-root node v obtained in step (A2) non The penalty value is calculated for the non-root node v using the following formula. non Corrected reward value:

[0204] adjustReward(v non ) = reward(v non )-τ·penalty(v non )

[0205] Wherein, τ is a hyperparameter that balances the reward value and the penalty value. Preferably, the value of τ is between 0 and 1, and preferably 0.001.

[0206] (A4) Based on the non-root node v obtained in step (A3) non The corrected reward value is calculated using the following formula for non-root node v. non Node utility value:

[0207]

[0208] Among them, v Δ Is it a non-root node v? non One of the child nodes in the array, and Δ∈[1, non-root node v non The total number of child nodes in the [V], Visit(v non ) represents a non-root node v non The number of times it is visited, Visit(v Δ ) represents child node v Δ The number of times the data is accessed, C is a hyperparameter that balances exploration and utilization, and its value range is any real number, preferably .

[0209] (4-6) Randomly select a rewrite rule r from the rewrite rule table constructed in step (2), and according to the extended node v selected in step (4-5) e Rewrite rule list L e Create a new linked list of rewrite rules L′ = L and rewrite rule r. e ∪r, create a new node v′, where the E-Graph of node v′ is related to v. e The E-Graph is the same, and node v′ is added to node v. e In the list of child nodes, initialize the number of visits to node v′ to 0, and then proceed to step (4-6);

[0210] (4-7) Create an E-Graph copy for node v′, and rewrite the rewrite rule list L′ of node v′ on this E-Graph copy to obtain the updated E-Graph copy;

[0211] (4-8) Randomly select a rewrite rule from the rewrite rule table created in step (2), add a node to the updated E-Graph replica according to the rewrite rule, and repeat this process up to num times (where num is in the range of 1 to 100 times, preferably 10 times), and obtain the node utility value of node v′, and then proceed to step (4-9).

[0212] (4-9) Increment the number of times node v′ has been visited by 1;

[0213] (4-10) Calculate the modified reward value of the parent node v″ of node v′, and determine whether the parent node v″ is the root node v0. If so, proceed to step (4-11); otherwise, set node v′ as the parent node v″ and return to step (4-9).

[0214] Specifically, the modified reward value of the parent node v″ is calculated using the following formula:

[0215] adjustReward(v″)=max(adjustReward(v″),adjustReward(v′))

[0216] (4-11) Set the counter cnt1 = cnt1 + 1, and return to step (4-3);

[0217] (4-12) Select the child node v with the largest node utility value from all child nodes of the root node v0, and rewrite the child node v's rule linked list L. v Reload the E-Graph of that child node to obtain a new E-Graph.

[0218] The advantage of this step is that by guiding the construction process of the E-Graph using the Monte Carlo tree search method, inefficiencies encoded in the E-Graph can be reduced.

[0219] (5) Obtain the e-node sequence with the minimum total cost in the new E-Graph obtained in step (4), and take this sequence as the optimal node sequence;

[0220] Specifically, this step obtains the sequence of e-nodes with the minimum total cost in the new E-Graph through step (a2).

[0221] (6) Convert the optimal node sequence obtained in step (5) into a query statement, input the query statement into the database system for querying, and obtain the final query result.

[0222] Specifically, this step involves constructing a relational algebra tree of type RelNode based on the type of the optimal node sequence and using Calcite's RelBuilder tool. Then, the RelBuilder tool is used to convert the RelNode type relational algebra tree into a query statement.

[0223] Experimental results

[0224] The experimental environment for this invention is shown in Table 3 below:

[0225] Table 3 Experimental Environment Configuration Information

[0226] CPU Apple M1 Pro (8-core) Memory capacity 16 GB main memory capacity 512GB SSD operating system macOS Sonoma 14.1.1 kernel version Darwin-23.1.0-arm64 Database system PostgreSQL 12.18

[0227] To illustrate the performance of the rewrite optimization of this invention, overall performance comparison experiments were conducted on the TPC-H and JOB datasets. Figure 3 The comparison results are presented on the TPC-H dataset. Figure 4 The comparison results on the JOB dataset are presented. As shown in the figure, the proposed solution (referred to as EQS-Rewrite in the figure, which is short for the query rewrite method based on Equality Saturation and Monte Carlo Tree Search in this invention's database system) achieves the best optimization performance.

[0228] On the TPC-H dataset, compared to baseline PostgreSQL, the execution cost (median, P90, and P99) decreased by 24.7%, 33.1%, and 34.8%, respectively, while the query latency (median, P90, and P99) decreased by 40.4%, 46.1%, and 76.3%, respectively. Compared to egg-1k, the execution cost (median, P90, and P99) decreased by 41.4%, 54.1%, and 74.2%, respectively, while the query latency (median, P90, and P99) decreased by 37.6%, 4... Compared to egg-5k, the execution cost median, P90, and P99 decreased by 33.1%, 49.6%, and 71.1%, respectively, while the query latency median, P90, and P99 decreased by 34.4%, 48.2%, and 67.2%, respectively. Compared to LR, the execution cost median, P90, and P99 decreased by 24.7%, 33.1%, and 34.8%, respectively, while the query latency median, P90, and P99 decreased by 40.4%, 46.1%, and 76.3%, respectively.

[0229] In the JOB dataset, compared to baseline PostgreSQL, the execution cost (median, P90, and P99) decreased by 24.7%, 33.1%, and 34.8%, respectively, while the query latency (median, P90, and P99) decreased by 40.4%, 46.1%, and 76.3%, respectively. Compared to egg-1k, the execution cost (median, P90, and P99) decreased by 23.6%, 31.8%, and 33.2%, respectively, while the query latency (median, P90, and P99) decreased by 20.0%, 25%, and 25%, respectively. Compared to egg-5k, the execution cost median, P90, and P99 decreased by 19.2%, 29.8%, and 33.2%, respectively, while the query latency median, P90, and P99 decreased by 17.3%, 23.1%, and 68.1%, respectively. Compared to LR, the execution cost median, P90, and P99 decreased by 24.7%, 33.1%, and 34.8%, respectively, while the query latency median, P90, and P99 decreased by 40.4%, 46.1%, and 76.3%, respectively.

[0230] Those skilled in the art will readily understand that the above description is merely a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention should be included within the scope of protection of the present invention.

Claims

1. A query rewriting method of a database system, characterized by, Includes the following steps: (1) Obtain the query rewrite request from the client; (2) Convert the original query statement in the query rewrite request obtained in step (1) into an extended relational algebra expression; (3) Establish the language and rewriting rules of the extended relational algebra expression obtained in step (2), and construct the extended relational algebra expression obtained in step (2) into an E-Graph; (4) Rewrite the E-graph constructed in step (3) using the Monte Carlo tree search method to obtain a new E-graph; step (4) includes the following sub-steps: (4-1) Initialize the policy tree, which is initially an empty root node. The E-Graph is the E-Graph obtained in step (3), and is the root node. Initialize and rewrite the rule list Empty; (4-2) Set the counter cnt1=1; (4-3) Determine whether the counter cntl is equal to the iteration threshold If so, proceed to step (4-12), otherwise proceed to step (4-4); (4-4) Determine whether the number of nodes included in the E-Graph of the current node in the current policy tree has reached the preset threshold. If yes, proceed to step (4-12); otherwise, proceed to step (4-5). (4-5) selecting a node to be expanded from the current policy tree and then goes to step (4-6); (4-6) Randomly select a rewrite rule from the rewrite rule table constructed in step (2). Based on the expansion node selected in step (4-5) Rewrite rule list and rewrite rules Create a new rewrite rule linked list Create a new node , where the node E-Graph and The same as the E-Graph, and the nodes Add to node In the list of child nodes, initialize the node. The number of times it was accessed is 0, then proceed to step (4-6). (4-7) are nodes Create an E-Graph replica, and store the nodes. Rewrite rule list Reload the E-Graph copy to obtain an updated E-Graph copy; (4-8) Randomly select a rewrite rule from the rewrite rule table created in step (2), add nodes to the updated E-Graph replica according to the rewrite rule, and repeat this process until... Next, and obtain the node. The node utility value is then determined, and the process proceeds to step (4-9), where... The value range is from 1 to 100. (4-9) Increment the number of visits of the node by 1; (4-10) Computation nodes parent node The corrected reward value is determined, and the parent node is identified. Is it the root node? If yes, proceed to step (4-11); otherwise, remove the node. Set as parent node And return to steps (4-9): In particular, the modified reward value of the parent node is calculated using the following formula: ； (4-11) Set the counter cnt1 = cnt1 + 1, and return to step (4-3). (4-12) From the root node Select the child node with the highest node utility value from all child nodes. , child nodes Rewrite rule list Reload the E-Graph of this child node to obtain a new E-Graph; (5) Obtain the e-node sequence with the minimum total cost in the new E-Graph obtained in step (4), and take this sequence as the optimal node sequence; (6) Convert the optimal node sequence obtained in step (5) into a query statement, input the query statement into the database system for querying, and obtain the final query result.

2. The query rewriting method for a database system according to claim 1, characterized in that, In step (2), the process of converting the original query statement in the query rewrite request into an extended relational algebra expression is as follows: first, the original query statement is converted into a parse tree of type RelNode using Calcite's query parsing tool; then, the parse tree is converted into an extended relational algebra expression using Calcite's query validation tool. Step (3) is to use the Egg application interface "define_language!" to establish the language of the extended relational algebra expression obtained in step (2), and use the Egg application interface "rewrite!" to establish a rewrite rule table consisting of multiple rewrite rules for the extended relational algebra expression.

3. The query rewriting method of a database system according to claim 2, wherein, Steps (4-5) include the following sub-steps: (4-5-1) Determine the current node in the current policy tree Whether it has not been expanded, or the current node It is the root node and has not been fully expanded, or the current node. If a node is not the root node, has not been fully expanded, and has a random probability less than 0.5, output the current node. Then the process ends; otherwise, proceed to step (4-5-2). (4-5-2) From the current node in the current policy tree Select the child node with the highest node utility value. and use it as the current node. Then return to step (4-5-1).

4. The query rewriting method for a database system according to claim 3, characterized in that, For the root node in the current policy tree whose node utility value is fixed at 0; For a non-root node in the current policy tree whose node utility value is computed by the following steps: (A1) The reward value of a non-root node is calculated by the following formula: reward = reward + (1 - reward) * (1 - p) ； in Represents a non-root node E-graph, Represents the root node E-graph, Represents a non-root node The corresponding optimal e-node sequence Represents the root node The corresponding optimal e-node sequence Represents a non-root node The cost of the corresponding optimal e-node sequence, Represents the root node The cost of the corresponding optimal e-node sequence; (A2) Calculate the non-root nodes using the following formula. Penalty value: ； in, Represents a non-root node The number of e-nodes in an E-graph Represents the root node The number of e-nodes in the E-graph; (A3) Non-root nodes obtained from step (A1) The reward value, and the non-root node obtained in step (A2). The penalty value is calculated for the non-root node using the following formula. Corrected reward value: ； wherein, is a hyperparameter balancing the reward value and the penalty value, has a value in the range between 0 and 1; (A4) a non-root node obtained according to step (A3) the modified reward value, and the node utility value of the non-root node is calculated by the following formula: ； in, Non-root node One of the child nodes in, and has ∈[1, non-root node The total number of child nodes in the [database]. Represents a non-root node The number of times it was accessed. Represents child nodes The number of times it was accessed. It is a hyperparameter that balances exploration and utilization, and its value range is any real number.

5. The query rewriting method for a database system according to claim 4, characterized in that, Any node in the strategy tree The cost of the corresponding optimal e-node sequence It is obtained through the following sub-steps: (a1) will node Rewrite rule list Reload onto the node's E-Graph to obtain a new E-Graph, i.e. Then proceed to step (a2); (a2) obtaining the cost of each e-node node from the e-node nodes obtained in step (al) the cost of each e-node node, and obtaining the total cost and the minimum e-node node sequence as the optimal e-node node sequence according to the cost of each e-node node obtained and then entering step (a3); Specifically, this step involves first obtaining the result from step (a1). The process involves calculating the cost of each e-node in the new E-Graph. Then, starting from the root node of the new E-Graph, the costs of all e-nodes in the e-class to which the root node belongs are obtained. Multiple e-nodes with the lowest costs are selected, and one e-node is randomly selected from these multiple e-nodes as the optimal node. Next, all child nodes of the selected optimal node in the new E-Graph are obtained. For each selected child node, the costs of all e-nodes in the e-class to which the child node belongs are obtained. Multiple child nodes with the lowest costs are selected, and one child node is randomly selected from these multiple child nodes as the optimal child node. This process is repeated until all child nodes in the E-Graph have been processed. The final optimal node and all optimal child nodes form the optimal e-node sequence. (a3) Take any node obtained in step (a2) Optimal e-node sequence The cost of each e-node is accumulated and used as an arbitrary node. The cost of the corresponding optimal e-node sequence.

6. The query rewriting method for a database system according to claim 5, characterized in that, Calculating the cost of the e-node in step (a2) This process, including the following sub-steps: (b1) Determine the e-node node Is the type LogicTableScan, LogicFilter, LogicProject, Aggregate, HashAgg, SortAgg, Join, HashJoin, MergeJoin, or another type? If it is LogicTableScan, proceed to step (b2); if it is LogicFilter, proceed to step (b3); if it is LogicProject, proceed to step (b4); if it is Aggregate, proceed to step (b5); if it is HashAgg, proceed to step (b6); if it is SortAgg, proceed to step (b7); if it is Join, proceed to step (b8); if it is HashJoin, proceed to step (b9); if it is MergeJoin, proceed to step (b10); if it is another type, proceed to step (b11). (b2) Calculate the cost of the e-node node by the following formula: e-node = e-node + e-node + e-node + e-node ； wherein, is the number of rows of the data column in the data table scanned by the LogicTableScan operation corresponding to the e-node node; For table t1, the value is obtained by executing "SELECT count(*) FROM t1"; This indicates the average width of the data columns in the data table scanned by the scan operation corresponding to the e-node. It is obtained by reading the avg_length field in the static statistics table corresponding to data table t1. The relationship between the data table and the pre-established static statistics table is that the fields in the static statistics table summarize the data distribution in each data column of the data table, and each data column corresponds to a static statistics table. (b3) Calculate the cost of node x (e-node) using the following formula: ； is the selectivity of the predicate expression of the LogicFilter operation represented by the e-node node; (b4) Calculate the cost of node x (e-node) using the following formula: ， (b5) Calculate the cost of node x (e-node) using the following formula: ， (b6) Calculate the cost of node x (e-node) using the following formula: ， in, It is the e-node to which the aggregation key of the HashAgg operation corresponding to this e-node belongs. This is the cost of that node; (b7) Calculate the cost of node x (e-node) using the following formula: ； (b8) Calculate the cost of node x (e-node) using the following formula: ； in, This indicates the e-node to which the left table of the join operation belongs. It is the number of data rows in the data table corresponding to this node. It is the cost of that node. This indicates the e-node to which the right data table of the join operation corresponding to this e-node belongs. It is the number of data rows in the data table corresponding to this node. This is the cost of that node; (b9) Calculate the cost of node x (e-node) using the following formula: ； (b10) Calculate the cost of node x (e-node) using the following formula: ；； (b11) Calculate the cost of e-node x using the following formula: 。 7. The query rewriting method for a database system according to claim 6, characterized in that, The selectivity of the predicate expression for the LogicFilter operation represented by the e-node. The calculation process includes the following sub-steps: (c1) Initialize the selectivity sel to 1; (c2) Determine whether the current predicate in the predicate expression contained in the e-node is "IS NULL". If so, modify the selectivity sel to the value of the null_frac field in the static statistics table, and then proceed to step (c9); otherwise, proceed to step (c3). (c3) Determine whether the current predicate in the predicate expression contained in the e-node is ">" or ">=". If so, calculate the cumulative frequency. And change sel to If yes, proceed to step (c9); otherwise, proceed to step (c4). ； Among them, b This is a histogram bucket for the attribute columns in the predicate expression contained in the e-node. The attribute values ​​in the predicate expression contained in this e-node. The bucket number in the histogram. This represents the offset value within the histogram bucket. and These represent the maximum and minimum values ​​of the data in the histogram buckets, respectively. The remaining frequency is calculated using the following formula: ； in, and Retrieved from the corresponding field with the same name in the static statistics table; (c4) Determine whether the current predicate in the predicate expression contained in the e-node is "<" or "<=". If so, modify sel to If yes, proceed to step (c9); otherwise, proceed to step (c5). (c5) Determine whether the current predicate in the predicate expression contained in the e-node is "=". If so, determine the attribute value of the predicate expression. If the value is in the `most_common_vals` field of the static statistics table, modify `sel` to the frequency corresponding to that value in the `most_common_freq` field of the static statistics table; otherwise, modify `sel` to... ,in If the value is the same as the field in the static statistics table, proceed to step (c9); otherwise, proceed to step (c6). (c6) Determine if the current predicate in the predicate expression contained in the e-node is "IN". If so, first modify the sel value to 0, and then, for the list of attribute values ​​of the predicate expression... , … Judge in sequence , … If the value is in the most_common_vals field of the static statistics table, then sel increments the frequency corresponding to that value in the most_common_freq field of the static statistics table; otherwise, sel increments the frequency of the value. ,in If the value is the same as the field in the static statistics table, proceed to step (c9) after the judgment is completed; otherwise, proceed to step (c7). (c7) Determine whether the current predicate in the predicate expression contained in the e-node is "LIKE". If so, change sel to a fixed value to replace it; proceed to step (c9); otherwise, proceed to step (c8). (c8) Determine whether the current predicate in the predicate expression contained in the e-node contains the "NOT" prefix. If so, modify sel to 1-sel; then, proceed to step (c9). (c9) Returns the selectivity sel.

8. The query rewriting system for a database system according to claim 7, characterized in that, Step (6) involves constructing a relational algebra tree of type RelNode based on the type of the optimal node sequence and using Calcite's RelBuilder tool. Then, the RelNode relational algebra tree is converted into a query statement using the RelBuilder tool.

9. A query rewriting system for a database system, implemented based on the query rewriting method for the database system described in claim 1, characterized in that, The query rewriting system includes: The first module is used to obtain query rewrite requests from the client; The second module is used to convert the original query statement in the query rewrite request obtained by the first module into an extended relational algebra expression. The third module is used to establish the language and rewriting rules of the extended relational algebra expressions obtained in the second module, and to construct the extended relational algebra expressions obtained in the second module into an E-Graph; The fourth module is used to rewrite the E-graph constructed in the third module using the Monte Carlo tree search method to obtain a new E-graph; The fifth module is used to obtain the e-node sequence with the minimum total cost in the new E-Graph obtained from the fourth module, and this sequence is taken as the optimal node sequence; The sixth module is used to convert the optimal node sequence obtained by the fifth module into a query statement, input the query statement into the database system for querying, and obtain the final query result.

Images