Fragmented query optimization method based on distributed database KaiwuDB

Abstract

The invention provides a fragmentation query optimization method based on a distributed database KaiwuDB, and belongs to the technical field of databases, the method comprises the following steps: decomposing a query span into a range with smaller granularity, and generating a multi-range query plan; after a multi-range query plan is generated, the query plan of each node is sent to the corresponding node through gRPC; after each node receives the execution plan, orderly executing according to the execution plan; and after execution of each node is completed, summarizing is carried out at the gateway node, and coding and returning are carried out to the client. And the query parallelism degree is improved.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, and in particular to a sharding query optimization method based on distributed database KaiwuDB. BACKGROUND

[0002] Distributed database: a cluster composed of multiple database nodes, data can be stored in multiple data nodes.

[0003] gRPC: a means of data transmission between nodes.

[0004] Sharding: regarding a range as a piece, sharding is to divide the data of a table into multiple ranges for query.

[0005] Query plan: a series of specific step-by-step execution sets generated by a database to execute a query SQL, the query plan is used to guide the executor to read data and calculate.

[0006] Range: a set of part of data in a distributed database.

[0007] TableReader: a kind of identifier for reading table data in a query plan.

[0008] Span: here refers to a set of multiple ranges of a single node.

[0009] AGG: aggregation calculation, max (calculate maximum value) / min (calculate minimum value) / count (count the number of data in the group) and other calculations on a group of data. SUMMARY

[0010] To solve the above technical problems, the present application provides a sharding query optimization method based on distributed database KaiwuDB, which can enhance the parallelism of the database and improve the query efficiency in the distributed database.

[0011] The technical scheme of the present application is:

[0012] A sharding query optimization method based on distributed database KaiwuDB, comprising

[0013] decomposing the query span into smaller granularity ranges and generating a multi-range query plan;

[0014] After generating the multi-range query plan, the query plan of each node is sent to the corresponding node through gRPC;

[0015] When each node receives the execution plan, it performs ordered execution according to the execution plan;

[0016] When each node is executed, the gateway node is summarized and encoded back to the client.

[0017] Further,

[0018] When the user inputs a query SQL, the SQL execution reaches the query plan generation stage, first obtains the range information of the corresponding table, and generates a multi-range query plan according to the range information.

[0019] Further,

[0020] When reading data, each scan only reads the data of one range.

[0021] Further,

[0022] The method includes the following modules:

[0023] The range information module obtains all ranges of the table according to the interface function PartitionSpansByTalbeID provided by the replica layer according to the table id;

[0024] The multi-range query plan generation module splits the normal query plan into smaller granularity queries, each range has its own TableReader, and each TableReader is executed in parallel;

[0025] The plan sending module, KaiwuDB's range is distributed to each node, so the generated plan has node attributes, each range corresponds to a node, and the generated node plan is sent to the corresponding node through the established gRPC channel;

[0026] The multi-range execution module, when the executor of each node receives the execution plan of each shard of the node, first establishes a gRPC data transmission channel, each channel is distinguished by flowID and rangeID, and after the data transmission channel is established, the actual execution begins.

[0027] Among them,

[0028] Each query SQL will generate a unique identifier.

[0029] The actual execution first obtains all the data of the range according to the rangeID information carried by the TableReader, and returns it to the upper layer for AGG aggregation calculation, and finally returns the data to the gateway node through the gRPC channel for summarization, and returns it to the client after encoding, and finally feedback to the user.

[0030] The specific working process of the multi-range query plan generation module is as follows:

[0031] Start a three-node cluster, generate TableReader operators according to the number of ranges obtained, and the current query has several ranges, then generate the corresponding number of TableReader. Each shard reads data and sends the data to the AGG operator, and then continues to do AGG aggregation calculation, and finally the results are aggregated on the gateway node.

[0032] The beneficial effects of the present application are

[0033] The parallelism of the database can be enhanced by the scheme, and the query performance of some scenarios of the distributed database can be improved. The TableReader is split into smaller granular queries, which can reduce the lock waiting time between ranges. BRIEF DESCRIPTION OF DRAWINGS

[0034] Figure 1 is a workflow schematic diagram of the present application;

[0035] Figure 2 is a three-node multi-range query plan schematic diagram. DETAILED DESCRIPTION

[0036] In order to make the purpose, technical scheme and advantages of the embodiments of the present application clearer, the technical scheme in the embodiments of the present application will be described clearly and completely in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those skilled in the art without creative labor are within the scope of protection of the present application.

[0037] The present application provides a sharding query optimization method based on a distributed database KaiwuDB, and the implementation process is as follows,

[0038] The overall execution process is shown in the accompanying Figure 1 When a user inputs a query SQL, the SQL is executed to the query plan generation stage, first obtains the range information of the corresponding table, generates a multi-range query plan according to the range information, then sends the plan of each node to the corresponding node for execution, and the gateway node receives the summary result of each node and returns it to the client after encoding.

[0039] Obtain the range information module:

[0040] According to the interface function PartitionSpansByTalbeID provided by the replica layer, the range information of the table can be obtained according to the table id.

[0041] Multi-range query plan generation module:

[0042] Start a three-node cluster, take the statement select max(col_2) from table_name group by col_1 as an example, the three-node multi-range query plan is as shown in the accompanying Figure 2 The data (range) of the table table_name is evenly distributed in node1, node2 and node3, r1r2 is in node1, r3r4 is in node2, and r5r6 is in node3, according to the number of ranges obtained, a TableReader operator is generated, the current query has 6 ranges (r1, r2, r3, r4, r5, r6), then 6

[0043] TableReader (tr1, tr2, tr3, tr4, tr5, tr6), each shard reads data and sends the data to the AGG operator, and continues to do AGG aggregation calculation, and finally the results are summarized on the gateway node, the multi-range plan is to split the normal query plan into smaller granularity queries, each range has its own TableReader, and each TableReader is executed in parallel, which reduces the lock waiting time and makes the query more efficient.

[0044] Plan sending module:

[0045] KaiwuDB is a distributed database, and the range of KaiwuDB is distributed to each node, so the generated plan has a node attribute, each range corresponds to a node, and the generated node plan is sent to the corresponding node through the established gRPC channel.

[0046] Multi-range execution module:

[0047] The execution process is executed in order according to the query plan shown in the accompanying Figure 2 When the executors of each node receive the execution plan of each shard of the node, they will first establish a gRPC data transmission channel, each channel is distinguished by flowID (a unique identifier generated for each query SQL) and rangeID, and when the data transmission channel is established, the actual execution begins. The actual execution first obtains all the data of the range according to the rangeID information carried by the TableReader, and returns it to the upper layer for AGG aggregation calculation, and finally returns the data to the gateway node through the gRPC channel for summarization, encodes it and returns it to the client, and finally feeds back to the user.

[0048] The above merely describes the preferred embodiments of the present application, which are used for explaining the technical solutions of the present application, and are not used for limiting the protection scope of the present application. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application shall be included in the protection scope of the present application.

Claims

1. A sharded query optimization method based on the distributed database KaiwuDB, characterized in that, include: The query span is broken down into smaller ranges, and a multi-range query plan is generated. After generating a multi-range query plan, the query plan for each node is sent to the corresponding node via gRPC; Once each node receives the execution plan, it executes the plan in an orderly manner. After each node has completed its execution, the results are aggregated at the gateway node and encoded before being returned to the client.

2. The method according to claim 1, characterized in that, When a user enters a query SQL, when the SQL execution reaches the query plan generation stage, it first obtains the range information of the corresponding table, and generates a multi-range query plan based on the range information.

3. The method according to claim 2, characterized in that, When reading data, each scan reads only one range of data.

4. The method according to claim 1, 2, or 3, characterized in that, The execution of this method includes the following modules: The module for obtaining range information uses the interface function PartitionSpansByTalbeID provided by the replica layer to obtain all ranges of the table based on the table ID. The multi-range query plan generation module breaks down a normal query plan into smaller-granular queries. Each range has its own TableReader, and each TableReader executes in parallel. The plan sending module distributes KaiwuDB's ranges across various nodes, and the generated plans have node attributes. Each range corresponds to one node, and the generated node plans are sent to the corresponding nodes through the established gRPC channel. In the multi-range execution module, after the executor of each node receives the execution plan of each shard of that node, it first establishes a gRPC data transmission channel. Each channel is distinguished by flowID and rangeID. Once the data transmission channel is established, the actual execution begins.

5. The method according to claim 4, characterized in that, Each SQL query generates a unique identifier.

6. The method according to claim 4, characterized in that, In actual execution, the system first obtains all the data in the range based on the rangeID information carried by the TableReader, and then returns it upwards to continue AGG aggregation calculation. Finally, the data is returned to the gateway node through the gRPC channel for aggregation, encoded, and returned to the client, and finally fed back to the user.

7. The method according to claim 4, characterized in that, The specific working process of the multi-range query plan generation module is as follows: Start a three-node cluster and generate TableReader operators based on the number of ranges obtained. The number of TableReaders generated corresponds to the number of ranges in the current query.

8. The method according to claim 7, characterized in that, After each shard reads data, it sends the data to the AGG operator to continue AGG aggregation calculation, and finally the results are summarized to the gateway node.

Images