Execution optimization method, product and equipment for database operation statements

Abstract

The invention provides an execution optimization method for database operation statements, a product and equipment. The method comprises the steps of determining system resource data of a target database in a current state in response to a database operation statement; obtaining a query tree corresponding to the database operation statement and decomposing the query tree to obtain a plurality of execution path nodes, wherein each execution path node corresponds to an operator type; performing cost evaluation on an operator type and an operator execution mode of each execution path node according to the system resource data, and determining a target operator corresponding to the execution path node according to a cost evaluation result; and combining the target operators corresponding to each execution path node to obtain an optimal execution plan of the database operation statement in the target database. By means of the method, the system resource data of the database can be dynamically obtained, the optimal execution plan better conforming to the current system resource use condition is generated, and therefore the execution efficiency of the database operation statement is improved.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, in particular to a database operation statement execution optimization method, product and equipment. BACKGROUND

[0002] In the current general database technology, the optimizer and the executor are very important for the execution of the database operation statement, which directly affects the execution efficiency of the statement. In the execution process of the optimizer and the executor, the basis for evaluation is mainly the statistical information, the cost model and the related information of the query characteristics dimension, and the related information of the system resource management dimension is not dynamically used as the evaluation basis. This implementation mode will lead to low execution efficiency of the database operation statement and waste of system resources, and due to the lack of flexibility, it will also lead to difficulty in subsequent optimization and maintenance. SUMMARY

[0003] In view of the above problems, the present application provides a database operation statement execution optimization method, product and equipment which overcomes the above problems or at least partially solves the above problems.

[0004] An object of the present application is to dynamically obtain system resource data in the current database, and determine the optimal execution plan of the database operation statement in the current database according to the system resource data;

[0005] A further object of the present application is to improve the execution efficiency of the database operation statement in the database and avoid waste of system resources.

[0006] In particular, the present application provides a database operation statement execution optimization method, comprising:

[0007] In response to the database operation statement, determining the system resource data of the target database in the current state;

[0008] Obtaining a query tree corresponding to the database operation statement and decomposing the query tree to obtain a plurality of execution path nodes, each execution path node corresponding to an operator type;

[0009] At each execution path node, the operator type and the operator execution mode of the node are cost evaluated according to the system resource data, and the target operator corresponding to the execution path node is determined according to the cost evaluation result;

[0010] Combining the target operator corresponding to each execution path node to obtain the optimal execution plan of the database operation statement in the target database;

[0011] According to the optimal execution plan, the execution of the database operation statement in the target database is completed.

[0012] Optionally, the step of performing cost evaluation on the operator type and the operator execution mode of each execution path node according to the system resource data comprises: calculating the operator cost corresponding to each operator type and different operator execution modes by using a preset operator cost calculation formula.

[0013] The preset operator cost calculation formula is:

[0014] Operator cost = old operator cost + ∑ 系统资源 (resource weight * cost evaluation coefficient)

[0015] wherein the old operator cost is the cost of executing the target operator by the target database without considering the current state of the system resource data, ∑ 系统资源 represents the sum of the costs of each type of system resource involved in the calculation of the target operator, the resource weight is the weight value of the current type of system resource in the target database in the calculation of the target operator, and the cost evaluation coefficient is the difficulty coefficient of obtaining the current type of system resource in the calculation of the target operator.

[0016] Optionally, the system resource data comprises preset threshold information of various types of system resources and usage information of various types of system resources in the current target database.

[0017] The step of obtaining the cost evaluation coefficient comprises:

[0018] determining the evaluation target operator and the evaluation target resource type;

[0019] obtaining the evaluation target resource usage information corresponding to the evaluation target resource type from the system resource usage information;

[0020] obtaining the evaluation target resource threshold information corresponding to the evaluation target resource type from the system resource threshold information;

[0021] performing a grading operation on the evaluation target resource usage information according to the evaluation target resource threshold information to obtain the resource occupation level of the evaluation target resource type in the current target database;

[0022] performing a conversion calculation on the resource occupation level by using a preset cost evaluation coefficient calculation formula to obtain the cost evaluation coefficient of the evaluation target operator in the target database.

[0023] Optionally, the preset cost evaluation coefficient calculation formula is:

[0024] Cost evaluation coefficient = w * resource occupation level + (1-w) * resource demand level;

[0025] wherein w is a preset weight coefficient of the resource occupation level, the resource occupation level is an index calculated by the real-time usage of the system resource of the corresponding type in the target database and the preset threshold value corresponding to the system resource, and the resource demand level is a preset demand level of the current evaluation target operator for the system resource of the corresponding type.

[0026] Optionally, the step of combining the target operators corresponding to each execution path node to obtain the optimal execution plan of the database operation statement in the target database further comprises:

[0027] determining whether there is a concurrent operator supporting concurrent execution in the target operator;

[0028] in the case where there is a concurrent operator supporting concurrent execution in the target operator, determining the concurrency degree corresponding to the concurrent operator according to the system resource data;

[0029] adding the concurrency degree corresponding to each concurrent operator to the optimal execution plan;

[0030] the step of executing the execution of the database operation statement in the target database according to the optimal execution plan.

[0031] Optionally, the step of determining the concurrency degree corresponding to the concurrent operator according to the system resource data comprises:

[0032] obtaining the concurrent resource usage information and the concurrent resource threshold value information corresponding to the concurrent operator in the target database;

[0033] performing a grading operation on the concurrent resource usage information according to the concurrent resource threshold value information to obtain the resource occupation level of the concurrent resource usage information in the current target database;

[0034] adjusting the concurrency degree of the concurrent operator according to the resource occupation level corresponding to the concurrent resource usage information.

[0035] Optionally, the system resource data comprises preset threshold value information of various types of system resources and usage information of various types of system resources in the current target database;

[0036] the step of obtaining the concurrent resource usage information and the concurrent resource threshold value information corresponding to the concurrent operator in the target database comprises:

[0037] determining the concurrent resource type involved in the concurrent operator and the concurrent adjustment threshold value corresponding to the concurrent operator;

[0038] obtaining the concurrent resource usage information corresponding to each concurrent resource type in the system resource usage information;

[0039] obtaining the concurrent resource threshold value information corresponding to each concurrent resource type from the system resource threshold value information.

[0040] Optionally, the step of adjusting the concurrency degree of the concurrent operator according to the resource occupation level corresponding to the concurrent resource usage information comprises:

[0041] obtaining a preset concurrency adjustment threshold corresponding to the concurrent operator;

[0042] in a case where the resource occupation level corresponding to the concurrent resource usage information is lower than the preset concurrency adjustment threshold, increasing the concurrency degree corresponding to the concurrent operator;

[0043] in a case where the resource occupation level corresponding to the concurrent resource usage information is higher than the preset concurrency adjustment threshold, decreasing the concurrency degree corresponding to the concurrent operator;

[0044] judging whether the concurrency degree meets a preset requirement;

[0045] if not, re-executing the step of obtaining the concurrent resource usage information and the concurrent resource threshold information corresponding to the concurrent operator in the target database.

[0046] According to another aspect of the present application, there is also provided a computer program product comprising a computer program which, when executed by a processor, implements the steps of the database operation statement execution optimization method of any of the above.

[0047] According to still another aspect of the present application, there is also provided a computer device comprising a memory, a processor, and a machine executable program stored in the memory and running on the processor, and the processor implements the steps of the database operation statement execution optimization method of any of the above when executing the machine executable program.

[0048] The database operation statement execution optimization method of the present application first determines the system resource data of a target database in a current state in response to a database operation statement; then obtains a query tree corresponding to the database operation statement and decomposes the query tree to obtain a plurality of execution path nodes, each execution path node corresponding to an operator type; next, at each execution path node, the operator type and the operator execution mode of the node are cost evaluated according to the system resource data, and a target operator corresponding to the execution path node is determined according to the cost evaluation result; the target operators corresponding to each execution path node are combined to obtain an optimal execution plan of the database operation statement in the target database; finally, the execution of the database operation statement in the target database is completed according to the optimal execution plan. Through this method, the system resource data of the database can be dynamically obtained in the generation phase and the execution phase of the execution plan, and according to this data, an optimal execution plan more in line with the current system resource usage can be generated, so as to maximize the execution efficiency of the database operation statement while ensuring full utilization of system resources.

[0049] The above and other objects, advantages and features of the present application will become more apparent after a reading of the following detailed description of the embodiments thereof, taken in conjunction with the annexed drawings. BRIEF DESCRIPTION OF DRAWINGS

[0050] Some specific embodiments of the present application will now be described in detail by way of example with reference to the drawings. The same or similar components or parts are designated by the same or similar reference numerals, and a repeated description thereof will be omitted. It should be noted that the drawings are not necessarily drawn to scale. In the drawings:

[0051] Figure 1 is a schematic diagram of an architecture of a method for optimizing execution of a database operation statement according to an embodiment of the present application;

[0052] Figure 2 is a schematic diagram of a flow of a method for optimizing execution of a database operation statement according to an embodiment of the present application;

[0053] Figure 3 is a schematic diagram of a cost evaluation coefficient conversion process in a method for optimizing execution of a database operation statement according to an embodiment of the present application;

[0054] Figure 4 is a schematic diagram of a concurrent operator adjustment flow in a method for optimizing execution of a database operation statement according to an embodiment of the present application;

[0055] Figure 5 is a schematic diagram of a computer program product according to an embodiment of the present application;

[0056] Figure 6 is a schematic diagram of a computer-readable storage medium according to an embodiment of the present application; and

[0057] Figure 7 is a schematic diagram of a computer device according to an embodiment of the present application. DETAILED DESCRIPTION

[0058] It should be understood by those skilled in the art that the embodiments described below are only some of the embodiments of the present application, rather than all the embodiments of the present application, and are intended to explain the technical principles of the present application, rather than limit the protection scope of the present application. Based on the embodiments provided by the present application, all other embodiments obtained by those of ordinary skill in the art without creative effort shall fall within the protection scope of the present application.

[0059] It should be noted that the logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any computer-readable medium for use by, or in conjunction with, an instruction execution system, apparatus or device (such as a computer-based system, a processor-included system or other system that can fetch and execute instructions from, an instruction execution system, apparatus or device).

[0060] An optional example of database operation statements is SQL (Structured Query Language). SQL statements have multiple functions such as data manipulation and data definition. This language is interactive and provides great convenience to users. In the method of this invention, the database operation statements are generally received either by user input or automatically generated by the database program during operation due to business needs. Those skilled in the art can decide on the method of receiving database operation statements according to the actual situation.

[0061] In a database system, the overall execution process of an SQL statement generally involves several different stages, including:

[0062] SQL parsing phase (parser): mainly responsible for parsing SQL statements and generating parse trees, including lexical analysis, syntax analysis, semantic analysis, etc.

[0063] Statement rewriting phase: Based on the rewriting rules, the parse tree generated in the SQL parsing phase is rewritten to generate a new equivalent parse tree;

[0064] Query planning phase (optimizer): Primarily responsible for generating execution plans, estimating costs, and selecting the optimal execution path;

[0065] Query execution phase (executor): Primarily responsible for executing specific operator operations according to the execution plan to obtain the corresponding query results;

[0066] Returning Results Stage: The result set generated by the executor is returned to the client for display, either row by row or in batches.

[0067] The "query plan phase" (optimizer) and the "query execution phase" (executor) are two very important phases in the execution of SQL statements. They are mainly used to solve the core problems of "how to plan" and "how to do it" in SQL statements. Therefore, they directly affect the execution efficiency of SQL statements.

[0068] In existing database systems, the optimizer and executor are crucial for SQL execution, directly impacting statement execution efficiency. During the optimizer and executor's execution, the evaluation is primarily based on statistical information, cost models, and query characteristics, without dynamically incorporating system resource management information. This approach presents several major problems:

[0069] 1) Low execution efficiency

[0070] If SQL execution plans are not generated based on system resources, the database may not be able to fully utilize existing hardware resources, such as CPU, memory, and disk I / O (Input / Output), resulting in low execution efficiency. Specifically, the database may not be able to select appropriate indexes, join methods, or sorting algorithms, thus impacting query performance.

[0071] 2) Waste of system resources

[0072] Because the execution plan is not optimized based on the actual resource usage of the system, the database may allocate excessive resources when executing SQL statements, and these resources may not be fully utilized. This waste of resources not only affects database performance but may also increase operating costs.

[0073] 3) Lack of flexibility

[0074] System resources are dynamic, including hardware upgrades, changes in database load, and updates to statistics. If the execution plan is not generated based on system resources, it may not be able to adjust in a timely manner to adapt to the new environment when system resources change. This will result in the database performance not being continuously optimized and lacking the necessary flexibility.

[0075] 4) Difficult to optimize and maintain

[0076] Generating execution plans without considering system resources can also create difficulties for database maintenance. Because the execution plan does not take into account the actual usage of system resources, when system resources change, it may be necessary to manually adjust the execution plan or optimizer rules to re-optimize the SQL statements. This not only increases the workload of maintenance but may also introduce new errors or performance issues.

[0077] To address the aforementioned problems, this invention provides a method for optimizing the execution of database operation statements. Figure 1This is a schematic diagram of the architecture of a database operation statement execution optimization method according to an embodiment of the present invention. It mainly includes system resource data 110 and a pre-set system configuration 120. The resource management module 130 obtains a unified view 131 of the resource usage of the database in the current state by recording the system resource data 110. The database also includes a parser 140, an optimizer 150, and an executor 160, etc. The optimizer 150 and the executor 160 can generate the optimal execution plan for the corresponding SQL statement based on the unified view 131 of resource usage and the system configuration 120. That is, by dynamically obtaining this resource usage information, and generating an optimal execution plan that is more in line with the current system resource usage based on such information, such as determining the optimal execution path, operator type, and execution method of the current SQL statement, thereby maximizing the execution efficiency of the SQL statement while ensuring full utilization of system resources.

[0078] The resource management module 130 includes relatively complete resource management functions in the database, which mainly include, but are not limited to, monitoring and controlling system resources, as well as concurrency control. Regarding system resource data 110, the resource management module 130 is used, on the one hand, to control the usage restrictions of system resources by database system processes; on the other hand, it is used to monitor and obtain the real-time usage of various system resources (i.e., the usage information of various types of system resources in the current target database), and save the relevant information to the unified view of the database system for easy access and use by other processes.

[0079] In some optional embodiments, the resource management module 130 monitors the system resource data 110 by either independently managing the system resources of the database itself, or by combining it with the resource management and restriction mechanisms provided by the operating system kernel. This invention does not limit the management method of the resource management module 130; those skilled in the art can determine it based on the operating system infrastructure and the inherent functionalities of the database system.

[0080] Optionally, the unified resource usage view 131 is generally used to record the real-time usage of various system resources (including but not limited to CPU, memory, disk I / O, network bandwidth, etc.) in the database. It is usually stored in the unified resource usage view 131 in the form of structured data. This data includes, but is not limited to, the total amount of various system resources, the duration of resource occupation, the absolute value, and the overall percentage.

[0081] The unified view of resource usage 131 can have various actual forms: it can be a database system table; it can be a shared memory page that is globally visible to the database; or it can be a file with a fixed format that can be parsed and recognized by the database. Those skilled in the art can determine this based on the specific implementation of the database system.

[0082] Optionally, the system configuration 120 involved in this invention mainly defines the occupancy level of specified types of system resources by setting thresholds (i.e., pre-set threshold information for various types of system resources). Logically, the database system divides the occupancy levels of different types of resources into several levels (e.g., high, medium, and low), with the specific number of levels determined by actual usage requirements. The threshold parameters in the system configuration are used to define the threshold boundaries of different levels. These threshold parameters can be either the absolute value of the usage of a specified type of resource or the percentage of usage of that type of resource. These thresholds generally need to be pre-set so that the system can dynamically identify the occupancy level based on the usage of different types of resources. Finally, the database system converts the occupancy level into a cost evaluation coefficient using a pre-set cost evaluation coefficient calculation formula, which is then added to the optimizer's cost evaluation calculation. Ultimately, by dynamically adjusting the cost evaluation coefficient of the corresponding resource type by capturing system resource usage in real time, the effect of dynamically optimizing the SQL execution plan is achieved.

[0083] Based on the above settings Figure 2 This is a flowchart illustrating a method for optimizing the execution of database operation statements according to an embodiment of the present invention, as shown below. Figure 2 As shown, the method for optimizing the execution of database operation statements includes at least the following steps S201 to S206.

[0084] Step S201: In response to the database operation statement, determine the resource data of the target database in its current state. In the method of this invention, the database operation statement can generally be received by user input or automatically generated by the database program during operation due to business needs. Those skilled in the art can determine the method of receiving the database operation statement according to the actual situation. The resource data generally refers to key system resources required for database operation, including but not limited to CPU utilization, memory usage (including allocated / remaining memory), disk I / O throughput (read / write speed), and network bandwidth usage.

[0085] The methods for obtaining resource data can generally be through, for example... Figure 1 The resource management module 130 shown acquires data in real time to determine various real-time resource data of the database when executing the database operation statement, thereby facilitating the dynamic adjustment of the execution plan of the database operation statement based on the real-time resource data.

[0086] Step S202: Obtain the query tree corresponding to the database operation statement and decompose the query tree to obtain multiple execution path nodes. Each execution path node corresponds to an operator type.

[0087] In databases, query tree generation typically involves a parser performing lexical analysis, syntactic analysis, and semantic validation on input database operation statements (such as SELECT, JOIN, etc.) to generate an Abstract Syntax Tree (AST). This AST is then logically optimized and transformed into a query tree. The query tree describes the logical flow of the query in a tree-like structure, for example, "read table A → read table B → join A and B → filter results." This next step breaks down the query tree into multiple independent execution path nodes based on logical operations, with each node corresponding to an operator type. For example, nodes for reading table data correspond to the "scan operator type"; nodes for multi-table joins correspond to the "join operator type"; nodes for filtering conditions correspond to the "filter operator type"; and nodes for sorting operations correspond to the "sort operator type," etc.

[0088] Step S203: At each execution path node, a cost assessment is performed on the operator type and operator execution method of that node based on system resource data. In the process of generating an execution plan, estimating costs, and selecting the optimal execution path, the optimizer of this invention not only relies on existing cost estimation standards but also takes the real-time usage of current system resources as a reference, thereby dynamically selecting the optimal execution path suitable for the current system resource load and ultimately generating the optimal execution plan.

[0089] In some optional embodiments, step S203 may generally include: calculating the operator cost corresponding to different operator execution methods in each operator type using a preset operator cost calculation formula, as shown in equation (1):

[0090] Operator cost = Operator old cost + ∑ 系统资源 (Resource weight * cost assessment coefficient) Equation (1)

[0091] Where the old cost of the operator is the cost of calculating the target operator in the target database without considering the current state of system resource data, ∑ 系统资源This represents the sum of costs for all types of system resources involved in calculating the target operator. Resource weights are the weight values ​​of the current type of system resource in the target database within the calculation of the target operator. For example, if the current operator type (such as the Sort operator) only involves CPU and memory resources, then the "CPU resource weight" and "memory resource weight" are positive numbers greater than 0, while the resource weights of other irrelevant system resources are 0. The specific value of each resource weight needs to be defined internally by the database system and is positively correlated with the operator's dependence on that type of resource. In principle, the higher the operator's dependence on that type of resource, the larger its weight; conversely, the smaller the weight. The cost evaluation coefficient is the difficulty coefficient for the target operator to acquire the current type of system resource. This value mainly reflects the cost evaluation coefficient for the current operator to acquire the specified system resource, or the ease or difficulty of acquiring the specified system resource. The higher the difficulty, the higher the coefficient value; conversely, the lower the coefficient value. This cost evaluation coefficient is calculated based on the actual usage of this type of system resource, through threshold grading, and then conversion.

[0092] This invention does not limit the specific types of system resources. Those skilled in the art can determine the types of system resources involved in different operators based on the actual situation. For example, operator cost = old operator cost + CPU resource weight * CPU cost evaluation coefficient + memory resource weight * memory cost evaluation coefficient + disk I / O resource weight * disk I / O resource weight + ...

[0093] The main tasks of a database optimizer are typically to generate candidate execution plans, calculate execution costs, and select the optimal execution path. Generally, the optimizer uses dynamic programming to decompose the problem into several subproblems, solving them sequentially. The solutions to earlier subproblems provide valuable information and conditions for solving subsequent subproblems. When solving subproblems, various candidate local solutions are listed. Based on certain rules, the optimizer decides which local solutions are likely to reach the optimal solution and discards others. This process continues until all the local optima of the subproblems are obtained, which is then used as the optimal solution for the overall problem.

[0094] In the context of this invention, generating the optimal execution path involves decomposing the query tree corresponding to the SQL statement into different types of operators (including, but not limited to, scan operators, join operators, materialization operators, and control operators). Each type of operator has multiple execution methods; for example, the join operator includes nested loop joins, merge joins, and hash joins. The optimizer then selects the most efficient operator and its execution method at each node of the execution path through cost estimation, thereby facilitating the subsequent determination of the optimal execution plan for the SQL statement.

[0095] The method of this invention is to add the "cost evaluation coefficient" obtained by transforming the "system resource usage" into the cost evaluation calculation of the database system when the optimizer performs the cost evaluation of the operator (that is, to preset the operator cost calculation formula), so as to associate the system resource usage with the operator cost evaluation result, thereby dynamically influencing the optimizer's selection of the optimal execution plan.

[0096] Optionally, system resource data may generally include pre-set system resource threshold information of various types and system resource usage information of various types in the current target database. The steps for obtaining the cost assessment coefficient may generally include: determining the target operator and the target resource type; obtaining the target resource usage information corresponding to the target resource type from the system resource usage information; obtaining the target resource threshold information corresponding to the target resource type from the system resource threshold information; performing a tiered operation on the target resource usage information based on the target resource threshold information to obtain the resource occupancy level of the target resource type in the current target database; and converting and calculating the resource occupancy level using a pre-set cost assessment coefficient calculation formula to obtain the cost assessment coefficient corresponding to the target operator in the target database.

[0097] Different types of operators have different cost assessment coefficients for various resources. The calculation of the cost assessment coefficient is related to two factors: firstly, the current usage of such system resources; in principle, the more resources are occupied, the larger the cost assessment coefficient. Secondly, the operator's demand for such system resources; in principle, the greater the resource demand, the larger the cost assessment coefficient.

[0098] Optionally, the formula for calculating the preset cost evaluation coefficient is shown in equation (2):

[0099] Cost assessment coefficient = w * resource occupancy level + (1-w) * resource demand level; Equation (2)

[0100] Where w is a pre-defined weighting coefficient for the resource occupancy level, and 0 ≤ w ≤ 1, it is generally defined internally by the database system; the resource occupancy level is an indicator calculated by combining the real-time usage of the corresponding type of system resource in the target database with the preset threshold of the system resource itself, that is, it is obtained by combining the actual usage of this type of system resource with the threshold classification in the system configuration. In addition, the total number of levels is generally defined internally by the database system according to its own system requirements and control granularity (for example, divided into 3 levels, high, medium and low, corresponding to 3, 2 and 1 respectively). The resource demand level is a pre-defined demand level of the current evaluation target operator for the corresponding type of system resource.

[0101] Figure 3This is a schematic diagram of the cost evaluation coefficient conversion process in a database operation statement execution optimization method according to an embodiment of the present invention, as shown below. Figure 3 As shown, there are many types of system resources, such as CPU, memory and disk I / O. The actual usage of different types of system resources in the current database can be classified according to a pre-set threshold. For example, the actual usage of CPU in the current database is between the first threshold and the second threshold. After classification, the resource occupancy level of CPU can be determined to be 2. Then, the cost evaluation coefficient corresponding to CPU can be obtained by conversion calculation according to the preset cost evaluation coefficient calculation formula shown in Equation (2). Similarly, the resource occupancy level of memory in the current database is also 2. The cost evaluation coefficient corresponding to memory can be obtained by conversion calculation.

[0102] Step S204: Determine the target operator corresponding to the execution path node based on the cost assessment results. This selection of operator execution methods based on system resource usage effectively improves the balance of system resource utilization, preventing bottlenecks caused by excessive consumption of certain resources and thus affecting the overall efficiency of system resource utilization.

[0103] To clearly illustrate the effect of the method of the present invention, a specific example is provided: taking the selection of the optimizer join operator as an example, the join operator itself has various execution methods, including but not limited to Nested Loop Join, Hash Join, Merge Join, and Map Join. The resource requirement levels (represented by high, medium-high, medium, medium-low, and low) of the join operators with different execution methods are shown in Table 1:

[0104] Table 1. Resource requirements of join operators with different execution methods.

[0105] Connection Type CPU Resource Requirement Memory Resource Requirement Disk IO Resource Requirement Nested Loop Join Low Low High Hash Join High High Medium Merge Join Medium Medium High Medium High Map Join Medium High Low

[0106] In one alternative example, a join operation is performed between a smaller table and a larger table. Under the traditional optimizer approach, the join type is selected based on cost evaluation. With the table structure and data size remaining constant, the evaluation results are generally consistent, leading to the selection of the same join type (e.g., ultimately choosing a Map Join). This approach performs well in systems with relatively abundant memory resources, but in scenarios with insufficient memory, it further consumes memory, becoming a bottleneck in system resources. This not only affects the efficient execution of subsequent SQL statements but also leads to the underutilization of other system resources. The root cause of this problem is that traditional cost evaluation is not correlated with the current system resource usage, making it unable to accurately identify changes in the system environment and make corresponding adjustments.

[0107] This invention adds a comprehensive cost assessment of system resources to the traditional cost evaluation method. Taking the above scenario as an example, when system memory resources are abundant, the resource acquisition cost of the already low-cost MapJoin execution method is not significant, and the system will still choose this execution method after comprehensively calculating the total cost of the operators. However, when system memory resources are insufficient, the memory resource acquisition cost of the MapJoin execution method will be high, and the calculated total cost of the operators will be higher than that of the Nested Loop Join execution method, which has lower memory resource requirements. Therefore, the system will choose the Nested Loop Join method. This can alleviate the rapid growth of system memory resource consumption and also allow other types of system resources (such as disk I / O in tables) to be fully utilized, thereby balancing the utilization ratio of various system resources and maximizing the overall efficiency of system resource utilization.

[0108] Step S205 combines the target operators corresponding to each execution path node to obtain the optimal execution plan for the database operation statement in the target database. This operation allows the final selected target operators of each node to be chained into an ordered execution chain according to the logical order of the query tree (e.g., "scan → filter → join → sort"), forming an efficient execution plan.

[0109] Step S206: Execute the database operation statements in the target database according to the optimal execution plan.

[0110] This method adds a comprehensive cost assessment of system resources to the traditional cost evaluation. By taking into account the current resource usage of the system and using specific calculation methods, it calculates the optimal execution plan of SQL statements that is more in line with the current system resource usage status. This makes reasonable use of system resources, improves the overall efficiency of resource utilization, and avoids resource bottlenecks. It also improves the dynamic flexibility of SQL statement execution plans, allowing them to change with the system resources of the operating environment, thereby reducing the difficulty of SQL optimization and maintenance.

[0111] In some alternative embodiments, certain executor operators (including but not limited to scan operators, HashJoin operators, etc.) support concurrency. Regardless of whether the database system backend uses a process model or a thread model, the execution efficiency of operators can be improved through concurrent operations of multiple processes (threads). This involves the control of concurrency. The operator concurrency of traditional databases is completely fixed through database system configuration or internal definition, and cannot be dynamically adjusted according to the current resource usage of the system.

[0112] Optionally, after the step of combining the target operators corresponding to each execution path node to obtain the optimal execution plan of the database operation statement in the target database, the process may also include: determining whether there are concurrent operators that support concurrent execution in the target operators; if there are concurrent operators that support concurrent execution in the target operators, determining the concurrency degree corresponding to the concurrent operators based on system resource data; adding the concurrency degree corresponding to each concurrent operator to the optimal execution plan; and executing the steps of completing the database operation statement in the target database according to the optimal execution plan.

[0113] The steps for determining the concurrency level of a concurrency operator based on system resource data generally include: obtaining the concurrency resource usage information and concurrency resource threshold information corresponding to the concurrency operator in the target database; performing a tiered operation on the concurrency resource usage information based on the concurrency resource threshold information to obtain the resource occupancy level of the concurrency resource usage information in the current target database; and adjusting the concurrency level of the concurrency operator based on the resource occupancy level corresponding to the concurrency resource usage information.

[0114] System resource data generally includes pre-set system resource threshold information for various types and system resource usage information for various types in the current target database. The steps to obtain the concurrent resource usage information and concurrent resource threshold information corresponding to a concurrent operator in the target database generally include: determining the concurrent resource types involved in the concurrent operator and the corresponding concurrent adjustment threshold; obtaining the concurrent resource usage information corresponding to each concurrent resource type from the system resource usage information; and obtaining the concurrent resource threshold information corresponding to each concurrent resource type from the system resource threshold information.

[0115] The steps for adjusting the concurrency of a concurrency operator based on the resource occupancy level corresponding to the concurrency resource usage information generally include: obtaining a preset concurrency adjustment threshold corresponding to the concurrency operator; increasing the concurrency of the concurrency operator if the resource occupancy level corresponding to the concurrency resource usage information is lower than the preset concurrency adjustment threshold; decreasing the concurrency of the concurrency operator if the resource occupancy level corresponding to the concurrency resource usage information is higher than the preset concurrency adjustment threshold; determining whether the concurrency meets the preset requirements; if not, re-executing the steps of obtaining the concurrency resource usage information and concurrency resource threshold information corresponding to the concurrency operator in the target database.

[0116] Figure 4 This is a schematic diagram of the concurrency operator adjustment process in a database operation statement execution optimization method according to an embodiment of the present invention, as shown below. Figure 4 As shown, the concurrent operator adjustment process includes at least the following steps S401 to S408.

[0117] Step S401: Determine if there are concurrent operators that support concurrent execution among the target operators. Traverse all target operators in the optimal execution plan and determine whether they support concurrent execution based on the operator type. Operators that support concurrent execution include: scan operators (such as parallel table scan, parallel index scan); join operators (such as parallel Hash Join, parallel Merge Join); aggregation operators (such as parallel Group By, parallel sorting).

[0118] Step S402: Obtain the concurrent resource usage information and concurrent resource threshold information corresponding to the concurrent operator in the target database.

[0119] Step S403: The concurrent resource usage information is categorized based on the concurrent resource threshold information to obtain the resource occupancy level of the concurrent resource usage information in the current target database. The concurrent resource usage information generally includes: CPU utilization; memory usage; disk I / O throughput, etc.; the concurrent resource threshold information generally includes: CPU high watermark (e.g., limiting concurrency when utilization > 80%); memory low watermark (e.g., reducing parallelism when available memory < 1GB); disk I / O saturation threshold (e.g., stopping increasing concurrency when IOPS > 10000). Subsequently, for each concurrent operator, a mapping table between its corresponding resource usage and thresholds is generated.

[0120] Step S404: Determine whether the resource occupancy level is lower than the preset concurrency adjustment threshold. The preset concurrency adjustment threshold can generally be a system default or pre-configured resource level threshold (e.g., concurrency can be increased when "resource occupancy level ≤ medium").

[0121] An optional example of the judgment process is as follows: if the occupancy level of all resources is below the threshold (e.g., all are "low" or "medium"), then proceed to step S405; if the occupancy level of any resource is above the threshold (e.g., "high"), then proceed to step S406.

[0122] Step S405: If the determination in step S404 is yes, increase the concurrency level corresponding to the concurrency operator. The purpose of increasing the concurrency level corresponding to the concurrency operator is to improve concurrency and accelerate query execution when resources are sufficient. Concurrency adjustment strategies generally include: linear growth: increasing by a fixed step size each time (e.g., +1 thread); exponential growth: increasing proportionally (e.g., current concurrency level × 1.5); adaptive growth: dynamically calculating based on resource availability (e.g., new concurrency level = current concurrency level × (1 + resource idle rate)), etc.

[0123] Step S406: If the determination in step S404 is negative, reduce the concurrency level corresponding to the concurrency operator. The purpose of reducing the concurrency level of the concurrency operator is to reduce parallelism when resources are scarce, thus avoiding system overload. Concurrency adjustment strategies generally include: linear reduction: reducing by a fixed step size each time (e.g., -1 threads); exponential reduction: reducing proportionally (e.g., current concurrency level × 0.7); resource-priority reduction: prioritizing the reduction of concurrency for operators with high dependence on scarce resources (e.g., reducing the concurrency of Hash Join when memory is scarce), etc.

[0124] Step S407: After step S405 or S406 has been completed, determine whether the concurrency level meets the preset requirements. If not, continue to step S402. The purpose of this step is to ensure that the adjusted concurrency level is within a reasonable range and to avoid ineffective adjustments. The preset requirements generally include: stability check: whether the magnitude of N consecutive adjustments is less than a threshold (e.g., change <10%); resource utilization check: whether the resource usage level after adjustment is close to but does not exceed the threshold (e.g., CPU utilization is maintained at 70%-80%); performance indicator check: whether execution time, throughput, etc., meet expectations (in conjunction with a feedback mechanism).

[0125] Step S408: Add the concurrency level corresponding to each concurrency operator to the optimal execution plan.

[0126] In summary, to address the concurrency issue, the method of this invention further dynamically optimizes and adjusts the concurrency of concurrently executable operators. The executor in the database system can obtain the current system resource usage and determine the resource occupancy level through threshold grading. When initially executing an operator, the executor derives the corresponding resource occupancy level based on the current system resource usage for each type. Then, based on the concurrency adjustment threshold set in the system configuration, it determines whether to increase or decrease the parallelism. After adjustment, it checks whether the condition for ending the parallelism adjustment is met. If it is, the operator is executed at the current parallelism; otherwise, a new round of adjustment begins until the adjustment termination condition is met. This dynamic adjustment of operator concurrency based on system resource usage can further effectively improve the resource utilization efficiency and execution stability of the database system.

[0127] Taking the concurrent execution of the scan operator as an example, on the one hand, when the database system load changes, the utilization of system resources will also change accordingly. Based on traditional processing methods, the system executes the scan operator according to a fixed process concurrency level. Even if the current system load is low, excess system resources (CPU, memory, I / O, etc.) cannot be fully utilized. This technical solution, however, can dynamically adjust the execution concurrency of the scan operator based on system resource usage. If the system resources on which the scan operator depends have a low resource consumption level, the execution concurrency level is increased; otherwise, it is decreased. This ensures that system resources are fully utilized when the system load is low, while preventing excessive consumption of specific system resources and potential system crashes when the system load is high. On the other hand, when the system hardware configuration changes, even if the database system load remains constant, the utilization of system resources will also change. That is, when the hardware configuration is improved, the corresponding system resource consumption level will decrease; conversely, it will increase. Even with a fixed concurrency level, there are still issues where certain system resources are underutilized or excessively consumed. The method of this invention can solve this problem by dynamically adjusting the concurrency level of the scan operator, which not only adapts to changes in the hardware environment to the greatest extent but also greatly reduces the manual maintenance cost of the database system.

[0128] In summary, the technical effects brought about by the method of the present invention generally include the following aspects:

[0129] 1) Improved SQL query efficiency

[0130] Databases can make reasonable and full use of existing hardware resources, and based on this, select appropriate indexes, join methods or sorting algorithms to generate more efficient SQL execution plans to guide the database engine to execute database operation statements in the best way, thereby improving overall query efficiency. In addition, by optimizing the execution plan, unnecessary system resource consumption can be reduced, further improving system performance.

[0131] 2) Optimized the overall system resource utilization efficiency.

[0132] The SQL execution plan is optimized based on the actual resource usage of the system to avoid the database system allocating too many resources when executing SQL statements, which would result in these resources not being fully utilized. This ensures that the execution plan can make full use of system resources, such as CPU processing time, cache, and disk I / O, thereby reducing excessive occupation of system resources and improving resource utilization.

[0133] 3) Enhanced system flexibility and scalability

[0134] On the one hand, an efficient execution plan can support larger data volumes and more complex query requirements, thereby enhancing the scalability of the system; on the other hand, when the data volume increases or the query complexity increases, optimizing the execution plan can also maintain the stability and overall performance of the system.

[0135] 4) Improved adaptability to hardware environment

[0136] System resources are dynamic, including hardware upgrades, changes in database load, and updates to statistics. The database system optimizer and executor can dynamically adapt to the actual resource usage environment of the system. This not only ensures the efficient execution of SQL statements in different system resource environments but also improves the overall database system's adaptability to the hardware environment.

[0137] 5) Simplifies the difficulty of SQL optimization and maintenance.

[0138] Even if system resources change for various reasons, the database system can effectively adjust accordingly to ensure the efficient execution of SQL statements. This avoids the need for system optimization analysis and manual adjustment of execution plans or optimizer rules to re-optimize SQL statements, greatly reducing the workload of system maintenance.

[0139] The flowchart provided in this embodiment is not intended to indicate that the operations of the method will be performed in any particular order, or that all operations of the method are included in every case. Furthermore, the method may include additional operations. Within the scope of the technical concept provided by the method in this embodiment, additional variations can be made to the above method.

[0140] It should be understood that in some embodiments, the components may be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods may be implemented using software or firmware stored in memory and executed by a suitable instruction execution system.

[0141] This embodiment also provides a computer program product 10, a computer-readable storage medium 20, and a computer device 30. Figure 5 This is a schematic diagram of a computer program product 10 according to an embodiment of the present invention. Figure 6 This is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention. Figure 7This is a schematic diagram of a computer device 30 according to an embodiment of the present invention. The computer program product 10 includes a computer program 11, which, when executed by the processor 32, implements the steps of the database operation statement execution optimization method described above. A computer-readable storage medium 20 stores the computer program 11 thereon, which, when executed by the processor 32, implements the steps of the database operation statement execution optimization method described above. The computer device 30 may include a memory 31, a processor 32, and the computer program 11 stored in the memory 31 and running on the processor 32.

[0142] The computer program 11 used to perform the operations of this invention may be assembly instructions, Instruction Set Architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, status setting data, integrated circuit configuration data, or source code or object code written in any combination of one or more programming languages ​​and procedural programming languages. The computer program 11 may execute entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network, including a Local Area Network (LAN) or Wide Area Network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider). In some embodiments, to perform aspects of this invention, electronic circuits, including, for example, programmable logic circuits, Field-Programmable Gate Arrays (FPGAs), or Programmable Logic Arrays (PLAs), may execute computer-readable program instructions using status information from computer-readable program instructions to personalize the electronic circuits.

[0143] For the purposes of this embodiment, computer program product 10 is a related product containing computer program 11. For the purposes of this embodiment, computer-readable storage medium 20 is a tangible device capable of holding and storing computer program 11, and can be any device capable of containing, storing, communicating, propagating, or transmitting program 11 for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of computer-readable storage medium 20 include: portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), portable optical disc read-only memory (CD-ROM), digital versatile disc (DVD), memory stick, floppy disk, mechanical encoding device, and any suitable combination thereof.

[0144] Computer device 30 can be, for example, a server, desktop computer, laptop computer, tablet computer, or smartphone. In some examples, computer device 30 can be a cloud computing node. Computer device 30 can be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, program modules can include routines, programs, object programs, components, logic, data structures, etc., that perform specific tasks or implement specific abstract data types. Computer device 30 can be implemented in a distributed cloud computing environment where tasks are performed by remote processing devices linked through a communication network. In a distributed cloud computing environment, program modules can reside on local or remote computing system storage media, including storage devices.

[0145] Computer device 30 may include a processor 32 adapted to execute stored instructions and a memory 31 that provides temporary storage space for the operation of said instructions during operation. The processor 32 may be a single-core processor, a multi-core processor, a computing cluster, or any other configuration. The memory 31 may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0146] Computer device 30 may also include a network adapter / interface and an input / output (I / O) interface. The I / O interface allows external devices that can be connected to the computer device to input and output data. The network adapter / interface provides communication between the computer device and a network, typically represented as a communication network.

[0147] Therefore, those skilled in the art should recognize that although numerous exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications conforming to the principles of the present invention can be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Thus, the scope of the present invention should be understood and construed as covering all such other variations or modifications.

Claims

1. A method for optimizing the execution of database operation statements, comprising: In response to the database operation statement, determine the system resource data of the target database in its current state; Obtain the query tree corresponding to the database operation statement and decompose the query tree to obtain multiple execution path nodes, each execution path node corresponding to an operator type; At each execution path node, a cost assessment is performed on the operator type and operator execution method of the node based on the system resource data, and the target operator corresponding to the execution path node is determined based on the cost assessment results; The target operators corresponding to each execution path node are combined to obtain the optimal execution plan of the database operation statement in the target database; The database operation statements are executed in the target database according to the optimal execution plan.

2. The method for optimizing the execution of database operation statements according to claim 1, wherein, The step of evaluating the cost of the operator type and operator execution method of each execution path node based on the system resource data includes: calculating the operator cost corresponding to each operator type and different operator execution methods using a preset operator cost calculation formula; The preset operator cost calculation formula is as follows: Operator cost = Operator old cost + ∑ 系统资源 (Resource weight * Cost assessment coefficient) The old cost of the operator is the cost incurred by the target database in calculating the target operator without considering the current state of the system resource data, ∑ 系统资源 This represents the sum of costs of all types of system resources involved in the computational target operator. The resource weight is the weight value of the current type of system resource in the target database in the computational target operator. The cost evaluation coefficient is the difficulty coefficient of the computational target operator in acquiring the current type of system resource.

3. The method for optimizing the execution of database operation statements according to claim 2, wherein, The system resource data includes pre-set system resource threshold information of various types and current system resource usage information of various types in the target database; The steps for obtaining the cost assessment coefficients include: Determine the target operator and the type of target resource to be evaluated; Obtain the evaluation target resource usage information corresponding to the evaluation target resource type from the system resource usage information; Obtain the evaluation target resource threshold information corresponding to the evaluation target resource type from the system resource threshold information; The evaluation target resource usage information is classified according to the evaluation target resource threshold information to obtain the resource occupancy level of the evaluation target resource type in the current target database; The cost assessment coefficient corresponding to the assessment target operator in the target database is obtained by converting and calculating the resource occupancy level using a preset cost assessment coefficient calculation formula.

4. The method for optimizing the execution of database operation statements according to claim 3, wherein, The formula for calculating the preset cost evaluation coefficient is as follows: Cost assessment coefficient = w * resource occupancy level + (1-w) * resource demand level; Wherein w is a pre-set weight coefficient for the resource occupancy level, the resource occupancy level is an indicator calculated by combining the real-time usage of the corresponding type of system resource in the target database with a preset threshold corresponding to the system resource itself, and the resource demand level is a pre-set demand level for the corresponding type of system resource by the current evaluation target operator.

5. The method for optimizing the execution of database operation statements according to claim 1, wherein, The step of combining the target operators corresponding to each execution path node to obtain the optimal execution plan of the database operation statement in the target database further includes: Determine whether there are concurrent operators that support concurrent execution among the target operators; If there are concurrent operators that support concurrent execution in the target operators, the concurrency degree corresponding to the concurrent operators is determined based on the system resource data; The concurrency degree corresponding to each of the concurrency operators is added to the optimal execution plan; The steps involve executing the database operation statements in the target database according to the optimal execution plan.

6. The method for optimizing the execution of database operation statements according to claim 5, wherein, The step of determining the concurrency degree corresponding to the concurrency operator based on the system resource data includes: Obtain the concurrent resource usage information and concurrent resource threshold information corresponding to the concurrent operator in the target database; The concurrent resource usage information is classified according to the concurrent resource threshold information to obtain the resource occupancy level of the concurrent resource usage information in the current target database; The concurrency degree of the concurrency operator is adjusted according to the resource occupancy level corresponding to the concurrency resource usage information.

7. The method for optimizing the execution of database operation statements according to claim 6, wherein, The system resource data includes pre-set system resource threshold information of various types and current system resource usage information of various types in the target database; The step of obtaining the concurrent resource usage information and concurrent resource threshold information corresponding to the concurrent operator in the target database includes: Determine the types of concurrent resources involved in the concurrency operator and the concurrency adjustment threshold corresponding to the concurrency operator; Obtain the concurrent resource usage information corresponding to each concurrent resource type from the system resource usage information; Obtain the concurrent resource threshold information corresponding to each concurrent resource type from the system resource threshold information.

8. The method for optimizing the execution of database operation statements according to claim 6, wherein, The step of adjusting the concurrency degree of the concurrency operator according to the resource occupancy level corresponding to the concurrency resource usage information includes: Obtain the preset concurrency adjustment threshold corresponding to the concurrency operator; If the resource occupancy level corresponding to the concurrent resource usage information is lower than the preset concurrent adjustment threshold, the concurrency degree corresponding to the concurrent operator is increased; If the resource occupancy level corresponding to the concurrent resource usage information is higher than the preset concurrent adjustment threshold, reduce the concurrency degree corresponding to the concurrent operator; Determine whether the concurrency level meets the preset requirements; If not, then repeat the step of obtaining the concurrent resource usage information and concurrent resource threshold information corresponding to the concurrent operator in the target database.

9. A computer program product comprising a computer program that, when executed by a processor, implements the steps of the method for optimizing the execution of database operation statements according to any one of claims 1 to 8.

10. A computer device comprising a memory, a processor, and a machine-executable program stored in the memory and running on the processor, wherein the processor, when executing the machine-executable program, implements the steps of the method for optimizing the execution of database operation statements according to any one of claims 1 to 8.

Images