Method and apparatus for processing database query statement

Abstract

The present disclosure discloses a method and device for processing a database query statement, the method comprising: receiving a database query statement, the database query statement comprising a main query and a subquery, a query condition of the subquery being a related condition of the main query, the main query comprising a filter condition for filtering based on a query result of the subquery, and partition table data of the main query and the subquery being distributed on multiple servers; querying target data from partition table data of the main query, the target data comprising first data related to the main query and second data related to the subquery; distributing the first data and the second data to a target server, the target server being capable of obtaining all partition table data of the subquery; performing, in parallel, a query on the partition table data of the subquery based on the second data to obtain a first query result; and performing, in parallel, a filtering operation on the first data using the filter condition according to the first query result.

Description

TECHNICAL FIELD

[0001] The present disclosure relates to the field of databases, and in particular, to a method and device for processing a database query statement. BACKGROUND

[0002] When a distributed database executes a structured query language (SQL) statement containing a correlated subquery, a traditional execution plan needs to pull the data involved in the main query and the subquery back to locally execute the filtering condition of the subquery in a single thread, and for each row of the main query, the subquery needs to be scanned again. Such an execution plan is difficult to parallelize, and thus has poor execution performance. SUMMARY

[0003] Therefore, the present disclosure provides a method and device for processing a database query statement, which can improve the execution performance of a distributed database when executing a SQL statement containing a correlated subquery.

[0004] In a first aspect, a method for processing a database query statement is provided, including: receiving a database query statement, the database query statement including a main query and a subquery, a query condition of the subquery being a correlated condition of the main query, the main query including a filtering condition for filtering based on a query result of the subquery, and partition table data of the main query and the subquery being distributed on multiple servers; querying target data from the partition table data of the main query, the target data including first data related to the main query and second data related to the subquery; distributing the first data and the second data to a target server, the target server being able to obtain all partition table data of the subquery; based on the second data, performing a query on the partition table data of the subquery in parallel to obtain a first query result; and performing a filtering operation on the first data in parallel using the filtering condition according to the first query result.

[0005] Optionally, the distributing the first data and the second data to the target server includes distributing the first data and the second data to the target server by adaptively selecting a hash or a random distribution mode.

[0006] Optionally, the method further includes collecting a distribution condition of the first data and the second data, and determining to distribute the first data and the second data to the target server by a hash or a random distribution mode according to the distribution condition.

[0007] Optionally, the target server is capable of obtaining all partition table data of the subquery, comprising: the target server obtaining all partition table data of the subquery through a DISTRIBUTED TABLE SCAN operator.

[0008] In a second aspect, an apparatus for processing a database query statement is provided, comprising: a receiving module configured to receive a database query statement, the database query statement comprising a main query and a subquery, a query condition of the subquery being a related condition of the main query, the main query comprising a filter condition for filtering based on a query result of the subquery, and partition table data of the main query and the subquery being distributed on multiple servers; a querying module configured to query target data from the partition table data of the main query, the target data comprising first data related to the main query and second data related to the subquery; a distributing module configured to distribute the first data and the second data to a target server, the target server being capable of obtaining all partition table data of the subquery; the querying module is further configured to: based on the second data, perform a query on the partition table data of the subquery in parallel to obtain a first query result; and a filtering module configured to perform a filtering operation on the first data in parallel using the filter condition according to the first query result.

[0009] Optionally, the distributing module is further configured to distribute the first data and the second data to the target server by adaptively selecting a hash or random distribution manner.

[0010] Optionally, the apparatus further comprises: a collecting module configured to collect a distribution condition of the first data and the second data; and a judging module configured to judge whether to distribute the first data and the second data to the target server by a hash or random distribution manner according to the distribution condition.

[0011] Optionally, the target server obtains all data table data of the subquery through a DISTRIBUTED TABLE SCAN operator.

[0012] In a third aspect, a computer readable storage medium is provided, the computer readable storage medium storing a computer program, the computer program being executed to implement the method of the first aspect.

[0013] In a fourth aspect, a computer program product is provided, comprising executable code, the executable code being executed to implement the method of the first aspect.

[0014] The scheme of the embodiment of the present disclosure distributes the first data related to the main query and the second data related to the subquery to the target server, which is a server capable of obtaining all the partition table data of the subquery. Since the target server can perform tasks in parallel, the target server can execute the subquery based on the second data in parallel, and perform the filtering operation on the first data in parallel, that is, the filtering condition of the subquery can be executed in parallel, thereby improving the execution performance of the SQL statement. BRIEF DESCRIPTION OF DRAWINGS

[0015] Figure 1 A system example diagram of a distributed database provided by an embodiment of the present disclosure.

[0016] Figure 2 A flowchart of a method for processing a database query statement provided by an embodiment of the present disclosure.

[0017] Figure 3 A flowchart of a method for processing a database query statement provided by another embodiment of the present disclosure.

[0018] Figure 4 A flowchart of a method for processing a database query statement provided by another embodiment of the present disclosure.

[0019] Figure 5 A structure diagram of an apparatus for processing a database query statement provided by an embodiment of the present disclosure.

[0020] Figure 6 A structure diagram of an apparatus for processing a database query statement provided by another embodiment of the present disclosure. DETAILED DESCRIPTION

[0021] The technical scheme in the embodiments of the present disclosure will be described clearly and completely. Obviously, the described embodiments are only part of the embodiments of the present disclosure, not all the embodiments.

[0022] With the development of the Internet and the continuous increase of data volume, distributed databases gradually replace single-machine databases. In database use, read operations account for most of database operations, which is reflected in SQL that data query statements are frequently used. In a SQL statement, the structure composed of "select-from-where" is called a query block. When a query block is nested in another query block as a query condition, it is called a subquery.

[0023] A SQL statement can be composed of a main query and a subquery. The main query can also be referred to as an outer query, and the subquery can also be referred to as an inner query. If the execution of the subquery does not depend on the query result of the main query, the subquery is a non-correlated subquery. The non-correlated subquery is executed from inside to outside, and each query needs to be executed only once. That is, the non-correlated subquery is executed first, and the execution result of the subquery is cached, and then the main query is executed.

[0024] If the execution of the subquery depends on the query result of the main query, the subquery is a correlated subquery. In the SQL containing the correlated subquery, the subquery needs to be traversed once for each row of the main query. This execution method makes the execution time of the SQL containing the correlated subquery increase with the increase of the number of rows of the main query. For the correlated subquery with complex nesting level, the execution time of the SQL increases exponentially.

[0025] Specifically, the correlated subquery indicates that there is a certain condition association between two queries. The following describes the concept of the correlated subquery according to the following SQL statement.

[0026] SELECT T2.v2

[0027] FROM T2

[0028] WHERE T2.v2=(SELECT Sum(v2)

[0029] FROM T1

[0030] WHERE T2.v1<T1.v1);

[0031] The above SQL statement contains a main query and a subquery. The main query statement is "SELECT T2.v2 FROM T2 WHERE T2.v2=( )", and the subquery statement is "(SELECT Sum(v2) FROM T1 WHERE T2.v1<T1.v1)". The subquery statement includes the expression "WHERE T2.v1<T1.v1", and obviously, the expression references the field v2 in the T2 table of the main query object. That is, the query result of the T1 table of the subquery object depends on the T2 table of the main query object. Therefore, the subquery is a correlated subquery.

[0032] In addition, the main query includes a filter condition based on the query result of the subquery, and the filter condition can also be referred to as a subquery filter condition. Still taking the above SQL statement as an example, the filter condition can refer to "WHERE T2.v2=( )".

[0033] In a correlated subquery, the statement refers to the main query, so the expression that makes the subquery associated with the main query is called a correlated expression. As an example, the expression "where T2.v1 < T1.v1" in the above SQL is a correlated expression that makes the subquery object T1 associated with the main query object T2, so "where T2.v1 < T1.v1" is the correlated expression of the correlated subquery, which is also called a correlated condition.

[0034] The execution process of the correlated subquery in the above SQL statement is described as follows:

[0035] In the first step, a tuple (i.e., a row) is taken from the main query object table T2, and the value of the tuple's related column v1 (i.e., T2.v1) is passed to the subquery.

[0036] In the second step, the subquery executes a complete subquery on the T1 table with the value of the T2.v1 column as a condition, and the query result is passed to the main query. The execution of a complete subquery is called a loop.

[0037] In the third step, a filtering operation is performed. The query result of the subquery is compared with the value of the tuple's related column v2 (i.e., T2.v2) to determine whether the value of the T2.v2 column meets the filtering condition.

[0038] In the fourth step, the next tuple is taken from the main query object table T2, and the first to third steps are executed in a loop until all tuples in the main query object table T2 are processed.

[0039] When a database processes a SQL statement, an execution plan is generated for the execution of the SQL statement. It needs to be explained that the execution plan is the execution path or algorithm of the SQL statement. The execution plan affects the execution performance of the SQL statement. In some scenarios, an optimizer may find an execution method with less resource usage among many execution plans and display it.

[0040] For a SQL statement with dependent subqueries (i.e., a correlated subquery), the database generates a subquery execution plan (subplan). In a distributed database, the execution logic of the subplan is usually to pull the data involved in the main query (such as T2.v2 and T2.v1 in the above example) locally, then execute a complete subquery for each row of the data involved in the main query, and pull the execution result locally. The calculation of the subquery-related filtering condition in the local execution is also called the calculation of the subquery filter condition. It needs to be explained that the correlated filtering condition is the SQL statement that filters the subquery result, for example, it can be the where statement that matches the subquery result with the main query.

[0041] The following describes the execution process of the correlated subquery in the above SQL statement:Figure 1 The execution process of a SQL statement including a related subquery in a distributed database is introduced.

[0042] Figure 1 A structure example of a distributed database is provided for the embodiments of the present application. Figure 1 The distributed database 100 in the figure can include a client node 110, a coordinator node 120 and a computer node 130.

[0043] The client node 110, for example, a client including a computer, a tablet, a mobile phone, etc., is used to send a SQL statement of a required query.

[0044] The coordinator node 120, also called a coordinator, interacts with the client node 110, is responsible for providing a query interface to the client node 110, receiving a SQL statement sent by the client node 110, and converting the SQL statement into a plurality of executable subquery plans and distributing them to the computer node 130.

[0045] The computer node 130 is responsible for receiving a query task sent by the coordinator node 120 and executing a subquery plan. The computer node 130 has a computing and data storage function. As an example, the distributed database can further include a storage node for storing data. The present disclosure does not limit the structure of the distributed database.

[0046] Specifically, in the distributed database, the execution process of the SQL statement is as follows: the coordinator node 120 converts the received SQL statement into a plurality of subquery plans which can be executed in parallel on different computer nodes 130, and selects a suitable computer node 130 according to the CPU utilization, the idle memory size and other information of each computer node to send. The computer node 130 executes the subquery plan and returns the result to the coordinator node. The coordinator node 120 sends the computing result to the client node 110.

[0047] It should be noted that the process of sending the subquery execution result to the coordinator node 120 by the computer node 130 can be called pulling back locally. That is, the traditional subquery execution plan needs to pull back the query result of the main query and the query result of the subquery to the local to execute the calculation of the filter condition of the related subquery in the local.

[0048] In some application scenarios, the computer node 130 can be deployed on multiple servers, such as Figure 1The computing nodes 1 to N shown in the middle are respectively deployed on N servers. The coordination node 120 can only be deployed on one server. In other words, any one of the computing nodes 130 can be the coordination node 120, but there can only be one coordination node 120.

[0049] It should be noted that the SQL semantics require that the filter condition of the subquery can only return one loop result each time when the subquery data is executed. If the subquery returns multiple loop results at a time, the system will provide an error. Since the filter condition of the subquery is placed locally and executed in a single thread in the traditional execution plan, there is a problem of poor execution efficiency.

[0050] The specific implementation process of the subquery execution plan in the distributed database will be described below in conjunction with the SQL (1) in example (1).

[0051] The SQL (1) statement is as follows:

[0052] creat table T1(v1 int, v2 int) partition by hash(v1) partitions 4;

[0053] creat table T2(v1 int, v2 int);

[0054] creat table index T1_idx1 on T1(v2) local;

[0055] SELECT T2.v2

[0056] FROM T2

[0057] WHERE T2.v2=(SELECT Sum(v2)

[0058] FROM T1

[0059] WHERE T2.v1<T1.v1);

[0060] In the above SQL (1) statement, the partition table T2 of the main query and the partition table T1 of the subquery are distributed on multiple servers. The aggregate calculation (i.e., sum calculation) of the subquery needs to be recalculated for each row in T2. Then, the aggregate calculation result is matched with the v2 value of the row in T2 to see if it meets the condition, and if it meets the condition, the query result is returned. As an example, the filter condition of the subquery in SQL (1) is "WHERE T2.v2=(…)".

[0061] When executing SQL (1) in the distributed database, the following execution plan (1) is generated:

[0062] ID OPERATOR NAME 0 SUBPLAN FILTER 1 PX COORDINATOR 2 EXCHANGE OUT DISTR : EX10000 3 PX PARTITION ITERATOR 4 TABLE SCAN T2 5 SCALAR GROUP BY 6 PX COORDINATOR 7 EXCHANGE OUT DISTR : EX20000 8 MERGE GROUP BY 9 PX PARTITION ITERATOR 10 TABLE SCAN T1

[0063] In some application scenarios, an execution plan can be divided into multiple local sub-plans, and each local sub-plan can contain one or more operators. For example, local sub-plans can be divided by the "EXCHANGE" operator as a boundary. As in the execution plan (1), the 2nd operator to the 4th operator can constitute a local sub-plan of the main query (which can also be referred to as a data flow operation (DFO)); the 7th operator to the 10th operator can constitute a local sub-plan of the sub-query.

[0064] As an example, a local sub-plan can be executed by multiple threads. For example, when the main query partition table T2 is distributed on multiple servers, multiple threads can be used to scan the partition table T2 on the multiple servers in parallel.

[0065] The specific execution process of the execution plan (1) is as follows:

[0066] The 1st operator "PX COORDINATOR" (i.e., the coordinator) initiates multiple threads to execute the scan of the main query partition table T2 and sends the scan results to the coordinator.

[0067] The coordinator starts the sub-query execution plan and initiates multiple threads to execute the scan of the sub-query partition table T1. As an implementation, each thread of the sub-query will execute the scan of the T1 table with the relevant query results (such as T2.v1) of the previous main query.

[0068] Each thread of the sub-query will get a query result, and each thread will return its query result to the coordinator. In some embodiments, the query result can be an aggregated result, i.e., each thread can aggregate the query result of the partition table data it queries. In other embodiments, the query result can be a data set, such as a set of T1.v1.

[0069] After the coordinator receives the query result of each thread of the sub-query, it can perform a global aggregation on the query result of each thread to obtain the execution result of the sub-query.

[0070] After the coordinator obtains the execution result of the sub-query, it performs the calculation of the relevant sub-query filter condition, i.e., the calculation of the "WHERE T2.v2 = (...)" condition, which, as described above, is completed by a single thread.

[0071] Therefore, it can be seen that in the above execution plan (1), the aggregation calculation of the subquery and the calculation of the subquery filter condition cannot be parallel.

[0072] In addition, when the SQL (1) is used as an execution condition of a higher-level SQL statement or needs to be connected with other tables, and the execution result of the SQL (1) needs to be sent to other SQL query statements, the execution plan (1) will generate an execution single point at the subquery filter, which causes the sending process to also be unable to be executed in parallel.

[0073] In summary, in the process of executing the SQL statement containing the related subquery in the distributed database, the subquery calculation and the subquery filter condition calculation cannot be parallel, which leads to poor execution performance. Moreover, there is no good optimization method at present. Especially when the number of returned rows of the main query is large or the subquery is a relatively complex distributed plan, the execution performance will be worse.

[0074] Based on the above problems, the embodiment of the present disclosure provides a new execution manner, and the calculation of the subquery filter condition is not performed by the coordination node but by the target server in the calculation node. The coordination node can send the related data in the partition table data of the main query to the target server, and the target server can execute the calculation of the subquery filter condition in parallel, so as to improve the execution performance of the SQL statement.

[0075] Figure 2 The flowchart of the method 200 for processing the database query statement provided by the embodiment of the present disclosure is shown. It should be understood that, Figure 2 The steps or operations of the method 200 are shown, but these steps or operations are only examples, or these steps can be executed in other orders. The method 200 can include steps S210 to S250, which are shown as follows.

[0076] In step S210, a database query statement is received. The database query statement includes a main query and a subquery, the query condition of the subquery is a related condition of the main query, and the partition table data of the main query and the subquery is distributed on multiple servers.

[0077] The database query statement can be any SQL statement described above. Since the database query statement includes a main query and a subquery, the query condition of the subquery is a related condition of the main query, and thus the database query statement can be a SQL statement containing a related subquery.

[0078] In the distributed database, the partition table data of the main query and the subquery can be distributed on multiple servers. As an example, one or more partitions of the data of the main query and / or the subquery can be stored on each server.

[0079] Referring to example (1), the partition table data T2 of the main query can be distributed on two servers server 1 and server 2, and the partition table data T1 of the subquery can be distributed on three servers server 3, server 4 and server 5.

[0080] In some embodiments, the main query can include a filter condition for filtering the query result of the subquery. The filter condition can be the subquery filter condition described above. Taking the above SQL (1) as an example, the filter condition is the “WHERE T2.v2 = (…)” statement.

[0081] In step S220, the target data is queried from the partition table data of the main query. The target data can include first data related to the main query and second data related to the subquery.

[0082] Taking the above SQL (1) as an example, the first data can be T2.v2, and the second data can be T2.v1. The second data can be understood as the data in the partition table data of the main query that is associated with the subquery.

[0083] The coordinator initiates multiple worker threads to query the partition table data of the main query according to the distribution of the partition table data of the main query, and obtains the target data. The target data can be, for example, a row of data in the partition table data of the main query, or one or more values in the row of data. For example, the target data can include the v1 column and the v2 column data in the partition table data of the main query. For another example, the target data can be the entire T2 table.

[0084] As an example, since the server 1 and the server 2 both store the data of the T2 table, the coordinator can send worker threads to the server 1 and the server 2 to execute the query of the T2 table. For example, the coordinator initiates 3 threads on the server 1 and the server 2 respectively to scan the partition table data T2 of the main query, and obtains the T2.v1 and T2.v2 values in each row of data in T2.

[0085] The above step S220 can be performed by a computing node of the main query. The computing node can be, for example, a computing node that stores the partition table data of the main query. There can be one or more worker threads on a computing node. If there are multiple worker threads on a computing node, the multiple worker threads can perform the query of the partition table data of the main query in parallel.

[0086] In step S230, the first data and the second data are distributed to target servers. The target servers can obtain all partition table data of the subquery. The number of the target servers can be multiple.

[0087] In some embodiments, the process can be performed by the coordination node described above. The worker of the main query can send the first data and the second data to the coordination node, and then the coordination node distributes the first data and the second data to the target servers. In other embodiments, the process can be performed by the worker of the main query. After the worker of the main query obtains the first data and the second data, the worker can directly distribute the first data and the second data to the target servers.

[0088] The target servers are servers to which the partition table data of the subquery is distributed. The target servers can include multiple servers, such as server 3, server 4, and server 5 described above.

[0089] When distributing the first data and the second data to the target servers, the first data and the second data of the same row can be distributed to the same server. For example, T2.v1 and T2.v2 of the first row in the T2 table can be distributed to server 3, T2.v1 and T2.v2 of the second row in the T2 table can be distributed to server 4, and T2.v1 and T2.v2 of the third row in the T2 table can be distributed to server 5.

[0090] There can be one or more workers on a target server. Distributing the first data and the second data to the target servers can be understood as distributing the first data and the second data to the workers of the target servers. The coordination node can initiate one or more workers on the target servers according to the distribution of the partition table data of the subquery. The coordination node can distribute the first data and the second data to each worker. In some embodiments, the coordination node can distribute the first data and the second data of the same row to the same worker.

[0091] In step S240, the query of the partition table data of the subquery is performed in parallel based on the second data, and a first query result is obtained. The process can be performed by the target servers.

[0092] The target servers can execute the query of the partition table data of the subquery based on the second data as a query condition. The coordination node can send different second data to different target servers, and different target servers can perform the query operation in parallel based on the received second data.

[0093] Still taking the server 3, the server 4 and the server 5 as an example, the server 3 receives the T2.v1 of the first row in the T2 table, and the server 3 can execute the query on the T1 table by taking the T2.v1 of the first row as the query condition. The server 4 receives the T2.v1 of the second row in the T2 table, and the server 4 can execute the query on the T1 table by taking the T2.v1 of the second row as the query condition. The server 5 receives the T2.v1 of the third row in the T2 table, and the server 5 can execute the query on the T1 table by taking the T2.v1 of the third row as the query condition. Since the server 3, the server 4 and the server 5 receive different rows of T2.v1, the server 3, the server 4 and the server 5 can execute the query on the T1 table in parallel.

[0094] The first query result is the query result of the subquery. Taking the SQL (1) as an example, the first query result is the execution result of "(SELECT Sum(v2) FROM T1 WHERE T2.v1 < T1.v1)". The first query result can include the query results for different rows of T2.v1.

[0095] In step S250, according to the first query result, the filtering operation on the first data is executed in parallel using the filtering condition.

[0096] In the embodiments of the present disclosure, the filtering condition of the subquery is executed on the target server.

[0097] In the conventional scheme, the query result of the subquery needs to be returned to the coordination node, and the filtering operation is executed by the coordination node. Unlike the conventional scheme, in the method 200, the coordination node directly distributes the query result (such as the target data) of the main query to the subquery. For example, the coordination node initiates multiple worker threads on the target server according to the data distribution of the subquery partition table, and the multiple worker threads execute the scan of the data of the subquery partition table in parallel after receiving the target data of the main query.

[0098] Still taking the server 3, server 4 and server 5 as an example, the server 3 can take T2.v1 of the first row of the T2 table as a query condition to query the T1 table to obtain a query result 1, and further, the server 3 can perform a filtering operation, that is, compare the query result 1 with T2.v2 of the first row of the T2 table to determine whether T2.v2 of the first row of the T2 table is equal to the query result 1. If yes, T2.v2 of the first row of the T2 table is returned; if no, no data is returned. The server 4 can take T2.v1 of the second row of the T2 table as a query condition to query the T1 table to obtain a query result 2, and further, the server 4 can perform a filtering operation, that is, compare the query result 2 with T2.v2 of the second row of the T2 table to determine whether T2.v2 of the second row of the T2 table is equal to the query result 2. If yes, T2.v2 of the second row of the T2 table is returned; if no, no data is returned. The server 5 can take T2.v1 of the third row of the T2 table as a query condition to query the T1 table to obtain a query result 3, and further, the server 5 can perform a filtering operation, that is, compare the query result 3 with T2.v2 of the third row of the T2 table to determine whether T2.v2 of the third row of the T2 table is equal to the query result 3. If yes, T2.v2 of the third row of the T2 table is returned; if no, no data is returned.

[0099] In some embodiments, multiple worker threads can run on one target server. For example, 2 threads can run on each of the server 3, server 4 and server 5. In this case, the coordinator can initiate 2 threads on the server 3, server 4 and server 5 respectively to perform the scan of the subquery partitioned table data T1, that is, the coordinator can distribute the target data of the main query in step S220 to the 6 threads to realize the parallel scan of the subquery partitioned table data T1. The execution process of each worker thread is similar to the execution process of the server 3, server 4 and server 5 described above, and is not described herein again for brevity.

[0100] In some embodiments, the coordinator distributes the first scan result to the target servers by selecting an adaptive random or hash distribution manner. The adaptive random or hash distribution manner can refer to a manner of dynamically selecting to use random or hash to distribute data according to the distribution situation of data during execution.

[0101] As an example, the random distribution method can be a round robin distribution method. It should be noted that the round robin distribution method can uniformly distribute data to each thread of the subquery, so that the round robin distribution method can ensure that the data obtained on each thread is uniform, preventing data skew.

[0102] The hash distribution method can also uniformly distribute data to each thread of the subquery. However, for the hash distribution method, if the same row data appears in the partition table data of the main query, the hash distribution method will distribute the same row data to the same thread. Since the thread has cached the previous calculation results of the subquery about the data, when the same row data is received, the thread does not need to repeatedly calculate the subquery, thereby improving the execution efficiency.

[0103] However, in some application scenarios, if there are many same data rows in the main query, the hash distribution method will distribute all the same data rows to the same thread. In this way, the thread will be in a working saturated state, while other threads may be in an idle state, resulting in data distribution skew, thereby affecting the execution efficiency.

[0104] If the random distribution method is used, each row of data of the main query will be distributed to different working threads of the subquery in turn, so that although the problem of data distribution skew can be avoided, the execution performance improvement due to the same row data without calculating the subquery cannot be enjoyed.

[0105] To solve this problem, the method 200 provided by the embodiments of the present disclosure uses an adaptive random or hash method to distribute the first data and the second data of the main query to all working threads of the subquery.

[0106] Specifically, the first scan result of the main query can be distributed according to a default data distribution method first, for example, the hash distribution method is used to distribute the first data and the second data to the working threads of the subquery.

[0107] Then, the distribution result of the specified row is sampled. If the hash distribution result is not uniform, the random distribution method is used to continue the distribution. If the hash distribution result is uniform, the hash distribution method can be continued to be used.

[0108] As an implementation manner, the sampling can be that each worker thread of the main query reports its distribution result to the coordination node, the coordination node aggregates the distribution results, and decides which distribution manner to use according to the aggregated distribution results. After the coordination node determines to use the hash or random distribution manner, the coordination node synchronizes the decision result to all worker threads of the main query.

[0109] As an embodiment, the target server can obtain all partition table data of the subquery through the “DISTRIBUTED TABLE SCAN” operator. That is, the operator “DISTRIBUTED TABLE SCAN” provides the ability of distributed scanning data, and thus the worker thread on the target server can perform the scanning of the subquery and the execution of the subquery filter condition in parallel.

[0110] The target server obtains the first scanning result, that is, the worker thread of the subquery obtains the first scanning result of the main query, can perform the scanning of the partition table data of the subquery in parallel, and after obtaining the subquery result, can perform the calculation of the subquery filter condition in parallel.

[0111] According to the above execution process, it can be seen that the method 200 provided by the present disclosure can realize the parallel scanning of the subquery, and thus the calculation of the subquery can be performed in parallel, and the calculation of the subquery filter condition can also be performed in parallel. In addition, if the query result calculated on the target server needs to be sent to other SQL statements, the process can also be performed in parallel. Therefore, the new execution plan provided by the embodiment of the present disclosure can greatly improve the execution performance of the SQL statement.

[0112] The method provided by the embodiment of the present disclosure will be further described below in combination with Figure 3 and example (2). Figure 3 The method flowchart for executing the SQL (1) statement is provided for an embodiment of the present disclosure. Example (2) is the execution plan (2) generated by executing the SQL (1) statement in example (1) according to the method provided by the embodiment of the present disclosure, and the execution plan (2) is as follows:

[0113]

[0114]

[0115] The execution logic of the above execution plan (2) is as follows:

[0116] In step S310, the parallel execution (PX) schedules the DFO where the main query and the subquery are located to execute.

[0117] Operators 3 through 6 in execution plan (2) are local subplans of the main query, such as the DFO where the main query resides, and are executed on the server where the main query partition table data T2 is distributed. Operators 7 and 8 are local subplans of the subquery, such as the DFO where the subquery resides, and are executed on the server where the subquery partition table data T1 is distributed, such as the target server mentioned above.

[0118] PX is the operator No. 0 "PX COORDINATOR" in the execution plan (2), which is executed on the coordinator. In other words, the coordinator schedules the DFO where the main query and subquery are located to start the scan through the PX operator.

[0119] In step S320 , the multiple worker threads of the main query execute a scan of the T2 data, and the data is distributed to the worker threads of the subqueries using a hash method by default.

[0120] The DFO in which the main query resides uses multiple threads to scan the partition table data T2 of the main query, obtaining a row of data in partition table data T2. This row of data is then distributed to the multiple threads in which the subqueries reside using hash distribution. This distribution process can be implemented, for example, by using operator #4 "EXCHANGE OUT DISTR (Adaptive)" in execution plan (2).

[0121] Step S330: Adaptive sampling decisions are distributed using a hash or random method.

[0122] Figure 4 This is an example flow chart of a data distribution process provided by an embodiment of the present disclosure. Figure 4 As shown, PX samples the distribution results of the main query and synchronizes the distribution method of the decision to the main query for continued distribution.

[0123] Specifically, PX samples the data distribution of multiple threads in the main query, for example, the first 1,000 rows of the main query. As an implementation, each thread in the main query reports its own data distribution status to PX, which then aggregates the results.

[0124] If the distribution result is uniform, or the uniformity of the distribution result is within the preset range, or the distribution result does not cause data distribution skew, then the original hash distribution method will be continued. At the same time, PX will synchronize the decision to use hash distribution method to all worker threads of the main query, so that the worker threads of the main query will continue to use hash distribution method to send scan results to the worker threads of the subquery.

[0125] If the distribution result is uneven, or the uniformity of the distribution result is not within the preset range, or the distribution result has caused data distribution skew, the PX changes the original hash distribution method to the random distribution method to distribute the data. Meanwhile, the PX synchronizes the decision of using the random distribution method to all worker threads of the main query, so that the worker threads of the main query use the random distribution method to send the scan result of the main query to the worker threads of the sub query.

[0126] When the data distribution of the main query reaches the specified number of rows (i.e., 1000 rows), the PX again decides to use the random or hash distribution method according to the summary distribution result to continue distributing the scan result of the main query, and synchronizes the decision result to all worker threads of the main query. Repeating the above process can effectively avoid the problem of data distribution skew, so that the data distribution is within a controllable range.

[0127] In step S340, the worker thread where the sub query is located receives the row data of the main query (such as the first data and the second data described above), scans the sub query data in parallel, and calculates the filter condition.

[0128] After receiving the scan result distributed by the main query, multiple threads of the DFO where the sub query is located perform a scan on the partition table T1 of the sub query in parallel, where the No. 8 operator "DISTRIBUTED TABLE SCAN" provides the ability to distribute the scanned data.

[0129] The scan result of the sub query and the previously obtained scan result of the main query are matched, and the rows that satisfy the filter condition of the sub query are returned. This process is performed in parallel by multiple threads.

[0130] In step S350, the rows that satisfy the filter condition are returned to the client.

[0131] In step S340, the rows that satisfy the filter condition are obtained, and the rows are returned to the client.

[0132] In summary, the embodiments of the present disclosure propose a new execution mode, which distributes the scan result of the main query into the sub query through an adaptive distribution method. The execution mode supports the parallel execution of the filter condition of the sub query by splitting the data of the main query, so that the execution performance of the related execution plan can be linearly improved with the increase of the parallel degree. On this basis, the embodiments of the present disclosure also creatively propose to adaptively select the hash or random distribution method to distribute the result of the main query, thereby optimizing the problem of poor performance caused by data distribution skew in the parallel process.

[0133] The above is described in combination with Figures 1-4, the detailed description of the method embodiments provided by the present disclosure is described in detail. The following is combined with Figures 5-6 The device embodiments provided by the present disclosure are described in detail. It should be understood that the description of the device embodiments corresponds to the description of the method embodiments, and therefore, the parts not described in detail can be referred to the previous method embodiments.

[0134] Figure 5 is a structural schematic diagram of the device for processing database query statements according to an embodiment of the present disclosure. The device can be a database, such as a distributed database. In some scenarios, the device can be a native distributed database, where the native distributed database can be a self-developed distributed database, which is not a secondary development or encapsulation of an existing distributed database. Of course, the device can also be other databases, which are not limited by the embodiments of the present disclosure. The device is introduced below in combination with Figure 5 The device applying the embodiments of the present disclosure is introduced.

[0135] Figure 5 The device 500 in the device 500 can include a receiving module 510, a query module 520, a distribution module 530, and a filtering module 540.

[0136] The receiving module 510 is configured to receive a database query statement, the database query statement including a main query and a subquery, a query condition of the subquery being a related condition of the main query, the main query including a filter condition for filtering based on a query result of the subquery, and partition table data of the main query and the subquery being distributed on multiple servers.

[0137] The query module 520 is configured to query target data from the partition table data of the main query, the target data including first data related to the main query and second data related to the subquery.

[0138] The distribution module 530 is configured to distribute the first data and the second data to a target server, the target server being capable of obtaining all partition table data of the subquery.

[0139] The query module 520 is further configured to perform, in parallel, a query on the partition table data of the subquery based on the second data, to obtain a first query result.

[0140] The filtering module 540 is configured to perform, in parallel, a filtering operation on the first data using the filter condition according to the first query result.

[0141] Optionally, the distribution module is further configured to distribute the first data and the second data to the target server by adaptively selecting a hash or a random distribution mode.

[0142] Optionally, the apparatus 500 further comprises: a collecting module, configured to collect distribution conditions of the first data and the second data; and a judging module, configured to judge, according to the distribution conditions, whether to distribute the first data and the second data to the target server by using the hash or random distribution mode.

[0143] Optionally, the target server obtains all partition table data of the subquery by using a DISTRIBUTED TABLE SCAN operator.

[0144] Figure 6 is a structural diagram of an apparatus for processing a database query statement provided by another embodiment of the present disclosure. Figure 6 The apparatus 600 shown can be a database or a server. The apparatus 600 can include a memory 610 and a processor 620.

[0145] The memory 610 can be configured to store executable code. The processor 620 can be configured to execute the executable code stored in the memory 610 to implement the steps in the various methods described above.

[0146] In some embodiments, the apparatus 600 can further include a network interface 630, and data exchange between the processor 620 and external devices can be achieved through the network interface 630.

[0147] In the embodiments described above, all or some of the embodiments can be implemented by software, hardware, firmware or any combination thereof. When implemented by software, all or some of the embodiments can be implemented in the form of a computer program product storing computer program instructions. When the computer program instructions are loaded into and executed by a computer, all or some of the procedures or functions according to the embodiments of the present disclosure are generated. The computer can be a general purpose computer, a special purpose computer, a computer network, or other programmable apparatus. The computer program instructions can be stored in a computer readable storage medium or transmitted from one computer readable storage medium to another computer readable storage medium, for example, the computer program instructions can be transmitted from one website, computer, server or data center to another website, computer, server or data center through wired (such as coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (such as infrared, wireless, microwave, etc.) manner. The computer readable storage medium can be any available medium that can be accessed by a computer or a data storage device such as a server, data center, etc. integrated with one or more available media. The available media can be a magnetic medium (such as a floppy disk, a hard disk, a magnetic tape), an optical medium (such as a digital video disc (DVD)), or a semiconductor medium (such as a solid state disk (SSD)), etc.

[0148] Those skilled in the art can realize that the units and algorithm steps of each example described in connection with the embodiments of the present disclosure can be realized by electronic hardware, or a combination of computer software and electronic hardware. Whether the functions are performed by hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of the present disclosure.

[0149] In several embodiments provided by the present disclosure, it should be understood that the disclosed system, device and method can be implemented in other ways. For example, the device embodiments described above are merely schematic, for example, the division of the units is only a logical function division, and actual implementation can have another division manner, for example, a plurality of units or components can be combined or integrated into another system, or some features can be ignored or not executed. In addition, the coupling or direct coupling or communication connection between the units or components shown or discussed can be indirect coupling or communication connection through some interfaces, devices or units, and can be electrical, mechanical or other forms.

[0150] The units described as separate components may or may not be physically separate, and the components displayed as units may or may not be physical units, that is, may be located in one place, or may also be distributed to multiple network units. Part or all of the units can be selected to achieve the purpose of the embodiment of the present application according to actual needs.

[0151] In addition, each functional unit in various embodiments of the present disclosure can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit.

[0152] The above is only a specific embodiment of the present disclosure, but the protection scope of the present disclosure is not limited thereto, and any person skilled in the art can easily think of changes or replacements within the technical range disclosed by the present disclosure, which should be covered within the protection scope of the present disclosure. Therefore, the protection scope of the present disclosure should be subject to the protection scope of the claims.

Claims

1. A method for processing a database query statement, comprising: Receive a database query statement, the database query statement including a main query and a subquery, the query condition of the subquery being a related condition of the main query, the main query including a filtering condition for filtering based on a query result of the subquery, and partition table data of the main query and the subquery being distributed on multiple servers; Querying target data from the partition table data of the main query, the target data including first data related to the main query and second data related to the subquery; Distributing the first data and the second data of the same row to the same target server, where the target server can obtain all partition table data of the subquery, and the target server includes multiple servers; Based on the second data, the target server executes a query on the partition table data of the subquery in parallel to obtain a first query result; According to the first query result, a filtering operation on the first data is performed in parallel using the filtering condition.

2. The method according to claim 1, wherein distributing the first data and the second data of the same row to the same target server comprises: The first data and the second data of the same row are distributed to the same target server by adaptively selecting a hash or random distribution method.

3. The method according to claim 1, further comprising: collecting distribution information of the first data and the second data; According to the distribution situation, it is determined whether to use a hash or random distribution method to distribute the first data and the second data of the same row to the same target server.

4. The method according to claim 1, wherein the target server is able to obtain all partition table data of the subquery, including: The target server obtains all partition table data of the subquery through the DISTRIBUTED TABLE SCAN operator.

5. A device for processing a database query statement, comprising: a receiving module, configured to receive a database query statement, the database query statement including a main query and a subquery, the query conditions of the subquery being related conditions of the main query, the main query including a filtering condition for filtering based on the query results of the subquery, and the partition table data of the main query and the subquery being distributed on multiple servers; A query module, configured to query target data from the partition table data of the main query, wherein the target data includes first data related to the main query and second data related to the subquery; a distribution module, configured to distribute the first data and the second data of the same row to the same target server, where the target server can obtain all partition table data of the subquery, and the target server includes multiple servers; The query module is further configured to: based on the second data, the target server executes a query on the partition table data of the subquery in parallel to obtain a first query result; A filtering module is used to perform a filtering operation on the first data in parallel using the filtering conditions according to the first query result.

6. The device according to claim 5, wherein the distribution module is further configured to distribute the first data and the second data of the same row to the same target server by adaptively selecting a hash or random distribution method.

7. The apparatus according to claim 5, further comprising: a collection module, configured to collect distribution information of the first data and the second data; The judgment module is used to judge whether to use a hash or random distribution method to distribute the first data and the second data of the same row to the same target server according to the distribution situation.

8. The device according to claim 5, wherein the target server obtains all partition table data of the subquery through a DISTRIBUTED TABLE SCAN operator.

9. A computer-readable storage medium having executable code stored thereon, which can implement the method according to any one of claims 1 to 4 when the executable code is executed.

Images