A database query optimization method, a terminal and a storage medium

Abstract

This invention discloses a database query optimization method, terminal, and storage medium. The method includes: acquiring table data and a query statement to be executed from a database; inputting features from the table data and query statement into a training model to predict K nodes that need re-optimization; dividing the K nodes according to a partitioning strategy to obtain K+1 execution stages; and sequentially operating on the K+1 execution stages according to a preset execution logic to complete the query optimization. This invention combines a machine learning model with traditional query optimization methods, primarily employing a multi-stage, step-by-step execution of a single SQL statement. It mainly uses Join nodes predicted by the machine learning model to segment the query plan. Then, it further optimizes the plan at different stages using real-time cardinality. This method solves the problem of inaccurate estimation in traditional cardinality estimation methods under multi-table joins, while avoiding the drawbacks of a single machine learning model.

Description

Technical Field

[0001] This invention relates to the field of database querying, and more particularly to a database query optimization method, terminal, and storage medium. Background Technology

[0002] Cardinality estimation has always been a crucial issue in database query optimization. The accuracy of cardinality estimation significantly impacts the generation of a good query plan. Research in query optimization has demonstrated, through argumentation and experimentation, that cardinality estimation has a more significant impact on the query plan than the cost model.

[0003] Existing base estimates are mainly divided into two categories:

[0004] The first category is the traditional cardinality estimation method. This primarily involves sampling and statistically analyzing the data in a table to create a histogram describing the data distribution, while simultaneously recording various data values. For example, the proportion of NULL values ​​and the most frequently occurring value. Cardinality can then be estimated using this information. However, some papers have found that this traditional method cannot accurately predict the cardinality of different nodes under multiple join queries, and the estimated cardinality often has a prediction error of several orders of magnitude compared to the true cardinality.

[0005] The second type of method employs machine learning, aiming to replace the traditional statistical and predictive models of the first type with machine learning models. In recent years, many studies related to this type of method have emerged, utilizing machine learning models such as Deep Learning, Reinforcement Learning, and Deep Reinforcement Learning. This type of method throws the complex cardinality estimation problem into the black box of machine learning, and performs the final cardinality estimation through training. Some papers point out that although this type of machine learning method can achieve relatively good cardinality estimation results when the right model is selected and sufficiently trained, it is often costly, and the predictive performance of a single machine learning model is unstable when the data distribution is complex.

[0006] Therefore, existing technologies still need improvement. Summary of the Invention

[0007] The technical problem to be solved by this invention is to provide a database query optimization method, terminal and storage medium to address the shortcomings of existing technologies and solve the problem of poor accuracy in traditional cardinality estimation methods.

[0008] The technical solution adopted by this invention to solve the technical problem is as follows:

[0009] In a first aspect, the present invention provides a database query optimization method, comprising:

[0010] Obtain table data and query statements to be executed from the database, and input the features extracted from the table data and query statements to the machine learning model to predict K nodes that need to be re-optimized;

[0011] The K nodes are divided into K+1 execution stages according to the execution stage division strategy; wherein each of the K+1 execution stages is a new execution stage obtained by re-optimization after the previous stage is completed.

[0012] Based on the preset execution logic, query analysis, logical optimization, physical optimization, and planned execution operations are performed sequentially on K+1 execution stages to optimize database queries.

[0013] In one implementation, the step of obtaining table data and the query statement to be executed from the database includes, prior to:

[0014] Obtain the training dataset and query statement;

[0015] The machine learning model is trained using the training dataset and the query statement to obtain the trained machine learning model.

[0016] In one implementation, the operation of sequentially performing query analysis, logical optimization, physical optimization, and planned execution on K+1 execution stages according to preset execution logic includes:

[0017] The variables required for the K+1 execution stages are shared globally, and the logical instructions in the execution query framework are controlled.

[0018] Save the intermediate results of different phases of the plan after execution, and obtain the true cardinality of the current node through the saved results;

[0019] The cardinality of the existing path tree is updated based on the true cardinality, and a new path tree is generated.

[0020] In one implementation, the global sharing of variables required for the K+1 execution stages and the control of logical instructions in the execution query framework include:

[0021] Create data structures for each state and pass these data structures as parameters in the corresponding functions to achieve global sharing of the required variables;

[0022] Determine whether the logical instructions in the currently executing query framework satisfy the corresponding query logic;

[0023] If yes, then control the execution of the current logical instruction;

[0024] If not, then exit the query framework.

[0025] In one implementation, the data structure includes one or more combinations of a portal data structure, an estate data structure, and an AQPState data structure.

[0026] In one implementation, saving the intermediate results of different stages of the completed plan and obtaining the true cardinality of the current node through the saved results includes:

[0027] It has been confirmed that there are already physically optimized nodes in the query plan;

[0028] The intermediate results of different phases of the plan are saved using the existing physical optimization nodes.

[0029] When there are no physical optimization nodes in the query plan, create new optimization nodes and add the new optimization nodes to the upper layer of each partition node to save the intermediate results of different stages of the executed plan.

[0030] The true cardinality of the current node is obtained by saving the results.

[0031] In one implementation, updating the cardinality of the existing path tree based on the true cardinality and generating a new path tree includes:

[0032] The previously estimated base number was revised based on the actual base number;

[0033] A new path tree is generated based on the modified dynamic programming mechanism and cost model for a new round of execution.

[0034] In one implementation, generating a new path tree based on the modified dynamic programming mechanism and cost model includes:

[0035] The deepest path subtree in the previous stage is selected according to the modified dynamic programming mechanism;

[0036] Create a new variable and use it as the identifier of the deepest path subtree in the previous stage and the identifier of the corresponding extended path subtree.

[0037] Determine whether the identifier corresponding to each node is different from the identifier of the current path;

[0038] If the identifier is different from the current path identifier, it means that the path does not belong to the deepest path subtree executed in the previous stage, and the corresponding node and path are cleared.

[0039] If the identifier is the same as the current path, the current path is retained to obtain a new path tree for the next stage.

[0040] In a second aspect, the present invention also provides a terminal, comprising: a processor and a memory, wherein the memory stores a database query optimization program, and the database query optimization program, when executed by the processor, is used to implement the database query optimization method as described in the first aspect.

[0041] Thirdly, the present invention also provides a storage medium, which is a computer-readable storage medium, storing a database query optimization program, which, when executed by a processor, is used to implement the database query optimization method as described in the first aspect.

[0042] The present invention, by employing the above technical solution, has the following effects:

[0043] This invention acquires table data and the query statement to be executed from a database, and inputs the features of the table data and the query statement into a training model to predict K nodes that need to be re-optimized. The K nodes are then divided into K+1 execution stages according to a partitioning strategy. The K+1 execution stages are then operated on sequentially according to a preset execution logic to complete the query optimization. This invention combines machine learning models with traditional query optimization methods, primarily employing a multi-stage, step-by-step execution of a single SQL statement. It mainly uses the Join nodes predicted by the machine learning model to segment the query plan. Then, real-time cardinality is used to further optimize the plan at different stages. This method solves the problem of inaccurate estimation in traditional cardinality estimation methods under multi-table joins, while avoiding the drawbacks of a single machine learning model. Attached Figure Description

[0044] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on the structures shown in these drawings without creative effort.

[0045] Figure 1 This is a flowchart of a database query optimization method in one implementation of the present invention.

[0046] Figure 2 This is a flowchart of the PG architecture in one implementation of the present invention.

[0047] Figure 3 This is a schematic diagram of the MaterialAnalyze related data structure in one implementation of the present invention.

[0048] Figure 4This is a schematic diagram illustrating the generation process of MaterialAnalyze-related data structures in the PG architecture in one implementation of the present invention.

[0049] Figure 5 This is a schematic diagram of the function design of the MaterialAnalyze related nodes in one implementation of the present invention.

[0050] Figure 6 This is a schematic diagram of an infinite loop in one implementation of the present invention.

[0051] Figure 7 This is a schematic diagram of the manode_num generation mechanism in one implementation of the present invention.

[0052] Figure 8 This is a schematic diagram of the tree selection mechanism in one implementation of the present invention.

[0053] Figure 9 This is a schematic diagram of subset generation relationship in one implementation of the present invention.

[0054] Figure 10 This is a schematic diagram illustrating the generation relationship of various connection nodes in one implementation of the present invention.

[0055] Figure 11 This is a schematic diagram of subset determination in one implementation of the present invention.

[0056] Figure 12 This is a schematic diagram illustrating the reduction of the node range that needs to be corrected in one implementation of the present invention.

[0057] Figure 13 This is a schematic diagram of the version setting in one implementation of the present invention.

[0058] Figure 14 This is a useless Path diagram in one implementation of the present invention.

[0059] Figure 15 This is a schematic diagram illustrating the evolution of different Stages in one implementation of the present invention.

[0060] Figure 16 This is a schematic diagram of the base node processing in one implementation of the present invention.

[0061] Figure 17 This is a functional schematic diagram of the terminal in one implementation of the present invention.

[0062] The realization of the objective, functional features and advantages of the present invention will be further explained in conjunction with the embodiments and with reference to the accompanying drawings. Detailed Implementation

[0063] To make the objectives, technical solutions, and advantages of this invention clearer and more explicit, 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 of the invention and are not intended to limit the invention.

[0064] Exemplary methods

[0065] Traditional cardinality estimation methods primarily involve sampling and statistically analyzing data in a table to create a histogram describing the data distribution, while simultaneously recording the numerical values ​​of each data point. This traditional method struggles to accurately predict the cardinality of different nodes in multi-join queries, and the estimated cardinality often deviates from the true cardinality by several orders of magnitude. Machine learning aims to replace the traditional statistical and predictive models in the first category with machine learning models. While these machine learning methods can achieve relatively good cardinality estimation results with the right model and sufficient training, they are often costly, and the predictive performance of a single machine learning model is unstable when the data distribution is complex.

[0066] To address the aforementioned technical problems, this invention provides a database query optimization method. This invention combines a machine learning model with the traditional cardinality estimation method, employs multi-stage execution of a single SQL statement, and segments the query plan through Join nodes. This solves the problem of inaccurate estimation in traditional cardinality estimation methods while reducing the cost of machine learning.

[0067] like Figure 1 As shown, this embodiment of the invention provides a database query optimization method, including the following steps:

[0068] Step S100: Obtain table data and query statements to be executed from the database, and input the features extracted from the table data and query statements to the machine learning model to predict K nodes that need to be re-optimized.

[0069] In this embodiment, the database query optimization method is applied to a terminal, which includes, but is not limited to, devices such as computers and mobile terminals. Specifically, this embodiment is applied to cardinality estimation in the field of database query optimization. Database query optimization can execute corresponding instructions (e.g., PG source code) through an AQP mechanism framework (e.g., a database query framework) and implement the database query optimization method based on these instructions. In addition, some distributed computing frameworks, such as Spark, have similar AQE mechanisms that can also implement the database query optimization method.

[0070] To address the inaccuracies in cardinality estimation using traditional cardinality estimation methods with multiple join queries, and the instability and high prediction costs of black-box models in machine learning approaches, this embodiment combines a machine learning model with the traditional cardinality estimation method, rather than completely replacing the traditional cardinality estimation model with a machine learning model.

[0071] Based on the above principles, in this embodiment, a machine learning model can be trained using a series of datasets and a query set (i.e., a set of query statements) to obtain a usable machine learning model.

[0072] Specifically, in one implementation of this embodiment, the following steps are included before step S100:

[0073] Step S101a: Obtain the training dataset and query statement;

[0074] Step S101b: Train the machine learning model using the training dataset and the query statement to obtain the trained machine learning model.

[0075] like Figure 2 As shown, in this embodiment, a traditional cardinality estimation model (i.e., using information such as histograms to estimate the cardinality of nodes) is adopted in the underlying design. However, based on this model, a method is considered to correct the inaccurate cardinality estimation in the planned nodes. To correct the problem of inaccurate cardinality estimation under multiple join queries, a multi-stage execution method is adopted in this embodiment.

[0076] After addressing the problem of traditional cardinality estimation, the selection of Join nodes needs to be considered. Although this embodiment divides the query plan using Join nodes, dividing each Join node would require a large number of query stages to execute a single SQL statement, leading to a sharp increase in query costs. Therefore, this embodiment needs to select several Join nodes with the "least accurate estimates" for partitioning, thereby minimizing the cost of cardinality correction and maximizing its effectiveness. To select Join nodes, machine learning is used for modeling and training, and the trained machine learning model is used to select Join nodes.

[0077] Specifically, the model is trained using a given dataset and SQL. Once training is complete, the model can predict which Join nodes were estimated least accurately. This achieves fine-grained stage division.

[0078] Through the general implementation scheme described above, this embodiment avoids the problem of inaccurate estimation in traditional cardinality estimation methods and also "reduces" the black box size of the machine learning model. Ultimately, a relatively better query plan can be obtained. Furthermore, since this embodiment only modifies the traditional cardinality estimation model rather than replacing it, if a better cardinality estimation model exists, the new model can be ported to this embodiment.

[0079] like Figure 1 As shown, in one implementation of this invention, the database query optimization method further includes the following steps:

[0080] Step S200: Divide the obtained K nodes according to the execution phase division strategy to obtain K+1 execution phases.

[0081] In this embodiment, after training a usable machine learning model, the relevant table data and the query dataset to be executed can be provided to the machine learning model. Based on the trained machine learning model, the K nodes with the worst estimated performance can be predicted. Then, the execution stage segmentation module will divide the process into K+1 stages based on the number of nodes K. However, it should be noted that these K+1 stages are not all generated at once. Instead, the next stage is divided sequentially after the previous stage is completed and executed. The dividing points of these K+1 stages are the K "worst" nodes generated in the model prediction process.

[0082] like Figure 1 As shown, in one implementation of this invention, the database query optimization method further includes the following steps:

[0083] Step S300: According to the preset execution logic, query analysis, logic optimization, physical optimization and planned execution operations are performed on K+1 execution stages in sequence to optimize the database query.

[0084] In this embodiment, during the execution phase, most of the execution logic within the PG architecture is executed first, such as query analysis, logic optimization, physical optimization, and plan execution. Most of this execution logic remains unchanged; therefore, we will mainly introduce the three new modules added to AQP (a query framework).

[0085] Specifically, in one implementation of this embodiment, step S300 includes the following steps:

[0086] Step S301: Globally share the variables required for K+1 execution stages and control the execution of logical instructions in the query framework;

[0087] Step S302: Save the intermediate results of the different stages of the plan after execution, and obtain the true cardinality of the current node through the saved results;

[0088] Step S303: Update the cardinality of the existing path tree according to the true cardinality, and generate a new path tree.

[0089] In this embodiment, the three newly added modules include: a connection and control module, an intermediate result processing module, and a physical plan update module. The connection and control module allows for global sharing of variables required by the K+1 execution stages and controls the execution of logical instructions within the query framework. The intermediate result processing module saves the intermediate results of different completed stage plans and obtains the true cardinality of the current node from the saved results. The physical plan update module updates the cardinality of the existing path tree based on the true cardinality and generates a new path tree. In this embodiment, the three newly added modules enable the optimization of the database query path tree.

[0090] Specifically, in one implementation of this embodiment, step S301 includes the following steps:

[0091] Step S301a: Create data structures for each state and pass the created data structures as parameters in the corresponding functions to achieve global sharing of the required variables;

[0092] Step S301b: Determine whether the logical instructions in the currently executed query framework satisfy the corresponding query logic;

[0093] Step S301c: If yes, then control the execution of the current logical instruction;

[0094] If step S301d is not true, then exit the query framework.

[0095] Specifically, in the connection and control module of this embodiment, the connection function is mainly used to connect the various modules mentioned above in the PG architecture (pospgresql, an open-source architecture), thereby enabling global sharing of some variables. Each execution function in the PG architecture also has related connection function designs, which mainly involve designing a data structure containing various states, which is then passed as parameters in relevant functions. For example, in the PortalRun related (query statement) code, there is a portal data structure. In the ExecutorRun related code, there is an state data structure. Therefore, this embodiment adopts a largely similar design, creating a new data structure named AQPState and passing it between different functions, thereby achieving global sharing of different variables.

[0096] Furthermore, the control section is mainly used to control whether to exit the AQP query mechanism as a whole, and to prevent the execution of AQP-related logic when the AQP query mechanism cannot be executed (for example, when encountering a DDL statement (data specification statement), the AQP-related logic cannot be executed); that is, to determine whether the logical instruction in the currently executed query framework satisfies the corresponding query logic. If yes, the current logical instruction is executed; if no, the query framework is exited.

[0097] Specifically, in one implementation of this embodiment, step S302 includes the following steps:

[0098] Step S302a: Determine if there are already physically optimized nodes in the query plan;

[0099] Step S302b: Use the existing physical optimization nodes to save the intermediate results of the completed plans for different stages;

[0100] Step S302c: When there is no physical optimization node in the query plan, create a new optimization node and add the new optimization node to the upper layer of each segmentation node to save the intermediate results of the different stages of the plan after execution.

[0101] Step S302d: Obtain the true cardinality of the current node using the saved results.

[0102] Specifically, the intermediate result processing module in this embodiment is mainly used to save the intermediate results of different stages of the executed plan and to obtain the true cardinality of the current Join node through the saved results. The saving of intermediate results is primarily achieved through physical optimization. The PG architecture already has a Material node for physical optimization, and the Sort node and others also have corresponding physical optimization functions. Therefore, this embodiment mainly utilizes existing physical optimization nodes in the query plan within the PG architecture to save intermediate results. When there are no relevant physical optimization nodes in the query plan, this embodiment can introduce a new MaterialAnalyze node, mainly used to add to the upper layer of the splitting node, so that the intermediate results can be saved. The relevant data structure of the introduced new node is as follows: Figure 3 As shown, the generation process of the relevant data structures for new nodes in the PG architecture is as follows: Figure 4 As shown, and the function design of the new node is as follows: Figure 5 As shown.

[0103] Specifically, in one implementation of this embodiment, step S303 includes the following steps:

[0104] Step S313: Correct the previously estimated base number based on the actual base number;

[0105] Step S323: Generate a new path tree based on the modified dynamic programming mechanism and cost model for a new round of execution.

[0106] Specifically, the physical plan update module in this embodiment can perform two main steps: the first is to update the cardinality of the old path tree, and the second is to generate a new path tree.

[0107] In this embodiment, the actual cardinality in the intermediate result processing module is stored in `rows` of `MaterialAnalyzeState`, and then stored in a corresponding data structure of the connection and control module, so that `rows` can be easily accessed in the PG framework code. Then, the original estimated cardinality of the path is corrected based on this actual cardinality. The modified dynamic programming mechanism then regenerates the path from `RelOptInfo` based on PG's cost model, ultimately generating a new Path Tree. This initiates a new round of stage execution.

[0108] For the part about regenerating the path tree, this embodiment only requires modification to the original dynamic programming generation mechanism of PG; the general path generation logic remains unchanged. However, the modified code architecture must still adhere to the following three conditions:

[0109] 1. Paths that have been run must be adopted when regenerating the Path Tree.

[0110] 2. Only regenerate the paths involved in the previous stage.

[0111] 3. Remove as many useless paths as possible.

[0112] Specifically, in one implementation of this embodiment, step S323 includes the following steps:

[0113] Step S323a: Select the deepest path subtree from the previous stage according to the modified dynamic programming mechanism;

[0114] Step S323b: Create a new variable and use the newly created variable as the identifier of the deepest path subtree in the previous stage and the identifier of the corresponding extended path subtree.

[0115] Step S323c: Determine whether the identifier corresponding to each node is different from the identifier of the current path;

[0116] In step S323d, if the identifier is different from the current path identifier, it means that the path does not belong to the deepest path subtree executed in the previous stage, and the corresponding node and path are cleared.

[0117] Step S323e: If the identifier is the same as the current path, then retain the current path to obtain a new path tree for the next stage. (This section corresponds to the one above.)

[0118] In this embodiment, the first condition mentioned above (i.e., the previously executed path must be adopted when regenerating the Path Tree) is mainly to prevent the PG framework system from getting stuck in an infinite loop. Imagine a scenario where, during AQP execution, the first stage's execution phase partitioning module obtains path 1 as the deepest path subtree. Then, when regenerating the path tree, dynamic programming doesn't choose a path tree containing the original path 1, but instead chooses a new path tree containing path 2, which has the same depth as path 1. Then, when entering the next stage, the execution phase partitioning module obtains path 2 as the deepest path subtree. If, during path tree regeneration, dynamic programming comically chooses again a path tree containing the original path 1, and this loop continues in stages 3 and 4, constantly cycling between path 1 and path 2, then AQP will be trapped at the lowest level and unable to exit, thus forming an infinite loop. The specific process is as follows... Figure 6 As shown.

[0119] The problem of path infinite loops essentially stems from the fact that the deepest join path found in the previous stage was not selected during the path filtering process in dynamic programming. Therefore, the solution is to ensure that the path from the previous stage is selected during path filtering. In the intermediate result processing module, this embodiment adds a MA path to the top of the found deepest subtree to generate MA-related data structures. Leveraging this characteristic, a new variable, manode_num, is proposed.

[0120] In this embodiment, `manode_num` represents the total number of MA nodes in the current subtree. The specific generation method is nested within the recursive path tree generation. First, the `manode_num` in the path of each base table is initialized to 0. Then, each path node in the upper layer is generated by adding its child path's `manode_num`. Only when recursively generating an MA node is `manode_num` incremented by one. See Figure 7 for details.

[0121] During path selection, a new condition can be added within PG: when the manode_num of one path tree is greater than the manode_num of another path tree, the path tree with the smaller manode_num will be discarded. This ensures that in the dynamic programming mechanism, the deepest path subtree from the previous stage will always be selected, because only by connecting to it can the final path tree have the most MA nodes. This solves the path selection problem. See Figure 8 for details.

[0122] In this embodiment, the second condition (i.e., regenerating the paths involved in the previous stage) is to narrow the scope of dynamic programming. In the PG framework, even with only four tables joined, the final paths will generate seven major categories, within which various permutations and combinations of Scan nodes and Join nodes need to be considered. Therefore, the number of paths involved in dynamic programming is enormous. If a global dynamic programming operation is performed every time, the entire mechanism will consume a great deal of time. Since only the paths involved in the previous stage need to be corrected, this embodiment does not require running a global dynamic programming operation.

[0123] Specifically, the scope of dynamic programming can be narrowed down to simply regenerating the paths generated from the corrected paths. Since each path has its corresponding RelOptInfo, it is only necessary to first find the RelOptInfo of the deepest subtree of the previous stage (referred to as Deepest RelOptInfo in this embodiment). Then, only those RelOptInfo that are subsets of Deepest RelOptInfo are regenerated. That is, if a RelOptInfo contains a subset of Deepest RelOptInfo, it proves that the path in the current RelOptInfo was generated by connecting the corrected path and other paths. See Figure 9 for details.

[0124] Therefore, in this embodiment, it is only necessary to find all relevant RelOptInfo values ​​and regenerate them. The filtered results are as follows: Figure 10 As shown.

[0125] For subset determination, in PG, `RelOptInfo` uses a bitmap to represent the relations it contains. Each relation has a specific bit; if a relation is contained, its bitmap bit is set to 1; otherwise, it is set to 0. To determine if one `RelOptInfo` is a subset of another, you can NOT each bit of the bitmap of one `RelOptInfo` and then perform a bitwise AND operation with the bitmap of the other `RelOptInfo`. If the result is not equal to 0, it proves that the later `RelOptInfo` is not a subset of the earlier `RelOptInfo`. Specifically... Figure 11 As shown.

[0126] In addition, such as Figure 10 As shown, in RelOptInfo levels below the Deepest RelOptInfo level, it's unnecessary to regenerate the path. This is because the RelOptInfo at a higher level cannot be a subset of the RelOptInfo at a lower level; that is, the path at a lower level cannot be generated from the path at a higher level. Therefore, we can also record the level of its parent RelOptInfo each time the Deepest Join Path is found in a stage. In the next dynamic programming iteration, we only need to traverse upwards from the level above the Deepest RelOptInfo level. Specifically... Figure 12 As shown.

[0127] In this embodiment, the third condition mentioned above aims to eliminate as many useless paths as possible. The detailed explanation of the second condition has already narrowed the scope of dynamic programming. The third condition further narrows the scope of dynamic programming by focusing on useless paths. In Section 2, the number of paths generated by dynamic programming is very large. Furthermore, different stages in AQP continuously call dynamic programming to generate paths. If some useless paths are not cleaned up, a large number of useless paths will accumulate, causing excessive time consumption during dynamic programming path generation.

[0128] To address this issue, this embodiment introduces a new variable, `version`. Figure 13 As shown in this embodiment, version is the identifier of stage, used to identify which stage it belongs to in the Plan node and State node (stage is 1, version is 1).

[0129] In the physics plan update module, dynamic programming is invoked once for each stage. This generates paths for different versions. When regenerating paths, regardless of whether it's a deep tree or a dense tree, the new path is generated by connecting paths in the lower-level RelOptInfo. Subsequent paths are then inserted into the pathlist of the upper-level RelOptInfo. However, for the upper-level RelOptInfo, updating the path generated by the new stage is more efficient than updating the path generated by the old stage. Therefore, the paths of the old version are no longer necessary. Thus, in dynamic programming, before retrieving a RelOptInfo and preparing to generate a new path, ... Figure 14 As shown, if it is determined that the version of this Rel is behind the version of the current stage, the pathlist in RelOptInfo can be cleared.

[0130] Furthermore, such as Figure 15 As shown, for RelOptInfo instances containing a "base MA path," even with a smaller version, the path cannot be cleared (the "base MA path" refers to the MA path actually executed in each stage, added in the intermediate result processing module). This would cause previously executed paths to be lost, violating the condition that executed paths must be adopted when regenerating the Path Tree. Meanwhile, since the "base MA path" does not need to be regenerated... Figure 16 As shown, if a RelOptInfo containing "base MA path" is found, it can be skipped without regenerating the path.

[0131] This embodiment achieves the following technical effects through the above technical solution:

[0132] This embodiment effectively combines machine learning models with traditional cardinality estimation models. It replaces the "overly large" black box and related problems inherent in relying solely on machine learning models for cardinality estimation. Instead of the precise requirement of accurately predicting cardinality, the embodiment focuses on the approximate accuracy of the cardinality estimate for a given node, allowing for some error tolerance. After identifying potentially inaccurate nodes, these nodes are used as split points. The actual cardinality is then retrieved and re-estimated using the traditional cardinality estimation model, achieving re-optimization. This embodiment combines machine learning models with traditional cardinality estimation methods, employing a multi-stage execution of a single SQL query and using Join nodes to segment the query plan. This solves the inaccuracy problem of traditional cardinality estimation methods while reducing the cost of machine learning approaches.

[0133] Exemplary device

[0134] Based on the above embodiments, the present invention also provides a terminal, comprising: a processor, a memory, an interface, a display screen, and a communication module connected via a system bus; wherein, the processor is used to provide computing and control capabilities; the memory includes a storage medium and internal memory; the storage medium stores an operating system and computer programs; the internal memory provides an environment for the operation of the operating system and computer programs in the storage medium; the interface is used to connect to external devices, such as mobile terminals and computers; the display screen is used to display corresponding information; and the communication module is used to communicate with a cloud server or a mobile terminal.

[0135] When the computer program is executed by the processor, it is used to implement a database query optimization method.

[0136] It will be understood by those skilled in the art that Figure 17 The schematic diagram shown is merely a partial structural diagram related to the present invention and does not constitute a limitation on the terminal to which the present invention is applied. A specific terminal may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0137] In one embodiment, a terminal is provided, comprising: a processor and a memory, the memory storing a database query optimization program, which, when executed by the processor, is used to implement the database query optimization method as described above.

[0138] In one embodiment, a storage medium is provided, wherein the storage medium stores a database query optimization program, which, when executed by the processor, is used to implement the database query optimization method as described above.

[0139] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile storage medium, and when executed, it can include the processes of the embodiments of the methods described above. Any references to memory, storage, databases, or other media used in the embodiments provided by this invention can include non-volatile and / or volatile memory.

[0140] In summary, this invention provides a database query optimization method, terminal, and storage medium. The method includes: acquiring table data and a query statement to be executed from the database; inputting features from the table data and the query statement into a training model to predict K nodes that need re-optimization; dividing the K nodes according to a partitioning strategy to obtain K+1 execution stages; and sequentially operating on the K+1 execution stages according to a preset execution logic to complete the query optimization. This invention combines a machine learning model with traditional query optimization methods, primarily employing a multi-stage, step-by-step execution of a single SQL statement. It mainly uses Join nodes predicted by the machine learning model to segment the query plan. Then, it further optimizes the plan at different stages using real-time cardinality. This method solves the problem of inaccurate estimation in traditional cardinality estimation methods under multi-table joins, while avoiding the drawbacks of a single machine learning model.

[0141] It should be understood that the application of the present invention is not limited to the examples above. Those skilled in the art can make improvements or modifications based on the above description, and all such improvements and modifications should fall within the protection scope of the appended claims.

Claims

1. A database query optimization method, characterized in that, include: Obtain table data and query statements to be executed from the database, and input the features extracted from the table data and query statements to the machine learning model to predict K nodes that need to be re-optimized; The K nodes are divided into K+1 execution stages according to the execution stage division strategy; wherein each of the K+1 execution stages is a new execution stage obtained by re-optimization after the previous stage is completed. According to the preset execution logic, query analysis, logic optimization, physical optimization, and planned execution operations are performed sequentially on K+1 execution stages to optimize database queries; The operation of sequentially performing query analysis, logic optimization, physical optimization, and planned execution on K+1 execution stages according to preset execution logic includes: The variables required for the K+1 execution stages are shared globally, and the logical instructions in the execution query framework are controlled. Save the intermediate results of different phases of the plan after execution, and obtain the true cardinality of the current node through the saved results; The cardinality of the existing path tree is updated based on the true cardinality, and a new path tree is generated; The global sharing of variables required for the K+1 execution stages and the control of logical instructions in the execution query framework include: Create data structures for each state and pass these data structures as parameters in the corresponding functions to achieve global sharing of the required variables; Determine whether the logical instructions in the currently executing query framework satisfy the corresponding query logic; If yes, then control the execution of the current logical instruction; If not, then exit the query framework.

2. The database query optimization method according to claim 1, characterized in that, The process of obtaining table data from the database and the query statement to be executed includes, prior to: Obtain the training dataset and query statement; The machine learning model is trained using the training dataset and the query statement to obtain the trained machine learning model.

3. The database query optimization method according to claim 1, characterized in that, The data structure includes one or more combinations of the following: portal data structure, state data structure, and AQPState data structure.

4. The database query optimization method according to claim 1, characterized in that, The process of saving intermediate results from different stages of the completed plan and obtaining the true cardinality of the current node using the saved results includes: It has been confirmed that there are already physically optimized nodes in the query plan; The intermediate results of different phases of the plan are saved using the existing physical optimization nodes. When there are no physical optimization nodes in the query plan, create new optimization nodes and add the new optimization nodes to the upper layer of each partition node to save the intermediate results of different stages of the executed plan. The true cardinality of the current node is obtained by saving the results.

5. The database query optimization method according to claim 1, characterized in that, The step of updating the cardinality of the existing path tree based on the true cardinality and generating a new path tree includes: The previously estimated base number was revised based on the actual base number; A new path tree is generated based on the modified dynamic programming mechanism and cost model for a new round of execution.

6. The database query optimization method according to claim 5, characterized in that, The step of generating a new path tree based on the modified dynamic programming mechanism and cost model includes: The deepest path subtree in the previous stage is selected according to the modified dynamic programming mechanism; Create a new variable and use it as the identifier of the deepest path subtree in the previous stage and the identifier of the corresponding extended path subtree. Determine whether the identifier corresponding to each node is different from the identifier of the current path; If the identifier is different from the current path identifier, it means that the path does not belong to the deepest path subtree executed in the previous stage, and the corresponding node and path are cleared. If the identifier is the same as the current path, the current path is retained to obtain a new path tree for the next stage.

7. A terminal, characterized in that, include: The processor and memory, wherein the memory stores a database query optimization program, which, when executed by the processor, is used to implement the database query optimization method as described in any one of claims 1-6.

8. A storage medium, characterized in that, The storage medium is a computer-readable storage medium, and the storage medium stores a database query optimization program, which, when executed by a processor, is used to implement the database query optimization method as described in any one of claims 1-6.

Images