Relational multi-table connection optimization method and system

Abstract

The invention discloses a relational multi-table connection optimization method and system. The relational multi-table connection optimization system comprises a multi-table connection query module, an adjacent query operator generation module and a multi-table connection optimization module, the multi-table connection query module detects whether an edge table exists in query based on a pre-constructed inter-table graph structure, and if the edge table exists, whether graph query can be constructed or not is judged according to an entity table ID associated with the edge table; the adjacency query operator generation module dynamically generates an adjacency query operator for a table conforming to a graph query structure, an Expand operator directly locates cross-table associated data through a preset adjacency index, linearization acceleration of a multi-table connection path is achieved, and meanwhile an optimizer generates the execution cost of the Expand operator; the multi-table connection optimization module executes a dynamic pruning strategy, selectively evaluates whether to continue to explore an execution plan of a Join operator according to the known cost of the Expand operator, an optimizer synchronously injects the cost of the Expand operator and the Join operator into a cost model, high-cost branches are quickly eliminated through a hybrid cost evaluation framework, and the optimal cost is obtained. And finally selecting the execution plan with the minimum global cost as a detection result. According to the method, the optimization delay problem in a large-scale table connection scene is remarkably relieved, the system resource consumption is reduced, and meanwhile, the redundant data scanning amount is reduced through an adjacent index jump mechanism.

Description

Technical Field

[0001] The present invention relates to the technical field of relational databases, and in particular to a relational multi-table connection optimization method and system. Background Art

[0002] In relational database systems, multi-table join queries, a core operation for complex OLAP analytics, face performance bottlenecks primarily due to the combinatorial explosion of join orders and join algorithms. For join scenarios involving n tables, the number of possible join orders grows with n! (factorial). Each join node must also determine the optimal strategy among multiple algorithms, such as hash joins, nested loop joins, and index joins, resulting in an exponentially expanding solution space. This dual complexity makes it difficult for traditional optimizers to efficiently generate globally optimal execution plans for large-scale table joins (n>10), forcing practical projects to turn to suboptimal solution searches constrained by time and resource trade-offs. Currently, mainstream relational database systems all include optimization methods for multi-table joins. For example, systems like Oracle, based on dynamic programming algorithms, retain locally optimal subplans through recursive pruning to approach the global optimal solution. However, their time complexity significantly degrades when the number of tables exceeds 10. PostgreSQL's genetic algorithm and MySQL's greedy algorithm reduce computational complexity by simulating biological evolutionary processes (such as population selection and crossover mutation). However, these heuristic methods are prone to local optimality and rely on manually tuned hyperparameters (such as the population size in genetic algorithms). While the recently emerging machine learning methods can predict join order through reinforcement learning models, their dependence on training data conflicts with the architectural compatibility of traditional optimizers, making it difficult to avoid prediction bias in cold start scenarios and limiting the practical application of industrial-grade databases.

[0003] Unlike the table-structure-based join optimization paradigm of relational databases, graph databases abstract data entities into nodes and edges through a property graph model, leveraging adjacency indexes to directly implement efficient traversal of cross-table relationships. For example, in the Cypher query statement MATCH(c:Company{id:100})--[Employ]→(p:Person)RETURNp, the chain relationship (Company→Employ→Person) avoids the need to decide the order of multi-table joins through explicit path declarations, significantly improving query efficiency in graph-based OLAP scenarios such as social network analysis and fraud detection. Although relational databases have attempted to introduce graph query extensions (such as Oracle Graph's support for Cypher-like PGQL syntax), their graph traversal optimizers remain strategically isolated from traditional SQL multi-table join optimizers. This separation makes it impossible to reuse the two types of optimization rules. For example, in a graph-table hybrid query scenario, the system needs to repeatedly switch between join order optimization and path traversal optimization. This not only generates additional serialization overhead, but also makes it difficult to achieve unified scheduling of global resource allocation, exposing the technical gap in the integration of heterogeneous data models. Summary of the Invention

[0004] Purpose of the invention: The present invention provides a relational multi-table join optimization method and system, which significantly alleviates the optimization delay problem in large-scale table join scenarios, reduces system resource consumption, and reduces the amount of redundant data scanning through an adjacency index jump mechanism.

[0005] Technical solution: The present invention provides a relational multi-table join optimization method, comprising the following steps:

[0006] Step 1: The user sends a query statement for multi-table joins. Based on the pre-built inter-table graph structure, the query is checked to see if there is an edge table. If so, the entity table ID associated with the edge table is used to determine whether a graph query can be constructed.

[0007] Step 2: Dynamically generate adjacency query operators for tables that conform to the graph query structure. The Expand operator directly locates cross-table associated data through a preset adjacency index, achieving linear acceleration of multi-table join paths. Simultaneously, the optimizer generates the execution cost of the Expand operator.

[0008] Step 3: Execute the dynamic pruning strategy and selectively evaluate whether to continue exploring the execution plan of the Join operator based on the known cost of the Expand operator. The optimizer simultaneously injects the costs of the Expand operator and the Join operator into the cost model. Through the hybrid cost evaluation framework, high-cost branches are quickly eliminated, and the execution plan with the lowest global cost is finally selected as the detection result.

[0009] Furthermore, in step 1, the pre-built inter-table graph structure includes the Part table, the Supplier table, and the PartSupp table, where Part and Supplier are entity tables, PartSupp is a relational table, PS_PARTKEY and PS_SUPPKEY are foreign key constraints and point to the PART table and SUPPLIER table, respectively.

[0010] Furthermore, in step 1, pre-construction of the inter-table graph structure includes the following steps:

[0011] Step 11: The user creates the Part table and the Supplier table using the CREATE statement. At this point, two independent tables exist in the database. Because there is no foreign key relationship between the Part table and the Supplier table, the system determines that they do not have graph structure characteristics.

[0012] Step 12: The user creates the PartSupp table and associates the Part table with the Supplier table through two foreign key constraint fields. The database automatically detects whether the entity table (Part / Supplier) and the relationship table (PartSupp) associated by the foreign key constraint meet the graph structure generation conditions.

[0013] Step 13: Build an adjacency index of a point array structure for the table marked as the point table. This index dynamically records the relationship chain of the latest adjacency edge table. For the edge table, build an edge array index to solidify the topological relationship. When the table already has historical data, the system needs to scan the associated table data in full in a synchronous or asynchronous manner to build an adjacency index to ensure strong consistency between the graph structure data and the business data.

[0014] Furthermore, in step 3, a dynamic pruning strategy is executed to synchronously inject the costs of the Expand operator and the Join operator into the cost model. High-cost branches are quickly eliminated through a hybrid cost evaluation framework, and the execution plan with the lowest global cost is finally selected as the detection result.

[0015] Furthermore, in step 3, the multi-table joint query plan may contain a hybrid structure of traditional Join operators and Expand operators, forming a heterogeneous execution plan tree of relational operators and graph query operators.

[0016] Correspondingly, a relational multi-table join optimization system includes: a multi-table join query module, an adjacency query operator generation module and a multi-table join optimization module; the multi-table join query module detects whether there is an edge table in the query based on a pre-built inter-table graph structure, and if there is an edge table, determines whether a graph query can be constructed based on the entity table ID associated with the edge table; the adjacency query operator generation module dynamically generates adjacency query operators for tables that conform to the graph query structure, wherein the Expand operator directly locates cross-table associated data through a preset adjacency index, thereby realizing linear acceleration of the multi-table join path, and the optimizer generates the execution cost of the Expand operator at the same time; the multi-table join optimization module executes a dynamic pruning strategy, selectively evaluates whether to continue exploring the execution plan of the Join operator based on the known cost of the Expand operator, and the optimizer simultaneously injects the costs of the Expand operator and the Join operator into the cost model, quickly eliminates high-cost branches through a hybrid cost evaluation framework, and finally selects the execution plan with the lowest global cost as the detection result.

[0017] Furthermore, the pre-built table graph structure includes the Part table, the Supplier table, and the PartSupp table, where Part and Supplier are entity tables, PartSupp is a relational table, PS_PARTKEY and PS_SUPPKEY are foreign key constraints and point to the PART table and SUPPLIER table respectively.

[0018] Furthermore, a dynamic pruning strategy is implemented to synchronously inject the costs of the Expand operator and the Join operator into the cost model. High-cost branches are quickly eliminated through a hybrid cost evaluation framework, and the execution plan with the lowest global cost is finally selected as the detection result.

[0019] Furthermore, a multi-table joint query plan may contain a hybrid structure of traditional Join operators and Expand operators, forming a heterogeneous execution plan tree of relational operators and graph query operators.

[0020] Beneficial effects: Compared with the existing technology, the present invention has the following significant advantages: (1) The multi-table join order is initialized based on graph topology constraints, and the search space of the table join order is reduced from the full permutation to the graph constraint subset, which significantly shortens the detection convergence path of the optimal table order; (2) The adjacency operator Expand and the traditional Join operator are incorporated into a unified cost model, in which the adjacency operator eliminates random I / O access by presetting the adjacency index, and realizes the linear time complexity execution of multi-table join; (3) The hybrid query plan dynamically arranges the two types of operators, combines the versatility of relational Join and the directional acceleration advantage of adjacency traversal, and can adapt to star queries with local graph associations, chain path queries and mixed load OLAP scenarios, and has cross-model optimization capabilities. BRIEF DESCRIPTION OF THE DRAWINGS

[0021] Figure 1 This is a diagram illustrating the relationship between the PART table and the SUPPLIER table of the present invention.

[0022] Figure 2 This is a diagram illustrating the relationship between the Nation table and the Supplier table of the present invention. DETAILED DESCRIPTION

[0023] A relational multi-table join optimization method includes the following steps:

[0024] Step 1: The user sends a query statement for multi-table joins. Based on the pre-built inter-table graph structure, the query is checked to see if there is an edge table. If so, the entity table ID associated with the edge table is used to determine whether a graph query can be constructed.

[0025] Step 2: Dynamically generate adjacency query operators for tables that conform to the graph query structure. The Expand operator directly locates cross-table associated data through a preset adjacency index, achieving linear acceleration of multi-table join paths. Simultaneously, the optimizer generates the execution cost of the Expand operator.

[0026] Step 3: Execute the dynamic pruning strategy and selectively evaluate whether to continue exploring the execution plan of the Join operator based on the known cost of the Expand operator. The optimizer simultaneously injects the costs of the Expand operator and the Join operator into the cost model. Through the hybrid cost evaluation framework, high-cost branches are quickly eliminated, and the execution plan with the lowest global cost is finally selected as the detection result.

[0027] First, the ER model of the TPCH model is given, which consists of the Part table, Supplier table, and PartSupp table. Part and Supplier are entity tables, PartSupp is a relationship table, PS_PARTKEY and PS_SUPPKEY are foreign key constraints and point to the PART table and SUPPLIER table respectively. The simplified table structure and its relationship are as follows: Figure 1 shown.

[0028] First of all, it should be noted that this solution only optimizes the architecture of the relational database kernel and does not require modification of the existing SQL syntax specifications. During SQL statement processing, the database needs to optimize tables that conform to graph structure attributes and maintain the graph attribute tag status in the table metadata. The graph attribute tags of each table include three types: ordinary table, vertex table and edge table. The initial tag of a newly created table is set to ordinary table by default. Figure 1 Describe the table structure construction optimization process in the database initialization state:

[0029] Step 1: The user creates the Part and Supplier tables using the CREATE statement. At this point, two independent tables exist in the database. Because there is no foreign key relationship between the Part and Supplier tables, the system determines that they do not have graph structure characteristics.

[0030] Step 2: The user creates the PartSupp table and associates the Part table with the Supplier table using two foreign key constraint fields. The database automatically detects whether the entity table (Part / Supplier) and the relationship table (PartSupp) associated by the foreign key constraint meet the following graph structure generation conditions:

[0031] (1) The three tables conform to the topological structure of the entity-relationship model (ER Model);

[0032] (2) The relationship between tables must satisfy the 1:N:1 cardinality constraint and conform to the definition of graph structure attributes;

[0033] (3) The current graph attribute of the entity table is marked as a normal table or a point table, and the relationship table is marked as a normal table.

[0034] If the verification passes, the system upgrades the graph attribute marks of the Part table and Supplier table to vertex tables, marks the PartSupp table as an edge table, and persistently stores the unique identifier of the associated entity table in the metadata of the PartSupp table.

[0035] Step 3: For the table marked as a point table, a point array adjacency index is constructed. This index dynamically records the relationship chain of the latest adjacency edge table. For the edge table, an edge array index is constructed to solidify the topological relationship. It is important to note that when a table already contains historical data (for example, if the PS_PARTKEY foreign key constraint is appended later), the system must synchronously or asynchronously scan the entire associated table data to construct the adjacency index to ensure strong consistency between the graph structure data and the business data.

[0036] In addition to dynamically parsing the associations between tables to build a graph structure during the table creation phase, the association information must also be incrementally written to the preset adjacency index when data is inserted to achieve persistent recording of cross-table data topology. In most relational database systems, the rowid column serves as a built-in physical address identifier, and its application mechanism is as follows: (1) When inserting point data, the system automatically generates the point data rowid (denoted as rowid-v) and writes it into the point array index. At this time, no other table data has established an adjacency association with the rowid-v, so the last adjacency relationship field corresponding to rowid-v in the point array can be initialized to a special marker value (such as NULL). (2) When inserting edge data, the system first generates the edge data rowid (denoted as rowid-e), and then performs the following operations:

[0037] (a) Foreign key constraint verification: Use the transaction-level lock mechanism to check whether the foreign keys of the edge table (such as PS_PARTKEY and PS_SUPPLIERKEY) satisfy the referential integrity constraints of the associated entity table;

[0038] (b) Adjacency information extraction: If the verification passes, parse the point table data associated with the foreign key and obtain the corresponding source point rowid (rowid-v1) and destination point rowid (rowid-v2);

[0039] (c) Index linkage update: Synchronously write the triplet (rowid-e, rowid-v1, rowid-v2) into the edge array index, and update the last adjacency field to rowid-e in the point array index corresponding to rowid-v1 and rowid-v2 respectively.

[0040] Through the above mechanism, the graph structure topology and adjacency relationships of cross-table data are fully represented. However, the multi-table join optimizer in traditional relational databases still uses an optimization strategy based on two-table join units. It generates candidate execution plans by enumerating table order combinations and selecting Join operators, and selects the optimal solution based on a cost estimation model. For example, there are six possible combinations of the Part table, Supplier table, and PartSupp table, as shown in Table 1:

[0041] Table 1 Part table, Supplier table and PartSupp table have 6 combinations

[0042]

[0043]

[0044] There are many join operators between two tables, such as Hashjoin and Indexjoin. Therefore, we can assume that the physical plan tree for the join order of Supplier, PartSupp, and Part is the following left-deep plan tree:

[0045]

[0046] Traditional join operators are based on cascading calculations between two tables. For example, when starting from the supplier table to link the parts table, it is necessary to perform a table return operation through the parts supply table and build a hash table in the Hash Join operator to cache the intermediate results. However, this solution optimizes the table join order through graph structure pre-sorting and introduces an adjacency index-driven Expand operator to achieve triple optimization: eliminating the hash table construction overhead (directly locating related data through adjacency indexes), avoiding secondary index backtracking (using adjacency relationships to achieve linear data access), and reducing algorithm complexity (from O(N) to O(N)). 2) cascade matching is reduced to O(N) adjacency traversal), thereby reconstructing the multi-table join execution paradigm while ensuring query logical equivalence. The multi-table join optimization steps of the present invention are as follows:

[0047] Step 1: The user sends a query statement for a multi-table join. Based on the pre-built inter-table graph structure, the query is checked for edge tables. If an edge table exists, the ID of the entity table associated with the edge table is used to determine whether a graph query can be constructed. For example, when a user queries the Supplier, Part, and PartSupp tables, the system analyzes the graph structure and discovers the topological relationship: Supplier → PartSupp ← Part. Based on this, the initial table order [Supplier, PartSupp, Part] is generated. Because this order incorporates the topological weight information of the graph structure, its initial execution cost is significantly lower than that generated by traditional sorting algorithms (for example, the cost of the Supplier-PartSupp-Part order is 5, while the cost of the Part-Supplier table combination in the traditional Join algorithm is already 5, and adding the PartSupp table will inevitably increase the cost to more than 5). Therefore, table join orders starting with Part-Supplier can be directly eliminated. This reduces the execution plan search space from the full permutation to a subset within the graph constraints, thereby accelerating the generation of the optimal execution plan. When the query involves enough tables with graph associations (for example, the number of tables with chain associations is ≥ 3), the initial table order is often the topologically optimal solution with the lowest cost.

[0048] Step 2: Dynamically generate adjacency query operators for tables that conform to the graph query structure. The Expand operator directly locates cross-table associated data through pre-set adjacency indexes, achieving linear acceleration of multi-table join paths. Simultaneously, the optimizer generates the execution cost of the Expand operator. For example, the cost of the Expand operator is 4. The following is an example of a typical execution plan tree structure using the Expand operator:

[0049] Project (top projection)

[0050] └──Expand(Supplier→PartSupp←Part)

[0051] └──Supplier (Supplier table)

[0052] Step 3: Execute a dynamic pruning strategy, selectively evaluating whether to continue exploring the traditional Join operator's execution plan based on the known cost of the Expand operator (for example, the Expand operator's cost is 4). The optimizer simultaneously injects the costs of the Expand and Join operators into the cost model (for example, comparing Expand (cost = 4) with HashJoin (cost = 6)). Using the hybrid cost evaluation framework, it quickly eliminates high-cost branches and ultimately selects the execution plan with the lowest global cost as the exploration result. Therefore, a multi-table join query plan may contain a hybrid structure of traditional Join and Expand operators (for example, Project → Expand → HashJoin → Scan), forming a heterogeneous execution plan tree of relational and graph query operators.

[0053] Special Note: In traditional OLAP scenarios, even if the query involves tables that only partially satisfy the graph structure constraints (for example, only five of ten tables form a chain topology of vertex table → edge table ← vertex table → edge table ← vertex table), this method can still achieve search space decomposition by generating local Expand operators. For this example, the system generates two independent Expand adjacency operators (e.g., Expand(A→B←C) and Expand(C→D←E)) for the five tables that conform to the graph structure. This reduces the original 10! full permutation search space to a Cartesian product of multiple subgraph search spaces, preserving the ability for progressive optimization even when partial graph structure is available.

[0054] The TPCH model is a benchmark model commonly used by relational databases to test OLAP performance. Figure 1 Based on the above, the Nation table is supplemented and the present invention is further described in detail in combination with the embodiments. It should be understood that the specific embodiments described here are only used to explain the present invention, not to limit the present invention. The relationship between the Nation table and the Supplier table is as follows: Figure 2 shown.

[0055] Example 1: Construction of graph structure and insertion of data:

[0056] S101. Create the Supplier, PartSupp, Part, and Nation tables and set the primary keys of the tables. Then, add foreign keys to Supplier and PartSupp respectively. The foreign key processing method is the same as S102.

[0057] S102, create the foreign key constraint between Supplier and Nation table, because the foreign key constraint is only one, can not constitute three table association, do not carry on the graph structure analysis; create the foreign key constraint of PartSupp and Part, Supplier table, two foreign key constraints constitute three table association, then analyze whether it can constitute graph structure, the association relationship and state of table meet the requirements, set the graph attribute of Part and Supplier table as point table, the graph attribute of PartSupp table as edge table, and record the id of Part and Supplier table in the data dictionary of PartSupp table.

[0058] S103, create the point array index in adjacency index for Part and Supplier table with graph attribute as point table, create the edge array index in adjacency index for PartSupp table with graph attribute as edge table.

[0059] S104, insert data into Supplier, PartSupp, Part and Nation table respectively, PartSupp as edge table, data is inserted after Supplier and Part table. When inserting each data of PartSupp, get the corresponding rowid in Part and Supplier table according to PS_PartKey and PS_SuppKey column, recorded as rowid_part and rowid_supp, then apply for the rowid of itself record, recorded as rowid_ps.

[0060] S105, set the rowid_ps of edge into the point array index of point table Supplier and Part table respectively. Then set the rowid_part and rowid_supp of point into the edge array index of edge table PartSupp, as the two end points of edge.

[0061] S106, repeat S104 and S105 for data insertion, if S102 is executed after data insertion, then need to traverse PartSupp data in sequence and then execute S104 to find rowid, finally execute S105 to complete the construction of adjacency index.

[0062] Example two: optimization of multi-table connection and generation of mixed execution plan:

[0063] S201, user executes multi-table connection query statement similar to (Select * From Nation, PartSupp, Part, Supplier).

[0064] S202: Analyze whether a graph structure exists in the query table. PartSupp is an edge table, and the vertex table IDs recorded in the dictionary correspond to the Supplier and Part tables, respectively. In the case of multiple tables, a complete structure graph can be generated based on other optimization rules, such as the longest path. Based on the structure graph, the tables are initialized in the order of Supplier, PartSupp, Part, and Nation.

[0065] S203. Generate an adjacency query operator for the tables that satisfy the structure graph. For Supplier, PartSupp, and Part, generate an adjacency query operator, Expand (Supplier → PartSupp ← Part), and calculate the adjacency operator cost, denoted as cost_expand. A traditional optimizer is used to generate a Join operator for the multiple tables in the adjacency query operator. During the optimization process, the Join operator cost, cost_join, is compared with cost_expand to accelerate the generation of the Join execution plan. Finally, the execution plan with the lowest cost is selected as the optimal plan for the Supplier, PartSupp, and Part tables. Generally, the optimal plan is still the Expand operator.

[0066] S204: Use the traditional optimizer to generate Join operators for structured and unstructured graphs, search for the minimum cost and generate an execution plan. For example, combine the Expand operator generated in S203 with Nation. The optimizer selects the Join method in the order of Expand and Nation, or Nation and Expand, and obtains the optimal plan by comparing the costs. Finally, the following hybrid execution plan can be generated.

[0067]

[0068] Correspondingly, a relational multi-table join optimization system includes: a multi-table join query module, an adjacency query operator generation module and a multi-table join optimization module; the multi-table join query module detects whether there is an edge table in the query based on a pre-built inter-table graph structure, and if there is an edge table, determines whether a graph query can be constructed based on the entity table ID associated with the edge table; the adjacency query operator generation module dynamically generates adjacency query operators for tables that conform to the graph query structure, wherein the Expand operator directly locates cross-table associated data through a preset adjacency index, thereby realizing linear acceleration of the multi-table join path, and the optimizer generates the execution cost of the Expand operator at the same time; the multi-table join optimization module executes a dynamic pruning strategy, selectively evaluates whether to continue exploring the execution plan of the Join operator based on the known cost of the Expand operator, and the optimizer simultaneously injects the costs of the Expand operator and the Join operator into the cost model, quickly eliminates high-cost branches through a hybrid cost evaluation framework, and finally selects the execution plan with the lowest global cost as the detection result.

Claims

1. A relational multi-table join optimization method, characterized in that: The steps include: Step 1: The user sends a query statement for multi-table joins. Based on the pre-built inter-table graph structure, the query is checked to see if there is an edge table. If so, the entity table ID associated with the edge table is used to determine whether a graph query can be constructed. Step 2: Dynamically generate adjacency query operators for tables that conform to the graph query structure. The Expand operator directly locates cross-table associated data through a preset adjacency index, achieving linear acceleration of multi-table join paths. Simultaneously, the optimizer generates the execution cost of the Expand operator. Step 3: Execute the dynamic pruning strategy and selectively evaluate whether to continue exploring the execution plan of the Join operator based on the known cost of the Expand operator. The optimizer simultaneously injects the costs of the Expand operator and the Join operator into the cost model. Through the hybrid cost evaluation framework, high-cost branches are quickly eliminated, and the execution plan with the lowest global cost is finally selected as the detection result.

2. The relational multi-table join optimization method according to claim 1, characterized in that: In step 1, the pre-built table graph structure includes the Part table, Supplier table, and PartSupp table, where Part and Supplier are entity tables, PartSupp is a relationship table, PS_PARTKEY and PS_SUPPKEY are foreign key constraints and point to the PART table and SUPPLIER table, respectively.

3. The relational multi-table join optimization method according to claim 1, wherein: In step 1, the pre-construction of the inter-table graph structure includes the following steps: Step 11: The user creates the Part table and the Supplier table using the CREATE statement. At this point, two independent tables exist in the database. Because there is no foreign key relationship between the Part table and the Supplier table, the system determines that they do not have graph structure characteristics. Step 12: The user creates the PartSupp table and associates the Part table with the Supplier table through two foreign key constraint fields. The database automatically detects whether the entity table Part / Supplier and the relationship table PartSupp associated by the foreign key constraint meet the graph structure generation conditions. Step 13: Build an adjacency index of a point array structure for the table marked as the point table. This index dynamically records the relationship chain of the latest adjacency edge table. For the edge table, build an edge array index to solidify the topological relationship. When the table already has historical data, the system needs to scan the associated table data in full in a synchronous or asynchronous manner to build an adjacency index to ensure strong consistency between the graph structure data and the business data.

4. The relational multi-table join optimization method according to claim 1, wherein: In step 3, a dynamic pruning strategy is executed to synchronously inject the costs of the Expand operator and the Join operator into the cost model. High-cost branches are quickly eliminated through the hybrid cost evaluation framework, and the execution plan with the lowest global cost is finally selected as the detection result.

5. The relational multi-table join optimization method according to claim 1, wherein: In step 3, the multi-table joint query plan may contain a hybrid structure of traditional Join operators and Expand operators, forming a heterogeneous execution plan tree of relational operators and graph query operators.

6. A system for implementing the relational multi-table join optimization method according to claim 1, characterized in that: include: Multi-table join query module, adjacency query operator generation module and multi-table join optimization module; The multi-table join query module detects whether there is an edge table in the query based on the pre-built inter-table graph structure. If there is an edge table, it determines whether a graph query can be constructed based on the entity table ID associated with the edge table; the adjacency query operator generation module dynamically generates adjacency query operators for tables that conform to the graph query structure. The Expand operator directly locates cross-table related data through preset adjacency indexes, achieving linear acceleration of multi-table join paths. At the same time, the optimizer generates the execution cost of the Expand operator; the multi-table join optimization module implements a dynamic pruning strategy, selectively evaluating whether to continue exploring the execution plan of the Join operator based on the known cost of the Expand operator. The optimizer simultaneously injects the costs of the Expand operator and the Join operator into the cost model, quickly eliminates high-cost branches through a hybrid cost evaluation framework, and finally selects the execution plan with the lowest global cost as the detection result.

7. The relational multi-table join optimization system according to claim 6, characterized in that: The pre-built table graph structure includes the Part table, Supplier table, and PartSupp table. Part and Supplier are entity tables, PartSupp is a relationship table, PS_PARTKEY and PS_SUPPKEY are foreign key constraints and point to the PART table and SUPPLIER table, respectively.

8. The relational multi-table join optimization system according to claim 6, characterized in that: A dynamic pruning strategy is implemented to synchronously inject the costs of the Expand operator and the Join operator into the cost model. High-cost branches are quickly eliminated through a hybrid cost evaluation framework, and the execution plan with the lowest global cost is ultimately selected as the detection result.

9. The relational multi-table join optimization system according to claim 6, wherein: A multi-table joint query plan may contain a hybrid structure of traditional Join operators and Expand operators, forming a heterogeneous execution plan tree of relational operators and graph query operators.

Images