Slow query optimization method and device

Abstract

The embodiment of the invention provides a slow query optimization method and device, and the method comprises the steps: receiving an SQL statement under the condition that the current SQL statement is a slow query action, carrying out the analysis of the SQL statement, obtaining a basic abstract syntax tree, dynamically injecting the context information of the basic abstract syntax tree during the operation of a database, forming an enhanced abstract syntax tree, and carrying out the optimization of a slow query. Analyzing the enhanced abstract syntax tree to obtain an operation type of the SQL statement, thereby determining a preset routing strategy of the cue word, generating a structured cue word comprising database fingerprint features, inputting the cue word and the SQL statement into a plurality of preset large language models in parallel, integrating initial optimization modes output by the models to obtain an optimization mode, and outputting the optimization mode. And updating the SQL statement according to the optimization mode, sending the SQL statement to a database, and updating the historical execution statistical characteristics and the table relation weight matrix. According to the method, the defects that the optimization efficiency of an optimization mode is low, and the stability and usability of software are affected are effectively overcome, and the optimization efficiency of the slow query action is remarkably improved.

Description

Technical Field

[0001] This application relates to the field of computer technology, specifically to a method and apparatus for optimizing slow queries. Background Technology

[0002] During the long-term operation of the software, as data continues to accumulate in the production environment, the software's query performance gradually declines, leading to longer response times. The main reason for the decline in query performance is that the growth in data scale has not been matched with corresponding query optimization strategies. When the amount of data in the software reaches a certain threshold, if the index design is unreasonable, the query execution plan is not optimized, or there is a lack of historical data archiving and partitioning strategies, the efficiency of database queries will drop significantly, resulting in reduced system throughput and increased user request latency.

[0003] In existing technologies, query performance optimization is mainly achieved through Structured Query Language (SQL). The speed of SQL statement execution directly affects the length of time resources are occupied. However, the optimization method for slow queries is difficult to determine, resulting in long feedback time for optimization methods, low optimization efficiency, and impact on software stability and availability, thus reducing the user experience. Summary of the Invention

[0004] To address the problems in the existing technology, this application provides a slow query optimization method and apparatus, which can effectively solve the shortcomings of traditional technologies in terms of long feedback time, low optimization efficiency, and impact on software stability and usability, significantly improve the optimization efficiency of slow query actions, and enhance the user experience.

[0005] To solve at least one of the above problems, this application provides the following technical solution: Firstly, this application provides a slow query optimization method, including: When the current SQL statement's query action is a slow query action, the SQL statement is received and parsed to obtain a basic abstract syntax tree. The context information of the database where the SQL statement is located is dynamically injected into the basic abstract syntax tree to obtain an enhanced abstract syntax tree. The context information includes the table relationship weight matrix and historical execution statistics. Parse the enhanced abstract syntax tree to obtain the operation type of the SQL statement, determine the preset routing strategy of the prompt words based on the operation type, generate structured prompt words including the fingerprint features corresponding to the current database, input the prompt words and SQL statement in parallel to multiple preset large language models, receive the initial optimization methods output by each preset large language model, integrate the initial optimization methods to obtain the optimization method; The SQL statement is updated based on the optimization method, and the updated SQL statement is sent to the database. The database then verifies the performance improvement data of the updated SQL statement and updates the historical execution statistics and table relationship weight matrix based on the performance improvement data.

[0006] Furthermore, it also includes: obtaining real-time system view data and multiple tables of the database where the SQL is located, extracting foreign key relationships between multiple tables based on the real-time system view data, and generating a table relationship weight matrix based on the foreign key relationships. The matrix elements in the table relationship weight matrix represent the association strength between each table. Query the database performance monitoring table to obtain the historical execution statistics of each SQL template within the preset historical time period. The historical execution statistics include average execution time, index hit rate, and lock wait time. Based on the preset syntax tree modification interface, the table relation weight matrix and historical execution statistics are injected into the corresponding nodes of the basic abstract syntax tree to obtain the enhanced abstract syntax tree.

[0007] Furthermore, it also includes: scanning all tables in the database according to a preset period, determining index feature groups based on the distribution of index types and the ranking of hot indexes in the tables, determining transaction feature groups based on the average transaction market and deadlock frequency in the tables, and determining storage feature groups based on the tablespace fragmentation rate in the tables. The index feature group, transaction feature group, and storage feature group are encoded according to the preset encoding dimensions to obtain the encoding results; The encoding results are reduced in dimensionality according to the preset feature dimensions to obtain the fingerprint features corresponding to the database.

[0008] Furthermore, the operation type includes selection type, and also includes: when the operation type is selection type, obtaining the current database's index usage preference and transaction isolation level, and determining the database's header fingerprint characteristics based on the index usage preference and transaction isolation level; The enhanced abstract syntax tree is converted into a nested structure in JSON format, and structured prompt words are generated based on the header fingerprint features and the nested structure.

[0009] Furthermore, it also includes: based on the database performance monitoring table, extracting operation statements with the same operation type as the current SQL statement and the historical execution plans corresponding to the operation statements, and identifying the historical execution plans corresponding to the operation statements as negative samples, so as to check structured prompt words based on negative samples; Obtain the preset server utilization threshold corresponding to the current database, and determine the preset server utilization threshold as one of the hard constraints during the generation of structured prompt words.

[0010] Furthermore, it also includes: determining the difference in execution time and memory consumption between the SQL statement before the update and the SQL statement after the update; If the execution time difference is greater than the preset execution time difference and the memory consumption difference is less than the preset memory consumption difference, the optimization method corresponding to the updated SQL statement is determined as an effective optimization, and the historical execution statistics and table relationship weight matrix are updated based on the optimization method.

[0011] Furthermore, it also includes: obtaining historical optimization data corresponding to historical optimization methods, including historical initial optimization methods and preset large language models corresponding to each historical initial optimization method; The adoption rate of each preset large language model is determined based on historical optimization data, and the matching degree between each current initial optimization method and the enhanced abstract syntax tree is determined. The optimization weights of each initial optimization method are determined based on the adoption rate and matching degree. The initial optimization methods with optimization weights greater than the preset optimization weights are determined as optimization methods. If the optimization weights corresponding to each initial optimization method are not greater than the preset optimization weights, or if there are at least two initial optimization methods whose optimization weights are greater than the preset optimization weights, a prompt message will be sent to prompt the user to select an optimization method from the initial optimization methods.

[0012] Secondly, this application provides a slow query optimization device, comprising: Abstract module 10 is used to receive and parse SQL statements when the current SQL statement's query action is a slow query action, obtain a basic abstract syntax tree, dynamically inject the context information of the database where the SQL statement is located into the basic abstract syntax tree, and obtain an enhanced abstract syntax tree. The context information includes the table relationship weight matrix and historical execution statistics features. Optimization module 20 is used to parse the enhanced abstract syntax tree, obtain the operation type of the SQL statement, determine the preset routing strategy of the prompt words based on the operation type, generate structured prompt words including the fingerprint features corresponding to the current database, input the prompt words and SQL statement in parallel to multiple preset large language models, receive the initial optimization methods output by each preset large language model, integrate the initial optimization methods to obtain the optimization method; The update module 30 is used to update SQL statements based on optimization methods and send the updated SQL statements to the database, so that the database can verify the performance improvement data of the updated SQL statements and update the historical execution statistics features and table relationship weight matrix based on the performance improvement data.

[0013] Thirdly, this application provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of the slow query optimization method.

[0014] Fourthly, this application provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the slow query optimization method described above.

[0015] Fifthly, this application provides a computer program product, including a computer program / instructions that, when executed by a processor, implement the steps of the slow query optimization method described above.

[0016] As can be seen from the above technical solution, this application provides a slow query optimization method and apparatus. Innovatively, when the current SQL statement is a slow query action, it receives and parses the SQL statement to obtain a basic abstract syntax tree (abstract syntax tree). Database runtime context information is dynamically injected into the basic abstract syntax tree to form an enhanced abstract syntax tree (ESB). The context information includes a table relation weight matrix and historical execution statistics. The operation type of the SQL statement is obtained by parsing the enhanced abstract syntax tree, thereby determining the preset routing strategy for the prompt words. Structured prompt words including database fingerprint features are generated. The prompt words and SQL statement are input in parallel into multiple preset large language models. The initial optimization methods output by each model are integrated to obtain the optimization method. The SQL statement is updated according to the optimization method and sent to the database. Performance improvement data is verified, and historical execution statistics and the table relation weight matrix are updated. This embodiment parses the SQL statement of a slow query and injects context information to determine the actual execution status of the SQL statement in the current database. This makes the resulting optimization methods more targeted, effectively improving the performance of the SQL statement corresponding to the slow query and making its execution more efficient. By processing multiple prompt words and SQL statements in parallel using multiple preset large language models, multiple initial optimization methods can be obtained, giving full play to the advantages of each preset large language model and providing initial optimization methods for the SQL statement from multiple perspectives, thereby improving the quality of the optimized SQL statement. This method effectively solves the shortcomings of traditional techniques, such as long feedback time, low optimization efficiency, and impact on software stability and usability. It significantly improves the optimization efficiency of slow queries and enhances the user experience. Attached Figure Description

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

[0018] Figure 1 This is a flowchart illustrating the slow query optimization method in the embodiments of this application; Figure 2 This is a structural diagram of the slow query optimization device in the embodiments of this application; Figure 3 This is a schematic diagram of the structure of the electronic device in the embodiments of this application.

[0019] Figure label: Electronic device 9600, central processing unit 9100, memory 9140, communication module 9110, input unit 9120, audio processor 9130, display 9160, power supply 9170, buffer memory 9141, application / function storage unit 9142, data storage unit 9143, driver storage unit 9144, antenna 9111, speaker 9131, microphone 9132. Detailed Implementation

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

[0021] The acquisition, storage, use, and processing of data in this application all comply with the relevant provisions of national laws and regulations.

[0022] In view of the problems existing in the prior art, this application provides a slow query optimization method and apparatus. Innovatively, when the current SQL statement is a slow query action, it receives and parses the SQL statement to obtain a basic abstract syntax tree (abstract syntax tree). Database runtime context information is dynamically injected into the basic abstract syntax tree to form an enhanced abstract syntax tree (ESB). The context information includes a table relation weight matrix and historical execution statistics. The operation type of the SQL statement is obtained by parsing the enhanced abstract syntax tree, thereby determining the preset routing strategy for the prompt words. Structured prompt words including database fingerprint features are generated. The prompt words and SQL statement are input in parallel into multiple preset large language models. The initial optimization methods output by each model are integrated to obtain the optimization method. The SQL statement is updated according to the optimization method and sent to the database. Performance improvement data is verified, and historical execution statistics and the table relation weight matrix are updated. This embodiment parses the SQL statement of a slow query action and injects context information to determine the actual execution status of the SQL statement in the current database. This makes the optimization methods more targeted and effectively improves the performance of the SQL statement corresponding to the slow query action, making its execution more efficient. By processing prompt words and SQL statements in parallel through multiple preset large language models, multiple initial optimization methods can be obtained, giving full play to the advantages of each preset large language model and providing initial optimization methods for SQL statements from multiple perspectives, thereby improving the quality of the optimized SQL statements.

[0023] To effectively address the shortcomings of traditional optimization techniques, such as long feedback times, low optimization efficiency, and impact on software stability and usability, and to significantly improve the optimization efficiency for slow query operations and enhance user experience, this application provides an embodiment of a slow query optimization method. See [link to embodiment]. Figure 1 The slow query optimization method specifically includes the following: Step S101: If the current SQL statement's query action is a slow query action, receive and parse the SQL statement to obtain the basic abstract syntax tree, dynamically inject the context information of the database where the SQL statement is located into the basic abstract syntax tree to obtain the enhanced abstract syntax tree.

[0024] The contextual information includes the table relationship weight matrix and historical execution statistics.

[0025] Optionally, in this embodiment, when the query action of the current SQL statement is a slow query action, the SQL statement is received and parsed. During the parsing process, a professional syntax analysis tool can be used to convert the SQL statement into a basic abstract syntax tree (AST). The basic AST performs a preliminary analysis of the syntactic structure of the SQL statement.

[0026] The process of converting an SQL statement into a basic abstract syntax tree (BST) can be summarized as follows: Lexical analysis decomposes the SQL statement into tokens; syntax analysis then performs structured processing on each token according to SQL syntax rules, resulting in the BST. The BST presents the syntactic components of the SQL statement in a tree-like structure, including various query clauses (e.g., SELECT, FROM, WHERE), table references, conditional expressions, and various operators.

[0027] In addition, to enable the basic abstract syntax tree to more accurately reflect the execution of the SQL statement corresponding to the slow query action in the actual database environment, a dynamic injection mechanism is introduced to dynamically inject the context information of the database runtime where the SQL statement is located into the basic abstract syntax tree, resulting in an enhanced abstract syntax tree with richer and more accurate expression.

[0028] The contextual information includes, but is not limited to, key data such as the table relationship weight matrix and historical execution statistics. The table relationship weight matrix is ​​used to represent the degree of association between tables in the database and the frequency of data interaction. The historical execution statistics are used to represent information such as the execution time and resource consumption of similar SQL statements in the past.

[0029] The table relationship weight matrix is ​​derived from the relationships between tables in the database. These relationships include, but are not limited to, foreign key constraints, data references, and actual business logic connections. The table relationship weight matrix numerically quantifies the tightness of the relationship between tables; higher weight values ​​indicate a tighter relationship and more frequent data interaction between the two tables. For example, in an e-commerce database, there might be frequent join queries between the order table and the user table to retrieve user order information; therefore, the weight values ​​of the order table and the user table in the table relationship weight matrix would be relatively high. Furthermore, the weight values ​​can be dynamically adjusted based on historical query records and business scenarios to adapt to the actual usage and data changes of the database.

[0030] Historical execution statistics are obtained by collecting and recording the execution status of historical SQL statements in the database. Execution status includes, but is not limited to, execution time, resource consumption, number of rows returned, and number of rows scanned. Resource consumption includes, but is not limited to, Central Processing Unit (CPU) utilization, memory usage, and the number of input / output (I / O) operations. Historical execution statistics can reflect the performance advantages and disadvantages of different types of SQL statements during actual execution, as well as the performance bottlenecks of the database when processing various queries.

[0031] Furthermore, during dynamic injection, the corresponding table relationship weight matrix and historical execution statistical feature data can be obtained from the database runtime environment based on the tables and operation types involved in the current SQL statement, and then associated and merged with the nodes in the basic abstract syntax tree, thereby enabling the enhanced abstract syntax tree to incorporate contextual information related to the actual database runtime environment.

[0032] For example, for SQL statements involving multiple table joins, the corresponding weight information from the table relationship weight matrix can be injected into the table join nodes in the basic AST. For SQL statements that include aggregate functions or sorting operations, the execution time and resource consumption data of the above operations from historical execution statistics can be injected into the relevant operation nodes.

[0033] This embodiment achieves accurate reflection of the actual database environment's operating status through deep parsing of the SQL statements corresponding to slow query actions and dynamic injection of context information.

[0034] Step S102: Parse the enhanced abstract syntax tree to obtain the operation type of the SQL statement, determine the preset routing strategy of the prompt words based on the operation type, generate structured prompt words including the fingerprint features corresponding to the current database, input the prompt words and SQL statement in parallel to multiple preset large language models, receive the initial optimization methods output by each preset large language model, integrate the initial optimization methods to obtain the optimization method.

[0035] Optionally, this embodiment parses the enhanced abstract syntax tree to obtain the operation type of the SQL statement. The operation type includes, but is not limited to, SELECT type and non-selection type. The non-selection type includes, but is not limited to, INSERT type, UPDATE type and DELETE type.

[0036] The selection type is used to retrieve specific data from the database. During the parsing of the enhanced AST, the complexity of the query action and potential performance issues can be determined based on features such as the conditional expressions, table joins, and aggregate function usage in the query statement. For example, queries involving multiple complex filtering conditions, multi-table joins, and grouping aggregations may have issues with long execution times and high resource consumption, which can be addressed through targeted optimization.

[0037] Insert types are used to add new data records to a database table. When parsing SQL statements for insert types, it's important to focus on the scale of the inserted data, the table's structural characteristics, and the performance impact of each database's transaction processing mechanism. Table structural characteristics include, but are not limited to, whether it contains indexes and triggers. For example, inserting a large amount of data into a table with multiple complex indexes may result in index maintenance operations consuming significant resources and time, thus affecting insertion efficiency.

[0038] Update types are used to modify existing data records in a database table. When parsing SQL statements with update types, in addition to focusing on table structure and index factors, it's also important to analyze the complexity of the update conditions and the impact of the update operation on data consistency and transaction processing. For example, when update conditions involve complex expression calculations or joins across multiple tables, it can lead to performance degradation and potential data consistency issues.

[0039] The delete type is used to remove specified data records from a database table. When processing delete type SQL statements, factors such as table relationships, foreign key constraints, and the database's transaction log mechanism can be considered to determine the execution cost and performance overhead of the delete operation. For example, deleting a record in a main table containing a large number of child records may involve cascading deletions of data in multiple related tables, leading to longer operation execution time and increased resource consumption.

[0040] Furthermore, after determining the operation type of the SQL statement, the corresponding prompt word preset routing strategy can be retrieved from the preset routing strategy library. These preset routing strategies are derived from extensive database practice experience and include optimization methods for various common SQL operation types and performance problem scenarios. The prompt word preset routing strategy provides precise guidance and direction for generating optimization solutions based on the preset large language model, ensuring that the generated prompt words fully reflect the characteristics of the SQL statement and the database's operating environment, thereby improving the quality and feasibility of the optimization method.

[0041] Based on the pre-defined routing strategy using the prompt words and the fingerprint characteristics of the current database, structured prompt words are generated. The fingerprint characteristics are a set of features that uniquely identify and reflect the database's architectural features, data distribution patterns, index status, and performance characteristics. These fingerprint characteristics can be extracted and calculated using database analysis tools and techniques.

[0042] The database's architectural characteristics include, but are not limited to, whether the current database is a relational or non-relational database, whether it adopts a distributed architecture, and its sharding strategy; data distribution patterns include, but are not limited to, data storage layout, hot data distribution, and data growth trends; index status includes, but is not limited to, index type, coverage, and index usage frequency; performance characteristics include, but are not limited to, the current database's response time, throughput, and resource utilization under different load conditions. Combining the database's fingerprint characteristics with a pre-defined routing strategy for prompt words can generate highly targeted and adaptive structured prompt words.

[0043] After generating structured prompts, the prompts and the original SQL statements are input in parallel into multiple pre-defined large language models. These pre-defined large language models have the ability to analyze, understand, and optimize SQL statements. Each pre-defined large language model adopts a different algorithm architecture, training dataset, and optimization objective to ensure that the initial optimization method obtained by processing SQL statements through different pre-defined large language models has its own unique advantages and characteristics.

[0044] In addition, after obtaining the initial optimization method, the initial optimization method is integrated to obtain the optimization method. The integration may include, but is not limited to, deduplication, conflict detection and resolution, and optimization scheme sorting and screening.

[0045] Deduplication is used because different pre-defined large language models may propose similar or identical initial optimization methods. It removes duplicate initial optimization methods to avoid redundant initial optimization methods from affecting the simplicity and operability of the optimization method.

[0046] Conflict detection and resolution is used when there may be conflicts or inconsistencies between the initial optimization methods proposed by different predefined large language models. Based on predefined conflict resolution rules and priority strategies, conflicting initial optimization methods can be evaluated and screened, and the optimal initial optimization method can be retained.

[0047] The optimization scheme ranking and filtering is used to rank and filter the initial optimization methods based on factors such as the expected performance improvement, implementation difficulty, and impact on database architecture and data consistency. Priority is given to initial optimization methods that can significantly improve SQL statement performance, are easy to implement, and have minimal impact on database stability, thereby obtaining a comprehensive optimization scheme.

[0048] This embodiment implements deep parsing and operation type determination based on enhanced abstract syntax trees. Combined with the pre-defined routing strategy of prompt words and the structured prompt words generated by database fingerprint features, it can provide accurate optimization direction and guidance for large language models. It fully considers the syntactic structure and semantic features of SQL statements as well as the actual operating environment of the database, avoids the inapplicability problems that may be caused by general optimization methods, and improves the pertinence and feasibility of the optimization scheme.

[0049] This embodiment implements a multi-model parallel processing approach, inputting prompts and SQL statements into multiple preset large language models in parallel. This fully utilizes the computational resources and intelligent advantages of each preset large language model, improving the efficiency of optimization method generation. When faced with complex SQL statement optimization tasks, it can quickly generate multiple optimization ideas and solutions, and then integrate and optimize them. Compared to a single-model processing approach, the multi-model parallel approach effectively avoids time delays and optimization limitations, ensuring the efficiency and timeliness of the optimization process and guaranteeing the high-performance operation of the database system.

[0050] Step S103: Update the SQL statement based on the optimization method and send the updated SQL statement to the database so that the database can verify the performance improvement data of the updated SQL statement and update the historical execution statistics feature and table relationship weight matrix based on the performance improvement data.

[0051] Optionally, this embodiment updates the original SQL statement according to the optimization method, so that the updated SQL statement maintains the original semantics and functions while improving performance, thereby improving the execution efficiency and resource utilization in the database.

[0052] In addition, the updated SQL statements are sent to the database for execution. During execution, performance improvement data for the updated SQL statements is recorded and verified. This performance improvement data includes, but is not limited to, changes in query response time, changes in the execution time of insert and update operations, changes in resource consumption, and changes in transaction throughput. By comparing and analyzing the performance data with that of the original SQL statements, the actual effect and value of the optimization methods can be clearly determined.

[0053] Meanwhile, dynamically updating historical execution statistics and table relationship weight matrices based on performance improvement data returned by the database allows the database runtime context information to reflect the latest optimization results and data changes in a timely manner. For example, if an updated SQL statement exhibits better performance during execution, such as a shorter query time, the execution time record for the corresponding query pattern will be updated in the historical execution statistics, providing a more accurate reference for optimizing similar SQL statements.

[0054] This embodiment enables the optimization method to self-evolve and optimize through continuous performance verification and feedback updates. As the database runtime increases and SQL statements are continuously executed, the accumulated performance data and context information will become richer and more accurate. This allows the determination of optimization methods for other SQL statements to be more in line with the actual operating conditions of the database and business needs, thereby continuously improving the overall performance of the database.

[0055] This embodiment achieves accurate, efficient, and continuous performance optimization capabilities by deeply parsing the SQL statements corresponding to slow query actions, dynamically injecting context information, optimizing multiple models in parallel, and continuously providing performance feedback and updates. This provides performance assurance for the database when handling complex business scenarios and large-scale data.

[0056] In some embodiments, the underlying abstract syntax tree is dynamically injected with the runtime context information of the database where the SQL statement resides, resulting in an enhanced abstract syntax tree, including: Obtain real-time system view data and multiple tables of the database where the SQL is located. Extract foreign key relationships between multiple tables based on the real-time system view data, and generate a table relationship weight matrix based on the foreign key relationships. The matrix elements in the table relationship weight matrix represent the association strength between each table. Query the database performance monitoring table to obtain the historical execution statistics of each SQL template within the preset historical time period. The historical execution statistics include average execution time, index hit rate, and lock wait time. Based on the preset syntax tree modification interface, the table relation weight matrix and historical execution statistics are injected into the corresponding nodes of the basic abstract syntax tree to obtain the enhanced abstract syntax tree.

[0057] Optionally, in this embodiment, real-time system view data is extracted from the database containing the SQL statement corresponding to the slow query action, and multiple tables in the database are obtained at the same time. The real-time system view data includes, but is not limited to, the current running status of the database, session information, and lock status.

[0058] Based on the acquired real-time system view data, foreign key relationships between multiple tables are analyzed and extracted. Foreign key relationships represent the way tables are associated, indicating the dependencies and associations between them. Furthermore, a table relationship weight matrix is ​​generated based on these foreign key relationships. In this matrix, each element represents the strength of the association between tables, thus quantifying the tightness of the relationships between multiple tables.

[0059] In addition, query the performance monitoring table in the database and extract the historical execution statistics for each SQL template within the preset historical period. These historical execution statistics include, but are not limited to, average execution time, index hit rate, and lock wait time.

[0060] The average execution time represents the average time consumed by an SQL template during its historical execution. It can intuitively reflect the execution efficiency of each SQL template and provide a basic time-dimensional indicator for evaluating query performance.

[0061] Index hit rate is used to represent the efficiency of the database in retrieving data through indexes during the execution of SQL templates. Index hit rate characterizes the efficiency of the database in quickly locating data through indexes. Index hit rate is positively correlated with the efficiency of locating data and negatively correlated with inefficient database operations.

[0062] Lock wait time represents the time consumed by an SQL template during execution while waiting for database lock resources. Excessive lock wait time may lead to a decrease in concurrency performance and affect the overall database throughput.

[0063] In addition, by using the preset syntax tree modification interface, the obtained table relationship weight matrix and the historical execution statistics features obtained from the query are dynamically injected into the corresponding nodes of the basic abstract syntax tree. In the above process, it is necessary to ensure that the context information can be deeply integrated with the node structure of the basic abstract syntax tree.

[0064] For example, for SQL statements involving multi-table joins, the corresponding join strength data from the table relationship weight matrix is ​​injected into the nodes related to the table join operation in the basic abstract syntax tree.

[0065] By dynamically injecting data, the basic abstract syntax tree is enriched and expanded to form an enhanced abstract syntax tree. The enhanced abstract syntax tree retains the syntactic structure information of the original SQL statement and also incorporates the context information of the database runtime, providing a more comprehensive, accurate and practically guiding information foundation for SQL statement optimization.

[0066] This embodiment integrates contextual information with syntactic structure, enabling optimization methods to be developed based on richer and more accurate semantic information, thus avoiding optimization deviations caused by insufficient or inaccurate information.

[0067] In some embodiments, generating structured prompts including fingerprint features corresponding to the current database includes: Scan all tables in the database according to a preset cycle, determine index feature groups based on the distribution of index types and the ranking of hot indexes in the tables, determine transaction feature groups based on the average transaction market and deadlock frequency in the tables, and determine storage feature groups based on the tablespace fragmentation rate in the tables. The index feature group, transaction feature group, and storage feature group are encoded according to the preset encoding dimensions to obtain the encoding results; The encoding results are reduced in dimensionality according to the preset feature dimensions to obtain the fingerprint features corresponding to the database.

[0068] Optionally, this embodiment scans all tables in the database according to a preset period, analyzes the distribution of index types and the ranking of hot indexes in all tables. The index type distribution is obtained by statistically analyzing the distribution ratio of different index types in each table, and is used to determine the usage frequency and coverage of various indexes. Index types include, but are not limited to, B-tree indexes, hash indexes, and bitmap indexes. The ranking of hot indexes is determined based on the actual query load and performance, that is, the ranking of indexes that are frequently accessed in queries and play a key role in improving query performance. Combining the index type distribution and the ranking of hot indexes yields an index feature group that can reflect the usage status of database indexes.

[0069] Similarly, all tables are scanned according to a preset period to calculate the average transaction market and the deadlock frequency. The average transaction market represents the efficiency and complexity of transaction processing, while the deadlock frequency represents the frequency at which the database's concurrency performance and stability are severely impacted. Based on the average transaction market and the deadlock frequency, a group of transaction characteristics describing the database's transaction properties is determined.

[0070] During the scanning process, the tablespace fragmentation rate of each table is determined. The tablespace fragmentation rate characterizes the storage space utilization efficiency and the rationality of the storage structure of each table. Based on the tablespace fragmentation rate, a storage feature group is constructed to characterize the features of the database storage layer.

[0071] In addition, the index feature group, transaction feature group and storage feature group are encoded according to the preset encoding dimension, and each feature group is converted into the corresponding encoding result. For example, the preset encoding dimension can be 128 bits.

[0072] In addition, to improve data processing efficiency, remove redundant information and highlight key features, dimensionality reduction is performed on the high-dimensional encoding results according to the preset feature dimension. The preset feature dimension can be the target dimension determined after comprehensively considering the feature expression ability and the degree of information retention. The dimensionality reduction operation can be implemented by dimensionality reduction algorithms such as principal component analysis and linear discriminant analysis to obtain concise and representative fingerprint features corresponding to the database. Among them, the fingerprint features can accurately and efficiently reflect the core features and key information of the database's operation status.

[0073] This embodiment realizes a complete process from multi-dimensional feature scanning of the database, feature group determination, encoding to dimensionality reduction, providing integrated and accurate feature support for database performance optimization, identification, classification and other operations, improving the overall management level and performance of the database, and enabling the database to operate stably and efficiently in complex business scenarios.

[0074] In some embodiments, the operation type includes a selection type, generating structured prompts including fingerprint features corresponding to the current database, including: When the operation type is selection, obtain the current database's index usage preference and transaction isolation level, and determine the database's header fingerprint characteristics based on the index usage preference and transaction isolation level; The enhanced abstract syntax tree is converted into a nested structure in JSON format, and structured prompt words are generated based on the header fingerprint features and the nested structure.

[0075] Optionally, the operation type in this embodiment includes a selection type. When the operation type is a selection type, the database performance monitoring module is consulted and the optimizer logs are queried to determine the usage frequency, hit rate, and performance data of each index in the query action under different query scenarios. The performance data is analyzed to determine the database's preference for different index types when processing selection type operations. Index types include clustered indexes and non-clustered indexes.

[0076] For example, if a clustered index is frequently used in a query, it indicates that the clustered index is highly suitable for selection operations in the current database environment. This is reflected in index usage preferences, where the characteristic of a clustered index is its ability to quickly locate data rows.

[0077] The database configuration parameters are queried to obtain the current transaction isolation level. Transaction isolation levels include, but are not limited to, Read Uncommitted, Read Committed, Repeatable Read, or Serializable. Transaction isolation levels can affect the consistency and concurrency performance of data reads. During the generation of prompt terms, the impact of the transaction isolation level on the semantics and execution plan of selection-type query actions is determined. For example, under the Read Committed isolation level, selection-type query actions can read updated data from other committed transactions, which may affect the timeliness and accuracy requirements of query results.

[0078] In addition, based on the obtained index usage preferences and transaction isolation levels, the impact on the performance and semantics of select type operations is analyzed, thereby determining the header fingerprint features of the database. The header fingerprint features are the core identifiers that reflect the characteristics of the database when processing select type query actions. They combine key information on query optimization based on index usage patterns and transaction isolation features, providing a key basis for generating targeted structured prompts.

[0079] Furthermore, the Enhanced Abstract Syntax Tree (EAST) is converted into a nested structure in JSON (a language type) format. During the conversion process, the nodes of the EAST can be traversed, and the type, attributes, hierarchical relationships, and dependencies between nodes are recorded in detail. These records are then organized in JSON key-value pairs to convert the EAST into a nested structure. The node types include, but are not limited to, table reference nodes, conditional expression nodes, and projection column nodes. The node attributes include, but are not limited to, table name, column name, and operators.

[0080] For example, in a SELECT query involving multiple tables, the nested structure in JSON format can include the nested relationship between table reference nodes and their associated condition nodes.

[0081] Furthermore, by combining head fingerprint features and nested JSON structures, structured prompts can be generated. These prompts can be obtained according to preset prompt generation rules and templates. The structured prompts integrate database index usage preferences, transaction isolation levels, and nested JSON structures of query actions in a standardized format, providing accurate and easy-to-parse guidance information for optimization methods.

[0082] For example, structured suggestions may include advising the optimizer to prioritize the use of indexes with high hit rates, adjusting data consistency guarantees based on transaction isolation levels, and identifying potential optimization points by combining nested structures, such as adjusting the association order and pushing down conditional filtering.

[0083] This embodiment enables the generation of accurate structured suggestions based on database index usage preferences, transaction isolation levels, and nested structures when the operation type is selection. This provides comprehensive and efficient targeted support for optimizing slow database queries, improves the execution efficiency and performance of selection operations, and ensures the efficient operation of the database in complex query scenarios.

[0084] In some embodiments, after generating structured prompts based on head fingerprint features and nested structures, the method further includes: Based on the database's performance monitoring table, extract the operation statements with the same operation type as the current SQL statement and the historical execution plans corresponding to the historical operation statements. The historical execution plans corresponding to the operation statements are identified as negative samples, so as to check structured prompt words based on negative samples. Obtain the preset server utilization threshold corresponding to the current database, and determine the preset server utilization threshold as one of the hard constraints during the generation of structured prompt words.

[0085] Optionally, this embodiment extracts historical operation statements and corresponding historical execution plans that are the same type of operation as the current SQL statement based on the database's performance monitoring table. Among them, the historical execution plans are more likely to have problems such as poor performance and excessive resource consumption. The historical execution plans are identified as negative samples. By analyzing the negative samples, the inefficient strategies and inappropriate execution plan characteristics adopted in the past when handling the same type of operation can be determined.

[0086] Furthermore, a check model or rule set based on negative samples can be constructed to compare the generated structured suggestions with the negative samples in detail. During the check, attention should be paid to whether the optimization methods recommended in the structured suggestions are similar to the inefficient patterns in the negative samples. For example, there may be unreasonable table scan order, excessive use of high-cost sorting operations, or inappropriate index selection. This will enable timely detection and elimination of optimization suggestions that may have potential performance risks in the structured suggestions, ensuring the quality and effectiveness of the suggestions.

[0087] In addition, the preset server utilization threshold corresponding to the current database is obtained. The preset server utilization threshold is set comprehensively based on factors such as server hardware performance, business load requirements, and database operation stability. During the generation of structured prompts, the preset server utilization threshold is determined as one of the hard constraints, indicating that during the generation and execution of optimization suggestions, it is necessary to strictly ensure that the real-time utilization of the server does not exceed the preset threshold.

[0088] In practice, when optimized SQL statements are expected to cause server real-time utilization to reach or approach a preset server utilization threshold, the optimization scheme can be adjusted based on hard constraints. For example, by limiting the number of concurrently executed queries, adjusting the execution order of queries, and increasing resource allocation restrictions for queries, it can be ensured that the server's resource usage remains within a controllable range when executing optimized SQL statements, thus avoiding problems such as server performance degradation, response latency, or even service interruption caused by excessive resource consumption.

[0089] This embodiment achieves resource usage control while ensuring the reliability of the optimization method through negative sample checking and server utilization threshold constraints. It provides a stable and efficient guarantee mechanism for optimizing database query actions, enabling the database to maintain a high-efficiency and stable operating state when processing various query actions, and meeting the strict requirements of business systems for database performance and reliability.

[0090] In some embodiments, the database verifies performance improvement data for the updated SQL statements and updates historical execution statistics and table relationship weight matrices based on the performance improvement data, including: Determine the difference in execution time and memory consumption between the SQL statement before and after the update in the database. If the execution time difference is greater than the preset execution time difference and the memory consumption difference is less than the preset memory consumption difference, the optimization method corresponding to the updated SQL statement is determined as an effective optimization, and the historical execution statistics and table relationship weight matrix are updated based on the optimization method.

[0091] Optionally, this embodiment can determine the execution time of the SQL statement before the update and the SQL statement after the update. The execution time is the time taken from receiving the execution instruction from the database to returning the query result. The difference between the above execution times is determined, that is, the execution time difference = execution time before the update - execution time after the update. The execution time difference reflects the improvement in time efficiency of the updated SQL statement.

[0092] During the execution of SQL statements, the server's memory usage is monitored in real time. For SQL statements before and after the update, the amount of memory used during execution is recorded. The difference in memory consumption can be expressed as: memory consumption difference = memory consumption before update - memory consumption after update. This difference reflects the improvement in resource utilization efficiency of the optimized SQL statement.

[0093] In addition, preset execution time difference and preset memory consumption difference can be set according to database performance optimization goals and resource status. When the execution time difference is greater than the preset execution time difference and the memory consumption difference is less than the preset memory consumption difference, it indicates that the optimized SQL statement has a significant improvement in time efficiency and memory usage efficiency, and the corresponding optimization method is effective optimization.

[0094] Furthermore, after confirming the effectiveness of the optimization method, the historical execution statistics and table relationship weight matrix are updated. For the historical execution statistics, the old corresponding data is replaced with new performance data such as the execution time and memory consumption of the updated SQL statements. At the same time, the relevant statistical characteristics of the SQL template are updated, including but not limited to average execution time, index hit rate, and lock wait time. The updated data can characterize the actual performance of the SQL statement after optimization.

[0095] For updating the table relationship weight matrix, the strength of the relationships between tables is reassessed based on the execution of the optimized SQL statement. If the optimized SQL statement involves changes in the table join method or the introduction of new tables, the corresponding element values ​​in the table relationship weight matrix are adjusted according to the actual situation. For example, if the optimized query statement joins two tables that were originally less related and the query performance is improved, it indicates that the relationship between the two tables has become more important in the current business scenario, and their weight values ​​in the table relationship weight matrix are increased accordingly.

[0096] This embodiment achieves the goal of accurately evaluating optimization effects and updating historical data in a timely manner, ensuring that the database can continuously learn and improve from previous optimization methods. Each effective optimization method makes the historical execution statistics and table relationship weight matrix closer to the actual operation requirements, thereby providing a more reliable basis for the next round of SQL statement optimization, realizing the continuous improvement of database performance and the continuous accumulation of optimization effects, and ensuring that the database is always in a high-efficiency and stable state during long-term operation.

[0097] In some embodiments, integrating the initial optimization method yields an optimized method, including: Obtain historical optimization data corresponding to historical optimization methods. Historical optimization data includes historical initial optimization methods and preset large language models corresponding to each historical initial optimization method. The adoption rate of each preset large language model is determined based on historical optimization data, and the matching degree between each current initial optimization method and the enhanced abstract syntax tree is determined. The optimization weights of each initial optimization method are determined based on the adoption rate and matching degree. The initial optimization methods with optimization weights greater than the preset optimization weights are determined as optimization methods. If the optimization weights corresponding to each initial optimization method are not greater than the preset optimization weights, or if there are at least two initial optimization methods whose optimization weights are greater than the preset optimization weights, a prompt message will be sent to prompt the user to select an optimization method from the initial optimization methods.

[0098] Optionally, this embodiment obtains historical optimization data corresponding to historical optimization methods from the optimization history of the database. The historical optimization data details the historical initial optimization method at each optimization, that is, the initial optimization suggestions proposed by each preset large language model, as well as the preset large language model identifier corresponding to each historical initial optimization method, so as to distinguish the contributions and characteristics of different preset large language models.

[0099] In addition, based on the historical optimization data, the ratio of the number of times the initial optimization methods proposed by each preset large language model were adopted and successfully implemented to the total number of proposals is used to determine the adoption rate of each preset large language model. The adoption rate reflects the reliability and effectiveness of different preset large language models in the historical optimization methods.

[0100] By comparing and analyzing each current initial optimization method with the enhanced abstract syntax tree, the degree of fit between each initial optimization method and the syntax structure, semantic information, and database context of the SQL statement can be evaluated. The matching degree between each current initial optimization method and the enhanced abstract syntax tree can be determined by the matching algorithm. The higher the matching degree, the more the initial optimization method fits the actual characteristics and optimization requirements of the current SQL statement. That is, the matching degree is positively correlated with the actual characteristics of the SQL statement.

[0101] In addition, the optimization weights of each initial optimization method are determined based on the adoption rate of each preset large language model and the matching degree between each current initial optimization method and the enhanced abstract syntax tree. The optimization weights include the historical performance of each preset large language model and the applicability of the current initial optimization method.

[0102] An initial optimization method with an optimization weight greater than a preset optimization weight is determined as the optimization method. The preset optimization weight is used to ensure that the optimization method has sufficient reliability and effectiveness.

[0103] In addition, when the optimization weights corresponding to each initial optimization method are not greater than the preset optimization weights, or when there are at least two initial optimization methods whose optimization weights are greater than the preset optimization weights, indicating that the reliability and effectiveness of all initial optimization methods have not met the requirements, or when there are multiple initial optimization methods with high reliability and effectiveness, making it difficult to determine a unique optimization method, a prompt message is sent. The prompt message may include, but is not limited to, the optimization weights, adoption rates, and matching degrees of each initial optimization method, to assist users in determining the optimization method among the initial optimization methods based on specific business scenarios and experience.

[0104] This embodiment enables the determination of the optimization method among multiple initial optimization methods based on the optimization weight. It can also intelligently guide users to participate in the decision-making process, ensuring that the SQL statement optimization process is both efficient and reliable, and comprehensively improving the intelligence level and optimization effect of database optimization.

[0105] To effectively address the shortcomings of traditional optimization techniques, such as long feedback times, low optimization efficiency, and impacts on software stability and usability, this application provides an embodiment of a slow query optimization apparatus for implementing all or part of the aforementioned slow query optimization method. See [link to relevant documentation]. Figure 2 The slow query optimization device specifically includes the following components: Abstract module 10 is used to receive and parse SQL statements when the current SQL statement's query action is a slow query action, obtain a basic abstract syntax tree, dynamically inject the context information of the database where the SQL statement is located into the basic abstract syntax tree, and obtain an enhanced abstract syntax tree. The context information includes the table relationship weight matrix and historical execution statistics features. Optimization module 20 is used to parse the enhanced abstract syntax tree, obtain the operation type of the SQL statement, determine the preset routing strategy of the prompt words based on the operation type, generate structured prompt words including the fingerprint features corresponding to the current database, input the prompt words and SQL statement in parallel to multiple preset large language models, receive the initial optimization methods output by each preset large language model, integrate the initial optimization methods to obtain the optimization method; The update module 30 is used to update SQL statements based on optimization methods and send the updated SQL statements to the database, so that the database can verify the performance improvement data of the updated SQL statements and update the historical execution statistics features and table relationship weight matrix based on the performance improvement data.

[0106] As described above, the slow query optimization device provided in this application embodiment can innovatively receive and parse an SQL statement when the current SQL statement is a slow query action to obtain a basic abstract syntax tree. It then dynamically injects database runtime context information into the basic abstract syntax tree to form an enhanced abstract syntax tree. The context information includes a table relation weight matrix and historical execution statistics. The device parses the enhanced abstract syntax tree to obtain the operation type of the SQL statement, thereby determining the preset routing strategy for the prompt words. It generates structured prompt words including database fingerprint features, inputs the prompt words and SQL statement in parallel into multiple preset large language models, integrates the initial optimization methods output by each model to obtain an optimization method, updates the SQL statement according to the optimization method, sends it to the database, verifies the performance improvement data, and updates the historical execution statistics and table relation weight matrix. This embodiment parses the SQL statement of a slow query and injects context information to determine the actual execution status of the SQL statement in the current database. This makes the resulting optimization methods more targeted, effectively improving the performance of the SQL statement corresponding to the slow query and making its execution more efficient. By processing multiple prompt words and SQL statements in parallel using multiple preset large language models, multiple initial optimization methods can be obtained, giving full play to the advantages of each preset large language model and providing initial optimization methods for the SQL statement from multiple perspectives, thereby improving the quality of the optimized SQL statement. This method effectively solves the shortcomings of traditional techniques, such as long feedback time, low optimization efficiency, and impact on software stability and usability. It significantly improves the optimization efficiency of slow queries and enhances the user experience.

[0107] From a hardware perspective, in order to effectively address the shortcomings of traditional technologies such as long feedback times, low optimization efficiency, and impact on software stability and usability, and to significantly improve the optimization efficiency for slow query operations and enhance the user experience, this application provides an embodiment of an electronic device for implementing all or part of the aforementioned slow query optimization method. The electronic device specifically includes the following components: The system comprises a processor, memory, a communications interface, and a bus; wherein the processor, memory, and communications interface communicate with each other via the bus; the communications interface is used to transmit information between the slow query optimization device and core business systems, user terminals, and related databases and other related devices; the logic controller can be a desktop computer, tablet computer, or mobile terminal, etc., and this embodiment is not limited to these. In this embodiment, the logic controller can be implemented with reference to the embodiments of the slow query optimization method and the slow query optimization device in the embodiments, the content of which is incorporated herein, and repeated details will not be described again.

[0108] It is understood that the user terminal may include smartphones, tablet computers, network set-top boxes, portable computers, desktop computers, personal digital assistants (PDAs), in-vehicle devices, smart wearable devices, etc. Among these, the smart wearable devices may include smart glasses, smartwatches, smart bracelets, etc.

[0109] In practical applications, the slow query optimization method can be partially executed on the electronic device side as described above, or all operations can be completed on the client device. The choice can be made based on the processing power of the client device and the limitations of the user's usage scenario. This application does not impose any limitations on this. If all operations are completed on the client device, the client device may further include a processor.

[0110] The aforementioned client device may have a communication module (i.e., a communication unit) that can communicate with a remote server to achieve data transmission with the server. The server may include a server on the task scheduling center side; in other implementation scenarios, it may also include a server on an intermediate platform, such as a server on a third-party server platform that has a communication link with the task scheduling center server. The server may include a single computer device, a server cluster consisting of multiple servers, or a distributed server structure.

[0111] Figure 3 This is a schematic block diagram illustrating the system configuration of the electronic device 9600 according to an embodiment of this application. Figure 3As shown, the electronic device 9600 may include a central processing unit 9100 and a memory 9140; the memory 9140 is coupled to the central processing unit 9100. It is worth noting that... Figure 3 This is an example; other types of structures can also be used to supplement or replace this structure to achieve telecommunications functions or other functions.

[0112] In one embodiment, the slow query optimization method functionality can be integrated into the central processing unit 9100. The central processing unit 9100 can be configured to perform the following controls: Step S101: If the current SQL statement's query action is a slow query action, receive and parse the SQL statement to obtain the basic abstract syntax tree. Dynamically inject the context information of the database where the SQL statement is located into the basic abstract syntax tree to obtain the enhanced abstract syntax tree. The context information includes the table relationship weight matrix and historical execution statistics. Step S102: Parse the enhanced abstract syntax tree to obtain the operation type of the SQL statement, determine the preset routing strategy of the prompt words based on the operation type, generate structured prompt words including the fingerprint features corresponding to the current database, input the prompt words and SQL statement in parallel to multiple preset large language models, receive the initial optimization methods output by each preset large language model, integrate the initial optimization methods to obtain the optimization method; Step S103: Update the SQL statement based on the optimization method and send the updated SQL statement to the database so that the database can verify the performance improvement data of the updated SQL statement and update the historical execution statistics feature and table relationship weight matrix based on the performance improvement data.

[0113] As described above, the electronic device provided in this application innovatively receives and parses an SQL statement when the current SQL statement is a slow query action, obtaining a basic abstract syntax tree. It then dynamically injects database runtime context information into the basic abstract syntax tree to form an enhanced abstract syntax tree. The context information includes a table relation weight matrix and historical execution statistics. The enhanced abstract syntax tree is parsed to obtain the operation type of the SQL statement, thereby determining the preset routing strategy for the prompt words. Structured prompt words including database fingerprint features are generated. The prompt words and the SQL statement are input in parallel into multiple preset large language models. The initial optimization methods output by each model are integrated to obtain an optimization method. The SQL statement is updated according to the optimization method and sent to the database. Performance improvement data is verified, and historical execution statistics and the table relation weight matrix are updated. This embodiment parses the SQL statement of a slow query and injects context information to determine the actual execution status of the SQL statement in the current database. This makes the resulting optimization methods more targeted, effectively improving the performance of the SQL statement corresponding to the slow query and making its execution more efficient. By processing multiple prompt words and SQL statements in parallel using multiple preset large language models, multiple initial optimization methods can be obtained, giving full play to the advantages of each preset large language model and providing initial optimization methods for the SQL statement from multiple perspectives, thereby improving the quality of the optimized SQL statement. This method effectively solves the shortcomings of traditional techniques, such as long feedback time, low optimization efficiency, and impact on software stability and usability. It significantly improves the optimization efficiency of slow queries and enhances the user experience.

[0114] In another embodiment, the slow query optimization device can be configured separately from the central processing unit 9100. For example, the slow query optimization device can be configured as a chip connected to the central processing unit 9100, and the slow query optimization method function can be implemented through the control of the central processing unit.

[0115] like Figure 3 As shown, the electronic device 9600 may further include: a communication module 9110, an input unit 9120, an audio processor 9130, a display 9160, and a power supply 9170. It is worth noting that the electronic device 9600 does not necessarily need to include these components. Figure 3 All components shown; in addition, the electronic device 9600 may also include Figure 3 For components not shown, please refer to existing technologies.

[0116] like Figure 3 As shown, the central processing unit 9100, sometimes also referred to as a controller or operating control, may include a microprocessor or other processor device and / or logic device, which receives inputs and controls the operation of various components of the electronic device 9600.

[0117] The memory 9140 may be, for example, one or more of a cache, flash memory, hard drive, removable media, volatile memory, non-volatile memory, or other suitable devices. It may store the aforementioned failure-related information, and also store a program for executing that information. The central processing unit 9100 may execute the program stored in the memory 9140 to perform information storage or processing, etc.

[0118] Input unit 9120 provides input to central processing unit 9100. Input unit 9120 may be, for example, a keypad or touch input device. Power supply 9170 provides power to electronic device 9600. Display 9160 displays images and text. Display may be, for example, an LCD display, but is not limited thereto.

[0119] The memory 9140 can be a solid-state memory, such as a read-only memory (ROM), random access memory (RAM), a SIM card, etc. It can also be a memory that retains information even when power is off, can be selectively erased, and contains more data; examples of this type of memory are sometimes referred to as EPROMs. The memory 9140 can also be some other type of device. The memory 9140 includes a buffer memory 9141 (sometimes referred to as a buffer). The memory 9140 may include an application / function storage unit 9142 for storing application programs and function programs or processes for executing the operation of the electronic device 9600 via the central processing unit 9100.

[0120] The memory 9140 may also include a data storage unit 9143 for storing data, such as contacts, digital data, pictures, sounds, and / or any other data used by the electronic device. The driver storage unit 9144 of the memory 9140 may include various drivers for the electronic device for communication functions and / or for performing other functions of the electronic device (such as messaging applications, address book applications, etc.).

[0121] The communication module 9110 is a transmitter / receiver that sends and receives signals via the antenna 9111. The communication module 9110 (transmitter / receiver) is coupled to the central processing unit 9100 to provide input signals and receive output signals, which is the same as in a conventional mobile communication terminal.

[0122] Based on different communication technologies, multiple communication modules 9110 can be configured in the same electronic device, such as cellular network modules, Bluetooth modules, and / or wireless LAN modules. The communication module 9110 (transmitter / receiver) is also coupled to a speaker 9131 and a microphone 9132 via an audio processor 9130 to provide audio output via the speaker 9131 and receive audio input from the microphone 9132, thereby realizing typical telecommunications functions. The audio processor 9130 may include any suitable buffer, decoder, amplifier, etc. Additionally, the audio processor 9130 is coupled to a central processing unit 9100, enabling on-device recording via the microphone 9132 and on-device playback of stored audio via the speaker 9131.

[0123] Embodiments of this application also provide a computer-readable storage medium capable of implementing all steps of the slow query optimization method with a server or client as the execution subject in the above embodiments. The computer-readable storage medium stores a computer program that, when executed by a processor, implements all steps of the slow query optimization method with a server or client as the execution subject in the above embodiments. For example, when the processor executes the computer program, it implements the following steps: Step S101: If the current SQL statement's query action is a slow query action, receive and parse the SQL statement to obtain the basic abstract syntax tree. Dynamically inject the context information of the database where the SQL statement is located into the basic abstract syntax tree to obtain the enhanced abstract syntax tree. The context information includes the table relationship weight matrix and historical execution statistics. Step S102: Parse the enhanced abstract syntax tree to obtain the operation type of the SQL statement, determine the preset routing strategy of the prompt words based on the operation type, generate structured prompt words including the fingerprint features corresponding to the current database, input the prompt words and SQL statement in parallel to multiple preset large language models, receive the initial optimization methods output by each preset large language model, integrate the initial optimization methods to obtain the optimization method; Step S103: Update the SQL statement based on the optimization method and send the updated SQL statement to the database so that the database can verify the performance improvement data of the updated SQL statement and update the historical execution statistics feature and table relationship weight matrix based on the performance improvement data.

[0124] As described above, the computer-readable storage medium provided in this application innovatively receives and parses an SQL statement when the current SQL statement is a slow query action, obtaining a basic abstract syntax tree. It then dynamically injects database runtime context information into the basic abstract syntax tree to form an enhanced abstract syntax tree. The context information includes a table relation weight matrix and historical execution statistics. The operation type of the SQL statement is obtained by parsing the enhanced abstract syntax tree, thereby determining the preset routing strategy for prompt words. Structured prompt words including database fingerprint features are generated. The prompt words and the SQL statement are input in parallel into multiple preset large language models. The initial optimization methods output by each model are integrated to obtain an optimization method. The SQL statement is updated according to the optimization method and sent to the database. Performance improvement data is verified, and historical execution statistics and the table relation weight matrix are updated. This embodiment parses the SQL statement of a slow query and injects context information to determine the actual execution status of the SQL statement in the current database. This makes the resulting optimization methods more targeted, effectively improving the performance of the SQL statement corresponding to the slow query and making its execution more efficient. By processing multiple prompt words and SQL statements in parallel using multiple preset large language models, multiple initial optimization methods can be obtained, giving full play to the advantages of each preset large language model and providing initial optimization methods for the SQL statement from multiple perspectives, thereby improving the quality of the optimized SQL statement. This method effectively solves the shortcomings of traditional techniques, such as long feedback time, low optimization efficiency, and impact on software stability and usability. It significantly improves the optimization efficiency of slow queries and enhances the user experience.

[0125] Embodiments of this application also provide a computer program product capable of implementing all steps of the slow query optimization method in the above embodiments, where the execution subject is a server or a client. When this computer program / instruction is executed by a processor, it implements the steps of the slow query optimization method. For example, the computer program / instruction implements the following steps: Step S101: If the current SQL statement's query action is a slow query action, receive and parse the SQL statement to obtain the basic abstract syntax tree. Dynamically inject the context information of the database where the SQL statement is located into the basic abstract syntax tree to obtain the enhanced abstract syntax tree. The context information includes the table relationship weight matrix and historical execution statistics. Step S102: Parse the enhanced abstract syntax tree to obtain the operation type of the SQL statement, determine the preset routing strategy of the prompt words based on the operation type, generate structured prompt words including the fingerprint features corresponding to the current database, input the prompt words and SQL statement in parallel to multiple preset large language models, receive the initial optimization methods output by each preset large language model, integrate the initial optimization methods to obtain the optimization method; Step S103: Update the SQL statement based on the optimization method and send the updated SQL statement to the database so that the database can verify the performance improvement data of the updated SQL statement and update the historical execution statistics feature and table relationship weight matrix based on the performance improvement data.

[0126] As described above, the computer program product provided in this application innovatively receives and parses an SQL statement when the current SQL statement is a slow query action, obtaining a basic abstract syntax tree. It then dynamically injects database runtime context information into the basic abstract syntax tree to form an enhanced abstract syntax tree. The context information includes a table relation weight matrix and historical execution statistics. The operation type of the SQL statement is obtained by parsing the enhanced abstract syntax tree, thereby determining the preset routing strategy for the prompt words. Structured prompt words including database fingerprint features are generated. The prompt words and the SQL statement are input in parallel into multiple preset large language models. The initial optimization methods output by each model are integrated to obtain an optimization method. The SQL statement is updated according to the optimization method and sent to the database. Performance improvement data is verified, and historical execution statistics and the table relation weight matrix are updated. This embodiment parses the SQL statement of a slow query and injects context information to determine the actual execution status of the SQL statement in the current database. This makes the resulting optimization methods more targeted, effectively improving the performance of the SQL statement corresponding to the slow query and making its execution more efficient. By processing multiple prompt words and SQL statements in parallel using multiple preset large language models, multiple initial optimization methods can be obtained, giving full play to the advantages of each preset large language model and providing initial optimization methods for the SQL statement from multiple perspectives, thereby improving the quality of the optimized SQL statement. This method effectively solves the shortcomings of traditional techniques, such as long feedback time, low optimization efficiency, and impact on software stability and usability. It significantly improves the optimization efficiency of slow queries and enhances the user experience.

[0127] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, apparatus, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0128] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (devices), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0129] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0130] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0131] Specific embodiments have been used to illustrate the principles and implementation methods of this invention. The descriptions of the embodiments above are only for the purpose of helping to understand the method and core ideas of this invention. At the same time, for those skilled in the art, there will be changes in the specific implementation methods and application scope based on the ideas of this invention. Therefore, the content of this specification should not be construed as a limitation of this invention.

Claims

1. A slow query optimization method, characterized in that, The method includes: When the current SQL statement's query action is a slow query action, the SQL statement is received and parsed to obtain a basic abstract syntax tree. The context information of the database where the SQL statement is located is dynamically injected into the basic abstract syntax tree to obtain an enhanced abstract syntax tree. The context information includes a table relationship weight matrix and historical execution statistics. The enhanced abstract syntax tree is parsed to obtain the operation type of the SQL statement. Based on the operation type, a preset routing strategy for the prompt words is determined. A structured prompt word including the fingerprint features corresponding to the current database is generated. The prompt words and the SQL statement are input in parallel to multiple preset large language models. The initial optimization methods output by each preset large language model are received. The initial optimization methods are integrated to obtain the optimization method. The SQL statement is updated based on the optimization method, and the updated SQL statement is sent to the database so that the database can verify the performance improvement data of the updated SQL statement and update the historical execution statistics and table relationship weight matrix based on the performance improvement data.

2. The method according to claim 1, characterized in that, Dynamically injecting the runtime context information of the database containing the SQL statement into the basic abstract syntax tree yields an enhanced abstract syntax tree, including: Obtain real-time system view data and multiple tables of the database where the SQL is located, extract foreign key relationships between the multiple tables based on the real-time system view data, and generate a table relationship weight matrix based on the foreign key relationships. The matrix elements in the table relationship weight matrix represent the association strength between each table. Query the performance monitoring table of the database to obtain the historical execution statistics of each SQL template within a preset historical period. The historical execution statistics include average execution time, index hit rate, and lock wait time. Based on the preset syntax tree modification interface, the table relationship weight matrix and the historical execution statistics are injected into the corresponding nodes of the basic abstract syntax tree to obtain the enhanced abstract syntax tree.

3. The method according to claim 1, characterized in that, Generate structured prompts that include fingerprint features corresponding to the current database, including: Scan all tables in the database according to a preset cycle, determine index feature groups based on the index type distribution and hot index ranking in the tables, determine transaction feature groups based on the average transaction market and deadlock frequency in the tables, and determine storage feature groups based on the tablespace fragmentation rate in the tables. The index feature group, the transaction feature group, and the storage feature group are encoded according to a preset encoding dimension to obtain the encoding result; The encoding result is reduced in dimensionality according to a preset feature dimension to obtain the fingerprint features corresponding to the database.

4. The method according to claim 1, characterized in that, The operation type includes a selection type, and the generation of structured prompts including fingerprint features corresponding to the current database includes: When the operation type is the selection type, obtain the current index usage preference and transaction isolation level of the database, and determine the header fingerprint feature of the database based on the index usage preference and the transaction isolation level; The enhanced abstract syntax tree is converted into a nested structure in JSON format, and structured prompt words are generated based on the header fingerprint features and the nested structure.

5. The method according to claim 4, characterized in that, After generating structured prompts based on the head fingerprint features and the nested structure, the method further includes: Based on the performance monitoring table of the database, extract historical operation statements with the same operation type as the current SQL statement and historical execution plans corresponding to the operation statements, and determine the historical execution plans corresponding to the operation statements as negative samples, so as to check the structured prompt words based on the negative samples; Obtain the preset server utilization threshold corresponding to the current database, and determine the preset server utilization threshold as one of the hard constraints during the generation of the structured prompt words.

6. The method according to claim 1, characterized in that, The step of enabling the database to verify the performance improvement data of the updated SQL statement, and updating the historical execution statistics and table relationship weight matrix based on the performance improvement data, includes: Determine the difference in execution time and memory consumption between the SQL statement before and after the update in the database; If the execution time difference is greater than a preset execution time difference and the memory consumption difference is less than a preset memory consumption difference, the optimization method corresponding to the updated SQL statement is determined as an effective optimization, and the historical execution statistics and the table relationship weight matrix are updated based on the optimization method.

7. The method according to claim 1, characterized in that, The integration of the initial optimization method to obtain the optimized method includes: Obtain historical optimization data corresponding to historical optimization methods, wherein the historical optimization data includes historical initial optimization methods and preset large language models corresponding to each historical initial optimization method; Based on the historical optimization data, the adoption rate of each preset large language model is determined, and the matching degree between each current initial optimization method and the enhanced abstract syntax tree is determined; The optimization weight of each initial optimization method is determined according to the adoption rate and the matching degree, and the initial optimization method corresponding to the optimization weight being greater than the preset optimization weight is determined as the optimization method; When the optimization weights corresponding to each of the initial optimization methods are not greater than the preset optimization weights, or when there are at least two initial optimization methods whose optimization weights are greater than the preset optimization weights, a prompt message is sent to prompt the user to determine the optimization method among the initial optimization methods.

8. A slow query optimization device, characterized in that, The device includes: The abstract module is used to receive and parse the SQL statement when the current SQL statement's query action is a slow query action, obtain a basic abstract syntax tree, dynamically inject the context information of the database where the SQL statement is located into the basic abstract syntax tree, and obtain an enhanced abstract syntax tree. The context information includes a table relationship weight matrix and historical execution statistics features. The optimization module is used to parse the enhanced abstract syntax tree to obtain the operation type of the SQL statement, determine the preset routing strategy of the prompt words based on the operation type, generate structured prompt words including the fingerprint features corresponding to the current database, input the prompt words and the SQL statement in parallel to multiple preset large language models, receive the initial optimization methods output by each preset large language model, integrate the initial optimization methods to obtain the optimization method; The update module is used to update the SQL statement based on the optimization method, and send the updated SQL statement to the database, so that the database verifies the performance improvement data of the updated SQL statement, and updates the historical execution statistics features and table relationship weight matrix based on the performance improvement data.

9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the program, it implements the steps of the slow query optimization method according to any one of claims 1 to 7.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the slow query optimization method according to any one of claims 1 to 7.

Images