Database connection sequence optimization method and device based on Bloom filter

Abstract

The invention relates to a database connection sequence optimization method and device based on a bloom filter. The method comprises the following steps: in response to multi-table connection query, constructing a weighted connection graph which takes data tables as nodes and connection conditions as edges, and calculating filtering potential weights of the edges based on cardinal numbers and selectivity of each table; generating a maximum spanning tree, determining a root node, forming a directed filtering scheduling tree, and further generating a multi-stage bloom filter scheduling plan; the execution engine constructs a Bloom filter stage by stage from bottom to top according to the plan and detects and filters a downstream table connection key to realize global data pruning; and finally, performing connection operation on the filtered data set according to an original connection sequence. According to the method, through a pre-filtering mechanism independent of a connection sequence, the performance cliff caused by a cardinal number estimation error is eliminated, the scale of intermediate results is greatly reduced, memory overflow and sudden performance drop are avoided, the robustness and resource efficiency of complex query are remarkably improved, and the execution stability is guaranteed.

Description

Technical Field

[0001] This application relates to the field of database query optimization technology, and in particular to a method, apparatus, computer-readable storage medium, and electronic device for optimizing database join order based on Bloom filters. Background Technology

[0002] In modern data processing systems, multi-table joins are a core operation in relational databases (such as PostgreSQL and Oracle) and distributed big data query engines (such as Spark SQL, Presto, and Impala), and their execution efficiency directly determines the performance of queries. The ability of the query optimizer to generate efficient execution plans is crucial, and the core of the execution plan lies in optimizing the join order.

[0003] Current mainstream query optimizers generally adopt a cost-based optimization (CBO) strategy to select the join order, and its core process includes the following steps: 1. Statistical Information Collection: The system pre-collects and maintains statistical information of the data tables, including basic features such as the total number of rows in the table (cardinality), the number of distinct values ​​in each column (distinct values), histograms of value distribution, and the proportion of null values; 2. Cardinality estimation: During the query planning phase, the optimizer uses the above statistical information to estimate the size of the intermediate result set generated by filtering, joining, and other operations through a complex mathematical model; 3. Cost Model and Plan Enumeration: The optimizer enumerates various possible connection orders (such as left deep tree, right deep tree, Busy tree, etc.) and evaluates the execution cost of each plan through a cost model that integrates CPU, I / O, and network overhead. 4. Optimal plan selection: The execution plan with the lowest estimated cost is finally selected and put into actual execution.

[0004] Furthermore, to improve execution layer efficiency, some advanced systems (especially distributed systems) have introduced dynamic filtering or runtime filtering techniques. A typical scenario is a star schema query (fact table JOIN dimension table). The system builds a filter (such as a hash set of the join key or a Bloom filter) on one end of the join (usually the smaller table, i.e., the dimension table) and pushes this filter to the other end (the larger table, i.e., the fact table). During fact table scanning, this filter can be used to preemptively remove rows that cannot match, essentially achieving initial control over the size of intermediate results through the pushdown of a semi-join.

[0005] However, existing technical solutions still have the following key shortcomings when facing complex query scenarios: (1) Cardinality estimation error leads to "performance cliff": The core limitation of CBO lies in the insufficient robustness of the cardinality estimation model. When the data is skewed, has multiple strong correlations, or has complex filtering predicates (such as LIKE fuzzy matching, user-defined functions UDF), the estimation results driven by statistical information may deviate by orders of magnitude from the actual size of the intermediate result set. Incorrect cardinality estimation can mislead the cost model, causing the optimizer to choose an execution plan with theoretically low cost but extremely poor actual performance. For example, if the optimizer underestimates the size of an intermediate result set, it may prioritize the join order that generates the result, eventually exhausting memory / CPU resources due to the "explosion" of intermediate results, causing a cliff-like drop in query performance.

[0006] (2) The search space for the connection order explodes, making it difficult to guarantee the global optimum: The potential order of N table connections grows in factorial order. When the number of tables exceeds 10-15, the computational complexity of exact search algorithms such as dynamic programming becomes unacceptable. The optimizer is forced to adopt greedy or heuristic search, which can only obtain local optimum solutions and may miss the global optimum connection order.

[0007] (3) The traditional semi-connection implementation is too expensive: Although the semi-connection can theoretically control the size of the intermediate results, its traditional implementation (such as building a complete hash table for each connection edge) is itself expensive. The construction of the hash table consumes a lot of memory, and broadcasting the complete hash table in a distributed environment will generate huge network overhead, which limits its application as a universal and robust guarantee mechanism.

[0008] (4) Existing dynamic filtering mechanisms lack global coordination: Current dynamic filtering technologies are mostly optimized for single join edges (such as point-to-point filtering between the fact table and dimension table in a star schema). For multi-table chained or tree-structured joins (such as A JOIN B JOIN C JOIN D), such mechanisms cannot form a global, multi-stage filtering chain, thus limiting the filtering effect. For example, data from table A cannot be directly used to pre-filter table C, resulting in fragmented control over the scale of intermediate results.

[0009] In summary, there is an urgent need to develop a low-overhead, systematic, and global pre-filtering mechanism as a supplement and "insurance" to CBO, so as to ensure that the size of intermediate results remains controllable even in scenarios such as cardinality estimation errors and the optimizer selecting the wrong join order, thereby improving the performance robustness of complex queries. Summary of the Invention

[0010] To overcome the aforementioned deficiencies in existing technologies, this application proposes a novel database join order optimization method based on Bloom filters. This invention aims to address the problem of drastic performance degradation or even crashes (i.e., "performance cliffs") in relational databases and big data systems caused by the query optimizer selecting a suboptimal join order.

[0011] The main technical strategy adopted in this invention is to systematically construct a Bloom filter chain based on the maximum spanning tree before the physical execution stage of multi-table joins, so as to realize multi-stage, cascaded pre-filtering and remove invalid data in each table on a large scale before the formal join begins.

[0012] Specifically, this application provides the following technical solutions: The first aspect of this application provides a database connection order optimization method based on a Bloom filter, the method comprising: S1. In response to multi-table join query requests, the query optimizer generates an initial physical execution plan; S2. Construct a weighted connection graph, wherein each data table participating in the connection is a node and the connection condition is an edge, and the filtering potential weight of each edge is calculated based on the cardinality and selectivity of each table. S3. Generate a maximum spanning tree based on the weighted connection graph, determine the root node of the maximum spanning tree and generate a directed filter scheduling tree, and then generate a multi-stage Bloom filter scheduling plan based on the directed filter scheduling tree. S4. According to the scheduling plan, the construction and probing operations of the Bloom filter are performed from bottom to top in stages. While scanning each data table, the Bloom filter of the current stage is used to filter the join keys of the downstream table to generate a globally pruned filtered dataset. S5. On the filtered dataset, perform join operations according to the original join order in the initial physical execution plan to generate query results.

[0013] Furthermore, in step S2 of this application, The node is defined as each data table involved in the query request; The edge is defined as the association between data tables established through join conditions (such as equi-join predicates); The filtering potential weight of each edge in the weighted connection graph is determined based on the cardinality of the data tables at both ends of the edge after predicate filtering. The higher the cardinality of the source table where the connection key is located, the greater the weight of the corresponding edge.

[0014] Furthermore, in the method of this application, the generation of the maximum spanning tree includes: (1) Select the edge with the highest filtering potential weight and add it to the spanning tree until all nodes are connected; (2) Take the data table with the largest cardinality in the spanning tree as the root node and determine the bottom-up filtering scheduling order.

[0015] Furthermore, in the method of this application, the root node is determined to be the table with the largest cardinality among the data tables participating in the connection, and the edge direction of the directed filtering scheduling tree points from the upstream filtering table to the downstream filtered table.

[0016] Furthermore, in the method of this application, the scheduling plan is generated by performing a post-order traversal of the directed filtering scheduling tree, wherein the post-order traversal order is defined as the traversal order from the leaf node to the root node.

[0017] Furthermore, step S4 of the method in this application includes: S41. Scan the first data table, extract the join key values ​​after filtering conditions, and construct the first Bloom filter; S42. Scan the second data table in parallel, extract its join key values ​​and probe the first Bloom filter, insert the join key values ​​that pass the probe into the second Bloom filter, and discard the data rows that fail the probe. S43. Scan the downstream data tables sequentially, using the Bloom filter built in the previous stage to probe the join keys of this table, filtering and passing on valid join key information at each level until the scanning and filtering of all tables is completed.

[0018] Furthermore, in the method of this application, the Bloom filter construction and table scan tasks at each stage are independently scheduled and executed in a parallel operator manner.

[0019] A second aspect of this application provides a database join order optimization apparatus based on a Bloom filter, the apparatus comprising: The execution plan generation module is used to respond to multi-table join query requests, and the query optimizer generates the initial physical execution plan. The weighted connection graph construction module is used to construct a weighted connection graph, which uses each data table participating in the connection as a node and the connection condition as an edge, and calculates the filtering potential weight of each edge based on the cardinality and selectivity of each table. The scheduling plan generation module is used to generate a maximum spanning tree based on a weighted connection graph, determine the root node of the maximum spanning tree and generate a directed filtering scheduling tree, and then generate a multi-stage Bloom filter scheduling plan based on the directed filtering scheduling tree. The pre-filtering execution module is used to perform the construction and probing operations of Bloom filters from bottom to top in stages according to the scheduling plan. While scanning each data table, the Bloom filter of the current stage is used to filter the join keys of downstream tables to generate a globally pruned filtered dataset. The join execution module is used to perform join operations on the filtered dataset according to the original join order determined by the query optimizer, and generate query results.

[0020] The device implements the steps of the aforementioned database connection order optimization method based on Bloom filters during operation.

[0021] A third aspect of this application provides an electronic device, including: a memory and a processor; Memory: Used to store computer programs; Processor: Used to execute the computer program to implement the steps of the aforementioned database connection order optimization method based on Bloom filters.

[0022] A fourth aspect of this application provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the aforementioned database connection order optimization method based on a Bloom filter.

[0023] In summary, compared with existing technologies, this invention offers the following advantages for complex query scenarios through a pre-filtering mechanism independent of the join order: (1) Strong robustness: Eliminate the "performance cliff", ensure worst-case performance, and improve the stability of query performance. To address the performance slump caused by the optimizer selecting the wrong join order due to cardinality estimation errors in the CBO (Cost-Based Optimizer), this solution employs a multi-table joint pre-filtering approach. This pre-filtering performs cross-table effective data screening on participating tables (e.g., A / B / C) independently of the final join order, significantly reducing the input size of subsequent join operations. Regardless of the optimizer's chosen join order (e.g., A→B→C or C→B→A), pre-filtering keeps the intermediate result size within a reasonable range, ensuring that query execution time, which could potentially take hours or fail due to resource exhaustion, is stably reduced to minutes or seconds, providing a "crash-proof" baseline for complex queries.

[0024] (2) Improve efficiency in multiple dimensions: reduce resource consumption and improve execution efficiency CPU computational overhead reduction: The pre-filtering stage achieves efficient data pruning through Bloom filters, which reduces the number of tuples that need to be processed in subsequent join operations (especially hash joins) by an order of magnitude (the amount of input data is reduced by 90%), directly reducing the amount of instructions executed in the hash table construction and probing stages; Memory usage optimization: The size of the compressed intermediate result set is significantly reduced, allowing memory data structures such as hash tables to be built within a limited memory space, effectively avoiding memory overflow (OOM) errors caused by the expansion of intermediate results and improving the query success rate; I / O and network transmission burden reduction: The pre-filtered small-scale intermediate result set can reduce the number of temporary files written to disk and the amount of data. In a distributed environment, the amount of data transferred between nodes through shuffle is reduced accordingly, which significantly reduces disk I / O load and network bandwidth pressure.

[0025] In summary, this invention focuses on low-overhead pre-filtering to achieve the dual goals of "enhanced stability" and "improved efficiency," providing more reliable execution guarantees for complex queries.

[0026] Other features and advantages of this application will be set forth in detail in the following description, or will become apparent through the implementation of the relevant technical solutions of this application. The objectives and other advantages of this application can be achieved through the technical features and means explicitly pointed out in the description, claims, and drawings, and will be obtained through the implementation of these technical contents. Attached Figure Description

[0027] To more clearly illustrate the technical solutions of the embodiments of this application, the accompanying drawings involved in the description of the embodiments will be briefly introduced below. It should be noted that the accompanying drawings only show some embodiments of this application. For those skilled in the art, other related drawings can be derived from these drawings without creative effort.

[0028] Figure 1 This is a flowchart illustrating the overall implementation of the database connection order optimization method based on Bloom filters in this application.

[0029] Figure 2 This is a flowchart illustrating the execution of an embodiment of the present application.

[0030] Figure 3 This is a structural diagram of the database connection order optimization device based on Bloom filters in this application.

[0031] Figure 4 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

[0032] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. It should be noted that the described embodiments are only some embodiments of this application, and not all embodiments. All other embodiments obtained by those skilled in the art based on the embodiments of this application without creative effort are within the protection scope of this application.

[0033] In this document, the term "comprising" and any variations thereof (such as "including," "including," etc.) are open-ended expressions and should be understood as "including but not limited to," meaning that the listed content is not exhaustive and may include other content not explicitly mentioned. The term "based on" should be understood as "at least partially based on," meaning that the basis or condition referred to may not be the only factor and may involve other relevant factors. The term "one embodiment" should be understood as "at least one embodiment," meaning that the described embodiment is not the only possible implementation, and other similar embodiments may exist.

[0034] In this application, the terms "a" and "a plurality of" are used to modify related elements or features, and their expression is illustrative rather than restrictive. Unless otherwise expressly stated in the context, "a" should be understood as "at least one," and "a plurality of" should be understood as "at least two." Those skilled in the art should reasonably interpret these terms based on the semantic and logical relationships of the context to ensure that they cover the possibility of "one or more."

[0035] Figure 1 The diagram shows the overall implementation flow of the database join order optimization method based on Bloom filters provided in this application, including the following steps: S1. In response to multi-table join query requests, the query optimizer generates an initial physical execution plan; S2. Construct a weighted connection graph, wherein each data table participating in the connection is a node and the connection condition is an edge, and the filtering potential weight of each edge is calculated based on the cardinality and selectivity of each table. S3. Generate a maximum spanning tree based on the weighted connection graph, determine the root node of the maximum spanning tree and generate a directed filter scheduling tree, and then generate a multi-stage Bloom filter scheduling plan based on the directed filter scheduling tree. S4. According to the scheduling plan, the construction and probing operations of the Bloom filter are performed from bottom to top in stages. While scanning each data table, the Bloom filter of the current stage is used to filter the join keys of the downstream table to generate a globally pruned filtered dataset. S5. On the filtered dataset, perform join operations according to the original join order in the initial physical execution plan to generate query results.

[0036] To more clearly illustrate the technical solution of this application, the following will provide further explanation through specific scenario embodiments.

[0037] Scene description: This example uses the openGauss database and is based on a typical e-commerce data analysis scenario. Assume we need to analyze customer order details for a specific market region. The SQL query is as follows: SELECT c.c_name, o.o_totalprice, l.l_quantity FROM Customer AS c, Orders AS o, Lineitem AS l WHERE c.c_custkey = o.o_custkey -- (join condition 1) AND o.o_orderkey = l.l_orderkey -- (Connection condition 2) AND c.c_mktsegment = 'AUTOMOBILE'; -- (filter condition) Data table overview: (1) Customer (customer table): approximately 1.5 million rows.

[0038] (2) Orders: Approximately 150 million rows.

[0039] (3) Lineitem (order item table): approximately 600 million rows.

[0040] This is a typical join between large and small tables.

[0041] Figure 2 The following is the execution flow of the present invention, which includes the following processes: Step 1: Query Optimization and Pre-Filtering Plan Generation 1. Standard Optimization: A user's SQL query first enters the query optimizer. Based on outdated or inaccurate statistics, the optimizer may generate a suboptimal join order, for example... This is a terrible plan because it joins the two largest tables first, which is very likely to cause intermediate results to explode.

[0042] 2. Start the pre-filter plan generator: When generating the physical execution plan, the system recognizes that this is a multi-table join query and calls the pre-filter plan generator of this invention.

[0043] 3. Construct a weighted connectivity graph: (1) Nodes: Customer, Orders, Lineitem.

[0044] (2) Edges: e1(Customer, Orders) and e2(Orders, Lineitem).

[0045] (3) Calculate the weights (filtering potential): For e1, the filtering occurs on the Orders table, and its potential is related to the selectivity of the Customer table. After the Customer table is filtered by c.c_mktsegment = 'AUTOMOBILE', the remaining number of rows Card(C_filtered) is assumed to be approximately 300,000.

[0046] W_e1 = Card(C_filtered) ≈ 300,000 (here the weight is simplified to the base number of the filtering source).

[0047] For e2, filtering occurs on the Lineitem table, and its potential is related to the cardinality of the Orders table. Card(Orders) has 150 million.

[0048] W_e2 = Card(Orders) = 150 million.

[0049] Clearly, W_e2 > W_e1, meaning that the potential benefits of using the Orders table to filter the Lineitem table are far greater than those of using the Customer table to filter the Orders table.

[0050] 4. Generate the Maximum Spanning Tree (MST) and scheduling plan: (1) Include the edges e2 and e1 with the largest weights to form a tree of Customer - Orders - Lineitem.

[0051] (2) Select the table with the largest cardinality, Lineitem, as the root node.

[0052] (3) Form a directed filtering scheduling tree: Customer → Orders → Lineitem.

[0053] (4) Generate a scheduling plan based on the post-order traversal of the tree (bottom-up): Task 1: Construct a Bloom filter BF_C based on the join key (c_custkey) of the Customer table.

[0054] Task 2: Probe the Orders table using BF_C and construct a Bloom filter BF_O based on the join key (o_orderkey) of the probed Orders rows.

[0055] Task 3: Use BF_O to probe the Lineitem table.

[0056] Step 2: Query the specific implementation of the execution engine Based on the aforementioned scheduling plan and the optimizer's original connection plan, the query execution engine generates a new physical execution plan that integrates pre-filtering.

[0057] 1. Phase One: Bloom Filter Construction and Cascade Detection (Pre-filtering Stage) (1) Execute Task 1 (Build BF_C): a) Start a scan task to read the Customer table.

[0058] b) During the scanning process, apply the WHERE c.c_mktsegment = 'AUTOMOBILE' filter condition.

[0059] c) For each row that passes the filter, extract its c_custkey value and insert it into a Bloom filter BF_C created in memory.

[0060] d) Assuming there are 300,000 customers after filtering, BF_C is built and ready.

[0061] (2) Execute Task 2 (probe Orders and build BF_O): a) Start another scan task to read the Orders table. This task can be executed in parallel with the previous task.

[0062] b) For each row read from the Orders table, extract its o_custkey value.

[0063] c) Use o_custkey to detect BF_C.

[0064] d) If the probe result is True (indicating that the customer of the order may be the target customer), then extract the o_orderkey of the line and insert it into the second Bloom filter BF_O.

[0065] e) If the probe result is False, the Orders row is immediately discarded and will not be involved in any subsequent calculations.

[0066] f) After the above steps, approximately 80% (assuming) of the order records in the Orders table that do not belong to the target customer are filtered in advance, and BF_O only contains those order keys that may be related to the target customer.

[0067] (3) Perform Task 3 (Detect Lineitem): a) Initiate a scan of the Lineitem table (the largest table).

[0068] b) For each row, extract the l_orderkey value.

[0069] c) Use l_orderkey to probe BF_O.

[0070] d) If the probe result is True, the Lineitem row is passed to the connection operator in the next stage.

[0071] e) If the probe result is False, the Lineitem row is discarded immediately.

[0072] f) Since BF_O is already highly condensed, this step will filter out a huge number of Lineitem records, with a filtering rate of over 99%.

[0073] 2. Phase Two: Perform the Final Join Stage At this point, the data stream entering the actual connection operator is no longer the original, massive data table, but a dataset that has been "purified" and greatly reduced.

[0074] The execution engine follows the optimizer's original settings. Connect sequentially.

[0075] However, at this point, the sizes of Lineitem_filtered and Orders_filtered are already very small. Even if they are concatenated first, the resulting intermediate result set is completely within a controllable range, avoiding memory overflow and performance bottlenecks.

[0076] Ultimately, the query completed quickly and yielded the correct result.

[0077] Through the above process, this invention transforms a query that might otherwise fail due to an incorrect connection order into an efficient and robust execution process.

[0078] Figure 3 The image shows a database join order optimization device based on a Bloom filter proposed in this application. The device includes: The execution plan generation module is used to respond to multi-table join query requests, and the query optimizer generates the initial physical execution plan. The weighted connection graph construction module is used to construct a weighted connection graph, which uses each data table participating in the connection as a node and the connection condition as an edge, and calculates the filtering potential weight of each edge based on the cardinality and selectivity of each table. The scheduling plan generation module is used to generate a maximum spanning tree based on a weighted connection graph, determine the root node of the maximum spanning tree and generate a directed filtering scheduling tree, and then generate a multi-stage Bloom filter scheduling plan based on the directed filtering scheduling tree. The pre-filtering execution module is used to perform the construction and probing operations of Bloom filters from bottom to top in stages according to the scheduling plan. While scanning each data table, the Bloom filter of the current stage is used to filter the join keys of downstream tables to generate a globally pruned filtered dataset. The join execution module is used to perform join operations on the filtered dataset according to the original join order determined by the query optimizer, and generate query results.

[0079] When the above-mentioned device is in operation, it implements the steps of the database connection order optimization method based on Bloom filter disclosed in this application.

[0080] The flowcharts and block diagrams in the accompanying drawings illustrate possible implementations of apparatus, methods, and computer program products according to various embodiments of this application, including architecture, functionality, and operation. In these figures, each block may represent a module, program segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should be noted that each block in the block diagrams and / or flowcharts, and combinations thereof, can be implemented using either a dedicated hardware-based system or a combination of dedicated hardware and computer instructions to achieve the specified function or operation.

[0081] like Figure 4 As shown, embodiments of this application also disclose an electronic device, including: a processor 310, a communication interface 320, a memory 330 for storing a processor-executable computer program, and a communication bus 340. The processor 310, communication interface 320, and memory 330 communicate with each other via the communication bus 340. The processor 310 executes the executable computer program to implement the steps of the aforementioned database join order optimization method based on a Bloom filter.

[0082] It is understood that, in addition to memory and a processor, this electronic device may also include input devices (such as a keyboard), output devices (such as a display), and other communication modules. These input devices, output devices, and other communication modules all communicate with the processor through I / O interfaces (i.e., input / output interfaces).

[0083] The operations described in this application can be implemented by writing computer program code using one or more programming languages ​​or a combination thereof. The programming languages ​​include, but are not limited to, the following types: Object-oriented programming languages, such as Java, Smalltalk, C++, etc. Conventional procedural programming languages, such as "C" or similar programming languages.

[0084] The execution methods of program code include, but are not limited to: It runs entirely on the user's computer; Part of it executes on the user's computer, and part of it executes on a remote computer; Execute as a standalone software package; It is executed entirely on a remote computer or server.

[0085] In scenarios involving remote computers, the remote computer can connect to the user's computer via any type of network, including but not limited to local area networks (LANs) or wide area networks (WANs). Furthermore, the remote computer can also connect to external computers through an internet service provider, for example, by utilizing the internet for connection.

[0086] Furthermore, this application also discloses a computer-readable storage medium, wherein when the instructions in the computer-readable storage medium are executed by a processor of an electronic device, the electronic device is able to perform the various steps of the database connection order optimization method based on Bloom filters disclosed in this application.

[0087] In the context of this application, a computer-readable storage medium refers to a tangible medium capable of storing computer program code and related data. Specific examples include, but are not limited to, the following: (1) Portable computer disk: such as floppy disks and other removable magnetic storage media.

[0088] (2) Hard disk: including mechanical hard disks and solid-state hard disks and other fixed storage devices.

[0089] (3) Random Access Memory (RAM): A volatile storage medium used for temporary storage of data and program code.

[0090] (4) Read-only memory (ROM): a non-volatile storage medium used to store fixed programs and data.

[0091] (5) Erasable programmable read-only memory (EPROM) or flash memory: non-volatile storage media that supports multiple erasures and reprogrammings.

[0092] (6) Fiber optic storage devices: storage media based on fiber optic technology.

[0093] (7) Portable compact disc read-only memory (CD-ROM): a read-only medium that stores data in the form of an optical disc.

[0094] (8) Optical storage devices: such as DVDs, Blu-ray discs and other storage media based on optical principles.

[0095] (9) Magnetic storage devices: such as magnetic tapes, disks and other storage media based on magnetic principles.

[0096] (10) Any suitable combination of the above: for example, combining multiple storage media to meet different storage needs.

[0097] These computer-readable storage media can be used to store the program code and related data described in this application to support program execution and persistent data storage.

[0098] Specifically, according to embodiments of this application, the processes described in the flowcharts can be implemented as computer software programs. For example, embodiments of this application relate to a computer program product comprising a computer program carried on a non-transitory computer-readable medium. This computer program includes program code for executing the database join order optimization method based on Bloom filters disclosed in this application. When the computer program is executed by a processing device, it can achieve the functions defined in the embodiments of this application.

[0099] While the foregoing discussion contains several specific implementation details, these details should not be construed as limiting the scope of this application. The above description is merely a preferred embodiment of this application and an explanation of the technical principles employed. Those skilled in the art should understand that the scope of this application is not limited to technical solutions formed by specific combinations of the above-described technical features. Furthermore, this application should also cover other technical solutions formed by any combination of the above-described technical features or their equivalents without departing from the foregoing disclosed concept.

[0100] Those skilled in the art should also understand that modifications can be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features, without departing from the spirit and scope of the technical solutions of the embodiments of this application. These modifications or substitutions will not cause the essence of the corresponding technical solutions to deviate from the core spirit and scope of the technical solutions of the embodiments of this application.

Claims

1. A database join order optimization method based on Bloom filters, characterized in that, The method includes: S1. In response to multi-table join query requests, the query optimizer generates an initial physical execution plan; S2. Construct a weighted connection graph, wherein each data table participating in the connection is a node and the connection condition is an edge, and the filtering potential weight of each edge is calculated based on the cardinality and selectivity of each table. S3. Generate a maximum spanning tree based on the weighted connection graph, determine the root node of the maximum spanning tree and generate a directed filter scheduling tree, and then generate a multi-stage Bloom filter scheduling plan based on the directed filter scheduling tree. S4. According to the scheduling plan, the construction and probing operations of the Bloom filter are performed from bottom to top in stages. While scanning each data table, the Bloom filter of the current stage is used to filter the join keys of the downstream table to generate a globally pruned filtered dataset. S5. On the filtered dataset, perform join operations according to the original join order in the initial physical execution plan to generate query results.

2. The method according to claim 1, characterized in that, In step S2, The node is defined as each data table involved in the query request; The edge is defined as the association between data tables established through connection conditions; The filtering potential weight of each edge in the weighted connection graph is determined based on the cardinality of the data tables at both ends of the edge after predicate filtering. The higher the cardinality of the source table where the connection key is located, the greater the weight of the corresponding edge.

3. The method according to claim 1, characterized in that, The generation of the maximum spanning tree includes: (1) Select the edge with the highest filtering potential weight and add it to the spanning tree until all nodes are connected; (2) Take the data table with the largest cardinality in the spanning tree as the root node and determine the bottom-up filtering scheduling order.

4. The method according to claim 1, characterized in that, The root node is determined as the table with the largest cardinality among the data tables participating in the connection, and the edge direction of the directed filtering scheduling tree is from the upstream filtering table to the downstream filtered table.

5. The method according to claim 1, characterized in that, The scheduling plan is generated by performing a post-order traversal of the directed filtering scheduling tree, wherein the post-order traversal order is defined as the traversal order from the leaf node to the root node.

6. The method according to claim 1, characterized in that, Step S4 includes: S41. Scan the first data table, extract the join key values ​​after filtering conditions, and construct the first Bloom filter; S42. Scan the second data table in parallel, extract its join key values ​​and probe the first Bloom filter, insert the join key values ​​that pass the probe into the second Bloom filter, and discard the data rows that fail the probe. S43. Scan the downstream data tables sequentially, using the Bloom filter built in the previous stage to probe the join keys of this table, filtering and passing on valid join key information at each level until the scanning and filtering of all tables is completed.

7. The method according to claim 6, characterized in that, The Bloom filter construction and table scan tasks at each stage are scheduled and executed independently using parallel operators.

8. A database join order optimization device based on a Bloom filter, characterized in that, The device includes: The execution plan generation module is used to respond to multi-table join query requests, and the query optimizer generates the initial physical execution plan. The weighted connection graph construction module is used to construct a weighted connection graph, which uses each data table participating in the connection as a node and the connection condition as an edge, and calculates the filtering potential weight of each edge based on the cardinality and selectivity of each table. The scheduling plan generation module is used to generate a maximum spanning tree based on a weighted connection graph, determine the root node of the maximum spanning tree and generate a directed filtering scheduling tree, and then generate a multi-stage Bloom filter scheduling plan based on the directed filtering scheduling tree. The pre-filtering execution module is used to perform the construction and probing operations of Bloom filters from bottom to top in stages according to the scheduling plan. While scanning each data table, the Bloom filter of the current stage is used to filter the join keys of downstream tables to generate a globally pruned filtered dataset. The join execution module is used to perform join operations on the filtered dataset according to the original join order determined by the query optimizer, and generate query results.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements the steps of the database connection order optimization method based on Bloom filters as described in any one of claims 1-7.

10. An electronic device, characterized in that, include: Memory and processor; Memory: Used to store computer programs; Processor: for executing the computer program to implement the steps of the database connection order optimization method based on Bloom filters as described in any one of claims 1-7.

Images