Data processing optimization method and device for database, electronic equipment and storage medium

Abstract

The invention provides a data processing optimization method and device for a database, electronic equipment and a storage medium, and can be applied to the technical field of databases and big data. The method comprises the following steps: determining an original data set for grouping and aggregating each storage node, and screening out a target data subset from the original data set by using a hybrid sampling method; carrying out independent fragmentation on the target data subset by utilizing a Hash fragmentation algorithm, taking independent fragmentation results mapped to different processing nodes as independent data blocks, and carrying out initial data fragmentation on other data subsets in the original data set; calculating the average data volume of the initial data slicing results, re-fragmenting the initial data slicing results meeting a preset re-fragmenting condition on the basis of the average data volume, and taking the re-fragmenting result as a common data block; and performing mixed sorting on the independent data blocks and the common data blocks, and performing concurrent execution on a mixed sorting result through a multi-thread data function of the distributed database.

Description

Technical Field

[0001] The present invention relates to the field of database and big data technology, and in particular to a data processing optimization method and device for a database, an electronic device and a storage medium. Background Art

[0002] Distributed databases have obvious advantages in processing large-scale data and high concurrent access. By effectively managing and storing data, they support enterprises' big data analysis, real-time applications, and cloud-native architecture. ‌Distributed databases store data on multiple physical nodes and implement data management through distributed computing and storage technologies. In the process of data processing in distributed databases, it is often necessary to group and aggregate data (such as group by operations), that is, to divide the result set into multiple groups according to the values ​​of one or more columns, and then use aggregate functions independently in each group, so that users can group data according to specific rules and perform aggregation operations on each group to obtain aggregated data results. However, the existing data grouping and aggregation processing technology solutions have technical problems such as time-consuming calculations and long query times, which greatly affect the user experience. Therefore, it is necessary to propose new technical solutions to optimize the existing data grouping and aggregation processing technology solutions. Summary of the invention

[0003] In view of the above problems, the present invention provides a method and device for optimizing data processing in a database, an electronic device, and a storage medium.

[0004] According to a first aspect of the present invention, there is provided a method for optimizing data processing for a database, the method comprising:

[0005] Determine the original data set that needs to be grouped and aggregated for each storage node in the distributed database, and use a hybrid sampling method to filter out the target data subset from the original data set;

[0006] Use the hash sharding algorithm to independently shard the target data subset, map the independent sharding results to different processing nodes as independent data blocks, and perform initial data slicing on other data subsets in the original data set;

[0007] Calculate the average data volume of the initial data slicing results, re-slice the initial data slicing results that meet the preset re-slicing conditions based on the average data volume, and use the re-slicing results as ordinary data blocks;

[0008] Independent data blocks and common data blocks are mixed and sorted, and the results of the mixed sorting are scheduled through the multi-threaded data function of the distributed database so as to concurrently perform grouping and aggregation operations on the original data set.

[0009] According to an embodiment of the present invention, the above-mentioned method of filtering out a target data subset from an original data set by using a hybrid sampling method includes:

[0010] Perform fixed sampling on the original data set in each storage node, filter out data whose repetition rate meets the preset sampling condition from the original data set, and add the filtered data to the data in the target data subset; and / or

[0011] The original data set in each storage node is sampled proportionally, the data whose repetition rate meets the preset sampling conditions is filtered out from the original data set, and the filtered data is added to the target data subset.

[0012] According to an embodiment of the present invention, the above-mentioned fixed sampling of the original data set in each storage node and screening out data whose repetition rate meets the preset sampling condition from the original data set include:

[0013] Count the repetition rate of each data in each original data set, and sort them from large to small according to the repetition rate to obtain a first sorting result;

[0014] Based on the first sorting result, N data with the highest repetition rate are selected as data meeting the preset sampling condition, where N is a positive integer set based on user requirements;

[0015] Obtain the total amount of data for each original data set, and set the number of sampling points and the amount of data collected at each sampling point according to the total amount of data;

[0016] The total sampling amount is calculated according to the number of sampling points and the collection amount of each sampling point, and the data that meets the preset sampling conditions in each original data set are evenly sampled according to the total sampling amount;

[0017] When the data satisfying the preset sampling condition in the original data set is less than the total sampling amount, all the data satisfying the preset sampling condition in the original data set are sampled.

[0018] According to an embodiment of the present invention, the above-mentioned sampling of the original data set in each storage node in proportion, and screening out data whose repetition rate meets the preset sampling condition from the original data set includes:

[0019] Counting the repetition rate of each data in each original data set, and sorting them from large to small according to the repetition rate, to obtain a second sorting result;

[0020] Based on the second sorting result, N data with the highest repetition rate are selected as data meeting the preset sampling condition, where N is a positive integer set based on user requirements;

[0021] Obtain the total amount of data for each original data set, and set the number of sampling points, the total number of sampling rows, the sampling interval, and the number of point samplings according to the total amount of data for each original data set;

[0022] According to the number of sampling points, the total number of sampling rows, the sampling interval, and the number of point sampling lines, the data in each original data set that meets the preset sampling conditions are sampled proportionally.

[0023] According to an embodiment of the present invention, the above-mentioned calculation of the average data volume of the initial data slicing results and re-slicing the initial data slicing results that meet the preset re-slicing conditions based on the average data volume include:

[0024] Based on the number of initial data slicing results and the total amount of data included in the initial data slicing results, the average data amount is calculated;

[0025] Calculate the data volume of each initial data slicing result and the average data volume, and determine the initial data slicing result that meets the preset re-slicing condition based on the calculation result and the predefined threshold;

[0026] The initial data slicing results that meet the preset re-slicing conditions are re-sliced ​​using the secondary partitioning method.

[0027] According to an embodiment of the present invention, the above-mentioned re-slicing of the initial data slicing results that meet the preset re-slicing conditions by using the secondary partitioning method includes:

[0028] All initial data slicing results that meet the preset re-slicing conditions are sorted from large to small according to the amount of data contained, to obtain a third sorting result;

[0029] Determine the number of re-shards based on the total amount of data of the initial data slicing results that meet the preset re-slicing conditions, the amount of data of each initial data slicing result that meets the preset re-slicing conditions, and the third sorting result;

[0030] Construct a hash function and initialize the parameters, and use the hash function and the number of sub-shards to calculate the hash value of the sub-shards;

[0031] According to the hash value of the re-sharding, the initial data slicing results that meet the preset re-sharding conditions are re-sharded.

[0032] According to an embodiment of the present invention, the above-mentioned determination of the number of re-sharding based on the total amount of data of the initial data slicing results that meet the preset re-slicing conditions, the amount of data of each initial data slicing result that meets the preset re-slicing conditions, and the third sorting result includes:

[0033] Calculate the proportion of the data volume of each initial data slicing result that meets the preset re-slicing condition in the total data volume of the initial data slicing result that meets the preset re-slicing condition;

[0034] Based on the third sorting result, the number of re-sharding is determined using the threshold and proportion set by the user.

[0035] According to a second aspect of the present invention, there is provided a data processing optimization device for a database, the device comprising:

[0036] The data screening module is used to determine the original data set that needs to be grouped and aggregated for each storage node in the distributed database, and to screen out the target data subset from the original data set using a hybrid sampling method;

[0037] The initial sharding module is used to independently shard the target data subset using the hash sharding algorithm, map the independent sharding results to different processing nodes as independent data blocks, and perform initial data slicing on other data subsets in the original data set;

[0038] A re-sharding module is used to calculate the average data volume of the initial data slicing results, re-shard the initial data slicing results that meet the preset re-sharding conditions based on the average data volume, and use the re-sharding results as ordinary data blocks;

[0039] The optimization processing module is used to sort the independent data blocks and the common data blocks in a mixed manner, and to schedule the mixed sorting results through the multi-threaded data function of the distributed database so as to concurrently perform the grouping and aggregation operations on the original data set.

[0040] A third aspect of the present invention provides an electronic device, comprising: one or more processors; a memory for storing one or more computer programs, wherein the one or more processors execute the one or more computer programs to implement the steps of the above method.

[0041] The fourth aspect of the present invention further provides a computer-readable storage medium on which a computer program or instruction is stored, and the steps of the above method are implemented when the above computer program or instruction is executed by a processor.

[0042] The above-mentioned data processing optimization method for databases provided by the present invention independently slices the target data subset so that data with a higher repetition rate can be processed preferentially; at the same time, the large slice obtained by the initial slicing is sliced ​​again so that each obtained data block has a reasonable amount of data, thereby avoiding spending a lot of time on grouping and aggregation operations on a certain large slice. The above-mentioned method provided by the present invention can effectively reduce the "barrel effect" in the process of grouping and aggregation operations of the database, greatly improve the performance of grouping and aggregation operations, and effectively reduce the query time cost of users. BRIEF DESCRIPTION OF THE DRAWINGS

[0043] The above contents and other objects, features and advantages of the present invention will become more apparent through the following description of the embodiments of the present invention with reference to the accompanying drawings, in which:

[0044] Figure 1 is a schematic diagram of optimization of grouping and aggregation operations according to an embodiment of the present invention;

[0045] Figure 2 is a flow chart of a data processing optimization method for a database according to an embodiment of the present invention;

[0046] Figure 3 is a schematic diagram of independent slicing and initial slicing according to an embodiment of the present invention;

[0047] Figure 4 is a schematic diagram of secondary slicing according to an embodiment of the present invention;

[0048] Figure 5 is a schematic diagram of a data processing task queue according to an embodiment of the present invention;

[0049] Figure 6 is a structural schematic diagram of a data processing optimization device for a database according to an embodiment of the present invention;

[0050] Figure 7 is a block diagram of an electronic device suitable for implementing a data processing optimization method for a database according to an embodiment of the present invention. DETAILED DESCRIPTION

[0051] Below, embodiments of the present invention will be described with reference to the accompanying drawings. However, it should be understood that these descriptions are exemplary only and are not intended to limit the scope of the present invention. In the following detailed description, for ease of explanation, many specific details are set forth to provide a comprehensive understanding of embodiments of the present invention. However, it is apparent that one or more embodiments may also be implemented without these specific details. In addition, in the following description, descriptions of known structures and technologies are omitted to avoid unnecessary confusion of concepts of the present invention.

[0052] The terms used herein are only for describing specific embodiments and are not intended to limit the present invention. The terms "comprise", "include", etc. used herein indicate the existence of the features, steps, operations and / or components, but do not exclude the existence or addition of one or more other features, steps, operations or components.

[0053] All terms (including technical and scientific terms) used herein have the meanings commonly understood by those skilled in the art unless otherwise defined. It should be noted that the terms used herein should be interpreted as having a meaning consistent with the context of this specification and should not be interpreted in an idealized or overly rigid manner.

[0054] When using expressions such as "at least one of A, B, and C, etc.", they should generally be interpreted according to the meaning of the expression commonly understood by those skilled in the art (for example, "a system having at least one of A, B, and C" should include but is not limited to a system having A alone, B alone, C alone, A and B, A and C, B and C, and / or A, B, C, etc.).

[0055] In the technical solution of the present invention, the user information (including but not limited to user personal information, user image information, user device information, such as location information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved are all information and data authorized by the user or fully authorized by all parties, and the collection, storage, use, processing, transmission, provision, disclosure and application of the relevant data comply with relevant laws, regulations and standards, take necessary confidentiality measures, do not violate public order and good morals, and provide corresponding operation entrances for users to choose to authorize or refuse.

[0056] The existing data grouping and aggregation processing technology solutions (taking group by operation as an example) are divided into two types: RR partitioning and Hash partitioning. In some scenarios, only Hash partitioning can be used. Among them, Hash partitioning is divided into One Pass Hash and Multi Pass Hash. One Pass Hash divides the data blocks into smaller ones as much as possible so that they can all fit in the memory. MultiPass Hash divides the data into corresponding blocks according to the number of parallelism, and each block will be larger.

[0057] The following uses the group by operation as an example to illustrate the technical problems faced by existing data grouping and aggregation operations.

[0058] Table 1: SQL query statement example 1

[0059]

[0060] In a certain use case, there are two columns (a, b) on Table 1. The following SQL query statement is used: select a, count(distinct(b)) from t1 group by a. Using hash partitioning, the query result is: (1, 4). Using RR partitioning, the results of the top three items are (1, 3) and the results of the bottom three items are (1, 2). If secondary aggregation and summary are required, the result becomes (1, 5). Therefore, in this case, only hash partitioning can be used and RR cannot be used for separate calculation.

[0061] However, the problem with using hash partitioning is that the data will be skewed. If the skew is severe, it will produce the barrel effect, and the overall query time depends on the processing time of the block with the most data. In addition, although one-pass partitioning is used, the divided blocks are pre-allocated equally to each thread. After each thread completes the block it has processed, it will no longer process new data. If a thread processes a lot of data, all threads will eventually wait for this thread, and the barrel effect will still exist.

[0062] In order to solve the technical problems existing in the existing technical solutions, the present invention provides a data processing optimization method, system, electronic device and storage medium for a database, which are used to solve the problems of uneven Hash partitioning and poor One Pass Hash partition scheduling optimization in the existing data grouping and aggregation processing technical solutions, which in turn lead to technical problems such as long grouping and aggregation time and long query time.

[0063] Figure 1 It is a schematic diagram of optimization of grouping and aggregation operations according to an embodiment of the present invention.

[0064] like Figure 1 As shown, the present invention proposes a method of sampling first and then dividing, which extracts the top 10 and top 20 values ​​with the highest repetition rate through sampling. For these data, these values ​​will be divided separately when doing hash division to become separate data blocks. As for the change of scheduling mode, it is chosen here to schedule through task queues, so that each thread can get tasks from the queue, so that each thread is in a working state until all tasks are processed, thereby avoiding the barrel effect of fast threads waiting for slow threads, and tasks with the largest amount of data can be placed at the front of the queue for priority processing, further optimizing time.

[0065] Figure 2 is a flow chart of a method for optimizing data processing in a database according to an embodiment of the present invention.

[0066] like Figure 2As shown, the above-mentioned data processing optimization method for a database includes operations S210 to S240.

[0067] Operation S210 is to determine the original data set that needs to be grouped and aggregated for each storage node in the distributed database, and to filter out a target data subset from the original data set using a hybrid sampling method.

[0068] There are multiple original data sets distributed in different storage nodes. Operation S210 is used to filter out a target data subset that meets the conditions in each original data set.

[0069] Operation S220, using a hash sharding algorithm to independently shard the target data subset, using the independent sharding results mapped to different processing nodes as independent data blocks, and performing initial data slicing on other data subsets in the original data set.

[0070] Operation S230, calculating an average data volume of the initial data slicing results, re-slicing the initial data slicing results that meet the preset re-slicing conditions based on the average data volume, and using the re-slicing results as common data blocks.

[0071] For other data subsets of non-independent slices, there is a problem that the data volume of some initial slice results is too large, which leads to a lot of time wasted on these initial data slice results during the grouping and aggregation operations, forming a "barrel effect" in the data processing process, which seriously affects the processing of other slices. Therefore, it is necessary to re-shard similar initial data slice results.

[0072] Operation S240 , the independent data blocks and the common data blocks are mixed and sorted, and the mixed sorting result is scheduled through the multi-threaded data function of the distributed database so as to concurrently perform grouping and aggregation operations on the original data set.

[0073] The above-mentioned data processing optimization method for databases provided by the present invention independently slices the target data subset so that data with a higher repetition rate can be processed preferentially; at the same time, the large slice obtained by the initial slicing is sliced ​​again so that each obtained data block has a reasonable amount of data, thereby avoiding spending a lot of time on grouping and aggregation operations on a certain large slice. The above-mentioned method provided by the present invention can effectively reduce the "barrel effect" in the process of grouping and aggregation operations of the database, greatly improve the performance of grouping and aggregation operations, and effectively reduce the query time cost of users.

[0074] According to an embodiment of the present invention, the above-mentioned use of the hybrid sampling method to filter out the target data subset from the original data set includes: fixed sampling of the original data set in each storage node, filtering out data whose repetition rate meets the preset sampling conditions from the original data set, and adding the filtered data to the data in the target data subset; and / or proportional sampling of the original data set in each storage node, filtering out data whose repetition rate meets the preset sampling conditions from the original data set, and adding the filtered data to the target data subset.

[0075] The present invention adopts the TopN sampling method. Considering that fixed data sampling may not reflect the actual situation, a hybrid sampling method is needed, namely: fixed data number sampling and proportional sampling, which is used to filter out the TopN data with the highest repetition rate from the original data set, such as Top10 or Top20 data.

[0076] According to an embodiment of the present invention, the above-mentioned fixed sampling of the original data set in each storage node and screening out data whose repetition rate meets the preset sampling condition from the original data set include: counting the repetition rate of each data in each original data set, and sorting them from large to small according to the repetition rate to obtain a first sorting result; based on the first sorting result, screening N data with the highest repetition rate as data that meet the preset sampling condition, wherein N is a positive integer set based on user needs; obtaining the total amount of data of each original data set, and setting the number of sampling points and the collection amount of each sampling point according to the total amount of data; calculating the total sampling amount according to the number of sampling points and the collection amount of each sampling point, and uniformly sampling the data that meet the preset sampling condition in each original data set according to the total sampling amount; when the data that meet the preset sampling condition in the original data set is less than the total sampling amount, all the data that meet the preset sampling condition in the original data set are sampled.

[0077] The fixed sampling process provided by the present invention is further described in detail below through specific embodiments.

[0078] The present invention uses the following algorithm for sampling: 100 sampling points are selected, each sampling point collects 2048 data, a total of about 200,000 data, and less than 200,000 data are considered as all samples. 100 sampling points are evenly distributed in the total number of rows of data, and the calculation method is the total number of rows / 100. For example, if there are 1 million rows of data, every ten thousand is a sampling point. In order to avoid too long sampling time, the sampling time is set to a maximum of 300 seconds. After the timeout, the sampling will exit even if the sampling number is not completed.

[0079] According to an embodiment of the present invention, the above-mentioned proportional sampling of the original data set in each storage node and screening out data whose repetition rate meets the preset sampling condition from the original data set include: counting the repetition rate of each data in each original data set, and sorting the data from large to small according to the repetition rate to obtain a second sorting result; based on the second sorting result, screening N data with the highest repetition rate as data that meet the preset sampling condition, wherein N is a positive integer set based on user needs; obtaining the total amount of data of each original data set, and setting the number of sampling points, the total number of sampling rows, the sampling interval and the number of point samplings according to the total amount of data of each original data set; and proportionally sampling the data that meet the preset sampling condition in each original data set according to the number of sampling points, the total number of sampling rows, the sampling interval and the number of point samplings.

[0080] The above-mentioned proportional sampling process provided by the present invention is further described in detail below through specific embodiments.

[0081] 100 points are collected for 2 million data, and 200 points are collected for 4 million data. In this way, sampling points are added to the above algorithm.

[0082] Number of sampling points = (total number of data items / 2 million + 1) 100;

[0083] Total number of sampled rows = total number of data items Percentage, for example, if 10% of 1 million data is sampled, then there are 100,000 records;

[0084] Sampling interval = total number of rows / number of sampling points. For example, if there are 2 million data and 100 sampling points, the interval is 20,000.

[0085] The number of sampling points = the total number of sampling rows / the number of sampling points. For example, if there are 2 million data and 100 sampling points, the sampling rate is 10%, that is, 2 million / 100=2 million, which means 10% of the data is sampled from the 2 million data.

[0086] Figure 3 Schematic diagram of independent slicing and initial slicing according to an embodiment of the present invention.

[0087] like Figure 3 As shown, after the above mixed sampling, Hash independent slicing and common slicing (i.e. slicing of non-target data subsets) are performed. Finally, the data will be divided into independent shards and common shards. There will be as many independent shards as there are topN.

[0088] According to an embodiment of the present invention, the above-mentioned calculation of the average data amount of the initial data slicing results and re-slicing the initial data slicing results that meet the preset re-slicing conditions based on the average data amount include: calculating the average data amount based on the number of initial data slicing results and the total amount of data contained in the initial data slicing results; calculating the data amount of each initial data slicing result and the average data amount, and determining the initial data slicing results that meet the preset re-slicing conditions based on the calculation result and a predefined threshold; and re-slicing the initial data slicing results that meet the preset re-slicing conditions using a secondary partitioning method.

[0089] According to an embodiment of the present invention, the above-mentioned use of the secondary partitioning method to re-slice the initial data slicing results that meet the preset re-slicing conditions includes: sorting all the initial data slicing results that meet the preset re-slicing conditions from large to small according to the amount of data contained, to obtain a third sorting result; determining the number of re-slices based on the total amount of data of the initial data slicing results that meet the preset re-slicing conditions, the amount of data of each initial data slicing result that meets the preset re-slicing conditions, and the third sorting result; constructing a hash function and initializing the parameters, and calculating the hash value of the re-slicing using the hash function and the number of re-slicing; and re-slicing the initial data slicing results that meet the preset re-slicing conditions according to the hash value of the re-slicing.

[0090] According to an embodiment of the present invention, the above-mentioned determination of the number of re-sharding based on the total data amount of the initial data slicing results that meet the preset re-sharding conditions, the data amount of each initial data slicing result that meets the preset re-sharding conditions, and the third sorting result includes: calculating the proportion of the data amount of each initial data slicing result that meets the preset re-sharding conditions in the total data amount of the initial data slicing results that meet the preset re-sharding conditions; based on the third sorting result, determining the number of re-sharding using the threshold and proportion set by the user.

[0091] The above-mentioned secondary slicing process is further described in detail below through specific embodiments and in conjunction with the accompanying drawings.

[0092] Figure 4 is a schematic diagram of secondary slicing according to an embodiment of the present invention.

[0093] For the shards after the first division, another hash division is performed. Here, the second division uses a different hash algorithm to achieve a more uniform division, such as Figure 4 shown.

[0094] Secondary partitioning can be controlled by parameters: first, exclude the largest topN shards and calculate the average data volume of other shards; second, if the data volume of a large shard exceeds the preset threshold (for example, 5 times) of the average value, it will be divided twice, and each large shard will be divided into small shards according to a specific multiple, where the specific multiple = the data volume in the large shard / average value.

[0095] like Figure 4 As shown in the figure, the two shards with the largest amount of data are separated, and the rest are divided by hash, but there is still data skew, which is caused by Figure 4 It can be seen that the amount of data in p6 is much larger than that in p3. In this case, secondary division should be performed.

[0096] The secondary partitioning algorithm includes: recording and sorting data, automatically selecting the N value of the largest Top N shards, secondary partitioning preparation, excluding the largest shard according to the N value and performing secondary hash partitioning, as well as fine-tuning and verification.

[0097] Among them, record and sort data: record each column value and its corresponding data volume and sort them from large to small according to the data volume.

[0098] The automatic selection of the N value of the largest Top N shards includes calculating the total data volume, calculating the data volume ratio of each shard, setting the threshold, judging the ratio, and determining the N value.

[0099] Calculate the total data volume: Calculate the total data volume T of all data;

[0100] Calculate the proportion of data volume of each shard: Calculate the proportion of data volume of each shard to the total data volume;

[0101] Set a threshold: Set a threshold, such as 10%. This threshold is used to determine whether the amount of data in a single shard is too large;

[0102] Determine the proportion: Start from the shard with the largest data volume and determine its proportion. If the proportion exceeds the threshold, it is considered a large shard.

[0103] Determine the N value: The number of shards that exceed the threshold after all judgments are completed is the N value.

[0104] Among them, the secondary hash algorithm prepared for secondary partitioning is as follows:

[0105] h(x) = (h1(x) + h2(x)) % M, where M is the total number of shards

[0106] h1(x) = (A x) % P

[0107] h2(x) = (B x) %Q.

[0108] First, select the initial hash function parameters P and Q;

[0109] Determine the selection range of P: P is a prime number larger than the column value range. For example, if the maximum value of the column value is 200, set its upper limit to 400.

[0110] The process of generating P is as follows:

[0111] Create a Boolean array is_prime with a length equal to the upper limit plus 1 and all initial values ​​are True. The array subscripts correspond to numbers, and the values ​​indicate whether they are prime numbers; set 0 and 1 to False; start from 2 and check each number one by one. If the number i is a prime number, mark all multiples of i as False; repeat this process until the square root of the upper limit is checked; extract all numbers with True values ​​from the Boolean array; select the smallest number that is larger than the maximum column value from the numbers that meet the conditions, which is P.

[0112] Determine the selection range of Q: Q is a prime number greater than the number of initial data slices. For example, if the number of initial data slices is 50, then set its upper limit to 100. The number of initial data slices is determined by the first division.

[0113] The process of generating Q is as follows: generate a series of numbers through an algorithm similar to the "process of generating P", and select the smallest number larger than the initial data slice number, which is Q.

[0114] Next, generate and select random integers A and B: Randomly generate two integers A and B and make sure they are different.

[0115] Among them, the largest shard is excluded according to the N value and secondary hash division is performed: for each shard that needs secondary division, the hash value is calculated using the hash function h(x) = (h1(x) + h2(x)) % M, and it is divided into the corresponding shard according to the hash value.

[0116] Among them, fine-tuning and verification: verify the data distribution after sharding to ensure that the data volume of each shard is within a reasonable range. If the data volume of a certain shard is still too large, repeat the process of "excluding the largest shard according to the N value and performing secondary hash division". If it exceeds the maximum number of shards, it is allocated to the shards based on the principle of hash value approximation.

[0117] Figure 5 is a schematic diagram of a data processing task queue according to an embodiment of the present invention.

[0118] After independent sharding and secondary division, each shard has a reasonable amount of data. The common shards and independent shards are mixed and sorted to form a task queue. The processing thread takes tasks from the task queue in turn for processing. Figure 5shown.

[0119] Figure 6 It is a structural diagram of a data processing optimization device for a database according to an embodiment of the present invention.

[0120] like Figure 6 As shown, the above-mentioned data processing optimization device 600 for database includes a data screening module 610, an initial sharding module 620, a secondary sharding module 630 and an optimization processing module 640.

[0121] The data screening module 610 is used to determine the original data set that needs to be grouped and aggregated for each storage node in the distributed database, and to screen out the target data subset from the original data set using a hybrid sampling method. In one embodiment, the data screening module 610 can be used to perform the operation S210 described above, which will not be repeated here.

[0122] The initial sharding module 620 is used to independently shard the target data subset using a hash sharding algorithm, use the independent sharding results mapped to different processing nodes as independent data blocks, and perform initial data slicing on other data subsets in the original data set. In one embodiment, the initial sharding module 620 can be used to perform the operation S220 described above, which will not be repeated here.

[0123] The re-slicing module 630 is used to calculate the average data volume of the initial data slicing results, re-slice the initial data slicing results that meet the preset re-slicing conditions based on the average data volume, and use the re-slicing results as ordinary data blocks. In one embodiment, the re-slicing module 630 can be used to perform the operation S230 described above, which will not be repeated here.

[0124] The optimization processing module 640 is used to perform mixed sorting of independent data blocks and common data blocks, and schedule the mixed sorting results through the multi-threaded data function of the distributed database so as to concurrently perform grouping and aggregation operations on the original data set. In one embodiment, the optimization processing module 640 can be used to perform the operation S240 described above, which will not be repeated here.

[0125] According to an embodiment of the present invention, any multiple modules among the data screening module 610, the initial slicing module 620, the re-slicing module 630 and the optimization processing module 640 can be combined into one module for implementation, or any one of the modules can be split into multiple modules. Alternatively, at least part of the functions of one or more of these modules can be combined with at least part of the functions of other modules and implemented in one module. According to an embodiment of the present invention, at least one of the data screening module 610, the initial slicing module 620, the re-slicing module 630 and the optimization processing module 640 can be at least partially implemented as a hardware circuit, such as a field programmable gate array (FPGA), a programmable logic array (PLA), a system on a chip, a system on a substrate, a system on a package, an application specific integrated circuit (ASIC), or can be implemented by hardware or firmware such as any other reasonable way of integrating or packaging the circuit, or implemented in any one of the three implementation methods of software, hardware and firmware or in a suitable combination of any of them. Alternatively, at least one of the data screening module 610, the initial sharding module 620, the secondary sharding module 630, and the optimization processing module 640 may be at least partially implemented as a computer program module, which may perform corresponding functions when executed.

[0126] According to an embodiment of the present invention, the data screening module 610 includes a fixed sampling submodule and / or a proportional sampling submodule.

[0127] The fixed sampling submodule is used to perform fixed sampling on the original data set in each storage node, filter out data whose repetition rate meets the preset sampling conditions from the original data set, and add the filtered data to the data in the target data subset.

[0128] The proportional sampling submodule is used to perform proportional sampling on the original data set in each storage node, filter out data whose repetition rate meets the preset sampling conditions from the original data set, and add the filtered data to the target data subset.

[0129] According to an embodiment of the present invention, the fixed sampling submodule includes a first statistical sorting unit, a data screening unit, a sampling setting unit, a uniform sampling unit and a special sampling unit.

[0130] The first statistical sorting unit is used to count the repetition rate of each data in each original data set, and sort the data from large to small according to the repetition rate to obtain a first sorting result.

[0131] The data screening unit is used to screen N data with the highest repetition rate as data meeting the preset sampling condition based on the first sorting result, wherein N is a positive integer set based on user requirements.

[0132] The sampling setting unit is used to obtain the total amount of data of each original data set, and set the number of sampling points and the collection amount of each sampling point according to the total amount of data.

[0133] The uniform sampling unit is used to calculate the total sampling amount according to the number of sampling points and the collection amount of each sampling point, and to uniformly sample the data that meets the preset sampling conditions in each original data set according to the total sampling amount.

[0134] The special sampling unit is used to sample all the data that meet the preset sampling conditions in the original data set when the data that meet the preset sampling conditions in the original data set is less than the total sampling amount.

[0135] According to an embodiment of the present invention, the above-mentioned proportional sampling submodule includes a second statistical sorting unit, a data screening unit, a sampling setting unit and a data sampling unit.

[0136] The second statistical sorting unit is used to count the repetition rate of each data in each original data set, and sort the data from large to small according to the repetition rate to obtain a second sorting result.

[0137] The data screening unit is used to screen N data with the highest repetition rate as data meeting the preset sampling condition based on the second sorting result, wherein N is a positive integer set based on user requirements.

[0138] The sampling setting unit is used to obtain the total amount of data of each original data set, and set the number of sampling points, the total number of sampling rows, the sampling interval and the number of point sampling strips according to the total amount of data of each original data set.

[0139] The data sampling unit is used to sample the data in each original data set that meets the preset sampling conditions in proportion according to the number of sampling points, the total number of sampling rows, the sampling interval, and the number of point sampling lines.

[0140] According to an embodiment of the present invention, the above-mentioned re-slicing module includes an average data amount calculation submodule, a re-slicing determination submodule and a secondary division submodule.

[0141] The average data volume calculation submodule is used to calculate the average data volume based on the number of initial data slicing results and the total amount of data included in the initial data slicing results.

[0142] The re-slicing determination submodule is used to calculate the data volume of each initial data slicing result and the average data volume, and determine the initial data slicing result that meets the preset re-slicing conditions based on the calculation result and the predefined threshold.

[0143] The secondary partitioning submodule is used to re-slice the initial data slicing results that meet the preset re-slicing conditions using the secondary partitioning method.

[0144] According to an embodiment of the present invention, the secondary partitioning submodule includes a sorting unit, a shard quantity determining unit, a hash operator unit and a re-slicing unit.

[0145] A sorting unit is used to sort all initial data slicing results that meet the preset re-slicing conditions from large to small according to the amount of data contained therein, to obtain a third sorting result;

[0146] The shard quantity determining unit is used to determine the number of re-shards based on the total amount of data of the initial data slicing results that meet the preset re-sharding conditions, the data amount of each initial data slicing result that meets the preset re-sharding conditions, and the third sorting result.

[0147] The hash operator unit is used to construct a hash function and initialize parameters, and calculate the hash value of the re-sharding by using the hash function and the number of re-sharding.

[0148] The re-slicing unit is used to re-slice the initial data slicing results that meet the preset re-slicing conditions according to the hash value of the re-slicing.

[0149] According to an embodiment of the present invention, the above-mentioned shard quantity determination unit includes a proportion calculation subunit and a shard determination subunit.

[0150] The proportion calculation subunit is used to calculate the proportion of the data volume of each initial data slicing result that meets the preset re-slicing conditions in the total data volume of the initial data slicing results that meet the preset re-slicing conditions.

[0151] The sharding determination subunit is used to determine the number of re-sharding based on the third sorting result using a threshold and a proportion set by a user.

[0152] Figure 7 is a block diagram of an electronic device suitable for implementing a data processing optimization method for a database according to an embodiment of the present invention.

[0153] like Figure 7 As shown, the electronic device 700 according to an embodiment of the present invention includes a processor 701, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 702 or a program loaded from a storage part 708 to a random access memory (RAM) 703. The processor 701 may include, for example, a general-purpose microprocessor (such as a CPU), an instruction set processor and / or a related chipset and / or a special-purpose microprocessor (for example, an application-specific integrated circuit (ASIC)), etc. The processor 701 may also include an onboard memory for caching purposes. The processor 701 may include a single processing unit or multiple processing units for performing different actions of the method flow according to an embodiment of the present invention.

[0154] In RAM 703, various programs and data required for the operation of electronic device 700 are stored. Processor 701, ROM 702 and RAM 703 are connected to each other via bus 704. Processor 701 performs various operations of the method flow according to the embodiment of the present invention by executing the programs in ROM 702 and / or RAM 703. It should be noted that the program can also be stored in one or more memories other than ROM 702 and RAM 703. Processor 701 can also perform various operations of the method flow according to the embodiment of the present invention by executing the programs stored in the one or more memories.

[0155] According to an embodiment of the present invention, the electronic device 700 may further include an input / output (I / O) interface 705, which is also connected to the bus 704. The electronic device 700 may further include one or more of the following components connected to the input / output (I / O) interface 705: an input portion 706 including a keyboard, a mouse, etc.; an output portion 707 including a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker, etc.; a storage portion 708 including a hard disk, etc.; and a communication portion 709 including a network interface card such as a LAN card, a modem, etc. The communication portion 709 performs communication processing via a network such as the Internet. A drive 710 is also connected to the input / output (I / O) interface 705 as needed. A removable medium 711, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 710 as needed, so that a computer program read therefrom is installed into the storage portion 708 as needed.

[0156] The present invention also provides a computer-readable storage medium, which may be included in the device / apparatus / system described in the above embodiment; or may exist independently without being assembled into the device / apparatus / system. The above computer-readable storage medium carries one or more programs, and when the above one or more programs are executed, the method according to the embodiment of the present invention is implemented.

[0157] According to an embodiment of the present invention, the computer-readable storage medium may be a non-volatile computer-readable storage medium, for example, it may include but is not limited to: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination thereof. In the present invention, the computer-readable storage medium may be any tangible medium containing or storing a program, which may be used by or in combination with an instruction execution system, an apparatus or a device. For example, according to an embodiment of the present invention, the computer-readable storage medium may include the ROM 702 and / or RAM 703 described above and / or one or more memories other than ROM 702 and RAM 703.

[0158] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present invention. In this regard, each box in the flow chart or block diagram can represent a module, a program segment, or a part of a code, and the above-mentioned module, program segment, or a part of a code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a different order from the order marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flow chart, and the combination of the boxes in the block diagram or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0159] It will be appreciated by those skilled in the art that the features described in the various embodiments of the present invention may be combined and / or combined in various ways, even if such combinations or combinations are not explicitly described in the present invention. In particular, without departing from the spirit and teachings of the present invention, the features described in the various embodiments of the present invention may be combined and / or combined in various ways. All of these combinations and / or combinations fall within the scope of the present invention.

[0160] The embodiments of the present invention are described above. However, these embodiments are only for the purpose of illustration, and are not intended to limit the scope of the present invention. Although each embodiment is described above, it does not mean that the measures in each embodiment cannot be used in combination advantageously. Without departing from the scope of the present invention, those skilled in the art may make various substitutions and modifications, which should all fall within the scope of the present invention.

Claims

1. A data processing optimization method for a database, characterized in that: The method comprises: Determine the original data set that needs to be grouped and aggregated for each storage node in the distributed database, and use a hybrid sampling method to filter out a target data subset from the original data set; The target data subset is independently sharded by using a hash sharding algorithm, the independent sharding results mapped to different processing nodes are used as independent data blocks, and other data subsets in the original data set are initially sliced; Calculate the average data volume of the initial data slicing results, re-slice the initial data slicing results that meet the preset re-slicing conditions based on the average data volume, and use the re-slicing results as common data blocks; The independent data blocks and the common data blocks are mixed and sorted, and the mixed sorting results are scheduled through the multi-threaded data function of the distributed database so as to concurrently perform grouping and aggregation operations on the original data set.

2. The method according to claim 1, characterized in that Using the mixed sampling method to filter out the target data subset from the original data set includes: Performing fixed sampling on the original data set in each storage node, filtering out data whose repetition rate meets a preset sampling condition from the original data set, and adding the filtered data to the data in the target data subset; and / or The original data set in each storage node is sampled in proportion, data whose repetition rate meets the preset sampling condition is screened out from the original data set, and the screened out data is added to the target data subset.

3. The method according to claim 2, characterized in that Performing fixed sampling on the original data set in each storage node, and filtering out data whose repetition rate meets the preset sampling condition from the original data set includes: Counting the repetition rate of each data in each of the original data sets, and sorting the data from large to small according to the repetition rate to obtain a first sorting result; Based on the first sorting result, N data with the highest repetition rate are selected as data meeting the preset sampling condition, where N is a positive integer set based on user requirements; Acquire the total amount of data of each of the original data sets, and set the number of sampling points and the collection amount of each sampling point according to the total amount of data; Calculating a total sampling amount according to the number of sampling points and the collection amount of each sampling point, and uniformly sampling the data that meets the preset sampling condition in each of the original data sets according to the total sampling amount; When the amount of data in the original data set that meets the preset sampling condition is less than the total sampling amount, all the data in the original data set that meets the preset sampling condition are sampled.

4. The method according to claim 2, characterized in that: Proportionally sampling the original data set in each storage node, and filtering out data whose repetition rate meets the preset sampling condition from the original data set includes: Counting the repetition rate of each data in each of the original data sets, and sorting the data from large to small according to the repetition rate to obtain a second sorting result; Based on the second sorting result, N data with the highest repetition rate are selected as data meeting the preset sampling condition, where N is a positive integer set based on user requirements; Obtaining the total amount of data of each of the original data sets, and setting the number of sampling points, the total number of sampling rows, the sampling interval, and the number of point samplings according to the total amount of data of each of the original data sets; According to the number of sampling points, the total number of sampling rows, the sampling interval and the number of point samplings, the data in each of the original data sets that meets the preset sampling conditions are sampled in proportion.

5. The method according to claim 1, characterized in that Calculating an average data volume of the initial data slicing results, and re-slicing the initial data slicing results that meet the preset re-slicing conditions based on the average data volume includes: The average data amount is calculated based on the number of the initial data slicing results and the total amount of data included in the initial data slicing results; Calculate the data volume of each of the initial data slicing results and the average data volume, and determine the initial data slicing results that meet the preset re-slicing condition based on the calculation result and a predefined threshold; The initial data slicing results that meet the preset re-slicing conditions are re-sliced ​​using a secondary partitioning method.

6. The method according to claim 5, characterized in that Re-slicing the initial data slicing results that meet the preset re-slicing conditions using the secondary partitioning method includes: All initial data slicing results that meet the preset re-slicing condition are sorted from large to small according to the amount of data included, to obtain a third sorting result; Determine the number of re-sharding based on the total amount of data of the initial data slicing results that meet the preset re-slicing condition, the amount of data of each initial data slicing result that meets the preset re-slicing condition, and the third sorting result; Constructing a hash function and initializing parameters, and calculating a hash value of the re-sharding by using the hash function and the number of re-sharding; According to the hash value of the re-sharding, the initial data slicing results that meet the preset re-sharding conditions are re-sharded.

7. The method according to claim 6, characterized in that Determining the number of re-shards based on the total amount of data of the initial data slicing results that meet the preset re-slicing condition, the amount of data of each initial data slicing result that meets the preset re-slicing condition, and the third sorting result comprises: Calculate the proportion of the data volume of each initial data slicing result that meets the preset re-slicing condition in the total data volume of the initial data slicing result that meets the preset re-slicing condition; Based on the third sorting result, the number of the re-sharding is determined by using a threshold set by a user and the proportion.

8. A data processing optimization device for a database, characterized in that: The device comprises: A data screening module is used to determine the original data set that needs to be grouped and aggregated for each storage node in the distributed database, and to screen out a target data subset from the original data set using a hybrid sampling method; An initial sharding module is used to independently shard the target data subset using a hash sharding algorithm, use the independent sharding results mapped to different processing nodes as independent data blocks, and perform initial data slicing on other data subsets in the original data set; A re-slicing module is used to calculate the average data volume of the initial data slicing results, re-slice the initial data slicing results that meet the preset re-slicing conditions based on the average data volume, and use the re-slicing results as ordinary data blocks; The optimization processing module is used to perform mixed sorting on the independent data blocks and the common data blocks, and schedule the mixed sorting results through the multi-threaded data function of the distributed database so as to concurrently perform grouping and aggregation operations on the original data set.

9. An electronic device, comprising: one or more processors; a memory for storing one or more computer programs, It is characterized in that the one or more processors execute the one or more computer programs to implement the steps of the method according to any one of claims 1 to 7.

10. A computer-readable storage medium having a computer program or instruction stored thereon, characterized in that: When the computer program or instruction is executed by a processor, the steps of the method according to any one of claims 1 to 7 are implemented.

Images