SQL query performance prediction method and device based on database mode perception
By constructing a query execution graph and a database schema relationship graph, combining attention mechanism and multi-layer neural network, the problems of single-hot encoding and correlation ignorance in the existing technology are solved, and more accurate SQL query performance prediction is achieved.
Patent Information
- Application Number
- CN202410971529.1
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-07-19
- Publication Date
- 2024-11-15
- Estimated Expiration
- 2044-07-19
AI Technical Summary
The existing SQL query performance prediction method relies on single-hot encoding when encoding query features, making it difficult to learn deep features, and ignores the explicit correlation between query statements and execution plans, and fails to make full use of database schema information, resulting in prediction accuracy. and insufficient generalization ability.
Using a database schema perception method, a new query execution graph structure is constructed, and the correlation between query statements and execution plans is captured using attention mechanism, and combined with the database schema relationship diagram, feature fusion and multi-layer fully connected neural network prediction query are carried out. Execution time.
It improves the accuracy of SQL query performance prediction and model understanding ability, enhances the prediction effect of complex queries, captures semantic information through unified semantic feature extraction and coding modules, and adaptively pays attention to database pattern features, improving prediction effect.
Smart Images

Figure CN118964155A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the fields of deep learning, data mining and database technology, and in particular to a method and device for predicting SQL query performance based on database pattern perception. Background Art
[0002] With the rapid development of big data and artificial intelligence technologies, a large amount of data is constantly being generated and collected. Behind the accumulation of data is the growing demand for data processing. As a tool for efficient storage and management of data, database systems play an important role in supporting data-driven decision-making, intelligent analysis and business innovation. Recently, many research works have combined neural network technology in deep learning with SQL query performance prediction scenarios. These models model complex queries and data patterns in the database to provide more accurate prediction models. Query performance prediction is usually affected by multiple factors in the database environment, such as the attributes of the query workload, the execution process of the query workload, data distribution, and the database model. It is very important to comprehensively consider multiple factors that affect query performance prediction.
[0003] There are still some shortcomings in the current research work: First, query feature encoding relies on one-hot encoding. Although this hand-designed hard coding is simple and intuitive, it has great limitations and is difficult to learn deep features, which affects the accuracy of prediction and the generalization ability of the model. Second, existing query performance prediction methods usually consider query statements or execution plans alone, ignoring the modeling of explicit correlations between the two. Models that only rely on query statements or execution plans lack an understanding of the entire query process and are difficult to accurately predict performance. Third, database schema and statistical information, as important factors affecting the effect of query performance prediction, have not been fully utilized. The database schema defines the table structure and the relationship between them, and the data distribution in the statistical information determines the selection and execution of the filter predicate. These features play an important role in solving the query performance prediction problem under complex queries. Summary of the invention
[0004] In order to solve at least one of the technical problems existing in the prior art to a certain extent, the present invention aims to provide a method and device for predicting SQL query performance based on database schema perception.
[0005] The technical solution adopted by the present invention is:
[0006] A method for predicting SQL query performance based on database schema perception includes the following steps:
[0007] Obtain SQL query statement data set, generate corresponding execution plan, extract relevant information of database mode, and preprocess the data set;
[0008] Get the SQL query statement, execution plan, and initialization feature vector of the database schema;
[0009] A new query execution graph structure is constructed using SQL query statements and execution plans. The correlation between SQL query statements and execution plans is captured through the attention mechanism to obtain execution plan features enhanced by query features.
[0010] Constructing a database schema relationship diagram according to the table-column relationship of the database schema, and obtaining comprehensive features of the database schema according to the database schema relationship diagram;
[0011] The execution plan features and comprehensive features are integrated to capture the degree of attention of nodes in the query execution graph to database schema features and generate feature vectors for SQL queries.
[0012] A multi-layer fully connected neural network is used to establish the relationship between the feature vector of SQL query and query performance to predict the query execution time.
[0013] Furthermore, the step of obtaining a SQL query statement data set, generating a corresponding execution plan, extracting relevant information of the database schema, and preprocessing the data set includes:
[0014] Execute the SQL query statement data set in a real database system environment, generate the execution plan for the corresponding query statement, and collect the data definition and statistical information of the database model;
[0015] Split the query statement into three query elements: query relationship table, connection condition and filter condition;
[0016] Extract effective attributes of execution plan nodes in the execution plan, including operation type, execution attributes, computing performance and other information, and extract query execution time as a prediction label;
[0017] Makes table column metadata definitions and statistics available as field properties for tables and columns in the database schema.
[0018] Furthermore, the obtaining of the SQL query statement, execution plan and initialization feature vector of the database mode includes:
[0019] Use Word2vec word vector technology to generate initial feature vectors for attribute field words in SQL query statements, execution plans, and database schemas.
[0020] Furthermore, after Word2vec generates the initial semantic features, a bidirectional long short-term memory network BiLSTM is concatenated to extract and fuse the comprehensive information in the attribute sequence to capture higher-quality initial query element features with context dependence.
[0021] Furthermore, the SQL query statement and the execution plan are used to construct a new query execution graph structure, and the correlation between the SQL query statement and the execution plan is captured through the attention mechanism to obtain the execution plan features after query feature enhancement, including:
[0022] Based on the execution nodes of the execution plan, query nodes are introduced, that is, the query relationship table, connection conditions and filter predicates in the SQL query statement are used as three types of query nodes, and the association is established with the execution nodes;
[0023] Add edges between query nodes and execution nodes to form a new query execution graph structure. The edge order follows the logical order of the query expression.
[0024] Query-guided attention is used to capture the information generated by the SQL query statement guiding the execution plan, and relation-aware attention is used to capture the execution logic and computing performance information of the execution plan, thus obtaining the execution plan features enhanced by query features.
[0025] Furthermore, the query-guided attention is used to capture the information of the SQL query statement guiding the execution plan generation, and the relationship-aware attention is used to capture the execution logic and computing performance information of the execution plan, to obtain the execution plan features after query feature enhancement, including:
[0026] The query-guided attention mechanism is used to model the information generated by query nodes guiding execution plan nodes, and the attention mask is used to control the logical guidance relationship between the two types of nodes;
[0027] The relationship-aware attention mechanism is used to control the dependencies between execution nodes and the direction of information flow to ensure that execution information flows from bottom to top;
[0028] The query execution graph completes feature update and learning through an encoder based on the attention mechanism, generating execution plan features enhanced by query features.
[0029] Furthermore, constructing a database schema relationship graph according to the table column relationship of the database schema, and obtaining comprehensive features of the database schema according to the database schema relationship graph, includes:
[0030] The table columns of the database schema are associated and organized into a relationship graph as the database schema relationship graph; wherein the nodes of the relationship graph are composed of table nodes and column nodes, and the edge types are divided into ten types of relationship edges according to the connection nodes, primary and foreign key relationships, and connection directions;
[0031] A relational graph convolutional neural network is used to update node features, taking into account the topological structure of the edges between nodes and the rich information of various edge types;
[0032] The average of the features of all table nodes is taken as the comprehensive feature of database schema information.
[0033] Furthermore, the execution plan features and the comprehensive features are integrated to capture the degree of attention of the nodes in the query execution graph to the database mode features and generate the feature vector of the SQL query, including:
[0034] Calculate the correlation score between each node feature in the query execution graph and the database schema feature;
[0035] The query execution node features are weighted and summed using the correlation score to complete feature fusion.
[0036] Furthermore, the establishing of the relationship between the feature vector of the SQL query and the query performance to predict the query execution time includes:
[0037] A two-layer fully connected neural network is built, taking the feature vector of the SQL query as input and the normalized query execution time as the prediction label;
[0038] The model training uses the average mean square error as the loss function. The model continuously adjusts the weight parameters according to the loss function value until the loss function converges and the model training is completed.
[0039] Another technical solution adopted by the present invention is:
[0040] A SQL query performance prediction device based on database mode perception, comprising:
[0041] at least one processor;
[0042] at least one memory for storing at least one program;
[0043] When the at least one program is executed by the at least one processor, the at least one processor implements the above method.
[0044] Compared with the prior art, the present invention has the following beneficial effects:
[0045] (1) The present invention comprehensively considers the characteristics of SQL query statements, execution plans, and database schemas, and adopts a unified semantic feature extraction and encoding module to initialize the features of the three query elements. Compared with one-hot encoding, it can capture the semantic information behind the query and enhance the model's understanding ability.
[0046] (2) The present invention designs a new query execution graph structure, which splits the query statement into query nodes and establishes connections with execution plan nodes. It also captures the information generated by the query nodes guiding the execution plan nodes through the query-guided attention mechanism and the relationship-aware attention mechanism, and proposes a hierarchical traversal position encoding modeling execution logic order.
[0047] (3) The present invention uses a relational graph convolutional neural network to perform feature learning on the database model relationship graph. The characteristics of the nodes are composed of the data distribution in the table column metadata and statistical information. The relationship between the edges contains rich subordinate relationships and primary and foreign key connection relationships between table columns. The comprehensive characteristics of the data model are obtained through the relational graph convolution operation.
[0048] (4) The present invention fuses the query execution graph and database schema information through a fusion method based on database schema perception, so that the node features in the query execution graph can adaptively focus on the impact of database schema features and mine key node operations that affect query performance, thereby improving the query performance prediction effect. BRIEF DESCRIPTION OF THE DRAWINGS
[0049] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the embodiments of the present invention or the drawings of related technical solutions in the prior art are introduced below. It should be understood that the drawings introduced below are only for the convenience of clearly describing some embodiments of the technical solutions of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative work.
[0050] Figure 1 is a flow chart of a method for predicting SQL query performance based on database mode perception in an embodiment of the present invention;
[0051] Figure 2 It is a model overall framework diagram of the method in the embodiment of the present invention;
[0052] Figure 3 is a schematic diagram of the query execution graph structure in an embodiment of the present invention;
[0053] Figure 4 It is a schematic diagram of the structure of the query execution graph encoder in an embodiment of the present invention. DETAILED DESCRIPTION
[0054] The embodiments of the present invention are described in detail below, and examples of the embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals throughout represent the same or similar elements or elements having the same or similar functions. The embodiments described below with reference to the accompanying drawings are exemplary and are only used to explain the present invention, and are not to be construed as limitations of the present invention. For the step numbers in the following embodiments, they are only provided for the convenience of explanation, and the order between the steps is not limited in any way, and the execution order of each step in the embodiment can be adaptively adjusted according to the understanding of those skilled in the art.
[0055] In the description of the present invention, it should be understood that descriptions involving orientations, such as up, down, front, back, left, right, etc., and orientations or positional relationships indicated are based on the orientations or positional relationships shown in the accompanying drawings, and are only for the convenience of describing the present invention and simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, be constructed and operated in a specific orientation, and therefore should not be understood as a limitation on the present invention.
[0056] In the description of the present invention, the meaning of "several" is one or more, the meaning of "more" is two or more, and the meanings of "greater than", "less than", "exceed" and the like are understood as not including the number itself, and the meanings of "above", "below", "within" and the like are understood as including the number itself. If there is a description of the first and the second, it is only used for the purpose of distinguishing the technical features, and cannot be understood as indicating or implying the relative importance or implicitly indicating the number of the indicated technical features or implicitly indicating the order of the indicated technical features. In addition, "and / or" describes the association relationship of the associated objects, indicating that there can be three relationships. For example, A and / or B can represent: A exists alone, A and B exist at the same time, and B exists alone. The character " / " generally indicates that the related objects before and after are in an "or" relationship.
[0057] In the description of the present invention, unless otherwise clearly defined, terms such as setting, installing, connecting, etc. should be understood in a broad sense, and technicians in the relevant technical field can reasonably determine the specific meanings of the above terms in the present invention based on the specific content of the technical solution.
[0058] like Figure 1 and Figure 2 As shown, this embodiment provides a SQL query performance prediction method based on database pattern perception, uses a query execution graph structure to explicitly model the relationship between query statements and execution plans, and proposes an attention mechanism based on query guidance and relationship perception, uses query element features to enhance execution plan features, and uses a relational graph convolutional neural network to model database patterns and statistical information, and finally generates SQL query features based on database pattern perception for query performance prediction. The method specifically includes the following steps:
[0059] S1. Obtain SQL query statement data set, generate corresponding execution plan, extract relevant information of database mode, and preprocess the data set.
[0060] Specifically, SQL query statements are collected in the database environment, the corresponding execution plan is generated, and the relevant information of the database model is extracted. The effective attributes of the three types of input data are extracted to complete the preprocessing of the data set.
[0061] As an optional implementation, step S1 specifically includes steps S11-S15:
[0062] S11. Taking PostgreSQL database as an example, select the public SQL query datasets IMDB and STATS. Create tables and import raw data according to the database schema provided by the IMDB and STATS datasets. Generate training datasets by randomly selecting query relationship tables, join conditions, and filter predicates. The test set uses the default test workloads of the two databases.
[0063] S12. Execute the EXPLAIN ANALYZE command in the PostgreSQL database system environment to generate corresponding execution plans for all query statements. The execution time of the SQL query is the average of three runs.
[0064] S13. Collect SQL query statements, and split the query statements into three query elements: query relation table, join condition, and filter condition.
[0065] S14. Collect the execution plan and extract effective attributes for each execution plan node in the execution plan, mainly including operation type, execution attribute, computing performance and other information, and extract the query execution time as the prediction label.
[0066] S15. For database schema information, metadata definitions of table columns, such as data types, primary and foreign keys, indexes and other database definition objects, and statistical information are combined as valid field attributes of tables and columns in the database schema.
[0067] S2. Get the SQL query statement, execution plan, and initialization feature vector of the database schema.
[0068] Specifically, a unified semantic feature extraction and encoding module is used to obtain the initialization feature vectors of SQL query statements, execution plans and database schemas.
[0069] As an optional implementation, step S2 specifically includes steps S21-S22:
[0070] S21. Use Word2vec word vector technology to generate initial feature vectors for attribute field words in query statements, execution plans, and database schemas.
[0071] S22. In order to process the contextual information of short sequences and capture higher-quality initial query element features with contextual dependency, after Word2vec generates the initial semantic features, a bidirectional long short-term memory network BiLSTM is spliced to extract and fuse the comprehensive information in the attribute sequence. The semantic feature extraction module is composed of Word2vec combined with BiLSTM. For the three query elements, they can be represented as a set of valid attributes after data preprocessing. The attribute sequence is treated as a short text and passes through the semantic feature extraction module to generate an initial feature vector with contextual information.
[0072]
[0073] in, It is the feature of the i-th word in the query element attribute sequence initialized by Word2vec. This feature is passed through BiLSTM to generate the forward pass feature of each word and back-propagation features The final feature E seq is the forward feature of the last word in the sequence and the reverse feature of the first word Splicing formation.
[0074] S3. Use SQL query statements and execution plans to construct a new query execution graph structure, capture the correlation between SQL query statements and execution plans through the attention mechanism, and obtain the execution plan features after query feature enhancement.
[0075] Specifically, a new query execution graph structure is constructed using SQL query statements and execution plans to explicitly model the correlation between the two. At the same time, the information guided by the query statements to generate the execution plan and the computing performance requirement information of the execution plan are captured through the attention mechanism to obtain the execution plan features after query feature enhancement.
[0076] As an optional implementation, step S3 specifically includes steps S31-S35:
[0077] S31, such as Figure 3 As shown, based on the execution nodes of the execution plan, query nodes are introduced, that is, the query relationship table, connection conditions and filter predicates in the query statement are used as three types of query nodes, and an association is established with the execution nodes.
[0078] S32. The edges between execution plan nodes represent the direction of execution logic and query data flow, which itself forms a tree structure. In addition to the execution order edges of the execution nodes themselves, edges are added between query nodes. The edge order follows the logical order of the query expression, that is, they are connected in the order of query relationship table, connection conditions, and filter predicates.
[0079] S33. The information that query elements guide the generation of execution plans mainly includes: the query relationship table contains the source of the query to be executed by the operation node, the operation node of the join condition converted to the Join type, the operation node of the filter predicate converted to the Scan type, etc. Therefore, an edge is added between the query node and the execution node, that is, the query node guides the generation of the execution plan node, and an edge is added between the two nodes to form a new query execution graph structure.
[0080] S34. The input features of the query execution graph are composed of the node semantic features, the hierarchical traversal position encoding features and the node type features. The hierarchical traversal position encoding represents the execution order of the nodes for generating the execution plan after query parsing. The node type is used to distinguish query nodes from execution nodes. The query execution graph encoder is composed of a multi-layer network. The attention of each layer includes relationship-aware attention and query-guided attention. The attention network layer is followed by a feedforward neural network layer. The encoder based on the attention mechanism completes the update and learning of the features and generates the execution plan features enhanced by the query features.
[0081] E qp =emd(node)+order(node)+type(node)
[0082] E qp =LayerNorm(E qp +Attention(E qp ))
[0083] E qp =LayerNorm(E qp +FeedForward(E qp ))
[0084] Among them, E qp To query the execution graph node features, the node features are composed of the node semantic initialization feature emd(node), the hierarchical traversal position encoding order(node) and the node type encoding type(node). LayerNorm is the normalization layer, Attention is the attention network layer, and FeedForward is the feedforward neural network layer.
[0085] S35, such as Figure 4As shown in the figure, the query execution graph encoder uses query-guided attention to capture the information generated by the query statement to guide the execution plan, and uses relationship-aware attention to capture the execution logic and computing performance information of the execution plan, and obtains the execution plan features after query feature enhancement. The query-guided attention mechanism is used to model the information generated by the query node guiding the execution plan node. The attention mask is used to control the logical guidance relationship between the two types of nodes. The relationship-aware attention mechanism is used to control the dependency relationship and information flow direction between the execution nodes to ensure that the execution information flows from the bottom to the top.
[0086]
[0087] Among them, E qp Query execution graph node features, W Q , W K , W V are the query, key, and value weight matrices of self-attention in the query execution graph encoder, respectively. Softmax is the activation function. is a scaling factor, Rel is a relationship-aware attention mask used to control the direction of data flow between execution plan nodes to ensure that the execution processing logic is bottom-up, and Guide is a query-guided attention mask, that is, the attention mechanism is used only when there is a mapping relationship between the query node and the execution node, such as a query table, a join condition, and a filter predicate.
[0088] S4. Construct a database model relationship diagram according to the table column relationship of the database model, and obtain comprehensive characteristics of the database model according to the database model relationship diagram.
[0089] As an optional implementation, step S4 specifically includes steps S41-43:
[0090] S41. The table-column associations of the database model are organized into a relationship graph, where the nodes are composed of table nodes and column nodes. The edge types are divided into ten types of relationship edges according to the connection nodes, primary and foreign key relationships, connection directions, etc., including three relationships of foreign key associations between tables, three relationships between columns belonging to the same table or foreign key associations, two relationships of primary key associations between tables and columns, and two relationships of primary key associations between columns and tables.
[0091] S42. A relational graph convolutional neural network is used to update the features of database pattern nodes, while taking into account the topological structure of the edges between nodes and the rich information of various edge types. Compared with simple graph convolutional neural networks, the relational graph convolutional neural network not only takes into account the topological structure of the edges between nodes, but also assigns different weights to the edge types. It is very suitable for application in database pattern relationship graphs with multiple edge types.
[0092]
[0093] in, is the feature of the relationship graph node in the l-th layer of graph convolution, is the feature of the graph convolution of the relationship graph node in the l+1 layer, As node n i The size of the neighbor set represents the normalization factor, r represents the relationship type between nodes, and for different neighbors n j and node n i The edge relationship uses different linear transformation matrices ,and is the transformation matrix corresponding to the node self-connection.
[0094] S43. Take the average of the features of all table nodes in the last layer of relationship graph convolution as the comprehensive features of the database pattern information, which is used to model the characteristics of table column connection relationships and implicit data distribution.
[0095] S5. The execution plan features and the comprehensive features are integrated to capture the degree of attention paid by the nodes in the query execution graph to the database schema features, and generate a feature vector for the SQL query.
[0096] As an optional implementation, step S5 specifically includes steps S51-52:
[0097] S51. Calculate the correlation score between each node feature in the query execution graph and the database schema feature by means of cosine similarity. The correlation score measures the influence of the database schema on the node operation.
[0098]
[0099] Among them, a i is the relevance score of the i-th node, E s is the database schema relationship diagram feature, E qpi To query the node features in the execution graph, n is the total number of nodes. The attention score is calculated using the dot product method and is normalized as the weight of the current node.
[0100] S52. Perform weighted summation of query execution node features through correlation scores to complete the fusion of query execution graph node features and database schema features, and capture the node's perception of database schema information.
[0101] S6. Use a multi-layer fully connected neural network to establish the relationship between the feature vector of the SQL query and the query performance to predict the query execution time.
[0102] As an optional implementation, step S6 specifically includes steps S61-S62:
[0103] S61. Establish a two-layer fully connected neural network, take the SQL query features as input, and output the standardized query execution time as the prediction label after linear transformation.
[0104] S62. The average mean square error is used as the loss function in model training. The model continuously adjusts the weight parameters according to the loss function value until the loss function converges and the model training is completed.
[0105] In summary, the method of this embodiment has the following advantages and beneficial effects compared with the prior art:
[0106] The method of this embodiment aims to solve the problems of explicitly modeling the correlation between query statements and execution plans and making full use of database schema information. A unified semantic feature extraction module is used to encode SQL query statements, execution plans, and database schemas, and a new query execution graph structure is designed to directly establish connections between query nodes and execution plan nodes. The information generated by the query-guided execution plan and the execution optimization logic sequence are captured through query-guided attention and relationship-aware attention. A relational graph convolutional neural network is used to learn the connection patterns and statistical information contained in the database schema relationship graph. The node features of the query execution graph are fused in a database schema-aware manner to pay attention to the impact of database schema features on query operations, thereby improving the query performance prediction effect for complex queries.
[0107] This embodiment also provides a SQL query performance prediction device based on database mode perception, including:
[0108] at least one processor;
[0109] at least one memory for storing at least one program;
[0110] When the at least one program is executed by the at least one processor, the at least one processor implements Figure 1 and / or Figure 2 / or Figure 3 / or Figure 4 The method shown.
[0111] A SQL query performance prediction device based on database pattern perception in this embodiment can execute a SQL query performance prediction method based on database pattern perception provided by an embodiment of the method of the present invention, can execute any combination of implementation steps of the method embodiment, and has the corresponding functions and beneficial effects of the method.
[0112] The present application also discloses a computer program product or a computer program, which includes a computer instruction stored in a computer-readable storage medium. A processor of a computer device can read the computer instruction from the computer-readable storage medium, and the processor executes the computer instruction, so that the computer device executes Figure 1 and / or Figure 2 / or Figure 3 / or Figure 4 The method shown.
[0113] This embodiment also provides a storage medium storing instructions or programs that can execute a SQL query performance prediction method based on database pattern perception provided by an embodiment of the method of the present invention. When the instructions or programs are run, any combination of implementation steps of the method embodiment can be executed, and the corresponding functions and beneficial effects of the method can be obtained.
[0114] In some selectable embodiments, the function / operation mentioned in the block diagram may not occur in the order mentioned in the operation diagram. For example, depending on the function / operation involved, the two boxes shown in succession can actually be executed substantially simultaneously or the boxes can sometimes be executed in reverse order. In addition, the embodiment presented and described in the flow chart of the present invention is provided by way of example, for the purpose of providing a more comprehensive understanding of technology. The disclosed method is not limited to the operation and logic flow presented herein. Selectable embodiments are expected, wherein the order of various operations is changed and the sub-operation of a part for which is described as a larger operation is performed independently.
[0115] In addition, although the present invention is described in the context of functional modules, it should be understood that, unless otherwise specified, one or more of the functions and / or features described may be integrated into a single physical device and / or software module, or one or more functions and / or features may be implemented in separate physical devices or software modules. It is also understood that a detailed discussion of the actual implementation of each module is unnecessary for understanding the present invention. More specifically, in view of the properties, functions, and internal relationships of the various functional modules in the device disclosed herein, the actual implementation of the module will be understood within the conventional skills of the engineer. Therefore, those skilled in the art can implement the present invention set forth in the claims without excessive experimentation using ordinary techniques. It is also understood that the specific concepts disclosed are merely illustrative and are not intended to limit the scope of the present invention, which is determined by the full scope of the appended claims and their equivalents.
[0116] If the functions are implemented in the form of software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art or the 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, including several instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the methods described in each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk, etc., which can store program codes.
[0117] The logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as an ordered list of executable instructions for implementing logical functions, and can be embodied in any computer-readable medium for use by an instruction execution system, device or apparatus (such as a computer-based system, a system including a processor, or other system that can fetch instructions from an instruction execution system, device or apparatus and execute instructions), or in conjunction with such instruction execution systems, devices or apparatuses. For the purposes of this specification, "computer-readable medium" can be any device that can contain, store, communicate, propagate or transmit a program for use by an instruction execution system, device or apparatus, or in conjunction with such instruction execution systems, devices or apparatuses.
[0118] More specific examples of computer-readable media (a non-exhaustive list) include the following: an electrical connection with one or more wires (electronic device), a portable computer disk case (magnetic device), a random access memory (RAM), a read-only memory (ROM), an erasable and programmable read-only memory (EPROM or flash memory), an optical fiber device, and a portable compact disk read-only memory (CDROM). In addition, the computer-readable medium may even be a paper or other suitable medium on which the program is printed, since the program may be obtained electronically, for example, by optically scanning the paper or other medium, followed by editing, deciphering or, if necessary, processing in another suitable manner, and then stored in a computer memory.
[0119] It should be understood that the various parts of the present invention can be implemented by hardware, software, firmware or a combination thereof. In the above-mentioned embodiments, a plurality of steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system. For example, if implemented by hardware, as in another embodiment, it can be implemented by any one of the following technologies known in the art or their combination: a discrete logic circuit having a logic gate circuit for implementing a logic function for a data signal, a dedicated integrated circuit having a suitable combination of logic gate circuits, a programmable gate array (PGA), a field programmable gate array (FPGA), etc.
[0120] In the above description of this specification, the description with reference to the terms "one embodiment / example", "another embodiment / example" or "certain embodiments / examples" etc. means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present invention. In this specification, the schematic representation of the above terms does not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described may be combined in any one or more embodiments or examples in a suitable manner.
[0121] Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the claims and their equivalents.
[0122] The above is a specific description of the preferred implementation of the present invention, but the present invention is not limited to the above embodiments. Those skilled in the art may make various equivalent modifications or substitutions without violating the spirit of the present invention. These equivalent modifications or substitutions are all included in the scope defined by the claims of this application.
Claims
1. A SQL query performance prediction method based on database pattern perception, characterized in that: The following steps are involved: Obtain SQL query statement data set, generate corresponding execution plan, extract relevant information of database mode, and preprocess the data set; Get the SQL query statement, execution plan, and initialization feature vector of the database schema; A new query execution graph structure is constructed using SQL query statements and execution plans. The correlation between SQL query statements and execution plans is captured through the attention mechanism to obtain execution plan features enhanced by query features. Constructing a database schema relationship diagram according to the table-column relationship of the database schema, and obtaining comprehensive features of the database schema according to the database schema relationship diagram; The execution plan features and comprehensive features are integrated to capture the degree of attention of nodes in the query execution graph to database schema features and generate feature vectors for SQL queries. The relationship between the feature vector of SQL query and query performance is established to predict query execution time.
2. The method for predicting SQL query performance based on database schema perception according to claim 1, characterized in that: The step of obtaining a SQL query statement data set, generating a corresponding execution plan, extracting relevant information of the database mode, and preprocessing the data set includes: Execute the SQL query statement data set in a real database system environment, generate the execution plan for the corresponding query statement, and collect the data definition and statistical information of the database model; Split the query statement into three query elements: query relationship table, connection condition and filter condition; Extract effective attributes from execution plan nodes in the execution plan, and extract query execution time as a prediction label; Makes table column metadata definitions and statistics available as field properties for tables and columns in the database schema.
3. The method for predicting SQL query performance based on database schema perception according to claim 1, characterized in that: The step of obtaining the SQL query statement, execution plan, and initialization feature vector of the database mode includes: Use Word2vec word vector technology to generate initial feature vectors for attribute field words in SQL query statements, execution plans, and database schemas.
4. The method for predicting SQL query performance based on database schema perception according to claim 3, characterized in that: After Word2vec generates the initial semantic features, a bidirectional long short-term memory network BiLSTM is concatenated to extract and fuse the comprehensive information in the attribute sequence to capture better initial query element features with context dependence.
5. The method for predicting SQL query performance based on database schema perception according to claim 1, characterized in that: The SQL query statement and execution plan are used to construct a new query execution graph structure, and the correlation between the SQL query statement and the execution plan is captured through the attention mechanism to obtain the execution plan features after query feature enhancement, including: Based on the execution nodes of the execution plan, query nodes are introduced, that is, the query relationship table, connection conditions and filter predicates in the SQL query statement are used as three types of query nodes, and the association is established with the execution nodes; Add edges between query nodes and execution nodes to form a new query execution graph structure. The edge order follows the logical order of the query expression. Query-guided attention is used to capture the information of SQL query statements guiding the generation of execution plans, and relation-aware attention is used to capture the execution logic and computing performance information of the execution plan, thus obtaining the execution plan features enhanced by query features.
6. The method for predicting SQL query performance based on database schema perception according to claim 5, characterized in that: The method of using query-guided attention to capture the information of SQL query statements guiding the generation of execution plans, and using relationship-aware attention to capture the execution logic and computing performance information of the execution plans, to obtain execution plan features enhanced by query features, includes: The query-guided attention mechanism is used to model the information generated by query nodes guiding execution plan nodes, and the attention mask is used to control the logical guidance relationship between the two types of nodes; The relationship-aware attention mechanism is used to control the dependencies between execution nodes and the direction of information flow to ensure that execution information flows from bottom to top; The query execution graph completes feature update and learning through an encoder based on the attention mechanism, generating execution plan features enhanced by query features.
7. The method for predicting SQL query performance based on database schema perception according to claim 1, characterized in that: The step of constructing a database schema relationship graph according to the table column relationship of the database schema and obtaining comprehensive features of the database schema according to the database schema relationship graph includes: Associating and organizing the tables and columns of the database schema into a relationship graph as a database schema relationship graph; wherein the nodes of the relationship graph are composed of table nodes and column nodes; A relational graph convolutional neural network is used to update node features, taking into account the topological structure of the edges between nodes and the rich information of various edge types; The average of the features of all table nodes is taken as the comprehensive feature of database schema information.
8. The method for predicting SQL query performance based on database schema perception according to claim 1, characterized in that: The execution plan features and comprehensive features are integrated to capture the degree of attention of nodes in the query execution graph to database mode features and generate a feature vector of the SQL query, including: Calculate the correlation score between each node feature in the query execution graph and the database schema feature; The query execution node features are weighted and summed using the correlation score to complete feature fusion.
9. The method for predicting SQL query performance based on database schema perception according to claim 1, characterized in that: The step of establishing the relationship between the feature vector of the SQL query and the query performance to predict the query execution time includes: A two-layer fully connected neural network is built, taking the feature vector of the SQL query as input and the normalized query execution time as the prediction label; The model training uses the average mean square error as the loss function. The model continuously adjusts the weight parameters according to the loss function value until the loss function converges and the model training is completed.
10. A SQL query performance prediction device based on database pattern perception, characterized in that: include: at least one processor; at least one memory for storing at least one program; When the at least one program is executed by the at least one processor, the at least one processor implements the method according to any one of claims 1 to 9.
Citation Information
Patent Citations
Method, device and system for predicting performance of database management system
CN113806190A
Structured query statement performance prediction method and device, equipment and medium
CN116680536A
SQL (Structured Query Language) feature extraction method and device based on multi-modal pre-training and medium
CN117555915A
Fully automated SQL tuning
US20090077016A1
Cited By
Autonomous decision-making by estimating performance of artificial intelligence agents
WO2026151674A1
