Sharded storage and query optimization method and system for high-concurrency database

Abstract

The present invention belongs to the field of query optimization, and in particular relates to a sharded storage and query optimization method and system for a high-concurrency database. The method comprises: analyzing business characteristics through a preset decision tree model to select the optimal sharding key, combining hash, range, and list sharding rules and shard split-merge strategies to build a sharding rule engine and initialize the database; using the ANTLR4 parser to generate a query abstract syntax tree, detecting whether a preset Cube is hit to directly return the result; if no hit is found, dynamically routing to the target shard list based on the sharding key field, load balancing, and failover strategies, accelerating data retrieval in combination with high-frequency indexes, and merging shard-level results through the T-TopK algorithm; the present invention realizes adaptive matching of sharding strategies and business needs, push-down of query calculations, and result optimization, thereby ensuring low latency and high availability in high-concurrency scenarios.

Description

Technical Field

[0001] The present invention belongs to the field of query optimization, and in particular relates to a sharded storage and query optimization method and system for a high-concurrency database. Background Art

[0002] With the rapid growth of industries such as e-commerce and logistics, settlement systems need to cope with the high-concurrency settlement document processing needs of more than 100,000 per day, especially during promotional seasons or when regional business surges. Traditional single-database architectures face severe challenges: at the storage level, the amount of data in a single table exceeds 500GB, resulting in a sharp increase in index depth, and write performance is difficult to support instantaneous peaks; at the query level, cross-shard association operations require full table scans, and response time fluctuates significantly, which cannot meet real-time requirements; at the scalability level, fixed sharding strategies lack dynamic load balancing capabilities. When regional business suddenly increases, the shard load imbalance rate increases, exacerbating performance bottlenecks. In addition, the industry-specific multi-dimensional field storage and flexible rule adaptation requirements further expose the limitations of traditional relational databases. It is difficult to balance storage flexibility and query efficiency, and rule changes often require downtime to modify table structures, affecting business continuity. Although existing technologies alleviate some of the pressure by sharding libraries and tables, they still cannot meet the high-concurrency, high-real-time, and high-elasticity settlement scenario requirements in terms of sharding logic, cross-shard querying, and scalability. To this end, the present invention provides a sharding storage and query optimization method and system for high-concurrency databases. Summary of the Invention

[0003] In response to the deficiencies of the prior art, the present invention proposes a sharding storage and query optimization method and system for high-concurrency databases. The method includes: analyzing business characteristics through a preset decision tree model to select the optimal sharding key, combining hash, range, list sharding rules and shard split-merge strategies to build a sharding rule engine and initialize the database; using the ANTLR4 parser to generate a query abstract syntax tree, detecting whether it hits the preset Cube to directly return the result; if not, dynamically routing to the target sharding list based on the sharding key field, load balancing and failover strategy, combining high-frequency indexes to accelerate data retrieval, and merging shard-level results through the T-TopK algorithm. The present invention realizes the adaptive matching of sharding strategy and business needs, the calculation push-down and result optimization of the query process, and ensures low latency and high availability in high-concurrency scenarios.

[0004] To achieve the above object, the present invention provides the following technical solutions:

[0005] Sharded storage and query optimization methods for high-concurrency databases, including:

[0006] Initialize the distributed database based on the preset decision tree model and sharding rule engine, and generate the query abstract syntax tree based on user needs and in combination with the ANTLR4 parser;

[0007] The sharding rule engine is constructed by combining the optimal sharding key selected by the decision tree model, sharding rules with business requirement priority tags, and shard split-merge strategies. Sharding rules include hash sharding, range sharding, or list sharding.

[0008] The shard split-merge strategy adjusts shard splits and merges during the query process based on the data volume of each shard, the number of queries per second, and the corresponding shard load threshold.

[0009] Determine whether the query matches the preset Cube based on the query abstract syntax tree. If so, directly return the merged query results.

[0010] If no hit is found, the sharding rules preset in the sharding rule engine are called to combine the query abstract syntax tree, sharding key fields, and the preset load balancing strategy and failover strategy to obtain the target sharding list;

[0011] The query merging strategy is constructed based on the target shard list, the preset high-frequency index list, and the T-TopK algorithm to obtain the merged query results.

[0012] Specifically, the construction and training process of the decision tree model includes:

[0013] Obtain the business request log field and sharding performance indicator field corresponding to the historical optimal sharding key, and use the business request log field and sharding performance indicator field in combination with the query type field through association rules to obtain the hierarchical query type priority mapping and sharding performance constraint set corresponding to each optimal sharding key;

[0014] The business request log fields include the request unique identifier, business classification code, price type value, query type, and request timestamp; query types include point query, range query, and list query;

[0015] The shard performance indicator fields include the number of queries per second, average response time, shard data volume, and CPU or memory utilization;

[0016] The hierarchical query type priority mapping, shard performance constraint set, and shard split-merge strategy are input into the decision tree model constructed by the decision tree algorithm. The model is then trained using the target loss function constructed using QPS, query latency, shard data Gini coefficient, and the probability of conflict between multiple shards under the optimal shard key during the query process to obtain a trained decision tree model.

[0017] Specifically, the construction process of the hierarchical query type priority mapping includes:

[0018] Based on the frequency of query access using each query type for each business scenario in each historical quarter, a first mapping layer is constructed between each business scenario and the query type.

[0019] Based on the business request log fields and sharding performance indicator fields corresponding to the historical optimal sharding key for each quarter, the preset business analysis and evaluation model is used to obtain the query type and corresponding query type priority in each business scenario, and determine the multi-sharding rules and sharding rule priority under the optimal sharding key for each query type in the business scenario;

[0020] Based on the query type and corresponding query type priority in each business scenario and the sharding rules under each optimal sharding key, a forward mapping layer is constructed through association rules, which allows each query type with the same priority in each business scenario to face different sharding rules at the same time.

[0021] Similarly, by determining the multi-sharding rules and sharding rule priorities under the optimal sharding key for each query type in the business scenario, a first reverse mapping layer is obtained in which the same shard in the business scenario simultaneously faces different query types with the same priority, and a second reverse mapping layer in which each query type with the same priority in different business scenarios simultaneously faces the same shard.

[0022] Specifically, the process of constructing the hierarchical query type priority mapping also includes:

[0023] Constructing a hierarchical query type priority mapping based on the first mapping layer, the forward mapping layer, the first reverse mapping layer, and the second reverse mapping layer, respectively setting a first initial conflict probability for each query type of the same priority level simultaneously facing different sharding rules, a second initial conflict probability for the same shard simultaneously facing different query types of the same priority level under a business scenario, and a third initial conflict probability for each query type of the same priority level simultaneously facing the same shard under different business scenarios;

[0024] Based on the hierarchical query type priority mapping combined with the Monte Carlo simulation algorithm and historical query data, the query frequency distribution state corresponding to each unit time is simulated and analyzed, and query conflict simulation is performed to obtain the first conflict probability, second initial conflict probability and third conflict probability of each simulation in the hierarchical query type priority mapping.

[0025] Specifically, the process of constructing the hierarchical query type priority mapping also includes:

[0026] Feeding back the first conflict probability, the second initial conflict probability, and the third conflict probability of each simulation to the target loss function in the decision tree model and optimizing the training in combination with the preset comprehensive loss threshold, the optimal first conflict probability, the optimal second conflict probability, and the optimal third conflict probability that meet the threshold conditions and the corresponding forward mapping layer, the first reverse mapping layer, and the second reverse mapping layer are obtained;

[0027] Based on the optimal first conflict probability, the optimal second conflict probability and the optimal third conflict probability and the corresponding forward mapping layer, the first reverse mapping layer and the second reverse mapping layer, an optimal hierarchical query type priority mapping is constructed through a graph algorithm.

[0028] Specifically, the process of constructing the hierarchical query type priority mapping also includes:

[0029] According to the optimal hierarchical query type priority mapping, combined with the access frequency of the query type corresponding to each business scenario in the first mapping layer and the preset high-frequency access threshold, the optimal hierarchical high-frequency query index mapping is constructed;

[0030] The optimal hierarchical high-frequency query index mapping is built into the decision tree model for real-time query indexing. At the same time, the query index parameter set corresponding to each quarter is collected, and the collected query index parameter set corresponding to each quarter is fed back to the decision tree model to update the decision tree model and the optimal hierarchical high-frequency query index mapping.

[0031] Specifically, the process of obtaining the query type and the corresponding query type priority in each business scenario and determining the multi-sharding rule and sharding rule priority under the optimal sharding key corresponding to each query type in the business scenario includes:

[0032] The business request log fields and shard performance indicator fields corresponding to the historical optimal shard key for each quarter are aligned with the shard ID according to the timestamp and cross-quarter query transactions are eliminated to obtain the business evaluation input sequence; the business analysis and evaluation model includes a business analysis sub-model and a business evaluation sub-model;

[0033] Based on the business evaluation input sequence, the business parsing sub-model pre-trained in Chinese is used for parsing to obtain the query type evaluation field sub-sequence and sharding rule evaluation field sub-sequence for each business scenario;

[0034] Based on the query type call frequency and query latency in the query type evaluation field subsequence in each business scenario in each quarter, a comprehensive fuzzy algorithm is used in combination with the preset priority evaluation level interval to obtain the query call priority of the query type in each business scenario in each quarter.

[0035] Similarly, based on the sharding rules under the optimal sharding key in each scenario within each quarter, the conflict probability and load balancing degree corresponding to the multiple sharding rules in the field subsequence are evaluated, and the priority corresponding to the sharding rules under the optimal sharding key in each scenario within each quarter is obtained.

[0036] Specifically, the process of building a shard split-merge strategy includes:

[0037] Based on the trained decision tree model combined with a real-time stream processing algorithm, we simulated the shard load and query QPS for each shard rule at each mapping layer in the optimal hierarchical high-frequency query index mapping. We then used a pre-set evaluation algorithm to analyze and obtain the corresponding shard load and query QPS results for each quarter.

[0038] Based on the corresponding shard load results and query QPS results for each quarter, combined with the preset shard data volume threshold or query QPS threshold, the shards under the corresponding sharding rules are split or merged, and a new forward or reverse mapping is established between the new shards after splitting or merging and the query type, and the decision tree model and the optimal hierarchical high-frequency query index mapping are updated.

[0039] Specifically, query merging strategies include common query merging and aggregated result merging; aggregated result merging includes intermediate result aggregation and TopK optimization;

[0040] Common query merging: By merging the data rows returned by multiple shards and sorting or deduplicating them according to the query conditions, the final merged query data is obtained.

[0041] The intermediate result aggregation is performed by merging the local aggregate values ​​of each shard into a global result to obtain the final merged query requirement data;

[0042] TopK optimization merges query data through the query field restriction conditions in the query field to obtain the final merged query requirement data.

[0043] The shard storage and query optimization system for high-concurrency databases includes: initialization module and query discrimination module;

[0044] The initialization module initializes the distributed database based on the preset decision tree model and sharding rule engine; at the same time, it combines the ANTLR4 parser based on user needs to generate the query abstract syntax tree;

[0045] The query discrimination module includes a pre-calculation discrimination unit and a discrimination merging unit; the pre-calculation discrimination unit determines whether the preset Cube is hit based on the query abstract syntax tree. If it is hit, it directly returns the merged query result; if it is not hit, it calls the sharding rules preset in the sharding rule engine and combines the query abstract syntax tree, sharding key field, and the preset load balancing strategy and failover strategy to obtain the target sharding list;

[0046] The discrimination merging unit is used to merge the hit or miss query target lists to obtain the final query result.

[0047] Compared with the prior art, the present invention has the following beneficial effects:

[0048] In response to the deficiencies of the prior art, the present invention achieves precise matching of sharding rules and business scenarios through the collaborative design of dynamic sharding key selection and hierarchical query type priority mapping, combined with the adaptive learning of business characteristics and sharding performance indicators by the decision tree model; optimizes the hierarchical mapping conflict probability based on Monte Carlo simulation and graph algorithm, combines the real-time load balancing strategy with the high-frequency index acceleration mechanism, effectively balances the sharding load and reduces cross-shard query redundancy; ensures data distribution uniformity and query continuity through dynamic threshold triggering and reverse mapping reconstruction of the shard split-merge strategy; utilizes the T-TopK algorithm to optimize pruning of distributed result merging and accelerate the aggregation of pre-computed Cubes, reducing network transmission and computing overhead; finally, multi-dimensional technology coupling forms a closed-loop optimization system, which supports the accuracy of query routing, the flexibility of sharding resource scheduling and the overall stability of the system in high-concurrency scenarios. BRIEF DESCRIPTION OF THE DRAWINGS

[0049] Figure 1 This is a flow chart of a sharded storage and query optimization method for a high-concurrency database according to embodiment 1 of the present invention;

[0050] Figure 2 This is a module diagram of the sharded storage and query optimization system for a high-concurrency database in Example 2 of the present invention. DETAILED DESCRIPTION

[0051] Example 1:

[0052] See also Figure 1 The present invention provides an embodiment of a sharded storage and query optimization method for a high-concurrency database, comprising the following steps:

[0053] S1. Initialize the distributed database based on the preset decision tree model and sharding rule engine, and generate the query abstract syntax tree based on user needs and the ANTLR4 parser;

[0054] Furthermore, the process of initializing the database based on the preset decision tree model and sharding rule engine in this embodiment includes:

[0055] Collect historical business request log data, including fields such as request unique identifier, business classification code, price type value, query type, and request timestamp; also collect sharding performance indicator data, such as the number of queries per second, average response time, sharding data volume, CPU or memory utilization, etc.; use this data to train a decision tree model and determine the optimal sharding key by maximizing QPS and minimizing query latency; integrate the optimal sharding key, various sharding rules, and shard split-merge strategies into the sharding rule engine to complete the initial configuration of the distributed database.

[0056] It should be noted that the sharding rule engine in this embodiment is constructed by the optimal sharding key selected by the decision tree model, the sharding rules with business requirement priority tags, and the sharding split-merge strategy; the sharding rules include hash sharding, range sharding, or list sharding;

[0057] It should be noted that the shard split-merge strategy in this embodiment is constructed based on the amount of data in each shard, the number of queries per second, and the preset shard load threshold, and is adjusted in real time through the shard-merge simulation process during the query process.

[0058] Furthermore, the process of constructing the shard split-merge strategy in this embodiment includes:

[0059] Based on the trained decision tree model combined with a real-time stream processing algorithm, we simulated the shard load and query QPS for each shard rule at each mapping layer in the optimal hierarchical high-frequency query index mapping. We then used a pre-set evaluation algorithm to analyze and obtain the corresponding shard load and query QPS results for each quarter.

[0060] Based on the corresponding shard load results and query QPS results for each quarter, combined with the preset shard data volume threshold or query QPS threshold, the shards under the corresponding sharding rules are split or merged, and a new forward or reverse mapping is established between the new shards after splitting or merging and the query type, and the decision tree model and the optimal hierarchical high-frequency query index mapping are updated.

[0061] Furthermore, in this embodiment, a more detailed process of constructing a shard split-merge strategy includes:

[0062] Deploy a real-time stream processing framework to collect shard load and query QPS data in real time. Combine the real-time data with the decision tree model, use the model to predict the load and QPS change trends under different sharding rules, analyze and process the simulated data using a preset evaluation algorithm, and generate detailed shard load and query QPS result reports on a quarterly basis. Use the analysis report results to adjust and update the shard split-merge strategy on a quarterly basis.

[0063] S2. Determine whether the query matches the preset Cube based on the query abstract syntax tree. If so, directly return the merged query result.

[0064] It should be noted that this embodiment matches the query abstract syntax tree with the preset Cube; further, the preset Cube in this embodiment is a pre-defined structure in the database that stores commonly used query results or aggregated data; if the query abstract syntax tree successfully matches the preset Cube, it means that the query result already exists or can be quickly obtained, and the merged query result is directly returned to avoid repeated query operations and improve query efficiency.

[0065] It should be further explained that this embodiment matches the generated query abstract syntax tree with the structure and content of the preset Cube through a similarity matching algorithm to determine whether the query fields, conditions, aggregation operations, etc. are consistent with the definitions in the preset Cube; if the match is successful, the corresponding merged query results are extracted from the preset Cube and returned.

[0066] S3. If no hit is found, the sharding rules preset in the sharding rule engine are called to combine the query abstract syntax tree, sharding key fields, and the preset load balancing strategy and failover strategy to obtain the target sharding list;

[0067] Furthermore, in this embodiment, the query processing process when no cube is found includes:

[0068] If the query does not hit the preset Cube, the preset sharding rules in the sharding rule engine are called, and the target sharding list is determined according to the preset load balancing strategy and failover strategy in combination with the sharding key field in the query abstract syntax tree. Furthermore, in this embodiment, the load balancing strategy is used to ensure that the query requests are evenly distributed to each shard to avoid excessive load on a single shard. The failover strategy in this embodiment transfers the query request to other available shards when a shard fails.

[0069] S4. A query merging strategy is constructed based on the target shard list, the preset high-frequency index list, and the T-TopK algorithm to obtain the merged query results.

[0070] Furthermore, the query merging strategy in this embodiment includes common query merging and aggregated result merging; wherein, aggregated result merging includes intermediate result aggregation and TopK optimization;

[0071] It should be noted that the common query merge in this embodiment is to obtain the final merged query requirement data by merging the data rows returned by multiple shards and sorting or deduplicating them according to the query conditions;

[0072] It should be noted that the intermediate result aggregation in this embodiment is achieved by merging the local aggregate values ​​of each shard into a global result to obtain the final merged query demand data;

[0073] It should be noted that the TopK optimization in this embodiment merges query data through the query field restriction conditions in the query field to obtain the final merged query demand data.

[0074] Furthermore, the T-TopK algorithm is used in this embodiment to optimize the TopK aggregation result merging. Its core goal is to efficiently merge the local TopK results returned by multiple shards into a global TopK. It also combines load balancing algorithms, high-frequency indexes, and dynamic shard adjustment features to ensure low latency and high accuracy. It should be noted that the high-frequency index in this embodiment is constructed by those skilled in the art based on the historical frequency of each query field or type.

[0075] It should be noted that the specific implementation of TopK optimization in this embodiment is as follows:

[0076] Based on the query field restrictions in the query abstract syntax tree, each target shard executes the query locally and quickly locates data based on the high-frequency index list to generate a local TopK result set.

[0077] If the query field hits a high-frequency index, the local TopK is directly obtained through index sorting to avoid a full table scan.

[0078] It should be noted that the shards of this embodiment need to monitor their own load in real time when calculating the local TopK. If the load exceeds the threshold, the calculation granularity is dynamically adjusted to avoid performance bottlenecks.

[0079] Furthermore, in this embodiment, when each shard returns a local TopK result, the following metadata is appended, specifically:

[0080] When a shard returns a local TopK, it adds load status, sort key boundary values, and data distribution statistics. It also uses this statistical information to predict shard conflict risks and trigger the initialization of the decision tree model.

[0081] It should be further explained that the specific implementation of conflict risk prediction in the high-concurrency distributed system of this embodiment is as follows:

[0082] In a highly concurrent distributed system, each shard node uses a sliding time window algorithm to maintain a priority queue and calculate local TopK results through a heap sort algorithm. It also collects node operation status data synchronously and obtains load indicators such as CPU usage, memory occupancy, and network I / O throughput through the performance counter API. At the same time, it uses a distributed extreme value algorithm to record the maximum and minimum values ​​of the sort key, uses the Welford algorithm to calculate the mean and standard deviation online, and uses the T-Digest algorithm to efficiently calculate quantiles.

[0083] Based on the collected data, a feature engineering algorithm was used to construct a risk feature vector containing M dimensions. The original indicators were Z-score standardized, and the rate of change of each dimension was calculated. Using the C4.5 decision tree algorithm, a risk assessment model was trained on a set of historical conflict event records containing risk feature vectors and corresponding conflict level labels. The Shannon entropy algorithm was used to calculate information gain and determine the optimal feature partitioning node. The shard conflict risk was then categorized into levels 0-3, with level 0 indicating no risk and level 3 indicating high risk.

[0084] An online learning framework is used to implement real-time model updates. When the interquartile range is calculated using the boxplot method and the real-time risk score exceeds the preset risk threshold, the risk assessment model retraining process is triggered. The Hoeffding tree incremental training algorithm is used to update the decision tree parameters. The model consistency is ensured through the version control mechanism, ultimately achieving real-time early warning of potential shard conflict risks and providing decision support for the data merging stage of the distributed system. This process systematically collects shard operation data and key statistical information, and uses the decision tree algorithm to identify shards with conflict risks in advance, providing early warnings for subsequent processing and ensuring that the system can avoid potential problems during the data merging stage. The incremental training algorithm is preferably the Hoeffding tree;

[0085] Based on the sort key boundary values ​​of the shards, Monte Carlo simulation is used to predict the global key value distribution. Combined with the conflict probability output by the decision tree model, a dynamic penalty factor is introduced to adjust the threshold to suppress the impact of high-conflict shards on the global results.

[0086] It should be noted that the dynamic penalty factor introduced in this embodiment is constructed by quantifying the coupling relationship between the shard conflict probability and the load status, combined with the global distribution prediction results of Monte Carlo simulation, to map the conflict risk to a suppression coefficient for threshold adjustment.

[0087] It should be further explained that the implementation process of global distribution prediction and threshold adjustment in this embodiment includes:

[0088] Based on the sort key boundary values ​​provided by each shard, a Monte Carlo simulation algorithm based on Latin hypercube sampling is used. Combined with the distribution characteristics obtained by kernel density estimation of the shard data, N random samplings are performed to generate a simulated data set and infer the global key value distribution pattern.

[0089] It should be noted that the Monte Carlo simulation in this embodiment generates a large amount of simulated data based on the distribution characteristics of the sharded data through multiple random samplings, and then infers the distribution pattern of the global data;

[0090] The dynamic penalty factor is constructed by multiplying the shard conflict probability output by the decision tree model with the shard load status and combining it with the global distribution tail probability obtained from the Monte Carlo simulation.

[0091] It should be noted that the size of the penalty factor in this embodiment is positively correlated with the conflict probability and load pressure, and is used to adjust the pre-filtering threshold. Shards with high conflict probability and high load have larger penalty factors, which will increase the threshold accordingly to reduce the impact of these shards on the global results. This process uses Monte Carlo simulation to scientifically predict the global data distribution, and dynamically adjusts the threshold based on the conflict probability and load status. This provides a quantitative basis for local data pre-filtering and improves the accuracy and reliability of data processing.

[0092] Local results are filtered based on a dynamic threshold, and records with key values ​​above the threshold are retained. The missing value range is estimated based on the data distribution statistics of high-load shards, and the threshold is dynamically relaxed to accommodate potential omissions, reduce the size of the merged data, and ensure the integrity of the results. In this embodiment, one implementation method for filtering local results based on a dynamic threshold is as follows:

[0093] Based on Monte Carlo simulation algorithm and dynamic penalty factor , obtain the basic threshold T, use the data screening algorithm to pre-filter the local TopK results of each shard, and retain the records with key value K>T; for high-load shards whose load status L exceeds the preset threshold, use the T-Digest algorithm to calculate the upper quartile Q3 and standard deviation of the data to estimate the range of missing values; use the missing value impact assessment algorithm to compare the empirical distribution function of the retained data with the original distribution characteristics, and calculate the KS statistic as the impact I; when I exceeds the corresponding threshold, the basic threshold is dynamically adjusted to 0 based on the Lagrange interpolation method. , the key value data is re-incorporated into the result set; is the adjustment coefficient, take 0.8, is the adjusted threshold; the preset threshold in this embodiment is determined by the 95% quantile of the historical load data; where P is the conflict probability, L is the load state, is the global distribution tail probability

[0094] The pre-filtered data is assigned to different queues according to the business scenario priority, and then the queues are subdivided according to the sharding rule priority to ensure that high-priority data enters the global heap merging process first, forming a processing order logic; the sharding rule priority in this embodiment is hash > range > list;

[0095] The current optimal element is extracted from the head of each queue and inserted into the global heap in turn, maintaining a heap structure of fixed size K. When key values ​​conflict, the latest version is retained according to the sharding rule priority or timestamp, and the elements in the heap are dynamically updated to approach the true TopK result.

[0096] It should be further explained that the implementation process of data priority queue allocation in this embodiment includes: first, the pre-filtered local data is allocated to different main queues according to the priority set by the business scenario, such as real-time requirements, business importance, etc., and is divided into high, medium and low priority queues; within each main queue, it is further subdivided according to the priority of the sharding rule (hash>range>list) to form sub-queues; each sub-queue is stored in a priority queue data structure to ensure that high-priority data is at the head of the queue and can enter the global heap merging process first; this process clarifies the data processing sequence logic through this two-layer priority allocation mechanism, ensures that data that is more important to the business and has a higher rule priority can be processed first, and improves the rationality and effectiveness of the system's data processing.

[0097] It should be further explained that the implementation process of global heap merging and conflict handling in this embodiment includes:

[0098] A minimum heap structure of fixed size K is set up, and the currently optimal element is sequentially extracted from the head of each priority queue and inserted into the heap. During the insertion process, if the heap is full, the minimum element at the top of the heap is removed to maintain the same heap size. When a key-value conflict occurs, the record of the higher-priority shard is retained according to the sharding rule priority. If the sharding rule priority is the same, the latest version of the record is retained based on the data timestamp. As data is continuously inserted and updated, the elements in the heap are continuously adjusted to gradually approach the true global TopK result. This process, through the minimum heap structure and a specific conflict resolution strategy, efficiently achieves the merging of local data and gradually builds an accurate global TopK result set.

[0099] During the heap merge process, the shard load status is detected. If the overload persists, the shard split-merge strategy is triggered, migrating some query traffic to the new shard and updating the target shard list. At the same time, the load status is fed back to the decision tree model, forming a closed-loop adjustment mechanism to optimize the weight of subsequent sharding rules.

[0100] It should be further explained that the implementation process of shard load monitoring and dynamic adjustment in this embodiment includes: during the global heap merging process, real-time monitoring of the load status of each shard, collecting key indicators such as CPU usage, memory usage, and request processing time; using a sliding window algorithm to count the load data in the current period to determine whether the shard is in a continuous overload state; when a shard is detected to be continuously overloaded, triggering the shard split-merge strategy; migrating part of the query traffic of the shard to the newly created shard, and updating the target shard list in the system; at the same time, feeding back the new load status data to the decision tree model, retraining the model, and adjusting the weights of subsequent sharding rules; this closed-loop adjustment mechanism enables the system to dynamically optimize the sharding strategy according to the real-time load, thereby ensuring the stability and processing performance of the system.

[0101] The merged TopK results are written into the distributed cache and a hierarchical high-frequency query index is established. A fast response path is built based on the business scenario and sharding rule priority, providing millisecond-level result reuse capabilities for subsequent identical queries. At the same time, the index mapping is updated to adapt to dynamic sharding rules.

[0102] It should be further explained that the implementation process of result caching and index construction in this embodiment includes: writing the merged global TopK results into a distributed cache system, such as Redis; in order to improve query efficiency, a hierarchical high-frequency query index is established based on business scenarios and sharding rule priorities; first, a primary index is created according to the business scenario, and query results of the same business scenario are associated; then, under each business scenario index, a secondary index is created according to the sharding rule priority; at the same time, a Bloom filter algorithm is used to reduce the number of disk accesses and improve index query efficiency; when the sharding rules change dynamically, the index mapping relationship is updated in time to ensure that the cached data can respond quickly and accurately to subsequent identical query requests, achieving millisecond-level result reuse capabilities, and improving the overall response speed and query performance of the system.

[0103] Furthermore, in this embodiment, based on the optimal hierarchical high-frequency query index mapping, the pre-screened results are organized into the following levels:

[0104] Based on the preset hierarchical query type priority mapping, the TopK results are assigned to different priority queues to ensure that data from high-value business scenarios enters the merging process first;

[0105] Within the same business scenario queue, the queue is subdivided twice according to the priority of the sharding rules, giving priority to processing data from high-reliability shards, reducing the risk of conflicts caused by inconsistent sharding rules;

[0106] The best element is extracted from the head of each sub-queue and inserted into the global heap. When the heap size reaches K, the heap structure is dynamically replaced and adjusted by comparing the key value of the top element with the new element, gradually approaching the global optimal TopK result.

[0107] If records with the same key value but different shard sources are encountered, a unique version is retained based on the sharding rule priority or business timestamp to ensure the authority and consistency of the results;

[0108] When a shard's load is detected to be continuously exceeding the limit, the shard split-merge strategy is automatically triggered to migrate some data to the new shard and update the shard list to alleviate the pressure of the local hotspot while maintaining the integrity of the sharding rules.

[0109] Feedback the shard load status and conflict resolution records to the decision tree model, dynamically adjusting the shard rule weights for subsequent queries, forming an adaptive closed loop of "execution, monitoring, and optimization";

[0110] The merged TopK results are written to the distributed cache, and a hierarchical index is established based on the business scenario and sharding rules. At the same time, the index mapping is updated to adapt to the latest sharding rule changes;

[0111] Furthermore, the cache key in this embodiment is generated by querying the hash value of the abstract syntax tree to avoid repeated calculations.

[0112] If the current query is not matched by the preset Cube, but meets the Cube update conditions, for example, the query frequency exceeds the threshold:

[0113] The TopK results are sliced ​​by business classification code and time range, and written into storage as new Cube blocks to update the Cube metadata index to ensure direct hits during the next match.

[0114] Based on the above merging process, the actual conflict probability in this merging process is compared with the predicted value of the decision tree model to obtain the loss function;

[0115] Based on the loss function, the decision tree model parameters are updated through back propagation to optimize the sharding rule selection strategy;

[0116] Based on the actual performance of query merging, adjust the optimal hierarchical high-frequency query index mapping, improve the index level of high-frequency query fields, accelerate local TopK calculations, and remove inefficient indexes to reduce storage overhead and maintenance costs.

[0117] The classification-merge strategy in this embodiment uses high-frequency indexing to locate data and local TopK calculations to avoid full table scans and accelerate shard data preprocessing. It combines load dynamic adjustment granularity to prevent performance bottlenecks and achieve low-latency queries. The shards return metadata such as additional load status and boundary values. The conflict risk is quantified through Monte Carlo simulation and decision tree models. A dynamic penalty factor is introduced to suppress the impact of high-conflict shards. Combined with dynamic threshold screening and high-load shard missing value estimation, the integrity of the results is guaranteed while reducing the size of the merged data. The queues are subdivided according to the priority of the business and sharding rules. The TopK is maintained and key value conflicts are handled through global heap merging. The shard split-merge strategy is used to achieve load balancing and traffic migration, forming a "monitoring and adjustment" closed loop. The results are stored in a distributed cache and a hierarchical high-frequency index is established. Combined with the dynamic update of the Cube and the reverse optimization of the decision tree model, an "execution and optimization" adaptive system is constructed, which not only improves the reuse efficiency of high-frequency queries, but also achieves a balanced optimization of storage overhead and query performance through dynamic adjustment of the index level and removal of inefficient indexes.

[0118] Furthermore, the construction and training process of the decision tree model in this embodiment includes:

[0119] Obtain the business request log field and sharding performance indicator field corresponding to the historical optimal sharding key, and use the business request log field and sharding performance indicator field in combination with the query type field through association rules to obtain the hierarchical query type priority mapping and sharding performance constraint set corresponding to each optimal sharding key;

[0120] The business request log fields include the request unique identifier, business classification code, price type value, query type, and request timestamp; query types include point query, range query, and list query;

[0121] The shard performance indicator fields include the number of queries per second, average response time, shard data volume, and CPU or memory utilization;

[0122] The hierarchical query type priority mapping, shard performance constraint set, and shard split-merge strategy are input into the decision tree model constructed by the decision tree algorithm. The model is then trained using the target loss function constructed using QPS, query latency, shard data Gini coefficient, and the probability of conflict between multiple shards under the optimal shard key during the query process to obtain a trained decision tree model.

[0123] Furthermore, the process of constructing the hierarchical query type priority mapping in this embodiment includes:

[0124] Based on the frequency of query access using each query type for each business scenario in each historical quarter, a first mapping layer is constructed between each business scenario and the query type.

[0125] Based on the business request log fields and sharding performance indicator fields corresponding to the historical optimal sharding key for each quarter, the preset business analysis and evaluation model is used to obtain the query type and corresponding query type priority in each business scenario, and determine the multi-sharding rules and sharding rule priority under the optimal sharding key for each query type in the business scenario;

[0126] Furthermore, in this embodiment, the process of obtaining the query type and the corresponding query type priority in each business scenario and determining the multi-sharding rule and the sharding rule priority under the optimal sharding key corresponding to each query type in the business scenario includes:

[0127] The business request log fields and shard performance indicator fields corresponding to the historical optimal shard key for each quarter are aligned with the shard ID according to the timestamp and long-term query transactions across quarters are eliminated to obtain the business evaluation input sequence. The business analysis and evaluation model includes a business analysis sub-model and a business evaluation sub-model.

[0128] Furthermore, in this embodiment, long-term, cross-quarter query transactions refer to those whose execution cycles span multiple quarters and involve complex query logic. These transactions typically have the following characteristics: a long time span, specifically, the corresponding transaction initiation and completion times are distributed in different quarters; complex query logic, specifically, including multi-stage operations that require long resource usage; and strong data and state dependencies, specifically, subsequent operations of the transaction depend on the results or intermediate states of previous operations, resulting in the transaction lifecycle not being able to be closed within a quarter.

[0129] Based on the business evaluation input sequence, the business parsing sub-model pre-trained in Chinese is used for parsing to obtain the query type evaluation field sub-sequence and sharding rule evaluation field sub-sequence for each business scenario;

[0130] Based on the query type call frequency and query latency in the query type evaluation field subsequence for each business scenario in each quarter, a comprehensive fuzzy algorithm is used in combination with the preset priority evaluation level interval to obtain the query call priority for each query type in each business scenario in each quarter.

[0131] Similarly, based on the sharding rules under the optimal sharding key in each scenario within each quarter, the conflict probability and load balancing degree corresponding to the multiple sharding rules in the field subsequence are evaluated, and the priority corresponding to the sharding rules under the optimal sharding key in each scenario within each quarter is obtained.

[0132] Based on the query type and corresponding query type priority in each business scenario and the sharding rules under each optimal sharding key, a forward mapping layer is constructed through association rules, which allows each query type with the same priority in each business scenario to face different sharding rules at the same time.

[0133] Similarly, by determining the multi-sharding rules and sharding rule priorities under the optimal sharding key for each query type in the business scenario, a first reverse mapping layer is obtained in which the same shard in the business scenario simultaneously faces different query types with the same priority, and a second reverse mapping layer in which each query type with the same priority in different business scenarios simultaneously faces the same shard.

[0134] Constructing a hierarchical query type priority mapping based on the first mapping layer, the forward mapping layer, the first reverse mapping layer, and the second reverse mapping layer, respectively setting a first initial conflict probability for each query type of the same priority level simultaneously facing different sharding rules, a second initial conflict probability for the same shard simultaneously facing different query types of the same priority level under a business scenario, and a third initial conflict probability for each query type of the same priority level simultaneously facing the same shard under different business scenarios;

[0135] Based on the hierarchical query type priority mapping combined with the Monte Carlo simulation algorithm and historical query data, the query frequency distribution state corresponding to each unit time is simulated and analyzed, and query conflict simulation is performed to obtain the first conflict probability, second initial conflict probability and third conflict probability of each simulation in the hierarchical query type priority mapping.

[0136] Feeding back the first conflict probability, the second initial conflict probability, and the third conflict probability of each simulation to the target loss function in the decision tree model and optimizing the training in combination with the preset comprehensive loss threshold, the optimal first conflict probability, the optimal second conflict probability, and the optimal third conflict probability that meet the threshold conditions and the corresponding forward mapping layer, the first reverse mapping layer, and the second reverse mapping layer are obtained;

[0137] Based on the optimal first conflict probability, the optimal second conflict probability and the optimal third conflict probability and the corresponding forward mapping layer, the first reverse mapping layer and the second reverse mapping layer, an optimal hierarchical query type priority mapping is constructed through a graph algorithm.

[0138] According to the optimal hierarchical query type priority mapping, combined with the access frequency of the query type corresponding to each business scenario in the first mapping layer and the preset high-frequency access threshold, the optimal hierarchical high-frequency query index mapping is constructed;

[0139] The optimal hierarchical high-frequency query index mapping is built into the decision tree model for real-time query indexing. At the same time, the query index parameter set corresponding to each quarter is collected, and the collected query index parameter set corresponding to each quarter is fed back to the decision tree model to update the decision tree model and the optimal hierarchical high-frequency query index mapping.

[0140] It should be further explained that the hierarchical query type priority mapping in this embodiment is a three-layer tree structure implemented using a B+ tree, which is used to define the priority relationship between business scenarios, query types, and sharding rules, specifically:

[0141] The first layer is the business scenario layer, which identifies business domains by business classification codes and arranges nodes in descending order by average daily query volume;

[0142] The second layer is the query type layer. A query type node is mounted in each business scenario, and the priority weight is calculated based on the response time threshold.

[0143] The third layer is the sharding rule layer. Each query type is associated with a sharding rule, and the weight is dynamically adjusted based on the conflict probability: rule weight = 1-(historical conflict count / total query count);

[0144] This mapping constructs the optimal query path through the Dijkstra shortest path algorithm and finally outputs the execution priority score of each request.

[0145] It should be further explained that the optimal hierarchical high-frequency query index mapping in this embodiment is a two-layer index structure based on access popularity, specifically:

[0146] The first layer is the business hotspot index, which uses the HyperLogLog algorithm to count the access popularity of business scenarios and screen the top five high-frequency businesses, such as e-commerce orders and payment verification.

[0147] The second-tier query hotspot index achieves dynamic maintenance of fast queries in hotspot services through the following specific technical means: The LFU algorithm is combined with a time window set to 7 days, and a hash table is used to store the access counts of each field. When the number of accesses exceeds 10,000, it is marked as a hot query field. The hot sharding rule stores the CRC64 signature of the hash sharding rule through a Bloom filter to achieve efficient deduplication and existence verification. The cache association link uses the LRU-K algorithm with a K value of 2 to bind the hot query field and the result cache through a doubly linked list and hash table. The overall mapping structure is based on a skip list data structure and achieves O(log n) level retrieval efficiency through multi-layer index pointers. When the query hits, the result is directly returned from the memory cache. If it misses, the underlying data retrieval is triggered and the hotspot index is updated.

[0148] It should be further explained that, in this embodiment, the overall mapping structure is based on a skip list data structure, and the specific implementation process of achieving O(log n) level retrieval efficiency through multi-layer index pointers is as follows:

[0149] A coin-tossing model with probability p is used to randomly generate node levels. In this embodiment, the initial level is 1, and the level increases by 1 each time the coin tosses heads. The maximum number of levels is calculated by dynamically monitoring the data size n. A forward pointer array is maintained within the node, pointing to the next node at each level. During insertion, a new node is generated at a random level and the pointers at each level are updated. During deletion, the node is located and the pointers are disconnected layer by layer. A skip search algorithm combined with a dynamic level adjustment mechanism ensures a stable search complexity of O(log n). A lazy level compression algorithm is also used to avoid frequent level reconstruction when the data size fluctuates. The skip search algorithm starts at the highest valid level, skipping invalid nodes as much as possible at each level (by comparing the key value of the next node pointed to by the pointer with the target key). When the key value of the node under the current level is greater than the target, the level is demoted until the bottom level is searched. The time complexity is guaranteed to be O(log n) due to the randomness of the levels. The lazy hierarchical compression algorithm is a counter that maintains node hierarchical redundancy. When the proportion of invalid high-level nodes exceeds a threshold, a delayed compression task is initiated, the skip list is traversed, and nodes with a level higher than the theoretical maximum level are pruned, that is, high-level pointers are disconnected. Batch processing is used to avoid frequent reconstruction, and the atomicity of retrieval operations is maintained during the compression process.

[0150] This process constructs a first mapping layer based on historical query frequencies, quantifying the correlation between business scenarios and query types in a data-driven manner. This closely integrates the personalized needs of business scenarios with the usage characteristics of query types, avoiding the limitations of traditional static mapping and providing data support for subsequent refined scheduling. A business analysis and evaluation model deeply analyzes the fields related to the historically optimal sharding key. By aligning timestamps with shard IDs and eliminating long-term, multi-quarter queries, this approach accurately filters out invalid data interference, ensuring that the obtained query type and sharding rule priorities are more aligned with the real-time needs of actual business scenarios and ensuring rational resource allocation. Furthermore, the construction of forward and reverse mapping layers dynamically establishes the association between query types, sharding rules, and business scenarios from multiple perspectives, breaking the constraints of a single mapping relationship and achieving three-dimensional optimization of query scheduling strategies. Initial conflict probability settings and the introduction of a Monte Carlo simulation algorithm proactively identify potential query conflict risks through simulation and deduction of historical query data. Combined with the optimization and training of a decision tree model, this further reduces the probability of conflicts in actual operations and improves the stability and smoothness of system operations. The optimal hierarchical high-frequency query index mapping finally constructed is screened based on access frequency and preset thresholds, focusing on high-frequency query scenarios. Combined with a real-time update mechanism, it enables the system to quickly respond to business needs, continuously improve query efficiency, reduce resource waste, and provide a solid guarantee for the efficient operation and performance optimization of the business system.

[0151] Furthermore, in this embodiment, the process of building and training the decision tree model and mapping the hierarchical query type priorities includes:

[0152] First, dynamic time warping (DTW) is used to align service request logs with shard performance indicators based on timestamps and shard IDs. A sliding window filtering algorithm and scenario-focusing principles are used to remove long-term, cross-quarter transaction noise, generating a standardized service load assessment sequence. It should be noted that dynamic time warping (DTW) in this embodiment is used to align data of different frequencies, and the sliding window filtering algorithm can effectively filter out low-frequency noise data.

[0153] Second, we use the pre-trained Bert model to extract semantic features of business codes and query types. We then combine the TF-IDF algorithm with a response time statistical analysis algorithm to establish a priority mapping relationship between business scenarios and sharding rules, achieving semantically driven matching from business needs to resource allocation strategies. It should be noted that the Bert model has advantages in semantic understanding, and the TF-IDF algorithm can quantify the importance of word frequency. The BM25 algorithm is preferred because it takes document length normalization and word frequency saturation effects into account on the basis of TF-IDF, which can better reflect the importance of words to documents.

[0154] Third, a forward matching link between business, query type, and sharding rules is constructed based on a layered graph mapping model. Low-latency sharding resources are dynamically allocated to high-priority queries through a priority queue scheduling strategy. A Bayesian network model is also introduced to quantify the resource competition risk of multiple query types within the same shard and shared shards across businesses through Bayesian probability calculation, supporting conflict prediction and policy adjustment. It should be noted that the layered graph mapping model is suitable for building multi-level mapping relationships, and the Bayesian network can handle uncertain reasoning. The layered graph mapping model is preferably a Directed Acyclic Graph (DAG) model, which represents dependencies between nodes through directed edges and is commonly used for task scheduling and process modeling.

[0155] It should be further explained that the priority queue scheduling strategy in this embodiment includes but is not limited to shortest job first, that is, priority is given to tasks with the shortest execution time; earliest deadline first, that is, priority is arranged according to the deadline of the task, and the earlier the deadline, the higher the priority; multi-level feedback queue, which divides tasks into multiple priority queues, and new tasks enter the highest priority queue. As the task is executed and the time slice is used up, the priority is gradually reduced, taking into account both the fast execution of short tasks and the fair scheduling of long tasks.

[0156] Fourth, sharding load trends are predicted based on the LSTM neural network. Real-time monitoring data collection and feedback control principles are combined to trigger sharding rule optimization. A hash sharding algorithm, index pre-building algorithm, and traffic tiered scheduling technology are used to isolate high-concurrency loads and balance query performance and storage costs. LSTM is commonly used for time series prediction, and hash sharding can achieve balanced data distribution. It should be noted that the preferred hash sharding algorithm is Consistent Hashing with Virtual Nodes. By introducing virtual nodes, node distribution is more even and data skew is reduced.

[0157] Bulk Loading is the preferred index pre-building algorithm. This allows indexes to be built directly during the data import phase, which is more efficient than building indexes when inserting data one by one.

[0158] Traffic classification and scheduling technologies include, but are not limited to: differentiated services, which classifies and marks data packets to provide different service qualities for different types of traffic; integrated services, which reserves resources for each data flow and provides end-to-end service quality guarantees, but is relatively complex to implement; and weighted fair queuing, which allocates bandwidth based on traffic weights to ensure fairness for traffic of different priorities.

[0159] Fifth, based on the consistent hashing algorithm, the sharding key weight and traffic distribution strategy are dynamically adjusted. For emergencies, sharding is automatically triggered by splitting by time or business field, dispersing load pressure and optimizing data distribution balance. It should be noted that the consistent hashing algorithm can reduce the amount of data migration when nodes change, ensuring load balancing. The consistent hashing algorithm is preferably the Ketama hashing algorithm, which improves the uniformity of node distribution by calculating the hash value of the node IP and port.

[0160] Sixth, cross-shard scan overhead is reduced through B+ tree index pre-building and dynamic index generation strategies. Combined with the CART decision tree model, adaptive sharding rules are optimized to achieve real-time adaptation of sharding strategies to business scenarios, ensuring system stability and resource utilization under high concurrency and mixed multi-business loads. It should be further clarified that B+ tree indexes are suitable for range queries, while CART decision trees can optimize rule classification based on features. Dynamic index generation strategies include, but are not limited to: adaptive index management, which automatically creates, deletes, or modifies indexes based on data update frequency and query patterns; index tuning tools, which analyze database query load and data distribution to recommend appropriate index structures and parameters; and instant indexing, which dynamically generates temporary indexes as needed during query execution, eliminating index maintenance overhead but potentially increasing query execution time. The preferred CART decision tree model is ID3 (Iterative Dichotomiser 3), which selects splitting attributes based on information gain.

[0161] This embodiment first integrates business request logs and sharding performance indicators, removes cross-quarter long-term transaction noise through time series filtering, generates a standardized evaluation sequence, ensures the purity and timeliness of model input data, and lays the foundation for accurate sharding decision-making; secondly, uses the pre-trained Bert model to extract the semantic features of business and query types, combines frequency and latency quantification to build priority mapping, realizes semantic-driven matching between business needs and sharding resources, and improves the intelligence of resource allocation; thirdly, by constructing a forward matching link and reverse conflict analysis mechanism for business, query type, and sharding rules, and using the Bayesian probability model to quantify resource competition risks, low-latency resource allocation and conflict prediction for high-priority queries are achieved, ensuring key Business response speed; Fourth, based on historical traffic simulation and real-time monitoring, sharding rule optimization is triggered, and high-concurrency loads are isolated through splitting, index pre-building and traffic adjustment technologies. Combined with the load balancing principle, the sharding key weight and traffic strategy are dynamically adjusted to achieve a balance between query performance and storage costs; Fifth, high-frequency combined index pre-building and medium-frequency on-demand indexing technology reduce cross-shard scanning overhead, and the decision tree model adaptively optimizes sharding rules, so that the system can adapt to sharding strategies and business scenarios in real time under high concurrency and multi-business mixed loads, forming a closed-loop optimization system of data purification, semantic matching, conflict prediction, dynamic optimization and index acceleration, ultimately improving system stability and resource utilization, and achieving efficient coordination between business needs and system resources.

[0162] Example 2:

[0163] See also Figure 2 ,Another embodiment provided by the present invention: a shard storage and query optimization system for a high-concurrency database, comprising: an initialization module, a query discrimination module;

[0164] The initialization module initializes the distributed database based on the preset decision tree model and sharding rule engine; at the same time, it combines the ANTLR4 parser based on user needs to generate the query abstract syntax tree;

[0165] The query discrimination module includes a pre-calculation discrimination unit and a discrimination merging unit; the pre-calculation discrimination unit determines whether the preset Cube is hit based on the query abstract syntax tree. If it is hit, it directly returns the merged query result; if it is not hit, it calls the sharding rules preset in the sharding rule engine and combines the query abstract syntax tree, sharding key field, and the preset load balancing strategy and failover strategy to obtain the target sharding list;

[0166] The discrimination and merging unit is used to merge the hit or miss query target lists to obtain a final query result.

[0167] The embodiments of the present invention are described above in conjunction with the accompanying drawings, but the present invention is not limited to the above-mentioned specific implementation methods. The above-mentioned specific implementation methods are merely illustrative and not restrictive. Under the guidance of the present invention, ordinary technicians in this field can also change, modify, replace and modify the above-mentioned embodiments without departing from the scope of protection of the purpose of the present invention and the claims, and all of these are protected by the present invention.

Claims

1. A sharded storage and query optimization method for a high-concurrency database, characterized in that: include: Initialize the distributed database based on the preset decision tree model and sharding rule engine, and generate the query abstract syntax tree based on user needs and in combination with the ANTLR4 parser; The sharding rule engine is constructed by the optimal sharding key selected by the decision tree model, the sharding rules with business demand priority labels, and the shard split-merge strategy; The sharding rules include hash sharding, range sharding or list sharding; The shard split-merge strategy adjusts the shard split-merge during the query process based on the data volume of each shard, the number of queries per second, and the corresponding shard load threshold; Determine whether the query matches the preset Cube based on the query abstract syntax tree. If so, directly return the merged query results. If no hit is found, the sharding rules preset in the sharding rule engine are called to combine the query abstract syntax tree, sharding key fields, and the preset load balancing strategy and failover strategy to obtain the target sharding list; A query merging strategy is constructed based on the target shard list, the preset high-frequency index list, and the T-TopK algorithm to obtain the merged query results. The construction and training process of the decision tree model includes: Obtain the business request log field and sharding performance indicator field corresponding to the historical optimal sharding key, and use the business request log field and sharding performance indicator field in combination with the query type field through association rules to obtain the hierarchical query type priority mapping and sharding performance constraint set corresponding to each optimal sharding key; The business request log fields include a request unique identifier, a business classification code, a price type value, a query type, and a request timestamp; the query types include point query, range query, and list query; The shard performance indicator fields include the number of queries per second, average response time, shard data volume, and CPU or memory utilization; The hierarchical query type priority mapping, shard performance constraint set, and shard split-merge strategy are input into the decision tree model constructed by the decision tree algorithm. The model is then trained using the target loss function constructed using QPS, query latency, shard data Gini coefficient, and the probability of conflict between multiple shards under the optimal shard key during the query process to obtain a trained decision tree model.

2. The shard storage and query optimization method for a high-concurrency database according to claim 1, wherein: The process of constructing the hierarchical query type priority mapping includes: Based on the frequency of query access using each query type for each business scenario in each historical quarter, a first mapping layer is constructed between each business scenario and the query type. Based on the business request log fields and sharding performance indicator fields corresponding to the historical optimal sharding key for each quarter, the preset business analysis and evaluation model is used to obtain the query type and corresponding query type priority in each business scenario, and determine the multi-sharding rules and sharding rule priority under the optimal sharding key for each query type in the business scenario; Based on the query type and corresponding query type priority in each business scenario and the sharding rules under each optimal sharding key, a forward mapping layer is constructed through association rules, which allows each query type with the same priority in each business scenario to face different sharding rules at the same time. By determining the multi-sharding rules and sharding rule priorities under the optimal sharding key for each query type in the business scenario, a first reverse mapping layer is obtained in which the same shard in the business scenario simultaneously faces different query types with the same priority, and a second reverse mapping layer in which each query type with the same priority in different business scenarios simultaneously faces the same shard.

3. The shard storage and query optimization method for a high-concurrency database according to claim 2, wherein: The process of constructing the hierarchical query type priority mapping further includes: Constructing a hierarchical query type priority mapping based on the first mapping layer, the forward mapping layer, the first reverse mapping layer, and the second reverse mapping layer, and setting a first initial conflict probability for each query type of the same priority level simultaneously facing different sharding rules, a second initial conflict probability for the same shard simultaneously facing different query types of the same priority level under a business scenario, and a third initial conflict probability for each query type of the same priority level simultaneously facing the same shard under different business scenarios, respectively, in the forward mapping layer, the first reverse mapping layer, and the second reverse mapping layer; Based on the hierarchical query type priority mapping combined with the Monte Carlo simulation algorithm and historical query data, the query frequency distribution state corresponding to each unit time is simulated and analyzed, and a query conflict simulation is performed to obtain the first conflict probability, the second initial conflict probability and the third conflict probability for each simulation in the hierarchical query type priority mapping.

4. The shard storage and query optimization method for a high-concurrency database according to claim 3, wherein: The process of constructing the hierarchical query type priority mapping further includes: Feeding back the first conflict probability, the second initial conflict probability, and the third conflict probability of each simulation to the target loss function in the decision tree model and optimizing the training in combination with the preset comprehensive loss threshold, the optimal first conflict probability, the optimal second conflict probability, and the optimal third conflict probability that meet the threshold conditions and the corresponding forward mapping layer, the first reverse mapping layer, and the second reverse mapping layer are obtained; Based on the optimal first conflict probability, the optimal second conflict probability and the optimal third conflict probability and the corresponding forward mapping layer, the first reverse mapping layer and the second reverse mapping layer, an optimal hierarchical query type priority mapping is constructed through a graph algorithm.

5. The shard storage and query optimization method for a high-concurrency database according to claim 4, characterized in that: The process of constructing the hierarchical query type priority mapping further includes: Constructing an optimal hierarchical high-frequency query index mapping based on the optimal hierarchical query type priority mapping combined with the access frequency of the query type corresponding to each business scenario in the first mapping layer and the preset high-frequency access threshold; The optimal hierarchical high-frequency query index mapping is built into the decision tree model for real-time query indexing. At the same time, the query index parameter set corresponding to each quarter is collected, and the collected query index parameter set corresponding to each quarter is fed back to the decision tree model to update the decision tree model and the optimal hierarchical high-frequency query index mapping.

6. The method for sharding storage and query optimization of a high-concurrency database according to claim 5, wherein: The process of obtaining the query type and the corresponding query type priority in each business scenario and determining the multi-sharding rule and the sharding rule priority under the optimal sharding key corresponding to each query type in the business scenario includes: The business request log fields and shard performance indicator fields corresponding to the historical optimal shard key of each quarter are aligned with the shard ID according to the timestamp and cross-quarter query transactions are eliminated to obtain a business evaluation input sequence; the business analysis and evaluation model includes a business analysis sub-model and a business evaluation sub-model; Based on the business evaluation input sequence, the business parsing sub-model pre-trained in Chinese is used for parsing to obtain the query type evaluation field sub-sequence and sharding rule evaluation field sub-sequence for each business scenario; Based on the query type call frequency and query latency in the query type evaluation field subsequence for each business scenario in each quarter, a comprehensive fuzzy algorithm is used in combination with the preset priority evaluation level interval to obtain the query call priority for each query type in each business scenario in each quarter. Based on the sharding rules under the optimal sharding key in each scenario in each quarter, the conflict probability and load balancing degree of the multiple sharding rules in the field subsequence are evaluated, and the priority of the sharding rules under the optimal sharding key in each scenario in each quarter is obtained.

7. The method for sharding storage and query optimization of a high-concurrency database according to claim 6, wherein: The process of constructing the shard split-merge strategy includes: Based on the trained decision tree model combined with a real-time stream processing algorithm, the shard load and query QPS corresponding to each shard rule under each mapping layer in the optimal hierarchical high-frequency query index mapping are simulated, and the shard load results and query QPS results corresponding to each quarter are obtained through analysis using a preset evaluation algorithm; Based on the corresponding shard load results and query QPS results for each quarter, combined with the preset shard data volume threshold or query QPS threshold, the shards under the corresponding sharding rules are split or merged, and a new forward or reverse mapping is established between the new shards after the split or merge and the query type, and the decision tree model and the optimal hierarchical high-frequency query index mapping are updated.

8. The method for sharding storage and query optimization of a high-concurrency database according to claim 7, wherein: The query merging strategy includes common query merging and aggregated result merging; the aggregated result merging includes intermediate result aggregation and TopK optimization; The common query merge is performed by merging the data rows returned by multiple shards and sorting or deduplicating them according to the query conditions to obtain the final merged query requirement data; The intermediate result aggregation is performed by merging the local aggregate values ​​of each shard into a global result to obtain the final merged query demand data; The TopK optimization merges query data through query field limiting conditions in the query field to obtain the final merged query demand data.

9. A sharded storage and query optimization system for a high-concurrency database, which is used to implement the sharded storage and query optimization method for a high-concurrency database according to any one of claims 1 to 8, characterized in that: include: Initialization module, query and discrimination module; The initialization module initializes the distributed database based on the preset decision tree model and sharding rule engine; and generates a query abstract syntax tree based on user needs and in combination with the ANTLR4 parser; The query discrimination module includes a pre-calculation discrimination unit and a discrimination merging unit; The pre-calculation judgment unit determines whether the preset Cube is hit based on the query abstract syntax tree, and directly returns the merged query result if it is hit; If no hit is found, the sharding rules preset in the sharding rule engine are called to combine the query abstract syntax tree, sharding key fields, and the preset load balancing strategy and failover strategy to obtain the target sharding list; The discrimination and merging unit is used to merge the hit or miss query target lists to obtain the final query result; The construction and training process of the decision tree model includes: Obtain the business request log field and sharding performance indicator field corresponding to the historical optimal sharding key, and use the business request log field and sharding performance indicator field in combination with the query type field through association rules to obtain the hierarchical query type priority mapping and sharding performance constraint set corresponding to each optimal sharding key; The business request log fields include a request unique identifier, a business classification code, a price type value, a query type, and a request timestamp; the query types include point query, range query, and list query; The shard performance indicator fields include the number of queries per second, average response time, shard data volume, and CPU or memory utilization; The hierarchical query type priority mapping, shard performance constraint set, and shard split-merge strategy are input into the decision tree model constructed by the decision tree algorithm. The model is then trained using the target loss function constructed using QPS, query latency, shard data Gini coefficient, and the probability of conflict between multiple shards under the optimal shard key during the query process to obtain a trained decision tree model.

Images