Evaluation method, device, equipment and medium for machine learning query optimization

By obtaining the original statement and its original plan, using machine learning-based explorers and selectors for execution plan exploration and selection, generating plan candidate sets, and uniformly evaluating them based on the total execution parameters, the evaluation of machine learning query optimization method is solved. The accuracy and efficiency issues have achieved efficient evaluation results.

CN118535435BActive Publication Date: 2024-11-29SHENZHEN INST OF COMPUTING SCI
View PDF 2 Cites 0 Cited by

Patent Information

Application Number
CN202410665259.1
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2024-05-27
Publication Date
2024-11-29
Estimated Expiration
2044-05-27

AI Technical Summary

Technical Problem

In the prior art, the evaluation of machine learning query optimization methods lacks a unified benchmark and evaluation system, resulting in inaccurate evaluation results and low efficiency.

Method used

By obtaining the original statement and its original plan, using preset explorers and selectors based on machine learning to explore and select execution plans, generate plan candidate sets, and uniformly evaluate them based on the total execution parameters and evaluation reports to improve the evaluation results. Accuracy and efficiency.

Benefits of technology

A unified evaluation system for machine learning query optimization methods is realized, which improves the accuracy and efficiency of evaluation results and reduces the cumbersomeness of comparison experiments.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118535435B_ABST
    Figure CN118535435B_ABST
Patent Text Reader

Abstract

The present application is applicable to the technical field of database optimization, and in particular, relates to an evaluation method, device, equipment and medium for machine learning query optimization. The method obtains the original statement and the original plan, uses an explorer built based on machine learning to explore the execution plan for the original statement, obtains a plan candidate set, performs an indicator evaluation on the explorer based on the plan candidate set and the original plan, obtains a first evaluation report, uses a selector built based on machine learning to select a plan from the plan candidate set and the original plan, obtains a selection result, performs an indicator evaluation on the selector based on the selection result, obtains a second evaluation report, and after the original statement is executed, evaluates the explorer and the selector based on the total execution parameters, the first evaluation report and the second evaluation report to obtain an evaluation result. The method solves the problem of lack of a unified benchmark and evaluation system for the evaluation of machine learning query optimization, and improves the accuracy of the evaluation results.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present application is applicable to the field of database optimization technology, and in particular relates to an evaluation method, device, equipment and medium for machine learning query optimization. Background Art

[0002] Database query optimization method is a key technology in database management system. Before executing query statement, the query optimizer in the database formulates the optimal execution plan for the query statement according to various judgment criteria to improve the execution efficiency of query statement and reduce the execution cost of query statement. With the widespread application of machine learning technology in the field of database, machine learning query optimization method has also emerged. This machine learning query optimization method is to use machine learning technology to perform the work of query optimizer in database. With the introduction of more and more machine learning query optimization methods and models, relevant researchers and developers usually need to conduct a large number of complicated comparative experiments on various machine learning query optimization methods in order to evaluate the real performance of various machine learning query optimization methods. However, the comparison methods, evaluation indicators, comparison objects and data sets used in different comparative experiments are different, resulting in the lack of unified benchmarks and evaluation systems for the evaluation of various machine learning query optimization methods, and thus the real performance of various machine learning query optimization methods cannot be understood efficiently and comprehensively. Therefore, how to improve the accuracy of the evaluation results of machine learning query optimization methods has become an urgent problem to be solved. Summary of the invention

[0003] In view of this, the embodiments of the present application provide an evaluation method, apparatus, device and medium for machine learning query optimization to solve the problem of how to improve the accuracy of the evaluation results of the machine learning query optimization method.

[0004] In a first aspect, an embodiment of the present application provides an evaluation method for machine learning query optimization, the evaluation method comprising:

[0005] Obtaining an original statement and an original plan corresponding to the original statement;

[0006] Use a preset explorer built based on machine learning in a preset database to perform execution plan exploration for the original statement to obtain a plan candidate set, and perform an indicator evaluation on the preset explorer based on the plan candidate set and the original plan to obtain a first evaluation report;

[0007] Using a preset selector constructed based on machine learning in the preset database, selecting a plan from the plan candidate set and the original plan to obtain a selection result, and performing an indicator evaluation on the preset selector according to the selection result to obtain a second evaluation report;

[0008] After the original statement is executed, the total execution parameters are obtained, and the preset explorer and the preset selector are evaluated according to the total execution parameters, the first evaluation report and the second evaluation report to obtain an evaluation result.

[0009] In a second aspect, an embodiment of the present application provides an evaluation device for machine learning query optimization, the evaluation device comprising:

[0010] A statement acquisition module, used to acquire an original statement and an original plan corresponding to the original statement;

[0011] An explorer evaluation module, used to use a preset explorer built based on machine learning in a preset database to perform execution plan exploration for the original statement, obtain a plan candidate set, and perform an indicator evaluation on the preset explorer according to the plan candidate set and the original plan to obtain a first evaluation report;

[0012] A selector evaluation module, configured to use a preset selector constructed based on machine learning in the preset database to select a plan from the plan candidate set and the original plan, obtain a selection result, and perform an indicator evaluation on the preset selector according to the selection result to obtain a second evaluation report;

[0013] An evaluation module is used to obtain total execution parameters after the original statement is executed, and to evaluate the preset explorer and the preset selector according to the total execution parameters, the first evaluation report and the second evaluation report to obtain an evaluation result.

[0014] In a third aspect, an embodiment of the present application provides a computer device, comprising a processor, a memory, and a computer program stored in the memory and executable on the processor, wherein the processor implements the evaluation method described in the first aspect when executing the computer program.

[0015] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the evaluation method as described in the first aspect is implemented.

[0016] Compared with the prior art, the embodiments of the present application have the following beneficial effects: the present application obtains the original statement and the original plan corresponding to the original statement, uses the preset explorer constructed based on machine learning to perform execution plan exploration for the original statement, obtains a plan candidate set, performs an index evaluation on the preset explorer based on the plan candidate set and the original plan, obtains a first evaluation report, uses the preset selector constructed based on machine learning to select a plan from the plan candidate set and the original plan, obtains a selection result, performs an index evaluation on the preset selector based on the selection result, obtains a second evaluation report, and after the original statement is executed, evaluates the preset explorer and the preset selector based on the total execution parameters, the first evaluation report and the second evaluation report to obtain an evaluation result. Among them, by performing independent and unified index evaluations on the preset explorer and the preset selector constructed based on machine learning based on the original plan corresponding to the original statement, and automatically generating a unified evaluation result based on the total execution parameters, the first evaluation report and the second evaluation report, the evaluation system of the machine learning query optimization method is unified, the accuracy of the evaluation result is improved, and the evaluation result can be quickly obtained without a large number of tedious comparative experiments, thereby improving the efficiency of the evaluation. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.

[0018] Figure 1 This is a schematic diagram of an application environment of an evaluation method for machine learning query optimization provided in Example 1 of the present application;

[0019] Figure 2 It is a flowchart of an evaluation method for machine learning query optimization provided in Example 2 of the present application;

[0020] Figure 3 This is a schematic diagram of the overall process of an evaluation method for machine learning query optimization provided in Example 2 of the present application;

[0021] Figure 4 It is a flowchart of an evaluation method for machine learning query optimization provided in Example 3 of the present application;

[0022] Figure 5 It is a flowchart of an evaluation method for machine learning query optimization provided in Example 4 of the present application;

[0023] Figure 6This is a flowchart of an evaluation method for machine learning query optimization provided in Example 5 of the present application;

[0024] Figure 7 This is a flowchart of an evaluation method for machine learning query optimization provided in Example 6 of the present application;

[0025] Figure 8 This is a flow chart of an evaluation method for machine learning query optimization provided in Example 7 of the present application;

[0026] Fig. 9 This is a flow chart of an evaluation method for machine learning query optimization provided in Example 8 of the present application;

[0027] Fig.10 It is a structural schematic diagram of an evaluation device for machine learning query optimization provided in Example 9 of the present application;

[0028] Fig.11 It is a structural diagram of a computer device provided in Example 10 of the present application. DETAILED DESCRIPTION

[0029] In the following description, specific details such as specific system structures, technologies, etc. are provided for the purpose of illustration rather than limitation, so as to provide a thorough understanding of the embodiments of the present application. However, it should be clear to those skilled in the art that the present application may also be implemented in other embodiments without these specific details. In other cases, detailed descriptions of well-known systems, devices, circuits, and methods are omitted to prevent unnecessary details from obstructing the description of the present application.

[0030] It should be understood that when used in the present specification and the appended claims, the term "comprising" indicates the presence of described features, wholes, steps, operations, elements and / or components, but does not exclude the presence or addition of one or more other features, wholes, steps, operations, elements, components and / or combinations thereof.

[0031] It should also be understood that the term “and / or” used in the specification and appended claims refers to any and all possible combinations of one or more of the associated listed items, and includes these combinations.

[0032] As used in the specification and appended claims of this application, the term "if" can be interpreted as "when" or "uponce" or "in response to determining" or "in response to detecting", depending on the context. Similarly, the phrase "if it is determined" or "if [described condition or event] is detected" can be interpreted as meaning "uponce it is determined" or "in response to determining" or "uponce [described condition or event] is detected" or "in response to detecting [described condition or event]", depending on the context.

[0033] In addition, in the description of the present application specification and the appended claims, the terms "first", "second", "third", etc. are only used to distinguish the descriptions and cannot be understood as indicating or implying relative importance.

[0034] References to "one embodiment" or "some embodiments" etc. described in the specification of this application mean that one or more embodiments of the present application include specific features, structures or characteristics described in conjunction with the embodiment. Therefore, the statements "in one embodiment", "in some embodiments", "in some other embodiments", "in some other embodiments", etc. that appear in different places in this specification do not necessarily refer to the same embodiment, but mean "one or more but not all embodiments", unless otherwise specifically emphasized in other ways. The terms "including", "comprising", "having" and their variations all mean "including but not limited to", unless otherwise specifically emphasized in other ways.

[0035] The embodiments of the present application can acquire and process relevant data based on artificial intelligence technology. Artificial intelligence is the theory, method, technology and application system that uses digital computers or machines controlled by digital computers to simulate, extend and expand human intelligence, perceive the environment, acquire knowledge and use knowledge to obtain the best results.

[0036] AI basic technologies generally include sensors, dedicated AI chips, cloud computing, distributed storage, big data processing technology, operation / interaction systems, mechatronics, etc. AI software technologies mainly include computer vision technology, robotics technology, biometrics technology, speech processing technology, natural language processing technology, and machine learning / deep learning.

[0037] It should be understood that the size of the serial numbers of the steps in the following embodiments does not mean the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of the present application.

[0038] In order to illustrate the technical solution of the present application, a specific embodiment is provided below for illustration.

[0039] The evaluation method for machine learning query optimization provided in the first embodiment of the present application can be applied to Figure 1In the application environment, the server communicates with the client, the server provides the evaluation service of machine learning query optimization, and the client triggers the evaluation task to the server. The client includes but is not limited to PDA, desktop computer, notebook computer, ultra-mobile personal computer (UMPC), netbook, cloud computer equipment, personal digital assistant (PDA) and other devices. The computer equipment corresponding to the server can be implemented by an independent server or a server cluster composed of multiple servers.

[0040] See also Figure 2 , is a flow chart of an evaluation method for machine learning query optimization provided in Example 2 of the present application. The evaluation method for machine learning query optimization is applied to Figure 1 The server in the example connects to the client to obtain the original statement sent by the client and the original plan corresponding to the original statement. Figure 2 As shown, the evaluation method may include the following steps:

[0041] Step S201, obtaining an original statement and an original plan corresponding to the original statement.

[0042] In the embodiments of the present application, the original statement may refer to a database query statement. The query language corresponding to the original statement needs to be a query language that can be processed by the server. The original statement can be used to query, insert, delete and other operations on the data stored in the database. The original statement can come from a database benchmark test, or a user-defined statement and corresponding data. The original plan may refer to the execution path of the original statement in the database automatically generated by the query optimizer built into the database, wherein the query optimizer can be used to formulate an execution plan for the execution of the original statement according to various judgment criteria.

[0043] For example, if there is a table called Students, which includes student ID, name, age and other information, then the Students table is queried for student information whose student ID (StudentID) is greater than 10, and the information is output in ascending order according to the student ID. The original statement can be: "SELECT * FROM Students WHERE StudentID>10 ORDER BY StudentID ASC;" The original plan corresponding to the original statement can be: 1) Scan the Students table to obtain records of all students, filter the obtained student records, and only retain student records whose student ID is greater than 10; 2) Sort the results in 1) in ascending order according to the student ID; 3) Return the query results in 2).

[0044] In the process of obtaining the original plan corresponding to the original statement, first, the obtained original statement is parsed grammatically and semantically. Then, based on the results of grammatical and semantic parsing, the query optimizer generates multiple possible execution plans as candidate plans for the execution of the original statement. Finally, the query optimizer evaluates the execution of each candidate plan based on factors such as data volume, time complexity, and resource consumption, obtains an evaluation result, and selects an optimal execution plan from the candidate plans as the original plan corresponding to the original statement based on the evaluation result.

[0045] Step S202: Use a preset explorer built based on machine learning in a preset database to explore an execution plan for the original statement to obtain a candidate plan set, and perform an indicator evaluation on the preset explorer based on the candidate plan set and the original plan to obtain a first evaluation report.

[0046] In the embodiment of the present application, machine learning may refer to simulating human learning behavior through computers, learning from historical data or experience, automatically discovering rules or making predictions based on the results of learning, and then developing algorithms or research methods. The work of the query optimizer in the database through machine learning technology is a machine learning query optimization method. For example, the machine learning query optimization method may refer to a model trained using relevant historical data based on machine learning technology. The model can generate a series of possible execution plans based on the input original statement as a plan candidate set, and select the best execution plan from the plan candidate set. According to the query optimization process, the machine learning query optimization method can be divided into two main parts, namely: 1) Execution of the original statement generates a series of possible execution plans as a plan candidate set; 2) Use a machine learning algorithm to select the best execution plan from the plan candidate set, where the first main part of the machine learning query optimization method, that is, the process of generating a plan candidate set, can be regarded as a candidate plan exploration process, which can be abstracted as an explorer part.

[0047] The preset database may refer to a pre-set database management system, the plan candidate set may refer to a set of multiple possible execution plans generated by a preset explorer for the execution of the original statement, the indicator evaluation may refer to the process of measuring and evaluating the preset explorer through a series of evaluation indicators. For example, the evaluation indicators of the indicator evaluation may include the quality of the plan candidate set, the scalability of the plan candidate set, and the stability of the plan candidate set, etc. The first evaluation report may refer to the comprehensive evaluation results of the indicator evaluation of the preset explorer.

[0048] For example, in the process of obtaining the first evaluation report, the original statement can be input into a model that has been trained for machine learning query optimization. After the explorer part of the machine learning query optimization model, the execution plan of the original statement is explored to obtain a series of possible execution plans as a candidate plan set. According to preset evaluation indicators, such as the quality, effectiveness, scalability and stability of the candidate plan set, each candidate plan in the candidate plan set and the original plan are evaluated according to a preset evaluation scheme to obtain an evaluation result. The evaluation result can represent the performance of the explorer part in the machine learning query optimization model. The evaluation results can be sorted and converted to obtain the first evaluation report.

[0049] Step S203, using a preset selector built based on machine learning in a preset database, select a plan from a set of plan candidates and an original plan to obtain a selection result, and based on the selection result, perform an indicator evaluation on the preset selector to obtain a second evaluation report.

[0050] In an embodiment of the present application, based on the second main part of the machine learning query optimization method in the above step S202, the process of selecting the optimal execution plan from the plan candidate set and the original plan can be abstracted as a selector part, and the selection result may refer to the execution plan selected by the preset selector from the plan candidate set and the original plan, and the second evaluation report may refer to the comprehensive evaluation result of the indicator evaluation of the preset selector.

[0051] For example, in the process of obtaining the second evaluation report, the plan candidate set output by the above-mentioned explorer part and the obtained original plan can be input into the selector part of the model that has been trained for machine learning query optimization. After the selector part, the execution plan corresponding to the original statement is selected from the plan candidate set and the original plan as the selection result, and the selector part makes multiple selections on the plan candidate set and the original plan to obtain multiple selection results. Based on the multiple selection results and according to preset evaluation indicators, such as selector capability, whether multiple features are learned and stability, etc., the preset selector is evaluated to obtain evaluation results. The evaluation results are sorted and converted to obtain the second evaluation report.

[0052] Optionally, a pre-processed standard candidate set containing a plan candidate set and an original plan can be imported from outside the database and directly input into a preset selector, so that the selector makes multiple selections from the standard candidate set to obtain multiple selection results, and based on the selection results, an indicator evaluation is performed on the preset selector to obtain a second evaluation report.

[0053] Step S204, after the original statement is executed, the total execution parameters are obtained, and the preset explorer and the preset selector are evaluated according to the total execution parameters, the first evaluation report and the second evaluation report to obtain an evaluation result.

[0054] In an embodiment of the present application, the total execution parameter may refer to the execution plan automatically generated by the preset explorer and the preset selector to execute the original statement, and the execution parameter obtained after the original statement is executed. For example, the total execution parameter may be the total execution time of the original statement and the proportion of query optimization, etc. The evaluation result may refer to the comprehensive evaluation result of the preset explorer and the preset selector.

[0055] In the process of obtaining the comprehensive evaluation results of the preset explorer and the preset selector, the original statement can be executed according to the execution plan selected by the machine learning query optimization method and the original plan selected by the built-in query optimizer of the database. After the original statement is executed, the execution time and resource consumption value data of the execution plan obtained by the machine learning query optimization method and the original plan obtained by the query optimizer are obtained respectively. Based on the execution time and resource consumption value data of the two, the optimization ratio of the machine learning query optimization method compared with the query optimizer is calculated, and the optimization ratio can be the total execution parameter. The total execution parameter, the first evaluation report and the second evaluation report can be sorted to obtain the comprehensive evaluation results of the explorer and the selector.

[0056] like Figure 3 The figure shows an overall flow chart of an evaluation method for machine learning query optimization provided in Example 2 of the present application, see Figure 3 , if two machine learning query optimization methods need to be evaluated, namely machine learning query optimization method 1 and machine learning query optimization method 2, the machine learning query optimization method 1 corresponds to explorer 1 and selector 1, and the machine learning query optimization method 2 corresponds to explorer 2 and selector 2, then the overall process of evaluating the machine learning query optimization method 1 and the machine learning query optimization method 2 can be:

[0057] 1) The user configures the initialization parameter file such as connection database parameters, workload and test times through the corresponding application interface, and sends the configured parameter file to the corresponding database through the evaluation system agent; 2) The original statement and the original plan corresponding to the original statement generated by the database query optimizer are obtained; 3) The execution of the original statement is explored by explorer 1 to obtain plan candidate set 1, and the execution of the original statement is explored by explorer 2 to obtain plan candidate set 2; 4) The plan candidate set 1, the plan candidate set 2 and the original plan are input into the explorer evaluation system, the explorer 1 is evaluated according to the plan candidate set 1 and the original plan, and the first evaluation report 1 of the explorer 1 is obtained, and the explorer 2 is evaluated according to the plan candidate set 2 and the original plan, and the first evaluation report 2 of the explorer 2 is obtained; 5) Input the plan candidate set 1 and the original plan, or the plan candidate set 2 and the original plan, generated by the explorer in 4) above into the selector evaluation system, and perform indicator evaluation on selector 1 and selector 2. If the plan candidate set 1 and the original plan generated by explorer 1 are input into the selector evaluation system, then the indicators of selector 1 and selector 2 are evaluated respectively according to the plan candidate set 1 and the original plan, and a second evaluation report 1 of selector 1 and a second evaluation report 2 of selector 2 are obtained respectively; 6) Comprehensively evaluate the machine learning query optimization 1 according to the total execution parameters, the first evaluation report 1 and the second evaluation report 1 to obtain the evaluation result 1 of the machine learning query optimization method 1, and comprehensively evaluate the machine learning query optimization 2 according to the total execution parameters, the first evaluation report 2 and the second evaluation report 2 to obtain the evaluation result 2 of the machine learning query optimization method 2.

[0058] Optionally, a pre-processed standard candidate set including a plan candidate set and an original plan may be imported from outside the database and input into the selector evaluation system to execute the step of evaluating selector 1 and selector 2 in 5) above.

[0059] The embodiment of the present application obtains the original statement and the original plan corresponding to the original statement, uses the preset explorer built based on machine learning to explore the execution plan for the original statement, obtains a plan candidate set, performs an index evaluation on the preset explorer based on the plan candidate set and the original plan, obtains a first evaluation report, uses the preset selector built based on machine learning to select a plan from the plan candidate set and the original plan, obtains a selection result, performs an index evaluation on the preset selector based on the selection result, obtains a second evaluation report, and after the original statement is executed, evaluates the preset explorer and the preset selector based on the total execution parameters, the first evaluation report and the second evaluation report to obtain an evaluation result. Among them, by performing independent and unified index evaluations on the preset explorer and the preset selector built based on machine learning based on the original plan corresponding to the original statement, and automatically generating a unified evaluation result based on the total execution parameters, the first evaluation report and the second evaluation report, the evaluation system of the machine learning query optimization method is unified, the accuracy of the evaluation result is improved, and the evaluation result can be quickly obtained without a large number of tedious comparative experiments, thereby improving the efficiency of the evaluation.

[0060] See also Figure 4 , is a flow chart of an evaluation method for machine learning query optimization provided in Example 3 of this application. Figure 4 As shown, the evaluation method may also include the following steps:

[0061] Step S401, obtaining a preset visualization function.

[0062] Step S402: Use a preset visualization function to analyze the total execution parameters, the first evaluation report, the second evaluation report, and the evaluation results to obtain a visualization chart.

[0063] Step S403: display the visualization chart.

[0064] In an embodiment of the present application, the preset visualization function may refer to a pre-set function for visually representing data, and the visualization chart may refer to a single graphical representation of the overall execution parameters, the first evaluation report, the second evaluation report, and any one of the evaluation results, or a combined graphical representation of any two, three, or more of the overall execution parameters, the first evaluation report, the second evaluation report, and the evaluation results.

[0065] In the process of obtaining visualization charts, first, the data in the total execution parameters, the first evaluation report, the second evaluation report and the evaluation results can be preprocessed by conversion or normalization, and then, the preprocessed data can be analyzed through preset visualization functions, such as line chart functions and bar chart functions, and one or more corresponding visualization charts can be generated according to specific needs, and the visualization charts can be displayed through an interactive interface.

[0066] The embodiment of the present application uses a preset visualization function to analyze the total execution parameters, the first evaluation report, the second evaluation report, and the evaluation results to obtain a visualization chart and display the visualization chart. By converting the data into an intuitive chart form, the intuitiveness of the result is enhanced, which facilitates the user to understand the distribution, trend, and relationship of the data, thereby improving the efficiency of data analysis.

[0067] See also Figure 5 , is a flow chart of an evaluation method for machine learning query optimization provided in Example 4 of the present application. Figure 5 As shown, in the above step S202, the preset explorer is evaluated according to the plan candidate set and the original plan to obtain a first evaluation report, which may include the following steps:

[0068] Step S501, compare each candidate plan in the plan candidate set with the original plan, determine a first target plan subset that is better than the original plan, a second target plan subset that is worse than the original plan, and the optimal optimization parameters of the optimal plan in the first target plan subset compared to the original plan.

[0069] In an embodiment of the present application, a candidate plan may refer to the execution path of the original statement generated by a preset explorer, the first target plan subset may refer to a set of candidate plans that are better than the original plan, the second target plan subset may refer to a set of candidate plans that are worse than the original plan, the optimal plan may refer to the best candidate plan in the first target plan subset, and the optimal optimization parameter may refer to the optimization ratio of the optimal plan compared to the original plan.

[0070] In the process of determining the first target plan subset and the second target plan subset, the original plan and each candidate plan in the plan candidate set can be directly executed to determine the first target plan subset and the second target plan subset, or the execution process of the original plan and each candidate plan in the plan candidate set can be simulated and evaluated through corresponding simulation tools to determine the first target plan subset and the second target plan subset. For example, the first target plan subset and the second target plan subset are determined by direct execution. First, the original statement is executed according to the original plan. After the original statement is executed, the execution time, system resource consumption, and accuracy of the execution result of the original statement with the original plan are recorded. Secondly, each candidate plan in the plan candidate set is executed serially, and the execution time, system resource consumption, and accuracy of the execution result of the original statement with each candidate plan are recorded. Then, based on the execution time, system resource consumption, and accuracy of the execution result, the comprehensive execution result of the original plan and each candidate plan is determined. Finally, the set of candidate plans whose comprehensive execution results are better than the comprehensive execution results of the original plan is determined as the first target plan subset. Accordingly, the set of candidate plans whose comprehensive execution results are worse than the comprehensive execution results of the original plan is determined as the second target plan subset. According to the comprehensive execution results of each plan in the first target plan subset, the optimal plan is determined. According to the comprehensive execution results of the optimal plan and the original plan, the optimization ratio of the optimal plan compared with the original plan is determined. The optimization ratio is the optimal optimization parameter.

[0071] Step S502, evaluating the quality index of the plan candidate set according to the number of candidate plans in the first target plan subset, the optimal optimization parameters and the second target plan subset, and obtaining a quality index evaluation result.

[0072] In an embodiment of the present application, the quality indicator may refer to an indicator that reflects the quality of the plan candidate set, and the quality indicator evaluation result may refer to the evaluation result of evaluating the plan candidate set based on the quality indicator. For example, the quality indicator evaluation result may be in the form of percentage, grade, table, etc.

[0073] In the process of obtaining the quality index evaluation result, first, according to the content in the above step S401, the number of candidate plans in the first target plan subset, the number of candidate plans in the second target plan subset and the optimal optimization parameters can be determined, and then, according to the second target plan subset and the original plan, the average degradation ratio of the second target plan subset compared with the original plan is calculated, and the worst degradation ratio of the worst candidate plan in the second target plan subset compared with the original plan is calculated. According to the calculated degradation ratio and the worst degradation ratio, the cost of the explorer selecting the wrong candidate plan is comprehensively obtained. Finally, the number of candidate plans in the first target plan subset, the number of candidate plans in the second target plan subset, the optimal optimization parameters and the cost of the explorer selecting the wrong candidate plan are integrated and transformed to obtain the quality index evaluation result.

[0074] Step S503, evaluating the effectiveness index of the plan candidate set according to the number and plan diversity of each type of candidate plans in the plan candidate set, and obtaining an effectiveness index evaluation result.

[0075] In an embodiment of the present application, plan diversity may refer to the difference between each candidate plan and the original plan, wherein, if the difference is smaller, the plan diversity is lower, and if the difference is greater, the plan diversity is higher. The effectiveness index may refer to an indicator reflecting the effectiveness of the generated plan candidate set, and the effectiveness index evaluation result may refer to the evaluation result of evaluating the plan candidate set according to the effectiveness index.

[0076] In the process of obtaining the effectiveness index evaluation result, the number of candidate plans of each type in the plan candidate set can be determined according to the execution path of each candidate plan, and the plan diversity can be calculated based on the average distance calculation method. The number of candidate plans of each type in the plan candidate set and the plan diversity result can be integrated and transformed to obtain the effectiveness index evaluation result. For example, in the process of calculating the plan diversity based on the average distance calculation method, first, according to the preset distance measurement method, the distance between each candidate plan and the original plan in the plan candidate set is calculated, and each candidate plan and the original plan is regarded as a plan pair. Then, the distances of all the calculated plan pairs are added up, and the result of the addition is divided by the number of plan pairs to obtain the average distance. Finally, the calculated average distance can represent the plan diversity of the plan candidate set.

[0077] Optionally, the average edit distance of the plan candidate set can be obtained based on the edit distance between any two candidate plans in the plan candidate set. The calculated average edit distance can also represent the plan diversity of the plan candidate set. That is, the larger the average edit distance, the higher the plan diversity is considered to be, and correspondingly, the smaller the average distance, the lower the plan diversity is considered to be.

[0078] Step S504, evaluating the scalability index of the plan candidate set to obtain a scalability index evaluation result.

[0079] In the embodiment of the present application, the scalability index may refer to an index reflecting the scalability of the plan candidate set, and the scalability index evaluation result may refer to an evaluation result of evaluating the plan candidate set according to the scalability index.

[0080] In the process of obtaining the scalability index evaluation result, first, by increasing the number of generated candidate plans, the explorer generates a plan candidate set based on the original statement multiple times, and then, the plan candidate set generated each time can be deduplicated according to the execution path of each candidate plan to determine the maximum number of candidate plans that the explorer can explore after deduplication, and according to the contents in step S501 and step S502, determine whether the quality index evaluation result of the plan candidate set is improved after increasing the number of generated candidate plans. Finally, the scalability index evaluation result of the plan candidate set is determined according to the maximum number of candidate plans that the explorer can explore after deduplication and whether the quality of the candidate set will be improved after increasing the size of the candidate set.

[0081] Step S505: Write the quality indicator evaluation result, the effectiveness indicator evaluation result and the scalability indicator evaluation result into the first evaluation report.

[0082] The quality index evaluation results, effectiveness index evaluation results and scalability index evaluation results obtained in the above steps S501 to S504 are integrated and converted to obtain the first evaluation report of the explorer.

[0083] The embodiment of the present application obtains a first evaluation report by conducting a multi-angle evaluation of the explorer in terms of quality, effectiveness, and scalability, and provides a unified benchmark and evaluation system, so that relevant personnel can have a more comprehensive understanding of the true performance of the explorer, thereby improving the accuracy of the evaluation results of comparing different explorers.

[0084] See also Figure 6 , is a flow chart of an evaluation method for machine learning query optimization provided in Example 5 of the present application, such as Figure 6 As shown, the evaluation method may also include the following steps:

[0085] Step S601, for the same workload, repeatedly execute a preset number of tests using a preset explorer built based on machine learning in a preset database to explore the execution plan for the original statement, obtain a plan candidate set, and perform an indicator evaluation on the preset explorer based on the plan candidate set and the original plan to obtain a first evaluation report.

[0086] Step S602, obtaining a stability index under the same workload according to the quality index evaluation result corresponding to each test execution under the same workload.

[0087] In the embodiment of the present application, the workload may refer to the task volume of the query statement input into the explorer, and the preset number of tests may refer to the pre-set number of tests on the explorer under the same workload.

[0088] In the process of obtaining the stability index under the same workload, first, under the same workload, the explorer generates a set of plan candidate tests for a preset number of times, and obtains the quality index evaluation results of the plan candidate sets for each test. For specific quality index evaluation results, please refer to the contents of steps S501 to S502, and then, the variance value of the quality index evaluation results of the preset number of tests is calculated according to the variance calculation formula. The variance value is the stability index of the explorer under the same workload.

[0089] Step S603, for different workloads, repeatedly use a preset explorer built based on machine learning in a preset database to explore the execution plan for the original statement, obtain a plan candidate set, and perform an indicator evaluation on the preset explorer based on the plan candidate set and the original plan to obtain a first evaluation report.

[0090] Step S604, obtaining stability indicators under different workloads according to the quality indicator evaluation results corresponding to each workload under different workloads.

[0091] In the process of obtaining stability indicators under different workloads, first, for any different workload, the explorer generates a test of the plan candidate set to obtain the quality indicator evaluation results corresponding to each workload under different workloads. The specific quality indicator evaluation results can refer to the contents of steps S501 to S502, and then, the variance value of the quality indicator evaluation results under different workloads is calculated according to the variance calculation formula. The variance value is the stability indicator of the explorer under different workloads.

[0092] Step S605, obtaining a first stability index evaluation result of a preset explorer according to the stability index under the same workload and the stability index under different workloads, and writing the first stability index evaluation result into a first evaluation report.

[0093] In an embodiment of the present application, the first stability index evaluation result may refer to a comprehensive evaluation result reflecting the stability of the explorer.

[0094] The stability index under the same workload and the stability index under different workloads calculated above are integrated and converted to obtain the first stability index evaluation result of the explorer, and the first stability index evaluation result is written into the first evaluation report of the explorer.

[0095] The embodiment of the present application obtains the stability index of the explorer under the same workload by testing the explorer generation plan candidate set for a preset number of tests under the same workload, and obtains the stability index of the explorer under different workloads by testing the explorer generation plan candidate set under different workloads. According to the stability index of the explorer under the same workload and the stability index of the explorer under different workloads, a first stability index evaluation result of the preset explorer is obtained, and the first stability index evaluation result is written into a first evaluation report. By performing stability tests on the explorer under the same workload and under different workloads, the comprehensiveness and accuracy of the stability test results are improved.

[0096] See also Figure 7 , is a flow chart of an evaluation method for machine learning query optimization provided in Example 6 of the present application. Figure 7 As shown, the step S203 of performing an index evaluation on the preset selector according to the selection result to obtain a second evaluation report may include the following steps:

[0097] Step S701, based on the selection results, determines the number of candidate plans in the candidate set of plans after deduplication, the number of candidate plans better than the original plan and the quality score of the candidate set of plans, determines the selection frequency index, and determines the number and proportion of selecting the optimal plan, the number and proportion of selecting candidate plans better than the original plan, and the number and proportion of selecting the original plan.

[0098] In the embodiment of the present application, the selection times indicator may refer to the number of times a preset selector makes a selection.

[0099] According to the selection results, determine the number of candidate plans in the plan candidate set after deduplication, and refer to the content in step S504 for details. The number of candidate plans that are better than the original plan, and refer to the content in step S501 for details. Determine the quality score of the plan candidate set. The percentage of the number of candidate plans that are better than the original plan to the number of all candidate plans in the plan candidate set can be calculated. The calculated percentage is the quality score of the plan candidate set.

[0100] According to the selection results, the number and proportion of the optimal plan selected are determined. The plan selection test of the selector can be performed with a preset number of selections to obtain the selection results of the selector each time, determine the number of times the selector's selection result is the optimal plan, and calculate the percentage of the number of times the selection result is the optimal plan to the preset number of selections. The calculated percentage is the proportion of the optimal plan selected. If the plan candidate set selected by the selector is automatically generated by the explorer, the determination of the optimal plan can refer to the content in step S501. If the plan candidate set selected by the selector is a pre-processed standard candidate set containing plan candidate sets and original plans imported from outside the database, the optimal plan in the standard candidate set can be set in advance.

[0101] Based on the selection results, the number and proportion of candidate plans that are better than the original plan are determined. The number of candidate plans that are better than the original plan can be determined based on the selection results of each selector, and the percentage of the number of candidate plans that are better than the original plan is calculated to be the percentage of the preset number of selections. The calculated percentage is the proportion of candidate plans that are better than the original plan.

[0102] According to the selection results, the number of times and proportion of the original plan are selected can be determined. The selection results of the selector each time can be compared with the original plan to determine whether the selection result is the original plan, and the number of times the original plan is selected can be recorded. The percentage of the number of times the original plan is selected to the preset number of selections is calculated. The calculated percentage is the proportion of the original plan selected.

[0103] Step S702, calculating the configuration index evaluation result according to the number of candidate plans in the candidate set of plans after deduplication, the number of candidate plans better than the original plan, and the quality score.

[0104] In the embodiment of the present application, the configuration index evaluation result may refer to the comprehensive evaluation result of the configuration index of the plan candidate set. The configuration index evaluation result can be obtained by integrating and converting the number of candidate plans in the plan candidate set after deduplication, the number of candidate plans better than the original plan, and the quality score.

[0105] Step S703, according to the number and ratio of selecting the optimal plan, the number and ratio of selecting the candidate plan better than the original plan, and the number and ratio of selecting the original plan, the selection capability index evaluation result is calculated.

[0106] In the embodiment of the present application, the selection capability index evaluation result may refer to a comprehensive evaluation result of the selection capability of the selector. The selection capability index evaluation result can be obtained by integrating and converting the number and proportion of selecting the optimal plan, the number and proportion of selecting the candidate plan that is better than the original plan, and the number and proportion of selecting the original plan.

[0107] Step S704: Detect whether the selection result is the initial plan of the preset selector. If it is not the initial plan, calculate the learning metric evaluation result based on the score value and cost value of the selection result.

[0108] In the embodiments of the present application, the initial plan may refer to the original plan. The score value of the selection result may refer to the score value of the selection result calculated by the preset selector based on cost. The cost value of the selection result may refer to the cost value of the selection result calculated by the query optimizer built into the database based on cost.

[0109] Compare the execution path of the selection result with the execution path of the original plan. If the execution paths are the same, determine that the selection result is the initial plan. If it is not the initial plan, calculate the learning metric evaluation result based on the score value and cost value of the selection result.

[0110] For example, in the process of obtaining the learning metric evaluation result based on the score value and cost value of the selection result, if the preset selector selects three candidate plans with the smallest costs based on cost, denoted as Plan1, Plan2, and Plan3 respectively, the score values of these three candidate plans calculated by the preset selector are denoted as Score1, Score2, and Score3 respectively, and the cost values of these three candidate plans calculated by the query optimizer built into the database based on cost are denoted as Cost1, Cost2, and Cost3 respectively. If the score value and cost value of Plan1 are: {Score1: 1; Cost1: 10}, the score value and cost value of Plan2 are: {Score2: 2; Cost2: 20}, and the score value and cost value of Plan3 are: {Score3: 3; Cost3: 30}, then the sorting result of the three candidate plans in ascending order according to the score value (Score) is: Plan1 < Plan2 < Plan3, and the sorting result of the three candidate plans in ascending order according to the cost value (Cost) is: Plan1 < Plan2 < Plan3. It can be determined that the sorting results of the two parts are consistent, and it is considered that the preset selector overly relies on the score value of Cost and does not learn too many features, and the learning metric evaluation result of the preset selector is determined to be poor.

[0111] Step S705: Write the configuration metric evaluation result, selection count metric, selection ability metric evaluation result, and learning metric evaluation result into the second evaluation report.

[0112] Integrate and transform the configuration metric evaluation result, selection count metric, selection ability metric evaluation result, and learning metric evaluation result in the above steps S701 to S704 to obtain the second evaluation report on the selector.

[0113] The embodiment of the present application obtains a second evaluation report by performing a multi-angle evaluation of the selector's plan candidate set configuration, selection ability, and learning ability, providing a unified benchmark and evaluation system, so that relevant personnel can have a more comprehensive understanding of the true performance of the selector, thereby improving the accuracy of the evaluation results of comparing different selectors.

[0114] See also Figure 8 , is a flow chart of an evaluation method for machine learning query optimization provided in Example 7 of this application. Figure 8 As shown, the evaluation method also includes the following steps:

[0115] Step S801, for different workloads, repeatedly execute the preset selector built based on machine learning in the preset database, select a plan from the plan candidate set and the original plan, obtain a selection result, and based on the selection result, perform an indicator evaluation on the preset selector to obtain a second evaluation report.

[0116] Step S802, obtaining a second stability indicator evaluation result of a preset selector under different workloads according to the selection capability indicator evaluation result corresponding to each workload under different workloads.

[0117] Step S803: Write the second stability index evaluation result into the second evaluation report.

[0118] In the embodiment of the present application, the second stability index evaluation result may refer to a comprehensive evaluation result reflecting the stability of the selector.

[0119] In the process of obtaining the second stability index evaluation result, first, for any different workload, a test of the selector plan selection is performed to obtain the selection capability index evaluation result corresponding to each workload under different workloads. The specific selection capability index evaluation result can refer to the contents of step S701 to step S703, and then, the variance value of the selection capability index evaluation result under different workloads is calculated according to the variance calculation formula. The calculated variance value and the minimum value of the selection capability index evaluation result under different workloads are the second stability index evaluation result of the selector under different workloads, and the second stability index evaluation result is written into the second evaluation report.

[0120] The embodiment of the present application tests the selector's plan selection under different workloads to obtain the second stability index evaluation results of the selector under different workloads, and writes the second stability index evaluation results into the second evaluation report. By testing the stability of the selector under different workloads, the comprehensiveness and accuracy of the stability test results are improved.

[0121] See also Fig. 9 , is a flow chart of an evaluation method for machine learning query optimization provided in Example 8 of the present application. Fig. 9 As shown, before obtaining the original sentence and the original plan corresponding to the original sentence in the above step S201, the evaluation method may further include the following steps:

[0122] Step S901, configuring a parameter file, the parameter file includes a connection configuration for connecting to a preset database, a workload configuration, and a test times configuration.

[0123] Step S902, sending the parameter file to the system agent.

[0124] In an embodiment of the present application, a parameter file may refer to an initialization configuration file containing configuration parameters, and a connection configuration may refer to a parameter configuration for connecting to a database, such as the address and port number of a preset database server, etc. The system agent may be used to determine the preset database based on the parameter file, and send the workload configuration and the test number configuration to the preset database.

[0125] The embodiment of the present application configures a parameter file and sends the parameter file to a preset database through a system agent. After the initial configuration of the parameter file is completed, the idle computing resources can be used in the background to complete the evaluation of the explorer and the selector, and an evaluation report and score can be given according to a unified evaluation system. Relevant personnel can customize the parameter file according to specific evaluation requirements, meet different evaluation scenarios of machine learning query optimization, and effectively utilize idle computing resources, simplify the operational process of machine learning query optimization evaluation, and improve the efficiency of evaluation.

[0126] Corresponding to the evaluation method for machine learning query optimization in the above embodiment, Fig.10 The structural block diagram of the evaluation device for machine learning query optimization provided by the ninth embodiment of the present application is shown. The evaluation device is applied to Figure 1 The server in the embodiment of the present invention is connected to the client to obtain the original statement sent by the client and the original plan corresponding to the original statement. For the convenience of explanation, only the part related to the embodiment of the present application is shown.

[0127] See also Fig.10 , the evaluation device comprises:

[0128] A statement acquisition module 1001 is used to acquire an original statement and an original plan corresponding to the original statement;

[0129] An explorer evaluation module 1002 is used to use a preset explorer built based on machine learning in a preset database to perform execution plan exploration for the original statement to obtain a plan candidate set, and perform an indicator evaluation on the preset explorer according to the plan candidate set and the original plan to obtain a first evaluation report;

[0130] A selector evaluation module 1003 is used to use a preset selector constructed based on machine learning in the preset database to select a plan from the plan candidate set and the original plan, obtain a selection result, and perform an indicator evaluation on the preset selector according to the selection result to obtain a second evaluation report;

[0131] The evaluation module 1004 is used to obtain the total execution parameters after the execution of the original statement is completed, and evaluate the preset explorer and the preset selector according to the total execution parameters, the first evaluation report and the second evaluation report to obtain an evaluation result.

[0132] Optionally, the evaluation device further includes:

[0133] Function acquisition module, used to obtain preset visualization functions;

[0134] An analysis module, configured to analyze the total execution parameters, the first evaluation report, the second evaluation report, and the evaluation result using the preset visualization function to obtain a visualization chart;

[0135] A display module is used to display the visual chart.

[0136] Optionally, the explorer evaluation module 1002 includes:

[0137] A comparison unit, used for comparing each candidate plan in the plan candidate set with the original plan, to determine a first target plan subset that is better than the original plan, a second target plan subset that is worse than the original plan, and an optimal optimization parameter of the optimal plan in the first target plan subset compared with the original plan;

[0138] A quality indicator evaluation unit, configured to evaluate the quality indicator of the candidate plan set according to the number of candidate plans in the first target plan subset, the optimal optimization parameter and the second target plan subset, to obtain a quality indicator evaluation result;

[0139] An effectiveness index evaluation unit, used to evaluate the effectiveness index of the plan candidate set according to the number and plan diversity of each type of candidate plans in the plan candidate set, and obtain an effectiveness index evaluation result;

[0140] A scalability index evaluation unit, used to evaluate the scalability index of the plan candidate set to obtain a scalability index evaluation result;

[0141] The first writing unit is used to write the quality indicator evaluation result, the effectiveness indicator evaluation result and the scalability indicator evaluation result into a first evaluation report.

[0142] Optionally, the evaluation device further includes:

[0143] A first execution module is used to repeatedly execute the preset test times for the same workload, using the preset explorer built based on machine learning in a preset database to perform execution plan exploration for the original statement, obtain a plan candidate set, and perform indicator evaluation on the preset explorer according to the plan candidate set and the original plan to obtain a first evaluation report;

[0144] A first stability index evaluation module is used to obtain a stability index under the same workload according to a quality index evaluation result corresponding to each test execution under the same workload;

[0145] A second execution module is used to repeatedly execute the execution plan exploration for the original statement using a preset explorer built based on machine learning in a preset database for different workloads to obtain a plan candidate set, and perform an indicator evaluation on the preset explorer according to the plan candidate set and the original plan to obtain a first evaluation report;

[0146] A second stability index evaluation module is used to obtain the stability index under the different workloads according to the quality index evaluation result corresponding to each workload under the different workloads;

[0147] The second writing module is used to obtain the first stability index evaluation result of the preset explorer according to the stability index under the same workload and the stability index under the different workloads, and write the first stability index evaluation result into the first evaluation report.

[0148] Optionally, the selector evaluation module 1003 includes:

[0149] a determination unit, configured to determine, according to the selection result, the number of candidate plans in the candidate plan set after deduplication, the number of candidate plans better than the original plan, and the quality score of the candidate plan set, determine a selection frequency index, and determine the number and ratio of selecting the optimal plan, the number and ratio of selecting the candidate plans better than the original plan, and the number and ratio of selecting the original plan;

[0150] A configuration indicator evaluation unit, configured to calculate a configuration indicator evaluation result according to the number of candidate plans in the plan candidate set after deduplication, the number of candidate plans better than the original plan, and the quality score;

[0151] A selection capability indicator evaluation unit is used to calculate the selection capability indicator evaluation result according to the number and proportion of selecting the optimal plan, the number and proportion of selecting the candidate plan better than the original plan, and the number and proportion of selecting the original plan;

[0152] A learning indicator evaluation unit, used to detect whether the selection result is an initial plan of the preset selector, and if it is not the initial plan, calculate the learning indicator evaluation result according to the score value of the selection result and the cost value of the selection result;

[0153] The third writing unit is used to write the configuration index evaluation result, the selection number index, the selection ability index evaluation result and the learning index evaluation result into a second evaluation report.

[0154] Optionally, the evaluation device further includes:

[0155] A third execution module is used to repeatedly execute the preset selector constructed based on machine learning in the preset database for different workloads, select a plan from the plan candidate set and the original plan, obtain a selection result, and perform an indicator evaluation on the preset selector according to the selection result to obtain a second evaluation report;

[0156] A third stability index evaluation module is used to obtain a second stability index evaluation result of the preset selector under the different workloads according to the selection capability index evaluation result corresponding to each workload under the different workloads;

[0157] The fourth writing module is used to write the second stability indicator evaluation result into the second evaluation report.

[0158] Optionally, the evaluation device further includes:

[0159] A configuration module, used to configure a parameter file, wherein the parameter file includes a connection configuration for connecting to a preset database, a workload configuration, and a test number configuration;

[0160] A sending module is used to send the parameter file to the system agent, and the system agent is used to determine the preset database according to the parameter file to send the workload configuration and the test number configuration to the preset database.

[0161] It should be noted that the information interaction, execution process and other contents between the above-mentioned modules are based on the same concept as the method embodiment of the present application. Their specific functions and technical effects can be found in the method embodiment part and will not be repeated here.

[0162] Fig.11 This is a schematic diagram of the structure of a computer device provided in Example 10 of the present application. Fig.11 As shown, the computer device of this embodiment includes: at least one processor ( Fig.11 Only one is shown), a memory, and a computer program stored in the memory and executable on at least one processor, wherein when the processor executes the computer program, the steps in any of the above-mentioned evaluation method embodiments for machine learning query optimization are implemented.

[0163] The computer device may include, but is not limited to, a processor and a memory. Those skilled in the art will appreciate that Fig.11 This is merely an example of a computer device and does not constitute a limitation on the computer device. The computer device may include more or fewer components than those shown in the figure, or a combination of certain components, or different components. For example, it may also include a network interface, a display screen, and an input device.

[0164] The processor may be a CPU, or other general-purpose processors, a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. A general-purpose processor may be a microprocessor or any conventional processor, etc.

[0165] The memory includes a readable storage medium, an internal memory, etc., wherein the internal memory may be the memory of a computer device, and the internal memory provides an environment for the operation of an operating system and computer-readable instructions in the readable storage medium. The readable storage medium may be a hard disk of a computer device, and in other embodiments may also be an external storage device of a computer device, for example, a plug-in hard disk, a smart memory card (Smart Media Card, SMC), a secure digital (Secure Digital, SD) card, a flash card (Flash Card), etc. equipped on a computer device. Further, the memory may also include both an internal storage unit of a computer device and an external storage device. The memory is used to store an operating system, an application program, a boot loader (BootLoader), data, and other programs, such as program codes of computer programs, etc. The memory may also be used to temporarily store data that has been output or is to be output.

[0166] The technicians in the relevant field can clearly understand that for the convenience and simplicity of description, only the division of the above-mentioned functional units and modules is used as an example. In practical applications, the above-mentioned function allocation can be completed by different functional units and modules as needed, that is, the internal structure of the device is divided into different functional units or modules to complete all or part of the functions described above. The functional units and modules in the embodiment can be integrated into a processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The above-mentioned integrated unit can be implemented in the form of hardware or in the form of software functional units. In addition, the specific names of the functional units and modules are only for the convenience of distinguishing each other, and are not used to limit the scope of protection of this application. The specific working process of the units and modules in the above-mentioned device can refer to the corresponding process in the above-mentioned method embodiment, which will not be repeated here. If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on such understanding, the present application implements all or part of the processes in the above-mentioned embodiment method, which can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a computer-readable storage medium, and the computer program can implement the steps of the above-mentioned method embodiment when executed by the processor. Among them, the computer program includes computer program code, and the computer program code can be in source code form, object code form, executable file or some intermediate form. The computer-readable medium may at least include: any entity or device capable of carrying computer program code, recording medium, computer memory, read-only memory (ROM), random access memory (RAM), electric carrier signal, telecommunication signal and software distribution medium. For example, a USB flash drive, a mobile hard disk, a magnetic disk or an optical disk. In some jurisdictions, according to legislation and patent practice, computer-readable media cannot be electric carrier signals and telecommunication signals.

[0167] The present application implements all or part of the processes in the above-mentioned embodiment method, and may also be completed through a computer program product. When the computer program product runs on a computer device, the computer device can implement the steps in the above-mentioned method embodiment when executing the computer program product.

[0168] In the above embodiments, the description of each embodiment has its own emphasis. For parts that are not described or recorded in detail in a certain embodiment, reference can be made to the relevant descriptions of other embodiments.

[0169] Those of ordinary skill in the art will appreciate that the units and algorithm steps of each example described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professional and technical personnel can use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of this application.

[0170] In the embodiments provided in the present application, it should be understood that the disclosed devices / computer equipment and methods can be implemented in other ways. For example, the device / computer equipment embodiments described above are only schematic, for example, the division of modules or units is only a logical function division, and there may be other division methods in actual implementation, such as multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, indirect coupling or communication connection of devices or units, which can be electrical, mechanical or other forms.

[0171] The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0172] The above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit them. Although the present application has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. These modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present application, and should all be included in the protection scope of the present application.

Claims

1. An evaluation method for machine learning query optimization, characterized in that: The evaluation method includes: Obtaining an original statement and an original plan corresponding to the original statement; Use a preset explorer built based on machine learning in a preset database to perform execution plan exploration for the execution of the original statement to obtain a plan candidate set, and perform an indicator evaluation on the preset explorer based on the plan candidate set and the original plan to obtain a first evaluation report; Using a preset selector constructed based on machine learning in the preset database, selecting a plan from the plan candidate set and the original plan to obtain a selection result, and performing an indicator evaluation on the preset selector according to the selection result to obtain a second evaluation report; After the original statement is executed, a total execution parameter is obtained, and the preset explorer and the preset selector are evaluated according to the total execution parameter, the first evaluation report and the second evaluation report to obtain an evaluation result; The step of performing an indicator evaluation on the preset explorer according to the plan candidate set and the original plan to obtain a first evaluation report includes: Compare each candidate plan in the plan candidate set with the original plan, determine a first target plan subset that is better than the original plan, a second target plan subset that is worse than the original plan, and an optimal optimization parameter of the optimal plan in the first target plan subset compared with the original plan; According to the number of candidate plans in the first target plan subset, the optimal optimization parameter and the second target plan subset, the quality index of the plan candidate set is evaluated to obtain a quality index evaluation result; According to the number and plan diversity of each type of candidate plans in the plan candidate set, the effectiveness index of the plan candidate set is evaluated to obtain an effectiveness index evaluation result; Evaluate the scalability index of the plan candidate set to obtain a scalability index evaluation result; Writing the quality indicator evaluation result, the effectiveness indicator evaluation result and the scalability indicator evaluation result into a first evaluation report; The step of performing an index evaluation on the preset selector according to the selection result to obtain a second evaluation report includes: According to the selection result, determine the number of candidate plans in the candidate plan set after deduplication, the number of candidate plans better than the original plan, and the quality score of the candidate plan set, determine the selection frequency index, and determine the number and proportion of selecting the optimal plan, the number and proportion of selecting the candidate plan better than the original plan, and the number and proportion of selecting the original plan; Calculate the configuration index evaluation result according to the number of candidate plans in the candidate plan set after deduplication, the number of candidate plans better than the original plan, and the quality score; The selection capability index evaluation result is calculated based on the number and proportion of selecting the optimal plan, the number and proportion of selecting the candidate plan that is better than the original plan, and the number and proportion of selecting the original plan; Detecting whether the selection result is an initial plan of the preset selector, and if not, calculating a learning indicator evaluation result according to the score value of the selection result and the cost value of the selection result; The configuration index evaluation result, the selection number index, the selection ability index evaluation result and the learning index evaluation result are written into a second evaluation report.

2. The evaluation method according to claim 1, characterized in that: The evaluation method further comprises: Get the preset visualization function; Using the preset visualization function to analyze the total execution parameter, the first evaluation report, the second evaluation report, and the evaluation result to obtain a visualization chart; The visualization chart is displayed.

3. The evaluation method according to claim 1, characterized in that: The evaluation method further comprises: For the same workload, repeatedly executing the preset test times, using the preset explorer built based on machine learning in the preset database to perform execution plan exploration for the execution of the original statement, obtaining a plan candidate set, and performing an indicator evaluation on the preset explorer based on the plan candidate set and the original plan to obtain a first evaluation report; Obtaining a first stability indicator under the same workload according to a quality indicator evaluation result corresponding to each test execution under the same workload; For different workloads, repeatedly executing the method of using a preset explorer built based on machine learning in a preset database to perform execution plan exploration for the execution of the original statement to obtain a plan candidate set, and performing an indicator evaluation on the preset explorer according to the plan candidate set and the original plan to obtain a first evaluation report; Obtaining a second stability indicator under the different workloads according to a quality indicator evaluation result corresponding to each workload under the different workloads; According to the first stability index and the second stability index, a first stability index evaluation result of the preset explorer is obtained, and the first stability index evaluation result is written into the first evaluation report.

4. The evaluation method according to claim 1, characterized in that: The evaluation method further comprises: For different workloads, repeatedly executing the preset selector constructed based on machine learning using the preset database, selecting a plan from the plan candidate set and the original plan, obtaining a selection result, and performing an indicator evaluation on the preset selector according to the selection result to obtain a second evaluation report; Obtaining a second stability index evaluation result of the preset selector under the different workloads according to the selection capability index evaluation result corresponding to each workload under the different workloads; The second stability indicator evaluation result is written into the second evaluation report.

5. The evaluation method according to claim 1, characterized in that: Before obtaining the original statement and the original plan corresponding to the original statement, the method further includes: Configuration parameter file, the parameter file includes connection configuration for connecting to a preset database, workload configuration, and test number configuration; The parameter file is sent to a system agent, and the system agent is used to determine the preset database according to the parameter file, so as to send the workload configuration and the test number configuration to the preset database.

6. An evaluation device for machine learning query optimization, characterized in that: The evaluation device comprises: A statement acquisition module, used to acquire an original statement and an original plan corresponding to the original statement; An explorer evaluation module, used to use a preset explorer built based on machine learning in a preset database to perform execution plan exploration for the execution of the original statement, obtain a plan candidate set, and perform an indicator evaluation on the preset explorer according to the plan candidate set and the original plan to obtain a first evaluation report; A selector evaluation module, configured to use a preset selector constructed based on machine learning in the preset database to select a plan from the plan candidate set and the original plan, obtain a selection result, and perform an indicator evaluation on the preset selector according to the selection result to obtain a second evaluation report; An evaluation module, configured to obtain total execution parameters after the original statement is executed, and to evaluate the preset explorer and the preset selector according to the total execution parameters, the first evaluation report and the second evaluation report to obtain an evaluation result; The explorer evaluation module includes: A comparison unit, used for comparing each candidate plan in the plan candidate set with the original plan, to determine a first target plan subset that is better than the original plan, a second target plan subset that is worse than the original plan, and an optimal optimization parameter of the optimal plan in the first target plan subset compared with the original plan; A quality indicator evaluation unit, configured to evaluate the quality indicator of the candidate plan set according to the number of candidate plans in the first target plan subset, the optimal optimization parameter and the second target plan subset, to obtain a quality indicator evaluation result; An effectiveness index evaluation unit, used to evaluate the effectiveness index of the plan candidate set according to the number and plan diversity of each type of candidate plans in the plan candidate set, and obtain an effectiveness index evaluation result; A scalability index evaluation unit, used to evaluate the scalability index of the plan candidate set to obtain a scalability index evaluation result; A first writing unit, used for writing the quality index evaluation result, the effectiveness index evaluation result and the scalability index evaluation result into a first evaluation report; The selector evaluation module comprises: a determination unit, configured to determine, according to the selection result, the number of candidate plans in the candidate plan set after deduplication, the number of candidate plans better than the original plan, and the quality score of the candidate plan set, determine a selection frequency index, and determine the number and ratio of selecting the optimal plan, the number and ratio of selecting the candidate plans better than the original plan, and the number and ratio of selecting the original plan; A configuration indicator evaluation unit, configured to calculate a configuration indicator evaluation result according to the number of candidate plans in the plan candidate set after deduplication, the number of candidate plans better than the original plan, and the quality score; A selection capability indicator evaluation unit is used to calculate the selection capability indicator evaluation result according to the number and proportion of selecting the optimal plan, the number and proportion of selecting the candidate plan better than the original plan, and the number and proportion of selecting the original plan; A learning indicator evaluation unit, used to detect whether the selection result is an initial plan of the preset selector, and if not, calculate a learning indicator evaluation result according to the score value of the selection result and the cost value of the selection result; The third writing unit is used to write the configuration index evaluation result, the selection number index, the selection ability index evaluation result and the learning index evaluation result into a second evaluation report.

7. A computer device, characterized in that: The computer device includes a processor, a memory, and a computer program stored in the memory and executable on the processor, and the processor implements the evaluation method according to any one of claims 1 to 5 when executing the computer program.

8. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the evaluation method according to any one of claims 1 to 5 is implemented.

Citation Information

Patent Citations

  • Database management system, related device, method and medium

    CN114443615A

  • Database query optimization method and system, electronic equipment and storage medium

    CN116028528A