A method and device for predicting SQL query performance based on database schema perception

Abstract

This invention discloses a database schema-aware SQL query performance prediction method and apparatus. The method includes: acquiring SQL query statements, execution plans, and database schema information; extracting features and uniformly encoding various query-related elements; generating a novel query execution graph structure using the query statement and execution plan, learning the explicit correlations and execution processing logic between the two through an attention mechanism, and obtaining query execution graph features that integrate both information; using a relational graph convolutional neural network to learn features from the database schema relational graph, capturing rich connections between table columns and underlying data distribution information; and fusing the query execution graph and database schema information through a database schema-aware fusion method to generate query features for predicting query performance. This invention fully utilizes various query-related information and mines the correlations between features to improve query performance prediction, and can be applied to the field of database technology.

Description

Technical Field

[0001] This invention relates to the fields of deep learning, data mining and database technology, and in particular to a method and apparatus for predicting SQL query performance based on database schema awareness. Background Technology

[0002] With the rapid development of big data and artificial intelligence technologies, massive amounts of data are constantly being generated and collected. Behind this data accumulation lies a continuously growing demand for data processing. Database systems, as tools for efficiently storing and managing data, play a crucial role in supporting data-driven decision-making, intelligent analysis, and business innovation. Recent research has combined neural network techniques from deep learning with SQL query performance prediction scenarios. These models model complex queries and data patterns in databases to provide more accurate prediction models. Query performance prediction is typically influenced 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 schema. Therefore, comprehensively considering these multiple factors affecting query performance prediction is essential.

[0003] Current research still has some shortcomings: First, query feature encoding relies on one-hot encoding. While this manually designed hard encoding is simple and intuitive, it has significant limitations and struggles to learn deep features, thus affecting prediction accuracy and model generalization ability. Second, existing query performance prediction methods typically consider only the query statement or execution plan, neglecting to model the explicit correlation between them. Models relying solely on the query statement or execution plan lack a comprehensive understanding of the query process and are difficult to accurately predict performance. Third, database schema and statistical information, as important factors influencing query performance prediction, have not been fully utilized. The database schema defines the table structure and the relationships between them, and the data distribution in statistical information determines the selection and execution of filtering predicates. These features play a crucial role in solving query performance prediction problems under complex queries. Summary of the Invention

[0004] In order to at least partially solve one of the technical problems existing in the prior art, the purpose of this invention is to provide a method and apparatus for predicting SQL query performance based on database schema awareness.

[0005] The technical solution adopted in this invention is:

[0006] A database schema-aware SQL query performance prediction method includes the following steps:

[0007] Obtain the SQL query statement dataset, generate the corresponding execution plan, extract relevant database schema information, and preprocess the dataset;

[0008] Obtain the initial feature vectors of the SQL query statement, execution plan, and database schema;

[0009] A novel query execution graph structure is constructed using SQL query statements and execution plans. An attention mechanism is used to capture the correlation between SQL query statements and execution plans, resulting in execution plan features enhanced by query features.

[0010] Construct a database schema relationship diagram based on the table column relationships in the database schema, and obtain the comprehensive characteristics of the database schema based on the database schema relationship diagram;

[0011] By fusing execution plan features and comprehensive features, the degree of attention that nodes in the query execution graph pay to database schema features is captured, and feature vectors of SQL queries are generated.

[0012] A multi-layer fully connected neural network is used to establish the relationship between the feature vector of SQL queries and query performance in order to predict query execution time.

[0013] Furthermore, the steps of obtaining the SQL query statement dataset, generating the corresponding execution plan, extracting relevant database schema information, and preprocessing the dataset include:

[0014] The SQL query statement dataset is executed in a real database system environment to generate the execution plan for the corresponding query statement, while collecting data definition and statistical information of the database schema.

[0015] The query statement is broken down into three query elements: query relationship table, join conditions, and filter conditions;

[0016] Effective attributes are extracted from the execution plan nodes in the execution plan, mainly including information such as operation type, execution attributes, and computational performance. At the same time, the query execution time is extracted as a prediction label.

[0017] Use the metadata definitions and statistics of table columns as valid field attributes for tables and columns in the database schema.

[0018] Furthermore, obtaining the initialization feature vector of the SQL query statement, execution plan, and database schema 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 comprehensive information from the attribute sequence in order to capture higher-quality initial query element features with contextual dependencies.

[0021] Furthermore, the method involves constructing a novel query execution graph structure using SQL query statements and execution plans, capturing the correlation between SQL query statements and execution plans through an attention mechanism, and obtaining execution plan features enhanced by query features, including:

[0022] Based on the execution nodes of the execution plan, query nodes are introduced, that is, the query relation tables, join conditions and filter predicates in the SQL query statement are treated as three types of query nodes and associated with the execution nodes;

[0023] Add edges between query nodes and execution nodes to form a new query execution graph structure; the order of the edges follows the logical order of the query expression.

[0024] By leveraging query-guided attention to capture information about the execution plan generated by the SQL query statement, and by leveraging relation-aware attention to capture the execution logic and computational performance information of the execution plan, we obtain the execution plan features enhanced by query features.

[0025] Furthermore, the process of capturing information about the execution plan generation guided by the SQL query statement using query-guided attention, and capturing the execution logic and computational performance information of the execution plan using relation-aware attention, yields execution plan features enhanced by query features, including:

[0026] The query-guided attention mechanism is used to model the information generated by the query node to guide the execution plan node, and uses attention masking to control the logical guidance relationship between the two types of nodes;

[0027] Relationship-aware attention mechanisms are used to control the dependencies and information flow direction between execution nodes to ensure that execution information flows from bottom to top.

[0028] The query execution graph is updated and learned by an attention-based encoder to generate execution plan features enhanced by query features.

[0029] Furthermore, the step of constructing a database schema relationship diagram based on the table column relationships of the database schema, and obtaining comprehensive features of the database schema based on the database schema relationship diagram, includes:

[0030] The table columns of the database schema are organized into a relational graph, which is called the database schema relational graph. The nodes of the relational graph consist of table nodes and column nodes, and the edge types are divided into ten types based on the connection nodes, primary and foreign key relationships, connection direction, etc.

[0031] A relational graph convolutional neural network is used to update the features of nodes, taking into account the topological structure of the edges between nodes and the rich information of various edge types.

[0032] The average value of the features of all table column nodes is taken as the comprehensive feature of the database schema information.

[0033] Furthermore, the process of fusing execution plan features and comprehensive features to capture the degree of attention nodes in the query execution graph pay to database schema features generates a feature vector for the SQL query, including:

[0034] Calculate the correlation score between the features of each node in the query execution graph and the features of the database schema;

[0035] Feature fusion is achieved by weighting and summing the features of the query execution nodes using relevance scores.

[0036] Furthermore, establishing the relationship between the feature vector of the SQL query and query performance to predict query execution time includes:

[0037] A two-layer fully connected neural network is established, with the feature vector of the SQL query as input and the standardized query execution time as the prediction label;

[0038] The model training uses the mean squared error as the loss function. The model continuously adjusts the weight parameters based on the loss function value until the loss function converges, thus completing the model training.

[0039] Another technical solution adopted in this invention is:

[0040] A database schema-aware SQL query performance prediction device includes:

[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 method described above.

[0044] Compared with the prior art, the present invention has the following beneficial effects:

[0045] (1) This invention comprehensively considers the characteristics of SQL query statements, execution plans and database models, and uses 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) This invention designs a novel 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 in the execution plan nodes through query-guided attention mechanism and relation-aware attention mechanism. At the same time, it proposes hierarchical traversal position encoding to model the order of execution logic.

[0047] (3) This invention uses a relational graph convolutional neural network to learn features of the database pattern relational graph. The features of the nodes consist of the data distribution in the table column metadata and statistical information. The relationship of the edges includes rich subordinate relationships and primary and foreign key connections between table columns. The comprehensive features of the data pattern are obtained through relational graph convolution operation.

[0048] (4) This invention integrates query execution graph and database schema information through a database schema awareness fusion method, so that the node features in the query execution graph can adaptively pay attention to the impact of database schema features, and discover key node operations that affect query performance, thereby improving the query performance prediction effect. Attached Figure Description

[0049] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following description is provided with accompanying drawings of the relevant technical solutions in the embodiments of the present invention or the prior art. It should be understood that the accompanying drawings described below are only for the purpose of clearly illustrating 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 effort.

[0050] Figure 1 This is a flowchart of a database schema-aware SQL query performance prediction method according to an embodiment of the present invention;

[0051] Figure 2 This is a model overall framework diagram of the method in the embodiments of the present invention;

[0052] Figure 3 This is a schematic diagram of the query execution graph structure in an embodiment of the present invention;

[0053] Figure 4 This is a schematic diagram of the query execution graph encoder structure in an embodiment of the present invention. Detailed Implementation

[0054] The embodiments of the present invention are described in detail below. Examples of these embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals denote the same or similar elements or elements having the same or similar functions throughout. The embodiments described below with reference to the accompanying drawings are exemplary and are only used to explain the present invention, and should not be construed as limiting the present invention. The step numbers in the following embodiments are set only for ease of explanation, and there is no limitation on the order between the steps. The execution order of each step in the embodiments 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., indicating orientations or positional relationships, are based on the orientations or positional relationships shown in the accompanying drawings. They 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. Therefore, they cannot be understood as limitations on the present invention.

[0056] In the description of this invention, "several" means one or more, "multiple" means two or more, "greater than," "less than," and "exceeding" are understood to exclude the stated number, while "above," "below," and "within" are understood to include the stated number. If "first" or "second" is used, it is only for distinguishing technical features and should not be construed as indicating or implying relative importance, or implicitly indicating the number of indicated technical features, or implicitly indicating the order of the indicated technical features. Furthermore, "and / or" describes the relationship between related objects, indicating that three relationships can exist. For example, A and / or B can represent: A alone, A and B simultaneously, or B alone. The character " / " generally indicates that the preceding and following related objects have an "or" relationship.

[0057] In the description of the present invention, unless otherwise clearly defined, terms such as setting, installing, and connecting 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 database schema-aware SQL query performance prediction method. It 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 relation awareness. It uses query element features to enhance execution plan features, and employs a relational graph convolutional neural network to model database schemas and statistical information. Finally, it generates database schema-aware SQL query features for query performance prediction. The method specifically includes the following steps:

[0059] S1. Obtain the SQL query statement dataset, generate the corresponding execution plan, extract relevant information about the database schema, and preprocess the dataset.

[0060] Specifically, SQL query statements are collected in the database environment, corresponding execution plans are generated, relevant information of the database schema is extracted, effective attributes are extracted from the three types of input data, and the dataset preprocessing is completed.

[0061] As an optional implementation, step S1 specifically includes steps S11-S15:

[0062] S11. Taking the PostgreSQL database as an example, select the publicly available SQL query datasets IMDB and STATS datasets. Perform table creation operations and import the original data according to the database schemas provided by the IMDB and STATS datasets. At the same time, generate the training dataset by randomly selecting query relation tables, join conditions and filtering predicates. The test set uses the default test workload under the two databases.

[0063] S12. In the PostgreSQL database system environment, execute the command EXPLAIN ANALYZE 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 break them down into three main query elements: Relation Table, Join Predicate, and Filter Predicate.

[0065] S14. Collect the execution plan and extract effective attributes for each execution plan node, mainly including information such as operation type, execution attributes, and computational performance. At the same time, extract the query execution time as a prediction label.

[0066] S15. For database schema information, combine the metadata definitions of table columns, such as data types, primary and foreign keys, indexes, and other database definition objects, with statistical information as valid field attributes of tables and columns in the database schema.

[0067] S2. Obtain the initialization feature vector of the SQL query statement, execution plan, and database schema.

[0068] Specifically, a unified semantic feature extraction and encoding module is used to obtain the initial 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. To process the contextual information of short sequences and capture higher-quality features of initial query elements with contextual dependencies, a bidirectional long short-term memory (BiLSTM) network is concatenated after Word2vec generates initial semantic features to extract and fuse comprehensive information from the attribute sequence. The semantic feature extraction module consists of Word2vec combined with BiLSTM. For the three types of query elements, after data preprocessing, they can all be represented as a set of effective attributes. The attribute sequence, as a short text, is processed by 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, and this feature is used to generate the forward propagation feature of each word through BiLSTM. and reverse propagation features Final Feature E seq Forward features of the last word in the sequence The opposite features of the first word It is formed by piecing together.

[0074] S3. Construct a new query execution graph structure using SQL query statements and execution plans, and capture the correlation between SQL query statements and execution plans through an attention mechanism to obtain execution plan features enhanced by query features.

[0075] Specifically, a novel 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, an attention mechanism is used to capture information about the query statement guiding the generation of the execution plan and the computational performance requirements of the execution plan, resulting in execution plan features enhanced by query features.

[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 relation table, join conditions and filter predicates in the query statement are used as three types of query nodes, and a relationship is established with the execution nodes.

[0078] S32. The edges between execution plan nodes represent the direction of execution logic and query data flow, forming 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, the edges are connected in the order of query relation table, join condition, and filter predicate.

[0079] S33. The information that the query element guides in generating the execution plan mainly includes: the query relationship table contains the source of the query to be executed by the operation node, the operation node whose join condition is converted into a Join type, and the operation node whose filter predicate is converted into a Scan type. Therefore, by adding an edge between the query node and the execution node, that is, since the query node guides the generation of the execution plan node, an edge is added between the two types of nodes to form a completely new query execution graph structure.

[0080] S34. The input features of the query execution graph consist of the sum of node semantic features, hierarchical traversal position encoding features, and node type features. The hierarchical traversal position encoding indicates the execution order of nodes that generate the execution plan after query parsing, and the node type is used to distinguish query nodes from execution nodes. The query execution graph encoder consists of multiple layers of networks. The attention in each layer includes relation-aware attention and query-guided attention. After the attention network layer is a feedforward neural network layer, the encoder based on the attention mechanism completes the feature update and learning, generating execution plan features enhanced by 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 node features of the execution graph, the node features consist of the node semantic initialization feature emd(node), the hierarchical traversal position encoding order(node), and the node type encoding type(node). LayerNorm is a normalization layer, Attention is an attention network layer, and FeedForward is a feedforward neural network layer.

[0085] S35, such as Figure 4As shown, the query execution graph encoder uses query-guided attention to capture information about the execution plan generated by the query statement, and uses relation-aware attention to capture the execution logic and computational performance information of the execution plan, resulting in execution plan features enhanced by query features. The query-guided attention mechanism is used to model the information generated by the query node guiding the execution plan node, and an attention mask is used to control the logical guidance relationship between the two types of nodes. The relation-aware attention mechanism is used to control the dependency relationship and information flow direction between execution nodes, ensuring that execution information flows from bottom to top.

[0086]

[0087] Among them, E qp To query the characteristics of execution graph nodes, W Q W K W V These are the query, key, and value weight matrices for the self-attention mechanism in the query execution graph encoder, respectively, with softmax being the activation function. Rel is the scaling factor, which is a relation-aware attention mask used to control the direction of data flow between execution plan nodes, ensuring that the execution processing logic is bottom-up. Guide is a query-guided attention mask, which means that the attention mechanism is only used when there is a mapping relationship between the query node and the execution node, such as a lookup table, join condition, and filter predicate.

[0088] S4. Construct a database schema relationship diagram based on the table column relationships in the database schema, and obtain the comprehensive characteristics of the database schema based on the database schema relationship diagram.

[0089] As an optional implementation, step S4 specifically includes steps S41-43:

[0090] S41. Organize the table column associations of the database schema into a relational graph. The nodes consist of table nodes and column nodes. The edge types are divided into ten types based on the connection nodes, primary and foreign key relationships, and connection direction. These include three types of foreign key associations between tables, three types of foreign key associations between columns that belong to the same table or are associated with foreign keys, two types of primary key associations between tables and columns, and two types of primary key associations between columns and tables.

[0091] S42. The features of database schema nodes are updated using a relational graph convolutional neural network. It takes into account the topological structure of the edges between nodes and the rich information of various edge types. Compared with a simple graph convolutional neural network, the relational graph convolutional neural network not only considers the topological structure of the edges between nodes, but also assigns different weights to the edge types. It is very suitable for application in relational graphs of database schemas with multiple edge types.

[0092]

[0093] in, The features of the graph nodes in the l-th layer of the graph convolution are... The features of the graph nodes in the (l+1)th layer of the graph convolution are... As node n i The set of neighbors, whose size represents the normalization factor, and r represents the relationship type between nodes, for different neighbors n j and node n i The connection relationships are determined using different linear transformation matrices. ,and This is the transformation matrix corresponding to the self-connection of nodes.

[0094] S43. Take the average of the features of all table column nodes convolved in the last layer relationship graph as the comprehensive feature of the database schema information, and use it to model the features of table column connection relationships and implicit data distribution.

[0095] S5. Integrate execution plan features and comprehensive features to capture the degree of attention that nodes in the query execution graph pay to database schema features, and generate feature vectors for SQL queries.

[0096] As an optional implementation, step S5 specifically includes steps S51-52:

[0097] S51. Calculate the correlation score between the features of each node in the query execution graph and the features of the database schema using cosine similarity. This correlation score measures the degree of influence of the database schema on node operations.

[0098]

[0099] Among them, a i Let E be the relevance score of the i-th node. s For database schema relational graph features, 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 then normalized as the weight of the current node.

[0100] S52. By weighting and summing the features of query execution nodes through relevance scores, the features of query execution graph nodes and database schema features are fused together, capturing the nodes' ability to perceive database schema information.

[0101] S6. Use a multi-layer fully connected neural network to establish the relationship between the feature vector of SQL queries and query performance in order to predict 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 model training uses the mean squared error as the loss function. The model continuously adjusts the weight parameters according to the loss function value until the loss function converges, thus completing the model training.

[0105] In summary, the method of this embodiment has the following advantages and beneficial effects compared with the prior art:

[0106] This embodiment addresses the issues of explicitly modeling the relevance between query statements and execution plans, as well as fully utilizing database schema information. It employs a unified semantic feature extraction module to encode SQL queries, execution plans, and database schemas. Furthermore, it designs a novel query execution graph structure that directly establishes connections between query nodes and execution plan nodes. Through query-guided attention and relation-aware attention, it captures information generated by the query-guided execution plan and the execution optimization logic order. A relational graph convolutional neural network is used to learn the connection patterns and statistical information contained in the database schema relational graph. A database schema-aware fusion approach is applied to the node features of the query execution graph to consider the impact of database schema features on query operations, thereby improving the performance prediction effect for complex queries.

[0107] This embodiment also provides a database schema-aware SQL query performance prediction device, 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] This embodiment of the SQL query performance prediction device based on database schema awareness can execute the SQL query performance prediction method based on database schema awareness provided in the method embodiment of the present invention. It can execute any combination of implementation steps of the method embodiment and has the corresponding functions and beneficial effects of the method.

[0112] This application also discloses a computer program product or computer program, which includes computer instructions stored in a computer-readable storage medium. A processor of a computer device can read the computer instructions from the computer-readable storage medium and execute the computer instructions, causing the computer device to perform... 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 the database schema-aware SQL query performance prediction method provided in the method embodiment 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 method has the corresponding functions and beneficial effects.

[0114] In some alternative embodiments, the functions / operations mentioned in the block diagrams may not occur in the order shown in the operation diagrams. For example, depending on the functions / operations involved, two consecutively shown blocks may actually be executed substantially simultaneously, or the blocks may sometimes be executed in reverse order. Furthermore, the embodiments presented and described in the flowcharts of this invention are provided by way of example to provide a more comprehensive understanding of the technology. The disclosed methods are not limited to the operations and logic flows presented herein. Alternative embodiments are contemplated in which the order of various operations is altered and sub-operations described as part of a larger operation are executed independently.

[0115] Furthermore, although the invention has been described in the context of functional modules, it should be understood that, unless otherwise stated, one or more of the described functions and / or features may be integrated into a single physical device and / or software module, or one or more functions and / or features may be implemented in a separate physical device or software module. It is also understood that a detailed discussion of the actual implementation of each module is unnecessary for understanding the invention. Rather, given the properties, functions, and internal relationships of the various functional modules in the apparatus disclosed herein, the actual implementation of the module will be understood within the scope of conventional skill of an engineer. Therefore, those skilled in the art can implement the invention as set forth in the claims using ordinary techniques without excessive experimentation. It is also understood that the specific concepts disclosed are merely illustrative and not intended to limit the scope of the 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 and includes several instructions for enabling a computer device (which can be a personal computer, server, or network device, etc.) to execute all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage medium includes various media that can store program codes, such as a USB flash drive, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, or an optical disk.

[0117] The logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be embodied in any computer-readable medium for use by, or in conjunction with, an instruction execution system, apparatus, or device (such as a computer-based system, a processor-included system, or other system that can fetch and execute instructions from, an instruction execution system, apparatus, or device). For the purposes of this specification, "computer-readable medium" can be any means that can contain, store, communicate, propagate, or transmit programs for use by, or in conjunction with, an instruction execution system, apparatus, or device.

[0118] More specific examples of computer-readable media (a non-exhaustive list) include: electrical connections (electronic devices) having one or more wires, portable computer disk drives (magnetic devices), random access memory (RAM), read-only memory (ROM), erasable and editable read-only memory (EPROM or flash memory), fiber optic devices, and portable optical disc read-only memory (CDROM). Furthermore, computer-readable media can even be paper or other suitable media on which the program can be printed, since the program can be obtained electronically, for example, by optically scanning the paper or other medium, followed by editing, interpreting, or otherwise processing as necessary, and then stored in computer memory.

[0119] It should be understood that various parts of the present invention can be implemented in hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented in software or firmware stored in memory and executed by a suitable instruction execution system. For example, if implemented in hardware, as in another embodiment, it can be implemented using any one or a combination of the following techniques known in the art: discrete logic circuits having logic gates for implementing logical functions on data signals, application-specific integrated circuits (ASICs) having suitable combinational logic gates, programmable gate arrays (PGAs), field-programmable gate arrays (FPGAs), etc.

[0120] In the foregoing description of this specification, references to terms such as "one embodiment," "another embodiment," or "some embodiments" indicate that a specific feature, structure, material, or characteristic described in connection with an embodiment or example is included in at least one embodiment or example of the present invention. In this specification, illustrative expressions of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics described may be combined in any suitable manner in one or more embodiments or examples.

[0121] Although embodiments of the invention have been shown and described, those skilled in the art will understand that various changes, modifications, substitutions and alterations can be made to these embodiments without departing from the principles and spirit of the invention, the scope of which is defined by the claims and their equivalents.

[0122] The above is a detailed description of the preferred embodiments of the present invention. However, the present invention is not limited to the above embodiments. Those skilled in the art can make various equivalent modifications or substitutions without departing from the spirit of the present invention. All such equivalent modifications or substitutions are included within the scope defined by the claims of this application.

Claims

1. A database schema-aware SQL query performance prediction method, characterized in that, Includes the following steps: Obtain the SQL query statement dataset, generate the corresponding execution plan, extract relevant database schema information, and preprocess the dataset; Obtain the initial feature vectors of the SQL query statement, execution plan, and database schema; A novel query execution graph structure is constructed using SQL query statements and execution plans. An attention mechanism is used to capture the correlation between SQL query statements and execution plans, resulting in execution plan features enhanced by query features. Construct a database schema relationship diagram based on the table column relationships in the database schema, and obtain the comprehensive characteristics of the database schema based on the database schema relationship diagram; By fusing execution plan features and comprehensive features, the degree of attention that nodes in the query execution graph pay to database schema features is captured, and feature vectors of SQL queries are generated. Establish the relationship between the feature vectors of SQL queries and query performance in order to predict query execution time.

2. The SQL query performance prediction method based on database schema awareness according to claim 1, characterized in that, The steps of obtaining the SQL query statement dataset, generating the corresponding execution plan, extracting relevant database schema information, and preprocessing the dataset include: The SQL query statement dataset is executed in a real database system environment to generate the execution plan for the corresponding query statement, while collecting data definition and statistical information of the database schema. The query statement is broken down into three query elements: query relationship table, join conditions, and filter conditions; Effective attributes are extracted from the execution plan nodes in the execution plan, and the query execution time is extracted as the prediction label. Use the metadata definitions and statistics of table columns as valid field attributes for tables and columns in the database schema.

3. The SQL query performance prediction method based on database schema awareness according to claim 1, characterized in that, The process of obtaining the initial feature vectors of the SQL query statement, execution plan, and database schema 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 SQL query performance prediction method based on database schema awareness according to claim 3, characterized in that, After Word2vec generates initial semantic features, a bidirectional long short-term memory network (BiLSTM) is concatenated to extract and fuse comprehensive information from the attribute sequence in order to capture higher-quality initial query element features with contextual dependencies.

5. The SQL query performance prediction method based on database schema awareness according to claim 1, characterized in that, The method involves constructing a novel query execution graph structure using SQL query statements and execution plans, capturing the correlation between SQL query statements and execution plans through an attention mechanism, and obtaining execution plan features enhanced by query features, including: Based on the execution nodes of the execution plan, query nodes are introduced, that is, the query relation tables, join conditions and filter predicates in the SQL query statement are treated as three types of query nodes and associated with the execution nodes; Add edges between query nodes and execution nodes to form a new query execution graph structure; the order of the edges follows the logical order of the query expression. By leveraging query-guided attention to capture information about the execution plan generated by the SQL query statement, and by leveraging relation-aware attention to capture the execution logic and computational performance information of the execution plan, we obtain the execution plan features enhanced by query features.

6. The SQL query performance prediction method based on database schema awareness according to claim 5, characterized in that, The process of capturing information about the execution plan generated by SQL query statements using query-guided attention, and capturing the execution logic and computational performance information of the execution plan using relation-aware attention, yields execution plan features enhanced by query features, including: The query-guided attention mechanism is used to model the information generated by the query node to guide the execution plan node, and uses attention masking to control the logical guidance relationship between the two types of nodes; Relationship-aware attention mechanisms are used to control the dependencies and information flow direction between execution nodes to ensure that execution information flows from bottom to top. The query execution graph is updated and learned by an attention-based encoder to generate execution plan features enhanced by query features.

7. The SQL query performance prediction method based on database schema awareness according to claim 1, characterized in that, The process of constructing a database schema relationship diagram based on the table column relationships of the database schema, and obtaining comprehensive characteristics of the database schema based on the database schema relationship diagram, includes: The table columns of the database schema are organized into a relational diagram, which is called the database schema relational diagram; the nodes of the relational diagram consist of table nodes and column nodes. A relational graph convolutional neural network is used to update the features of nodes, taking into account the topological structure of the edges between nodes and the rich information of various edge types. The average value of the features of all table column nodes is taken as the comprehensive feature of the database schema information.

8. The SQL query performance prediction method based on database schema awareness according to claim 1, characterized in that, The process of fusing execution plan features and comprehensive features to capture the degree of attention nodes in the query execution graph pay to database schema features generates a feature vector for the SQL query, including: Calculate the correlation score between the features of each node in the query execution graph and the features of the database schema; Feature fusion is achieved by weighting and summing the features of the query execution nodes using relevance scores.

9. The SQL query performance prediction method based on database schema awareness according to claim 1, characterized in that, The process of establishing the relationship between the feature vector of an SQL query and query performance to predict query execution time includes: A two-layer fully connected neural network is established, with the feature vector of the SQL query as input and the standardized query execution time as the prediction label; The model training uses the mean squared error as the loss function. The model continuously adjusts the weight parameters based on the loss function value until the loss function converges, thus completing the model training.

10. A database schema-aware SQL query performance prediction device, 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 of any one of claims 1-9.

Images