Enterprise database data processing method and system

Abstract

The present invention discloses a method and system for processing enterprise database data, which relates to the field of database management technology. The method comprises constructing a column-based storage distributed database architecture based on an LSM tree; performing data sharding operations on the distributed database; applying column storage compression technology to each data shard, selecting a corresponding compression algorithm based on different data column characteristics; constructing a distributed query processor to receive and parse SQL query requests and generate a query request execution plan; executing subquery request tasks in parallel on the data shards according to the query request execution plan and collecting intermediate result sets; merging the intermediate result sets, performing data conflict detection, and generating a final result set; and serializing the final result set and transmitting it to a client, thereby completing enterprise database data processing. By adopting an LSM tree structure and column-based storage, combined with an efficient data compression algorithm, the present invention significantly improves data processing efficiency, enabling the system to process and store large amounts 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 and their businesses rapidly develop, traditional database systems face numerous challenges in processing large amounts of complex data. Existing enterprise database systems often suffer from low data processing efficiency, poor query performance, insufficient system reliability, and limited scalability. These issues severely restrict enterprises' efficient use of data and affect the speed and quality of their decision-making. Furthermore, with the rapid growth in data volumes, traditional database systems are facing immense pressure in terms of storage efficiency and costs. 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:

[0006] In a first aspect, an embodiment of the present invention provides an enterprise database data processing method, which includes constructing a column-based 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 the data shards to construct a data dependency graph; applying column storage compression technology to each data shard, and selecting a corresponding compression algorithm based on different data column characteristics; constructing a distributed query processor to receive and parse SQL query requests from the client and generate a query request execution plan; executing distributed query requests, and executing sub-query request tasks in parallel on related data shards according to the query request execution plan, and collecting intermediate result sets; merging the intermediate result sets, applying a consistency check algorithm to detect data conflicts, and generating a final result set; serializing the final result set and transmitting it to the client to complete the enterprise database data processing.

[0007] As a preferred solution of the enterprise database data processing method described in the present invention, wherein: constructing a columnar storage distributed database architecture based on 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 the LSM tree; establishing a data writing mechanism, writing the business data to be processed into the memory table MemTable, 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; constructing 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 for each data operation; creating a timestamp vector storage structure, recording the latest operation timestamp of each business data item.

[0008] As a preferred solution of the enterprise database data processing method described in the present invention, wherein: setting a hierarchical merging 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 merging trigger mechanism, when the number of SSTable files or the total size reaches a second preset threshold, triggering the merge operation; designing a merge file selection algorithm, selecting files to be merged according to the first principle, the first principle includes: for the L0 layer, selecting all files with overlapping time ranges Perform the merge. For layers L1 to Ln, 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.

[0009] 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 shard 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 shard size and distribution when the node load difference exceeds a third preset threshold; and designing a fault recovery mechanism, which includes data backup and log replay to deal with node failures or network partitions.

[0010] 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 to detect 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 type 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, retaining the value of the latest timestamp; for the second data type, performing field-level merges, retaining the latest version of each field; for conflicts that cannot be resolved automatically, mark them as to be manually processed and record the conflict details; for conflicts to be manually processed, provide a conflict resolution interface, allowing administrators or predefined business logic to manually intervene; 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.

[0011] As a preferred solution of the enterprise database data processing method described in the present invention, the method further comprises: selecting a corresponding compression algorithm according to different data column characteristics, constructing a compression algorithm feature library containing the characteristics, applicable data types and compression performance parameters of various compression algorithms; 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; selecting the best single compression algorithm or compression algorithm combination for each data column based on the prediction results; testing the selected single compression algorithm or compression algorithm combination to verify the actual compression effect; comparing the test results with the prediction results, and if the deviation exceeds a fourth preset threshold, updating the machine learning model and re-executing the prediction process; and recording the final selected single compression algorithm or compression algorithm combination, as well as the corresponding compression algorithm parameters.

[0012] 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 relevant data shard, and arranges the execution order of the sub-query request tasks according to a predefined query priority order; considering the connection strategy and aggregation method, generates a query request execution plan including a sub-query request task sequence, execution order, and data dependency.

[0013] In the 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, assigning a timestamp to each data operation, and generating 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 based on different data column characteristics; a query request module for constructing a distributed query processor, receiving and parsing SQL query requests from the client, and generating a query request execution plan; a query execution module for the 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.

[0014] The beneficial effects of the present invention are as follows: by adopting an LSM tree structure and column storage, combined with an efficient data compression algorithm, the present invention significantly improves data processing efficiency, enabling the system to process and store large amounts of transaction data more quickly. At the same time, with the help of a distributed query processor and a 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 a consistency check algorithm, this solution enhances system reliability, improves fault tolerance and data consistency, and ensures stable operation in the event of network fluctuations or node failures. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] 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 paying any creative work.

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

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

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

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

[0020] In the following description, many specific details are set forth to facilitate a full understanding of the present invention. However, the present invention may also be implemented in other ways different from those described herein. 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.

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

[0022] Example 1

[0023] Reference Figures 1 to 3 , which is the first embodiment of the present invention, provides an enterprise database data processing method, comprising:

[0024] S1: Build a columnar storage distributed database architecture based on the LSM tree, assign a timestamp to each data operation, and generate a timestamp vector.

[0025] Specifically, the following steps are included:

[0026] S1.1: Design the LSM tree structure, including the memory table MemTable and the disk table SSTables.

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

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

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

[0030] S1.4: Set the layered merge strategy and perform background merge on SSTable files.

[0031] Specifically, the following steps are included:

[0032] S1.4.1: Define the hierarchical structure of SSTable files.

[0033] 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.

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

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

[0036] 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.

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

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

[0039] 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 key range overlap in this layer are selected for merging.

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

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

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

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

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

[0045] S1.4.9: Dynamically adjust merge parameters based on system load, query patterns, and hot data distribution to implement an adaptive merge strategy.

[0046] Specifically, when the system CPU utilization is lower 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 will be 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 will be increased by 15% to reduce the merge frequency; for SSTable files containing data that has been accessed more than twice the average number of system accesses in the past 24 hours, the merge trigger threshold of these SSTable files will be reduced by 20%, and the number of SSTable files containing hot data participating in the merge operation will be increased. 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.

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

[0048] 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 its retention time, and reclaim storage space.

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

[0050] Among them, the priority is from high to low as MemTable, the latest generated SSTable to the earliest generated SSTable.

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

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

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

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

[0055] Specifically, the following steps are included:

[0056] S2.1: Define the sharding strategy and determine the sharding method and rules for business data in the column-based storage distributed database architecture.

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

[0058] 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.

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

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

[0061] Specifically, the following steps are included:

[0062] S2.5.1: Maintain a latest timestamp vector for each shard, recording the latest operation timestamp of each business data item in this shard.

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

[0064] 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 non-conflicting. 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.

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

[0066] 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.

[0067] S2.5.4: For conflicts marked for manual resolution, provide a conflict resolution interface that allows manual intervention by administrators or predefined business logic.

[0068] S2.5.5: Establish a conflict resolution priority mechanism and sort multiple pending conflicts based on data importance and business impact.

[0069] Specifically, the priorities are as follows 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 frequently accessed data; conflicts in large-scale data sets; and conflicts in ordinary business data.

[0070] Among them, 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 are resolved first; the amount of data involved, conflicts involving larger amounts of data are resolved first; the complexity of conflict resolution, conflicts with lower complexity are resolved first.

[0071] S2.5.6: Establish a conflict logging system to record in detail all detected conflicts, their resolutions, and final outcomes.

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

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

[0074] 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.

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

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

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

[0078] Specifically, the following steps are included:

[0079] 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.

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

[0081] Specifically, the following steps are included:

[0082] S3.2.1: Build a compression algorithm feature library that includes the characteristics of various compression algorithms, applicable data types, and compression performance parameters.

[0083] The compression performance parameters include compression ratio, compression speed, decompression speed and random access capability.

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

[0085] 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.

[0086] S3.2.3: Collect historical compression data, including data column characteristics, the compression algorithm used, and its actual compression effect, and divide it into training and test data sets according to the preset ratio;

[0087] The data column characteristics include but are not limited to data type, cardinality, distribution characteristics, repetition, and update frequency.

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

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

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

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

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

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

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

[0095] 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.

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

[0097] Specifically, the test dataset is used to evaluate the compression ratio, compression speed, decompression speed and random access performance.

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

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

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

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

[0102] 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.

[0103] S3.5: Create indexes for compressed column data and set up a decompression mechanism.

[0104] 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 machine learning model prediction result.

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

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

[0107] Specifically, the distributed query processor receives the SQL query request sent by the client and uses the SQL parser to convert the query request into an abstract syntax tree; traverses the abstract syntax tree to extract the query parameters involved in the query request, including table name, column name, filter conditions, connection operations, grouping and aggregation functions, and sorting requirements; 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 connection 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.

[0108] 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.

[0109] 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 it is 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; the sub-query request is sent to the corresponding data shard node, and the memory table and disk table are queried on each shard node according to the priority order of the LSM tree structure; the data is decompressed as needed, the 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 shard 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.

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

[0111] 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 sets; perform consistency checks 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 predefined conflict resolution strategies for detected conflicts; generate the final result set, including excluding data items filtered by the consistency check, applying query request restrictions and offset parameters; calculate the data volume indicators of the final result set, and generate query request execution statistics including total execution time, data scanning volume, I / O operation frequency and system resource utilization.

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

[0113] Furthermore, this embodiment also provides an enterprise database data processing system, including 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 based on different data column characteristics; a query request module for constructing a distributed query processor, receiving and parsing SQL query requests from the client, and generating a query request execution plan; a query execution module for the architecture design module for 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; 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

[0114] In summary, the present invention significantly improves data processing efficiency by adopting an LSM tree structure and column storage, combined with an efficient data compression algorithm, enabling the system to process and store large amounts of transaction data more quickly. At the same time, with the help of a distributed query processor and a parallel execution mechanism, the present invention optimizes query performance and significantly improves the response speed of various query operations. Furthermore, by implementing data sharding and asynchronous synchronization strategies, and introducing a consistency check algorithm, this solution enhances system reliability, improves fault tolerance and data consistency, and ensures stable operation despite network fluctuations or node failures.

[0115] Example 2

[0116] Reference Figures 1 to 3 , which is the second embodiment of the present invention, 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.

[0117] Specifically, the experimental environment consisted of five medium-spec servers, each equipped with a 16-core CPU, 64GB of RAM, and 1TB of SSD storage. The servers were interconnected via a 1Gbps network and ran the Ubuntu 20.04 LTS operating system. The experimental data consisted of 5 million transaction records from a small retail chain over a two-year period, totaling approximately 50GB of raw data.

[0118] First, we built a columnar, distributed database architecture based on LSM trees, as described in S1. We then imported all transaction records into the system and assigned a unique timestamp to each record. The MemTable size was set to 256MB, and when the threshold was reached, a flush to the SSTable was triggered.

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

[0120] Then, based on the S3 description, column-level compression techniques are applied to each data shard. A simple heuristic algorithm is used to select the appropriate compression algorithm for each data column. For example, dictionary encoding is used for the product ID column, and delta encoding is used for the price column.

[0121] Finally, following steps S4-S7, we built a distributed query processor and ran a series of query tests. These tests simulated common queries in the daily operations of small retailers, such as daily sales statistics for a single store and monthly best-selling product rankings.

[0122] Experimental results show that this invention achieves significant performance improvements in multiple areas. The data import speed reaches approximately 3,500 transaction records per second, a 75% increase over traditional methods, significantly improving the system's ability to process large amounts of data. The invention also demonstrates excellent storage efficiency, reducing data usage from 50GB to 35GB, saving 30% of storage space. This not only reduces storage costs but also helps improve data access speeds.

[0123] Furthermore, query performance is another highlight of the present invention, with an average improvement of between 40% and 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, enabling companies to obtain valuable business insights more quickly.

[0124] The present invention also performs well in terms of concurrent processing capabilities and stability. During peak periods, the system can handle approximately 350 concurrent query requests, a 75% increase compared to the 200 concurrent requests of traditional methods, which means the system can better cope with the pressure of peak business periods. Under simulated network fluctuations, the present invention can successfully detect and resolve 99.5% of data conflicts, a slight improvement over the 98% of traditional methods, ensuring data consistency and reliability.

[0125] Finally, the present invention demonstrates greater resilience in terms of system availability. In the extreme case of simulating the random shutdown of a single node, the system maintained normal operation, with query performance dropping by only 18%, a significant improvement over the 25% performance drop seen with traditional methods. This demonstrates that the present invention is better able to maintain normal system operation in the face of partial hardware failures, improving overall system reliability.

[0126] It should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention and are not intended to limit the present invention. 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 by: include, Build a column-based storage distributed database architecture based on LSM trees, 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-based compression technology to each data shard and select the appropriate 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 related 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; The data sharding operation includes the following steps: Define sharding strategies and determine the sharding methods and rules for business data in a columnar 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 timestamps 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 shard size and distribution when the node load difference exceeds a third preset threshold; Design a fault recovery mechanism, including data backup and log replay, to cope with node failures or network partitions.

2. The enterprise database data processing method according to claim 1, wherein: The construction of a column storage distributed database architecture based on LSM tree includes: Design an LSM tree structure, which 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 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, wherein: 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 the limits increase exponentially as the level increases; Design a layered merge trigger mechanism to trigger a merge operation when the number or total size of SSTable files reaches a second preset threshold; Design a merge file selection algorithm to select files to be merged based on first principles. The first principle includes: for layer L0, select all files with overlapping time ranges for merging; for layers L1 to Ln, select the N files with the highest key range overlap in this layer for merging; Merge the selected file with the overlapping file 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 layers to perform merge operations simultaneously; Dynamically adjust merging parameters based on system load, query patterns, and hot data distribution to implement adaptive merging strategies; Record storage space usage and query performance changes before and after the merge operation, and continuously optimize the merge strategy; During the merging process, a garbage collection mechanism is implemented 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 1, wherein: 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, a 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 requiring 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 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. The automatic merge strategy includes: for the first data type, retaining the value of the latest timestamp; for the second data type, performing a field-level merge to retain the latest version of each field; for conflicts that cannot be resolved automatically, marking them for manual processing and recording the conflict details; For conflicts that need to be handled manually, a conflict resolution interface is provided, allowing administrators or predefined business logic to manually intervene; Set up a conflict resolution priority mechanism and sort multiple pending conflicts based on data importance and business impact. Set up a conflict logging system to record all detected conflicts, their resolutions, and the final outcomes.

5. The enterprise database data processing method according to claim 1, wherein: The selection 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 the compression effects of different compression algorithms on a given data column; Collect historical compression data, including data column characteristics, the compression algorithm used, and its actual compression effect, and divide it into training data set and test data set according to the preset ratio; Use the training data set to train the machine learning model and optimize the machine learning model parameters; Apply a 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; Conduct tests on the selected single compression algorithm or combination of compression algorithms to verify the actual compression effect; Comparing the test results with the predicted results, and if the deviation exceeds a fourth preset threshold, updating the machine learning model and re-executing the prediction process; Record the final selected single compression algorithm or compression algorithm combination, as well as the corresponding compression algorithm parameters.

6. The enterprise database data processing method according to claim 1, wherein: Generating a query request execution plan includes 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 subquery request task sequence, execution order, and data dependency.

7. An enterprise database data processing system, configured to implement the enterprise database data processing method according to any one of claims 1 to 6, characterized in that: include, 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 distributed databases, perform asynchronous data synchronization between data shards, and build a data dependency graph; The column storage compression module is used to apply column storage compression technology to each data shard and select the corresponding compression algorithm based on 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 sub-query 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