Slow SQL statement optimization method and system, electronic equipment and storage medium

Abstract

The invention relates to the technical field of database optimization, in particular to a slow SQL (Structured Query Language) statement optimization method and system, electronic equipment and a storage medium, the slow SQL statement optimization method comprises the following steps: preprocessing a collected target slow SQL statement to obtain multi-modal data of the target slow SQL statement; inputting the multi-modal data of the target slow SQL statement into the multi-modal large model, and performing feature extraction and fusion to generate comprehensive semantic representation; generating an optimization suggestion of the target slow SQL statement according to the comprehensive semantic representation; the optimization suggestions are verified, and then the optimization suggestions which are verified to be qualified are executed in a database. Through multi-dimensional data acquisition, large-model deep analysis and automatic strategy execution, full-life-cycle quality detection and optimization of the slow SQL statement are realized.

Description

Technical Field

[0001] The present invention relates to the technical field of database optimization, and specifically relates to a method, system, electronic device, and storage medium for optimizing slow SQL statements. Background Art

[0002] In the field of modern information technology, the database, as a key component for storing, managing, and retrieving data, is of self-evident importance. With the continuous growth of enterprise data volume, an efficient and stable database system is crucial for ensuring business continuity and data security.

[0003] During the database operation process, slow SQL statements are a common problem leading to performance bottlenecks. These inefficient SQL statements consume a large amount of system resources, resulting in query latency, slow system response, and even potential crashes of the database service. Optimizing slow SQL statements can not only significantly improve the running efficiency of the database but also reduce the system load and enhance the user experience. Therefore, how to accurately identify and optimize these slow SQL statements is an important research direction in the database system.

[0004] With the expansion of the database scale and the increase in complexity, including the increase in the amount of data stored in the database and the general complexity of the system, it has become increasingly difficult to identify and optimize slow SQL statements. The emergence of automated tools provides a solution direction for this problem. Through automated tools, not only can the work efficiency be improved, the cost of manual intervention be reduced, but also the database performance can be more accurately optimized to ensure that the database continuously and stably supports business requirements. In this context, automated and intelligent methods play an important role in analyzing and optimizing slow SQL statements. Summary of the Invention

[0005] The purpose of the present invention is to provide a method, system, electronic device, and storage medium for optimizing slow SQL statements, which realizes the full-life cycle quality detection and optimization of slow SQL statements through multi-dimensional data collection, in-depth analysis of large models, and automated strategy execution.

[0006] To achieve the above purpose, the technical solutions adopted by the present invention are as follows:

[0007] In the first aspect, the present invention discloses a method for optimizing slow SQL statements, which includes:

[0008] Preprocessing the collected target slow SQL statements to obtain multi-modal data of the target slow SQL statements;

[0009] Inputting the multi-modal data of the target slow SQL statements into a multi-modal large model for feature extraction and fusion to generate a comprehensive semantic representation;

[0010] Based on the comprehensive semantic representation, match similar cases from the knowledge base using RAG, and generate optimization suggestions for slow SQL statements;

[0011] Verify the optimization suggestions, and then execute the qualified optimization suggestions in the database.

[0012] Furthermore, the multimodal data of the target slow SQL statement includes text data, structured data, and time-series data. The text data includes the SQL statement text and the SQL statement abstract syntax tree. The structured data includes the execution plan. The time-series data includes CPU utilization rate, memory occupancy rate, and disk I / O.

[0013] Furthermore, input the multimodal data of the target slow SQL statement into a multimodal large model for feature extraction and fusion to generate a comprehensive semantic representation, which specifically includes: using a language model to process the text data to obtain semantic vectors, using a graph neural network to extract the operator dependency relationship feature vectors in the execution plan, and using a temporal convolutional network to capture the periodic load fluctuation feature vectors of the time-series data; concatenate the semantic vectors, dependency relationship feature vectors, and periodic load fluctuation feature vectors, and then perform feature extraction and fusion through a feature fusion module based on the self-attention mechanism stacked multiple times to generate a comprehensive semantic representation.

[0014] Furthermore, store the executed optimization results as feedback data in the feedback knowledge base to form a self-feedback closed-loop system, realizing the dynamic iterative update of the knowledge base to support the quick invocation of historical optimization experiences.

[0015] Furthermore, after generating the comprehensive semantic representation, based on the comprehensive semantic representation, retrieve the top k keys with the highest similarity ranking and the description information corresponding to the keys in the pre-encoded vector knowledge base. The vector knowledge base combines the pre-training data of the multimodal large model with the description data of the external knowledge base through RAG technology, and constructs a vector index through vector similarity search and clustering library to realize dynamic RAG; generate optimization suggestions for the target slow SQL statement based on the k keys and the description information corresponding to the keys.

[0016] Furthermore, the collection of the target slow SQL statement includes: deploying dynamic tracking probes at the database nodes to monitor the query parser and execution engine of the database kernel in a low-intrusive manner, and collecting and capturing slow SQL statements and their context information in real time.

[0017] Furthermore, the optimization suggestions include index creation, query rewriting, or parameter tuning for the target slow SQL statement itself.

[0018] In a second aspect, the present invention discloses a slow SQL statement optimization system, which includes:

[0019] A preprocessing module for preprocessing the collected target slow SQL statements to obtain multimodal data of the target slow SQL statements;

[0020] A first generation module for inputting the multimodal data of the target slow SQL statements into a multimodal large model for feature extraction and fusion to generate a comprehensive semantic representation;

[0021] A second generation module for matching similar cases from a knowledge base based on the comprehensive semantic representation using RAG to generate optimization suggestions for the slow SQL statements;

[0022] A verification module for verifying the optimization suggestions;

[0023] An execution module for executing the qualified optimization suggestions in the database.

[0024] In a third aspect, the present invention discloses an electronic device, comprising: a memory and a processor, the memory and the processor being connected; the memory is used for storing programs; the processor is used for calling the programs stored in the memory to execute the above-mentioned slow SQL statement optimization method.

[0025] In a fourth aspect, the present invention discloses a storage medium, on which a computer program is stored, and when the computer program is run by a computer, it executes the above-mentioned slow SQL statement optimization method.

[0026] The present invention has the following unexpected beneficial effects:

[0027] 1. By collecting multimodal data of the target slow SQL statements, covering the text data, structured data, and time series data of the SQL statements, the present invention can more accurately locate the root cause of the slow SQL statement problems compared with a single data source. Through the powerful feature extraction and fusion capabilities of the multimodal large model, in-depth analysis of the multimodal data is carried out to capture the hidden associations and patterns between the data, and potential problems that are difficult to detect by traditional methods are discovered. For example, through learning a large amount of historical slow SQL statement data, the model can identify certain SQL statement writing habits that seem reasonable but actually cause performance problems in specific business scenarios, thus providing a more comprehensive perspective for optimization.

[0028] 2. Based on the comprehensive semantic representation generated by the multi-modal large model, the present invention can quickly generate optimization suggestions for target slow SQL statements. Compared with manual analysis and traditional rule-based optimization tools, it greatly shortens the time for formulating optimization plans. After the optimization suggestions are generated, they can be automatically verified in the database without manual execution and monitoring of each optimization step, which not only reduces the time cost of manual operations but also avoids additional time waste caused by human errors. In a large database system, a large number of slow SQL statements may be generated every day. This automated verification and execution method can greatly improve the overall optimization efficiency, quickly solve the problem of slow SQL statements, and ensure the normal operation of the business.

[0029] 3. The method of the present invention realizes the full-life-cycle quality detection of slow SQL statements. Starting from the generation of SQL statements, it continuously monitors their execution conditions, timely discovers potential performance problems and optimizes them. This preventive maintenance method can effectively avoid the situation where the performance of the database system drops sharply or even crashes due to the accumulation of slow SQL statements. By effectively optimizing slow SQL statements, the execution efficiency of the database is improved, and the demand for hardware resources by the database server is reduced.

[0030] 4. Before executing the optimization suggestions in the database, the method of the present invention conducts verification to ensure that the optimization strategy will not have a negative impact on the existing data and business logic. Only the optimization suggestions that have been verified to be effective will be officially applied to the database, which greatly reduces the risks of data loss, data inconsistency, or business interruption caused by incorrect optimization operations, and ensures the stability and reliability of the data system. BRIEF DESCRIPTION OF THE DRAWINGS

[0031] In order to more clearly illustrate the specific embodiments of the present invention or the technical solutions in the prior art, the following will briefly introduce the drawings required for use in the description of the embodiments or the prior art. Obviously, the drawings in the following description are only some embodiments of the present invention.

[0032] Figure 1 The flowchart showing an implementation manner of the slow SQL statement optimization method according to an embodiment of the present invention is shown.

[0033] Figure 2 The flowchart showing another implementation manner of the slow SQL statement optimization method according to an embodiment of the present invention is shown.

[0034] Figure 3 The flowchart showing another implementation manner of the slow SQL statement optimization method according to an embodiment of the present invention is shown.

[0035] Figure 4 The structural schematic diagram of the slow SQL statement optimization system according to an embodiment of the present invention is shown. Detailed implementation manners

[0036] The following will describe the implementation manners of the present invention with reference to the accompanying drawings and preferred embodiments. Those skilled in the art can easily understand other advantages and effects of the present invention from the content disclosed in this specification. The present invention can also be implemented or applied through other different specific implementation manners. Various details in this specification can also be modified or changed based on different viewpoints and applications without departing from the spirit of the present invention. It should be understood that the preferred embodiments are only for explaining the present invention, rather than limiting the protection scope of the present invention.

[0037] In one embodiment, the present invention discloses a method for optimizing slow SQL statements. Refer to Figure 1 as shown, which includes the following steps:

[0038] S1. Preprocess the collected target slow SQL statements to obtain multi-modal data of the target slow SQL statements. SQL (Structured Query Language) is a programming language used to manage relational database systems. An SQL statement is the basic statement unit in SQL and is used to perform various database operations. A slow SQL statement refers to an SQL statement that takes too long to execute in the database. Generally speaking, if the execution time of an SQL statement exceeds a specific threshold, it is determined to be a slow SQL.

[0039] S2. Input the multi-modal data of the target slow SQL statements into a multi-modal large model for feature extraction and fusion to generate a comprehensive semantic representation.

[0040] S3. Based on the comprehensive semantic representation, match similar cases from the knowledge base using RAG to generate optimization suggestions for the slow SQL statements. RAG refers to Retrieval-Augmented Generation, which is a method that combines information retrieval and language generation technologies and aims to use external knowledge sources to enhance the generation ability of language models. The RAG model includes a retriever and a generator. The retriever is responsible for retrieving information related to the input question from large-scale external knowledge sources (such as document libraries, knowledge bases, etc.), and the generator generates answers based on the retrieved information and the input context. In this way, RAG can use external knowledge to enrich the generated content and improve the accuracy and reliability of the answers.

[0041] S4. Verify the optimization suggestions, and then execute the qualified optimization suggestions in the database.

[0042] The present invention collects multimodal data of target slow SQL statements, covering text data, structured data, and time-series data of SQL statements. Compared with a single data source, it can more accurately locate the root cause of slow SQL statement problems. Through the powerful feature extraction and fusion capabilities of the multimodal large model, in-depth analysis of the multimodal data is carried out to capture the hidden associations and patterns between the data and discover potential problems that are difficult to detect by traditional methods. For example, through learning a large amount of historical slow SQL statement data, the model can identify SQL statement writing habits that seem reasonable but actually cause performance problems in specific business scenarios, thus providing a more comprehensive perspective for optimization.

[0043] Based on the comprehensive semantic representation generated by the multimodal large model and combined with the dynamic knowledge base constructed by the RAG technology, the present invention adopts a hybrid retrieval strategy. The accurate matching optimization mode can quickly generate optimization suggestions for the target slow SQL statement. Compared with manual analysis and traditional rule-based optimization tools, it greatly shortens the time for formulating optimization solutions. After the optimization suggestions are generated, they can be automatically verified in the database without manual execution and monitoring of each optimization step. This not only reduces the time cost of manual operations but also avoids additional time waste caused by human errors. In a large database system, a large number of slow SQL statements may be generated every day. This automated verification and execution method can greatly improve the overall optimization efficiency, quickly solve the slow SQL statement problem, and ensure the normal operation of the business.

[0044] The method of the present invention realizes the full-life-cycle quality detection of slow SQL statements. Starting from the generation of SQL statements, it continuously monitors their execution conditions, timely discovers potential performance problems and optimizes them. This preventive maintenance method can effectively avoid the situation where the performance of the database system drops sharply or even crashes due to the accumulation of slow SQL statements. Through the effective optimization of slow SQL statements, the execution efficiency of the database is improved, and the demand of the database server for hardware resources is reduced.

[0045] Before executing the optimization suggestions in the database, the method of the present invention can ensure that the optimization strategy will not have a negative impact on the existing data and business logic. Only the optimization suggestions that have been verified to be effective will be officially applied to the database, which greatly reduces the risks of data loss, data inconsistency, or business interruption caused by incorrect optimization operations, and guarantees the stability and reliability of the data system.

[0046] As a preferred embodiment of the present invention, see Figure 2As shown, the multimodal data of the target slow SQL statement includes text data, structured data, and time-series data. The text data includes the SQL statement text and the SQL statement abstract syntax tree. The structured data includes the execution plan. The time-series data includes CPU utilization, memory occupancy, and disk I / O.

[0047] Since the multimodal data of the target slow SQL statement contains text data, structured data, and time-series data, this diverse combination of data types provides comprehensive information about the slow SQL statement. The SQL statement text in the text data records the original query statement, and the SQL statement abstract syntax tree details the structure of the statement, enabling developers to clearly understand the logical architecture of the query. The execution plan in the structured data shows the specific steps and resource allocation when the database executes the SQL statement. The time-series data reflects the dynamic resource usage of the database server during the execution of the SQL statement. For example, when analyzing a complex multi-table join query, the SQL statement text and the abstract syntax tree can help determine the complexity and logical relationships of the query. The execution plan can point out which table join operations may be performance bottlenecks, and time-series data such as CPU utilization, memory occupancy, and disk I / O can further verify whether the slow query is caused by resource constraints.

[0048] In this preferred embodiment, by complementing each other with different types of data, a complete data portrait of the slow SQL statement is constructed, enabling the optimization process to consider problems comprehensively from multiple dimensions and avoiding analysis biases caused by the limitations of a single data type. For example, based solely on the execution plan, it may be found that a certain query step takes a long time, but it is not clear whether it is due to insufficient server resources or the logic of the query itself. Combining the time-series data can determine whether the CPU or memory resources reach the bottleneck during the execution of this step, thus more accurately locating the problem.

[0049] The SQL statement text and the SQL statement abstract syntax tree, as text data, help the multimodal large model deeply understand the semantics of the SQL statement. The multimodal large model can accurately grasp the query intent and logic by analyzing information such as keywords, table names, and column names in the SQL statement text, as well as the structure of the SQL statement abstract syntax tree. For example, for a query statement with complex conditions, the multimodal large model can identify which conditions are key filtering conditions and which are redundant conditions that can be optimized by analyzing the SQL statement abstract syntax tree, thus providing a more accurate basis for optimization suggestions.

[0050] As structured data, the execution plan clearly shows the specific steps and order of the database to execute SQL statements. Based on information such as operators and index usage in the execution plan, the multimodal large model can identify potential performance bottlenecks. For example, if the execution plan shows that a query uses a full table scan, the multimodal large model can combine other data to determine whether an appropriate index should be added to the relevant table to improve query efficiency.

[0051] Time series data such as CPU utilization, memory occupancy, and disk I / O can reflect the resource usage of the database server during the execution of SQL statements in real time. The large model can use this data to determine whether slow SQL statements are caused by resource competition or resource shortage. For example, if the CPU utilization remains high during the execution of a certain SQL statement, it may be necessary to consider optimizing the query to reduce the CPU's computational burden; if the disk I / O is too high, it may be necessary to optimize the data storage or query method to reduce disk read and write operations.

[0052] Moreover, time series data is real-time and can promptly reflect the running state of the database server. Through the real-time monitoring of this time series data, the system can promptly detect the occurrence of slow SQL statements and quickly analyze and optimize them. At the same time, due to the real-time nature of multimodal data, this optimization method can quickly respond to changes in database performance and promptly adjust the optimization strategy. During the business peak period, the load on the database may change significantly, causing some originally normal SQL statements to become slow SQL statements. By real-time monitoring multimodal data, the system can quickly detect these changes and generate more appropriate optimization suggestions based on the latest data to ensure that the database can maintain good performance under different load conditions.

[0053] The multimodal data structure covering text data, structured data, and time series data enables this optimization method to adapt to different types of databases and business scenarios. Whether it is a relational database or a non-relational database, whether it is a simple query or a complex business logic query, optimization can be carried out by collecting and analyzing the corresponding multimodal data. For example, in database systems in different industries such as e-commerce, finance, and healthcare, although the business requirements and data characteristics are different, this method can provide effective optimization solutions for slow SQL statements in different scenarios by adjusting the focus of data collection and analysis.

[0054] Exemplarily, after collecting and capturing slow SQL statements, multiple related information are queried and integrated according to the location information (execution template, execution time) of the slow SQL statements. The related information includes but is not limited to: resource monitoring integration, database configuration query, slow query log parsing, and execution plan extraction. Specifically, relevant resource indicators are quickly collected through Prometheus Exporter, including CPU utilization, memory usage, disk I / O, etc. Query database-related configuration parameters: buffer pool size, isolation level, connection number limit; extract relevant logs from the slow SQL statement log; obtain the corresponding execution plan through the EXPLAIN statement in the database.

[0055] After the information is collected, the SQL statement abstract syntax tree (AST) is generated through the syntax parser, irrelevant characters and comments are removed, and unstructured data (such as execution plans) are converted into a unified event stream format. A data set containing multiple dimensions is output to the multimodal large model analysis engine through a stream processing platform (such as Kafka). The data set content includes text data, structured data and time series data, wherein the text data includes SQL statement text and SQL statement abstract syntax tree, the structured data includes execution plan, and the time series data includes CPU utilization, memory occupancy and disk I / O.

[0056] As a preferred embodiment of the present invention, see Figure 2 As shown in the figure, the multimodal data of the target slow SQL statement is input into the multimodal large model for feature extraction and fusion to generate a comprehensive semantic representation. Specifically, the language model is used to process the text data to obtain a semantic vector. The language model's powerful semantic understanding ability can capture the subtle differences and potential meanings in the SQL statement. For example, for query statements with different expressions but similar semantics, the language model can map them to similar semantic vector spaces, thereby providing an accurate semantic basis for subsequent optimization analysis. It helps to identify SQL statements that are different on the surface but have the same actual functions, and avoid repeated and inefficient optimization of similar queries.

[0057] Graph neural networks are used to extract operator dependency feature vectors in execution plans. An execution plan is essentially a directed graph structure composed of operators. Graph neural networks can process this type of graph structure data well and mine the dependencies between operators and potential performance bottlenecks.

[0058] Use a temporal convolutional network to capture the periodic load fluctuation feature vectors of time-series data. The load of a database usually exhibits certain periodic changes over time, such as business peak and trough periods. The temporal convolutional network can learn these periodic patterns and predict potential performance issues in advance. For example, before the arrival of a business peak period, based on historical load fluctuation features, the system can optimize queries that may generate slow SQL statements in advance to avoid a sharp drop in performance during high load.

[0059] Concatenate the semantic vector, dependency feature vector, and periodic load fluctuation feature vector to achieve a preliminary integration of multi-source data features. This integration method retains the feature information of different types of data and provides a rich data basis for subsequent deep fusion. Different types of features complement each other and can more comprehensively describe the performance characteristics of slow SQL statements.

[0060] Perform feature extraction and fusion through multiple stacked feature fusion modules based on the self-attention mechanism to generate a comprehensive semantic representation. With this setting, it can adaptively focus on the important relationships between different features. The self-attention mechanism can assign different weights according to the importance of features, highlighting key features and suppressing irrelevant information. For example, in some cases, the operator dependencies in the execution plan may have a greater impact on query performance. The self-attention mechanism can automatically assign a higher weight to the dependency feature vector, thereby generating a more accurate comprehensive semantic representation. This deep fusion method can uncover the complex associations between features and improve the quality and effectiveness of the comprehensive semantic representation.

[0061] The generated comprehensive semantic representation contains multi-faceted information of text data, execution plan, and time-series data, and can provide a comprehensive and accurate basis for generating optimization suggestions. Based on such a comprehensive representation, the system can more deeply understand the performance issues of slow SQL statements and thus propose more targeted optimization suggestions. For example, for a slow SQL statement that is greatly affected by the periodicity of database load, the optimization suggestions generated in combination with the periodic load fluctuation feature vector may consider adopting different optimization strategies at different load stages, such as preferentially optimizing resource occupancy during high load and performing more comprehensive query rewriting during low load.

[0062] Since the comprehensive semantic representation can reflect the dynamic operating state of the database and the semantic features of SQL statements, this optimization method can better adapt to complex and changing database environments. In practical applications, the load, data distribution, and business requirements of the database may change at any time. The optimization suggestions based on the comprehensive semantic representation can be adjusted in a timely manner according to these changes. For example, when the hardware configuration of the database changes or the business logic is updated, the system can re-evaluate the performance issues of slow SQL statements based on the new comprehensive semantic representation and generate new optimization plans.

[0063] Specifically, the semantic vector, the dependency feature vector, and the periodic load fluctuation feature vector are concatenated into a matrix V:

[0064] V SQL is the semantic vector corresponding to the SQL statement text, V AST is the semantic vector corresponding to the SQL statement abstract syntax tree, V GNN is the operator dependency feature vector in the execution plan extracted using a graph neural network, V TCN is the periodic load fluctuation feature vector of the time series data captured using a temporal convolutional network.

[0065] Then, based on this matrix V, the multi-head attention mechanism is used to fuse these features:

[0066] v fused = MultiHead(V) = Concat(head1, head2, …, head h )W O ;

[0067] W O is a trainable output projection matrix with a dimension of (h·d v )d model , which is responsible for fusing the outputs of multiple attention heads into a unified semantic space.

[0068] The calculation formula for each head is:

[0069]

[0070] where Q i , K i , V i are the query, key, and value matrices obtained through linear transformation:

[0071] Q i = VW i Q , K i = VW i K , V i = VW i V

[0072] W i Q , W i K , W i V are the trainable weight matrices corresponding to the query, key, and value matrices of each head.

[0073] Map multi-modal features to a unified semantic space to generate a comprehensive semantic representation v fused 。

[0074] As a preferred embodiment of the present invention, store the executed optimization result as feedback data in the feedback knowledge base, align the vector spaces of the multi-modal pre-training data and the description data of the external knowledge base through the RAG technology, construct a dynamically updated vector index, and finally achieve retrieval enhancement with business context awareness, strengthening the adaptability of the multi-modal large model to complex scenarios (such as high-concurrency writing, distributed transactions).

[0075] The operating environment of the database is in dynamic change, including the growth of data volume, the adjustment of business logic, the change of hardware configuration, etc. By putting the optimization result as feedback data into the RAG knowledge base, the multi-modal large model can dynamically integrate external knowledge updates through the RAG technology, combine hybrid retrieval strategies, perceive the business context in real time, and timely adjust the priority of the optimization strategy to generate optimization suggestions that are strongly adapted to the current system state. For example, as the business develops, the data volume in the database increases significantly, and the originally effective SQL optimization strategy may no longer be applicable. The multi-modal large model can learn the new data distribution characteristics through the vector index dynamically updated by the RAG technology, so as to generate optimization suggestions that are more in line with the current data scale.

[0076] The feedback of each optimization result provides a valuable learning opportunity for the model. The multi-modal large model can evaluate whether the optimization suggestions generated by itself are correct and effective according to the actual optimization effect, and then adjust its own decision-making mechanism. For example, if a certain index optimization suggestion generated by the model before does not significantly improve the query performance after actual execution, through the feedback data, the multi-modal large model can learn the inapplicability of this index in the current database environment from the dynamically updated knowledge base, so as to avoid proposing similar suggestions in subsequent optimizations and improve the accuracy of the optimization suggestions.

[0077] The large number of optimization results stored in the feedback knowledge base is equivalent to a rich experience base. Over time, the multi-modal large model can learn various types of slow SQL statement problems and their corresponding optimization strategies from these feedback data, thus accumulating more optimization experience. This accumulation of experience helps the multi-modal large model to quickly and accurately generate effective optimization suggestions when facing new and unseen slow SQL statement problems, improving the generalization ability of the model.

[0078] The optimized results are fed back into model training, forming a complete closed-loop management process. From the discovery of slow SQL statements, the generation of optimization suggestions, the execution of optimization plans, to the feedback of optimization results and the update of the model, the whole process forms a continuously cycling and improving system. This closed-loop management ensures the sustainability and effectiveness of the optimization process, can timely detect and correct problems occurring in the optimization process, and continuously improve the quality of slow SQL statement optimization.

[0079] The feedback knowledge base not only provides data support for model training, but also provides a shared knowledge platform for the database management team. Team members can better understand the essence of slow SQL statement problems and optimization methods by viewing the optimization results and lessons learned in the feedback knowledge base, thereby improving the team's collaboration efficiency. For example, newly recruited database administrators can quickly master common slow SQL statement optimization techniques by learning the cases in the feedback knowledge base, reducing the learning cost.

[0080] With the continuous improvement of the performance of multi-modal large models, the accuracy and effectiveness of the generated optimization suggestions will also continuously improve, thereby reducing the workload of manual review and adjustment of optimization suggestions. Database administrators can focus more on dealing with complex problems that are difficult for the model to solve, improving work efficiency. And the optimization results in the feedback knowledge base can be saved and queried as historical records. When encountering similar slow SQL statement problems, team members can directly refer to previous optimization experiences, avoiding repeated analysis and optimization work, and reducing the optimization cost.

[0081] As a preferred implementation manner of the present invention, as shown in Figure 3 generating optimization suggestions for slow SQL statements by matching similar cases from the knowledge base based on the comprehensive semantic representation according to the RAG specifically includes: retrieving the top k keys with the highest similarity ranking and the description information corresponding to the keys in the pre-encoded vector knowledge base based on the comprehensive semantic representation, where the vector knowledge base combines the pre-training data of the multi-modal large model and the description data of the external knowledge base through the RAG technology, and realizes dynamic RAG by vector similarity search and clustering library to construct a vector index; subsequently generating optimization suggestions for the target slow SQL statement based on the k keys and the description information corresponding to the keys.

[0082] After generating a comprehensive semantic representation, by retrieving the k keys with the highest similarity ranking and the corresponding description information in the vector knowledge base, historical scenarios similar to the current slow SQL statement problem can be accurately found. The vector knowledge base combines the pre-trained data of the multimodal large model and the description data of the external knowledge base, covering a wealth of experience in processing slow SQL statements. For example, for a slow SQL statement caused by a complex multi-table association query, the system can find previously processed optimization cases of similar multi-table association queries from the knowledge base. The keys and description information in these cases can provide targeted solutions to the current problem, thereby greatly improving the relevance of the optimization suggestions to the actual problem.

[0083] The information in the vector knowledge base is a priori knowledge obtained through the accumulation and processing of a large amount of data. By retrieving this information, the multimodal large model can draw on previous successful optimization experiences and avoid re-exploring potentially invalid optimization paths. For example, for certain types of database operations (such as full-text search), the knowledge base may have recorded the best index settings and query rewriting methods. The model can directly refer to this information to generate optimization suggestions and improve the accuracy of optimization.

[0084] The vector knowledge base combines the pre-trained data of the multimodal large model and the description data of the external knowledge base, which makes the retrieved information source extensive. Different data sources may provide different optimization ideas and methods, thus bringing more possibilities for the generated optimization suggestions. The vector knowledge base builds a vector index through vector similarity search and clustering library to achieve dynamic RAG. The clustering operation can group similar slow SQL statement problems and optimization solutions, so that relevant categories can be quickly located during retrieval. Dynamic retrieval can adjust the retrieval strategy in real time according to the characteristics of the current comprehensive semantic representation to obtain information that better meets the needs. This method can discover more different types of optimization methods and avoid the limitations of the solution that may be caused by a single fixed retrieval method.

[0085] The process of searching in the vector knowledge base is relatively efficient, and can quickly find k keys and description information related to the current slow SQL statement problem. Compared with the traditional manual review of a large number of documents or manual analysis of historical data, this automated search method saves a lot of time. For example, when dealing with urgent slow SQL statement problems, the system can obtain relevant optimization suggestions from the knowledge base in a short time, solve the problem in time, and reduce the impact on the business.

[0086] By using the retrieval function of the vector knowledge base, we can avoid repeated analysis and research on similar slow SQL statement problems. When encountering similar problems, we can directly obtain existing experience and optimization solutions from the knowledge base without having to conduct a comprehensive problem analysis and solution design again. This not only improves optimization efficiency, but also reduces labor costs and resource consumption.

[0087] The vector knowledge base integrates the pre-training data of the multi-modal large model and the description data of the external knowledge base to form a unified knowledge platform. This platform facilitates the sharing of knowledge and experience among team members, enabling newly recruited database administrators to quickly access and utilize these valuable knowledge resources. For example, in a large database management team, the slow SQL statement problems and optimization solutions handled by different members can be stored in the knowledge base for other members to refer to and learn from. And as new slow SQL statement problems continuously emerge and optimization solutions are constantly updated, the vector knowledge base can also be dynamically updated. By regularly adding new optimization cases and experiences to the knowledge base, it ensures that the information in the knowledge base always remains up-to-date and most effective. At the same time, this accumulation and inheritance of knowledge also helps to improve the database optimization capabilities and levels of the entire team.

[0088] As a preferred embodiment of the present invention, the collection of the target slow SQL statement includes: deploying a dynamic tracing probe at the database node to monitor the query parser and execution engine of the database kernel in a low-intrusive manner, and collecting and capturing the slow SQL statement and its context information in real time.

[0089] Exemplarily, the dynamic tracing probe is an eBPF lightweight probe. Deploy the eBPF lightweight probe at the database node to monitor the query parser and execution engine of the database kernel in a low-intrusive manner, and capture the slow SQL statement and its context information (such as SQL text, execution time, lock wait events, etc.) in real time.

[0090] With such a setting, the comprehensiveness and accuracy of data collection are ensured. The low-intrusiveness guarantees the stable operation of the database, and the real-time nature of real-time capture meets the requirement of rapid response.

[0091] As a preferred embodiment of the present invention, as shown in Figure 2 the optimization suggestions include index creation, query rewriting, or parameter tuning for the target slow SQL statement itself.

[0092] For the target slow SQL statement, creating an appropriate index can significantly improve query performance. An index is like a directory of the database. A reasonable index can help the database quickly locate the required data and reduce the scope of data scanning.

[0093] Query Rewriting Optimization Logic Structure: Query rewriting is to optimize and adjust the logical structure of slow SQL statements to make them execute more efficiently. This may include simplifying complex nested queries, adjusting join orders, eliminating redundant subqueries, etc. For example, rewriting a slow SQL statement with multiple nested subqueries and poor performance into using join operations to achieve the same function may significantly improve query efficiency. By analyzing the semantics and execution plan of the target slow SQL statement and performing targeted query rewriting, the execution method of the query can be fundamentally optimized, and the performance problems caused by unreasonable logical structure can be solved.

[0094] Parameter Tuning to Adapt to the Operating Environment: Parameter tuning is to adjust relevant parameters according to the operating environment of the database and the characteristics of the target slow SQL statement to achieve the best performance. There are many parameters in the database that affect the execution of SQL, such as buffer size, sorting memory allocation, etc. By reasonably adjusting the parameters, the database can better adapt to the execution requirements of slow SQL statements and optimize the overall performance. Parameter tuning also helps to balance the allocation of various resources in the database system. For example, adjusting the buffer parameter can optimize the caching strategy of data in memory, enabling frequently used data to be accessed more quickly, while avoiding waste of memory due to too large a buffer. By reasonably adjusting the parameters, it is ensured that different types of queries (such as read and write operations) can be executed under appropriate resource conditions, improving the overall resource utilization rate and stability of the database.

[0095] Through index creation, query rewriting, and parameter tuning, the unnecessary consumption of database resources (such as CPU, memory, disk I / O, etc.) by the target slow SQL statement can be reduced. For example, optimized query rewriting may avoid full table scans, thereby reducing disk I / O operations and reducing the computing pressure on the CPU. Reasonable index creation can also reduce the memory occupation when reading data. This enables the database to more efficiently utilize limited resources, provide more resource support for other business queries, and improve the concurrent processing ability of the entire database system.

[0096] As a preferred embodiment of the present invention, verifying the optimization suggestions and then executing the qualified optimization suggestions in the database specifically includes:

[0097] Building a database image consistent with the production environment based on Docker containers and importing a certain proportion of data volume according to the actual problem. Pre-executing the relevant instructions in the optimization suggestions and comparing the execution plans before and after optimization (such as index hit rate, scanned row count) and resource consumption (such as CPU peak value, I / O throughput) to determine whether the optimization suggestion meets the expectations and reaches the production environment execution standard.

[0098] Before implementation in the database, a transaction rollback test is performed to ensure that the optimization operation will not destroy data consistency or cause deadlock. After the test is completed, the database two-phase commit (2PC) is performed to record transaction measures such as Redolog and execute SQL. If an abnormal situation occurs, rollback can be achieved in seconds.

[0099] Collect pre-defined key indicators, such as the reduction in execution time (ΔT), the reduction in lock conflicts (ΔL), the change in CPU utilization (ΔC), etc., and feed the indicator data back to the execution layer. Combined with the previously collected slow SQL information and optimization strategies, the data is fed back to the model knowledge base after sorting.

[0100] In another embodiment, the present invention also discloses a slow SQL statement optimization system, see Figure 4 As shown, the optimization system 10 includes a preprocessing module 11, a first generation module 12, a second generation module 13, a verification module 14 and an execution module 15. The preprocessing module 11 is used to preprocess the collected target slow SQL statements to obtain multimodal data of the target slow SQL statements. The first generation module 12 is used to input the multimodal data of the target slow SQL statements into the multimodal large model, perform feature extraction and fusion, and generate a comprehensive semantic representation. The second generation module 13 matches similar cases from the knowledge base based on the comprehensive semantic representation and RAG to generate optimization suggestions for slow SQL statements. The verification module 14 is used to verify the optimization suggestions. The execution module 15 is used to execute the verified optimization suggestions in the database.

[0101] In another embodiment, the present invention further discloses an electronic device, comprising a memory and a processor, wherein the memory and the processor are connected; the memory is used to store programs; and the processor is used to call the programs stored in the memory to execute the slow SQL statement optimization method described in any of the above embodiments.

[0102] The processor is connected to the memory via a bus, and the memory stores program codes. When the program codes are executed by the processor, the processor performs various steps in the above-mentioned slow SQL statement optimization method.

[0103] The processor may be a general-purpose processor, such as a central processing unit (CPU), a digital signal processor (DSP), an application specific integrated circuit (ASIC), a field programmable gate array (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, and can execute the slow SQL statement optimization method, steps, and logic block diagram disclosed in the embodiments of the present application. The general-purpose processor may be a microprocessor or any conventional processor, etc. The steps of the slow SQL statement optimization method disclosed in combination with the embodiments of the present application can be directly embodied as being executed and completed by a hardware processor, or executed and completed by a combination of hardware and software modules in the processor.

[0104] As a non-volatile storage medium, the memory can be used to store non-volatile software programs, non-volatile computer executable programs, and modules. The memory may include at least one type of storage medium, for example, it may include flash memory, hard disk, multimedia card, card-type memory, random access memory (RAM), static random access memory (SRAM), programmable read-only memory (PROM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), magnetic memory, magnetic disk, optical disk, and so on. The memory is any other medium that can be used to carry or store the desired program code in the form of instructions or data structures and can be accessed by a computer, but is not limited thereto. The memory in the embodiments of the present application may also be a circuit or any other system capable of implementing a storage function, for storing program instructions and / or data.

[0105] In another embodiment, the present invention also discloses a computer-readable storage medium, on which a computer program is stored. When the computer program is run by a computer, it executes the slow SQL statement optimization method described in any of the above embodiments.

[0106] The above embodiments are only preferred embodiments given to fully illustrate the present invention, and the protection scope of the present invention is not limited thereto. Equivalent substitutions or transformations made by those skilled in the art on the basis of the present invention are all within the protection scope of the present invention.

Claims

1. A method for optimizing slow SQL statements, characterized in that: include: Preprocess the collected target slow SQL statements to obtain multimodal data of the target slow SQL statements; Input the multimodal data of the target slow SQL statement into the multimodal large model to extract and fuse features and generate a comprehensive semantic representation; Based on the comprehensive semantic representation, similar cases are matched from the knowledge base based on RAG to generate optimization suggestions for slow SQL statements; The optimization suggestions are verified, and then the optimization suggestions that pass the verification are executed in the database.

2. The slow SQL statement optimization method according to claim 1, characterized in that: The multimodal data of the target slow SQL statement includes text data, structured data and time series data, the text data includes SQL statement text and SQL statement abstract syntax tree, the structured data includes execution plan, and the time series data includes CPU utilization, memory occupancy and disk I / O.

3. The slow SQL statement optimization method according to claim 2, characterized in that: Input the multimodal data of the target slow SQL statement into the multimodal large model for feature extraction and fusion to generate a comprehensive semantic representation. Specifically, the following steps are involved: Use language models to process text data to obtain semantic vectors, use graph neural networks to extract operator dependency feature vectors in execution plans, and use time convolutional networks to capture periodic load fluctuation feature vectors of time series data. The semantic vector, dependency feature vector and periodic load fluctuation feature vector are concatenated, and then feature extraction and fusion are performed through multiple stacked feature fusion modules based on the self-attention mechanism to generate a comprehensive semantic representation.

4. The slow SQL statement optimization method according to claim 1, characterized in that: The optimization results after execution are stored as feedback data in the RAG knowledge base to realize a knowledge feedback closed loop.

5. The slow SQL statement optimization method according to claim 1, characterized in that: According to the comprehensive semantic representation, similar cases are matched from the knowledge base based on RAG, and optimization suggestions for slow SQL statements are generated, specifically including: based on the comprehensive semantic representation, the k keys with the highest similarity ranking and the description information corresponding to the keys are retrieved in the pre-encoded vector knowledge base, and the vector knowledge base is constructed by vector similarity search and clustering library through RAG technology to realize dynamic RAG by combining pre-trained data of a multimodal large model and description data of an external knowledge base; optimization suggestions for target slow SQL statements are generated based on the k keys and the description information corresponding to the keys.

6. The slow SQL statement optimization method according to claim 1, characterized in that: The collection of the target slow SQL statements includes: deploying dynamic tracking probes on database nodes, monitoring the query parser and execution engine of the database kernel in a low-intrusive manner, and collecting and capturing slow SQL statements and their context information in real time.

7. The slow SQL statement optimization method according to claim 1, characterized in that: The optimization suggestions include index creation, query rewriting or parameter tuning for the target slow SQL statement itself.

8. A slow SQL statement optimization system, characterized in that: include: A preprocessing module is used to preprocess the collected target slow SQL statements to obtain multimodal data of the target slow SQL statements; The first generation module is used to input the multimodal data of the target slow SQL statement into the multimodal large model, perform feature extraction and fusion, and generate a comprehensive semantic representation; The second generation module generates optimization suggestions for slow SQL statements based on the comprehensive semantic representation and matching similar cases from the knowledge base based on RAG; A verification module, used for verifying the optimization suggestion; The execution module is used to execute the verified optimization suggestions in the database.

9. An electronic device, characterized in that: comprising a memory and a processor, wherein the memory is connected to the processor; The memory is used to store programs; The processor is used to call the program stored in the memory to execute the slow SQL statement optimization method according to any one of claims 1 to 7.

10. A storage medium, characterized in that: A computer program is stored thereon, and when the computer program is run by a computer, the slow SQL statement optimization method according to any one of claims 1 to 7 is executed.

Images