Enterprise database data processing method and system

Abstract

The invention discloses an enterprise database data processing method and system, and relates to the technical field of database management, and the method comprises the steps: constructing a column storage distributed database architecture based on an LSM tree; executing data fragmentation operation on the distributed database; applying a column storage compression technology to each data fragment, and selecting a corresponding compression algorithm according to different data column features; constructing a distributed query processor, receiving and analyzing the SQL query request, and generating a query request execution plan; executing the sub-query request tasks in parallel on the data fragments according to the query request execution plan, and collecting an intermediate result set; combining the intermediate result sets, and performing data conflict detection to generate a final result set; and serializing the final result set, and transmitting the serialized final result set to a client to complete enterprise database data processing. According to the invention, the LSM tree structure and column storage are adopted, and an efficient data compression algorithm is combined, so that the data processing efficiency is greatly improved, and the system can process and store a large amount of transaction data more quickly.

Description

Technical Field

[0001] The present invention relates to the technical field of database management, and in particular to a method and system for processing enterprise database data. Background Art

[0002] As enterprises expand in size and their businesses develop rapidly, traditional database systems face many challenges in processing large amounts of complex data. Existing enterprise database systems often have problems such as low data processing efficiency, poor query performance, insufficient system reliability, and limited scalability. These problems seriously restrict enterprises from using data efficiently and affect the speed and quality of their decision-making. In addition, with the rapid growth of data volume, traditional database systems are also facing tremendous pressure in terms of storage efficiency and cost. Summary of the invention

[0003] In view of the problems existing in the existing enterprise database system, the present invention is proposed.

[0004] Therefore, the problem to be solved by the present invention is how to design an efficient, reliable and scalable enterprise database data processing method and system to overcome the limitations of traditional database systems in processing efficiency, query performance, system reliability and scalability.

[0005] In order to solve the above technical problems, the present invention provides the following technical solutions: In the first aspect, an embodiment of the present invention provides an enterprise database data processing method, which includes building a column storage distributed database architecture based on an LSM tree, assigning a timestamp to each data operation, and generating a timestamp vector; performing data sharding operations on the distributed database, and performing asynchronous data synchronization between data shards to build a data dependency graph; applying column storage compression technology to each data shard, and selecting a corresponding compression algorithm according to different data column characteristics; building a distributed query processor, receiving and parsing SQL query requests from clients, and generating a query request execution plan; executing distributed query requests, executing sub-query request tasks in parallel on related data shards according to the query request execution plan, and collecting intermediate result sets; merging intermediate result sets, applying a consistency check algorithm to perform data conflict detection, and generating a final result set; serializing the final result set and transmitting it to the client to complete the enterprise database data processing.

[0006] As a preferred solution of the enterprise database data processing method described in the present invention, wherein: building a columnar storage distributed database architecture based on an LSM tree includes designing an LSM tree structure, wherein the LSM tree structure includes a memory table MemTable and a disk table SSTables; creating a columnar storage format, organizing business data by column and storing it in an LSM tree; establishing a data writing mechanism, writing the business data to be processed into the memory table MemTable, and when the amount of data in the MemTable reaches a first preset threshold, triggering a data transfer operation, transferring its content to the disk, and generating an SSTable; setting a hierarchical merge strategy, and performing background merging of SSTable files; building a reading mechanism, querying the memory table MemTable and the disk table SSTables in order of priority; creating a global timestamp allocator, automatically assigning a monotonically increasing timestamp to each data operation; creating a timestamp vector storage structure, and recording the latest operation timestamp of each business data item.

[0007] As a preferred solution of the enterprise database data processing method described in the present invention, wherein: setting a hierarchical merge strategy includes the following steps: defining the hierarchical structure of SSTable files, organizing SSTable files into a multi-level structure from L0 to Ln; setting size limits and file quantity limits for each level, and as the level increases, the limit value increases exponentially; designing a hierarchical merge trigger mechanism, when the number of SSTable files or the total size reaches a second preset threshold, triggering a merge operation; designing a merge file selection algorithm, selecting files to be merged according to a first principle, the first principle includes: for the L0 layer, selecting all files with overlapping time ranges Perform the merge. For the L1 to Ln layers, select the N files with the highest key range overlap in this layer for merging; merge the selected files with the overlapping files of the previous layer to generate a new SSTable file; dynamically adjust the number of concurrent merge tasks according to the system load and available resources, allowing multiple layers to merge at the same time; dynamically adjust the merge parameters according to the system load, query mode and hot data distribution to implement an adaptive merge strategy; record the storage space usage and query performance changes before and after the merge operation, and continuously optimize the merge strategy; implement a garbage collection mechanism during the merge process to clean up data that has been marked as deleted and has exceeded the retention time, and reclaim storage space.

[0008] As a preferred solution of the enterprise database data processing method described in the present invention, the execution of data sharding operation includes the following steps: defining a sharding strategy to determine the sharding method and rules of business data in a column-based storage distributed database architecture; implementing business data sharding to distribute business data to multiple physical nodes according to the sharding strategy; establishing a sharding index to use a tree structure to record the sharding location of each business data item, and storing the sharding index information in an LSM tree; establishing an asynchronous synchronization mechanism to regularly transmit changed business data between shards; designing a conflict detection algorithm based on LSM tree timestamps to identify and resolve business data conflicts between shards; setting up a data version control system to track the change history of each business data item; drawing a data dependency graph to record the business data association and dependency between shards; establishing a sharding balancing mechanism to dynamically adjust the sharding size and distribution when the node load difference exceeds a third preset threshold; designing a fault recovery mechanism, which includes data backup and log replay to deal with node failures or network partitions.

[0009] As a preferred solution of the enterprise database data processing method described in the present invention, the design of a conflict detection algorithm based on LSM tree timestamp includes the following steps: maintaining an latest timestamp vector for each shard, recording the latest operation timestamp of each business data item in this shard; in the asynchronous synchronization process, using a vector clock algorithm to compare the timestamp vectors of the source shard and the target shard, and detecting concurrent operations and potential conflicts: when the source shard timestamp is greater than the target shard timestamp, it is marked as an update operation and the target shard data is updated; when the source shard timestamp is less than the target shard timestamp, it is marked as no need to update and the target shard data is retained; when the source shard timestamp is equal to the target shard timestamp, or there are incomparable components in the timestamp vectors of the source shard and the target shard, it is marked as a concurrent operation; for concurrent operations, the operation types and If the operation type and content are completely consistent, it is considered that there is no conflict. If the operation type and content are not completely consistent, it is marked as a potential conflict and enters the conflict resolution process; for the detected potential conflicts, an automatic merge strategy based on predefined rules is adopted, and the automatic merge strategy includes: for the first data type, retain the value of the latest timestamp; for the second data type, perform field-level merges and retain the latest version of each field; for conflicts that cannot be resolved automatically, mark them as to be manually processed and record the details of the conflict; for conflicts to be manually processed, provide a conflict resolution interface to allow administrators or predefined business logic to intervene manually; set a priority mechanism for conflict resolution, and sort multiple conflicts to be resolved according to data importance and business impact; set a conflict log system to record all detected conflicts, resolution methods and final results.

[0010] As a preferred solution of the enterprise database data processing method described in the present invention, wherein: selecting a corresponding compression algorithm according to different data column characteristics includes: constructing a compression algorithm feature library, which includes the characteristics of various compression algorithms, applicable data types and compression performance parameters; designing a machine learning model to predict the compression effects of different compression algorithms on a given data column; collecting historical compression data, including data column characteristics, the compression algorithm used and its actual compression effect, and dividing it into a training data set and a test data set according to a preset ratio; using the training data set to train the machine learning model and optimize the machine learning model parameters; applying the trained machine learning model to each data column to predict the compression effects of various compression algorithms; based on the prediction results, selecting the best single compression algorithm or compression algorithm combination for each data column; testing the selected single compression algorithm or compression algorithm combination to verify the actual compression effect; comparing the test results with the prediction results, if the deviation exceeds the fourth preset threshold, updating the machine learning model and re-executing the prediction process; recording the final selected single compression algorithm or compression algorithm combination, and the corresponding compression algorithm parameters.

[0011] As a preferred solution of the enterprise database data processing method described in the present invention, generating a query request execution plan includes the following steps: a distributed query processor receives an SQL query request sent by a client, and uses an SQL parser to convert the query request into an abstract syntax tree; traverses the abstract syntax tree and extracts query parameters involved in the query request; determines the data shards involved in the query request based on the shard index; creates a sub-query request task for each related data shard, and arranges the execution order of the sub-query request tasks according to a predefined query priority order; considers the connection strategy and aggregation method, and generates a query request execution plan including a sub-query request task sequence, execution order, and data dependency.

[0012] In a second aspect, an embodiment of the present invention provides an enterprise database data processing system, which includes an architecture design module for constructing a column-based storage distributed database architecture based on an LSM tree, and assigning a timestamp to each data operation to generate a timestamp vector; a data sharding module for performing data sharding operations on a distributed database, and performing asynchronous data synchronization between data shards to construct a data dependency graph; a column storage compression module for applying column storage compression technology to each data shard, and selecting a corresponding compression algorithm according to different data column characteristics; a query request module for constructing a distributed query processor, receiving and parsing SQL query requests from a client, and generating a query request execution plan; a query execution module for an architecture design module for executing distributed query requests, executing sub-query request tasks in parallel on relevant data shards according to the query request execution plan, and collecting intermediate result sets; a result merging module for merging intermediate result sets, applying a consistency check algorithm for data conflict detection, and generating a final result set; a transmission module for serializing the final result set and transmitting it to the client to complete enterprise database data processing.

[0013] The beneficial effects of the present invention are as follows: the present invention greatly improves data processing efficiency by adopting LSM tree structure and column storage, combined with efficient data compression algorithm, so that the system can process and store large amounts of transaction data faster. At the same time, with the help of distributed query processor and parallel execution mechanism, the present invention optimizes query performance and significantly improves the response speed of various query operations. In addition, by implementing data sharding and asynchronous synchronization strategies, and introducing consistency check algorithms, this solution enhances the reliability of the system, improves fault tolerance and data consistency, and ensures stable operation in the event of network fluctuations or node failures. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0015] Figure 1 The framework flow chart of the enterprise database data processing method.

[0016] Figure 2 Design a flow chart for the column storage distributed database architecture of the enterprise database data processing method.

[0017] Figure 3 Design a flow chart for the conflict detection algorithm of enterprise database data processing method. DETAILED DESCRIPTION

[0018] In order to make the above-mentioned objects, features and advantages of the present invention more obvious and easy to understand, the specific implementation methods of the present invention are described in detail below in conjunction with the accompanying drawings.

[0019] In the following description, many specific details are set forth to facilitate a full understanding of the present invention, but the present invention may also be implemented in other ways different from those described herein, and those skilled in the art may make similar generalizations without violating the connotation of the present invention. Therefore, the present invention is not limited to the specific embodiments disclosed below.

[0020] Secondly, the term "one embodiment" or "embodiment" as used herein refers to a specific feature, structure, or characteristic that may be included in at least one implementation of the present invention. The term "in one embodiment" that appears in different places in this specification does not necessarily refer to the same embodiment, nor does it refer to a separate or selective embodiment that is mutually exclusive with other embodiments.

[0021] Example 1 Reference Figure 1~Figure 3 , which is the first embodiment of the present invention, and provides a method for processing data in an enterprise database, comprising: S1: Build a column-based storage distributed database architecture based on LSM tree, assign a timestamp to each data operation, and generate a timestamp vector.

[0022] Specifically, the following steps are included: S1.1: Design the LSM tree structure, including the memory table MemTable and the disk table SSTables.

[0023] S1.2: Create a columnar storage format to organize business data by columns and store them in an LSM tree.

[0024] S1.3: Establish a data writing mechanism to write the business data to be processed into the memory table MemTable.

[0025] It should be noted that when the amount of data in MemTable reaches the first preset threshold, the data transfer operation is triggered, and its content is transferred to the disk to generate SSTable; the first preset threshold refers to the maximum data capacity of MemTable, which is set according to the system memory size and performance requirements.

[0026] S1.4: Set the layered merge strategy to perform background merges on SSTable files.

[0027] Specifically, the following steps are included: S1.4.1: Define the hierarchical structure of SSTable files.

[0028] Specifically, the SSTable file is organized into a multi-level structure from L0 to Ln, where L0 is the latest written data and Ln is the level with the most historical data accumulated.

[0029] S1.4.2: Set size limits and file count limits for each tier, with limits increasing exponentially with each tier.

[0030] For example, L1 is limited to 10GB in size, L2 to 100GB, L3 to 1TB, and so on.

[0031] S1.4.3: Design a layered merge trigger mechanism to trigger a merge operation when the number or total size of SSTable files in a certain layer reaches the second preset threshold.

[0032] The second preset threshold is set to 75% of the level size limit or 90% of the number of files, whichever is reached first.

[0033] S1.4.4: Design a merge file selection algorithm to select files to be merged based on first principles.

[0034] Specifically, the first principle means that for the L0 layer, all files with overlapping time ranges are selected for merging, and for the L1 to Ln layers, the N files with the highest degree of overlap in key ranges in this layer are selected for merging.

[0035] S1.4.5: Merge the selected file with the overlapping files in the previous layer to generate a new SSTable file.

[0036] It should be noted that during the merging process, possible data version conflicts are resolved and the latest version of the data is retained.

[0037] S1.4.6: Dynamically adjust the number of concurrent merge tasks based on system load and available resources, allowing multiple levels to perform merge operations simultaneously.

[0038] S1.4.7: Use the copy-on-write technology to create snapshots for the merged files during the merge process to achieve read-write separation during the merge process.

[0039] S1.4.8: Design a checkpoint mechanism for merge operations, regularly record merge progress, and support breakpoint recovery.

[0040] S1.4.9: Dynamically adjust merge parameters according to system load, query pattern, and hot data distribution to implement an adaptive merge strategy.

[0041] Specifically, when the system CPU utilization is less than 30% and the I / O waiting time is less than 5%, the system load is considered to be low. At this time, the merge trigger threshold is temporarily reduced by 10% to increase the merge frequency; when the system CPU utilization exceeds 70% or the I / O waiting time exceeds 20%, the system is considered to be at its peak. At this time, the merge trigger threshold is increased by 15% to reduce the merge frequency; for SSTable files containing data that has been accessed more than twice the system average number of times in the past 24 hours, the merge trigger threshold of these SSTable files is reduced by 20%, and the SSTable files containing hot data are increased to participate in the merge operation. probability; calculate the data access frequency every 6 hours, update the hot data list and the corresponding SSTable file priority; set a merge frequency adjustment factor λ (initial value is 1.0, λ value range is 0.5 to 1.5) to dynamically adjust the merge trigger conditions of SSTable files, and automatically adjust the value of λ every 24 hours according to system performance indicators (such as query latency, storage space utilization) (the larger the λ value, the more frequent the merge operations, and the smaller the λ value, the fewer the merge operations). The actual merge trigger threshold is calculated by multiplying the default threshold by λ; monitor system resource usage in real time, and dynamically adjust the concurrency of merge operations according to resource utilization.

[0042] S1.4.10: Record storage space usage and query performance changes before and after the merge operation to continuously optimize the merge strategy.

[0043] S1.4.11: Implement a garbage collection mechanism during the merge process to clean up data that has been marked for deletion and has exceeded the retention time, and reclaim storage space.

[0044] S1.5: Build a reading mechanism to query the memory table MemTable and the disk table SSTables in order of priority.

[0045] Among them, the priorities are from high to low: MemTable, the latest generated SSTable to the earliest generated SSTable.

[0046] S1.6: Create a global timestamp assigner to automatically assign a monotonically increasing timestamp to each data operation.

[0047] It should be noted that data operations include writing, updating and deleting.

[0048] S1.7: Create a timestamp vector storage structure to record the latest operation timestamp of each business data item.

[0049] S2: Perform data sharding operations on the distributed database, perform asynchronous data synchronization between data shards, and build a data dependency graph.

[0050] Specifically, the following steps are included: S2.1: Define the sharding strategy and determine the sharding method and rules for business data in the column-based storage distributed database architecture.

[0051] S2.2: Implement business data sharding and distribute business data to multiple physical nodes according to the defined sharding strategy.

[0052] S2.3: Create a shard index, use a tree structure to record the shard location of each business data item, and store the shard index information in the LSM tree.

[0053] S2.4: Establish an asynchronous synchronization mechanism to regularly transmit changed business data between shards.

[0054] S2.5: Design a conflict detection algorithm based on LSM tree timestamp to identify and resolve data conflicts between shards.

[0055] Specifically, the following steps are included: S2.5.1: Maintain a latest timestamp vector for each shard, recording the latest operation timestamp of each business data item in this shard.

[0056] S2.5.2: During the asynchronous synchronization process, the vector clock algorithm is used to compare the timestamp vectors of the source shard and the target shard to detect concurrent operations and potential conflicts.

[0057] Specifically, when the source shard timestamp is greater than the target shard timestamp, it is marked as an update operation and the target shard data is updated; when the source shard timestamp is less than the target shard timestamp, it is marked as no need to update and the target shard data is retained; when the source shard timestamp is equal to the target shard timestamp, or there are incomparable components in the timestamp vectors of the source shard and the target shard, it is marked as a concurrent operation; for marked concurrent operations, the operation type and content of the source shard and the target shard are compared. If the operation type and content are exactly the same, it is considered to be conflict-free. If the operation type and content are not exactly the same, it is marked as a potential conflict and the conflict resolution process of S2.5.3 is entered.

[0058] S2.5.3: For detected potential conflicts, use an automatic merge strategy based on predefined rules.

[0059] Specifically, for simple (first) data types (such as integers and strings), the value of the latest timestamp is retained; for complex (second) data types (such as JSON objects), field-level merges are performed to retain the latest version of each field; for conflicts that cannot be resolved automatically, they are marked for manual processing and the conflict details are recorded.

[0060] S2.5.4: For conflicts marked for manual resolution, provide a conflict resolution interface to allow administrators or predefined business logic to manually intervene.

[0061] S2.5.5: Set up a conflict resolution priority mechanism and sort multiple conflicts to be resolved based on data importance and business impact.

[0062] Specifically, the priorities are from high to low: data conflicts that affect the core functions of the system; data conflicts involving user sensitive information; data conflicts that affect key business processes; conflicts in high-frequency access data; conflicts in large-scale data sets; and conflicts in ordinary business data.

[0063] When multiple conflicts have the same priority, they are further sorted according to the following factors: the time when the conflict occurred, conflicts that occurred earlier will be resolved first; the amount of data involved, conflicts involving larger amounts of data will be resolved first; the complexity of conflict resolution, conflicts with lower complexity will be resolved first.

[0064] S2.5.6: Set up a conflict log system to record in detail all detected conflicts, their resolutions, and final outcomes.

[0065] S2.6: Set up a data version control system to track the change history of each business data item.

[0066] S2.7: Draw a data dependency diagram to record the business data associations and dependencies between shards.

[0067] S2.8: Establish a shard balancing mechanism to dynamically adjust the shard size and distribution when the node load difference exceeds a third preset threshold.

[0068] It should be noted that the node load difference refers to the degree of imbalance in the amount of data or request processed by each node, and the third preset threshold refers to the maximum allowable load difference percentage.

[0069] S2.9: Design failure recovery mechanisms, including data backup and log replay, to cope with node failures or network partitions.

[0070] S3: Apply column storage compression technology to each data shard and select the corresponding compression algorithm based on the characteristics of different data columns.

[0071] Specifically, the following steps are included: S3.1: Perform sampling analysis on the columns in each data shard to identify the data type, distribution characteristics, and update frequency of each column.

[0072] S3.2: Based on the sampling analysis results, use the machine learning model to dynamically select and combine compression algorithms.

[0073] Specifically, the following steps are included: S3.2.1: Build a compression algorithm feature library that includes the characteristics of various compression algorithms, applicable data types, and compression performance parameters.

[0074] Among them, compression performance parameters include compression ratio, compression speed, decompression speed and random access capability.

[0075] S3.2.2: Design a machine learning model to predict the compression effect of different compression algorithms on a given data column.

[0076] Specifically, a deep neural network model is used, where the input layer receives data column features and the output layer predicts the performance indicators of each compression algorithm.

[0077] S3.2.3: Collect historical compression data, including data column characteristics, compression algorithms used and their actual compression effects, and divide them into training data sets and test data sets according to preset ratios; The data column characteristics include, but are not limited to, data type, cardinality, distribution characteristics, repetition, and update frequency.

[0078] S3.2.4: Train the machine learning model using the training dataset and optimize the model parameters.

[0079] Specifically, the cross-validation method is used to evaluate the model performance, and the gradient descent algorithm is used to optimize the model parameters.

[0080] S3.2.5: Apply the trained machine learning model to each data column to predict the compression effect of various compression algorithms.

[0081] Specifically, the data column features are input into the model to obtain the prediction performance indicators of each compression algorithm.

[0082] S3.2.6: Based on the prediction results, select the best single compression algorithm or combination of compression algorithms for each data column.

[0083] Specifically, the comprehensive score of each compression scheme is calculated, and the scheme with the highest score is selected.

[0084] S3.2.7: Design a compression algorithm combination strategy, considering both multi-level compression and parallel compression.

[0085] Among them, multi-level compression refers to the serial application of multiple compression algorithms, and parallel compression refers to dividing data into blocks and then applying different compression algorithms in parallel.

[0086] S3.2.8: For a selected single compression algorithm or combination of compression algorithms, perform tests to verify the actual compression effect.

[0087] Specifically, the compression ratio, compression speed, decompression speed and random access performance are evaluated using a test dataset.

[0088] S3.2.9: Compare the test results with the predicted results. If the deviation exceeds the fourth preset threshold, update the machine learning model and re-execute the prediction process.

[0089] The fourth preset threshold refers to a deviation threshold, which is determined according to system performance requirements and an acceptable error range.

[0090] S3.2.10: Record the final selected single compression algorithm or compression algorithm combination, and the corresponding compression algorithm parameters, for subsequent compression operations.

[0091] S3.3: Apply the selected compression algorithm to each column block for compression.

[0092] S3.4: Generate compression metadata, record the compression algorithm used, compression algorithm parameters, data size before and after compression, and performance indicators predicted by the machine learning model, and store the compression metadata in the LSM tree structure.

[0093] S3.5: Create indexes and set up decompression mechanisms for compressed column data.

[0094] S3.6: Record the result data of the compression operation, including the actual compression ratio, compression time, decompression time, query response time before and after compression, and deviation from the prediction result of the machine learning model.

[0095] S3.7: Store the compression algorithm statistics including the compression algorithm type, compression algorithm parameters and result data in the system table.

[0096] S4: Build a distributed query processor to receive and parse SQL query requests and generate a query request execution plan.

[0097] Specifically, the distributed query processor receives the SQL query request sent by the client, and converts the query request into an abstract syntax tree by using the SQL parser; traverses the abstract syntax tree, extracts the query parameters involved in the query request, including table name, column name, filter condition, join operation, grouping and aggregation function, and sorting requirement; determines the data shards involved in the query request based on the pre-established shard index; creates a subquery request task for each relevant data shard, and arranges the execution order of the subquery request tasks according to the predefined query priority order; considers the join strategy and aggregation method, and generates a query request execution plan including the subquery request task sequence, execution order, and data dependency; creates query request metadata including the query request ID, type, list of involved shards, estimated result set size, and query request priority.

[0098] S5: Execute distributed query requests, execute subquery request tasks in parallel on related data shards according to the query request execution plan, and collect intermediate result sets.

[0099] Specifically, the query request coordinator is initialized according to the query request execution plan; a unique identifier is assigned to each sub-query request task and added to the task queue; the sub-query request tasks are executed in parallel according to the execution order in the query request execution plan; sub-query requests are sent to the corresponding data sharding nodes, and the memory table and disk table are queried on each sharding node in the order of priority of the LSM tree structure; data is decompressed as needed, filtering conditions are applied, local aggregation is performed, and the result data version is marked with a timestamp vector; the intermediate result set returned by each data sharding node is received and added to the result set container; the query request execution progress is monitored, the task execution time is recorded, the overall progress is calculated, and when the query request execution time exceeds the preset execution time threshold, a progress update is sent to the client.

[0100] S6: Merge the intermediate result sets, apply the consistency check algorithm to detect data conflicts, and generate the final result set.

[0101] Specifically, initialize the global result set processor; merge the intermediate result sets based on the data dependencies in the query request execution plan; implement the distributed connection algorithm, global aggregation operation and sorting on the merged intermediate result set; perform consistency check by traversing the merged result set and querying the data dependency graph; use the conflict detection algorithm to compare the timestamps of related data, and apply the predefined conflict resolution strategy for the detected conflicts; generate the final result set, including eliminating the data items filtered by the consistency check, applying the query request restrictions and offset parameters; calculate the data volume index of the final result set, and generate query request execution statistics including the total execution time, data scanning volume, I / O operation frequency and system resource utilization.

[0102] S7: The final result set is serialized and transmitted to the client to complete the enterprise database data processing.

[0103] Furthermore, the present embodiment also provides an enterprise database data processing system, including an architecture design module, which is used to build a column storage distributed database architecture based on LSM tree, and assign a timestamp to each data operation to generate a timestamp vector; a data sharding module, which is used to perform data sharding operations on the distributed database, and perform asynchronous data synchronization between data shards to build a data dependency graph; a column storage compression module, which is used to apply column storage compression technology to each data shard, and select a corresponding compression algorithm according to different data column characteristics; a query request module, which is used to build a distributed query processor, receive and parse SQL query requests from the client, and generate a query request execution plan; a query execution module, which is used for the architecture design module, which is used to execute distributed query requests, execute sub-query request tasks in parallel on related data shards according to the query request execution plan, and collect intermediate result sets; a result merging module, which is used to merge intermediate result sets, apply a consistency check algorithm to detect data conflicts, and generate a final result set; a transmission module, which is used to serialize the final result set and transmit it to the client to complete the enterprise database data processing In summary, the present invention greatly improves data processing efficiency by adopting LSM tree structure and column storage, combined with efficient data compression algorithm, so that the system can process and store large amounts of transaction data faster. At the same time, with the help of distributed query processor and parallel execution mechanism, the present invention optimizes query performance and significantly improves the response speed of various query operations. In addition, by implementing data sharding and asynchronous synchronization strategies, and introducing consistency check algorithms, this solution enhances the reliability of the system, improves fault tolerance and data consistency, and ensures stable operation in the event of network fluctuations or node failures.

[0104] Example 2 Reference Figure 1~Figure 3 , which is the second embodiment of the present invention, and this embodiment provides an enterprise database data processing method. In order to verify the beneficial effects of the present invention, scientific demonstration is carried out through economic benefit calculation and simulation experiments.

[0105] Specifically, the experimental environment consists of 5 medium-configuration servers, each with a 16-core CPU, 64GB RAM, and 1TB SSD storage. The servers are interconnected via a 1Gbps network and run the Ubuntu 20.04 LTS operating system. The experimental data contains 5 million transaction records of a small retail chain in 2 years, with an original data volume of about 50GB.

[0106] First, build a column storage distributed database architecture based on LSM tree according to the description of S1. Then, import all transaction records into the system and assign a unique timestamp to each record. The MemTable size is set to 256MB, and when the threshold is reached, the flush to SSTable is triggered.

[0107] Next, data sharding is performed according to the S2 method. A range sharding strategy based on transaction dates is adopted to evenly distribute data to 5 nodes. At the same time, an asynchronous data synchronization mechanism is implemented every 5 minutes.

[0108] Then, according to the description of S3, column storage compression technology is applied to each data shard. Through a simple heuristic algorithm, appropriate compression algorithms are selected for different data columns. For example, dictionary encoding is used for the product ID column and Delta encoding is used for the price column.

[0109] Finally, follow steps S4-S7 to build a distributed query processor and conduct a series of query tests. These tests simulate common queries in the daily operations of small retail enterprises, such as daily sales statistics of a single store and monthly best-selling product rankings.

[0110] The experimental results show that the present invention has achieved significant performance improvements in many aspects. The data import speed reaches about 3,500 transaction records per second, which is 75% higher than the traditional method, which greatly improves the system's ability to process large amounts of data. In terms of storage efficiency, the present invention also performs well, reducing the space occupied by data from 50GB to 35GB, saving 30% of storage space, which not only reduces storage costs, but also helps to improve data reading speed.

[0111] Furthermore, query performance is another highlight of the present invention, with an average improvement of 40% to 60%. Specifically, the query time for daily sales statistics of a single store is shortened from 3 seconds to 1.2 seconds; the statistical time for the monthly Top 50 best-selling products is reduced from 45 seconds to 22 seconds; the query time for a specific customer's one-year purchase history is reduced from 12 seconds to 6 seconds; and the processing time for quarterly sales comparisons of 10 stores is shortened from 60 seconds to 35 seconds. These performance improvements significantly improve the efficiency of data analysis, allowing companies to obtain valuable business insights more quickly.

[0112] The invention also performs well in terms of concurrent processing capability and stability of the system. The system can process about 350 concurrent query requests at the same time during peak hours, which is 75% higher than the 200 of the traditional method, which means that the system can better cope with the pressure of business peak hours. In the case of simulated network fluctuations, the invention can successfully detect and resolve 99.5% of data conflicts, which is slightly higher than the 98% of the traditional method, ensuring the consistency and reliability of the data.

[0113] Finally, in terms of system availability, the present invention also demonstrates stronger resilience. In the extreme case of simulating the random shutdown of one node, the system can still maintain normal operation, and the query performance only drops by 18%, which is a significant improvement over the 25% performance drop of the traditional method. This shows that the present invention can better maintain the normal operation of the system in the face of partial hardware failures, thereby improving the overall reliability of the system.

[0114] It should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention rather than to limit it. Although the present invention has been described in detail with reference to the preferred embodiments, those skilled in the art should understand that the technical solutions of the present invention may be modified or replaced by equivalents without departing from the spirit and scope of the technical solutions of the present invention, which should all be included in the scope of the claims of the present invention.

Claims

1. A method for processing enterprise database data, characterized in that: include, Build a column-based storage distributed database architecture based on LSM tree, assign a timestamp to each data operation, and generate a timestamp vector; Perform data sharding operations on distributed databases, perform asynchronous data synchronization between data shards, and build a data dependency graph; Apply column storage compression technology to each data shard and select the corresponding compression algorithm based on the characteristics of different data columns; Build a distributed query processor to receive and parse SQL query requests from clients and generate query request execution plans; Execute distributed query requests, execute subquery request tasks in parallel on relevant data shards according to the query request execution plan, and collect intermediate result sets; Merge the intermediate result sets, apply the consistency check algorithm to detect data conflicts, and generate the final result set; The final result set is serialized and transmitted to the client to complete the enterprise database data processing.

2. The enterprise database data processing method according to claim 1, characterized in that: The construction of a column storage distributed database architecture based on an LSM tree includes: Design an LSM tree structure, wherein the LSM tree structure includes a memory table MemTable and a disk table SSTables; Create a columnar storage format to organize business data by columns and store them in an LSM tree; Establish a data writing mechanism to write the business data to be processed into the memory table MemTable. When the amount of data in MemTable reaches the first preset threshold, trigger the data transfer operation to transfer its content to the disk and generate SSTable. Set the layered merge strategy to merge SSTable files in the background; Build a read mechanism to query the memory table MemTable and disk table SSTables in order of priority; Create a global timestamp assigner to automatically assign a monotonically increasing timestamp to each data operation; Create a timestamp vector storage structure to record the latest operation timestamp of each business data item.

3. The enterprise database data processing method according to claim 2, characterized in that: The step of setting the layered merging strategy includes the following steps: Define the hierarchical structure of SSTable files and organize SSTable files into a multi-level structure from L0 to Ln; Set size limits and file quantity limits for each level, and increase the limits exponentially as the levels increase; Design a layered merge trigger mechanism to trigger a merge operation when the number or total size of SSTable files reaches the second preset threshold; Design a merge file selection algorithm to select files to be merged based on the first principle, which includes: for the L0 layer, select all files with overlapping time ranges for merging; for the L1 to Ln layers, select the N files with the highest key range overlap in this layer for merging; Merge the selected file with the overlapping files in the previous layer to generate a new SSTable file; Dynamically adjust the number of concurrent merge tasks based on system load and available resources, allowing multiple levels to perform merge operations simultaneously; Dynamically adjust merging parameters according to system load, query mode and hot data distribution to implement adaptive merging strategy; Record storage space usage and query performance changes before and after the merge operation, and continuously optimize the merge strategy; A garbage collection mechanism is implemented during the merge process to clean up data that has been marked for deletion and has exceeded the retention time, thereby reclaiming storage space.

4. The enterprise database data processing method according to claim 3, characterized in that: The data sharding operation comprises the following steps: Define the sharding strategy and determine the sharding method and rules for business data in the column-based storage distributed database architecture; Implement business data sharding and distribute business data to multiple physical nodes according to the sharding strategy; Create a shard index, use a tree structure to record the shard location of each business data item, and store the shard index information in the LSM tree; Establish an asynchronous synchronization mechanism to regularly transmit changed business data between shards; Design a conflict detection algorithm based on LSM tree timestamp to identify and resolve business data conflicts between shards; Set up a data version control system to track the change history of each business data item; Draw a data dependency diagram to record the business data associations and dependencies between shards; Establish a shard balancing mechanism to dynamically adjust the shard size and distribution when the node load difference exceeds a third preset threshold; Design a fault recovery mechanism, which includes data backup and log replay, to cope with node failures or network partitions.

5. The enterprise database data processing method according to claim 4, characterized in that: The design of the conflict detection algorithm based on LSM tree timestamp includes the following steps: Maintain a latest timestamp vector for each shard, recording the latest operation timestamp of each business data item in this shard; During the asynchronous synchronization process, the vector clock algorithm is used to compare the timestamp vectors of the source and target shards to detect concurrent operations and potential conflicts: When the timestamp of the source shard is greater than the timestamp of the target shard, it is marked as an update operation and the target shard data is updated; When the timestamp of the source shard is less than the timestamp of the target shard, it is marked as not needing to be updated and the target shard data is retained; When the source shard timestamp is equal to the target shard timestamp, or there are incomparable components in the timestamp vectors of the source and target shards, it is marked as a concurrent operation; For concurrent operations, compare the operation type and content of the source and target shards. If the operation type and content are completely consistent, it is considered that there is no conflict. If the operation type and content are not completely consistent, it is marked as a potential conflict and enters the conflict resolution process; For detected potential conflicts, an automatic merge strategy based on predefined rules is adopted, and the automatic merge strategy includes: for the first data type, retaining the value of the latest timestamp; for the second data type, performing field-level merge and retaining the latest version of each field; for conflicts that cannot be automatically resolved, marking them for manual processing and recording conflict details; For conflicts that need to be handled manually, a conflict resolution interface is provided to allow administrators or predefined business logic to manually intervene; Set up a conflict resolution priority mechanism and sort multiple pending conflicts according to data importance and business impact; Set up a conflict logging system to record all conflicts detected, how they were resolved, and the final outcome.

6. The enterprise database data processing method according to claim 1, characterized in that: The selecting of corresponding compression algorithms according to different data column characteristics includes: Build a compression algorithm feature library, which includes the characteristics of various compression algorithms, applicable data types, and compression performance parameters; Design machine learning models to predict how well different compression algorithms will compress a given data column; Collect historical compression data, including data column features, compression algorithms used and their actual compression effects, and divide them into training data sets and test data sets according to preset ratios; Use the training data set to train the machine learning model and optimize the machine learning model parameters; Apply the trained machine learning model to each data column to predict the compression effect of various compression algorithms; Based on the prediction results, the best single compression algorithm or combination of compression algorithms is selected for each data column; For the selected single compression algorithm or compression algorithm combination, test and verify the actual compression effect; Compare the test result with the prediction result, and if the deviation exceeds a fourth preset threshold, update the machine learning model and re-execute the prediction process; Record the final selected single compression algorithm or compression algorithm combination, and the corresponding compression algorithm parameters.

7. The enterprise database data processing method according to claim 1, characterized in that: Generating a query request execution plan comprises the following steps: The distributed query processor receives the SQL query request sent by the client and converts the query request into an abstract syntax tree using the SQL parser; Traverse the abstract syntax tree and extract the query parameters involved in the query request; Determine the data shards involved in the query request based on the shard index; Create a subquery request task for each relevant data shard, and arrange the execution order of the subquery request tasks according to the predefined query priority order; Considering the connection strategy and aggregation method, a query request execution plan is generated that includes the sub-query request task sequence, execution order, and data dependency.

8. An enterprise database data processing system, based on the enterprise database data processing method according to any one of claims 1 to 7, characterized in that: Also includes, The architecture design module is used to build a column-based storage distributed database architecture based on the LSM tree, assign a timestamp to each data operation, and generate a timestamp vector; The data sharding module is used to perform data sharding operations on the distributed database, perform asynchronous data synchronization between data shards, and build a data dependency graph; The column storage compression module is used to apply the column storage compression technology to each data shard and select the corresponding compression algorithm according to the characteristics of different data columns; The query request module is used to build a distributed query processor, receive and parse SQL query requests from clients, and generate query request execution plans; The query execution module is used in the architecture design module to execute distributed query requests, execute subquery request tasks in parallel on related data shards according to the query request execution plan, and collect intermediate result sets; The result merging module is used to merge the intermediate result sets, apply the consistency check algorithm to detect data conflicts, and generate the final result set; The transmission module is used to serialize the final result set and transmit it to the client to complete the enterprise database data processing.

Images