Method and device for rapidly collecting and analyzing slow query reason of MySQL database in Java
By using MyBatis interceptors to build business logic and data collection threads on the Java program side, slow query information from the MySQL database is actively collected, solving the problems of inaccurate data collection and excessive noise in existing technologies, and achieving information-rich and efficient slow query analysis.
Patent Information
- Application Number
- CN202511136003.2
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-08-14
- Publication Date
- 2025-11-28
AI Technical Summary
Existing methods for collecting slow query logs from MySQL databases are inaccurate, provide limited information, and are noisy, making it difficult to accurately locate business SQL queries. Third-party monitoring solutions suffer from complex deployment and inconsistent information.
On the Java program side, use MyBatis interceptors to build business threads and data collection threads to actively collect query information whose execution time exceeds a threshold, obtain the SQL execution plan and table creation information, and obtain detailed information through EXPLAIN and show create table, and output it to logs, emails or notifications.
It enables accurate collection of slow SQL information on the Java program side, reduces noise, provides rich information, is suitable for analyzing unreasonable SQL issues, does not affect performance, is easy to use, and does not require modification of the original project code.
Smart Images
Figure CN121029554A_ABST
Abstract
Description
TECHNICAL FIELD
[0001] The present application relates to the technical field of database analysis, in particular to a method and device for quickly collecting and analyzing reasons for slow query of MySQL database in Java. BACKGROUND
[0002] In today's rapid digitization, databases have become the "data hub" of industries such as finance, e-commerce, healthcare, and education. Business expansion and volume growth have made databases must carry higher concurrency, larger capacity, and faster response. The resulting hidden dangers are increasingly prominent. Slow query, deadlock, and transaction blocking can significantly reduce system response speed. Due to the complex structure of the database and the variable external load, these hidden dangers are often difficult to predict. Only by continuously and accurately monitoring its operating indicators can we ensure that the system always operates efficiently and stably.
[0003] MySQL is one of the most popular open-source relational database management systems in the world. In MySQL database, SQL is the "universal language" for communicating with the database, and its execution speed directly determines the performance of the entire system. Complex SQL statements can easily become slow SQL that slows down performance and affects the overall performance of the database system.
[0004] Traditional slow SQL collection is usually through the slow query log provided by MySQL, which has the following limitations: 1) Inaccurate: Each different business has different standards, such as analyzing business that requires a large amount of data and allowing a larger time for querying. However, the slow query log can only configure a uniform execution time threshold. 2) Not rich: The slow query log only has a small amount of information such as SQL and execution time. Additional information needs to be collected manually for analysis. 3) High noise: The slow query log is collected by MySQL database for all SQL. Different SQLs may correspond to multiple business programs, making it difficult to pinpoint the business. If a third-party program monitoring solution is used, there are more problems, such as: 1) Need to deploy additional monitoring programs; 2) Need to collect all SQL; 3) Cannot correspond to specific business; 4) Difficult to implement; 5) Still has the problems of the MySQL slow query log solution. SUMMARY
[0005] The technical problem to be solved by the present application is to provide a method and device for quickly collecting and analyzing reasons for slow query of MySQL database in Java. By actively collecting and analyzing slow query information and reasons of MySQL database in Java program, the collected information is more accurate, the information content is more abundant, and the noise is less, which is very suitable for analyzing database SQL unreasonable problems.
[0006] Firstly, this invention provides a method for quickly collecting and analyzing the causes of slow queries in a MySQL database using Java, integrated into the Java program, including:
[0007] A business thread is constructed, which is implemented using the MyBatis database access framework interceptor to handle query methods whose execution time exceeds a specified threshold.
[0008] A data collection thread is constructed. When triggered by a business thread, it collects the execution plan and table creation information from the database based on the SQL information passed by the business thread, and then outputs the results.
[0009] Furthermore, the business thread uses a custom MyBatis interceptor to execute query SQL, obtain the execution method name, execution time, and execution SQL statement; when the execution time exceeds the threshold, the execution method name and execution SQL statement are sent to the collection thread to trigger collection.
[0010] Furthermore, the acquisition thread obtains the SQL execution plan by executing EXPLAIN[sql] and obtains table creation information, including the tables used in the SQL, table structure, and indexes, by executing show create table.
[0011] Furthermore, the information collected by the acquisition thread is output to logs, email notifications, telephone notifications, and / or work group notifications.
[0012] Secondly, the present invention provides a device for quickly collecting and analyzing the causes of slow queries in a MySQL database in Java, which is integrated into the Java program and includes a business thread module and a collection thread module.
[0013] The business thread module is implemented using the MyBatis database access framework interceptor to handle query methods whose execution time exceeds a specified threshold.
[0014] When the data acquisition thread module is triggered by the business thread, it collects the execution plan and table creation information from the database based on the SQL information passed by the business thread and then outputs it.
[0015] Furthermore, the business thread module uses a custom MyBatis interceptor to execute query SQL, obtain the execution method name, execution time, and execution SQL statement; when the execution time exceeds the threshold, the execution method name and execution SQL statement are sent to the collection thread to trigger collection.
[0016] Furthermore, the acquisition thread obtains the SQL execution plan by executing EXPLAIN[sql] and obtains table creation information, including the tables used in the SQL, table structure, and indexes, by executing show create table.
[0017] Furthermore, the information collected by the acquisition thread is output to logs, email notifications, telephone notifications, and / or work group notifications.
[0018] The technical solutions provided in the embodiments of the present invention have at least the following technical effects:
[0019] 1. Integrated into various Java programs, it can set a specified time threshold according to the program's needs to accurately collect information on slow SQL queries; the collected information is rich, including execution methods, SQL statements, execution plans, table creation information, etc., basically covering all the information that the program needs to care about; at the same time, it only collects SQL queries used in the current Java program, so there is less noise.
[0020] 2. It does not affect performance and only outputs a small amount of information, typically only about 10 pieces of collected information per day.
[0021] 3. Easy to use, no need to modify the original project code, just import the extension dependency package.
[0022] 4. Information is easy to view; it can be directly output to log files, email notifications, phone notifications, and work group notifications.
[0023] The above description is merely an overview of the technical solution of the present invention. In order to better understand the technical means of the present invention and to implement it in accordance with the contents of the specification, and in order to make the above and other objects, features and advantages of the present invention more apparent and understandable, specific embodiments of the present invention are described below. Attached Figure Description
[0024] The present invention will be further described below with reference to the accompanying drawings and embodiments.
[0025] Figure 1 This is an overall flowchart of the method in Embodiment 1 of the present invention;
[0026] Figure 2 This is a flowchart illustrating the execution process of the method in Embodiment 1 of the present invention;
[0027] Figure 3 This is a schematic diagram of the device in Embodiment 2 of the present invention. Detailed Implementation
[0028] This invention provides a method and apparatus for quickly collecting and analyzing the causes of slow queries in MySQL databases using Java. By actively collecting and analyzing information and causes of slow queries in MySQL databases on the Java program side, the collected information is more accurate, richer in content, and has less noise, making it very suitable for analyzing problems such as unreasonable SQL in databases.
[0029] The overall concept of the technical solutions in the embodiments of the present invention is as follows:
[0030] 1. Implement an interceptor using the database access framework MyBatis;
[0031] 2. Based on internal Java program processing, it uses a shared database connection pool, resulting in low resource consumption;
[0032] 3. Asynchronous parsing, without affecting the main business;
[0033] 4. The program directly obtains SQL information, related table information, and relevant database execution plans. The information is accurate, and there is no need to query other database information to analyze abnormal situations effectively.
[0034] Example 1
[0035] This embodiment provides a method for quickly collecting and analyzing the causes of slow queries in a MySQL database using Java, integrated into the Java program, such as... Figure 1 As shown, the method includes:
[0036] S1. Construct a business thread, which is implemented using the MyBatis database access framework interceptor to handle query methods whose execution time exceeds the threshold.
[0037] S2. Construct a data collection thread. When triggered by a business thread, it collects the execution plan and table creation information from the database based on the SQL information passed by the business thread and then outputs the results.
[0038] This method integrates with each Java application, rather than querying the database itself. It allows setting specific time thresholds based on the needs of each application, accurately collecting information on slow SQL queries. For example, a larger time threshold can be specified for Java applications performing analytical tasks, or for those performing consumer-facing (TO C) tasks. Multiple Java applications can share the same database. Because this method is integrated into the Java applications, different time thresholds can be specified. Furthermore, it only collects SQL queries used within the current Java application, resulting in less noise. The collected information is comprehensive, including execution methods, SQL statements, execution plans, and table creation information—essentially covering all the information the application needs, eliminating the need for manual collection of additional information.
[0039] In one specific embodiment, the execution flow is as follows: Figure 2 As shown:
[0040] The business thread uses a custom MyBatis interceptor to execute query SQL, obtain the execution method name, execution time, and execution SQL statement; when the execution time exceeds the threshold, the execution method name and execution SQL statement are sent to the collection thread to trigger collection.
[0041] The data collection thread obtains the SQL execution plan by executing `EXPLAIN[sql]` and retrieves table creation information, including the tables used in the SQL, their structure, and indexes, by executing `show createtable`. The information collected by the thread is output to a log, allowing analysts to perform analysis and optimization based solely on the information provided.
[0042] The information collected by the collection thread can also be output in other custom ways, such as email notifications, phone notifications, and work group notifications. This solves the problem of difficulty in extending abnormal alarms when querying through the slow query log provided by MySQL, such as the inability to promptly notify relevant personnel when a large number of slow queries occur.
[0043] Based on the same inventive concept, this application also provides an apparatus corresponding to the method in Embodiment 1, as detailed in Embodiment 2.
[0044] Example 2
[0045] This embodiment provides a device for quickly collecting and analyzing the causes of slow queries in a MySQL database using Java, integrated into the Java program, such as... Figure 3 As shown, it includes: a business thread module and a data acquisition thread module;
[0046] The business thread module is implemented using the MyBatis database access framework interceptor to handle query methods whose execution time exceeds a specified threshold.
[0047] When the data acquisition thread module is triggered by the business thread, it collects the execution plan and table creation information from the database based on the SQL information passed by the business thread and then outputs it.
[0048] Specifically, the business thread module uses a custom MyBatis interceptor to execute query SQL, obtain the execution method name, execution time, and execution SQL statement; when the execution time is higher than the threshold, the execution method name and execution SQL statement are sent to the collection thread to trigger collection.
[0049] Specifically, the acquisition thread obtains the execution plan of the SQL by executing EXPLAIN[sql] and obtains the table creation information, including the tables, table structure and indexes used in the SQL, by executing showcreate table.
[0050] Preferably, the information collected by the acquisition thread is output to a log file, email notification, telephone notification, and / or work group notification.
[0051] Since the apparatus described in Embodiment 2 of the present invention is an apparatus used to implement the method of Embodiment 1 of the present invention, those skilled in the art can understand the specific structure and variations of the apparatus based on the method described in Embodiment 1 of the present invention, and therefore will not be described again here. All apparatuses used in the method of Embodiment 1 of the present invention fall within the scope of protection of the present invention.
[0052] The technical solutions provided in the embodiments of the present invention have at least the following technical effects:
[0053] 1. Integrated into various Java programs, it can set a specified time threshold according to the program's needs to accurately collect information on slow SQL queries; the collected information is rich, including execution methods, SQL statements, execution plans, table creation information, etc., basically covering all the information that the program needs to care about; at the same time, it only collects SQL queries used in the current Java program, so there is less noise.
[0054] 2. It does not affect performance and only outputs a small amount of information, typically only about 10 pieces of collected information per day.
[0055] 3. Easy to use, no need to modify the original project code, just import the extension dependency package.
[0056] 4. Information is easy to view; it can be directly output to log files, email notifications, phone notifications, and work group notifications.
[0057] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, apparatus, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.
[0058] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.
[0059] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.
[0060] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.
[0061] While specific embodiments of the present invention have been described above, those skilled in the art should understand that the specific embodiments described are merely illustrative and not intended to limit the scope of the present invention. Equivalent modifications and variations made by those skilled in the art in accordance with the spirit of the present invention should be covered within the scope of protection of the claims of the present invention.
Claims
1. A method for quickly collecting and analyzing the causes of slow queries in a MySQL database using Java, characterized in that... Integrated into the Java program, including: A business thread is constructed, which is implemented using the MyBatis database access framework interceptor to handle query methods whose execution time exceeds a specified threshold. A data collection thread is constructed. When triggered by a business thread, it collects the execution plan and table creation information from the database based on the SQL information passed by the business thread, and then outputs the results.
2. The method according to claim 1, characterized in that: The business thread uses a custom MyBatis interceptor to execute query SQL, obtain the execution method name, execution time, and execution SQL statement; when the execution time exceeds the threshold, the execution method name and execution SQL statement are sent to the collection thread to trigger collection.
3. The method according to claim 1, characterized in that: The data collection thread obtains the SQL execution plan by executing EXPLAIN[sql] and obtains table creation information, including the tables used in the SQL, table structure, and indexes, by executing show create table.
4. The method according to claim 1 or 3, characterized in that: The information collected by the acquisition thread is output to logs, email notifications, telephone notifications, and / or work group notifications.
5. A device for quickly collecting and analyzing the causes of slow queries in a MySQL database using Java, characterized in that, It is integrated into the Java program and includes a business thread module and a data acquisition thread module; The business thread module is implemented using the MyBatis database access framework interceptor to handle query methods whose execution time exceeds a specified threshold. When the data acquisition thread module is triggered by the business thread, it collects the execution plan and table creation information from the database based on the SQL information passed by the business thread and then outputs it.
6. The apparatus according to claim 5, characterized in that: The business thread module uses a custom MyBatis interceptor to execute query SQL, obtain the execution method name, execution time, and execution SQL statement; when the execution time exceeds the threshold, the execution method name and execution SQL statement are sent to the collection thread to trigger collection.
7. The apparatus according to claim 5, characterized in that: The data collection thread obtains the SQL execution plan by executing EXPLAIN[sql] and obtains table creation information, including the tables used in the SQL, table structure, and indexes, by executing show create table.
8. The apparatus according to claim 5 or 7, characterized in that: The information collected by the acquisition thread is output to logs, email notifications, telephone notifications, and / or work group notifications.