Optimization scheme generation method and device, equipment and storage medium

Abstract

The invention discloses an optimization scheme generation method and device, equipment and a storage medium. The optimization scheme generation method is applied to database middleware, the database middleware is in communication connection with a routing node and a system library, and the optimization scheme generation method comprises the steps that in response to an initial structured query language (SQL) statement which is forwarded by the routing node and contains query performance optimization annotations, the initial SQL statement is forwarded to a distributed database to be executed, obtaining the initial execution time of the initial SQL statement; under the condition that the initial execution time exceeds a set time threshold value, obtaining an execution plan of the initial SQL statement from a distributed database, and obtaining database parameters and table metadata from a system library; and generating a single-class optimization scheme based on the execution plan of the initial SQL statement, the database parameters, the table metadata and the table and the field associated with the initial SQL statement, and performing execution cost estimation on the single-class optimization scheme to obtain a target optimization scheme. According to the technical scheme, the SQL execution efficiency can be improved.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to an optimization solution generation method, device, equipment and storage medium. Background Art

[0002] Structured Query Language (SQL) is a commonly used database query and programming language used to store data and query, update and manage relational database systems.

[0003] With the development of information technology, data is growing explosively, and a large amount of data needs to be stored and queried. When the amount of data is large and the SQL execution efficiency is low, how to generate SQL optimization suggestions is very important to ensure the SQL execution efficiency. Summary of the invention

[0004] The present invention provides an optimization solution generation method, device, equipment and storage medium to solve the problem of low SQL execution efficiency.

[0005] According to one aspect of the present invention, there is provided an optimization solution generation method, which is applied to a database middleware, wherein the database middleware is respectively connected to a routing node and a system library in communication, and comprises:

[0006] In response to the initial structured query language SQL statement containing the query performance optimization annotation forwarded by the routing node, forward the initial SQL statement to the distributed database for execution, and obtain the initial execution time of the initial SQL statement;

[0007] When the initial execution time exceeds a set time threshold, obtaining an execution plan of the initial SQL statement from the distributed database, and obtaining database parameters and table metadata from the system library;

[0008] Based on the execution plan, database parameters, table metadata of the initial SQL statement, and tables and fields associated with the initial SQL statement, a single-class optimization plan is generated, and the execution cost of the single-class optimization plan is estimated to obtain a target optimization plan.

[0009] According to another aspect of the present invention, there is provided an optimization solution generation device, which is applied to a database middleware, wherein the database middleware is respectively connected to a routing node and a system library for communication, and comprises:

[0010] An initial time acquisition module, configured to respond to an initial structured query language SQL statement containing query performance optimization annotations forwarded by a routing node, forward the initial SQL statement to a distributed database for execution, and acquire an initial execution time of the initial SQL statement;

[0011] An optimized data acquisition module is used to obtain the execution plan of the initial SQL statement from the distributed database and obtain database parameters and table metadata from the system library when the initial execution time exceeds a set time threshold;

[0012] The optimization solution generation module is used to generate a single-type optimization solution based on the execution plan, database parameters, table metadata of the initial SQL statement, and the tables and fields associated with the initial SQL statement, and to estimate the execution cost of the single-type optimization solution to obtain a target optimization solution.

[0013] According to another aspect of the present invention, an electronic device is provided, the electronic device comprising:

[0014] at least one processor; and

[0015] a memory communicatively connected to the at least one processor; wherein,

[0016] The memory stores a computer program that can be executed by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute the optimization solution generation method described in any embodiment of the present invention.

[0017] According to another aspect of the present invention, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement the optimization solution generation method described in any embodiment of the present invention when executed.

[0018] According to another aspect of the present invention, a computer program product is provided, including a computer program, and when the computer program is executed by a processor, the optimization solution generating method of any embodiment of the present disclosure is implemented.

[0019] The technical solution of the embodiment of the present invention, after receiving the initial structured query language SQL statement containing query performance optimization annotations forwarded by the routing node, forwards the initial SQL statement to the distributed database for execution, and obtains the initial execution time of the initial SQL statement. When the initial execution time exceeds the set time threshold, obtains the execution plan of the initial SQL statement from the distributed database, and obtains database parameters and table metadata from the system library. Finally, based on the execution plan, database parameters, table metadata of the initial SQL statement, and tables and fields associated with the initial SQL statement, a single-class optimization plan is generated, and the execution cost of the single-class optimization plan is estimated to obtain a target optimization plan, so that the SQL execution cost can be controlled by optimizing the execution plan.

[0020] It should be understood that the contents described in this section are not intended to identify the key or important features of the embodiments of the present invention, nor are they intended to limit the scope of the present invention. Other features of the present invention will become easily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS

[0021] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0022] Figure 1a is a flowchart of a method for generating an optimization solution according to Embodiment 1 of the present invention;

[0023] Figure 1b is a structural diagram of an optimization solution generation system provided according to Embodiment 1 of the present invention;

[0024] Figure 2a is a flow chart of a method for generating an optimization solution according to Embodiment 2 of the present invention;

[0025] Figure 2b is a flow chart of determining a target optimization solution provided in accordance with Embodiment 2 of the present invention;

[0026] Figure 3a is a flow chart of a method for generating an optimization solution according to Embodiment 3 of the present invention;

[0027] Figure 3b is a flowchart of determining a target optimization solution provided in Embodiment 3 of the present invention;

[0028] Figure 4 is a structural schematic diagram of an optimization solution generating device provided according to Embodiment 4 of the present invention;

[0029] Figure 5 It is a structural schematic diagram of an electronic device for implementing the optimization solution generation method of an embodiment of the present invention. DETAILED DESCRIPTION

[0030] In order to enable those skilled in the art to better understand the scheme of the present invention, the technical scheme in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the present invention.

[0031] It should be noted that the terms "first", "second", etc. in the specification and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchanged where appropriate, so that the embodiments of the present invention described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units that are clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0032] Embodiment 1

[0033] Figure 1a A flowchart of an optimization solution generation method is provided for the first embodiment of the present invention. This embodiment is applicable to the case where an optimization solution is generated according to an execution plan, table metadata, database parameters, and SQL-associated tables and fields. The method can be executed by an optimization solution generation device, which can be implemented in the form of hardware and / or software, and can be configured in various general-purpose computing devices.

[0034] To facilitate understanding, we first explain the optimization solution generation system. The specific results of the optimization solution generation system are as follows: Figure 1b As shown, the optimization solution generation system includes a client access layer, a routing node, a database middleware, a system library, and a distributed database, wherein the distributed database includes multiple nodes.

[0035] The client access layer faces the user and sends the SQL statement entered by the user to the routing node. The routing node is used to distribute the SQL statement received from the client to the database node corresponding to the corresponding distributed database. If the client forwards an SQL statement containing a query performance optimization annotation (hint) to the routing node, the database middleware will forward the SQL statement to the database middleware, which will then be sent to the corresponding node of the distributed database for execution. The database middleware is essentially an optimization middleware. After the database middleware is turned on, it will synchronize metadata and database parameters from the system library according to the set cycle. After receiving the SQL statement containing a specific hint, the database middleware will forward it to the corresponding node of the distributed database for execution, and if the SQL statement execution efficiency does not meet the user's expectations, it will generate an optimization plan for the SQL statement and feedback the optimization bill to the client through the routing node.

[0036] like Figure 1a As shown, the optimization solution generation method includes:

[0037] S110 . In response to the initial structured query language SQL statement containing the query performance optimization annotation forwarded by the routing node, forward the initial SQL statement to the distributed database for execution, and obtain the initial execution time of the initial SQL statement.

[0038] The initial SQL statement is initiated by the client and forwarded to the database middleware through the routing node, which contains specific query performance optimization annotations (hints). The initial SQL statement can be a data query statement, a data modification statement, or a data write statement.

[0039] In the embodiment of the present invention, after the routing node obtains the initial SQL sent by the client, it parses the initial SQL statement, determines that it contains a specific hint, and then forwards the initial SQL statement to the database middleware. After the database middleware responds to the initial SQL statement containing the query performance optimization annotation forwarded by the routing node, it forwards the initial SQL statement to the distributed database for execution, and obtains the initial execution time of the initial SQL statement, so as to judge whether the execution efficiency of the initial SQL statement meets the user's needs according to the initial execution time.

[0040] Specifically, the database middleware first determines at least one target database node associated with the initial SQL statement, and forwards the initial SQL statement to the target database node included in the distributed database for execution. Further, the database middleware obtains the execution time fed back by each target database node, and determines the initial execution time of the initial SQL statement based on the execution time fed back by each database node. For example, the maximum time is selected from the execution times fed back by each target database node as the initial execution time.

[0041] S120: When the initial execution time exceeds the set time threshold, an execution plan of the initial SQL statement is obtained from the distributed database, and database parameters and table metadata are obtained from the system library.

[0042] In the embodiment of the present invention, the initial execution time of the initial SQL statement is compared with a preset time threshold. If the initial execution time exceeds the set time threshold, it indicates that the execution efficiency of the initial SQL statement cannot meet user needs and solution optimization is required.

[0043] In the prior art, when the execution efficiency of the initial SQL statement cannot meet the user's needs, the focus is usually only on the SQL statement itself, and there is a lack of understanding of the database system variables. In the embodiment of the present invention, in order to improve the optimization reliability, when the initial execution time exceeds the set time threshold, the execution plan of the initial SQL statement is obtained from the distributed database, and the database parameters and table metadata are obtained from the system library to improve the database middleware's understanding of the database context and generate a reliable optimization plan.

[0044] Database parameters may include database performance parameters and business parameters. For example, database performance parameters may include database cache space size or the number of concurrent connections, and business parameters may be custom parameters associated with the business, such as the limit value of the SQL statement concatenation length.

[0045] S130, based on the execution plan of the initial SQL statement, database parameters, table metadata, and tables and fields associated with the initial SQL statement, generate a single-class optimization plan, and estimate the execution cost of the single-class optimization plan to obtain a target optimization plan.

[0046] To improve the execution efficiency of the initial SQL statement, the solutions that can be adopted include multiple dimensions, for example, the optimization dimensions may include SQL rewriting, table structure optimization, and database parameter optimization, etc. A single-type optimization solution refers to an optimization solution that only includes one optimization dimension, for example, a solution that rewrites SQL statements or a solution that optimizes the table structure by adding indexes to database tables.

[0047] In the embodiment of the present invention, multiple single-class optimization solutions are generated based on the execution plan of the acquired initial SQL statement, database parameters, table metadata, and tables and fields associated with the initial SQL statement. Specifically, for the SQL rewriting dimension, the initial SQL statement can be analyzed based on the execution plan of the initial SQL statement to determine whether it contains unnecessary query operations. If so, the initial SQL statement is rewritten to correct the query method and reduce the query workload.

[0048] For table structure optimization, we can determine the operations with high overhead during the initial SQL execution process, such as full scan, based on the execution plan of the initial SQL statement. Furthermore, we can obtain the tables and fields associated with the initial SQL statement, and extract the metadata of the tables and fields associated with the initial SQL statement from the table metadata, for example, the metadata is the table index. Finally, we can replace the full scan by modifying or adding the table index to improve the execution efficiency of the initial SQL.

[0049] For parameter optimization dimensions, for example, if the database parameters include a limit data value for the concatenation length of SQL statements, the execution plan is analyzed. If the initial SQL execution efficiency is low due to unreasonable setting of the limit data value, the initial SQL execution efficiency can be improved by optimizing the parameters.

[0050] After determining multiple single-class optimization schemes, the single-class optimization schemes can be adopted and verified in turn. Specifically, the execution cost of each single-class optimization scheme is estimated in turn, and a variable is maintained to store the single-class optimization scheme with the lowest current estimated cost during the calculation process. Each time the variable is reassigned, the execution time needs to be predicted based on the estimated execution cost. If the predicted time is less than the set time threshold, it means that the current single-class optimization scheme can meet the user's efficiency requirements, and the current single-class optimization scheme is directly determined as the target optimization scheme. If the current predicted time is greater than the set time threshold, it means that the current single-class optimization scheme cannot meet the user's efficiency requirements, and the next single-class optimization scheme will be taken until the target optimization scheme is determined or the complete single-class optimization scheme is traversed.

[0051] Optionally, in order to reduce the impact of the optimization process on the database, the single-category optimization solutions can be sorted, and the single-category optimization solutions with less impact on the database are adopted and verified first. Exemplarily, the priorities are sorted from high to low into the SQL rewriting category, the table structure optimization category, and the parameter optimization category. According to the category priority, the single-category optimization solutions belonging to the SQL rewriting category are adopted and verified first, and then the single-category optimization solutions of the table structure optimization category are adopted and verified, and finally the single-category optimization solutions of the parameter optimization category are adopted and verified.

[0052] The technical solution of the embodiment of the present invention, after receiving the initial structured query language SQL statement containing query performance optimization annotations forwarded by the routing node, forwards the initial SQL statement to the distributed database for execution, and obtains the initial execution time of the initial SQL statement. When the initial execution time exceeds the set time threshold, obtains the execution plan of the initial SQL statement from the distributed database, and obtains database parameters and table metadata from the system library. Finally, based on the execution plan, database parameters, table metadata of the initial SQL statement, and tables and fields associated with the initial SQL statement, a single-class optimization plan is generated, and the execution cost of the single-class optimization plan is estimated to obtain a target optimization plan, so that the SQL execution cost can be controlled by optimizing the execution plan.

[0053] Embodiment 2

[0054] Figure 2a This is a flowchart of a method for generating an optimization solution provided in the second embodiment of the present invention. This embodiment further refines the above embodiment and provides specific steps for estimating the execution cost of a single type of optimization solution to obtain a target optimization solution. Figure 2a As shown, the method includes:

[0055] S210 . In response to the initial structured query language SQL statement containing the query performance optimization annotation forwarded by the routing node, forward the initial SQL statement to the distributed database for execution, and obtain the initial execution time of the initial SQL statement.

[0056] S220: When the initial execution time exceeds the set time threshold, an execution plan of the initial SQL statement is obtained from the distributed database, and database parameters and table metadata are obtained from the system library.

[0057] S230. Generate a single-category optimization plan based on the execution plan of the initial SQL statement, database parameters, table metadata, and tables and fields associated with the initial SQL statement, classify the single-category optimization plans, and prioritize the single-category optimization plans according to pre-set category priorities.

[0058] In an embodiment of the present invention, a single-class optimization solution is generated based on the execution plan of the initial SQL statement, database parameters, table metadata, and tables and fields associated with the initial SQL statement. For example, for the SQL rewriting dimension, the initial SQL statement can be analyzed based on the execution plan of the initial SQL statement to determine whether it contains unnecessary query operations. If so, the initial SQL statement is rewritten to correct the query method and reduce the query workload. The single-class optimization solution only includes optimization means of one dimension, such as SQL rewriting, table structure optimization, or parameter optimization.

[0059] The specific process of estimating the execution cost of a single type of optimization solution and obtaining the target optimization solution is as follows: Figure 2b After generating multiple single-class optimization schemes, the single-class optimization schemes are classified according to the optimization means used by each single-class optimization scheme, and finally the single-class optimization schemes are prioritized according to the pre-set category priorities of various optimization means.

[0060] In a specific example, the optimization means include SQL rewriting, table structure optimization or parameter optimization. Based on the optimization means adopted, the generated multiple single-class optimization schemes are divided into SQL rewriting category, table structure optimization category or parameter optimization category. The category priorities are SQL rewriting, table structure optimization or parameter optimization from high to low. According to the priority, the single-class optimization schemes are sorted. The higher the priority of the category, the higher the sorting position. Multiple single-class optimization schemes of the same category can be sorted randomly or according to the amount of modified content. For example, the less modified content, the higher the sorting position.

[0061] S240. Based on the sorting results, extract a single-category optimization solution in turn as the current solution, and estimate the execution cost of the current solution to obtain the current estimated execution cost.

[0062] In an embodiment of the present invention, after the single-class optimization schemes are prioritized, a queue of single-class optimization schemes is obtained. A single-class optimization scheme is extracted from the queue in turn as the current scheme, and the execution cost of the current scheme is estimated to obtain the current estimated execution cost. Specifically, for each single-class optimization scheme, the single-class optimization scheme can be sent to the node associated with the current single-class optimization scheme in the distributed database. Then, the local execution cost fed back by each node is received, and the current estimated execution cost is finally determined according to each local execution cost. For example, the largest one can be selected from multiple local execution costs as the current estimated execution cost, and multiple local execution costs can be weighted and summed to obtain the current estimated execution cost. Among them, the execution cost includes input / output (I / O) cost and central processing unit (CPU) cost. I / O cost is the performance loss of the process of loading data from disk to memory; CPU cost is the performance loss of operations such as reading and detecting whether the records meet the corresponding search conditions and sorting the result set.

[0063] In addition, the database middleware can also directly determine the tables related to the current solution based on the current SQL statement associated with the current solution, and obtain the table metadata of the related tables from the system library, such as field information, index information, and data volume, etc. Finally, the execution cost is calculated based on the table metadata and the current SQL statement.

[0064] For example, the current solution is to add a new index. According to the table metadata (field information, index information, data volume, etc.), the number of data pages of the new index, index discreteness, etc. are estimated. Then, the number of entries expected to be scanned by the current SQL, the primary index, and other information are combined to calculate the execution cost.

[0065] The estimation method does not modify the database, and its calculation speed is relatively fast. It is suitable for situations where the table data volume is large. The estimated execution cost will have a certain range of errors. When the table data volume is small, in order to obtain an accurate execution cost, you can also directly optimize according to the current solution, for example, rewrite the SQL, modify the table structure, or modify the system parameters, and then send the optimized SQL statement to the corresponding node of the distributed database, and determine the accurate execution cost based on the execution cost fed back by the node.

[0066] Optionally, the execution cost of the current solution is estimated to obtain the current estimated execution cost, including:

[0067] Determine at least one target node associated with the current solution in the distributed database, and send the current solution to the target node;

[0068] The local execution cost fed back by the target node for the current solution is obtained, and based on the local execution cost fed back by each target node, the current estimated execution cost of the current solution is determined.

[0069] In this optional embodiment, a specific method for estimating the execution cost of the current solution and obtaining the current estimated execution cost is provided: first, at least one target node associated with the current solution is determined in a distributed database, and the current solution is sent to the target node. Further, the local execution cost fed back by the target node for the current solution is obtained, and based on the local execution cost fed back by each target node, the current estimated execution cost of the current solution is determined.

[0070] Specifically, after receiving the current solution, each target node will estimate the local execution cost based on the optimization method of the current solution and the system table stored in the system library. The information that can be included in the system table includes table structure, table index, and database operation parameters. The database middleware will analyze the SQL statement associated with the current cost in advance, determine the table involved in the SQL statement, and mark the table with a large number of occurrences as a hotspot table. If the record data of the hotspot table changes exceeds the set threshold, the database middleware will update the data associated with the hotspot table in the system table of the system library to ensure the accuracy of the estimated execution cost. The data associated with the hotspot table lock is updated more frequently than other tables, which can further improve the accuracy of cost estimation.

[0071] S250: When the current estimated execution cost is lower than the estimated execution cost of other single-type optimization solutions that have completed estimation, estimate the first execution time of the current solution according to the current estimated execution cost.

[0072] In the embodiment of the present invention, a variable can be maintained to store the currently traversed single-class optimization solution with the lowest estimated execution cost. When the current estimated execution cost is lower than the estimated execution cost of other single-class optimization solutions that have been estimated, the current solution is stored in the above variable. Then, based on the current estimated execution cost, the first execution time of the current solution is estimated.

[0073] Optionally, before forwarding the initial SQL statement to the distributed database for execution and obtaining the initial execution time of the initial SQL statement, the following steps are performed:

[0074] Obtain the actual execution cost and actual execution time of at least two historical SQL statements during execution, and fit a time prediction model based on the corresponding relationship between the actual execution cost and the actual execution time;

[0075] Based on the current estimated execution cost, estimate the first execution time of the current solution, including:

[0076] The current estimated execution cost is input into the time prediction model to obtain the first execution time of the current solution output by the time prediction model.

[0077] In summary, before forwarding the initial SQL statement to the distributed database for execution and obtaining the initial execution time of the initial SQL statement, obtain the actual execution cost and actual execution time of at least two historical SQL statements during the execution process, and fit the time prediction model based on the corresponding relationship between the actual execution cost and the actual execution time. Specifically, collect a series of historical SQL statements with different execution costs in the business scenario, and record the actual execution cost and the actual execution time. Since the system load during business peak and trough periods is different, it has a certain impact on the results, so statistics will be repeated in different time periods to obtain more comprehensive data. Then analyze the data to initially establish a model, and use this model to return to the business scenario to verify the accuracy. If the accuracy is not high, consider collecting more data.

[0078] In this optional embodiment, a specific method for estimating the first execution time of the current solution based on the current estimated execution cost is also provided: the current estimated execution cost can be directly input into the time prediction model to obtain the first execution time of the current solution output by the time prediction model.

[0079] S260: When the first execution time does not exceed a set time threshold, determine the current solution as a target optimization solution.

[0080] In the embodiment of the present invention, when the estimated first execution time does not exceed the set time threshold, it indicates that the current execution efficiency can meet the user's needs, and the current solution can be directly used as the target optimization solution.

[0081] S270. When the first execution time exceeds the set time threshold, return to execute the operation based on the sorting result, and extract a single-category optimization solution as the current solution in sequence, until the target optimization solution is determined or the traversal of the single-category optimization solution is completed.

[0082] In an embodiment of the present invention, when the estimated first execution time exceeds the set time threshold, indicating that the current execution efficiency cannot meet the user's needs, the operation returns to execution based on the sorting result, extracting one single-category optimization solution as the current solution in turn, and continuing to judge whether the next single-category optimization solution can meet the user's needs until the target optimization solution is determined or the traversal of the single-category optimization solution is completed.

[0083] The technical solution of the embodiment of the present invention classifies the single-class optimization schemes, and prioritizes the single-class optimization schemes according to a preset category priority. Based on the sorting result, one single-class optimization scheme is extracted in turn as the current scheme, and the execution cost of the current scheme is estimated to obtain the current estimated execution cost. When the current estimated execution cost is lower than the estimated execution cost of other single-class optimization schemes that have completed the estimation, the first execution time of the current scheme is estimated according to the current estimated execution cost. When the first execution time does not exceed the set time threshold, the current scheme is determined to be the target optimization scheme. When the first execution time exceeds the set time threshold, the operation of extracting one single-class optimization scheme as the current scheme based on the sorting result is returned to be executed in turn until the target optimization scheme is determined or the traversal of the single-class optimization scheme is completed. The optimization scheme that has less impact on the database and meets the user's efficiency requirements can be preferentially selected according to the priority, thereby improving the execution efficiency of SQL.

[0084] Embodiment 3

[0085] Figure 3a This is a flowchart of an optimization solution generation method provided by Embodiment 3 of the present invention. This embodiment further refines the above embodiment and provides specific steps for estimating the execution cost of a single type of optimization solution and obtaining a target optimization solution. Figure 3a As shown, the method includes:

[0086] S310 . In response to the initial structured query language SQL statement containing the query performance optimization annotation forwarded by the routing node, forward the initial SQL statement to the distributed database for execution, and obtain the initial execution time of the initial SQL statement.

[0087] S320: When the initial execution time exceeds the set time threshold, an execution plan of the initial SQL statement is obtained from the distributed database, and database parameters and table metadata are obtained from the system library.

[0088] S330. Generate a single-category optimization plan based on the execution plan of the initial SQL statement, database parameters, table metadata, and tables and fields associated with the initial SQL statement, classify the single-category optimization plans, and extract a class of single-category optimization plans as the current class in turn according to a pre-set category priority.

[0089] In an embodiment of the present invention, a single-class optimization solution is generated based on the execution plan of the initial SQL statement, database parameters, table metadata, and tables and fields associated with the initial SQL statement. For example, for the table structure optimization dimension, the operation with high overhead during the initial SQL execution process, such as a full scan, can be determined based on the execution plan of the initial SQL statement. Further, the tables and fields associated with the initial SQL statement are obtained, and metadata of the tables and fields associated with the initial SQL statement, such as table indexes, are extracted from the table metadata. Finally, the full scan can be replaced by modifying or adding table indexes to improve the execution efficiency of the initial SQL.

[0090] In the embodiment of the present invention, the process of determining the target optimization solution is as follows: Figure 3b After generating multiple single-class optimization solutions, the single-class optimization solutions are classified, and a class of single-class optimization solutions is extracted in turn as the current class according to the pre-set class priority. In a specific example, the optimization means include SQL rewriting, table structure optimization or parameter optimization. Based on the adopted optimization means, the generated multiple single-class optimization solutions are divided into SQL rewriting category, table structure optimization category or parameter optimization category. The class priorities from high to low are SQL rewriting, table structure optimization or parameter optimization. According to the priority, a class of single-class optimization solutions is extracted in turn as the current class.

[0091] Optionally, single-category optimization solutions are sorted from high to low based on category priority, including SQL rewrite category, table structure optimization category, and parameter optimization category.

[0092] In this optional embodiment, a single type of optimization solution may include categories, namely, SQL rewriting category, table structure optimization category, and parameter optimization category. Priority is sorted according to the degree of impact on the database. The smaller the impact on the database, the higher the priority. The priority sorting results from high to low are SQL rewriting category, table structure optimization category, and parameter optimization category.

[0093] S340. Determine candidate solutions associated with the current class based on the single-class optimization solutions included in the current class; the candidate solutions include single-class optimization solutions and combined optimization solutions composed of optimization solutions of at least two categories.

[0094] In the embodiment of the present invention, all candidate solutions included in the current class are determined based on the single-class optimization solutions included in the current class. Specifically, the single-class optimization solutions included in the current class can be directly used as candidate solutions associated with the current class, and the single-class optimization solutions included in the current class can also be combined with single-class optimization solutions of other categories to obtain a combined optimization solution associated with the current class, and the combined solution is determined as a candidate solution.

[0095] In a specific example, the current class is a table structure optimization category, and the single-class optimization solutions associated with the current class include solution 1, solution 2, and solution 3, where the SQL statement involved in solution 1 is a data query statement, and the screening condition in the query statement includes the screening of the fifth column of table A. In addition to using solutions 1, 2, and 3 as single-class optimization solutions, you can also combine solutions of the table structure optimization type with single-class optimization solutions of the SQL rewrite type. For example, combine solution 1 with the optimization solution of adding an index to the fifth column of table A, and the resulting combined solution 4 is also used as a candidate solution associated with the current class.

[0096] Optionally, according to the single-class optimization solutions included in the current class, the candidate solutions associated with the current class are determined, including:

[0097] Combining each pre-stored valid optimization solution with each single-class optimization solution of the current class in pairs to obtain at least one candidate combination solution;

[0098] Determining a combined optimization scheme from among the candidate combined schemes according to the degree of association between at least two optimization schemes included in the candidate combined schemes;

[0099] The single-class optimization solutions contained in the current class and the combined optimization solution are taken together as candidate solutions associated with the current class.

[0100] In this optional embodiment, a specific method is provided for determining the candidate solutions associated with the current class based on the single-class optimization solutions contained in the current class: each pre-stored valid optimization solution is combined with each optimization solution of the current class in pairs to obtain at least one candidate combination solution. Among them, the valid optimization solution is the optimization solution that has achieved the expected value for reducing the execution cost among the currently adopted and verified optimization solutions, for example, the optimization solution that reduced the execution cost by more than P% in the previous round.

[0101] Not any combination of two optimization schemes will have the effect of reducing execution cost or execution time. Therefore, it is necessary to determine the combined optimization scheme from the candidate combination schemes based on the degree of association between at least two optimization schemes included in the candidate combination schemes, and finally use the single-class optimization schemes and combined optimization schemes included in the current class as candidate schemes associated with the current class.

[0102] In a specific example, the SQL statement involved in Solution 1, which belongs to the SQL rewrite category, is a data query statement, and the screening condition in the query statement includes screening for the fifth column of Table A. Solution 7, which belongs to the table structure optimization category, is to add an index to the fifth column of Table A. The similarity of the tables and columns associated with these two solutions is higher than the set threshold, and they can be determined as a combined optimization solution.

[0103] S350: Estimate the execution cost of each candidate solution in the current class to obtain the estimated execution cost of each candidate solution.

[0104] In an embodiment of the present invention, the execution cost of each candidate solution in the current class is estimated to obtain the estimated execution cost of each candidate solution. Specifically, the database middleware or each node of the distributed database can estimate the execution cost according to the execution plan of the initial SQL statement, database parameters, table metadata, and tables and fields associated with the initial SQL statement.

[0105] S360: Calculate the reduction ratio of the estimated execution cost to the actual execution cost of the initial SQL statement, and determine at least one effective optimization plan in the sub-plans of the current class for storage based on the reduction ratio.

[0106] In an embodiment of the present invention, after obtaining the estimated execution cost of each candidate solution of the current class, the reduction ratio of the estimated execution cost compared to the actual execution cost of the initial SQL statement is calculated, and based on the reduction ratio, at least one effective optimization solution is determined in the sub-solutions of the current class for storage.

[0107] Exemplarily, after each category of candidate solutions is estimated, an effective optimization solution is determined based on the cost reduction ratio of the candidate solution compared to the actual execution cost of the initial SQL statement. For example, a candidate solution with a reduction ratio greater than or equal to P% is taken as an effective optimization solution. The effective optimization solution is stored in order to be combined with the optimization solution of the next category, and the threshold of the reduction ratio is limited at the same time, so as to avoid forming too many invalid optimization solutions in the combination.

[0108] In addition, a solution number threshold n can be set. If the number of valid optimization solutions in the current class of candidate solutions is greater than n, they are sorted from high to low according to the reduction ratio, and the first n candidate solutions are selected as valid optimization solutions.

[0109] S370. Estimate a second execution time of the effective optimization solution based on the estimated execution cost of the effective optimization solution.

[0110] In the embodiment of the present invention, after determining the effective optimization scheme, the second execution time of the effective optimization scheme is estimated according to the estimated execution cost of the effective optimization scheme. Specifically, the estimated execution cost can be input into a pre-established time prediction model to obtain the second execution time of the effective optimization scheme output by the model.

[0111] S380: When the second execution time does not exceed the set time threshold, determine the current effective optimization solution as the target optimization solution.

[0112] In the embodiment of the present invention, when the estimated second execution time does not exceed the set time threshold, it indicates that the current execution efficiency can meet the user's needs, and the current effective optimization solution is directly determined as the target optimization solution.

[0113] S390. When the second execution time exceeds the set time threshold, return to execute the operation of sequentially extracting a single-class optimization solution as the current class until the target optimization solution is determined or the traversal of each class is completed.

[0114] In an embodiment of the present invention, when the estimated second execution time exceeds the set time threshold, indicating that the current execution efficiency cannot meet user needs, the operation of extracting a single class of optimization solutions as the current class in turn is returned to continue to determine whether the optimization solutions included in the next class can meet user needs until the target optimization solution is determined or the traversal of each class is completed.

[0115] The technical solution of the embodiment of the present invention generates a single-class optimization plan based on the execution plan, database parameters, table metadata, and tables and fields associated with the initial SQL statement of the initial SQL statement, classifies the single-class optimization plans, and extracts a class of single-class optimization plans as the current class in turn according to the pre-set category priority. According to the single-class optimization plans contained in the current class, the candidate plans associated with the current class are determined, and then the execution cost of each candidate plan in the current class is estimated to obtain the estimated execution cost of each candidate plan, and the reduction ratio of the estimated execution cost compared to the actual execution cost of the initial SQL statement is calculated. According to the reduction ratio, at least one effective optimization plan is determined in the sub-plan of the current class for storage, and according to the estimated execution cost of the effective optimization plan, the second execution time of the effective optimization plan is estimated. When the second execution time does not exceed the set time threshold, the current effective optimization plan is determined as the target optimization plan, which can improve the SQL execution efficiency.

[0116] Embodiment 4

[0117] Figure 4 A schematic diagram of the structure of an optimization solution generating device provided in Embodiment 4 of the present invention.

[0118] like Figure 4 As shown, the device comprises:

[0119] The initial time acquisition module 410 is used to respond to the initial structured query language SQL statement containing the query performance optimization annotation forwarded by the routing node, forward the initial SQL statement to the distributed database for execution, and obtain the initial execution time of the initial SQL statement;

[0120] The optimization data acquisition module 420 is used to obtain the execution plan of the initial SQL statement from the distributed database and obtain database parameters and table metadata from the system library when the initial execution time exceeds the set time threshold;

[0121] The optimization plan generation module 430 is used to generate a single-type optimization plan based on the execution plan of the initial SQL statement, database parameters, table metadata, and tables and fields associated with the initial SQL statement, and to estimate the execution cost of the single-type optimization plan to obtain a target optimization plan.

[0122] The technical solution of the embodiment of the present invention, after receiving the initial structured query language SQL statement containing query performance optimization annotations forwarded by the routing node, forwards the initial SQL statement to the distributed database for execution, and obtains the initial execution time of the initial SQL statement. When the initial execution time exceeds the set time threshold, obtains the execution plan of the initial SQL statement from the distributed database, and obtains database parameters and table metadata from the system library. Finally, based on the execution plan, database parameters, table metadata of the initial SQL statement, and tables and fields associated with the initial SQL statement, a single-class optimization plan is generated, and the execution cost of the single-class optimization plan is estimated to obtain a target optimization plan, so that the SQL execution cost can be controlled by optimizing the execution plan.

[0123] Optionally, the optimization solution generation module 430 includes:

[0124] A scheme sorting unit, used to classify the single-category optimization schemes and sort the single-category optimization schemes according to a preset category priority;

[0125] A first cost estimation unit is used to extract a single-class optimization solution as a current solution in turn based on the sorting result, and estimate the execution cost of the current solution to obtain a current estimated execution cost;

[0126] A first time estimation unit, configured to estimate a first execution time of the current solution according to the current estimated execution cost when the current estimated execution cost is lower than the estimated execution costs of other single-type optimization solutions that have completed estimation;

[0127] A first optimization solution generating unit, configured to determine that the current solution is a target optimization solution when the first execution time does not exceed a set time threshold;

[0128] The solution traversal unit is used to return to execute the operation based on the sorting result and extract a single-category optimization solution as the current solution in sequence when the first execution time exceeds the set time threshold, until the target optimization solution is determined or the traversal of the single-category optimization solution is completed.

[0129] Optionally, the optimization solution generating module 430 further includes:

[0130] A class extraction unit, used to classify the single-class optimization solutions and extract a class of single-class optimization solutions as the current class in turn according to a preset class priority;

[0131] A candidate solution determination unit, configured to determine a candidate solution associated with the current class according to the single-class optimization solutions contained in the current class; the candidate solutions include single-class optimization solutions and combined optimization solutions composed of at least two types of optimization solutions;

[0132] The second cost estimation unit is used to estimate the execution cost of each candidate solution in the current class to obtain the estimated execution cost of each candidate solution;

[0133] An effective plan screening unit, used to calculate the reduction ratio of the estimated execution cost compared to the actual execution cost of the initial SQL statement, and determine at least one effective optimization plan in the sub-plans of the current class for storage according to the reduction ratio;

[0134] A second time estimation unit, configured to estimate a second execution time of the effective optimization solution according to the estimated execution cost of the effective optimization solution;

[0135] A second optimization solution generating unit, configured to determine the current effective optimization solution as the target optimization solution when the second execution time does not exceed a set time threshold;

[0136] The class traversal unit is used to return to the operation of extracting a class of single-class optimization solutions as the current class in sequence when the second execution time exceeds the set time threshold, until the target optimization solution is determined or the traversal of each class is completed.

[0137] Optionally, the optimization solution generating device further includes:

[0138] A model fitting module is used to obtain the actual execution cost and actual execution time of at least two historical SQL statements during the execution process before forwarding the initial SQL statement to the distributed database for execution and obtaining the initial execution time of the initial SQL statement, and fit the time prediction model according to the corresponding relationship between the actual execution cost and the actual execution time;

[0139] The first time estimation unit is specifically used for:

[0140] The current estimated execution cost is input into the time prediction model to obtain the first execution time of the current solution output by the time prediction model.

[0141] Optionally, the first cost estimation unit is specifically used to:

[0142] Determine at least one target node associated with the current solution in a distributed database, and send the current solution to the target node;

[0143] The local execution cost fed back by the target node for the current solution is obtained, and based on the local execution cost fed back by each target node, a current estimated execution cost of the current solution is determined.

[0144] Optionally, the candidate solution determination unit is specifically used to:

[0145] Combining each pre-stored valid optimization solution with each single-class optimization solution of the current class in pairs to obtain at least one candidate combination solution;

[0146] Determining a combined optimization scheme from among the candidate combined schemes according to the degree of association between at least two optimization schemes included in the candidate combined schemes;

[0147] The single-class optimization solutions contained in the current class and the combined optimization solution are taken together as candidate solutions associated with the current class.

[0148] Optionally, the single-category optimization solutions are sorted from high to low according to category priority, including SQL rewriting category, table structure optimization category and parameter optimization category.

[0149] The optimization solution generation device provided in the embodiment of the present invention can execute the optimization solution generation method provided in any embodiment of the present invention, and has the corresponding functional modules and beneficial effects of the execution method.

[0150] In the technical solution of the present invention, the collection, storage, use, processing, transmission, provision and disclosure of user personal information involved are in compliance with the provisions of relevant laws and regulations and do not violate public order and good morals.

[0151] Embodiment 5

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

[0153] Figure 5A schematic diagram of the structure of an electronic device 10 that can be used to implement an embodiment 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, applicators, blade applicators, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processing, cellular phones, smart phones, wearable devices (such as helmets, glasses, watches, etc.) and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the present invention described and / or required herein.

[0154] like Figure 5 As shown, the electronic device 10 includes at least one processor 11, and a memory connected to the at least one processor 11, such as a read-only memory (ROM) 12, a random access memory (RAM) 13, etc., wherein the memory stores a computer program that can be executed by at least one processor, and the processor 11 can perform various appropriate actions and processes according to the computer program stored in the read-only memory (ROM) 12 or the computer program loaded from the storage unit 18 to the random access memory (RAM) 13. In the RAM 13, various programs and data required for the operation of the electronic device 10 can also be stored. The processor 11, the ROM 12, and the RAM 13 are connected to each other through a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0155] A number of components in the electronic device 10 are connected to the I / O interface 15, including: an input unit 16, such as a keyboard, a mouse, etc.; an output unit 17, such as various types of displays, speakers, etc.; a storage unit 18, such as a disk, an optical disk, etc.; and a communication unit 19, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 19 allows the electronic device 10 to exchange information / data with other devices through a computer network such as the Internet and / or various telecommunication networks.

[0156] The processor 11 may be a variety of general and / or special processing components with processing and computing capabilities. Some examples of the processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, digital signal processors (DSPs), and any appropriate processors, controllers, microcontrollers, etc. The processor 11 executes the various methods and processes described above, such as an optimization solution generation method.

[0157] In some embodiments, the optimization solution generation method may be implemented as a computer program, which is tangibly contained in a computer-readable storage medium, such as a storage unit 18. In some embodiments, part or all of the computer program may be loaded and / or installed on the electronic device 10 via the ROM 12 and / or the communication unit 19. When the computer program is loaded into the RAM 13 and executed by the processor 11, one or more steps of the optimization solution generation method described above may be performed. Alternatively, in other embodiments, the processor 11 may be configured to perform the optimization solution generation method in any other appropriate manner (e.g., by means of firmware).

[0158] Various implementations of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), systems on chips (SOCs), complex programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various implementations can include: being implemented in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which can be a special purpose or general purpose programmable processor that can receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit data and instructions to the storage system, the at least one input device, and the at least one output device.

[0159] Computer programs for implementing 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, so that when the computer program is executed by the processor, the functions / operations specified in the flow chart and / or block diagram are implemented. The computer program may be executed entirely on the machine, partially on the machine, partially on the machine and partially on a remote machine as a stand-alone software package, or entirely on a remote machine or application.

[0160] In the context of the present invention, a computer-readable storage medium may be a tangible medium that may contain or store a computer program for use by or in combination with an instruction execution system, device or equipment. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, devices or equipment, or any suitable combination of the foregoing. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. A more specific example of a machine-readable storage medium may include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

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

[0162] The systems and techniques described herein may be implemented in a computing system that includes a backend component (e.g., as a data applier), or a computing system that includes a middleware component (e.g., an application applier), or a computing system that includes a frontend component (e.g., a user computer with a graphical user interface or a web browser through which a user can interact with an implementation of the systems and techniques described herein), or a computing system that includes any combination of such backend components, middleware components, or frontend components. The components of the system may be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.

[0163] A computing system may include a client and an application server. The client and the application server are generally remote from each other and usually interact through a communication network. The relationship between the client and the application server is generated by computer programs running on corresponding computers and having a client-application server relationship with each other. The application server may be a cloud application server, also known as a cloud computing application server or a cloud host, which is a host product in a cloud computing application system to solve the defects of difficult management and weak business scalability in traditional physical hosts and VPS applications.

[0164] It should be understood that the various forms of processes shown above can be used to reorder, add or delete steps. For example, the steps described in the present invention can be executed in parallel, sequentially or in different orders, as long as the desired results of the technical solution of the present invention can be achieved, and this document does not limit this.

[0165] The above specific implementations do not constitute a limitation on the protection scope of the present invention. It should be understood by those skilled in the art that various modifications, combinations, sub-combinations and substitutions can be made according to design requirements and other factors. Any modification, equivalent substitution and improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.

Claims

1. A method for generating an optimization solution, characterized in that: Applied to database middleware, the database middleware is respectively connected to the routing node and the system library in communication, including: In response to an initial structured query language SQL statement containing a query performance optimization annotation forwarded by a routing node, forward the initial SQL statement to a distributed database for execution, and obtain an initial execution time of the initial SQL statement; When the initial execution time exceeds a set time threshold, obtaining an execution plan of the initial SQL statement from the distributed database, and obtaining database parameters and table metadata from the system library; Based on the execution plan, database parameters, table metadata of the initial SQL statement, and tables and fields associated with the initial SQL statement, a single-class optimization plan is generated, and the execution cost of the single-class optimization plan is estimated to obtain a target optimization plan.

2. The method according to claim 1, characterized in that The execution cost of the single-class optimization scheme is estimated to obtain a target optimization scheme, including: Classifying the single-category optimization solutions and prioritizing the single-category optimization solutions according to a preset category priority; Based on the sorting results, a single-class optimization solution is extracted in turn as the current solution, and the execution cost of the current solution is estimated to obtain the current estimated execution cost; When the current estimated execution cost is lower than the estimated execution costs of other single-type optimization solutions that have completed estimation, estimating the first execution time of the current solution according to the current estimated execution cost; When the first execution time does not exceed a set time threshold, determining the current solution as a target optimization solution; When the first execution time exceeds the set time threshold, the operation returns to execution based on the sorting result, and extracts one single-category optimization solution as the current solution in turn, until the target optimization solution is determined or the traversal of the single-category optimization solution is completed.

3. The method according to claim 1, characterized in that Estimating the execution cost of the single-class optimization solution to obtain a target optimization solution also includes: Classifying the single-class optimization solutions, and extracting a class of single-class optimization solutions as the current class in turn according to a preset class priority; Determine candidate solutions associated with the current class according to the single-class optimization solutions included in the current class; the candidate solutions include single-class optimization solutions and combined optimization solutions composed of optimization solutions of at least two categories; Estimate the execution cost of each candidate solution in the current class to obtain the estimated execution cost of each candidate solution; Calculating a reduction ratio of the estimated execution cost to the actual execution cost of the initial SQL statement, and determining at least one effective optimization plan in the sub-plans of the current class for storage according to the reduction ratio; estimating a second execution time of the effective optimization solution according to the estimated execution cost of the effective optimization solution; When the second execution time does not exceed the set time threshold, determining the current effective optimization solution as the target optimization solution; When the second execution time exceeds the set time threshold, the operation returns to sequentially extracting a single-class optimization solution as the current class until the target optimization solution is determined or the traversal of each class is completed.

4. The method according to claim 2, characterized in that: Before forwarding the initial SQL statement to the distributed database for execution and obtaining the initial execution time of the initial SQL statement, the process includes: Obtaining actual execution costs and actual execution times of at least two historical SQL statements during execution, and fitting a time prediction model according to the corresponding relationship between the actual execution costs and the actual execution times; Estimating a first execution time of the current solution according to the current estimated execution cost includes: The current estimated execution cost is input into the time prediction model to obtain the first execution time of the current solution output by the time prediction model.

5. The method according to claim 2, characterized in that: Estimate the execution cost of the current solution to obtain the current estimated execution cost, including: Determine at least one target node associated with the current solution in a distributed database, and send the current solution to the target node; The local execution cost fed back by the target node for the current solution is obtained, and based on the local execution cost fed back by each target node, a current estimated execution cost of the current solution is determined.

6. The method according to claim 3, characterized in that According to the single-class optimization solutions contained in the current class, determine the candidate solutions associated with the current class, including: Combining each pre-stored valid optimization solution with each single-class optimization solution of the current class in pairs to obtain at least one candidate combination solution; Determining a combined optimization scheme from among the candidate combined schemes according to the degree of association between at least two optimization schemes included in the candidate combined schemes; The single-class optimization solutions contained in the current class and the combined optimization solution are taken together as candidate solutions associated with the current class.

7. The method according to claim 2 or 3, characterized in that: The single-category optimization solutions are sorted from high to low according to category priority, including SQL rewriting category, table structure optimization category and parameter optimization category.

8. An optimization scheme generating device, characterized in that: Applied to database middleware, the database middleware is respectively connected to the routing node and the system library in communication, including: An initial time acquisition module, configured to respond to an initial structured query language SQL statement containing query performance optimization annotations forwarded by a routing node, forward the initial SQL statement to a distributed database for execution, and acquire an initial execution time of the initial SQL statement; An optimized data acquisition module is used to obtain the execution plan of the initial SQL statement from the distributed database and obtain database parameters and table metadata from the system library when the initial execution time exceeds a set time threshold; The optimization solution generation module is used to generate a single-type optimization solution based on the execution plan, database parameters, table metadata of the initial SQL statement, and the tables and fields associated with the initial SQL statement, and to estimate the execution cost of the single-type optimization solution to obtain a target optimization solution.

9. An electronic device, characterized in that: The electronic device comprises: 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, and the computer program is executed by the at least one processor so that the at least one processor can execute the optimization solution generation method according to any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement the optimization solution generation method according to any one of claims 1 to 7 when executed.

Images