Database SQL query optimization method and device, equipment and storage medium

Abstract

The invention relates to the field of data processing, and discloses a database SQL query optimization method, device and equipment and a storage medium, and the method is used for intelligently generating and optimizing an SQL query. The method comprises the following steps: acquiring a user request, and generating an SQL statement; analyzing the SQL statement to extract a related table, and obtaining structure information of the related table from a database; obtaining a historical query log, performing multi-modal feature fusion on the SQL statement, the structural information of the table and the historical query log based on a pre-trained Transform model, and generating a context-aware joint embedded vector; inputting the joint embedded vector into a pre-trained optimization strategy generation model to obtain optimization suggestions applicable to cross platforms; and collecting user feedback data, updating parameters of the optimization strategy generation model to obtain an updated optimization strategy generation model, and replacing the existing optimization strategy generation model with the updated optimization strategy generation model.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a database SQL query optimization method, device, equipment and storage medium. Background Art

[0002] In today's digital age, the logistics and transportation industry is booming, generating massive amounts of logistics data. Efficient processing, accurate querying, and in-depth analysis of this data are crucial for improving the competitiveness of logistics companies and optimizing service quality. In this process, SQL queries, as the core means of database operations, have a direct impact on data processing efficiency and the smooth operation of business operations. However, existing SQL performance optimization technologies have numerous drawbacks.

[0003] Limitations of Static Optimization: Traditional optimizers rely heavily on static rules and statistical information. Faced with dynamically changing data distribution and diverse query patterns, they struggle to make timely and effective adjustments, significantly reducing optimization effectiveness. In logistics, data is highly real-time, with information such as cargo transportation status and inventory levels constantly changing. Traditional optimizers are unable to adapt to these changes, resulting in suboptimal query performance.

[0004] Low intelligence: Existing tools lack intelligent, autonomous decision-making capabilities and are unable to accurately identify and effectively resolve complex performance bottlenecks. This requires extensive manual intervention by specialized technical personnel, increasing labor costs and placing high demands on technical expertise. When processing large amounts of logistics data, manual optimization is inefficient and unable to meet the demands of rapid business development.

[0005] Poor adaptability: Different database systems differ in architecture, syntax, and performance characteristics. Existing tools are difficult to use across multiple database systems, often requiring customized optimization for specific databases. This undoubtedly increases the complexity and cost of cross-platform applications and limits companies' flexibility in database selection. Logistics companies may need to switch from one database system to another due to business development needs. If optimization tools are not universally compatible, significant manpower and material resources will need to be reinvested in optimization.

[0006] Therefore, the existing technology still needs to be improved and developed. Summary of the Invention

[0007] The present invention provides a database SQL query optimization method, device, equipment and storage medium for intelligently generating and optimizing SQL queries.

[0008] A first aspect of the present invention provides a database SQL query optimization method, which includes: obtaining a user request and generating an SQL statement based on the user request; parsing the SQL statement to extract the involved tables, and obtaining structural information of the involved tables from a database; obtaining historical query logs, performing multimodal feature fusion on the SQL statement, table structural information and historical query logs based on a pre-trained Transformer model, and generating a context-aware joint embedding vector; inputting the joint embedding vector into a pre-trained optimization strategy generation model to obtain optimization suggestions applicable across platforms, the optimization suggestions including index adjustment, query rewriting and database and table sharding solutions; collecting user feedback data, updating the parameters of the optimization strategy generation model based on the user feedback data, obtaining an updated optimization strategy generation model, and using the updated optimization strategy generation model to replace the existing optimization strategy generation model.

[0009] Optionally, in a first implementation manner of the first aspect of the present invention, obtaining a user request and generating an SQL statement based on the user request includes: obtaining a user request, parsing the user request to obtain a user request type and user request parameters; determining a business scenario based on the user request type, and matching an SQL template corresponding to the business scenario from a preset SQL template library based on the business scenario; verifying the user request parameters, and filling the verified user request parameters into the matched SQL template to generate an SQL statement.

[0010] Optionally, in a second implementation of the first aspect of the present invention, the parsing of the SQL statement to extract the table name involved, and obtaining the structural information of the table involved from the database based on the table name, includes: performing lexical analysis on the SQL statement to obtain multiple lexical units; constructing an abstract syntax tree based on the multiple lexical units, traversing the abstract syntax tree, finding table-related nodes, and extracting the table name involved in the SQL statement from the table-related nodes; obtaining the structural information of the table involved from the database based on the table name.

[0011] Optionally, in a third implementation method of the first aspect of the present invention, the historical query log is obtained, and multimodal feature fusion is performed on the SQL statement, the structural information of the table and the historical query log based on a pre-trained Transformer model to generate a context-aware joint embedding vector, including: obtaining the historical query log from the log records of the database, extracting the query features of the historical query log, and converting the query features into a historical feature vector; converting the SQL statement into a word vector, and encoding the structural information of the table to convert the structural information of the table into a table vector; inputting the historical feature vector, the word vector and the table vector into the pre-trained Transformer model for multimodal feature fusion to generate a context-aware joint embedding vector.

[0012] Optionally, in a fourth implementation method of the first aspect of the present invention, the historical feature vector, the word vector and the table vector are input into a pre-trained Transformer model for multimodal feature fusion to generate a context-aware joint embedding vector, including: constructing a Transformer model, and introducing a cross-modal attention mechanism into the Transformer model to obtain an improved Transformer model; training the improved Transformer model to obtain a pre-trained Transformer model; inputting the historical feature vector, the word vector and the table vector into the pre-trained Transformer model to obtain the context-aware joint embedding vector output by the pre-trained Transformer model.

[0013] Optionally, in a fifth implementation of the first aspect of the present invention, the joint embedding vector is input into a pre-trained optimization strategy generation model to obtain optimization suggestions applicable across platforms, and the optimization suggestions include index adjustment, query rewriting, and database and table sharding schemes, including: inputting the joint embedding vector into a pre-trained optimization strategy generation model to obtain an optimizable query pattern output by the optimization strategy generation model; generating an optimization scheme based on the optimizable query pattern, and if there are multiple optimizable query patterns, combining the optimization schemes corresponding to each query pattern; adjusting the syntax format of the optimization scheme, and outputting optimization suggestions applicable across platforms based on the adjusted optimization scheme, and the optimization suggestions include index adjustment, query rewriting, and database and table sharding schemes.

[0014] Optionally, in a sixth implementation manner of the first aspect of the present invention, the collecting of user feedback data, updating the parameters of the optimization strategy generation model according to the user feedback data to obtain an updated optimization strategy generation model, and using the updated optimization strategy generation model to replace the existing optimization strategy generation model, includes: collecting user feedback data, the user feedback data including performance indicators of query execution and query result feedback; labeling the user feedback data to determine the problems reflected by the user feedback data; updating the parameters of the optimization strategy generation model according to the reflected problems to obtain an updated optimization strategy generation model, and using the updated optimization strategy generation model to replace the existing optimization strategy generation model.

[0015] The second aspect of the present invention provides a database SQL query optimization device, comprising: a first generation module, used to obtain user requests and generate SQL statements based on the user requests; a parsing module, used to parse SQL statements to extract the tables involved and obtain structural information of the tables involved from the database; a second generation module, used to obtain historical query logs, perform multimodal feature fusion on SQL statements, table structural information and historical query logs based on a pre-trained Transformer model, and generate a context-aware joint embedding vector; a third generation module, used to input the joint embedding vector into a pre-trained optimization strategy generation model to obtain cross-platform applicable optimization suggestions, wherein the optimization suggestions include index adjustment, query rewriting and database and table sharding solutions; an update module, used to collect user feedback data, update the parameters of the optimization strategy generation model according to the user feedback data, obtain an updated optimization strategy generation model, and use the updated optimization strategy generation model to replace the existing optimization strategy generation model.

[0016] Optionally, in a first implementation manner of the second aspect of the present invention, the first generation module includes: a first acquisition unit, used to acquire a user request, parse the user request to obtain a user request type and user request parameters; a determination unit, used to determine a business scenario based on the user request type, and match an SQL template corresponding to the business scenario from a preset SQL template library based on the business scenario; a first generation unit, used to verify the user request parameters, and fill the verified user request parameters into the matched SQL template to generate an SQL statement.

[0017] Optionally, in a second implementation of the second aspect of the present invention, the parsing module includes: a parsing unit, used to perform lexical analysis on the SQL statement to obtain multiple lexical units; an extraction unit, used to construct an abstract syntax tree based on the multiple lexical units, traverse the abstract syntax tree, find table-related nodes, and extract the table name involved in the SQL statement from the table-related nodes; a second acquisition unit, used to obtain structural information of the table involved from the database based on the table name.

[0018] Optionally, in a third implementation of the second aspect of the present invention, the second generation module includes: a third acquisition unit, used to obtain historical query logs from the log records of the database, extract query features of the historical query logs, and convert the query features into historical feature vectors; a conversion unit, used to convert SQL statements into word vectors, and encode the structural information of the table to convert the structural information of the table into a table vector; a second generation unit, used to input the historical feature vector, the word vector and the table vector into a pre-trained Transformer model for multimodal feature fusion to generate a context-aware joint embedding vector.

[0019] Optionally, in a fourth implementation of the second aspect of the present invention, the third generation module includes: a third generation unit, used to input the joint embedding vector into a pre-trained optimization strategy generation model to obtain an optimizable query pattern output by the optimization strategy generation model; a combination unit, used to generate an optimization plan based on the optimizable query pattern, and if there are multiple optimizable query patterns, the optimization plans corresponding to each query pattern are combined; an adjustment unit, used to adjust the grammatical format of the optimization plan, and output cross-platform optimization suggestions based on the adjusted optimization plan, and the optimization suggestions include index adjustment, query rewriting and database and table sharding plans.

[0020] Optionally, in a fifth implementation of the second aspect of the present invention, the update module includes: a collection unit for collecting user feedback data, the user feedback data including performance indicators of query execution and query result feedback; a labeling unit for labeling the user feedback data and determining the problems reflected by the user feedback data; an updating unit for updating the parameters of the optimization strategy generation model according to the reflected problems, obtaining an updated optimization strategy generation model, and using the updated optimization strategy generation model to replace the existing optimization strategy generation model.

[0021] The third aspect of the present invention provides a database SQL query optimization device, comprising: a memory and at least one processor, wherein the memory stores computer-readable instructions, and the memory and the at least one processor are interconnected via lines; the at least one processor calls the computer-readable instructions in the memory so that the database SQL query optimization device executes the various steps of the database SQL query optimization method as described above.

[0022] A fourth aspect of the present invention provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer-readable instructions, which, when executed on a computer, enable the computer to execute the various steps of the database SQL query optimization method described above.

[0023] The technical solution provided by the present invention is based on the multimodal feature fusion capability of the Transformer model, effectively integrating query semantics, table structure and historical pattern information, combining with the optimization strategy generation model to realize dynamic strategy generation, and forming a closed-loop optimization through user feedback. It can adapt to different database platforms, significantly improve query efficiency, reduce resource consumption, and at the same time enhance the system's adaptability and long-term optimization capabilities. BRIEF DESCRIPTION OF THE DRAWINGS

[0024] Figure 1 A first flow chart of a database SQL query optimization method provided by an embodiment of the present invention;

[0025] Figure 2A second flow chart of the database SQL query optimization method provided by an embodiment of the present invention;

[0026] Figure 3 A third flow chart of the database SQL query optimization method provided by an embodiment of the present invention;

[0027] Figure 4 A fourth flow chart of the database SQL query optimization method provided by an embodiment of the present invention;

[0028] Figure 5 A fifth flow chart of the database SQL query optimization method provided by an embodiment of the present invention;

[0029] Figure 6 A sixth flow chart of the database SQL query optimization method provided by an embodiment of the present invention;

[0030] Figure 7 A schematic diagram of the structure of a database SQL query optimization device provided by an embodiment of the present invention;

[0031] Figure 8 A schematic diagram of the structure of a database SQL query optimization device provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0032] An embodiment of the present invention provides a database SQL query optimization method, apparatus, device, and storage medium, which is used to intelligently generate and optimize SQL queries. The method includes: obtaining a user request and generating an SQL statement based on the user request; parsing the SQL statement to extract the involved tables and obtaining structural information of the involved tables from the database; obtaining historical query logs, performing multimodal feature fusion on the SQL statement, the structural information of the table, and the historical query logs based on a pre-trained Transformer model to generate a context-aware joint embedding vector; inputting the joint embedding vector into a pre-trained optimization strategy generation model to obtain optimization suggestions applicable across platforms, the optimization suggestions including index adjustment, query rewriting, and database and table sharding solutions; collecting user feedback data, updating the parameters of the optimization strategy generation model based on the user feedback data, obtaining an updated optimization strategy generation model, and using the updated optimization strategy generation model to replace the existing optimization strategy generation model.

[0033] The terms "first," "second," "third," "fourth," and the like (if any) in the description and claims of the present invention and in the accompanying drawings are used to distinguish similar objects and are not necessarily used to describe a particular order or precedence. It should be understood that the terms used in this manner are interchangeable where appropriate so that the embodiments described herein can be implemented in an order other than that illustrated or described herein. In addition, the terms "including" or "having" and any variations thereof are intended to cover non-exclusive inclusions, for example, a process, method, system, product, or apparatus that includes a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units that are not explicitly listed or that are inherent to these processes, methods, products, or apparatus.

[0034] For ease of understanding, the specific process of the embodiment of the present invention is described below. Figure 1 A first embodiment of a database SQL query optimization method according to an embodiment of the present invention includes:

[0035] S101: Obtain a user request and generate an SQL statement according to the user request.

[0036] In this embodiment, user request parameters are obtained. The request may come from a user interface (such as a web interface, client program) or other application program interface. The request content covers the user's operation requirements on data, such as querying specific data, inserting new data, updating existing data, or deleting data.

[0037] Before generating SQL statements based on user request parameters, the user request parameters are verified. After the verification passes, the SQL statements are generated based on the user request parameters.

[0038] S102: Parse the SQL statement to extract the involved tables, and obtain the structural information of the involved tables from the database.

[0039] In this embodiment, the SQL statement is subjected to lexical analysis and syntax analysis to extract the tables involved. The database system typically maintains a set of system tables (System Catalog) that store metadata information about the database. By querying these system tables, the structural information of the tables can be obtained.

[0040] S103: Obtain historical query logs, perform multimodal feature fusion on SQL statements, table structure information, and historical query logs based on a pre-trained Transformer model, and generate a context-aware joint embedding vector.

[0041] In this embodiment, the pre-trained Transformer model uses a multi-layer Transformer encoder, each layer of which includes a self-attention mechanism and a feedforward neural network.

[0042] In this example, the pre-trained Transformer model uses an attention mechanism to focus on the correspondence between the columns referenced in SQL and the table structure. For example, when the SQL query "users.name" is executed, the model's attention to the "name" column in the "users" table is enhanced.

[0043] In this example, the pre-trained Transformer model uses execution patterns in historical query logs to guide its understanding of the current SQL. For example, if similar queries have historically been significantly optimized using indexes, the pre-trained Transformer model will prioritize index-related features.

[0044] In this embodiment, position codes are added to data of different modalities to distinguish SQL text, table structure information, and location information of historical logs.

[0045] S104. Input the joint embedding vector into the pre-trained optimization strategy generation model to obtain optimization suggestions applicable across platforms. The optimization suggestions include index adjustment, query rewriting, and database and table sharding solutions.

[0046] In this embodiment, the basic architecture of the optimization strategy generation model is to use a sequence-to-sequence (Seq2Seq) model or a Transformer-based generation model to map the joint embedding vector to the optimization strategy space.

[0047] The input layer of the optimization strategy generation model receives the joint embedding vector generated by multimodal feature fusion, and the dimension is usually 768-1024.

[0048] The middle layer of the optimization strategy generation model uses a multi-layer Transformer decoder or a graph neural network (GNN) to learn the mapping relationship between the embedding vector and the optimization rule.

[0049] The output layer of the optimization strategy generation model generates structured optimization suggestions, including index adjustment, query rewriting, and database and table sharding solutions.

[0050] S105 , collecting user feedback data, updating the parameters of the optimization strategy generation model according to the user feedback data, obtaining an updated optimization strategy generation model, and using the updated optimization strategy generation model to replace the existing optimization strategy generation model.

[0051] In this embodiment, user feedback data is collected, and the user feedback data includes at least any one of performance indicator feedback, query result feedback, operation convenience feedback, and business requirement compliance feedback.

[0052] Performance indicator feedback: Collect query execution performance data, such as query response time, database server CPU utilization, memory usage, disk I / O times, and traffic. For example, record the average query response time before and after optimization to analyze the impact of the optimization strategy on performance.

[0053] Query result feedback: Obtain user feedback on query results to check their accuracy and completeness. For example, users may find that optimized query results are missing certain data or contain incorrect records. This feedback can help determine whether the optimization strategy has affected the accuracy of the query results.

[0054] Feedback on operational ease: Understand the user experience when applying optimization strategies, including whether the optimization suggestions are easy to understand and implement. For example, users may report that certain complex sharding solutions are difficult to implement, or that index adjustments are difficult to perform in a specific database environment.

[0055] Feedback on business needs alignment: This section evaluates whether the optimization strategy meets business needs, such as data real-time requirements and data consistency requirements. For example, if the business has high requirements for data consistency, the optimization strategy may make it difficult to maintain data consistency after sharding, and users may report that the strategy is not applicable.

[0056] Analyze user feedback data and update the parameters of the optimization strategy generation model based on the analysis results of the user feedback data.

[0057] It is understandable that the execution subject of the present invention can be a database SQL query optimization device, or a terminal or a server, which is not limited here. The embodiment of the present invention is described by taking the server as the execution subject as an example.

[0058] This embodiment provides a database SQL query optimization method, which is based on the multimodal feature fusion capability of the Transformer model, effectively integrates query semantics, table structure and historical pattern information, combines the optimization strategy generation model to realize dynamic strategy generation, and forms a closed-loop optimization through user feedback. It can adapt to different database platforms, significantly improve query efficiency, reduce resource consumption, and enhance the system's adaptability and long-term optimization capabilities.

[0059] See also Figure 2 The second embodiment of the database SQL query optimization method in the embodiment of the present invention includes:

[0060] S201: Obtain a user request, parse the user request to obtain a user request type and user request parameters.

[0061] In this embodiment, a user request is first received, and the request type is extracted therefrom, such as operation categories such as data query, insert, update or delete.

[0062] In this embodiment, the user request parameters include specific business data and conditions, such as the start and end time in the above order query scenario.

[0063] S202: Determine a business scenario according to the user request type, and obtain an SQL template corresponding to the business scenario from a preset SQL template library according to the business scenario.

[0064] In this embodiment, user request types are mapped to specific business scenarios based on predefined rules. For example, a user requesting to query order data within a certain time period corresponds to the "Order Query" scenario. After determining the business scenario, a search and match is performed within a pre-defined SQL template library. This template library contains standard SQL statement templates designed for various business scenarios, ensuring that the generated SQL statements are syntactically and logically standardized.

[0065] S203: Verify the user request parameters, fill the verified user request parameters into the matched SQL template, and generate an SQL statement.

[0066] In this example, parameters are validated to ensure that their data types are consistent with the template requirements. For example, time parameters must conform to the specified date format. Furthermore, the parameter's value range is verified to avoid abnormal values ​​that violate business logic. For example, the query time cannot be later than the current time. Finally, the validated user request parameters are accurately filled in according to the placeholders reserved in the SQL template to generate the SQL statement.

[0067] In this embodiment, SQL statements are generated through template matching and parameter verification, offering significant advantages such as strong standardization, high efficiency, security, and good adaptability. Based on a pre-set SQL template library, it ensures statement compliance through business scenario matching. The parameter verification mechanism effectively mitigates data anomalies and security risks. Flexible template replacement enables rapid response to changing business needs, significantly improving the efficiency and quality of SQL statement generation.

[0068] See also Figure 3 A third embodiment of a database SQL query optimization method according to an embodiment of the present invention includes:

[0069] S301: Perform lexical analysis on the SQL statement to obtain multiple lexical units.

[0070] In this embodiment, the input SQL statement is processed and scanned character by character according to the lexical rules of the SQL language and split into independent lexical units. For example, the SQL statement "SELECT name FROM users WHERE age>18" will be decomposed into lexical units such as "SELECT" (keyword), "name" (column name), "FROM" (keyword), "users" (table name), "WHERE" (keyword), "age" (column name), ">" (operator), and "18" (constant).

[0071] S302: construct an abstract syntax tree based on the multiple lexical units, traverse the abstract syntax tree, find table-related nodes, and extract table names involved in the SQL statement from the table-related nodes.

[0072] In this embodiment, an abstract syntax tree (AST) is constructed based on the grammatical rules of the SQL language. The abstract syntax tree clearly presents the logical hierarchy of the SQL statement in a tree structure. For example, the "FROM" clause, the "WHERE" clause, the "JOIN" operation, etc. will form corresponding nodes in the tree. After the abstract syntax tree is constructed, it is deeply traversed, and the nodes related to the table are located by identifying specific node types and paths. For example, under the "FROM" clause node, the specific table name node can be found, thereby extracting the table name involved in the SQL statement, such as "users" in the above example. For SQL statements containing complex structures such as subqueries and multi-table connections, this method can also accurately extract all relevant table names by recursively traversing the subtree.

[0073] S303: Obtain structure information of the table involved from the database according to the table name.

[0074] In this embodiment, after successfully extracting the table names involved in the SQL statement, the system uses the metadata query interface provided by the database to perform a query operation on each table name. Different database systems have their own metadata storage mechanisms and query methods.

[0075] For example, in MySQL, column information is obtained by querying the INFORMATION_SCHEMA.COLUMNS table, and index information is obtained by querying the INFORMATION_SCHEMA.STATISTICS table. In PostgreSQL, metadata is obtained through the information_schema.columns view and the pg_indexes view. The system integrates the query results to obtain complete structural information for each table, including column name, data type, whether it is a primary key, whether there are foreign key constraints, index definitions, and other information. This provides critical data support for subsequent database operations and query optimization.

[0076] In this embodiment, through the multi-step collaboration of lexical analysis, syntax parsing and metadata acquisition, efficient and accurate extraction of table names and structural information involved in SQL statements is achieved. It has the significant advantages of rigorous logic, strong versatility and adaptability to complex scenarios. It lays the foundation with the help of lexical unit decomposition, relies on abstract syntax tree structured parsing, and finally accurately obtains the table name. Combined with database metadata query, it provides reliable data support for subsequent query optimization, semantic understanding and other tasks, effectively ensuring the efficiency and accuracy of database operations.

[0077] See also Figure 4 A fourth embodiment of a database SQL query optimization method according to an embodiment of the present invention includes:

[0078] S401: Obtain historical query logs from log records in a database, extract query features from the historical query logs, and convert the query features into historical feature vectors.

[0079] In this embodiment, historical query logs are screened from database log records and analyzed in depth to extract key query features, such as query execution time, execution frequency, amount of returned data, and the types of operations involved (add, delete, modify, and query). These query features are then converted into computer-processable historical feature vectors through data normalization and encoding, representing the patterns and performance of historical queries in numerical form.

[0080] S402: Convert the SQL statement into a word vector and encode the structure information of the table to convert the structure information of the table into a table vector.

[0081] In this embodiment, natural language processing technology is used to segment SQL statements and map each word to a corresponding word vector. A common approach is to use pre-trained word vector models, such as Word2Vec and BERT, to ensure that each word in the SQL statement finds a corresponding position in the vector space, thereby converting the SQL statement into a sequence of word vectors with semantic information, thereby expressing the textual semantics of the SQL statement.

[0082] In this embodiment, specific encoding rules are designed for each table's structural information, including column names, data types, constraints (primary keys, foreign keys, etc.), index information, etc. For example, column names are hash-encoded, data types are mapped to fixed-dimensional vectors, and constraints are represented as binary bits. In this way, the table structure information is converted into a table vector, allowing the table's structural features to participate in subsequent calculations in vector form.

[0083] S403: Input the historical feature vector, word vector, and table vector into the pre-trained Transformer model for multimodal feature fusion to generate a context-aware joint embedding vector.

[0084] In this embodiment, the comprehensive feature vector is input into a pre-trained Transformer model to obtain a context-aware joint embedding vector output by the pre-trained Transformer model, specifically including: constructing a Transformer model and introducing a cross-modal attention mechanism into the Transformer model to obtain an improved Transformer model; training the improved Transformer model to obtain a pre-trained Transformer model; inputting the comprehensive feature vector into the pre-trained Transformer model to obtain a context-aware joint embedding vector output by the pre-trained Transformer model.

[0085] In this example, a basic Transformer model architecture is built, consisting of multiple encoder layers, each composed of a multi-head self-attention mechanism and a feedforward neural network. On this basis, a cross-modal attention mechanism is introduced. This mechanism enables the model to automatically learn the associations and interactions between different modalities when processing multimodal data. For example, it allows historical feature vectors, word vectors, and table vectors to influence each other, enhancing the model's ability to fuse multimodal data, thereby obtaining an improved Transformer model.

[0086] In this embodiment, a large number of multimodal data samples are prepared, which are composed of historical feature vectors, word vectors, and table vectors. The improved Transformer model is trained using the stochastic gradient descent algorithm and the cross entropy loss function. During the training process, the model continuously adjusts its own parameters to learn how to better integrate multimodal data to minimize the gap between the predicted results and the true labels (if there is a supervised task), or to maximize the semantic consistency between multimodal data (unsupervised task), and finally obtain a pre-trained Transformer model.

[0087] In this embodiment, the extracted and processed historical feature vectors, word vectors, and table vectors are spliced ​​or combined in a specific way to form a comprehensive feature vector. The comprehensive feature vector is input into the trained Transformer pre-training model. The model uses the internal self-attention mechanism and cross-modal attention mechanism to deeply interact and fuse the multimodal information in the comprehensive feature vector, capturing the contextual associations between different modal data. After calculation and processing by a multi-layer network, a context-aware joint embedding vector is finally output. This vector integrates historical query patterns, SQL statement semantics, and table structure information, and can provide rich and effective feature representations for subsequent database query optimization, semantic understanding, and other tasks.

[0088] In this embodiment, through feature extraction of multimodal data, historical, textual and structural information is comprehensively captured. The improved Transformer model introduces a cross-modal attention mechanism to enhance the model's ability to learn complex associations. The joint embedding vector generated by the trained model can effectively support downstream tasks such as query optimization and improve the intelligence level of database operations.

[0089] See also Figure 5 A fifth embodiment of a database SQL query optimization method in an embodiment of the present invention includes:

[0090] S501: Input the joint embedding vector into a pre-trained optimization strategy generation model. The optimization strategy generation model maps the joint embedding vector to a predefined optimization rule space and identifies optimizable query patterns.

[0091] In this embodiment, the joint embedding vector obtained through multimodal feature fusion is input into a pre-trained optimization strategy generation model. An optimization rule space covering various database optimization rules is pre-constructed within the optimization strategy generation model. These rules are based on database principles, query optimization theory, and a large amount of practical experience. The optimization strategy generation model maps the joint embedding vector to the optimization rule space by analyzing and calculating it. During this process, the optimization strategy generation model uses machine learning algorithms and preset pattern recognition mechanisms to scan the information contained in the vector, determine whether there are parts of the current query that can be optimized, and identify specific query patterns that can be optimized. For example, if the joint embedding vector shows that the query frequently accesses certain columns and does not use indexes, the optimization strategy generation model will identify the "missing index" query pattern that can be optimized; if a complex sub-query structure is found, the "query structure redundancy" pattern will be identified.

[0092] S502: Generate an optimization plan according to the query pattern that can be optimized. If there are multiple query patterns that can be optimized, combine the optimization plans corresponding to each query pattern.

[0093] In this embodiment, based on the identified query patterns that can be optimized, a corresponding optimization solution is generated for each pattern according to predefined rules and strategies. For example, for the "index missing" pattern, a solution for creating relevant indexes is generated; for the "query structure redundant" pattern, a solution for rewriting the query to simplify the structure is generated.

[0094] When there are multiple optimizable query patterns, the model will comprehensively consider the mutual influence and priority of each solution, and use optimization algorithms (such as greedy algorithms, dynamic programming algorithms, etc.) to combine the optimization solutions corresponding to each query pattern.

[0095] S503. Adjust the syntax format of the optimal solution and output cross-platform optimization suggestions. The optimization suggestions include index adjustment, query rewriting, and database and table sharding solutions.

[0096] In this example, after obtaining the optimal solution, the syntax of the optimal solution is adjusted for the target database platform, due to differences in syntax rules and operation methods across different database platforms (such as MySQL, PostgreSQL, and Oracle). This includes converting the operation instructions, function names, and parameter formats in the optimized solution according to the syntax specifications and characteristics of each platform, ensuring that the optimization suggestions can be correctly executed on different platforms. Ultimately, a cross-platform optimization suggestion is output, covering index adjustments, query rewriting, and database and table sharding solutions.

[0097] In this embodiment, based on the joint embedding vector, an optimization strategy generation model is used to achieve accurate, efficient and universal database query optimization, which has significant advantages. It can deeply analyze the multimodal information contained in the input vector, accurately locate the query mode that can be optimized, and generate optimization suggestions that are both professional and universal through intelligent combination and format adaptation, effectively solving the query performance problems of different database platforms and improving the overall operation efficiency of the database.

[0098] See also Figure 6 A sixth embodiment of a database SQL query optimization method according to an embodiment of the present invention includes:

[0099] S601: Collect user feedback data, where the user feedback data includes query execution performance indicators and query result feedback.

[0100] In this embodiment, query execution performance indicators refer to the collection of query execution performance data, such as query response time, database server CPU utilization, memory usage, disk I / O times, and traffic. For example, the average query response time before and after optimization can be recorded to analyze the impact of the optimization strategy on performance.

[0101] Query result feedback refers to obtaining user feedback on query results to verify their accuracy and completeness. For example, users may discover that optimized query results are missing certain data or contain incorrect records. This feedback can help determine whether the optimization strategy has affected the accuracy of the query results.

[0102] S602: Mark the user feedback data to determine the problems reflected by the user feedback data.

[0103] In this embodiment, first, the noise and outliers in the user feedback data are removed. For example, data with abnormally high response time due to temporary system failure or feedback information that was entered incorrectly needs to be cleaned.

[0104] Then, we normalize different types of user feedback data to make them comparable. For example, we normalize performance metrics (such as response time and CPU utilization) and user feedback ratings to the same range to facilitate subsequent analysis and model updates.

[0105] Finally, add labels to user feedback data to clarify the types of problems reflected by the data, such as performance issues, result accuracy issues, and operational convenience issues.

[0106] S603: Update the parameters of the optimization strategy generation model according to the reflected problem to obtain an updated optimization strategy generation model, and use the updated optimization strategy generation model to replace the existing optimization strategy generation model.

[0107] In this example, we first determine the direction for adjusting model parameters based on the reported issues. For example, if we find that certain optimization strategies are ineffective on large-scale databases, we need to adjust parameters related to database scale, such as index adjustment thresholds and database and table sharding strategy parameters.

[0108] Then, a parameter update algorithm such as stochastic gradient descent (SGD) and adaptive moment estimation (Adam) is used to update the parameters of the model to obtain an updated optimization strategy generation model.

[0109] Finally, the updated optimization strategy generation model is used to replace the existing optimization strategy generation model.

[0110] In this embodiment, by constructing a closed-loop optimization system of "feedback collection-problem labeling-model update", the database SQL query optimization capability is dynamically improved and accurately adapted. The optimization strategy generation model parameters are updated and iterated based on the problem, so that the model can deeply learn the relationship between the optimization strategy and the actual effect, gradually optimize the strategy generation logic, and finally replace the original model with an updated model that is more adapted to actual needs, significantly enhancing the accuracy and reliability of the optimization strategy. At the same time, the system is given the ability to self-evolve, which can flexibly respond to dynamic changes in the database environment, query mode and business needs.

[0111] The above describes the database SQL query optimization method in the embodiment of the present invention. The following describes the device in the embodiment of the present invention. Figure 7 , the implementation of the database SQL query optimization device in the embodiment of the present invention includes:

[0112] The first generating module 701 is used to obtain a user request and generate an SQL statement according to the user request;

[0113] Parsing module 702, used to parse SQL statements to extract involved tables and obtain structural information of the involved tables from the database;

[0114] The second generation module 703 is used to obtain historical query logs, perform multimodal feature fusion on SQL statements, table structure information and historical query logs based on the pre-trained Transformer model, and generate a context-aware joint embedding vector;

[0115] A third generation module 704 is configured to input the joint embedding vector into a pre-trained optimization strategy generation model to obtain optimization suggestions applicable across platforms, wherein the optimization suggestions include index adjustment, query rewriting, and database and table sharding solutions;

[0116] The updating module 705 is used to collect user feedback data, update the parameters of the optimization strategy generation model according to the user feedback data, obtain an updated optimization strategy generation model, and use the updated optimization strategy generation model to replace the existing optimization strategy generation model.

[0117] In this embodiment, the first generation module 701 includes: a first acquisition unit 7011, used to obtain a user request, parse the user request to obtain a user request type and user request parameters; a determination unit 7012, used to determine a business scenario according to the user request type, and match an SQL template corresponding to the business scenario from a preset SQL template library according to the business scenario; a first generation unit 7013, used to verify the user request parameters, and fill the verified user request parameters into the matched SQL template to generate an SQL statement.

[0118] In this embodiment, the parsing module 702 includes: a parsing unit 7021, which is used to perform lexical analysis on the SQL statement to obtain multiple lexical units; an extraction unit 7022, which constructs an abstract syntax tree based on the multiple lexical units, traverses the abstract syntax tree, finds table-related nodes, and extracts the table name involved in the SQL statement from the table-related nodes; a second acquisition unit 7023, which is used to obtain structural information of the table involved from the database based on the table name.

[0119] In this embodiment, the second generation module 703 includes: a third acquisition unit 7031, which is used to obtain historical query logs from the log records of the database, extract query features of the historical query logs, and convert the query features into historical feature vectors; a conversion unit 7032, which is used to convert SQL statements into word vectors, and encode the structural information of the table to convert the structural information of the table into a table vector; a second generation unit 7033, which is used to input the historical feature vector, the word vector and the table vector into a pre-trained Transformer model for multimodal feature fusion to generate a context-aware joint embedding vector.

[0120] In this embodiment, the third generation module 704 includes: a third generation unit 7041, which is used to input the joint embedding vector into the pre-trained optimization strategy generation model to obtain an optimizable query pattern output by the optimization strategy generation model; a combination unit 7042, which is used to generate an optimization plan based on the optimizable query pattern. If there are multiple optimizable query patterns, the optimization plans corresponding to each query pattern are combined; an adjustment unit 7043, which is used to adjust the syntax format of the optimization plan, and output cross-platform optimization suggestions based on the adjusted optimization plan, wherein the optimization suggestions include index adjustment, query rewriting, and database and table sharding plans.

[0121] In this embodiment, the update module 705 includes: a collection unit 7051, which is used to collect user feedback data, and the user feedback data includes performance indicators of query execution and query result feedback; a labeling unit 7052, which is used to label the user feedback data and determine the problems reflected by the user feedback data; an update unit 7053, which is used to update the parameters of the optimization strategy generation model according to the reflected problems, obtain an updated optimization strategy generation model, and use the updated optimization strategy generation model to replace the existing optimization strategy generation model.

[0122] In this embodiment, the multimodal feature fusion capability of the Transformer model is based on the effective integration of query semantics, table structure and historical pattern information, and the optimization strategy generation model is combined to realize dynamic strategy generation. A closed-loop optimization is formed through user feedback, which can adapt to different database platforms, significantly improve query efficiency, reduce resource consumption, and enhance the system's adaptability and long-term optimization capabilities.

[0123] Figure 7 The structure of the database SQL query optimization device shown does not constitute a limitation on the database SQL query optimization device, and can implement the steps of the database SQL query optimization method provided by the above-mentioned method embodiments.

[0124] above Figure 7 The database SQL query optimization device in the embodiment of the present invention is described in detail from the perspective of modular functional entities. The database SQL query optimization device in the embodiment of the present invention is described in detail from the perspective of hardware processing.

[0125] Figure 8 : It is a structural diagram of a database SQL query optimization device provided by an embodiment of the present invention. The device 800 may have relatively large differences due to different configurations or performances, and may include one or more processors (central processing units, CPU) 810 (for example, one or more processors) and a memory 820, and one or more storage media 830 (for example, one or more massive storage devices) for storing application programs 833 or data 832. Among them, the memory 820 and the storage medium 830 can be short-term storage or persistent storage. The program stored in the storage medium 830 may include one or more modules (not shown), and each module may include a series of instruction operations on the device 800. Furthermore, the processor 810 can be configured to communicate with the storage medium 830 to execute a series of instruction operations in the storage medium on the device 800.

[0126] The device 800 may also include one or more power supplies 840, one or more wired or wireless network interfaces 850, one or more input and output interfaces 860, and / or one or more operating systems 831, such as Windows Server, Mac OS X, Unix, Linux, FreeBSD, etc.

[0127] An embodiment of the present invention also provides a computer-readable storage medium, which may be a non-volatile computer-readable storage medium or a volatile computer-readable storage medium. The computer-readable storage medium stores instructions, which, when executed on a computer, enable the computer to execute the steps of the database SQL query optimization method.

[0128] Those skilled in the art will clearly understand that, for the convenience and brevity of description, the specific working processes of the above-described systems, devices, and units can refer to the corresponding processes in the aforementioned method embodiments and will not be repeated here.

[0129] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention is essentially or the part that contributes to the prior art or all or part of the technical solution can be embodied in the form of a software product. The computer software product is stored in a storage medium and includes several instructions for enabling a computer device (which can be a personal computer, server, or network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM), random access memory (RAM), magnetic disk or optical disk, etc., various media that can store program code.

[0130] As described above, the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit the same. Although the present invention has been described in detail with reference to the above embodiments, those skilled in the art should understand that the technical solutions described in the above embodiments can still be modified, or some of the technical features thereof can be replaced by equivalents. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A database SQL query optimization method, characterized in that: The database SQL query optimization method includes: Obtaining user requests and generating SQL statements based on the user requests; Parse SQL statements to extract the involved tables and obtain the structural information of the involved tables from the database; Obtain historical query logs and perform multimodal feature fusion on SQL statements, table structure information, and historical query logs based on a pre-trained Transformer model to generate a context-aware joint embedding vector. Inputting the joint embedding vector into a pre-trained optimization strategy generation model to obtain cross-platform applicable optimization suggestions, including index adjustment, query rewriting, and database and table sharding solutions; Collect user feedback data, update the parameters of the optimization strategy generation model according to the user feedback data, obtain an updated optimization strategy generation model, and use the updated optimization strategy generation model to replace the existing optimization strategy generation model.

2. The database SQL query optimization method according to claim 1, characterized in that: The obtaining of the user request and generating an SQL statement according to the user request includes: Obtaining a user request, parsing the user request to obtain a user request type and user request parameters; Determine the business scenario according to the user request type, and match the SQL template corresponding to the business scenario from a preset SQL template library according to the business scenario; The user request parameters are verified, and the verified user request parameters are filled into the matched SQL template to generate an SQL statement.

3. The database SQL query optimization method according to claim 1, characterized in that: The step of parsing the SQL statement to extract the table names involved and obtaining the structural information of the tables involved from the database according to the table names includes: Perform lexical analysis on the SQL statement to obtain multiple lexical units; Build an abstract syntax tree based on multiple lexical units, traverse the abstract syntax tree, find table-related nodes, and extract the table name involved in the SQL statement from the table-related nodes; Get the structure information of the table involved from the database according to the table name.

4. The database SQL query optimization method according to claim 1, characterized in that: The process of obtaining historical query logs and performing multimodal feature fusion on SQL statements, table structure information, and historical query logs based on a pre-trained Transformer model to generate a context-aware joint embedding vector includes: Obtain historical query logs from database log records, extract query features from the historical query logs, and convert the query features into historical feature vectors; Convert SQL statements into word vectors and encode the table structure information to convert the table structure information into table vectors; The historical feature vector, the word vector, and the table vector are input into a pre-trained Transformer model for multimodal feature fusion to generate a context-aware joint embedding vector.

5. The database SQL query optimization method according to claim 4, characterized in that: The step of inputting the historical feature vector, the word vector, and the table vector into a pre-trained Transformer model for multimodal feature fusion to generate a context-aware joint embedding vector includes: Construct a Transformer model and introduce a cross-modal attention mechanism into the Transformer model to obtain an improved Transformer model; Training the improved Transformer model to obtain a pre-trained Transformer model; The historical feature vector, the word vector, and the table vector are input into a pre-trained Transformer model to obtain a context-aware joint embedding vector output by the pre-trained Transformer model.

6. The database SQL query optimization method according to claim 1, characterized in that: The joint embedding vector is input into a pre-trained optimization strategy generation model to obtain cross-platform applicable optimization suggestions, wherein the optimization suggestions include index adjustment, query rewriting, and database and table sharding solutions, including: Input the joint embedding vector into the pre-trained optimization strategy generation model to obtain the optimizable query pattern output by the optimization strategy generation model; Generate an optimization plan based on the query patterns that can be optimized. If there are multiple query patterns that can be optimized, combine the optimization plans corresponding to each query pattern. Adjust the syntax format of the optimization plan and output cross-platform optimization suggestions based on the adjusted optimization plan. The optimization suggestions include index adjustment, query rewriting, and database and table sharding solutions.

7. The database SQL query optimization method according to claim 1, characterized in that: The collecting of user feedback data, updating parameters of the optimization strategy generation model according to the user feedback data to obtain an updated optimization strategy generation model, and using the updated optimization strategy generation model to replace the existing optimization strategy generation model includes: Collecting user feedback data, including query execution performance indicators and query result feedback; Marking the user feedback data to determine the problem reflected by the user feedback data; The parameters of the optimization strategy generation model are updated according to the reflected problems to obtain an updated optimization strategy generation model, and the updated optimization strategy generation model is used to replace the existing optimization strategy generation model.

8. A database SQL query optimization device, characterized in that: include: A first generating module is used to obtain a user request and generate an SQL statement according to the user request; A parsing module, used for parsing SQL statements to extract involved tables and obtain structural information of the involved tables from the database; The second generation module is used to obtain historical query logs and perform multimodal feature fusion on SQL statements, table structure information, and historical query logs based on the pre-trained Transformer model to generate a context-aware joint embedding vector; A third generation module is configured to input the joint embedding vector into a pre-trained optimization strategy generation model to obtain optimization suggestions applicable across platforms, wherein the optimization suggestions include index adjustment, query rewriting, and database and table sharding solutions; The updating module is used to collect user feedback data, update the parameters of the optimization strategy generation model according to the user feedback data, obtain an updated optimization strategy generation model, and use the updated optimization strategy generation model to replace the existing optimization strategy generation model.

9. A database SQL query optimization device, characterized in that: comprising a memory and at least one processor, wherein the memory has computer-readable instructions stored therein; The at least one processor calls the computer-readable instructions in the memory to execute each step of the database SQL query optimization method according to any one of claims 1 to 7.

10. A computer-readable storage medium having computer-readable instructions stored thereon, characterized in that: When the computer-readable instructions are executed by a processor, the steps of the database SQL query optimization method according to any one of claims 1 to 7 are implemented.

Images