MPP database query method and system in node replacement scenario

Abstract

The present invention provides an MPP database query method and system in a node replacement scenario, belonging to the field of distributed database technology, the method comprising: receiving and parsing a query statement to obtain distribution information of a table to be queried; according to the distribution information, replacing the distribution number of an inclusion table and an included table with an inclusion relationship to obtain a query table to be associated with the same distribution number; executing a query according to the query statement on each node shard of the query table to be associated to obtain multiple query results; outputting a final query result according to the multiple query results. The present invention reduces the performance loss caused by node replacement, avoids a large amount of overhead caused by the redistribution process by redistributing the unfinished inclusion relationship in the expansion of the primary and standby shards, and improves the query efficiency on the premise that the query results are accurate.

Description

Technical Field

[0001] The present invention relates to the technical field of distributed databases, and in particular to an MPP database query method and system in a node replacement scenario. Background Art

[0002] The master-slave sharding architecture ensures data security and high availability of the distributed database, enabling it to recover quickly from failures. However, in certain scenarios during the recovery process, there is a type of performance degradation problem.

[0003] In the scenario of primary-secondary sharding, during the expansion process, only part of the table data will be redistributed. Since the two tables are distributed differently at this time, the query process requires sending the complete data of one of the tables to all nodes, or redistributing the data from one table from one distribution method to another, and continuing the query while ensuring that the data from the two tables are distributed in the same way.

[0004] In actual business, the number of tables involved in the query is greater, more redistribution operations are required, and the amount of redistributed data is also greater, so the overhead of such redistribution is very large. This results in that after the node is replaced, when the data of each table has not yet been fully restored to the previous distribution method, the multi-table association query task that was originally designed to be directly executed on each node has a large amount of data transmission between nodes in the above scenario, and the query efficiency is greatly reduced. Summary of the invention

[0005] The present invention aims to solve at least one of the technical problems existing in the related art. To this end, the present invention provides an MPP database query method and system in a node replacement scenario.

[0006] The present invention provides an MPP database query method in a node replacement scenario, comprising:

[0007] S1: Receive and parse the query statement to obtain the distribution information of the table to be queried;

[0008] S2: According to the distribution information, the including table and the included table that have an inclusion relationship are replaced by distribution numbers to obtain a to-be-associated query table with the same distribution number;

[0009] S3: executing a query according to the query statement on each node shard of the query table to be associated, and obtaining multiple query results;

[0010] S4: Outputting a final query result according to the multiple query results.

[0011] According to an MPP database query method in a node replacement scenario provided by the present invention, step S1 further includes:

[0012] S11: parsing the received query statement to obtain all tables included in the query statement;

[0013] S12: Query the distribution mode of all tables;

[0014] S13: According to the distribution mode, the multiple shard distribution nodes included in the table to be queried are numbered in sequence to obtain distribution information of the table to be queried.

[0015] According to an MPP database query method in a node replacement scenario provided by the present invention, in step S1, the table to be queried includes a first table and a second table.

[0016] According to an MPP database query method in a node replacement scenario provided by the present invention, in step S2, the method for determining the inclusion relationship is:

[0017] In the same node, when any shard in the second table does not have a corresponding shard in the first table, there is no inclusion relationship between the first table and the second table;

[0018] In the same node, when each shard of the second table has a corresponding shard in the first table, the first table and the second table have an inclusion relationship.

[0019] According to an MPP database query method in a node replacement scenario provided by the present invention, in step S2,

[0020] When the total number of shards of the first table is greater than that of the second table, the first table is used as the including table and the second table is used as the included table;

[0021] When the total number of shards of the first table is less than that of the second table, the second table is used as the including table and the first table is used as the included table.

[0022] According to an MPP database query method in a node replacement scenario provided by the present invention, step S2 specifically includes:

[0023] When the including table is the first table and the included table is the second table, the distribution number of the second table is replaced by the distribution number of the first table;

[0024] When the including table is the second table and the included table is the first table, the distribution number of the first table is replaced with the distribution number of the second table.

[0025] According to an MPP database query method in a node replacement scenario provided by the present invention, in step S3, when querying the associated query table, nodes in the associated query table for which redistribution of table data has not been completed are not queried.

[0026] According to an MPP database query method in a node replacement scenario provided by the present invention, when a query statement is changed, the distribution number of the table to be associated with is restored to the distribution information of the table to be queried.

[0027] The present invention also provides an MPP database query system in a node replacement scenario, which is used to execute an MPP database query method in a node replacement scenario as described in any one of the above items, including:

[0028] Parsing module: used to receive and parse query statements to obtain distribution information of the table to be queried;

[0029] A determination module is used to determine whether there is an inclusion relationship between the tables to be queried according to the distribution information;

[0030] Replacement module: used to replace the distribution numbers of the inclusion table and the included table in the inclusion relationship to obtain the to-be-associated query table with the same distribution number;

[0031] Query module: used to execute query according to the query statement on each node shard of the query table to be associated, obtain multiple query results, and output the final query result according to the multiple query results.

[0032] The present invention provides an MPP database query method and system in a node replacement scenario. According to the data distribution characteristics in the node replacement scenario, a method for reducing the performance loss caused by node replacement is proposed. Since the two distribution modes generated in the node replacement scenario have certain particularities, that is, the distribution mode after expansion can contain another one, the present invention determines whether there is an inclusion relationship between different distribution situations, and uses this inclusion relationship to avoid the large amount of overhead caused by the redistribution process as much as possible, which can ensure that the query efficiency is greatly improved under the premise of accurate query results.

[0033] Additional aspects and advantages of the present invention will be given in part in the following description and in part will be obvious from the following description, or will be learned through practice of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0034] In order to more clearly illustrate the technical solutions in the present invention or the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0035] Figure 1 It is a flowchart of an MPP database query method in a node replacement scenario provided by an embodiment of the present invention.

[0036] Figure 2 It is a schematic diagram of the structure of an MPP database query system in a node replacement scenario provided by an embodiment of the present invention.

[0037] Figure 3 It is a schematic diagram of node distribution of the primary and backup shards provided in an embodiment of the present invention.

[0038] Figure 4 It is a schematic diagram of node distribution for the capacity reduction of the primary and backup shards provided by an embodiment of the present invention.

[0039] Figure 5 It is a schematic diagram of node distribution in the case where the redistribution of primary and backup shards is not completed provided by an embodiment of the present invention.

[0040] Reference numerals:

[0041] 100, parsing module; 200, identification module; 300, replacement module; 400, query module. DETAILED DESCRIPTION

[0042] In order to make the purpose, technical scheme and advantages of the present invention clearer, the technical scheme of the present invention will be clearly and completely described below in conjunction with the drawings in the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments in the present invention, all other embodiments obtained by ordinary technicians in the field without creative work are within the scope of protection of the present invention. The following embodiments are used to illustrate the present invention, but cannot be used to limit the scope of the present invention.

[0043] In the description of the embodiments of the present invention, the terms “first”, “second” and “third” are used for descriptive purposes only and should not be understood as indicating or implying relative importance.

[0044] In the description of the embodiments of the present invention, it should be noted that, unless otherwise clearly specified and limited, the terms "connected" and "connection" should be understood in a broad sense, for example, it can be a fixed connection, a detachable connection, or an integral connection; it can be a mechanical connection or an electrical connection; it can be a direct connection or an indirect connection through an intermediate medium. For ordinary technicians in this field, the specific meanings of the above terms in the embodiments of the present invention can be understood according to specific circumstances.

[0045] In the embodiments of the present invention, unless otherwise clearly specified and limited, the first feature being "above" or "below" the second feature may mean that the first and second features are in direct contact, or the first and second features are in indirect contact through an intermediate medium. Moreover, the first feature being "above", "above" and "above" the second feature may mean that the first feature is directly above or obliquely above the second feature, or simply means that the first feature is higher in level than the second feature. The first feature being "below", "below" and "below" the second feature may mean that the first feature is directly below or obliquely below the second feature, or simply means that the first feature is lower in level than the second feature.

[0046] In the description of this specification, the description with reference to the terms "one embodiment", "some embodiments", "example", "specific example", or "some examples" etc. means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the embodiment of the present invention. In this specification, the schematic representations of the above terms do not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described may be combined in any one or more embodiments or examples in a suitable manner. In addition, those skilled in the art may combine and combine the different embodiments or examples described in this specification and the features of the different embodiments or examples, without contradiction.

[0047] First, the primary and backup shards and related technical issues mentioned in the present invention are explained in detail.

[0048] Active / standby sharding is a common architectural design pattern in distributed database systems, used to improve system availability and performance. In the active / standby sharding architecture, the data of a table is divided into multiple shards, each of which has a primary node and one or more backup nodes. The active / standby sharding architecture provides reliable fault tolerance for distributed database systems by introducing backup nodes, allowing the system to quickly recover and continue to provide stable services when a failure occurs.

[0049] If the master node fails, the system can quickly recover from the failure by promoting a backup node to the new master node. At the same time, in order to ensure the stable operation of the system, normal nodes are used to replace the failed nodes. This process can be achieved by reducing and expanding capacity.

[0050] Combine the following Figures 3 to 5 This article gives an example to illustrate the process of replacing a node using the expansion and reduction operations.

[0051] like Figure 3As shown in the figure, t1 and t2 represent tables, n1 to n3 represent nodes, and tables t1 and t2 are distributed on three nodes in a primary and secondary manner. When node 3 fails and needs to be replaced, node 3 is removed from the cluster. Figure 4 As shown in the figure, this is the shrinking process; then a new node (node ​​4) is used to replace node 3, and the increase is as follows Figure 5 As shown, capacity expansion.

[0052] Since it takes some time for table data to be redistributed to the original one-master-one-slave distribution mode, during the expansion process, only part of the table data may be redistributed. Figure 5 As shown in the figure, the data in table t1 has been restored to the distribution mode before the failure, while the data in table t2 is now only distributed on the first two nodes.

[0053] exist Figure 5 In the scenario shown, if the distributed database performs a related query on the t1 table and the t2 table, since the two tables are distributed in different ways at this time, during the query process, it is necessary to send the complete data of one of the tables, including all shards of the table, to all three nodes, or redistribute the data from one of the tables from one distribution mode to another, and continue the query while ensuring that the data from the two tables are distributed in the same way.

[0054] In actual business, the number of tables involved in the query is greater, more redistribution operations are required, and the amount of redistributed data is also greater, so the overhead of such redistribution is very large. This results in that after the node is replaced, when the data of each table has not yet been fully restored to the previous distribution method, the multi-table association query task that was originally designed to be directly executed on each node has a large amount of data transmission between nodes in the above scenario, and the query efficiency is greatly reduced.

[0055] by Figure 5 For example, assume that the distribution mode of table t1 is distribution 1, and the distribution mode of table t2 is distribution 2. Table t2 is only distributed on nodes 1 and 2. Table t1 has corresponding shards on these two nodes. In this case, distribution 1 is considered to contain distribution 2. Obviously, nodes 1 and 2 already contain all the data of the two tables. Node 4 is not considered. Only queries are performed on these two nodes, and the query results are accurate.

[0056] In order to solve the above problems, the present invention proposes an MPP database query method and system in a node replacement scenario.

[0057] Combine the following Figures 1 to 5 Embodiments of the present invention are described.

[0058] In order to better understand the present embodiment, firstly, a method for executing a query in a case where the redistribution of the primary and standby shards is not completed by using an MPP database query method in a node replacement scenario provided by the present invention is described.

[0059] When designing the table structure in actual business, if there are frequent associated queries between two tables, the two tables can select the same hash distribution column to ensure that data with the same column value is distributed on the same data shard.

[0060] For example, both tables t1 and t2 use column a as the hash distribution column and are distributed on each node in the same way. When executing a join query with hash column equality conditions such as select * from t1 join t2 on t1.a = t2.a, the query can be executed directly on each node. For example, select * from t1_n1 joint2_n1 on t1_n1.a = t2_n1.a is executed on shard n1. After the query execution on each node is completed, the query results can be summarized.

[0061] Such a table structure design can fully reduce data transmission between nodes and improve the performance of such associated query services. However, after the node is replaced, the distribution of tables involved in such associated queries will be inconsistent, and the query plan will change.

[0062] For the same query statement select * from t1 join t2 on t1.a = t2.a, the query process is as follows:

[0063] Parse the query statement and collect statistics on all tables included in the query and the distribution of table data;

[0064] During the query plan generation phase, for two tables that require associated queries (join and other operations), it is detected that the distribution modes of the two tables are inconsistent;

[0065] For the association query between two tables with different distribution modes, the following two solutions are usually adopted. The data of one of the tables is selected to generate an intermediate table. Taking t2 as an example, after the intermediate table containing t2 data is generated, the t1 table and these intermediate tables are used for association query.

[0066] Specifically, the first method is to generate a hash distribution intermediate table, which is to redistribute the query data in table t2. In the extreme case of select * from t1 join t2 on t1.a = t2.a, the entire table t2 needs to be redistributed during the query. Although in most cases there will be filtering conditions in the SQL statement, the intermediate table does not need to contain all the data of t2, which is less expensive than waiting for the entire table to be redistributed, but the amount of data may be very large, and generating an intermediate table with the same distribution method as the other table is inefficient.

[0067] The second method is to generate a replicated table, which contains all the data of the t2 table and will be sent to all nodes containing shards of the t1 table. This solution is often only suitable for situations where the t2 table has less data, otherwise the overhead will be even greater.

[0068] Finally, summarize the above query results.

[0069] In order to solve one of the above problems, Figure 1 As shown, the present invention provides an MPP database query method in a node replacement scenario, comprising:

[0070] S1: Receive and parse the query statement to obtain the distribution information of the table to be queried;

[0071] Wherein, in step S1, the table to be queried includes a first table and a second table.

[0072] Wherein, step S1 further comprises:

[0073] S11: parsing the received query statement to obtain all tables included in the query statement;

[0074] S12: Query the distribution mode of all tables;

[0075] S13: According to the distribution mode, the multiple shard distribution nodes included in the table to be queried are numbered in sequence to obtain distribution information of the table to be queried.

[0076] In this phase, the query statement is parsed first, and then the distribution information of all tables and table data included in this query is counted. The distribution information of the table is identified by different numbers, that is, on which node each shard of the table is distributed, that is, the distribution number. Assume that the distribution numbers of table t1 and table t2 are 1 and 2 respectively.

[0077] S2: According to the distribution information, the including table and the included table that have an inclusion relationship are replaced by distribution numbers to obtain a to-be-associated query table with the same distribution number;

[0078] Wherein, in step S2, the method for determining the inclusion relationship is:

[0079] In the same node, when any shard in the second table does not have a corresponding shard in the first table, there is no inclusion relationship between the first table and the second table;

[0080] In the same node, when each shard of the second table has a corresponding shard in the first table, the first table and the second table have an inclusion relationship.

[0081] Wherein, in step S2, when the total number of shards of the first table is greater than that of the second table, the first table is used as the including table and the second table is used as the included table;

[0082] When the total number of shards of the first table is less than that of the second table, the second table is used as the including table and the first table is used as the included table.

[0083] Wherein, step S2 specifically includes:

[0084] When the including table is the first table and the included table is the second table, the distribution number of the second table is replaced by the distribution number of the first table;

[0085] When the including table is the second table and the included table is the first table, the distribution number of the first table is replaced with the distribution number of the second table.

[0086] Before the query begins, for the tables that need to be associated with the query, determine whether their distribution modes have an inclusion relationship based on the distribution numbers of each table. If such an inclusion relationship exists between the two tables, replace the distribution number of the table with more shards with the number corresponding to the distribution mode it contains. For tables t1 and t2, 1 contains 2, and the distribution number of table t1 is replaced with 2.

[0087] S3: executing a query according to the query statement on each node shard of the query table to be associated, and obtaining multiple query results;

[0088] Wherein, in step S3, when querying the associated query table, the nodes in the associated query table for which redistribution of table data has not been completed are not queried.

[0089] After the distribution numbers of the two tables are replaced in step S2, the distribution mode of the two tables of the associated query is detected in the plan generation stage, and the two tables are treated as having the same distribution mode, that is, the distribution mode of the two tables is considered to be the distribution mode with fewer nodes used. There is no need to generate a new intermediate table for the associated query, but the query is directly performed on each node shard, for example, select * from t1_n1 join t2_n1 on t1_n1.a = t2_n1.a).

[0090] by Figure 5 Taking the scenario as an example, the final effect is that when querying, only nodes 1 and 2 exist, and the data on node 4 is not used, thereby avoiding the additional data transmission overhead caused by the different distribution methods of the two tables during query.

[0091] S4: Outputting a final query result according to the multiple query results.

[0092] After steps S1 to S3, the query results obtained are summarized to obtain the query results outputted finally.

[0093] When the query statement is changed, the distribution number of the table to be associated with the query is restored to the distribution information of the table to be queried.

[0094] It should be noted that the replacement of the distribution number is effective only in this query. When the next query starts, the distribution number of each table still corresponds to its actual distribution information. Before each query starts, the inclusion relationship between the distribution methods is re-judged to determine whether optimization is needed.

[0095] For DML (Data Manipulation Language) statements, only the select query part in the statement is optimized, and the distribution number of the target table is not operated. For example, in insert into t1 select *from t1 join t2 on t1.a = t2.a, only the select query part is optimized. When inserting data, it cannot be assumed that the t1 table is only distributed on node 1 and node 2, otherwise the data distribution of the primary and backup shards will be inconsistent.

[0096] It should also be noted that the MPP database query system in a node replacement scenario provided by the present invention, in addition to the above-mentioned two-table association query example, also supports multi-table (more than two) association queries in some embodiments. The multi-table query has the same logic as the above-mentioned example, and is associated with each other. For example, a query has tables t1, t2, and t3. All tables that meet the conditions involved in this query are replaced with distribution numbers. The execution logic is to associate t1 and t2 first, obtain the result t12, and then associate t12 with t3, and so on, and finally complete the multi-table association query.

[0097] The present invention also provides an MPP database query system in a node replacement scenario, which is used to execute an MPP database query method in a node replacement scenario as described in any one of the above items, including:

[0098] Parsing module 100: receiving and parsing a query statement to obtain distribution information of a table to be queried;

[0099] Determination module 200: used to determine whether there is an inclusion relationship between the tables to be queried according to the distribution information;

[0100] Replacement module 300: used to replace the distribution numbers of the inclusion table and the included table that have an inclusion relationship, and obtain a to-be-associated query table with the same distribution number;

[0101] Query module 400: used to execute a query according to the query statement on each node shard of the query table to be associated, obtain multiple query results, and output a final query result based on the multiple query results.

[0102] The present invention provides an MPP database query method and system in a node replacement scenario. According to the data distribution characteristics in the node replacement scenario, a method for reducing the performance loss caused by node replacement is proposed. Since the two distribution modes generated in the node replacement scenario have certain particularities, that is, the distribution mode after expansion can contain another one, the present invention determines whether there is an inclusion relationship between different distribution situations, and uses this inclusion relationship to avoid the large amount of overhead caused by the redistribution process as much as possible, which can ensure that the query efficiency is greatly improved under the premise of accurate query results.

[0103] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. An MPP database query method in a node replacement scenario, characterized in that: include: S1: Receive and parse the query statement to obtain the distribution information of the table to be queried; S2: According to the distribution information, the including table and the included table that have an inclusion relationship are replaced by distribution numbers to obtain a to-be-associated query table with the same distribution number; The table to be queried includes a first table and a second table; The method for determining the inclusion relationship is: In the same node, when any shard in the second table does not have a corresponding shard in the first table, there is no inclusion relationship between the first table and the second table; In the same node, when each shard of the second table has a corresponding shard in the first table, the first table and the second table have a containment relationship; When the total number of shards of the first table is greater than that of the second table, the first table is used as the including table and the second table is used as the included table; When the total number of shards of the first table is less than that of the second table, the second table is used as the including table and the first table is used as the included table; When the including table is the first table and the included table is the second table, the distribution number of the second table is replaced by the distribution number of the first table; When the including table is the second table and the included table is the first table, the distribution number of the first table is replaced by the distribution number of the second table; S3: executing a query according to the query statement on each node shard of the query table to be associated, and obtaining multiple query results; S4: Outputting a final query result according to the multiple query results.

2. The MPP database query method in a node replacement scenario according to claim 1, characterized in that: Step S1 further comprises: S11: parsing the received query statement to obtain all tables included in the query statement; S12: Query the distribution mode of all tables; S13: According to the distribution mode, the multiple shard distribution nodes included in the table to be queried are numbered in sequence to obtain distribution information of the table to be queried.

3. The MPP database query method in a node replacement scenario according to claim 1, characterized in that: In step S3, when querying the associated query table, the nodes in the associated query table for which redistribution of table data has not been completed are not queried.

4. The MPP database query method in a node replacement scenario according to claim 1, characterized in that: When the query statement is changed, the distribution number of the table to be associated with the query is restored to the distribution information of the table to be queried.

5. An MPP database query system in a node replacement scenario, used to execute an MPP database query method in a node replacement scenario as claimed in any one of claims 1 to 4, characterized in that: include: Parsing module: used to receive and parse query statements to obtain distribution information of the table to be queried; A determination module is used to determine whether there is an inclusion relationship between the tables to be queried according to the distribution information; Replacement module: used to replace the distribution numbers of the inclusion table and the included table in the inclusion relationship to obtain the to-be-associated query table with the same distribution number; The table to be queried includes a first table and a second table; The method for determining the inclusion relationship is: In the same node, when any shard in the second table does not have a corresponding shard in the first table, there is no inclusion relationship between the first table and the second table; In the same node, when each shard of the second table has a corresponding shard in the first table, the first table and the second table have a containment relationship; When the total number of shards of the first table is greater than that of the second table, the first table is used as the including table and the second table is used as the included table; When the total number of shards of the first table is less than that of the second table, the second table is used as the including table and the first table is used as the included table; When the including table is the first table and the included table is the second table, the distribution number of the second table is replaced by the distribution number of the first table; When the including table is the second table and the included table is the first table, the distribution number of the first table is replaced by the distribution number of the second table; Query module: used to execute query according to the query statement on each node shard of the query table to be associated, obtain multiple query results, and output the final query result according to the multiple query results.

Images