SQL optimization performance evaluation method and system based on multi-index consistency analysis

Abstract

According to the SQL optimization performance evaluation method and system based on multi-index consistency analysis, an SQL optimization performance evaluation algorithm is adopted to comprehensively reflect the overall performance of SQL query, and a traditional SQL optimization evaluation method usually focuses on a single performance index; the existing evaluation method does not effectively consider the performance consistency of the optimized SQL query under different execution conditions and ignores the stability of the performance, and the evaluation method provided by the invention introduces the scoring deviation and fluctuation ratio, quantifies the stability of the optimized SQL query under different execution conditions, ensures that the scoring result not only reflects the performance improvement, but also ensures that the performance of the SQL query is improved. And the volatility and the consistency of the performance can be accurately evaluated. The final score is not only a simple summary of performance improvement, but also can provide practical guiding significance to help developers to further optimize SQL query.

Description

Technical Field

[0001] The invention relates to a SQL optimization performance evaluation method and system based on multi-index consistency analysis, and belongs to the field of database performance evaluation. Background Art

[0002] As the scale of database systems expands, optimization of SQL query performance can improve query performance and adapt to the increasingly complex and large database system requirements. Top SQL is specifically used to capture and record the SQL statements with the highest execution frequency and the most resource consumption. Database administrators can gain an in-depth understanding of the execution performance of SQL statements in the database and obtain key performance indicators including CPU usage, memory consumption, execution time, etc.

[0003] Performance comparison during SQL query optimization is the key to evaluating the optimization effect. This comparison process usually covers multiple levels, including rewriting query statements, adjusting indexes, and optimizing database configuration. At the same time, this process requires specific data, especially performance data from Top SQL, to prove whether the optimization has achieved the expected effect. This includes not only shortening query execution time, but also optimizing resource usage, such as reducing CPU, memory, and I / O consumption.

[0004] Existing performance optimization evaluation methods mainly rely on the analysis of individual indicators. Although they can provide certain guidance, due to the lack of a systematic scoring mechanism, it is often difficult to comprehensively evaluate the overall performance difference before and after optimization. The limitation of existing methods is that they usually only focus on one or a few performance indicators, while ignoring the relationship between the indicators. For example, an optimization may reduce the execution time of a query, but at the same time increase the memory usage or the number of I / O operations. In this case, judging the optimization effect based on the change of a single indicator alone may lead to inaccurate or even misleading conclusions.

[0005] In order to more comprehensively and objectively evaluate the effect of SQL query optimization, database administrators can fully understand the actual effect of SQL optimization through detailed analysis based on Top SQL. This not only provides solid data support for subsequent database maintenance and optimization, but also guides future database design and development strategies to ensure efficient and stable operation of the database system. BRIEF DESCRIPTION OF THE DRAWINGS

[0006] Figure 1 It is the system flow chart. Summary of the invention

[0007] In order to solve the technical problems existing in the prior art, the present invention proposes a SQL optimization performance evaluation method based on multi-index consistency analysis, which includes the following steps:

[0008] (1) Identify and manage the core metrics for SQL query performance evaluation;

[0009] (2) Define key system variables and provide data support for the entire evaluation process;

[0010] (3) Combine the scores of each key performance indicator and calculate the score difference and total variance;

[0011] (4) Evaluate the performance stability of optimized SQL statements by solving specific equations;

[0012] (5) Calculate the final score after SQL optimization and evaluate the stability after optimization by adjusting the volatility and score deviation.

[0013] Furthermore, the following method is used to calculate the score difference in step (3):

[0014] 3.1 Calculate the total score variance in μ is the initial performance score of the SQL statement before execution, μ j is the score of the SQL statement, j∈[1,m], m is the total number of SQL statements, φ j is the scoring error of other SQL statements, s j The actual performance changes before and after SQL statement optimization.

[0015] 3.2 Calculating the total score difference

[0016] Furthermore, in step (4), the performance stability of the optimized SQL statement is evaluated by solving a specific equation, wherein the specific equation is: f(A) = 0, solve for the value of A and get the new volatility σ′ = e A / 2 ; Calculate new scoring bias

[0017] Furthermore, in step (5), the final score after SQL optimization is calculated by integrating the scores, score differences Δ, volatility σ′ and score deviation φ′ of each key indicator.

[0018] Based on the above method, the present invention further proposes a SQL optimization performance evaluation system based on multi-index consistency analysis, the system comprising:

[0019] (1) Core indicator management module: This module is responsible for determining and managing the core indicators of SQL query performance evaluation;

[0020] (2) Evaluation basic module: This module defines the key variables of the system and provides data support for the entire evaluation process;

[0021] (3) Score difference calculation module: This module combines the scores of each key performance indicator to calculate the score difference and total variance;

[0022] (4) Volatility and stability analysis module: This module evaluates the performance stability of optimized SQL statements by solving specific equations;

[0023] (5) Final score calculation module: This module calculates the final score after SQL optimization and evaluates the stability after optimization by adjusting the volatility and score deviation.

[0024] Furthermore, the score difference calculation module adopts the following method:

[0025] 3.1 Calculate the total score variance in μ is the initial performance score of the SQL statement before execution, μ j is the score of the SQL statement, j∈[1,m], m is the total number of SQL statements, φ j is the scoring error of other SQL statements, s j The actual performance changes before and after SQL statement optimization.

[0026] 3.2 Calculating the total score difference

[0027] Furthermore, the volatility and stability analysis module evaluates the performance stability of the optimized SQL statement by solving a specific equation, where: f(A) = 0, solve for the value of A and get the new volatility σ′ = e A / 2 ; Calculate new scoring bias

[0028] Furthermore, in the final score calculation module, the final score after SQL optimization is calculated by integrating the scores of each key indicator, the score difference Δ, the volatility σ′ and the score deviation φ′

[0029] The present invention adopts SQL optimization performance evaluation algorithm to comprehensively reflect the overall performance of SQL queries. Traditional SQL optimization evaluation methods usually focus on a single performance indicator. Existing evaluation methods do not effectively consider the performance consistency of optimized SQL queries under different execution conditions and ignore the stability of performance. The present invention introduces scoring deviation and volatility to quantify the stability of SQL queries under different execution conditions after optimization, ensuring that the scoring results not only reflect performance improvement, but also accurately evaluate the volatility and consistency of performance. The final score is not only a simple summary of performance improvement, but also provides practical guidance to help developers further optimize SQL queries. DETAILED DESCRIPTION

[0030] Example 1

[0031] In order to more comprehensively and objectively evaluate the effect of SQL query optimization, this application will integrate multiple key performance indicators and quantify the changes in overall performance before and after optimization through a unified scoring standard. The system flow chart design is shown in Figure 1 .Depend on Figure 1 It can be seen that:

[0032] 1. Core indicator management module: determine the core indicators of greatest concern;

[0033] When evaluating SQL optimization performance, first determine the most important indicators, including but not limited to:

[0034] Execution time: the time it takes for a SQL statement to be executed from the beginning to the end;

[0035] CPU usage: CPU resources used when executing SQL statements;

[0036] Memory consumption: memory resources consumed during the execution of SQL statements;

[0037] I / O operations: disk read and write operations involved in SQL statement execution;

[0038] Query result accuracy: Check whether the query results before and after optimization are consistent to ensure that the optimization does not affect the correctness of the results.

[0039] 2. Evaluation basic module: define key system variables

[0040] In the SQL optimization evaluation system, in order to achieve a comprehensive and accurate evaluation of the SQL statement execution performance, a scoring system that comprehensively considers multiple key performance indicators is constructed to measure the overall performance of SQL statements before and after optimization. This scoring system can quantitatively analyze the overall performance of SQL statements before and after optimization, and is implemented through the following system variables:

[0041] The following are the core variable definitions in the scoring system:

[0042] Basic score μ: The initial performance score of the SQL statement before execution. As a baseline value before optimization, the basic score is obtained by evaluating and quantifying the key performance indicators of the unoptimized SQL query in actual operation.

[0043] Scoring error φ: measures the volatility of SQL statement execution performance and reflects the consistency of query performance under different conditions.

[0044] Volatility σ: Volatility, which evaluates the volatility of the score and is used to adjust the sensitivity of the scoring system to changes

[0045] Actual score after optimization: The actual score based on each key performance indicator after SQL optimization, reflecting the overall performance after optimization.

[0046] Through these basic variables, this system can quantitatively analyze the overall performance of SQL queries before and after optimization.

[0047] 3. Rating difference calculation module: The system will combine the scores of each key performance indicator to calculate the rating gap and total variance

[0048] In order to achieve accurate quantitative analysis of actual performance changes before and after SQL query optimization, this application will comprehensively consider multiple key indicators. This application proposes to calculate the score difference and variance, with the aim of flexibly responding to the volatility of execution performance of different SQL statements, helping the system to more accurately evaluate the stability of the optimization, and ensuring that the performance improvement after optimization is not only reflected in a single indicator, but also remains consistent overall. The total score variance v will be calculated by combining the scores of each key indicator, where Combining the index difference and deviation in exponential form can more carefully reflect the nonlinear relationship between different indicators and quantify the performance changes more precisely. The formula is as follows:

[0049]

[0050] in μ is the initial performance score of the SQL statement before execution, μ j is the score of other SQL statements, j∈(1,m), the total number of SQL statements is m, φ j is the scoring error of other SQL statements, s j The actual performance changes before and after SQL statement optimization.

[0051] By combining the scores of each key indicator, the system can calculate the total score difference and variance, thereby accurately evaluating the performance changes after SQL query optimization. This evaluation method not only focuses on the improvement of a single performance indicator, but also considers the stability of the overall performance, making the optimization effect more practical. On the other hand, the interaction between different indicators j and k is evaluated, their impact is evaluated, and the total score difference is calculated. The score difference formula is as follows:

[0052]

[0053] 4. Volatility and stability analysis module

[0054] Performance stability is not only related to the effect of a single execution, but also to the consistency of SQL query performance in different environments. To this end, the system introduces a volatility calculation method to evaluate the performance stability of optimized SQL statements by solving specific equations. In order to measure the score volatility of optimized SQL statements under different execution conditions, it can be calculated in the following way, selecting a small constant τ to limit the score fluctuation. Solve the following equation:

[0055]

[0056] Solve for the value of A that makes f(A) = 0 and get the new volatility σ′, σ′ = e A / 2 , based on the changes in the performance indicator over multiple executions, a new volatility is calculated.

[0057] The new score deviation is calculated using the new volatility σ′, which reflects the score volatility of the optimized SQL statement under different execution conditions. If the SQL statement performs stably under different conditions, the volatility σ′ should be lower and the new score deviation φ′ will also decrease accordingly, indicating that the system's performance evaluation is more stable and reliable.

[0058]

[0059] This adjustment mechanism can enhance the system's adaptability to different SQL query optimization effects, and the system can dynamically adjust the score based on the performance changes after optimization.

[0060] 5. Final score calculation module

[0061] In the SQL optimization performance scoring system, the calculation of the final score is the core of the entire evaluation process. The final score after SQL optimization is calculated by integrating the scores of various key indicators, the score difference Δ, the volatility σ′ and the score deviation φ′. This score measures the degree of improvement in SQL query performance and also evaluates the stability after optimization by adjusting the volatility and score deviation. This ensures that the score not only reflects the performance improvement, but also takes into account the consistency of the system's performance under different execution conditions. The calculation formula is as follows:

[0062]

[0063] Example 2

[0064] 1. Core indicator management module

[0065] The following five core indicators will be focused on:

[0066] Execution time: 200 milliseconds before optimization and 150 milliseconds after optimization.

[0067] CPU usage: 30% before optimization, 25% after optimization.

[0068] Memory consumption: 50MB before optimization, 40MB after optimization.

[0069] I / O operations: 1000 reads before optimization and 800 reads after optimization.

[0070] · Query result accuracy: The results before and after optimization are consistent.

[0071] 2. Evaluation basic module

[0072] Define the following key variables:

[0073] Basic score μ: Assume that the basic score of the SQL statement before optimization is μ=1500.

[0074] Scoring error φ: The initial scoring error is φ=200.

[0075] Volatility σ: The initial volatility is σ = 0.06.

[0076] Actual score after optimization: will be calculated based on the improvement of core indicators.

[0077] 3. Rating difference calculation module

[0078] Assume that the scores of other SQL statements in the system are j =1450,φ j =180, the optimized scores of other SQL statements j =1600, that is

[0079]

[0080] The final variance is The score difference is

[0081] 4. Volatility and stability analysis module

[0082] In order to evaluate the stability after optimization, the new volatility σ′ is calculated, assuming τ 2 =1.0, x=0.05, and finally σ′=e A / 2 ≈e 0.5·0.05 =1.025,

[0083] 5. Final score calculation module

[0084] Finally, the new basic score is calculated as μ′:

[0085]

[0086] The final score is about 1681, reflecting the performance improvement. This score also takes into account stability under different execution environments.

[0087] Through the specific demonstration of the above embodiments, in the SQL optimization performance evaluation algorithm proposed in this application, compared with the existing methods, the traditional SQL optimization evaluation method usually only focuses on a single performance indicator, such as execution time or resource consumption, and such a method cannot fully reflect the overall performance of SQL queries. This algorithm comprehensively quantifies the performance of SQL queries by combining multiple key performance indicators to ensure accurate analysis of the actual changes before and after optimization. The score not only measures the degree of improvement in SQL query performance, but also evaluates the stability and consistency after optimization by adjusting volatility and scoring deviation. This ensures that the score not only reflects the performance improvement, but also takes into account the consistency of the system's performance under different execution conditions.

[0088] Compared with traditional methods, this algorithm combines multiple key performance indicators and dynamic adjustment mechanisms to achieve a comprehensive evaluation of SQL query performance. This application not only focuses on the improvement of a single indicator, but also emphasizes the stability and consistency of overall performance. The final score can provide developers with more valuable optimization guidance and help them achieve better performance improvement in complex database environments.

[0089] The units, devices or modules described in the above embodiments can be implemented by computer chips or entities, or by products with certain functions. For the convenience of description, the above devices are described separately by functions divided into various modules. Of course, when implementing this application, the functions of each module can be implemented in the same or more software and / or hardware, or the modules that implement the same function can be implemented by a combination of multiple sub-modules or sub-units. The device embodiments described above are only schematic. For example, the division of the units is only a logical function division. There may be other division methods in actual implementation, such as multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the coupling or direct coupling or communication connection between each other shown or discussed can be through some interfaces, indirect coupling or communication connection of devices or units, which can be electrical, mechanical or other forms.

[0090] Those skilled in the art also know that, in addition to implementing the controller in a purely computer-readable program code, the controller can be made to implement the same function in the form of logic gates, switches, application-specific integrated circuits, programmable logic controllers, and embedded microcontrollers by logically programming the method steps. Therefore, such a controller can be considered as a hardware component, and the devices for implementing various functions included therein can also be considered as structures within the hardware component. Or even, the devices for implementing various functions can be considered as both software modules for implementing the method and structures within the hardware component.

[0091] The present application may be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, classes, etc. that perform specific tasks or implement specific abstract data types. The present application may also be practiced in distributed computing environments where tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules may be located in local and remote computer storage media, including storage devices.

[0092] It can be seen from the description of the above implementation methods that those skilled in the art can clearly understand that the present application can be implemented by means of software plus a necessary general hardware platform. Based on such an understanding, the technical solution of the present application can be essentially or partly contributed to the prior art in the form of a software product, which can be stored in a storage medium such as ROM / RAM, a magnetic disk, an optical disk, etc., and includes several instructions for enabling a computer device (which can be a personal computer, a mobile terminal, a server, or a network device, etc.) to execute the methods described in the various embodiments of the present application or certain parts of the embodiments.

[0093] The various embodiments in this specification are described in a progressive manner, and the same or similar parts between the various embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. The present application can be used in many general or special computer system environments or configurations. For example: personal computers, server computers, handheld devices or portable devices, tablet devices, multi-processor systems, microprocessor-based systems, set-top boxes, programmable electronic devices, network PCs, minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, etc.

[0094] The specific embodiments described above further illustrate the purpose, technical solutions and beneficial effects of the present application. It should be understood that the above description is only a specific embodiment of the present application and is not intended to limit the scope of protection of the present application. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present application should be included in the scope of protection of the present application.

Claims

1. A SQL optimization performance evaluation method based on multi-index consistency analysis, characterized by: The method comprises the following steps: (1) Identify and manage the core metrics for SQL query performance evaluation; (2) Define key system variables and provide data support for the entire evaluation process; (3) Combine the scores of each key performance indicator and calculate the score difference and total variance; (4) Evaluate the performance stability of optimized SQL statements by solving specific equations; (5) Calculate the final score after SQL optimization and evaluate the stability after optimization by adjusting the volatility and score deviation.

2. The SQL optimization performance evaluation method based on multi-index consistency analysis according to claim 1, characterized in that: The following method is used to calculate the score difference in step (3): 3.1 Calculate the total score variance in μ is the initial performance score of the SQL statement before execution, μ j is the score of the SQL statement, j∈[1,m], m is the total number of SQL statements, φ j is the scoring error of other SQL statements, s j The actual performance changes before and after SQL statement optimization. 3.2 Calculating the total score difference 3. The SQL optimization performance evaluation method based on multi-index consistency analysis according to claim 1, characterized in that: In step (4), the performance stability of the optimized SQL statement is evaluated by solving a specific equation, where: f(A) = 0, solve for the value of A and get the new volatility σ′ = e A / 2 ; Calculate new scoring bias 4. The SQL optimization performance evaluation method based on multi-index consistency analysis according to claim 1, characterized in that: In step (5), the final score after SQL optimization is calculated by integrating the scores of each key indicator, the score difference Δ, the volatility σ′ and the score deviation φ′ 5. A SQL optimization performance evaluation system based on multi-index consistency analysis, characterized by: The system includes: (1) Core indicator management module: This module is responsible for determining and managing the core indicators of SQL query performance evaluation; (2) Evaluation basic module: This module defines the key variables of the system and provides data support for the entire evaluation process; (3) Score difference calculation module: This module combines the scores of each key performance indicator to calculate the score difference and total variance; (4) Volatility and stability analysis module: This module evaluates the performance stability of SQL statements after optimization by solving specific equations; (5) Final score calculation module: This module calculates the final score after SQL optimization and evaluates the stability after optimization by adjusting the volatility and score deviation.

6. The SQL optimization performance evaluation system based on multi-index consistency analysis according to claim 5, characterized in that: The score difference calculation module uses the following method: 3.1 Calculate the total score variance in μ is the initial performance score of the SQL statement before execution, μ j is the score of the SQL statement, j∈[1,m], m is the total number of SQL statements, φ j is the scoring error of other SQL statements, s j The actual performance changes before and after SQL statement optimization. 3.2 Calculating the total score difference 7. The SQL optimization performance evaluation system based on multi-index consistency analysis according to claim 5, characterized in that: The volatility and stability analysis module evaluates the performance stability of SQL statements after optimization by solving a specific equation. The specific equation is f(A) = 0, solve for the value of A and get the new volatility σ′ = e A / 2 ; Calculate new scoring bias 8. The SQL optimization performance evaluation system based on multi-index consistency analysis according to claim 5, characterized in that: In the final score calculation module, the final score after SQL optimization is calculated by integrating the scores of each key indicator, the score difference Δ, the volatility σ′ and the score deviation φ′

Images