MyBatis framework-oriented SQL automatic analysis optimization method and system

Abstract

The invention discloses a MyBatis framework-oriented SQL (Structured Query Language) automatic analysis and optimization method and system, and the method comprises the steps: calling a GenericTokenParser, an XNodeParser and a SqlSourceBuilder tool class officially provided by Apache MyBatis, completing the grammar analysis of a dynamic SQL statement, the recognition and replacement of a parameter placeholder, generating a BoundSql object, and obtaining complete execution plan information; inputting the static SQL text and the execution plan into a configurable artificial intelligence large model interface, and deeply analyzing the performance bottleneck by the large model; and automatically diagnosing the problem severity through a rule engine based on a quantitative index of an execution plan field, dividing the problem severity into three levels of emergency optimization, important optimization and suggested optimization, and generating a structured diagnosis report containing problem description, reason analysis and an executable optimization scheme. According to the method, the MyBatis framework characteristics and the AI large model capacity are deeply fused, the problems that dynamic SQL analysis is inaccurate, diagnosis lacks quantitative grading, an optimized closed loop is incomplete, and rules are statically solidified are solved, and the efficiency and the intelligent level of database performance management are remarkably improved.

Description

Technical Field

[0001] This invention relates to the field of database performance optimization technology, and in particular to an automatic SQL analysis and optimization method and system for the MyBatis framework. Background Technology

[0002] With the continuous expansion of internet business, relational databases, as the storage medium for core business data, directly determine the response speed and user experience of upper-layer applications through their query performance. SQL statements, as the primary means of database querying, directly impact the resource utilization and system throughput of the database server. In real-world production environments, SQL performance issues constitute one of the main bottlenecks to system stability and scalability.

[0003] Currently, SQL performance optimization mainly relies on two technical methods:

[0004] The first method is the traditional manual analysis approach. Database administrators periodically review slow query logs, extract SQL statements with long execution times, manually execute the EXPLAIN command to obtain the execution plan, and combine this with their own experience to determine if there are problems such as full table scans, missing indexes, or sort overflows, and then write optimization suggestions. This method has significant time lag, often failing to detect problems for hours or even longer after they occur; it is highly dependent on personal experience, and the analysis conclusions of different administrators may vary significantly; manual analysis is inefficient when dealing with massive SQL requests and struggles to achieve comprehensive coverage; for applications based on persistence layer frameworks such as MyBatis, the SQL statements recorded in the logs are still templates with parameter placeholders, failing to accurately reflect the final execution form, leading to a mismatch between analysis results and actual performance.

[0005] The second type is the AI-based automated analysis tools that have emerged in recent years. Some technical solutions attempt to input SQL statements into a general-purpose large language model, which then generates optimization suggestions. While this approach lowers the barrier to entry for manual analysis to some extent, it still has significant drawbacks: it is not deeply adapted to the MyBatis framework ecosystem and cannot accurately identify... <if> 、 <foreach>The dynamic tag structure leads to a high parsing error rate; the analysis process lacks detailed numerical interpretation of execution plan fields, and the diagnostic results remain at a superficial level, failing to establish a quantitative evaluation system based on fields such as the number of scanned rows, query type, and additional information; the output suggestions are in general text format and are not deeply bound to the index creation syntax and optimizer features of specific database systems, resulting in limited operability; the diagnostic results are only displayed through the console or web interface and are not integrated with enterprise-level communication tools such as DingTalk and email, making it impossible to proactively reach responsible personnel and form a closed loop of "discovery-notification-processing"; the hierarchical rules are static and fixed, unable to adapt to dynamic scenarios such as the growth of business data volume and changes in data distribution, resulting in insufficient long-term effectiveness.

[0006] In summary, existing technologies have failed to provide complete solutions for MyBatis framework compatibility, numerical diagnostics of execution plans, closed-loop feedback mechanisms, and rule adaptability, resulting in low efficiency, unstable effects, and high maintenance costs in SQL performance optimization. Therefore, there is an urgent need for an automated SQL analysis and optimization technology that deeply integrates with the MyBatis ecosystem, possesses quantitative and hierarchical diagnostic capabilities, and supports dynamic evolution and closed-loop push. Summary of the Invention

[0007] The purpose of this invention is to provide a method and system for automatic SQL analysis and optimization for the MyBatis framework, thereby solving the aforementioned problems in the prior art.

[0008] To achieve the above objectives, the technical solution adopted by the present invention is as follows:

[0009] An automatic SQL analysis and optimization method for the MyBatis framework includes the following steps:

[0010] By deploying interceptors at the database driver layer, SQL query statements that are processed by the database connection pool and finally sent to the database server are captured in real time.

[0011] The captured SQL query statement is parsed and processed by calling the GenericTokenParser, XNodeParser and SqlSourceBuilder classes provided by the Apache MyBatis framework to complete the syntax parsing of the SQL statement, the identification and replacement of parameter placeholders #{} and ${}, converting the SQL statement containing dynamic tags into static executable SQL text, generating a BoundSql object, and then obtaining the corresponding complete execution plan information through the EXPLAIN command of the database management system.

[0012] Input static SQL text and its execution plan information into the customizable AI big data model interface. The AI ​​big data model will perform in-depth analysis of the performance of the SQL query statement and identify performance problems such as query response time exceeding the preset threshold, improper use of indexes, and unreasonable data access patterns.

[0013] Performance issues are categorized into three levels—urgent optimization, important optimization, and recommended optimization—based on pre-defined severity grading rules. For each level of performance issue, a diagnostic report is generated that includes a problem description, a cause analysis based on execution plan fields, and executable optimization solutions. The diagnostic report is then automatically pushed to a pre-defined receiving terminal via an enterprise-level communication interface.

[0014] Preferably, the interceptor is implanted into the database driver through the Java Agent mechanism to capture the final form of the SQL statement before execution. The execution plan information includes at least the query type, the number of rows scanned, and the Extra field.

[0015] Preferably, severity classification rules are implemented through a rule engine. The rule engine's configuration file contains decision expressions for three levels: urgent optimization, important optimization, and recommended optimization. Each decision expression consists of an execution plan field, comparison operators, and threshold parameters. The rule engine parses the configuration file at runtime and generates executable decision logic.

[0016] Preferably, the expression for determining the emergency optimization level is: emergency_rule=(type=='ALL') AND(rows>M1)AND(cost>C1), where M1 is the first threshold, with a default value of 1 million rows, and C1 is the cost threshold, with a default value of 10,000. The cost threshold is obtained from the cost field in the execution plan or calculated based on the product of the rows value and the system overhead coefficient α, where α ranges from 0.01 to 0.1.

[0017] The preferred logic for determining the level of important optimization includes:

[0018] Execution plan Extra field text matching: Detects sorting or temporary table operations using the regular expression / (Using filesort|Usingtemporary) / ;

[0019] Index missing detection: Query the database metadata table information_schema.STATISTICS to verify whether the combination of fields involved in ORDER BY and GROUP BY exists in the existing index;

[0020] When both of the above conditions are met, it is determined to be an important optimization level, and an index creation suggestion containing the syntax CREATE INDEXidx_composite ON table(sort_field, group_field) is generated.

[0021] Preferably, it is recommended to determine the optimization level by recursively parsing the execution plan's id column:

[0022] Extract the id value of each row in the execution plan. The id value consists of numbers and "." symbols, which represent the nesting level of the subquery.

[0023] Calculate the maximum number of occurrences of the "." symbol in the id string and add 1 to obtain the actual nesting level L of the subquery;

[0024] When L>3, it is determined to be the recommended optimization level and the subquery rewriting process is triggered, rewriting the IN (subquery) or EXISTS (subquery) structure in the original SQL into a JOIN relational form.

[0025] Preferably, the rule engine supports a dynamic update mechanism:

[0026] During initialization, the hierarchical rule configuration file is loaded from the distributed configuration center. The configuration file is in YAML or JSON format.

[0027] The configuration file hash value is checked for changes at preset time intervals T (default 30 minutes). If a change is found, the new rule is hot-loaded.

[0028] The AI ​​big data model automatically adjusts the threshold parameters M1, C1, and T based on feedback data from historical optimization cases. The adjustment algorithm adopts the Q-learning model in reinforcement learning, and the reward function r = (percentage improvement in performance after optimization - misjudgment rate × penalty coefficient β).

[0029] An automated SQL analysis and optimization system based on the same concept includes:

[0030] The SQL capture module is used to capture SQL query statements that are processed by the database connection pool and finally sent to the database server in real time through interceptors deployed in the database driver layer.

[0031] The SQL parsing module integrates the GenericTokenParser, XNodeParser, and SqlSourceBuilder classes from the Apache MyBatis framework. It is used to convert captured dynamic SQL statements into static executable SQL text and generate BoundSql objects, as well as to obtain the corresponding complete execution plan information.

[0032] The AI ​​analysis module includes a customizable interface for large-scale artificial intelligence models, which receives static SQL text and execution plan information and performs in-depth analysis of the performance of SQL query statements.

[0033] The diagnostic push module is used to generate diagnostic reports based on the analysis results of the AI ​​analysis module and according to the severity classification rules, and to automatically push the diagnostic reports to preset receiving terminals through an enterprise-level communication interface.

[0034] Preferably, the SQL capture module uses a JDBC interface to call the EXPLAIN command of the database management system in real time to obtain execution plan information, and the execution plan information is parsed into a structured data format containing type, rows, and Extra fields;

[0035] The SQL parsing module is configured to recognize dynamic tag structures in MyBatis mapping files. Dynamic tags include... <if> 、 <foreach>and <where>Label the parameter placeholders #{} and ${} with the actual parameter values;

[0036] The diagnostic push module has a built-in severity classification engine, which is implemented through a rule engine and includes judgment rules for three levels of optimization: urgent optimization, important optimization, and recommended optimization, based on the comparison of thresholds in the execution plan fields.

[0037] In another embodiment, a computer-readable storage medium or electronic device is provided, wherein a computer program is stored on the storage medium, or the electronic device includes a processor and a memory, wherein the computer program, when executed by the processor, implements the steps of any of the above methods.

[0038] The beneficial effects of this invention are:

[0039] This invention, by deeply integrating the features of the MyBatis framework with the capabilities of large-scale artificial intelligence models, constructs a fully automated optimization system covering the entire chain from real-time SQL capture, deep parsing, intelligent analysis, quantitative diagnosis to closed-loop push. Compared with existing technologies, it achieves the following significant advantages:

[0040] First, it achieves precision and real-time SQL capture. By embedding a bytecode enhancement interceptor at the database driver layer, the capture timing is precisely positioned after connection pool processing and before database execution, ensuring that the captured SQL statement is completely consistent with the final form received by the database. This completely solves technical problems such as inaccurate MyBatis dynamic SQL parsing, unrecognized parameter placeholders, and tracing failure caused by connection pool rewriting. Compared to post-event analysis relying on logs, this invention achieves second-level discovery of performance problems, significantly shortening the problem awareness time.

[0041] Secondly, it significantly improves the accuracy of the parsing process. This invention creatively utilizes the three-stage toolchain of GenericTokenParser, XNodeParser, and SqlSourceBuilder provided by Apache MyBatis, following the framework's native parsing logic to convert dynamic SQL into static executable text and generate BoundSql objects, achieving an extremely high level of parsing accuracy. Compared to traditional regular expressions or string replacement schemes, this method avoids parsing errors caused by the complexity of SQL syntax, providing reliable input for subsequent execution plan analysis.

[0042] Third, a quantitative and graded intelligent diagnostic mechanism was established. This invention pioneered a three-tiered severity judgment rule based on numerical indicators of execution plan fields. Through a rule engine, discrete fields such as the `type` field, `rows` (number of rows scanned), and `Extra` (extra information) are integrated into quantifiable judgment expressions, achieving precise grading of performance issues. Compared to the qualitative descriptions of existing technologies, this invention's three-tiered system of urgent optimization, important optimization, and recommended optimization makes optimization priorities immediately clear, helping technical personnel quickly focus on key issues and avoiding the waste of resources on low-value optimizations.

[0043] Fourth, the optimization suggestions are highly actionable and interpretable. For each judgment level, the system not only generates a problem description but also provides a root cause analysis based on the specific values ​​of the execution plan fields, and directly outputs executable DDL index creation statements or SQL rewriting solutions. The suggestions are deeply linked to the execution plan fields; for example, for the case where type=ALL and rows exceed the threshold, the system explicitly provides the CREATE INDEX statement; for the case where Extra=Using filesort, a composite index combination is recommended. This strong correlation significantly increases the adoption rate of the suggestions, allowing technical personnel to apply them directly without secondary interpretation.

[0044] Fifth, a complete closed-loop feedback system has been established. This invention automatically pushes diagnostic reports to preset receiving terminals via an enterprise-level communication interface, supporting both DingTalk Webhook and email SMTP channels. The pushed content adopts a JSON structured format, including complete information such as SQL summary, problem level, execution plan snapshot, and optimization solutions. Compared with traditional tools that rely on manual login to the console, the closed-loop push ensures that performance issues reach the responsible party as soon as possible, forming a virtuous cycle of "automatic discovery - proactive notification - rapid processing," significantly improving problem response speed and processing efficiency.

[0045] Sixth, it supports dynamic evolution and adaptive adjustment of rules. The rule engine configuration file of this invention supports hot loading, and the system periodically detects configuration changes and automatically applies them without restarting the service. More importantly, it introduces the Q-learning model from reinforcement learning, automatically adjusting threshold parameters based on feedback data from historical optimization cases, enabling hierarchical rules to evolve with changes in business data distribution. Compared to static, fixed rules, this invention maintains stable diagnostic accuracy in long-term use, avoiding rule failure issues caused by data volume growth or changes in business models.

[0046] Seventh, it possesses broad framework and database adaptation capabilities. This invention is deeply adapted to all versions of the MyBatis framework and supports all dynamic tags ( <if> 、 <foreach> 、 <choose>The system combines parameters (e.g., `#{}`, `${}`) with parameter placeholders. It is also compatible with mainstream database systems such as MySQL, PostgreSQL, and Oracle. To address the differences in EXPLAIN output formats across different databases, the system has a built-in parsing adapter to ensure accurate extraction of execution plan fields. This broad adaptability gives the invention strong versatility and engineering implementation capabilities.

[0047] Eighth, a balance between performance optimization and resource consumption is achieved. The Java Agent interception mechanism adopts an asynchronous non-blocking design, and SQL parsing and analysis tasks utilize message queues and thread pools to smooth out peak and off-peak times. The AI ​​analysis module supports timeout circuit breaking and degradation strategies. The overall system's performance impact on business applications is kept to an extremely low level, with minimal increase in CPU and memory usage and reasonable network bandwidth consumption, making it suitable for high-concurrency scenarios in production environments. Attached Figure Description

[0048] Figure 1 This is a flowchart of the method of the present invention;

[0049] Figure 2 This is the system architecture diagram of the present invention. Detailed Implementation

[0050] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention.

[0051] Reference Figure 1 The method for automatic SQL analysis and optimization for the MyBatis framework, as shown, includes the following steps:

[0052] S1. By deploying an interceptor in the database driver layer, SQL query statements that are processed by the database connection pool and finally sent to the database server are captured in real time.

[0053] S2. Parse and process the captured SQL query statement. Call the GenericTokenParser, XNodeParser and SqlSourceBuilder classes provided by the Apache MyBatis framework to complete the syntax parsing of the SQL statement, the identification and replacement of parameter placeholders #{} and ${}, convert the SQL statement containing dynamic tags into static executable SQL text, generate a BoundSql object, and then obtain the corresponding complete execution plan information through the EXPLAIN command of the database management system.

[0054] S3. Input the static SQL text and its execution plan information into the customizable AI big model interface. The AI ​​big model will perform in-depth analysis of the performance of the SQL query statement and identify performance problems such as query response time exceeding the preset threshold, improper use of indexes, and unreasonable data access patterns.

[0055] S4. According to the pre-set severity classification rules, performance issues are divided into three levels: urgent optimization, important optimization, and recommended optimization. For each level of performance issues, a diagnostic report is generated, which includes a problem description, a cause analysis based on the execution plan field, and an executable optimization solution. The diagnostic report is then automatically pushed to the preset receiving terminal through the enterprise-level communication interface.

[0056] Preferably, the interceptor is implanted into the database driver through the Java Agent mechanism to capture the final form of the SQL statement before execution. The execution plan information includes at least the query type, the number of rows scanned, and the Extra field.

[0057] Preferably, severity classification rules are implemented through a rule engine. The rule engine's configuration file contains decision expressions for three levels: urgent optimization, important optimization, and recommended optimization. Each decision expression consists of an execution plan field, comparison operators, and threshold parameters. The rule engine parses the configuration file at runtime and generates executable decision logic.

[0058] Preferably, the expression for determining the emergency optimization level is: emergency_rule=(type=='ALL') AND(rows>M1)AND(cost>C1), where M1 is the first threshold, with a default value of 1 million rows, and C1 is the cost threshold, with a default value of 10,000. The cost threshold is obtained from the cost field in the execution plan or calculated based on the product of the rows value and the system overhead coefficient α, where α ranges from 0.01 to 0.1.

[0059] The preferred logic for determining the level of important optimization includes:

[0060] Execution plan Extra field text matching: Detects sorting or temporary table operations using the regular expression / (Using filesort|Usingtemporary) / ;

[0061] Index missing detection: Query the database metadata table information_schema.STATISTICS to verify whether the combination of fields involved in ORDER BY and GROUP BY exists in the existing index;

[0062] When both of the above conditions are met, it is determined to be an important optimization level, and an index creation suggestion containing the syntax CREATE INDEXidx_composite ON table(sort_field, group_field) is generated.

[0063] Preferably, it is recommended to determine the optimization level by recursively parsing the execution plan's id column:

[0064] Extract the id value of each row in the execution plan. The id value consists of numbers and "." symbols, which represent the nesting level of the subquery.

[0065] Calculate the maximum number of occurrences of the "." symbol in the id string and add 1 to obtain the actual nesting level L of the subquery;

[0066] When L>3, it is determined to be the recommended optimization level and the subquery rewriting process is triggered, rewriting the IN (subquery) or EXISTS (subquery) structure in the original SQL into a JOIN relational form.

[0067] Preferably, the rule engine supports a dynamic update mechanism:

[0068] During initialization, the hierarchical rule configuration file is loaded from the distributed configuration center. The configuration file is in YAML or JSON format.

[0069] The configuration file hash value is checked for changes at preset time intervals T (default 30 minutes). If a change is found, the new rule is hot-loaded.

[0070] The AI ​​big data model automatically adjusts the threshold parameters M1, C1, and T based on feedback data from historical optimization cases. The adjustment algorithm adopts the Q-learning model in reinforcement learning, and the reward function r = (percentage improvement in performance after optimization - misjudgment rate × penalty coefficient β).

[0071] Reference Figure 2 The illustrated concept of an automated SQL analysis and optimization system includes:

[0072] The SQL capture module is used to capture SQL query statements that are processed by the database connection pool and finally sent to the database server in real time through interceptors deployed in the database driver layer.

[0073] The SQL parsing module integrates the GenericTokenParser, XNodeParser, and SqlSourceBuilder classes from the Apache MyBatis framework. It is used to convert captured dynamic SQL statements into static executable SQL text and generate BoundSql objects, as well as to obtain the corresponding complete execution plan information.

[0074] The AI ​​analysis module includes a customizable interface for large-scale artificial intelligence models, which receives static SQL text and execution plan information and performs in-depth analysis of the performance of SQL query statements.

[0075] The diagnostic push module is used to generate diagnostic reports based on the analysis results of the AI ​​analysis module and according to the severity classification rules, and to automatically push the diagnostic reports to preset receiving terminals through an enterprise-level communication interface.

[0076] Preferably, the SQL capture module uses a JDBC interface to call the EXPLAIN command of the database management system in real time to obtain execution plan information, and the execution plan information is parsed into a structured data format containing type, rows, and Extra fields;

[0077] The SQL parsing module is configured to recognize dynamic tag structures in MyBatis mapping files. Dynamic tags include... <if> 、 <foreach>and <where>Label the parameter placeholders #{} and ${} with the actual parameter values;

[0078] The diagnostic push module has a built-in severity classification engine, which is implemented through a rule engine and includes judgment rules for three levels of optimization: urgent optimization, important optimization, and recommended optimization, based on the comparison of thresholds in the execution plan fields.

[0079] In another embodiment, a computer-readable storage medium or electronic device is provided, wherein a computer program is stored on the storage medium, or the electronic device includes a processor and a memory, wherein the computer program, when executed by the processor, implements the steps of any of the above methods.

[0080] Example 1: Complete Implementation of SQL Capture and MyBatis Parsing

[0081] 1.1 Bytecode Enhancement Interceptor Deployment

[0082] The Java Agent is loaded via JVM parameters when the application starts. The Agent's entry class implements the `premain` method, registering a `ClassFileTransformer` before class loading. This transformer intercepts implementations of the mainstream database driver `PreparedStatement`, including drivers for MySQL, PostgreSQL, and Oracle.

[0083] The Javassist framework is used to enhance the bytecode of implementations of the PreparedStatement interface, inserting capture logic before the executeQuery and executeUpdate method calls. The interception point is set before the PreparedStatement methods execute; at this point, MyBatis has completed dynamic SQL parsing and parameter population, and the connection pool has rewritten the SQL. The captured SQL text is the final form sent to the database server. The interception logic uses ThreadLocal to store the current SQL context, ensuring data isolation in a multi-threaded environment. Real-world testing shows that on a single application instance with a 500 QPS load, the agent increases CPU usage by an average of 2.8% and memory usage by approximately 50MB, with minimal impact on business performance.

[0084] 1.2 MyBatis Three-Phase Parsing Toolchain Call

[0085] The captured SQL text may contain residual MyBatis dynamic tags, requiring normalization processing using the official toolchain. The parsing process consists of three stages:

[0086] The first stage uses GenericTokenParser to identify and extract dynamic tag structures and parameter placeholders from the SQL text, and replaces the placeholders with JDBC standard question mark placeholders.

[0087] The second stage uses XNodeParser to parse the Mapper XML configuration into XML node objects and handle dynamic tag structures.

[0088] The third stage uses SqlSourceBuilder to replace parameter placeholders with actual parameter values, generating a BoundSql object containing complete SQL statements and parameter mappings.

[0089] Through a three-stage parsing process, the user-written dynamic SQL is ultimately converted into static executable SQL text and a BoundSql object is generated, which contains the executable SQL text, parameter mapping relationships, and a list of parameter values, providing standardized input for subsequent execution plan analysis.

[0090] 1.3 Real-time Acquisition and Analysis of Execution Plans

[0091] The system invokes the EXPLAIN command in the database via JDBC interface and parses the JSON format results. It constructs the EXPLAIN statement, binds parameters from BoundSql, and executes it to obtain the execution plan in JSON format. The parsing process extracts key fields such as query type, estimated number of rows scanned, additional information, filtering ratio, indexes used, estimated cost, and the execution plan ID column.

[0092] For different database systems, the system has built-in parsing adapters: MySQL 8.0 supports direct parsing of FORMAT=JSON; PostgreSQL calls EXPLAIN (FORMAT JSON) to parse the top-level Plan node; Oracle first executes EXPLAINPLAN FOR and then queries PLAN_TABLE to convert it into a unified format.

[0093] Example 2: Specific Implementation of the Severity Rating Rule Engine

[0094] This embodiment details the configuration, parsing, judgment, and dynamic update mechanism of the rule engine.

[0095] 2.1 Rule Configuration File Structure

[0096] The rule configuration file uses YAML format and supports both manual editing and hot reloading by the program. The file includes a version number, update time, configuration hash value, and judgment rules for three levels.

[0097] Emergency optimization rules must simultaneously meet three conditions: a full table scan, more than 1 million rows scanned, and a cost exceeding 10,000. Important optimization rules use a logical OR pattern and are triggered when the file is sorted and lacks an index, or when the response time exceeds 2000 milliseconds. It is recommended that optimization rules target subqueries with nested levels exceeding three.

[0098] Each decision rule includes an enabled status, description, severity level, a list of conditions, and logical operators. Conditions consist of execution plan fields, comparison operators, and threshold parameters, supporting operations such as equal to, greater than, and regular expression matching.

[0099] 2.2 Rule Engine Initialization and Runtime Loading

[0100] When the rules engine starts, it loads the configuration file and builds an executable decision logic tree. The system reads the YAML content, parses it into a rule configuration object, and then converts it into an internal rule object. Each condition is converted into a predicate function and stored in the rule cache.

[0101] The rule determination entry point evaluates three levels sequentially: first, it determines whether to apply an emergency optimization rule; if it is applied, an emergency-level diagnostic report is generated. If it is not applied, it determines whether to apply an important optimization rule; if it is applied, an important-level report is generated. If it is not applied again, it determines whether to apply a suggested optimization rule; if it is applied, a suggested-level report is generated. If it is not applied to any of the three rules, it is marked as normal and no push notification is sent.

[0102] 2.3 Logic for Determining Emergency Optimization Level

[0103] Emergency optimization decisions require three conditions to be met simultaneously. The system first checks if the query type is a full table scan, then verifies if the number of scanned rows exceeds a threshold, and finally assesses if the cost exceeds the threshold. For databases that do not return a cost field, the system uses a cost estimation model: cost equals the number of scanned rows multiplied by the single-row scan overhead coefficient plus fixed overhead. The coefficient is dynamically adjusted based on the database type; the default value is 0.04 for MySQL, 0.06 for PostgreSQL, and 0.03 for Oracle.

[0104] 2.4 Logic for Determining the Level of Important Optimizations

[0105] Key optimization decisions require checking file sorting and verifying index existence. The system first uses regular expressions to check if the `Extra` field of the execution plan contains "Using filesort" or "Using temporary". Then, it queries the database metadata table `information_schema.STATISTICS` to verify if the sorting field exists as the leftmost prefix of an existing index. Finally, it checks if the response time exceeds a 2000-millisecond threshold.

[0106] When an important optimization is determined, the system generates a composite index suggestion according to the leftmost prefix principle, prioritizing the placement of fields with high selectivity in the WHERE condition on the leftmost side of the index, followed by the sorting field.

[0107] 2.5 It is recommended to optimize the level determination logic.

[0108] It is suggested that the optimization decision be implemented by recursively parsing the `id` column of the execution plan. The system extracts the `id` value from each row of the execution plan and uses the maximum value as the nesting level. When the nesting level exceeds 3 levels, the suggested optimization rule is triggered.

[0109] The system uses SQLGlot to parse SQL into an abstract syntax tree, identify subquery nodes, and rewrite IN (subquery) or EXISTS (subquery) structures into JOIN association forms, maintaining semantic equivalence of queries.

[0110] 2.6 Dynamic Update Mechanism

[0111] The rule thresholds support hot reloading and AI adaptive adjustment. The system checks for changes in the configuration file hash value every 30 minutes; if a change is found, the new rule is hot-loaded. Simultaneously, a reinforcement learning Q-learning model is introduced to automatically adjust the threshold parameters based on historical optimization feedback data.

[0112] The Q-learning model calculates the expected return based on a reward function, where the reward value equals the performance improvement minus the false positive rate multiplied by a penalty coefficient. The model maintains a Q-table recording the state-action value mapping and employs an ε-greedy strategy to balance exploration and utilization. Batch training is performed daily at midnight based on feedback data from the past 7 days, automatically adjusting thresholds such as M1, C1, and T2 to adapt to changes in business data distribution.

[0113] Example 3: In-depth analysis and result verification of large AI models

[0114] This embodiment illustrates the Prompt project template design, model calling process, and result credibility verification mechanism of the AI ​​analysis module.

[0115] 3.1 Prompt Project Template System

[0116] A structured Prompt template library is built for different database types and problem levels. Urgent optimization templates require AI to analyze the causes of full table scans and provide index creation suggestions down to the field level. Important optimization templates require AI to analyze file sorting issues and recommend composite indexes. Suggested optimization templates require AI to rewrite nested subqueries as JOIN operations and explain the performance improvement principles.

[0117] Each template includes input information, task requirements, output format, etc., ensuring that the AI ​​outputs structured JSON, including fields such as diagnostic conclusions, optimization suggestions, and expected improvements.

[0118] 3.2 AI Model Calling and Result Analysis

[0119] The system selects an appropriate Prompt template based on the problem severity and constructs contextual information including SQL text, execution plan, and table structure. Then, it selects an AI model based on problem complexity: high-precision models are used for urgent problems, while lower-cost models are used for ordinary problems.

[0120] After calling the AI ​​API, the system parses the JSON response and extracts the diagnostic results. If the AI ​​analysis fails or the validation fails, the system reverts to the rule-based generation logic to ensure that usable optimization suggestions are always provided.

[0121] 3.3 Result Reliability Verification Mechanism

[0122] The recommendations generated by the AI ​​model must be verified through multiple dimensions: the table names and fields in the index recommendations must exist in the execution plan and database metadata; the performance improvement estimate must be within a reasonable range of 10% to 99%; the rewritten SQL syntax must be valid; and the values ​​of the execution plan fields referenced in the diagnostic text must be consistent with the actual values.

[0123] If the verification passes, the diagnostic report enters the push queue; if the verification fails, the system reverts to the rule-based backup suggestion generation logic.

[0124] Example 4: Closed-loop push and system integration

[0125] This embodiment illustrates the complete implementation of automatically pushing diagnostic reports through an enterprise-level communication interface.

[0126] 4.1 Implementation of DingTalk Robot Push Notifications

[0127] The DingTalk push module constructs Markdown-formatted messages, including information such as issue level, application module, SQL summary, execution plan, root cause analysis, optimization plan, and expected improvement. It also supports the "@relevant responsible person" function.

[0128] Frequency control is implemented before push notifications are sent; the same SQL fingerprint and issue level combination is pushed only once per hour to avoid message bombardment. Push status is recorded in a Redis cache.

[0129] If a DingTalk push fails, the system will automatically detect the error and downgrade to email push.

[0130] 4.2 Implementation of Email SMTP Push

[0131] The email push module uses the Thymeleaf template engine to generate HTML reports, which include styled execution plans, color-coded issue levels, complete execution plan JSON attachments, and optimized SQL scripts.

[0132] When email delivery fails, the exception is captured and logged, triggering a monitoring alert.

[0133] 4.3 Push Failure Degradation and Circuit Breaker Mechanism

[0134] The push service is configured with a circuit breaker; if the number of failures reaches a threshold within one minute, push notifications will be suspended for 5 minutes. Failure diagnostic reports are saved to the local file system, and an alarm is triggered to notify operations and maintenance personnel.

[0135] Push channels are prioritized and tried in turn via DingTalk and email. If all fail, the report is persisted locally to ensure no data is lost.

[0136] Example 5: System Overall Architecture and Deployment Scheme

[0137] This embodiment describes the system's physical deployment architecture, the interaction between modules, and performance metrics.

[0138] System deployment architecture:

[0139] The system adopts a layered, distributed architecture. A Java Agent is deployed on the business application server cluster to capture SQL queries and deliver them to the analysis cluster via a Kafka message queue. After consuming the messages, the analysis cluster calls MyBatis to parse the chain, obtain the execution plan, perform AI analysis, generate a diagnostic report, writes it to MySQL, and delivers it to the push service. The push service then sends the report to DingTalk or email.

[0140] The SQL capture module runs as a Java Agent within the JVM processes of various business applications. It captures SQL non-intrusively and sends it asynchronously via Kafka Producer, avoiding impact on business threads. The Agent package is approximately 800KB in size, loaded at startup, and consumes approximately 50MB of memory.

[0141] The message queue uses a Kafka cluster, the Topic is configured as sql-capture, the number of partitions is 10 to support parallel consumption, and the replication factor is 2 to ensure reliability. The average size of a single message is 2KB.

[0142] The SQL analysis service is deployed independently, consuming Kafka messages, calling MyBatis to parse the SQL chain, obtain the execution plan, perform AI analysis, and generate diagnostic reports. The number of service instances dynamically scales up and down based on SQL throughput, with a single instance capable of processing approximately 500 SQL statements per second. Parsing results and execution plans are cached in Redis with a TTL of 1 hour.

[0143] The push service is an independent process, consuming diagnostic reports in Kafka Topics. It supports dual channels of DingTalk Webhook and email SMTP, configures a circuit breaker mechanism, and persists failure reports to local files and triggers monitoring alarms.

[0144] The configuration center uses Nacos or Apollo to store rule configuration files, Prompt templates, and routing tables, and supports hot updates and canary releases.

[0145] Interaction process between the rules engine and the AI ​​analysis module:

[0146] The SQL capture module sends a BoundSql message to the MyBatis parsing module. The parsing module performs a three-stage parsing process to generate static SQL, obtains the execution plan, and then calls the rules engine for evaluation. The rules engine loads the configuration file, builds a decision logic tree, and sequentially executes the urgent optimization decision, the important optimization decision, and the recommended optimization decision.

[0147] Upon hitting any rule, the corresponding AI analysis template is triggered. The AI ​​module renders the Prompt template, calls the large model API, parses the JSON response, and performs a confidence check. If the check passes, an AnalysisResult is returned; otherwise, it falls back to the rule-based backup suggestion.

[0148] The push service generates diagnostic reports and routes them to DingTalk or email channels based on the application module and the severity of the problem.

[0149] Performance and resource consumption indicators:

[0150] In a real-world test of a financial risk control system, the average CPU usage of the business application server increased by 2.8%, and the average CPU usage of the analytics cluster increased by 35%. Agent memory usage was approximately 50MB per instance, and the JVM heap memory usage of the analytics service was approximately 2GB per instance. Kafka's peak bandwidth was approximately 8Mbps, and the AI ​​analytics module consumed approximately 500MB of traffic daily when calling the large model API. The diagnostic report MySQL table grew by approximately 600MB daily.

[0151] The capture latency, from SQL execution to data entering Kafka, averages 12ms, with a 99th percentile threshold of 35ms. The analysis latency, from Kafka consumption to report generation, averages 180ms. The push latency, from report generation to delivery, averages 850ms. The total end-to-end latency is approximately 1.5 seconds.

[0152] The system is highly available: Agent anomalies do not affect business startup and operation; a single node failure in a three-node Kafka cluster does not affect sending and receiving; the analysis service is deployed in multiple instances and automatically scales up and down through Kubernetes HPA; the push service has dual instances for mutual backup, and a circuit breaker mechanism prevents cascading failures.

[0153] Example 6: Dynamic Rule Adjustment and Reinforcement Learning Training

[0154] This embodiment illustrates a reinforcement learning threshold adjustment mechanism based on historical feedback data.

[0155] Feedback data collection:

[0156] Feedback is recorded when developers adopt or reject optimization suggestions, including report ID, adoption status, actual performance improvement, reason for rejection, current rule threshold snapshot, and false positive rate. Feedback data is written to MySQL and Redis queues for training purposes.

[0157] The false positive rate is calculated as follows: when explicitly rejecting a case and stating that it is a false positive, the false positive rate is 1.0; when rejecting a case without stating a reason, the rate is 0.5; and when accepting a case, the rate is 0.0.

[0158] Reinforcement learning training process:

[0159] The daily training task reads feedback data from the Redis queue for the past 7 days at midnight. For each piece of feedback, the system constructs a state vector (current threshold combination), infers the action to be taken (increase, decrease, or maintain the threshold) based on the feedback result, calculates the reward value, and then applies the action to obtain the new state and update the Q table.

[0160] The reward value is calculated as the percentage improvement in actual performance minus the false positive rate multiplied by the penalty coefficient. The Q-table is updated using the Q-Learning formula, which combines the learning rate, discount factor, and the maximum Q-value of the next state.

[0161] After training, the system iterates through the Q-table to find the threshold corresponding to the state with the highest average Q-value and applies it to the rule engine. After 30 days of training, the M1 threshold of a certain e-commerce platform was automatically adjusted from 1 million to 850,000, and the C1 threshold was adjusted from 10,000 to 8,500. The adoption rate of optimization suggestions increased from 62% to 78%, and the false positive rate decreased from 12% to 4.1%.

[0162] Example 7: Industrial Application Case

[0163] Case 1: Order Query Optimization of a Leading E-commerce Platform

[0164] The order query interface receives approximately 2 million calls daily, with core SQL queries involving multi-table JOINs and complex WHERE conditions. During promotional periods, a large number of order status query SQL queries resulted in full table scans, causing the interface's P99 latency to worsen from 200ms to 4 seconds.

[0165] After deploying this invention, the agent captures SQL in real time, emergency optimization rules are hit, and GPT-4 analysis suggests creating a composite index. A DingTalk alert notifies the DBA, who creates the index within 10 minutes. After optimization, the query type changes from a full table scan to a range scan, the number of rows scanned decreases from 3.2 million to 800, performance is improved by 99.9%, and interface latency is restored to 150ms.

[0166] Case 2: Subquery Optimization of a Financial Risk Control System

[0167] The risk control rule engine uses a large number of nested subqueries. One rule SQL has a nesting level of up to 5 levels, and the execution plan id column shows multiple levels of nesting. A single query takes 8.7 seconds.

[0168] The system determined the task to be at a recommended optimization level, and the DeepSeek model rewrote the subquery as a JOIN join. After the rewrite, the execution plan's id column displayed a single-level structure, the query type changed from a full table scan to an equi-join, the number of rows scanned decreased from 8 million to 12,000, the response time decreased to 45 milliseconds, performance improved by 99.5%, risk control decision latency was significantly reduced, and business throughput increased by 3 times.

[0169] Industrial applicability

[0170] This invention is applicable to Java enterprise-level application systems that widely adopt the MyBatis framework, and is particularly suitable for scenarios such as high-concurrency Internet applications, financial-grade core systems, complex business logic systems, and microservice architecture systems.

[0171] This invention has been piloted and deployed in leading companies across multiple industries, with a cumulative analysis of over 300 million SQL statements. The adoption rate of optimization suggestions has remained stable at over 75%, significantly reducing the average repair time for database performance issues and improving the overall stability and resource utilization of the system. It possesses mature industrial application value and promising prospects for promotion.

[0172] By adopting the above-disclosed technical solution of this invention, the following beneficial effects are obtained:

[0173] This invention deeply integrates the features of the MyBatis framework with the capabilities of large-scale artificial intelligence models to construct a fully automated optimization system that encompasses real-time SQL capture, deep parsing, intelligent analysis, quantitative diagnosis, and closed-loop push. The core innovation lies in a quantitative hierarchical rule engine based on the numerical characteristics of execution plan fields, combined with a dynamic threshold adjustment mechanism, enabling accurate identification and intelligent evolution of performance issues. Compared to existing technologies, this invention achieves significant improvements in parsing accuracy, diagnostic quantification, closed-loop integrity, and rule adaptability, effectively addressing industry pain points in SQL performance optimization within the MyBatis environment.

[0174] The above description is only a preferred embodiment of the present invention. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the principle of the present invention, and these improvements and modifications should also be considered within the scope of protection of the present invention.< / where> < / foreach> < / if> < / choose> < / foreach> < / if> < / where> < / foreach> < / if> < / foreach> < / if>

Claims

1. A method for automatic SQL analysis and optimization for the MyBatis framework, characterized in that, Includes the following steps: By deploying interceptors at the database driver layer, SQL query statements that are processed by the database connection pool and finally sent to the database server are captured in real time. The captured SQL query statement is parsed and processed by calling the GenericTokenParser, XNodeParser and SqlSourceBuilder classes provided by the Apache MyBatis framework to complete the syntax parsing of the SQL statement, the identification and replacement of parameter placeholders #{} and ${}, converting the SQL statement containing dynamic tags into static executable SQL text, generating a BoundSql object, and then obtaining the corresponding complete execution plan information through the EXPLAIN command of the database management system. The static SQL text and its execution plan information are input into the customizable AI big model interface. The AI ​​big model performs in-depth analysis of the performance of the SQL query statement and identifies performance problems such as query response time exceeding a preset threshold, improper use of indexes, and unreasonable data access patterns. Performance issues are categorized into three levels—urgent optimization, important optimization, and recommended optimization—based on pre-defined severity grading rules. For each level of performance issue, a diagnostic report is generated that includes a problem description, a cause analysis based on execution plan fields, and executable optimization solutions. The diagnostic report is then automatically pushed to a pre-defined receiving terminal via an enterprise-level communication interface.

2. The method according to claim 1, characterized in that, The interceptor is implanted into the database driver through the Java Agent mechanism to capture the final form of the SQL statement before execution. The execution plan information includes at least the query type, the number of rows scanned, and the Extra field.

3. The method according to claim 1, characterized in that, The severity classification rules are implemented through a rule engine. The configuration file of the rule engine contains decision expressions for three levels: urgent optimization, important optimization, and recommended optimization. Each decision expression consists of an execution plan field, a comparison operator, and a threshold parameter. The rule engine parses the configuration file at runtime and generates executable decision logic.

4. The method according to claim 3, characterized in that, The expression for determining the emergency optimization level is: emergency_rule=(type=='ALL') AND (rows>M1)AND(cost>C1), where M1 is the first threshold, with a default value of 1 million rows, and C1 is the cost threshold, with a default value of 10,000. The cost threshold is obtained from the cost field in the execution plan or calculated based on the product of the rows value and the system overhead coefficient α, where α ranges from 0.01 to 0.

1.

5. The method according to claim 3, characterized in that, The logic for determining the important optimization level includes: Execution plan Extra field text matching: Detects sorting or temporary table operations using the regular expression / (Using filesort|Usingtemporary) / ; Index missing detection: Query the database metadata table information_schema.STATISTICS to verify whether the combination of fields involved in ORDER BY and GROUP BY exists in the existing index; When both of the above conditions are met, it is determined to be an important optimization level, and an index creation suggestion containing the syntax CREATE INDEX idx_composite ON table(sort_field, group_field) is generated.

6. The method according to claim 3, characterized in that, The recommended optimization level is determined by recursively parsing the execution plan's id column: Extract the id value of each row in the execution plan. The id value consists of numbers and "." symbols, representing the nesting level of the subquery. Calculate the maximum number of occurrences of the "." symbol in the id string and add 1 to obtain the actual nesting level L of the subquery; When L>3, it is determined to be the recommended optimization level and the subquery rewriting process is triggered, rewriting the IN (subquery) or EXISTS (subquery) structure in the original SQL into a JOIN relational form.

7. The method according to any one of claims 3 to 6, characterized in that, The rule engine supports a dynamic update mechanism: During initialization, a hierarchical rule configuration file is loaded from a distributed configuration center. The configuration file is in YAML or JSON format. The configuration file hash value is checked for changes at preset time intervals T (default 30 minutes). If a change is found, the new rule is hot-loaded. The AI ​​big data model automatically adjusts the threshold parameters M1, C1, and T based on feedback data from historical optimization cases. The adjustment algorithm adopts the Q-learning model in reinforcement learning, and the reward function r = (percentage improvement in performance after optimization - misjudgment rate × penalty coefficient β).

8. An automatic SQL analysis and optimization system, characterized in that, include: The SQL capture module is used to capture SQL query statements that are processed by the database connection pool and finally sent to the database server in real time through interceptors deployed in the database driver layer. The SQL parsing module integrates the GenericTokenParser, XNodeParser, and SqlSourceBuilder classes from the Apache MyBatis framework. It is used to convert captured dynamic SQL statements into static executable SQL text and generate BoundSql objects, as well as to obtain the corresponding complete execution plan information. The AI ​​analysis module includes a customizable and configurable large-scale artificial intelligence model interface, which is used to receive the static SQL text and execution plan information, and to perform in-depth analysis of the performance of the SQL query statements. The diagnostic push module is used to generate a diagnostic report based on the analysis results of the AI ​​analysis module according to the severity classification rules, and to automatically push the diagnostic report to a preset receiving terminal through an enterprise-level communication interface.

9. The SQL automatic analysis and optimization system according to claim 8, characterized in that, include: The SQL capture module uses the JDBC interface to call the EXPLAIN command of the database management system in real time to obtain execution plan information. The execution plan information is parsed into a structured data format containing type, rows, and Extra fields. The SQL parsing module is configured to recognize dynamic tag structures in the MyBatis mapping file, the dynamic tags including <if> 、 <foreach>and <where>Label the parameter placeholders #{} and ${} with the actual parameter values;< / where> < / foreach> < / if> The diagnostic push module has a built-in severity classification engine, which is implemented through a rule engine and includes judgment rules for three levels of optimization: urgent optimization, important optimization, and recommended optimization, based on the comparison of thresholds in the execution plan fields.

10. A computer-readable storage medium or electronic device, characterized in that, The storage medium stores a computer program, or the electronic device includes a processor and a memory, wherein the computer program, when executed by the processor, implements the steps of the method according to any one of claims 1 to 7.

Images