A key SQL intelligent identification method based on multi-dimensional evaluation and dynamic scene

Abstract

The application discloses a kind of key SQL intelligent identification method based on multidimensional evaluation and dynamic scene, belong to database intelligent operation and maintenance technical field.The method includes: collecting SQL running data and carrying out normalization processing, constructs the SQL portrait feature vector containing multidimensional characteristics;Identify current dynamic business scenario, generate scene factor;For the SQL of insufficient historical data, adopt the same kind SQL group baseline to carry out cold start evaluation;According to scene factor, dynamically adjust the weight of each scoring dimension, multidimensional comprehensive score is carried out to the SQL portrait feature vector, three-layer progressive detection architecture identifies key SQL;According to the identification result, the risk level, risk reason, evidence chain and disposal priority of key SQL are output, and user feedback is recorded to update scene weight and baseline.The application can adapt to multiple dynamic scenes, reduce false alarm and missed alarm caused by fixed threshold and single index identification, improve the accuracy and interpretability of key SQL identification in complex business system.

Description

Technical Field

[0001] This invention relates to the field of intelligent database operation and maintenance technology, and more specifically to a method for intelligent identification of key SQL based on multi-dimensional evaluation and dynamic scenarios. Background Technology

[0002] In existing large-scale enterprise information systems, databases typically need to simultaneously handle multiple business loads, including high-concurrency online transactions, batch statistics, report queries, API services, and data synchronization. To ensure stable system operation, operations and maintenance personnel need to identify and monitor SQL performance. Current mainstream SQL performance identification methods mainly rely on slow SQL log collection, fixed threshold alerts, and filtering Top SQL queries based on execution time or resource consumption, such as CPU time, logical reads, and sorting. These methods are simple to implement, have controllable resource overhead, and can identify some obviously abnormal SQL statements, thus they are widely used in production environments. However, as business scenarios become increasingly complex and database environments become more heterogeneous, these traditional methods are gradually revealing their limitations in applicability.

[0003] Existing technical solutions have the following significant drawbacks: First, single-metric identification is prone to distortion. Sorting based solely on a single dimension such as execution time, CPU time, or logical reads is easily affected by high-frequency, lightweight SQL queries, long batch SQL queries, or occasional slow SQL queries, making it difficult to accurately pinpoint the key SQL queries that are truly causing system bottlenecks. Second, static thresholds cannot adapt to dynamic business scenarios. The reasonable performance of the same SQL query varies significantly during peak and off-peak hours of online transactions, end-of-day batch processing, monthly statistics, and data synchronization windows. Fixed thresholds inevitably lead to false positives or false negatives. Third, SQL identifier aggregation can mask parameter differences. When using bind variables or normalized SQL template aggregation, the same template may produce completely different execution plans and resource consumption under different parameters and data distributions. Simply taking an average will mask a few high-risk execution cases. Fourth, there is a lack of business impact dimensions. Existing tools mainly judge whether SQL queries are abnormal based on internal database metrics, making it difficult to combine information such as interface SLAs, transaction success rates, call chains, and business levels to determine which SQL queries deserve priority processing. Fifth, metrics are inconsistent across database environments. Databases such as Oracle, MySQL, and PostgreSQL differ significantly in performance views, wait events, execution plan fields, and sampling mechanisms, making the migration of traditional identification rules costly. Sixth, there is insufficient capability to identify cold starts and new business scenarios. Newly launched businesses or new SQL queries lack historical baselines, making methods relying on comparisons with historical periods ineffective in a timely manner.

[0004] Therefore, providing a key SQL intelligent recognition method based on multi-dimensional evaluation and dynamic scenarios is a problem that urgently needs to be solved by those skilled in the art. Summary of the Invention

[0005] To solve the above problems, the present invention adopts the following technical solution:

[0006] This invention provides a method for intelligent identification of key SQL statements based on multi-dimensional evaluation and dynamic scenarios, comprising the following steps: S1: Collect SQL execution data and perform normalization processing to generate SQL execution events, and construct an SQL profile feature vector containing multi-dimensional features based on the SQL execution events; S2: Identify the current dynamic business scenario and generate scenario factors based on SQL structure characteristics, execution behavior, business time period, and load context; among them, for SQL with insufficient historical data, a cold start evaluation is performed using a baseline of similar SQL groups; S3: Dynamically adjust the weights of each scoring dimension according to the scenario factors, perform multi-dimensional comprehensive scoring on the SQL profile feature vector, and obtain a comprehensive risk score; S4: Based on the comprehensive risk score, a three-layer progressive detection architecture consisting of rapid initial screening, accurate assessment and hard verification is adopted to identify key SQL statements; S5: Based on the identification results, output the risk level, risk cause, evidence chain and handling priority of key SQL statements, and record user feedback to update the scenario weight and baseline.

[0007] Furthermore, in step S1, SQL execution data is collected and normalized to generate SQL execution events, and an SQL profile feature vector containing multi-dimensional features is constructed based on the SQL execution events, specifically including: SQL execution data is collected through database dynamic performance views, active session sampling, slow SQL logs, audit logs, APM call chains, and business interface gateways. The collected SQL text is normalized, including removing comments, standardizing whitespace characters, replacing literals, unifying capitalization, parameter placeholders, and statement templates, generating SQL template identifiers, and retaining parameter distribution summaries; Generate SQL execution events. Each execution event should include at least the SQL template identifier, database instance, business system, collection time window, number of executions, average execution time, P95 execution time, CPU time, logical reads, physical reads, number of rows scanned, number of rows returned, execution plan hash value, and business SLA level. Based on the SQL execution event, basic performance characteristics, derived risk characteristics, trend deterioration characteristics, business impact characteristics, and environmental context characteristics are extracted. After mapping the indicators from different database sources according to a unified indicator dictionary, they are combined into the SQL profile feature vector.

[0008] Furthermore, in step S2, identifying the current dynamic business scenario and generating scenario factors based on SQL structure characteristics, execution behavior, business time period, and load context specifically includes the following steps: Parse the syntax structure of SQL statements, extract structural features, statistically analyze the execution behavior characteristics of SQL within the current sliding window, and obtain the current business time period and load context; Based on the aforementioned structural features, execution behavior features, and load context, the scenario factor SF is calculated. The calculation formula for the scenario factor is as follows: SF=sigmoid(a1×R scan +a2×C join +a3×C agg +a4×B batch a5×F exec a6×S SLA ) Among them, R scan Represents the normalized scan line number, C join Denotes the join complexity, C agg B represents the complexity of aggregation sorting. batch Indicates whether it is in a batch window, F exec Indicates execution frequency, S sla Indicates the SLA stringency of the interface; a1 to a6 are configurable parameters.

[0009] Furthermore, in step S2, for SQL queries with insufficient historical data, a cold start evaluation is performed using a baseline of similar SQL groups, specifically including: When it is determined that the historical execution data of the SQL is insufficient, it matches the same SQL group based on its syntax structure, the size of the associated table, the business interface type and the database instance type, obtains the performance index distribution of similar SQL within the group, uses the group median or a specified quantile as a temporary baseline, calculates the degree of deviation of the current SQL relative to the group baseline, and uses this deviation as the cold start evaluation result, replacing its own historical data in the subsequent comprehensive scoring.

[0010] Furthermore, the matching of similar SQL groups specifically refers to: Extract the SQL syntax tree structure features, related table identifier sets, business interface category codes, and database instance type codes to form a feature vector; Calculate the cosine similarity between the feature vector and the center vector of each group, and take the group with the highest similarity as the matching group; if the highest similarity is lower than the preset threshold, then create a new group for the SQL.

[0011] Furthermore, in step S3, the weights of each scoring dimension are dynamically adjusted according to the scenario factors, and a multi-dimensional comprehensive score is performed on the SQL profile feature vector to obtain a comprehensive risk score, specifically including: Based on the scenario factor SF generated in step S2, determine the dynamic weights of each scoring dimension; The current weight is calculated using linear interpolation. When SF is closer to 0, the weight of time consumption, lock wait and interface SLA is increased. When SF is closer to 1, the weight of logical read, physical read and scan return ratio is increased. The original indicators of each dimension in the SQL profile feature vector are normalized and unified to comparable units. The normalized indicators of each dimension are weighted and summed with their corresponding dynamic weights to obtain the comprehensive risk score. The formula for calculating the comprehensive risk score is as follows:

[0012] Among them, f k Let N represent the k-th feature. k w represents the normalization function. k (scene,t) represents the dynamic weights related to the current scene and time window; Dynamic weights are calculated using linear interpolation:

[0013] Where SF is the scene factor, w k TP For online transaction weighting templates, w k AP To analyze batch weight templates, w k context This is a context correction item.

[0014] Furthermore, in step S4, based on the comprehensive risk score, a three-layer progressive detection architecture consisting of rapid initial screening, accurate assessment, and hard verification is used to identify key SQL queries, specifically including: First-level rapid screening: For all SQL queries, based on the comprehensive risk score, combined with sliding window TopK, resource overlap deduplication, or anomaly score algorithm, the candidate SQL set with the highest anomaly score is selected, and obviously normal SQL queries are removed to reduce subsequent computational overhead. The second layer of precise evaluation: For the candidate SQL set, a weighted scoring model, GBDT, XGBoost or lightweight neural network is used, combined with SQL profile feature vectors and dynamic weights, to output four risk levels: low, medium, high and very high, and the importance of the features is recorded. The third layer of hard verification: High-risk SQL statements output by the precise assessment are reviewed using expert rules and execution plan rules. These rules include full table scans of large tables, abnormal scan return ratios, frequent changes in execution plan hashes, expired statistics, continuously expanding lock waits, sorting and disk writes, large transaction blocking, breaches of core interface SLAs, and running batch SQL statements during peak business periods. By merging the results of the three layers of detection, SQL statements that pass the hard verification or have a very high overall score even if they fail are identified as critical SQL statements, and a list of critical SQL statements is generated.

[0015] Furthermore, in step S5, the risk level, risk cause, evidence chain, and handling priority of the key SQL are output based on the identification results, specifically including: For each identified key SQL statement, output its SQL template identifier, normalized SQL summary, database instance, associated business system, and associated interface; Output the risk level of the SQL statement, which includes four levels: P0, P1, P2, and P3, and the corresponding comprehensive risk score. Output the main causes of risk, including one or more of the following: full table scan, index failure, execution plan deterioration, lock wait, sorting disk write, read amplification, bind variable selectivity anomaly, and business peak conflict; Output the evidence chain, including the original indicator values, historical baseline, deviation rate, execution plan summary, waiting events, business interface latency, and the hard validation rules that were hit; Output processing priorities and recommended verification metrics to focus on, including P99 timeout, scan return ratio, logical read, lock wait time, and interface timeout rate.

[0016] Furthermore, in step S5, user feedback is recorded to update the scene weights and baseline, specifically as follows: Record feedback data from operations and maintenance personnel regarding confirmation of identification results, false alarms, or benefits after optimization; The feedback data is used to update the scene weight template, group baseline, and model training samples.

[0017] Furthermore, in step S4, after identifying the key SQL, the method further includes: automatically generating optimization suggestions based on the handling priority of the key SQL and pushing them to the corresponding business system or database administrator terminal. The optimization suggestions include at least one of the following: creating or modifying indexes, rewriting SQL, adjusting execution plan bindings, database sharding and table partitioning, and rate limiting and circuit breaking.

[0018] The beneficial effects of the above-described technical solutions provided in the embodiments of the present invention include at least the following: This invention departs from relying on a single fixed threshold, instead constructing a multi-dimensional feature vector encompassing performance, risk, trends, business processes, and environment. This vector, combined with a three-layer progressive detection architecture, dynamically adapts to different scenarios such as online transactions and batch processing. This method effectively distinguishes between business-reasonable long SQL queries and bottleneck SQL queries that truly impact system stability, significantly improving recognition accuracy compared to traditional solutions.

[0019] This invention standardizes raw indicators from different databases into common features through a unified indicator dictionary and mapping rules, forming a unified SQL profiling library across platforms. This allows the invention to be smoothly deployed in hybrid database environments without the need to develop separate analysis tools for each database, significantly reducing system construction and maintenance costs.

[0020] The final output of this invention clearly marks the P0-P3 risk level, the main causes of the risk, and the impact on related business operations, while providing evidence such as original indicators, historical baseline deviations, and execution plan summaries. Operations personnel can intuitively understand the judgment criteria, quickly locate the root cause, and take targeted optimization measures.

[0021] For newly launched SQL queries with insufficient historical data, this invention allows the system to generate a temporary baseline and conduct risk assessments using profiles of similar SQL queries, effectively avoiding missed detections during the cold start phase. Simultaneously, by recording manual confirmation and optimization feedback, the system continuously updates the scene weight template, model parameters, and rule thresholds, enabling the recognition capability to automatically iterate and evolve as the system runs.

[0022] In this invention, the rapid initial screening layer employs lightweight algorithms such as sliding window TopK and isolated forest to perform low-latency filtering on the entire SQL stream, sending only a small number of candidate SQLs to subsequent in-depth analysis and rule validation. This design ensures millisecond-level detection latency in the production environment while enabling fine-grained evaluation of high-risk SQLs. The output results can directly guide SQL rewriting, index optimization, or architecture changes, improving the overall stability of the database. Attached Figure Description

[0023] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on the provided drawings without creative effort.

[0024] Figure 1 This is a flowchart of a key SQL intelligent identification method based on multi-dimensional evaluation and dynamic scenarios provided in an embodiment of the present invention.

[0025] Figure 2This is a schematic diagram illustrating the construction of a unified SQL profile across databases provided in an embodiment of the present invention.

[0026] Figure 3 This is a schematic diagram of dynamic weight adjustment based on scene factors provided in an embodiment of the present invention.

[0027] Figure 4 This is a schematic diagram of a three-layer progressive intelligent detection architecture provided in an embodiment of the present invention.

[0028] Figure 5 This is a schematic diagram of the key SQL identification results and evidence chain output provided in the embodiments of the present invention. Detailed Implementation

[0029] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0030] like Figure 1 As shown in the figure, this invention discloses a key SQL intelligent recognition method based on multi-dimensional evaluation and dynamic scenarios, including the following steps: S1: Collect SQL execution data and perform normalization processing to generate SQL execution events, and construct SQL profile feature vectors containing multi-dimensional features based on the SQL execution events; this provides a unified and standardized data foundation for subsequent evaluation and solves the problem of fusion of multi-source heterogeneous data.

[0031] S2: Identify the current dynamic business scenario and generate scenario factors based on SQL structure characteristics, execution behavior, business time period and load context; for SQL with insufficient historical data, a cold start evaluation is performed using a baseline of similar SQL groups; this enables the system to adapt to business changes and still have preliminary evaluation capabilities even without historical data.

[0032] S3: The weights of each scoring dimension are dynamically adjusted according to the scenario factors, and the SQL profile feature vector is comprehensively scored from multiple dimensions to obtain a comprehensive risk score; this achieves flexible scoring based on scenario awareness and avoids misjudgment under different loads due to fixed weights.

[0033] S4: Based on the comprehensive risk score, a three-layer progressive detection architecture consisting of rapid initial screening, accurate assessment, and hard verification is adopted to identify key SQL queries; while ensuring real-time performance, the accuracy and interpretability of the identification are improved, and the computational overhead is reduced.

[0034] S5: Based on the identification results, outputs the risk level, risk cause, evidence chain, and handling priority of key SQL statements, and records user feedback to update scenario weights and baselines. It not only provides actionable decision support but also forms a closed-loop learning mechanism, enabling continuous system optimization.

[0035] This invention overcomes the shortcomings of single-indicator identification distortion and poor adaptability of fixed thresholds by introducing a scenario-factor driven dynamic weighting mechanism; it achieves multi-dimensional comprehensive evaluation in cross-database environments by constructing a unified SQL profile that includes basic performance, derived risks, trend degradation, business impact, and environmental context; and it significantly improves the accuracy and interpretability of key SQL identification in complex business systems by outputting risk level, cause, and evidence chain while ensuring real-time performance through a three-layer progressive detection architecture, and can continuously optimize the identification model through a feedback mechanism.

[0036] The following is a detailed description of the key SQL intelligent recognition method based on multi-dimensional evaluation and dynamic scenarios of the present invention: Step S1: Each execution event must include at least the SQL template identifier, database instance, business system, associated interface, collection time window, number of executions, average execution time, P90 execution time, P95 execution time, P99 execution time, CPU time, logical read, physical read, lock wait time, number of rows scanned, number of rows returned, execution plan hash value, and business SLA level. For P90 and P99 execution times, within each sliding time window, the system aggregates and sorts the execution times of a single SQL execution according to the SQL template identifier, and takes the execution times located at the 90th and 99th percentiles as the P90 and P99 execution times of the SQL in the current window, respectively. When the collection source can provide details of a single SQL execution, the quantile value is directly calculated based on the single execution time sequence. When the collection source only provides aggregated statistics or sampled data, P90 and P99 are calculated based on the time histogram, bucket statistics, or approximate quantile algorithm to reduce data collection and calculation overhead. The P90 and P99 metrics can be derived from SQL execution time records in database dynamic performance views, slow SQL logs, active session sampling, APM call chains, or business interface gateway logs.

[0037] like Figure 2 As shown, during the data acquisition phase, the system acquires raw SQL execution data in parallel through multiple methods. Specifically, for databases that support active session sampling, the system uses a fixed sampling period, which can be set to 1 second, to capture currently executing SQL statements, their session information, wait events, etc.

[0038] Simultaneously, the system parses the database's built-in slow query logs and audit logs, capturing SQL execution records whose execution time exceeds a preset threshold, such as 500 milliseconds, or meets specific conditions. Furthermore, the system extracts SQL execution fragments associated with individual business requests from the distributed call chain provided by the application performance monitoring system, thereby obtaining the binding relationship between SQL and business interfaces, the call context, and end-to-end latency. Finally, the system also collects host resource metrics such as host CPU utilization, I / O pressure, and memory usage, and obtains information such as the query rate per second, success rate, and SLA level of business interfaces from the interface gateway logs.

[0039] After obtaining the raw SQL text, the system performs normalization. Normalization includes: removing comments and redundant whitespace characters from the SQL statement; replacing integer, string, and date literals with parameter placeholders, such as using a question mark to represent a specific value; standardizing SQL keywords to lowercase; and compressing multiple values ​​in the IN or VALUES list into a standardized representation, such as uniformly representing them as "IN (...)". After these processes, the system generates a unique SQL template identifier, denoted as SQL_SIG, for each logically identical SQL structure. Simultaneously, to avoid masking performance risks caused by parameter differences due to normalization, the system not only retains a necessary parameter distribution summary but also retains the execution plan hash value for each execution event. It also performs lightweight histogram statistics on the value ranges of bind variables. For example, numerical parameters are divided into 10 equally frequent buckets, recording the execution count and average resource consumption within each bucket. This allows for subsequent detection of abnormal scenarios where the same SQL template experiences sudden changes in execution plan or significant deviations in resource consumption due to different parameters.

[0040] Subsequently, the system associates the normalized SQL template with the environment information of a single execution or aggregated execution to form a structured SQL execution event record. Each execution event contains at least the following fields: SQL_SIG, database instance name, business system identifier, associated interface name, session identifier, start and end times of the collection time window, number of executions, average execution time, P95 execution time, CPU time, number of logical reads, number of physical reads, lock wait time, number of rows scanned, number of rows returned, execution plan hash value, accessed object name, error code, call chain identifier, and business SLA level. If the collection source supports sampling to the single execution granularity, such as sampling through active sessions or APM call chains, the system prioritizes retaining single execution events; if only aggregated statistics can be obtained, such as slow logs aggregated by template, aggregation is performed in minute-level windows, but the number of executions and resource distribution quantiles within the window must be recorded additionally.

[0041] Based on this, the system constructs a unified feature vector F containing multi-dimensional features for each SQL_SIG within each sliding window. A typical sliding window is set to 5 minutes, with a sliding step size of 1 minute. To support cross-database environments, the raw metrics of different databases are first mapped using a pre-defined unified metric dictionary. For example, the buffer_gets metric for Oracle databases, the innodb_buffer_pool_read_requests metric for MySQL databases, and the logical read metric for DM databases are all uniformly mapped to logical reads. Furthermore, wait events from different databases are categorized into types such as I / O wait, lock wait, CPU wait, network wait, and memory wait. Based on the mapped metrics, the system constructs the following five categories of features.

[0042] The first category is basic performance characteristics, including execution time, CPU time, logical reads, physical reads, waiting time, lock waiting time, number of executions, number of rows scanned, number of rows returned, and number of times temporary tables are used.

[0043] The second category is derived risk characteristics, including single execution resource density (resource consumption divided by the number of executions), logical read ratio, physical read ratio, scan return ratio (number of scanned rows divided by number of returned rows), CPU utilization ratio, waiting ratio, plan stability (frequency of change of execution plan hash value), hot object concentration, and read amplification factor.

[0044] The third category is trend deterioration characteristics, including the deviation rate of the most recent hourly indicator relative to the baseline of the same period over the past seven days, the change rate of P95 time consumption, the change rate of logical reads, the change rate of execution count, the number of execution plan hash changes, and the number of consecutive windows of anomalies.

[0045] The fourth category is business impact characteristics, including the number of associated interfaces, the weight of core interfaces, changes in interface P95 or P99 latency, changes in transaction success rate, changes in timeout rate, the scale of calling users, and the business level.

[0046] The fifth category is environmental context features, including time period types such as business peaks or troughs, batch processing window markers, version release window markers, holiday markers, database load levels, host CPU and I / O pressure, connection pool wait times, and database type.

[0047] Finally, the above features are combined into a multi-dimensional profile feature vector F for the SQL statement within the current window and stored in a unified profile repository.

[0048] Step S2: Identify the current dynamic business scenario and generate scenario factors based on SQL structure characteristics, execution behavior, business time period, and load context; for SQL with insufficient historical data, use a baseline of similar SQL groups for cold start evaluation; enable the system to understand the business environment of the current SQL, and use a continuous value, namely scenario factor SF, to quantify the tendency of SQL from online transaction scenario to analysis batch processing scenario, and solve the cold start problem of new SQL.

[0049] like Figure 3 As shown, the system first parses the syntax tree of the SQL statement to extract its structural features, such as the number of tables involved, join types, and whether it contains aggregate functions and sorting operations. Simultaneously, the system statistically analyzes the execution behavior characteristics within the current sliding window, including execution frequency and the number of rows scanned. Furthermore, the system obtains the current business time period and load context information. Based on this information, the system calculates the scenario factor SF. The value of SF ranges from 0 to 1. When SF is closer to 0, it indicates that the SQL statement is more suited to low-latency, high-frequency online transaction scenarios; when SF is closer to 1, it indicates that it is more suited to large-scan, low-frequency, batch analysis scenarios.

[0050] In an optional implementation, the scene factor SF is calculated using the following sigmoid function: SF=sigmoid(a1×R scan +a2×C join +a3×C agg +a4×B batch a5×F exec a6×S SLA ) Among them, R scan This represents the normalized scan line count, which is normalized by dividing the logarithm of the current window's scan line count by the system's maximum scan line count, truncating the value to the [0,1] interval. C join The join complexity is represented by the logarithm of the number of base tables involved. For example, 0 for a single table, 0.3 for two tables, 0.6 for three to four tables, and 1.0 for five or more tables. agg The complexity of aggregation sorting is defined by the existence of operations such as GROUP BY, ORDER BY, DISTINCT, and window functions. For each such operation, the complexity increases by 0.25, up to a maximum of 1.0. batch Indicates whether it is in a batch window; the value is 1 if it is in a batch window, and 0 otherwise; F exec This represents the execution frequency, specifically the number of times the code is executed per second within the current window. It is calculated by taking the logarithm to base 10, dividing by 3, and truncating to [0,1]. slaThis indicates the strictness of the interface SLA. For core transaction interfaces, it is defined as a timeout rate threshold of ≤0.01% or a business level of A, with a value of 1. For ordinary interfaces, the value is 0.5, and for backend batch interfaces, the value is 0. The default example values ​​can be: a1=0.4, a2=0.3, a3=0.2, a4=0.5, a5=0.6, a6=0.4.

[0051] When the system determines that there is insufficient historical execution data for a particular SQL statement, it initiates a cold start evaluation mechanism. The specific criteria for "insufficient historical data" are: the total number of executions of the SQL_SIG in the past 7 days is less than 50, or the SQL_SIG appears for the first time, meaning there are no historical records in the profile repository. In this case, the system initiates the cold start evaluation mechanism. First, the system matches the SQL statement with a similar SQL group. The matching process is as follows: The system extracts the syntax tree structure features of the SQL statement, such as parsing the SQL into a 10-dimensional feature vector composed of operation type, number of tables, join type, aggregation operations, etc., along with the associated base table identifier set, business interface category code, and database instance type code. This information forms a feature vector. Then, the system calculates the cosine similarity between this feature vector and the center vectors of existing groups, and selects the group with the highest similarity as the matching group. If the highest similarity is lower than a preset threshold of 0.75, the system creates a new group for the SQL statement, using its current metrics as the initial baseline, median, and 90th quantile for the group, and sets the group center vector as the feature vector of the SQL statement. After a successful match, the system obtains the performance metric distribution of all similar SQL queries within the group, such as the number of rows scanned, median execution time, P90 quantile, and P99 quantile. The group's median or P90 quantile is used as a temporary baseline for the current SQL query. The group baseline is recalculated every 24 hours using historical execution data of all SQL queries within the group, including the median, P90, and P99, and SQL templates that haven't been updated for more than 30 days are discarded. Subsequently, the system calculates the deviation of the current SQL query's real-time metrics from the group baseline; for example, if the current number of rows scanned is 5 times the group's P90 baseline. This deviation serves as a crucial anomaly factor, directly contributing to the subsequent comprehensive scoring, thus enabling effective identification of anomalous SQL queries even without its own historical data.

[0052] Step S3: Dynamically adjust the weights of each scoring dimension according to the scenario factors, and perform multi-dimensional comprehensive scoring on the SQL profile feature vector to obtain a comprehensive risk score. The weights of the same indicator on system stability vary greatly in different scenarios. This invention enables the scoring model to be adapted to local conditions through dynamic weights.

[0053] Based on the scene factor SF generated in step S2, the system determines the dynamic weight w for each rating dimension k. k(scene,t). A typical method for calculating dynamic weights is through linear interpolation:

[0054] Where SF is the scene factor, w k TP This is an online transaction weighting template where weights for dimensions such as execution time, lock wait, connection pool wait, interface P99 latency, and transaction success rate are set to high values. k AP To analyze the batch processing weight template, the weights for dimensions such as logical reads, physical reads, scan return ratio, temporary table usage, and execution plan stability are set to relatively high values. k context This is a context-correction item, and its value is non-zero only during specific periods. For example, it may temporarily increase the weight of the execution plan stability dimension within a version release window, or temporarily increase the weight of the trend deterioration characteristic within 30 minutes after a database restart, to address sudden or special operational events. The specific dimensions and default values ​​of the weight template are shown in Table 1. Table 1: Specific Dimensions and Default Values ​​of Weight Template

[0055] The sum of the weights of each dimension in the table above is 1. Context correction items are only non-zero during specific operational events. For example, within a version release window, the weight of dimension 10 is temporarily increased by 0.10, while other dimensions are proportionally reduced to keep the sum at 1; within 30 minutes after a database restart, the weight of dimension 13 deviation rate is temporarily increased by 0.05.

[0056] After obtaining the dynamic weights, the system analyzes the original indicators f of each dimension in the SQL profile feature vector F. k Normalization is performed, mapping the scores to a comparable dimension, such as between 0 and 100. Normalization methods can include min-max normalization, Z-score normalization, or normalization based on historical quantiles. Finally, the system weights and sums the normalized dimensional indicators with their corresponding dynamic weights to obtain the comprehensive risk score, calculated using the following formula:

[0057] Among them, f k Let N represent the k-th feature. k w represents the normalization function. k(scene,t) represents the dynamic weights related to the current scene and time window; normalization can be performed using Min-Max, Z-Score, or quantile-based methods, and dynamically aligned with the historical baseline. This score is a continuous value from 0 to 100, intuitively reflecting the overall risk level of the SQL in the current dynamic scene, with higher scores indicating greater risk.

[0058] Step S4: Based on the comprehensive risk score, a three-layer progressive detection architecture consisting of rapid initial screening, accurate assessment and hard verification is adopted to identify key SQL statements.

[0059] like Figure 4 As shown, in order to balance real-time performance, accuracy, and interpretability in massive SQL streams, this invention designs a three-layer progressive detection architecture.

[0060] The first layer is a rapid initial screening. This layer targets the entire SQL event stream, aiming to filter out obviously normal SQL queries in a low-latency, low-overhead manner, significantly reducing the size of the candidate set to be evaluated. It's important to note that to avoid computational overhead, this layer does not use the comprehensive scoring involving multi-dimensional normalization and dynamic weights as in step S3. Instead, it employs a set of lightweight, single-dimensional heuristic metrics for rapid scoring. The specific algorithms used include: Sliding window TopK method: Calculate the lightweight resource consumption score of each SQL template in each window = (average time × number of executions) + (total logical reads × 0.001) + (total physical reads × 0.01), and then select the top 5% of SQLs in terms of resource consumption; Anomaly score calculation method: Use unsupervised algorithms such as Isolation Forest to perform outlier detection on the three simplest features of execution time, logical reads, and number of rows scanned, and include SQL statements with anomaly scores exceeding the threshold of 0.7 into the candidate set; Resource overlap deduplication method: By calling the traceId, multiple SQL statements belonging to the same business transaction are merged into a single aggregate record, preserving the maximum time consumption and total resource consumption, and avoiding duplicate alerts for multiple slow SQL statements in the same transaction.

[0061] The first layer outputs a candidate SQL list, the size of which is generally controlled within 5% of the total number of SQL templates. This layer outputs a candidate SQL list of controllable size and its preliminary anomaly score.

[0062] The second layer is for precise evaluation. This layer only performs in-depth analysis on the candidate SQL set output by the first layer, and executes the complete S3 calculation, including multi-dimensional feature extraction, historical baseline query, dynamic weight adjustment, and comprehensive risk scoring.

[0063] The evaluation methods include: multi-dimensional feature fusion scoring, which applies the comprehensive risk score (Score) and corresponding risk level calculated in step S3, with risk levels categorized into low, medium, high, and extremely high; machine learning model assistance, which uses supervised learning models such as XGBoost, gradient boosting decision trees, or lightweight neural networks. The input to the supervised learning model is the complete 13-dimensional feature vector constructed in step S1 and the weighted features after dynamic weight adjustment. The output is the binary classification probability and feature importance ranking. The model training samples come from historically manually confirmed SQL events. Positive samples are events that are ultimately marked as critical SQL, and negative samples are normal events. Offline retraining is triggered every 200 newly labeled samples; baseline and group cross-validation, which compares the features of the current SQL with its own historical baseline and the baseline of the same SQL group to verify the consistency of its abnormality level. For example, if the number of scanned rows of a SQL far exceeds its own baseline and the group's P99 quantile, its risk score is further improved. The second layer outputs the final comprehensive risk score (Score, 0-100), risk level (low, medium, high, extremely high), and model-predicted probability for each candidate SQL.

[0064] The third layer is hard validation. This layer performs deterministic rule verification on SQL queries identified as high-risk in the second layer to ensure that the identification results conform to basic database principles and expert experience, reducing the false positive rate. Hard rules include, but are not limited to: full table scans or index failures on large tables, where "large table" is defined as a physical size exceeding 1GB or more than 10 million rows, and the execution plan shows that a full table scan or full index scan was used; frequent changes in the execution plan hash, i.e., the execution plan hash value changes more than 3 times in a short period of time; breach of core interface SLA, where "core interface" is defined as an interface with a business SLA level of A or S, or an interface timeout rate threshold ≤0.1%, and the current interface P99 latency exceeds 1.2 times its SLA commitment value; scan return ratio Anomalies include: a scanned row count to returned row count ratio exceeding 1000:1; continuously increasing lock wait times (lock wait time increasing by more than 200% compared to the baseline of the past hour); sorting to disk (temporary table using more than 1GB of disk space); large transaction blocking (transaction uncommitted for more than 60 seconds and blocking other sessions); running batch SQL during peak business hours (SQL marked as batch processing was executed during the system's preset peak business period); and resource consumption anomalies (the resource consumption of this SQL exceeds 10 times the average of all other SQL on the same instance). If a SQL hits any of the hard validation rules, its risk level will be forcibly upgraded by one level, for example, from medium risk to high risk, or from high risk to extremely high risk, and the specific rule hit will be recorded as the core risk cause. If an SQL scores extremely high risk at the second level but does not hit any hard rules, it will still be retained as a critical SQL, but the evidence chain will note "overall score extremely high, manual review recommended".

[0065] Finally, the system merges the results from the three layers of detection to generate a list of critical SQL statements. A critical SQL statement is defined as one that passes the second-layer accurate assessment and achieves a comprehensive risk score ≥ 70, or that hits any of the third-layer hard validation rules. The generated list of critical SQL statements includes each SQL statement's template identifier, risk level (P0-P3), comprehensive risk score, hit rule, and evidence chain.

[0066] Step S5: Based on the identification results, output the risk level, risk cause, evidence chain, and handling priority of the key SQL, and record user feedback to update the scenario weight and baseline; make the identification results interpretable, traceable, and verifiable, and establish a feedback loop so that the system can continuously evolve.

[0067] like Figure 5 As shown, for each identified key SQL statement, the system outputs a structured identification result. This includes: basic information (SQL_SIG), a normalized SQL summary, the database instance it belongs to, the associated business systems and interfaces; risk assessment (risk level and comprehensive risk score), with risk levels divided into four levels: P0, P1, P2, and P3, corresponding to extremely high risk, high risk, medium risk, and low risk, respectively: P0: Score ≥ 90; P1: 70 ≤ Score < 90; P2: 50 ≤ Score < 70; P3: Score < 50; main risk causes, such as full table scan, index failure, execution plan deterioration, lock wait, sorting write-to-disk, read amplification, abnormal bind variable selectivity, or business peak conflicts; and a complete chain of evidence. The evidence chain includes original metric values ​​such as average execution time, logical reads, physical reads, and execution counts in the most recent hour; historical baselines and deviation rates, such as the deviation multiple of the current metric compared to the same period in the past seven days; execution plan summaries and differences from historical stable plans; types and percentages of waiting events; business-level evidence such as changes in P99 latency and success rate of related business interfaces; and the hard validation rules that have been triggered. In addition, the system outputs processing priorities and recommended verification metrics to prioritize, such as P99 latency, scan return ratio, logical reads, lock wait time, and interface timeout rate.

[0068] In terms of feedback learning, the system records the confirmation, false positives, and false negatives of each identification result by operations and maintenance personnel, as well as the benefits of optimization, such as the specific percentage decrease in average processing time after SQL optimization. This feedback data is used to continuously optimize the system. Specifically, the system uses an exponentially weighted moving average method to recalculate the weight values ​​in the online transaction weight template and the analysis batch processing weight template daily based on the most recent batch, for example, one hundred confirmed samples. Simultaneously, every time a certain number of manually labeled samples are accumulated, for example, two hundred, the system triggers an offline retraining task to update machine learning models such as XGBoost in the second layer. Furthermore, if the false positive rate of a certain hard rule exceeds a preset threshold, for example, five percent, for several consecutive days, the system automatically increases the threshold for that rule, for example, adjusting the threshold for a large table from one million rows to two million rows, and records the change log. Finally, the system recalculates the seven-day and thirty-day baselines of each SQL template daily and updates the performance distribution characteristics of each SQL group. Through this closed-loop feedback mechanism, the system's identification capabilities and adaptability can continuously iterate and improve as business evolves.

[0069] After identifying critical SQL queries, optimization suggestions are automatically generated based on their handling priority. These suggestions include, but are not limited to, creating or modifying indexes, rewriting SQL, adjusting execution plan bindings, database sharding, and rate limiting / circuit breaking, and are pushed to the corresponding business systems or database administrator terminals.

[0070] The following examples illustrate the key SQL intelligent recognition method of the present invention based on multi-dimensional evaluation and dynamic scenarios: Example 1: Identification of critical SQL statements in high-concurrency mission-critical systems: A certain business system experienced slow login interface response at 10:00 AM on a weekday. The system collected approximately 120,000 SQL execution events from the database instance within the current 5-minute window, which were normalized to form 4,200 SQL templates.

[0071] The system first identifies a peak online transaction scenario based on interface type, execution frequency, number of rows scanned, and SLA level, with a scenario factor (SF) of 0.18. Based on this, the system increases the weights of interface P99, lock wait, single-transaction time, connection pool wait, and transaction success rate.

[0072] The average execution time of candidate SQLA was 180ms, which did not exceed the traditional 500ms slow SQL threshold. However, its execution frequency was high, and its logical reads accounted for 22% of the entire instance. The latency of the associated login interface P99 increased from 260ms to 1100ms, and the scan return ratio reached 1800:1. The system's first-level initial screening included it in the candidate set, the second-level model determined it to be high-risk, and the third-level rules hit the core interface association, read amplification anomaly, and continuous occurrence of peak windows, ultimately classifying SQLA as a P1 level critical SQL.

[0073] The system output evidence chain: SQLA's logical reads increased by 65% ​​in the last hour compared to the baseline of the same period 7 days ago; the execution plan hash changed twice in 24 hours; the associated login interface P99 exceeded the SLA; and the scan return ratio was abnormal. Based on this, the operations and maintenance personnel further checked the indexes and statistics, verifying that the SQL's execution plan deterioration was caused by outdated statistics.

[0074] Example 2: Identifying Key SQL Statements in End-of-Day Batch Processing A data statistics task runs during the end-of-day batch processing window, with a single SQL statement taking over 20 minutes to execute. Traditional thresholds would directly classify this as a severely slow SQL statement. However, the system calculates a scenario factor SF of 0.82 based on the SQL statement's characteristics of GROUPBY, partition scan, low execution frequency, and batch processing window flags, thus identifying it as an analytical batch processing scenario.

[0075] In this scenario, the system doesn't solely rely on execution time for judgment; instead, it prioritizes calculating physical reads, scan return ratios, temporary table write-to-disk performance, window conflicts, and resource congestion. While candidate SQLB had a longer execution time, it ran within a pre-defined batch window, resulting in a lower resource usage percentage than historical baselines and no impact on core interfaces; therefore, it was ultimately classified as a P3 observation-level SQL. Candidate SQLC took 8 minutes, shorter than SQLB, but its physical read percentage reached 45% of the entire database, generating temporary table write-to-disk performance, and its execution time overlapped with peak traffic on core transaction interfaces; therefore, the system classified it as a P1 level critical SQL.

[0076] This embodiment illustrates that the present invention can distinguish between "business-reasonable long SQL" and "critical SQL that truly affects system stability".

[0077] Example 3: Cold start identification of newly launched services: A newly deployed reporting module lacked a 60-day historical baseline. The system identified the SQL statement as containing multi-table JOINs, window functions, and large table scans, and generated a temporary baseline based on similar reporting SQL groups. This SQL statement was executed only 3 times in the past 7 days, below the threshold of 50 executions, indicating insufficient historical data. When matching similar groups, the system calculated a cosine similarity of 0.82, exceeding the threshold of 0.75, successfully matching it to the large table scan group. The baseline P90 scan count for this group was 8500 rows. After deployment, it was discovered that a certain SQL statement had a significantly higher scan count than the P90 of similar groups, and returned an extremely low number of rows, resulting in an abnormal scan return ratio. Even without its own historical data, the system still included it as a high-risk candidate based on the group baseline to avoid missed detections during cold start.

[0078] Simulation experiments and effect verification: To verify the effectiveness of the key SQL intelligent recognition method based on multi-dimensional evaluation and dynamic scenarios proposed in this invention, the applicant built a simulation test platform simulating the production environment of a large enterprise information system and conducted systematic comparative experiments on the platform.

[0079] To verify the technical effectiveness of the method of this invention, a simulation test platform was built, and a database instance was deployed. The application load was generated by a combination of JMeter and scripts to simulate online transactions. Three business types were simulated: order creation, login, and inventory deduction, accounting for approximately 70% of the total requests; end-of-day batch processing, statistical summarization, and partition cleanup, accounting for approximately 20%; and report queries, historical order export, and trend analysis, accounting for approximately 10%. The order table had 5 million rows of data pre-set, the user table had 2 million rows, and the product table had 500,000 rows. The experiment ran continuously for six hours, covering the peak online transaction period (10:00-11:00) and the batch processing window (13:00-14:00).

[0080] Comparison methods: Method 1 uses the traditional slow log threshold method, with a slow SQL threshold of 500 milliseconds. Every hour, the top 20 alerts are sorted in descending order of execution time. Method 2 is the method of this invention, fully implementing steps S1 to S5. The sliding window is 5 minutes with a step size of 1 minute. Scenario factor parameters are a1=0.4, a2=0.3, a3=0.2, a4=0.5, a5=0.6, and a6=0.4. Dynamic weights use linear interpolation. The first layer uses a combination of sliding window TopK and isolated forest; the second layer uses the XGBoost model; and the third layer incorporates nine hard validation rules. Risk scoring thresholds are: P0≥90, 70≤P1<90, 50≤P2<70, and P3<50.

[0081] Experiment 1: Peak Online Transaction Scenarios During peak hours, the top 5 SQL queries output by traditional methods are primarily order statistics reports, with an average execution time of 3800ms. However, the login SQL queries that actually cause CPU spikes have an average execution time of 95ms, an execution frequency of 850 times / second, and account for 42% of the total database reads, yet they are not included in the slow log list. The method of this invention first includes this login SQL query in the candidate set, then calculates the scenario factor SF=0.21, resulting in a comprehensive risk score of 91, and finally hits the core interface association and resource congestion rules, accurately identifying this SQL query as the critical SQL query.

[0082] Experiment 2: End-of-Day Batch Processing Window Scenario Within the batch processing window, the traditional method generates 20 slow log alerts. The method of this invention only outputs two high-risk SQL statements: one is a batch update SQL statement with a physical read of 850MB, a temporary table write-to-disk size of 12GB, SF=0.81, a score of 88, and a P1 level; the other is a batch delete SQL statement that took 1100ms but had no resource contention, a score of 31, and is not alerted (P3 level). After manual verification, 18 of the 20 SQL statements alerted by the traditional method were normal batch processing behavior.

[0083] Experiment 3: Binding Variable Parameter Dependency Scenarios Scenario of parameter skew: A SQL template filters order status fields. Low-percentage status values ​​are scanned using indexes, while high-percentage status values ​​are misjudged as full table scans, resulting in a surge in scanned rows to millions. Traditional normalized aggregation yields normal average metrics and does not trigger alarms. The proposed method uses an isolated forest layer to detect outliers in scanned rows (0.92 anomaly score), a second layer with a comprehensive score of 79, and a third layer that hits the rules for full table scans and scan return ratio anomalies, outputting a high-risk judgment with an evidence chain. The method was executed 15 times with high-percentage status values ​​as the bound variable, averaging 4.8 million scans, resulting in a 35% increase in interface response time.

[0084] Experiment 4: Cold Start Scenario The newly deployed reporting module contained 8 SQL statements with no historical records. Traditional methods only capture one of these SQL statements whose execution time exceeds a threshold. This invention identifies cold start states by matching similar report groups based on SQL syntax and business interface category. The baseline for the number of rows scanned in this group (P90) is 8500 rows. Two of the new SQL statements had initial execution scan counts of 420,000 and 380,000 rows respectively, far exceeding the group baseline. The cold start assessment marked them as high-risk candidates, with comprehensive scores of 78 and 72, respectively, resulting in a P1-level warning. Manual verification revealed that both SQL statements lacked join conditions; after fixing these conditions, the scan count decreased to below 5000 rows.

[0085] The above experimental comparisons show that the method of the present invention can accurately identify the key SQL that truly affects system stability in a multi-service mixed load and heterogeneous database environment, effectively reduce the false alarm rate and false negative rate, and output a complete chain of evidence to support operation and maintenance decisions.

[0086] The various embodiments in this specification are described in a progressive manner, with each embodiment focusing on its differences from other embodiments. Similar or identical parts between embodiments can be referred to interchangeably. For the systems disclosed in the embodiments, since they correspond to the methods disclosed in the embodiments, the descriptions are relatively simple; relevant parts can be referred to the method section.

[0087] The above description of the disclosed embodiments enables those skilled in the art to make or use the invention. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the invention. Therefore, the invention is not to be limited to the embodiments shown herein, but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A key SQL intelligent identification method based on multi-dimensional evaluation and dynamic scenarios, characterized in that, Includes the following steps: S1: Collect SQL execution data and perform normalization processing to generate SQL execution events, and construct an SQL profile feature vector containing multi-dimensional features based on the SQL execution events; S2: Identify the current dynamic business scenario and generate scenario factors based on SQL structure characteristics, execution behavior, business time period, and load context; among them, for SQL with insufficient historical data, a cold start evaluation is performed using a baseline of similar SQL groups; S3: Dynamically adjust the weights of each scoring dimension according to the scenario factors, perform multi-dimensional comprehensive scoring on the SQL profile feature vector, and obtain a comprehensive risk score; S4: Based on the comprehensive risk score, a three-layer progressive detection architecture consisting of rapid initial screening, accurate assessment and hard verification is adopted to identify key SQL statements; S5: Based on the identification results, output the risk level, risk cause, evidence chain and handling priority of key SQL statements, and record user feedback to update the scenario weight and baseline.

2. The method as described in claim 1, characterized in that, In step S1, SQL execution data is collected and normalized to generate SQL execution events. Based on these SQL execution events, a multi-dimensional SQL profile feature vector is constructed, specifically including: SQL execution data is collected through database dynamic performance views, active session sampling, slow SQL logs, audit logs, APM call chains, and business interface gateways. The collected SQL text is normalized, including removing comments, standardizing whitespace characters, replacing literals, unifying capitalization, parameter placeholders, and statement templates, generating SQL template identifiers, and retaining parameter distribution summaries; Generate SQL execution events. Each execution event should include at least the SQL template identifier, database instance, business system, collection time window, number of executions, average execution time, P95 execution time, CPU time, logical reads, physical reads, number of rows scanned, number of rows returned, execution plan hash value, and business SLA level. Based on the SQL execution event, basic performance characteristics, derived risk characteristics, trend deterioration characteristics, business impact characteristics, and environmental context characteristics are extracted. After mapping the indicators from different database sources according to a unified indicator dictionary, they are combined into the SQL profile feature vector.

3. The method as described in claim 1, characterized in that, In step S2, the current dynamic business scenario is identified, and scenario factors are generated based on SQL structure characteristics, execution behavior, business time period, and load context. This specifically includes the following steps: Parse the syntax structure of SQL statements, extract structural features, statistically analyze the execution behavior characteristics of SQL within the current sliding window, and obtain the current business time period and load context; Based on the aforementioned structural features, execution behavior features, and load context, the scenario factor SF is calculated. The calculation formula for the scenario factor is as follows: SF=sigmoid(a1×R scan +a2×C join +a3×C agg +a4×B batch a5×F exec a6×S SLA ) Among them, R scan Represents the normalized scan line number, C join Denotes the join complexity, C agg B represents the complexity of aggregation sorting. batch Indicates whether it is in a batch window, F exec Indicates execution frequency, S sla Indicates the SLA stringency of the interface; a1 to a6 are configurable parameters.

4. The method as described in claim 1, characterized in that, In step S2, for SQL queries with insufficient historical data, a cold start evaluation is performed using a baseline of similar SQL groups, specifically including: When it is determined that the historical execution data of the SQL is insufficient, it matches the same SQL group based on its syntax structure, the size of the associated table, the business interface type and the database instance type, obtains the performance index distribution of similar SQL within the group, uses the group median or a specified quantile as a temporary baseline, calculates the degree of deviation of the current SQL relative to the group baseline, and uses this deviation as the cold start evaluation result, replacing its own historical data in the subsequent comprehensive scoring.

5. The method as described in claim 4, characterized in that, The specific meaning of matching similar SQL groups is: Extract the SQL syntax tree structure features, related table identifier sets, business interface category codes, and database instance type codes to form a feature vector; Calculate the cosine similarity between the feature vector and the center vector of each group, and take the group with the highest similarity as the matching group; if the highest similarity is lower than the preset threshold, then create a new group for the SQL.

6. The method as described in claim 1, characterized in that, In step S3, the weights of each scoring dimension are dynamically adjusted according to the scenario factors, and a multi-dimensional comprehensive score is performed on the SQL profile feature vector to obtain a comprehensive risk score, specifically including: Based on the scenario factor SF generated in step S2, determine the dynamic weights of each scoring dimension; The current weight is calculated using linear interpolation. When SF is closer to 0, the weight of time consumption, lock wait and interface SLA is increased. When SF is closer to 1, the weight of logical read, physical read and scan return ratio is increased. The original indicators of each dimension in the SQL profile feature vector are normalized and unified to comparable units. The normalized indicators of each dimension are weighted and summed with their corresponding dynamic weights to obtain the comprehensive risk score. The formula for calculating the comprehensive risk score is as follows: Among them, f k Let N represent the k-th feature. k w represents the normalization function. k (scene,t) represents the dynamic weights related to the current scene and time window; Dynamic weights are calculated using linear interpolation: Where SF is the scene factor, w k TP For online transaction weighting templates, w k AP To analyze batch weight templates, w k context This is a context correction item.

7. The method as described in claim 1, characterized in that, In step S4, based on the comprehensive risk score, a three-layer progressive detection architecture consisting of rapid initial screening, accurate assessment, and hard verification is used to identify key SQL queries. Specifically, this includes: First-level rapid screening: For all SQL queries, based on the comprehensive risk score, combined with sliding window TopK, resource overlap deduplication, or anomaly score algorithm, the candidate SQL set with the highest anomaly score is selected, and obviously normal SQL queries are removed to reduce subsequent computational overhead. The second layer of precise evaluation: For the candidate SQL set, a weighted scoring model, GBDT, XGBoost or lightweight neural network is used, combined with SQL profile feature vectors and dynamic weights, to output four risk levels: low, medium, high and very high, and the importance of the features is recorded. The third layer of hard verification: High-risk SQL statements output by the precise assessment are reviewed using expert rules and execution plan rules. These rules include full table scans of large tables, abnormal scan return ratios, frequent changes in execution plan hashes, expired statistics, continuously expanding lock waits, sorting and disk writes, large transaction blocking, breaches of core interface SLAs, and running batch SQL statements during peak business periods. By merging the results of the three layers of detection, SQL statements that pass the hard verification or have a very high overall score even if they fail are identified as critical SQL statements, and a list of critical SQL statements is generated.

8. The method as described in claim 1, characterized in that, In step S5, the risk level, risk cause, evidence chain, and handling priority of the key SQL are output based on the identification results, specifically including: For each identified key SQL statement, output its SQL template identifier, normalized SQL summary, database instance, associated business system, and associated interface; Output the risk level of the SQL statement, which includes four levels: P0, P1, P2, and P3, and the corresponding comprehensive risk score. Output the main causes of risk, including one or more of the following: full table scan, index failure, execution plan deterioration, lock wait, sorting disk write, read amplification, bind variable selectivity anomaly, and business peak conflict; Output the evidence chain, including the original indicator values, historical baseline, deviation rate, execution plan summary, waiting events, business interface latency, and the hard validation rules that were hit; Output processing priorities and recommended verification metrics to focus on, including P99 timeout, scan return ratio, logical read, lock wait time, and interface timeout rate.

9. The method as described in claim 1, characterized in that, In step S5, user feedback is recorded to update the scene weights and baseline, specifically as follows: Record feedback data from operations and maintenance personnel regarding confirmation of identification results, false alarms, or benefits after optimization; The feedback data is used to update the scene weight template, group baseline, and model training samples.

10. The method as described in claim 1, characterized in that, In step S4, after identifying the key SQL, the method further includes: automatically generating optimization suggestions based on the handling priority of the key SQL and pushing them to the corresponding business system or database administrator terminal. The optimization suggestions include at least one of the following: creating or modifying indexes, rewriting SQL, adjusting execution plan bindings, database sharding and table partitioning, and rate limiting and circuit breaking.

Images