Processing method, device and apparatus of SQL statement, and storage medium

Abstract

The application provides a SQL statement processing method, device and equipment and a storage medium, and relates to the technical field of database processing. In the application, after an operation request of a database is acquired, a target SQL statement is generated based on the operation request; target fingerprint information is generated based on the target SQL statement; when the target fingerprint information matches fingerprint information of a target monitoring SQL statement, the target SQL statement is processed based on an execution duration of the target monitoring SQL statement, the target monitoring SQL statement is an SQL statement in at least one monitoring SQL statement, and the execution duration of each monitoring SQL statement is greater than a first preset duration. In the application, the target SQL statement is identified as a slow SQL statement before execution, so that a processing strategy of the target SQL statement is dynamically determined, secondary execution of a slow SQL of the same type as the target monitoring SQL statement is avoided, and the risk of expansion of a database system exception is reduced.

Description

Technical Field

[0001] This invention relates to the field of database processing technology, and in particular to a method, apparatus, device, and storage medium for processing SQL statements. Background Technology

[0002] In database systems, the execution efficiency of Structured Query Language (SQL) is one of the key factors affecting system performance. With the continuous growth of data volume and the increasing complexity of business processes, the problem of slow SQL has become increasingly prominent. These slow SQL statements can lead to database system response delays, excessive resource consumption, and even system anomalies. Slow SQL statements are defined as those whose execution time exceeds a preset duration. For example, in e-commerce promotions, flash sales, and limited-time offers, due to the high demands on database performance, SQL statements whose execution time exceeds 5 seconds are considered slow SQL.

[0003] In existing technologies, after SQL statements are executed, slow SQL statements are usually filtered out and displayed so that operations and maintenance personnel can analyze them to find the cause of database system anomalies and optimize accordingly.

[0004] However, in the above method, since only the slow SQL statements that have been executed are analyzed, there is still a risk of the database system anomaly expanding during the execution of subsequent SQL statements. Summary of the Invention

[0005] This invention provides a method, apparatus, device, and storage medium for processing SQL statements, which addresses the shortcomings of existing technologies where the risk of database system anomalies still exists during the execution of subsequent SQL statements, thereby reducing the risk of database system anomalies.

[0006] This invention provides a method for processing SQL statements, including: After obtaining the database operation request, generate the target SQL statement based on the operation request; Generate target fingerprint information based on the target SQL statement; If the target fingerprint information matches the fingerprint information of the target monitoring SQL statement, the target SQL statement is processed based on the execution time of the target monitoring SQL statement. The target monitoring SQL statement is an SQL statement in at least one monitoring SQL statement, and each monitoring SQL statement is an SQL statement with an execution time greater than a first preset time.

[0007] According to a method for processing SQL statements provided by the present invention, the step of processing the target SQL statement based on the execution time of the target monitoring SQL statement includes: If the execution time of the target monitoring SQL statement exceeds a second preset time, the execution of the target SQL statement shall be prohibited. If the execution time of the target monitoring SQL statement is within a preset time range, the target SQL statement is executed, where the maximum time within the preset time range is less than or equal to the second preset time.

[0008] According to a method for processing SQL statements provided by the present invention, the preset duration range includes a first sub-duration range and a second sub-duration range, wherein the maximum duration in the first sub-duration range is less than the minimum duration in the second sub-duration range; The step of executing the target SQL statement when its execution time is within a preset time range includes: If the execution time of the target monitoring SQL statement is within the first sub-time range, then the target SQL statement is executed. If the execution duration of the target monitoring SQL statement is within the second sub-duration range and an authorization identifier is obtained, the target SQL statement is executed. The authorization identifier is an identifier sent based on a preset period to allow the execution of the SQL statement.

[0009] According to a method for processing SQL statements provided by the present invention, the method further includes: For each of the monitoring SQL statements, a prompt message is generated based on the monitoring SQL statement. The prompt message is used to indicate the output of the review result for the monitoring SQL statement. The review result includes the performance review score of the monitoring SQL statement and / or the optimization strategy of the monitoring SQL statement. The prompt information and the monitoring SQL statement are input into the large language model to obtain the review result output by the large language model.

[0010] According to a method for processing SQL statements provided by the present invention, generating target fingerprint information based on the target SQL statement includes: Based on the target SQL statement, an operation triplet is obtained, which includes the operation type of the database, the table name corresponding to the operation type, and the operation condition field. The target fingerprint information is generated based on the operation type of the database, the table name corresponding to the operation type, and the operation condition field.

[0011] According to a method for processing SQL statements provided by the present invention, the method further includes: Based on the operation request, determine the identification information of the terminal that sent the operation request and the business interface information called when obtaining the operation request; Add comment information to the target SQL statement. The comment information includes the identification information of the sending terminal and the business interface information.

[0012] According to a method for processing SQL statements provided by the present invention, adding comment information to the target SQL statement includes: If it is determined that the target SQL statement will be executed, the comment information is added to the target SQL statement.

[0013] According to a method for processing SQL statements provided by the present invention, the comment information further includes a tracking identifier for the operation request, wherein the tracking identifier is an identifier used to track the operation request within the operation business process to which the operation request belongs.

[0014] The present invention also provides an apparatus for processing SQL statements, comprising: The generation module is used to obtain an operation request for the database and generate a target SQL statement based on the operation request. The generation module is also used to generate target fingerprint information based on the target SQL statement; The processing module is used to process the target SQL statement based on the execution time of the target monitoring SQL statement when the target fingerprint information matches the fingerprint information of the target monitoring SQL statement. The target monitoring SQL statement is an SQL statement in at least one monitoring SQL statement, and each monitoring SQL statement is an SQL statement with an execution time greater than a first preset time.

[0015] The present invention also provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the processing method of any of the SQL statements described above.

[0016] The present invention also provides a non-transitory computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the SQL statement processing method described above.

[0017] The present invention also provides a computer program product, including a computer program that, when executed by a processor, implements the SQL statement processing method described above.

[0018] The present invention provides a method, apparatus, device, and storage medium for processing SQL statements. After obtaining an operation request for a database, a target SQL statement is generated based on the operation request, and target fingerprint information is generated based on the target SQL statement. If the target fingerprint information matches the fingerprint information of a target monitoring SQL statement, the target SQL statement is processed based on its execution duration. The target monitoring SQL statement is an SQL statement among at least one monitoring SQL statement, and each monitoring SQL statement has an execution duration greater than a first preset duration. Because the target fingerprint information of the target SQL statement can be matched with the fingerprint information of each monitoring SQL statement before the target SQL statement is executed, potential SQL statements with execution durations greater than the first preset duration can be identified before the target SQL statement is executed. Based on the execution duration of the target monitoring SQL statement, the processing strategy for the target SQL statement is dynamically adjusted, achieving timely interception of the target SQL statement's execution. This avoids secondary execution of slow SQL statements of the same type as the target monitoring SQL statement, reducing the risk of escalating database system anomalies during subsequent SQL statement execution. Attached Figure Description

[0019] To more clearly illustrate the technical solutions in this invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of this invention. For those skilled in the art, other drawings can be obtained from these drawings without creative effort.

[0020] Figure 1 This is one of the flowcharts illustrating the SQL statement processing method provided in this embodiment of the invention.

[0021] Figure 2 This is the second flowchart illustrating the SQL statement processing method provided in this embodiment of the invention.

[0022] Figure 3 This is the third flowchart illustrating the SQL statement processing method provided in this embodiment of the invention.

[0023] Figure 4 This is a schematic diagram of the structure of the SQL statement processing device provided in an embodiment of the present invention.

[0024] Figure 5 This is a schematic diagram of the physical structure of an electronic device provided in an embodiment of the present invention. Detailed Implementation

[0025] To make the objectives, technical solutions, and advantages of this invention clearer, the technical solutions of this invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, of the embodiments of this invention. All other embodiments obtained by those skilled in the art based on the embodiments of this invention without creative effort are within the scope of protection of this invention.

[0026] In modern database management systems, SQL statement performance optimization has always been a key aspect of improving overall system performance, especially in high-concurrency and large-data-volume scenarios, where slow SQL queries become a bottleneck restricting system performance. Currently, slow SQL statements can be recorded using database monitoring tools, allowing operations personnel to analyze them, identify problems, and optimize them. However, this method can only analyze slow SQL statements that have already been executed. Therefore, during the execution of subsequent SQL statements, if similar slow SQL statements exist, they will also be executed, thus still posing a risk of escalating database system anomalies.

[0027] In view of the above-mentioned problems, this invention proposes a method for processing SQL statements. In this method, the target SQL statement to be executed can be matched with the monitoring SQL statement previously recorded as slow SQL. In this way, it is possible to actively identify whether the target SQL statement is slow SQL before it is executed. When it is identified as slow SQL, the execution of the target SQL statement can be intercepted in time, thereby avoiding the secondary execution of slow SQL of the same type as the monitoring SQL statement. In the subsequent SQL statement execution process, the risk of database system anomalies can be reduced.

[0028] The following is combined with Figures 1 to 3 The SQL statement processing method provided in this embodiment of the invention is described below. This invention can be applied to any business scenario requiring database operations, such as high-concurrency scenarios like e-commerce promotions, flash sales, and limited-time offers, where a large number of users simultaneously operate on the database, easily leading to slow SQL issues; or it can be applied to scenarios requiring analysis and optimization of slow SQL statements during database performance optimization. The execution entity of this method can be an electronic device such as a terminal device, computer, server, server cluster, or a specially designed SQL statement processing device. It can also be an SQL statement processing device installed in such an electronic device, which can be implemented through software, hardware, or a combination of both.

[0029] Figure 1 This is one of the flowcharts illustrating the SQL statement processing method provided in an embodiment of the present invention, such as... Figure 1As shown, the method includes: Step 101: After obtaining the operation request for the database, generate the target SQL statement based on the operation request.

[0030] In this step, an operation request can be understood as a request sent by the user terminal or through a business interface, such as a query request initiated by the user through the terminal, or a call request between backend services. Operation requests may include, for example, database query, insert, update, or delete requests.

[0031] After receiving a database operation request, the operation request is parsed to extract the request parameters, which may include table name, operation type, and condition fields, and then the target SQL statement is generated based on the extracted request parameters.

[0032] For example, if the operation request is "retrieve the order with order number 123 from the orders table", the request parameters extracted from this operation request include: table name "orders", operation type "query", and condition field "order number 123". Therefore, based on the extracted request parameters, the target SQL statement can be generated as: SELECT FROM orders WHERE order_id =123.

[0033] Step 102: Generate target fingerprint information based on the target SQL statement.

[0034] In this step, the target fingerprint information can be understood as a summary, identifier, or key value of the target SQL statement, which is used to quickly identify and match SQL statements.

[0035] When generating target fingerprint information, the target SQL statement can be formatted first, based on its format characteristics, to remove redundant spaces and specific values, thus obtaining the formatted SQL statement. Then, a hash operation is performed on the formatted SQL statement to generate a unique identifier, which is the target fingerprint information of the target SQL statement. Hash operation is an algorithm that converts input data of arbitrary length into a fixed-length output value. Commonly used hash algorithms include Message-Digest Algorithm 5 (MD5) and Secure Hash Algorithm 1 (SHA-1).

[0036] Step 103: If the target fingerprint information matches the fingerprint information of the target monitoring SQL statement, process the target SQL statement based on the execution time of the target monitoring SQL statement. The target monitoring SQL statement is an SQL statement in at least one monitoring SQL statement, and each monitoring SQL statement is an SQL statement with an execution time greater than the first preset time.

[0037] In this step, the execution time of each SQL statement is monitored during execution. If the execution time of any SQL statement exceeds a first preset time, it is considered a slow SQL statement and is identified as one of the monitored SQL statements. This first preset time can be set based on experience or actual application scenarios. For example, in e-commerce promotions, flash sales, and limited-time offers, where database performance requirements are high, a smaller first preset time, such as 0.5 seconds, is set. Conversely, in data backup scenarios where real-time performance requirements are lower, a larger first preset time, such as 2 seconds, can be set.

[0038] After generating the fingerprint information of the monitoring SQL statement, the fingerprint information and the corresponding execution time of the monitoring SQL statement are stored in the monitoring list. Therefore, at least one monitoring SQL statement stored in the monitoring list is a slow SQL statement that has been previously executed and whose execution time exceeds a first preset time. The execution time of the SQL statement can be understood as the time required to complete the execution of the SQL statement, that is, the time from the start time of the SQL statement to the end time of the SQL statement's execution, which can be used to evaluate the performance of the SQL statement.

[0039] The generated target fingerprint information is matched with the fingerprint information of each monitoring SQL statement in the monitoring list. If the fingerprint information of the target monitoring SQL statement matches the target fingerprint information, it means that the target SQL statement has the same characteristics as the target monitoring SQL statement, and the target SQL statement may also be a slow SQL statement.

[0040] Furthermore, when the target fingerprint information successfully matches the fingerprint information of the target monitoring SQL statement, the execution time of the target monitoring SQL statement is retrieved from the monitoring list and compared with a preset threshold to determine the processing strategy for the target SQL statement based on the comparison result. The processing strategy includes directly executing the target SQL statement, executing the target SQL statement only when an authorization instruction token is obtained, or prohibiting the execution of the target SQL statement.

[0041] The SQL statement processing method provided in this embodiment of the invention obtains an operation request for the database, generates a target SQL statement based on the operation request, generates target fingerprint information based on the target SQL statement, and processes the target SQL statement based on its execution time when the target fingerprint information matches the fingerprint information of a target monitoring SQL statement. The target monitoring SQL statement is an SQL statement in at least one monitoring SQL statement, and each monitoring SQL statement has an execution time longer than a first preset time. Because the target fingerprint information of the target SQL statement can be matched with the fingerprint information of each monitoring SQL statement before the target SQL statement is executed, potential slow SQL statements can be identified before the target SQL statement is executed. Based on the execution time of the target monitoring SQL statement, the processing strategy for the target SQL statement can be dynamically adjusted, achieving the goal of timely interception of the target SQL statement's execution. This avoids the secondary execution of slow SQL statements of the same type as the target monitoring SQL statement, reducing the risk of escalating database system anomalies during subsequent SQL statement execution.

[0042] Figure 2 This is the second flowchart illustrating the SQL statement processing method provided in this embodiment of the invention. Figure 1 Based on the illustrated embodiment, the process of processing the target SQL statement in step 103 based on the execution time of the target monitoring SQL statement will be described in detail. For example... Figure 2 As shown, the method includes: Step 201: After obtaining the operation request for the database, generate the target SQL statement based on the operation request.

[0043] In this step, after receiving the operation request sent by the terminal device, the operation request will be intercepted by an interceptor or other database middleware. The request parameters in the operation request will be extracted by the interceptor or middleware. Then, a target SQL statement will be generated by combining the predefined SQL statement template with the parsed request parameters. The database operations can then be performed using the target SQL statement.

[0044] Step 202: Generate target fingerprint information based on the target SQL statement.

[0045] In this step, the target SQL statement can be formatted to remove redundant spaces and specific values. Based on this formatted SQL statement, a target fingerprint information can be generated using a hash algorithm. Alternatively, key features, such as table names, operation types, and condition fields, can be extracted from the target SQL statement. These extracted key features are then used as input to the hash algorithm to generate the target fingerprint information corresponding to the target SQL statement. This target fingerprint information can be used to characterize the features of the target SQL statement for rapid identification and matching.

[0046] Step 203: If the target fingerprint information matches the fingerprint information of the target monitoring SQL statement, and if the execution time of the target monitoring SQL statement exceeds the second preset time, the execution of the target SQL statement is prohibited.

[0047] In this step, when the target fingerprint information matches the fingerprint information of the target monitoring SQL statement, it indicates that the key characteristics of the target SQL statement and the target monitoring SQL statement are consistent, and they belong to the same type of SQL. Since the target monitoring SQL statement is a slow SQL statement, the target SQL statement may also be a slow SQL statement. When determining the processing strategy for the target SQL statement, the execution time of the target monitoring SQL statement can be used to determine whether to execute the target SQL statement.

[0048] Specifically, when the execution time of the target monitoring SQL statement exceeds the second preset duration, it indicates that the execution time of the target monitoring SQL statement is relatively long. Continuing to execute similar target SQL statements would significantly impact the performance of the database system. Therefore, to reduce the impact on database system performance, the execution of this target SQL statement will be prohibited. The second preset duration can be set based on experience or actual scenarios. For example, during e-commerce promotions, flash sales, and limited-time offers, the database faces high concurrency requests and has extremely high performance requirements. In this case, a smaller second preset duration, such as 1 second, can be set. In data backup scenarios, the database system has lower real-time requirements and allows for longer SQL execution times; therefore, a larger second preset duration, such as 5 seconds, can be set. This embodiment of the invention does not limit the specific value of the second preset duration. Setting different second preset durations according to different scenarios allows for flexible adaptation to various business needs, ensuring database performance without affecting normal business operations.

[0049] In the above method, if the execution time of the target monitored SQL statement exceeds the second preset time, the execution of the target SQL statement is prohibited. This can promptly intercept the execution of slow SQL statements and avoid the expansion of database performance bottlenecks and database anomalies.

[0050] Step 204: If the target fingerprint information matches the fingerprint information of the target monitoring SQL statement, and if the execution time of the target monitoring SQL statement is within the preset time range, execute the target SQL statement. The maximum time within the preset time range is less than or equal to the second preset time.

[0051] In this step, a preset duration range can be set, where the maximum duration is less than or equal to a second preset duration. When the target fingerprint information matches the fingerprint information of the target monitoring SQL statement, the execution duration of the target monitoring SQL statement is compared with the preset duration range to determine whether the execution duration falls within the preset duration range. For example, if the execution duration of the target monitoring SQL statement is 2.5 seconds and the preset duration range is [1 second, 3 seconds], then it can be determined that the execution duration of the target monitoring SQL statement falls within the preset duration range.

[0052] When the execution time of the target monitored SQL statement is determined to be within the preset time range, it indicates that although the target SQL statement is a slow SQL statement, continuing to execute it will not significantly impact the performance of the database system. Therefore, to ensure smooth business operations, the execution of this target SQL statement can be controlled. Similar to the second preset time range, the preset time range can also be set based on experience or actual scenarios. Setting different preset time ranges for different scenarios allows for flexible adaptation to various business needs, ensuring not only database performance but also uninterrupted normal business operations.

[0053] In the above method, the target SQL statement will continue to be executed as long as the execution time of the target monitoring SQL statement is within the preset time range. This ensures the normal operation of the business and guarantees business continuity without excessively affecting the performance of the database system.

[0054] In one possible implementation, the aforementioned preset duration range includes a first sub-duration range and a second sub-duration range, where the maximum duration in the first sub-duration range is less than the minimum duration in the second sub-duration range. When the execution duration of the target monitoring SQL statement is within the preset duration range, the execution of the target SQL statement can be performed as follows: if the execution duration of the target monitoring SQL statement is within the first sub-duration range, or if the execution duration of the target monitoring SQL statement is within the second sub-duration range and an authorization identifier has been obtained. The authorization identifier is an identifier sent based on a preset period to allow the execution of the SQL statement.

[0055] Specifically, to further reduce the impact on database system performance when executing the target SQL statement and avoid the risk of the exception continuing to escalate, the preset duration range can be further subdivided into a first sub-duration range and a second sub-duration range. The maximum duration in the first sub-duration range is less than the minimum duration in the second sub-duration range; that is, each duration in the first sub-duration range is less than each duration in the second sub-duration range.

[0056] It should be understood that although SQL statements whose execution time is within the first sub-time range are slow SQL statements, even if these SQL statements are executed subsequently, they will not have a significant impact on the performance of the database system. Therefore, if the execution time of the target monitored SQL statement is within the first sub-time range, the performance of the same type of target SQL statement is considered acceptable, and the target SQL statement can be executed directly.

[0057] SQL statements whose execution time falls within the second sub-time range are considered slow SQL, but their execution time is within an acceptable range. Although the execution of these SQL statements has a relatively small impact on the performance of the database system, it may still put some pressure on system resources in high-concurrency scenarios. Therefore, to ensure the overall performance and stability of the database system, the target SQL statement should only be executed after obtaining the authorization identifier.

[0058] The authorization identifier can be, for example, a token, which can be sent based on a preset period. This preset period can be set based on experience or actual scenarios. For instance, during e-commerce promotions, flash sales, and limited-time offers, the database faces high concurrency requests and has high performance requirements. Strict control over the execution of slow SQL statements is necessary to ensure rapid response to user needs. Therefore, a longer preset period, such as 20 minutes, can be set. In business scenarios with relatively low database load and low performance requirements, authorization identifiers can be sent more frequently to ensure normal business operation. Therefore, a shorter preset period, such as 10 minutes, can be set.

[0059] In this embodiment, by dividing the preset time range into a first sub-time range and a second sub-time range, the target SQL statement is executed when its execution time falls within the first sub-time range. This ensures that SQL statements with shorter execution times can be executed in a timely manner, thereby improving the response of the database system. When the execution time of the target SQL statement falls within the second sub-time range, the target SQL statement is executed only after obtaining an authorization identifier. In this way, by controlling the execution of SQL statements through an authorization mechanism, not only can business operations proceed normally, but the risk of continuous expansion of database anomalies can also be reduced.

[0060] For example, based on the above embodiments, in order to quickly trace the source of a slow SQL statement to the business party, the identification information of the terminal sending the operation request and the business interface information called when the operation request is made can be determined based on the operation request, and comment information can be added to the target SQL statement. The comment information includes the identification information of the sending terminal and the business interface information.

[0061] In existing technologies, for microservice scenarios, slow database logs or database platform tools cannot trace the business service interface that caused the slow SQL statement and the business party of the upstream application that called the business service interface. Even if the abnormal SQL statement is identified, the complex call relationships within the application system require manual analysis and verification, making it impossible to immediately rate-limit the SQL statement sent by the business party to protect the database.

[0062] To address the aforementioned issues, in this embodiment, upon receiving an operation request, the identification information of the sending terminal and the business interface information invoked when obtaining the operation request are determined. In one implementation, the sending terminal includes its own identification information in the operation request; thus, the identification information can be obtained by parsing the operation request. In another implementation, if the operation request does not contain the sending terminal's identification information, this information can be obtained after receiving the operation request, based on the source of the request. The identification information can be understood as a unique identifier for the sending terminal, such as its Internet Protocol (IP) address or Media Access Control (MAC) address.

[0063] Furthermore, the MyBatis framework can be used to intercept StatementHandler#prepare by implementing its Interceptor interface. Java reflection technology can then be used to modify the target SQL statement to add comment information, which includes the identification information of the sending terminal and the business interface information.

[0064] In this embodiment, comments can be added to the target SQL statement. When a problem occurs in the target SQL statement, the specific business interface and sending terminal can be quickly located, thus achieving the purpose of tracing the source of the problem to the business party.

[0065] For example, when adding comment information to the target SQL statement, the comment information can be added to the target SQL statement only after it is determined that the target SQL statement will be executed.

[0066] Specifically, when it is determined that the target SQL statement will be executed directly, or executed with the authorization token, a comment will be added to the target SQL statement. Conversely, when it is determined that the execution of the target SQL statement is prohibited, no comment will be added. This not only reduces system resource consumption but also allows for more accurate monitoring of the actually executed SQL statements and their corresponding business logic, facilitating subsequent performance analysis and optimization.

[0067] For example, the above-mentioned annotation information also includes a tracking identifier for the operation request. The tracking identifier is an identifier used to track the operation request during the operation business process to which the operation request belongs.

[0068] Specifically, the trace identifier can be, for example, TraceID. The sending terminal can carry the trace identifier in the operation request when sending it, or it can generate the trace identifier based on the operation request after receiving the operation request sent by the sending terminal.

[0069] In a microservice architecture, an operation request may involve calls to multiple services. By tracing identifiers, the complete call chain of the operation request within the operation's business process can be identified, that is, tracing the operation request within the business process. For example, Java's reflection technology can be used to modify the target SQL statement to be executed and add comments. interface, client IP, traceID / ], where interface represents the business interface information called when obtaining the operation request, client Ip represents the identification information of the sending terminal, and traceID represents the trace identifier.

[0070] In this embodiment, by adding a tracking identifier to the comment information of the target SQL statement, the target SQL statement can be associated with the specific call process in the specific operation business, thereby realizing full-link tracing from operation request to database operation.

[0071] For example, based on the above embodiments, each monitoring SQL statement in the monitoring list needs to be analyzed and optimized. In this embodiment, for each monitoring SQL statement, prompt information can be generated based on the monitoring SQL statement. The prompt information is used to indicate the output of the review result for the monitoring SQL statement. The review result includes the performance review score of the monitoring SQL statement and / or the optimization strategy of the monitoring SQL statement. The prompt information and the monitoring SQL statement are input into the large language model to obtain the review result output by the large language model.

[0072] In existing technologies, when a database anomaly occurs, a large number of SQL statements are typically identified as slow SQL. Operations personnel manually identify and filter out the abnormal SQL statements that caused the database service failure. This method results in low efficiency in filtering abnormal SQL statements. In contrast, in this embodiment, for each record containing a monitoring SQL statement whose execution time exceeds a first preset duration, a prompt message is generated based on the monitoring SQL statement. This prompt message is used to instruct on the performance review of the monitoring SQL statement using a large language model and / or optimization strategies for the monitoring SQL statement.

[0073] The prompts may include, for example, the following: As an experienced Database Administrator (DBA), you possess extensive expertise in SQL query optimization and database management. Please review the following SQL query and provide a total score out of 100 based on the following criteria: 1. Performance (40 points): How efficient is the query execution? Is there room for improvement to reduce resource consumption or increase speed? 2. Maintainability (30 points): Is the query structure and organization clear and readable? Is it easy to maintain and modify? 3. Security (20 points): Does the query contain security risks, such as SQL injection vulnerabilities? Does it follow best security practices? 4. Accuracy (10 points): Does the query accurately return the expected results? Please provide detailed feedback and improvement suggestions.

[0074] After generating the prompt message, the prompt message and the monitoring SQL statement are input into the large language model, thereby obtaining the review results output by the large language model. The review results include the review score of the performance of the monitoring SQL statement based on each evaluation dimension in the prompt message, and / or the optimization strategy of the monitoring SQL statement.

[0075] For example, you can also input the prompt information, the monitoring SQL statement, and the table structure information of the tables involved in the monitoring SQL statement into the large language model, thereby obtaining the evaluation results of the SQL statement and the evaluation results of the table structure output by the large language model. The table structure information includes the table name, primary key information, main table information, field information, etc.

[0076] For example, suppose the monitoring SQL statement is: select The table structure information includes: `CREATE TABLE lwb_extend (id bigint(20) NOT NULL COMMENT 'primary key', wb_id bigint(20) NOT NULL COMMENT 'main table id', attr_key varchar(50) NOT NULL COMMENT 'corresponding attribute key'...`

[0077] Input the monitoring SQL statement, table structure information, and the prompts from the previous example into the large language model. The large language model can output the following: 1. Performance (40 points): Index Usage: The query conditions use `attr_key` and `attr_value`, but there are no indexes for these two fields in the table structure. Considering the data volume exceeds 100 million rows, it is recommended to create a composite index (attr_key, attr_value, yn) to improve query performance. This can significantly reduce the possibility of full table scans and speed up queries. Disk I / O: With such a large data volume, full table scans will lead to a large number of disk I / O operations. Creating appropriate indexes can significantly reduce this burden. Partitioned Tables: If the data can be partitioned according to some logic (e.g., by date), consider using partitioned tables to further improve performance. Performance Score: 25 / 40; 2. Maintainability (30 points): Query Structure: The query structure is simple and clear, and easy to understand. However, if the business logic becomes more complex, it may be necessary to encapsulate the query logic within views or stored procedures to improve maintainability. Maintainability score: 25 / 30; 3. Security (20 points)..., Summary: Total score: 77 / 100, Improvement suggestions: Create an index: Create a composite index for attr_key and attr_value (and yn) to improve query performance...

[0078] In this embodiment, the analysis capabilities of a large language model are utilized to review each monitoring SQL statement and obtain a corresponding total score. Based on the total score of each monitoring SQL statement, monitoring SQL statements with a total score lower than a preset score are filtered out. These monitoring SQL statements with a total score lower than the preset score, along with corresponding optimization suggestions, are sent to the terminal devices of the operation and maintenance personnel. The operation and maintenance personnel can then optimize these monitoring SQL statements without having to analyze all monitoring SQL statements, thus reducing workload and improving the optimization efficiency of monitoring SQL statements.

[0079] It should be noted that when monitoring SQL statements contain comments, the identification information of the sending terminal and the business interface information can be extracted from the comments. Monitoring SQL statements with a total score lower than the preset score, the corresponding optimization suggestions, and the identification information and business interface information of the sending terminal are all sent to the terminal devices of the operation and maintenance personnel, so that the operation and maintenance personnel can quickly trace the business party.

[0080] For example, based on the above embodiments, when generating target fingerprint information based on the target SQL statement, it can be done in the following way: Based on the target SQL statement, obtain the operation triple, which includes the database operation type, the table name corresponding to the operation type, and the operation condition field. Based on the database operation type, the table name corresponding to the operation type, and the operation condition field, generate the target fingerprint information.

[0081] Specifically, the operation type can include SELECT, INSERT, UPDATE, DELETE, etc., the table name corresponding to the operation type is the table name involved in the target SQL statement, and the operation condition field is the condition field in the target SQL statement, such as the field in the WHERE clause.

[0082] By parsing the target SQL statement, the operation type, the table name corresponding to the operation type, and the operation condition fields can be extracted. This extracted information can be combined, and the combined content can be used as input to a hash algorithm to generate the target fingerprint information corresponding to the target SQL statement.

[0083] In this embodiment, target fingerprint information is generated by the database operation type, the table name corresponding to the operation type, and the operation condition field. In this way, SQL statements with the same operation type, the corresponding table name, and the operation condition field will generate the same fingerprint information, thereby enabling the classification and monitoring of SQL statements with the same characteristics, which improves the monitoring efficiency of SQL statements.

[0084] Figure 3 This is the third flowchart illustrating the SQL statement processing method provided in this embodiment of the invention, taking a query request as an example. Figure 3 As shown, a server with an order system deployed will receive query requests from different clients. These clients may include, for example, a gateway, a merchant's personal computer (PC), a customer mini-program, a purchase order, or other clients that can send requests for database operations.

[0085] Upon receiving a query request, the trace identifier of the request chain, the called business interface information, and the identification information of the sending terminal can be obtained through the Software Development Kit (SDK) of the Application Performance Management (APM) system. Alternatively, the called business interface information and the identification information of the sending terminal can be obtained from the request header information of the Remote Procedure Call (PRC), and a trace identifier can be generated based on the received query request. The APM system is a tool used to monitor and manage the performance of software applications. PRC is a protocol or technology that enables a program to execute code in different address spaces (typically on different computers), just as if calling a local subroutine.

[0086] Furthermore, the acquired tracking identifier, invoked business interface information, and sending terminal identification information can be stored in the current thread's ThreadLocal for direct access in subsequent applications. Alternatively, this information can be stored in other threads. ThreadLocal provides an independent storage space for each thread, ensuring that threads can safely store and access their own data without worrying about interference from other threads.

[0087] In addition, a target SQL statement will be generated based on the query request. Before the target SQL statement is executed, it will be intercepted and a corresponding target fingerprint information will be generated based on the target SQL statement.

[0088] The target fingerprint information is matched against the fingerprint information of each monitoring SQL statement in the monitoring list to determine whether the target fingerprint information matches the fingerprint information in the monitoring list. Each monitoring SQL statement in the monitoring list is a slow SQL statement, meaning its execution time exceeds a first preset time.

[0089] If a match is successful, a processing strategy for the target SQL statement will be selected based on its execution duration. For example, if the execution duration of the target SQL statement exceeds the second preset duration, a prohibition strategy will be implemented, meaning the execution of the target SQL statement will be prohibited, and error and warning messages will be output. If the execution duration of the target SQL statement is within the first sub-duration range, a direct execution strategy will be selected, and the target SQL statement will be executed directly. If the execution duration of the target SQL statement is within the second sub-duration range, an execution strategy based on the authorization token will be selected. It will be determined whether an authorization token has been obtained. If no authorization token has been obtained, the process will continue to wait. If a timeout occurs before an authorization token is obtained, an error termination or warning message will be output. If an authorization token is obtained, comment information will be added to the target SQL statement.

[0090] Additionally, if the target fingerprint information does not match the fingerprint information of any of the monitored SQL statements in the monitoring list, comment information will be added to the target SQL statement. This comment information includes the trace identifier stored in ThreadLocal, the called business interface information, and the identifier of the sending terminal. After adding the comment information, the target SQL statement will be executed normally. Because comment information is added to the target SQL statement, if the target SQL statement is a slow SQL statement, this comment information can be recorded in the slow SQL log and the sent alert information, facilitating tracing the business party.

[0091] For each monitored SQL statement in the monitoring list, monitoring can be performed asynchronously. Specifically, by implementing the Runnable interface, the main thread records the start time of the SQL statement when sending it to the database, and the end time after execution. The asynchronous thread calculates the execution duration of the current SQL statement by executing the `run` method after obtaining the start and end times. If the execution duration exceeds a first preset duration, the current SQL statement is designated as a monitoring SQL statement, and a fingerprint is generated. This fingerprint and its corresponding execution duration are then added to the monitoring list. If the SQL statement finishes execution and the execution duration does not exceed the first preset duration, the thread will sleep. By generating fingerprint information, SQL statements with the same characteristics can be identified, thus preventing or delaying the continued expansion of database anomalies caused by the continued execution of SQL statements with similar characteristics. Furthermore, asynchronous thread monitoring allows for unified monitoring without needing to focus on the business SQL.

[0092] To improve the optimization efficiency of slow SQL statements in the monitoring list, for each monitored SQL statement, a prompt message can be generated based on that statement. This prompt message, along with the monitored SQL statement, is then input into a large language model. The large language model then reviews the monitored SQL statements, obtaining a review result that includes a performance score and / or optimization strategies. Through the analytical capabilities of the large language model, optimization suggestions and relevant business information can be alerted to operations and maintenance personnel, thus improving the efficiency of monitoring SQL statement optimization.

[0093] The SQL statement processing apparatus provided by the present invention will be described below. The SQL statement processing apparatus described below can be referred to in correspondence with the SQL statement processing method described above.

[0094] Figure 4 This is a schematic diagram of the structure of the SQL statement processing device provided in an embodiment of the present invention, as shown below. Figure 4 As shown, the SQL statement processing device 400 includes: Generation module 11 is used to obtain an operation request for the database and generate a target SQL statement based on the operation request; The generation module 11 is also used to generate target fingerprint information based on the target SQL statement; The processing module 12 is used to process the target SQL statement based on the execution time of the target monitoring SQL statement when the target fingerprint information matches the fingerprint information of the target monitoring SQL statement. The target monitoring SQL statement is an SQL statement in at least one monitoring SQL statement, and each monitoring SQL statement is an SQL statement with an execution time greater than a first preset time.

[0095] The SQL statement processing apparatus provided in this embodiment of the invention obtains an operation request for a database, generates a target SQL statement based on the operation request, and generates target fingerprint information based on the target SQL statement. If the target fingerprint information matches the fingerprint information of a target monitoring SQL statement, the apparatus processes the target SQL statement based on its execution duration. The target monitoring SQL statement is an SQL statement from at least one monitoring SQL statement, and each monitoring SQL statement has an execution duration greater than a first preset duration. Because the target fingerprint information of the target SQL statement can be matched with the fingerprint information of each monitoring SQL statement before the target SQL statement is executed, potential slow SQL statements can be identified before the target SQL statement is executed. Based on the execution duration of the target monitoring SQL statement, the processing strategy for the target SQL statement can be dynamically adjusted, achieving the goal of timely interception of the target SQL statement's execution. This avoids the secondary execution of slow SQL statements of the same type as the target monitoring SQL statement, reducing the risk of escalating database system anomalies during subsequent SQL statement execution.

[0096] In one example embodiment, the processing module 12 is specifically used for: If the execution time of the target monitoring SQL statement exceeds a second preset time, the execution of the target SQL statement shall be prohibited. If the execution time of the target monitoring SQL statement is within a preset time range, the target SQL statement is executed, where the maximum time within the preset time range is less than or equal to the second preset time.

[0097] In one example embodiment, the preset duration range includes a first sub-duration range and a second sub-duration range, wherein the maximum duration in the first sub-duration range is less than the minimum duration in the second sub-duration range; Processing module 12 is specifically used for: If the execution time of the target monitoring SQL statement is within the first sub-time range, then the target SQL statement is executed. If the execution duration of the target monitoring SQL statement is within the second sub-duration range and an authorization identifier is obtained, the target SQL statement is executed. The authorization identifier is an identifier sent based on a preset period to allow the execution of the SQL statement.

[0098] In one example embodiment, the device further includes: an input module, wherein: The generation module 11 is further configured to generate prompt information based on the monitoring SQL statement for each of the monitoring SQL statements. The prompt information is used to indicate the output of the review result for the monitoring SQL statement. The review result includes the performance review score of the monitoring SQL statement and / or the optimization strategy of the monitoring SQL statement. The input module is used to input the prompt information and the monitoring SQL statement into the large language model to obtain the review result output by the large language model.

[0099] In one example embodiment, the generation module 11 is specifically used for: Based on the target SQL statement, an operation triplet is obtained, which includes the operation type of the database, the table name corresponding to the operation type, and the operation condition field. The target fingerprint information is generated based on the operation type of the database, the table name corresponding to the operation type, and the operation condition field.

[0100] In one example embodiment, the apparatus further includes: a determining module and an adding module, wherein: The determination module is used to determine, based on the operation request, the identification information of the terminal that sent the operation request and the business interface information called when obtaining the operation request; An add module is used to add comment information to the target SQL statement. The comment information includes the identification information of the sending terminal and the business interface information.

[0101] In one example embodiment, a module is added specifically for: If it is determined that the target SQL statement will be executed, the comment information is added to the target SQL statement.

[0102] In one example embodiment, the annotation information further includes a tracking identifier for the operation request, which is an identifier used to track the operation request within the operation service process to which the operation request belongs.

[0103] The apparatus of this embodiment can be used to execute the method of any embodiment in the SQL statement processing method side embodiment. Its specific implementation process and technical effects are similar to those in the SQL statement processing method side embodiment. For details, please refer to the detailed description in the SQL statement processing method side embodiment, which will not be repeated here.

[0104] Figure 5 This is a schematic diagram of the physical structure of an electronic device provided in an embodiment of the present invention, such as... Figure 5As shown, the electronic device may include a processor 510, a communications interface 520, a memory 530, and a communication bus 540, wherein the processor 510, communications interface 520, and memory 530 communicate with each other via the communication bus 540. The processor 510 can call logical instructions in the memory 530 to execute a method for processing SQL statements. This method includes: obtaining an operation request for a database, generating a target SQL statement based on the operation request; generating target fingerprint information based on the target SQL statement; and, if the target fingerprint information matches the fingerprint information of a target monitoring SQL statement, processing the target SQL statement based on its execution duration. The target monitoring SQL statement is an SQL statement in at least one monitoring SQL statement, and each monitoring SQL statement is an SQL statement with an execution duration greater than a first preset duration.

[0105] Furthermore, the logical instructions in the aforementioned memory 530 can be implemented as software functional units and, when sold or used as independent products, can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art, or a part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of the present invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0106] On the other hand, the present invention also provides a computer program product, which includes a computer program that can be stored on a non-transitory computer-readable storage medium. When the computer program is executed by a processor, the computer can execute the SQL statement processing method provided by the above methods. The method includes: obtaining an operation request for a database and generating a target SQL statement based on the operation request; generating target fingerprint information based on the target SQL statement; and, if the target fingerprint information matches the fingerprint information of a target monitoring SQL statement, processing the target SQL statement based on the execution duration of the target monitoring SQL statement. The target monitoring SQL statement is an SQL statement in at least one monitoring SQL statement, and each monitoring SQL statement is an SQL statement with an execution duration greater than a first preset duration.

[0107] In another aspect, the present invention also provides a non-transitory computer-readable storage medium storing a computer program thereon. When executed by a processor, the computer program implements a processing method for executing the SQL statements provided by the above methods. The method includes: after obtaining an operation request for a database, generating a target SQL statement based on the operation request; generating target fingerprint information based on the target SQL statement; and, if the target fingerprint information matches the fingerprint information of a target monitoring SQL statement, processing the target SQL statement based on the execution duration of the target monitoring SQL statement. The target monitoring SQL statement is an SQL statement in at least one monitoring SQL statement, and each monitoring SQL statement is an SQL statement with an execution duration greater than a first preset duration.

[0108] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs. Those skilled in the art can understand and implement this without any creative effort.

[0109] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus necessary general-purpose hardware platforms, and of course, it can also be implemented by hardware. Based on this understanding, the above technical solutions, in essence or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments or some parts of the embodiments.

[0110] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for processing SQL statements, characterized in that, include: After obtaining the database operation request, generate the target SQL statement based on the operation request; Generate target fingerprint information based on the target SQL statement; If the target fingerprint information matches the fingerprint information of the target monitoring SQL statement, the target SQL statement is processed based on the execution time of the target monitoring SQL statement. The target monitoring SQL statement is an SQL statement in at least one monitoring SQL statement, and each monitoring SQL statement is an SQL statement with an execution time greater than a first preset time.

2. The method for processing SQL statements according to claim 1, characterized in that, The processing of the target SQL statement based on its execution time includes: If the execution time of the target monitoring SQL statement exceeds a second preset time, the execution of the target SQL statement shall be prohibited. If the execution time of the target monitoring SQL statement is within a preset time range, the target SQL statement is executed, where the maximum time within the preset time range is less than or equal to the second preset time.

3. The method for processing SQL statements according to claim 2, characterized in that, The preset duration range includes a first sub-duration range and a second sub-duration range, wherein the maximum duration in the first sub-duration range is less than the minimum duration in the second sub-duration range; The step of executing the target SQL statement when its execution time is within a preset time range includes: If the execution time of the target monitoring SQL statement is within the first sub-time range, then the target SQL statement is executed. If the execution duration of the target monitoring SQL statement is within the second sub-duration range and an authorization identifier is obtained, the target SQL statement is executed. The authorization identifier is an identifier sent based on a preset period to allow the execution of the SQL statement.

4. The method for processing SQL statements according to any one of claims 1-3, characterized in that, The method further includes: For each of the monitoring SQL statements, a prompt message is generated based on the monitoring SQL statement. The prompt message is used to indicate the output of the review result for the monitoring SQL statement. The review result includes the performance review score of the monitoring SQL statement and / or the optimization strategy of the monitoring SQL statement. The prompt information and the monitoring SQL statement are input into the large language model to obtain the review result output by the large language model.

5. The method for processing SQL statements according to any one of claims 1-3, characterized in that, The process of generating target fingerprint information based on the target SQL statement includes: Based on the target SQL statement, an operation triplet is obtained, which includes the operation type of the database, the table name corresponding to the operation type, and the operation condition field. The target fingerprint information is generated based on the operation type of the database, the table name corresponding to the operation type, and the operation condition field.

6. The method for processing SQL statements according to any one of claims 1-3, characterized in that, The method further includes: Based on the operation request, determine the identification information of the terminal that sent the operation request and the business interface information called when obtaining the operation request; Add comment information to the target SQL statement. The comment information includes the identification information of the sending terminal and the business interface information.

7. The method for processing SQL statements according to claim 6, characterized in that, Adding comment information to the target SQL statement includes: If it is determined that the target SQL statement will be executed, the comment information is added to the target SQL statement.

8. The method for processing SQL statements according to claim 6, characterized in that, The annotation information also includes a tracking identifier for the operation request, which is an identifier used to track the operation request within the operation service process to which the operation request belongs.

9. A device for processing SQL statements, characterized in that, include: The generation module is used to obtain an operation request for the database and generate a target SQL statement based on the operation request. The generation module is also used to generate target fingerprint information based on the target SQL statement; The processing module is used to process the target SQL statement based on the execution time of the target monitoring SQL statement when the target fingerprint information matches the fingerprint information of the target monitoring SQL statement. The target monitoring SQL statement is an SQL statement in at least one monitoring SQL statement, and each monitoring SQL statement is an SQL statement with an execution time greater than a first preset time.

10. An electronic device comprising a memory, a processor, and a computer program stored in the memory and running on the processor, characterized in that, When the processor executes the computer program, it implements the SQL statement processing method as described in any one of claims 1 to 8.

11. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the SQL statement processing method as described in any one of claims 1 to 8.

12. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the SQL statement processing method as described in any one of claims 1 to 8.

Images