Multi-table connection query method, device, equipment and storage medium

Abstract

The present invention belongs to the field of computer technology, and discloses a multi-table connection query method, device, equipment and storage medium. The present invention extracts table connection relations and data filtering conditions in a target multi-table query statement, and constructs a multi-table connection matrix according to the table connection relations; performs connection probability analysis on the multi-table connection matrix through a preset connection strategy model to obtain a multi-table connection vector; constructs a query execution plan according to the multi-table connection vector and the data filtering condition; and performs database query based on the query execution plan. Since the preset connection strategy model can perform probability analysis on the multi-table connection matrix, a table connection sequence with high execution efficiency is selected according to the connection probability, and a multi-table connection vector is generated, there is no need to enumerate the connection space, and then a query execution plan with high execution efficiency can be generated according to the multi-table connection vector for query, which avoids enumerating all connection spaces while also ensuring the execution efficiency of the query execution plan.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a multi-table connection query method, device, equipment and storage medium. Background Art

[0002] As a sub-problem of query optimization, the multi-table join order has an important impact on the efficiency of data queries. The classic join order problem is NP-hard. In order to avoid exhaustively enumerating all possible join orders, most optimizers use heuristic methods to prune the search space. For example, the classic System R system limits its search space to a left-deep tree. Query optimizers sometimes use genetic algorithms or random algorithms to apply heuristic methods to large join queries. In some cases, heuristic methods fail and lead to poor plans. Traditional database management systems use dynamic programming or greedy algorithms to solve this problem. Dynamic programming enumerates all join execution plans. Although it can find the optimal join execution plan, it consumes a lot of performance and is not suitable for queries with a large number of join relations. The greedy algorithm selects the one with the lowest join cost each time. Although it is suitable for situations with a large number of relations, it is very likely to only find a local optimal solution.

[0003] The above contents are only used to assist in understanding the technical solution of the present invention and do not constitute an admission that the above contents are prior art. Summary of the invention

[0004] The main purpose of the present invention is to provide a multi-table connection query method, device, equipment and storage medium, aiming to solve the technical problem that the execution efficiency of the query execution plan cannot be guaranteed when querying multiple tables in the prior art.

[0005] To achieve the above object, the present invention provides a multi-table connection query method, the method comprising the following steps:

[0006] Extracting table connection relationships and data filtering conditions in the target multi-table query statement, and constructing a multi-table connection matrix based on the table connection relationships;

[0007] Performing connection probability analysis on the multi-table connection matrix through a preset connection strategy model to obtain a multi-table connection vector;

[0008] Constructing a query execution plan according to the multi-table connection vector and the data filtering condition;

[0009] A database query is performed based on the query execution plan.

[0010] Optionally, before the step of extracting the table connection relationship and the data filtering condition in the target multi-table query statement and constructing the multi-table connection matrix according to the table connection relationship, the step further includes:

[0011] Traverse the preset training sample set and use the traversed training sample statements as the current query statement;

[0012] Constructing a current connection matrix according to the current query statement;

[0013] Constructing a plurality of execution plans to be verified according to the current connection matrix, and generating a target execution plan based on the initial connection strategy model and the current connection matrix;

[0014] Obtaining the plan execution cost corresponding to each execution plan to be verified and the target execution cost corresponding to the target execution plan;

[0015] Adjusting parameters of the initial connection strategy model according to the planned execution cost and the target execution cost;

[0016] At the end of the traversal, the adjusted initial connection strategy model is used as the preset connection strategy model.

[0017] Optionally, the step of using the adjusted initial connection strategy model as the preset connection strategy model at the end of the traversal includes:

[0018] At the end of the traversal, the adjusted initial connection strategy model is used as an adjusted model to obtain a real execution sample set;

[0019] Traversing the real execution sample set, and taking the traversed real execution samples as current real samples;

[0020] Reading a sample execution plan and a real execution delay corresponding to each sample execution plan from the current real execution sample;

[0021] Analyze the sample execution statements in the current real execution sample by using the one-time adjustment model to obtain a target sample plan;

[0022] Determine the target execution delay corresponding to the target sample plan according to the actual execution delay corresponding to each sample execution plan;

[0023] Performing secondary parameter adjustment on the primary adjustment model based on the actual execution delay and the target execution delay;

[0024] When the traversal of the real execution sample set is finished, the first adjustment model after the second parameter adjustment is used as the preset connection strategy model.

[0025] Optionally, the step of using the first adjustment model after the second parameter adjustment as the preset connection strategy model at the end of the traversal of the real execution sample set includes:

[0026] At the end of the traversal, the first adjustment model after the second parameter adjustment is used as the model to be verified;

[0027] Obtaining a preset test sample set and a standard connection vector set corresponding to the preset test sample set;

[0028] Performing connection probability analysis on each test sample sentence in the preset test sample set by using the model to be verified to obtain a model prediction vector set;

[0029] Generate a model prediction accuracy rate according to the model prediction vector set and the standard connection vector set;

[0030] If the prediction accuracy of the model is greater than or equal to a preset accuracy threshold, the model to be verified is used as a preset connection strategy model.

[0031] Optionally, the step of obtaining the plan execution cost corresponding to each execution plan to be verified and the target execution cost corresponding to the target execution plan includes:

[0032] Analyze each execution plan to be verified through the database optimizer to obtain the execution cost corresponding to each execution plan to be verified;

[0033] Obtaining a target connection vector corresponding to the target execution plan;

[0034] Compare the target connection vector with the multi-table connection vectors corresponding to each execution plan to be verified;

[0035] The execution plan to be verified that has a corresponding multi-table connection vector consistent with the target connection vector is used as the target execution plan to be verified;

[0036] The plan execution cost corresponding to the target execution plan to be verified is used as the target execution cost corresponding to the target execution plan.

[0037] Optionally, the step of adjusting parameters of the initial connection strategy model according to the planned execution cost and the target execution cost includes:

[0038] Obtaining the minimum value of the execution cost of the plan to obtain the minimum execution cost;

[0039] comparing the minimum execution cost with the target execution cost;

[0040] If the target execution cost is greater than the minimum execution cost, the parameters of the initial connection strategy model are adjusted according to the planned execution cost and the target execution cost.

[0041] Optionally, the step of performing a database query based on the query execution plan includes:

[0042] Extracting result sorting conditions from the target multi-table query statement;

[0043] Perform a database query based on the query execution plan and the result sorting condition to obtain a data query result;

[0044] The data query results are displayed.

[0045] In addition, to achieve the above purpose, the present invention also proposes a multi-table connection query device, which includes the following modules:

[0046] An information extraction module is used to extract table connection relationships and data filtering conditions in a target multi-table query statement, and to construct a multi-table connection matrix based on the table connection relationships;

[0047] A probability analysis module, used to perform connection probability analysis on the multi-table connection matrix through a preset connection strategy model to obtain a multi-table connection vector;

[0048] A plan building module, used to build a query execution plan according to the multi-table connection vector and the data filtering condition;

[0049] A data query module is used to perform database query based on the query execution plan.

[0050] In addition, to achieve the above-mentioned purpose, the present invention also proposes a multi-table connection query device, which includes: a processor, a memory, and a multi-table connection query program stored in the memory and executable on the processor, and the multi-table connection query program implements the steps of the multi-table connection query method described above when executed by the processor.

[0051] In addition, to achieve the above purpose, the present invention also proposes a computer-readable storage medium, on which a multi-table connection query program is stored, and when the multi-table connection query program is executed, the steps of the multi-table connection query method described above are implemented.

[0052] The present invention extracts the table connection relationship and data filtering conditions in the target multi-table query statement, and constructs a multi-table connection matrix according to the table connection relationship; performs connection probability analysis on the multi-table connection matrix through a preset connection strategy model to obtain a multi-table connection vector; constructs a query execution plan according to the multi-table connection vector and the data filtering conditions; and performs database query based on the query execution plan. Since the preset connection strategy model can perform probability analysis on the multi-table connection matrix, a table connection sequence with high execution efficiency is selected according to the connection probability, and a multi-table connection vector is generated, it is not necessary to enumerate the connection space, and then a query execution plan with high execution efficiency can be generated according to the multi-table connection vector for query, which avoids enumerating all connection spaces while ensuring the execution efficiency of the query execution plan. BRIEF DESCRIPTION OF THE DRAWINGS

[0053] Figure 1 It is a schematic diagram of the structure of an electronic device in a hardware operating environment involved in an embodiment of the present invention;

[0054] Figure 2 It is a flowchart of the first embodiment of the multi-table connection query method of the present invention;

[0055] Figure 3 It is a flowchart of a second embodiment of a multi-table connection query method of the present invention;

[0056] Figure 4 This is a structural block diagram of the first embodiment of the multi-table connection query device of the present invention.

[0057] The realization of the purpose, functional features and advantages of the present invention will be further explained in conjunction with embodiments and with reference to the accompanying drawings. DETAILED DESCRIPTION

[0058] It should be understood that the specific embodiments described herein are only used to explain the present invention, and are not used to limit the present invention.

[0059] Reference Figure 1 , Figure 1 The present invention is a schematic diagram of a multi-table connection query device structure in a hardware operating environment according to an embodiment of the present invention.

[0060] like Figure 1As shown, the electronic device may include: a processor 1001, such as a central processing unit (CPU), a communication bus 1002, a user interface 1003, a network interface 1004, and a memory 1005. Among them, the communication bus 1002 is used to realize the connection and communication between these components. The user interface 1003 may include a display screen (Display), an input unit such as a keyboard (Keyboard), and the optional user interface 1003 may also include a standard wired interface and a wireless interface. The network interface 1004 may optionally include a standard wired interface and a wireless interface (such as a wireless fidelity (Wireless-Fidelity, WI-FI) interface). The memory 1005 may be a high-speed random access memory (Random Access Memory, RAM), or a stable non-volatile memory (Non-Volatile Memory, NVM), such as a disk storage. The memory 1005 may also be a storage device independent of the aforementioned processor 1001.

[0061] Those skilled in the art will understand that Figure 1 The structure shown in the figure does not constitute a limitation on the electronic device, and may include more or less components than shown in the figure, or combine certain components, or arrange the components differently.

[0062] like Figure 1 As shown, the memory 1005 as a storage medium may include an operating system, a network communication module, a user interface module, and a multi-table connection query program.

[0063] exist Figure 1 In the electronic device shown, the network interface 1004 is mainly used for data communication with the network server; the user interface 1003 is mainly used for data interaction with the user; the processor 1001 and the memory 1005 in the electronic device of the present invention can be set in a multi-table connection query device, and the electronic device calls the multi-table connection query program stored in the memory 1005 through the processor 1001, and executes the multi-table connection query method provided by the embodiment of the present invention.

[0064] The embodiment of the present invention provides a multi-table connection query method, referring to Figure 2 , Figure 2 The figure is a flow chart of a first embodiment of a multi-table connection query method of the present invention.

[0065] In this embodiment, the multi-table connection query method includes the following steps:

[0066] Step S10: extracting table connection relationships and data filtering conditions in the target multi-table query statement, and constructing a multi-table connection matrix according to the table connection relationships.

[0067] It should be noted that the executor of this embodiment can be the multi-table connection query device, and the multi-table connection query device can be an electronic device such as a personal computer, a server, or other devices that can achieve the same or similar functions. This embodiment does not limit this. In this embodiment and the following embodiments, the multi-table connection query method of the present invention is described by taking the multi-table connection query device as an example.

[0068] It should be noted that the target multi-table query statement may be an SQL statement that requires a multi-table join query, and the target multi-table query statement may be input by a user on the multi-table join query device, or may be sent to the multi-table join query device by other devices. Among them, the data filtering condition may be a condition for filtering the queried data.

[0069] In actual use, extracting the table connection relationship and data filtering conditions in the target multi-table query statement can be done by extracting the database table names and statement query conditions involved from the target multi-table query statement, dividing the statement query conditions into table association conditions and data filtering conditions, and then combining the database table names and table association conditions into a table connection relationship. The table association condition can be a condition for establishing an association relationship between two data tables.

[0070] In a specific implementation, when the condition connector in the statement query condition is an equality symbol and there are attributes of different data tables at both ends, the statement query condition can be determined to be a table association condition; in other cases, the statement query condition can be determined to be a data filtering condition.

[0071] For example: Assume that there are three query conditions in the statement, namely "A.id=B.id", "B.id=C.id" and "B.a2>100", among which "A.id=B.id", "B.id=C.id" are table association conditions, and "B.a2>100" is the data filtering condition.

[0072] It should be noted that the multi-table link matrix can be a matrix used to represent the association relationship between tables, wherein the multi-table link matrix is ​​an n*n adjacency matrix, n is the total number of database tables involved in the target multi-table query statement, and if any matrix value in the multi-table link matrix is ​​1, it means that there is a connection relationship between the two data tables corresponding to the horizontal and vertical coordinates of the matrix value.

[0073] For example, assuming that the target multi-table query statement is "SELECT * FROM A, B, C, D WHERE A.id = B.id AND B.id = C.id AND C.id = D.id AND B.a2>100", the database tables extracted at this time are A, B, C, and D, and the table association conditions are "A.id = B.id", "B.id = C.id", and "C.id = D.id". The data filtering condition is "B.a2>100", then the multi-table link matrix constructed at this time is:

[0074]

[0075] In actual use, in order to facilitate data processing, the fields with data filtering conditions can be identified in the form of vectors. For example, assuming that the data filtering condition is "B.a2>100", the vector can be represented as:

[0076]

[0077] Among them, the vector value corresponding to B.a2 is 1, which means that the B.a2 field has a data filtering condition.

[0078] Step S20: performing connection probability analysis on the multi-table connection matrix using a preset connection strategy model to obtain a multi-table connection vector.

[0079] It should be noted that the preset connection strategy model can be a model obtained after pre-training the initial connection strategy model. The preset connection strategy model can analyze the connection probability of each table. The higher the connection probability, the higher the efficiency of executing the associated query between the two tables. Among them, the initial connection strategy model can be a policy-based deep reinforcement learning algorithm model.

[0080] In actual use, the connection probability analysis of the multi-table connection matrix is ​​performed through the preset connection strategy model, and the multi-table connection vector is obtained by inputting the multi-table connection matrix into the preset connection strategy model. The preset connection strategy model will perform connection probability analysis when receiving the multi-table connection matrix, analyze the connection probability between each table, and connect the two tables with the highest connection probability, and repeat this multiple times to generate a multi-table connection vector. The multi-table connection vector is used to distinguish the left and right relationships of the table connection and the depth of the connection tree. In order to facilitate the distinction between the left and right connection relationships, the vector value corresponding to the left branch of the connection tree is 1, and the vector value corresponding to the right branch is 2.

[0081] For example: Assuming that the multi-table connection matrix is ​​the same as the multi-table connection matrix in the above example, the steps for generating the multi-table connection vector are divided into 4 steps: a, b, c, and d:

[0082] a. In the initial state, there is no table involved in the connection, so the vector at this time is [000 000 000 000];

[0083] b. Assume that in the first connection probability analysis, the connection probability of table A and table B is the highest. At this time, table A is on the left side of the connection tree, and table B is on the right side of the connection tree. The vector obtained after encoding the connection result is [001 002 000 000];

[0084] c. Assume that in the next connection probability analysis, the intermediate result (A∞B) obtained by connecting table A and table B has the highest connection probability, and is connected to table C as the left subtree. The encoding vector obtained by this connection is [011 012 002 000];

[0085] d. The last connection probability analysis shows that the connection with the highest probability is to connect the query subtree as the left subtree with the D table. The resulting encoding vector is [111 112 012 002]. The final multi-table connection vector is [111112 012 002].

[0086] Step S30: constructing a query execution plan according to the multi-table join vector and the data filtering condition.

[0087] It can be understood that the multi-table connection vector can be used to distinguish the left and right relationships of table connections and the depth of the connection tree. The order of querying each database table can be determined based on the multi-table connection vector, and the data filtering condition can be used to filter the data. Therefore, when querying each database table in sequence based on the multi-table connection vector, the data filtering condition corresponding to the database table can be used to reduce the amount of data to be processed, thereby improving the query execution efficiency.

[0088] For example: Assume that the multi-table join vector is [111 112 012 002], and the data filtering condition is B.a2>100. The query execution plan generated at this time is: first filter the data in table B through the data filtering condition to obtain the filtered intermediate data B1, and then connect table A with table B1. Table A is on the left side of the join tree, and table B1 is on the right side of the join tree. Then, the intermediate result (A∞B1) obtained by connecting table A with table B1 is connected to table C as the left subtree, and then the intermediate result (A∞B1) obtained by connecting table C with the intermediate result ((A∞B1)∞C) as the left subtree is connected to table D, and finally the query result (((A∞B1)∞C)∞D) is obtained.

[0089] Step S40: performing a database query based on the query execution plan.

[0090] It can be understood that since the preset link strategy model can analyze the connection probability of each table, the higher the connection probability, the higher the execution efficiency of the associated query between the two tables. A query execution plan is constructed according to the multi-table connection vector and data filtering conditions output by the preset link strategy model, and then a database query is performed according to the query execution plan, which can ensure that the execution efficiency of the query execution plan is the highest.

[0091] In actual use, performing a database query based on a query execution plan may be sending the query execution plan to a database management system, which then executes the data query.

[0092] Furthermore, when setting a multi-table join query statement, the user may also set a special sorting condition. To adapt to this implementation scenario, step S40 in this embodiment may include:

[0093] Extracting result sorting conditions from the target multi-table query statement;

[0094] Perform a database query based on the query execution plan and the result sorting condition to obtain a data query result;

[0095] The data query results are displayed.

[0096] It should be noted that the result sorting condition may be a sorting condition for sorting the query results in the target multi-table query statement. Performing a database query based on the query execution plan and the result sorting condition to obtain the data query result may be to send the query execution plan and the result sorting condition to a database management system, which performs data query and sorting based on the query execution plan and the result sorting condition, and then feeds back the sorted data query result to the multi-table connection query device.

[0097] In actual use, the data query results can be displayed on a display device of a multi-table connection query device, or sent to a device that initiates a target multi-table query statement for display, which is not limited in this embodiment.

[0098] This embodiment extracts the table connection relationship and data filtering conditions in the target multi-table query statement, and constructs a multi-table connection matrix according to the table connection relationship; performs connection probability analysis on the multi-table connection matrix through a preset connection strategy model to obtain a multi-table connection vector; constructs a query execution plan according to the multi-table connection vector and the data filtering conditions; and performs a database query based on the query execution plan. Since the preset connection strategy model can perform probability analysis on the multi-table connection matrix, a table connection sequence with high execution efficiency is selected according to the connection probability, and a multi-table connection vector is generated, there is no need to enumerate the connection space. After that, a query execution plan with high execution efficiency can be generated according to the multi-table connection vector for query, which avoids enumerating all connection spaces while ensuring the execution efficiency of the query execution plan.

[0099] refer to Figure 3 , Figure 3 The figure is a flow chart of a second embodiment of a multi-table connection query method of the present invention.

[0100] Based on the first embodiment described above, the multi-table connection query method of this embodiment further includes, before step S10:

[0101] Step S01: traverse a preset training sample set, and use the traversed training sample sentences as the current query sentence.

[0102] It should be noted that the preset training sample set may be a set containing a large number of training sample statements, wherein the training sample statements may be multi-table connection query statements. The preset training sample set may be pre-set by the administrator of the multi-table connection query device.

[0103] Step S02: constructing a current connection matrix according to the current query statement.

[0104] It should be noted that constructing the current connection matrix according to the current query statement may be to extract table connection relationships and data filtering conditions from the current query statement, then construct a multi-table connection matrix according to the table connection relationships, and use the constructed multi-table connection matrix as the current connection matrix.

[0105] Step S03: construct multiple execution plans to be verified according to the current connection matrix, and generate a target execution plan based on the initial connection strategy model and the current connection matrix.

[0106] It should be noted that constructing multiple execution plans to be verified based on the current connection matrix can be based on the connection possibilities of each table in the current connection matrix, thereby generating multiple different multi-table connection vectors, and then generating multiple different query execution plans based on each multi-table connection vector and the previously extracted data filtering conditions, and then using the generated query execution plan as the execution plan to be verified.

[0107] Generating a target execution plan based on the initial connection strategy model and the current connection matrix can be to perform connection probability analysis on the current connection matrix through the initial connection model strategy, and then construct a query execution plan according to the multi-table connection vector generated after the analysis and the data filtering conditions extracted previously, and then use the constructed query execution plan as the target execution plan.

[0108] Step S04: Obtain the plan execution cost corresponding to each execution plan to be verified and the target execution cost corresponding to the target execution plan.

[0109] It should be noted that the plan execution cost may be the execution delay when executing the data query plan, that is, the time required to query according to the data query plan. Obtaining the plan execution cost corresponding to each execution plan to be verified may be to analyze each execution plan to be verified, thereby determining the plan execution cost when each execution plan to be verified is executed. Obtaining the target execution cost corresponding to the target execution plan may be to use the plan execution cost when executing the target execution plan as the target execution cost.

[0110] Furthermore, since directly executing the query execution plan to be tested in the database will cause great pressure on the database and require a long wait time, and the database optimizer can estimate the plan execution cost required for the query execution plan, therefore, each query execution plan can be estimated by the database optimizer to obtain the plan execution cost of each query execution plan. At this time, step S04 in this embodiment may include:

[0111] Analyze each execution plan to be verified through the database optimizer to obtain the execution cost corresponding to each execution plan to be verified;

[0112] Obtaining a target connection vector corresponding to the target execution plan;

[0113] Compare the target connection vector with the multi-table connection vectors corresponding to each execution plan to be verified;

[0114] The execution plan to be verified that has a corresponding multi-table connection vector consistent with the target connection vector is used as the target execution plan to be verified;

[0115] The plan execution cost corresponding to the target execution plan to be verified is used as the target execution cost corresponding to the target execution plan.

[0116] It should be noted that analyzing each execution plan to be verified by a database optimizer and obtaining the plan execution cost corresponding to each execution plan to be verified can be performed by predicting the cost of each execution plan to be verified by a database optimizer, thereby obtaining the plan execution cost corresponding to each execution plan to be verified.

[0117] It should be noted that, since the connection possibility of each table in the current connection matrix is ​​taken into consideration when generating the execution plan to be verified, there must be an execution plan to be verified that is the same as the target execution plan. At this time, in order to avoid unnecessary performance overhead, the target execution plan can no longer be analyzed by the database optimizer, but the plan execution cost corresponding to the execution plan to be verified that is the same as the target execution plan can be directly used as the target execution cost. Obtaining the target connection vector corresponding to the target execution plan can be to use the multi-table connection vector corresponding to the target execution plan as the target connection vector.

[0118] It can be understood that the multi-table join vector can be used to distinguish the left and right relationships of table joins and the depth of the join tree. Obviously, the multi-table join vectors corresponding to different query execution plans must be different. Therefore, by comparing the multi-table join vectors corresponding to each execution plan to be verified with the target join vector, the execution plan to be verified that is the same as the target execution plan can be found, and the found execution plan to be verified is used as the target execution plan to be verified, and then the plan execution cost corresponding to the target execution plan to be verified is used as the target execution cost corresponding to the target execution plan.

[0119] Step S05: adjusting parameters of the initial connection strategy model according to the planned execution cost and the target execution cost.

[0120] It can be understood that, based on the plan execution cost and the target execution cost, it can be confirmed whether the target execution plan is the query execution plan with the best execution efficiency. If not, it means that the initial connection strategy model prediction is wrong. At this time, the parameters of the initial connection strategy model can be adjusted.

[0121] In actual use, when adjusting the parameters of the initial connection strategy model, the gradient of the reward is used to search for the optimal strategy parameters, and then the searched strategy parameters are replaced into the initial connection strategy model, thereby achieving parameter adjustment of the initial connection strategy model.

[0122] In a specific implementation, in order to accurately determine whether the target execution plan is a query execution plan with the best execution efficiency, step S05 in this embodiment may include:

[0123] Obtaining the minimum value of the execution cost of the plan to obtain the minimum execution cost;

[0124] comparing the minimum execution cost with the target execution cost;

[0125] If the target execution cost is greater than the minimum execution cost, the parameters of the initial connection strategy model are adjusted according to the planned execution cost and the target execution cost.

[0126] It should be noted that the plan execution cost is the execution delay when the query execution plan is executed. The smaller the plan execution cost, the higher the execution efficiency of the query execution plan. Therefore, the minimum value of the plan execution cost corresponding to the execution plan to be verified is obtained, and the minimum value is used as the minimum execution cost. Then, the target execution cost is compared with the minimum execution cost to accurately and quickly determine whether the target execution plan is the query execution plan with the best execution efficiency. Obviously, if the target execution cost is greater than the minimum execution cost, it means that the target execution plan is not the query execution plan with the best execution efficiency. It can be determined that the prediction of the initial connection strategy model is wrong. Therefore, the parameters of the initial connection strategy model can be adjusted.

[0127] Step S06: When the traversal is finished, the adjusted initial connection strategy model is used as the preset connection strategy model.

[0128] It is understandable that if the traversal is completed, it means that the training of the initial connection strategy model has been completed. Therefore, at this time, the adjusted initial connection strategy model can be directly used as the preset connection strategy model.

[0129] Furthermore, although it is easy to determine the query execution plan according to the database optimizer, the estimation of the database optimizer may be inaccurate, which may cause inaccurate prediction of the preset connection strategy model obtained after training. In order to overcome this defect, step S06 in this embodiment may include:

[0130] At the end of the traversal, the adjusted initial connection strategy model is used as an adjusted model to obtain a real execution sample set;

[0131] Traversing the real execution sample set, and taking the traversed real execution samples as current real samples;

[0132] Reading a sample execution plan and a real execution delay corresponding to each sample execution plan from the current real execution sample;

[0133] Analyze the sample execution statements in the current real execution sample by using the one-time adjustment model to obtain a target sample plan;

[0134] Determine the target execution delay corresponding to the target sample plan according to the actual execution delay corresponding to each sample execution plan;

[0135] Performing secondary parameter adjustment on the primary adjustment model based on the actual execution delay and the target execution delay;

[0136] When the traversal of the real execution sample set is finished, the first adjustment model after the second parameter adjustment is used as the preset connection strategy model.

[0137] It should be noted that the real execution sample set can be a set composed of multiple real execution samples, and the real execution sample set can be pre-set by the administrator of the multi-table connection query device. The real execution sample includes a sample execution statement, multiple sample execution plans, and the real execution delay corresponding to each sample execution plan, wherein the sample execution statement is a multi-table connection query statement, and the multiple sample execution plans are constructed based on all connection possibilities of each table in the sample execution statement. The real execution delay is the execution time fed back by the database after the sample execution plan is directly executed in the database.

[0138] In actual use, the sample execution statements in the current real execution sample are analyzed through the one-time adjustment model, and the target sample plan can be obtained by generating a multi-table connection matrix according to the table connection relationship from the sample execution statement, and then performing connection probability analysis on the multi-table connection matrix through the one-time adjustment model, and then constructing a query execution plan according to the multi-table connection vector and data filtering conditions generated after the analysis, and then using the constructed query execution plan as the target sample plan.

[0139] It can be understood that since the sample execution plan is generated by taking into account the possibility of connecting the tables involved in the sample execution statement, there must be a sample execution plan that is the same as the target sample plan. At this time, determining the target execution delay corresponding to the target sample plan based on the actual execution delay corresponding to each sample execution plan can be to use the actual execution delay of the sample execution plan that is the same as the target sample plan as the target execution delay corresponding to the target sample plan.

[0140] In a specific implementation, performing a secondary parameter adjustment on the primary adjustment model based on the actual execution delay and the target execution delay can be to determine whether the target sample plan is the query execution plan with the highest execution efficiency based on the actual execution delay and the target execution delay, thereby further adjusting the parameters of the primary adjustment model.

[0141] Furthermore, since the number of training sets cannot be guaranteed, when the number of samples in the training set is insufficient, the prediction accuracy of the preset connection strategy model finally obtained may be low. At this time, if the model is directly put into actual use, the effect may be poor, and even cause the business system to freeze. In order to avoid this phenomenon, the step of using the first adjustment model after the second parameter adjustment as the preset connection strategy model at the end of the traversal of the real execution sample set in this embodiment may include:

[0142] At the end of the traversal, the first adjustment model after the second parameter adjustment is used as the model to be verified;

[0143] Obtaining a preset test sample set and a standard connection vector set corresponding to the preset test sample set;

[0144] Performing connection probability analysis on each test sample sentence in the preset test sample set by using the model to be verified to obtain a model prediction vector set;

[0145] Generate a model prediction accuracy rate according to the model prediction vector set and the standard connection vector set;

[0146] If the prediction accuracy of the model is greater than or equal to a preset accuracy threshold, the model to be verified is used as a preset connection strategy model.

[0147] It should be noted that the preset test sample set may be a set composed of a large number of test sample statements, and the test sample statements are multi-table join query statements. The standard join vector set corresponding to the preset test sample set includes the standard join vectors corresponding to each test sample statement in the preset test sample set, wherein the standard join vector may be the multi-table join vector of the query execution plan with the highest execution efficiency among the multiple query execution plans corresponding to the test sample statements.

[0148] In actual use, generating the model prediction accuracy based on the model prediction vector set and the standard connection vector set can be to determine the total number of vectors in the standard connection vector set, and then divide the number of vectors that are the same as those in the model prediction vector set and the standard connection vector set by the total number of vectors to obtain the model prediction accuracy.

[0149] It should be noted that the preset accuracy threshold can be set by the administrator of the multi-table connection query device according to actual needs, for example, the preset accuracy threshold is set to 95%.

[0150] It is understandable that if the model prediction accuracy is greater than or equal to the preset accuracy threshold, it means that the prediction accuracy of the model to be verified has met the requirements and the model to be verified can be put into practical use. Therefore, the model to be verified can be used as the preset connection strategy model.

[0151] This embodiment traverses the preset training sample set, and uses the traversed training sample statements as the current query statement; constructs the current connection matrix according to the current query statement; constructs multiple execution plans to be verified according to the current connection matrix, and generates a target execution plan based on the initial connection strategy model and the current connection matrix; obtains the plan execution cost corresponding to each execution plan to be verified and the target execution cost corresponding to the target execution plan; adjusts the parameters of the initial connection strategy model according to the plan execution cost and the target execution cost; at the end of the traversal, the adjusted initial connection strategy model is used as the preset connection strategy model. Since the preset training sample set containing a large number of training sample statements is used in advance for training, and the parameters of the initial connection strategy model are continuously adjusted during the training process, the parameters in the initial connection strategy model are continuously optimized, so that the preset connection strategy model finally obtained has a very high prediction accuracy, ensuring the reliability of the multi-table connection query method.

[0152] In addition, an embodiment of the present invention further provides a storage medium, on which a multi-table connection query program is stored. When the multi-table connection query program is executed by a processor, the steps of the multi-table connection query method described above are implemented.

[0153] Reference Figure 4 , Figure 4 This is a structural block diagram of the first embodiment of the multi-table connection query device of the present invention.

[0154] like Figure 4 As shown, the multi-table connection query device proposed in the embodiment of the present invention includes:

[0155] The information extraction module 10 is used to extract the table connection relationship and data filtering conditions in the target multi-table query statement, and construct a multi-table connection matrix according to the table connection relationship;

[0156] A probability analysis module 20, configured to perform connection probability analysis on the multi-table connection matrix through a preset connection strategy model to obtain a multi-table connection vector;

[0157] A plan building module 30, used to build a query execution plan according to the multi-table connection vector and the data filtering condition;

[0158] The data query module 40 is used to perform database query based on the query execution plan.

[0159] This embodiment extracts the table connection relationship and data filtering conditions in the target multi-table query statement, and constructs a multi-table connection matrix according to the table connection relationship; performs connection probability analysis on the multi-table connection matrix through a preset connection strategy model to obtain a multi-table connection vector; constructs a query execution plan according to the multi-table connection vector and the data filtering conditions; and performs a database query based on the query execution plan. Since the preset connection strategy model can perform probability analysis on the multi-table connection matrix, a table connection sequence with high execution efficiency is selected according to the connection probability, and a multi-table connection vector is generated, there is no need to enumerate the connection space. After that, a query execution plan with high execution efficiency can be generated according to the multi-table connection vector for query, which avoids enumerating all connection spaces while ensuring the execution efficiency of the query execution plan.

[0160] Furthermore, the information extraction module 10 is also used to traverse a preset training sample set, and use the traversed training sample statements as current query statements; construct a current connection matrix based on the current query statement; construct multiple execution plans to be verified based on the current connection matrix, and generate a target execution plan based on the initial connection strategy model and the current connection matrix; obtain the plan execution cost corresponding to each execution plan to be verified and the target execution cost corresponding to the target execution plan; adjust the parameters of the initial connection strategy model according to the plan execution cost and the target execution cost; at the end of the traversal, use the adjusted initial connection strategy model as the preset connection strategy model.

[0161] Furthermore, the information extraction module 10 is also used to, at the end of the traversal, use the adjusted initial connection strategy model as a primary adjustment model to obtain a real execution sample set; traverse the real execution sample set, and use the traversed real execution samples as current real samples; read the sample execution plan and the real execution delay corresponding to each sample execution plan from the current real execution sample; analyze the sample execution statements in the current real execution sample through the primary adjustment model to obtain the target sample plan; determine the target execution delay corresponding to the target sample plan according to the real execution delay corresponding to each sample execution plan; perform secondary parameter adjustment on the primary adjustment model based on the real execution delay and the target execution delay; at the end of the traversal of the real execution sample set, use the primary adjustment model after the secondary parameter adjustment as the preset connection strategy model.

[0162] Furthermore, the information extraction module 10 is also used to, at the end of the traversal, use the once-adjusted model after the secondary parameter adjustment as the model to be verified; obtain a preset test sample set and a standard connection vector set corresponding to the preset test sample set; perform connection probability analysis on each test sample statement in the preset test sample set through the model to be verified to obtain a model prediction vector set; generate a model prediction accuracy based on the model prediction vector set and the standard connection vector set; if the model prediction accuracy is greater than or equal to a preset accuracy threshold, use the model to be verified as the preset connection strategy model.

[0163] Furthermore, the information extraction module 10 is also used to analyze each execution plan to be verified through a database optimizer to obtain a plan execution cost corresponding to each execution plan to be verified; obtain a target connection vector corresponding to the target execution plan; compare the target connection vector with the multi-table connection vector corresponding to each execution plan to be verified; use the execution plan to be verified whose corresponding multi-table connection vector is consistent with the target connection vector as the target execution plan to be verified; and use the plan execution cost corresponding to the target execution plan to be verified as the target execution cost corresponding to the target execution plan.

[0164] Furthermore, the information extraction module 10 is also used to obtain the minimum value of the planned execution cost to obtain the minimum execution cost; compare the minimum execution cost with the target execution cost; if the target execution cost is greater than the minimum execution cost, adjust the parameters of the initial connection strategy model according to the planned execution cost and the target execution cost.

[0165] Furthermore, the data query module 40 is also used to extract result sorting conditions from the target multi-table query statement; perform database query based on the query execution plan and the result sorting conditions to obtain data query results; and display the data query results.

[0166] It should be understood that the above is only an example and does not constitute any limitation on the technical solution of the present invention. In specific applications, technicians in this field can make settings as needed, and the present invention does not limit this.

[0167] It should be noted that the workflow described above is merely illustrative and does not limit the scope of protection of the present invention. In practical applications, technicians in this field can select part or all of them according to actual needs to achieve the purpose of the present embodiment, and no limitation is made here.

[0168] In addition, for technical details not fully described in this embodiment, reference may be made to the multi-table connection query method provided in any embodiment of the present invention, and will not be repeated here.

[0169] In addition, it should be noted that, in this article, the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article or system including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or system. In the absence of further restrictions, an element defined by the sentence "comprises a ..." does not exclude the existence of other identical elements in the process, method, article or system including the element.

[0170] The serial numbers of the above embodiments of the present invention are only for description and do not represent the advantages or disadvantages of the embodiments.

[0171] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus a necessary general hardware platform, and of course by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present invention, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as a read-only memory (ROM) / RAM, a magnetic disk, or an optical disk), and includes a number of instructions for a terminal device (which can be a mobile phone, a computer, a server, or a network device, etc.) to execute the methods described in each embodiment of the present invention.

[0172] The above are only preferred embodiments of the present invention, and are not intended to limit the patent scope of the present invention. Any equivalent structure or equivalent process transformation made using the contents of the present invention specification and drawings, or directly or indirectly applied in other related technical fields, are also included in the patent protection scope of the present invention.

Claims

1. A multi-table connection query method, characterized in that: The multi-table connection query method comprises the following steps: Extracting table connection relationships and data filtering conditions in the target multi-table query statement, and constructing a multi-table connection matrix based on the table connection relationships; Performing connection probability analysis on the multi-table connection matrix through a preset connection strategy model to obtain a multi-table connection vector; Constructing a query execution plan according to the multi-table connection vector and the data filtering condition; Performing a database query based on the query execution plan; Wherein, before the step of extracting the table connection relationship and data filtering conditions in the target multi-table query statement and constructing a multi-table connection matrix according to the table connection relationship, it also includes: Traverse the preset training sample set and use the traversed training sample statements as the current query statement; Constructing a current connection matrix according to the current query statement; Constructing a plurality of execution plans to be verified according to the current connection matrix, and generating a target execution plan based on the initial connection strategy model and the current connection matrix; Obtaining the plan execution cost corresponding to each execution plan to be verified and the target execution cost corresponding to the target execution plan; Adjusting parameters of the initial connection strategy model according to the planned execution cost and the target execution cost; At the end of the traversal, the adjusted initial connection strategy model is used as the preset connection strategy model; The step of using the adjusted initial connection strategy model as the preset connection strategy model at the end of the traversal includes: At the end of the traversal, the adjusted initial connection strategy model is used as an adjusted model to obtain a real execution sample set; Traversing the real execution sample set, and taking the traversed real execution sample as the current real execution sample; Reading a sample execution plan and a real execution delay corresponding to each sample execution plan from the current real execution sample; Analyze the sample execution statements in the current real execution sample by using the one-time adjustment model to obtain a target sample plan; Determine the target execution delay corresponding to the target sample plan according to the actual execution delay corresponding to each sample execution plan; Performing secondary parameter adjustment on the primary adjustment model based on the actual execution delay and the target execution delay; When the traversal of the real execution sample set is finished, the first adjustment model after the second parameter adjustment is used as the preset connection strategy model.

2. The multi-table connection query method according to claim 1, characterized in that: The step of using the first adjustment model after the second parameter adjustment as the preset connection strategy model at the end of the traversal of the real execution sample set includes: At the end of the traversal, the first adjustment model after the second parameter adjustment is used as the model to be verified; Obtaining a preset test sample set and a standard connection vector set corresponding to the preset test sample set; Performing connection probability analysis on each test sample sentence in the preset test sample set by using the model to be verified to obtain a model prediction vector set; Generate a model prediction accuracy rate according to the model prediction vector set and the standard connection vector set; If the prediction accuracy of the model is greater than or equal to a preset accuracy threshold, the model to be verified is used as a preset connection strategy model.

3. The multi-table connection query method according to claim 1, characterized in that: The step of obtaining the plan execution cost corresponding to each execution plan to be verified and the target execution cost corresponding to the target execution plan includes: Analyze each execution plan to be verified through the database optimizer to obtain the execution cost corresponding to each execution plan to be verified; Obtaining a target connection vector corresponding to the target execution plan; Compare the target connection vector with the multi-table connection vectors corresponding to each execution plan to be verified; The execution plan to be verified that has a corresponding multi-table connection vector consistent with the target connection vector is used as the target execution plan to be verified; The plan execution cost corresponding to the target execution plan to be verified is used as the target execution cost corresponding to the target execution plan.

4. The multi-table connection query method according to claim 1, characterized in that: The step of adjusting parameters of the initial connection strategy model according to the planned execution cost and the target execution cost includes: Obtaining the minimum value of the execution cost of the plan to obtain the minimum execution cost; comparing the minimum execution cost with the target execution cost; If the target execution cost is greater than the minimum execution cost, the parameters of the initial connection strategy model are adjusted according to the planned execution cost and the target execution cost.

5. The multi-table connection query method according to any one of claims 1 to 4, characterized in that: The step of performing a database query based on the query execution plan includes: Extracting result sorting conditions from the target multi-table query statement; Perform a database query based on the query execution plan and the result sorting condition to obtain a data query result; The data query results are displayed.

6. A multi-table connection query device, characterized in that: The multi-table connection query device includes the following modules: An information extraction module is used to extract table connection relationships and data filtering conditions in a target multi-table query statement, and to construct a multi-table connection matrix based on the table connection relationships; A probability analysis module, used to perform connection probability analysis on the multi-table connection matrix through a preset connection strategy model to obtain a multi-table connection vector; A plan building module, used to build a query execution plan according to the multi-table connection vector and the data filtering condition; A data query module, used for performing database query based on the query execution plan; The information extraction module is further used to traverse the preset training sample set and use the traversed training sample sentences as the current query sentence; Constructing a current connection matrix according to the current query statement; Construct multiple execution plans to be verified according to the current connection matrix, and generate a target execution plan based on the initial connection strategy model and the current connection matrix; obtain the plan execution cost corresponding to each execution plan to be verified and the target execution cost corresponding to the target execution plan; adjust the parameters of the initial connection strategy model according to the plan execution cost and the target execution cost; at the end of the traversal, use the adjusted initial connection strategy model as the preset connection strategy model; The information extraction module is further used to, at the end of the traversal, use the adjusted initial connection strategy model as an adjustment model to obtain a real execution sample set; traverse the real execution sample set, and use the traversed real execution samples as current real execution samples; Reading a sample execution plan and a real execution delay corresponding to each sample execution plan from the current real execution sample; analyzing the sample execution statements in the current real execution sample by using the one-time adjustment model to obtain a target sample plan; Determine the target execution delay corresponding to the target sample plan according to the actual execution delay corresponding to each sample execution plan; The first adjustment model is subjected to a second parameter adjustment based on the real execution delay and the target execution delay; and when the traversal of the real execution sample set is completed, the first adjustment model after the second parameter adjustment is used as a preset connection strategy model.

7. A multi-table connection query device, characterized in that: The multi-table connection query device includes: a processor, a memory, and a multi-table connection query program stored in the memory and executable on the processor. When the multi-table connection query program is executed by the processor, the steps of the multi-table connection query method according to any one of claims 1 to 5 are implemented.

8. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a multi-table connection query program, which implements the steps of the multi-table connection query method according to any one of claims 1 to 5 when executed.

Images