Cross-node Join query method for database middleware based on query tree

Abstract

The present disclosure relates to a database middleware cross-node Join query method based on a query tree, comprising the steps of: constructing an SQL query plan tree, comprising: converting an SQL query statement into a relational calculation expression, wherein the relational calculation expression corresponds to a root node of the SQL query plan tree, and performing tuple calculation on the relational calculation expression to determine tuples and operations that can be sequentially sent down to the next layer in the SQL query plan tree; sending the tuples and operations corresponding to the leaf nodes in the SQL query plan tree to the database nodes to execute a sub-SQL query; constructing an SQL query result tree, comprising: using the query results of the sub-SQL query as the leaf nodes of the SQL query result tree, and according to the leaf nodes of the SQL query result tree, obtaining non-leaf nodes of the SQL query result tree in sequence from the parent node of the leaf node in the SQL query result tree to the root node through the tuples and operations defined by each node in the SQL query plan tree.

Description

Technical Field

[0001] The present disclosure generally relates to middleware for accessing distributed databases. Background Art

[0002] With the development of informatization, IT systems in various fields generate a large amount of data every moment. The traditional single database architecture is difficult to adapt to the rapid growth of data volume, so the distributed database system came into being, and also promoted the development of database middleware technology.

[0003] Existing database middleware (such as MyCat) has some problems in cross-node Join query: In the case of sharding or sharding tables, there are three main implementation methods for cross-node Join query of database middleware MyCat, namely global table, ER Join and Share Join. These three methods handle cross-node Join query by simply splitting the query statement and sending it to each database node, and then simply summarizing the results. Such existing technology cannot effectively handle complex aggregation, filtering and other operations, and the query results may be incorrect. Summary of the invention

[0004] A brief overview of the disclosure is given below in order to provide a basic understanding of some aspects of the disclosure. However, it should be understood that this overview is not an exhaustive overview of the disclosure. It is not intended to identify the key or important parts of the disclosure, nor is it intended to limit the scope of the disclosure. Its purpose is simply to give some concepts of the disclosure in a simplified form as a prelude to a more detailed description given later.

[0005] According to one aspect of the present disclosure, a database middleware cross-node Join query method based on a query tree is provided, comprising the steps of:

[0006] Build a SQL query plan tree, including:

[0007] Performing lexical, grammatical and semantic analysis on the SQL query statement to convert the SQL query statement into a relational calculation expression corresponding to the root node of the SQL query plan tree,

[0008] Tuple calculation is performed on the relational calculation expression to determine tuples and operations that can be sent down to the next layer in the SQL query plan tree in sequence, wherein the tuples and operations that can be sent down to the database node for execution correspond to the leaf nodes in the SQL query plan tree, and the tuples and operations that cannot be sent down to the database node for execution correspond to the non-leaf nodes in the SQL query plan tree,

[0009] Send the tuples and operations corresponding to the leaf nodes in the SQL query plan tree to the database node to execute the sub-SQL query.

[0010] Build a SQL query result tree, including:

[0011] The query result of the sub-SQL query is used as a leaf node of the SQL query result tree.

[0012] According to the leaf nodes of the SQL query result tree, through the tuples and operations defined by each node in the SQL query plan tree, the non-leaf nodes of the SQL query result tree are obtained in sequence from the parent node of the leaf node in the SQL query result tree to the root node, wherein the root node of the SQL query result tree corresponds to the result of the SQL query statement.

[0013] According to another aspect of the present invention, a database middleware cross-node Join query device based on a query tree is provided, comprising:

[0014] a memory having instructions stored thereon; and

[0015] The processor is configured to execute instructions stored in the memory to perform the above method.

[0016] According to another aspect of the present invention, a computer-readable storage medium is provided, comprising computer-executable instructions, which, when executed by one or more processors, enable the one or more processors to perform the above method.

[0017] According to another aspect of the present invention, a computer program product is provided, comprising computer executable instructions, which, when executed by one or more processors, cause the one or more processors to perform the above method.

[0018] Other features and advantages of the present invention will become apparent from the following detailed description of preferred embodiments of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] The accompanying drawings, which constitute a part of the specification, illustrate embodiments of the present disclosure and, together with the description, serve to explain the principles of the present disclosure.

[0020] The present disclosure may be more clearly understood from the following detailed description with reference to the accompanying drawings, in which:

[0021] Figure 1 A schematic diagram showing an application environment of database middleware;

[0022] Figure 2 A flowchart of a database middleware cross-node Join query method based on a query tree according to the present invention is shown;

[0023] Figure 3 shows an example of a SQL query plan tree;

[0024] Figure 4 An example of a SQL query result tree is shown;

[0025] Figure 5 An exemplary configuration of a computing device capable of implementing an embodiment according to the present disclosure is shown. DETAILED DESCRIPTION

[0026] The following detailed description is made with reference to the accompanying drawings, and the following detailed description is provided to assist in a comprehensive understanding of the various exemplary embodiments of the present disclosure. The following description includes various details to assist in understanding, but these details are considered to be examples only and not to limit the present disclosure, which is defined by the appended claims and their equivalents. The words and phrases used in the following description are only used to enable a clear and consistent understanding of the present disclosure. In addition, for the sake of clarity and brevity, descriptions of well-known structures, functions, and configurations may be omitted. Those of ordinary skill in the art will recognize that various changes and modifications may be made to the examples described herein without departing from the spirit and scope of the present disclosure.

[0027] Figure 1 A schematic diagram of the application environment of database middleware is shown. Database middleware is an intermediate service between the database (DB) and the application program (APP) for data processing and interaction. It mainly implements functions such as sub-library and sub-table, read-write separation, etc. With database middleware, the application program no longer needs to deal with operations such as sharding clusters, data source switching, transaction processing, data aggregation, etc. As a preferred typical example, the database is a distributed database. A distributed database usually includes multiple physical nodes.

[0028] As a specific example of a distributed database, a supermarket has a goods table (goods) and a sales detail table (sales). The sales detail table has a lot of data, so the sales detail table is split into N pieces by date. The goods table is also split into different shards for storage due to the large amount of data.

[0029] Here, only two shard nodes DN1 and DN2 are illustrated. As an example, the sales details table stores data with dates of "20190211" and "20190212" on the two shard nodes DN1 and DN2 respectively. The product table stores product data in two shard nodes DN1 and DN2 in a dispersed manner according to the middleware sharding rules. The following table illustrates the specific situation of the data in the two shard nodes DN1 and DN2.

[0030]

[0031]

[0032] Figure 2 A flow chart of a database middleware cross-node Join query method based on a query tree according to the present invention is shown.

[0033] First, in step S201, a SQL query plan tree is constructed, including: performing lexical, grammatical and semantic analysis on the SQL query statement to convert the SQL query statement into a relational calculation expression, wherein the relational calculation expression corresponds to the root node of the SQL query plan tree, and performing tuple calculation on the relational calculation expression to determine tuples and operations that can be sequentially sent down to the next layer in the SQL query plan tree, wherein the tuples and operations that can be sent down to the database node for execution correspond to the leaf nodes in the SQL query plan tree, and wherein the tuples and operations that cannot be sent down to the database node for execution correspond to the non-leaf nodes in the SQL query plan tree.

[0034] In the cross-node Join query method of the database middleware based on the query tree of the present invention, the relational data model theory is used. For SQL queries containing Join queries, tuple calculation can be performed after the query Join statement is converted to construct a SQL query plan tree suitable for multi-node situations, and the sub-SQL is sent to each node for execution to obtain the sub-SQL query result, and the final result is generated according to the query plan tree. That is, the query plan tree is based on relational algebra and relational calculation theory. By parsing SQL, it can be converted into a relational calculation expression, and then the expression is subjected to tuple calculation, which can determine the tuples and operations that can be sequentially delegated, thereby constructing a SQL query plan tree with a tree structure.

[0035] Then, in step S202, the tuples and operations corresponding to the leaf nodes in the SQL query plan tree are sent to the database nodes to execute the sub-SQL query. The sub-SQL query is executed in the corresponding database node.

[0036] Based on the result of the sub-SQL query, in step S203, a SQL query result tree is constructed, including: taking the query result of the sub-SQL query as the leaf node of the SQL query result tree, and according to the leaf node of the SQL query result tree, through the tuples and operations defined by each node in the SQL query plan tree, obtaining the non-leaf nodes of the SQL query result tree in sequence from the parent node of the leaf node in the SQL query result tree to the root node, wherein the root node of the SQL query result tree corresponds to the result of the SQL query statement.

[0037] As a possible step, the result of the SQL query statement can be further returned to the upper-level application.

[0038] The present invention will be described in more detail below by way of example in conjunction with the following SQL query statements.

[0039] select g.type,count(distinct s.barcode)as type_cnt from goods g innerjoin sales s on s.barcode=g.barcode; (1)

[0040] First, a SQL query plan tree is constructed as described in step S201. The SQL query statement is subjected to lexical, grammatical and semantic analysis, and the SQL query statement can be converted into the following relational calculation expression.

[0041]

[0042]

[0043] The above relational calculation expression (2) corresponds to the root node of the SQL query plan tree. In this example, the COUNT aggregation function operation is performed when the data is aggregated, as the top parent node of the tree.

[0044] Then, tuple calculation is performed on the relational calculation expression (2) to determine the tuples and operations that can be sequentially sent down to the next layer in the SQL query plan tree. Specifically, by performing tuple calculation on the relational calculation expression (2), the tuple g.barcode=s.barcode that can be sent down to the next layer in the SQL query plan tree is determined. The tuple g.barcode=s.barcode cannot be sent down to the database node for execution because the data of g and s are stored in various nodes, and executing the corresponding SQL operation will cause an error in the result. In this example, the tuple g.barcode=s.barcode serves as the secondary parent node of the tree. In the present invention, tuples and operations involving data stored in different database nodes are not sent down to the database node for execution.

[0045] Then, by further performing tuple calculus, the next level of tuples and operations in the SQL query plan tree are determined. Specifically, the tuple and It is determined to be a tuple and operation at the next level in the SQL query plan tree. and It can be sent to each database node for execution. The corresponding sub-SQL query statements are as follows (3) and (4):

[0046] SQL1: select*from goods g; (3)

[0047] SQL2: select*from sales s; (4)

[0048] In the present invention, tuples and operations (e.g., tuples) that can be sent to database nodes for execution and ) corresponds to a leaf node in the SQL query plan tree, wherein tuples and operations that cannot be sent down to the database node for execution (such as the tuple g.barcode=s.barcode and the COUNT aggregate function operation) correspond to non-leaf nodes in the SQL query plan tree. In the present invention, tuples and operations corresponding to non-leaf nodes in the SQL query plan tree are not sent down to the database node for execution; tuples and operations corresponding to leaf nodes in the SQL query plan tree are sent down to the database node for execution.

[0049] In the example specifically illustrated above, the constructed SQL query plan tree is as follows: Figure 3 shown.

[0050] The above examples illustrate the construction of the SQL query plan tree as described in step S201.

[0051] Then, as described in step S202, the tuples and operations corresponding to the leaf nodes in the SQL query plan tree (e.g., tuples and ) is sent to the database node to execute the sub-SQL query (for example, the above-mentioned sub-SQL query statements (3) and (4)).

[0052] The results of the above sub-SQL queries are used to build the SQL query result tree.

[0053] Specifically, as described in step S203, the query result of the sub-SQL query is used as a leaf node of the SQL query result tree.

[0054] In the case of the distributed database specifically illustrated above, the above sub-SQL query statements (3) and (4) both involve the database shard node DN1 and the database shard node DN2. Therefore, it is necessary to merge the query result SQL1 of the sub-SQL query statement (3) on the database shard node DN1 and the query result SQL1 on the database shard node DN2 to obtain the result MERGE1 of the sub-SQL query statement (3); similarly, it is necessary to merge the query result SQL2 of the sub-SQL query statement (4) on the database shard node DN1 and the query result SQL2 on the database shard node DN2 to obtain the result MERGE2 of the sub-SQL query statement (4).

[0055] Furthermore, through the tuples and operations defined by each node in the SQL query plan tree, the non-leaf nodes of the SQL query result tree are obtained in sequence from the parent node of the leaf node in the SQL query result tree to the root node, wherein the root node of the SQL query result tree corresponds to the result of the SQL query statement. Wherein, if the non-leaf node of the SQL query result tree is not a root node, the processing result is continued to be processed as a subtree result and a subtree result at the same level to obtain their parent node, until the root node is finally obtained. In the case of the distributed database specifically illustrated above, since the non-leaf nodes MERGE1 and MERGE2 of the SQL query result tree are not root nodes, the processing result is continued to be processed as a subtree result and a subtree result at the same level to obtain their parent node JOIN. In the case of the distributed database specifically illustrated above, the parent node JOIN is the root node.

[0056] In the case of the distributed database specifically illustrated above, the constructed SQL query result tree is as follows: Figure 4 The part in the dotted box is shown in the figure. Figure 4 Further below the dotted box are shown the database shard node DN1 and the database shard node DN2. Figure 4 An application program (APP) that calls the database middleware is further shown above the dotted box.

[0057] For the above specific example, first merge the query results of each node, then filter and JOIN the result set according to g.barcode=s.barcode, and finally perform COUNT aggregation operation on the result set to obtain the correct result (tableware:1,stationery:3).

[0058] It can be seen that in the present invention, complex Join query statements are parsed and constructed into a SQL query plan tree, and sub-SQL queries are executed at each node. The obtained sub-tree results are filtered, aggregated, and other operations are performed based on the previously generated SQL query plan tree to generate a query result tree from bottom to top, and the result of the final root node is the query result.

[0059] In addition, for example, a simple SQL query statement that does not involve table joins can be simplified first, then routed to each node for SQL query, and then the query results of each node are simply aggregated before returning the result. Therefore, such a simple SQL query statement that does not involve table joins can also correspond to a simple SQL query plan tree and SQL query result tree.

[0060] As a further preferred example, the present invention can return the result of the SQL query statement to the upper-layer application.

[0061] In the query tree-based database middleware cross-node Join query method proposed in the present invention, by constructing a SQL query plan tree suitable for multi-node situations, it is possible to avoid query result errors caused by simple parsing and execution of SQL statements in the current cross-node Join query method, thereby improving the correctness and efficiency of cross-node Join queries.

[0062] Compared with the prior art, the present invention has the following advantages and effects. On the one hand, according to the current relational data model theory, a query plan tree suitable for middleware with multiple tables or multiple libraries is constructed, which can avoid the problems of cross-node Join queries using existing middleware, and provide upper-layer applications with correct results of Join queries under sub-tables or sub-libraries. On the other hand, the query plan tree is constructed on the basis of relational algebra and relational calculus, has a solid theoretical foundation, and can continuously improve the performance of cross-node queries based on existing query depth optimization technology.

[0063] Figure 5 An exemplary configuration of a computing device 500 capable of implementing embodiments according to the present disclosure is shown.

[0064] The computing device 500 is an example of a hardware device to which the above aspects of the present disclosure can be applied. The computing device 500 can be any machine configured to perform processing and / or computing. The computing device 500 can be, but is not limited to, a workstation, a server, a desktop computer, a laptop computer, a tablet computer, a personal data assistant (PDA), a smart phone, a vehicle-mounted computer, or a combination thereof.

[0065] like Figure 5 As shown, the computing device 500 may include one or more components that may be connected or communicated with a bus 502 via one or more interfaces. The bus 502 may include, but is not limited to, an Industry Standard Architecture (ISA) bus, a Micro Channel Architecture (MCA) bus, an Enhanced ISA (EISA) bus, a Video Electronics Standards Association (VESA) local bus, and a Peripheral Component Interconnect (PCI) bus. The computing device 500 may include, for example, one or more processors 504, one or more input devices 506, and one or more output devices 508. The one or more processors 504 may be any type of processor and may include, but is not limited to, one or more general-purpose processors or special-purpose processors (such as special-purpose processing chips). The processor 504 may be configured to execute, for example, Figure 2The method shown. Input device 506 can be any type of input device capable of inputting information to a computing device, and can include, but is not limited to, a mouse, a keyboard, a touch screen, a microphone, and / or a remote controller. Output device 508 can be any type of device capable of presenting information, and can include, but is not limited to, a display, a speaker, a video / audio output terminal, a vibrator, and / or a printer.

[0066] The computing device 500 may also include or be connected to a non-transitory storage device 514, which may be any non-transitory storage device that can implement data storage, and may include, but is not limited to, a disk drive, an optical storage device, a solid-state memory, a floppy disk, a flexible disk, a hard disk, a tape, or any other magnetic medium, a compressed disk or any other optical medium, a cache memory, and / or any other storage chip or module, and / or any other medium from which a computer can read data, instructions, and / or code. The computing device 500 may also include a random access memory (RAM) 510 and a read-only memory (ROM) 512. The ROM 512 may store programs, utilities, or processes to be executed in a non-volatile manner. The RAM 510 may provide volatile data storage and store instructions related to the operation of the computing device 500. The computing device 500 may also include a network / bus interface 516 coupled to a data link 518. The network / bus interface 516 may be any type of device or system capable of enabling communication with an external device and / or a network, and may include but is not limited to a modem, a network card, an infrared communication device, a wireless communication device, and / or a chipset (such as a Bluetooth TM equipment, 802.11 equipment, WiFi equipment, WiMax equipment, cellular communication facilities, etc.).

[0067] The present disclosure may be implemented as any combination of an apparatus, a system, an integrated circuit, and a computer program on a non-transitory computer-readable medium. One or more processors may be implemented as an integrated circuit (IC), an application specific integrated circuit (ASIC), or a large scale integrated circuit (LSI), a system LSI, a super LSI, or a super LSI component that performs some or all of the functions described in the present disclosure.

[0068] The present disclosure includes the use of software, applications, computer programs, or algorithms. The software, applications, computer programs, or algorithms may be stored on a non-transitory computer-readable medium to enable a computer such as one or more processors to perform the above steps and the steps described in the accompanying drawings. For example, one or more memories store the software or algorithm in executable instructions, and one or more processors may associate a set of instructions to execute the software or algorithm to provide various functions according to the embodiments described in the present disclosure.

[0069] Software and computer programs (which may also be referred to as programs, software applications, applications, components or code) include machine instructions for a programmable processor and may be implemented in a high-level procedural language, an object-oriented programming language, a functional programming language, a logic programming language, or an assembly language or a machine language. The term "computer-readable medium" refers to any computer program product, apparatus or device for providing machine instructions or data to a programmable data processor, such as magnetic disks, optical disks, solid-state storage devices, memories and programmable logic devices (PLDs), including computer-readable media that receive machine instructions as computer-readable signals.

[0070] For example, computer readable media may include dynamic random access memory (DRAM), random access memory (RAM), read-only memory (ROM), electrically erasable read-only memory (EEPROM), compact disk read-only memory (CD-ROM) or other optical disk storage devices, magnetic disk storage devices or other magnetic storage devices, or any other medium that can be used to carry or store the desired computer readable program code in the form of instructions or data structures and can be accessed by a general or special purpose computer or a general or special purpose processor. As used herein, disk or disc includes compact disc (CD), laser disc, optical disc, digital versatile disc (DVD), floppy disk and Blu-ray disc, where disks usually reproduce data magnetically, while discs reproduce data optically by laser. Combinations of the above are also included in the scope of computer readable media.

[0071] The subject matter of the present disclosure is provided as an example of devices, systems, methods and programs for performing the features described in the present disclosure. However, in addition to the above-mentioned features, other features or variations may also be expected. It is expected that the implementation of the components and functions of the present disclosure may be completed with any emerging technology that may replace any of the above-mentioned implementation technologies.

[0072] In addition, the above description provides examples without limiting the scope, applicability or configuration set forth in the claims. Without departing from the spirit and scope of the present disclosure, the functions and arrangements of the elements discussed may be changed. Various embodiments may appropriately omit, replace or add various processes or components. For example, features described with respect to certain embodiments may be combined in other embodiments.

[0073] Similarly, while operations are depicted in a particular order in the drawings, this should not be understood as requiring that such operations be performed in the particular order shown or in sequential order, or that all illustrated operations be performed to achieve desired results. In certain circumstances, multitasking and parallel processing can be advantageous.

Claims

1. A database middleware cross-node Join query method based on a query tree, comprising the steps of: Build a SQL query plan tree, including: Performing lexical, grammatical and semantic analysis on the SQL query statement to convert the SQL query statement into a relational calculation expression corresponding to the root node of the SQL query plan tree, Tuple calculation is performed on the relational calculation expression to determine tuples and operations that can be sent down to the next layer in the SQL query plan tree in sequence, wherein the tuples and operations that can be sent down to the database node for execution correspond to the leaf nodes in the SQL query plan tree, and the tuples and operations that cannot be sent down to the database node for execution correspond to the non-leaf nodes in the SQL query plan tree, Send the tuples and operations corresponding to the leaf nodes in the SQL query plan tree to the database node to execute the sub-SQL query. Build a SQL query result tree, including: The query result of the sub-SQL query is used as a leaf node of the SQL query result tree. According to the leaf nodes of the SQL query result tree, the non-leaf nodes of the SQL query result tree are obtained in sequence from the parent node of the leaf node in the SQL query result tree to the root node through the tuples and operations defined by each node in the SQL query plan tree, where: The root node of the SQL query result tree corresponds to the result of the SQL query statement.

2. The method according to claim 1, further comprising: Return the results of the SQL query statement to the upper-level application.

3. The method according to claim 1, wherein: The database is a distributed database.

4. The method according to claim 1, wherein: The conversion of SQL query statements into relational calculus expressions is based on relational algebra and relational calculus theory.

5. The method according to claim 1, wherein: The sub-SQL query is executed in the corresponding database node.

6. The method according to claim 1, wherein: Tuples and operations involving data stored in different database nodes are not sent to the database nodes for execution.

7. The method according to claim 1, wherein: Tuples and operations corresponding to non-leaf nodes in the SQL query plan tree are not sent to the database node for execution.

8. The method according to claim 1, wherein: The tuples and operations corresponding to the leaf nodes in the SQL query plan tree are sent to the database nodes for execution.

9. A database middleware cross-node Join query device based on a query tree, comprising: a memory having instructions stored therein; as well as A processor configured to execute instructions stored on the memory to perform the method according to any one of claims 1 to 8.

10. A computer-readable storage medium comprising computer-executable instructions, which, when executed by one or more processors, cause the one or more processors to perform the method according to any one of claims 1 to 8.

11. A computer program product comprising computer executable instructions, which, when executed by one or more processors, cause the one or more processors to perform the method according to any one of claims 1 to 8.

Images