SQL (Structured Query Language) statement optimization suggestion generation method and device, medium and electronic equipment

Abstract

The invention provides an SQL statement optimization suggestion generation method and device, a medium and electronic equipment, and is applied to the technical field of artificial intelligence. The method comprises the following steps: acquiring SQL log monitoring data, including an execution timestamp and a time consumption value of an SQL statement, dividing the data according to a preset time window, and constructing a to-be-processed data set; a median approximation algorithm is used for calculating the median of SQL execution time consumption, and then abnormal SQL statements in the running log are screened out. And for each abnormal SQL, reconstructing an execution context thereof, obtaining an abstract syntax tree and an execution plan, and substituting the abstract syntax tree and the execution plan into a preset cue word template to generate cue words. And finally, calling a large model to analyze the cue word so as to obtain an optimization suggestion for the abnormal SQL. By collecting and analyzing the SQL logs and combining the median algorithm and the large model technology, efficient SQL statement optimization is achieved.

Description

Technical Field

[0001] The present invention relates to the field of artificial intelligence technology, and in particular, to a method, device, medium and electronic device for generating SQL statement optimization suggestions. Background Art

[0002] In a distributed business system, the SQL execution efficiency directly affects the success rate of transaction processing and the performance of system throughput. Especially, it faces different challenges in different business scenarios such as e-commerce, finance, and cloud services. For example: in the e-commerce scenario, it is necessary to cope with the timeout risk caused by millions of instantaneous transactions; the financial industry has strict requirements for low latency in high-frequency transactions; in the multi-tenant environment of cloud services, refined monitoring capabilities are required.

[0003] However, existing database log-based monitoring tools (such as Splunk) generally rely on alarm mechanisms based on averages or fixed thresholds, and it is difficult to meet the requirements for real-time monitoring and dynamic tuning of SQL performance in high-demand scenarios.

[0004] Therefore, how to efficiently optimize SQL statements has become a technical problem that needs to be solved urgently by those skilled in the art. Summary of the Invention

[0005] In view of the above problems, the present invention provides a method, device, medium and electronic device for generating SQL statement optimization suggestions that overcome the above problems or at least partially solve the above problems. The technical solutions are as follows:

[0006] A method for generating SQL statement optimization suggestions includes:

[0007] Obtain SQL log monitoring data, where the SQL log monitoring data includes the execution timestamps and elapsed time values of multiple SQL statements in the operation log of the business system server;

[0008] Divide the SQL log monitoring data into multiple time window sequences according to a preset time window granularity, and respectively construct a dataset to be processed for each time window sequence. The dataset to be processed consists of the execution durations of each SQL statement in the time window sequence, and the execution duration is related to the execution timestamp and the elapsed time value of the SQL statement;

[0009] For any one of the time window sequences: use a preset median approximation algorithm to generate the median of the SQL execution elapsed time of the dataset to be processed, and use the median of the SQL execution elapsed time to screen out the abnormal SQL statements in the time window sequence;

[0010] For any of the abnormal SQL statements: reconstruct the execution context of the abnormal SQL statement, obtain the abstract syntax tree and execution plan of the abnormal SQL statement, substitute the abstract syntax tree and the execution plan into a preset prompt template to generate a prompt;

[0011] Call a large model to parse the prompt and obtain the optimization suggestions output by the large model for the abnormal SQL statement.

[0012] Optionally, the screening of the abnormal SQL statements in the time window sequence by using the median SQL execution time includes:

[0013] Use the median SQL execution time and a preset scaling factor to obtain a dynamic deviation coefficient, where the preset scaling factor is a constant determined based on the interquartile range and the actual server load fluctuation characteristics;

[0014] Identify the SQL statements with time-consuming values greater than the dynamic deviation coefficient in the time window sequence as abnormal SQL statements.

[0015] Optionally, the preset prompt template includes a problem definition section, a context constraint section, an analysis guidance section, and a security verification rule. The problem definition section is used to determine the optimization goal of the large model for the abnormal SQL statement. The context constraint section is used to provide the execution environment information and database structure of the abnormal SQL statement to the large model. The analysis guidance section is used to guide the large model to output optimization suggestions based on the execution path and execution plan of the abnormal SQL statement. The security verification rule is used to constrain the optimization suggestions output by the large model to comply with security standards, where the security standards refer to a series of specifications and requirements followed when optimizing the abnormal SQL statement.

[0016] Optionally, the division of the SQL log monitoring data into multiple time window sequences according to a preset time window granularity, and the construction of the to-be-processed data sets for each of the time window sequences respectively, includes:

[0017] Divide the SQL log monitoring data into multiple time window sequences according to a preset time window granularity, and respectively construct the initial data sets for each of the time window sequences, where the initial data set consists of the execution durations of each SQL statement in the time window sequence;

[0018] Calculate the baseline change rate of the log content length corresponding to each time window sequence;

[0019] Based on the baseline change rate of multiple said time window sequences, determine whether the preset time window granularity is accurate. If so, use the initial data set as the data set to be processed. If not, adjust the preset time window granularity and re-execute the step of dividing the SQL log monitoring data into multiple time window sequences according to the preset time window granularity and constructing the initial data set for each said time window sequence respectively.

[0020] Optionally, after calling the large model to parse the prompt word and obtaining the optimization suggestions output by the large model for the abnormal SQL statement, the method further includes:

[0021] Obtain the feedback data of the optimization suggestions;

[0022] Adjust the preset prompt word template and / or the model parameters of the large model according to the feedback data.

[0023] Optionally, obtaining the SQL log monitoring data includes:

[0024] Obtain the operation log of the business system service;

[0025] Extract the SQL execution log from the operation log, where the SQL execution log includes multiple SQL statements;

[0026] Perform desensitization processing on each SQL statement in the SQL execution log to obtain the desensitized SQL execution log;

[0027] Use a preset regular expression pattern library to scan the desensitized SQL execution log to generate SQL log monitoring data, where the preset regular expression pattern library is used to capture the execution timestamp and elapsed time value of the SQL statement through a context-aware algorithm.

[0028] Optionally, the preset median approximation algorithm is the T-Digest algorithm.

[0029] An SQL statement optimization suggestion generation device includes: an SQL log monitoring data acquisition unit, a data set to be processed construction unit, an abnormal SQL statement screening unit, a prompt word generation unit, and an optimization suggestion output unit.

[0030] The SQL log monitoring data acquisition unit is used to acquire SQL log monitoring data, where the SQL log monitoring data includes the execution timestamp and elapsed time value of multiple SQL statements in the operation log of the business system server;

[0031] The to-be-processed dataset construction unit is used to divide the SQL log monitoring data into multiple time window sequences according to a preset time window granularity, and construct a to-be-processed dataset for each of the time window sequences. The to-be-processed dataset consists of the execution durations of each SQL statement within the time window sequence, and the execution duration is related to the execution timestamp and the elapsed time value of the SQL statement;

[0032] The abnormal SQL statement screening unit is used for any one of the time window sequences: generating the median of the SQL execution elapsed time of the to-be-processed dataset by using a preset median approximation algorithm, and screening out the abnormal SQL statements in the time window sequence by using the median of the SQL execution elapsed time;

[0033] The prompt word generation unit is used for any one of the abnormal SQL statements: reconstructing the execution context of the abnormal SQL statement to obtain the abstract syntax tree and the execution plan of the abnormal SQL statement, and substituting the abstract syntax tree and the execution plan into a preset prompt word template to generate a prompt word;

[0034] The optimization suggestion output unit is used to call a large model to parse the prompt word and obtain the optimization suggestions output by the large model for the abnormal SQL statement.

[0035] A computer-readable storage medium, on which a program is stored, and when the program is executed by a processor, the SQL statement optimization suggestion generation method described above is implemented.

[0036] An electronic device, the electronic device includes at least one processor, at least one memory connected to the processor, and a bus; wherein, the processor and the memory complete communication with each other through the bus; the processor is used to call the program instructions in the memory to execute the SQL statement optimization suggestion generation method described above.

[0037] With the above technical solution, the method, device, medium and electronic device for generating SQL statement optimization suggestions provided by the present invention obtain SQL log monitoring data, where the SQL log monitoring data includes the execution timestamps and time-consuming values of multiple SQL statements in the operation logs of the business system server; divide the SQL log monitoring data into multiple time window sequences according to a preset time window granularity, and respectively construct a dataset to be processed for each time window sequence, where the dataset to be processed consists of the execution durations of each SQL statement within the time window sequence, and the execution duration is related to the execution timestamp and time-consuming value of the SQL statement; for any time window sequence: use a preset median approximation algorithm to generate the median of the SQL execution time consumption of the dataset to be processed, and use the median of the SQL execution time consumption to screen out abnormal SQL statements in the time window sequence; for any abnormal SQL statement: reconstruct the execution context of the abnormal SQL statement to obtain the abstract syntax tree and execution plan of the abnormal SQL statement, substitute the abstract syntax tree and execution plan into a preset prompt word template to generate a prompt word; call a large model to parse the prompt word to obtain the optimization suggestions output by the large model for the abnormal SQL statement. The present invention collects and analyzes SQL log monitoring data, uses the median approximation algorithm to identify abnormal SQL statements, generates prompt words based on a preset prompt word template in combination with the abstract syntax tree and execution plan, and finally calls a large model to provide optimization suggestions for abnormal SQL statements, thereby efficiently optimizing the execution performance of SQL statements.

[0038] The above description is only an overview of the technical solution of the present invention. In order to be able to understand the technical means of the present invention more clearly, it can be implemented according to the content of the specification. And in order to make the above and other purposes, features and advantages of the present invention more obvious and understandable, the following specifically illustrates the embodiments of the present invention. Brief Description of the Drawings

[0039] By reading the detailed description of the preferred embodiments below, various other advantages and benefits will become clear to those of ordinary skill in the art. The drawings are only for the purpose of showing the preferred embodiments and are not considered to be a limitation of the present invention. And throughout the drawings, the same reference numerals are used to represent the same components. In the drawings:

[0040] Figure 1 It shows a schematic flowchart of an implementation manner of the method for generating SQL statement optimization suggestions provided by an embodiment of the present invention;

[0041] Figure 2 It shows a schematic flowchart of another implementation manner of the method for generating SQL statement optimization suggestions provided by an embodiment of the present invention;

[0042] Figure 3The flowchart shows another implementation of the SQL statement optimization suggestion generation method provided by the embodiments of the present invention;

[0043] Figure 4 The structural diagram shows the SQL statement optimization suggestion generation device provided by the embodiments of the present invention;

[0044] Figure 5 The structural diagram shows the electronic device provided by the embodiments of the present invention. Detailed implementation manners

[0045] The exemplary embodiments of the present invention will be described in more detail below with reference to the accompanying drawings. Although the exemplary embodiments of the present invention are shown in the drawings, it should be understood that the present invention can be implemented in various forms and should not be limited by the embodiments set forth herein. On the contrary, these embodiments are provided so that the present invention can be more thoroughly understood and the scope of the present invention can be fully conveyed to those skilled in the art.

[0046] In a distributed business system, the SQL execution efficiency is a key performance indicator, which directly affects the success rate of transaction processing and the throughput of the system. With the continuous increase of business requirements, the manifestation forms and optimization focuses of SQL performance bottlenecks in different scenarios are also different, especially in the fields of e-commerce, finance, and cloud services.

[0047] In the e-commerce scenario, the system needs to process millions of instantaneous transaction requests and peak traffic, which may lead to timeouts of user operations, affecting user experience and business conversion rates. The financial industry faces the challenge of high-frequency trading, and the latency of SQL may directly lead to transaction failures, causing serious economic losses. The multi-tenant environment of cloud services makes the demand for SQL performance monitoring complex, and existing solutions often fail to meet such complex requirements.

[0048] To solve these problems, the use of full-link tracing technology to perform real-time performance monitoring on high-frequency queries, batch operations, and long-transaction SQLs has become a core operation and maintenance measure to ensure system stability and business continuity. However, existing database log monitoring tools (such as Splunk, etc.) have multiple problems in practical applications: First, relying on averages or fixed thresholds for threshold calculation is easily affected by extreme values, making it difficult to accurately reflect the SQL execution efficiency and lacking the ability to dynamically adjust; Second, the sensitivity of anomaly detection is insufficient, resulting in high false alarm and missed alarm rates, affecting the timely handling of problems; Then, the optimization mechanism relies on manual analysis, with slow feedback and easy errors, reducing operation and maintenance efficiency; In addition, the lack of an automated knowledge precipitation mechanism makes key experiences easily missed, affecting system stability; Finally, these tools consume a large amount of resources in a high-concurrency environment, resulting in a significant increase in infrastructure costs and imposing a burden on enterprises.

[0049] In summary, in high-demand scenarios such as e-commerce, finance, and cloud services, the existing SQL performance monitoring solutions fail to effectively meet the real-time monitoring and dynamic tuning requirements of the business. There is an urgent need for a new technical solution to improve the SQL execution efficiency and the overall performance of the system.

[0050] Based on this, in the embodiments of the present invention, a method for generating SQL statement optimization suggestions is provided. By obtaining SQL log monitoring data, including the execution timestamp and elapsed time value of SQL statements, and dividing the data according to a preset time window, a dataset to be processed is constructed. The median approximation algorithm is used to calculate the median of the SQL execution elapsed time, and then the abnormal SQL statements in the running logs are screened out. For each abnormal SQL, its execution context is reconstructed to obtain the abstract syntax tree and execution plan, and they are substituted into a preset prompt template to generate a prompt. Finally, a large model is called to parse the prompt to obtain optimization suggestions for the abnormal SQL. It can be seen that the present invention realizes efficient SQL statement optimization by collecting and analyzing SQL logs, combining the median algorithm and large model technology, thereby improving the SQL execution efficiency and the overall performance of the system.

[0051] As Figure 1 shown, a flowchart of an implementation manner of the method for generating SQL statement optimization suggestions provided by the embodiments of the present invention may include:

[0052] S100. Obtain SQL log monitoring data, where the SQL log monitoring data includes the execution timestamp and elapsed time value of multiple SQL statements in the running logs of the business system server.

[0053] Among them, the SQL log monitoring data refers to the relevant information obtained by monitoring and recording the SQL statements executed in the business system server.

[0054] Among them, the business system server refers to a computer system or server that runs specific business application programs in e-commerce, finance, and / or cloud service scenarios, and is used to process user requests, manage data, and execute business logic. The business system server may include a database, an application program, and other related service components.

[0055] Among them, the running log refers to a record file generated on the business system server, which details the running status, events, error information, and execution status of SQL statements of the system.

[0056] Among them, the SQL statement is a programming language statement used to interact with the database management system, and is often used to query, insert, update, and delete data in the database.

[0057] Among them, the execution timestamp refers to the specific time point when a certain SQL statement is executed as recorded by the business system server, including the start timestamp and the completion timestamp.

[0058] Among them, the elapsed time value represents the time taken for a certain SQL statement to be executed from the start to the completion.

[0059] Specifically, embodiments of the present invention can collect the operation logs of the system backend program of the business system server in real time through a lightweight log collection tool pre-installed on the business system server. Among them, the lightweight log collection tool is a lightweight transmission program for forwarding and centralizing log data, installed as an agent on the server, and used to monitor and collect the specified directories or files on the server in real time and forward them to the specified analysis server. Then, it is transmitted and stored in the disk of the analysis server through the internal network, and a log parsing tool is run on the analysis server to parse the content of the log file of the received operation logs to obtain multiple SQL statements in the operation logs. Then, based on the context-aware algorithm, the operation log file is scanned line by line to record the execution timestamp and the elapsed time value of the SQL statements.

[0060] S110. Divide the SQL log monitoring data into multiple time window sequences according to a preset time window granularity, and respectively construct a data set to be processed for each time window sequence. Among them, the data set to be processed is composed of the execution durations of each SQL statement within the time window sequence, and the execution duration is related to the execution timestamp and the elapsed time value of the SQL statement.

[0061] Among them, the preset time window granularity refers to the size of the time period (i.e., time window) set in advance when analyzing the SQL log monitoring data, and is used to group the SQL log monitoring data. The specific value of the preset time window granularity can be set according to the actual analysis granularity and business requirements. For example, the preset time window granularity can be 30 minutes.

[0062] Among them, the data set to be processed refers to a data set formed for each time window sequence after dividing the SQL log monitoring data into different time window sequences. The data set to be processed contains the execution duration of each SQL statement within the corresponding time window sequence.

[0063] Among them, the execution duration refers to the complete execution duration of a specific single SQL statement within a certain time window sequence. For example: If the start timestamp and the completion timestamp of a certain SQL statement are both within the first time window sequence, and the elapsed time value is 150 milliseconds, then the execution duration of this SQL statement is 150 milliseconds. If the start timestamp of a certain SQL is within the second time window sequence and the completion timestamp is within the third time window sequence, and if the elapsed time value of this SQL statement is 400 milliseconds, then the execution time corresponding to this SQL statement in the pending dataset of the second time window sequence can be 250 milliseconds, and the execution time corresponding to this SQL statement in the pending dataset of the third time window sequence can be 150 milliseconds.

[0064] Optionally, embodiments of the present invention can divide the SQL log monitoring data into multiple consecutive time window sequences according to a preset time window granularity, establish a mapping relationship between the window numbers of each time window sequence and the execution records of the corresponding SQL statements, and then collect the execution durations of all SQL statements within each time window sequence to construct a pending dataset for each time window sequence. For example: The pending dataset can be expressed as "X={x 1 ,x 2 ,....,x n}", where each element xi in the pending dataset represents the complete execution duration of a single SQL statement within the corresponding time window sequence.

[0065] S120. For any time window sequence: Use a preset median approximation algorithm to generate the median of the SQL execution elapsed time of the pending dataset, and use the median of the SQL execution elapsed time to filter out the abnormal SQL statements in the time window sequence.

[0066] Among them, the preset median approximation algorithm is an algorithm for estimating the median in a dataset by grouping or classifying the dataset. The principle of the preset median approximation algorithm is to reasonably segment the data, then find the median in each segment, and finally combine the medians of each segment to approximate the median of the overall data.

[0067] Among them, the median of the SQL execution elapsed time refers to the median value of the execution durations of each SQL statement in the pending dataset.

[0068] Specifically, embodiments of the present invention use the median of the SQL execution elapsed time as the median baseline threshold, and by comparing the median baseline threshold with the execution duration of each SQL statement, filter out the abnormal SQL statements in the corresponding time window sequence. For example: Confirm the SQL statements with execution durations greater than the median baseline threshold as abnormal SQL statements.

[0069] Optionally, the preset median approximation algorithm provided by the embodiments of the present invention may be the T-Digest algorithm.

[0070] Among them, the T-Digest algorithm is a median approximation calculation algorithm applicable to streaming data, which has the characteristics of low memory occupancy, fast execution, and high accuracy.

[0071] In view of the performance and memory consumption problems faced by traditional median algorithms when dealing with large amounts of data, the embodiments of the present invention innovatively adopt the open-source component HdrHistogram authorized by the Apache 2.0 protocol. This component implements the high-performance T-Digest algorithm, successfully breaking through the limitations of traditional methods. Its core technical features include:

[0072] 1. High dynamic range: The T-Digest algorithm has a dynamic range of up to 1:10 18 and can effectively handle extreme values and a wide range of data distributions.

[0073] 2. Adaptive precision: This algorithm supports adaptive precision, and the memory occupancy rate is significantly reduced to about 10% of the traditional method, enabling efficient operation in resource-constrained environments.

[0074] 3. Lock-free multi-thread support: Based on the design of a lock-free circular buffer, the T-Digest algorithm can achieve multi-thread parallel writing, which is particularly suitable for high-concurrency and large-data-volume monitoring scenarios.

[0075] During use, the embodiments of the present invention can initialize the histogram instance by calling the "HdrHistogram.create()" method. The key technical parameter configurations include: setting the lower limit of the value range to 1 millisecond to exclude the interference of invalid zero values. The upper limit of the value range is dynamically bound to the maximum value calculated previously to ensure comprehensive coverage of the observed data distribution. The precision range is set between 0.001% and 0.01%, and the dynamic range is from 1 millisecond to 1000 milliseconds, ensuring double decimal precision under dynamic bitwidth allocation.

[0076] When traversing the dataset to be processed in the embodiments of the present invention, the "histogram.addRecord(x i )" method is called to perform distributed writing. This process adopts vectorized processing technology optimized based on the SIMD instruction set, and 256 samples can be batch-written at a time. At the same time, the algorithm also includes an adaptive compression strategy. When the sample value deviates from the current bin center by more than the set threshold, dynamic bin reorganization will be automatically triggered.

[0077] In the embodiment of the present invention, the median value median is generated by calling the "histogram.getPercentile(50.0)" method. The technical implementation process includes: constructing a cumulative distribution function (CDF) curve, and complementing the data between bins through linear interpolation. Performing a binary search algorithm to locate the 50% percentile. Outputting an accurate floating-point value with two decimal places, and the calculation delay is stable at the microsecond level.

[0078] The finally generated median will be used as the median baseline threshold. After multiple rounds of simulation calculations and comparisons with traditional algorithms, the statistical data is shown in Table 1. It can be seen from the statistical data that after adopting the T-Digest algorithm, the time consumption in the low-load scenario is reduced by 30%, and the time consumption in the high-load scenario is reduced by 40%. The memory usage is about 10.9% of the traditional algorithm in the low-load scenario and about 11.3% in the high-load scenario. This result fully verifies the significant improvement of the T-Digest algorithm in performance and memory efficiency.

[0079] Table 1

[0080] Load condition Time consumption of traditional median algorithm Memory usage of traditional median algorithm Time consumption of optimized algorithm Memory usage of optimized algorithm Low load (100 QPS) 500 milliseconds 570M 350 milliseconds 62M High load (2000 QPS) 1800 milliseconds 1090M 1080 milliseconds 118M

[0081] In the embodiment of the present invention, by using the T-Digest algorithm, the median value of the SQL execution time consumption of the dataset to be processed can be calculated quickly and accurately, so as to quickly identify the SQL statements with abnormal execution time in a large number of datasets, thereby reducing memory consumption.

[0082] S130. For any abnormal SQL statement: reconstruct the execution context of the abnormal SQL statement, obtain the abstract syntax tree and execution plan of the abnormal SQL statement, and substitute the abstract syntax tree and execution plan into a preset prompt word template to generate a prompt word.

[0083] Specifically, in the embodiment of the present invention, various types of information related to the execution of the abnormal SQL statement can be collected and sorted out to reconstruct its execution environment. The process of reconstructing the execution context of the abnormal SQL statement may include obtaining the execution plan and abstract syntax tree (Abstract Syntax Tree, AST) of the abnormal SQL statement, as well as other necessary context information (such as connection information, variable values, session parameters, etc.).

[0084] Among them, the abstract syntax tree is a way to represent the syntax of source code in a tree structure, used to show the relationship and hierarchy between expressions in the SQL statement. For example: the abstract syntax tree may include the SQL type, field names, table association relationships, subquery nesting levels, and bound variable values (after desensitization) of the abnormal SQL statement. In the embodiment of the present invention, the parsing tool of the database can be used to convert the abnormal SQL statement into an abstract syntax tree.

[0085] Among them, the execution plan is a query plan made based on the abstract syntax tree obtained after the SQL statement passes through the query analyzer and the statistical information of related tables, indicating the steps and methods for executing the SQL query. For example: The execution plan can include the index name of the abnormal SQL statement, the sorting key name, the temporary table name, and the resource consumption indicators (such as estimated number of rows and estimated cost, etc.) of key operation operators (full table scan, index scan, aggregation, sorting, etc.). Embodiments of the present invention can obtain the execution plan of the abnormal SQL statement according to different database types through tools or commands (such as EXPLAIN) of the database management system.

[0086] Embodiments of the present invention can integrate the obtained abstract syntax tree and execution plan of the abnormal SQL statement into a structured knowledge unit as shown in Table 2, so as to substitute it into a preset prompt template to generate an effective prompt.

[0087] Table 2

[0088] Type Data content Metadata Table name, schema name, field name, table association relationship, subquery, index, etc. Performance quantification metrics SQL execution time Execution path Based on the operator dependency relationship of the execution plan, mark the proportion of the critical path time consumption Context association Timestamp, execution plan

[0089] Among them, the preset prompt template is a structured text framework for generating prompts. The preset prompt template contains fixed sentence patterns and placeholders, allowing specific execution context information (such as abstract syntax tree and execution plan, etc.) to be filled in to generate prompts.

[0090] Optionally, the preset prompt template can include a problem definition section, a context constraint section, an analysis guidance section, and a security verification rule.

[0091] Among them, the problem definition section is used to determine the optimization goal of the large model for the abnormal SQL statement. The problem definition section can include the description of the abnormal SQL statement, performance metrics (such as response time, throughput, etc.), and specific optimization goals (such as reducing execution time, reducing resource consumption, etc.). The core purpose of the problem definition section is to ensure that the large model understands the user's expectations and requirements, so as to generate more accurate optimization suggestions.

[0092] Among them, the context constraint section is used to provide the execution environment information and database structure of the abnormal SQL statement to the large model. The context constraint section provides a detailed description of the execution environment information and database structure related to the abnormal SQL statement. The context constraint section can include database type, table structure, index situation, data volume, user permissions, session parameters during execution, etc. Through this part of information, the large model can better understand the execution background of the SQL statement and ensure that the optimization suggestions are feasible and effective in the actual environment.

[0093] Among them, the analysis guidance segment is used to guide the large model to output optimization suggestions based on the execution path and execution plan of the abnormal SQL statement. The analysis guidance segment aims to guide the large model to output practical optimization suggestions based on the execution path and execution plan of the abnormal SQL statement. The analysis guidance segment can include analysis guidelines for the execution plan, potential performance bottlenecks, recommended improvement methods (such as index optimization, query rewriting, data partitioning, etc.) to help the large model focus on key issues and provide targeted solutions.

[0094] Among them, the security verification rules are used to ensure that the optimization suggestions output by the large model comply with security standards. The security verification rules refer to the standards and conditions for restricting the output content of the large model when generating optimization suggestions. The purpose of these rules is to ensure that the provided optimization suggestions comply with security standards and avoid potential security hazards, such as risks of SQL injection, data leakage, privilege escalation, etc. The security verification rules can include reviews of the legality, rationality, and compliance of the suggestions to protect the security of the system and data.

[0095] Among them, the security standards refer to a series of specifications and requirements followed when optimizing abnormal SQL statements, aiming to ensure that the optimization suggestions do not introduce security hazards and can effectively protect the security of the system and data. For example: The security standards can include technical security specifications, network security requirements, data security requirements, privacy information protection requirements, operation rationality requirements, and risk avoidance requirements, etc.

[0096] Optionally, the specific content of the preset prompt word template provided by the embodiments of the present invention can be shown in Table 3.

[0097] Table 3

[0098] Prompt segmented name Content Problem definition section {sql}\nThe above SQL statement started execution at {timestamp} and took a total of {cost} milliseconds. Your goal is to reduce the SQL execution time to within the baseline range of {threshold} milliseconds through various optimization means. Context constraint section The execution environment is as follows: the database type is {dbType}, and the version number is {dbVersion}; the involved schema is {schema}; the involved table names include {tableNames}; the involved indexes include {indexes}; the subquery is {subQuery}; the involved execution path is {executionSequence}; the detailed execution plan is {explain}. Analysis guidance section Please deeply analyze the execution path and execution plan of this SQL statement in combination with the database type and version number; if there is an index, evaluate the effectiveness of the index; if there is a connection in this SQL statement, analyze whether there is a problem with the connection order. Please output the result in the form of a JSON format object, and this object contains the following 2 fields: problem field: output the problems you found; guide: output feasible optimization suggestions. Precautions: Try to avoid full table scans; give priority to using indexes; use the same SQL statements analyzed in the past as a reference; Security verification rules It is recommended to prohibit generating UPDATE and DELETE operations without WHERE conditions. It is recommended to prohibit generating high-risk DDL operations. Do not return tables or fields that do not exist in the original SQL statement.

[0099] Among them, the prompt (Prompt) is a standard request message used to guide and stimulate the large model to generate optimization suggestions for abnormal SQL statements, and is used to generate optimization suggestions for abnormal SQL statements. For example: The prompt text constructed based on the preset prompt word template in the embodiments of the present invention can be: "{

[0100] "model":"Large model name",

[0101] "messages":[

[0102] {

[0103] "role": "system",

[0104] "content":"You are a senior database technology expert."

[0105] },

[0106] {

[0107] "role":"user",

[0108] "content":"Complete prompt text"

[0109] }

[0110] ,

[0111] "temperature":0

[0112] }”.

[0113] In the embodiments of the present invention, when analyzing an abnormal SQL statement, by reconstructing its execution context and obtaining the abstract syntax tree and execution plan, and integrating this information into a preset prompt template, it not only helps to clarify the nature of the problem and the optimization goal, but also provides necessary context information to ensure that the large model understands and reasonably analyzes the specific situation of SQL execution, effectively guiding the model to focus on the key issues in the execution path and plan, and ensuring the security and effectiveness of the output optimization suggestions, thereby improving the overall quality of the optimization suggestions output by the large model for abnormal SQL statements, making the optimization suggestions more targeted and practical, and then efficiently optimizing the execution performance of SQL statements.

[0114] S140. Call the large model to parse the prompt and obtain the optimization suggestions output by the large model for the abnormal SQL statement.

[0115] Among them, the large model, also known as the Large Language Model (LLM), is an artificial intelligence model with a large number of parameters and a complex structure.

[0116] Among them, the optimization suggestions refer to the improvement plans or solutions provided for the abnormal SQL statement. For example, the optimization suggestions can include specific suggestions for rewriting the SQL query to improve performance, reduce execution time or reduce resource consumption, and can also include more specific improvement means, such as modifying the structure of the SQL statement, adding indexes, adjusting the query logic or optimizing the database configuration, etc.

[0117] Specifically, the embodiment of the present invention can input prompt words into the big model to obtain the optimization suggestions of the big model for the output of abnormal SQL statements. In practical applications, the embodiment of the present invention can contact the server administrator of the local privately deployed big model to apply for an access token. After obtaining the token, use the curl tool that comes with the operating system to send the prompt words in the JSON message format obtained in the previous step to the big model through an HTTP POST request, so as to call the big model to parse the abnormal SQL statement and obtain the corresponding optimization suggestions. For example: The HTTP POST request can be:

[0118] “curl-X POST https: / / local artificial intelligence large model server domain name / v1 / chat / completions\

[0119] -H "Content-Type:application / json"\

[0120] -H "Authorization:Bearer Access Token"\

[0121] -d 'JSON message content'".

[0122] Furthermore, the embodiment of the present invention can perform subsequent processing according to the response status code of the large model. For example, when the received status code is 404, the large model server administrator is contacted to confirm the correctness of the interface URL. When the received status code is 500 to 503, the retry mechanism is triggered, and the maximum number of retries is 5. When the received status code is 200, it indicates that the call is successful, and the corresponding content is obtained and parsed.

[0123] After calling the large model analysis prompt word, the embodiment of the present invention can obtain a structured analysis report output by the large model including optimization suggestions for abnormal SQL statement output. For example, the analysis report can be shown in Table 4.

[0124] Table 4

[0125] Type Content Original SQL Original SQL statement with parameter placeholders Parameter desensitized SQL SQL statement with parameter placeholders replaced by desensitized parameter values Start timestamp Execution start timestamp Time consumption Time consumed by SQL statement execution (milliseconds) Reference threshold Baseline threshold (milliseconds) calculated based on a sliding time window SQL type Specific type of SQL statement: Data Query Language (DQL), Data Manipulation Language (DML), Data Control Language (DCL), Data Definition Language (DDL) Table name Table names used, from the Abstract Syntax Tree (AST) Field name Field names used, from the Abstract Syntax Tree (AST) Index Possible index names used, from the execution plan Problem Specific problem description, from the problem field returned by the large artificial intelligence model Optimization suggestions Specific optimization suggestions come from the "guide" field returned by the large AI model. For example, in the case of the MySQL database scenario, the large model will suggest using "FORCE INDEX" to manually specify that the query optimizer uses a specific index to execute the query, avoiding the use of inefficient indexes by the MySQL engine; in the Oracle database scenario, the large model will suggest adding "parallel" to enable parallel query to improve query performance; in the PostgreSQL database scenario, the large model will suggest using partial indexes to improve query performance.

[0126] The SQL statement optimization suggestion generation method provided by the present invention obtains SQL log monitoring data, where the SQL log monitoring data includes the execution timestamps and elapsed time values of multiple SQL statements in the operation log of the business system server; divides the SQL log monitoring data into multiple time window sequences according to a preset time window granularity, and respectively constructs a dataset to be processed for each time window sequence, where the dataset to be processed consists of the execution durations of each SQL statement within the time window sequence, and the execution duration is related to the execution timestamp and elapsed time value of the SQL statement; for any time window sequence: uses a preset median approximation algorithm to generate the median of the SQL execution elapsed time of the dataset to be processed, and uses the median of the SQL execution elapsed time to filter out abnormal SQL statements in the time window sequence; for any abnormal SQL statement: reconstructs the execution context of the abnormal SQL statement to obtain the abstract syntax tree and execution plan of the abnormal SQL statement, substitutes the abstract syntax tree and execution plan into a preset prompt template to generate a prompt; calls a large model to parse the prompt to obtain the optimization suggestion output by the large model for the abnormal SQL statement. By collecting and analyzing SQL log monitoring data, the present invention uses the median approximation algorithm to identify abnormal SQL statements, generates prompts based on a preset prompt template in combination with the abstract syntax tree and execution plan, and finally calls a large model to provide optimization suggestions for abnormal SQL statements, thereby helping to efficiently optimize the execution performance of SQL statements.

[0127] Optionally, based on one or more corresponding Figure 1 embodiments, in another optional embodiment provided by the embodiments of the present invention, using the median of the SQL execution elapsed time to filter out abnormal SQL statements in the time window sequence may specifically include:

[0128] Using the median of the SQL execution elapsed time and a preset scaling factor to obtain a dynamic deviation coefficient, where the preset scaling factor is a constant determined based on the interquartile range and the actual server load fluctuation characteristics; identifying SQL statements with elapsed time values greater than the dynamic deviation coefficient as abnormal SQL statements in the time window sequence.

[0129] The dynamic deviation coefficient is data calculated by using the median of the SQL execution elapsed time and a preset scaling factor, and is used to measure the abnormality degree of the SQL execution elapsed time.

[0130] Specifically, in the embodiments of the present invention, after arranging the execution durations of each SQL statement in the to-be-processed dataset in ascending order, the value at the middle position can be obtained as the median of the SQL execution time consumption, and then the median of the SQL execution time consumption can be adjusted by combining the classic method (Tukey's Rule) of outlier detection using the interquartile range (IQR) in statistics and the preset scaling factor determined according to the actual server load fluctuation characteristics, so as to more accurately reflect the normal execution time range under a specific load, and thus, within a specific time window, the SQL statements whose execution durations exceed the dynamic deviation coefficient can be detected and considered to have performance problems or abnormal conditions.

[0131] Further, in the embodiments of the present invention, the median of the SQL execution time consumption can be input into the formula:

[0132]

[0133] to obtain the dynamic deviation coefficient, where is the dynamic deviation coefficient, is the preset scaling factor; is the median of the SQL execution time consumption.

[0134] Optionally, the preset scaling factor can be 1.5.

[0135] In the embodiments of the present invention, the time window sequence can be traversed, and the SQL statements whose SQL execution time (SQL_execution_time) is greater than the set threshold (Threshold) can be screened out as abnormal SQL statements.

[0136] In the embodiments of the present invention, by combining the preset scaling factor determined according to the interquartile range and the actual server load fluctuation characteristics, the median of the SQL execution time consumption is effectively adjusted, the dynamic deviation coefficient is calculated, and then the SQL statements whose time consumption values are greater than the dynamic deviation coefficient are marked as abnormal SQL statements, so that abnormal SQL statements can be more effectively identified in a changing load environment.

[0137] Optionally, based on Figure 1 the method shown, as Figure 2 shown, a schematic flowchart of another implementation manner of the SQL statement optimization suggestion generation method provided by the embodiments of the present invention, step S110 may include:

[0138] S200. Divide the SQL log monitoring data into multiple time window sequences according to the preset time window granularity, and respectively construct the initial dataset of each time window sequence, where the initial dataset is composed of the execution durations of each SQL statement within the time window sequence.

[0139] Specifically, embodiments of the present invention can divide SQL log monitoring data into multiple consecutive time window sequences according to a preset time window granularity, establish a mapping relationship between the window numbers of each time window sequence and the execution records of the corresponding SQL statements, and then collect the execution durations of all SQL statements within each time window sequence to construct an initial data set for each time window sequence.

[0140] S210. Calculate the baseline change rate of the log content length corresponding to each time window sequence.

[0141] Among them, the baseline change rate is used to measure the degree of change in the log content length within a specific time window sequence. For example: embodiments of the present invention can compare the change degree between the mean value of the log content length corresponding to the time window sequence and a preset reference content length, and calculate the corresponding baseline change rate.

[0142] S220. Based on the baseline change rates of multiple time window sequences, determine whether the preset time window granularity is accurate. If so, execute step S230; if not, execute step S240.

[0143] S230. Use the initial data set as the data set to be processed.

[0144] S240. Adjust the preset time window granularity and re-execute step S200.

[0145] Specifically, embodiments of the present invention can compare the baseline change rates of adjacent 3 to 6 time window sequences, and analyze the relationship between their absolute values and a preset threshold (such as: 30%) to determine whether the current time window granularity is accurate. If it is found that the absolute value of the change rate between windows exceeds this threshold, it means that the current time window granularity cannot effectively reflect the change characteristics of the data. At this time, the adaptive adjustment mechanism is triggered to reduce the time window granularity and recalculate the baseline change rate to improve the monitoring accuracy. On the contrary, if the absolute value of the change rate between windows is less than the preset threshold, the current time window granularity can be kept unchanged, and the initial data set can be used as the data set to be processed for subsequent analysis.

[0146] Embodiments of the present invention can evaluate the accuracy of the current time window granularity by calculating the baseline change rate of the log content length corresponding to each time window sequence. If the baseline change rate indicates that the time window granularity cannot effectively reflect the data change characteristics, the window granularity can be adaptively adjusted to ensure more accurate data analysis. That is to say, only under the appropriate time window granularity can the obtained initial data set be used as the data set to be processed, which is conducive to further improving the recognition accuracy of abnormal SQL statements and thus improving the efficiency of generating optimization suggestions for abnormal SQL statements.

[0147] Optionally, in the aboveFigure 1 Based on one or more corresponding embodiments, in another alternative embodiment provided by the embodiments of the present invention, after invoking the large model to parse the prompt words and obtaining the optimization suggestions output by the large model for the abnormal SQL statements, the method may further include:

[0148] Obtaining feedback data of the optimization suggestions; adjusting the preset prompt word template and / or the model parameters of the large model according to the feedback data.

[0149] Among them, the feedback data is the information collected according to the implementation effect of the optimization suggestions provided by the large model. The feedback data may include the hit rate of the optimization suggestions, the change in execution duration, the scores of each suggestion, and relevant context information (such as database type, version number, etc.).

[0150] Specifically, in the embodiments of the present invention, for the optimization suggestions provided by the large model, MySQL, Oracle, and PostgreSQL databases may be respectively deployed on three virtual machines with the same configuration. To ensure the consistency of the test, tables with the same fields are created, and 100,000 identical test data are written into each of these three databases. Then, a simulation comparison test is conducted on the same SQL statement to evaluate the optimization effect and obtain the feedback data. The feedback data can be as shown in Table 5. It can be seen that after optimizing the abnormal SQL statement according to the optimization suggestions given by the large model, the execution time of the SQL statement is reduced, and the efficiency is improved by more than 30%.

[0151] Table 5

[0152] Database type Original execution time (milliseconds) Execution time after optimization according to AI suggestions (milliseconds) Efficiency improvement MySQL database 600 401 33% Oracle database 1230 750 39% PostgreSQL database 1300 831 36%

[0153] Among them, the model parameters refer to the variables used to control the behavior of the large model in the large model. The model parameters may include the learning rate, regularization coefficient, number of layers, and number of nodes of the large model.

[0154] Specifically, in the embodiments of the present invention, after testing and analyzing the optimization suggestions given by the large model to obtain the feedback data, the feedback data can be summarized and integrated into the existing log monitoring and operation and maintenance platform (such as Splunk) and knowledge base platform (such as Confluence) to realize functions such as visual display, dynamic interaction, real-time update, and feedback module of the feedback data, so as to adjust the preset prompt word template and the model parameters of the large model according to the feedback data.

[0155] The embodiments of the present invention can convert the abstract abnormal statistical data in the feedback data into intuitive charts and visual elements, and output the SQL execution time trend chart in the form of a line chart, etc., to help administrators and operation and maintenance personnel quickly identify the change trend and abnormal values and improve the analysis efficiency. For example: in the visualization interface, the top 100 SQL paths with the longest execution time and their execution time information can be highlighted.

[0156] Embodiments of the present invention can respond to the trend graph time switching requests initiated by administrators and operation and maintenance personnel, and quickly switch the SQL execution time-consuming change curves of different time ranges (such as the most recent 30 days, 7 days, and 72 hours, etc.). Users can obtain detailed performance data of corresponding points in the trend graph through simple click and hover operations and conduct in-depth analysis. When the user clicks on an abnormal SQL, relevant abnormal indexes and operators can be automatically located, the complete execution path can be displayed, and specific abnormal fields or table names can be highlighted in a prominent color to help quickly identify and solve problems.

[0157] Embodiments of the present invention can dynamically display the latest feedback data and continuously update it, enabling administrators and operation and maintenance personnel to grasp the change trend in real time and ensure the timeliness and accuracy of decision-making.

[0158] Embodiments of the present invention can build a closed-loop knowledge precipitation mechanism based on feedback data. R & D operation and maintenance personnel can provide accurate feedback on the implementation of optimization suggestions, promoting continuous improvement and experience accumulation. For example: Embodiments of the present invention can obtain the hit rate feedback and scoring of R & D operation and maintenance personnel for each optimization suggestion, and can also use key indicators such as the hit rate and execution duration of SQL optimization suggestions based on historical feedback data, and focus on the prompt words with the highest hit rate and score, dynamically optimize the prompt word template, and automatically adjust the model parameters to adapt to different load scenarios. The large model adopts a reinforcement learning mechanism and preferentially uses the best-performing strategies in the most recent feedback data when generating prompt words. When obtaining feedback data, information such as the database type, version number, and new features is recorded to enable the large model to better adapt to the operating environment. When the SQL optimization is not effective or the hit rate decreases, the feedback from R & D operation and maintenance personnel will trigger the self-adjustment mechanism of the large model, enabling it to continuously try and optimize strategies by interacting with the environment. In complex SQL scenarios, the index covering query scheme will be preferentially recommended.

[0159] Embodiments of the present invention can make the optimization suggestions output by the large model more accurate and effective by utilizing the feedback data of optimization suggestions, improve the hit rate of optimization suggestions, and significantly improve the intelligent level and overall performance of the system.

[0160] Optionally, based on Figure 1 the method shown, as Figure 3 shown, a schematic flowchart of another implementation manner of the SQL statement optimization suggestion generation method provided by embodiments of the present invention, step S100 may include:

[0161] S300. Obtain the operation logs of the business system service.

[0162] S310. Extract the SQL execution logs from the operation logs, where the SQL execution logs include multiple SQL statements.

[0163] S320. Desensitize each SQL statement in the SQL execution log to obtain the desensitized SQL execution log.

[0164] It can be understood that in scenarios such as e-commerce, finance, and cloud services, the processing of SQL execution logs needs to meet specific sensitive privacy protection requirements, and corresponding desensitization processing needs to be performed on each SQL statement in the SQL execution log.

[0165] Specifically, the embodiments of the present invention can identify sensitive information in the log through specific regular expressions and efficiently extract potential sensitive privacy fields using concurrent threads. Subsequently, the open-source encryption library Bouncy Castle is integrated, and the SM3 hashing algorithm is used to encrypt the extracted sensitive fields. At the same time, timeliness and device fingerprint salt values are injected to generate a collision-resistant input byte stream. Finally, a 256-bit fixed-length digest string is generated through cascaded hashing operations to replace the original log content, thereby achieving compliant desensitization processing and ensuring the security of sensitive information.

[0166] The embodiments of the present invention can use regular expressions to identify potential sensitive privacy fields from log texts. Specific regular expressions may include: "Name: [\u4e00-\u9fa5]{2,4}", "Enterprise Name: (([\u4e00-\u9fa5]+)([a-zA-Z\d$$]*)\s?(Company|Group|Enterprise|Studio|Center))", "Mobile Phone Number: 1[3-9]\d{9}", "Fixed Telephone Number: (0\d{2,3}-\d{7,8}|0\d{2,3}\d{7,8})", "Email Address: [a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\.[a-zA-Z0-9_-]+)+", "ID Number: \d{17}[\dXx]", "Unified Social Credit Code: ^[1-9A-GY]{1}

[1239] {1}[1-5]{1}[0-9]{5}[0-9A-Z]{10}$", and "Bank Account Number: \b\d{16,19}\b". The embodiments of the present invention can pre-compile the above regular expressions through the "java.util.regex.Pattern.compile()" method built into the JRE running environment to generate reusable pattern objects.

[0167] In the embodiments of the present invention, a "java.util.regex.Matcher" matcher can be instantiated based on a pattern object, and an iterative scan of the log text can be performed using an 8-thread concurrent mechanism, with a maximum processing volume of 10,000 logs per second for each thread. When an item matching the sensitive information feature is detected, the key-value pair data of these fields is automatically extracted and marked to ensure efficient and accurate capture of sensitive fields. Then, by calling the method "Security.addProvider(new BouncyCastleProvider())", the open-source encryption library Bouncy Castle that complies with international standards is integrated into the security provider list of the Java Runtime Environment (JRE).

[0168] In the embodiments of the present invention, the sensitive field capture queue obtained in the previous steps can be traversed, and for each original field value to be processed, an SM3 hash processor that complies with the standard of GM / T 0004-2012 "SM3 Cryptographic Hash Algorithm" can be instantiated using the MessageDigest tool to ensure the compliance and security of the algorithm. Next, preprocessing for compliance is performed on the original field: a timeliness salt value generated based on the timestamp is concatenated at the head of the field; the server device information is obtained by executing the dmidecode command, and the SM4 encryption algorithm is used to encrypt this information to generate a device fingerprint salt value, which is then appended to the end of the field to form a collision-resistant enhanced input byte stream. Then, the SM3 enhanced iterative engine performs a cascaded hash operation on the byte stream N∈[3,5] times, and the previous hash digest is introduced as an additional entropy source in each iteration, and finally a 256-bit fixed-length digest string is generated. Finally, the original log content is replaced with the hash digest to generate the SQL execution log.

[0169] S330. Scan the desensitized SQL execution log using a preset regular expression pattern library to generate SQL log monitoring data, where the preset regular expression pattern library is used to capture the execution timestamp and elapsed time value of the SQL statement through a context-aware algorithm.

[0170] Among them, the preset regular expression pattern library is a collection containing multiple pre-compiled regular expressions, including a thread ID identification pattern, a SQL statement start identifier, and a timestamp capture pattern.

[0171] Among them, the context-aware algorithm is an intelligent algorithm used to consider the context information of the data when analyzing the data.

[0172] In an embodiment of the present invention, the desensitized SQL execution log is scanned line by line using a preset regular expression pattern library. The regular expressions in the preset regular expression pattern library are designed with polymorphic matching rules. For example, the thread ID recognition pattern uses "([[A-Za-z0-9_()\-\s.:]+])" to effectively adapt to the differences in different system log formats. At the same time, a dynamic tracking mechanism is established to create a two-way hash mapping table between the thread ID and the SQL operation, and these functions are implemented through a context-aware algorithm: maintaining a thread state machine in real time to record the current life cycle stage of each thread ID. When the "==> Preparing:" marker is detected, the tracking context object is initialized and the start timestamp t1 is captured. When the "<==Total:" identifier is matched, the corresponding context is quickly located according to the thread ID, and the end timestamp t2 is recorded. By calculating the formula Δt = t2 - t1, the execution duration of the accurate SQL statement is obtained, and multi-dimensional structured SQL log monitoring data is generated. The SQL log monitoring data may include the original SQL text for maintaining the integrity of the statement structure, the desensitized SQL parameter version generated based on the desensitized log obtained in the foregoing steps, and the metadata of the execution stage (including the start timestamp, the end timestamp, and the elapsed time value).

[0173] In an embodiment of the present invention, by desensitizing the SQL execution log, the security of sensitive information can be effectively protected. Then, the desensitized SQL execution log is scanned using a preset regular expression pattern library, and the execution timestamp and elapsed time value of the SQL statement are captured in combination with a context-aware algorithm, thereby generating SQL log monitoring data, which not only enhances the privacy protection of the data, but also lays a solid foundation for subsequent performance analysis and anomaly detection, ensuring that accurate data can be obtained without leaking any sensitive information when processing and analyzing SQL execution performance, and meeting the privacy protection requirements in specific application scenarios.

[0174] Although the operations are depicted in a particular order, this should not be construed as requiring that the operations be performed in the particular order shown or in sequential order. In certain circumstances, multitasking and parallel processing may be advantageous.

[0175] It should be understood that the various steps recited in the method embodiments of the present invention may be executed in different orders and / or in parallel. In addition, the method embodiments may include additional steps and / or omit the steps shown. The scope of the present invention is not limited in this regard.

[0176] Corresponding to the above method embodiment, an embodiment of the present invention further provides a device for generating SQL statement optimization suggestions, and its structure is as Figure 4As shown in the figure, it may include: an SQL log monitoring data acquisition unit 10, a dataset to be processed construction unit 20, an abnormal SQL statement screening unit 30, a prompt word generation unit 40, and an optimization suggestion output unit 50.

[0177] The SQL log monitoring data acquisition unit 10 is used to acquire SQL log monitoring data, where the SQL log monitoring data includes the execution timestamps and elapsed time values of multiple SQL statements in the operation logs of the business system server.

[0178] The dataset to be processed construction unit 20 is used to divide the SQL log monitoring data into multiple time window sequences according to a preset time window granularity, and respectively construct the datasets to be processed for each time window sequence. The dataset to be processed consists of the execution durations of each SQL statement within the time window sequence, and the execution duration is related to the execution timestamp and elapsed time value of the SQL statement.

[0179] The abnormal SQL statement screening unit 30 is used for any time window sequence: generating the median of the SQL execution elapsed time of the dataset to be processed by using a preset median approximation algorithm, and screening out the abnormal SQL statements in the time window sequence by using the median of the SQL execution elapsed time.

[0180] The prompt word generation unit 40 is used for any abnormal SQL statement: reconstructing the execution context of the abnormal SQL statement, obtaining the abstract syntax tree and execution plan of the abnormal SQL statement, and substituting the abstract syntax tree and execution plan into a preset prompt word template to generate a prompt word.

[0181] The optimization suggestion output unit 50 is used to call a large model to parse the prompt word and obtain the optimization suggestions output by the large model for the abnormal SQL statement.

[0182] Optionally, the abnormal SQL statement screening unit 30 may specifically be used to obtain a dynamic deviation coefficient by using the median of the SQL execution elapsed time and a preset scaling factor, where the preset scaling factor is a constant determined based on the interquartile range and the actual server load fluctuation characteristics; identifying the SQL statements with elapsed time values greater than the dynamic deviation coefficient as abnormal SQL statements in the time window sequence.

[0183] Optionally, the preset prompt template includes a problem definition section, a context constraint section, an analysis guidance section, and a security verification rule. Among them, the problem definition section is used to determine the optimization goal of the large model for the abnormal SQL statement, the context constraint section is used to provide the execution environment information and database structure of the abnormal SQL statement to the large model, the analysis guidance section is used to guide the large model to output optimization suggestions based on the execution path and execution plan of the abnormal SQL statement, and the security verification rule is used to constrain the optimization suggestions output by the large model to meet the security standards. Among them, the security standards refer to a series of specifications and requirements followed when optimizing the abnormal SQL statement.

[0184] Optionally, the dataset to be processed construction unit 20 may include: an initial dataset construction subunit, a baseline change rate calculation subunit, and a time window granularity determination subunit.

[0185] The initial dataset construction subunit is used to divide the SQL log monitoring data into multiple time window sequences according to the preset time window granularity, and construct the initial datasets of each time window sequence respectively. Among them, the initial dataset is composed of the execution durations of each SQL statement within the time window sequence.

[0186] The baseline change rate calculation subunit is used to calculate the baseline change rate of the log content length corresponding to each time window sequence.

[0187] The time window granularity determination subunit is used to determine whether the preset time window granularity is accurate based on the baseline change rates of multiple time window sequences. If so, the initial dataset is used as the dataset to be processed. If not, the preset time window granularity is adjusted to trigger the initial dataset construction subunit.

[0188] Optionally, the SQL statement optimization suggestion generation device may further include: a feedback data acquisition unit and an adjustment unit.

[0189] The feedback data acquisition unit is used to obtain the feedback data of the optimization suggestion after the optimization suggestion output unit 50 calls the large model to parse the prompt and obtains the optimization suggestion output by the large model for the abnormal SQL statement.

[0190] The adjustment unit is used to adjust the preset prompt template and / or the model parameters of the large model according to the feedback data.

[0191] Optionally, the SQL log monitoring data acquisition unit 10 may include: an operation log acquisition subunit, a desensitization processing subunit, an SQL execution log extraction subunit, and an SQL log monitoring data generation subunit.

[0192] The operation log acquisition subunit is used to obtain the operation log of the business system service.

[0193] The SQL execution log extraction subunit is used to extract the SQL execution log from the running log, where the SQL execution log includes multiple SQL statements.

[0194] The desensitization processing subunit is used to perform desensitization processing on each SQL statement in the SQL execution log to obtain the desensitized SQL execution log.

[0195] The SQL log monitoring data generation subunit is used to scan the desensitized SQL execution log using a preset regular expression pattern library to generate SQL log monitoring data, where the preset regular expression pattern library is used to capture the execution timestamp and elapsed time value of the SQL statement through a context-aware algorithm.

[0196] Optionally, the preset median approximation algorithm is the T-Digest algorithm.

[0197] The SQL statement optimization suggestion generation device provided by the present invention obtains SQL log monitoring data, where the SQL log monitoring data includes the execution timestamp and elapsed time value of multiple SQL statements in the running log of the business system server; divides the SQL log monitoring data into multiple time window sequences according to the preset time window granularity, and constructs a dataset to be processed for each time window sequence respectively, where the dataset to be processed consists of the execution duration of each SQL statement within the time window sequence, and the execution duration is related to the execution timestamp and elapsed time value of the SQL statement; for any time window sequence: uses the preset median approximation algorithm to generate the median of the SQL execution elapsed time of the dataset to be processed, and uses the median of the SQL execution elapsed time to filter out the abnormal SQL statements in the time window sequence; for any abnormal SQL statement: reconstructs the execution context of the abnormal SQL statement to obtain the abstract syntax tree and execution plan of the abnormal SQL statement, substitutes the abstract syntax tree and execution plan into the preset prompt template to generate a prompt; calls the large model to parse the prompt to obtain the optimization suggestion output by the large model for the abnormal SQL statement. The present invention helps to efficiently optimize the execution performance of SQL statements by collecting and analyzing SQL log monitoring data, identifying abnormal SQL statements using the median approximation algorithm, generating prompts based on the preset prompt template in combination with the abstract syntax tree and execution plan, and finally calling the large model to provide optimization suggestions for abnormal SQL statements.

[0198] Regarding the device in the above embodiments, the specific manner in which each unit performs operations has been described in detail in the embodiments related to the method, and will not be elaborated here.

[0199] The SQL statement optimization suggestion generation device includes a processor and a memory. The above-mentioned SQL log monitoring data acquisition unit 10, the dataset to be processed construction unit 20, the abnormal SQL statement screening unit 30, the prompt word generation unit 40, and the optimization suggestion output unit 50, etc. are all stored in the memory as program units, and the corresponding functions are realized by the processor executing the above program units stored in the memory.

[0200] The processor contains a kernel, and the kernel retrieves the corresponding program units from the memory. One or more kernels can be set, and by adjusting the kernel parameters, SQL logs are collected and analyzed, and combined with the median algorithm and large model technology to achieve efficient SQL statement optimization.

[0201] An embodiment of the present invention provides a computer-readable storage medium, on which a program is stored, and when the program is executed by a processor, it implements the SQL statement optimization suggestion generation method.

[0202] An embodiment of the present invention provides a processor, and the processor is used to run a program, wherein when the program runs, it executes the SQL statement optimization suggestion generation method.

[0203] As Figure 5 shown, an embodiment of the present invention provides an electronic device 1000, which includes at least one processor 1001, at least one memory 1002 connected to the processor 1001, and a bus 1003; wherein, the processor 1001 and the memory 1002 complete communication with each other through the bus 1003; the processor 1001 is used to call program instructions in the memory 1002 to execute the above-mentioned SQL statement optimization suggestion generation method. The electronic device in this article can be a server, a PC, a PAD, a mobile phone, etc.

[0204] The present invention also provides a computer program product, which is suitable for executing a program initialized with the steps of the SQL statement optimization suggestion generation method when executed on an electronic device.

[0205] The present invention is described with reference to the flowcharts and / or block diagrams of methods, devices, electronic devices (systems), and computer program products according to embodiments of the present invention. It should be understood that each process and / or block in the flowchart and / or block diagram, and the combination of processes and / or blocks in the flowchart and / or block diagram can be implemented by computer program instructions. These computer program instructions can be provided to the processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable devices to generate a machine, so that the instructions executed by the processor of the computer or other programmable devices generate a device for realizing the specified functions in Figure 1 one process or multiple processes and / or blocks Figure 1 one block or multiple blocks.

[0206] In a typical configuration, an electronic device includes one or more processors (CPUs), a memory, and a bus. The electronic device may also include an input / output interface, a network interface, etc.

[0207] The memory may include non-permanent memory in the form of computer-readable media, random access memory (RAM), and / or non-volatile memory such as read-only memory (ROM) or flash RAM. The memory includes at least one storage chip. The memory is an example of computer-readable media.

[0208] Computer-readable media includes permanent and non-permanent, removable and non-removable media that can store information by any method or technology. The information can be computer-readable instructions, data structures, program modules, or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassette tapes, magnetic tape disk storage or other magnetic storage devices, or any other non-transitory media that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transitory computer-readable media such as modulated data signals and carrier waves.

[0209] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data for analysis, stored data, displayed data, etc.) involved in the present invention are all information and data authorized by the user or fully authorized by all parties, and the collection, use, and processing of relevant data need to comply with the relevant laws, regulations, and standards of relevant countries and regions.

[0210] It can be understood that before using the technical solutions disclosed in the embodiments of the present disclosure, the types, usage scopes, usage scenarios, etc. of the personal information involved in the present disclosure should be informed to the user and the user's authorization should be obtained in an appropriate manner in accordance with relevant laws and regulations.

[0211] In the description of the present invention, it should be understood that if terms such as "upper", "lower", "front", "rear", "left" and "right" are used to indicate the orientation or positional relationship, they are based on the orientation or positional relationship shown in the drawings. This is only for the convenience of describing the present invention and simplifying the description, rather than indicating or implying that the indicated position or element must have a specific orientation, be constructed and operate in a specific orientation. Therefore, it should not be construed as a limitation of the present invention.

[0212] It should be noted that in this text, relational terms such as first and second are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any actual relationship or order between these entities or operations. It should also be noted that the term "comprising", "including" or any other variant thereof is intended to cover non-exclusive inclusion, so that a process, method, commodity or device comprising a series of elements not only includes those elements, but also includes other elements not expressly listed, or elements inherent to such process, method, commodity or device. Without further limitation, an element defined by the statement "comprising an..." does not exclude the existence of additional identical elements in the process, method, commodity or device comprising the element.

[0213] Those skilled in the art should understand that the embodiments of the present invention can be provided as a method, a system or a computer program product. Therefore, the present invention can take the form of a complete hardware embodiment, a complete software embodiment or an embodiment combining software and hardware aspects. Moreover, the present invention can take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0214] The above are only the embodiments of the present invention and are not used to limit the present invention. For those skilled in the art, the present invention can have various changes and modifications. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention should be included within the scope of the present invention.

Claims

1. A method for generating SQL statement optimization suggestions, characterized in that: include: Obtaining SQL log monitoring data, wherein the SQL log monitoring data includes execution timestamps and time-consuming values ​​of multiple SQL statements in the operation log of the business system server; Divide the SQL log monitoring data into multiple time window sequences according to the preset time window granularity, and construct a to-be-processed data set for each of the time window sequences, wherein the to-be-processed data set is composed of the execution duration of each SQL statement in the time window sequence, and the execution duration is related to the execution timestamp and the time-consuming value of the SQL statement; For any of the time window sequences: using a preset median approximation algorithm to generate the median SQL execution time of the data set to be processed, and using the median SQL execution time to filter out abnormal SQL statements in the time window sequence; For any of the abnormal SQL statements: rebuilding the execution context of the abnormal SQL statement, obtaining an abstract syntax tree and an execution plan of the abnormal SQL statement, substituting the abstract syntax tree and the execution plan into a preset prompt word template, and generating a prompt word; The big model is called to parse the prompt word, and optimization suggestions output by the big model for the abnormal SQL statement are obtained.

2. The method according to claim 1, characterized in that The using the median SQL execution time to filter out abnormal SQL statements in the time window sequence includes: The dynamic deviation coefficient is obtained by using the median of the SQL execution time and a preset scaling factor, wherein the preset scaling factor is a constant determined based on the interquartile range and the actual server load fluctuation characteristics; In the time window sequence, the SQL statement whose time consumption value is greater than the dynamic deviation coefficient is identified as an abnormal SQL statement.

3. The method according to claim 1, characterized in that The preset prompt word template includes a problem definition segment, a context constraint segment, an analysis guidance segment and a security verification rule, wherein the problem definition segment is used to determine the optimization target of the big model for the abnormal SQL statement, the context constraint segment is used to provide the big model with the execution environment information and database structure of the abnormal SQL statement, the analysis guidance segment is used to guide the big model to output optimization suggestions based on the execution path and execution plan of the abnormal SQL statement, and the security verification rule is used to constrain the optimization suggestions output by the big model to comply with security standards, wherein the security standards refer to a series of specifications and requirements followed when optimizing the abnormal SQL statement.

4. The method according to claim 1, characterized in that: The step of dividing the SQL log monitoring data into multiple time window sequences according to the preset time window granularity, and constructing a data set to be processed for each of the time window sequences, includes: Divide the SQL log monitoring data into multiple time window sequences according to the preset time window granularity, and construct an initial data set for each of the time window sequences, wherein the initial data set consists of the execution duration of each SQL statement in the time window sequence; Calculate the baseline change rate of the log content length corresponding to each of the time window sequences; Based on the baseline change rate of the multiple time window sequences, determine whether the preset time window granularity is accurate. If so, use the initial data set as the data set to be processed. If not, adjust the preset time window granularity, re-execute the steps of dividing the SQL log monitoring data into multiple time window sequences according to the preset time window granularity, and constructing the initial data set of each time window sequence respectively.

5. The method according to claim 1, characterized in that After calling the big model to parse the prompt word and obtaining the optimization suggestion output by the big model for the abnormal SQL statement, the method further includes: Obtaining feedback data of the optimization suggestion; The preset prompt word template and / or the model parameters of the large model are adjusted according to the feedback data.

6. The method according to claim 1, characterized in that The obtaining of SQL log monitoring data includes: Obtaining the operation log of the business system service; Extracting an SQL execution log from the operation log, wherein the SQL execution log includes a plurality of SQL statements; Desensitizing each SQL statement in the SQL execution log to obtain the desensitized SQL execution log; The desensitized SQL execution log is scanned using a preset regular expression pattern library to generate SQL log monitoring data, wherein the preset regular expression pattern library is used to capture the execution timestamp and time value of the SQL statement through a context-aware algorithm.

7. The method according to any one of claims 1 to 6, characterized in that The preset median approximation algorithm is the T-Digest algorithm.

8. A device for generating SQL statement optimization suggestions, characterized in that: include: SQL log monitoring data acquisition unit, to-be-processed data set construction unit, abnormal SQL statement screening unit, prompt word generation unit and optimization suggestion output unit, The SQL log monitoring data obtaining unit is used to obtain SQL log monitoring data, wherein the SQL log monitoring data includes execution timestamps and time-consuming values ​​of multiple SQL statements in the operation log of the business system server; The to-be-processed data set construction unit is used to divide the SQL log monitoring data into multiple time window sequences according to a preset time window granularity, and respectively construct a to-be-processed data set of each of the time window sequences, wherein the to-be-processed data set is composed of the execution duration of each SQL statement in the time window sequence, and the execution duration is related to the execution timestamp and the time-consuming value of the SQL statement; The abnormal SQL statement screening unit is used for: for any of the time window sequences: using a preset median approximation algorithm to generate the median SQL execution time of the data set to be processed, and using the median SQL execution time to screen out abnormal SQL statements in the time window sequence; The prompt word generating unit is used for: for any of the abnormal SQL statements: rebuilding the execution context of the abnormal SQL statement, obtaining an abstract syntax tree and an execution plan of the abnormal SQL statement, substituting the abstract syntax tree and the execution plan into a preset prompt word template, and generating a prompt word; The optimization suggestion output unit is used to call the big model to parse the prompt word and obtain the optimization suggestion output by the big model for the abnormal SQL statement.

9. A computer-readable storage medium having a program stored thereon, characterized in that: When the program is executed by a processor, the method for generating SQL statement optimization suggestions according to any one of claims 1 to 7 is implemented.

10. An electronic device, characterized in that: The electronic device includes at least one processor, and at least one memory and a bus connected to the processor; wherein the processor and the memory communicate with each other through the bus; the processor is used to call program instructions in the memory to execute the SQL statement optimization suggestion generation method as described in any one of claims 1 to 7.

Images