A method, apparatus, device and medium for testing a database optimizer

Abstract

The application discloses a kind of database optimizer's test method, device, equipment and medium.The method comprises: according to the structural features of target SQL statement and the preset optimizer decision knowledge base, determine test case set, each test case includes candidate execution plan, data characteristic parameter and plan locking mode;For each test case, according to data characteristic parameter generation test data set, iteratively execute target SQL statement, according to optimizer tracking information correction test data set, until the actual execution plan of target SQL statement and candidate execution plan are consistent;Based on each test data set, according to default execution mode and intervention execution mode respectively execute target SQL statement, collect actual execution plan and execution performance index under each execution mode to verify test case;According to the test case of verification, build baseline library;If database version changes, according to baseline library regression comparison.The automation and accurate test of optimizer are realized.

Description

Technical Field

[0001] This invention relates to the field of database optimizer evaluation technology, and in particular to a database optimizer testing method, apparatus, equipment, and medium. Background Technology

[0002] The database optimizer is a core component of a database management system, responsible for selecting the optimal execution plan for a given SQL (Structured Query Language) statement. Based on cost and rule models, the optimizer estimates the computational cost and resource overhead of each plan in the candidate plan space, ultimately selecting the execution plan with the lowest estimated cost. The quality of the optimizer's decision directly impacts database query performance; therefore, thorough testing and validation of the optimizer is a crucial aspect of ensuring database product quality.

[0003] Currently, the industry mainly uses the following methods for testing and validating database optimizers: (1) Fixed test cases based on manual construction. Testers manually write SQL statements and corresponding test datasets based on their experience, and judge whether the optimizer behavior meets expectations by observing the execution plan and actual execution performance. The drawback of this method is that the statistical distribution characteristics of the test dataset are uncontrollable, making it difficult to construct a data distribution that can reach a specific execution plan pattern; the construction of test cases is highly dependent on the tester's personal experience and understanding of the optimizer's internal mechanisms, and coverage and reproducibility are difficult to guarantee.

[0004] (2) Fuzz testing based on random data generation. By randomly generating SQL statements or random data, abnormal behavior of the optimizer can be discovered in large-scale random testing, such as crashes, incorrect plan selection, and extreme performance degradation. However, random generation lacks targeted guidance on the internal decision-making logic of the optimizer, and a large amount of test computing power is consumed on non-target scenarios, making it difficult to systematically cover specific optimizer decision paths (such as the selection boundary between Hash Join and Nested Loop Join).

[0005] (3) Performance regression testing based on different versions. The same SQL load is executed across different database versions, and performance indicators such as execution time are compared to find performance degradation. However, this method usually only focuses on the change in end-to-end execution time and lacks fine-grained analysis of changes in execution plan structure. It is difficult to locate the specific root cause of performance changes, such as plan drift, operator selection change, cost estimation deviation, etc., and it is even more difficult to manually analyze step by step when there are many types of SQL.

[0006] Furthermore, existing methods lack observability of the optimizer's internal decision-making process. The optimizer's execution plan selection depends on the accuracy of data statistical characteristics, but testers find it difficult to establish a systematic mapping relationship of "how changes in data statistical characteristics affect the optimizer's decisions," resulting in a lack of theoretical basis for test case design and making it difficult to systematically identify and manage regression risks during version evolution.

[0007] Therefore, there is an urgent need for a testing method that can systematically construct test datasets to reach specific execution plans and perform quantifiable, traceable verification and regression testing of optimizer decision-making behavior. Summary of the Invention

[0008] This invention provides a testing method, apparatus, device, and medium for a database optimizer, in order to solve the technical problems in the prior art where the uncontrollable distribution characteristics of the test dataset make it difficult to reach the target execution plan, the difficulty in locating the root cause of optimizer decision anomalies, and the difficulty in automatically identifying and regressing the risk of execution plan drift during version evolution.

[0009] According to one aspect of the present invention, a method for testing a database optimizer is provided, comprising: Based on the structured feature information of the target SQL statement and the preset optimizer decision knowledge base, a test case set for the target SQL statement is determined. Each test case includes a candidate execution plan, data feature parameters, and plan locking method. For each test case, a test dataset is generated based on the data feature parameters, the target SQL statement is executed iteratively, and the test dataset is corrected based on optimizer trace information until the actual execution plan of the target SQL statement is consistent with the candidate execution plan. Based on each test dataset, the target SQL statement is executed according to the default execution method and at least one intervention execution method. The actual execution plan and execution performance indicators under each execution method are collected, and the test cases are verified according to the actual execution plan and execution performance indicators. Store the data feature parameters, candidate execution plans, and execution performance metrics under the default execution mode of the verified test cases into the baseline library; When the database version changes, the test dataset is reconstructed based on the baseline database and regression comparison is performed.

[0010] According to another aspect of the present invention, a testing apparatus for a database optimizer is provided, comprising: The test case construction module is used to determine the test case set of the target SQL statement based on the structured feature information of the target SQL statement and the preset optimizer decision knowledge base. Each test case includes a candidate execution plan, data feature parameters and plan locking method. The test data generation module is used to generate a test dataset for each test case based on the data feature parameters, iteratively execute the target SQL statement, and correct the test dataset based on optimizer trace information until the actual execution plan of the target SQL statement is consistent with the candidate execution plan. The test case verification module is used to execute the target SQL statement according to the default execution method and at least one intervention execution method based on each test dataset, collect the actual execution plan and execution performance indicators under each execution method, and verify the test case according to the actual execution plan and the execution performance indicators; The baseline storage module is used to store the data feature parameters, candidate execution plans, and execution performance metrics under the default execution mode of verified test cases into the baseline library. The regression analysis module is used to reconstruct the test dataset based on the baseline library and perform regression comparisons when the database version changes.

[0011] According to another aspect of the present invention, an electronic device is provided, the electronic device comprising: At least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores a computer program executable by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform the database optimizer test method according to any embodiment of the present invention.

[0012] According to another aspect of the present invention, a computer-readable storage medium is provided, the computer-readable storage medium storing computer instructions for causing a processor to execute and implement the test method of the database optimizer according to any embodiment of the present invention.

[0013] According to another aspect of the present invention, a computer program product is provided, comprising a computer program that, when executed by a processor, implements a test method for a database optimizer according to any embodiment of the present invention.

[0014] This invention determines the test case set based on the structured feature information of the target SQL statement and a pre-set optimizer decision knowledge base, enabling optimizer testing to cover multiple candidate execution plan scenarios without relying on manual experience to design test cases. By iteratively correcting the test data for each test case until the actual execution plan matches the candidate execution plan, the invention achieves automated and accurate construction of test data, solving the technical problems of uncontrollable data distribution characteristics and difficulty in targeting the target execution plan in traditional methods. By executing the target SQL statement on each test data according to the default execution method and the intervention execution method and collecting performance indicators for verification, the invention achieves quantitative evaluation and automated verification of the optimizer's decision behavior. By storing the verified test cases in the baseline library and performing regression comparison when the database version changes, the invention achieves automated detection and risk identification of execution plan drift and performance degradation.

[0015] It should be understood that the description in this section is not intended to identify key or essential features of the embodiments of the present invention, nor is it intended to limit the scope of the invention. Other features of the invention will become readily apparent from the following description. Attached Figure Description

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

[0017] Figure 1 This is a flowchart of a database optimizer testing method provided in an embodiment of the present invention; Figure 2 This is a flowchart of a database optimizer testing method provided in an embodiment of the present invention; Figure 3 This is a schematic diagram of the structure of a database optimizer testing device provided in an embodiment of the present invention; Figure 4 This is a schematic diagram of the structure of an electronic device that implements the testing method of the database optimizer in the embodiments of the present invention. Detailed Implementation

[0018] 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, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[0019] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0020] Figure 1 This is a flowchart illustrating a database optimizer testing method provided in an embodiment of the present invention. This embodiment is applicable to situations where automated testing and performance verification of the optimizer are performed during the version iteration of a database management system. The method can be executed by a database optimizer testing device, which can be implemented in hardware and / or software and can be configured in an electronic device with corresponding data processing capabilities. Figure 1 As shown, the method includes: S110. Based on the structured feature information of the target SQL statement and the preset optimizer decision knowledge base, determine the test case set of the target SQL statement. Each test case includes a candidate execution plan, data feature parameters, and plan locking method.

[0021] The test case set corresponding to the target SQL statement is determined to cover various execution plans that the optimizer may select. Specifically, after obtaining the target SQL statement, the SQL parser is invoked to perform lexical and syntactic analysis on the target SQL statement, generating an abstract syntax tree (AST). The AST represents the syntactic components of the SQL statement in a tree structure, and structured feature information is extracted from the AST. This structured feature information includes connection graphs, predicate patterns, aggregation and grouping features, sorting features, subquery forms and nesting levels, common table expressions and window function usage patterns, and one or more of the relevant table / column / index metadata. The connection graph describes the connection relationships and connection types between tables in the target SQL statement, such as inner joins and left outer joins, as well as the form of the join conditions, such as equi-joins or non-equi-joins. The predicate pattern describes the columns, operator types, constant or parameter bindings, and selectivity estimation ranges of each filter condition in the WHERE clause. The aggregation and grouping features describe the set of columns involved in the GROUP BY clause and the type of aggregate functions. The subquery form describes the type of subquery (associative or non-associative), nesting level, and its association with the outer query.

[0022] The extracted structured feature information is used as query input, and matching reasoning is performed in a pre-defined optimizer decision knowledge base to determine the set of test cases that match the target SQL statement. The optimizer decision knowledge base pre-stores the mapping relationship between structured feature information and test cases. Each mapping record in the knowledge base contains a structured feature template, a candidate execution plan corresponding to the template, and the data feature parameters and plan locking method corresponding to the candidate execution plan. The candidate execution plan is an operator tree structure description of the execution plan that the optimizer may select for an SQL statement with the structured feature template. The data feature parameters are a set of statistical distribution parameters of the test dataset that must be satisfied to induce the optimizer to select the candidate execution plan, including one or more of the following: number of table rows, column NDV (Number of Distinct Values), null value ratio, data skewness, and value range. The plan locking method is an intervention measure used to lock the execution plan of the target SQL statement as a candidate execution plan, including at least one of optimizer hints and session-level parameter adjustments. Optimizer hints are instructional comments embedded in SQL statements, instructing the optimizer to select a specified execution plan shape. Session-level parameter adjustments are configuration commands that operate on the current database session, indirectly influencing the optimizer's plan selection by disabling specific operators or adjusting cost factors. The mapping relationships in the optimizer's decision knowledge base are continuously updated and iterated through historical test verification results to cover new execution plan scenarios.

[0023] The specific method of matching inference is as follows: The structured feature information of the target SQL statement is matched with the structured feature templates in the knowledge base to calculate the comprehensive matching degree. All mapping records with matching degrees exceeding a preset threshold are recalled. The candidate execution plan, data feature parameters, and plan locking method in each record are combined into a test case, forming the test case set for the target SQL statement. Each test case points to a candidate execution plan and provides the data conditions and locking methods required to induce the optimizer to select that plan.

[0024] S120. For each test case, generate a test dataset based on the data feature parameters, iteratively execute the target SQL statement, and correct the test dataset based on optimizer trace information until the actual execution plan of the target SQL statement is consistent with the candidate execution plan.

[0025] For each test case, a test dataset corresponding to the candidate execution plans in that test case needs to be generated so that the optimizer can naturally select the candidate execution plan on that test dataset.

[0026] Optionally, generating a test dataset based on the data feature parameters, iteratively executing the target SQL statement, and correcting the test dataset based on optimizer trace information until the actual execution plan of the target SQL statement matches the candidate execution plan includes: generating an initial test dataset based on the data feature parameters, making the statistical distribution of the initial test dataset approximate the target value of the data feature parameters; executing the target SQL statement based on the initial test dataset and collecting optimizer trace information, parsing the operator tree of the actual execution plan from the optimizer trace information, performing a structural comparison between the operator tree of the actual execution plan and the operator tree of the candidate execution plan, locating structural divergence nodes to determine the deviation operator, and determining the deviation type; calculating the correction amount of the data feature parameters based on the deviation type, updating the data feature parameters based on the correction amount, regenerating the test dataset, and re-performing the comparison on the newly generated test dataset until the operator tree of the actual execution plan matches the operator tree of the candidate execution plan.

[0027] Optionally, an initial test dataset is generated based on the data feature parameters in the current test case, such that the statistical distribution of the generated initial test dataset approximates the target value specified by the data feature parameters. The generated initial test dataset is loaded into the target database instance as the execution environment for the first iteration.

[0028] The target SQL statement was executed on the initial test dataset, and the database optimizer tracing function was simultaneously enabled during execution to collect optimizer tracing information. The optimizer tracing information records the internal information of the optimizer during the decision-making process, including all candidate execution plans enumerated by the optimizer and their complete cost decompositions, the pruning reasons for each candidate execution plan, the operator tree structure of the finally selected actual execution plan, and the estimated row count and estimated cost of each operator in the operator tree.

[0029] The operator tree structure of the final selected execution plan is parsed from the optimizer trace information, and then a structural difference comparison is performed between the operator tree structure of the actual execution plan and the operator trees of the candidate execution plans in the current test case. Specifically, a top-down node traversal is performed on both operator trees, comparing the operator type, access path, and child node relationships of each node in turn, and locating the first node with inconsistent structure as the deviation operator. The operator tree structure comparison does not involve runtime metrics such as execution time and resource consumption; it only compares whether the operator trees of the execution plans are consistent at the structural level, such as whether the operator type of the root node is the same, whether the access paths of each node are consistent, and whether the connection order is the same.

[0030] If the structure comparison results are consistent, it means that the current test dataset is sufficient to guide the optimizer to select a candidate execution plan for the test case, the iteration terminates, and the current test dataset is saved as a test dataset specific to that test case. If the structure comparison results are inconsistent, the type of deviation causing the structural divergence is further determined.

[0031] Optionally, the deviation types include line count estimation deviation, cost weight deviation, and pruning deviation; the step of performing a structural comparison between the operator tree of the actual execution plan and the operator tree of the candidate execution plan, locating structural divergence nodes to determine the deviation operator, and determining the deviation type includes: performing a node traversal comparison between the operator tree of the actual execution plan and the operator tree of the candidate execution plan, locating the first structurally inconsistent node as the deviation operator; if the optimizer's estimated line count of the deviation operator deviates from a preset threshold range relative to the actual number of executed lines of the deviation operator, it is determined to be a line count estimation deviation; if the consistency between the optimizer's estimated line count of the deviation operator and the actual number of executed lines is within a preset threshold range, and the sorting of each execution method based on the current test dataset by estimated cost is inconsistent with the sorting by actual execution cost, it is determined to be a cost weight deviation; if the operator tree of the candidate execution plan does not appear in the candidate enumeration list recorded in the optimizer trace information, it is determined to be a pruning deviation.

[0032] Optionally, the deviation types include three categories: row count estimation deviation, cost weight deviation, and pruning deviation. Row count estimation deviation refers to a significant discrepancy between the optimizer's estimated output row count for a certain operator during cost estimation and the actual number of rows executed, leading the optimizer to make an incorrect plan selection based on an incorrect row count estimate. Cost weight deviation refers to a situation where, although the optimizer's row count estimate is generally accurate, the weight configuration of each cost component (such as CPU cost and IO cost) in the optimizer's cost model does not match the actual database operating environment, causing the optimizer to select an incorrect plan during cost ranking. Pruning deviation refers to the exclusion of the operator combination corresponding to the candidate execution plan by the pruning strategy during the optimizer's plan enumeration stage, preventing it from entering the cost comparison stage. The type of deviation is identified based on information such as the estimated row count, actual number of rows executed, estimated cost decomposition, and pruning reason records in the optimizer tracking information, and the correction amount of the data characteristic parameters is calculated accordingly.

[0033] Optionally, the step of calculating the correction amount of the data feature parameters according to the deviation type, updating the data feature parameters according to the correction amount, and regenerating the test dataset includes: when the deviation type is row number estimation deviation, adjusting the table row number parameter in the data feature parameters according to the ratio of the actual number of executed rows to the estimated number of rows of the deviation operator; when the deviation type is cost weight deviation, obtaining the cost component with the largest estimated deviation in the cost decomposition of the deviation operator, and adjusting the data distribution parameter corresponding to the cost component in the data feature parameters according to the ratio of the actual cost to the estimated cost of the cost component, wherein the data distribution parameter includes the number of distinct column values ​​(NDV) and data skewness; when the deviation type is pruning deviation, obtaining the metadata preconditions required for the candidate execution plan to enter the optimizer candidate enumeration list, determining whether the database instance meets the metadata preconditions, and generating a metadata change instruction if not.

[0034] For example, if a candidate execution plan does not appear in the enumeration list, it means that the optimizer excluded the plan directly during the enumeration phase because the metadata conditions were not met (such as the lack of an index), so an index needs to be created or the statistics updated.

[0035] The correction amount for the data feature parameters is calculated based on the deviation type. After updating the data feature parameters according to the correction amount, the test dataset is regenerated. The target SQL statement is executed again on the newly generated test dataset, and optimizer trace information is collected. The above comparison, location, and correction process is repeated until the operator tree of the actual execution plan matches the operator tree of the candidate execution plan. Through the above closed-loop iterative mechanism, the statistical distribution of the test dataset is gradually adjusted to the target state that can induce the optimizer to select candidate execution plans for test cases.

[0036] By constructing a dedicated test dataset for each candidate execution plan of a test case and introducing a closed-loop iterative correction mechanism based on optimizer tracking information during the data generation process, the statistical distribution of the test dataset can be automatically adjusted to a state that can induce the optimizer to select the target execution plan. This eliminates the reliance on manual data generation for tests targeting specific execution plan patterns, improving the automation and accuracy of test dataset construction. Furthermore, a three-tiered deviation judgment mechanism based on operator tree structure comparison—row count estimation deviation, cost weight deviation, and pruning deviation—is introduced. Differentiated correction strategies, such as adjusting the row count parameter ratio, adjusting the data distribution parameter ratio, and compensating for metadata, are adopted according to the deviation type. This further realizes the systematic and automated correction of the statistical characteristic parameters of the test dataset, solving the technical problems of unclear direction and low efficiency when manually adjusting data based on experience. This ensures that the iterative data generation process can accurately converge to the candidate execution plan of the test case.

[0037] S130. Based on each test dataset, execute the target SQL statement according to the default execution method and at least one intervention execution method, collect the actual execution plan and execution performance indicators under each execution method, and verify the test case according to the actual execution plan and the execution performance indicators.

[0038] S140. Store the data feature parameters, candidate execution plans, and execution performance metrics under the default execution mode of the verified test cases into the baseline library.

[0039] S150. When the database version changes, reconstruct the test dataset based on the baseline database and perform regression comparison.

[0040] After generating a dedicated test dataset for each test case, the optimizer's decision-making behavior on this dataset needs to be verified to confirm that the optimizer's natural selection of the candidate execution plan for that test case under this data distribution is correct and reliable. Simultaneously, the test cases that pass verification are stored as baselines for regression comparison during subsequent database version changes.

[0041] For each test dataset, the target SQL statement is executed on that dataset using both the default execution method and at least one intervention execution method. The actual execution plans and performance metrics under each execution method are collected. The default execution method refers to executing the target SQL statement without any intervention, allowing the optimizer to naturally select an execution plan based on the current data statistical characteristics. This method reflects the optimizer's actual decision-making behavior in a real-world environment. Intervention execution methods involve manually altering the optimizer's decision path to generate an execution plan different from the default execution method, thus providing a control. Optionally, intervention execution methods include one or more of the following: locking the execution plan of the target SQL statement to an execution plan other than the candidate execution plans in the test case using plan locking; executing after equivalent rewriting of the target SQL statement; and executing after influencing the optimizer's decision through session-level parameter adjustments.

[0042] Collect the actual execution plan and end-to-end execution performance metrics after each execution method. Execution performance metrics are used to quantify the running efficiency of each execution method, including end-to-end execution time. Verify the test case's pass / fail status based on the actual execution plan and the collected execution performance metrics for each execution method. For test cases that pass verification, store their data characteristic parameters, candidate execution plans, and execution performance metrics under the default execution method in the baseline database as a baseline record for the target SQL statement.

[0043] When the database version changes, the test data is reconstructed based on the baseline database and regression comparison is performed to identify whether the database version upgrade introduces the risk of execution plan drift or performance degradation.

[0044] The verification results and regression analysis results of test cases can be written into the optimizer's decision knowledge base as feedback information to update the mapping relationship between structured feature information and test cases.

[0045] This invention determines the test case set based on the structured feature information of the target SQL statement and a pre-set optimizer decision knowledge base, enabling optimizer testing to cover multiple candidate execution plan scenarios without relying on manual experience to design test cases. By iteratively correcting the test data for each test case until the actual execution plan matches the candidate execution plan, the invention achieves automated and accurate construction of test data, solving the technical problems of uncontrollable data distribution characteristics and difficulty in targeting the target execution plan in traditional methods. By executing the target SQL statement on each test data according to the default execution method and the intervention execution method and collecting performance indicators for verification, the invention achieves quantitative evaluation and automated verification of the optimizer's decision behavior. By storing the verified test cases in the baseline library and performing regression comparison when the database version changes, the invention achieves automated detection and risk identification of execution plan drift and performance degradation.

[0046] Figure 2 This is a flowchart of a database optimizer testing method provided in an embodiment of the present invention. This embodiment is an optimization and improvement based on the above embodiment. Figure 2 As shown, the method includes: S210. Based on the structured feature information of the target SQL statement and the preset optimizer decision knowledge base, determine the test case set of the target SQL statement. Each test case includes a candidate execution plan, data feature parameters, and plan locking method.

[0047] S220. For each test case, generate a test dataset based on the data feature parameters, iteratively execute the target SQL statement, and correct the test dataset based on optimizer trace information until the actual execution plan of the target SQL statement is consistent with the candidate execution plan.

[0048] S230. Based on each test dataset, execute the target SQL statement according to the default execution method and at least one intervention execution method, and collect the actual execution plan and execution performance indicators under each execution method.

[0049] Optionally, the intervention execution method includes at least one of the following: locking the execution plan of the target SQL statement to an execution plan other than the candidate execution plan in the test case based on the plan locking method and then executing; performing an equivalent rewrite of the target SQL statement and then executing; or influencing the optimizer decision by adjusting session-level parameters and then executing.

[0050] After generating a dedicated test dataset for each test case, the target SQL statement is executed on each test dataset using the default execution method and at least one intervention execution method. The actual execution plan and execution performance indicators under each execution method are collected to verify whether the default decision behavior of the optimizer on the test dataset is reliable and optimal.

[0051] The default execution mode refers to directly submitting the target SQL statement to the database for execution, without applying any locking mechanisms as in plan locking, without issuing session-level parameter adjustment commands, and without performing equivalent rewriting of the SQL statement. The optimizer autonomously selects the execution plan based entirely on the statistical characteristics of the current test dataset. The default execution mode reflects the optimizer's native decision-making behavior in a real-world operating environment and serves as a benchmark for verifying the correctness of the optimizer's decisions.

[0052] Intervention execution methods refer to altering the optimizer's decision path through external intervention, causing the optimizer to generate an execution plan different from the default execution method, thus forming a control experiment. Intervention execution methods include at least one of the following three methods.

[0053] The first intervention method involves locking the execution plan of the target SQL statement to an execution plan other than the candidate execution plans in the test case, based on the plan locking method, before execution. On the test dataset specific to the current test case, the optimizer naturally selects the candidate execution plan corresponding to that test case. To verify whether this selection is truly optimal, a competing execution plan other than the candidate execution plan (i.e., a plan that the optimizer considers too costly and did not select) is forced through a hint, and the actual execution performance of this competing execution plan on the test dataset is obtained and compared with the performance of the default candidate execution plan. If the performance of the default candidate execution plan is better than the competing execution plan forced by the hint, it indicates that the optimizer's selection is correct; otherwise, it indicates that the optimizer has a cost estimation bias under this data distribution.

[0054] For example, the target SQL statement generated three test cases, corresponding to three candidate execution plans: Hash Join, Nested Loop, and Merge Join. For the Hash Join test dataset D_HashJoin, the default execution method was executed on this dataset, and the optimizer naturally chose Hash Join. To verify whether this choice was truly optimal, a hint was used to force Nested Loop and / or Merge Join. The forced SQL was executed on D_HashJoin, and the actual execution time of Nested Loop and / or Merge Join was collected and compared with the execution time of the default Hash Join.

[0055] The second intervention method involves rewriting the target SQL statement using an equivalent function before execution. Specifically, it identifies whether the target SQL statement involves the optimizer's automatic equivalence transformation. If so, it calls the SQL rewriting engine to convert the target SQL statement into a rewritten SQL statement with a different structure but semantic equivalence according to relational algebra equivalence transformation rules, and then directly submits the rewritten SQL statement to the database for execution.

[0056] The second intervention method is used to verify the correctness of the optimizer's automatic transformation logic. For SQL involving automatic transformation (such as SQL containing IN subqueries or view references), the optimizer will transform the original SQL internally into an equivalent form before selecting a plan during default execution. However, this transformation process is invisible to the user, and its correctness cannot be directly verified. By directly issuing the equivalent rewritten SQL for execution, the optimizer no longer performs automatic transformation in the same direction on the transformed SQL, but instead directly selects an execution plan based on the rewritten form. If there is a significant difference in the execution plan or execution performance selected by the default execution (with automatic transformation by the optimizer) and the equivalent rewritten execution (without transformation in this direction), it indicates that there is a deviation in the optimizer's automatic transformation logic or the cost estimation after transformation.

[0057] The third intervention method involves influencing the optimizer's decision through session-level parameter adjustments. Specifically, based on the candidate execution plans in the current test case, the operator type corresponding to that plan (e.g., Hash Join) is determined. The optimizer switch parameter mapping table corresponding to the target database type is queried, and the operator type is converted into the corresponding session-level SET command (e.g., `SET enable_hashjoin=off`). After issuing the SET command in the database session, the target SQL statement is executed. This method disables the operator type used by the candidate execution plan, preventing the optimizer from selecting that candidate execution plan. It forces the optimizer to re-estimate the cost from the remaining available operator types and select the plan with the lowest execution cost. This allows the optimizer to obtain the suboptimal choice and actual execution performance of the candidate execution plan when the candidate execution plan for the test case is unavailable. The performance of the candidate execution plan under the default execution method is then compared to verify whether the candidate execution plan is the truly optimal plan for this data distribution.

[0058] Let's take verifying whether Hash Join is optimal as an example. On the test dataset D_HashJoin, the optimizer defaults to Hash Join. To verify whether this choice is correct, Hash Join is disabled via session parameters, preventing the optimizer from selecting Hash Join and forcing it to choose the plan with the lowest execution cost from Nested Loop and Merge Join. After execution and collecting performance metrics, the performance is compared with that of Hash Join under the default execution method: if the performance of Hash Join is still better than the plan reselected by the optimizer after disabling Hash Join, it indicates that the optimizer's decision to choose Hash Join is correct; if other plans selected by the optimizer after disabling Hash Join execute faster, it indicates that the optimizer has a cost estimation bias, that is, it incorrectly believes that Hash Join has the lowest cost when it is not actually the case.

[0059] The third intervention method differs from the first in that the first method directly specifies an execution plan via a hint, representing forward precise locking; the third method disables the operator types used by candidate execution plans, forcing the optimizer to re-estimate costs and autonomously select a suboptimal plan from the remaining space after excluding that plan, representing reverse exclusion verification. The first method is used to obtain a performance reference for the locked plan, while the third method is used to verify the optimizer's ability to select a suboptimal plan and the accuracy of cost estimation when candidate plans are unavailable. The two methods complement each other, jointly verifying the optimality of candidate execution plans from both forward locking and reverse exclusion perspectives.

[0060] S240. If the actual execution plan under the default execution mode is consistent with the candidate execution plan, the execution performance index under the default execution mode meets the preset stability condition, and the execution performance index under the default execution mode is better than the execution performance index under each comparative execution mode, then the test case is determined to be verified.

[0061] After executing the default execution method and at least one intervention execution method on the test dataset dedicated to the test case, collect the actual execution plan and end-to-end execution performance indicators under each execution method, and verify whether the test case passes based on the collected performance data.

[0062] If the actual execution plan under the default execution mode is consistent with the candidate execution plan, the execution performance index under the default execution mode meets the preset stability condition, and the execution performance index under the default execution mode is superior to the execution performance index under each intervention execution mode, then the test case is deemed to have passed verification. The preset stability condition means that the fluctuation of the end-to-end execution time under the default execution mode is within a preset threshold range during multiple repeated executions, ensuring the repeatability of the optimizer's decision-making behavior under this data distribution; "superior" means that the end-to-end execution time under the default execution mode is less than the end-to-end execution time under each intervention execution mode. The verification passing result indicates that under the statistical distribution of this test dataset, the optimizer naturally selected the candidate execution plan, and the actual execution performance of this plan is indeed superior to other optional plans; that is, the optimizer's decision-making behavior under this data distribution has been proven to be correct and stable.

[0063] By executing the default execution method and at least one intervention execution method on a test dataset specific to the test cases, a multi-dimensional comparative experiment is formed: the default execution method reflects the optimizer's native decision-making behavior in the actual operating environment, while the intervention execution methods obtain the actual execution performance reference of each competing plan through methods such as hinting for positive locking, bypassing automatic transformation through equivalent rewriting, and reverse exclusion of session parameters. The actual performance of the execution plan autonomously selected by the optimizer under the default execution method is compared with the execution plans locked or induced under each intervention execution method. If the execution performance under the default execution method is better than that under each intervention execution method, it proves that the optimizer has made the correct optimal decision under the data distribution conditions; if the execution performance under any intervention execution method is better than that under the default execution method, it proves that the optimizer has a cost estimation bias or a transformation logic defect, and the test case verification fails. This mechanism makes the invisible decision-making process inside the optimizer explicit through comparative experiments, making the correctness of the optimizer's decision quantifiable and verifiable.

[0064] S250. Store the data feature parameters, candidate execution plans, and execution performance metrics under the default execution mode of the verified test cases into the baseline library.

[0065] For test cases that pass verification, the data characteristic parameters, candidate execution plans, and execution performance indicators under the default execution mode of the test case are stored as baseline records, serving as a reliable reference for the target SQL statement under specific data distribution conditions.

[0066] Specifically, three parts of information are extracted from the validated test cases and stored in the baseline library: The first part is the data feature parameters of the test case, which are used to record the statistical distribution characteristics of the test dataset, including the number of table rows, column NDV, null value ratio, data skewness, value range, etc., for reconstructing the same data distribution environment during subsequent regression testing; The second part is the candidate execution plan of the test case, which stores its operator tree structured signature, for structural comparison of execution plans during subsequent regression testing; The third part is the execution performance indicators under the default execution mode, which stores the baseline value of end-to-end execution time and the allowable fluctuation range, for performance differential comparison during subsequent regression testing.

[0067] Simultaneously, each baseline record is associated with the current database version number and optimizer parameter configuration information, which are stored as metadata tags in the baseline library to identify the environment state corresponding to the baseline when the version changes. Each validated test case is stored independently as a baseline record. The same target SQL statement may have multiple baseline records in the baseline library, each corresponding to a different candidate execution plan and its own data distribution conditions.

[0068] The baseline storage mechanism transforms validated test cases into reproducible and traceable test assets, providing a reference benchmark for regression comparison during subsequent database version upgrades.

[0069] S260. When the database version changes, reconstruct the test dataset based on the baseline database and perform regression comparison.

[0070] Optionally, the step of reconstructing the test dataset and performing regression comparison based on the baseline library when the database version changes includes: when the database version changes, retrieving matching baseline records from the baseline library using the target SQL statement as an index, reconstructing the test dataset based on the data feature parameters in the baseline records, executing the target SQL statement on the new version database in the default execution mode, collecting the operator tree of the actual execution plan and the current execution performance index; comparing the operator tree of the actual execution plan with the operator tree of the candidate execution plans in the baseline records to determine the structural difference; calculating the percentage deviation between the current execution performance index and the execution performance index in the baseline records to determine the performance offset; and determining the regression analysis result of the target SQL statement on the new version database based on the structural difference and the performance offset.

[0071] Optionally, determining the regression analysis result of the target SQL statement on the new version database based on the structural difference degree and the performance deviation degree includes: when the structural difference degree is zero and the performance deviation degree is within a preset threshold range, it is determined that there is no regression; when the structural difference degree is zero and the performance deviation degree is negative and its absolute value exceeds the preset threshold, it is determined that there is a performance improvement; when the structural difference degree is not zero and the performance deviation degree exceeds the preset threshold range, it is determined that the optimizer has a plan selection bias; when the structural difference degree is not zero and the performance deviation degree is within the preset threshold range, it is determined that the optimizer has a plan structure change.

[0072] Optionally, when the optimizer problem type is plan selection bias, the method further includes: locking the execution plan of the target SQL statement to the candidate execution plan in the baseline record according to the plan locking method in the baseline record, and then executing it, and collecting the actual execution plan and execution performance indicators; if the actual execution plan is consistent with the candidate execution plan, and the execution performance indicators recover to the preset threshold range of the execution performance indicators in the baseline record, then it is determined that the optimizer has a plan selection change; otherwise, it is determined that the new version of the database has execution engine performance degradation.

[0073] When the database version changes, it is necessary to perform regression comparison on the stored baseline records to identify whether the new version of the database introduces the risk of execution plan drift or performance degradation.

[0074] Specifically, using the target SQL statement as an index, matching baseline records are retrieved from the baseline database. The test dataset is reconstructed based on the data feature parameters in the baseline records. The target SQL statement is then executed on the new version database using the default execution method, and the operator tree of the actual execution plan and the current execution performance metrics are collected. The operator tree of the actual execution plan is compared with the operator trees of candidate execution plans in the baseline records to determine the structural difference. The percentage deviation between the current execution performance metrics and the execution performance metrics in the baseline records is calculated to determine the performance offset. Based on the structural difference and performance offset, the regression analysis results of the target SQL statement on the new version database are determined. If the structural difference is zero and the performance offset is within a preset threshold range, it is determined as no regression; if the structural difference is zero and the performance offset is negative and its absolute value exceeds the preset threshold, it is determined as a performance improvement; if the structural difference is not zero and the performance offset exceeds the preset threshold range, it is determined as a plan selection deviation; if the structural difference is not zero and the performance offset is within the preset threshold range, it is determined as a plan structure change.

[0075] When a plan selection deviation is identified, further attribution analysis is performed to pinpoint the root cause. Based on the plan locking method in the baseline record, the execution plan of the target SQL statement is locked to a candidate execution plan in the baseline record and then forcibly executed on the new version database, collecting the actual execution plan and performance metrics. If the forced execution plan matches the candidate execution plan, and the forced execution performance metrics recover to the preset threshold range of the execution performance metrics in the baseline record, then the performance degradation is determined to be caused by a change in optimizer plan selection—that is, the new version optimizer has changed its cost estimation or decision-making logic, no longer selecting the plan that was verified as optimal in the old version. If the forced execution plan matches the candidate execution plan but the execution performance metrics do not recover to the preset threshold range, then the new version database has execution engine performance degradation—that is, the optimizer can still select the correct plan, but the execution engine itself has slowed down. If the forced execution plan does not match the candidate execution plan, it indicates that the plan locking method itself has failed, a locking mechanism failure, requiring manual intervention to check the compatibility of the hint syntax or whether the parameter mapping table has been updated with the version.

[0076] By employing a process of elimination, performance degradation issues can be precisely pinpointed to either the optimizer decision-making layer or the execution engine layer: after locking onto candidate execution plans, execution is performed; if performance recovers, the root cause lies in the optimizer decision-making layer; if performance does not recover, the root cause lies in the execution engine layer. This mechanism enables developers to quickly identify the source of performance problems in version changes, avoiding inefficient debugging caused by the blurred boundaries between the optimizer and execution engine.

[0077] For baseline records showing changes in the plan structure or performance degradation of the execution engine, corresponding risk markers are output in the regression analysis report, indicating the need for manual review or further analysis. All regression analysis results are written as feedback information into the optimizer decision knowledge base to update the mapping relationship between structured feature information and test cases or adjust the matching priority of test cases. This allows the knowledge base to continuously accumulate testing experience during version evolution, improving the accuracy of subsequent test case generation.

[0078] This invention provides a multi-dimensional comparative experiment by executing the default execution method and at least one intervention execution method on the same test dataset. The default execution method reflects the optimizer's native decision-making behavior in the actual operating environment, while the intervention execution methods obtain the actual execution performance reference of each competing plan through methods such as hinting for positive locking, equivalent rewriting to bypass automatic transformation, and reverse elimination of session parameters. The actual performance of the execution plan autonomously selected by the optimizer under the default execution method is compared with the execution plans locked or induced under each intervention execution method. If the execution performance under the default execution method is better than that under each intervention execution method, it proves that the optimizer has made the correct optimal decision under the data distribution conditions. If the execution performance under any intervention execution method is better than that under the default execution method, it proves that the optimizer has a cost estimation bias or a transformation logic defect, and the test case verification fails. This mechanism makes the invisible decision-making process inside the optimizer explicit through comparative experiments, making the correctness of the optimizer's decision quantifiable and verifiable. Based on this, when the deviation is determined to be a plan selection bias, attribution analysis is further performed by forcibly executing candidate execution plans. Depending on whether the execution performance recovers to the baseline threshold range, the performance degradation problem is precisely located to the optimizer plan selection change or the execution engine performance degradation. This enables developers to quickly identify the source of performance problems in version changes and avoid low debugging efficiency caused by the blurred boundaries between the optimizer and the execution engine.

[0079] Figure 3 This is a schematic diagram of the structure of a testing device for a database optimizer provided in an embodiment of the present invention. Figure 3 As shown, the device includes: The test case construction module 310 is used to determine the test case set of the target SQL statement based on the structured feature information of the target SQL statement and the preset optimizer decision knowledge base. Each test case includes a candidate execution plan, data feature parameters and plan locking method. The test data generation module 320 is used to generate a test dataset for each test case based on the data feature parameters, iteratively execute the target SQL statement, and correct the test dataset based on optimizer trace information until the actual execution plan of the target SQL statement is consistent with the candidate execution plan. The test case verification module 330 is used to execute the target SQL statement according to the default execution method and at least one intervention execution method based on each test dataset, collect the actual execution plan and execution performance indicators under each execution method, and verify the test case according to the actual execution plan and the execution performance indicators; The baseline storage module 340 is used to store the data feature parameters, candidate execution plans, and execution performance indicators under the default execution mode of the verified test cases into the baseline library. The regression analysis module 350 is used to reconstruct the test dataset based on the baseline library and perform regression comparisons when the database version changes.

[0080] The database optimizer testing device provided in this embodiment of the invention can execute the database optimizer testing method provided in any embodiment of the invention, and has the corresponding functional modules and beneficial effects of the execution method.

[0081] Optionally, the test data generation module includes: An initial data generation unit is used to generate an initial test dataset based on the data feature parameters, so that the statistical distribution of the initial test dataset approximates the target value of the data feature parameters. The deviation analysis unit is used to execute the target SQL statement based on the initial test dataset and collect optimizer trace information, parse the operator tree of the actual execution plan from the optimizer trace information, perform a structural comparison between the operator tree of the actual execution plan and the operator tree of the candidate execution plan, locate structural branch nodes to determine the deviation operator, and determine the deviation type. The data correction unit is used to calculate the correction amount of the data feature parameters according to the deviation type, update the data feature parameters according to the correction amount, regenerate the test dataset, and re-execute the comparison on the newly generated test dataset until the operator tree of the actual execution plan is consistent with the operator tree of the candidate execution plan.

[0082] Optionally, the deviation types include row count estimation deviation, cost weight deviation, and pruning deviation; the deviation analysis unit includes: The deviation operator determination subunit is used to perform node traversal comparison between the operator tree of the actual execution plan and the operator tree of the candidate execution plan, and locate the first node with inconsistent structure as the deviation operator. The deviation analysis subunit is used to determine a line count estimation deviation if the optimizer's estimated number of lines deviates from the actual number of lines executed by the deviation operator within a preset threshold range; to determine a cost weight deviation if the optimizer's estimated number of lines and the actual number of lines executed are consistent within a preset threshold range, and the sorting of execution methods based on the current test dataset by estimated cost is inconsistent with the sorting by actual execution cost; and to determine a pruning deviation if the operator tree of the candidate execution plan does not appear in the candidate enumeration list recorded in the optimizer trace information.

[0083] Optionally, the intervention execution method includes at least one of the following: locking the execution plan of the target SQL statement to an execution plan other than the candidate execution plan in the test case based on the plan locking method before execution; performing an equivalent rewrite of the target SQL statement before execution; or influencing the optimizer decision by adjusting session-level parameters before execution; the test case verification module includes: The judgment unit is configured to determine that the test case verification is passed if the actual execution plan under the default execution mode is consistent with the candidate execution plan, the execution performance index under the default execution mode meets the preset stability conditions, and the execution performance index under the default execution mode is better than the execution performance index under each comparison execution mode.

[0084] Optionally, the regression analysis module includes: The data reconstruction and execution unit is used to retrieve matching baseline records from the baseline library using the target SQL statement as an index when the database version changes, reconstruct the test dataset according to the data feature parameters in the baseline records, execute the target SQL statement on the new version database in the default execution mode, and collect the operator tree of the actual execution plan and the current execution performance indicators. The difference calculation unit is used to compare the operator tree of the actual execution plan with the operator tree of the candidate execution plans in the baseline record to determine the structural difference degree; and to calculate the percentage deviation between the current execution performance index and the execution performance index in the baseline record to determine the performance offset degree. The regression determination unit is used to determine the regression analysis results of the target SQL statement on the new version database based on the structural difference degree and the performance offset degree.

[0085] Optionally, the regression determination unit is specifically used to determine no regression when the structural difference is zero and the performance deviation is within a preset threshold range; to determine performance improvement when the structural difference is zero and the performance deviation is negative and its absolute value exceeds a preset threshold range; to determine that the optimizer has a plan selection bias when the structural difference is not zero and the performance deviation exceeds a preset threshold range; and to determine that the optimizer has a plan structure change when the structural difference is not zero and the performance deviation is within a preset threshold range.

[0086] Optionally, the regression determination unit is further configured to: when the optimizer problem type is plan selection deviation, lock the execution plan of the target SQL statement to the candidate execution plan in the baseline record according to the plan locking method in the baseline record and then execute it, and collect the actual execution plan and execution performance indicators; if the actual execution plan is consistent with the candidate execution plan and the execution performance indicators recover to the preset threshold range of the execution performance indicators in the baseline record, then it is determined that the optimizer has a plan selection change; otherwise, it is determined that the new version of the database has execution engine performance degradation.

[0087] The database optimizer testing apparatus described in further detail can also execute the database optimizer testing method provided in any embodiment of the present invention, and has the corresponding functional modules and beneficial effects of the execution method.

[0088] According to embodiments of the present invention, the present invention also provides an electronic device, a readable storage medium, and a computer program product.

[0089] Figure 4 A schematic diagram of an electronic device 40 that can be used to implement embodiments of the present invention is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices (e.g., helmets, glasses, watches, etc.), and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the invention described and / or claimed herein.

[0090] like Figure 4 As shown, the electronic device 40 includes at least one processor 41 and a memory, such as a read-only memory 42 or a random access memory 43, communicatively connected to the at least one processor 41. The memory stores computer programs executable by the at least one processor. The processor 41 can perform various appropriate actions and processes based on the computer program stored in the read-only memory 42 or loaded from storage unit 48 into the random access memory 43. The random access memory 43 may also store various programs and data required for the operation of the electronic device 40. The processor 41, read-only memory 42, and random access memory 43 are interconnected via a bus 44. An input / output interface 45 is also connected to the bus 44.

[0091] Multiple components in electronic device 40 are connected to input / output interface 45, including: input unit 46, such as keyboard, mouse, etc.; output unit 47, such as various types of monitors, speakers, etc.; storage unit 48, such as disk, optical disk, etc.; and communication unit 49, such as network card, modem, wireless transceiver, etc. Communication unit 49 allows electronic device 40 to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunications networks.

[0092] Processor 41 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of processor 41 include, but are not limited to, central processing units, graphics processing units, various special-purpose artificial intelligence computing chips, various processors running machine learning model algorithms, digital signal processors, and any suitable processor, controller, microcontroller, etc. Processor 41 performs the various methods and processes described above, such as the testing methods of a database optimizer.

[0093] In some embodiments, the database optimizer testing method may be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 48. In some embodiments, part or all of the computer program may be loaded and / or installed on electronic device 40 via read-only memory 42 and / or communication unit 49. When the computer program is loaded into random access memory 43 and executed by processor 41, one or more steps of the database optimizer testing method described above may be performed. Alternatively, in other embodiments, processor 41 may be configured to execute the database optimizer testing method by any other suitable means (e.g., by means of firmware).

[0094] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field-programmable gate arrays, application-specific integrated circuits (ASICs), application-specific standard products (ASICs), systems-on-a-chip (SoCs), payload programmable logic devices, computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.

[0095] Computer programs used to implement the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the processor, the computer programs cause the functions / operations specified in the flowcharts and / or block diagrams to be performed. The computer programs may be executed entirely on a machine, partially on a machine, or as a standalone software package, partially on a machine and partially on a remote machine, or entirely on a remote machine or server.

[0096] In the context of this invention, a computer-readable storage medium can be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, apparatus, or device. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination thereof. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory, read-only memory, erasable programmable read-only memory, optical fibers, portable compact disk read-only memory, optical storage devices, magnetic storage devices, or any suitable combination thereof.

[0097] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a cathode ray tube, liquid crystal display, or monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).

[0098] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or computing systems that include middleware components (e.g., application servers), or computing systems that include frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.

[0099] A computing system can include clients and servers. Clients and servers are generally geographically separated and typically interact via communication networks. The client-server relationship is created by computer programs running on the respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a host product within the cloud computing service system to address the shortcomings of traditional physical hosts and virtual private servers, such as high management difficulty and weak business scalability.

[0100] It should be understood that the various forms of processes shown above can be used, with steps reordered, added, or deleted. For example, the steps described in this invention can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this invention can be achieved, and this is not limited herein.

[0101] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.

Claims

1. A method for testing a database optimizer, characterized in that, The method includes: Based on the structured feature information of the target SQL statement and the preset optimizer decision knowledge base, a test case set for the target SQL statement is determined. Each test case includes a candidate execution plan, data feature parameters, and plan locking method. For each test case, a test dataset is generated based on the data feature parameters, the target SQL statement is executed iteratively, and the test dataset is corrected based on optimizer trace information until the actual execution plan of the target SQL statement is consistent with the candidate execution plan. Based on each test dataset, the target SQL statement is executed according to the default execution method and at least one intervention execution method. The actual execution plan and execution performance indicators under each execution method are collected, and the test cases are verified according to the actual execution plan and execution performance indicators. Store the data feature parameters, candidate execution plans, and execution performance metrics under the default execution mode of the verified test cases into the baseline library; When the database version changes, the test dataset is reconstructed based on the baseline database and regression comparison is performed.

2. The method according to claim 1, characterized in that, The process of generating a test dataset based on the data feature parameters, iteratively executing the target SQL statement, and correcting the test dataset based on optimizer trace information until the actual execution plan of the target SQL statement matches the candidate execution plan includes: An initial test dataset is generated based on the data feature parameters, so that the statistical distribution of the initial test dataset approximates the target value of the data feature parameters; The target SQL statement is executed based on the initial test dataset, and optimizer trace information is collected. The operator tree of the actual execution plan is parsed from the optimizer trace information. The operator tree of the actual execution plan is compared with the operator tree of the candidate execution plan to locate the structural branch nodes, determine the deviation operator, and determine the deviation type. The correction amount of the data feature parameters is calculated according to the deviation type. After updating the data feature parameters according to the correction amount, the test dataset is regenerated. The comparison is re-executed on the newly generated test dataset until the operator tree of the actual execution plan is consistent with the operator tree of the candidate execution plan.

3. The method according to claim 2, characterized in that, The deviation types include row count estimation deviation, cost weight deviation, and pruning deviation; the step of performing a structural comparison between the operator tree of the actual execution plan and the operator tree of the candidate execution plan, locating structural branch nodes to determine the deviation operator, and determining the deviation type includes: The operator tree of the actual execution plan is compared with the operator tree of the candidate execution plan by traversing the nodes, and the first node with inconsistent structure is located as the deviation operator; If the number of rows estimated by the optimizer of the deviation operator deviates from a preset threshold range relative to the actual number of rows executed by the deviation operator, it is determined to be a row number estimation deviation; If the consistency between the optimizer's estimated number of rows and the actual number of rows executed by the bias operator is within a preset threshold range, and the sorting of each execution method based on the current test dataset by estimated cost is inconsistent with the sorting by actual execution cost, then it is determined to be a cost weight bias. If the operator tree of the candidate execution plan does not appear in the candidate enumeration list recorded in the optimizer trace information, it is determined to be a pruning bias.

4. The method according to claim 1, characterized in that, The intervention execution method includes at least one of the following: locking the execution plan of the target SQL statement to an execution plan other than the candidate execution plan in the test case based on the plan locking method, and then executing it; The target SQL statement is then rewritten using an equivalent method and executed. Influencing optimizer decisions and execution through session-level parameter adjustments; The step of verifying the test cases based on the actual execution plan and the execution performance metrics includes: If the actual execution plan under the default execution mode is consistent with the candidate execution plan, the execution performance index under the default execution mode meets the preset stability conditions, and the execution performance index under the default execution mode is better than the execution performance index under each comparative execution mode, then the test case is determined to have passed verification.

5. The method according to claim 1, characterized in that, When the database version changes, the process of reconstructing the test dataset based on the baseline database and performing regression comparison includes: When the database version changes, the target SQL statement is used as an index to retrieve matching baseline records from the baseline database. The test dataset is reconstructed based on the data feature parameters in the baseline records. The target SQL statement is executed on the new version database in the default execution mode, and the operator tree of the actual execution plan and the current execution performance indicators are collected. Compare the operator tree of the actual execution plan with the operator tree of the candidate execution plans in the baseline record to determine the structural difference; calculate the percentage deviation between the current execution performance index and the execution performance index in the baseline record to determine the performance offset. Based on the structural difference and the performance offset, the regression analysis results of the target SQL statement on the new version of the database are determined.

6. The method according to claim 5, characterized in that, The step of determining the regression analysis results of the target SQL statement on the new version database based on the structural difference degree and the performance offset degree includes: When the structural difference is zero and the performance deviation is within a preset threshold range, it is determined that there is no regression. When the structural difference is zero and the performance offset is negative and its absolute value exceeds a preset threshold, it is determined to be a performance improvement; When the structural difference is not zero and the performance deviation exceeds a preset threshold range, it is determined that the optimizer has a plan selection bias. When the structural difference is not zero and the performance deviation is within a preset threshold range, it is determined that the optimizer has a plan structure change.

7. The method according to claim 6, characterized in that, When the optimizer problem type is plan selection bias, it also includes: Based on the plan locking method in the baseline record, the execution plan of the target SQL statement is locked to the candidate execution plan in the baseline record and then executed, and the actual execution plan and execution performance indicators are collected; If the actual execution plan is consistent with the candidate execution plan, and the execution performance metrics recover to the preset threshold range of the execution performance metrics in the baseline record, then it is determined that the optimizer has a plan selection change; otherwise, it is determined that the new version of the database has an execution engine performance degradation.

8. A testing apparatus for a database optimizer, characterized in that, The device includes: The test case construction module is used to determine the test case set of the target SQL statement based on the structured feature information of the target SQL statement and the preset optimizer decision knowledge base. Each test case includes a candidate execution plan, data feature parameters and plan locking method. The test data generation module is used to generate a test dataset for each test case based on the data feature parameters, iteratively execute the target SQL statement, and correct the test dataset based on optimizer trace information until the actual execution plan of the target SQL statement is consistent with the candidate execution plan. The test case verification module is used to execute the target SQL statement according to the default execution method and at least one intervention execution method based on each test dataset, collect the actual execution plan and execution performance indicators under each execution method, and verify the test case according to the actual execution plan and the execution performance indicators; The baseline storage module is used to store the data feature parameters, candidate execution plans, and execution performance metrics under the default execution mode of verified test cases into the baseline library. The regression analysis module is used to reconstruct the test dataset based on the baseline library and perform regression comparisons when the database version changes.

9. An electronic device, characterized in that, The electronic device includes: At least one processor; and a memory communicatively connected to the at least one processor; The memory stores a computer program that can be executed by the at least one processor, which is then executed by the at least one processor to enable the at least one processor to perform the test method of the database optimizer according to any one of claims 1-7.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that, when executed by a processor, implement the test method of the database optimizer according to any one of claims 1-7.

Images