Optimization method, device, electronic device and storage medium

Abstract

An embodiment of the present invention discloses an optimization method, device, electronic device, and storage medium. The method includes: in the physical plan tree generation stage including a sorting operator, if the current environment is a multi-threaded environment, judging whether the next layer of operators of the sorting operator is loaded into the target statistical information, and obtaining a first judgment result; performing an optimization operation of adding a summary operator and a distribution operator to the physical plan tree to be generated according to the first judgment result; wherein the distribution operator is an operator for data distribution, and the summary operator is an operator for data aggregation. In a multi-threaded environment, the method performs an optimization operation of adding a summary operator and a distribution operator to the physical plan tree to be generated according to the first judgment result. By adding the summary operator and the distribution operator, the sorting operator can assist in performing operations related to data order to optimize the physical plan tree to be generated, thereby improving the efficiency of data processing.

Description

Technical Field

[0001] Embodiments of the present invention relate to the field of database processing technology, and in particular to an optimization method, device, electronic device, and storage medium. Background Art

[0002] In databases, multithreading can be used to manipulate the order of data, such as sorting data. Currently, multithreading is typically implemented by distributing data to multiple threads. Each thread then sorts its own data in parallel. Finally, each thread sends its data to the corresponding master thread, which merges and sorts all the data and outputs the result.

[0003] However, in the above implementation, if the amount of data is too large, the main thread will be under too much pressure to merge and sort, and may cause multiple merge and sort passes due to too many merge paths, thereby affecting the efficiency of data processing. Summary of the Invention

[0004] Embodiments of the present invention provide an optimization method, device, electronic device, and storage medium to improve the efficiency of data processing in a multi-threaded environment.

[0005] According to one aspect of the present invention, there is provided an optimization method, comprising:

[0006] In a physical plan tree generation phase including a sort operator, if the current environment is a multi-threaded environment, determining whether a next layer of operators passing through the sort operator is loaded into target statistical information to obtain a first determination result;

[0007] Performing an optimization operation of adding a summary operator and a distribution operator to the physical plan tree to be generated according to the first judgment result;

[0008] The target statistical information is the data statistical information of the sorting column corresponding to the sorting operator, the distribution operator is an operator for data distribution, and the summary operator is an operator for data summary.

[0009] According to another aspect of the present invention, there is provided an optimization device, comprising:

[0010] A judgment module is used to judge whether the next layer of operators passing through the sort operator is loaded into the target statistical information in the physical plan tree generation stage including the sort operator if the current environment is a multi-threaded environment, and obtain a first judgment result;

[0011] an optimization module, configured to perform an optimization operation of adding a summary operator and a distribution operator to the physical plan tree to be generated according to the first judgment result;

[0012] The target statistical information is the data statistical information of the sorting column corresponding to the sorting operator, the distribution operator is an operator for data distribution, and the summary operator is an operator for data summary.

[0013] According to another aspect of the present invention, an electronic device is provided, comprising:

[0014] at least one processor; and

[0015] a memory communicatively connected to the at least one processor; wherein,

[0016] The memory stores a computer program that can be executed by the at least one processor. The computer program is executed by the at least one processor to enable the at least one processor to perform the optimization method described in any embodiment of the present invention.

[0017] According to another aspect of the present invention, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement the optimization method described in any embodiment of the present invention when executed.

[0018] The technical solution of an embodiment of the present invention is as follows: during the generation phase of a physical plan tree including a sorting operator, if the current environment is a multi-threaded environment, it is determined whether the next layer of operators after the sorting operator is loaded into the target statistical information to obtain a first judgment result; based on the first judgment result, an optimization operation of adding a summary operator and a distribution operator to the physical plan tree to be generated is performed; wherein, the target statistical information is the data statistical information of the sorting column corresponding to the sorting operator, the distribution operator is an operator for data distribution, and the summary operator is an operator for data aggregation. In a multi-threaded environment, this solution performs the optimization operation of adding a summary operator and a distribution operator to the physical plan tree to be generated based on the first judgment result. By adding the summary operator and the distribution operator, the sorting operator can assist the sorting operator in performing operations related to data order to optimize the physical plan tree to be generated, thereby improving the efficiency of data processing.

[0019] It should be understood that the content described in this section is not intended to identify the key or important features of the embodiments of the present invention, nor is it intended to limit the scope of the present invention. Other features of the present invention will become readily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0021] Figure 1 A flowchart of an optimization method provided in Example 1 of the present invention;

[0022] Figure 2 A flowchart of an optimization method provided in Example 2 of the present invention;

[0023] Figure 3 A schematic diagram of an implementation of an unoptimized plan tree provided in the second embodiment of the present invention;

[0024] Figure 4 A schematic diagram of an optimized plan tree according to the second embodiment of the present invention;

[0025] Figure 5 A schematic diagram of another non-optimized plan tree implementation provided in the second embodiment of the present invention;

[0026] Figure 6 A schematic diagram of another optimized plan tree according to the second embodiment of the present invention;

[0027] Figure 7 A schematic structural diagram of an optimization device provided in Example 3 of the present invention;

[0028] Figure 8 This is a structural diagram of an electronic device provided in Example 4 of the present invention. DETAILED DESCRIPTION

[0029] In order to enable those skilled in the art to better understand the solutions of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the embodiments described are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts should fall within the scope of protection of the present invention.

[0030] It should be noted that the terms "first", "second", etc. in the description and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that the numbers used in this way can be interchanged where appropriate, so that the embodiments of the present invention described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions. For example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0031] Example 1

[0032] Figure 1 This is a flowchart of an optimization method provided in the first embodiment of the present invention. This embodiment is applicable to the case of optimizing data sequence in a multi-threaded environment. The method can be executed by an optimization device, which can be implemented in the form of hardware and / or software. The optimization device can be configured in an electronic device. In this embodiment, the electronic device includes but is not limited to: a server, a computer, a laptop, or a tablet computer. Figure 1 As shown, the method includes:

[0033] S110 , in the physical plan tree generation stage including the sort operator, if the current environment is a multi-threaded environment, determine whether the next layer of operators passing through the sort operator is loaded into the target statistical information to obtain a first determination result.

[0034] In this embodiment, the sorting operator may refer to an operator used to perform a sorting operation on data.

[0035] When operating a database through a structured query statement, an execution plan containing specific operations is generally generated based on the structured query statement. The execution plan can be implemented in the database as a "binary tree" composed of various operators, which can be understood as an execution plan tree; the operators can also be understood as plan execution nodes in the execution plan tree. The operators in the execution plan tree can be executed sequentially from bottom to top, which is not limited here. The intermediate plan tree can refer to an execution plan tree that is initially generated based on the structured query statement input by the user, so that the final execution plan tree can be obtained by subsequent optimization based on the intermediate plan tree. On this basis, the physical plan tree can be understood as the final execution plan tree obtained by optimization based on the intermediate plan tree. The physical plan tree generation stage can be understood as the stage of generating the physical plan tree based on the optimization of the intermediate plan tree.

[0036] The current environment can be understood as the computing environment in which the database currently processes data. The multithreaded environment can be understood as the environment in which the database uses multithreading technology to operate on data sequentially.

[0037] In a binary tree-structured execution plan, operators are generally connected in a top-down relationship. Therefore, operators below the current operator are referred to as next-level operators, and operators above the current operator are referred to as previous-level operators. Based on this, the next-level operators of a sort operator can be understood as the operators below the sort operator that are connected to it in the execution plan.

[0038] The target statistics can be understood as the data statistics of the sorting column corresponding to the sorting operator. For example, the target statistics can include the number of data items in the sorting column, the maximum value of the sorting column, and the minimum value of the sorting column. The sorting column can be understood as the column data corresponding to the sorting operator.

[0039] In this embodiment, during the physical plan tree generation phase including the sort operator, if the current environment is a multi-threaded environment, it is determined whether the next layer of operators passing through the sort operator is loaded into the target statistical information to obtain a first determination result.

[0040] This embodiment does not specifically limit how to determine whether the current environment is a multi-threaded environment. For example, whether the current environment is a multi-threaded environment can be determined based on relevant configuration information of the database.

[0041] The first judgment result may refer to a judgment result of whether the next layer of operators of the sorting operator can be loaded into the target statistical information. The first judgment result may include loading into the target statistical information through the next layer of operators of the sorting operator, or not loading into the target statistical information through the next layer of operators of the sorting operator.

[0042] S120: Execute an optimization operation of adding a summary operator and a distribution operator to the physical plan tree to be generated according to the first judgment result.

[0043] In this embodiment, the physical plan tree to be generated can be understood as a physical plan tree waiting to be generated. The distribution operator can be understood as an operator for performing data distribution, such as the distribution operator can be represented by the DIS operator. The aggregation operator can be understood as an operator for performing data aggregation, such as the aggregation operator can be represented by the GAT operator.

[0044] According to the first judgment result, an optimization operation of adding a summary operator and a distribution operator to the physical plan tree to be generated can be performed. The optimization operation can be understood as an operation of optimizing the physical plan tree to be generated to generate the final physical plan tree. It should be noted that the first judgment result is that the optimization can only be performed after the target statistical information is loaded. For example, a summary operator can be inserted between the sorting operator and the operator of the previous layer of the sorting operator, and a distribution operator can be inserted between the sorting operator and the operator of the next layer of the sorting operator to assist the sorting operator in performing corresponding processing on the data through the summary operator and the distribution operator; if the first judgment result is that the target statistical information is not loaded, no optimization is required, because the corresponding target statistical information is not loaded at this time, and the optimization will instead reduce the performance of the physical plan tree to be generated.

[0045] In this embodiment, different optimization operations can be performed by determining whether the previous operator of the sort operator is a merge join operator. A merge join operator can refer to an operator used to merge join data, or it can be considered an operator that performs data order-related processing. Aggregation operators and distribution operators can also assist in data order-related processing. Therefore, if the previous operator of the sort operator is a merge join operator, these two operators can share a single aggregation operator and a single distribution operator under certain conditions.

[0046] In one embodiment, the previous operator of the sort operator is not a merge join operator. Based on this, the process of performing the optimization operation of adding the summary operator and the dispatch operator to the physical plan tree to be generated according to the first judgment result of the sort operator can be: if the first judgment result of the sort operator is that the operator is not loaded into the target statistical information, the optimization can be exited; if the first judgment result of the sort operator is that the operator is loaded into the target statistical information, since the previous operator of the sort operator is not a merge join operator, a summary operator can be directly inserted between the sort operator and the previous operator of the sort operator, and a dispatch operator can be inserted between the sort operator and the next operator of the sort operator for optimization.

[0047] In one embodiment, the previous operator of the sort operator is a merge join operator. Based on this, the process of performing the optimization operation of adding the summary operator and the distribution operator to the physical plan tree to be generated according to the first judgment result of the sort operator may be: if the first judgment result of the sort operator is that the target statistical information is not loaded, then the optimization may be exited.

[0048] If the first judgment result of the sorting operator is that it is loaded into the target statistical information, a distribution operator can be inserted between the sorting operator and the operator in the next level of the sorting operator. It is understandable that a merge join operator generally has two child nodes. Based on this, if the two child nodes of the merge join operator include the sorting operator and another sorting operator other than the sorting operator, if it is determined that the operators in the next level of both sorting operators can be loaded into the corresponding target statistical information (i.e., the operators in the next level of the other sorting operator can be loaded into the corresponding target statistical information), it indicates that the other sorting operator can be optimized. In this case, a summary operator can be inserted between the merge join operator and the operator in the previous level of the merge join operator. If it is determined that the operators in the next level of the other sorting operator are not loaded into the corresponding target statistical information, it indicates that the other sorting operator is not suitable for optimization. In this case, a summary operator can be inserted between the sorting operator and the operator in the previous level of the sorting operator.

[0049] If the two child nodes of the merge join operator include a sorting operator and another non-sorting operator other than the sorting operator, a summary operator may be directly inserted between the merge join operator and the operator above the merge join operator.

[0050] Optionally, the method further comprises: in the physical plan tree generation phase, setting a first mark for the distribution operator and setting a second mark for the aggregation operator;

[0051] During the statement execution phase, the distribution operator containing the first tag is executed in the following manner: according to the processing scope corresponding to each thread, the first data within each thread that belongs to the corresponding processing scope is retained, and the second data that does not belong to the processing scope is distributed to the target thread, and the first result obtained by executing the distribution operator is sent to the upper-level operator of the distribution operator, wherein the target thread is the thread corresponding to the processing scope to which the second data belongs; the summary operator containing the second tag is executed in the following manner: the data of each thread is received and output in ascending order of the thread numbers, and the second result obtained by executing the summary operator is sent to the upper-level operator of the summary operator.

[0052] During the physical plan tree generation phase, after adding the distribution operator and the aggregation operator to the physical plan tree to be generated, a first flag may be set for the distribution operator and a second flag may be set for the aggregation operator. The first flag may be used to indicate that the distribution operator is to be executed according to the distribution optimization method being processed. Correspondingly, the second flag may be used to indicate that the aggregation operator is to be executed according to the aggregation optimization method being processed. The first and second flags may be used in the subsequent statement execution phase to execute the distribution operator and the aggregation operator according to the corresponding optimization method based on the flags on the distribution operator and the aggregation operator.

[0053] During the statement execution phase, the distribution operator containing the first tag (i.e., the distribution optimization method) can be executed in the following manner: according to the processing scope corresponding to each thread, the first data within each thread that belongs to the corresponding processing scope is retained, and the second data that does not belong to the processing scope is distributed to the target thread, and the first result obtained by executing the distribution operator is sent to the upper-level operator of the distribution operator, and the upper-level operator performs subsequent data processing.

[0054] The statement execution phase can be understood as the phase of executing structured query statements based on the physical plan tree. The target thread can be understood as the thread corresponding to the processing scope of the second data. The processing scope can be understood as the range of data that a thread can process. For each thread, the first data can be understood as the data within the processing scope corresponding to that thread; correspondingly, the second data can be understood as the data outside the processing scope of that thread. The first result can be understood as the data result obtained after executing the dispatch operator.

[0055] Optionally, before setting the first mark for the distribution operator, the method further includes: determining the number of threads and the processing range corresponding to each thread according to the target statistical information and the computing resource information, wherein the processing range corresponding to each thread is different.

[0056] Computing resource information can be understood as computing resource information used to process data, such as central processing unit performance information, memory size information, and input / output port performance information. There is no limitation here on how to determine the number of threads and the processing range corresponding to each thread based on the target statistical information and computing resource information. If the target statistical information indicates a large amount of data and the computing resource information has more resources available, a larger number of threads can be determined; on this basis, a total processing range is determined based on the maximum and minimum values ​​of the data in the target statistical information, and a sub-processing range is then allocated to each thread as the corresponding processing range. There is no specific limitation here and it can be flexibly set according to actual needs.

[0057] Optionally, after determining the number of threads and the processing scope of each thread based on target statistical information and computing resource information, the method further includes: controlling each thread to read an equal amount of data to be processed from the memory based on the ratio of the total amount of data to be processed to the number of threads, and the data to be processed read by each thread is different; wherein the data to be processed is the data required to be processed by the sorting operator.

[0058] After determining the number of threads and the processing scope of each thread based on target statistics and computing resource information, each thread is controlled to read an equal amount of data from the memory storing the corresponding data to be processed, based on the ratio of the total amount of data to be processed to the number of threads (i.e., determining an average as the amount of data read by each thread). To ensure that the time it takes for each thread to read data is roughly consistent and to prevent one thread from taking longer than other threads due to reading too much data, each thread is controlled to read an equal amount of data to be processed from the memory storing the corresponding data to be processed. Each thread reads different data to be processed, to avoid overlapping data read by threads and missing some data to be processed.

[0059] During the statement execution phase, the summary operator containing the second tag (i.e., the summary optimization method) can be executed in the following manner: the data of each thread is received and output in ascending order of the thread numbers, and the second result obtained by executing the summary operator is sent to the operator above the summary operator.

[0060] Among them, the thread number can refer to the number set for each thread, such as thread 0, thread 1, and thread 2. It is understandable that in order to ensure that the data received by the summary operator in the order of each thread number from small to large is ordered, each thread can be numbered according to the order of each thread's processing range. For example, assuming that the data to be processed is 100 different data in the range of 1-100, 3 threads are determined, and the processing ranges are respectively 1-30, 31-60, and 61-100; the thread number can be set according to the processing range of each thread, such as the thread with the frontmost processing range (i.e., 1-30 range) is numbered as thread 0, the thread with the 31-60 range is numbered as thread 1, and so on, the thread with the backmost processing range (i.e., 61-100 range) is numbered as thread 2.

[0061] This embodiment provides an optimization method. In the physical plan tree generation stage including a sorting operator, if the current environment is a multi-threaded environment, it is determined whether the next layer of operators after the sorting operator is loaded into the target statistical information to obtain a first judgment result. Based on the first judgment result, an optimization operation of adding a summary operator and a distribution operator to the physical plan tree to be generated is performed. The target statistical information is the data statistical information of the sorting column corresponding to the sorting operator, the distribution operator is an operator for data distribution, and the summary operator is an operator for data aggregation. In a multi-threaded environment, this method performs the optimization operation of adding a summary operator and a distribution operator to the physical plan tree to be generated based on the first judgment result. By adding the summary operator and the distribution operator, the sorting operator can assist the sorting operator in performing operations related to data order to optimize the physical plan tree to be generated, thereby improving the efficiency of data processing.

[0062] Example 2

[0063] Figure 2 This is a flowchart of an optimization method provided by the second embodiment of the present invention. This embodiment is a further refinement of the above embodiment. In this embodiment, the process of performing the optimization operation of adding the summary operator and the distribution operator to the physical plan tree to be generated according to the first judgment result is described in detail. Figure 2 As shown, the method includes:

[0064] S210 , in the physical plan tree generation phase including the sort operator, if the current environment is a multi-threaded environment, determine whether the next layer of operators after the sort operator is loaded into the target statistical information to obtain a first determination result.

[0065] S220 , determine whether the previous operator of the sort operator is a merge join operator; if not, execute S230 , otherwise execute S250 .

[0066] In this embodiment, if the previous operator of the sorting operator is not a merge join operator, S230 may be executed; if the previous operator of the sorting operator is a merge join operator, S250 may be executed.

[0067] S230 , determining whether the first determination result is loaded into the target statistical information, if so, executing S240 , otherwise executing S290 .

[0068] In this embodiment, if the previous operator of the sort operator is not a merge join operator, it is determined whether the first determination result of the sort operator is loaded into the target statistical information. If so, S240 may be executed; if not, S290 may be executed.

[0069] S240 . Insert a distribution operator between the sorting operator and the operator at the next level below the sorting operator, and insert a summary operator between the sorting operator and the operator at the previous level above the sorting operator.

[0070] In this embodiment, if the previous layer operator of the sorting operator is not a merge join operator, and the first judgment result of the sorting operator is to load into the target statistical information, a distribution operator can be inserted between the sorting operator and the next layer operator of the sorting operator, and a summary operator can be inserted between the sorting operator and the previous layer operator of the sorting operator to perform corresponding optimization.

[0071] S250 , determining whether the first determination result is to load into the target statistical information; if so, executing S260 , otherwise executing S290 .

[0072] In this embodiment, if the previous operator of the sort operator is a merge join operator, it is determined whether the first determination result of the sort operator is to load into the target statistical information; if so, S260 may be executed, otherwise S290 may be executed.

[0073] S260 , determining whether the two child nodes of the merge join operator include a sorting operator and another sorting operator other than the sorting operator; if so, executing S270 ; otherwise, executing S280 .

[0074] In this embodiment, it is determined whether the two child nodes of the merge join operator include a sorting operator and another sorting operator other than the sorting operator. If so, S270 can be executed; if not, it can be indicated that the two child nodes of the merge join operator include a sorting operator and another non-sorting operator other than the sorting operator, and S280 can be executed.

[0075] S270. Insert a distribution operator between the sort operator and the next-level operator of the sort operator, and determine whether the next-level operator of another sort operator is loaded into the target statistical information to obtain a second judgment result. Perform an optimization operation of adding a summary operator to the physical plan tree to be generated based on the second judgment result.

[0076] In this embodiment, the second judgment result can be understood as a judgment result of whether the next layer of operators passing through another sorting operator can be loaded into the corresponding target statistical information.

[0077] If the two child nodes include a sort operator and another sort operator other than the sort operator, a distribute operator can be first inserted between the sort operator and the operator in the next level of the sort operator. Based on this, an optimization operation of adding a summary operator to the physical plan tree to be generated can be performed based on the second judgment result. Specifically, if the second judgment result shows that the corresponding target statistical information can be loaded through the operator in the next level of the other sort operator, a summary operator can be inserted between the merge join operator and the operator in the previous level of the merge join operator. If the second judgment result shows that the corresponding target statistical information cannot be loaded through the operator in the next level of the other sort operator, a summary operator can be inserted between the sort operator and the operator in the previous level of the sort operator.

[0078] Optionally, an optimization operation of adding a summary operator to the physical plan tree to be generated is performed based on the second judgment result, including: if the second judgment result is that the corresponding target statistical information is loaded through the next layer operator of another sorting operator, then a summary operator is inserted between the merge join operator and the previous layer operator of the merge join operator; if the second judgment result is that the next layer operator of another sorting operator is not loaded into the corresponding target statistical information, then a summary operator is inserted between the sorting operator and the previous layer operator of the sorting operator.

[0079] S280: Insert a summary operator between the merge join operator and the operator in the previous layer of the merge join operator, and insert a distribution operator between the sort operator and the operator in the next layer of the sort operator.

[0080] In this embodiment, if the two child nodes of the merge join operator include a sorting operator and another non-sorting operator other than the sorting operator, a summary operator can be inserted between the merge join operator and the operator in the previous layer of the merge join operator, and a distribution operator can be inserted between the sorting operator and the operator in the next layer of the sorting operator to perform corresponding optimization.

[0081] S290, exit optimization.

[0082] This second embodiment provides an optimization method that specifically implements the process of performing an optimization operation of adding a summary operator and a dispatch operator to a physical plan tree to be generated based on a first judgment result. This method optimizes the physical plan tree to be generated by adding the summary operator and the dispatch operator based on the first judgment result, thereby improving data processing efficiency. Furthermore, when the operator above the sort operator is a merge join operator, different optimizations are implemented for the sort operator and the merge join operator by making corresponding judgments on the merge join operator, thereby increasing optimization flexibility.

[0083] The present invention is described below by way of example.

[0084] Multi-threaded data sorting is generally implemented by distributing the data to multiple threads. Each thread then sorts its own data in parallel. Each thread then sends its own data to the main thread, which merges and sorts all the data and outputs the results.

[0085] However, the above method has the problem of excessive pressure on the main thread merge sort and the possibility of too many merge paths, resulting in multiple merge sorts. On the other hand, in the database system, the characteristics of the data (i.e., target statistical information) can be known in advance with the help of statistical information, including the maximum and minimum values, the total number of data, and the total size of the data. In this case, the multi-threaded data sorting method can be improved and optimized based on the data characteristics. The present invention proposes the following improvement method:

[0086] Each thread reads data from disk or memory evenly and randomly, based on the total number of data, without overlapping. Each thread calculates and sets the data range it needs to process. Based on the data processing range, each thread retains data within its own processing range and distributes data outside of the range to a designated thread. After completing data distribution, each thread sorts its own data and sends it back to the main thread in sequence. The main thread then outputs the data in thread order, eliminating the need for merging and sorting the data. Because the main thread's data output and the data sorting of other threads are executed serially, threads that haven't yet completed data sorting can continue sorting while the main thread is outputting data, achieving parallel processing and fully utilizing machine resources. The specific data distribution method is determined by factors such as the machine's central processing unit (CPU) performance, disk or memory size, and input / output (I / O) performance, and is not detailed here.

[0087] For example, suppose there are 100 different numbers from 1 to 100 stored in a database in random order. Four threads are assigned to sort them based on the data size. First, four threads randomly read data from disk or memory, with each thread processing 25 numbers. The calculated data processing ranges for each thread are: 1-10, 11-30, 31-60, and 61-100. The four threads are numbered according to the size of the data processing range: thread 0 1-10, thread 1 11-30, thread 2 31-60, and thread 3 61-100. Each thread distributes the 25 numbers it read according to the above data processing range. After data distribution is completed, the data of its own thread is sorted. Once the sorting is completed, it is sent to the main thread for output. The main thread can output in the order of thread numbers (0, 1, 2, 3).

[0088] It should be noted that the algorithm concept of the present invention can be used not only in a multi-threaded environment, but also in other multi-site cluster environments, that is, it can be used in non-single-threaded environments.

[0089] Furthermore, this algorithm's approach isn't limited to sorting; any operation requiring data order can leverage this algorithm to achieve better performance. For example, in a merge join, where the data on both sides of the join are in order, in a multi-threaded or multi-process cluster, feature data can be distributed to different threads or processes. Each thread then performs its own merge join and outputs the results in order.

[0090] The general database processing process for Structured Query Language (SQL) statements includes lexical analysis, syntax analysis, semantic analysis, generation of an intermediate plan tree, generation of a physical plan tree, and statement execution. The plan tree may include operator nodes of various functional types.

[0091] The optimization method provided by the present invention mainly comprises the following steps:

[0092] 1. When generating the physical plan tree, sort operators are processed in step 2, and other types of operators are not processed;

[0093] 2. Determine whether the current environment is a multi-threaded environment or a multi-site cluster environment. If so, proceed to step 3; otherwise, exit the optimization;

[0094] 3. Determine whether the node below the sort operator (i.e., the next-level operator) can load the valid statistical information of the sort column (i.e., the target statistical information) to obtain its data characteristics (data characteristics can be understood as information such as the maximum and minimum values ​​of the data and the number of data obtained based on the statistical information). If so, proceed to step 4; otherwise, exit the optimization (if the data characteristics cannot be accurately obtained, the performance after optimization is likely to deteriorate, so optimization can be omitted in this case);

[0095] 4. For the sort operator, generate a communication operator (such as the DIS operator) below it to distribute data. Set the optimization flag F1 to TRUE, indicating that data needs to be distributed according to the data processing range. Calculate and set the number of threads for the communication operator and the data processing range corresponding to each thread based on data characteristics and machine performance (such as machine CPU performance, disk / memory size, I / O performance, etc.). Proceed to step 5;

[0096] 5. Determine whether there is a merge join operator above the sort operator (i.e., whether the sorted data needs to be merge joined). If not, generate a communication operator (such as a GAT operator) above the sort operator to aggregate the data. If so, determine whether the other child node of the merge join operator is a sort operator. If not, generate a communication operator (such as a GAT operator) above the merge join operator to aggregate the data. If so, perform the judgment of step 3 on the other sort operator. If the result is that valid statistical information can be loaded, generate a communication operator (such as a GAT operator) above the merge join operator to aggregate the data. Otherwise, generate a communication operator (such as a GAT operator) above the current sort operator to aggregate the data. Set the optimization flag F2 to TRUE, indicating that data needs to be received and output in the order of thread numbers when aggregating data.

[0097] 6. During the statement execution phase, for communication operators used to distribute data (such as the DIS operator), if the optimization flag F1 is TRUE, then according to the data processing scope corresponding to each thread, each thread sends the data within its own data processing scope to the upper-level operator (the upper-level operator sorts the data), and sends the data outside its own data processing scope to the corresponding thread;

[0098] 7. During the statement execution phase, for communication operators used to aggregate data (such as the GAT operator), if the optimization flag F2 is TRUE, the operator first receives data in batches from thread number 0 (after a thread completes data sorting or merge join, it can send the processed data in batches to the main thread). After processing a batch of data, it returns another batch of data to the upper-level operator node until all data sent by thread number 0 is received and processed. The operator then receives data in batches from thread number 1, and so on, until all thread data has been received and processed.

[0099] Figure 3 This is a schematic diagram of an unoptimized plan tree implementation provided by the second embodiment of the present invention. Figure 3 As shown in the unoptimized plan tree, the sort operator (i.e., SORT operator) is connected to the index scan operator (i.e., CSCN operator). The CSCN operator can be considered as the next level operator of the SORT operator; the SORT operator can be considered as the previous level operator of the CSCN operator.

[0100] Figure 4 This is a schematic diagram of an optimized plan tree provided in the second embodiment of the present invention. Figure 4 As shown, the optimization method provided by the present invention is used to optimize Figure 3The plan tree shown in FIG. A GAT operator (i.e., aggregation operator) can be generated between the SORT operator and the operator above the SORT operator, and a DIS operator (i.e., distribution operator) can be generated between the SORT operator and the CSCN operator.

[0101] Figure 5 This is a schematic diagram of another non-optimized plan tree implementation provided by the second embodiment of the present invention. Figure 5 As shown, the left and right child nodes of the next layer of the merge join operator (i.e., MI operator) are respectively connected to the SORT operator, and the next layer of each SORT operator is connected to a CSCN operator, and the corresponding target statistical information can be loaded through the next layer of operators of each SORT operator.

[0102] Figure 6 This is a schematic diagram of another optimized plan tree implementation provided by the second embodiment of the present invention. Figure 6 As shown, the optimization method provided by the present invention is used to optimize Figure 5 The plan tree shown in FIG. A GAT operator (i.e., aggregation operator) is generated between the MI operator and the operator above the MI operator; for each SORT operator, a DIS operator (i.e., distribution operator) is generated between the SORT operator and the CSCN operator.

[0103] Example 3

[0104] Figure 7 This is a schematic diagram of the structure of an optimization device provided in Example 3 of the present invention. Figure 7 As shown, the device includes:

[0105] The judgment module 310 is configured to, during a physical plan tree generation phase including a sort operator, determine whether the next layer of operators passing through the sort operator is loaded into target statistical information if the current environment is a multi-threaded environment, and obtain a first judgment result;

[0106] an optimization module 320, configured to perform an optimization operation of adding a summary operator and a distribution operator to the physical plan tree to be generated according to the first judgment result;

[0107] The target statistical information is the data statistical information of the sorting column corresponding to the sorting operator, the distribution operator is an operator for data distribution, and the summary operator is an operator for data summary.

[0108] The third embodiment provides an optimization device. The judgment module 310 determines whether the next layer of operators after the sorting operator is loaded into the target statistical information during the generation phase of the physical plan tree including the sorting operator if the current environment is a multi-threaded environment, and obtains a first judgment result. The optimization module 320 performs an optimization operation of adding a summary operator and a distribution operator to the physical plan tree to be generated based on the first judgment result. The target statistical information is the data statistical information of the sorting column corresponding to the sorting operator, the distribution operator is an operator for data distribution, and the summary operator is an operator for data aggregation. In a multi-threaded environment, the device performs the optimization operation of adding a summary operator and a distribution operator to the physical plan tree to be generated based on the first judgment result. The device can assist the sorting operator in performing operations related to data order by adding the summary operator and the distribution operator to optimize the physical plan tree to be generated, thereby improving the efficiency of data processing.

[0109] Optionally, the previous operator of the sorting operator is not a merge join operator;

[0110] The optimization module 320 includes:

[0111] A first optimization unit, configured to exit the optimization if the first judgment result is that the target statistical information is not loaded;

[0112] The second optimization unit is configured to insert a distribution operator between the sorting operator and the next layer operator of the sorting operator, and insert a summary operator between the sorting operator and the previous layer operator of the sorting operator if the first judgment result is to load into the target statistical information.

[0113] Optionally, the previous operator of the sorting operator is a merge join operator;

[0114] The optimization module 320 also includes:

[0115] a third optimization unit, configured to exit the optimization if the first judgment result is that the target statistical information is not loaded;

[0116] a fourth optimization unit, configured to insert a distribution operator between the sort operator and a next-layer operator of the sort operator if the first judgment result is loading into target statistical information;

[0117] a fifth optimization unit configured to, if the two child nodes of the merge join operator include the sort operator and another sort operator other than the sort operator, determine whether a next-layer operator passing through the other sort operator is loaded into target statistical information, obtain a second determination result, and perform an optimization operation of adding a summary operator to the physical plan tree to be generated based on the second determination result;

[0118] The fifth optimization unit is configured to insert a summary operator between the merge join operator and an operator on a previous layer of the merge join operator if the two child nodes of the merge join operator include the sorting operator and another non-sorting operator other than the sorting operator.

[0119] Optionally, performing the optimization operation of adding a summary operator to the physical plan tree to be generated according to the second judgment result includes:

[0120] If the second judgment result is that the target statistical information is loaded through the next layer operator of the other sorting operator, then inserting a summary operator between the merge join operator and the previous layer operator of the merge join operator;

[0121] If the second judgment result is that the next layer operator of the other sorting operator is not loaded into the target statistical information, a summary operator is inserted between the sorting operator and the previous layer operator of the sorting operator.

[0122] Optionally, the device further includes:

[0123] A setting module, configured to set a first mark for the distribution operator and a second mark for the aggregation operator during the physical plan tree generation phase;

[0124] a first execution module configured to execute, during a statement execution phase, a dispatch operator containing the first tag in the following manner: retaining, based on a processing scope corresponding to each thread, first data within the corresponding processing scope of each thread, dispatching second data not within the processing scope to a target thread, and sending a first result obtained by executing the dispatch operator to an operator above the dispatch operator, wherein the target thread is a thread corresponding to the processing scope to which the second data belongs;

[0125] The second execution module is used to execute the summary operator containing the second mark in the following manner: receive and output data of each thread in the order of thread numbers from small to large, and send the second result obtained by executing the summary operator to the upper-level operator of the summary operator.

[0126] Optionally, the device further includes:

[0127] The determination module is configured to determine the number of threads and a processing range corresponding to each thread according to the target statistical information and the computing resource information before setting the first mark for the distribution operator, wherein the processing range corresponding to each thread is different.

[0128] Optionally, the device further includes:

[0129] a reading module configured to, after determining the number of threads and the processing scope of each thread based on the target statistical information and the computing resource information, control each thread to read an equal amount of data to be processed from the memory based on the ratio of the total amount of data to be processed to the number of threads, with each thread reading different data to be processed;

[0130] The data to be processed is the data required to be processed by the sorting operator.

[0131] The optimization device provided in the embodiment of the present invention can execute the optimization method provided in any embodiment of the present invention, and has the corresponding functional modules and beneficial effects of the execution method.

[0132] Example 4

[0133] Figure 8 A schematic diagram of the structure of an electronic device provided for embodiment four of the present invention. The electronic device 10 is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device may also represent various forms of mobile devices, such as personal digital processing, cellular phones, smart phones, wearable devices (such as helmets, glasses, watches, etc.) and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the present invention described and / or required herein.

[0134] like Figure 8 As shown, the electronic device 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12, a random access memory (RAM) 13, etc., which is communicatively connected to the at least one processor 11. The memory stores a computer program that can be executed by the at least one processor. The processor 11 can perform various appropriate actions and processes according to the computer program stored in the read-only memory (ROM) 12 or the computer program loaded from the storage unit 18 into the random access memory (RAM) 13. Various programs and data required for the operation of the electronic device 10 can also be stored in the RAM 13. The processor 11, ROM 12, and RAM 13 are connected to each other via a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0135] Multiple components in the electronic device 10 are connected to the I / O interface 15, including an input unit 16, such as a keyboard, a mouse, etc.; an output unit 17, such as various types of displays, speakers, etc.; a storage unit 18, such as a magnetic disk, an optical disk, etc.; and a communication unit 19, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 19 allows the electronic device 10 to exchange information / data with other devices via a computer network such as the Internet and / or various telecommunication networks.

[0136] The processor 11 may be any general-purpose and / or specialized processing component with processing and computing capabilities. Some examples of the processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various specialized artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. The processor 11 executes the various methods and processes described above, such as the optimization method.

[0137] In some embodiments, the optimization method may be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program may be loaded and / or installed on electronic device 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the optimization method described above may be performed. Alternatively, in other embodiments, processor 11 may be configured to perform the optimization method in any other suitable manner (e.g., by means of firmware).

[0138] Various embodiments of the systems and techniques described herein can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), system-on-chip systems (SOCs), programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments can include being implemented in one or more computer programs that are executable and / or interpreted on a programmable system that includes at least one programmable processor, which can be a special purpose or general purpose programmable processor that can receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit data and instructions to the storage system, the at least one input device, and the at least one output device.

[0139] Computer programs for implementing the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when the computer program is executed by the processor, the functions / operations specified in the flowcharts and / or block diagrams are implemented. The computer program may be executed entirely on the machine, partially on the machine, as a stand-alone software package, partially on the machine and partially on a remote machine, or entirely on a remote machine or server.

[0140] In the context of the present invention, computer-readable storage media can be tangible media that can contain or store a computer program for use with an instruction execution system, device or equipment or used in combination with an instruction execution system, device or equipment. Computer-readable storage media can include but are not limited to electronic, magnetic, optical, electromagnetic, infrared or semiconductor systems, devices or equipment, or any suitable combination of the foregoing. Alternatively, computer-readable storage media can be machine-readable signal media. More specific examples of machine-readable storage media can include electrical connections based on one or more lines, portable computer disks, hard disks, random access memories (RAM), read-only memories (ROM), erasable programmable read-only memories (EPROM or flash memory), optical fibers, portable compact disk read-only memories (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination of the foregoing.

[0141] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user can provide input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, the feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including acoustic input, voice input, or tactile input).

[0142] The systems and techniques described herein can be implemented in a computing system that includes back-end components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes front-end components (e.g., a user computer with a graphical user interface or web browser through which a user can interact with implementations of the systems and techniques described herein), or a computing system that includes any combination of such back-end components, middleware components, or front-end components. The components of the system can be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.

[0143] A computing system may include clients and servers. The clients and servers are typically remote from each other and typically interact via a communication network. This client-server relationship arises through computer programs running on the respective computers, creating a client-server relationship. The server may be a cloud server, also known as a cloud computing server or cloud host. This server is a hosting product within the cloud computing service ecosystem that addresses the management difficulties and limited scalability of traditional physical hosting and VPS services.

[0144] It should be understood that the various forms of the processes shown above can be used to reorder, add, or delete steps. For example, the steps described in the present invention can be performed in parallel, sequentially, or in a different order, as long as the desired results of the technical solution of the present invention can be achieved. This is not limited herein.

[0145] The above specific embodiments do not limit the scope of protection of the present invention. Those skilled in the art will appreciate that various modifications, combinations, sub-combinations, and substitutions may be made based on design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention are intended to be included within the scope of protection of the present invention.

Claims

1. An optimization method, characterized in that: The method comprises: In a physical plan tree generation phase including a sort operator, if the current environment is a multi-threaded environment, determining whether a next layer of operators passing through the sort operator is loaded into target statistical information to obtain a first determination result; Performing an optimization operation of adding a summary operator and a distribution operator to the physical plan tree to be generated according to the first judgment result; The target statistical information is the statistical information of the sorting column corresponding to the sorting operator, the distribution operator is an operator for data distribution, and the summary operator is an operator for data aggregation; In the physical plan tree generation phase, a first tag is set for the distribution operator, and a second tag is set for the summary operator; wherein the first tag and the second tag are used in a subsequent statement execution phase to execute the distribution operator and the summary operator in a corresponding optimization manner according to the tags on the distribution operator and the summary operator; During the statement execution phase, the dispatch operator including the first tag is executed in the following manner: according to the processing scope corresponding to each thread, first data within each thread that belongs to the corresponding processing scope is retained, second data that does not belong to the processing scope is dispatched to a target thread, and a first result obtained by executing the dispatch operator is sent to an operator above the dispatch operator, wherein the target thread is the thread corresponding to the processing scope to which the second data belongs; The summary operator including the second tag is executed as follows: data of each thread is received and output in ascending order of thread numbers, and the second result obtained by executing the summary operator is sent to the upper-level operator of the summary operator.

2. The method according to claim 1, characterized in that The previous operator of the sort operator is not a merge join operator; The step of performing the optimization operation of adding a summary operator and a distribution operator to the physical plan tree to be generated according to the first judgment result includes: If the first judgment result is that the target statistical information is not loaded, then exit the optimization; If the first judgment result is to load into the target statistical information, a distribution operator is inserted between the sorting operator and the next layer operator of the sorting operator, and a summary operator is inserted between the sorting operator and the previous layer operator of the sorting operator.

3. The method according to claim 1, characterized in that The previous operator of the sort operator is a merge join operator; The step of performing the optimization operation of adding a summary operator and a distribution operator to the physical plan tree to be generated according to the first judgment result includes: If the first judgment result is that the target statistical information is not loaded, then exit the optimization; If the first judgment result is to load into the target statistical information, inserting a distribution operator between the sorting operator and the next layer operator of the sorting operator; If the two child nodes of the merge join operator include the sort operator and another sort operator other than the sort operator, determining whether the next layer of operators passing through the other sort operator is loaded into the target statistical information, obtaining a second determination result, and performing an optimization operation of adding a summary operator to the physical plan tree to be generated based on the second determination result; If the two child nodes of the merge join operator include the sorting operator and another non-sorting operator other than the sorting operator, a summary operator is inserted between the merge join operator and the operator of the previous layer of the merge join operator.

4. The method according to claim 3, characterized in that The performing of the optimization operation of adding a summary operator to the physical plan tree to be generated according to the second judgment result includes: If the second judgment result is that the target statistical information is loaded through the next layer operator of the other sorting operator, then inserting a summary operator between the merge join operator and the previous layer operator of the merge join operator; If the second judgment result is that the next layer operator of the other sorting operator is not loaded into the target statistical information, a summary operator is inserted between the sorting operator and the previous layer operator of the sorting operator.

5. The method according to claim 1, wherein Before setting the first flag for the dispatch operator, also include: The number of threads and the processing range corresponding to each thread are determined according to the target statistical information and the computing resource information, wherein the processing range corresponding to each thread is different.

6. The method according to claim 5, characterized in that After determining the number of threads and the processing scope of each thread according to the target statistical information and the computing resource information, the method further includes: According to the ratio of the total amount of data to be processed to the number of threads, each thread is controlled to read the same amount of data to be processed from the memory, and the data to be processed read by each thread is different; The data to be processed is the data required to be processed by the sorting operator.

7. An optimization device, characterized in that: include: A judgment module is used to judge whether the next layer of operators passing through the sort operator is loaded into the target statistical information in the physical plan tree generation stage including the sort operator if the current environment is a multi-threaded environment, and obtain a first judgment result; an optimization module, configured to perform an optimization operation of adding a summary operator and a distribution operator to the physical plan tree to be generated according to the first judgment result; The target statistical information is the statistical information of the sorting column corresponding to the sorting operator, the distribution operator is an operator for data distribution, and the summary operator is an operator for data aggregation; A setting module, configured to set a first mark for the distribution operator and a second mark for the aggregation operator during the physical plan tree generation phase; a first execution module configured to execute, during a statement execution phase, a dispatch operator containing the first tag in the following manner: retaining, based on a processing scope corresponding to each thread, first data within the corresponding processing scope of each thread, dispatching second data not within the processing scope to a target thread, and sending a first result obtained by executing the dispatch operator to an operator above the dispatch operator, wherein the target thread is a thread corresponding to the processing scope to which the second data belongs; The second execution module is used to execute the summary operator containing the second mark in the following manner: receive and output data of each thread in the order of thread numbers from small to large, and send the second result obtained by executing the summary operator to the upper-level operator of the summary operator.

8. An electronic device, characterized in that: The electronic device comprises: at least one processor; and a memory communicatively connected to the at least one processor; wherein, The memory stores a computer program executable by the at least one processor. The computer program is executed by the at least one processor to enable the at least one processor to perform the optimization method according to any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that The computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement the optimization method according to any one of claims 1 to 6 when executed.

Images