Join query method, apparatus, device, and program product

Abstract

This application discloses a join query method, apparatus, device, and program product. The solution includes: for the target data table involved in the join of the target query statement, using the corresponding fields in the sorted sequence as local sorting fields, and pre-sorting the target data table to generate a local ordered data stream corresponding to each target data table; constructing a global sorting organization tree, with the sorted sequence as the root node and the local sorting fields of each table as child nodes, and defining the dependency mapping from global sorting to local sorting; constructing a join execution tree based on the join conditions between tables and the mapping relationship, with the data source of the target data table as the leaf node and the join operators as non-leaf nodes, and defining the join order and operator rules; streaming execution: according to the order of data rows in the globally ordered data stream output by the global sorting organization tree, the corresponding join operation operators in the join execution tree are triggered sequentially to perform join operations in the join order, and the data generated by the join operation is used as the query result of the target query statement.

Description

Technical Field

[0001] This application relates to the field of database querying, and more particularly to a method, apparatus, device, and program product for connecting queries. Background Technology

[0002] In database applications, especially in complex query scenarios involving multi-table joins and sorting operations (such as JOIN queries with ORDER BY), the system typically needs to perform a complete join operation first to generate an intermediate result set, and then perform a global sort on that result set. While this approach is simple to implement and logically intuitive, it has significant performance bottlenecks when dealing with large-scale data: on the one hand, generating a complete result set requires a large amount of memory and I / O resources, resulting in high query response latency; on the other hand, sorting operations are computationally expensive when the result set is large, especially when partial results need to be returned (such as Top-K queries). Traditional methods cannot terminate early or return results in a streaming manner, resulting in wasted resources and a degraded user experience. Summary of the Invention

[0003] This application proposes a join query method, apparatus, device, and program product, aiming to solve the problems of high memory consumption, high response latency, and inability to stream partial results caused by the requirement to perform a full join before global sorting when combining multi-table join queries with sorting operations in existing technologies. Accordingly, the technical solution of this application is as follows: In a first aspect, embodiments of this application provide a connection query method, including: For each target data table in the multiple data tables joined by the target query statement, the corresponding field in the sorting sequence belonging to the target data table is used as the local sorting field, and the target data table is pre-sorted to generate the local ordered data stream corresponding to each target data table. The sorting sequence is the sorting field sequence specified by the sorting statement in the target query statement. A global sorting organization tree is constructed, and a join execution tree is constructed based on the join conditions between each target data table and the mapping relationship in the global sorting organization tree. The global sorting organization tree has the sorted sequence as the root node and the local sorting fields of each data table as child nodes, defining the mapping relationship based on sorting dependencies from the sorted sequence to each local sorting field through a tree structure. The join execution tree has the data source of each target data table as the leaf nodes and the join operation operators as non-leaf nodes, defining the join order between the target data tables and the operation rules of each join operation operator through a tree structure. Streaming execution: Following the order of data rows in the globally ordered data stream output by the global sorting organization tree, the corresponding join operation operators in the join execution tree are triggered sequentially to perform join operations in the join order, and the data generated by the join operations is used as the query result of the target query statement.

[0004] Secondly, embodiments of this application provide a connection query device, including: The pre-sorting module, for each of the multiple data tables involved in the connection of the target query statement, takes the corresponding field in the sorting sequence belonging to the target data table as the local sorting field, and performs pre-sorting processing on the target data table to generate the local ordered data stream corresponding to each target data table. The sorting sequence is the sorting field sequence specified by the sorting statement in the target query statement. The tree construction module constructs a global sorting organization tree and, based on the join conditions between target data tables and the mapping relationships in the global sorting organization tree, constructs a join execution tree. The global sorting organization tree has the sorted sequence as its root node and the local sorting fields of each data table as its child nodes, defining the sorting dependency-based mapping relationship from the sorted sequence to each local sorting field through a tree structure. The join execution tree has the data source of the target data table as its leaf nodes and the join operation operators as non-leaf nodes, defining the join order between the target data tables and the operation rules of each join operation operator through a tree structure. The query execution module performs streaming execution: following the order of data rows in the globally ordered data stream output by the global sorting organization tree, it sequentially triggers the corresponding join operation operators in the join execution tree to perform join operations in the join order, and uses the data generated by the join operations as the query result of the target query statement.

[0005] Thirdly, embodiments of this application provide an electronic device, including: a processor; and a memory configured to store computer-executable instructions, which, when executed, cause the processor to perform the method described in the first aspect.

[0006] Fourthly, embodiments of this application provide a computer program product, the computer program product including a computer-readable storage medium storing a computer program operable to cause a computer to perform the method described in the first aspect.

[0007] The solution in this embodiment first targets multiple data tables involved in the join in the target query statement. It uses the corresponding fields in the sorting sequence specified in the sorting statement belonging to each target data table as local sorting fields, and performs independent pre-sorting processing on each target data table to generate its own locally ordered data stream. Then, it constructs a global sorting organization tree with the sorting sequence as the root node and the local sorting fields of each target data table as child nodes. The tree structure defines the sorting dependency mapping relationship from the sorting sequence to each local sorting field. Simultaneously, based on the join conditions between each target data table and this mapping relationship, it constructs a join execution tree. This join execution tree uses the data source as leaf nodes and join operation operators as non-leaf nodes, defining the join order and operation rules between the target data tables. Finally, it performs streaming execution, that is, according to the order of data rows in the globally ordered data stream output by the global sorting organization tree, it sequentially triggers the corresponding join operation operators in the join execution tree to perform join operations in a predetermined join order, and uses the resulting data as the query result. Through the collaboration of the pre-sorting, global sorting organization tree, and join execution tree, as well as the streaming execution mechanism, this application achieves on-demand computation and progressive return of query results: the system does not need to wait for all join operations to complete and materialize the complete intermediate result set, but instead drives the join operation row by row in a pipeline manner. Each time, the system retrieves the data row to be processed from each locally ordered data stream according to the key-value order determined by the global sorting organization tree, assembles the result row in real time through the join execution tree, and outputs it immediately. This processing method requires only a small amount of state such as the current row and heap structure of each data stream to be maintained in memory, which greatly reduces memory consumption and I / O overhead. At the same time, since the results can be returned while being computed, users can obtain the sorted data at the top as soon as possible. This is particularly suitable for Top-K queries and pagination scenarios. The system can terminate the subsequent computation in advance after returning the required number of rows, thereby greatly shortening the response latency, significantly improving the overall query efficiency, and effectively solving the problems of resource waste and poor user experience in traditional methods. Attached Figure Description

[0008] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in the embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0009] Figure 1 This is a schematic diagram of the first type of connection query method according to an embodiment of this application.

[0010] Figure 2 This is a schematic diagram of the second flowchart of the connection query method according to an embodiment of this application.

[0011] Figure 3This is a schematic diagram of the connection query device according to an embodiment of this application.

[0012] Figure 4 This is a schematic diagram of the structure of an electronic device according to an embodiment of this application. Detailed Implementation

[0013] To enable those skilled in the art to better understand the technical solutions in this specification, the technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this specification, and not all embodiments. Based on the embodiments in this specification, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this specification.

[0014] One embodiment of this application provides a join query method. Figure 1 This is a flowchart illustrating the join query method, including: S101, for each of the multiple data tables involved in the connection of the target query statement, the corresponding field in the sorting sequence belonging to the target data table is used as the local sorting field, and the target data table is pre-sorted to generate the local ordered data stream corresponding to each target data table. The sorting sequence is the sorting field sequence specified in the sorting statement of the target query statement.

[0015] The significance of this step lies in pre-decomposing the global sorting operation, which would normally be performed on a large intermediate result set after the join is completed, into local sorting of each independent data table. This lays the foundation for subsequent global ordered merging and streaming joins. Specifically, the target query is a multi-table join query containing a sorting clause, such as "SELECT * FROM T1 JOIN T2 ON T1.id=T2.id JOIN T3 ON T2.x=T3.y ORDER BY T1.a, T3.bDESC". The multiple data tables involved in the join include T1, T2, and T3, and the sorting column specified in the sorting statement is T1.a and T3.b. These two fields together constitute the sorting field sequence. In this embodiment, for each target data table, the corresponding field in the sorting column belonging to that table is used as its local sorting field. For example, for table T1, the local sorting field is T1.a; for table T3, the local sorting field is T3.b. For table T2, which does not directly appear in the sorting column, no pre-sorting is required. By pre-sorting each target data table according to its local sorting field, a corresponding locally ordered data stream is generated. Specifically, the data in table T1 is sorted in ascending order by column 'a', the data in table T3 is sorted in descending order by column 'b', while table T2 retains its original order or is read as needed. In practical applications, such as billing queries in the telecommunications industry, the target query statement may involve multi-table joins of user tables, package tables, and call record tables, sorted by timestamp, user identifier, and cost fields. In this case, this embodiment pre-sorts the call record table by timestamp and user identifier, and pre-sorts the user table by user identifier, thus providing a data foundation for quickly generating ordered bills.

[0016] In terms of specific implementation, when pre-sorting each target data table, this embodiment first checks whether the target data table already has an index corresponding to the local sorting field. If it does, for example, if table T1 has a B+ tree index on column a, then this embodiment directly traverses and reads the data rows of table T1 according to the index. Since the index itself is ordered, traversing the index directly yields a locally ordered data stream sorted in ascending order by column a, without additional sorting operations, thus maximizing the use of the existing data structure and avoiding redundant calculations. If the target data table does not have an index corresponding to the local sorting field, for example, if table T3 does not have an index on column b, then this embodiment uses an external sorting method to generate a locally ordered data stream. Specifically, the data in table T3 is divided into multiple data blocks, the size of each data block should be adapted to the available memory, each data block is sorted in memory separately, after sorting, each ordered block is written to a temporary file, and finally, all temporary files are merged in multiple ways to generate a globally ordered data stream of table T3. Taking table T3 sorted in descending order by column b as an example, if the data volume of table T3 is extremely large and cannot be loaded into memory all at once, this embodiment ensures efficient generation of an ordered data stream even in massive data scenarios through block sorting and merging, while avoiding the risk of memory overflow. For each data block or temporary file, this embodiment also records the minimum and maximum values ​​of its key values. This metadata can be used for subsequent join optimization, such as skipping data blocks that cannot be matched during the join process, further reducing I / O overhead. Through the above two implementation paths, this embodiment ensures that a locally ordered data stream can be reliably generated regardless of the size of the data table or the status of the indexes, providing high-quality data input for the construction of the global sorting organization tree and streaming execution.

[0017] S102, construct a global sorting organization tree, and construct a join execution tree based on the join conditions between each target data table and the mapping relationship in the global sorting organization tree; wherein, the global sorting organization tree takes the sorting sequence as the root node and the local sorting fields of each target data table as child nodes, and defines the mapping relationship based on sorting dependency from the sorting sequence to each local sorting field through the tree structure; the join execution tree takes the data sources of multiple target data tables as leaf nodes and the join operation operators as non-leaf nodes, and defines the join order between multiple data tables and the operation rules of each join operation operator through the tree structure.

[0018] The significance of this step lies in logically organizing the previously generated locally ordered data streams according to global sorting requirements and planning the optimal connection execution path, thereby laying the structural foundation for subsequent streaming execution. The global sorting organization tree is a tree structure with the sorting sequence as the root node and the local sorting fields of each target data table as child nodes. It defines the sorting dependency mapping relationship from the root node's sorting sequence to the local sorting fields of each leaf node through edges. For example, if the root nodes are the field sequences T1.a and T3.b specified by the sorting statement, their child nodes are the local sorting field a of table T1 and the local sorting field b of table T3, respectively. This structure clearly expresses how the global sorting order is composed of the local ordering of each table. The join execution tree is a tree structure with the data sources of multiple target tables as leaf nodes and join operation operators as non-leaf nodes. It defines the join order between data tables and the operation rules of each join operation operator through the hierarchical relationship between nodes. For example, for a join of tables T1, T2, and T3, the join execution tree might use the join results of T1 and T2 as the left subtree, and then join with T3. Each join operator specifically employs algorithms such as nested loop joins, hash joins, or sort-merge joins. In the billing query scenario of the telecommunications industry, if the global sort sequence contains timestamp, user identifier, and cost fields, the root node of the global sort organization tree is this composite sort key, and the child nodes are the timestamp and user identifier of the call record table, the user identifier of the user table, and the cost field of the package table, respectively. The join execution tree plans the execution path based on the join conditions between the user table, package table, and call record table (such as equal user identifiers), first joining the user table and package table, and then joining the call record table, to ensure the orderly generation of the final billing data.

[0019] In practical implementation, constructing the global sorting organization tree first requires resolving the sorting dependencies between the sorting sequence and the local sorting fields of each target data table. This involves determining which target data table each sorting field in the sorting sequence belongs to, and how the local sorting keys for these fields are defined in their respective tables. For example, for the sorting statement `ORDER BY T1.a, T3.b DESC`, the sorting sequences are T1.a and T3.b. Resolving this reveals that T1.a belongs to table T1, and its local sorting field is column 'a'; T3.b belongs to table T3, and its local sorting field is column 'b'. Then, using the sorting sequence as the root node and the local sorting fields of each target data table as leaf nodes, a tree structure is built according to the sorting dependencies. Each node corresponds to a sorting field, and the edges between nodes represent the dependencies from global sorting to local sorting. In more complex scenarios, such as when global sorting involves multiple fields from different tables, or even when the same table contains multiple sorting fields, the global sorting organization tree can be further refined into a multi-level structure. For example, the root node can be a composite sorting key (c1, c4, c2, c3, c5), under which multiple subtrees are divided according to their dependencies, corresponding to local sorting fields such as (c1, c2) of table T1, (c1, c3) of table T2, (c2, c4) of table T3, and (c4, c5) of table T4. This tree-like organization clearly depicts how global ordering is formed by combining the local ordering of each table through specific mappings, providing clear guidance for subsequent multi-way merge operations. This allows the system to extract data sequentially from the ordered data streams of each leaf node according to the order of the root node, thereby merging the globally ordered data streams.

[0020] Constructing the join execution tree involves planning a join order that efficiently utilizes local ordering, based on the sorting dependencies established by the global sorting organization tree and the join conditions between the target data tables. This embodiment first enumerates multiple candidate join orders based on the join conditions between the target data tables and the mapping relationships in the global sorting organization tree. For example, for tables T1, T2, and T3, possible join orders include joining T1 and T2 first, then T3; joining T2 and T3 first, then T1; and joining T1 and T3 first, then T2, among other combinations. Subsequently, this embodiment selects a target join order from these candidate join orders based on cardinality estimation and order preservation. Cardinality estimation characterizes the size of the intermediate result sets generated during the execution of each candidate join order. Through statistical information or sampling estimation, the system can prioritize join orders with smaller intermediate results, thereby reducing computational and memory overhead. Order preservation characterizes the ability of the candidate join order to maintain the global ordering established by the global sorting organization tree, i.e., assessing whether the join order will disrupt the global ordering formed by local sorting. For example, if table T1 is already sorted by column 'a', and table T2 is associated with T1 via a join key and the order of column 'a' is maintained after the join, then the path of joining T1 and T2 first has better sort preservation. Conversely, if joining T2 and T3 first leads to disordered intermediate results, it may be necessary to re-sort them later, reducing efficiency. By comprehensively balancing cardinality estimation and sort preservation, this embodiment selects a target join order that can reduce the amount of intermediate data while maximizing the preservation of global order, and constructs a join execution tree with join operation operators as nodes based on this order. For example, in a telecommunications billing scenario, if the call record table is already sorted by timestamp and the user table is already sorted by user identifier, then the user table and the package table (with a smaller cardinality and sorted user identifiers) are joined first, and then the call record table is sorted and merged by timestamp. This can significantly reduce the size of intermediate results while ensuring that the final result is sorted by timestamp. In this way, the connection execution tree constructed in this embodiment not only defines the physical execution path of inter-table connections, but also pre-sets an appropriate algorithm (such as nested loop connections, hash connections, or sort-merge connections) for each connection operator, providing a complete execution blueprint for subsequent queries.

[0021] S103, Streaming execution: Following the order of data rows in the globally ordered data stream output by the global sorting organization tree, the corresponding join operation operators in the join execution tree are triggered sequentially to perform join operations in the join order, and the data generated by the join operations is used as the query result of the target query statement.

[0022] The significance of this step lies in enabling the coordinated operation of the previously constructed global sorting organization tree and the join execution tree, achieving on-demand computation and progressive return of query results, completely changing the traditional processing mode of first performing a complete join and then global sorting. Streaming execution means that the system does not need to wait for all join operations to complete and materialize a complete intermediate result set, but instead drives join operations row by row in a pipeline manner: according to the order of data rows in the globally ordered data stream output by the global sorting organization tree, the corresponding join operation operators in the join execution tree are triggered sequentially to perform join operations in a predetermined order, and the data rows generated by each join operation are immediately output as the query results of the target query statement. Compared with existing technologies, the core difference of streaming execution lies in its "on-demand driven" characteristic. Traditional methods must generate all intermediate results before sorting and output can begin, while this embodiment achieves result computation and return simultaneously, so users do not need to wait for the full data processing to be completed before obtaining the first row of results. For example, in a billing query scenario in the telecommunications industry, when it is necessary to return the user's 100 most recent call records and corresponding package information in descending order of timestamp, this embodiment can use streaming execution to return the data immediately after finding the first 100 records that meet the conditions, without having to wait for all historical call records to be processed, which greatly shortens the front-end response time.

[0023] In terms of specific implementation, this embodiment first encapsulates each join operation operator in the join execution tree as an independent operator supporting an iterator interface. Each independent operator obtains input data row by row and outputs the join result through the next() function. This design makes the entire query execution process form a complete iterator chain: each next() call of the global sorting organization tree requests the next row of data from the root node operator of the join execution tree, the root node operator then requests input from the child node operators in turn, and finally reads the actual data row from the data source of the leaf node. For example, for the join of three tables T1, T2, and T3, the join operators of T1 and T2 are encapsulated as an iterator. Its next() function obtains the input row by calling the next() function of the T1 iterator and the T2 iterator, executes the join logic, and outputs a row of results, which is then used as the input of the T3 join operator. Through this hierarchical drive of the iterator chain, the system only needs to maintain the current state of each iterator in memory, without caching intermediate result sets, thereby significantly reducing memory consumption. Meanwhile, since the data flows row by row, when the query contains a LIMIT clause or Top-K constraint, the system can terminate the iteration immediately after returning the specified number of rows, avoiding invalid calculations.

[0024] During streaming execution, this embodiment also monitors in real time whether the output results maintain the global sort order defined by the sorting sequence. Since the order may be disrupted due to the characteristics of join operations during actual execution—for example, hash joins may change the relative order of data rows during processing—dynamic adjustments are needed to ensure the correctness of the results. When a failure to maintain the global sort order is detected, this embodiment employs two intervention strategies. First, the intermediate results that cause the global sort order disruption are locally materialized and reordered. For example, if the join result of T2 and T3 disrupts the order of column T3.b, this intermediate result can be temporarily stored in memory or a temporary file, reordered according to T3.b, and then used in subsequent joins. Second, the join algorithm used by the join operation operator that caused the global sort order disruption is switched to sort-merge join, because sort-merge join itself relies on the order of the input data, and its output can maintain the original order. For example, if the current use of hash join results in disorder, this embodiment can dynamically switch to sort-merge join, utilizing the existing local order of T2 and T3 to re-execute the join, thereby restoring the global order. Through the aforementioned real-time monitoring and dynamic adjustment mechanism, this embodiment ensures that even in complex connection scenarios, the final output results can still strictly adhere to the requirements of the ORDER BY clause.

[0025] Furthermore, this embodiment pre-sets at least two join algorithms for each join operation operator to address potential efficiency fluctuations during execution. During streaming execution, this embodiment continuously monitors the processing efficiency of each join operation operator, including metrics such as processing time per row and memory usage. When the processing efficiency of the join algorithm currently used by the target join operation operator falls below a preset threshold—for example, a sudden drop in hash join performance in scenarios with extremely skewed data distribution—this embodiment selects one of the operator's pre-set join algorithms to replace the currently used one. Taking nested loop joins and hash joins as examples, if a hash join is initially selected, but hash table construction becomes slow due to memory pressure at some point, this embodiment can switch to nested loop joins, using a strategy of a smaller table driving a larger table to continue execution. Similarly, when the data volume is small but the sorting overhead of sort-merge joins is too high, a hash join can be switched to reduce preprocessing time. This dynamic algorithm switching mechanism allows the execution plan to adapt to changes in data characteristics and system load, avoiding performance bottlenecks caused by improper selection of a fixed algorithm, and ensuring the robustness and continuous efficiency of query execution. Through the aforementioned iterator encapsulation, sorting monitoring and conflict handling, and dynamic algorithm switching, the streaming execution mechanism in this embodiment achieves efficient memory utilization and minimizes response latency while ensuring the correctness of the results.

[0026] in, Figure 2This example illustrates the complete execution flow of the proposed solution in a specific query instance. First, independent local pre-sorting is performed on the four tables T1 to T4 involved in the join: T1 is sorted by (c1, c2), T2 by (c1, c3), T3 by (c2, c4), and T4 by (c4, c5), ensuring local ordering of each table on its respective local sorting field. Then, based on the global sorting requirements specified in the query statement (e.g., ORDER BY c1, c2, c3, c4, c5), a global sorting organization tree is constructed. This tree has the sorting sequence (c1, c2, c3, c4, c5) as its root node and the local sorting fields of each table as its child nodes. A multi-way merge algorithm is used to merge the locally ordered data streams into a globally ordered data stream. Based on this, and considering the inter-table join relationships—T1 and T2 connected via c1, T2 and T3 connected via c2, and T3 and T4 connected via c4—and the ordering established by the global sorting tree, multiple candidate join orders are enumerated and evaluated. The diagram illustrates two feasible candidate join orders: one is a join path T1→T3→T4 with T2 as an association, and the other is a join order T2→T1→T3→T4. The latter maximizes the transitivity of the c1 sort key because both T1 and T2 are ordered according to c1, and subsequent joins can maintain this ordering. Therefore, it is often preferred as the target join order, and a join execution tree is constructed accordingly. Subsequently, the system drives the join operation operators in the join execution tree row by row in a pipeline manner, following the globally ordered data flow output by the global sorting tree, to perform join operations without materializing intermediate results, returning query results while calculating. The diagram also illustrates a conflict handling scenario: when the global sorting requirement changes to ORDER BY c1, c4, c2, c3, c5, the T4 table lacks columns c2 and c3, and the sorting path cannot be directly supported by the local ordering of the existing data tables, causing the construction of the join execution tree to fail. In this situation, the system triggers a conflict handling mechanism, ensuring the correctness of the query results by dynamically supplementing missing columns, reordering intermediate results, or rolling back to a full sort.

[0027] In summary, the method in this embodiment first targets multiple data tables involved in the join in the target query statement. It uses the corresponding fields in the sorting sequence specified in the sorting statement belonging to each target data table as local sorting fields, and performs independent pre-sorting processing on each target data table to generate its own locally ordered data stream. Then, it constructs a global sorting organization tree with the sorting sequence as the root node and the local sorting fields of each target data table as child nodes. The tree structure defines the sorting dependency mapping relationship from the sorting sequence to each local sorting field. Simultaneously, based on the join conditions between each target data table and this mapping relationship, it constructs a join execution tree. This join execution tree uses the data source as leaf nodes and join operation operators as non-leaf nodes, defining the join order and operation rules between the target data tables. Finally, it performs streaming execution, that is, according to the order of data rows in the globally ordered data stream output by the global sorting organization tree, it sequentially triggers the corresponding join operation operators in the join execution tree to perform join operations in the predetermined join order, and uses the resulting data as the query result. Through the collaboration of the pre-sorting, global sorting organization tree, and join execution tree, as well as the streaming execution mechanism, this application achieves on-demand computation and progressive return of query results: the system does not need to wait for all join operations to complete and materialize the complete intermediate result set, but instead drives the join operation row by row in a pipeline manner. Each time, the system retrieves the data row to be processed from each locally ordered data stream according to the key-value order determined by the global sorting organization tree, assembles the result row in real time through the join execution tree, and outputs it immediately. This processing method requires only a small amount of state such as the current row and heap structure of each data stream to be maintained in memory, which greatly reduces memory consumption and I / O overhead. At the same time, since the results can be returned while being computed, users can obtain the sorted data at the top as soon as possible. This is particularly suitable for Top-K queries and pagination scenarios. The system can terminate the subsequent computation in advance after returning the required number of rows, thereby greatly shortening the response latency, significantly improving the overall query efficiency, and effectively solving the problems of resource waste and poor user experience in traditional methods.

[0028] In addition, corresponding to Figure 1 The method shown in this embodiment, in another embodiment, also provides a connection query device. Wherein, Figure 3 This is a schematic diagram of the structure of the connection query device 300, including: The pre-sorting module 310, for each of the multiple data tables involved in the connection of the target query statement, takes the corresponding field in the sorting sequence belonging to the target data table as the local sorting field, and performs pre-sorting processing on the target data table to generate the local ordered data stream corresponding to each target data table. The sorting sequence is the sorting field sequence specified by the sorting statement in the target query statement. The tree construction module 320 constructs a global sorting organization tree and, based on the connection conditions between each target data table and the mapping relationship in the global sorting organization tree, constructs a join execution tree. The global sorting organization tree has the sorted sequence as the root node and the local sorting fields of each data table as child nodes, defining a mapping relationship based on sorting dependencies from the sorted sequence to each local sorting field through a tree structure. The join execution tree has the data source of the target data table as the leaf nodes and the join operation operators as non-leaf nodes, defining the connection order between the target data tables and the operation rules of each join operation operator through a tree structure. The query execution module 330 performs streaming execution: following the order of data rows in the globally ordered data stream output by the global sorting organization tree, it sequentially triggers the corresponding join operation operators in the join execution tree to perform join operations in the join order, and uses the data generated by the join operations as the query result of the target query statement.

[0029] Optionally, the pre-sorting module 310 performs pre-sorting processing on the target data table, including: checking whether the target data table already has an index corresponding to the local sorting field; if it exists, traversing and reading the data rows of the target data table according to the index to obtain the locally ordered data stream; if it does not exist, dividing the data of the target data table into multiple data blocks, sorting each data block separately in memory and writing it to a temporary file, and merging the multiple temporary files in multiple ways to obtain the locally ordered data stream.

[0030] Optionally, each connection operation operator in the connection execution tree is encapsulated as an independent operator supporting an iterator interface during streaming execution. Each independent operator obtains input data line by line and outputs the connection result through the next() function.

[0031] Optionally, the query execution module 330 is further configured to: monitor whether the output results maintain the global sorting order defined by the sorting sequence during the streaming execution process; when it is detected that the global sorting order is not maintained, locally materialize and reorder the intermediate results that cause the global sorting order to be destroyed, and / or switch the join algorithm used by the join operation operator that causes the global sorting order to sort-merge join.

[0032] Optionally, the join operation operator has at least two preset join algorithms; the query execution module 330 is further configured to: monitor the processing efficiency of each join operation operator during streaming execution; when it is detected that the processing efficiency of the join algorithm currently used by the target join operation operator is lower than a preset threshold, select one of the preset join algorithms of the target join operation operator to replace the currently used join algorithm.

[0033] Optionally, the tree construction module 320 constructs a global sorting organization tree, including: parsing the sorting dependency relationship between the sorting sequence and the local sorting fields of each target data table, and determining the target data table to which each sorting field in the sorting sequence belongs; establishing a tree structure with the sorting sequence as the root node and the local sorting fields of each target data table as leaf nodes according to the sorting dependency relationship, wherein each node of the tree structure corresponds to a sorting field, and the edges represent the dependency relationship between sorting fields.

[0034] The tree construction module 320 constructs a join execution tree based on the join conditions between each data table and the mapping relationship in the global sorting organization tree, including: enumerating multiple candidate join orders based on the join conditions between each target data table and the mapping relationship in the global sorting organization tree; selecting a target join order from the multiple candidate join orders according to the cardinality estimation and order preservation of each candidate join order; the cardinality estimation is used to characterize the size of the intermediate result set generated during the execution of the candidate join order, and the order preservation is used to describe the ability of the candidate join order to maintain the global order established by the global sorting organization tree; and constructing a join execution tree with join operation operators as nodes based on the target join order.

[0035] It should be noted that the connection query device in this embodiment can be used as... Figure 1 The execution body of the method shown is therefore able to achieve... Figure 1 The steps and functions of the method shown are illustrated.

[0036] Figure 4 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Please refer to it. Figure 4 At the hardware level, the electronic device includes a processor, and optionally also includes an internal bus, a network interface, and memory. The memory may include main memory, such as high-speed random-access memory (RAM), or non-volatile memory, such as at least one disk drive. Of course, the electronic device may also include other hardware required for other business operations.

[0037] The processor, network interface, and memory can be interconnected via an internal bus, which can be an ISA (Industry Standard Architecture) bus, a PCI (Peripheral Component Interconnect) bus, or an EISA (Extended Industry Standard Architecture) bus, etc. This bus can be divided into address bus, data bus, control bus, etc. For ease of representation, Figure 4 The symbol is represented by a single double-headed arrow, but this does not mean that there is only one bus or one type of bus.

[0038] Memory is used to store computer programs. Specifically, a computer program may include program code, which includes computer operation instructions. Memory may include main memory and non-volatile memory, and provides the computer program to the processor.

[0039] Specifically, the processor reads the corresponding computer program from non-volatile memory into memory and then runs it, forming the above-mentioned logical structure. Figure 3 The connection query device is shown. Correspondingly, the processor executes the program stored in memory, specifically for performing the following operations: For each target data table involved in the connection of the target query statement, the corresponding field in the sorting sequence belonging to the target data table is used as the local sorting field, and the target data table is pre-sorted to generate the local ordered data stream corresponding to each target data table. The sorting sequence is the sorting field sequence specified in the sorting statement of the target query statement.

[0040] A global sorting organization tree is constructed, and a join execution tree is constructed based on the join conditions between each target data table and the mapping relationship in the global sorting organization tree. The global sorting organization tree has the sorted sequence as the root node and the local sorting fields of each data table as child nodes, defining the mapping relationship based on sorting dependencies from the sorted sequence to each local sorting field through a tree structure. The join execution tree has the data source of each target data table as the leaf nodes and the join operation operators as non-leaf nodes, defining the join order between the target data tables and the operation rules of each join operation operator through a tree structure.

[0041] Streaming execution: Following the order of data rows in the globally ordered data stream output by the global sorting organization tree, the corresponding join operation operators in the join execution tree are triggered sequentially to perform join operations in the join order, and the data generated by the join operations is used as the query result of the target query statement.

[0042] The above is as described in this instruction manual. Figure 1 The connection query method disclosed in the illustrated embodiments can be applied to a processor and implemented by the processor. The processor may be an integrated circuit chip with signal processing capabilities. During implementation, each step of the above method can be completed by integrated logic circuits in the processor's hardware or by instructions in software form. The processor can be a general-purpose processor, including a Central Processing Unit (CPU), a Network Processor (NP), etc.; it can also be a Digital Signal Processor (DSP), an Application Specific Integrated Circuit (ASIC), a Field-Programmable Gate Array (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components. It can implement or execute the methods, steps, and logic block diagrams disclosed in the embodiments of this application. The general-purpose processor can be a microprocessor or any conventional processor. The steps of the methods disclosed in the embodiments of this application can be directly embodied in the execution of a hardware decoding processor, or executed by a combination of hardware and software modules in the decoding processor. The software module can reside in a mature storage medium in the field, such as random access memory, flash memory, read-only memory, programmable read-only memory, electrically erasable programmable memory, or registers. This storage medium is located in memory, and the processor reads information from the memory and, in conjunction with its hardware, completes the steps of the above method.

[0043] Of course, in addition to software implementation, the electronic device described in this specification does not exclude other implementation methods, such as logic devices or a combination of hardware and software. In other words, the execution subject of the following processing flow is not limited to each logic unit, but can also be hardware or logic devices.

[0044] Furthermore, embodiments of this application also propose a computer program product, including a computer-readable storage medium storing one or more computer programs, the one or more computer programs including instructions.

[0045] When the aforementioned instructions are executed by a portable electronic device that includes multiple applications, they enable the portable electronic device to perform... Figure 1 The steps in the method shown include: For each target data table involved in the connection of the target query statement, the corresponding field in the sorting sequence belonging to the target data table is used as the local sorting field, and the target data table is pre-sorted to generate the local ordered data stream corresponding to each target data table. The sorting sequence is the sorting field sequence specified in the sorting statement of the target query statement.

[0046] A global sorting organization tree is constructed, and a join execution tree is constructed based on the join conditions between each target data table and the mapping relationship in the global sorting organization tree. The global sorting organization tree has the sorted sequence as the root node and the local sorting fields of each data table as child nodes, defining the mapping relationship based on sorting dependencies from the sorted sequence to each local sorting field through a tree structure. The join execution tree has the data source of each target data table as the leaf nodes and the join operation operators as non-leaf nodes, defining the join order between the target data tables and the operation rules of each join operation operator through a tree structure.

[0047] Streaming execution: Following the order of data rows in the globally ordered data stream output by the global sorting organization tree, the corresponding join operation operators in the join execution tree are triggered sequentially to perform join operations in the join order, and the data generated by the join operations is used as the query result of the target query statement.

[0048] Those skilled in the art will understand that the embodiments of this specification can be provided as methods, systems, or computer program products. Therefore, this specification may take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this specification may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0049] The foregoing has described specific embodiments of this specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims may be performed in a different order than that shown in the embodiments and may still achieve the desired result. Furthermore, the processes depicted in the drawings do not necessarily require the specific or sequential order shown to achieve the desired result. In some embodiments, multitasking and parallel processing are possible or may be advantageous.

[0050] The above are merely embodiments of this specification and are not intended to limit the scope of this specification. Various modifications and variations can be made to this specification by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this specification should be included within the scope of the claims of this specification. Furthermore, all other embodiments obtained by those skilled in the art without inventive effort should fall within the protection scope of this document.

Claims

1. A join query method, characterized in that, include: For each target data table in the multiple data tables joined by the target query statement, the corresponding field in the sorting sequence belonging to the target data table is used as the local sorting field, and the target data table is pre-sorted to generate the local ordered data stream corresponding to each target data table. The sorting sequence is the sorting field sequence specified by the sorting statement in the target query statement. A global sorting organization tree is constructed, and a join execution tree is constructed based on the join conditions between each target data table and the mapping relationship in the global sorting organization tree. The global sorting organization tree has the sorted sequence as the root node and the local sorting fields of each data table as child nodes, defining the mapping relationship based on sorting dependencies from the sorted sequence to each local sorting field through a tree structure. The join execution tree has the data source of each target data table as the leaf nodes and the join operation operators as non-leaf nodes, defining the join order between the target data tables and the operation rules of each join operation operator through a tree structure. Streaming execution: Following the order of data rows in the globally ordered data stream output by the global sorting organization tree, the corresponding join operation operators in the join execution tree are triggered sequentially to perform join operations in the join order, and the data generated by the join operations is used as the query result of the target query statement.

2. The method according to claim 1, characterized in that, Pre-sorting the target data table includes: Check whether the target data table already has an index corresponding to the local sorting field; If it exists, then traverse and read the data rows of the target data table according to the index to obtain the locally ordered data stream; If it does not exist, the data in the target data table is divided into multiple data blocks. Each data block is sorted in memory separately and written to a temporary file. Multiple temporary files are then merged in multiple ways to obtain the locally ordered data stream.

3. The method according to claim 1, characterized in that, Each connection operation operator in the connection execution tree is encapsulated as an independent operator that supports an iterator interface during the streaming execution process. Each independent operator obtains input data line by line and outputs the connection result through the next() function.

4. The method according to claim 1, characterized in that, Also includes: During streaming execution, monitor whether the output results maintain the global sorting order defined by the sorting sequence; When it is detected that the global sorting order is not maintained, the intermediate results that cause the global sorting order to be destroyed are locally materialized and reordered, and / or the join algorithm used by the join operation operator that causes the global sorting order to be destroyed is switched to sort-merge join.

5. The method according to claim 1, characterized in that, The join operation operator has at least two preset join algorithms; the method further includes: During streaming execution, monitor the processing efficiency of each join operation operator; When it is detected that the processing efficiency of the connection algorithm currently used by the target connection operator is lower than a preset threshold, one of the connection algorithms preset by the target connection operator is selected to replace the currently used connection algorithm.

6. The method according to any one of claims 1 to 5, characterized in that, Construct a global sorted organization tree, including: The sorting dependencies between the sorting sequence and the local sorting fields of each target data table are analyzed to determine the target data table to which each sorting field in the sorting sequence belongs. Using the sorting column as the root node and the local sorting fields of each target data table as leaf nodes, a tree structure is established according to the sorting dependency relationship. Each node of the tree structure corresponds to a sorting field, and the edges represent the dependency relationship between sorting fields.

7. The method according to any one of claims 1 to 5, characterized in that, Based on the join conditions between the data tables and the mapping relationships in the global sorting organization tree, a join execution tree is constructed, including: Based on the connection conditions between each target data table and the mapping relationship in the global sorting organization tree, multiple candidate connection orders are enumerated; Based on the cardinality estimation and order preservation of each candidate connection order, a target connection order is selected from the plurality of candidate connection orders; the cardinality estimation is used to characterize the size of the intermediate result set generated during the execution of the candidate connection order, and the order preservation is used to describe the ability of the candidate connection order to maintain the global ordering established by the global sorting organization tree; Based on the target connection order, construct a connection execution tree with connection operation operators as nodes.

8. A connection query device, characterized in that, include: The pre-sorting module, for each of the multiple data tables involved in the connection of the target query statement, takes the corresponding field in the sorting sequence belonging to the target data table as the local sorting field, and performs pre-sorting processing on the target data table to generate the local ordered data stream corresponding to each target data table. The sorting sequence is the sorting field sequence specified by the sorting statement in the target query statement. The tree construction module constructs a global sorting organization tree and, based on the join conditions between target data tables and the mapping relationships in the global sorting organization tree, constructs a join execution tree. The global sorting organization tree has the sorted sequence as its root node and the local sorting fields of each data table as its child nodes, defining the sorting dependency-based mapping relationship from the sorted sequence to each local sorting field through a tree structure. The join execution tree has the data source of the target data table as its leaf nodes and the join operation operators as non-leaf nodes, defining the join order between the target data tables and the operation rules of each join operation operator through a tree structure. The query execution module performs streaming execution: following the order of data rows in the globally ordered data stream output by the global sorting organization tree, it sequentially triggers the corresponding join operation operators in the join execution tree to perform join operations in the join order, and uses the data generated by the join operations as the query result of the target query statement.

9. An electronic device, comprising: processor; And a memory arranged to store computer-executable instructions, characterized in that, when executed, the executable instructions cause the processor to perform the method as described in any one of claims 1 to 7.

10. A computer program product, the computer program product comprising a computer-readable storage medium storing a computer program, characterized in that, The computer program is operable to cause the computer to perform the method as described in any one of claims 1 to 7.

Images