A sql optimization system and terminal based on intelligent diagnosis

Abstract

The present application belongs to the technical field of SQL optimization, and particularly relates to a SQL optimization system and terminal based on intelligent diagnosis. The present application takes abnormal operators and their associated paths as units, accurately finds hot nodes and bottleneck propagation links in SQL execution, avoids blind overall optimization, simultaneously considers actual and historical execution statistics, multiple resource consumption and row number indexes, and self-defined weights, ensures that optimization decisions take into account stability and efficiency, automatically generates multiple candidate rewriting schemes based on a rule base, and avoids the inefficiency and risks of manual one-by-one testing through modeling or sandbox estimation, landing execution, weight evaluation and historical comparison in an isolated environment, reduces the risk of online environment optimization failure, and ensures the stability of the production system.

Description

Technical Field

[0001] This invention belongs to the field of SQL optimization technology, specifically relating to an SQL optimization system and terminal based on intelligent diagnosis. Background Technology

[0002] Modern information technology relies heavily on database systems to store, manage, and retrieve massive amounts of data. Structured Query Language (SQL), as the core interface for interacting with databases, directly determines application performance, user experience, and the utilization rate of underlying hardware resources. With the evolution of the big data era, the complexity of business processes handled by database systems is increasing exponentially, and SQL query performance has become a key factor affecting the response speed of core business operations, resource costs, and service stability.

[0003] With the widespread application of big data and online transaction processing (OLTP) and online analytical processing (OLAP) systems, SQL performance optimization in relational database management systems (RDBMS) has become a crucial aspect of ensuring business response speed and resource utilization efficiency. Traditional SQL optimization methods primarily rely on the static cost-based optimizer (CBO) model and rule-based query rewriting technology. The CBO generates an execution plan by analyzing table statistics, index information, and cost models; rule rewriting, on the other hand, relies on expert experience to provide hints or index suggestions for specific query patterns.

[0004] However, with the explosive growth of data scale, the high diversification of business models, and the frequent changes in system operating environment, existing optimization methods rely heavily on human experience and static rules, which are prone to defects such as low diagnostic efficiency, poor adaptability, and high actual resource consumption. Especially in scenarios with massive data and high concurrency, traditional optimizers are prone to generating suboptimal execution plans due to the lag in statistical information or insufficient rule coverage, resulting in soaring query latency and waste of system resources. Summary of the Invention

[0005] The purpose of this invention is to provide an SQL optimization system and terminal based on intelligent diagnosis, which can automatically extract performance bottleneck operators, generate multi-dimensional optimization schemes, predict and verify optimization benefits, and achieve continuous adaptive SQL optimization through dynamic adjustment and closed-loop iteration based on real-time execution diagnosis and historical performance data.

[0006] The specific technical solution adopted by this invention is as follows:

[0007] A SQL optimization system based on intelligent diagnosis includes an actual execution module, an operator module, a rewrite scheme module, a performance prediction module, a performance comparison module, an optimization effect module, and an optimization adjustment module.

[0008] The actual execution module is used to obtain the target SQL statement and its associated execution plan data, and to obtain the actual execution cost of each operator node based on the associated execution plan data;

[0009] The exception operator extraction module obtains the exception operator node and its associated path based on the actual execution cost of each operator node.

[0010] The rewrite scheme module is used to obtain the corresponding optimization rule set based on the abnormal operator node and the associated path, and generate a candidate SQL rewrite scheme set.

[0011] The performance prediction module is used to obtain the performance benefit prediction for each candidate SQL rewriting scheme in the candidate SQL rewriting scheme set.

[0012] The performance comparison module is used to obtain the target candidate SQL rewriting schemes that meet the preset requirements based on multiple performance benefit predictions.

[0013] The optimization effect module is used to execute the target candidate SQL rewriting scheme in a sandbox and obtain the actual optimization effect of the abnormal operator node and related path;

[0014] The optimization and adjustment module dynamically adjusts the optimization rules based on actual optimization results and historical optimization data.

[0015] In a preferred embodiment, the actual execution module includes an SQL extraction unit, an operator unit, a historical execution unit, an execution weight unit, an execution reference unit, a cost table unit, and an actual execution unit.

[0016] The SQL extraction unit is used to obtain the target SQL statement and its associated execution plan data. This associated execution plan data includes the execution plan graph, actual execution time, and actual resource consumption.

[0017] Operator unit, which obtains multiple operator nodes of the target SQL statement based on the execution plan graph;

[0018] The historical execution unit is used to obtain historical execution statistics for each operator node, including historical execution time and historical resource consumption.

[0019] The execution weight unit is used to obtain the execution statistical weight, which includes the actual weight and the historical weight.

[0020] The execution reference unit obtains the actual execution reference value based on the actual execution time, actual resource consumption, historical execution statistics, and execution statistics weights.

[0021] The cost table unit is used to obtain the cost table, which includes multiple actual execution reference value ranges and the actual execution cost corresponding to each actual execution reference value.

[0022] The actual execution unit obtains the corresponding actual execution cost from the cost table based on the actual execution reference value range corresponding to the actual execution reference value.

[0023] In a preferred embodiment, the exception operator extraction module includes an execution threshold unit, an execution judgment unit, a plan tree extraction unit, and a path generation unit;

[0024] The execution threshold unit is used to obtain the execution cost threshold.

[0025] The execution judgment unit is used to determine whether the actual execution cost of each operator node exceeds the execution cost threshold.

[0026] If the actual execution cost exceeds the execution cost threshold, it is determined to be an abnormal operator node;

[0027] If the actual execution cost does not exceed the execution cost threshold, it is determined to be a normal operator node;

[0028] The plan tree extraction unit extracts the execution plan tree structure from the execution plan graph based on associated execution plan data.

[0029] The path generation unit is used to take the exception operator node as the starting point, propagate the exception status bidirectionally to the parent and child nodes according to the execution plan tree structure, and generate an exception association path covering all related nodes.

[0030] In a preferred embodiment, the rewrite scheme module includes a performance index unit, an optimization weight unit, an optimization unit, an optimization table unit, an optimization rule unit, and a scheme set unit.

[0031] The performance metrics unit is used to obtain the performance metrics data corresponding to the abnormal operator node. The performance metrics data includes the estimated execution time, estimated resource consumption, actual execution time, actual resource consumption, number of returned rows, and number of processed rows.

[0032] The optimization weight unit is used to obtain the corresponding path coefficients based on the associated paths and to obtain the performance indicator weights, which include time weight, consumption weight and row count weight.

[0033] The optimization unit is used to obtain the corresponding optimization value based on performance index data and path weights.

[0034] The optimization table unit is used to obtain the optimization table, which includes multiple optimization value ranges and the optimization rule set corresponding to each optimization value range;

[0035] The optimization rule unit retrieves the corresponding optimization rule set from the optimization table based on the optimization value range corresponding to the optimization value.

[0036] The scheme set unit is used to generate multiple candidate SQL rewriting schemes based on the optimization rule set, and to form a candidate SQL rewriting scheme set from the multiple candidate SQL rewriting schemes.

[0037] In a preferred embodiment, the performance prediction module includes a baseline data unit, a candidate data unit, and a revenue prediction unit;

[0038] The baseline data unit is used to obtain the baseline execution time and baseline resource consumption of the target SQL statement;

[0039] Candidate data units are used to obtain the candidate execution time and candidate resource consumption for each candidate SQL rewriting scheme;

[0040] The revenue prediction unit is used to obtain performance revenue predictions based on the baseline execution time, baseline resource consumption, candidate execution time, and candidate resource consumption.

[0041] In a preferred embodiment, the performance comparison module includes a revenue prediction extraction unit, a performance ranking unit, and a candidate confirmation unit.

[0042] The revenue prediction extraction unit is used to extract the performance revenue prediction corresponding to each candidate SQL rewriting scheme;

[0043] The performance ranking unit is used to sort multiple performance benefit predictions in ascending order to obtain a performance ranking table.

[0044] The candidate confirmation unit is used to extract the candidate SQL rewriting scheme corresponding to the first performance benefit prediction from the performance ranking table and mark it as the target candidate SQL rewriting scheme.

[0045] In a preferred embodiment, the optimization effect module includes a sandbox execution unit, a sandbox weight unit, and an actual optimization unit;

[0046] The sandbox execution unit is used to execute the target candidate SQL rewriting scheme in the sandbox to obtain sandbox execution data. The sandbox execution data includes the CPU cycle decline rate of the abnormal operator node, the change of the waiting time of the associated path lock, and the amount released in the temporary table.

[0047] The sandbox weight unit is used to obtain the sandbox weight, which includes the CPU cycle weight of the abnormal operator node, the waiting time weight of the associated path lock, and the release weight of the temporary table.

[0048] The actual optimization unit is used to obtain the actual optimization effect based on the sandbox execution data and sandbox weights.

[0049] In a preferred embodiment, the optimization adjustment module includes an optimization adjustment unit, an adjustment value unit, an adjustment table unit, and an adjustment strategy unit;

[0050] The optimization and adjustment unit is used to obtain the baseline optimization effect and determine whether the actual optimization effect exceeds the baseline optimization effect.

[0051] If the actual optimization effect exceeds the baseline optimization effect, the optimization is deemed successful.

[0052] If the actual optimization effect does not exceed the baseline optimization effect, the optimization is deemed abnormal, and historical optimization data is obtained, which includes multiple historical optimization effects.

[0053] The adjustment value unit is used to obtain the adjustment value based on the actual optimization effect and multiple historical optimization effects;

[0054] The adjustment table unit is used to obtain the adjustment table, which includes multiple adjustment value ranges and the corresponding adjustment strategy for each adjustment value range;

[0055] The adjustment strategy unit is used to retrieve the corresponding adjustment strategy from the adjustment table based on the adjustment value range corresponding to the adjustment value, and adjust the optimization rules based on the corresponding adjustment strategy.

[0056] In a preferred embodiment, the optimization and adjustment module further includes an adjustment count unit and an adjustment judgment unit;

[0057] The adjustment count unit is used to obtain the number of adjustments made to the optimization rules;

[0058] An adjustment judgment unit is used to obtain the number of adjustment thresholds and determine whether the number of adjustments exceeds the adjustment threshold.

[0059] If the number of adjustments exceeds the adjustment threshold, the adjustment is deemed abnormal, an alarm message is issued, and the process returns to the actual execution module to re-run.

[0060] If the number of adjustments does not exceed the adjustment threshold, the optimization process continues.

[0061] And, a SQL optimization terminal based on intelligent diagnostics, comprising:

[0062] One or more processors;

[0063] A storage device on which one or more programs are stored;

[0064] When one or more programs are executed by one or more processors, the one or more processors enable an SQL optimization system based on intelligent diagnostics.

[0065] The technical effects achieved by this invention are as follows:

[0066] This invention uses exception operators and their associated paths as units to accurately identify hot spots and bottleneck propagation links in SQL execution, avoiding blind overall optimization. It also considers actual and historical execution statistics, various resource consumption and row count indicators, and custom weights to ensure that optimization decisions balance stability and efficiency. Based on a rule base, it automatically generates multiple candidate rewrite schemes and uses modeling or sandboxing for prediction to avoid the inefficiency and risk of manual testing. It is implemented in an isolated environment, with weight evaluation and historical comparison to reduce the risk of optimization failure in the online environment and ensure the stability of the production system. Attached Figure Description

[0067] Figure 1 This is a system module diagram provided by the present invention;

[0068] Figure 2 This is a schematic diagram of the process provided by the present invention;

[0069] Figure 3 This is a schematic diagram of the architecture of the actual execution module provided by the present invention;

[0070] Figure 4 This is a flowchart illustrating the exception operator extraction module provided by the present invention;

[0071] Figure 5 This is a schematic diagram of the architecture of the performance prediction module provided by the present invention. Detailed Implementation

[0072] To make the above-mentioned objects, features and advantages of the present invention more apparent and understandable, the specific embodiments of the present invention will be described in detail below with reference to the accompanying drawings.

[0073] Many specific details are set forth in the following description in order to provide a full understanding of the invention. However, the invention may also be practiced in other ways different from those described herein, and those skilled in the art can make similar extensions without departing from the spirit of the invention. Therefore, the invention is not limited to the specific embodiments disclosed below.

[0074] Secondly, the term "an embodiment" or "embodiment" as used herein refers to a specific feature, structure, or characteristic that may be included in at least one implementation of the present invention. The phrase "in a preferred embodiment" appearing in different places throughout this specification does not necessarily refer to the same embodiment, nor is it a single or selective embodiment that mutually excludes other embodiments.

[0075] Furthermore, the present invention will be described in detail with reference to the schematic diagrams. When describing the embodiments of the present invention in detail, the schematic diagrams are merely examples for ease of explanation and should not limit the scope of protection of the present invention.

[0076] Please see the appendix Figures 1 to 5As shown, an SQL optimization system based on intelligent diagnosis is provided, including an actual execution module, an operator module, a rewrite scheme module, a performance prediction module, a performance comparison module, an optimization effect module, and an optimization adjustment module.

[0077] The actual execution module is used to obtain the target SQL statement and its associated execution plan data, and to obtain the actual execution cost of each operator node based on the associated execution plan data;

[0078] The exception operator extraction module obtains the exception operator node and its associated path based on the actual execution cost of each operator node.

[0079] The rewrite scheme module is used to obtain the corresponding optimization rule set based on the abnormal operator node and the associated path, and generate a candidate SQL rewrite scheme set.

[0080] The performance prediction module is used to obtain the performance benefit prediction for each candidate SQL rewriting scheme in the candidate SQL rewriting scheme set.

[0081] The performance comparison module is used to obtain the target candidate SQL rewriting schemes that meet the preset requirements based on multiple performance benefit predictions.

[0082] The optimization effect module is used to execute the target candidate SQL rewriting scheme in a sandbox and obtain the actual optimization effect of the abnormal operator node and related path;

[0083] The optimization and adjustment module dynamically adjusts the optimization rules based on actual optimization results and historical optimization data.

[0084] The above describes the actual execution module, which extracts the target SQL statement to be optimized from the database and simultaneously obtains its latest execution plan graph (including the plan tree structure and node information). Based on the execution plan graph and actual running statistics (execution time, resource consumption), it obtains the actual execution cost of each operator node. The abnormal operator extraction module sets an execution cost threshold and identifies nodes whose actual execution cost exceeds the threshold as abnormal operators. In the tree structure of the execution plan, starting from the abnormal node, it propagates bidirectionally upwards (parent node) and downwards (child node) to extract the abnormal association path covering all related nodes, so as to fully reflect the performance bottleneck propagation chain. The solution module is rewritten to address each abnormal node and its associations. The system collects multi-dimensional performance metrics (estimated, actual execution time, estimated, actual resource consumption, number of rows returned / processed, etc.) and obtains optimization values ​​based on path coefficients and metric weights. It then searches for corresponding intervals in a predefined optimization table based on these optimization values ​​to obtain the appropriate optimization rule sets (such as index suggestions, Join order adjustments, SQLHints, etc.). These rule sets are applied to the original SQL to generate multiple candidate rewrite schemes, forming a candidate SQL rewrite scheme set. The performance prediction module collects the baseline execution time and resource consumption of the original SQL, as well as the expected execution statistics of each candidate scheme in the sandbox environment. Based on these two sets of data, it obtains performance benefit predictions. The performance comparison module compares the benefit predictions of all candidate schemes. The system predicts performance requirements (ranked from smallest to largest), generates a performance ranking table, selects the top-ranked solution as the target candidate rewrite solution, and optimizes the effect module. The target rewrite solution is executed in an isolated sandbox environment, and sandbox execution data (CPU cycle drop rate, lock wait time changes, temporary table release amount, etc.) is collected. Combined with pre-set sandbox weights (CPU, lock wait, temporary tables, etc.), the actual optimization effect is obtained to verify the feasibility and effectiveness of the rewrite solution. The optimization and adjustment module compares the actual optimization effect with historical optimization data. If the effect is not satisfactory, an adjustment process is triggered. Adjustment values ​​are obtained based on the effect differences, and the corresponding adjustment strategy (such as widening / tightening the threshold, introducing more...) is found in the adjustment table. (Rules or rollback to historically successful solutions) Optimization rules are continuously calibrated, and the system learns from historical performance to achieve adaptive optimization for different scenarios. Using abnormal operators and their associated paths as units, it accurately identifies hot nodes and bottleneck propagation links in SQL execution, avoiding blind overall optimization. It also considers actual and historical execution statistics, various resource consumption and row count indicators, and custom weights to ensure that optimization decisions balance stability and efficiency. Based on the rule base, it automatically generates multiple candidate rewrite solutions and uses modeling or sandboxing for prediction to avoid the inefficiency and risk of manual testing. It is implemented and executed in an isolated environment, with weight evaluation and historical comparison to reduce the risk of optimization failure in the online environment and ensure the stability of the production system.

[0085] In a preferred embodiment, the actual execution module includes an SQL extraction unit, an operator unit, a historical execution unit, an execution weight unit, an execution reference unit, a cost table unit, and an actual execution unit.

[0086] The SQL extraction unit is used to obtain the target SQL statement and its associated execution plan data. This associated execution plan data includes the execution plan graph, actual execution time, and actual resource consumption.

[0087] Operator unit, which obtains multiple operator nodes of the target SQL statement based on the execution plan graph;

[0088] The historical execution unit is used to obtain historical execution statistics for each operator node, including historical execution time and historical resource consumption.

[0089] The execution weight unit is used to obtain the execution statistical weight, which includes the actual weight and the historical weight.

[0090] The execution reference unit obtains the actual execution reference value based on the actual execution time, actual resource consumption, historical execution statistics, and execution statistics weights.

[0091] The cost table unit is used to obtain the cost table, which includes multiple actual execution reference value ranges and the actual execution cost corresponding to each actual execution reference value.

[0092] The actual execution unit obtains the corresponding actual execution cost from the cost table based on the actual execution reference value range corresponding to the actual execution reference value.

[0093] The SQL extraction unit extracts the target SQL statement to be optimized from the database session or logs, and simultaneously obtains the corresponding execution plan graph (including node topology, logical and physical operator information of each node), actual execution time, and resource consumption (CPU, memory, I / O, etc.). The operator unit parses the execution plan graph and extracts each operator node (such as Scan, Join, Sort, Aggregate, etc.). The historical execution unit extracts historical execution statistics data for operator nodes from monitoring and performance repositories, including historical execution time and historical resource consumption. The execution weight unit obtains and maintains two types of parameters: actual weight and historical weight, used to balance the impact of the current execution and historical execution on the reference value. The actual weight can be adjusted according to business time periods and SLA requirements, and the historical weight can be automatically updated according to statistical confidence. The execution reference unit weights and merges the current actual execution time, resources, and historical execution statistics according to their respective weights to calculate the actual execution reference value for each node. This reference value reflects both the current running status and the historical data, and can more objectively measure the node cost. The formula for calculating the actual execution reference value is as follows: In the formula, D represents the actual execution reference value, H1 represents the actual resource consumption, T1 represents the actual execution time, α represents the actual weight, H0 represents the historical resource consumption, T0 represents the historical execution time, and β represents the historical weight. The cost table unit predefines a cost table, dividing the execution reference value into multiple intervals (such as low, medium, and high intervals), and mapping each interval to a standardized actual execution cost score. The cost table can be customized according to different business scenarios, hardware configurations, or database types. The actual execution unit, based on the execution reference value of each node, finds its corresponding cost interval, reads the corresponding actual execution cost score, and outputs the node's actual execution cost for the entire SQL statement. Real-time statistics and historical statistics are integrated through weighting to avoid misjudgments based solely on single fluctuations or historical averages, providing a more robust node cost reference. By mapping the reference value to a unified score through the cost table, it facilitates horizontal comparisons across nodes and SQL statements, assisting the automation module in quickly focusing on high-cost nodes.

[0094] In a preferred embodiment, the exception operator extraction module includes an execution threshold unit, an execution judgment unit, a plan tree extraction unit, and a path generation unit;

[0095] The execution threshold unit is used to obtain the execution cost threshold.

[0096] The execution judgment unit is used to determine whether the actual execution cost of each operator node exceeds the execution cost threshold.

[0097] If the actual execution cost exceeds the execution cost threshold, it is determined to be an abnormal operator node;

[0098] If the actual execution cost does not exceed the execution cost threshold, it is determined to be a normal operator node;

[0099] The plan tree extraction unit extracts the execution plan tree structure from the execution plan graph based on associated execution plan data.

[0100] The path generation unit is used to take the exception operator node as the starting point, propagate the exception status bidirectionally to the parent and child nodes according to the execution plan tree structure, and generate an exception association path covering all related nodes.

[0101] The above-mentioned execution threshold unit obtains the execution cost threshold from system configuration or historical statistics (which can be a global threshold or customized according to operator type or business scenario); the execution judgment unit compares the actual execution cost of each operator node with the threshold. If the actual execution cost exceeds the execution cost threshold, the node is marked as an abnormal operator node; if the actual execution cost does not exceed the execution cost threshold, it is marked as a normal operator node; the plan tree extraction unit parses the associated execution plan data and reconstructs the tree structure of the execution plan (parent-child relationship of each node, execution order, etc.); and the path generation unit starts from each abnormal node and moves along the execution plan tree towards the parent node. (Tracing upstream operators affecting this node), child node direction (analyzing the chain effect of this node on downstream operators), bidirectional propagation of anomaly markers, collection of all nodes propagated through the markers to form a complete anomaly association path, only including nodes with costs exceeding the threshold in the anomaly category, avoiding unnecessary optimization of low-cost nodes, focusing on real performance hotspots, and through bidirectional propagation, capturing the chain effect of bottleneck nodes in the upstream and downstream of the execution plan, helping to understand how performance problems spread in the operator chain, the threshold can be adjusted as needed (e.g., relaxed during peak business periods, tightened during off-peak periods), or dynamically set in combination with the historical distribution of operator types to improve sensitivity and robustness.

[0102] In a preferred embodiment, the rewrite scheme module includes a performance index unit, an optimization weight unit, an optimization unit, an optimization table unit, an optimization rule unit, and a scheme set unit.

[0103] The performance metrics unit is used to obtain the performance metrics data corresponding to the abnormal operator node. The performance metrics data includes the estimated execution time, estimated resource consumption, actual execution time, actual resource consumption, number of returned rows, and number of processed rows.

[0104] The optimization weight unit is used to obtain the corresponding path coefficients based on the associated paths and to obtain the performance indicator weights, which include time weight, consumption weight and row count weight.

[0105] The optimization unit is used to obtain the corresponding optimization value based on performance index data and path weights.

[0106] The optimization table unit is used to obtain the optimization table, which includes multiple optimization value ranges and the optimization rule set corresponding to each optimization value range;

[0107] The optimization rule unit retrieves the corresponding optimization rule set from the optimization table based on the optimization value range corresponding to the optimization value.

[0108] The scheme set unit is used to generate multiple candidate SQL rewriting schemes based on the optimization rule set, and to form a candidate SQL rewriting scheme set from the multiple candidate SQL rewriting schemes.

[0109] The performance metrics unit collects multi-dimensional performance metrics for each abnormal operator node identified from the abnormal association path, including estimated execution time and estimated resource consumption, actual execution time and actual resource consumption (real-time running statistics), and the number of rows returned and processed (quantified data flow). The optimization weight unit matches the corresponding path coefficient from the database based on the node's position in the association path. Based on business priorities or SLA requirements, different weights are assigned to time, resource consumption, and number of rows to ensure the optimization direction aligns with the strategy. The optimization unit multiplies the performance metric value of each abnormal node by the corresponding weight and combines it with the path coefficient to calculate the optimization value, which is a comprehensive score measuring the node's optimization potential. The formula for calculating the optimization value is as follows: In the formula, Y represents the optimized value, d represents the path coefficient, T1 represents the actual execution time, T2 represents the estimated execution time, a represents the time weight, H1 represents the actual resource consumption, H2 represents the estimated resource consumption, S1 represents the number of returned rows, S2 represents the number of processed rows, and c represents the row number weight. The optimization table unit maintains a predefined optimization table, dividing the optimized value into multiple intervals, and pre-setting an optimal set of optimization rules for each interval (such as index adjustment, Join method conversion, SQLHint addition, etc.). The optimization rule unit, based on the calculated optimized value, locates the interval it belongs to, extracts the rule set corresponding to the interval from the optimization table, and each rule set has different focuses for different scenarios. It can accurately identify bottleneck types and solution sets, automatically applying rule sets to the original SQL to generate multiple candidate rewrite solutions (such as different indexes, hint combinations, or subquery rewriting), and summarizing them into a complete candidate SQL rewrite solution set, ready for subsequent performance prediction and evaluation processes. At the same time, it integrates estimated / actual execution data with the number of returned / processed rows, and through a configurable weight system, achieves optimization decisions that are more in line with business goals. It encapsulates complex optimization experience rules in the optimization table, automatically matching the most suitable rule set based on the quantified optimization value, reducing the workload of manual rule selection, and generating multiple high-quality candidate solutions at once, which can cover multiple possible optimization paths and avoid the risk of single strategy failure.

[0110] In a preferred embodiment, the performance prediction module includes a baseline data unit, a candidate data unit, and a revenue prediction unit.

[0111] The baseline data unit is used to obtain the baseline execution time and baseline resource consumption of the target SQL statement;

[0112] Candidate data units are used to obtain the candidate execution time and candidate resource consumption for each candidate SQL rewriting scheme;

[0113] The revenue prediction unit is used to obtain performance revenue predictions based on the baseline execution time, baseline resource consumption, candidate execution time, and candidate resource consumption.

[0114] The baseline data unit extracts the baseline execution time and baseline resource consumption of the original (unoptimized) target SQL from the actual execution module or monitoring system. This data reflects the true performance level of the SQL in the current production environment and serves as a reference for subsequent comparisons. The candidate data unit executes each candidate SQL generated by the rewrite scheme module in a sandbox or simulation environment, collecting the corresponding candidate execution time and candidate resource consumption. The benefit prediction unit compares the execution metrics of the baseline and candidates, calculating multi-dimensional performance benefit predictions. The formula for calculating performance benefit prediction is as follows: In the formula, C represents the performance benefit prediction, H3 represents the candidate resource consumption, T3 represents the candidate execution time, H4 represents the baseline resource consumption, and T4 represents the baseline execution time. Before actual implementation, the potential benefits of each candidate solution are quantitatively predicted to avoid the performance risks caused by blind deployment. The prediction results are used to quickly eliminate low-return or negative-return solutions, and only the most promising solutions are submitted to the next step of performance comparison and sandbox verification, which greatly reduces the evaluation investment.

[0115] In a preferred embodiment, the performance comparison module includes a revenue prediction extraction unit, a performance ranking unit, and a candidate confirmation unit.

[0116] The revenue prediction extraction unit is used to extract the performance revenue prediction corresponding to each candidate SQL rewriting scheme;

[0117] The performance ranking unit is used to sort multiple performance benefit predictions in ascending order to obtain a performance ranking table.

[0118] The candidate confirmation unit is used to extract the candidate SQL rewriting scheme corresponding to the first performance benefit prediction from the performance ranking table and mark it as the target candidate SQL rewriting scheme.

[0119] The above-mentioned revenue prediction extraction unit receives the output from the performance prediction module, reads the performance revenue prediction corresponding to each candidate SQL rewriting scheme, and maps the scheme identifier to its revenue score to prepare for sorting. The performance sorting unit sorts the revenue predictions of all candidate schemes according to preset requirements (either manually set or derived from historical data), i.e., from smallest to largest, and generates a performance sorting table. The candidate confirmation unit selects the first (best) candidate scheme from the sorted list as the target candidate SQL rewriting scheme, marks the scheme and submits it to the optimization effect module for sandbox verification. The automatic sorting eliminates suboptimal schemes, focuses only on the optimal revenue scheme, shortens the decision time, improves response efficiency, and the sorting table clearly shows the revenue differences and indicator comparisons of each scheme.

[0120] In a preferred embodiment, the optimization effect module includes a sandbox execution unit, a sandbox weight unit, and an actual optimization unit;

[0121] The sandbox execution unit is used to execute the target candidate SQL rewriting scheme in the sandbox to obtain sandbox execution data. The sandbox execution data includes the CPU cycle decline rate of the abnormal operator node, the change of the waiting time of the associated path lock, and the amount released in the temporary table.

[0122] The sandbox weight unit is used to obtain the sandbox weight, which includes the CPU cycle weight of the abnormal operator node, the waiting time weight of the associated path lock, and the release weight of the temporary table.

[0123] The actual optimization unit is used to obtain the actual optimization effect based on the sandbox execution data and sandbox weights.

[0124] The sandbox execution unit submits the target candidate SQL rewriting scheme to a sandbox testing environment isolated from the production environment, collecting execution metrics for key operators and paths, including CPU cycle decrease rate (relative change in CPU cycle consumption of abnormal operator nodes before and after rewriting), changes in lock wait time for associated paths (increase or decrease in wait time for each node on the associated path due to lock contention), and temporary table release amount (temporary storage amount generated and released during execution (such as sorting or hash tables)). The sandbox weight unit obtains sandbox weights from the configuration or historical verification model and assigns different importance to the above three types of sandbox execution data: CPU cycle weight (measuring the importance of improving computational efficiency), lock wait weight (measuring the importance of improving concurrency performance), and temporary table release weight (measuring the importance of improving memory and disk pressure). The actual optimization unit multiplies the sandbox execution data by the corresponding sandbox weight, calculates the weighted score of each metric, and combines the weighted scores to output an actual optimization effect score, which serves as an evaluation of the rewriting scheme in real-world scenarios. The quantitative basis for the improvement brought about is calculated by the formula J = p·P + k·K + m·M, where J represents the actual optimization effect, p represents the CPU cycle weight of the abnormal operator node, P represents the CPU cycle decline rate of the abnormal operator node, k represents the weight of the associated path lock waiting time, K represents the change in the associated path lock waiting time, m represents the weight of the release within the temporary table, and M represents the amount of release within the temporary table. Testing is conducted in a sandbox completely isolated from the production environment to ensure that even if performance regressions or anomalies occur, the stability of online services will not be affected. It not only focuses on single indicators (such as execution time) but also comprehensively considers CPU consumption, lock contention, and temporary storage pressure to form a more comprehensive quality judgment of the optimization solution. The weighted average of various indicators yields a numerical score of the actual optimization effect, providing an objective and comparable basis for the final decision on whether to rewrite the solution online. Through sandbox verification and weight evaluation, not only is the possibility of performance improvement verified, but potential resource or concurrency risks are also detected, significantly reducing the risk of optimization failure.

[0125] In a preferred embodiment, the optimization adjustment module includes an optimization adjustment unit, an adjustment value unit, an adjustment table unit, and an adjustment strategy unit.

[0126] The optimization and adjustment unit is used to obtain the baseline optimization effect and determine whether the actual optimization effect exceeds the baseline optimization effect.

[0127] If the actual optimization effect exceeds the baseline optimization effect, the optimization is deemed successful.

[0128] If the actual optimization effect does not exceed the baseline optimization effect, the optimization is deemed abnormal, and historical optimization data is obtained, which includes multiple historical optimization effects.

[0129] The adjustment value unit is used to obtain the adjustment value based on the actual optimization effect and multiple historical optimization effects;

[0130] The adjustment table unit is used to obtain the adjustment table, which includes multiple adjustment value ranges and the corresponding adjustment strategy for each adjustment value range;

[0131] The adjustment strategy unit is used to retrieve the corresponding adjustment strategy from the adjustment table based on the adjustment value range corresponding to the adjustment value, and adjust the optimization rules based on the corresponding adjustment strategy.

[0132] The above-mentioned optimization and adjustment unit obtains the baseline optimization effect from the system preset or historical records (such as the optimization score, time / resource saving rate, etc. in the previous similar scenario), compares the actual optimization effect with the baseline, and if the actual optimization effect exceeds the baseline optimization effect, it is judged as qualified and the process ends. If the actual optimization effect does not exceed the baseline optimization effect, it is judged as an optimization anomaly, and the adjustment process is initiated. The unit then retrieves the historical optimization effect sequence for the SQL or similar scenarios. The adjustment value unit calculates the adjustment value based on the actual optimization effect and multiple sets of historical optimization effects. The formula for calculating the adjustment value is as follows: In the formula, W represents the adjustment value, J represents the actual optimization effect, and i represents the number of multiple historical optimization effects, i = 1, 2, 3…n, (j0). i Represented as the i-th historical optimization effect, the adjustment table unit maintains an adjustment table, dividing the adjustment value into multiple intervals (e.g., significant drop, moderate drop, slight drop). Each interval maps to a set of adjustment strategies (e.g., tightening thresholds, adding new rules, reverting to historically excellent solutions, increasing execution limit, etc.). The adjustment strategy unit extracts the corresponding adjustment strategy from the adjustment table based on the interval where the current adjustment value is located, and executes the strategy to correct or enhance the original optimization rules. For example, dynamically lowering the anomaly detection threshold to increase sensitivity, introducing more conservative or more aggressive index / Join rules, expanding / shrinking the range of weight coefficients, and introducing benchmark and historical comparisons to ensure that the optimization not only runs but also achieves or surpasses the existing best results. The strategy is automatically selected according to different degrees of drop, which can quickly correct small deviations or strongly intervene or revert in the case of severe drop, reducing blind repeated attempts. By using historical optimization effect data, successful experiences and lessons learned are mined, enabling the system to continuously learn and evolve.

[0133] In a preferred embodiment, the optimization adjustment module further includes an adjustment count unit and an adjustment judgment unit;

[0134] The adjustment count unit is used to obtain the number of adjustments made to the optimization rules;

[0135] An adjustment judgment unit is used to obtain the number of adjustment thresholds and determine whether the number of adjustments exceeds the adjustment threshold.

[0136] If the number of adjustments exceeds the adjustment threshold, the adjustment is deemed abnormal, an alarm message is issued, and the process returns to the actual execution module to re-run.

[0137] If the number of adjustments does not exceed the adjustment threshold, the optimization process continues.

[0138] The aforementioned adjustment count unit is responsible for recording the cumulative number of optimization rule adjustments in the current SQL optimization process. Each time the adjustment strategy unit modifies the optimization rule, the count is incremented. The adjustment judgment unit reads the adjustment threshold count (e.g., allowing a maximum of 3 rule adjustments) from the system configuration or business strategy, compares the current adjustment count with the threshold, and determines an adjustment anomaly if the adjustment count exceeds the threshold, triggering an alarm mechanism and returning control to the actual execution module for a full-process diagnostic re-examination. If the adjustment count does not exceed the threshold, optimization is deemed acceptable, and execution is allowed to continue. Setting a clear upper limit on the number of adjustments prevents the system from getting bogged down in endless rule modifications and evaluations during the fine-tuning phase, ensuring process convergence. An immediate alert is issued when the threshold is exceeded, and the system reverts to the diagnostic starting point, prompting the operations or development team to intervene. This prevents the automated process from spiraling out of control or becoming inefficient, limits excessive rule adjustments, reduces redundant overhead from sandbox execution and performance verification, saves computing resources and overall optimization time, and ensures that each iteration of the optimization loop is traceable, facilitating subsequent auditing and improvement. If multiple automatic adjustments fail to achieve the expected results, a timely rollback and manual intervention ensure that performance optimization in the production environment is both efficient and safe.

[0139] And, a SQL optimization terminal based on intelligent diagnostics, comprising:

[0140] One or more processors;

[0141] A storage device on which one or more programs are stored;

[0142] When one or more programs are executed by one or more processors, the one or more processors enable an SQL optimization system based on intelligent diagnostics.

[0143] The above description is merely a preferred embodiment of the present invention. It should be noted that those skilled in the art can make various improvements and modifications without departing from the principles of the present invention, and these improvements and modifications should also be considered within the scope of protection of the present invention. Structures, devices, and operating methods not specifically described or explained in this invention are implemented according to conventional methods in the art unless otherwise specified or limited.

Claims

1. A SQL optimization system based on intelligent diagnosis, characterized in that, It includes the actual execution module, operator module, rewrite scheme module, performance prediction module, performance comparison module, optimization effect module, and optimization adjustment module: The actual execution module is used to obtain the target SQL statement and its associated execution plan data, and to obtain the actual execution cost of each operator node based on the associated execution plan data; The exception operator extraction module obtains the exception operator node and its associated path based on the actual execution cost of each operator node. The rewrite scheme module is used to obtain the corresponding optimization rule set based on the abnormal operator node and the associated path, and generate a candidate SQL rewrite scheme set. The performance prediction module is used to obtain the performance benefit prediction for each candidate SQL rewriting scheme in the candidate SQL rewriting scheme set. The performance comparison module is used to obtain the target candidate SQL rewriting schemes that meet the preset requirements based on multiple performance benefit predictions. The optimization effect module is used to execute the target candidate SQL rewriting scheme in a sandbox and obtain the actual optimization effect of the abnormal operator node and related path; The optimization and adjustment module dynamically adjusts the optimization rules based on actual optimization results and historical optimization data. The actual execution module includes an SQL extraction unit, an operator unit, a historical execution unit, an execution weight unit, an execution reference unit, a cost table unit, and an actual execution unit. The SQL extraction unit is used to obtain the target SQL statement and its associated execution plan data. This associated execution plan data includes the execution plan graph, actual execution time, and actual resource consumption. Operator unit, which obtains multiple operator nodes of the target SQL statement based on the execution plan graph; The historical execution unit is used to obtain historical execution statistics for each operator node, including historical execution time and historical resource consumption. The execution weight unit is used to obtain the execution statistical weight, which includes the actual weight and the historical weight. The execution reference unit obtains the actual execution reference value based on the actual execution time, actual resource consumption, historical execution statistics, and execution statistics weights. The cost table unit is used to obtain the cost table, which includes multiple actual execution reference value ranges and the actual execution cost corresponding to each actual execution reference value. The actual execution unit obtains the corresponding actual execution cost from the cost table based on the actual execution reference value range corresponding to the actual execution reference value.

2. The SQL optimization system based on intelligent diagnosis according to claim 1, characterized in that, The exception operator extraction module includes an execution threshold unit, an execution judgment unit, a plan tree extraction unit, and a path generation unit; The execution threshold unit is used to obtain the execution cost threshold. The execution judgment unit is used to determine whether the actual execution cost of each operator node exceeds the execution cost threshold. If the actual execution cost exceeds the execution cost threshold, it is determined to be an abnormal operator node; If the actual execution cost does not exceed the execution cost threshold, it is determined to be a normal operator node; The plan tree extraction unit extracts the execution plan tree structure from the execution plan graph based on associated execution plan data. The path generation unit is used to take the exception operator node as the starting point, propagate the exception status bidirectionally to the parent and child nodes according to the execution plan tree structure, and generate an exception association path covering all related nodes.

3. The SQL optimization system based on intelligent diagnosis according to claim 1, characterized in that, The rewrite scheme module includes a performance indicator unit, an optimization weight unit, an optimization unit, an optimization table unit, an optimization rule unit, and a scheme set unit; The performance metrics unit is used to obtain the performance metrics data corresponding to the abnormal operator node. The performance metrics data includes the estimated execution time, estimated resource consumption, actual execution time, actual resource consumption, number of returned rows, and number of processed rows. The optimization weight unit is used to obtain the corresponding path coefficients based on the associated paths and to obtain the performance indicator weights, which include time weight, consumption weight and row count weight. The optimization unit is used to obtain the corresponding optimization value based on performance index data and path weights. The optimization table unit is used to obtain the optimization table, which includes multiple optimization value ranges and the optimization rule set corresponding to each optimization value range; The optimization rule unit retrieves the corresponding optimization rule set from the optimization table based on the optimization value range corresponding to the optimization value. The scheme set unit is used to generate multiple candidate SQL rewriting schemes based on the optimization rule set, and to form a candidate SQL rewriting scheme set from the multiple candidate SQL rewriting schemes.

4. The SQL optimization system based on intelligent diagnosis according to claim 1, characterized in that, The performance prediction module includes a baseline data unit, a candidate data unit, and a revenue prediction unit; The baseline data unit is used to obtain the baseline execution time and baseline resource consumption of the target SQL statement; Candidate data units are used to obtain the candidate execution time and candidate resource consumption for each candidate SQL rewriting scheme; The revenue prediction unit is used to obtain performance revenue predictions based on the baseline execution time, baseline resource consumption, candidate execution time, and candidate resource consumption.

5. The SQL optimization system based on intelligent diagnosis according to claim 1, characterized in that, The performance comparison module includes a revenue prediction extraction unit, a performance ranking unit, and a candidate confirmation unit; The revenue prediction extraction unit is used to extract the performance revenue prediction corresponding to each candidate SQL rewriting scheme; The performance ranking unit is used to sort multiple performance benefit predictions in ascending order to obtain a performance ranking table. The candidate confirmation unit is used to extract the candidate SQL rewriting scheme corresponding to the first performance benefit prediction from the performance ranking table and mark it as the target candidate SQL rewriting scheme.

6. The SQL optimization system based on intelligent diagnosis according to claim 1, characterized in that, The optimization effect module includes a sandbox execution unit, a sandbox weight unit, and an actual optimization unit; The sandbox execution unit is used to execute the target candidate SQL rewriting scheme in the sandbox to obtain sandbox execution data. The sandbox execution data includes the CPU cycle decline rate of the abnormal operator node, the change of the waiting time of the associated path lock, and the amount released in the temporary table. The sandbox weight unit is used to obtain the sandbox weight, which includes the CPU cycle weight of the abnormal operator node, the waiting time weight of the associated path lock, and the release weight of the temporary table. The actual optimization unit is used to obtain the actual optimization effect based on the sandbox execution data and sandbox weights.

7. The SQL optimization system based on intelligent diagnosis according to claim 1, characterized in that, The optimization and adjustment module includes an optimization and adjustment unit, an adjustment value unit, an adjustment table unit, and an adjustment strategy unit; The optimization and adjustment unit is used to obtain the baseline optimization effect and determine whether the actual optimization effect exceeds the baseline optimization effect. If the actual optimization effect exceeds the baseline optimization effect, the optimization is deemed successful. If the actual optimization effect does not exceed the baseline optimization effect, the optimization is deemed abnormal, and historical optimization data is obtained, which includes multiple historical optimization effects. The adjustment value unit is used to obtain the adjustment value based on the actual optimization effect and multiple historical optimization effects; The adjustment table unit is used to obtain the adjustment table, which includes multiple adjustment value ranges and the corresponding adjustment strategy for each adjustment value range; The adjustment strategy unit is used to retrieve the corresponding adjustment strategy from the adjustment table based on the adjustment value range corresponding to the adjustment value, and adjust the optimization rules based on the corresponding adjustment strategy.

8. The SQL optimization system based on intelligent diagnosis according to claim 1, characterized in that, The optimization and adjustment module also includes an adjustment count unit and an adjustment judgment unit; The adjustment count unit is used to obtain the number of adjustments made to the optimization rules; An adjustment judgment unit is used to obtain the number of adjustment thresholds and determine whether the number of adjustments exceeds the adjustment threshold. If the number of adjustments exceeds the adjustment threshold, the adjustment is deemed abnormal, an alarm message is issued, and the process returns to the actual execution module to re-run. If the number of adjustments does not exceed the adjustment threshold, the optimization process continues.

9. A SQL optimization terminal based on intelligent diagnosis, characterized in that, include: One or more processors; A storage device on which one or more programs are stored; When one or more programs are executed by one or more processors, the one or more processors implement the SQL optimization system based on intelligent diagnosis as described in any one of claims 1 to 8.

Images