Database equivalent connection control method and system and server

Abstract

The invention provides a database equivalent connection control method and system and a server, and relates to the technical field of databases, the method comprises the following steps: firstly, determining a first equivalent condition corresponding to a plurality of data tables according to equivalent connection operation statements corresponding to the plurality of data tables in a database; constructing an undirected graph corresponding to the database based on the data table and the first equivalence condition; then, the vertexes in the undirected graph are grouped according to a first equivalence condition, and a connected graph corresponding to an equivalence connection operation statement is generated according to a grouping result; determining a second equivalence condition corresponding to the plurality of data tables through a connection relation of the connected graph; and finally, controlling the plurality of data tables to perform equivalent connection according to the first equivalent condition and the second equivalent condition. According to the method, the implicit association path is deduced through the passing closure of the equivalence condition in the query analysis stage, so that the selection space of the JOIN sequence is expanded, and the execution performance of database equivalence connection is improved.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, in particular to a database equi-join control method, system and server. BACKGROUND

[0002] In distributed database, multi-table JOIN (equi-join) operation process depends on data distribution and JOIN order selection. Traditional method mainly generates execution plan based on explicit declared JOIN condition, and cannot fully utilize the transitivity of equi-join condition, resulting in potential high efficiency path not being explored. The existing technology optimizes JOIN order mainly based on cost estimation. If more plan choices can be generated, the solution space is expanded, and the cost estimation mechanism can be fully utilized.

[0003] For example, when the database executes the following statement SELECT * FROM t1 INNER JOIN t2 INNER JOIN t3 on t1.a = t2.b and t2.b = t3.c, the evaluation plan only has the following two: ; ; Obviously, according to the existing equi-join conditions t1.a = t2.b and t2.b = t3.c, t1.a = t3.c can be derived. At this time, the third plan can be selected: ; In some cases, the third plan of JOINing t1 and t3 first has much better performance than the other two. For example, t1 and t3 have special distribution mode, which can fully save the overhead of data transmission; or JOINing t1 and t3 first can filter out a large amount of data, and only generate a small result set. From the perspective of cost estimation, the derived new condition generates a new plan, and the new plan generates smaller network cost, CPU cost or I / O cost, which will be preferentially selected. However, the traditional method ignores the use of such hidden conditions, and does not consider such equivalent alternative plans, and often can only manually modify the query statement to realize the optimal plan when performance problems are found. SUMMARY

[0004] Therefore, the present application aims to provide a database equi-join control method, system and server. The method derives implicit association path through transitive closure of equi-join condition in the query analysis stage, thereby expanding the selection space of JOIN order and improving the execution performance of database equi-join.

[0005] In a first aspect, the present application provides a database equi-join control method, which comprises: determine a first equivalence condition corresponding to the plurality of data tables according to an equivalence join operation statement corresponding to the plurality of data tables in the database; construct a corresponding undirected graph of the database based on the data table and the first equivalence condition; wherein vertices of the undirected graph are constructed by fields in the data table, and edges of the undirected graph are constructed by fields corresponding to the first equivalence condition; group vertices in the undirected graph by using the first equivalence condition, and generate a connected graph corresponding to the equivalence join operation statement according to a grouping result; determine a second equivalence condition corresponding to the plurality of data tables according to a connection relationship of the connected graph; control the plurality of data tables to perform equivalence join according to the first equivalence condition and the second equivalence condition.

[0006] Optionally, the step of determining the first equivalence condition corresponding to the plurality of data tables according to the equivalence join operation statement corresponding to the plurality of data tables in the database comprises: obtain the equivalence join operation statement according to the plurality of data tables and fields corresponding thereto in the target database; determine a first field equivalence condition between a first data table and a second data table in the database and a second field equivalence condition between the second data table and a third data table by using the equivalence join operation statement; determine the first equivalence condition based on the first field equivalence condition and the second field equivalence condition.

[0007] Optionally, the step of constructing the corresponding undirected graph of the database based on the data table and the first equivalence condition comprises: construct a first vertex, a second vertex and a third vertex corresponding to the undirected graph based on the first data table, the second data table and the third data table respectively; determine a first field of the first data table, a second field of the second data table and a third field of the third data table according to the first equivalence condition; construct a first edge corresponding to the undirected graph based on the first field and the second field, and construct a second edge corresponding to the undirected graph based on the second field and the third field; construct the corresponding undirected graph of the database by using vertices corresponding to the first vertex, the second vertex and the third vertex and edges corresponding to the first edge and the second edge.

[0008] Optionally, the step of grouping the vertices in the undirected graph by using the first equivalence condition comprises: merge the first vertex and the second vertex corresponding to the first edge by using the first equivalence condition to obtain a first vertex set; merge the second vertex and the third vertex corresponding to the second edge by using the first equivalence condition to obtain a second vertex set; The root nodes corresponding to the vertexes in the second vertex set are acquired, and the vertexes are grouped according to the root nodes.

[0009] Optionally, a connected graph corresponding to the equi-join operation statement is generated according to the grouping result, and the method comprises: A first connected graph corresponding to the equi-join operation statement is generated according to the first root node corresponding to the first vertex; A second connected graph corresponding to the equi-join operation statement is generated according to the second root node corresponding to the second vertex; A third connected graph corresponding to the equi-join operation statement is generated according to the third root node corresponding to the third vertex; A fourth connected graph corresponding to the equi-join operation statement is generated based on the first root node, the second root node and the third root node.

[0010] Optionally, the second equi-conditions corresponding between the plurality of data tables are determined through the connection relationship of the connected graph, and the method comprises: A third field equi-condition between the first data table and the third data table is determined according to the connection relationship between the first root node and the third root node in the fourth connected graph; The second equi-conditions are determined based on the first field equi-condition, the second field equi-condition and the third field equi-condition.

[0011] Optionally, the plurality of data tables are controlled to perform equi-join according to the first equi-condition and the second equi-condition, and the method comprises: Original generation values and adjusted generation values corresponding to the equi-join operation statement under the first equi-condition and the second equi-condition are respectively acquired; If the adjusted generation value is less than the original generation value, the plurality of data tables are controlled to perform equi-join by using the second equi-condition; if the adjusted generation value is not less than the original generation value, the plurality of data tables are controlled to perform equi-join by using the first equi-condition.

[0012] Optionally, the step of respectively acquiring the original generation values and the adjusted generation values corresponding to the equi-join operation statement under the first equi-condition and the second equi-condition comprises: First query conditions corresponding to the equi-join operation statement under the first equi-condition are acquired, and second query conditions corresponding to the equi-join operation statement under the second equi-condition are acquired; First weight values corresponding to the first query conditions are determined, and second weight values corresponding to the second query conditions are determined; wherein the second weight values are greater than the first weight values; First generation values and second generation values corresponding to the plurality of data tables when performing equi-join are respectively determined based on the first query conditions and the second query conditions; The original generation value is determined according to the product of the first generation value and the first weight value, and the adjusted generation value is determined according to the product of the second generation value and the second weight value.

[0013] In a second aspect, the present application provides a database equi-join control system, comprising: a first equi-join condition determining module configured to determine a first equi-join condition corresponding to a plurality of data tables in a database according to an equi-join operation statement corresponding to the plurality of data tables; a graph constructing module configured to construct a graph corresponding to the database based on the data tables and the first equi-join condition, wherein vertices of the graph are constructed by fields in the data tables, and edges of the graph are constructed by fields corresponding to the first equi-join condition; a connected graph generating module configured to group vertices in the graph by using the first equi-join condition, and generate a connected graph corresponding to the equi-join operation statement according to a grouping result; a second equi-join condition determining module configured to determine a second equi-join condition corresponding to the plurality of data tables by a connection relationship of the connected graph; an equi-join control module configured to control the plurality of data tables to perform equi-join according to the first equi-join condition and the second equi-join condition.

[0014] In a third aspect, the present application provides a server, comprising a processor and a memory, wherein the memory stores computer executable instructions executable by the processor, and the processor executes the computer executable instructions to implement steps of the database equi-join control method provided in the first aspect.

[0015] In a fourth aspect, the present application provides a storage medium, wherein the storage medium stores computer executable instructions, and the computer executable instructions, when invoked and executed by a processor, cause the processor to implement steps of the database equi-join control method provided in the first aspect.

[0016] The database equi-join control method, system and server provided by the present application first determine a first equi-join condition corresponding to a plurality of data tables in a database according to an equi-join operation statement corresponding to the plurality of data tables, then construct a graph corresponding to the database based on the data tables and the first equi-join condition, wherein vertices of the graph are constructed by fields in the data tables, and edges of the graph are constructed by fields corresponding to the first equi-join condition, subsequently group vertices in the graph by using the first equi-join condition, and generate a connected graph corresponding to the equi-join operation statement according to a grouping result, then determine a second equi-join condition corresponding to the plurality of data tables by a connection relationship of the connected graph, and finally control the plurality of data tables to perform equi-join according to the first equi-join condition and the second equi-join condition. The method extends the selection space of JOIN order by deriving an implicit association path through the transmission closure of equi-join conditions in the query analysis stage, and improves the execution performance of database equi-join.

[0017] Additional features and advantages of the present application will be set forth in the description that follows, and in part will be apparent from the description, or can be learned by practice of the present application. The objectives and other advantages of the present application will be realized and attained by the structure particularly pointed out in the description and claims.

[0018] In order to make the above objectives, features and advantages of the present application more apparent, the following will describe a preferred embodiment in detail, with reference to the accompanying drawings. BRIEF DESCRIPTION OF DRAWINGS

[0019] In order to more clearly illustrate the specific embodiments of the present application or the technical solutions in the prior art, the following will briefly introduce the drawings needed to be used in the specific embodiments or the prior art description. Obviously, the drawings described below are some embodiments of the present application, and for those skilled in the art, other drawings can also be obtained without creative labor based on these drawings.

[0020] Figure 1 A flowchart of a database equivalent connection control method provided by an embodiment of the present application; Figure 2 A flowchart of step S101 of a database equivalent connection control method provided by an embodiment of the present application; Figure 3 A flowchart of step S102 of a database equivalent connection control method provided by an embodiment of the present application; Figure 4 A flowchart of step S103 of a database equivalent connection control method provided by an embodiment of the present application, in which the vertices in the undirected graph are grouped by using the first equivalent condition; Figure 5 A flowchart of step S103 of a database equivalent connection control method provided by an embodiment of the present application, in which the connected graph corresponding to the equivalent connection operation statement is generated according to the grouping result; Figure 6 A flowchart of step S104 of a database equivalent connection control method provided by an embodiment of the present application; Figure 7 A flowchart of step S105 of a database equivalent connection control method provided by an embodiment of the present application; Figure 8 A flowchart of step S701 of a database equivalent connection control method provided by an embodiment of the present application; Figure 9 A connected graph schematic diagram in a database equivalent connection control method provided by an embodiment of the present application; Figure 10A structural schematic diagram of a database equivalent connection control system provided by an embodiment of the present application is shown in the figure. Figure 11 A structural schematic diagram of a server provided by an embodiment of the present application is shown in the figure.

[0021] Icon: 1010 - first equivalent condition determining module; 1020 - undirected graph constructing module; 1030 - connected graph generating module; 1040 - second equivalent condition determining module; 1050 - equivalent connection control module. 101 - processor; 102 - memory; 103 - bus; 104 - communication interface. DETAILED DESCRIPTION

[0022] To make the objectives, technical solutions and advantages of the embodiments of the present application clearer, the technical solutions of the present application will be described below in connection with the embodiments. Obviously, the described embodiments are only some of the embodiments of the present application, rather than all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative work fall within the scope of protection of the present application.

[0023] To make the embodiments of the present application easier to understand, first, a database equivalent connection control method disclosed by the embodiments of the present application will be described in detail. Figure 1 As shown in the figure, the method comprises: Step S101: determining first equivalent conditions corresponding to a plurality of data tables in a database according to equivalent connection operation statements corresponding to the plurality of data tables.

[0024] From equivalent connection operation statements (such as SQL statements containing JOIN) involving multiple tables in the database, parse and determine equivalent association rules directly defined between the data tables, i.e. the first equivalent conditions. These conditions are the direct basis for the connection operation, and usually exist in the form of “table name. field name = another table name. field name” (for example, “order table. user ID = user table. ID”). By extracting these conditions, the most intuitive association relationship between the data tables can be determined.

[0025] Step S102: constructing an undirected graph corresponding to the database based on the data tables and the first equivalent conditions; wherein the vertices of the undirected graph are constructed by the fields in the data tables, and the edges of the undirected graph are constructed by the fields corresponding to the first equivalent conditions.

[0026] The fields in each data table in the database are taken as "vertices" of an undirected graph, and the first equivalence conditions obtained in step S101 are taken as "edges" between the vertices, to construct an undirected graph reflecting the multi-table association relationship. The existence of an edge indicates that there is a direct equivalence association between two vertices (data tables) through corresponding fields, and the edge does not distinguish direction (consistent with the characteristic that "the equivalence condition is bidirectional"). This process converts the abstract table relationship into a visual graph structure, facilitating subsequent analysis.

[0027] Step S103: Grouping the vertices in the undirected graph using the first equivalence conditions, and generating a connected graph corresponding to the equivalence join operation statement according to the grouping result.

[0028] Based on the first equivalence conditions, the vertices (data tables) in the undirected graph are grouped: if two vertices can be directly or indirectly connected by one or more edges (first equivalence conditions), they are grouped into the same group; each group of vertices and their associated edges collectively constitute a "connected graph". For example, if table A is connected to table B, and table B is connected to table C, then A, B, and C form a connected graph; a table not associated with any table forms a connected graph alone. The generation of a connected graph can clearly determine which tables can be effectively connected through equivalence conditions, avoiding data redundancy caused by irrelevant tables participating in the connection.

[0029] Step S104: Determining the corresponding second equivalence conditions between the multiple data tables through the connection relationship of the connected graph.

[0030] For each connected graph, the connection path (i.e., the combination relationship of edges) of the internal vertices is analyzed, and the indirect equivalence conditions between the data tables are deduced, referred to as "second equivalence conditions". These conditions are obtained through the transitivity of the first equivalence conditions, for example, from "table A.x = table B.y" and "table B.y = table C.z", it can be deduced that "table A.x = table C.z". The second equivalence conditions supplement the association relationships not covered by the direct conditions, ensuring the completeness of the connection logic.

[0031] Step S105: Controlling the equivalence join of the multiple data tables according to the first equivalence conditions and the second equivalence conditions.

[0032] Combining the first equivalence conditions (direct association) and the second equivalence conditions (indirect association), the equivalence join operation is only performed on the data tables within the same connected graph. This process not only limits the connection range through the connected graph (avoiding invalid association of irrelevant tables), but also ensures the accuracy and completeness of the associated records through the double conditions, ultimately achieving efficient and precise multi-table equivalence join.

[0033] Optionally, according to the equivalence join operation statements corresponding to the multiple data tables in the database, the step S101 of determining the corresponding first equivalence conditions between the multiple data tables is performed, such asFigure 2 As shown, comprising: Step S201: obtaining an equi-join operation statement according to a plurality of data tables in a target database and corresponding field numbers thereof.

[0034] From the target database, according to a plurality of data tables to be processed and field identifiers (such as field names, field numbers, etc.) contained therein, an equi-join operation statement related to the data tables is extracted or obtained. These statements are usually query instructions (such as JOIN statements in SQL) submitted by users or application programs, and contain specific logic for equi-joining fields between data tables. For example, if the “order table”, “user table” and “goods table” are to be processed, an SQL statement containing the equi-join of the three tables is obtained.

[0035] Step S202: determining a first field equi-join condition between a first data table and a second data table in the database, and a second field equi-join condition between the second data table and a third data table, using the equi-join operation statement.

[0036] Based on the equi-join operation statement obtained in step S201, the field-level equi-join conditions directly existing between the data tables are parsed. Specifically, it includes: determining a “first field equi-join condition” (such as “order table.user_id=user table.id”) between a first data table (such as “order table”) and a second data table (such as “user table”); determining a “second field equi-join condition” (such as “user table.last_buy_goods_id=goods table.id”) between the second data table (such as “user table”) and a third data table (such as “goods table”).

[0037] This step focuses on extracting the field equi-join relationship directly defined between two tables in the statement.

[0038] Step S203: determining a first equi-join condition based on the first field equi-join condition and the second field equi-join condition.

[0039] All direct field-level equi-join conditions parsed in step S202, such as “first field equi-join condition” and “second field equi-join condition”, are integrated to form the “first equi-join condition” described in step S101. That is, the first equi-join condition is a collection of all directly defined field equi-join conditions between multiple data tables, for example, it may contain the two conditions “order table.user_id=user table.id” and “user table.last_buy_goods_id=goods table.id” after integration, which are collectively used as the basis for subsequent graphical modeling.

[0040] Optionally, step S102 of constructing a database corresponding undirected graph based on the data tables and the first equi-join condition, such asFigure 3 As shown, comprising: Step S301: based on the first data table, the second data table and the third data table, respectively constructing the first vertex, the second vertex and the third vertex corresponding to the undirected graph.

[0041] Based on the specific data table participating in the connection, the vertex in the undirected graph is created for each data table: the related fields in the first data table are corresponded to the "first vertex" of the undirected graph, the second data table is corresponded to the "second vertex", and the third data table is corresponded to the "third vertex". Each vertex can be directly named by the name (or unique identifier) of the corresponding data table, clearly reflecting the mapping relationship of "vertex is the field in the data table", providing a basic node for the construction of the subsequent associated edge.

[0042] Step S302: determining the first field of the first data table, the second field of the second data table and the third field of the third data table according to the first equivalence condition.

[0043] From the first equivalence condition determined in step S101, the specific fields participating in the equivalence association in each data table are parsed: Determine the "first field" in the first data table participating in the connection (such as the "x" field of table A in the first equivalence condition "table A.x = table B.y"); Determine the "second field" in the second data table participating in the connection (such as the "y" field of table B in the above condition, which may also participate in the association with the third data table); Determine the "third field" in the third data table participating in the connection (such as the "z" field of table C in the first equivalence condition "table B.y = table C.z").

[0044] This step clearly defines the fields actually used for equivalence connection in each table, which is the core basis for constructing the associated edge between the vertices.

[0045] Step S303: based on the first field and the second field, constructing the first edge corresponding to the undirected graph, and based on the second field and the third field, constructing the second edge corresponding to the undirected graph.

[0046] According to the equivalence relationship between the fields, the edge of the undirected graph is created between the corresponding vertices: Based on the equivalence condition between the first field of the first data table and the second field of the second data table (such as "table A.x = table B.y"), a "first edge" is constructed between the first vertex and the second vertex, and the attribute of the edge can be marked The equivalence relationship of the field; Based on the equivalence condition between the second field of the second data table and the third field of the third data table (such as "table B.y = table C.z"), a "second edge" is constructed between the second vertex and the third vertex, and the corresponding field equivalence relationship is also marked.

[0047] The existence of the edge intuitively embodies the direct association of the data table through the field, and the edge does not distinguish the direction because of the bidirectionality of the equivalence condition.

[0048] Step S304: constructing a corresponding undirected graph of the database by using the vertices corresponding to the first vertex, the second vertex and the third vertex, and the edges corresponding to the first edge and the second edge.

[0049] The first vertex, the second vertex and the third vertex constructed in step S301 are integrated with the first edge and the second edge constructed in step S303 to form a complete undirected graph. The undirected graph takes the vertex to represent the data table and takes the edge to represent the direct equivalence association between the fields of the tables, and completely presents the association network formed by the multiple data tables through the first equivalence condition, thereby providing a visual structural basis for subsequent grouping and connected graph generation.

[0050] Optionally, the vertices in the undirected graph are grouped by using the first equivalence condition, as shown in the following formula: Figure 4 As shown in the following formula: Step S401: merging the first vertex and the second vertex corresponding to the first edge by using the first equivalence condition to obtain a first vertex set.

[0051] Based on the association relationship represented by the first edge (the edge connecting the first vertex and the second vertex) in the first equivalence condition, the first vertex and the second vertex are merged into a set, i.e., a first vertex set. The basis of this merging is that the first equivalence condition (such as "table A.x = table B.y") of the first edge indicates that the two vertices (data tables) have a direct association and should be preliminarily included in the same grouping range. For example, if the first vertex is an "order table" and the second vertex is a "user table", the first vertex set after merging contains "order table" and "user table".

[0052] Step S402: merging the second vertex and the third vertex by using the first equivalence condition for the second edge to obtain a second vertex set.

[0053] Based on the association relationship represented by the second edge (the edge connecting the second vertex and the third vertex) in the first equivalence condition, the second vertex and the third vertex are merged into a second vertex set. Since the second vertex is already in the first vertex set in step S401, this merging actually includes the third vertex in the range associated with the first vertex and the second vertex. For example, if the second edge corresponds to "table B.y = table C.z", the second vertex set will contain "order table", "user table" and "goods table", thereby realizing the inclusion of the indirectly associated vertex (goods table) into the same set through the intermediate vertex (user table).

[0054] Step S403: obtaining the root nodes corresponding to the vertices in the second vertex set, and grouping the vertices according to the root nodes.

[0055] In the second vertex set obtained in step S402, a "root node" (which can be understood as a representative reference vertex in the set, used to uniformly identify the entire connectivity relationship) corresponding to all vertices is determined. Subsequently, with the root node as the identifier, all vertices associated with the root node directly or indirectly through edges (first equivalence condition) are divided into the same group. This step ensures the uniqueness and consistency of the grouping, and the vertices under the same root node necessarily belong to the same connected component, and each grouping formed finally corresponds to a connected subgraph in the undirected graph. For example, if the root node is "user table", the "order table, user table, and commodity table" are divided into the same group because they are all associated with the root node.

[0056] Optionally, a connected graph corresponding to the equi-join operation statement is generated according to the grouping result, as shown in Figure 5 , including: Step S501: generating a first connected graph corresponding to the equi-join operation statement according to a first root node corresponding to a first vertex.

[0057] Taking the first root node corresponding to the first vertex as the core identifier, all vertices (such as the first vertex and vertices directly or indirectly associated with the first vertex) belonging to the root node grouping and the associated edges (edges corresponding to the first equivalence condition) between these vertices are integrated to generate a "first connected graph" corresponding to the equi-join operation statement. The connected graph completely contains all associated data tables and their direct connection relationships with the first root node as the reference, and is an independent subgraph unit.

[0058] Step S502: generating a second connected graph corresponding to the equi-join operation statement according to a second root node corresponding to a second vertex.

[0059] Taking the second root node corresponding to the second vertex as the core identifier, all vertices (such as the second vertex and vertices associated with the second vertex) in the root node grouping and the corresponding associated edges are integrated to generate a "second connected graph". The second connected graph is parallel to the first connected graph, and respectively corresponds to the associated relationship network dominated by different root nodes. If the second root node is not associated with the first root node, the two are independent subgraphs.

[0060] Step S503: generating a third connected graph corresponding to the equi-join operation statement according to a third root node corresponding to a third vertex.

[0061] Taking the third root node corresponding to the third vertex as the core identifier, all vertices and associated edges in the grouping are integrated to generate a "third connected graph". The connected graph also exists as an independent subgraph, containing all data tables and connection relationships associated with the third root node, and its range is determined by the grouping result of the third root node.

[0062] Step S504: generating a fourth connected graph corresponding to the equi-join operation statement based on the first root node, the second root node, and the third root node.

[0063] Based on the potential association relationship between the first root node, the second root node, and the third root node (such as an indirect connection condition not directly embodied in the previous steps), the parts associated in the first, second, and third connected graphs are integrated to generate a fourth connected graph containing the groups corresponding to the three root nodes. The fourth connected graph is a higher-level integration of the previous three subgraphs, reflecting the overall association network between the data tables to which the multiple root nodes belong, and is suitable for scenarios requiring equi-join across multiple subgraphs.

[0064] Optionally, the step S104 of determining the corresponding second equi-join condition between the multiple data tables based on the connection relationship of the connected graph includes: Figure 6 As shown in the following table, the step S104 includes: Step S601: determining a third field equi-join condition between the first data table and the third data table based on the connection relationship between the first root node and the third root node in the fourth connected graph.

[0065] Focusing on the fourth connected graph (which integrates the association networks of the first, second, and third root nodes), the connection path between the first root node (corresponding to the first data table) and the third root node (corresponding to the third data table) is analyzed. Since these two root nodes have an indirect association in the fourth connected graph (usually through the second data table corresponding to the second root node as an intermediate bridge), a third field equi-join condition between the first data table and the third data table can be derived based on the path. For example: if there is a connection path of "first root node (table A) - second root node (table B) - third root node (table C)" in the fourth connected graph, and the field conditions of table A and table B (table A.x = table B.y) and the field conditions of table B and table C (table B.y = table C.z) are known, then the third field equi-join condition of table A and table C (table A.x = table C.z) can be derived through path association.

[0066] Step S602: determining the second equi-join condition based on the first field equi-join condition, the second field equi-join condition, and the third field equi-join condition.

[0067] The "third field equivalence condition" derived in step S601 is integrated with the previously determined "first field equivalence condition" (between the first data table and the second data table) and "second field equivalence condition" (between the second data table and the third data table) to form the "second equivalence condition" described in step S104. The core of the second equivalence condition is to include all field equivalence conditions derived indirectly through the connection relationship of the connected graph (such as the above-mentioned table A.x = table C.z), which supplements the indirect association relationship not covered by the first equivalence condition (direct association) and ensures that all potential equivalence association logics can be fully utilized in multi-table connection.

[0068] Optionally, the step S105 of controlling the equivalence connection of the multiple data tables according to the first equivalence condition and the second equivalence condition includes the following steps. Figure 7 Step S701: Obtain the original generation value and the adjusted generation value corresponding to the equivalence connection operation statement under the first equivalence condition and the second equivalence condition, respectively.

[0069] This step calculates the corresponding "generation value" when executing the equivalence connection operation statement for the first equivalence condition (direct association condition) and the second equivalence condition (indirect association condition), respectively. The generation value calculated based on the first equivalence condition is called "original generation value", which reflects the resource consumption (such as calculation time, memory occupation, data scanning volume, etc.) when only using direct association condition for connection. The generation value calculated based on the second equivalence condition is called "adjusted generation value", which reflects the resource consumption when introducing indirect association condition for connection.

[0070] The calculation of the generation value usually depends on the cost estimation model of the database optimizer, which comprehensively considers the data volume, index usage, connection algorithm (such as nested loop, hash connection), etc. to provide comparable quantitative indicators for the two connection strategies.

[0071] Step S702: If the adjusted generation value is less than the original generation value, the second equivalence condition is used to control the equivalence connection of the multiple data tables; if the adjusted generation value is not less than the original generation value, the first equivalence condition is used to control the equivalence connection of the multiple data tables.

[0072] ​By comparing the adjusted generation value with the original generation value, a condition for finally controlling the multi-table equi-join is determined: if the adjusted generation value is less than the original generation value, it indicates that the introduction of the second equi-condition (indirect association) can reduce the connection cost and improve the efficiency, and the second equi-condition is selected to control the data table for equi-join; if the adjusted generation value is not less than the original generation value, it indicates that it is more efficient to only use the first equi-condition (direct association), and the first equi-condition is maintained to control the connection operation. The core of this step is to select a connection strategy with lower resource consumption under the premise of ensuring the accuracy and integrity of the connection, so as to optimize the execution efficiency of the database equi-join.

[0073] Optionally, the steps S701 of acquiring the original generation value and the adjusted generation value corresponding to the equi-join operation statement under the first equi-condition and the second equi-condition respectively, as shown in Figure 8 , include: Step S801: Acquire the first query condition corresponding to the equi-join operation statement under the first equi-condition, and acquire the second query condition corresponding to the equi-join operation statement under the second equi-condition.

[0074] The specific query conditions for limiting the connection range in the equi-join operation statement based on the first equi-condition and the second equi-condition are acquired respectively: The "first query condition" is a query restriction logic containing only the first equi-condition (direct association condition), for example "table A.x = table B.y AND table B.y = table C.z"; The "second query condition" is a query restriction logic containing the second equi-condition (indirect association condition), for example, adding "table A.x = table C.z" based on the first query condition.

[0075] These query conditions directly determine the rules and range of data filtering in the connection operation, and are the basis for subsequent generation value calculation.

[0076] Step S802: Determine the first weight value corresponding to the first query condition, and determine the second weight value corresponding to the second query condition; wherein the second weight value is greater than the first weight value.

[0077] This step assigns corresponding "weight values" to the first query condition and the second query condition: the "first weight value" corresponding to the first query condition is determined, which reflects the basic influence degree of the direct condition in the connection decision; the "second weight value" corresponding to the second query condition is determined, and the second weight value is greater than the first weight value. This setting is usually because the second equi-condition contains indirect association logic, which can more comprehensively filter invalid data and has higher priority or influence in connection optimization.

[0078] The size of the weight value can be dynamically adjusted according to database performance requirements, data correlation complexity and other factors, and the core is to reflect the different importance of the two conditions in the generation value calculation.

[0079] Step S803: Based on the first query condition and the second query condition, respectively determine the first generation value and the second generation value corresponding to the multiple data tables when the equal join.

[0080] Based on the first query condition and the second query condition extracted in step S801, the basic resource consumption cost of the multiple data tables when the equal join is calculated: for the first query condition, the "first generation value" is calculated by the database cost estimation model (considering data volume, index efficiency, connection algorithm, etc.), reflecting the original resource consumption of direct condition connection; for the second query condition, the "second generation value" is calculated in the same way, reflecting the resource consumption of the connection after introducing the indirect condition. The basic generation value is a quantitative measure of the complexity of the connection operation itself, and does not include weight adjustment factors.

[0081] Step S804: Determine the original generation value according to the product of the first generation value and the first weight value, and determine the adjusted generation value according to the product of the second generation value and the second weight value.

[0082] Combine the weight value in step S802 and the basic generation value in step S803 to calculate the final generation value used for comparison: the "original generation value" is obtained by multiplying the first generation value and the first weight value, the formula is: original generation value = first generation value x first weight value, which comprehensively reflects the weighted cost of direct condition connection; the "adjusted generation value" is obtained by multiplying the second generation value and the second weight value, the formula is: adjusted generation value = second generation value x second weight value, which comprehensively reflects the weighted cost of indirect condition connection. By weighting, the generation values under the two conditions have comparability, providing a quantitative basis for subsequent selection of the optimal connection strategy.

[0083] In the specific implementation process, the table is abstracted as the vertex of the graph, and the equal join condition is abstracted as the edge, and an undirected graph is constructed. The equal condition t1.a = t2.b and t2.b = t3.c can be abstracted as a connected graph as shown in a. Figure 9

[0084] ​The vertices are grouped by using the union-find algorithm. Union-find is an efficient data structure for handling the merging and querying of disjoint sets, mainly used to solve dynamic connectivity problems. In this method, union-find is used to group vertices, and nodes that can be connected are considered as a group. Assuming there are three inner join conditions, t1.a = t2.b, t2.b = t3.c, t1.b = t2.a; t1.a and t3.c both have an edge with t2.b, t1.a and t3.c are connected, and finally t1.a, t2.b, and t3.c belong to the same graph, that is, the same group, t1.b and t2.a are only connected to each other, belonging to another group.

[0085] In the specific processing process, first, create an independent set for each element, and its parent node points to itself. Abstract t1.a, t2.b, and t3.c as nodes A, B, and C. After A, B, and C are initialized: parent[A]=A, parent[B]=B, parent[C]=C.

[0086] Then perform the find operation, recursively find the root node (representative element) of the element, which is used to determine the set membership. find(B) returns its root node, which is parent[B] (when not merged).

[0087] Subsequently, the merge operation is performed. If there is an edge between two points, it means that they are connected, and the merge is performed. The merge process loops through all edges, that is, all equal conditions. If there is a condition t1.a = t2.b, there is an edge AB. At this time, merge A and B, change the root node of B to the root node of A, parent[B] = A, and the set becomes {A, B} and {C}. Then according to the condition t2.b = t3.c, merge again, parent[C]= A, and the set becomes {A,B,C}.

[0088] Then perform the grouping operation. The nodes that are connected after merging have the same parent, and the vertices with the same parent are grouped into the same group. Finally, generate the connected graph. The semantics of the query statement can be abstracted again, and it can be found that for columns in the same group, as long as the connectivity of the graph is guaranteed, the semantic equivalence can be guaranteed.

[0089] Figure 9 The query statement corresponding to a in the middle is: SELECT * FROM t1 INNER JOIN t2 INNER JOIN t3 on t1.a = t2.b and t2.b= t3.c; Figure 9 The query statement corresponding to b, c, and d in the middle is: SELECT * FROM t1 INNER JOIN t2 INNER JOIN t3 on t1.a = t2.b and t1.a= t3.c; SELECT * FROM t1 INNER JOIN t2 INNER JOIN t3 on t1.a = t3.c and t2.b= t3.c; SELECT * FROM t1 INNER JOIN t2 INNER JOIN t3 on t1.a = t2.b and t1.a= t3.c and t2.b = t3.c; It can be found that the result set is correct as long as the graph is connected. Therefore, all cases that can ensure the graph to be connected are added to the candidate plan by traversing all groups. It should be noted that some cases correspond to non-minimal connected graphs, which contain some redundant edges, for example Figure 9 The corresponding case contains three equality conditions, and deleting any one of them will not affect the result set. However, this method still retains this plan, and these redundant conditions can provide more query paths to optimize query performance, so non-minimal connected graphs should also be considered as candidates. Finally, the result of cost evaluation is used to determine whether to execute it.

[0090] Through the derivation of equality conditions, as many equivalent candidates as possible are generated before cost evaluation. Finally, the selection of the plan still depends on the perfect cost evaluation mechanism, and the core cost evaluation mechanism will not be changed, which can be universally applicable to various cost evaluation mechanisms.

[0091] However, considering that in the cost evaluation system of the query optimizer, the query statement explicitly written by the user directly reflects his query intention, which should be the core basis for optimization. For the implicit association conditions generated by the equality propagation derivation (such as t1.a=t3.a derived from t1.a=t2.a and t2.a=t3.a), a cautious weight adjustment strategy should be adopted: Basic weight: the query condition explicitly specified by the user remains the first generation value calculation weight (benchmark value α=1); Derivation condition penalty coefficient: for the execution plan generated by relying on implicit derivation, the second generation value needs to be multiplied by the adjustment coefficient β (β>1, recommended value range 1.2~1.5).

[0092] The optimizer generates a multi-version execution plan containing explicit conditions and derived conditions as an execution subject. If the adjusted generation value is less than the original generation value, the second equivalent condition is used to control the equivalent connection of the multiple data tables; if the adjusted generation value is not less than the original generation value, the first equivalent condition is used to control the equivalent connection of the multiple data tables. In an actual scenario, a maximum derivation depth limit can be set, and when the limit is exceeded, the derivation is stopped, the current derived conditions are retained, and the plan is continued to be generated.

[0093] According to the above database equivalent connection control method, the implicit association path is derived through the transmission closure of the equivalent condition in the query analysis stage, thereby expanding the selection space of the JOIN order and improving the execution performance of the database equivalent connection.

[0094] Corresponding to the above database equivalent connection control method embodiment, the embodiment of the application also provides a database equivalent connection control system, as shown in Figure 10 The system comprises: A first equivalent condition determination module 1010 is configured to determine the first equivalent condition corresponding to the multiple data tables according to the equivalent connection operation statement corresponding to the multiple data tables in the database. A directed graph construction module 1020 is configured to construct the undirected graph corresponding to the database based on the data tables and the first equivalent condition; wherein the vertices of the undirected graph are constructed by the fields in the data tables, and the edges of the undirected graph are constructed by the fields corresponding to the first equivalent condition. A connected graph generation module 1030 is configured to group the vertices in the undirected graph by using the first equivalent condition, and generate the connected graph corresponding to the equivalent connection operation statement according to the grouping result. A second equivalent condition determination module 1040 is configured to determine the second equivalent condition corresponding to the multiple data tables by the connection relationship of the connected graph. An equivalent connection control module 1050 is configured to control the equivalent connection of the multiple data tables according to the first equivalent condition and the second equivalent condition.

[0095] According to the above database equivalent connection control system, the implicit association path is derived through the transmission closure of the equivalent condition in the query analysis stage, thereby expanding the selection space of the JOIN order and improving the execution performance of the database equivalent connection.

[0096] The database equivalent connection control system provided by the embodiment of the application has the same implementation principle and technical effects as the above-mentioned database equivalent connection control method embodiment. For brevity of description, the part of the system embodiment not mentioned can be referred to the corresponding content in the above-mentioned database equivalent connection control method embodiment.

[0097] The embodiment also provides a server, and a structure diagram of the server is as shown inFigure 11 As shown, the device includes a processor 101 and a memory 102; wherein the memory 102 is configured to store one or more computer instructions, and the one or more computer instructions are executed by the processor to implement the steps of the database equi-join control method described above.

[0098] Figure 11 The server as shown further includes a bus 103 and a communication interface 104, and the processor 101, the communication interface 104 and the memory 102 are connected through the bus 103.

[0099] The memory 102 can include a high-speed random access memory (RAM), and can also include a non-volatile memory, such as at least one disk memory. The bus 103 can be an ISA bus, a PCI bus, an EISA bus, etc. The bus can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, Figure 11 Only one bidirectional arrow is used in the figure, but it does not mean that there is only one bus or only one type of bus.

[0100] The communication interface 104 is configured to connect with at least one user terminal and other network elements through a network interface, and send the encapsulated IPv4 packet or IPv4 packet to the user terminal through the network interface.

[0101] The processor 101 can be an integrated circuit chip with processing capability. In implementation process, each step of the above method can be completed by integrated logic circuit of hardware in the processor 101 or instruction in the form of software. The processor 101 described above can be a general processor, including a central processing unit (CPU), a network processor (NP), etc.; can also be a digital signal processor (DSP), an application specific integrated circuit (ASIC), a field-programmable gate array (FPGA) or other programmable logic device, a discrete gate or transistor logic device, a discrete hardware component. Each method, step and logic block diagram disclosed in the embodiments of the present disclosure can be implemented or executed. The general processor can be a microprocessor or the processor can also be any conventional processor. The steps of the method disclosed in combination with the embodiments of the present disclosure can be directly embodied as a hardware code processor to execute, or be executed by a combination of hardware and software modules in the code processor. The software module can be located in a random access memory, a flash memory, a read only memory, a programmable read only memory or an electrically erasable programmable memory, a register, or other mature storage medium in the art. The storage medium is located in the storage 102, and the processor 101 reads the information in the storage 102, and combines the hardware to complete the steps of the method of the above embodiments.

[0102] The embodiment of the present application further provides a storage medium, and the storage medium stores a computer program. When the computer program is run by a processor, the steps of the database equivalent connection control method in the above embodiment are executed.

[0103] In several embodiments provided in the present application, it should be understood that the disclosed system, device, equipment and method can be implemented by other ways. The system embodiments described above are only schematic, for example, the division of the units is only a logical function division, and actual implementation can have another division manner, for example, a plurality of units or components can be combined or integrated into another system, or some features can be ignored or not executed. In addition, the coupling or direct coupling or communication connection between the units shown or discussed can be indirect coupling or communication connection through some communication interface, device or unit, and can be electrical, mechanical or other forms.

[0104] The units described as separate components may or may not be physically separate, and the components displayed as units may or may not be physical units, i.e. may be located in one place, or may be distributed on multiple network units. Part or all of the units can be selected according to actual needs to achieve the purpose of the embodiment scheme.

[0105] In addition, each functional unit in each embodiment of the present application can be integrated in one processing unit, or each unit can exist physically, or two or more units can be integrated in one unit.

[0106] If the functions are realized in the form of software functional units and sold or used as independent products, they can be stored in a non-volatile computer readable storage medium executable by a processor. Based on this understanding, the technical solutions of the present application or the part of the present application that essentially contributes to the prior art or the part of the technical solutions can be embodied in the form of a software product, which is stored in a storage medium and includes instructions for making a computer device (which can be a personal computer, a server, or a network device, etc.) execute all or part of the steps of the method described in each embodiment of the present application. The foregoing storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), magnetic disk or optical disk, and various program code storage media.

[0107] Finally, it should be noted that: the above-described embodiments are only specific embodiments of the present application, used to illustrate the technical solutions of the present application, and not to limit them, the protection scope of the present application is not limited thereto, although the present application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand: any skilled person in the art within the technical range disclosed by the present application, they can still modify or easily think of changes to the technical solutions recorded in the foregoing embodiments, or make equivalent replacement to part of the technical features; and these modifications, changes or replacements do not make the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the embodiments of the present application, and all should be covered in the protection scope of the present application. Therefore, the protection scope of the present application should be subject to the protection scope of the claims.

Claims

1. A database equi-join control method characterized by, The method comprises: According to the corresponding equivalent connection operation statement of the plurality of data tables in the database, the first equivalent condition corresponding to the plurality of data tables is determined; Based on the data table and the first equivalent condition, the corresponding undirected graph of the database is constructed; wherein the vertices of the undirected graph are constructed by the fields in the data table, and the edges of the undirected graph are constructed by the fields corresponding to the first equivalent condition; The vertices in the undirected graph are grouped by using the first equivalent condition, and a connected graph corresponding to the equivalent connection operation statement is generated according to the grouping result; The second equivalent condition corresponding to the plurality of data tables is determined through the connection relationship of the connected graph; According to the first equivalent condition and the second equivalent condition, the plurality of data tables are controlled to be equivalent connected.

2. The database equi-join control method of claim 1, wherein, According to the corresponding equivalent connection operation statement of the plurality of data tables in the database, the first equivalent condition corresponding to the plurality of data tables is determined, comprising: According to the plurality of data tables and the corresponding field numbers in the target database, the equivalent connection operation statement is obtained; The first field equivalent condition between the first data table and the second data table in the database, and the second field equivalent condition between the second data table and the third data table are determined by using the equivalent connection operation statement; The first equivalent condition is determined based on the first field equivalent condition and the second field equivalent condition.

3. The database equi-join control method of claim 2, wherein, Based on the data table and the first equivalent condition, the corresponding undirected graph of the database is constructed, comprising: Based on the first data table, the second data table and the third data table, the first vertex, the second vertex and the third vertex corresponding to the undirected graph are constructed respectively; According to the first equivalent condition, the first field of the first data table, the second field of the second data table and the third field of the third data table are determined; Based on the first field and the second field, the first edge corresponding to the undirected graph is constructed, and based on the second field and the third field, the second edge corresponding to the undirected graph is constructed; The vertices corresponding to the first vertex, the second vertex and the third vertex, and the edges corresponding to the first edge and the second edge are used to construct the undirected graph corresponding to the database.

4. The database equi-join control method of claim 3, wherein, The vertices in the undirected graph are grouped by using the first equivalent condition, comprising: The first vertex and the second vertex corresponding to the first edge are merged by using the first equivalent condition to obtain a first vertex set; The second vertex and the third vertex corresponding to the second edge are merged by using the first equivalent condition to obtain a second vertex set; The root node corresponding to the vertices in the second vertex set is obtained, and the vertices are grouped according to the root node.

5. The database equi-join control method of claim 4, wherein, According to the grouping result, the connected graph corresponding to the equivalent connection operation statement is generated, comprising: According to the first root node corresponding to the first vertex, the first connected graph corresponding to the equivalent connection operation statement is generated; According to the second root node corresponding to the second vertex, the second connected graph corresponding to the equivalent connection operation statement is generated; generate a third connected graph corresponding to the equi-join operation statement according to a third root node corresponding to the third vertex; generate a fourth connected graph corresponding to the equi-join operation statement based on the first root node, the second root node and the third root node.

6. The database equi-join control method of claim 5, wherein, determine a second equi-join condition between the multiple data tables according to a connection relationship of the connected graph, including: determine a third field equi-join condition between the first data table and the third data table according to a connection relationship between the first root node and the third root node in the fourth connected graph; determine the second equi-join condition based on the first field equi-join condition, the second field equi-join condition and the third field equi-join condition.

7. The database equi-join control method of claim 6, wherein, control the multiple data tables to perform equi-join according to the first equi-join condition and the second equi-join condition, including: respectively acquire an original generation value and an adjusted generation value corresponding to the equi-join operation statement under the first equi-join condition and the second equi-join condition; if the adjusted generation value is less than the original generation value, control the multiple data tables to perform equi-join using the second equi-join condition; if the adjusted generation value is not less than the original generation value, control the multiple data tables to perform equi-join using the first equi-join condition.

8. The database equi-join control method of claim 7, wherein, respectively acquire an original generation value and an adjusted generation value corresponding to the equi-join operation statement under the first equi-join condition and the second equi-join condition, including: acquire a first query condition corresponding to the equi-join operation statement under the first equi-join condition, and acquire a second query condition corresponding to the equi-join operation statement under the second equi-join condition; determine a first weight value corresponding to the first query condition, and determine a second weight value corresponding to the second query condition; wherein the second weight value is greater than the first weight value; respectively determine a first generation value and a second generation value corresponding to the multiple data tables when performing equi-join based on the first query condition and the second query condition; determine the original generation value according to a product of the first generation value and the first weight value, and determine the adjusted generation value according to a product of the second generation value and the second weight value.

9. A database equi-join control system, characterized by, The system comprises: a first equi-join condition determination module configured to determine a first equi-join condition between multiple data tables in a database according to an equi-join operation statement corresponding to the multiple data tables; an undirected graph construction module configured to construct an undirected graph corresponding to the database based on the data tables and the first equi-join condition; wherein vertices of the undirected graph are constructed by fields in the data tables, and edges of the undirected graph are constructed by fields corresponding to the first equi-join condition; a connected graph generation module configured to group the vertices in the undirected graph using the first equi-join condition, and generate a connected graph corresponding to the equi-join operation statement according to a grouping result; a second equi-join condition determination module configured to determine a second equi-join condition between the multiple data tables according to a connection relationship of the connected graph; and a control module configured to control the multiple data tables to perform equi-join according to the first equi-join condition and the second equi-join condition. An equivalent connection control module is configured to control the multiple data tables to perform equivalent connection according to the first equivalent condition and the second equivalent condition.

10. A server, characterized by A database equivalent connection control method is provided, including the steps of: receiving a first equivalent condition and a second equivalent condition; and controlling multiple data tables to perform equivalent connection according to the first equivalent condition and the second equivalent condition. A database equivalent connection control method is provided, including the steps of: receiving a first equivalent condition and a second equivalent condition; and controlling multiple data tables to perform equivalent connection according to the first equivalent condition and the second equivalent condition.

Images