Slow SQL code positioning method, device and system and storage medium

Abstract

The invention discloses a slow SQL code positioning method, device and system and a storage medium, and the method comprises the steps: configuring an SQL monitoring module in a target business system, so as to generate slow SQL log information under the condition that the SQL query time consumption of the target business system exceeds a threshold value; acquiring slow SQL log information, analyzing the slow SQL log information, and determining target analysis information; according to the version control information and the code positioning information, positioning a code line of the target code of the corresponding version, and obtaining submission information of the code line, the submission information comprising a code submission object; adding comment information in the code line, wherein the comment information comprises part or all of the target analysis information; in response to the adding operation of the comment information, a prompt message is sent to the code submission object, and the prompt message comprises part or all of the comment information. Therefore, the slow SQL can be detected, the corresponding code line can be automatically positioned, the comment information is added in the code line, the code submission object is notified in time, and the timeliness and convenience of maintenance can be improved.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a method, device, system and storage medium for locating slow SQL codes. Background Art

[0002] In current software development and operations systems, database query performance directly impacts the overall efficiency of applications, and slow SQL is a key factor contributing to performance issues. Slow SQL queries, which take a long time to execute, are not identified as errors by business systems. Therefore, they rely solely on regular database performance analysis tools or manual spot checks for analysis. Existing slow SQL processing technologies suffer from the following drawbacks: insufficient real-time monitoring, low location efficiency, difficulty tracing code versions, delayed notification mechanisms, and inconvenient code maintenance. Summary of the Invention

[0003] The present invention aims to address at least one of the technical problems existing in the prior art. To this end, the present invention provides a method, device, system, and storage medium for locating slow SQL statements. These methods can detect slow SQL statements and automatically locate the corresponding lines of code, add comments to the lines of code, and promptly notify the code submitter, thereby improving the timeliness and convenience of maintenance.

[0004] In a first aspect, an embodiment of the present invention provides a method for locating slow SQL codes, including: An SQL monitoring module configured in the target business system for monitoring SQL query duration is used to generate slow SQL log information when the SQL query duration of the target business system exceeds a preset threshold; Obtain the slow SQL log information, and parse the slow SQL log information to determine target parsing information, where the target parsing information includes version control information of the code repository and code location information of the target code; Locating a code line of a target code of a corresponding version according to the version control information and the code location information, and obtaining submission information of the code line, the submission information including a code submission object; Calling the comment function interface of the code repository to add comment information to the code line, wherein the comment information includes part or all of the target parsed information; In response to the adding operation of the comment information, a prompt message is sent to the code submission object, where the prompt message includes part or all of the comment information.

[0005] According to some embodiments of the present invention, configuring an SQL monitoring module in the target business system for monitoring the time consumed by SQL queries includes: During the development phase of the target business system, integrate the SQL monitoring module for monitoring SQL query time consumption and configure the threshold for slow SQL queries.

[0006] According to some embodiments of the present invention, generating slow SQL log information when the SQL query time of the target business system exceeds a preset threshold includes: In response to an SQL query operation of the target business system, recording a start time and context information of the SQL query operation; When the SQL query operation is completed, determining the time consumption of the SQL query operation according to the start time and the end time of the SQL query operation; Based on an asynchronous thread, whether the SQL query operation is a slow SQL query is determined according to the duration of the SQL query operation and a preset threshold, and if the SQL query operation is determined to be a slow SQL query, slow SQL log information is generated according to context information of the SQL query operation.

[0007] According to some embodiments of the present invention, during the development phase of the target business system, integrating a SQL monitoring module for monitoring SQL query time consumption and configuring a threshold for slow SQL queries may further include: During the construction and release phase of the target business system, configuration is performed through a construction tool to embed metadata of the code repository in the construction product. The metadata of the code repository includes the code repository address and version control information.

[0008] According to some embodiments of the present invention, locating a code line of a target code of a corresponding version according to the version control information and the code location information includes: Access the corresponding code repository according to the preset code repository address or the code repository address obtained by parsing the slow SQL log information; According to the version control information, calling the API interface of the code repository to query the code snapshot of the corresponding version; According to the code location information and the code snapshot, the code line of the target code is located and the submission information of the code line is obtained.

[0009] According to some embodiments of the present invention, the target parsing information further includes slow SQL prompt information, and adding comment information to the code line includes: Comment information is generated based on a preset comment template and the slow SQL prompt information, and the comment information is added to the code line, where the comment information includes multiple of the slow SQL prompt information, version metadata, and intelligent solutions.

[0010] According to some embodiments of the present invention, adding comment information to the code line further includes at least one of the following: Generate a code hyperlink for the code line according to the preset code repository address and the code location information, and add the code hyperlink to the comment information; Generate a version hyperlink of the target code according to the code repository address and the version control information, and add the version hyperlink to the comment information; Obtain a log hyperlink of the slow SQL log information, and add the log hyperlink to the comment information.

[0011] According to some embodiments of the present invention, in response to the adding operation of the comment information, sending a prompt message to the code submission object includes: In response to the adding operation of the comment information, the comment information is obtained and a prompt message is generated according to the comment information, the prompt message including multiple of slow SQL prompt information, the version control information, the code location information, log details and navigation links; The preset notification service interface is called to send the prompt message to the code submission object.

[0012] In a second aspect, an embodiment of the present invention provides a slow SQL code location device, including: A configuration module is configured to configure an SQL monitoring module for monitoring SQL query time in the target business system, so as to generate slow SQL log information when the SQL query time of the target business system exceeds a preset threshold; A log parsing module is used to obtain the slow SQL log information, parse the slow SQL log information, and determine target parsing information, where the target parsing information includes version control information of the code repository and code location information of the target code; A code locating module, configured to locate a code line of a target code of a corresponding version according to the version control information and the code locating information, and obtain submission information of the code line, wherein the submission information includes a code submission object; A comment adding module, configured to call the comment function interface of the code repository and add comment information to the code line, wherein the comment information includes part or all of the target parsed information; The message notification module is used to send a prompt message to the code submission object in response to the adding operation of the comment information, where the prompt message includes part or all of the comment information.

[0013] In a third aspect, an embodiment of the present invention provides a slow SQL code locating system, including a processor and a memory, wherein the memory stores a computer program, and the processor is used to implement the above-mentioned slow SQL code locating method when running the computer program.

[0014] In a fourth aspect, an embodiment of the present invention provides a storage medium, wherein the storage medium stores a computer program, and when the computer program is executed, the above-mentioned slow SQL code location method is implemented.

[0015] The embodiments of the present invention have at least the following beneficial effects: A SQL monitoring module is configured in the target business system to monitor the time consumed by SQL queries, so as to generate slow SQL log information when the time consumed by the SQL queries of the target business system exceeds a preset threshold; the slow SQL log information is obtained and parsed to determine the target parsing information, which includes the version control information of the code repository and the code location information of the target code; the code line of the target code of the corresponding version is located according to the version control information and the code location information, and the submission information of the code line is obtained, and the submission information includes the code submission object; the comment function interface of the code repository is called to add comment information to the code line, and the comment information includes part or all of the target parsing information; in response to the comment information addition operation, a prompt message is sent to the code submission object, and the prompt message includes part or all of the comment information. In this way, slow SQL can be detected and the corresponding code line can be automatically located, comment information can be added to the code line, and the code submission object can be notified in a timely manner, which is conducive to improving the timeliness and convenience of maintenance.

[0016] Additional aspects and advantages of the present invention will be set forth in part in the description which follows and, in part, will be obvious from the description which follows, or may be learned by practice of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] The above and / or additional aspects and advantages of the present invention will become apparent and readily understood from the following description of the embodiments with reference to the accompanying drawings, in which: Figure 1 This is a flowchart of the steps of the method for locating slow SQL codes according to an embodiment of the present invention; Figure 2 A schematic diagram of a prompt message according to an embodiment of the present invention; Figure 3 This is a functional block diagram of a device for locating slow SQL codes according to an embodiment of the present invention; Figure 4 This is an example functional block diagram of a slow SQL code location system according to an embodiment of the present invention; Figure 5 This is an interactive flow chart of the slow SQL code location system according to an embodiment of the present invention. DETAILED DESCRIPTION

[0018] The following describes embodiments of the present invention in detail. Examples of the embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals throughout represent the same or similar elements or elements having the same or similar functions. The embodiments described below with reference to the accompanying drawings are exemplary and are intended only to explain the present invention and are not to be construed as limiting the present invention.

[0019] In the description of the present invention, "several" means one or more, "multiple" means more than two, "greater than," "less than," and "exceed" are understood to exclude the number itself, and "above," "below," and "within" are understood to include the number itself. The use of terms such as "first" and "second" is solely for the purpose of distinguishing technical features and should not be construed as indicating or implying relative importance, or implicitly specifying the number of the indicated technical features, or implicitly specifying the order of the indicated technical features.

[0020] In current software development and operations systems, database query performance directly impacts the overall operational efficiency of applications. For example, SQL databases are relational database management systems based on the Structured Query Language (SQL). Slow SQL is a key factor contributing to performance issues. Existing slow SQL processing technologies have the following drawbacks: Insufficient real-time monitoring: Traditional slow SQL detection relies on regular database performance analysis tools or manual spot checks. This fails to monitor slow SQL queries generated during application execution in real time, resulting in performance issues not being discovered in a timely manner, impacting user experience and business operations. Low location efficiency: It is extremely difficult to locate the specific code location that generates slow SQL from the massive amount of application logs. Developers need to spend a lot of time and energy to troubleshoot, which increases the cost and cycle of problem repair. Difficulty tracing code versions: After slow SQL occurs, due to iterative code updates, it is difficult to quickly trace the code version status where the slow SQL occurs. This not only hinders the complete resolution of the problem but also hinders subsequent optimization work. Lagging notification mechanism: Even if a slow SQL problem is discovered, the error information cannot be promptly and accurately notified to the relevant developers and responsible persons, making it difficult to quickly resolve the problem and further exacerbating the impact of performance issues. Inconvenient code maintenance: Existing technical solutions can often only locate slow SQL issues, but code optimization still requires developers to troubleshoot the problem.

[0021] Please refer to Figure 1This embodiment discloses a method for locating slow SQL code, including steps S100 to S500. It should be noted that the steps in this embodiment are numbered only for ease of review and understanding, and do not limit the order in which the steps are executed. The following details the contents of each step: S100: configuring an SQL monitoring module in the target business system for monitoring SQL query duration, so as to generate slow SQL log information when the SQL query duration of the target business system exceeds a preset threshold; Exemplarily, the target business system is the system to be monitored, such as an application running in a multi-language environment (such as Java, Python, Node.js), which interacts with the database through database access operations. Slow SQL queries refer to SQL queries or operations that take a long time to execute, which are usually not judged as runtime errors by the target business system. Therefore, slow SQL will not cause the target business system to generate alarm information or error log information. In order to be able to monitor the slow SQL of the target business system in real time, a SQL monitoring module is configured in the target business system. For example, during the development phase of the target business system, an SQL monitoring module is integrated, such as a slow SQL monitoring SDK (Software Development Kit), and a threshold for slow SQL queries is configured, such as 500ms. When the target business system performs an SQL query operation, the SQL monitoring module starts timing to determine the SQL query time; when the SQL query time exceeds the threshold, the SQL monitoring module automatically generates slow SQL log information and uploads the slow SQL log information to the log system.

[0022] In some application examples, after configuring the SQL monitoring module, the following also applies: During the build and release phase of the target business system, configuration is performed through a build tool, which can be Maven, Feiliu, or k8s, to embed metadata about the code repository in the build product, such as the code repository address (URL, a unique address identifier) ​​and version control information. For example, in a specific application example, the code repository uses a GitLab repository, and the version control information of the code repository can be CommitId. CommitId is a unique identifier generated by the GitLab repository each time the code is submitted, which is used to track version changes. Of course, in actual applications, different code repositories can be used, or one or more code repositories can be configured, with different metadata configured for different code repositories.

[0023] S200: Obtain slow SQL log information of a target business system, parse the slow SQL log information, and determine target parsing information, where the target parsing information includes version control information of a code repository and code location information of a target code; For example, slow SQL log information can be obtained through a logging system. An example of a logging system is Alibaba Cloud's Log Service (SLS), which offers high reliability, scalability, and powerful data processing capabilities. It can stably store massive amounts of log information and provide efficient data retrieval, query, and monitoring functions, providing a data storage foundation for subsequent analysis and processing of slow SQL logs. WebHook technology can be used to collect slow SQL log information for further processing. A WebHook is a method for adding or changing the presentation of a web page through a custom callback function. In other words, a WebHook is a URL used to receive HTTP POST (or other request methods such as GET, PUT, and DELETE).

[0024] The recorded content of the slow SQL log information covers the project name, operating environment, metadata of the code repository, code location information, and slow SQL prompt information, among which the code location information can be obtained from the stack information. The code location information includes the file path and line number. The file path can be the method name of the function module or the file name of the function module. The slow SQL prompt information includes information such as query time, occurrence time, and SQL statements. By parsing the slow SQL log information, the required content can be extracted from the slow SQL log information to obtain the target parsing information, so as to facilitate subsequent code location and add comment information. By combining the slow SQL log with the metadata and code location information of the code repository, it is possible to extract key information from the slow SQL log information and automatically locate it to a specific line of code, which is conducive to improving the efficiency of slow SQL code location, saving slow SQL troubleshooting time, and reducing the probability of errors in troubleshooting.

[0025] S300, locating a code line of a target code of a corresponding version according to the version control information and the code location information, and obtaining the submission information of the code line, the submission information including the code submission object; For example, during the software development process, the target business system usually has multiple different iterative versions. Even for the same version, there may be multiple iterative updates between different modules of the system, resulting in multiple different versions of code in the code repository. If the code location of the slow SQL is manually located, it takes a lot of time and effort, and it is easy to have version positioning errors, resulting in the problem of not being able to accurately locate the corresponding code line. This embodiment locates the code line of the target code of the corresponding version based on the version control information and code positioning information in the target parsing information, and the positioning efficiency and accuracy are relatively high. After locating the code line of the target code, the commit information of the code line can be obtained. For example, in the GitLab warehouse, the commit information (Commit) of the code line can be queried using the git blame API interface, thereby querying the commit record of the line of code in the historical version, and then obtaining detailed commit information, such as the code submission object and submission time, etc., to facilitate determining the person who handles the code maintenance.

[0026] S400: Call the comment function interface of the code repository to add comment information to the code line, where the comment information includes part or all of the target parsed information; For example, for code maintenance methods that utilize log information, it is usually necessary to switch back and forth between the code repository and the log system, which is inconvenient for maintenance personnel to view the code and log information. The code repository has its own comment function. By adding comment information to the code line, the target parsing information extracted based on the slow SQL log information can be associated with the code line, making it convenient for maintenance personnel to view content related to the slow SQL log information during the code maintenance process. In some application examples, the content of the comment information may include slow SQL core information, navigation links, version metadata, and intelligent solutions, among which the slow SQL core information can be extracted based on the target parsing information, such as the query time, occurrence time, and SQL statements in the slow SQL prompt information; the navigation link provides a code hyperlink to the code line and a log hyperlink to the slow SQL log information, making it convenient for maintenance personnel to quickly locate and view related content; the version metadata records information such as version control information, commit description, and commit time, which facilitates version tracing; the intelligent solution is a targeted solution suggestion generated based on the SQL statement in the slow SQL log information to improve the efficiency of code maintenance. In addition, there may be multiple versions of code in the code repository. Adding comment information to the code lines can serve as error markers and prompts. Maintenance personnel can determine the number and location of erroneous code lines by viewing the comment information in the same code, so as to facilitate unified modification. If no comment information is found in the code lines in the opened code, it means that the current version of the code is inconsistent with the version of the erroneous code, which can serve as a prompt to avoid inconsistent code versions during the maintenance process, which is conducive to improving the correctness of code maintenance.

[0027] S500: In response to the comment information adding operation, a prompt message is sent to the code submission object, where the prompt message includes part or all of the comment information.

[0028] For example, by monitoring the comment events of the code repository in real time, when a comment information is added to a code line, the API interface of the notification service is called to send a prompt message to the code submission object, and the information is promptly delivered to the relevant responsible person. The prompt message includes part or all of the comment information. For example, the prompt message includes version control information, code location information and slow SQL prompt information. For example, the prompt message also includes navigation links, such as code hyperlinks for code lines and log hyperlinks for slow SQL log information. Maintenance personnel can jump to the corresponding code line or slow SQL log details page by clicking the navigation link for easy viewing.

[0029] Through the above solution, slow SQL can be detected and the corresponding code line can be automatically located. Comment information can be added to the code line, and the code submission object can be notified in a timely manner, which is conducive to improving the timeliness and convenience of maintenance.

[0030] In step S100, when the SQL query time of the target business system exceeds a preset threshold, slow SQL log information is generated, including: In response to an SQL query operation on the target business system, record the start time and context information of the SQL query operation; When the SQL query operation is completed, the time consumption of the SQL query operation is determined according to the start time and the end time of the SQL query operation; Based on an asynchronous thread, whether the SQL query operation is a slow SQL query is determined according to the duration of the SQL query operation and a preset threshold. If the SQL query operation is determined to be a slow SQL query, slow SQL log information is generated according to the context information of the SQL query operation.

[0031] Exemplarily, the SQL monitoring module can monitor the SQL query operations of the target business system and determine whether the SQL query operations are slow SQL queries based on preset thresholds. It is worth mentioning that the judgment steps of the SQL query operations and the generation of slow SQL log information are all executed based on asynchronous threads, which can avoid affecting the SQL query operations of the target business system and help ensure the operating performance of the target business system. Below, taking the SDK of the Java technology stack as an example, the DataSource of Java Database Connectivity (JDBC) (a standardized database connection management interface that improves performance and resource utilization through connection pool technology) can proxy the SQL query operations of the target business system, thereby monitoring the execution process of the SQL query operations. Among them, before the SQL query operation is executed, the context information of the SQL query operation is recorded through a custom class. For example, in the following code: protected static class RunningQueryContext { protected AomiSlowSQLException aomiSlowSQLException; protected ExecutionInfo executionInfo; protected List <queryinfo>queryInfoList; protected Stopwatch stopwatch; } The executionInfo field represents SQL query execution information, such as the start time, user, and SQL statement. The queryInfoList field represents the currently executing SQL query. The stopwatch field represents a timer that records query duration. The aomiSlowSQLException field records code stack information, such as the line number, which can be used to locate the code location. After the SQL query completes, the stopwatch field is used to determine the duration of the SQL query to determine whether it exceeds a preset threshold. After the SQL query completes, an asynchronous task is created. In this asynchronous thread, the duration of the SQL query exceeds the threshold and a slow SQL log is generated to avoid blocking the main thread of the target business system. The slow SQL log information can include the line number, start time, user, and SQL statement. Depending on the analysis requirements, the slow SQL log information can include some or all of these elements.

[0032] In step S200, the slow SQL log information is parsed to determine target parsing information, including: Based on preset regular expressions or structured log templates, slow SQL log information is parsed to obtain target parsing information, which includes version control information, code location information, and slow SQL prompt information.

[0033] For example, some content of slow SQL log information is as follows: 2025-06-12 10:30:00 [1a2b3c4d] ERROR [Service] A slow query occurred mo.aomi.tools.slowsql.AomiSlowSQLException: Time:1341, SQL:["SELECT *FROM user WHERE id=?"] at EmployeeWorkLogMapper.java:45 The preset regular expressions can be used to match specific fields in the slow SQL log information to extract relevant information. For example, the target parsing content is shown in Table 1: Table 1 Among them, version control information can be represented by the commitId field; code location information can be a combination of file path and line number, and slow SQL prompt information includes query time, occurrence time and SQL statement. It should be noted that the above only shows one example of slow SQL log information. Depending on the programming language, the slow SQL log information generated by the target business system may be different. For example, for stack information, the Java language uses the class name + method name + line number method, while the Python language uses Traceback information. For slow SQL log information in different programming languages, different regular expressions can be constructed, or structured log templates can be used to parse the slow SQL log information, so as to adapt to different programming languages ​​and accurately extract the corresponding content.

[0034] In step S300, locating the code line of the target code of the corresponding version according to the version control information and the code location information includes: Access the corresponding code repository based on the preset code repository address or the code repository address parsed from slow SQL log information; Based on the version control information, call the code repository API to query the code snapshot of the corresponding version; Based on the code location information and code snapshot, locate the code line of the target code and obtain the commit information of the code line.

[0035] For example, in some application instances, the target business system has embedded metadata of the code repository during the construction and release phase. In this way, when slow SQL log information is generated, the slow SQL log information carries the code repository address and the version control information of the target code. According to the code repository address, the corresponding code repository can be accessed. Of course, in other application examples, the code repository address can be configured in the log system, so that the log system can access the code repository according to the pre-configured code repository address. Version control information, such as commitId, is parsed from the slow SQL log information, and the API interface of the code repository can be called to query the code snapshot of the corresponding version code. When using version control information for querying, a cache acceleration strategy can be adopted, such as a cache acceleration strategy based on the Redis database, to avoid repeated queries of historical commitIds. For fuzzy paths, such as when the class name cannot uniquely locate the file path, a path push based on a Git index or code retrieval service can be used. The Git index is a binary file located at .git / index that acts as a buffer between the working directory and the repository, recording the current state of stored files (file names, permissions, hash values, etc.). A code snapshot is created with each code commit to the repository. A snapshot records the state of a file or directory at a specific point in time. Each commit records a snapshot of all files in the current working directory, enabling efficient and reliable recording and retrieval of file history, thus providing strong version control capabilities. Using code location information (such as file path and line number) and code snapshots, you can quickly locate the target code line and retrieve the commit information for that line. By embedding repository metadata in slow SQL log information and parsing the slow SQL log information, you can automatically locate the target code line across different versions of the repository, achieving automatic, accurate, and timely code location.

[0036] In some application examples, step S400, adding comment information to a line of code, includes: Generates comment information based on preset comment templates and slow SQL prompt information, and adds comment information to the code line. The comment information includes slow SQL prompt information, version metadata, and multiple intelligent solutions.

[0037] For example, as mentioned above, the slow SQL prompt information includes query time, occurrence time and SQL statements. According to actual application requirements, relevant content can be extracted from the slow SQL prompt information based on the comment template to generate comment information. For example, the comment information includes multiple of the query time, occurrence time and SQL statements for maintenance personnel to view. For example, version metadata records information such as version control information, submission description and submission time, which makes it convenient for maintenance personnel to view the version of the target code to determine the solution. In other application examples, the comment information also includes intelligent solutions, such as based on large models + RAG (retrieval enhanced generation) technology, integrating internal corporate coding standards, historical error cases and technical documents from the entire network to generate targeted solution suggestions, thereby reducing the difficulty of code maintenance.

[0038] In some other application examples, adding comment information to a code line also includes at least one of the following: Generate a code hyperlink for the code line based on the preset code repository address and code location information, and add the code hyperlink to the comment information; Generate a version hyperlink of the target code based on the code repository address and version control information, and add the version hyperlink to the comment information; Get the log hyperlink of the slow SQL log information and add the log hyperlink to the comment information.

[0039] Exemplarily, a code hyperlink can be a combination of code repository address + file path + line number. By generating a code hyperlink and adding the code hyperlink to the comment information, maintenance personnel can click on the code hyperlink in the comment information to jump to the corresponding code line when viewing the comment information, thereby quickly locating the code line of slow SQL. Adding a version hyperlink in the comment information can associate the code line and the code version, making it easier to locate the corresponding code version; similarly, adding a log hyperlink in the comment information can jump to the corresponding slow SQL log details page by clicking on the log hyperlink, thereby improving the convenience of viewing the slow SQL log. In addition, adding a code hyperlink, a version hyperlink, and an error log hyperlink in the comment information at the same time can directly associate the code line, code version, and error log information, which is conducive to improving the integration of multiple types of information, ensuring that maintenance personnel can accurately receive scattered slow SQL log information, code version, and code line information, and reducing information errors caused by jumping between different information display interfaces.

[0040] Step S500, in response to the comment information adding operation, sends a prompt message to the code submission object, including: In response to an operation of adding comment information, obtaining the comment information and generating a prompt message according to the comment information, the prompt message including multiple of slow SQL prompt information, version control information, code location information, log details, and navigation links; Call the preset notification service interface to send the prompt message to the code submission object.

[0041] For example, based on the WebHook function provided by the code repository, the comment event of the code line is monitored, and in response to the adding operation of the comment information, the comment information is obtained and a prompt message is generated according to the comment information. For example, an example of a prompt message is as follows: Figure 2 As shown, the prompt message includes slow SQL prompt information, such as query time, log time and SQL statements. The prompt message also includes version control information (such as Commit) and code location information, such as file location. The prompt message also includes log details, so that maintenance personnel can quickly view slow SQL information. The prompt message can also include navigation links, such as code hyperlinks at the file location, version hyperlinks at the commit location, and log hyperlinks at the log details location. By clicking the corresponding navigation link, you can quickly jump to the corresponding location, improving the convenience of code maintenance. The notification service supports multiple different platforms, such as enterprise WeChat, Feishu or DingTalk communication platforms. By calling the preset notification service interface, you can send prompt messages to the code submission object, and it can also support multi-terminal push, such as supporting multi-channel notification methods such as desktop, mobile and email.

[0042] This embodiment of the present invention integrates a slow SQL monitoring module with a logging system. Unlike traditional methods that rely on periodic analysis or manual spot checks, this embodiment achieves real-time capture of slow SQL logs. Acting as a database access proxy, the slow SQL monitoring module can be flexibly integrated into target business systems based on various languages ​​(such as Java, Python, and Node.js). It detects slow SQL queries in real time using preset thresholds and generates slow SQL logs containing key information. Combined with a reliable logging system, this ensures that slow SQL issues are exposed without delay, significantly improving the timeliness and accuracy of monitoring.

[0043] This embodiment of the present invention also integrates log analysis with the code repository API, solving the previous problem of manually locating slow SQL code locations from massive log files. By parsing slow SQL log information based on programming language characteristics and combining it with code repository metadata, the corresponding line of code can be located by calling the code repository API and tracing the code's historical commit history. This enables automatic and precise association of slow SQL log information with code versions and specific locations, significantly improving troubleshooting efficiency.

[0044] This embodiment of the present invention also builds a notification system based on the GitLab Hook mechanism and integrates it with internal collaboration platforms (such as WeChat for Business, Lark, or DingTalk) to deliver accurate, real-time notifications about slow SQL issues, resolving the issue of delayed notifications. Furthermore, by introducing large-scale modeling and RAG technology, combined with internal enterprise standards and network-wide technical resources, it automatically generates targeted optimization suggestions, forming an intelligent closed-loop "discover-notify-fix" process, achieving breakthroughs in both notification efficiency and problem-solving capabilities.

[0045] This embodiment of the present invention also uses the commitId of the code repository to closely associate the code version at the time of the slow SQL occurrence, throughout the entire process from log collection and code location to problem remediation. This not only facilitates and quickly traces the root cause of the problem, but also provides strong support for code version management, ensuring the integrity and traceability of version information during project development and operations.

[0046] Please refer to Figure 3 The embodiment of the present invention further provides a slow SQL code location device, including: Configuration module 110, configured to configure an SQL monitoring module for monitoring SQL query duration in the target business system, so as to generate slow SQL log information when the SQL query duration of the target business system exceeds a preset threshold; The log parsing module 120 is configured to obtain the slow SQL log information, parse the slow SQL log information, and determine target parsing information, where the target parsing information includes version control information and code location information. A code locating module 130 is configured to locate a code line of a target code of a corresponding version according to the version control information and the code locating information, and obtain submission information of the code line, wherein the submission information includes a code submission object; A comment adding module 140, configured to add comment information to the code line, wherein the comment information includes part or all of the target parsed information; The message notification module 150 is configured to send a prompt message to the code submission object in response to the adding operation of the comment information, where the prompt message includes part or all of the comment information.

[0047] It should be noted that the inventive concept of this slow SQL code locating device is the same as the inventive concept of the aforementioned slow SQL code locating method embodiment. Any details not covered in this slow SQL code locating device embodiment can be referred to the aforementioned slow SQL code locating method embodiment and will not be repeated here. This slow SQL code locating device embodiment can detect slow SQL and automatically locate the corresponding line of code, add comment information to the line of code, and promptly notify the code submitter, thereby improving the timeliness and convenience of maintenance.

[0048] Please refer to Figure 4 An embodiment of the present invention further provides a slow SQL code location system, comprising a processor 210 and a memory 220. The memory 220 stores a computer program, and when the processor 210 runs the computer program, it is used to implement the above-mentioned slow SQL code location method. The details of the slow SQL code location method can be found above and will not be repeated here. The slow SQL code location system embodiment can detect slow SQL and automatically locate the corresponding code line, add comment information to the code line, and promptly notify the code submission object, which is conducive to improving the timeliness and convenience of maintenance.

[0049] Please refer to Figure 5 In some application examples, the slow SQL code location system can be divided into a log system and a slow SQL collection system. The log system is used to receive the slow SQL log information uploaded by the target business system and transmit the slow SQL log information to the slow SQL log collection system. The slow SQL log collection system is used to parse the slow SQL log information and call the API interface of the code repository to locate the code line and add comment information to the corresponding code line. The code repository is used to provide code storage services of different versions, wherein the code repository is configured with a notification system. The notification system is used to monitor the comment events of the code repository and send notification messages to the corresponding maintenance personnel in response to the addition of comment information. For example, the log system can adopt Alibaba Cloud's log service, the code repository adopts the GitLab repository, and the notification system is based on the GitLab Hook function and integrates multiple communication platforms.

[0050] An embodiment of the present invention provides a storage medium storing a computer program that, when executed, implements the aforementioned method for locating slow SQL code. The details of the method for locating slow SQL code are described above and are not further elaborated here. This storage medium embodiment can detect slow SQL and automatically locate the corresponding line of code, add comments to the line of code, and promptly notify the code submitter, thereby improving the timeliness and convenience of maintenance.

[0051] The embodiments of the present invention are described in detail above with reference to the accompanying drawings. However, the present invention is not limited to the above embodiments. Various changes can be made within the knowledge of ordinary technicians in the relevant technical field without departing from the scope of the present invention.< / queryinfo>

Claims

1. A method for locating slow SQL codes, characterized in that: include: An SQL monitoring module configured in the target business system for monitoring SQL query duration is used to generate slow SQL log information when the SQL query duration of the target business system exceeds a preset threshold; Obtain the slow SQL log information, and parse the slow SQL log information to determine target parsing information, where the target parsing information includes version control information of the code repository and code location information of the target code; Locating a code line of a target code of a corresponding version according to the version control information and the code location information, and obtaining submission information of the code line, the submission information including a code submission object; Calling the comment function interface of the code repository to add comment information to the code line, wherein the comment information includes part or all of the target parsed information; In response to the adding operation of the comment information, a prompt message is sent to the code submission object, where the prompt message includes part or all of the comment information.

2. The method for locating slow SQL codes according to claim 1, wherein: The SQL monitoring module configured in the target business system for monitoring the time consumed by SQL queries includes: During the development phase of the target business system, integrate the SQL monitoring module for monitoring SQL query time consumption and configure the threshold for slow SQL queries.

3. The method for locating slow SQL codes according to claim 1 or 2, characterized in that: The generating of slow SQL log information when the SQL query time of the target business system exceeds a preset threshold includes: In response to an SQL query operation of the target business system, recording a start time and context information of the SQL query operation; When the SQL query operation is completed, determining the time consumption of the SQL query operation according to the start time and the end time of the SQL query operation; Based on an asynchronous thread, whether the SQL query operation is a slow SQL query is determined according to the duration of the SQL query operation and a preset threshold, and if the SQL query operation is determined to be a slow SQL query, slow SQL log information is generated according to context information of the SQL query operation.

4. The method for locating slow SQL codes according to claim 2, wherein: During the development phase of the target business system, a SQL monitoring module for monitoring SQL query time consumption is integrated and a threshold for slow SQL queries is configured. The following steps are then included: During the construction and release phase of the target business system, configuration is performed through a construction tool to embed metadata of the code repository in the construction product. The metadata of the code repository includes the code repository address and version control information.

5. The method for locating slow SQL codes according to claim 1, wherein: The locating the code line of the target code of the corresponding version according to the version control information and the code location information includes: Access the corresponding code repository according to the preset code repository address or the code repository address obtained by parsing the slow SQL log information; According to the version control information, the API interface of the code repository is called to query the code snapshot of the corresponding version; According to the code location information and the code snapshot, the code line of the target code is located and the submission information of the code line is obtained.

6. The method for locating slow SQL codes according to claim 1, wherein: The target parsing information also includes slow SQL prompt information, and the comment information added to the code line includes: Comment information is generated based on a preset comment template and the slow SQL prompt information, and the comment information is added to the code line, where the comment information includes multiple of the slow SQL prompt information, version metadata, and intelligent solutions.

7. The method for locating slow SQL codes according to claim 6, wherein: Adding comment information to the code line further includes at least one of the following: Generate a code hyperlink for the code line according to the preset code repository address and the code location information, and add the code hyperlink to the comment information; Generate a version hyperlink of the target code according to the code repository address and the version control information, and add the version hyperlink to the comment information; Obtain a log hyperlink of the slow SQL log information, and add the log hyperlink to the comment information.

8. A slow SQL code location device, characterized in that: include: A configuration module is configured to configure an SQL monitoring module for monitoring SQL query time in the target business system, so as to generate slow SQL log information when the SQL query time of the target business system exceeds a preset threshold; A log parsing module is used to obtain the slow SQL log information, parse the slow SQL log information, and determine target parsing information, where the target parsing information includes version control information of the code repository and code location information of the target code; A code locating module, configured to locate a code line of a target code of a corresponding version according to the version control information and the code locating information, and obtain submission information of the code line, wherein the submission information includes a code submission object; A comment adding module, configured to call the comment function interface of the code repository and add comment information to the code line, wherein the comment information includes part or all of the target parsed information; The message notification module is used to send a prompt message to the code submission object in response to the adding operation of the comment information, where the prompt message includes part or all of the comment information.

9. A slow SQL code location system, comprising a processor and a memory, wherein the memory stores a computer program, characterized in that: When the processor runs the computer program, it is used to implement the slow SQL code location method according to any one of claims 1 to 7.

10. A storage medium storing a computer program, wherein: When the computer program is executed, the slow SQL code location method according to any one of claims 1 to 7 is implemented.

Images