A query method of a database and related products

Abstract

The application provides a database query method and related products. The method comprises the following steps: generating a query syntax tree with cross-branch repeated node annotations according to a query statement containing a UNION operation; generating at least two candidate execution plans according to the query syntax tree; obtaining the cardinality of columns involved in each branch of the query syntax tree; determining the confidence interval of the cardinality according to a quality influence factor, and generating the cost fluctuation range of each candidate execution plan; and obtaining a target execution plan according to the cost fluctuation range and the sensitivity of each candidate execution plan to the confidence interval of the cardinality. According to the scheme of the application, the cost fluctuation range of each candidate execution plan is generated according to the confidence interval of the cardinality, and the target execution plan is selected based on the overlapping relationship between the cost fluctuation ranges of each candidate execution plan and the sensitivity to the cardinality, so that the stability of query execution is improved.

Description

Technical Field

[0001] This invention relates to database technology, and in particular to a database query method and related products. Background Technology

[0002] In relational databases, when executing queries containing UNION operations, existing database systems typically employ a fixed deduplication strategy, performing deduplication at pre-defined execution nodes. When deduplication is performed uniformly after merging, if the data duplication rate is high, all data in each branch needs to be merged, resulting in a massive amount of intermediate data, high memory consumption, and a significant increase in deduplication costs as the data volume grows. Conversely, when deduplication is pre-executed within each branch, if the data duplication rate is low, the deduplication operations in each branch hardly reduce the data volume while introducing additional computational overhead. Furthermore, subsequent overall deduplication of all merged data still needs to be performed, increasing the overall computational load. Summary of the Invention

[0003] One objective of this invention is to improve the execution performance of queries that include UNION operations.

[0004] A further objective of this invention is to reduce the memory resource consumption of queries that include UNION operations during execution.

[0005] Specifically, the present invention provides a database query method, comprising: Generate a query syntax tree with cross-branch repeating node annotations based on the query statement containing the UNION operation; At least two candidate execution plans are generated based on the query syntax tree, and the processing positions corresponding to the cross-branch repeating nodes are different in each candidate execution plan; Obtain the cardinality of the columns involved in each branch of the query syntax tree, where the cardinality is used to represent the number of non-repeating values ​​in the corresponding column; Obtain at least one quality impact factor for assessing the reliability of the base, and determine the confidence interval of the base based on the quality impact factor; the quality impact factor includes the statistical information update time, statistical information sampling rate, or data change frequency of the base; Based on the confidence interval of the base, a cost fluctuation range is generated for each candidate execution plan. The cost fluctuation range is used to characterize the fluctuation range of execution cost caused by the difference in the boundary of the confidence interval of the base. Based on the overlap between the cost fluctuation ranges of each candidate execution plan and the sensitivity of each candidate execution plan to the confidence interval of the base, a target execution plan is selected from each candidate execution plan; The database's query execution engine executes the query statement according to the target execution plan and returns the query results.

[0006] Optionally, generating a query syntax tree with cross-branch repeating node annotations based on query statements containing UNION operations includes: Parse the syntax structure of the query statement and identify the UNION operation nodes in the query statement; Extract the syntax subtrees corresponding to each branch subquery of the UNION operation node, and determine the topology of each syntax subtree; Traverse each of the syntax subtrees and extract the node list of each branch subquery. The node list includes the base table scan node, join node, filter node, projection node and deduplication node in each branch. Based on the topology and node list of each branch, determine whether there are duplicate nodes in each branch; If there are fewer than two branches with duplicate nodes, generate a query syntax tree without cross-branch duplicate node annotations; If there are deduplication nodes in at least two branches, extract the target column information targeted by each deduplication node; Based on the target column information, perform pairwise matching on the deduplication nodes between different branches, and determine whether the target columns targeted by the two deduplication nodes are the same or equivalent. If the deduplication nodes between any two branches are successfully matched, cross-branch annotation information is added to the successfully matched deduplication nodes in the query syntax tree to generate a query syntax tree with cross-branch duplicate node annotations. If the deduplication of nodes between any two branches fails to match, a query syntax tree without cross-branch duplicate node annotations is generated.

[0007] Optionally, determining whether the target columns targeted by two deduplication nodes are the same or equivalent includes: Determine if the column names of the target columns targeted by the two deduplication nodes are the same; if so, determine that they are the same. If the column names are different, the system determines whether the two target columns have an equivalence relationship based on the preset column equivalence mapping table. If so, they are considered equivalent.

[0008] Optionally, generating at least two candidate execution plans based on the query syntax tree includes: Based on the cross-branch duplicate node annotation, the pattern type of the query statement is identified, and the pattern type includes a first pattern and a second pattern; in the first pattern, the query syntax tree contains a merge node and a deduplication operation node located after the merge node; in the second pattern, the query syntax tree contains a merge node, and at least two branches each contain a deduplication operation node located before the merge node. When the mode type is the first mode, a first mode candidate plan using the first mode and a second mode candidate plan using the second mode are generated. The second mode candidate plan is obtained by pushing the deduplication operation node down to each branch. Pushing the deduplication operation node down to each branch includes: copying the deduplication operation node to each branch of the merge node, and deleting the deduplication operation node after the merge node. When the mode type is the second mode, a second mode candidate plan using the second mode and a first mode candidate plan using the first mode are generated. The first mode candidate plan is obtained by pulling the deduplication operation nodes in each branch up to the merge node. Pulling the deduplication operation nodes in each branch up to the merge node includes: deleting the deduplication operation nodes in each branch and adding deduplication operation nodes after the merge node.

[0009] Optionally, determining the confidence interval of the base number based on the quality impact factor includes: Obtain the quality impact factor, which includes the statistical information update time, statistical information sampling rate, or data change frequency of the base. The time decay factor corresponding to the update time, the reliability factor corresponding to the sampling rate, and the fluctuation factor corresponding to the data change frequency are determined respectively; wherein, the value of the time decay factor decreases monotonically as the time difference between the update time and the current time increases; the value of the reliability factor increases monotonically as the sampling rate increases; and the value of the fluctuation factor decreases monotonically as the data change frequency increases. Obtain the first weight coefficient corresponding to the update time, the second weight coefficient corresponding to the sampling rate, and the third weight coefficient corresponding to the data change frequency; wherein, the first weight coefficient, the second weight coefficient, and the third weight coefficient are predetermined by the following method: obtaining historical query performance data, the historical query performance data including the actual execution plan used when at least one historical query statement was executed, the execution time of the actual execution plan, and the deviation between the statistical information used before the historical query statement was executed and the statistical information obtained again after execution; using the historical query performance data as a sample, the time difference between the update time and the execution time, the sampling rate, and the data change frequency as independent variables, and the actual execution time as the dependent variable, the values ​​of the first weight coefficient, the second weight coefficient, and the third weight coefficient are determined by regression analysis; The first error interval corresponding to the time decay factor, the second error interval corresponding to the reliability factor, and the third error interval corresponding to the volatility factor are determined respectively; wherein, the standard deviations of the historical value distributions of the time decay factor, the reliability factor, and the volatility factor in the historical query performance data are calculated respectively, and the corresponding error intervals are determined based on the standard deviations of each factor. The time decay factor, the reliability factor, and the volatility factor are weighted and summed according to the first weighting coefficient, the second weighting coefficient, and the third weighting coefficient to obtain the confidence benchmark value of the base. The first error interval, the second error interval, and the third error interval are weighted and propagated according to the first weight coefficient, the second weight coefficient, and the third weight coefficient, and the confidence interval of the base is determined with the confidence benchmark value as the center value.

[0010] Optionally, the cost range for generating each of the candidate execution plans includes: The confidence interval of the base includes a lower confidence limit and a higher confidence limit; Obtain the total amount of data in the columns involved in each branch of the query syntax tree; input the total amount of data and the lower confidence limit into a preset cost estimation function in the database system to obtain the lower cost limit of the candidate execution plan; input the total amount of data and the upper confidence limit into a preset cost estimation function in the database system to obtain the upper cost limit of the candidate execution plan. The lower limit and the upper limit of the cost are used as the upper and lower boundaries of the cost fluctuation range of the candidate execution plan.

[0011] Optionally, selecting the target execution plan from the candidate execution plans includes: Determine whether there is any overlap between the cost fluctuation ranges of the candidate execution plans; If not, select the candidate execution plan with the smallest upper limit of the cost fluctuation range as the target execution plan; If so, determine the sensitivity of each candidate execution plan relative to the confidence interval of the base, where the sensitivity is the rate of change of the cost fluctuation range boundary of the candidate execution plan as the confidence interval boundary moves; if there is a candidate execution plan with a sensitivity less than a preset sensitivity threshold, select the candidate execution plan with the smallest midpoint value in the cost fluctuation range as the target execution plan; if the sensitivity of all candidate execution plans is greater than or equal to the preset sensitivity threshold, select the candidate execution plan with the smallest sensitivity as the target execution plan.

[0012] According to another aspect of the present invention, a computer-readable storage medium is also provided, on which a computer program is stored, wherein the computer program, when executed by a processor, implements the steps of the database query method described above.

[0013] According to another aspect of the present invention, a computer program product is also provided, comprising a computer program that, when executed by a processor, implements the steps of any of the above-described database query methods.

[0014] According to another aspect of the present invention, a computer device is also provided, which includes a memory, a processor, and a computer program stored in the memory, wherein the processor executes the computer program to implement the steps of any database query method.

[0015] The database query method of this invention, by identifying query statements containing UNION operations and generating a query syntax tree with cross-branch duplicate node annotations, can accurately locate the optional execution positions of deduplication operations, improving the granularity of query optimization. By generating at least two candidate execution plans with different deduplication processing positions and evaluating the cost of each candidate execution plan based on the cardinality information of the columns involved in each branch, it can provide multiple optional execution paths according to different data distribution characteristics, avoiding performance loss caused by a single fixed strategy not matching the actual data distribution. By obtaining quality influencing factors such as statistical information update time, sampling rate, and data change frequency to determine the confidence interval of cardinality, the uncertainty of statistical information is quantified into a calculable confidence interval, enabling cost evaluation to fully consider the possible errors in the statistical information itself, improving the accuracy and reliability of cost estimation. By generating the cost fluctuation range of each candidate execution plan based on the confidence interval of the cardinality, and selecting the target execution plan based on the overlap between the cost fluctuation ranges of each candidate execution plan and the sensitivity to the cardinality, robust execution plans that are not sensitive to cardinality errors can be prioritized when there are errors in statistical information. This avoids selecting execution plans with low actual execution efficiency due to cardinality estimation bias, thereby improving the stability of query execution.

[0016] The above and other objects, advantages and features of the present invention will become more apparent to those skilled in the art from the following detailed description of specific embodiments of the invention in conjunction with the accompanying drawings. Attached Figure Description

[0017] The following sections will describe some specific embodiments of the invention in detail by way of example and not limitation, with reference to the accompanying drawings. The same reference numerals in the drawings denote the same or similar parts or portions. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings: Figure 1 This is a schematic flowchart of a database query method according to an embodiment of the present invention; Figure 2 This is a schematic flowchart illustrating the generation of a query syntax tree in a database query method according to an embodiment of the present invention; Figure 3 This is a schematic flowchart illustrating the generation of candidate execution plans in a database query method according to an embodiment of the present invention; Figure 4 This is a schematic flowchart illustrating the determination of the confidence interval of the cardinality in a database query method according to an embodiment of the present invention; Figure 5 This is a schematic flowchart illustrating the generation of cost fluctuation range in a database query method according to an embodiment of the present invention; Figure 6 This is a schematic flowchart illustrating the determination of a target execution plan in a database query method according to an embodiment of the present invention; Figure 7 This is a schematic diagram of a computer program product according to an embodiment of the present invention; Figure 8 This is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; Figure 9 This is a schematic diagram of a computer device according to an embodiment of the present invention. Detailed Implementation

[0018] In relational databases, queries involving UNION operations typically require merging and deduplicating multiple sets of data. Existing database systems determine the location for the deduplication operation during the query optimization phase when executing such queries, and this location remains constant throughout the entire execution of the query statement.

[0019] If the deduplication operation is scheduled to be executed uniformly after the merge node, the results of all branch subqueries must first be aggregated before deduplication is performed on the aggregated complete dataset. The advantage of this approach is that only one deduplication operation is performed, resulting in the most thorough deduplication. However, if the duplication rate of the data in each branch is high, the amount of intermediate data generated during the aggregation process increases, including duplicate data that will be filtered out by the deduplication operation later. This duplicate data continuously occupies memory resources before being filtered. Furthermore, the deduplication operation typically requires the use of data structures such as hash tables to determine whether data is duplicated, and its computational overhead increases with the amount of input data. In scenarios with a large amount of duplicate data, the combined effect of memory consumption and computational overhead leads to significant system resource consumption by this strategy, impacting query execution efficiency.

[0020] To address the aforementioned issues, another approach is to pre-execute the deduplication operation within each branch. This involves each branch's subqueries performing deduplication independently, then merging the results for overall deduplication. This method effectively reduces the output size of each branch in scenarios with high data duplication rates, thus lowering the data processing pressure during the merging process. However, if the duplication rate of the data in each branch is low, pre-executing deduplication within each branch yields almost no reduction in data volume, while still requiring hash table construction and data deduplication comparison, resulting in computational overhead. Furthermore, since the UNION operation itself requires that the final result contain no duplicate records, subsequent overall deduplication must still be performed on all merged data. Therefore, in the above scenario, the same batch of data undergoes multiple deduplication operations, without effectively reducing the intermediate data size, resulting in an overall computational burden higher than the method of deduplication only after merging.

[0021] In summary, query optimization methods that employ a fixed deduplication execution strategy cannot dynamically adjust based on the actual duplication rate of the data once the deduplication execution location is determined, making it difficult to achieve high execution efficiency across different duplication rate scenarios.

[0022] Figure 1 This is a schematic flowchart of a database query method according to an embodiment of the present invention. Figure 1 As shown, the query methods for this database can generally include: Step S101: Generate a query syntax tree with cross-branch duplicate node annotations based on the query statement containing the UNION operation; Step S102: Generate at least two candidate execution plans based on the query syntax tree, with different processing positions corresponding to the cross-branch duplicate nodes in each candidate execution plan; Step S103: Obtain the cardinality of the columns involved in each branch of the query syntax tree. The cardinality is used to represent the number of non-repeating values ​​in the corresponding column. Step S104: Obtain at least one quality impact factor for assessing the reliability of the base, and determine the confidence interval of the base based on the quality impact factor; the quality impact factor includes the statistical information update time of the base, the statistical information sampling rate, or the data change frequency; Step S105: Based on the confidence interval of the base, generate the cost fluctuation range of each candidate execution plan. The cost fluctuation range is used to characterize the fluctuation range of execution cost caused by the difference in the boundary of the confidence interval of the base. Step S106: Select the target execution plan from the candidate execution plans based on the overlap between the cost fluctuation ranges of each candidate execution plan and the sensitivity of each candidate execution plan to the confidence interval of the base. In step S107, the database's query execution engine executes the query statement according to the target execution plan and returns the query results.

[0023] In this embodiment, after receiving a query statement containing a UNION operation, the query parser based on the database system sequentially performs lexical scanning, syntax analysis, and semantic analysis to construct an original syntax tree. Based on this, it identifies the boundaries of the branches connected by the UNION operator, traverses all nodes in the syntax tree, and determines nodes with the same identifier (such as table nodes or column nodes) in each branch as cross-branch duplicate nodes. It then adds annotation information to the corresponding nodes in the original syntax tree, forming a query syntax tree with cross-branch duplicate node annotations.

[0024] In this embodiment, the query optimizer takes a query syntax tree with cross-branch duplicate node annotations as input and uses a multi-path enumeration strategy to generate at least two candidate execution plans. The candidate execution plans differ in the selection of physical operators and their execution order. Specifically, the deduplication operation corresponding to the cross-branch duplicate node is either executed within each branch of the UNION operation or on the merged result after the UNION operation. Because the execution position of the deduplication operation affects the amount of data participating in subsequent operations, the number of input tuples received by downstream operators in each candidate execution plan differs, resulting in different execution cost estimations.

[0025] In this embodiment, each branch of the query syntax tree is traversed, and all columns referenced by each branch are extracted. For each column, the database statistics table is accessed, and the unique value count corresponding to that column is read as the cardinality using the column identifier as the query key. This cardinality reflects the number of different values ​​in that column and is an input parameter for the query optimizer to estimate the cost.

[0026] In this embodiment, since statistical information is typically generated through periodic sampling rather than full calculation, the actual value of the base may deviate from the value recorded in the statistical information. To quantify this deviation, at least one of the following three types of quality impact factors is obtained: statistical information update time (the reliability of statistical information decreases as its update time is delayed), statistical information sampling rate (the higher the sampling rate, the higher the reliability), and data change frequency (the lower the change frequency, the higher the reliability). Based on the obtained quality impact factors, a confidence interval for the base is calculated using statistical inference methods. This confidence interval represents the possible range of values ​​for the actual base at a given confidence level, and is used to subsequently assess the cost fluctuation range of each candidate execution plan based on the uncertainty of the base.

[0027] In this embodiment, for each candidate execution plan, the lower and upper confidence limits of the confidence interval of the base number are used as inputs to evaluate the cost, resulting in corresponding lower and upper cost limits. These lower and upper cost limits together constitute the cost fluctuation range of the candidate execution plan, and the width of this range reflects the degree of influence of base number uncertainty on the execution cost.

[0028] In this embodiment, the cost fluctuation ranges of each candidate execution plan are compared, and the target execution plan is selected based on the overlap between the cost fluctuation ranges. If there is no overlap between the cost fluctuation ranges of the candidate execution plans, the candidate execution plan with the smaller overall cost fluctuation range is selected as the target execution plan; if there is overlap between the cost fluctuation ranges of the candidate execution plans, the sensitivity of each plan to changes in the confidence interval is further calculated, and the candidate execution plan with the lowest sensitivity is selected as the target execution plan.

[0029] In this embodiment, the selected target execution plan is converted into a physical execution plan and submitted to the database query execution engine. The query execution engine creates corresponding executors based on the operator sequence in the plan and schedules their execution to complete data reading, computation, and query result generation, and then returns the query results to the requesting party.

[0030] Based on the above steps, by identifying query statements containing UNION operations and generating a query syntax tree with cross-branch duplicate node annotations, the optional execution positions for deduplication operations can be accurately located, improving the granularity of query optimization. By generating at least two candidate execution plans with different deduplication processing positions and evaluating the cost of each candidate execution plan based on the cardinality information of the columns involved in each branch, multiple optional execution paths can be provided according to different data distribution characteristics, avoiding performance loss caused by a single fixed strategy not matching the actual data distribution. By obtaining quality influencing factors such as statistical information update time, sampling rate, and data change frequency to determine the confidence interval of cardinality, the uncertainty of statistical information is quantified into a calculable confidence interval, enabling cost evaluation to fully consider the possible errors in the statistical information itself, improving the accuracy and reliability of cost estimation. By generating the cost fluctuation range of each candidate execution plan based on the confidence interval of the cardinality, and selecting the target execution plan based on the overlap between the cost fluctuation ranges of each candidate execution plan and the sensitivity to the cardinality, robust execution plans that are not sensitive to cardinality errors can be prioritized when there are errors in statistical information. This avoids selecting execution plans with low actual execution efficiency due to cardinality estimation bias, thereby improving the stability of query execution.

[0031] Figure 2 This is a schematic flowchart illustrating the generation of a query syntax tree in a database query method according to an embodiment of the present invention. Figure 2 As shown, generating a query syntax tree with cross-branch repeating node annotations based on a query statement containing a UNION operation includes: Step S201: Parse the syntax structure of the query statement and identify the UNION operation nodes in the query statement; Step S202: Extract the syntax subtrees corresponding to each branch subquery of the UNION operation node, and determine the topology of each syntax subtree; Step S203: Traverse each syntax subtree and extract the node list of each branch subquery. The node list includes the base table scan node, join node, filter node, projection node and deduplication node in each branch. Step S204: Based on the topology and node list of each branch, determine whether there are duplicate nodes in each branch; Step S205: If there are fewer than two branches with duplicate nodes, generate a query syntax tree without cross-branch duplicate node annotations. Step S206: If there are deduplication nodes in at least two branches, extract the target column information targeted by each deduplication node; Step S207: Based on the target column information, perform pairwise matching on the deduplication nodes between different branches to determine whether the target columns targeted by the two deduplication nodes are the same or equivalent. Step S208: If the deduplication nodes between any two branches are successfully matched, add cross-branch annotation information to the successfully matched deduplication nodes in the query syntax tree to generate a query syntax tree with cross-branch duplicate node annotations. Step S209: If the deduplication of nodes between any two branches fails to match, generate a query syntax tree without cross-branch duplicate node annotations.

[0032] In this embodiment, after receiving the query statement, the database system calls the syntax parser to perform lexical and syntactic analysis on the query statement, generating an Abstract Syntax Tree (AST). The AST represents the logical structure of the query statement using a tree-like data structure, where each node corresponds to a syntactic unit in the query statement. Within the AST, using the node type "UNION" as the search condition, all nodes in the tree are traversed to identify UNION operation nodes. If the query statement contains nested UNIONs or UNION ALL, then UNION operation nodes at all levels are recursively identified.

[0033] For each identified UNION operation node, extract the syntax subtree corresponding to its various branch subqueries. Each branch subquery corresponds to an independent syntax subtree, which starts from the root node of the subquery and contains all the logical operation nodes of that subquery. By traversing from top to bottom, determine the topology of each syntax subtree, including the subtree's level depth, the parent-child relationship of each node, the sibling order of each node, and the logical combination method between branches.

[0034] In this embodiment, a depth-first traversal algorithm is used to traverse each syntax subtree. Node type is used as the base table scan, join, filter, projection, and distinct criteria to extract corresponding operation nodes from the node set of each syntax subtree, forming a node list for each branch. The node list is stored in the form of an array or linked list, and the order of elements corresponds to the logical execution order of the nodes in the subtree. Node type matching is performed by reading the node's type attribute; different types of nodes have different type identifiers.

[0035] In this embodiment, each node in the node list is traversed. If the type identifier of the node is a deduplication operation node, the branch is marked as having a deduplication node; if no deduplication node is found after the traversal, the branch is marked as not having a deduplication node.

[0036] In this embodiment, the statistical determination result is the total number of branches with duplicate nodes. If the total number of branches is less than 2, it is determined that there are no duplicate nodes that can be matched across branches, and no annotation is required. A query syntax tree without annotations for duplicate nodes across branches is directly generated. If the total number of branches is greater than or equal to 2, for each duplicate node in each branch, the target column information is extracted. The target column information includes the source table identifier (such as table name or table alias), the column name (or column identifier), and the data type of the target column.

[0037] In this embodiment, the query optimizer performs pairwise matching of deduplication nodes across different branches. A deduplication node from the first branch is paired with a deduplication node from the second branch, and the target columns they target are compared to see if they are the same or equivalent. The column names and base tables of the target columns targeted by the two deduplication nodes are determined to be the same. If they are, the target columns are considered the same, meaning the two deduplication nodes have successfully matched. If the column names or base tables of the target columns targeted by the two deduplication nodes are different, an equivalence relationship is determined based on a preset column equivalence mapping table; if so, they are considered equivalent, meaning the two deduplication nodes have successfully matched; otherwise, they are considered not equivalent, meaning the two deduplication nodes have failed to match.

[0038] In this embodiment, the query optimizer adds cross-branch annotation information to the successfully matched deduplicated nodes. The cross-branch annotation information includes at least: the identifiers of the other matched branches, the path information of the deduplicated nodes in the syntax tree within those branches, and the corresponding target column identifier. After annotation, the query optimizer outputs a query syntax tree with cross-branch duplicate node annotations.

[0039] In this embodiment, if the deduplication nodes between any two branches fail to match, that is, there is no pair of cross-branch deduplication nodes that satisfy the same or equivalent conditions, the query optimizer does not add annotation information to any deduplication node and directly outputs a query syntax tree without cross-branch duplicate node annotations.

[0040] Based on the above steps, by performing pairwise matching on the deduplication nodes between different branches, determining whether the target columns and their respective base tables are the same or equivalent, and adding cross-branch annotation information when the match is successful, it is possible to accurately identify situations where there are redundant deduplication operations between cross branches, avoid processing the deduplication nodes of each branch in isolation, thereby improving the granularity and accuracy of query optimization.

[0041] Figure 3 This is a schematic flowchart illustrating the generation of candidate execution plans in a database query method according to an embodiment of the present invention. Figure 3 As shown, generating at least two candidate execution plans based on the query syntax tree includes: Step S301: Based on the cross-branch duplicate node annotation, identify the pattern type of the query statement. The pattern type includes a first pattern and a second pattern. In the first pattern, the query syntax tree contains a merge node and a deduplication operation node located after the merge node. In the second pattern, the query syntax tree contains a merge node, and at least two branches each contain a deduplication operation node located before the merge node. Step S302: When the mode type is the first mode, generate a first mode candidate plan using the first mode and a second mode candidate plan using the second mode. The second mode candidate plan is obtained by pushing the deduplication operation node down to each branch. Pushing the deduplication operation node down to each branch includes: copying the deduplication operation node to each branch of the merge node, and deleting the deduplication operation node after the merge node. Step S303: When the mode type is the second mode, generate a second mode candidate plan using the second mode and a first mode candidate plan using the first mode. The first mode candidate plan is obtained by pulling the deduplication operation nodes in each branch to the merge node. Pulling the deduplication operation nodes in each branch to the merge node includes: deleting the deduplication operation nodes in each branch and adding the deduplication operation nodes after the merge node.

[0042] In this embodiment, the query optimizer reads the cross-branch duplicate node annotation information in the query syntax tree and identifies the pattern type of the current query statement based on the annotation information and the positional relationship of each node. The pattern type is determined by the relative positional relationship between the merge node and the deduplication operation node. In the first pattern, the query syntax tree contains both a merge node and a deduplication operation node, with the deduplication operation node located downstream of the merge node. The downstream position indicates that the deduplication operation is executed after the merge operation is completed. In the second pattern, the query syntax tree contains a merge node, and at least two branches each contain a deduplication operation node. Each deduplication operation node is located upstream of the merge node, with the upstream position indicating that the deduplication operation is executed separately in each branch before merging. If the query syntax tree does not satisfy the conditions of both the first and second patterns, the candidate plan generation process is not executed.

[0043] In this embodiment, if the query statement's mode type is the first mode, the query optimizer generates two candidate execution plans. The first candidate plan is the execution plan using the first mode, which maintains the original query syntax tree structure unchanged, and the deduplication operation is performed after the merge node. The second candidate plan is the execution plan using the second mode, obtained by performing a deduplication operation node pushdown operation on the original query syntax tree. Pushing the deduplication operation node down to each branch includes: copying the deduplication operation node after the merge node to each branch of the merge node, so that each branch contains one deduplication operation node, and then deleting the original deduplication operation node after the merge node.

[0044] In this embodiment, if the query statement's mode type is the second mode, the query optimizer generates two candidate execution plans. The first candidate plan is the execution plan using the second mode, which maintains the original query syntax tree structure, performs deduplication on each branch separately, and then merges them. The second candidate plan is the execution plan using the first mode, obtained by performing a deduplication operation node pull-up operation on the original query syntax tree. Pulling the deduplication operation nodes in each branch up to the merge node includes: deleting the deduplication operation nodes in each branch, and then adding a deduplication operation node after the merge node.

[0045] Based on the above steps, the query statement's pattern type is identified by marking duplicate nodes across branches, accurately distinguishing between the first and second patterns. When the pattern type is the first pattern, both a first-pattern candidate plan using the first pattern and a second-pattern candidate plan using the second pattern are generated simultaneously. The deduplication operation nodes are pushed down to each branch, allowing for flexible adjustment of the deduplication execution location. This enables the query optimizer to choose between different deduplication execution strategies, thus selecting a better execution path based on the actual data distribution characteristics. When the pattern type is the second pattern, both a second-pattern candidate plan using the second pattern and a first-pattern candidate plan using the first pattern are generated simultaneously. The deduplication operation nodes in each branch are pulled up after the merge node, achieving the merging and centralized processing of deduplication operations, avoiding redundant computational overhead caused by independent deduplication in each branch.

[0046] Figure 4 This is a schematic flowchart illustrating the determination of the confidence interval of the cardinality in a database query method according to an embodiment of the present invention. Figure 4 As shown, the confidence interval for determining the base number based on the quality impact factor includes: Step S401: Obtain the quality impact factor, which includes the update time of the statistical information of the base, the sampling rate of the statistical information, or the frequency of data change. Step S402: Determine the time decay factor corresponding to the update time, the reliability factor corresponding to the sampling rate, and the fluctuation factor corresponding to the data change frequency, respectively; wherein, the value of the time decay factor decreases monotonically as the time difference between the update time and the current time increases; the value of the reliability factor increases monotonically as the sampling rate increases; and the value of the fluctuation factor decreases monotonically as the data change frequency increases. Step S403: Obtain the first weight coefficient corresponding to the update time, the second weight coefficient corresponding to the sampling rate, and the third weight coefficient corresponding to the data change frequency; wherein, the first weight coefficient, the second weight coefficient, and the third weight coefficient are predetermined by the following method: Obtain historical query performance data, which includes the actual execution plan used by at least one historical query statement during execution, the execution time of the execution plan, and the deviation between the statistical information used before the historical query statement was executed and the statistical information obtained again after execution; using the historical query performance data as a sample, with the time difference between the update time and the execution time, the sampling rate, and the data change frequency as independent variables, and the actual execution time as the dependent variable, determine the values ​​of the first weight coefficient, the second weight coefficient, and the third weight coefficient through regression analysis; Step S404: Determine the first error interval corresponding to the time decay factor, the second error interval corresponding to the reliability factor, and the third error interval corresponding to the volatility factor; wherein, the standard deviations of the historical value distributions of the time decay factor, reliability factor, and volatility factor in the historical query performance data are calculated respectively, and the corresponding error intervals are determined based on the standard deviations of each factor. Step S405: The time decay factor, reliability factor, and volatility factor are weighted and summed according to the first weight coefficient, the second weight coefficient, and the third weight coefficient to obtain the confidence benchmark value of the base. Step S406: Perform weighted propagation on the first error interval, the second error interval, and the third error interval according to the first weighting coefficient, the second weighting coefficient, and the third weighting coefficient, and determine the confidence interval of the base with the confidence benchmark value as the center value.

[0047] In this embodiment, at least one quality influencing factor affecting the reliability of the base is obtained. This quality influencing factor includes at least one of the following: statistical information update time, statistical information sampling rate, and data change frequency. The statistical information update time is the most recent maintenance or update time of the statistical information in this column. The time difference between the update time and the execution time of the current query is used to characterize the freshness of the statistical information. The statistical information sampling rate is the proportion of data pages or rows actually scanned when generating the statistical information in this column, relative to the total amount of data. The sampling rate is used to characterize the coverage of the statistical information across the entire dataset. The data change frequency is the average number of updates to the data in this column per unit time, including insertion, modification, and deletion operations.

[0048] In this embodiment, the update time is converted into a time decay factor, which is determined based on the time difference between the update time and the current system time. The time decay factor is negatively correlated with the time difference and is used to characterize the degree of decay of statistical information over time. The sampling rate is converted into a reliability factor, which is positively correlated with the sampling rate and is used to characterize the reliability of statistical information at the sampling level. The data change frequency is converted into a fluctuation factor, which is negatively correlated with the data change frequency and is used to characterize the degree to which statistical information is affected by data updates.

[0049] In this embodiment, historical query performance data is collected. This historical query performance data includes the actual execution plan used when at least one historical query statement was executed, the execution time T of the execution plan, and the deviation value δ between the statistical information used before the historical query statement was executed and the statistical information obtained after execution. The deviation value δ characterizes the degree of deviation between the statistical information before execution and the actual data.

[0050] Using historical query performance data as training samples, a multiple linear regression model is established with the time difference Δt between update time and execution time, the sampling rate s, and the data change frequency c as independent variables, and the actual execution time T as the dependent variable. For example, the multiple linear regression model is expressed as: T = β0 + β1 Δt + β2 s + β3 c + ε, where β0 is the regression constant, β1, β2, and β3 are the regression coefficients corresponding to Δt, s, and c, respectively, and ε is the error term. The model parameters of the multiple linear regression model are estimated using the least squares method to obtain the values ​​of each regression coefficient β1, β2, and β3. β0, as a constant, is not included in the subsequent calculation of weight coefficients. The absolute values ​​of β1, β2, and β3 are taken and normalized to obtain the first weight coefficient corresponding to the update time, the second weight coefficient corresponding to the sampling rate, and the third weight coefficient corresponding to the data change frequency, respectively.

[0051] In this embodiment, the standard deviations σ1, σ2, and σ3 are determined based on the historical value distributions of the time decay factor, reliability factor, and volatility factor in the historical query performance data, respectively. The corresponding error interval is then determined based on the standard deviations of each factor: E1 = [-k σ1, +k σ1],E2=[-k σ2, +k σ2],E3=[-k σ3, +k σ3], where k is the preset confidence level coefficient.

[0052] The time decay factor, reliability factor, and volatility factor are weighted and summed based on the first, second, and third weighting coefficients to obtain the confidence baseline value C_base, where C_base = time decay factor. First weighting coefficient + reliability factor Second weighting coefficient + volatility factor The third weighting coefficient is given by the sum of the first weighting coefficient, the second weighting coefficient, and the third weighting coefficient, which equals 1.

[0053] In this embodiment, the first error interval, the second error interval, and the third error interval are weighted and propagated according to the first weighting coefficient, the second weighting coefficient, and the third weighting coefficient to obtain the comprehensive error interval. Using the confidence benchmark value C_base as the center value, the comprehensive error interval is expanded to both sides to obtain the confidence interval of the base.

[0054] Based on the above steps, the standard deviations of the historical values ​​of the time decay factor, reliability factor, and volatility factor in historical query performance data are statistically calculated, and the corresponding error intervals are determined based on the standard deviations of each factor. This quantifies the actual volatility of each factor in historical execution as the error boundary, improving the objectivity of error quantification. A confidence benchmark value is obtained by weighting and summing each factor according to its weight coefficients. Then, the confidence interval is determined by weighted propagation based on the error intervals of each factor according to its weight coefficients. This transforms the uncertainty of statistical information from a qualitative description into a quantifiable interval form.

[0055] Figure 5 This is a schematic flowchart illustrating the generation of a cost fluctuation range in a database query method according to an embodiment of the present invention. Figure 5 As shown, the cost fluctuation range for generating each candidate execution plan includes: Step S501, the confidence interval of the base includes the lower confidence limit and the upper confidence limit; Step S502: Obtain the total amount of data in the columns involved in each branch of the query syntax tree; input the total amount of data and the lower confidence limit into the preset cost estimation function in the database system to obtain the lower cost limit of the candidate execution plan; input the total amount of data and the upper confidence limit into the preset cost estimation function in the database system to obtain the upper cost limit of the candidate execution plan. Step S503: Use the lower limit and upper limit of cost as the upper and lower boundaries of the cost fluctuation range of the candidate execution plan.

[0056] Based on the above steps, by combining the confidence interval of the cardinality with the total data volume and inputting them into a pre-defined cost estimation function in the database system, the lower and upper cost limits of the candidate execution plans are obtained. This achieves a transfer mapping from cardinality uncertainty to execution plan cost uncertainty, quantifying the impact of errors in cardinality statistics on execution costs. The lower and upper cost limits are used to construct the upper and lower boundaries of the cost fluctuation range, realizing a complete transfer path from the cardinality confidence interval to the cost fluctuation range. This ensures that the statistical uncertainty of the cardinality is accurately transferred to the cost assessment level, providing a reliable data foundation for selecting candidate execution plans based on the cost fluctuation range.

[0057] Figure 6 This is a schematic flowchart illustrating the determination of a target execution plan in a database query method according to an embodiment of the present invention. Figure 6 As shown, the selection of the target execution plan from the candidate execution plans includes: Step S601: Determine whether there is any overlap between the cost fluctuation ranges of each candidate execution plan; Step S602: If not, select the candidate execution plan with the smallest upper limit of cost fluctuation range as the target execution plan; Step S603: If yes, determine the sensitivity of each candidate execution plan relative to the confidence interval of the base. The sensitivity is the rate of change of the cost fluctuation range boundary of the candidate execution plan as the confidence interval boundary moves. If there is a candidate execution plan with a sensitivity less than the preset sensitivity threshold, select the candidate execution plan with the smallest midpoint value in the cost fluctuation range as the target execution plan. If the sensitivity of all candidate execution plans is greater than or equal to the preset sensitivity threshold, select the candidate execution plan with the smallest sensitivity as the target execution plan.

[0058] Based on the above steps, by determining whether there is any overlap between the cost fluctuation ranges of each candidate execution plan, the candidate execution plan with the smallest upper limit of cost is selected as the target execution plan. Since the cost ranges of each candidate execution plan are separated from each other, the candidate execution plan with the smallest upper limit of cost has a clear cost advantage under all possible cardinality values, ensuring that the selected candidate execution plan will not be inferior to other plans under any circumstances, thus improving the predictability of query execution results.

[0059] The flowchart provided in this embodiment is not intended to indicate that the operations of the method will be performed in any particular order, or that all operations of the method are included in every case. Furthermore, the method may include additional operations. Within the scope of the technical concept provided by the method in this embodiment, additional variations can be made to the above method.

[0060] It should be understood that in some embodiments, the components may be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods may be implemented using software or firmware stored in memory and executed by a suitable instruction execution system.

[0061] This embodiment also provides a computer program product 10, a computer-readable storage medium 20, and a computer device 30. Figure 7 This is a schematic diagram of a computer program product 10 according to an embodiment of the present invention. Figure 8 This is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention. Figure 9 This is a schematic diagram of a computer device 30 according to an embodiment of the present invention. Figure 7 As shown, the computer program product 10 includes a computer program 11, which, when executed by the processor 32, implements the steps of any of the above-described database query methods. For example... Figure 8 As shown, a computer-readable storage medium 20 stores the aforementioned computer program 11, which, when executed by the processor 32, implements the steps of the database query method of any of the above embodiments. Figure 9As shown, the computer device 30 may include a memory 31, a processor 32, and a computer program 11 stored on the memory 31 and running on the processor 32.

[0062] The computer program 11 used to perform the operations of this invention may be assembly instructions, Instruction Set Architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, status setting data, integrated circuit configuration data, or source code or object code written in any combination of one or more programming languages ​​and procedural programming languages. The computer program 11 may execute entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network, including a Local Area Network (LAN) or a Wide Area Network (WAN), or may be connected to an external computer. In some embodiments, to perform aspects of this invention, electronic circuits, including, for example, programmable logic circuits, field-programmable gate arrays (FPGAs), or programmable logic arrays (PLAs), may execute computer-readable program instructions to personalize the electronic circuits by utilizing status information of the computer-readable program instructions.

[0063] For the purposes of this embodiment, computer program product 10 is a related product that includes computer program 11.

[0064] For the purposes of this embodiment, computer-readable storage medium 20 is a tangible device capable of holding and storing a computer program 11. It can be any device capable of containing, storing, communicating, propagating, or transmitting the program 11 for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of computer-readable storage medium 20 include: portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), portable optical disc read-only memory (CD-ROM), digital versatile disc (DVD), memory stick, floppy disk, mechanical encoding device, and any suitable combination thereof.

[0065] Computer device 30 can be, for example, a server, desktop computer, laptop computer, tablet computer, or smartphone. In some examples, computer device 30 can be a cloud computing node. Computer device 30 can be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, program modules can include routines, programs, object programs, components, logic, data structures, etc., that perform specific tasks or implement specific abstract data types. Computer device 30 can be implemented in a distributed cloud computing environment where tasks are performed by remote processing devices linked through a communication network. In a distributed cloud computing environment, program modules can reside on local or remote computing system storage media, including storage devices.

[0066] Computer device 30 may include a processor 32 adapted to execute stored instructions and a memory 31 that provides temporary storage space for the operation of said instructions during operation. The processor 32 may be a single-core processor, a multi-core processor, a computing cluster, or any other configuration. The memory 31 may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0067] Computer device 30 may also include a network adapter / interface and an input / output (I / O) interface. The I / O interface allows external devices that can be connected to the computer device to input and output data. The network adapter / interface provides communication between the computer device and a network, typically represented as a communication network.

[0068] Therefore, those skilled in the art should recognize that although numerous exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications conforming to the principles of the present invention can be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Thus, the scope of the present invention should be understood and construed as covering all such other variations or modifications.

Claims

1. A database query method, characterized in that, include: Generate a query syntax tree with cross-branch repeating node annotations based on the query statement containing the UNION operation; At least two candidate execution plans are generated based on the query syntax tree, and the processing positions corresponding to the cross-branch repeating nodes are different in each candidate execution plan; Obtain the cardinality of the columns involved in each branch of the query syntax tree, where the cardinality is used to represent the number of non-repeating values ​​in the corresponding column; Obtain at least one quality impact factor for assessing the reliability of the base, and determine the confidence interval of the base based on the quality impact factor; the quality impact factor includes the statistical information update time, statistical information sampling rate, or data change frequency of the base; Based on the confidence interval of the base, a cost fluctuation range is generated for each candidate execution plan. The cost fluctuation range is used to characterize the fluctuation range of execution cost caused by the difference in the boundary of the confidence interval of the base. Based on the overlap between the cost fluctuation ranges of each candidate execution plan and the sensitivity of each candidate execution plan to the confidence interval of the base, a target execution plan is selected from each candidate execution plan; The database's query execution engine executes the query statement according to the target execution plan and returns the query results.

2. The database query method according to claim 1, characterized in that, Based on a query statement containing a UNION operation, generate a query syntax tree with cross-branch repeating node annotations, including: Parse the syntax structure of the query statement and identify the UNION operation nodes in the query statement; Extract the syntax subtrees corresponding to each branch subquery of the UNION operation node, and determine the topology of each syntax subtree; Traverse each of the syntax subtrees and extract the node list of each branch subquery. The node list includes the base table scan node, join node, filter node, projection node and deduplication node in each branch. Based on the topology and node list of each branch, determine whether there are duplicate nodes in each branch; If there are fewer than two branches with duplicate nodes, generate a query syntax tree without cross-branch duplicate node annotations; If there are deduplication nodes in at least two branches, extract the target column information targeted by each deduplication node; Based on the target column information, pairwise matching is performed on the deduplication nodes between different branches to determine whether the target columns targeted by the two deduplication nodes are the same or equivalent. If the deduplication nodes between any two branches are successfully matched, cross-branch annotation information is added to the successfully matched deduplication nodes in the query syntax tree to generate a query syntax tree with cross-branch duplicate node annotations. If the deduplication of nodes between any two branches fails to match, a query syntax tree without cross-branch duplicate node annotations is generated.

3. The database query method according to claim 2, characterized in that, Determining whether two deduplication nodes target the same or equivalent target columns includes: Determine if the column names of the target columns targeted by the two deduplication nodes are the same; if so, determine that they are the same. If the column names are different, the system determines whether the two target columns have an equivalence relationship based on the preset column equivalence mapping table. If they do, they are considered equivalent.

4. The database query method according to claim 1, characterized in that, Generate at least two candidate execution plans based on the query syntax tree, including: Based on the cross-branch duplicate node annotation, the pattern type of the query statement is identified, and the pattern type includes a first pattern and a second pattern; in the first pattern, the query syntax tree contains a merge node and a deduplication operation node located after the merge node; in the second pattern, the query syntax tree contains a merge node, and at least two branches each contain a deduplication operation node located before the merge node. When the mode type is the first mode, a first mode candidate plan using the first mode and a second mode candidate plan using the second mode are generated. The second mode candidate plan is obtained by pushing the deduplication operation node down to each branch. Pushing the deduplication operation node down to each branch includes: copying the deduplication operation node to each branch of the merge node, and deleting the deduplication operation node after the merge node. When the mode type is the second mode, a second mode candidate plan using the second mode and a first mode candidate plan using the first mode are generated. The first mode candidate plan is obtained by pulling the deduplication operation nodes in each branch up to the merge node. Pulling the deduplication operation nodes in each branch up to the merge node includes: deleting the deduplication operation nodes in each branch and adding deduplication operation nodes after the merge node.

5. The database query method according to claim 1, characterized in that, The confidence interval for determining the base number based on the quality impact factor includes: Obtain the quality impact factor, which includes the statistical information update time, statistical information sampling rate, or data change frequency of the base. The time decay factor corresponding to the update time, the reliability factor corresponding to the sampling rate, and the fluctuation factor corresponding to the data change frequency are determined respectively; wherein, the value of the time decay factor decreases monotonically as the time difference between the update time and the current time increases; the value of the reliability factor increases monotonically as the sampling rate increases; and the value of the fluctuation factor decreases monotonically as the data change frequency increases. Obtain the first weight coefficient corresponding to the update time, the second weight coefficient corresponding to the sampling rate, and the third weight coefficient corresponding to the data change frequency; wherein, the first weight coefficient, the second weight coefficient, and the third weight coefficient are predetermined by the following method: obtaining historical query performance data, the historical query performance data including the actual execution plan used when at least one historical query statement was executed, the execution time of the actual execution plan, and the deviation between the statistical information used before the historical query statement was executed and the statistical information obtained again after execution; using the historical query performance data as a sample, the time difference between the update time and the execution time, the sampling rate, and the data change frequency as independent variables, and the actual execution time as the dependent variable, the values ​​of the first weight coefficient, the second weight coefficient, and the third weight coefficient are determined by regression analysis; The first error interval corresponding to the time decay factor, the second error interval corresponding to the reliability factor, and the third error interval corresponding to the volatility factor are determined respectively; wherein, the standard deviations of the historical value distributions of the time decay factor, the reliability factor, and the volatility factor in the historical query performance data are calculated respectively, and the corresponding error intervals are determined based on the standard deviations of each factor. The time decay factor, the reliability factor, and the volatility factor are weighted and summed according to the first weighting coefficient, the second weighting coefficient, and the third weighting coefficient to obtain the confidence benchmark value of the base. The first error interval, the second error interval, and the third error interval are weighted and propagated according to the first weight coefficient, the second weight coefficient, and the third weight coefficient, and the confidence interval of the base is determined with the confidence benchmark value as the center value.

6. The database query method according to claim 1, characterized in that, The cost range for generating each of the candidate execution plans includes: The confidence interval of the base includes a lower confidence limit and a higher confidence limit; Obtain the total amount of data in the columns involved in each branch of the query syntax tree; input the total amount of data and the lower confidence limit into a preset cost estimation function in the database system to obtain the lower cost limit of the candidate execution plan; input the total amount of data and the upper confidence limit into a preset cost estimation function in the database system to obtain the upper cost limit of the candidate execution plan. The lower limit and the upper limit of the cost are used as the upper and lower boundaries of the cost fluctuation range of the candidate execution plan.

7. The database query method according to claim 1, characterized in that, Selecting a target execution plan from the candidate execution plans includes: Determine whether there is any overlap between the cost fluctuation ranges of the candidate execution plans; If not, select the candidate execution plan with the smallest upper limit of the cost fluctuation range as the target execution plan; If so, determine the sensitivity of each candidate execution plan relative to the confidence interval of the base, where the sensitivity is the rate of change of the cost fluctuation range boundary of the candidate execution plan as the confidence interval boundary moves; if there is a candidate execution plan with a sensitivity less than a preset sensitivity threshold, select the candidate execution plan with the smallest midpoint value in the cost fluctuation range as the target execution plan; if the sensitivity of all candidate execution plans is greater than or equal to the preset sensitivity threshold, select the candidate execution plan with the smallest sensitivity as the target execution plan.

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

9. A computer program product, comprising a computer program, characterized in that, When executed by a processor, the computer program implements the steps of the database query method according to any one of claims 1 to 7.

10. A computer device, characterized in that, The system includes a memory, a processor, and a computer program stored in the memory, wherein the processor executes the computer program to implement the steps of the database query method according to any one of claims 1 to 7.

Images