Teaching efficiency-driven query plan suboptimum diagnosis method and system, electronic equipment and storage medium

Abstract

The invention discloses a teaching efficiency-driven query plan suboptimal diagnosis method and system, and mainly solves the problems that the difference between cost distribution and actual execution time distribution is difficult to quantify and estimate by depending on a single plan and the root of a cardinal number estimation error cannot be positioned in the prior art. According to the implementation scheme, the method comprises the steps that for SQL query given by a user, a database query optimizer is expanded based on an improved QuickPick algorithm to obtain a plurality of candidate query plans of the database query optimizer; acquiring estimated cost distribution of the plurality of candidate query plans, and acquiring actual execution time distribution by adopting a progressive approximation strategy; quantizing the difference between the estimated cost distribution and the actual execution time distribution; positioning an operator with a significant cardinal number estimation error based on query plan execution details; and feeding back a diagnosis conclusion based on the cardinality estimation error based on the distribution difference and the identified operator. According to the method, the query plan suboptimum problem can be diagnosed by quantifying the distribution difference and positioning the operator, the reason of the cardinality estimation error is provided, and the method can be used for optimizer performance diagnosis.

Description

Technical Field

[0001] This invention belongs to the field of database technology, specifically relating to a method and system for diagnosing query plan suboptimality, which can be used for database query optimizer performance diagnosis, cardinality estimation algorithm tuning, database system operation and maintenance decision support, and database kernel knowledge teaching. Background Technology

[0002] In relational database management systems, the core task of the query optimizer is to transform user-submitted SQL statements into an optimal query plan. Modern optimizers generally employ a cost-based model. First, they enumerate a large number of possible candidate plans. Then, based on an abstract cost model, they estimate the execution cost of each plan and finally select the query plan with the lowest estimated cost for execution. In this process, the key input to the cost model is cardinality estimation, which is a prediction of the number of rows processed and output for each operator.

[0003] However, due to the complexity of data distribution, imperfect statistical information, and simplifying assumptions in the optimizer, cardinality estimation often contains biases. This cardinality estimation error can cause the optimizer to choose a suboptimal query plan that estimates the lowest cost but has a long actual execution time. This optimizer suboptimality is one of the core issues causing database performance to fall short of expectations. Therefore, by diagnosing and quantifying this problem, optimizer algorithms can be improved and database system performance can be enhanced.

[0004] Patent document CN112069202A discloses "A method, system, device, and medium for SQL performance analysis based on tracing technology." This method obtains the actual SQL statements executed by the application through a tracing plugin and uses the database's native query plans to assess the risks of the SQL statements. While this method can automatically collect real requests and perform performance diagnostics on individual query plans, its performance analysis relies entirely on the single query plan ultimately selected by the optimizer. Therefore, it cannot systematically explore the set of suboptimal query plans that the optimizer might overlook, nor can it reveal the optimizer's own suboptimal decision-making and potential cardinality estimation problems through quantitative indicators.

[0005] Patent document CN102362276A discloses "a method for testing the efficiency and stability of a database query engine." This method calculates the overall efficiency index of the optimizer by saving all possible query plans and categorizing them into different performance buckets based on estimated costs and actual behavior. While this method can compare estimates with actual behavior, its diagnostics cannot accurately quantify the overall difference between the estimated cost and actual execution time distributions, nor can it automatically pinpoint specific operators with significant cardinality estimation errors that lead to suboptimal performance. Summary of the Invention

[0006] The purpose of this invention is to overcome the shortcomings of the existing technology and propose a teaching efficiency-driven query plan suboptimal diagnosis method and system. This method quantifies the global difference between the estimated cost distribution and the actual execution time distribution, and locates the specific operators with significant cardinality estimation errors. It transforms the complex quantitative diagnosis results into an intuitive explanation of the causes of cardinality estimation errors, providing an actionable basis for optimizer algorithm improvement and database administrators to make targeted improvements.

[0007] The technical approach to achieving the objective of this invention is as follows: Multiple candidate query plans for a given SQL query are obtained through database expansion based on the QuickPick algorithm; the estimated cost distribution and the actual execution time distribution are obtained by exploring a batch of candidate query plan sets and employing a progressive approximation strategy; the difference between the estimated cost distribution and the actual execution time distribution is quantified by calculating the differences in distribution shape and bucket composition; the error value of each operator is calculated based on the execution details of multiple candidate query plans to locate operators with significant cardinality estimation errors; and an explanation for the cause of the cardinality estimation error is derived through the distribution differences and the identified operator with the largest cardinality estimation error.

[0008] Based on the above ideas, the technical solution of the present invention includes:

[0009] 1. A method for diagnosing the suboptimality of query plans driven by teaching effectiveness, characterized in that it includes:

[0010] (1) For the SQL query given by the user, the database query optimizer is extended based on the improved QuickPick algorithm to obtain multiple candidate query plans;

[0011] (2) Obtain the estimated cost distribution of multiple candidate query plans, and use a progressive approximation strategy to obtain the actual execution time distribution;

[0012] (3) The difference between the quantitatively estimated cost distribution and the actual execution time distribution;

[0013] (4) Calculate the error value of each operator based on the execution details of multiple candidate query plans, and locate the operators with significant cardinality estimation errors;

[0014] (5) Based on the distribution differences and the identified operators, an explanation for the cause of the cardinality estimation error is derived, and the diagnostic conclusion is fed back to the user through the system interface so that optimizer tuning or technical improvement can be carried out.

[0015] Furthermore, step (2), obtaining the estimated cost distribution of multiple candidate query plans, involves executing the EXPLAIN command provided by the database management system on each query plan and extracting the total cost from the execution result as the estimated cost of that query plan. .

[0016] Furthermore, step (2) employs a progressive approximation strategy to obtain the actual execution time distribution, the implementation of which includes:

[0017] (2a) Estimated cost for multiple candidate query plans Sort;

[0018] (2b) Adaptive uniform sampling strategy is adopted in the sorted candidate query plan sequence, and the initial sampling number is set. Sampling interval Choose an initial subset ;

[0019] (2c) Use the EXPLAIN ANALYZE command provided by the database management system to perform the initial subset. The query plan in the query plan is used to obtain its actual execution time;

[0020] (2d) Based on the actual execution time of the query plan in this subset, construct an initial approximate histogram of the actual execution time distribution. ;

[0021] (2e) Run the EXPLAIN ANALYZE command to obtain more actual execution times of the query plans, and gradually refine the approximate histogram. And update the actual execution time distribution in real time.

[0022] Furthermore, in step (4), the operator with a significant cardinality estimation error is located by estimating the number of rows based on the k-th operator. and the actual number of rows for the k-th operator Calculated.

[0023] 2. A query plan suboptimal diagnosis system driven by teaching effectiveness, characterized in that it includes:

[0024] The batch execution plan generation module receives SQL queries and generates multiple candidate query plans based on the improved QuickPick algorithm.

[0025] Distribution generation module: used to obtain the estimated cost distribution and actual execution time distribution of multiple candidate query plans;

[0026] Progressive approximate distribution generation module: used to progressively obtain an approximate histogram of the actual execution time distribution;

[0027] Cardinality estimation analysis module: used to quantify the difference between the estimated cost distribution and the actual execution time distribution, and to locate operators with significant cardinality estimation errors based on the execution details of candidate query plans;

[0028] Interactive Q&A module: Used to match operators based on distribution difference measures and location to a predefined misclassification rule base and provide users with explanations about the causes of cardinality estimation errors.

[0029] Visual User Interface Module: This module receives SQL queries, visualizes the estimated cost distribution and actual execution time distribution in the form of histograms, and displays diagnostic conclusions.

[0030] 3. An electronic device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, when the processor executes the program, it implements the teaching effectiveness-driven query plan suboptimal diagnosis method as described in any one of claims 1 to 8.

[0031] 4. A non-transitory computer-readable storage medium, characterized in that the non-transitory computer-readable storage medium stores computer instructions, the computer instructions being used to cause the computer to execute the teaching effectiveness-driven query plan suboptimal diagnosis method as described in any one of claims 1 to 8.

[0032] Compared with the prior art, the present invention has the following advantages:

[0033] Firstly, because this invention collects and displays the estimated cost distribution and actual execution time distribution of multiple candidate query plans through an incremental approximation strategy, it can explore the overall impact of the evaluation cardinality estimation error on the optimizer's selection of query plans from a global perspective.

[0034] Secondly, this invention employs a multi-dimensional analysis mechanism that considers differences in distribution shape, differences in bucket composition, and operator location. By quantifying the magnitude of distribution differences and operator cardinality estimation errors, it can identify operators with significant cardinality estimation errors, diagnose suboptimal problems in the optimizer, and provide developers and database administrators with optimization goals and feasible optimizer improvement guidelines. Attached Figure Description

[0035] Figure 1 This is a flowchart illustrating the implementation of the query plan suboptimality diagnosis method driven by teaching effectiveness of the present invention.

[0036] Figure 2 This is a block diagram of the query plan suboptimal diagnosis system driven by teaching effectiveness of the present invention;

[0037] Figure 3 This is a schematic diagram of the electronic device of the present invention. Detailed Implementation

[0038] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, other embodiments obtained by those skilled in the art without creative effort should all fall within the protection scope of the present invention.

[0039] Example 1: A Method for Diagnosing the Suboptimality of Query Plans Driven by Teaching Efficacy

[0040] Reference Figure 1 The implementation steps of this example include the following:

[0041] Step 1: Generate multiple candidate query plans.

[0042] Existing algorithms for generating candidate query plans include greedy algorithms, QuickPick algorithms, and dynamic programming algorithms. This example uses, but is not limited to, the QuickPick algorithm. QuickPick is a random search algorithm used to quickly generate optimal query plans in a database query optimizer. However, its primary design goal is to find a single low-cost plan, and overly strict traditional pruning strategies ignore many query plans with high diagnostic value. This example improves the QuickPick algorithm by adjusting the search strategy and relaxing the pruning of suboptimal paths, thereby systematically generating a diverse and representative set of candidate query plans. Based on the improved QuickPick algorithm, the database query optimizer is extended to obtain multiple candidate query plans for a user-given SQL query. Specific implementations include:

[0043] 1.1) Based on the join_search_hook hook interface provided by the query optimizer, which is oriented towards join order enumeration, the original join order search logic of the optimizer is replaced, thereby taking over the join order selection process inside the optimizer and forcibly changing the query plan generation mechanism.

[0044] 1.2) From the query structure root->parse of the database management system, the query parse tree is recursively checked using a depth-first traversal method to extract all equi-join conditions, and each equi-join is abstracted as a query plan connection edge, which is finally formed into a linked list E of connection edges, which serves as the initial input set for the algorithm to enumerate the random connection order.

[0045] 1.3) Initialize variables:

[0046] Minimum total cost Set to maximum floating-point number This is used to record the lowest estimated total cost among all generated plans during the search process;

[0047] Copy the original linked list E to obtain the working linked list. This is used to randomly select and remove connecting edges in a single search iteration while keeping the original linked list E unchanged, in order to support multiple iterative searches;

[0048] Build an initial query plan Its initial content is a set consisting of all the base tables that need to be queried. , serving as the starting point for the bottom-up construction of the connection sequence;

[0049] Define the current best plan Set it to null to store the query plan with the lowest estimated cost found in all iterations;

[0050] 1.4) Before exceeding the set maximum number of iterations or the preset time limit, repeatedly execute the following steps:

[0051] 1.4.1) From the current work list A connection condition e is randomly selected to achieve randomness and diversity of plans during the search process for the connection order;

[0052] 1.4.2) Remove the selected join condition e from the working list Remove it from the current query plan path to avoid reusing the connection edge.

[0053] 1.4.3) Call the join predicate addition function ADDJOIN(q,e) to check whether the two relations involved in the join condition e are distributed in two different subplan trees of query q:

[0054] If so, it means that it is a valid connection. Then, the database management system's function make_join_rel is called to generate the connection relationship join_rel. The lowest cost path setting function set_cheapest is executed to select the connection method with the lowest estimated cost from the available physical connection methods for the newly generated connection relationship. The cost of the new subtree is used to replace the cost of the original two subtrees, and the total cost of the current planning tree is updated incrementally.

[0055] If not, it means that the relationship involved in the join condition e is already in the same subtree or is a self-join. In this case, the join condition will not be processed for the time being, and will be updated after the subsequent algorithm generates join_rel that covers the two ends of the relationship.

[0056] 1.4.4) Determine if the working linked list is empty, i.e., whether all relations have been connected to form a complete plan tree:

[0057] If so, it indicates that all connection conditions in the original connection edge E have been processed, and the current query plan q has successfully formed a complete plan tree. The current query plan q is recorded as a valid candidate query plan, and its estimated total cost is compared with the recorded lowest total cost r: if the estimated total cost of the current query plan q is lower, then the current best plan is... Update to the plan generated this time Minimum total cost Update to the total cost of the plan Conversely, no action is taken.

[0058] If not, return to 1.4.1) to continue the process of randomly selecting join conditions and constructing join relationships;

[0059] 1.5) Link the work list Reset to the initial linked list E, and change the query plan. Reset to initial A new round of random search begins. Each complete iteration produces an effective and correct query plan. By repeatedly running the entire random search process multiple times before exceeding the set number of iterations or time limit, a large number of different and effective candidate query plans are obtained.

[0060] Step 2: Obtain the estimated cost distribution and the actual execution time distribution.

[0061] 2.1) For each candidate query plan set, execute the EXPLAIN command provided by the database management system, and extract the total cost from the execution result as the estimated cost of that query plan. ;

[0062] 2.2) Arrange candidate query plans according to the estimated cost The sizes are sorted in ascending order to group together plans with similar estimated costs;

[0063] 2.3) From the sorted candidate query plan sequence, an adaptive uniform sampling strategy is used to select the set of query plans for actual execution, and an initial sampling number is set. Sampling interval A uniform initial subset is selected from the sorted sequence. This subset covers query plans at different levels of estimated cost, from small to large, and is used to initially depict the shape of the actual execution time distribution.

[0064] 2.4) Use the EXPLAIN ANALYZE command provided by the database management system to actually execute the initial subset. For each query plan in the dataset, obtain its actual execution time;

[0065] 2.5) Based on the actual execution time of the query plan in this subset, construct an initial approximate histogram of the actual execution time distribution. This histogram provides a preliminary overview of the actual execution time distribution;

[0066] 2.6) Run the EXPLAIN ANALYZE command to obtain more actual execution times of query plans according to the preset sampling strategy, and gradually update and refine the approximate histogram based on the latest data. It also updates the actual execution time distribution in real time, thereby gradually approaching the true execution time distribution while ensuring efficiency.

[0067] Step 3: Quantify the distribution differences.

[0068] 3.1) Estimated cost for all executed query plans and actual execution time Minimum-maximum normalization is performed to map the values ​​to a standard interval, eliminating dimensional differences and making the two distributions comparable. This yields the normalized estimated cost and execution time.

[0069] ,

[0070] in, It is the first The estimated cost of a query plan, It is the first The actual execution time of each query plan. , This represents the number of query plans that have been executed. It is the normalized first The estimated cost of a query plan, It is the normalized first The actual execution time of each query plan;

[0071] 3.2) Normalize the data and Mapping to N identical buckets, and by counting the number of samples falling into each bucket, we obtain the probability distribution for estimating the cost. Probability distribution of actual execution time :

[0072] ,

[0073] ,

[0074] in The estimated cost sample is in the th... The percentage in each bucket

[0075] The actual execution time sample is in the 1st The percentage in each bucket

[0076] , It refers to the number of buckets;

[0077] 3.3) Calculate the KL divergence between the estimated cost probability distribution P and the actual execution time probability distribution Q. Used to quantify the difference in shape between two distributions:

[0078] ,

[0079] The KL divergence, as an asymmetric distance metric, can be used to quantify the degree of difference between two distribution shapes. The larger the value, the greater the difference in distribution shapes and the higher the degree of distortion in the estimation.

[0080] 3.4) Calculate the Jaccard similarity between the estimated cost group and the actual execution time group:

[0081] To quantify the differences in composition within the two distribution buckets, this example introduces the Jaccard similarity, which measures the similarity between two sets. Its value equals the size of the intersection of the two sets divided by the size of their union. This metric reflects the consistency of the optimizer's judgment on the suboptimal nature of the query plan within the cost interval. A higher Jaccard similarity indicates a greater overlap between the estimated cost and the actual execution time within that bucket, and better estimation accuracy from the optimizer. The calculation formula is as follows:

[0082] ,

[0083] in Is it falling into the first The set of query plans corresponding to the estimated cost of each bucket. Is it falling into the first The set of query plans corresponding to the actual execution time of each bucket. , It refers to the number of buckets;

[0084] 3.5) Calculate the average Jaccard similarity for all buckets. This metric reflects the degree of overlap between the set of query plans whose estimated costs and actual execution times fall within the same cost range. It is a measure of the difference in the composition of the two distribution buckets, and the calculation formula is as follows:

[0085] .

[0086] Step 4, operators that significantly affect the localization base estimation error.

[0087] 4.1) Use the EXPLAIN ANALYZE command provided by the database management system to actually run the candidate query plan to obtain accurate cardinality and execution information for each operator. This command not only returns the structure and estimation information of the query plan, but also returns the actual execution statistics of each operator in the query plan, including the actual number of rows returned and the actual execution time.

[0088] 4.2) Analyze the execution details obtained from running the EXPLAIN ANALYZE command, and extract the estimated row count statistics for each operator in the query plan tree. and actual row count statistics ,in It is the optimizer's prediction of the number of output rows for the k-th operator when generating the plan. It is the number of rows actually output by the k-th operator during the actual execution of the query plan. , It is the number of operators;

[0089] 4.3) Use q-error as a metric to quantify the cardinality estimation error of each operator in the database query plan. It measures the deviation between the database query optimizer's predicted number of output rows for a certain operator and the actual output value of that operator during query execution. The calculation formula is as follows:

[0090] ,

[0091] in It is the q-error value of the k-th operator. It is calculated by taking the maximum value of the ratio of the estimated number of rows to the actual number of rows for the k-th operator. When the q-error is closer to 1, it means that the optimizer's cardinality estimate for that operator is more accurate. The larger the q-error, the higher the degree of cardinality estimate error, thus locating the specific operator that causes the query plan to be suboptimal.

[0092] Step 5: Obtain and report the diagnostic conclusion.

[0093] 5.1) Based on a deep understanding of the knowledge in the database kernel, this example summarizes the typical distribution difference patterns between estimated costs and actual execution times, as well as operator characteristics. These patterns and characteristics are associated with predefined cardinality estimation error causes to construct a predefined error classification rule base.

[0094] 5.2) The quantitative distribution difference results are used as input and matched with a predefined error classification rule base. Through searching and comparison, the error type and pattern of the suboptimal problem of the current query plan are determined.

[0095] 5.3) Analyze the suboptimal problem of the query optimizer and generate analytical diagnostic conclusions. These conclusions include two aspects:

[0096] First, based on the matching results between the distribution differences and the rule base, we can obtain the error patterns corresponding to the query optimizer's selection of suboptimal plans;

[0097] Second, based on the matching results of the operator location with significant cardinality estimation error and the rule base, feedback is provided on the possible reasons for the deviation between the operator that causes the query optimizer's suboptimal performance and the operator itself.

[0098] 5.4) Cache the complete analysis and diagnosis results, and when the user selects the corresponding diagnosis problem, the corresponding diagnosis results will be fed back to the user.

[0099] It should be noted that the step numbers in the above steps and claims are only for the purpose of clearly describing the embodiments of the present invention and facilitating understanding, and their order is not limited.

[0100] Example 2: A query plan suboptimal diagnosis system driven by teaching effectiveness;

[0101] Reference Figure 2 This example includes a batch execution plan generation module 1, a distribution generation module 2, an incremental approximate distribution generation module 3, a cardinality estimation and analysis module 4, an interactive question-and-answer module 5, and a visual user interface module 6. The working principle of the entire system is as follows:

[0102] The batch execution plan generation module 1 is used to receive SQL queries from the visual user interface module 6, explore the query plan space multiple times based on the improved QuickPick algorithm, and generate multiple effective and diverse candidate query plans to be passed to the distribution generation module 2.

[0103] The distribution generation module 2 is used to receive a set of candidate query plans from the batch execution plan generation module 1, obtain the estimated cost distribution of multiple candidate query plans, and call the progressive approximate distribution generation module 3 to gradually obtain the actual execution time distribution, construct the estimated cost distribution histogram and the actual execution time distribution histogram and output them to the visualization user interface module 6, and pass the data of the estimated cost distribution and the actual execution time distribution to the cardinality estimation analysis module 4.

[0104] The progressive approximate distribution generation module 3 is used to obtain approximate histogram data of the actual execution time distribution using a progressive strategy after being called by the distribution generation module 2, and return the data to the distribution generation module 2.

[0105] The cardinality estimation analysis module 4 is used to receive two sets of distribution data from the distribution generation module 2, obtain the EXPLAIN ANALYZE command execution details of the query plan, quantify the difference between the estimated cost distribution and the actual execution time distribution, locate the operator with significant cardinality estimation error based on the execution details of the candidate query plan, and send the difference results and operator results to the interactive question and answer module 5.

[0106] The interactive question-and-answer module 5 is used to receive the quantitative difference value and the operator with significant cardinality estimation error from the cardinality estimation analysis module 4, and at the same time receive the user question request from the visualization user interface module 6. Based on the distribution difference measurement and the operator matching of the location, the module sends the result to the visualization user interface module 6, thereby providing the user with an explanation of the cause of the cardinality estimation error.

[0107] The visualization user interface module 6 is used to receive SQL queries from a relational database, receive and display the estimated cost distribution histogram and the actual execution time distribution histogram from the distribution generation module 2 to the user, and receive diagnostic conclusions from the interactive question-and-answer module 5 and provide feedback to the user.

[0108] It should be noted that the above functional modules can be implemented, in whole or in part, through software, hardware, firmware, or any combination thereof. When implemented in software, they can be implemented, in whole or in part, as a program instruction product. A program instruction product includes one or a set of program instructions. When the program instructions are loaded and executed on a computer, the described process or function is generated, in whole or in part. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The program instructions can be stored in a computer-readable storage medium or transferred from one computer-readable storage medium to another.

[0109] The modules shown in this embodiment can be directly coupled or communicate with each other through indirect coupling or communication connections via interfaces, devices, or modules. The various functional modules and sub-modules in this embodiment can dynamically reside within a single processing unit, or two or more modules can dynamically reside within a single processing unit. When these dynamic components are implemented as software functional modules and sold or used as independent products, they can also be stored in a computer-readable storage medium. This storage medium can be a memory, disk, or optical disc, etc.

[0110] Example 3: Electronic device for diagnosing the suboptimality of query plans driven by teaching effectiveness;

[0111] Reference Figure 3The electronic device in this example includes a processor 31, a memory 32, an input / output interface 33, a communication interface 34, and a bus 35. The processor 31, memory 32, input / output interface 33, and communication interface 34 are interconnected within the device via the bus 35. The working principle of the entire device is as follows:

[0112] The processor 31 can be implemented using a general-purpose CPU, microprocessor, application-specific integrated circuit, or one or more integrated circuits, and is used to execute relevant programs to implement the technical solutions provided in the embodiments of this specification.

[0113] The memory 32 can be implemented in the form of read-only memory (ROM), random access memory (RAM), static storage device, dynamic storage device, etc., and the memory 32 can store the operating system and other applications. When the technical solutions provided in the embodiments of this specification are implemented by software or firmware, the relevant program code is stored in the memory 32 and is called and executed by the processor 31.

[0114] Input / output interface 33 is used to connect input / output modules to realize information input and output. Input / output modules can be configured as components in the device or externally connected to the device to provide corresponding functions. Input devices can include keyboards, mice, touch screens, microphones, various sensors, etc., and output devices can include displays, speakers, vibrators, indicator lights, etc.

[0115] The communication interface 34 is used to connect the communication module to enable communication and interaction between this device and other devices. The communication module can communicate via wired means such as USB or Ethernet cable, or via wireless means such as mobile network, WIFI, or Bluetooth.

[0116] Bus 35 includes a path for transmitting information between the device's processor 1, memory 2, input / output interface 3, and communication interface 4;

[0117] It should be noted that although the above device only shows the processor 31, memory 32, input / output interface 33, communication interface 34 and bus 35, in specific implementation, the device may also include other components necessary for normal operation. In addition, those skilled in the art will understand that the above device may only include the components necessary for implementing the embodiments of this specification, and may not necessarily include all the components shown in the figures.

[0118] Example 4: Teaching effectiveness-driven query plan suboptimal diagnosis using a non-transitory computer-readable storage medium;

[0119] This invention provides a non-transitory computer-readable storage medium storing multiple instructions that can be loaded by a processor to execute the steps in the query plan suboptimal diagnosis method provided in this invention.

[0120] The non-transitory computer-readable medium of this embodiment includes permanent and non-permanent, removable and non-removable media, which can be used to store information by any method or technology. The information can be computer-readable instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic disk storage or other magnetic storage devices or any other non-transfer medium that can be used to store information that can be accessed by a computing device.

[0121] The above descriptions are merely a few specific examples of the present invention and do not constitute any limitation on the present invention. Obviously, those skilled in the art, after understanding the content and principles of the present invention, may make various modifications and changes in form and detail without departing from the principles and structure of the present invention. For example: ① The QuickPick algorithm used in the batch execution plan generation module 1 can be replaced with other randomized or heuristic plan search algorithms, such as random search or iterative optimization; ② The KL divergence and Jaccard similarity used in the cardinality estimation analysis module 4 can be replaced with other distribution difference metrics, such as Wasserstein distance or Jensen-Shannon divergence; ③ The form of the database extension module can be changed to an independent service or cloud-native architecture, communicating with a relational database via API; ④ The objective of diagnostic analysis can be expanded from cardinality estimation error to the estimation error analysis of other parameters of the cost model; ⑤ The predefined rule base matching used in the interactive question-answering module 5 can be replaced with natural language interpretation generation based on machine learning or large language models. However, these modifications and changes based on the ideas of the present invention are still within the scope of protection of the claims of the present invention.

Claims

1. A method for diagnosing the suboptimality of query plans driven by teaching effectiveness, characterized in that, include: (1) For the SQL query given by the user, the database query optimizer is extended based on the improved QuickPick algorithm to obtain multiple candidate query plans; (2) Obtain the estimated cost distribution of multiple candidate query plans, and use a progressive approximation strategy to obtain the actual execution time distribution; (3) The difference between the quantitatively estimated cost distribution and the actual execution time distribution; (4) Calculate the error value of each operator based on the execution details of multiple candidate query plans, and locate the operators with significant cardinality estimation errors; (5) Based on the distribution differences and the identified operators, an explanation for the cause of the cardinality estimation error is derived, and the diagnostic conclusion is fed back to the user through the system interface so that optimizer tuning or technical improvement can be carried out.

2. The method according to claim 1, characterized in that, The implementation of (1) by extending the database query optimizer based on the improved QuickPick algorithm includes: (1a) Based on the hook interface join_search_hook provided by the query optimizer, the join order selection process of the optimizer is taken over, and the generation of the query plan is changed; (1b) From the query structure root->parse of the database management system, the query parse tree is traversed in depth first to extract all equi-join conditions and form a linked list E of query plan join edges. (1c) Initialize variables: Minimum total cost Set to maximum floating-point number ; Copy the linked list E to obtain the working linked list. ; Build an initial query plan Its initial content is a set consisting of all the base tables that need to be queried. ; Define the current best plan Set it to an empty value; (1d) Before exceeding the set number of iterations or time limit, repeatedly execute the following steps: (1d1) From the working list Randomly select a connection condition e; (1d2) Remove the join condition e from the working list Remove from; (1d3) Call the join predicate addition function ADDJOIN(q,e) to check whether the two relations involved in the join condition e are distributed in the query. In two different sub-plan trees: If so, the database management system's `make_join_rel` function is called to generate the join relationship `join_rel`, and the `set_cheapest` function is executed to select the best join method for the new join. Simultaneously, the costs of the two subtrees are replaced with the costs of the new subtree, and the total cost of the plan tree is incrementally updated. ; If not, i.e., the relation involved in the join condition e is already in the same subtree or is a self-join, then the processing is delayed until the join_rel covering its two ends is generated before updating; (1d4) Determine the working linked list Whether it is empty indicates that all relations have been connected into a complete plan tree: If so, record the plan. For a valid candidate query plan, execute (1e): If not, return to (1d1) and continue execution; (1e) The total cost of the current planned tree With minimum total cost Comparison: like Then the current best plan Update to the plan generated this time Minimum total cost Update to the total cost of the plan Then execute (1f); like If so, then execute (1f) directly; (1f) Link the work list Reset to the initial linked list E, and change the query plan. Reset to initial This initiates a new round of random search. Each complete iteration produces an effective and correct query plan, and through multiple runs, a large number of different and effective candidate query plans are obtained.

3. The method according to claim 1, characterized in that: (2) Obtaining the estimated cost distribution of multiple candidate query plans involves executing the EXPLAIN command provided by the database management system for each query plan and extracting the total cost from the execution result as the estimated cost of that query plan. ; The operator with significant cardinality estimation error is located in (4) and its formula is as follows: ， in, It is the estimated row number for the k-th operator. It is the actual line number of the k-th operator. , It represents the number of operators.

4. The method according to claim 1, characterized in that, The step (2) of obtaining the actual execution time distribution using a progressive approximation strategy includes the following implementation: (2a) Estimated cost for multiple candidate query plans Sort; (2b) Adaptive uniform sampling strategy is adopted in the sorted candidate query plan sequence, and the initial sampling number is set. Sampling interval Choose an initial subset ; (2c) Use the EXPLAIN ANALYZE command provided by the database management system to perform the initial subset. The query plan in the query plan is used to obtain its actual execution time; (2d) Based on the actual execution time of the query plan in this subset, construct an initial approximate histogram of the actual execution time distribution. ; (2e) Run the EXPLAIN ANALYZE command to obtain more actual execution times of the query plans, and gradually refine the approximate histogram. And update the actual execution time distribution in real time.

5. The method according to claim 1, characterized in that, The difference between the estimated cost distribution and the actual execution time distribution in (3) includes quantifying the difference in the shape of the two distributions and quantifying the difference in the composition of the two distribution buckets. The implementation steps include: (3a) Estimated cost for all query plans and actual execution time Perform min-max normalization: ， in, It is the first The estimated cost of a query plan, It is the first The actual execution time of each query plan. M is the number of query plans that have been executed. It is the normalized first The estimated cost of a query plan, It is the normalized first The actual execution time of each query plan; (3b) Map the normalized data into N identical buckets to obtain the probability distribution of the estimated cost. Probability distribution of actual execution time : ， ， in The estimated cost sample is in the th... The percentage in each bucket The actual execution time sample is in the 1st The percentage in each bucket N is the number of buckets; (3c) Calculate the KL divergence between the estimated cost probability distribution P and the actual execution time probability distribution Q as a measure of the difference in the shapes of the two distributions. : ， (3d) For the first A bucket, making This is the set of query plans corresponding to the estimated cost of falling into this bucket. For the set of query plans corresponding to the actual execution time that falls into this bucket, calculate... and Jaccard similarity between them: ， (3e) Calculate the average Jaccard similarity of all N buckets as a measure of the difference in intra-bucket composition between the two distributions. : 。 6. The method according to claim 1, characterized in that, The calculation of the error value for each operator based on the execution details of multiple candidate query plans in (4) includes the following implementation: (4a) Use the EXPLAIN ANALYZE command in the database management system to obtain the execution details of the candidate query plans; (4b) For each operator in a query plan, parse the execution details to obtain its estimated number of rows. and actual number of rows ; (4c) Calculate the q-error value for each operator. : ， in It is the estimated row number for the k-th operator. It is the actual line number of the k-th operator. , It represents the number of operators.

7. The method according to claim 1, characterized in that, The explanation for the cardinality estimation error derived in (5) based on the distribution difference and the identified operators includes: (5a) Based on the knowledge induction typical distribution difference patterns and operator features in the database kernel, construct a predefined error classification rule base; (5b) Match the distribution difference with the storage distribution difference measure to the predefined error classification rule base of the error cause mapping relationship; (5c) Based on the distribution difference matching results and operator positioning results, generate an analytical and diagnostic conclusion on the causes of cardinality estimation error. This conclusion includes the specific problems in the optimizer program selection and the causes of the problems.

8. A query plan suboptimal diagnosis system driven by teaching effectiveness, characterized in that, include: The batch execution plan generation module receives SQL queries and generates multiple candidate query plans based on the improved QuickPick algorithm. Distribution generation module: used to obtain the estimated cost distribution and actual execution time distribution of multiple candidate query plans; Progressive approximate distribution generation module: used to progressively obtain an approximate histogram of the actual execution time distribution; Cardinality estimation analysis module: used to quantify the difference between the estimated cost distribution and the actual execution time distribution, and to locate operators with significant cardinality estimation errors based on the execution details of candidate query plans; Interactive question-and-answer module: Used to match operators based on distribution difference measures and location, and provide users with explanations about the causes of cardinality estimation errors. Visual User Interface Module: This module receives SQL queries, visualizes the estimated cost distribution and actual execution time distribution in the form of histograms, and displays diagnostic conclusions.

9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the program, it implements the teaching effectiveness-driven query plan suboptimal diagnosis method as described in any one of claims 1 to 8.

10. A non-transitory computer-readable storage medium, characterized in that, The non-transitory computer-readable storage medium stores computer instructions for causing the computer to execute the teaching effectiveness-driven query plan suboptimal diagnosis method as described in any one of claims 1 to 8.

Images