Micro-service slow SQL (Structured Query Language) monitoring analysis method

Abstract

The invention provides a micro-service slow SQL (Structured Query Language) monitoring analysis method which comprises the following steps: S1, capturing a slow SQL statement through an interceptor Mybatis, obtaining a fingerprint SQL to be analyzed, a real SQL and SQL execution time through interface parameters of the interceptor Mybatis, and sending the fingerprint SQL, the real SQL and the SQL execution time to an analyzer; s2, after obtaining a fingerprint SQL to be analyzed, a real SQL and SQL execution time, the analyzer judges whether the fingerprint SQL is analyzed or not; and the analysis result is associated with the link log, so that the specific request and service instance can be traced back. Therefore, when performance troubleshooting and optimization are carried out, accurate positioning and deep analysis of problems can be realized in combination with log information of the whole process of the request, automatic capture and intelligent analysis of slow SQL statements are realized, problem positioning efficiency and system maintainability are improved through link tracking and an email alarm mechanism, and the method and the system are suitable for popularization and application. And the method has good engineering practice value and popularization significance.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and more specifically, to a microservice slow SQL monitoring and analysis method.

[0002] In modern software development, database performance has a crucial impact on overall system efficiency. Especially with the widespread adoption of microservices architectures, SQL performance issues have become increasingly complex and difficult to troubleshoot. With the expansion of business scale and the decomposition of service modules, database access patterns are characterized by high concurrency and multiple interface calls. SQL execution efficiency is directly related to system response speed and resource utilization.

[0003] However, in actual R&D and O&M, SQL performance monitoring and analysis still face numerous challenges. Most existing database performance monitoring tools can only identify "slow SQL statements" (i.e., SQL statements whose execution time exceeds a set threshold) and lack the ability to visually track the entire SQL execution process. While this monitoring approach can identify potential performance bottlenecks, it lacks sufficient context to identify the root cause, such as missing indexes, illogical query logic, or poorly designed table structures.

[0004] Furthermore, most current SQL monitoring tools are significantly disconnected from microservices architectures. Microservices typically route requests through API gateways, involving interoperable calls between multiple services. SQL, a key component of the data access layer, often hides its execution behind the service call chain, making it difficult to correlate specific API requests or service instances. This forces R&D personnel to compare and manually analyze logs across multiple systems when performance issues arise, significantly increasing troubleshooting costs and time.

[0005] Therefore, there is an urgent need for a monitoring and analysis tool that can intuitively display SQL execution performance and deeply integrate with the microservice call chain. Summary of the Invention

[0006] The technical problem to be solved by the present invention is to provide a microservice slow SQL monitoring and analysis method that improves development and operation efficiency in response to the shortcomings of the above technical solutions.

[0007] The present invention provides a microservice slow SQL monitoring and analysis method, the method comprising the following steps:

[0008] S1, captures slow SQL statements through the interceptor Mybatis, obtains the fingerprint SQL to be analyzed, the real SQL and the SQL execution time through the interface parameters of the interceptor Mybatis, and sends the fingerprint SQL, the real SQL and the SQL execution time to the analyzer;

[0009] S2: After obtaining the fingerprint SQL to be analyzed, the real SQL, and the SQL execution time, the analyzer determines whether the fingerprint SQL has been analyzed before, analyzes the real SQL execution status by executing the explain statement, and evaluates the index usage. It determines whether the slow SQL statement is slowed down due to index problems based on the type column value, records the analysis results in the database, and sends the analysis results to the alarm device.

[0010] S3, the alarm device obtains the analysis results, exports the analysis results through the POI component and sends an alarm email to the configured relevant personnel mailbox.

[0011] In the microservice slow SQL monitoring and analysis method of the present invention, step S1 includes the following steps:

[0012] S11, obtain the fingerprint SQL to be analyzed through the interface parameters of the interceptor Mybatis, and use the configured regular expression to match the fingerprint SQL. If the fingerprint SQL matches successfully, terminate the subsequent operations.

[0013] In the microservice slow SQL monitoring and analysis method of the present invention, step S1 further includes the following steps:

[0014] S12, set a configuration threshold, start a timer to record the SQL execution time, compare the SQL execution time with the preset configuration threshold to determine whether it is a slow SQL statement, and terminate subsequent operations if the SQL execution time is less than the preset configuration threshold.

[0015] In the microservice slow SQL monitoring and analysis method described in the present invention, in step S1, the interface parameters include the link ID and the service name.

[0016] In the microservice slow SQL monitoring and analysis method of the present invention, step S1 further includes the following steps:

[0017] S13, sending the link ID, service name, fingerprint SQL to be analyzed, actual execution SQL and SQL execution time to the analyzer.

[0018] In the microservice slow SQL monitoring and analysis method of the present invention, step S2 includes the following steps:

[0019] S21, the analyzer receives the link ID, service name, fingerprint SQL to be analyzed, actual execution SQL and SQL execution time from step S13, and determines whether the fingerprint SQL has been analyzed. If so, the analysis process ends; if not, the actual execution SQL is parsed through the MySQL explain command.

[0020] In the microservice slow SQL monitoring and analysis method of the present invention, step S2 further includes the following steps:

[0021] S22, analyzing the type column of each row in the returned analysis result. If the value of the type column is ALL or INDEX, it is considered that the problem is caused by an unqualified index, and the analysis result is recorded in the database.

[0022] In the microservice slow SQL monitoring and analysis method of the present invention, step S3 includes the following steps:

[0023] S31, import the analysis results into an Excel table through the POI component, and report the analysis results to relevant personnel by email, so as to timely discover and solve potential performance problems.

[0024] The present invention's microservice slow SQL monitoring and analysis method can trace analysis results back to specific requests and service instances by associating them with link logs. This allows for precise problem location and in-depth analysis during performance troubleshooting and optimization, combining log information from the entire request process. This not only enables automatic capture and intelligent analysis of slow SQL statements, but also improves problem location efficiency and system maintainability through link tracking and email alert mechanisms, demonstrating excellent engineering practice and potential for widespread adoption. BRIEF DESCRIPTION OF THE DRAWINGS

[0025] Figure 1 It is a flowchart of the microservice slow SQL monitoring and analysis method of the present invention. DETAILED DESCRIPTION

[0026] In order to make the purpose, technical solutions and advantages of the present invention more clearly understood, the present invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not intended to limit the present invention.

[0027] It should be noted that the terms "first," "second," and the like in the description and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects and are not necessarily used to describe a specific order or precedence. It should be understood that the numbers used in this manner are interchangeable where appropriate so that the embodiments of the present invention described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having," as well as any variations thereof, are intended to cover non-exclusive inclusions. For example, a process, method, application, product, or apparatus that includes a series of steps or units is not necessarily limited to those steps or units that are explicitly listed, but may include other steps or units that are not explicitly listed or that are inherent to these processes, methods, products, or apparatus.

[0028] like Figure 1 As shown, Figure 1 This is a flow chart of an embodiment of a method for monitoring and analyzing slow SQL statements in microservices according to the present invention. A method for monitoring and analyzing slow SQL statements in microservices is provided, comprising the following steps:

[0029] In step S1, the slow SQL statement is captured by the interceptor Mybatis, the fingerprint SQL to be analyzed, the real SQL and the SQL execution time are obtained through the interface parameters of the interceptor Mybatis, and the fingerprint SQL, the real SQL and the SQL execution time are sent to the analyzer;

[0030] In step S2, after obtaining the fingerprint SQL to be analyzed, the real SQL and the SQL execution time, the analyzer determines whether the fingerprint SQL has been analyzed, and analyzes the real SQL execution status by executing the explain statement. At the same time, it evaluates the index usage and determines whether the slow SQL statement is slowed down due to index problems based on the type column value. The analysis results are recorded in the database and sent to the alarm device.

[0031] In step S3, the alarm device obtains the analysis results, exports the analysis results through the POI component, and sends an alarm email to the configured relevant personnel mailbox.

[0032] In the microservice slow SQL monitoring and analysis method of the present invention, step S1 includes the following steps:

[0033] In step S11, the fingerprint SQL to be analyzed is obtained through the interface parameters of the interceptor Mybatis, and the configured regular expression is used to match the fingerprint SQL. If the fingerprint SQL is matched successfully, the subsequent operations are terminated.

[0034] In the microservice slow SQL monitoring and analysis method of the present invention, step S1 further includes the following steps:

[0035] In step S12, a configuration threshold is set, and a timer is started to record the SQL execution time. The SQL execution time is compared with the preset configuration threshold to determine whether it is a slow SQL statement. If it is less than the preset configuration threshold, subsequent operations are terminated.

[0036] In the microservice slow SQL monitoring and analysis method described in the present invention, in step S1, the interface parameters include the link ID and the service name.

[0037] In the microservice slow SQL monitoring and analysis method of the present invention, step S1 further includes the following steps:

[0038] In step S13, the link ID, service name, fingerprint SQL to be analyzed, actual execution SQL and SQL execution time are sent to the analyzer.

[0039] In the microservice slow SQL monitoring and analysis method of the present invention, step S2 includes the following steps:

[0040] In step S21, the analyzer receives the link ID, service name, fingerprint SQL to be analyzed, actual execution SQL and SQL execution time from step S13, and determines whether the fingerprint SQL has been analyzed. If it has been analyzed, the analysis process ends; if it has not been analyzed, the actual execution SQL is parsed through the MySQL explain command.

[0041] In the microservice slow SQL monitoring and analysis method of the present invention, step S2 further includes the following steps:

[0042] In step S22, the type column of each row in the returned analysis result is analyzed. If the value of the type column is ALL or INDEX, it is considered that the problem is caused by an unqualified index, and the analysis result is recorded in the database.

[0043] In the microservice slow SQL monitoring and analysis method of the present invention, step S3 includes the following steps:

[0044] In step S31, the analysis results are imported into an Excel table through the POI component, and the Excel table containing the analysis results is reported to relevant personnel by email, so as to timely discover and solve potential performance problems.

[0045] By executing the above process, we can identify slow SQL statements with overly large result sets or index misses during operation. By deeply integrating the analysis results with the link logs, we can accurately link them to specific requests and services. This allows us to conduct precise location analysis based on the complete request process logs during performance troubleshooting and optimization. This correlation mechanism helps operations and maintenance personnel quickly identify the business scenarios where slow SQL statements occur. By combining the contextual information in the request link, we can gain a more comprehensive and in-depth understanding of the causes of performance bottlenecks, significantly improving the efficiency of problem diagnosis and resolution, and providing strong support for system performance optimization.

[0046] Specifically, a fingerprint SQL statement is a processed string that uniquely identifies a slow SQL statement. Fingerprint SQL statements are matched using a configured regular expression to prevent repeated analysis of common SQL statements.

[0047] The interceptor utilizes extension points provided by MyBatis to monitor and collect data on the execution of slow SQL statements. The analyzer uses the "type" column in the returned results to determine whether there are indexing issues and provides optimization recommendations accordingly. The alerter uses the POI component to generate Excel files and send emails, ensuring that detailed analysis reports on slow SQL statements are promptly delivered to relevant technical personnel or management personnel, enabling the timely identification and resolution of potential performance issues.

[0048] Among them, take explain analysis as an example:

[0049] Type: Access type. Performance is ranked from best to worst as follows: system > const > eq_ref > ref > range > INDEX > ALL. "const" indicates a primary key / unique index query, and "ALL" indicates a full table scan. INDEX also performs poorly, so both "ALL" and "INDEX" are considered substandard indexes and should be logged and optimized. For example, by running "EXPLAIN select * from t_order" in the database, you can see that the index type in the query "table: t_order" is ALL. By checking this column, it is determined that the index is a full table scan. Index misses require logging and optimization.

[0050] Specifically, this application achieves end-to-end tracking from entry requests to database operations by binding the actual SQL execution to specific service interface requests; it can not only identify slow SQL statements, but also analyze key indicators such as execution plans, resource consumption, lock waits, etc.; it associates slow SQL statements with meta-information such as the service that calls it, thread, user request ID, etc.; based on historical data and execution characteristics, it automatically recommends optimization strategies, such as adding indexes, rewriting queries, etc.; and provides a graphical interface to help R&D and operation and maintenance personnel quickly understand SQL behavior and its impact on system performance.

[0051] This method automatically inserts monitoring code during SQL execution by intercepting the Mybatis interface. This allows for real-time and accurate acquisition of the fingerprint SQL to be analyzed, the actual SQL, and the SQL execution time. This ensures that all slow SQL statements exceeding the threshold are captured, significantly improving efficiency compared to manual troubleshooting. Furthermore, by matching fingerprint SQL statements with regular expressions, this method avoids repeated analysis of common SQL statements, reduces ineffective computations, and improves system efficiency.

[0052] The analyzer uses explain statements to deeply analyze actual SQL execution. Combined with an evaluation of the type column value, it can pinpoint index usage issues and determine whether poor indexing is causing slow SQL execution, providing a reliable basis for optimizing database performance. It also records analyzed fingerprint SQL statements to avoid repeated analysis, conserve system resources, and improve analysis efficiency.

[0053] The alarm device exports the analysis results into a standardized Excel spreadsheet through the POI component, making it easy for operation and maintenance personnel to view and analyze them. It also automatically sends alarm emails to designated mailboxes to ensure that relevant personnel can obtain slow SQL information and its causes in a timely manner, respond quickly, reduce the impact of slow SQL on system performance and user experience, and ensure stable and efficient system operation.

[0054] This application organically integrates the functions of slow SQL statement capture, analysis, and alarm to form a complete automated monitoring closed loop, reduce manual intervention, lower operation and maintenance costs, improve the intelligence level of system management, and enhance the maintainability and stability of the system.

[0055] The beneficial effects of the microservice slow SQL monitoring and analysis method provided by the embodiment of the present invention are at least:

[0056] 1. The present invention improves overall performance and detects problems in advance;

[0057] 2. The present invention simplifies the difficulty of finding problems by finding specific services based on link IDs.

[0058] It should be noted that for the aforementioned method embodiments, for simplicity of description, they are all expressed as a series of action combinations. However, those skilled in the art should be aware that the present invention is not limited by the order of the actions described, because according to the present invention, certain steps can be performed in other orders or simultaneously. Secondly, those skilled in the art should also be aware that the embodiments described in this specification are all preferred embodiments, and the actions and modules involved are not necessarily required by the present invention.

[0059] Through the description of the above embodiments, those skilled in the art can clearly understand that the method according to the above embodiment can be implemented by means of software plus the necessary general hardware platform, and of course it can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of the present invention is essentially or the part that contributes to the prior art can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), and includes a number of instructions for enabling a terminal device (which can be a mobile phone, computer, server, or network device, etc.) to execute the methods described in each embodiment of the present invention.

[0060] Therefore, the above is only a preferred specific embodiment of the present invention, and the scope of protection of the present invention is not limited to this. Any changes or replacements that can be easily thought of by any technician familiar with this technical field within the technical scope disclosed by the present invention should be covered within the scope of protection of the present invention. The scope of protection of the present invention should be based on the scope of protection of the claims.

Claims

1. A microservice slow SQL monitoring and analysis method, characterized in that: The method comprises the following steps: S1, captures slow SQL statements through the interceptor Mybatis, obtains the fingerprint SQL to be analyzed, the real SQL and the SQL execution time through the interface parameters of the interceptor Mybatis, and sends the fingerprint SQL, the real SQL and the SQL execution time to the analyzer; S2: After obtaining the fingerprint SQL to be analyzed, the real SQL, and the SQL execution time, the analyzer determines whether the fingerprint SQL has been analyzed before, analyzes the real SQL execution status by executing the explain statement, and evaluates the index usage. It determines whether the slow SQL statement is slowed down due to index problems based on the type column value, records the analysis results in the database, and sends the analysis results to the alarm device. S3, the alarm device obtains the analysis results, exports the analysis results through the POI component and sends an alarm email to the configured relevant personnel mailbox.

2. The microservice slow SQL monitoring and analysis method according to claim 1 is characterized in that: The step S1 comprises the following steps: S11, obtain the fingerprint SQL to be analyzed through the interface parameters of the interceptor Mybatis, and use the configured regular expression to match the fingerprint SQL. If the fingerprint SQL matches successfully, terminate the subsequent operations.

3. The microservice slow SQL monitoring and analysis method according to claim 2 is characterized in that: The step S1 further comprises the following steps: S12, set a configuration threshold, start a timer to record the SQL execution time, compare the SQL execution time with the preset configuration threshold to determine whether it is a slow SQL statement, and terminate subsequent operations if the SQL execution time is less than the preset configuration threshold.

4. The microservice slow SQL monitoring and analysis method according to claim 2 is characterized in that: In step S1, the interface parameters include link ID and service name.

5. The microservice slow SQL monitoring and analysis method according to claim 4 is characterized in that: The step S1 further comprises the following steps: S13, sending the link ID, service name, fingerprint SQL to be analyzed, actual execution SQL and SQL execution time to the analyzer.

6. The microservice slow SQL monitoring and analysis method according to claim 5 is characterized in that: The step S2 comprises the following steps: S21, the analyzer receives the link ID, service name, fingerprint SQL to be analyzed, actual execution SQL and SQL execution time from step S13, and determines whether the fingerprint SQL has been analyzed. If so, the analysis process ends; if not, the actual execution SQL is parsed through the MySQL explain command.

7. The microservice slow SQL monitoring and analysis method according to claim 6 is characterized in that: The step S2 further comprises the following steps: S22, analyzing the type column of each row in the returned analysis result. If the value of the type column is ALL or INDEX, it is considered that the problem is caused by an unqualified index, and the analysis result is recorded in the database.

8. The microservice slow SQL monitoring and analysis method according to claim 7 is characterized in that: The step S3 comprises the following steps: S31, import the analysis results into an Excel table through the POI component, and report the analysis results to relevant personnel by email, so as to timely discover and solve potential performance problems.

Images