A database efficient reading method based on multi-level cache optimization and dynamic index fragmentation

Abstract

The application relates to the field of database reading, and particularly discloses a database efficient reading method based on multi-level cache optimization and dynamic index fragmentation, which comprises the following steps: high-frequency query data is accurately identified through a sliding window algorithm, dynamic cache loading and preheating strategies are implemented, query delay is effectively shortened, and the burden of the database is reduced; a consistent hash algorithm is used to realize intelligent distribution of index fragmentation, distributed query routing and transaction processing optimization are matched, and query efficiency is greatly improved; query load balancing is realized by means of a weighted round robin and a least connection strategy, real-time monitoring and an elastic expansion and contraction mechanism are combined, and it is ensured that the system can stably and efficiently operate under a high-concurrency scene. The application significantly enhances the database access efficiency and dynamic adaptability, and is particularly suitable for large-scale distributed data processing environments.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database reading, and in particular to a high-efficiency database reading method based on multi-level cache optimization and dynamic index sharding. BACKGROUND

[0002] With the explosive growth of Internet applications and the sharp increase in global data volume, database reading performance has become a core bottleneck restricting the digital transformation of enterprises. According to industry research data from DB-Engines, more than 68% of enterprises face serious challenges in high-concurrency query scenarios such as e-commerce promotions, ticket system purchases, and financial real-time transactions. These scenarios place extremely high demands on the response speed and processing capacity of databases:

[0003] 1. Response delay in high-concurrency query scenarios, such as e-commerce promotions (e.g., Double 11), ticket system purchases, and financial real-time transactions. According to existing data, the average query delay of a MySQL cluster for a certain leading e-commerce platform during peak periods soared from the normal 15ms to 220ms. Because traditional databases rely on B+ tree index structures, although they can guarantee query efficiency, in the case of massive data, the index level deepens, leading to multiple disk I / O, and the query delay increases significantly. For example, when the data volume of a single table exceeds 100 million, the index query of MySQL may involve 3-5 disk addressing, severely affecting the response speed.

[0004] 2. Cache invalidation under dynamic business load: The current mainstream cache systems (such as Redis and Memcached) have two major defects. One is that the static strategy does not match the dynamic demand, for example, the hourly change rate of a certain video platform's hot video list reaches 55%, but the fixed TTL causes 30% of the cache resources to store outdated data. Because existing cache technologies usually adopt static cache strategies (such as LRU and FIFO), they cannot dynamically adapt to changes in business access patterns. The second is the chain reaction of cache penetration. Many websites or systems may be attacked by malicious crawlers or interface abnormalities, which may trigger tens of thousands of queries per second for non-existent data, directly impacting the database. For example, a social platform once experienced such a problem, causing the database CPU to run at 100% for 37 minutes.

[0005] 3. Scalability dilemma of traditional sharding technology: Existing sharding solutions face three major pain points. Uneven sharding: When sharding by range, 30% of requests in a payment system are concentrated on 5% of shards; high cost of expansion: a bank system needs to be offline for 8 hours to migrate 200TB of data; poor cross-shard query performance: JOIN operation delay can be more than 20 times that of single-shard query. Indexes in traditional databases are usually concentrated on a single node. When query request volume surges, CPU and memory load of the index node rises sharply, forming a performance bottleneck. For example, a financial transaction system has a query per second (QPS) of over 100,000 in peak hours, and single-node indexing cannot support it, leading to a sharp increase in query delay.

[0006] 4. Lack of scalability: Traditional databases (such as MySQL) support master-slave replication, but write operations are still concentrated on the master, and indexes cannot be dynamically sharded, making it difficult to achieve true horizontal scaling.

[0007] The current industry's common optimization solutions include three main types:

[0008] 1. Hardware upgrade solution: Replacing disks with SSDs can reduce single I / O delay to 0.1ms, but the cost per TB of storage increases by 5 times; however, it cannot solve the inherent delay caused by index depth (such as B+ tree height problem); using Redis cluster storage is expensive and cannot support complex SQL queries.

[0009] 2. Software optimization: Read-write separation theory can improve read performance by 3 times, but the write pressure on the master cannot be distributed. Query optimizer tuning can theoretically improve 15%~30%, but the effect on complex queries is limited. Connection pool expansion can theoretically improve 20%~50%, but it is limited by the database thread model.

[0010] 3. Machine learning index: requires continuous model training, and the prediction accuracy drops by 60% when data distribution changes (such as e-commerce promotions).

[0011] 4. New hardware acceleration: the cost of modification is extremely high, and compatibility with existing architecture is poor.

[0012] Based on industry research and technical analysis, the current database read optimization faces three major contradictions:

[0013] 1. Performance and cost contradiction: memory acceleration solution is costly, and traditional disk solution is insufficient;

[0014] 2. Static architecture and dynamic demand contradiction: fixed sharding and caching strategies are difficult to adapt to business fluctuations;

[0015] 3. Scalability and consistency contradiction: it is difficult to balance data consistency and horizontal expansion in a distributed environment.

[0016] Therefore, there is an urgent need for a technical solution that can simultaneously optimize cache, index, and query load balancing. SUMMARY

[0017] The purpose of the present application is to solve the problems of low database read efficiency, insufficient cache utilization, and single-point performance bottleneck in the prior art. A database efficient reading method based on multi-level cache optimization and dynamic index sharding is proposed. Through dynamic cache management and distributed index technology, the query speed and system throughput of the database are significantly improved.

[0018] To achieve the above purpose, the present application adopts the following technical solutions:

[0019] A database efficient reading method based on multi-level cache optimization and dynamic index sharding includes the following specific steps:

[0020] S1: Intelligent multi-level cache optimization:

[0021] S11: High-frequency data recognition (dynamic hot spot discovery): Adopt sliding window algorithm (Sliding Window) to real-time statistics of query frequency within 5 minutes, adopt heat calculation algorithm to identify Top-N hot data, finally store the result in Redis ZSET (ordered set), sort by access frequency; The test data shows that compared with the traditional counter hot spot recognition, the accuracy is improved by 35%, the memory occupation is reduced by 40%, and the processing throughput reaches 1.5 million times / sec;

[0022] S12: Dynamic cache loading (adaptive TTL strategy): Set dynamic expiration time (TTL) for hot data, the more frequent the access, the longer the TTL; Adopt write-through (Write-Through) strategy to ensure cache and database consistency; At the same time, use batch pipeline (pipeline) operation, asynchronous non-blocking write and local cache buffer layer to optimize overall performance;

[0023] S13: Cache warm-up (predictive loading): Based on historical access patterns, use time series prediction (such as ARIMA model) to load data that has a probability of being accessed in advance; For example: In e-commerce system, the access peak is from 20:00 to 22:00 every day, so the hot commodity data can be preheated at 19:30;

[0024] S2: Dynamic index sharding (Distributed Index Sharding):

[0025] S21: Sharding rules (consistent hashing algorithm): Consistent Hashing is used to map index shards to different nodes, virtual nodes are used to optimize sharding rules by weight and capacity, and dynamic adjustment of virtual node distribution is used to avoid data skew through hotspot shard detection;

[0026] S22: Distributed query routing (intelligent middleware), specifically including:

[0027] S221: Read-write separation, when distributing read tasks among multiple slave databases, define the load weight of each slave database , and use weighted round robin to distribute tasks;

[0028] S222: Sharding positioning, queries are automatically routed to shards based on request conditions, using consistent hashing algorithm to compare the hash value of the query condition with the shard node hash value;

[0029] S223: Sharding uniformity adjustment, introduce virtual node weight , dynamically adjust shard distribution;

[0030] S224: SQL parsing cache and shard metadata cache, using a hotness-based eviction policy;

[0031] S225: Cross-shard LIMIT optimization, each shard first calculates the candidate row count of its subquery locally, then aggregates all shard results and sorts them, and finally takes the part that meets the LIMIT;

[0032] S226: Distributed transaction processing, using two-phase commit protocol;

[0033] S23: Dynamic scaling (online sharding migration): when adding new nodes, use data rebalancing (Rebalance) algorithm, the process includes target distribution calculation, difference analysis, incremental migration, atomic switching, to ensure only the affected shard data is migrated, avoiding full migration;

[0034] S3: Query load balancing (adaptive scheduling):

[0035] S31: Request distribution strategy: use weighted round robin strategy (Weighted Round Robin): dynamically adjust weight according to node load, and least connections strategy (Least Connections): prefer nodes with the fewest connections;

[0036] S32: Real-time monitoring and elastic expansion and contraction: elastic expansion and contraction includes monitoring alarm triggering, decision engine evaluation, resource orchestration and service registration update; based on Prometheus+Grafana, the CPU utilization, memory occupation and disk I / O delay of each node are monitored, and when the load of a node exceeds a threshold (such as CPU>80%), automatic expansion is triggered.

[0037] As a further technical solution of the application, the S11 specifically comprises:

[0038] S111: Query frequency statistics: set the time window length to 300 seconds (5 minutes), and at the time point t, count the access times of each query within the time window , wherein: is an indication variable, which is 1 when a query occurs at the time point t, and 0 otherwise;

[0039] S112: Hotness calculation: based on query frequency and dynamic weight factor, the hotness value of the query is calculated ,

[0040] wherein: is a dynamic weight factor driven by the access volume in the current time window; is the historical average query frequency; is the access times of each query within the time window;

[0041] S113: Hot data storage: according to the hotness value, the result is stored in the ZSET (ordered set) of Redis, sorted by access frequency, and the score in ZSET is the hotness value: .

[0042] As a further technical solution of the application, the S12 specifically comprises:

[0043] S121: Adaptive TTL setting: dynamically adjust the expiration time (TTL) of the cache according to the access frequency calculated by the sliding window algorithm, the higher the access frequency, the longer the TTL, to avoid performance loss caused by frequent updates; the specific formula is: , wherein: BaseTTL is the default TTL value, is the exponential decay coefficient, is the access frequency, represents the exponential function ;

[0044] S122: Write-through strategy: ensure cache and database consistency, when a write operation occurs, data is first written to the database, when the data in the cache is updated, the corresponding data in the database is also updated to prevent the cache from being invalidated and unable to obtain the latest data;​​

[0045] S123: Batch pipeline operation: Asynchronous, non-blocking processing of batch data operations, improving cache write performance, packaging frequent write operations into batch tasks, and sequentially submitting them to the cache and database asynchronously to reduce the blocking time of single operations. The throughput of batch pipeline operation is: wherein: is the number of cache update operations in batch processing, is the total time (milliseconds) to complete all tasks.

[0046] As a further technical solution of the present application, S13 specifically includes:

[0047] S131: Access frequency prediction: Assuming that the data access frequency changes linearly over time with periodic fluctuations, the data access frequency is approximately represented by the formula wherein: is the average access frequency, is the periodic variation amplitude, is the cycle start time, is the cycle length;

[0048] S132: Load priority calculation: Considering the predicted access frequency and load cost, the load priority formula is defined as wherein: is the predicted access frequency of data ; is the load cost, defined as , covering data size and load complexity ; is the trade-off coefficient;

[0049] S133: Preheat task execution: Start preheating time (such as 30 minutes) before the access peak, gradually load data in the order of calculated priority, ensure that the cache is preheated during the peak period, and improve system response efficiency.

[0050] As a further technical solution of the present application, S21 specifically includes:

[0051] S211: To quantify the uniformity of the shard, a uniformity index is defined to measure the data distribution of the th physical node, and the calculation formula is: wherein: represents the total interval length of all virtual nodes of the th physical node; is the total length of the hash ring; is the number of physical nodes;

[0052] S212: To dynamically adjust the distribution of virtual nodes and avoid data skew, define the adjustment amount. for: ,in: It is the target distribution ratio; the goal of the adjustment is to reduce the total deviation. Minimize, thereby achieving a more balanced data distribution.

[0053] As a further technical solution of the present invention, S22 specifically includes:

[0054] S221: Read / Write Splitting: When distributing read tasks among multiple slave databases, define the load weight for each slave database. The system uses a weighted round-robin method to allocate tasks, and the load balancing formula is as follows: ,in: For the warehouse CPU utilization For the warehouse memory usage and These are weighting coefficients used to reflect the priority between the CPU and memory;

[0055] S222: Shard Location: Queries are automatically routed to shards based on request conditions, using a consistent hashing algorithm to route query conditions. hash value With sharded nodes hash value The comparison shows that the partitioning mapping formula is as follows: ,in: This is the total number of fragments;

[0056] S223: Sharding Uniformity: To dynamically adjust the uniformity of sharding, virtual node weights are introduced. And the uniformity of the segments is dynamically adjusted using a formula: ,in: It is the total hash ring length. It is the weight of the virtual node. It is the first The hash value corresponding to the position of each virtual node on the hash ring;

[0057] S224: SQL Parsing Cache and Sharded Metadata Cache: To improve cache hit rate, a hot-based eviction strategy is adopted. For the SQL parsing cache, the cache eviction priority formula is: ,in: It is a query Access frequency, It refers to the cache dwell time. and is a weight coefficient;

[0058] S225: Cross-shard LIMIT optimization and distributed transactions: For cross-shard LIMIT query optimization, each shard first locally calculates the candidate row number of its subquery, then aggregates the results of all shards and sorts them, and finally takes the part that meets the LIMIT; the global result calculation formula is: ;

[0059] S226: In distributed transaction processing, the two-phase commit (2PC) protocol is adopted, and the transaction success probability is considered as the product of the availability of the nodes The transaction success probability formula is expressed as: , wherein: is the availability of the shard node .

[0060] As a further technical solution of the application, the S23 specifically comprises:

[0061] S231: Target shard distribution calculation: Use the consistent hashing algorithm to determine the data distribution when the new node joins by the weight proportion of the virtual node, and the virtual node hash value calculation formula is random disturbance, wherein: is the virtual node hash value, is the physical node hash value, and are weight coefficients for adjusting the proportion of physical nodes and random disturbance;

[0062] S232: Difference analysis: Compare the difference between the existing shard distribution and the target distribution, and use the difference measurement formula , wherein: is the target shard distribution, is the current shard distribution, represents the distribution difference of each shard node;

[0063] S233: Incremental migration: To efficiently migrate data, design an incremental migration function (difference data volume, ), wherein: is the data volume of the th migration, is the maximum threshold of each migration, used to dynamically adjust the migration volume to balance the migration efficiency and system stability;

[0064] S234: Atomic switching verification: After migration, verify data consistency to ensure successful migration and complete consistency of data, and the verification formula is , if , it indicates that the migration is successful.

[0065] As a further technical solution of the present application, the S31 specifically comprises:

[0066] S311: Weighted round robin assigns weights to each node, and the node with higher weight processes more requests in priority; the specific formula is: the request proportion handled by each node , wherein: is the node weight; at the same time, a dynamic weight adjustment factor is introduced to dynamically adjust the weight according to the node load, and the formula is , wherein: , and are weight coefficients, reflecting the priority of different indicators; and are the CPU utilization and memory occupancy of the node , respectively; is the dynamically adjusted node weight;

[0067] S312: The least connection strategy preferentially distributes requests to the node with the least current connection number, and the formula is: , wherein: is the current connection number of the node; in order to avoid single point overload, the weighted round robin strategy is combined to preferentially select the node with the least connection number in the nodes with similar weights, and the formula is: , wherein: is the average load weight, is the deviation range; in this way, the requests can be reasonably distributed, the resources can be effectively utilized, and the overall performance of the system can be improved.

[0068] As a further technical solution of the present application, in the S32, the elastic expansion and contraction specifically comprises monitoring alarm triggering, decision engine evaluation, resource orchestration and service registration update, which comprises:

[0069] The monitoring alarm triggering calculates the comprehensive load and the load distribution imbalance degree by comprehensively considering the CPU utilization, memory utilization and disk I / O delay indicators, and sets the threshold (triggering expansion), (triggering contraction) and (triggering expansion), (triggering contraction);

[0070] The decision engine evaluation judges whether to perform the expansion and contraction operation through the single node comprehensive load after the monitoring alarm triggering;

[0071] The resource orchestration is based on the system total load , the current node number and the target load balancing point Calculate the number of nodes N that need to be adjusted;

[0072] Service registration updates ensure that newly added nodes are registered after passing health checks, and that scaled-down nodes are removed from the registry after the task migration is complete, maintaining the consistency of the service registry.

[0073] As a further technical solution of the present invention, S32 specifically includes:

[0074] S321: Single-node comprehensive load calculation: ,in: For nodes The overall load, For nodes CPU utilization For nodes memory usage ratio For nodes Disk I / O latency; This is a weighting coefficient that can be dynamically adjusted based on the historical load distribution of the nodes.

[0075] S322: Dynamic weight adjustment:

[0076]

[0077]

[0078]

[0079] in: The fluctuation values ​​of each indicator over a recent period are measured by standard deviation.

[0080] S323: System load distribution imbalance: ,in: For load distribution imbalance, The total number of nodes. For nodes The overall load, The average total load across all nodes;

[0081] S324: Capacity expansion / shrinkage trigger conditions:

[0082] Trigger expansion: If or ;

[0083] Trigger shrinkage: If and ;

[0084] in: Single-node comprehensive load trigger upper and lower threshold values, respectively; Distribution imbalance trigger upper and lower threshold values, respectively;

[0085] S325: Extend node quantity estimation: Wherein: N is the recommended number of newly added nodes, N is the current total number of nodes, N is the total load of the system, N is the target load balancing point.

[0086] The beneficial effects of the present application are:

[0087] 1. Significantly reduce query delay: through multi-level cache optimization technology, store high-frequency accessed data in cache, effectively reduce direct access to database, greatly reduce the frequency of disk I / O operation; at the same time, through dynamic index sharding technology, the database index is divided into multiple parts according to certain rules, and is stored on different nodes to disperse query load and avoid single point bottleneck; under the joint action of these measures, the query response time is significantly shortened, and the user can get the required data faster, improving the overall user experience.

[0088] 2. Improve system throughput: adopt distributed architecture, disperse data processing tasks to multiple nodes, realize parallel processing, and improve system processing capacity; at the same time, through load balancing technology, dynamically distribute query requests to different database nodes to avoid some nodes overload, so that the overall throughput is significantly improved; the combination of this distributed architecture and load balancing technology makes the system can efficiently process high-concurrency requests, meet the performance requirements in large-scale application scenarios.

[0089] 3. Strong dynamic adaptability: dynamic cache management and sharding expansion capability make the system can flexibly cope with changes in data volume and access mode; through real-time monitoring of data access, the system can dynamically adjust the cache strategy, load hot data into cache in time, improve the cache hit rate; at the same time, when the data volume grows or the access mode changes, the system can dynamically adjust the sharding rules to realize the balanced distribution of data, avoid some nodes become performance bottleneck due to too much data; this strong dynamic adaptability makes the system can continuously maintain high performance state, meet the changing application requirements. BRIEF DESCRIPTION OF DRAWINGS

[0090] Figure 1 A flowchart of a database efficient reading method based on multi-level cache optimization and dynamic index sharding proposed by the present application. DETAILED DESCRIPTION

[0091] In order to make the technical means, creative features, purposes and effects of the present application easy to understand, the present application is further described below in conjunction with specific embodiments.

[0092] Please refer to the attached Figure 1 , a database efficient reading method based on multi-level cache optimization and dynamic index fragmentation, comprising the following specific steps:

[0093] S1: Intelligent multi-level cache optimization:

[0094] S11: High-frequency data identification (dynamic hot spot discovery): a sliding window algorithm (Sliding Window) is used to real-time statistics of query frequency in the last 5 minutes, a hotness calculation algorithm is used to identify Top-N hot data, and finally the results are stored in Redis ZSET (ordered set) and sorted by access frequency; The test data shows that compared with the traditional counter hot spot identification, the accuracy is improved by 35%, the memory occupation is reduced by 40%, and the processing throughput reaches 1.5 million times / sec;

[0095] S111: Query frequency statistics: set the time window length to seconds (5 minutes), and at time point , the access times of each query in the time window , wherein: is an indicator variable, which is 1 when a query occurs at time point , otherwise it is 0;

[0096] S112: Hotness calculation: based on query frequency and dynamic weight factor, the hotness value of the query is calculated ,

[0097] wherein: is a dynamic weight factor driven by the access volume in the current time window; is the historical average query frequency; is the access times of each query in the time window;

[0098] S113: Hot data storage: according to the hotness value, the results are stored in Redis ZSET (ordered set) and sorted by access frequency, and the score in ZSET is the hotness value: .

[0099] Through this mechanism, S11 can identify high-frequency data (dynamic hot spots) in real time and store them efficiently to support subsequent cache optimization and query load balancing.

[0100] S12: Dynamic cache loading (adaptive TTL strategy): Set a dynamic expiration time (TTL) for hot data, the more frequent the access, the longer the TTL; use the Write-Through strategy to ensure cache consistency with the database; at the same time, use batch pipeline operations, asynchronous non-blocking writing and local cache buffer layer to optimize overall performance;

[0101] S121: Adaptive TTL setting: dynamically adjust the expiration time (TTL) of the cache according to the access frequency calculated by the sliding window algorithm, the higher the access frequency, the longer the TTL, to avoid performance loss caused by frequent updates; the specific formula is: , where BaseTTL is the default TTL value, is the exponential decay coefficient, is the access frequency, represents the exponential function ;

[0102] S122: Write-Through strategy: ensure cache consistency with the database, when a write operation occurs, data is first written to the database, when the data in the cache is updated, the corresponding data in the database is also updated to prevent cache invalidation from being unable to access the latest data;

[0103] S123: Batch pipeline operation: asynchronous, non-blocking processing of batch data operations to improve cache write performance, packaging frequent write operations into batch processing tasks, and asynchronously submitting them to the cache and database in order to reduce the blocking time of single operations, the throughput of batch pipeline operation is: , where: is the number of cache update operations in batch processing, is the total time (milliseconds) to complete all tasks.

[0104] These strategies work together to optimize the cache loading process and improve system performance and data consistency.

[0105] S13: Cache warming (predictive loading): based on historical access patterns, use time series prediction (such as ARIMA model) to load data that has a probability of being accessed in advance; for example: e-commerce systems have a peak access period from 20:00 to 22:00, so hot commodity data can be warmed up at 19:30;

[0106] S131: Access frequency prediction: assuming that data access frequency changes over time with a linear trend plus periodic fluctuations, data access frequency is approximately represented by the formula , where: is the average access frequency, is the periodic variation amplitude, is the cycle start time, is the cycle length;

[0107] S132: Load priority calculation: considering the predicted access frequency and the load cost, define the load priority formula as , where: is the predicted access frequency of data ; is the load cost, defined as , covering data size and load complexity ; is the trade-off coefficient;

[0108] S133: Pre-warming task execution: start pre-warming 30 minutes before the access peak, load data in the order of calculated priority, ensure that the cache is pre-warmed during the peak period, and improve system response efficiency.

[0109] Through the above steps, intelligent pre-warming of the cache can be achieved, and system performance can be optimized.

[0110] S2: Dynamic index sharding (Distributed Index Sharding):

[0111] S21: Sharding rules (Consistent Hashing algorithm): use consistent hashing to map index shards to different nodes, use virtual nodes to optimize sharding rules by weight and capacity, dynamically adjust virtual node distribution through hot shard detection, and avoid data skew;

[0112] The consistent hashing algorithm maps data and nodes to a virtual ring to achieve uniform distribution of data and dynamic addition and deletion of nodes. Specifically, each physical node creates multiple virtual nodes, which are evenly distributed on the hash ring, ensuring that data is evenly distributed to each physical node;

[0113] S211: To quantify the uniformity of the shard, define a uniformity index , which measures the data distribution of the th physical node, and the calculation formula is: , where: represents the total interval length of all virtual nodes of the th physical node; is the total length of the hash ring; is the number of physical nodes;

[0114] S212: To dynamically adjust the virtual node distribution to avoid data skew, define the adjustment amount as: ,in: It is the target distribution ratio; the goal of the adjustment is to reduce the total deviation. Minimize, thereby achieving a more balanced data distribution.

[0115] S22: Distributed Query Routing (Intelligent Middleware): Develop a lightweight query routing middleware that supports read / write separation: write requests are sent to the master database, and read requests are balanced to the slave databases; supports shard location: automatically routes to the corresponding shard based on query conditions; optimizes through SQL parsing caching, local shard metadata caching, and batch query merging; supports cross-shard LIMIT optimization and distributed transactions.

[0116] S221: Read / Write Splitting: When distributing read tasks among multiple slave databases, define the load weight for each slave database. The system uses a weighted round-robin method to allocate tasks, and the load balancing formula is as follows: ,in: For the warehouse CPU utilization For the warehouse memory usage and These are weighting coefficients used to reflect the priority between the CPU and memory;

[0117] S222: Shard Location: Queries are automatically routed to shards based on request conditions, using a consistent hashing algorithm to route query conditions. hash value With sharded nodes hash value The comparison shows that the partitioning mapping formula is as follows: ,in: This is the total number of fragments;

[0118] S223: Sharding Uniformity: To dynamically adjust the uniformity of sharding, virtual node weights are introduced. And the uniformity of the segments is dynamically adjusted using a formula: ,in: It is the total hash ring length. It is the weight of the virtual node. It is the first The hash value corresponding to the position of each virtual node on the hash ring;

[0119] S224: SQL Parsing Cache and Sharded Metadata Cache: To improve cache hit rate, a hot-based eviction strategy is adopted. For the SQL parsing cache, the cache eviction priority formula is: ,in: It is a query Access frequency, It refers to the cache dwell time. and are weight coefficients;

[0120] S225: Cross-shard LIMIT optimization and distributed transactions: For cross-shard LIMIT query optimization, each shard first locally calculates the candidate row number of its subquery, then aggregates the results of all shards and sorts them, and finally takes the part that meets the LIMIT; the global result calculation formula is: ;

[0121] S226: In distributed transaction processing, the two-phase commit (2PC) protocol is adopted, and the transaction success probability is considered as the product of the availability of the nodes The transaction success probability formula is expressed as: , where: is the availability of the shard node .

[0122] S23: Dynamic scaling (online shard migration): When a new node is added, the data rebalancing (Rebalance) algorithm is used, and the process includes target distribution calculation, difference analysis, incremental migration, and atomic switching, which ensures that only the affected shard data is migrated and avoids full migration;

[0123] S231: Target shard distribution calculation: Using the consistent hashing algorithm, the data distribution when a new node is added is determined by the weight proportion of the virtual node, and the virtual node hash value calculation formula is random disturbance, where: is the virtual node hash value, is the physical node hash value, and are weight coefficients, used to adjust the proportion of physical nodes and random disturbance;

[0124] S232: Difference analysis: Compare the difference between the existing shard distribution and the target distribution using the difference measurement formula , where: is the target shard distribution, is the current shard distribution, represents the distribution difference of each shard node;

[0125] S233: Incremental migration: To efficiently migrate data, an incremental migration function (difference data volume, ) is designed, where: is the data volume of the th migration, is the maximum threshold for each migration, used to dynamically adjust the migration volume to balance migration efficiency and system stability;

[0126] S234: Atomic switch verification: After migration, verify data consistency to ensure successful migration and complete consistency, and the verification formula is , if , it means that the migration is successful.

[0127] The above steps collectively ensure uniform distribution of sharded data, efficient migration, and data consistency during dynamic scaling, achieving smooth transition of online sharding migration.

[0128] S3: Query load balancing (adaptive scheduling):

[0129] S31: Request distribution strategy: Weighted Round Robin strategy is adopted: dynamically adjust the weight according to node load, and Least Connections strategy: prefer to select the node with the least number of current connections;

[0130] S311: Weighted Round Robin assigns weights to each node, and nodes with higher weights are given priority to handle more requests; the specific formula is: the request proportion handled by each node , where: is the node weight; at the same time, a dynamic weight adjustment factor is introduced to dynamically adjust the weight according to the node load, and the formula is , where: , and are weight coefficients reflecting the priority of different indicators; and are the CPU utilization and memory occupancy rate of the node , respectively; is the dynamically adjusted node weight;

[0131] S312: The Least Connections strategy preferentially distributes requests to the node with the least number of current connections, and the formula is: , where: is the current number of connections of the node; to avoid single point overload, combined with the Weighted Round Robin strategy, the node with the least number of connections is selected among the nodes with similar weights, and the formula is: , where: is the average load weight, is the deviation range; in this way, the requests can be reasonably distributed, and the resources can be effectively utilized to improve the overall performance of the system.

[0132] S32: Real-time monitoring and elastic expansion and contraction: Elastic expansion and contraction includes monitoring alarm triggering, decision engine evaluation, resource orchestration, and service registration update; based on Prometheus+Grafana, the CPU utilization, memory occupation, and disk I / O delay of each node are monitored, and when the load of a certain node exceeds the threshold (such as CPU > 80%), automatic expansion is triggered;

[0133] Monitoring alarm triggering calculates the comprehensive load by comprehensively considering CPU utilization, memory utilization, and disk I / O delay and load imbalance , and sets thresholds (triggering expansion), (triggering contraction), and (triggering expansion), (triggering contraction);

[0134] Decision engine evaluation determines whether to perform expansion and contraction operations based on the comprehensive load of a single node after monitoring alarm triggering;

[0135] Resource orchestration calculates the number of nodes N that need to be adjusted according to the total load of the system , the current number of nodes , and the target load balancing point ;

[0136] Service registration update ensures that newly added nodes are registered after passing the health check, and that the nodes that are contracted are removed from the registration table after the task is completely migrated, maintaining the consistency of the service registration table;

[0137] S321: Single-node comprehensive load calculation: , where: is the comprehensive load of node , is the CPU utilization of node , is the memory occupation ratio of node , is the disk I / O delay of node ; is the weight coefficient, which can be dynamically adjusted according to the historical load distribution of the node;

[0138] S322: Dynamic weight adjustment:

[0139]

[0140]

[0141]

[0142] , where: The fluctuation value of each index in the recent period is measured by the standard deviation;

[0143] S323: System load imbalance degree: Wherein: is the load imbalance degree, is the total number of nodes, is the node comprehensive load, is the average comprehensive load of all nodes;

[0144] S324: Scaling trigger condition:

[0145] Trigger scaling up: if Or ;

[0146] Trigger scaling down: if And ;

[0147] Wherein: are the upper and lower thresholds of the single-node comprehensive load trigger, respectively; are the upper and lower thresholds of the distribution imbalance trigger, respectively;

[0148] S325: Estimate the number of extended nodes: Wherein: is the number of recommended new nodes, is the current total number of nodes, is the total load of the system, is the target load balancing point.

[0149] The above steps can help monitor the system load in real time, and automatically trigger the scaling operation according to the preset threshold and calculation result, so as to maintain the balance of system performance and resource utilization.

[0150] From the above description, it can be seen that the above-mentioned embodiments of the present application achieve the following technical effects: significantly reduce the query delay: through the multi-level cache optimization technology, the high-frequency accessed data is stored in the cache, which effectively reduces the direct access to the database, thereby greatly reducing the frequency of disk I / O operation; at the same time, through the dynamic index sharding technology, the database index is divided into multiple parts according to certain rules and stored on different nodes to disperse the query load and avoid the occurrence of single-point bottleneck; under the joint action of these measures, the query response time is significantly shortened, the user can get the required data faster, and the overall user experience is improved.

[0151] Improving system throughput: a distributed architecture is adopted, and data processing tasks are dispersed to multiple nodes to realize parallel processing, thereby improving the processing capacity of the system; at the same time, through load balancing technology, query requests are dynamically distributed to different database nodes, avoiding the situation that some nodes are overloaded, so that the overall throughput is significantly improved; the combination of this distributed architecture and load balancing technology enables the system to efficiently process high-concurrency requests and meet the performance requirements in large-scale application scenarios.

[0152] Strong dynamic adaptability: dynamic cache management and sharding expansion capability enable the system to flexibly cope with changes in data volume and access patterns; by monitoring data access in real time, the system can dynamically adjust the cache strategy and load hot data into the cache in a timely manner to improve cache hit rate; at the same time, when the data volume grows or the access pattern changes, the system can dynamically adjust the sharding rules to achieve balanced distribution of data and avoid some nodes becoming performance bottlenecks due to excessive data; this strong dynamic adaptability enables the system to maintain high performance continuously and meet the changing application requirements.

[0153] Those skilled in the art should understand that the above discussion of any embodiment is only exemplary and is not intended to suggest that the scope of the present application is limited to these examples; under the idea of the present application, the technical features in the above embodiments or different embodiments can also be combined, the steps can be implemented in any order, and there are many other changes of different aspects of the present application as described above. In order to be brief, they are not provided in details.

[0154] The present application is intended to cover all such alternatives, modifications and variations as fall within the broad scope of the specification. Therefore, any omission, modification, equivalent replacement, improvement, etc. made in the spirit and principle of the present application shall be included in the protection scope of the present application.

Claims

1. A method for efficient read of database based on multi-level cache optimization and dynamic index sharding, characterized in that, The method comprises the following specific steps: S1: intelligent multi-level cache optimization: S11: high-frequency data identification: using a sliding window algorithm to calculate the query frequency in the last 5 minutes in real time, using a heat calculation algorithm to identify Top-N hot data, and finally storing the results in Redis ZSET and sorting by access frequency; S12: dynamic cache loading: setting a dynamic expiration time for hot data; using a write-through strategy to ensure cache consistency with the database; using batch pipeline operations, asynchronous non-blocking writing, and local cache buffer layer optimization to improve overall performance; S13: cache preheating: based on historical access patterns, using time series prediction to load data that is likely to be accessed in advance; S2: dynamic index sharding: S21: sharding rules: using consistent hashing to map index shards to different nodes, using virtual nodes to optimize sharding rules by weight and capacity, and dynamically adjusting virtual node distribution through hot shard detection; S22: distributed query routing, specifically including: S221: read-write separation, when distributing read tasks among multiple slave libraries, define the load weight of each slave library And adopt the method of weighted round robin to distribute tasks; S222: Sharding positioning, querying is automatically routed to shards according to request conditions, using consistent hashing algorithm, comparing hash value of query condition with shard node hash value ; S223: Slice uniformity adjustment, introduce virtual node weight , dynamically adjust slice distribution; S224: SQL parsing cache and shard metadata cache, using a heat-based eviction strategy; S225: cross-shard LIMIT optimization, calculating the candidate row number of each shard's subquery locally, then aggregating the results of all shards and sorting them, and finally taking the part that meets the LIMIT; S226: distributed transaction processing, using a two-phase commit protocol; S23: dynamic scaling: when adding new nodes, using a data rebalancing algorithm, the process includes target distribution calculation, difference analysis, incremental migration, and atomic switching; S3: query load balancing: S31: request distribution strategy: using a weighted round-robin strategy: dynamically adjusting weights based on node load, and a least connection strategy: preferentially selecting nodes with the fewest current connections; S32: real-time monitoring and elastic scaling: elastic scaling includes monitoring alarm triggering, decision engine evaluation, resource orchestration, and service registration update; based on Prometheus+Grafana, monitoring each node's CPU utilization, memory usage, and disk I / O latency, and automatically triggering scaling when a node's load exceeds a threshold.

2. The method of claim 1, wherein, The S11 specifically includes: S111: Query frequency statistics: set the time window length to seconds, at time point , count the access times of each query within the time window , where: is an indicator variable, which is 1 when the query occurs at time point , and 0 otherwise; S112: Hotness calculation: based on the query frequency and the dynamic weight factor, the hotness value of the query is calculated , wherein: is a dynamic weight factor driven by the volume of accesses within the current time window; is the historical average query frequency; is the number of accesses for each query within the time window; S113: Hotspot data storage: according to the hotness value, the result is stored in the ZSET of Redis, sorted by access frequency, and the score in the ZSET is the hotness value: .

3. The method of claim 2, wherein, The S12 specifically includes: S121: adaptive TTL setting: dynamically adjust the expiration time of the cache according to the access frequency calculated by the sliding window algorithm, the higher the access frequency, the longer the TTL; the specific formula is: , wherein: BaseTTL is the default TTL value, is the exponential decay coefficient, is the access frequency, represents the exponential function ; S122: write-through strategy: when a write operation occurs, data is first written to the database, and when the data in the cache is updated, the corresponding data in the database is also updated; S123: Batch pipeline operation: Asynchronous, non-blocking processing of batch data operations, packing frequent write operations into batch tasks, and sequentially submitting them to the cache and the database asynchronously. The throughput of the batch pipeline operation is: wherein: is the number of cache update operations of batch processing, is the total time consumption for completing all tasks.

4. The method of claim 3, wherein, The S13 specifically includes: S131: Access frequency prediction: Assuming that the data access frequency varies with time as a linear trend plus periodic fluctuations, the data access frequency is approximated by the formula where: is the average access frequency, is the periodic variation amplitude, is the periodic start time, is the periodic length; S132: Load priority calculation: considering the predicted access frequency and the load cost, the load priority formula is defined as wherein: is the predicted access frequency of data ; is the load cost, defined as , covering the data size and the load complexity ; is the trade-off coefficient; S133: Pre-warm task execution: before access peak Pre-warming starts at a time and loads data in the order of the calculated priority.

5. The method of claim 1, wherein, The S21 specifically includes: S211: In order to quantify the uniformity of the shards, a uniformity index is defined , which is used to measure the data distribution of the th physical node, and the calculation formula is: , wherein: represents the total interval length of all virtual nodes of the th physical node; is the total length of the hash ring; is the number of physical nodes; S212: To dynamically adjust the virtual node distribution, define the adjustment amount is: where: is the target distribution ratio; the goal of adjustment is to make the total deviation minimized.

6. The method of claim 5, wherein, The S22 specifically includes: S221: read-write separation: when distributing read tasks among multiple slave databases, define the load weight of each slave database , and use weighted round robin method to distribute tasks, load balancing formula: , wherein: is the CPU occupancy rate of the slave database , is the memory occupancy rate of the slave database , and are weight coefficients, which are used to reflect the priority between CPU and memory; S222: Shard Location: Queries are automatically routed to shards based on request conditions, using a consistent hashing algorithm to route query conditions. hash value With sharded nodes hash value The comparison shows that the partitioning mapping formula is as follows: ,in: This is the total number of fragments; S223: Sharding uniformity: To dynamically adjust the uniformity of sharding, introduce virtual node weights , and dynamically adjust the uniformity of sharding through the formula: , where: is the total hash ring length, is the weight of the virtual node, is the hash value corresponding to the position of the th virtual node on the hash ring; S224: SQL Parsing Cache and Sharded Metadata Cache: To improve cache hit rate, a hot-based eviction strategy is adopted. For the SQL parsing cache, the cache eviction priority formula is: ,in: It is a query Access frequency, It refers to the cache dwell time. and These are weighting coefficients; S225: Cross-shard LIMIT optimization and distributed transactions: For cross-shard LIMIT query optimization, each shard first locally calculates the candidate row number of its subquery, then aggregates the results of all shards and sorts them, and finally takes the part that meets the LIMIT; the global result calculation formula is: global result ; S226: In the distributed transaction processing, the two-phase commit protocol is adopted, and the transaction success probability is regarded as the availability of the node The transaction success probability formula is expressed as: wherein: is the availability of the sharded node .

7. The method of claim 6, wherein, The S23 specifically includes: S231: target shard distribution calculation: using a consistent hashing algorithm, the data distribution when the new node joins is determined by the weight proportion of the virtual node, the virtual node hash value calculation formula is Random perturbation, wherein: is the virtual node hash value, is the physical node hash value, and is the weight coefficient, used to adjust the proportion of the physical node and the random perturbation; S232: Difference analysis: compare the difference between the existing shard distribution and the target distribution, using the difference measure formula wherein: is the target shard distribution, is the current shard distribution, represents the distribution difference of each shard node; S233: Incremental migration: design an incremental migration function for efficient migration of data (difference data volume, ), wherein: is the data volume of the th migration, is the maximum threshold value of each migration; S234: Atomic switch verification: After migration, verify data consistency, the verification formula is , if , it means that the migration is successful.

8. The method of claim 1, wherein, The S31 specifically includes: S311: Weighted round robin assigns weights to each node, and the node with higher weight is given priority to handle more requests; the specific formula is: the request proportion handled by each node wherein: is the node weight; at the same time, a dynamic weight adjustment factor is introduced, which dynamically adjusts the weight according to the node load, and the formula is wherein: , and are weight coefficients, reflecting the priority of different indicators; and are the CPU utilization and memory occupancy of the node , respectively; is the dynamically adjusted node weight; S312: The least connection strategy preferentially distributes the request to the node with the least current connection number, and the formula is: Wherein: is the current connection number of the node; to avoid single point overload, the weighted round robin strategy is combined to preferentially select the node with the least connection number in the nodes with similar weights, and the formula is: Wherein: is the average load weight, is the deviation range.

9. The method of claim 8, wherein, In the S32, elastic scaling includes monitoring alarm triggering, decision engine evaluation, resource orchestration, and service registration update specifically including: The monitoring alarm trigger calculates the comprehensive load by comprehensively considering CPU utilization, memory utilization and disk I / O delay index and load imbalance degree , and sets a threshold , and , ; Decision engine evaluation then, after monitoring alarm triggering, determines whether to perform scaling operations based on the comprehensive load of a single node; The resource orchestration calculates the number of nodes N that need to be adjusted according to the total load of the system , the current number of nodes and the target load balancing point . Service registration update ensures that newly added nodes are registered after passing health checks, and that nodes being scaled down are removed from the registration table after the migration is complete, maintaining the consistency of the service registration table.

10. The method of claim 9, wherein, The S32 specifically includes: S321: Single-node comprehensive load calculation: wherein: is the comprehensive load of the node is the CPU utilization of the node is the memory occupancy ratio of the node is the disk I / O delay of the node is a weight coefficient, which can be dynamically adjusted according to the historical load distribution of the node;​​​​ S322: dynamic weight adjustment: wherein: is the fluctuation value of each index in the recent period, measured by the standard deviation; S323: System load distribution imbalance: ,in: For load distribution imbalance, The total number of nodes. For nodes The overall load, The average total load across all nodes; S324: scaling trigger conditions: Triggered scaling: If or ; Triggered condensing: if And ; wherein: respectively, are single-node aggregate load trigger upper and lower thresholds; respectively, are distribution imbalance trigger upper and lower thresholds; S325: Estimate the number of extended nodes: where: is the number of suggested new nodes, is the current total number of nodes, is the total load of the system, is the target load balancing point.

Images