Data table optimization method and device, computer equipment and storage medium

Abstract

The invention discloses a data table optimization method and device, computer equipment and a storage medium, and belongs to the technical field of big data. The method comprises the following steps: performing full table partition scanning on a data table to obtain data partitions of the data table; performing data sampling and statistical analysis on the data partitions to obtain data distribution characteristics; determining whether the data partition has data skew based on the data distribution characteristics; acquiring the data partition with data skew to obtain a first data partition, identifying hotspot data in the first data partition, and dividing the hotspot data into a new data partition; and updating the data table, identifying the data partition storing the data in the updated data table to obtain a second data partition, and carrying out load balancing adjustment on the second data partition. The invention further relates to the technical field of block chains, and the data table is stored on a block chain network. The data access efficiency is improved by identifying the data skew, migrating the hotspot data to the new partition and performing data equalization.

Description

Technical Field

[0001] The present application belongs to the field of big data technology, and specifically relates to a data table optimization method, device, computer equipment and storage medium. Background Art

[0002] In the property insurance big data platform, thousands of tasks need to be processed every day. These tasks involve complex data analysis and calculations, which place extremely high demands on system performance and resource utilization efficiency. Especially for tasks with high timeliness requirements, such as real-time risk assessment and fraud detection, their execution efficiency directly affects the timeliness and accuracy of business decisions. However, in the process of big data processing, data skew becomes one of the key factors restricting system performance.

[0003] Data skew refers to the situation in which the amount of data on some partitions or nodes is significantly higher than that on other partitions or nodes during distributed computing, which results in an overload of computing tasks on these nodes, slowing down the overall processing speed and even causing problems such as memory overflow. In the property insurance big data platform, data skew is particularly prominent due to the uneven distribution of data itself or data concentration caused by specific business logic. This not only affects the execution efficiency of big data tasks, but also increases the system's operation and maintenance costs and resource consumption. Summary of the invention

[0004] The purpose of the embodiments of the present application is to propose a data table optimization method, device, computer equipment and storage medium to solve the technical problems of high task execution efficiency, system operation and maintenance costs and resource consumption caused by data skew in the existing distributed task processing process.

[0005] In order to solve the above technical problems, the present application embodiment provides a data table optimization method, which adopts the following technical solution:

[0006] A data table optimization method, comprising:

[0007] Perform a full table partition scan on the data table to obtain the data partitions of the data table;

[0008] Perform data sampling and statistical analysis on the data partitions to obtain data distribution characteristics of the data partitions;

[0009] Determine whether data skew exists in data partitions based on data distribution characteristics;

[0010] Acquire the data partition with data skew to obtain a first data partition, identify hot data in the first data partition, and divide the hot data into a new data partition;

[0011] The data table is updated, a data partition storing data in the updated data table is identified, a second data partition is obtained, and a load balancing adjustment is performed on the second data partition.

[0012] Furthermore, the step of performing a full table partition scan on the data table to obtain data partitions of the data table specifically includes:

[0013] Scan the table structure of the data table using a full table partition scan to determine the partition number and partition key of the data partition in the data table, wherein the partition key defines the column or column combination of the data partition;

[0014] Determine the partition range of the data partition based on the partition number and partition key;

[0015] Determine a partition path of the data partition according to the partition range of the data partition;

[0016] Determine the data storage location of the data partition in the data table according to the partition path of the data partition;

[0017] The data partitions in the data table are determined based on the data storage location of the data partitions.

[0018] Furthermore, the step of performing data sampling and statistical analysis on the data partitions to obtain data distribution characteristics of the data partitions specifically includes:

[0019] Sampling the data in the data partition by using stratified sampling to obtain a first partition data set;

[0020] Statistically analyzing the first partition data set to obtain statistical features of the first partition data set;

[0021] Identify the distribution type to which the first partition data set belongs based on the statistical features of the first partition data set, and obtain a target feature distribution;

[0022] The characteristics of the target feature distribution are extracted to obtain the data distribution characteristics of the data partition.

[0023] Furthermore, the distribution types include normal distribution, exponential distribution and Poisson distribution. The step of determining whether data partitions have data skew based on data distribution characteristics specifically includes:

[0024] Obtain a data partition whose distribution type is a normal distribution, and obtain a third data partition;

[0025] Counting the data volume of the third data partition, and calculating the mean and standard deviation of the third data partition;

[0026] Determine whether there is data skew in the third data partition according to the data volume, mean and standard deviation of the third data partition;

[0027] Obtain a data partition whose distribution type is exponential distribution, and obtain a fourth data partition;

[0028] identifying a data boundary of a fourth data partition, and determining whether data skew exists in the fourth data partition based on the data boundary;

[0029] Obtain a data partition whose distribution type is Poisson distribution, and obtain a fifth data partition;

[0030] Calculating a Poisson parameter of the fifth data partition, and dividing the fifth data partition based on the Poisson parameter to obtain data sub-partitions;

[0031] By comparing the data amounts of the data sub-partitions, it is determined whether there is data skew in the fifth data partition.

[0032] Further, the steps of calculating the Poisson parameter of the fifth data partition and dividing the fifth data partition based on the Poisson parameter to obtain data sub-partitions specifically include:

[0033] Performing data sampling on the fifth data partition to obtain a second partition data set;

[0034] Calculate the mean and variance of the second partition data set, and estimate the Poisson parameter based on the mean and variance of the second partition data set;

[0035] The number of sub-partitions of the fifth data partition is an integer multiple of the Poisson parameter;

[0036] The fifth data partition is divided based on the sub-partition division quantity to obtain data sub-partitions.

[0037] Furthermore, the step of identifying hotspot data in the first data partition and dividing the hotspot data into new data partitions specifically includes:

[0038] Performing frequency statistical analysis on the data in the first data partition and drawing a data distribution curve using a histogram;

[0039] Determine hotspot data in the first data partition based on the data distribution curve and a preset hotspot threshold;

[0040] Performing a clustering operation on the hotspot data in the first data partition to obtain a hotspot data set;

[0041] Query the new data partition in the data table and transfer the hot data set to the new data partition.

[0042] Furthermore, after the step of querying the data table for the new data partition and transferring the hot data set to the new data partition, the method further includes:

[0043] Obtain the storage path of the hotspot data set in the first data partition to obtain a hotspot path set;

[0044] Get the data index of the hotspot dataset and cancel the data index of the hotspot dataset;

[0045] Store the hotspot data set into a new data partition according to the hotspot path set, and generate a new data index.

[0046] In order to solve the above technical problems, the embodiment of the present application further provides a data table optimization device, which adopts the following technical solution:

[0047] A data table optimization device, comprising:

[0048] The partition scanning module is used to perform a full table partition scan on the data table to obtain the data partitions of the data table;

[0049] The sampling and statistics module is used to perform data sampling and statistical analysis on the data partitions to obtain the data distribution characteristics of the data partitions;

[0050] A tilt determination module, used to determine whether a data partition has data tilt based on data distribution characteristics;

[0051] A data transfer module is used to obtain the data partition with data skew, obtain the first data partition, identify the hot data in the first data partition, and divide the hot data into a new data partition;

[0052] The data update module is used to update the data table, identify the data partition storing data in the updated data table, obtain the second data partition, and perform load balancing adjustment on the second data partition.

[0053] In order to solve the above technical problems, the embodiment of the present application further provides a computer device, which adopts the following technical solution:

[0054] A computer device comprises a memory and a processor, wherein the memory stores computer-readable instructions, and the processor implements the steps of any one of the above-mentioned data table optimization methods when executing the computer-readable instructions.

[0055] In order to solve the above technical problems, the embodiment of the present application further provides a computer-readable storage medium, which adopts the following technical solution:

[0056] A computer-readable storage medium stores computer-readable instructions, which, when executed by a processor, implement the steps of the data table optimization method described in any one of the above.

[0057] Compared with the prior art, the embodiments of the present application have the following beneficial effects:

[0058] The present application discloses a data table optimization method, device, computer equipment and storage medium, which belongs to the field of big data technology. The present application obtains data partitions of the data table by performing a full table partition scan on the data table; performs data sampling and statistical analysis on the data partitions to obtain data distribution characteristics of the data partitions; determines whether the data partitions have data skew based on the data distribution characteristics; obtains the data partitions with data skew to obtain the first data partition, and identifies the hot data in the first data partition, and divides the hot data into new data partitions; updates the data table, identifies the data partitions storing data in the updated data table, obtains the second data partition, and performs load balancing adjustment on the second data partition. The present application effectively identifies and handles the problem of data skew through full table partition scanning and data analysis, reduces query load bottlenecks by migrating hot data to new partitions, and improves data access efficiency. At the same time, load balancing adjustment is performed on the updated data partitions to further optimize the performance of the data table, ensure the stable operation of the system in a high-concurrency environment, and improve the overall data processing capability and system response speed. BRIEF DESCRIPTION OF THE DRAWINGS

[0059] In order to more clearly illustrate the scheme in the present application, a brief introduction is given below to the drawings required for use in the description of the embodiments of the present application. Obviously, the drawings described below are some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0060] Figure 1 An exemplary system architecture diagram is shown in which the present application can be applied;

[0061] Figure 2 A flow chart showing an embodiment of a data table optimization method according to the present application is shown;

[0062] Figure 3 A schematic diagram showing the structure of an embodiment of a data table optimization device according to the present application is shown;

[0063] Figure 4 A schematic structural diagram of an embodiment of a computer device according to the present application is shown. DETAILED DESCRIPTION

[0064] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as those commonly understood by technicians in the technical field of the present application; the terms used in the specification of the application herein are only for the purpose of describing specific embodiments and are not intended to limit the present application; the terms "including" and "having" and any variations thereof in the specification and claims of the present application and the above-mentioned drawings are intended to cover non-exclusive inclusions. The terms "first", "second", etc. in the specification and claims of the present application or the above-mentioned drawings are used to distinguish different objects, not to describe a specific order.

[0065] Reference to "embodiments" herein means that a particular feature, structure, or characteristic described in conjunction with the embodiments may be included in at least one embodiment of the present application. The appearance of the phrase in various locations in the specification does not necessarily refer to the same embodiment, nor is it an independent or alternative embodiment that is mutually exclusive with other embodiments. It is explicitly and implicitly understood by those skilled in the art that the embodiments described herein may be combined with other embodiments.

[0066] In order to enable those skilled in the art to better understand the solution of the present application, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the accompanying drawings.

[0067] like Figure 1 As shown, the system architecture 100 may include terminal devices 101, 102, 103, a network 104 and a server 105. The network 104 is used to provide a medium for communication links between the terminal devices 101, 102, 103 and the server 105. The network 104 may include various connection types, such as wired, wireless communication links or optical fiber cables, etc.

[0068] Users can use terminal devices 101, 102, 103 to interact with server 105 through network 104 to receive or send messages, etc. Various communication client applications can be installed on terminal devices 101, 102, 103, such as web browser applications, shopping applications, search applications, instant messaging tools, email clients, social platform software, etc.

[0069] Terminal devices 101, 102, 103 can be various electronic devices with display screens and supporting web browsing, including but not limited to smart phones, tablet computers, e-book readers, MP3 players (Moving Picture Experts Group Audio Layer III), MP4 (Moving Picture Experts Group Audio Layer IV) players, laptop computers and desktop computers, etc.

[0070] Server 105 can be a server that provides various services, such as a background server that provides support for pages displayed on terminal devices 101, 102, and 103. The server can be an independent server or a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, content distribution networks (CDNs), and big data and artificial intelligence platforms.

[0071] It should be noted that the data table optimization method provided in the embodiment of the present application is generally executed by a server, and accordingly, the data table optimization device is generally arranged in the server.

[0072] It should be understood that Figure 1 The number of terminal devices, networks and servers in the embodiment is only for illustration. Any number of terminal devices, networks and servers may be provided according to the implementation requirements.

[0073] Continue to refer Figure 2 , shows a flow chart of an embodiment of the data table optimization method according to the present application. The present application embodiment can acquire and process relevant data based on artificial intelligence technology. Among them, artificial intelligence (AI) is the theory, method, technology and application system of using digital computers or machines controlled by digital computers to simulate, extend and expand human intelligence, perceive the environment, acquire knowledge and use knowledge to obtain the best results.

[0074] The basic technologies of artificial intelligence generally include sensors, dedicated artificial intelligence chips, cloud computing, distributed storage, big data processing technology, operation / interaction systems, mechatronics and other technologies. Artificial intelligence software technology mainly includes computer vision technology, robotics technology, biometrics technology, speech processing technology, natural language processing technology, and machine learning / deep learning. The data table optimization method includes the following steps:

[0075] S201, performing a full table partition scan on the data table to obtain data partitions of the data table.

[0076] A data table refers to the entire data table in a database or data warehouse. The data table is usually divided into multiple data partitions according to business processing requirements. By dividing the entire data table into multiple smaller data partitions, data can be managed and queried more efficiently. For example, taking the property insurance database as an example, the database can be divided into policy information partitions, claim information partitions, customer information partitions, insurance product partitions, channel information partitions, etc. Data partitions can be based on different strategies, such as range partitions, list partitions, hash partitions, etc. Select the appropriate partitioning strategy based on the specific application scenario and data characteristics.

[0077] Specifically, the system performs a full table partition scan to fully understand the structure and storage status of the data table. By scanning all partitions in the data table, detailed information about each partition can be obtained, such as partition size, data distribution, etc.

[0078] S202, performing data sampling and statistical analysis on the data partitions to obtain data distribution characteristics of the data partitions.

[0079] The purpose of data sampling and statistical analysis on data partitions is to efficiently and accurately understand the data characteristics within the data partitions, such as distribution pattern, concentration level, etc., so as to identify data skew problems in the data partitions.

[0080] Specifically, by sampling the data partitions, representative samples of the data in each data partition are collected, avoiding the high computational cost of processing the entire partition data. Subsequently, these sampled samples are statistically analyzed to obtain the data distribution characteristics of the data partitions, such as the concentration and distribution form of the data. The data distribution characteristics reflect the storage and access efficiency bottlenecks of the data partitions.

[0081] S203: Determine whether data skew exists in the data partition based on the data distribution characteristics.

[0082] Specifically, after obtaining the data distribution characteristics of the data partitions, the data balance within or between data partitions can be evaluated by comparing and analyzing these data distribution characteristics. For example, if the amount of data in a data partition far exceeds that of other data partitions, or certain types of data are concentrated in a few partitions, or the difference in the amount of data in the sub-partitions of a data partition exceeds the set threshold, then it can be determined that the data partition has data skew. By identifying data skew and taking targeted processing measures, such as migrating hot data and adjusting partition strategies, the performance of the data table can be improved.

[0083] S204, obtaining the data partition with data skew, obtaining the first data partition, identifying the hot data in the first data partition, and dividing the hot data into a new data partition.

[0084] Hot data refers to data that is frequently accessed or queried. For example, frequently accessed data such as the number of auto insurance claims, claim amounts, claim types (such as collisions, thefts, natural disasters, etc.), and claim processing time can be considered hot data. When this data is concentrated in the same data partition, the access pressure of the partition will increase significantly, which will in turn affect query performance. By transferring hot data to a new data partition, the access pressure can be dispersed, so that query requests can be more evenly distributed on different partitions, which can reduce the degree of data skewness in the original partition and make the data distribution more even, thereby improving overall query efficiency.

[0085] Specifically, the system identifies all data partitions with data skew, obtains the first data partition, identifies the hot data in the first data partition, and divides the hot data into new data partitions. The hot data sets are transferred to the new data partitions in order to optimize query performance, achieve load balancing, improve resource utilization efficiency, enhance system scalability, and reduce data skew.

[0086] S205, updating the data table, identifying the data partition storing data in the updated data table, obtaining the second data partition, and performing load balancing adjustment on the second data partition.

[0087] Specifically, after the hot data is divided into new data partitions, the system updates the data table and further performs load balancing adjustments on the data partitions. By identifying the data partitions storing data in the updated data table, the second data partition is obtained, and load balancing adjustments are performed on the second data partitions. After the hot data is divided into new data partitions, load balancing adjustments are further performed on the data partitions to ensure that the system can continue to run efficiently and improve system performance, stability and reliability.

[0088] In the above embodiment, the present application effectively identifies and handles the data skew problem through full table partition scanning and data analysis, and reduces query load bottlenecks and improves data access efficiency by migrating hot data to new partitions. At the same time, load balancing is performed on the updated data partitions to further optimize the data table performance, ensure the stable operation of the system in a high-concurrency environment, and improve the overall data processing capability and system response speed.

[0089] Furthermore, the step of performing a full table partition scan on the data table to obtain data partitions of the data table specifically includes:

[0090] Scan the table structure of the data table using a full table partition scan to determine the partition number and partition key of the data partition in the data table, wherein the partition key defines the column or column combination of the data partition;

[0091] Determine the partition range of the data partition based on the partition number and partition key;

[0092] Determine a partition path of the data partition according to the partition range of the data partition;

[0093] Determine the data storage location of the data partition in the data table according to the partition path of the data partition;

[0094] The data partitions in the data table are determined based on the data storage location of the data partitions.

[0095] A partition key is a key column or column combination used to define how a table is divided into multiple physical parts (i.e. partitions). A partition key is one or more columns in a data table that are used to determine which partition a data row should be assigned to. The choice of a partition key depends on the data access pattern, query type, and characteristics of the data itself. For example, if the data in a table grows in chronological order and you often need to query data by time range, then the time column may be a good partition key.

[0096] Different database systems support different partitioning types, such as range partitioning, list partitioning, hash partitioning, etc. Each partitioning type has its own specific way of defining the partition range.

[0097] The value of the partition key is used to determine which partition a row belongs to. Each partition defines a range, and the data row determines the partition it belongs to based on which part of the range the partition key value falls into. For example, if the partition key is a date, you can define multiple partitions, each containing data for a specific year or month.

[0098] In this embodiment, first, the system identifies the partition number and partition key by scanning the table structure. Then, the specific range of each partition is determined according to the partition number and partition key to ensure that the data is correctly classified into the corresponding partition. Subsequently, the partition path is derived through the partition range to quickly locate the data at the physical storage level. Finally, the data storage location is determined according to the partition path, thereby accurately identifying the data partition in the data table.

[0099] The system will set a clear value range or list for each partition based on the definition of the partition key (such as date, region, etc.) and the partition strategy (such as range partitioning, list partitioning, etc.). Then, when data is inserted or updated, the system will automatically determine the range of the partition to which the data should belong based on the value of the partition key and store it in the corresponding partition.

[0100] In the database, the partition path is derived from the partition range. The specific implementation usually depends on the internal mapping mechanism of the database management system. The system will associate the partition range with the physical path or logical identifier of the storage device. When the data of a partition needs to be accessed, the system will query the corresponding partition path according to the partition range, and then quickly locate the data file or block under the path on the physical storage device.

[0101] Through the above steps, the partition structure of the data table can be accurately identified through full table partition scanning, including partition number, partition key and partition range, and then the partition path can be derived through the partition range to achieve rapid positioning of data at the physical storage level, thereby improving the accuracy and efficiency of data management and optimizing data access performance. In particular, when processing large-scale data sets, it can significantly reduce query latency and enhance the overall responsiveness of the system.

[0102] Furthermore, the step of performing data sampling and statistical analysis on the data partitions to obtain data distribution characteristics of the data partitions specifically includes:

[0103] Sampling the data in the data partition by using stratified sampling to obtain a first partition data set;

[0104] Statistically analyzing the first partition data set to obtain statistical features of the first partition data set;

[0105] Identify the distribution type to which the first partition data set belongs based on the statistical features of the first partition data set, and obtain a target feature distribution;

[0106] The characteristics of the target feature distribution are extracted to obtain the data distribution characteristics of the data partition.

[0107] In this embodiment, first, a stratified sampling method is used to select representative samples from the data partitions to ensure that the samples can reflect the diversity of the overall data, thereby obtaining a first partition data set. Subsequently, the first partition data set is statistically analyzed to extract key statistical features, such as mean, variance, skewness, and kurtosis. Next, based on the statistical features, the statistical principles are used to identify the distribution type that the data obeys, such as normal distribution, Poisson distribution, etc., and the target feature distribution is determined according to the distribution type that the data obeys.

[0108] Through the above steps, stratified sampling and statistical analysis are used to accurately extract the data distribution characteristics of data partitions. Stratified sampling ensures the representativeness of the sample, statistical analysis reveals the statistical characteristics, and distribution type identification clarifies the data pattern. Finally, the extracted characteristics fully reflect the data characteristics.

[0109] Furthermore, the distribution types include normal distribution, exponential distribution and Poisson distribution. The step of determining whether data partitions have data skew based on data distribution characteristics specifically includes:

[0110] Obtain a data partition whose distribution type is a normal distribution, and obtain a third data partition;

[0111] Counting the data volume of the third data partition, and calculating the mean and standard deviation of the third data partition;

[0112] Determine whether there is data skew in the third data partition according to the data volume, mean and standard deviation of the third data partition;

[0113] Obtain a data partition whose distribution type is exponential distribution, and obtain a fourth data partition;

[0114] identifying a data boundary of a fourth data partition, and determining whether data skew exists in the fourth data partition based on the data boundary;

[0115] Obtain a data partition whose distribution type is Poisson distribution, and obtain a fifth data partition;

[0116] Calculating a Poisson parameter of the fifth data partition, and dividing the fifth data partition based on the Poisson parameter to obtain data sub-partitions;

[0117] By comparing the data amounts of the data sub-partitions, it is determined whether there is data skew in the fifth data partition.

[0118] Normal distribution is a symmetrical distribution, and its data is symmetrically distributed around the mean. When judging whether there is data skew in the normal distribution data, we can compare the data volume, mean, standard deviation and other indicators of each normal distribution data partition to determine whether there are obvious outliers or significant inconsistencies.

[0119] Exponential distribution is often used to describe the interval between events or the probability of rare events occurring consecutively. Since the characteristic of exponential distribution is that data is mainly concentrated in a small value range and decreases rapidly as the value increases, the partition boundaries can be checked to determine whether there is data skew in exponential distribution data.

[0120] Poisson distribution is used to describe the number of times an event occurs within a fixed time or space interval. You can determine whether data skew exists by checking whether the partitions are reasonably divided according to the parameters of the Poisson distribution (such as the average occurrence rate λ).

[0121] In this embodiment, the data distribution types of data partitions include normal distribution, exponential distribution and Poisson distribution. For data partitions with normal distribution, the system evaluates the balance of data distribution by statistical quantities, mean and standard deviation, combined with statistical principles, so as to determine whether there is data skew. For data partitions with exponential distribution, the system becomes the key by identifying data boundaries, and evaluates whether the data is concentrated or dispersed through boundary analysis, and then determines the data skew. For data partitions with Poisson distribution, the system calculates Poisson parameters and further divides the partitions to obtain data sub-partitions, and evaluates data skew by comparing the data volume of the sub-partitions.

[0122] Through the above steps, the system improves the accuracy and efficiency of data skew identification by utilizing the characteristics of different distribution types and adopting targeted detection methods.

[0123] Further, the steps of calculating the Poisson parameter of the fifth data partition and dividing the fifth data partition based on the Poisson parameter to obtain data sub-partitions specifically include:

[0124] Performing data sampling on the fifth data partition to obtain a second partition data set;

[0125] Calculate the mean and variance of the second partition data set, and estimate the Poisson parameter based on the mean and variance of the second partition data set;

[0126] The number of sub-partitions of the fifth data partition is an integer multiple of the Poisson parameter;

[0127] The fifth data partition is divided based on the sub-partition division quantity to obtain data sub-partitions.

[0128] In this embodiment, a data sub-partitioning method based on Poisson parameters is proposed for data partitioning of Poisson distribution. First, a representative data set, i.e., a second partition data set, is obtained by sampling the data partition of Poisson distribution, and its mean and variance are calculated, and the Poisson parameter λ is estimated on this basis. Subsequently, an integer multiple of the Poisson parameter λ is used as the basis for the number of sub-partitioning divisions. For example, the data partition can be divided into λ sub-partitions, 2λ sub-partitions, 3λ sub-partitions, etc. Finally, based on the determined number of sub-partitioning divisions, the original data partition is divided to obtain data sub-partitions.

[0129] Through the above steps, the system accurately estimates the Poisson parameters and uses integer multiples of the parameters as the basis for sub-partitioning, thereby achieving effective partitioning of Poisson distribution data. This not only retains the natural distribution characteristics of the data, but also improves the flexibility and efficiency of data processing, helps to reduce data skew, and improves the overall performance of the system.

[0130] Furthermore, the step of identifying hotspot data in the first data partition and dividing the hotspot data into new data partitions specifically includes:

[0131] Performing frequency statistical analysis on the data in the first data partition and drawing a data distribution curve using a histogram;

[0132] Determine hotspot data in the first data partition based on the data distribution curve and a preset hotspot threshold;

[0133] Performing a clustering operation on the hotspot data in the first data partition to obtain a hotspot data set;

[0134] Query the new data partition in the data table and transfer the hot data set to the new data partition.

[0135] In this embodiment, first, through frequency statistical analysis and histogram drawing, the data distribution is intuitively displayed to provide a basis for hotspot identification. Then, combined with the preset hotspot threshold, the hotspot data is accurately located to ensure the accuracy and effectiveness of hotspot data identification. Then, the hotspot data is clustered to form a hotspot data set, which is convenient for subsequent unified processing and management. Finally, by querying and creating a new data partition, the hotspot data set is migrated to the new partition, which effectively alleviates the access pressure of the original partition and improves the performance and efficiency of data processing.

[0136] Through the above steps, hot data is transferred to the new data partition, which can disperse the access pressure and make query requests more evenly distributed on different partitions. This can reduce the data skewness of the original partition and make data distribution more even, thereby improving the overall query efficiency.

[0137] Furthermore, after the step of querying the data table for the new data partition and transferring the hot data set to the new data partition, the method further includes:

[0138] Obtain the storage path of the hotspot data set in the first data partition to obtain a hotspot path set;

[0139] Get the data index of the hotspot dataset and cancel the data index of the hotspot dataset;

[0140] Store the hotspot data set into a new data partition according to the hotspot path set, and generate a new data index.

[0141] In this embodiment, first, the accuracy of data migration is ensured by obtaining the storage path of the hotspot dataset in the original partition. Then, the data index of the hotspot dataset in the original partition is cancelled to avoid subsequent data redundancy and index conflict, and ensure data consistency and queryability. Finally, the hotspot dataset is stored in the new partition according to the new storage path, and a new data index is generated to support fast access and query of the new partition data.

[0142] Through the above steps, after migrating the hot data, the consistency and accuracy of the data index are effectively maintained. By canceling the old index and generating a new index, the efficiency and reliability of data access are ensured. At the same time, the data storage path is clarified, the accuracy and integrity of data migration are guaranteed, the data distribution is further optimized, and the performance of data management and query is improved.

[0143] In the above embodiment, the present application discloses a data table optimization method, which belongs to the field of big data technology. The present application obtains data partitions of the data table by performing a full table partition scan on the data table; performs data sampling and statistical analysis on the data partitions to obtain data distribution characteristics of the data partitions; determines whether the data partitions have data skew based on the data distribution characteristics; obtains the data partitions with data skew to obtain the first data partition, and identifies the hot data in the first data partition, and divides the hot data into new data partitions; updates the data table, identifies the data partitions storing data in the updated data table, obtains the second data partition, and performs load balancing adjustment on the second data partition. The present application effectively identifies and handles the problem of data skew through full table partition scanning and data analysis, reduces query load bottlenecks by migrating hot data to new partitions, and improves data access efficiency. At the same time, load balancing adjustment is performed on the updated data partitions to further optimize the performance of the data table, ensure the stable operation of the system in a high-concurrency environment, and improve the overall data processing capability and system response speed.

[0144] In this embodiment, the electronic device (eg, Figure 1The server shown in the figure) can receive instructions or obtain data through a wired connection or a wireless connection. It should be noted that the above wireless connection method may include but is not limited to 3G / 4G connection, WiFi connection, Bluetooth connection, WiMAX connection, Zigbee connection, UWB (ultra wideband) connection, and other wireless connection methods currently known or to be developed in the future.

[0145] It should be emphasized that in order to further ensure the privacy and security of the above data table, the above data table can also be stored in a node of a blockchain.

[0146] The blockchain referred to in this application is a new application model of computer technologies such as distributed data storage, peer-to-peer transmission, consensus mechanism, encryption algorithm, etc. Blockchain is essentially a decentralized database, a string of data blocks generated by cryptographic methods. Each data block contains a batch of network transaction information, which is used to verify the validity of its information (anti-counterfeiting) and generate the next block. Blockchain can include the underlying blockchain platform, platform product service layer, and application service layer.

[0147] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiments can be implemented by instructing related hardware through computer-readable instructions, and the computer-readable instructions can be stored in a computer-readable storage medium. When the computer-readable instructions are executed, they can include the processes of the embodiments of the above-mentioned methods. Among them, the aforementioned storage medium can be a non-volatile storage medium such as a disk, an optical disk, a read-only memory (ROM), or a random access memory (RAM).

[0148] It should be understood that, although the steps in the flowchart of the accompanying drawings are displayed in sequence as indicated by the arrows, these steps are not necessarily executed in sequence in the order indicated by the arrows. Unless otherwise specified herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least a part of the steps in the flowchart of the accompanying drawings may include multiple sub-steps or multiple stages, and these sub-steps or stages are not necessarily executed at the same time, but can be executed at different times, and their execution order is not necessarily sequential, but can be executed in turn or alternately with other steps or at least a part of the sub-steps or stages of other steps.

[0149] Further references Figure 3 , as a response to the above Figure 2 The present application provides an embodiment of a data table optimization device, and the device embodiment is similar to Figure 2 Corresponding to the method embodiment shown, the device can be specifically applied to various electronic devices.

[0150] like Figure 3 As shown, the data table optimization device 300 described in this embodiment includes:

[0151] The partition scanning module 301 is used to perform a full table partition scan on the data table to obtain data partitions of the data table;

[0152] The sampling and statistics module 302 is used to perform data sampling and statistical analysis on the data partitions to obtain data distribution characteristics of the data partitions;

[0153] A tilt determination module 303, used to determine whether a data partition has data tilt based on data distribution characteristics;

[0154] The data transfer module 304 is used to obtain the data partition with data skew, obtain the first data partition, identify the hot data in the first data partition, and divide the hot data into a new data partition;

[0155] The data updating module 305 is used to update the data table, identify the data partition storing data in the updated data table, obtain the second data partition, and perform load balancing adjustment on the second data partition.

[0156] Furthermore, the partition scanning module 301 is specifically used for:

[0157] Use full table partition scan to scan the table structure of the data table to determine the partition number and partition key of the data partition in the data table, where the partition key defines the column or column combination of the data partition; determine the partition range of the data partition according to the partition number and partition key; determine the partition path of the data partition according to the partition range of the data partition; determine the data storage location of the data partition in the data table according to the partition path of the data partition; determine the data partition in the data table according to the data storage location of the data partition.

[0158] Furthermore, the sampling statistics module 302 is specifically used for:

[0159] Stratified sampling is used to sample the data in the data partition to obtain a first partition data set; statistical analysis is performed on the first partition data set to obtain statistical characteristics of the first partition data set; based on the statistical characteristics of the first partition data set, the distribution type to which the first partition data set belongs is identified to obtain a target characteristic distribution; characteristics of the target characteristic distribution are extracted to obtain data distribution characteristics of the data partition.

[0160] Furthermore, the distribution types include normal distribution, exponential distribution and Poisson distribution, and the tilt determination module 303 is specifically used for:

[0161] Obtain a data partition whose distribution type is a normal distribution to obtain a third data partition; count the data volume of the third data partition, and calculate the mean and standard deviation of the third data partition; determine whether the third data partition has data skew according to the data volume, mean and standard deviation of the third data partition; obtain a data partition whose distribution type is an exponential distribution to obtain a fourth data partition;

[0162] Identify the data boundary of the fourth data partition, and determine whether there is data skew in the fourth data partition based on the data boundary; obtain the data partition with a distribution type of Poisson distribution to obtain the fifth data partition; calculate the Poisson parameter of the fifth data partition, and divide the fifth data partition based on the Poisson parameter to obtain data sub-partitions; determine whether there is data skew in the fifth data partition by comparing the data volume of the data sub-partitions.

[0163] Furthermore, the tilt determination module 303 is further used for:

[0164] Data sampling is performed on the fifth data partition to obtain a second partition data set; the mean and variance of the second partition data set are calculated, and the Poisson parameter is estimated based on the mean and variance of the second partition data set; the number of sub-partitions of the fifth data partition is an integer multiple of the Poisson parameter; the fifth data partition is divided based on the number of sub-partitions to obtain data sub-partitions.

[0165] Furthermore, the data transfer module 304 is specifically used for:

[0166] Performing frequency statistical analysis on the data in the first data partition and drawing a data distribution curve using a histogram;

[0167] Determine hotspot data in the first data partition based on the data distribution curve and a preset hotspot threshold;

[0168] Performing a clustering operation on the hotspot data in the first data partition to obtain a hotspot data set;

[0169] Query the new data partition in the data table and transfer the hot data set to the new data partition.

[0170] Furthermore, the data table optimization device 300 also includes a data update module, and the index update module is specifically used for:

[0171] Obtain the storage path of the hotspot data set in the first data partition and obtain a hotspot path set; obtain the data index of the hotspot data set and cancel the data index of the hotspot data set; store the hotspot data set to a new data partition according to the hotspot path set and generate a new data index.

[0172] In the above embodiment, the present application discloses a data table optimization device, which belongs to the field of big data technology. The present application obtains data partitions of the data table by performing a full table partition scan on the data table; performs data sampling and statistical analysis on the data partitions to obtain data distribution characteristics of the data partitions; determines whether the data partitions have data skew based on the data distribution characteristics; obtains the data partitions with data skew to obtain the first data partition, and identifies the hot data in the first data partition, and divides the hot data into new data partitions; updates the data table, identifies the data partitions storing data in the updated data table, obtains the second data partition, and performs load balancing adjustment on the second data partition. The present application effectively identifies and handles the problem of data skew through full table partition scanning and data analysis, reduces query load bottlenecks and improves data access efficiency by migrating hot data to new partitions. At the same time, load balancing adjustment is performed on the updated data partitions to further optimize the performance of the data table, ensure the stable operation of the system in a high-concurrency environment, and improve the overall data processing capability and system response speed.

[0173] To solve the above technical problems, the present application also provides a computer device. Figure 4 , Figure 4 This is a basic structural block diagram of the computer device in this embodiment.

[0174] The computer device 4 includes a memory 41, a processor 42, and a network interface 43 that are interconnected through a system bus. It should be noted that the figure only shows a computer device 4 with components 41-43, but it should be understood that it is not required to implement all the components shown, and more or fewer components can be implemented instead. Among them, those skilled in the art can understand that the computer device here is a device that can automatically perform numerical calculations and / or information processing according to pre-set or stored instructions, and its hardware includes but is not limited to microprocessors, application specific integrated circuits (Application Specific Integrated Circuit, ASIC), field-programmable gate arrays (Field-Programmab l eGate Array, FPGA), digital processors (Digital Signal Processor, DSP), embedded devices, etc.

[0175] The computer device may be a computing device such as a desktop computer, a notebook, a PDA, a cloud server, etc. The computer device may interact with a user through a keyboard, a mouse, a remote controller, a touch pad, or a voice control device.

[0176] The memory 41 includes at least one type of readable storage medium, and the readable storage medium includes flash memory, hard disk, multimedia card, card-type memory (for example, SD or DX memory, etc.), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM), magnetic memory, disk, optical disk, etc. In some embodiments, the memory 41 can be an internal storage unit of the computer device 4, such as a hard disk or memory of the computer device 4. In other embodiments, the memory 41 can also be an external storage device of the computer device 4, such as a plug-in hard disk equipped on the computer device 4, a smart memory card (Smart Media Card, SMC), a secure digital (Secure Digital, SD) card, a flash card (FlashCard), etc. Of course, the memory 41 can also include both the internal storage unit of the computer device 4 and its external storage device. In this embodiment, the memory 41 is generally used to store the operating system and various application software installed on the computer device 4, such as computer-readable instructions of the data table optimization method, etc. In addition, the memory 41 can also be used to temporarily store various types of data that have been output or are to be output.

[0177] The processor 42 may be a central processing unit (CPU), a controller, a microcontroller, a microprocessor, or other data processing chips in some embodiments. The processor 42 is generally used to control the overall operation of the computer device 4. In this embodiment, the processor 42 is used to run the computer-readable instructions stored in the memory 41 or process data, such as computer-readable instructions for running the data table optimization method.

[0178] The network interface 43 may include a wireless network interface or a wired network interface. The network interface 43 is generally used to establish a communication connection between the computer device 4 and other electronic devices.

[0179] In the above embodiment, the present application discloses a computer device, which belongs to the field of big data technology. The present application obtains data partitions of the data table by performing a full table partition scan on the data table; performs data sampling and statistical analysis on the data partitions to obtain data distribution characteristics of the data partitions; determines whether the data partitions have data skew based on the data distribution characteristics; obtains the data partitions with data skew to obtain the first data partition, and identifies the hot data in the first data partition, and divides the hot data into new data partitions; updates the data table, identifies the data partitions storing data in the updated data table, obtains the second data partition, and performs load balancing adjustment on the second data partition. The present application effectively identifies and handles the problem of data skew through full table partition scanning and data analysis, reduces query load bottlenecks by migrating hot data to new partitions, and improves data access efficiency. At the same time, load balancing adjustment is performed on the updated data partitions to further optimize the performance of the data table, ensure the stable operation of the system in a high-concurrency environment, and improve the overall data processing capability and system response speed.

[0180] The present application also provides another embodiment, namely, providing a computer-readable storage medium, wherein the computer-readable storage medium stores computer-readable instructions, and the computer-readable instructions can be executed by at least one processor to enable the at least one processor to perform the steps of the data table optimization method as described above.

[0181] In the above embodiment, the present application discloses a computer-readable storage medium, which belongs to the field of big data technology. The present application obtains data partitions of the data table by performing a full table partition scan on the data table; performs data sampling and statistical analysis on the data partitions to obtain data distribution characteristics of the data partitions; determines whether the data partitions have data skew based on the data distribution characteristics; obtains the data partitions with data skew to obtain the first data partition, and identifies the hot data in the first data partition, and divides the hot data into new data partitions; updates the data table, identifies the data partitions storing data in the updated data table, obtains the second data partition, and performs load balancing adjustment on the second data partition. The present application effectively identifies and handles the problem of data skew through full table partition scanning and data analysis, reduces query load bottlenecks by migrating hot data to new partitions, and improves data access efficiency. At the same time, load balancing adjustment is performed on the updated data partitions to further optimize the performance of the data table, ensure the stable operation of the system in a high-concurrency environment, and improve the overall data processing capability and system response speed.

[0182] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus a necessary general hardware platform, and of course by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present application, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), and includes a number of instructions for a terminal device (which can be a mobile phone, computer, server, air conditioner, or network device, etc.) to execute the methods described in each embodiment of the present application.

[0183] The present application can be used in many general or special computer system environments or configurations. For example: personal computers, server computers, handheld or portable devices, tablet devices, multiprocessor systems, microprocessor-based systems, set-top boxes, programmable consumer electronics, network PCs, minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, etc. The present application can be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform specific tasks or implement specific abstract data types. The present application can also be practiced in distributed computing environments, in which tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules can be located in local and remote computer storage media including storage devices.

[0184] Obviously, the embodiments described above are only some embodiments of the present application, rather than all embodiments. The preferred embodiments of the present application are given in the accompanying drawings, but they do not limit the patent scope of the present application. The present application can be implemented in many different forms. On the contrary, the purpose of providing these embodiments is to make the understanding of the disclosure of the present application more thorough and comprehensive. Although the present application is described in detail with reference to the aforementioned embodiments, for those skilled in the art, it is still possible to modify the technical solutions recorded in the aforementioned specific implementation methods, or to perform equivalent replacement of some of the technical features therein. Any equivalent structure made using the contents of the specification and drawings of this application, directly or indirectly used in other related technical fields, is similarly within the scope of patent protection of this application.

Claims

1. A data table optimization method, characterized in that: include: Performing a full table partition scan on the data table to obtain data partitions of the data table; Performing data sampling and statistical analysis on the data partitions to obtain data distribution characteristics of the data partitions; Determining whether data skew exists in the data partition based on the data distribution characteristics; Acquire a data partition with data skew to obtain a first data partition, identify hot data in the first data partition, and divide the hot data into a new data partition; The data table is updated, a data partition storing data in the updated data table is identified, a second data partition is obtained, and a load balancing adjustment is performed on the second data partition.

2. The data table optimization method according to claim 1, characterized in that: The step of performing a full table partition scan on the data table to obtain data partitions of the data table specifically includes: Scanning the table structure of the data table by using the full table partition scan to determine the partition number and partition key of the data partition in the data table, wherein the partition key defines a column or a combination of columns of the data partition; Determine a partition range of the data partition according to the partition number and the partition key; Determine a partition path of the data partition according to a partition range of the data partition; Determine a data storage location of the data partition in the data table according to a partition path of the data partition; The data partition in the data table is determined according to the data storage location of the data partition.

3. The data table optimization method according to claim 1, characterized in that: The step of performing data sampling and statistical analysis on the data partition to obtain data distribution characteristics of the data partition specifically includes: Sampling the data in the data partition by stratified sampling to obtain a first partition data set; Statistically analyzing the first partition data set to obtain statistical features of the first partition data set; Identify the distribution type of the first partitioned data set based on the statistical features of the first partitioned data set, and obtain a target feature distribution; The characteristics of the target feature distribution are extracted to obtain the data distribution characteristics of the data partition.

4. The data table optimization method according to claim 3, characterized in that: The distribution types include normal distribution, exponential distribution and Poisson distribution. The step of determining whether the data partition has data skew based on the data distribution characteristics specifically includes: Obtain a data partition whose distribution type is a normal distribution, and obtain a third data partition; Counting the data volume of the third data partition, and calculating the mean and standard deviation of the third data partition; Determining whether there is data skew in the third data partition according to the data volume, mean and standard deviation of the third data partition; Obtain a data partition whose distribution type is exponential distribution, and obtain a fourth data partition; identifying a data boundary of the fourth data partition, and determining whether data skew exists in the fourth data partition based on the data boundary; Obtain a data partition whose distribution type is Poisson distribution, and obtain a fifth data partition; Calculating a Poisson parameter of the fifth data partition, and dividing the fifth data partition based on the Poisson parameter to obtain data sub-partitions; Whether data skew exists in the fifth data partition is determined by comparing the data amounts of the data sub-partitions.

5. The data table optimization method according to claim 4, characterized in that: The step of calculating the Poisson parameter of the fifth data partition and dividing the fifth data partition based on the Poisson parameter to obtain data sub-partitions specifically includes: Performing data sampling on the fifth data partition to obtain a second partition data set; Calculating the mean and variance of the second partition data set, and estimating the Poisson parameter based on the mean and variance of the second partition data set; The number of sub-partitions of the fifth data partition is determined by taking an integer multiple of the Poisson parameter; The fifth data partition is divided based on the sub-partition division quantity to obtain the data sub-partitions.

6. The data table optimization method according to claim 1, characterized in that: The step of identifying hotspot data in the first data partition and dividing the hotspot data into a new data partition specifically includes: Performing frequency statistical analysis on the data in the first data partition and drawing a data distribution curve using a histogram; Based on the data distribution curve and a preset hotspot threshold, determining hotspot data in the first data partition; Performing a clustering operation on the hotspot data in the first data partition to obtain a hotspot data set; The new data partition is queried in the data table, and the hot data set is transferred to the new data partition.

7. The data table optimization method according to claim 6, characterized in that: After the step of querying the new data partition in the data table and transferring the hot data set to the new data partition, the method further includes: Acquire a storage path of the hotspot data set in the first data partition to obtain a hotspot path set; Obtaining a data index of the hotspot data set, and canceling the data index of the hotspot data set; The hotspot data set is stored in the new data partition according to the hotspot path set, and a new data index is generated.

8. A data table optimization device, characterized in that: include: A partition scanning module is used to perform a full table partition scan on a data table to obtain data partitions of the data table; A sampling and statistics module, used to perform data sampling and statistical analysis on the data partitions to obtain data distribution characteristics of the data partitions; A tilt determination module, used for determining whether the data partition has data tilt based on the data distribution characteristics; A data transfer module, used to obtain a data partition with data skew, obtain a first data partition, identify hot data in the first data partition, and divide the hot data into a new data partition; The data update module is used to update the data table, identify the data partition storing data in the updated data table, obtain the second data partition, and perform load balancing adjustment on the second data partition.

9. A computer device, characterized in that: The method comprises a memory and a processor, wherein the memory stores computer-readable instructions, and the processor implements the steps of the data table optimization method according to any one of claims 1 to 7 when executing the computer-readable instructions.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-readable instructions, and when the computer-readable instructions are executed by a processor, the steps of the data table optimization method according to any one of claims 1 to 7 are implemented.

Images