Multi-table connection sequence query optimization method, device and equipment

Abstract

The invention relates to the technical field of database optimization, and discloses a multi-table connection sequence query optimization method, device and equipment, and the method comprises the steps: carrying out the feature extraction of a received database query statement, obtaining a query feature representation, and enabling the database query statement to comprise the data query of a plurality of tables; inputting the query feature representation into a pre-trained target network model, and predicting execution time of different connection sequences in a database query statement to obtain an execution time degree; and on the basis of a Monte Carlo tree, taking the query feature representation and the execution time degree as reward functions, and optimizing the connection sequence of the database query statements to obtain a target connection sequence. According to the method, feature extraction, model prediction and an optimization process based on the Monte Carlo tree are integrated, the execution time and complexity are comprehensively considered, the optimal connection sequence is selected, the limitation that a traditional method is only based on a cost model is overcome, and more accurate connection sequence selection is achieved.

Description

Technical Field

[0001] The present application relates to the technical field of database optimization, and in particular to a method, device and equipment for optimizing multi-table join sequence queries. Background Art

[0002] Traditional query optimizers often select the multi-table join order based on manually designed cost models. Although they choose the join order with the lowest cost, the actual execution time may not be the shortest, and they cannot accurately reflect the query plan execution time, making it difficult to achieve the optimal join order selection. Summary of the Invention

[0003] The main purpose of this application is to provide a multi-table join order query optimization method, device and equipment, aiming to solve the technical problem that traditional query optimizers based on cost models are not accurate enough to predict actual performance and have difficulty in achieving optimal join order selection.

[0004] To achieve the above objectives, the present application proposes a multi-table join order query optimization method, which includes:

[0005] Performing feature extraction on a received database query statement to obtain a query feature representation, wherein the database query statement includes data queries on multiple tables;

[0006] Inputting the query feature representation into the pre-trained target network model, predicting the execution time of different connection orders in the database query statement, and obtaining the execution time degree;

[0007] The query feature representation and the execution time are used as a reward function based on a Monte Carlo tree to optimize the connection sequence of the database query statements to obtain a target connection sequence.

[0008] Optionally, the step of extracting features from the received database query statement to obtain a query feature representation includes:

[0009] Extract target feature information of database query statements;

[0010] Encoding the target feature information to obtain a target feature code, where the target feature code is at least one of an attribute code, a table connection code, and a connection order code;

[0011] A query feature representation of the database query statement is constructed based on the target feature code.

[0012] Optionally, the target feature code is an attribute code, and the step of encoding the target feature information to obtain the target feature code includes:

[0013] Determining a unique value for each attribute in the database query statement based on the attribute information in the target feature information;

[0014] Calculating the selection rate of the attribute according to the unique value;

[0015] The attribute information is encoded using the selection rate as a weight to obtain an attribute code.

[0016] Optionally, the target feature code is a table connection code, and the step of encoding the target feature information to obtain the target feature code includes:

[0017] Constructing an undirected matrix based on the table connection information in the target feature information;

[0018] Compressing the undirected matrix to obtain a first upper triangular matrix;

[0019] A table connection code of the table connection information is determined according to the first upper triangular matrix.

[0020] Optionally, the target feature code is a connection sequence code, and the step of encoding the target feature information to obtain the target feature code includes:

[0021] Performing plane characterization on the connection sequence information in the target feature information to construct a connection matrix;

[0022] Compressing the connection matrix to obtain a second upper triangular matrix;

[0023] A connection order code of the connection order information is determined according to the second upper triangular matrix.

[0024] Optionally, before the step of inputting the query feature representation into a pre-trained target network model and predicting the execution time of different connection orders in the database query statement to obtain the execution time, the method further includes:

[0025] Get historical query codes and actual query time;

[0026] Minimizing the historical query code and the predicted execution time corresponding to the historical query code by using negative log-likelihood loss to construct a loss function;

[0027] The pre-trained multi-classification model is trained based on the loss function, the historical query encoding and the actual query time to obtain a target network model.

[0028] Optionally, the step of optimizing the connection sequence of the database query statements based on a Monte Carlo tree using the query feature representation and the execution time as a reward function to obtain a target connection sequence includes:

[0029] Generate simulated connection orders of different connection modes based on the Monte Carlo tree and the query feature representation;

[0030] determining a target execution time corresponding to the simulated connection sequence from the execution time;

[0031] The target execution time and the query feature representation are used as a reward function, and a target connection sequence with the highest reward value is selected from the simulated connection sequences.

[0032] Optionally, the step of generating simulated connection orders of different connection modes based on the Monte Carlo tree and the query feature representation includes:

[0033] Constructing a root node of a Monte Carlo tree according to the query feature representation, and selecting a leaf node from the root node based on a game tree search algorithm;

[0034] After selecting the leaf node, adding a new child node to the leaf node;

[0035] The child node is used as a starting point for simulation to generate a simulation connection sequence.

[0036] In addition, to achieve the above-mentioned purpose, the present application also proposes a multi-table join order query optimization device, which includes:

[0037] A query representation module is used to extract features from a received database query statement to obtain a query feature representation, wherein the database query statement includes data queries on multiple tables;

[0038] A time prediction module is used to input the query feature representation into the pre-trained target network model to predict the execution time of different connection orders in the database query statement to obtain the execution time degree;

[0039] A sequence optimization module is used to optimize the connection sequence of the database query statements based on a Monte Carlo tree using the query feature representation and the execution time as a reward function to obtain a target connection sequence.

[0040] In addition, to achieve the above-mentioned purpose, the present application also proposes a multi-table join order query optimization device, which includes: a memory, a processor, and a computer program stored on the memory and executable on the processor, wherein the computer program is configured to implement the steps of the multi-table join order query optimization method as described above.

[0041] This application discloses extracting features from a received database query statement to obtain a query feature representation, wherein the database query statement includes data queries for multiple tables; inputting the query feature representation into a pre-trained target network model to predict the execution time of different connection orders in the database query statement to obtain an execution time degree; and optimizing the connection order of the database query statement using the query feature representation and the execution time degree as a reward function based on a Monte Carlo tree to obtain a target connection order. By integrating feature extraction, model prediction, and a Monte Carlo tree-based optimization process, the optimal connection order is selected by comprehensively considering execution time and complexity, overcoming the limitations of traditional methods based solely on cost models and achieving more accurate connection order selection. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0043] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without any creative work.

[0044] Figure 1 This is a flowchart of the first embodiment of the multi-table join order query optimization method of the present application;

[0045] Figure 2 A schematic diagram of the cost time and execution time of the cost model for this application;

[0046] Figure 3 This is a comparison chart of the accuracy and loss value of the prediction model before and after compression for this application;

[0047] Figure 4 This is a framework diagram of the multi-table join sequence query optimization method for this application;

[0048] Figure 5 This is a flow chart of the second embodiment of the multi-table join order query optimization method of the present application;

[0049] Figure 6 A schematic diagram of the connection sequence coding for this application;

[0050] Figure 7 This is a flowchart of the third embodiment of the multi-table join order query optimization method of the present application;

[0051] Figure 8 This is a diagram showing part of the code for query complexity calculation for this application;

[0052] Figure 9 This application shows the query optimization method for multi-table join order and the query scatter plot of AlphaJoin with shorter execution time than PostgreSql;

[0053] Figure 10 This is a schematic diagram of the parallel execution strategy for this application;

[0054] Figure 11 This is a comparison chart of the execution time and search time of the multi-table join order query optimization method and AlphaJoin for this application;

[0055] Figure 12 This is a comparison chart of execution time for different weight parameters of the multi-table join order query optimization method in this application;

[0056] Figure 13 This is a comparison chart of the execution time of the multi-table join order query optimization method for this application, AlphaJoin, and the multi-table join order query optimization method without a penalty mechanism;

[0057] Figure 14 This is a comparison chart of execution time using parallel strategy;

[0058] Figure 15 This is a schematic diagram of the module structure of the multi-table join order query optimization device according to an embodiment of the present application;

[0059] Figure 16 This is a schematic diagram of the device structure of the hardware operating environment involved in the multi-table join order query optimization method in the embodiment of the present application.

[0060] The purpose, features and advantages of this application will be further explained in conjunction with the embodiments and with reference to the accompanying drawings. DETAILED DESCRIPTION

[0061] It should be understood that the specific embodiments described herein are merely used to explain the technical solutions of the present application and are not intended to limit the present application.

[0062] In order to better understand the technical solution of the present application, a detailed description will be given below in conjunction with the accompanying drawings and specific implementation methods.

[0063] The main solution of the embodiment of the present application is: feature extraction is performed on the received database query statement to obtain a query feature representation, wherein the database query statement contains data queries on multiple tables; the query feature representation is input into the pre-trained target network model, and the execution time of different connection orders in the database query statement is predicted to obtain the execution time degree; based on the Monte Carlo tree, the query feature representation and the execution time degree are used as a reward function to optimize the connection order of the database query statement to obtain the target connection order.

[0064] Multi-table join optimization remains a hot research topic in database optimization, and traditional query optimizers often miss the optimal execution plan for multi-table join queries. Determining an effective join order is critical for achieving good performance in database systems. A major challenge in join order selection is enumerating all candidate feasible execution orders and selecting the one with the lowest execution cost. Searching in a larger candidate space increases the likelihood of finding a good plan, but also increases the search cost for query optimization. Existing multi-table join methods include ReJOIN and AlphaJoin. ReJOIN is a proof-of-concept join order enumerator powered entirely by deep reinforcement learning. By learning from previously executed plans, preliminary results show that ReJOIN outperforms PostgreSQL's join enumeration process in terms of effectiveness and efficiency. However, like traditional heuristic methods, ReJOIN relies on a cost-based approach, searching a subspace of all possible join orders and selecting the "cheapest" order based on a cost model constructed from statistical information. This approach is limited by its reliance on a manually designed cost model. AlphaJoin, a join order selection method inspired by AlphaGo, utilizes chessboard coding techniques and Monte Carlo Tree Search (MCTS) to generate join orders. Its core idea is to simulate multiple possible join orders in a tree structure to achieve efficient exploration and learning. However, AlphaJoin fails to consider the potential impact of column selectivity on the efficiency of the join order.

[0065] This application provides a method for optimizing multi-table join order queries using reinforcement learning (ReJos). This method aims to optimize join order selection by jointly considering execution time and query complexity. ReJos introduces a complexity-aware penalty mechanism into the reward function of Monte Carlo Tree Search (MCTS), thereby improving the accuracy of join order selection. Furthermore, we restructure attribute feature encoding, significantly reducing model size and improving search efficiency.

[0066] It should be noted that the execution subject of this embodiment can be a computing service device with data processing, network communication, and program execution functions, such as a computer, or an electronic device capable of performing the above functions. This embodiment and the following embodiments are described below using a database management system as an example.

[0067] Based on this, the embodiment of the present application provides a multi-table join order query optimization method, referring to Figure 1 , Figure 1This is a flow chart of the first embodiment of the multi-table join order query optimization method of this application.

[0068] In this embodiment, the multi-table join order query optimization method includes:

[0069] Step S10: extracting features from the received database query statement to obtain a query feature representation, wherein the database query statement includes data queries on multiple tables.

[0070] It should be noted that the query feature representation is a data structure obtained after processing the database query statement. It integrates the key information in the query statement, such as table connection information and attribute information, and is more suitable for model processing.

[0071] It should be understood that different join orders in database query statements will result in different statement execution times. Multi-table join order refers to selecting the order in which tables are joined to minimize execution costs when executing SQL queries involving multiple tables. Because different join orders can significantly vary the size of intermediate results and execution time, choosing the right join order is crucial for improving query efficiency.

[0072] In one example, reference Figure 2 , Figure 2 This is a diagram showing the cost and execution time of the cost model used in this application. In a multi-table join query scenario, for example, "SELECT * FROM T1,T2,T3,T4 WHERE T1.C1=T2.C1 AND T1.C2=T3.C2 AND T3.C3=T4.C3" is a typical data query involving multiple tables (T1, T2, T3, T4). The join conditions between the tables (such as T1.C1=T2.C1) are specified to retrieve data that meets the conditions. In a cost model that relies on manual design, although the PostgreSQL execution engine selects the lowest-cost join order, its actual execution time may not be the shortest, indicating that the cost model may not truly reflect the execution time of the query plan. The figure shows three different join orders and calculates their cost and execution time. Experiments show that even if some join orders have a lower cost estimate (such as a cost time of 49.29ms), their actual execution time (such as 3.791ms) may be much lower than other plans. This suggests that optimizing based on cost models alone may not be sufficient to accurately predict actual performance.

[0073] Step S20: input the query feature representation into the pre-trained target network model, predict the execution time of different connection orders in the database query statement, and obtain the execution time.

[0074] It should be noted that the target network model refers to a pre-trained neural network model designed to predict the execution time of different join orders in database query statements. By learning from a large amount of query data and its corresponding actual execution time, it establishes a mapping relationship between query features and execution time, thereby enabling prediction of new query feature representations. Execution time is the target network model's prediction of the execution time of different join orders in database query statements, indicating the expected execution time of a query under a specific join order.

[0075] It can be understood that the target network model receives the processed query feature representation as input and outputs the predicted execution time. The hidden layer contains multiple neurons, which are trained on a large amount of historical query data and continuously adjust their own parameters to minimize the error between the predicted execution time and the actual execution time, thereby learning the inherent laws and relationships between query features and execution time.

[0076] Furthermore, in order to more accurately predict the execution time of different connection sequences, before step S20, the following steps may be included:

[0077] Obtain historical query codes and actual query times; construct a loss function by minimizing the historical query codes and the predicted execution times corresponding to the historical query codes through negative log-likelihood loss; train a pre-trained multi-classification model based on the loss function, the historical query codes, and the actual query times to obtain a target network model.

[0078] It should be noted that the historical query code is the code obtained after feature extraction of database query statements that have been executed in the past. It contains key information of the historical query statements, such as the tables involved, the connection relationship between tables, attributes and other features. The actual query time refers to the time spent on the actual execution of the historical query. It is used as the target value for model training and is compared and optimized with the execution time predicted by the model. The negative log-likelihood loss is used to evaluate the gap between the historical query execution time predicted by the model and the actual query time, and the model is optimized by minimizing this loss. A multi-classification model is a model that can handle multiple category classification problems. It is used to predict the category or degree of query execution time under different connection orders.

[0079] It should be understood that by using a multi-classification model for training, the prediction of query execution time can be regarded as a multi-classification problem, where each possible execution time range or degree is regarded as a category.

[0080] It is understandable that when training a multi-classification model, the model computation overhead can be reduced by compressing the input feature encoding, and the model can be made lightweight by reducing the length of the input vector while maintaining the complete expression of the query table connection information.

[0081] In one example, reference Figure 3 , Figure 3 This figure compares the accuracy and loss of the prediction model used in this application before and after compression. The prediction model consists of an input layer, three hidden layers H (1024, 512, and 128 dimensional units), a ReLU activation function, and an output layer. The neural network's goal is to estimate the speed of query plans. The network's input data is the plan encoding of the query representation, and the output is the execution time for multi-label classification. The estimated execution time is categorized into five time scales, 0-4, with increasing execution time. Log-likelihood is used to generate an appropriate probability distribution of execution times. During training, the error between historical query plans and their corresponding predicted execution times is minimized using a negative log-likelihood loss. The upper half of the figure shows the loss over epochs before and after compression. The blue curve represents "ReJos (without compression)," the ReJos method without compression; the red curve represents "ReJos," the ReJos method with compression. It can be seen that at higher rounds (such as more than 40,000 rounds), the loss value tends to stabilize, and the loss values of the two methods are relatively close. The lower half of the figure shows the change of accuracy (Accuracy) with the number of training rounds in the two cases. The green curve represents "ReJos (without compression)" and the yellow curve represents "ReJos". In the early stages of training, the accuracy is low, and as the rounds increase, the accuracy rises rapidly. However, the best results were obtained when the execution efficiency K = 5. As can be seen from the above figure, the accuracy is around 90%. The figure shows that the model can better balance loss and accuracy during the training process, and has a certain degree of reliability and practicality.

[0082] Step S30 , based on a Monte Carlo tree, the query feature representation and the execution time are used as a reward function to optimize the connection sequence of the database query statement to obtain a target connection sequence.

[0083] It's important to note that Monte Carlo trees are used to search and explore possible action sequences in decision-making problems. They are used to search for different join orders for database queries to find the optimal join order. A reward function calculates rewards for different join orders based on query feature representation and execution time, guiding the Monte Carlo tree search towards the optimal join order. The target join order is the optimal database query join order obtained.

[0084] Specifically, the query feature representation is used as the initial state of the Monte Carlo tree to start building the tree structure. Each node represents a possible connection order or part of a connection order, and the root node represents the initial state, that is, the state where no connection operation has been performed. Starting from the root node, nodes are selected downward along the tree according to a specific strategy (such as the UCT algorithm). When a leaf node is reached, new child nodes are added according to the possible connection order to expand the scope of the tree. Starting from the newly expanded node, the remaining connection operations are completed by random simulation until all tables are connected. Based on the simulation results, the reward is propagated from the new node upward to the root node. And based on the quality of the simulation results, the number of visits and cumulative rewards of the nodes along the way are updated. After multiple Monte Carlo tree selection, expansion, simulation and backpropagation operations, the node information and rewards are continuously updated, and finally the target connection order is determined based on the path with the highest cumulative reward.

[0085] In one example, reference Figure 4 , Figure 4 This is the framework diagram of the multi-table join order query optimization method used in this application. ReJos achieves efficient and accurate optimization by combining three modules: query representation, model training, and join order exploration. As shown in the upper left corner of the figure, there is an example query statement: "SELECT * FROM T1,T2,T3,T4 WHERE T1.c1=T2.c1 AND T1.c2=T3.c2 AND T3.c3=T4.c3."

[0086] In the query representation, as shown in the lower left corner of the figure, ReJos performs feature extraction on each incoming query. First, it extracts attribute information from the query, such as attribute c1 in table T1 in the figure, and vectorizes these attribute features. Unlike AlphaJoin's query representation, ReJos incorporates selectivity as a weight into the attribute encoding, more comprehensively reflecting data distribution characteristics. Next, it extracts table join information from the query, such as T1.c1 = T2.c1 and T2.c2 = T3.c2, and encodes it into a two-dimensional matrix. This matrix is then compressed into an upper triangular matrix and further flattened into a one-dimensional vector to form a feature encoding for the table join. Furthermore, the query plan is analyzed using the PostgreSQL EXPLAIN statement to obtain the join relationships between the tables in the query. These join relationships are then vectorized and encoded, and the Flatten function is used to reduce the dimensionality to form a join order encoding. The resulting table join and join order encodings are further compressed, significantly reducing the input parameters required for model training and effectively reducing computational complexity and memory consumption. Finally, by combining table join encoding and attribute encoding, ReJos constructs a complete query feature representation that not only retains key features but also efficiently supports subsequent model training.

[0087] During model training, as shown in the upper right corner of the figure, a deep neural network is used to train a multi-classification model. The model learns the relationship between query encoding and actual query execution time to predict the execution time of different join orders within the same query. The predicted execution time serves as the model's output and is used in the subsequent join order exploration phase to provide a basis for optimizing query execution. The prediction model consists of one input layer, three hidden layers, and one output layer.

[0088] In the join order exploration, as shown in the lower right corner of the figure, ReJos uses Monte Carlo Tree Search (MCTS) to explore the join order in the query. MCTS uses a reward function that combines the query representation and the model's predicted execution time. It simulates and expands the search space through selection, expansion, simulation, and backpropagation. A complexity-based penalty mechanism is introduced during the tree search process, allowing for a comprehensive optimization process that considers both execution time and complexity, ultimately selecting the optimal join order.

[0089] Through the close integration of these three parts, ReJos can efficiently optimize database queries, overcome the limitations of traditional cost-based models, and achieve more accurate and efficient query execution time prediction and optimization.

[0090] In this embodiment, features are extracted from a received database query statement to obtain a query feature representation, where the database query statement includes data queries across multiple tables. The query feature representation is then input into a pre-trained target network model to predict the execution time for different connection orders in the database query statement, obtaining the execution time metrics. The query feature representation and the execution time metrics are then used as reward functions based on a Monte Carlo tree to optimize the connection order of the database query statement, obtaining a target connection order. By integrating feature extraction, model prediction, and Monte Carlo tree-based optimization, the optimal connection order is selected by comprehensively considering execution time and complexity, overcoming the limitations of traditional methods based solely on cost models and achieving more accurate connection order selection.

[0091] Reference Figure 5 , Figure 5 This is a flow chart of the second embodiment of the multi-table join order query optimization method of the present application. Based on the above-mentioned first embodiment, the second embodiment of the multi-table join order query optimization method of the present application is proposed.

[0092] In the second embodiment, step S20 includes:

[0093] Step S201: extract target feature information of the database query statement.

[0094] It is important to understand that target feature information is key information extracted from database query statements that has a significant impact on subsequent query optimization, execution plan generation, and other operations. This information can reflect the characteristics and requirements of the query, such as data table attributes, table connections, and table connection order.

[0095] It should be understood that the target feature information can be determined by extracting the table information, column information, and condition information in the data query statement, such as identifying the table names involved in the query statement, analyzing the connection relationship between the tables, and determining whether it is an inner join (such as the equi-join in the above example), an outer join, or other connection methods; finding the columns involved in the query statement, including the columns appearing in the SELECT clause, WHERE clause, JOIN clause, etc., and analyzing the constraints of the columns; extracting the conditional expression in the WHERE clause, and counting the constant values, function calls, and other information involved in the conditions.

[0096] It is understandable that after extracting the target feature information of the database query statement, the attribute features, table connection information and connection order need to be encoded separately, wherein the attribute features are reconstructed and the representation of the table connection code and the connection order code is optimized.

[0097] Step S202: Encode the target feature information to obtain a target feature code, where the target feature code is at least one of an attribute code, a table connection code, and a connection order code.

[0098] As you can see, encoding attributes not only considers data distribution but also reflects the priority and relevance of each attribute in the query representation. During query optimization, a higher selectivity value indicates a higher priority for that attribute and should be prioritized. This approach not only improves data compression efficiency but also enhances query efficiency.

[0099] It should be understood that table connection coding encodes the connection relationship between tables in the form of a two-dimensional matrix, etc., which can clearly and intuitively show which tables are connected and the conditions for the connection (such as which columns are used for connection). This can help quickly sort out the relationship between the tables and avoid misunderstandings and optimization errors caused by confusing connection relationships.

[0100] As you can understand, the connection order encoding assigns a unique code to each possible connection order, allowing different connection orders to be clearly distinguished and represented. During the optimization process, the connection order encoding can serve as a target and basis for search. The optimization algorithm can select the next connection order encoding to explore based on the current connection order encoding and a specific strategy (e.g., based on execution time prediction, complexity assessment, etc.), thereby gradually finding the optimal connection order.

[0101] Furthermore, in order to reflect the priority and relevance of each attribute, the target feature code is an attribute code, and the step S202 includes:

[0102] The unique value of each attribute in the database query statement is determined based on the attribute information in the target feature information; the selectivity of the attribute is calculated according to the unique value; and the attribute information is encoded using the selectivity as a weight to obtain an attribute code.

[0103] It's important to note that attribute information refers to the columns involved in database query statements, including the column name, column data type, and the table in which the column resides. A unique value refers to a unique data value within a column. Selectivity is a statistic that reflects the distribution of attribute data.

[0104] In one example, to obtain attribute codes, we first count the attributes involved in each query. In PostgreSQL, we can execute the query SELECT n_distinct FROM pg_stats WHERE tablename = 'T1' AND attname = 'c1' to obtain the unique values corresponding to column c1 in table T1. Next, we calculate the selectivity, which is the ratio of the number of unique values to the total number of rows, using the following formula:

[0105]

[0106] Among them, n distinct Indicates the number of unique values in the column in the table, n total Indicates the total number of rows in the table.

[0107] When n distinct ≥0, it means that the column has a positive number of unique values. For example, if n distinct = 10, it means that the column has about 10 different values. distinct When <0, it indicates the ratio of the number of unique values of the field to the total number of rows. For example, if n distinct =-0.5 means that about 50% of the values in this column are unique.

[0108] This featurization approach enables more efficient index selection and execution paths in complex queries, improving overall database performance. For example, in a featurized matrix, a larger value in the second column of the first row indicates that the column has a higher priority in the query; the value in the third column of the second row reflects the selectivity relationship between the column and other columns, further assisting in optimizing query execution plans.

[0109] Furthermore, in order to achieve lightweight model while maintaining complete expression of query table connection information, the target feature encoding is table connection encoding, and the step S202 includes:

[0110] An undirected matrix is constructed based on the table connection information in the target feature information; the undirected matrix is compressed to obtain a first upper triangular matrix; and a table connection code of the table connection information is determined according to the first upper triangular matrix.

[0111] It's understandable that because table joins are symmetrical—that is, if Table A is joined to Table B, then Table B is also necessarily joined to Table A—redundancy can be reduced by retaining only the upper triangular portion. This compression method effectively reduces the matrix dimension and significantly reduces its length. Since most queries typically involve more than 20 tables, the compressed matrix not only saves storage space but also significantly reduces the number of input parameters.

[0112] Specifically, you can use the PostgreSQL EXPLAIN statement to analyze the query plan and identify the joins between the tables in the query. By analyzing these joins, we can map each table in the query into a one-hot encoded vector. These vectors form an undirected matrix, where each element indicates whether a join exists between the two tables. Specifically, if a join exists between Table A and Table B in the query, the corresponding matrix element is 1; otherwise, it is 0. Next, we compress the resulting undirected matrix to convert it into an upper triangular matrix. Finally, we expand the upper triangular matrix into a one-dimensional vector.

[0113] It should be understood that compression can reduce the dimensionality of input data during model training, thereby reducing model complexity and storage requirements. By reducing the length of input vectors, the model becomes lightweight while maintaining the complete representation of query table connection information, effectively improving training efficiency and reducing model computational overhead.

[0114] Furthermore, in order to reduce the computational cost of the subsequent Monte Carlo tree search for the connection order, the target feature encoding is a connection order encoding, and the step S202 includes:

[0115] The connection sequence information in the target feature information is plane-featured to construct a connection matrix; the connection matrix is compressed to obtain a second upper triangular matrix; and a connection sequence code of the connection sequence information is determined according to the second upper triangular matrix.

[0116] It is understandable that the table connection relationship extracted through the query statement can be expressed in a nested bracket form.

[0117] In one example, reference Figure 6 . Figure 6A schematic diagram of the connection sequence coding for this application. Based on the schematic database query statement "SELECT * FROM T1, T2, T3, T4 WHERE T1.c1 = T2.c1 AND T1.c2 = T3.c2 AND T3.c3 = T4.c3", it is prompted in a nested bracket manner, and the connection is made through three connection conditions. The "3" in the second column of the first row first corresponds to the connection predicate connecting T1 and T2. Then, "2" in the second column of the third row corresponds to the result of the connection predicate connection (T1T2) and T3, and "1" in the third column of the fourth row corresponds to the result of the connection predicate connection ((T1T2)T3) and the result of T4. In the initial coding, the values are assigned according to the connection conditions. Because T1 and T2 are connected through column c1, the values 3 are assigned to the (T1, T2) and (T2, T1) positions of the matrix respectively. Similarly, T2 and T3 are connected through column c2, and the values 2 are assigned to the (T2, T3) and (T3, T2) positions respectively. T3 and T4 are connected through column c3, and the values 1 are assigned to the (T3, T4) and (T4, T3) positions respectively. Since there is no direct connection relationship, the other positions are assigned a value of 0. At the same time, by compressing the characterized matrix into an upper triangular matrix, the dimension of the matrix can be greatly reduced without losing the meaning of the connection order. The compression of the upper triangular matrix effectively reduces the computational cost of the subsequent Monte Carlo tree search for the connection order and avoids the processing of redundant data.

[0118] Step S203: constructing a query feature representation of the database query statement based on the target feature code.

[0119] It is understandable that the target feature encoding obtained by merging the table connection encoding and the attribute encoding includes one or more of the attribute encoding, table connection encoding and connection order encoding. These encodings may be different data structures. For example, the attribute encoding may be a series of encoding pairs (encoding value and data type), the table connection encoding may be a two-dimensional matrix or a compressed vector, and the connection order encoding may be a number or vector representing different connection orders. Therefore, it is necessary to merge these different encodings in a specific order to form a complete feature representation. The information in the attribute encoding (such as encoding value and data type information) can be arranged in sequence, and then the elements of the table connection encoding are added to the back, and finally the elements of the connection order encoding are added.

[0120] In this embodiment, target feature information of a database query statement is extracted; the target feature information is encoded to obtain a target feature code, which is at least one of an attribute code, a table join code, and a join order code; and a query feature representation of the database query statement is constructed based on the target feature code. Constructing the query feature representation structures the originally complex information, enabling the model to better capture the essential characteristics of the query, thereby more effectively learning the relationship between different join orders and execution time, and improving the accuracy of the model's predictions.

[0121] Reference Figure 7 , Figure 7 This is a flow chart of the third embodiment of the multi-table join order query optimization method of the present application. Based on the above second embodiment, the third embodiment of the multi-table join order query optimization method of the present application is proposed.

[0122] In the third embodiment, step S30 includes:

[0123] Step S301: Generate simulated connection sequences of different connection modes based on the Monte Carlo tree and the query feature representation.

[0124] It is understandable that when exploring based on Monte Carlo trees, the UCT (Upper Confidence Bound for Trees) algorithm can be used to solve the problem of which tree node should be selected.

[0125] Furthermore, in order to expand the search space and increase the chance of finding a better connection order, step S301 may include:

[0126] A root node of a Monte Carlo tree is constructed according to the query feature representation, and a leaf node is selected from the root node based on a game tree search algorithm; after the leaf node is selected, a new child node is added to the leaf node; and the child node is used as a starting point for simulation to generate a simulated connection sequence.

[0127] It should be understood that in query optimization, MCTS is used to select the optimal join order, combining the statistical properties of tree search with the systematic nature of tree search. MCTS continuously simulates the search space, gradually expanding the decision tree and ultimately selecting the optimal path. The value of each node is evaluated by its success rate across all simulations—that is, the ratio of the number of correct decisions to the total number of simulations.

[0128] In one example, reference Figure 8 , Figure 8 This is a diagram showing part of the code for query complexity calculation in this application.

[0129] In the figure, Algorithm 2Calculate Query Complexity is the query complexity calculation algorithm 2, query_state is the query state, total_number_of_tables indicates the total number of tables, table_size_state is the table size state, mapping_tablecounts is the mapping of table counts, which is a data structure that maps a table identifier (such as an index) to a table count (which can be a measure such as the table size or number of records), and index_to_table_mapping is the mapping of index to table.

[0130] The algorithm flow is:

[0131] 1. Initialize related variables: Find the maximum value from state.board and assign it to max_num.

[0132] 2. Create a collection for storing table lengths: Create an empty collection table_lengths.

[0133] 3. Calculate table count complexity: (max_num-1)*log(max_num) is the complexity calculation based on the number of tables.

[0134] 4. Initialize connection complexity: Initialize the connection complexity connection_complexity to 0.

[0135] 5-11. Traverse the index to calculate the connection complexity and collect table length information: start a loop, traverse the index i from 0 to max_num-2, get the first value first of index i in state.board, get the value index from i+1 to max_num-1 in state.board, and use the get_matrix_indices function to get the matrix index indices according to index and the total number of tables total_number_of_tables. For each pair of indexes index1 and index2 in the obtained matrix index iindices, find the corresponding table through the int_to_table mapping, and then get the counts of these tables from mapping_tablecounts, accumulate their products into connection_complexity, and add the tables corresponding to index1 and index2 to the table_lengths collection.

[0136] 12-15. Calculate the sum of all table lengths: Initialize the table length table_length to 0, iterate over each table in the table_lengths collection, and for each table in the collection, obtain the length of the table from tablecounts and add it to table_length. This gives the sum of all table lengths.

[0137] 16. Calculate the total complexity: Add the calculated table count complexity table_count_complexity, the sum of all table lengths table_length, and the connection complexity connection_complexity to obtain the total complexity of the query total_complexity.

[0138] 17. Return total complexity: return total_complexity returns the calculated total complexity.

[0139] The total complexity is the sum of table count complexity, table length, and join complexity. The reward calculation formula is:

[0140]

[0141] In k-maxindex, k represents the execution efficiency of the query plan. k is a constant that controls the upper limit of the query reward. The smaller the index value maxindex, the higher the query efficiency and the higher the reward value.

[0142] -α·complexity is the complexity penalty term, where α is a weight parameter that controls the degree to which complexity affects the reward value. The larger the value, the stronger the complexity penalty and the faster the reward value decreases.

[0143] The MCTS process can be divided into four steps:

[0144] 1. Selection: Apply the UCT (Upper Confidence Bound for Trees) algorithm to select paths from the root node to leaf nodes, prioritizing nodes with the highest value to balance exploration and utilization. When a leaf node is encountered, the expansion step begins.

[0145] 2. Expansion: Add a new child node at the leaf node to represent a new connection order, expand the width of the search tree, and explore more possible paths.

[0146] 3. Simulation: Starting from the newly expanded node, random simulations are performed until all tables are connected. The simulation results (such as execution time) will be used to evaluate the pros and cons of the connection order.

[0147] 4. Backward propagation: trace back from the new node to the root node, update each node on the path, increase the number of simulations, and adjust the reward based on the simulation results. If the simulation results are good (for example, the execution time is shorter), the node's reward will increase.

[0148] The UCT calculation formula is:

[0149]

[0150] where v i is the current node, v is the parent node, Q(v i ) is the number of times the advantage is obtained at the current node, N(v i ) and N(v) is the total number of visits to the current node (parent node), and C is a parameter to adjust the exploration sensitivity. Taking AlphaGo as an example, the first part of the formula determines the probability of "winning" after selecting this node. The second part of the formula is "exploration". If N(v i ) is large, explore other untouched nodes (excluding v i ) with high probability. Note that the path from each child node in the tree to the root node represents a complete connection sequence.

[0151] Step S302: determining a target execution time corresponding to the simulated connection sequence from the execution time.

[0152] In one example, reference Figure 9 and Figure 10 , Figure 9 This is a scatter plot of the query optimization method for multi-table join order in this application and the query execution time of AlphaJoin is shorter than that of PostgreSql. Figure 10 Schematic diagram of the parallel execution strategy for this application. Figure 9Scatter plot of true entries (Scatter Plot of True Entries), the horizontal axis is Index (index), the vertical axis is Time (time), the red dots represent the scatter points of the true entries corresponding to the ReJos method of this application, the blue dots represent the scatter points of the true entries corresponding to the AlphaJoin method, and the green dots Both True represent the scatter points of the true entries of the query in which both methods are shorter than PostgreSql. Through the scatter plot analysis of complex queries, it is found that the method proposed in this application shows significant advantages in processing complex queries (i.e. queries with longer execution time). However, for some queries with shorter execution time, AlphaJoin may perform better than this application. Specifically, AlphaJoin 2.0 improves query performance by introducing a decision network to dynamically select the optimal connection order between the PostgreSQL native optimizer and AlphaJoin 1.0. However, the training time of the decision network may become a bottleneck, resulting in an increase in the final execution time, especially when processing a large number of queries.

[0153] In order to solve these problems, the overall execution performance can be further improved through parallel execution strategies. Unlike traditional parallelization methods (such as processing more data in parallel or executing more queries at the same time), this application recommends generating multiple execution plans for the same query and executing them in parallel. Once the execution result of a certain execution plan is completed, other plans are immediately terminated and the results are returned to the user. This strategy avoids the potential risks of selecting a single plan and can reduce the impact of decision network training time on overall performance. Figure 10 As shown, a three-step parallelization optimization strategy improves query performance. Specifically, for the same query, the PostgreSQL optimizer, AlphaJoin 1.0, and ReJos generate their own optimal join sequences. Prior to AlphaJoin 1.0 and ReJos, a Monte Carlo tree is used to explore the join sequence. The join sequences generated by these optimizers are then used as independent execution plans and executed simultaneously in a multi-threaded environment. During parallel execution, once a plan completes the query, the execution of the other plans is terminated, and the results of that plan are used as the final output. The resulting result is the minimum of the three methods: Min(PostgreSQL, AlphaJoin 1.0, ReJos).

[0154] Step S303 : Using the target execution time and the query feature representation as a reward function, a target connection sequence with the highest reward value is selected from the simulated connection sequences.

[0155] Understandably, in query optimization problems, the goal is to generate a query plan with the shortest execution time. Therefore, the shorter the execution time, the higher the probability of "winning." In reinforcement learning, a suitable reward function is required to guide the Monte Carlo Tree Search (MCTS) in selecting the optimal join order. However, relying solely on the predicted execution time as the reward function ignores factors such as the properties between tables in the query, the complexity of the join operation, and the table size. This results in the reward function failing to fully reflect the actual execution performance of the query plan. To more accurately evaluate the performance of query plans, a new complexity calculation method is proposed. This method considers the impact of the join order, table length, and inter-table joins, thereby more comprehensively estimating the execution cost of the query plan. First, the join order determines the order in which tables are computed in the query plan. Different join methods and orders can significantly affect the query's computational load and execution efficiency. For example, certain join orders can result in less data transfer and computation, thereby improving query execution efficiency. Second, the length of the table directly determines the complexity of the query computation. Longer tables (i.e., those containing more rows of data) require more computing resources, increasing the overall computational burden of the query. Finally, the join method between tables also has a significant impact on query complexity. In particular, prioritizing joins for long tables can reduce the computational effort of subsequent join operations, leading to a decrease in overall query execution efficiency. Based on these factors, a comprehensive reward function can be designed that uses a weighted approach to account for the impact of table length, join order, and inter-table joins. This weighted approach fully reflects the impact of complexity in the reward function, enabling MCTS to more accurately explore the query plan space and ultimately select the optimal join order, reducing overall query execution time.

[0156] In one example, reference Figure 11 、 Figure 12 、 Figure 13 and Figure 14 , Figure 11 This is a comparison chart of the execution time and search time of the multi-table join order query optimization method and AlphaJoin for this application. Figure 12 This is a comparison chart of the execution time of different weight parameters for the multi-table join sequence query optimization method of this application. Figure 13 This is a comparison chart of the execution time of the multi-table join sequence query optimization method for this application, AlphaJoin, and the multi-table join sequence query optimization method without a penalty mechanism. Figure 14 This is a comparison chart of execution time using a parallel strategy.

[0157] Comparative results can be obtained by conducting experiments on imdb_tiny (PilotScope), an extension of the Join Order Benchmark. The Join Order Benchmark (JOB) is a query workload built on IMDB, a real-world dataset. Unlike synthetic datasets, IMDB is highly skewed and contains numerous correlations, posing significant challenges for query optimization. The IMDB dataset must be loaded into the database and all foreign keys added. JOB consists of 113 queries from 33 different templates, each containing between 4 and 17 relationships. In contrast, imdb_tiny consists of 990 queries, each containing between 4 and 20 relationships. To investigate the performance of ReJos on all imdb_tiny templates, 297 queries were randomly sampled from the test workload in the experiment as the testing workload, and the remaining 693 queries were used as the training workload. These 990 queries were divided into 11 groups of 90 queries each.

[0158] In this experiment, we used the hook mechanism provided by the PostgreSQL pg_hint_plan plugin to set hint flags for queries. By compressing the matrix, we significantly reduced the number of input parameters of the model, thereby effectively reducing the complexity of the model. The experimental database and experimental environment were set up as follows:

[0159] Table 1 Experimental environment

[0160] Configuration parameter Operating System Ubuntu 20.04.6LTS Graphics Processing Unit (GPU) NVIDIA RTX 4060 Programming Language Python 3.12.4 Deep Learning Framework Torch 2.5.1 GPU Acceleration Library CUDA 12.6

[0161] The experimental results show that the size of the compressed model is significantly reduced compared to the original model. The effect of this process is fully reflected in the comparison of the execution time before and after model compression. Figure 11 As shown in the figure, the vertical axis represents execution time, and the horizontal axis represents different groups. Execution Time and Search Time represent the execution time and search time of AlphaJoin; ReJos Execution Time and ReJos Search Time represent the execution time and search time of ReJos. Reducing the model size directly reduces the time overhead of join order search, thereby reducing the overall query execution time cost. This shows that model compression not only optimizes the utilization of storage and computing resources, but also effectively improves query execution efficiency, achieving a lightweight model and showing promising application prospects.

[0162] On the basis of model lightweighting, we further introduced a complexity mechanism into the reward function of Monte Carlo tree search. In the experiment, we set the execution efficiency k=5 and set the weight parameter α to 0.000005, 0.000008, 0.00001, 0.000012 and 0.00005 respectively, and conducted multiple experiments. The experimental results are as follows: Figure 12 As shown in the figure, it can be observed that when α is set to 0.00001, the best effect is achieved. Therefore, the setting of α = 0.00001 is uniformly adopted in subsequent experiments.

[0163] The experiment also compared the group time and search time of ReJos without penalty and AlphaJoin1.0 with ReJos after penalty. Figure 13 As shown in the figure, the horizontal axis represents the different groups, and the vertical axis represents the execution time. The overall execution time has dropped significantly, with the seven ReJos groups having the shortest execution time. However, after taking the search time into account, the ReJos with the added penalty mechanism still has the shortest overall execution time.

[0164] Figure 14 The comparison of parallel execution time and PostgreSQL execution time at different time periods shows that for some fast and complex queries, parallel execution time (Paralleltime) has certain advantages over PostgreSQL. Therefore, it is feasible to consider parallel processing in some long or short query scenarios.

[0165] The experiments verified the practicality and robustness of ReJos on the PilotScope platform and demonstrated its advantages after integration with PostgreSQL. Experimental results show that ReJos outperforms traditional methods in optimizing execution time, especially after the introduction of a complexity penalty mechanism, which significantly reduces the overall execution time. By setting an appropriate α value, we found that the optimal execution time effect occurs when α = 0.00001. In addition, although ReJos and AlphaJoin1.0 have similar execution times for certain join orders, ReJos exhibits the shortest overall execution time after optimizing the search time. Through use, we found that only considering the join order without considering the operators between each two table joins may be the main reason for the small difference in execution time. In future work, it is possible to plan to consider the join operators between the two tables during the encoding process and add more encoding features to further improve the optimization effect.

[0166] In this embodiment, simulated connection sequences with different connection methods are generated based on a Monte Carlo tree and the query feature representation. A target execution time corresponding to the simulated connection sequence is determined from the execution time. The target execution time and the query feature representation are used as a reward function, and the target connection sequence with the highest reward value is selected from the simulated connection sequences. This takes into account the combined influence of execution time and query feature representation. It is possible to find a more optimal execution solution among many possible connection sequences, balance the performance and characteristics of different connection sequences, optimize query execution performance, and improve query efficiency and resource utilization.

[0167] It should be noted that the above examples are only used to understand the present application and do not constitute a limitation on the multi-table join order query optimization method of the present application. More simple transformations based on this technical concept are all within the scope of protection of the present application.

[0168] This application also provides a multi-table join order query optimization device, please refer to Figure 15 , the multi-table join order query optimization device includes:

[0169] A query representation module 10 is configured to extract features from a received database query statement to obtain a query feature representation, wherein the database query statement includes data queries on multiple tables;

[0170] The time prediction module 20 is used to input the query feature representation into the pre-trained target network model to predict the execution time of different connection orders in the database query statement to obtain the execution time degree;

[0171] The sequence optimization module 30 is configured to optimize the connection sequence of the database query statements based on a Monte Carlo tree by using the query feature representation and the execution time as a reward function to obtain a target connection sequence.

[0172] The multi-table join order query optimization device provided in this application, which utilizes the multi-table join order query optimization method in the above-described embodiment, can address the technical problem that traditional cost-model-based query optimizers are unable to accurately predict actual performance and thus struggle to select the optimal join order. Compared to the prior art, the multi-table join order query optimization device provided in this application has the same beneficial effects as the multi-table join order query optimization method provided in the above-described embodiment. Other technical features of the multi-table join order query optimization device are the same as those disclosed in the above-described embodiment and are not further elaborated here.

[0173] The present application provides a multi-table join order query optimization device, which includes: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to execute the multi-table join order query optimization method in the above-mentioned embodiment 1.

[0174] Reference below Figure 16 , which shows a schematic diagram of the structure of a multi-table join order query optimization device suitable for implementing an embodiment of the present application. The multi-table join order query optimization device in the embodiment of the present application can include, but is not limited to, mobile terminals such as mobile phones, laptop computers, digital broadcast receivers, PDAs (Personal Digital Assistants), PADs (Portable Application Descriptions), PMPs (Portable Media Players), in-vehicle terminals (e.g., in-vehicle navigation terminals), and fixed terminals such as digital TVs and desktop computers. Figure 16 The multi-table join order query optimization device shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present application.

[0175] like Figure 16As shown, the multi-table join sequence query optimization device may include a processing device 1001 (e.g., a central processing unit, a graphics processing unit, etc.), which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 1002 or a program loaded from a storage device 1003 into a random access memory (RAM) 1004. Various programs and data required for the operation of the multi-table join sequence query optimization device are also stored in RAM 1004. The processing device 1001, ROM 1002, and RAM 1004 are connected to each other via a bus 1005. An input / output (I / O) interface 1006 is also connected to the bus. Typically, the following systems can be connected to the I / O interface 1006: an input device 1007 including, for example, a touch screen, a touchpad, a keyboard, a mouse, an image sensor, a microphone, an accelerometer, a gyroscope, etc.; an output device 1008 including, for example, a liquid crystal display (LCD), a speaker, a vibrator, etc.; a storage device 1003 including, for example, a magnetic tape, a hard disk, etc.; and a communication device 1009. The communication device 1009 can allow the multi-table join sequence query optimization device to communicate wirelessly or wired with other devices to exchange data. Although the figure shows a multi-table join sequence query optimization device with various systems, it should be understood that it is not required to implement or have all of the systems shown. More or fewer systems can be implemented or have instead.

[0176] In particular, according to the embodiments disclosed in the present application, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, the embodiments disclosed in the present application include a computer program product comprising a computer program carried on a computer-readable medium, the computer program comprising program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from a network via a communication device, or installed from a storage device 1003, or installed from a ROM 1002. When the computer program is executed by the processing device 1001, the above-mentioned functions defined in the method of the embodiment disclosed in the present application are executed.

[0177] The multi-table join order query optimization device provided in this application, which utilizes the multi-table join order query optimization method in the above-described embodiment, can address the technical problem that traditional cost-model-based query optimizers are unable to accurately predict actual performance and thus struggle to select the optimal join order. Compared to the prior art, the multi-table join order query optimization device provided in this application has the same beneficial effects as the multi-table join order query optimization method provided in the above-described embodiment. Other technical features of the multi-table join order query optimization device are the same as those disclosed in the above-described embodiment and are not further elaborated here.

[0178] It should be understood that the various parts disclosed in this application can be implemented using hardware, software, firmware, or a combination thereof. In the description of the above embodiments, specific features, structures, materials, or characteristics can be combined in any one or more embodiments or examples in a suitable manner.

[0179] The above description is merely a specific embodiment of the present application, but the scope of protection of the present application is not limited thereto. Any changes or substitutions that can be easily conceived by a person skilled in the art within the technical scope disclosed in this application should be included in the scope of protection of this application. Therefore, the scope of protection of this application should be based on the scope of protection of the claims.

[0180] The above description is only part of the embodiments of the present application and does not limit the patent scope of the present application. All equivalent structural transformations made by using the contents of the present application specification and drawings under the technical concept of the present application, or direct / indirect application in other related technical fields are included in the patent protection scope of the present application.

Claims

1. A multi-table join order query optimization method, characterized in that: The multi-table join order query optimization method includes: Performing feature extraction on a received database query statement to obtain a query feature representation, wherein the database query statement includes data queries on multiple tables; Inputting the query feature representation into the pre-trained target network model, predicting the execution time of different connection orders in the database query statement, and obtaining the execution time degree; The query feature representation and the execution time are used as a reward function based on a Monte Carlo tree to optimize the connection sequence of the database query statements to obtain a target connection sequence.

2. The multi-table join order query optimization method according to claim 1, characterized in that: The step of extracting features from the received database query statement to obtain a query feature representation includes: Extract target feature information of database query statements; Encoding the target feature information to obtain a target feature code, where the target feature code is at least one of an attribute code, a table connection code, and a connection order code; A query feature representation of the database query statement is constructed based on the target feature code.

3. The multi-table join order query optimization method according to claim 2, characterized in that: The target feature code is an attribute code, and the step of encoding the target feature information to obtain the target feature code includes: Determining a unique value for each attribute in the database query statement based on the attribute information in the target feature information; Calculating the selection rate of the attribute according to the unique value; The attribute information is encoded using the selection rate as a weight to obtain an attribute code.

4. The multi-table join order query optimization method according to claim 2, characterized in that: The target feature code is a table connection code, and the step of encoding the target feature information to obtain the target feature code includes: Constructing an undirected matrix based on the table connection information in the target feature information; Compressing the undirected matrix to obtain a first upper triangular matrix; A table connection code of the table connection information is determined according to the first upper triangular matrix.

5. The multi-table join order query optimization method according to claim 2, characterized in that: The target feature code is a connection sequence code, and the step of encoding the target feature information to obtain the target feature code includes: Performing plane characterization on the connection sequence information in the target feature information to construct a connection matrix; Compressing the connection matrix to obtain a second upper triangular matrix; A connection order code of the connection order information is determined according to the second upper triangular matrix.

6. The multi-table join order query optimization method according to claim 1, characterized in that: Before the step of inputting the query feature representation into the pre-trained target network model and predicting the execution time of different connection orders in the database query statement to obtain the execution time, the method further includes: Get historical query codes and actual query time; Minimizing the historical query code and the predicted execution time corresponding to the historical query code by using negative log-likelihood loss to construct a loss function; The pre-trained multi-classification model is trained based on the loss function, the historical query encoding and the actual query time to obtain a target network model.

7. The multi-table join order query optimization method according to claim 1, characterized in that: The step of optimizing the connection sequence of the database query statements based on the Monte Carlo tree by using the query feature representation and the execution time as a reward function to obtain a target connection sequence includes: Generate simulated connection orders of different connection modes based on the Monte Carlo tree and the query feature representation; determining a target execution time corresponding to the simulated connection sequence from the execution time; The target execution time and the query feature representation are used as a reward function, and a target connection sequence with the highest reward value is selected from the simulated connection sequences.

8. The multi-table join order query optimization method according to claim 7, characterized in that: The step of generating simulated connection sequences of different connection modes based on the Monte Carlo tree and the query feature representation includes: Constructing a root node of a Monte Carlo tree according to the query feature representation, and selecting a leaf node from the root node based on a game tree search algorithm; After selecting the leaf node, adding a new child node to the leaf node; The child node is used as a starting point for simulation to generate a simulation connection sequence.

9. A multi-table join sequence query optimization device, characterized in that: The device comprises: A query representation module is used to extract features from a received database query statement to obtain a query feature representation, wherein the database query statement includes data queries on multiple tables; A time prediction module is used to input the query feature representation into the pre-trained target network model to predict the execution time of different connection orders in the database query statement to obtain the execution time degree; A sequence optimization module is used to optimize the connection sequence of the database query statements based on a Monte Carlo tree using the query feature representation and the execution time as a reward function to obtain a target connection sequence.

10. A multi-table join sequence query optimization device, characterized in that: The device includes: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the computer program is configured to implement the steps of the multi-table join sequence query optimization method according to any one of claims 1 to 8.

Images