SQL query result quick return method based on distributed cache preloading

Abstract

The invention relates to the technical field of database optimization, in particular to a distributed cache preloading-based SQL query result quick return method, which comprises the following steps of: performing parameter abstraction on an SQL to generate a key prefix; judging SQL fingerprint hotspots, marking preloaded objects, and writing the preloaded objects into a hotspot list; generating a preloading task by utilizing the hotspot list; designing cache and storage; loading the result set in a fragmentation manner, splitting the result set into a plurality of fragmentation tasks, and concurrently executing pre-computation by using threads; adding data consistency metadata when the cache is written; splicing cache keys, and querying cache and storage hit conditions; providing a cache failure and avalanche prevention mechanism of the step random TTL; and the gateway layer exposes cache system design and performance monitoring indexes, and triggers an alarm by using the cache system design or performance monitoring. According to the method, the problems of high passive back-to-source delay, inaccurate hotspot identification, poor data consistency, large result set loading bottleneck, cache avalanche and the like in the existing distributed cache technology are solved.

Description

Technical Field

[0001] This invention relates to the field of database optimization technology, and in particular to a method for quickly returning SQL query results based on distributed cache preloading. Background Technology

[0002] Database systems often experience slow query response and connection pool exhaustion when faced with sudden surges in traffic. Traditional optimization methods, such as adding indexes, upgrading hardware, or read / write separation, can alleviate the pressure to some extent, but they cannot fundamentally solve the latency bottleneck caused by repeated disk accesses from hot queries.

[0003] To reduce database load, a distributed caching architecture is introduced to cache hot data in memory. However, a passive caching mode of "on-demand origin retrieval" is often adopted, which means that the SQL query is executed and the result is written to the cache only when the user request fails. This mode can cause a large number of requests to return to the origin at the same time during a surge in traffic, resulting in "cache breakdown" and "avalanche" phenomena. The overall response time is still difficult to meet the millisecond requirement.

[0004] In addition, passive caching is poorly adaptable to data update scenarios: once the underlying data changes, the cache can only passively wait for the next request to trigger a return to the origin, causing the front-end business to read expired data, affecting the consistency experience; some solutions alleviate this by setting a short TTL or actively invalidating, but frequent returns to the origin will increase the pressure on the database and form a new performance bottleneck. Some studies have proposed preloading or asynchronous refresh strategies, but they generally have the following shortcomings: First, they lack a precise identification mechanism for hot SQL queries, leading to a waste of cache resources; second, the preloading task has low coupling with data change events, resulting in high update latency; third, when a single node loads a large result set, CPU and bandwidth can easily become bottlenecks, and there is a lack of distributed sharding parallel solutions; fourth, the cache layering design is simple and cannot take into account both sub-millisecond access to extremely hot data and low-cost storage of cold data. Summary of the Invention

[0005] To address the shortcomings of existing methods, this invention aims to solve problems such as high latency in passive origin retrieval, inaccurate hotspot identification, poor data consistency, bottlenecks in loading large result sets, and cache avalanche in existing distributed caching technologies. Through a nine-step closed-loop process of "proxy gateway capture → hotspot determination → dual-drive preloading → three-level cache hotspots → sharded parallel computing → version signature consistency → tiered TTL expiration → query short-circuit origin retrieval → full-link observability", it achieves millisecond-level query response, significantly reduced database load, and zero intrusion into business code.

[0006] The technical solution adopted in this invention is: a method for quickly returning SQL query results based on distributed cache preloading, comprising the following steps: Step 1: Abstract the SQL parameters and generate key prefixes; In a preferred embodiment of the present invention, the database access proxy gateway layer abstracts the parameters of incoming and outgoing SQL, generates a key prefix composed of SQL fingerprint and parameter template, and performs preprocessing on the SQL, including literal value replacement, expression normalization, and non-cacheable feature filtering.

[0007] Step 2: Determine SQL fingerprint hotspots, mark preloaded objects, and write them to the hotspot list; In a preferred embodiment of the present invention, when the query frequency (QPS), average response time (RT), and traffic (Bytes) are all higher than the threshold, the SQL fingerprint is marked as a preloaded object.

[0008] Step 3: Generate preloaded tasks using the hotspot list; For the SQL queries in the hotspot list, generate a preloading task based on the SQL parameter template; In a preferred embodiment of the present invention, the preloading task adopts time-driven and event-driven approaches.

[0009] In a preferred embodiment of the present invention, time-driven operation involves periodically performing full or incremental pre-calculations according to a cron expression using a timer scheduler.

[0010] In a preferred embodiment of the present invention, the event-driven approach captures data change events related to SQL fingerprints by subscribing to the database binary log (binlog) in real time, triggers asynchronous reruns of SQL, and updates the cache.

[0011] Step 4: Design caching and storage; Design a local heap cache L1, a distributed memory cache L2, and a persistent key-value store L3; Step 5: Load the result set in chunks, splitting the result set into several chunk tasks and using threads to perform pre-computation concurrently; In a preferred embodiment of the present invention, the result set is split into sharding tasks based on the SQL partition key, the sharding strategy is determined according to whether the hot SQL has a partition key, and the sharding results are cached independently.

[0012] Step 6: Attach data consistency metadata when writing to the cache; In a preferred embodiment of the present invention, the metadata includes: version number, data signature, and expiration time (expire_at).

[0013] Step 7: Assemble the cache key and check the cache and storage hit status; In a preferred embodiment of the present invention, after the query request reaches the gateway, a cache key is assembled. When any one of the L1, L2, or L3 layers is hit, the result is returned. When none of the three layers are hit, a distributed lock is used to ensure that the same key is returned to the source database only once, and other threads are woken up after the result is written to the cache.

[0014] Step 8: Provide a cache invalidation and avalanche prevention mechanism with tiered random TTL; Step 9: Expose the cache system design and performance monitoring metrics at the gateway layer, and trigger alarms using the cache system design or performance monitoring. As a preferred embodiment of the present invention, triggering an alarm includes: triggering an alarm when the hit rate is lower than a threshold or the preloading delay is higher than a threshold.

[0015] The beneficial effects of this invention are: 1. The method of this invention is a novel distributed cache preloading system that can proactively detect hotspots, pre-calculate and cache in layers, and simultaneously ensure data consistency and high availability. 2. Query response time has been reduced from hundreds of milliseconds to sub-tens of milliseconds. Through the closed loop of "proxy gateway capture → hotspot determination → sharded parallel preloading → L1 / L2 / L3 three-level hot zones" in steps one to five, hot SQL results are calculated and cached in the Caffeine heap memory closest to the compute node before the user request arrives. In a real-world test scenario of 30,000 QPS, the P99 latency has been reduced from 120 ms to 8 ms, and the cache hit rate has remained stable at over 93%. 3. Data update to cache refresh latency <3 seconds, consistency is verifiable. The binlog is consumed in real time using the "event-driven" branch in step three to trigger the recalculation of the associated SQL fingerprint. Combined with the "version number + CRC32 signature" double verification in step six, it is ensured that only new version data will overwrite the old cache. Online tracking shows that the average latency from order status change to cache effect is 2.1 seconds, and the signature conflict rate is <0.02%, achieving verifiable linear consistency. 4. Large result set loading bandwidth usage decreased by 60%, with no risk of cache avalanche; Step 5 split the result set into 64 fragments by partition key and loaded them concurrently using 64-way coroutines, with each fragment being <1 MB; Step 8's "ladder random TTL" was overlaid to distribute the expiration time between 0-300 s, avoiding instantaneous failure; During stress testing, the full preloading time of the 64 GB large table was shortened from 190 s to 75 s, and the peak network bandwidth decreased from 1.8 Gbps to 0.7 Gbps, with no DB spikes caused by cache avalanche. Attached Figure Description

[0016] Figure 1 This is a flowchart of the method for quickly returning SQL query results based on distributed cache preloading according to the present invention. Detailed Implementation

[0017] The present invention will be further described below with reference to the accompanying drawings and embodiments. The drawings are simplified schematic diagrams, which only illustrate the basic structure of the present invention in a schematic manner, and therefore only show the components related to the present invention.

[0018] like Figure 1 As shown, a method for quickly returning SQL query results based on distributed cache preloading includes the following steps: Step 1: Database access proxy gateway layer, captures and parses all incoming and outgoing SQL statements in real time, abstracts parameters from the SQL text, and generates a key prefix composed of SQL fingerprint and parameter template; Deploy a non-intrusive database access proxy gateway (ShardingSphere-Proxy or self-developed Sidecar) between the application and the database, and capture SQL text in real time through a protocol parsing module; perform standardization processing on the SQL: 1. Replace the literal values ​​in the WHERE condition with placeholders "?", for example, `status=1` → `status=?`; b) Normalize expressions with an IN list length greater than 4 to "IN(?)" to avoid parameter combination explosion; c) Filter out SQL statements containing non-cacheable features such as random functions, now(), and temporary tables; Calculate the MD5 fingerprint of the standardized SQL, concatenate the parameter template to generate the key prefix `{fp}:{param_tpl}`, and initialize three atomic counters: query frequency (QPS), average response time (RT), and result set size (Bytes) for subsequent hot spot determination. The parameter template consists of variable conditions in SQL, mainly including: range conditions and enumeration conditions; range conditions refer to query conditions involving time or numerical ranges, such as gmt_create >= ?, which the system will automatically specify as business-related time windows such as "the last hour" during preloading; enumeration conditions refer to discrete value conditions such as status = ? or type IN(?), which the system will obtain all valid enumeration values ​​of the field from the dictionary table for combination generation.

[0019] Step 2: Determine hotspots for SQL fingerprints by calculating the query frequency (QPS), average response time (RT), and traffic (Bytes) per unit time. When the QPS exceeds the preset first threshold, the RT exceeds the preset second threshold, and the Bytes exceeds the preset third threshold, mark the SQL fingerprint as a preloaded object and write it into the hotspot list. A 10-second sliding window is used for statistics, accumulating QPS, RT, and Bytes at the bucket level within the window. Hotspot determination formula: (QPS>first threshold α)∩(RT>second threshold β)∩(Bytes<third threshold γ) Among them, α is preferred at 200, β at 50 ms, and γ at 2 MB, and can be dynamically pushed through the configuration center; after the judgment is passed, the SQL fingerprint is written to the ZooKeeper node ` / hotsql / list / {fp}` for the preload scheduler to listen for, so as to prevent duplicate calculation; Step 3: For each SQL statement in the hotspot list, generate a preloading task based on its parameter template. The preloading task adopts a dual-drive mechanism of "time + event". Time-driven: Full or incremental pre-computation is performed periodically according to cron expressions using a timer scheduler; Using Quartz cluster mode, a cron expression is generated for each hot fingerprint (executed every 5 minutes by default); the scheduler automatically generates a parameter space based on the parameter template, with no more than 1024 parameter combinations; for range conditions, the most recent 1 hour is taken, and for enumeration conditions, all enumeration values ​​in the business dictionary table are taken, and the Cartesian product is used to generate ≤1024 parameter combinations, which are serialized into JSON messages and delivered to the Kafka Topic `preload-time-{fp}`, with 64 partitions to ensure that the same seller_id falls in the same partition and is consumed sequentially. Event-driven: By subscribing to the database binary log (binlog) in real time, data change events related to SQL fingerprints are captured, triggering asynchronous reruns of the SQL and updating the cache; Subscribe to binlog via Debezium or Canal, filtering event types to Write / Update / Delete; maintain an inverted index for "table → SQL fingerprint" in memory, and immediately trigger all associated `{fp}` preload tasks when a related table update is captured; use token bucket rate limiting: a maximum of 200 preloads per second, and retry after a 1-second delay if the limit is exceeded. Preferably, event-driven preloading also includes: using Kafka or Pulsar to stream consume binlog and writing it in the order of table-level partitions to ensure the order and consistency of change events for the same primary key.

[0020] Step 4: Design the three-level cache hotspots; L1 local heap cache, deployed within the application process, is implemented using Caffeine or Guava and caches the hottest result sets within the last minute; A single-node Caffeine instance has a maximum capacity of 256 MB and expires 1 minute after being written; it employs a W-TinyLFU strategy to guarantee a hit rate of >90%. L2 distributed memory cache, using Redis Cluster or KeyDB multi-threaded cluster, caches result sets from the most recent 30 minutes to 2 hours; Redis Cluster has 3 masters and 3 slaves, with each shard being 16 GB. HashTag is enabled to route the same `{fp}` to the same slot, and multi-key pipeline writes are supported. L3 persistent key-value storage, using RocksDB or object storage, caches daily cold backup results; Using RocksDB local disks, 2 TB per instance, the write format is `key=hot:{fp}:{shard}:{version}`, value=Snappy compressed Protobuf byte stream; The write order strictly follows the chained write-back of "DB transaction commit → binlog event → L3 → L2 → L1". If any step fails, a rollback will be performed and the failure Audit log will be recorded for alerts and compensation.

[0021] Preferably, the three-level cache hot zone supports elastic scaling: when the L2 Redis cluster memory usage exceeds 80%, cold data is automatically moved down to L3 RocksDB and the cache routing table is updated to achieve dynamic migration of hot, warm and cold data.

[0022] Step 5: Perform parallel loading of the result set in shards: Split the result set into N shard tasks based on the SQL partition key, and perform pre-computation concurrently using multi-threading or coroutines. Each shard result is cached independently, and the cache key format is: hot:{sql_md5}:{shard_index}:{version}; The parallel loading of shards also includes: dynamically adjusting the number of shards N based on the modulo 64 of the business partition key seller_id or user_id, so that the result set of a single shard is less than 1MB, thus avoiding network and serialization bottlenecks; The sharding strategy is determined based on whether the frequently used SQL queries have a partition key: 1. If it contains `seller_id` and the table has been sharded and partitioned by `seller_id`, then use `seller_id % 64`; 2. If there is no obvious partition key, divide the range of the primary key `order_id` into 64 equal segments; The preloaded thread pool has a core thread count equal to twice the number of CPU cores, a queue length of 5000, and a rejection policy of "caller runs" to prevent memory exhaustion. The execution flow for each task shard is as follows: 1. Assemble SQL statements with sharding conditions; 2. Use `SELECT ... LIMIT 10000` + covering index on the database side to avoid table lookups; 3. Serialize the result set into Protobuf and calculate the row-level CRC32 signature; 4. When writing to the cache, include `version=transaction snapshot version`, `sign=CRC32`, and `expire=1800+rand(300)s`; After all shards are completed, a completion flag is written to the ZooKeeper node ` / hotsql / ready / {fp}`. The gateway layer listens and switches to the new version cache to achieve second-level canary deployment. Step 6: When writing to the cache, attach data consistency metadata, including: version number, taken from the database transaction timestamp or a globally incrementing sequence; data signature, generated by the joint hash of the result set row content and the version number; and expiration time, expire_at, which uses the base TTL plus a random perturbation of 0 to 300 seconds. Preferably, the random perturbation TTL is generated by taking the modulo of sql_md5 using a hash function to generate a random value of 0 to 300 seconds, which is then superimposed on the basic 30-minute TTL to achieve a uniform distribution of cache expiration time; Version number generation: uses database transaction snapshot sequence number or Snowflake millisecond timestamp, globally incremented; Data signature: For each row of the result set, concatenate the primary key, all column values, and version number, calculate CRC32, and sum the results from multiple rows to obtain the final sign; The query carries the `min_version` parameter. The gateway compares the cached `version>=min_version` before returning the result; otherwise, it triggers an asynchronous origin retrieval. When a new version is brought up by a binlog event, the old version cache is deleted first and then the new version is written. The deletion and writing are performed using Lua scripts to ensure atomicity.

[0023] Step 7: After the query request reaches the gateway, the cache key is assembled according to the same rules as preloading, and L1, L2, and L3 are queried in sequence. If any level is hit, the result is returned and asynchronously filled back into the next level cache. If none of the three levels are hit, a distributed lock is used to ensure that the same key is retrieved from the source database only once, and other threads are woken up after the result is written to the cache. The rule of preloading means that when a query request reaches the gateway, the complete logic used to generate cache keys throughout the preloading process must be reused. This logic begins in step one by performing literal replacement, expression normalization, and filtering of non-cacheable features on the original SQL to generate a standardized key prefix composed of SQL fingerprints and parameter templates. In steps five and six, the partition key (such as seller_id) is further combined to perform sharding, generating an independent cache key for each shard in the format hot:{sql_md5}:{shard_index}:{version}, while attaching consistent metadata such as version number and data signature. Only by strictly following this set of rules to assemble the cache key can the query path be accurately aligned with the preloaded path in the L1 / L2 / L3 three-level cache, ensuring that the preloaded results are hit and avoiding cache invalidation or duplicate origin requests due to inconsistent keys.

[0024] Other threads refer to threads that failed to acquire the distributed lock in concurrent requests, as opposed to the first thread that successfully acquired the distributed lock for the cache key. When multiple requests simultaneously find that a cache key is not found, the system will attempt to acquire a distributed lock for that key, allowing only one thread (the first thread to successfully acquire the lock) to execute the complete process of returning to the origin database, processing the result, and writing it to the cache. The remaining threads are blocked or enter a waiting state, not repeatedly querying the database, but waiting to be awakened after the first thread completes the cache write and directly read the result from the cache. This mechanism ensures that the same cache key triggers a database return to the origin only once at any given time, effectively preventing cache breakdown and ensuring data consistency and system stability under high concurrency.

[0025] Preferably, the distributed lock uses Redis Redlock or a ZooKeeper-based ephemeral sequential node, and the lock timeout is set to 3 times the average SQL response time to prevent deadlocks. Short-circuit origin lookup and distributed locks: When a user request enters the gateway, the key is first assembled according to the same rules as the pre-loaded key, and then the L1→L2→L3 keys are queried in sequence; if any key is hit, it is deserialized and returned, and at the same time, an asynchronous thread fills the result back into the next level cache (read-through). If all three levels fail, the thread enters the origin protection phase: 1. Use Redis Redlock to acquire the lock, with lock key=`lock:{fp}:{shard}`, value=UUID, and expiration=RTp95×3; 2. The thread that acquires the lock performs a database query and writes to the cache. After writing, it first writes to L3, then L2, and finally deletes the lock; 3. Threads that do not acquire the lock spin and wait for 50 ms before retrying to read the cache, with a maximum of 3 retries to prevent thread backlog. The same signature and version verification is performed on the source code. If the version returned by the database is lower than the cached version, the write is abandoned to ensure monotonic consistency.

[0026] Step 8: Provide a cache invalidation and avalanche prevention mechanism with tiered random TTL; When a binlog event triggers a data update, all cached fragments of the corresponding SQL fingerprint are immediately invalidated and preloading is re-executed. The expiration time is evenly distributed by using tiered random TTL to avoid a large number of caches from expiring at the same time; Preferably, cache invalidation includes: when the data signature (sign) comparison fails, immediately triggering a single-key origin retrieval and rewriting the cache to ensure that the data version read by the business is not lower than the minimum version number carried in the request parameters; The base TTL is set to 30 minutes. Before writing, the hash of `{fp}` is taken and modulo 300 to obtain a random perturbation of 0 to 300 seconds. Expired threads are phased out in batches over 10-second windows using Redisson's `RScheduledExecutorService` to avoid instantaneous failure.

[0027] When Redis memory usage exceeds 80%, cold data sinking is triggered: the LRU eviction policy is used to migrate keys that have not been accessed in the last 24 hours to L3 RocksDB, and the gateway routing table is modified so that queries are automatically downgraded to L3.

[0028] Step 9: Expose key metrics such as hit rate, preloading latency, origin QPS, and version conflict count in the caching system design and performance monitoring at the gateway layer, connect to Prometheus monitoring, and trigger alarms when the hit rate is below 90% or the preloading latency is above 5 seconds. Hit rate has a dual nature; it is both a metric for caching system design and performance monitoring. Preloading latency is primarily a design consideration for caching systems, and secondarily for performance monitoring. The QPS for origin server access is used for runtime performance monitoring. Version conflict count is a performance monitoring metric.

[0029] Preferably, the metrics are collected using Micrometer or Prometheus Java Client, with data points embedded at both ends of the gateway filter and the preloaded task to achieve end-to-end observability; End-to-end observability and elastic scaling; metric tracking: Micrometer output in Prometheus format; key metrics include: `cache_hit_l1_total`, `cache_hit_l2_total`, and `cache_miss_total`. `preload_latency_seconds` (binlog to cache write time), `db_query_total` (number of origin retries), `sign_mismatch_total` (consistency conflicts); Alarm rules: PagerDuty is triggered when the hit rate is less than 90% for 2 minutes, the preload_latency is greater than 5 seconds, or the QPS for returning to the source suddenly increases by 3 times.

[0030] Elastic scaling: Both Redis Cluster and RocksDB are integrated with Kubernetes HPA, automatically scaling horizontally based on CPU usage of 70% or memory usage of 80%; the number of shards N can be adjusted online. When scaling, the number of shards is doubled first, and then the data is migrated asynchronously in the background. After completion, the routing is switched to achieve zero downtime.

[0031] It has zero intrusion into business code. All caching logic, preloading logic, and invalidation logic are completed within the database access proxy gateway and asynchronous task framework, supporting high-concurrency online transactions and real-time analysis scenarios in e-commerce, finance, and logistics. Preferably, preloading, cache writing, invalidation, and backfilling operations are all decoupled from the message queue through an asynchronous thread pool, ensuring that the time consumed by the online query path is increased by no more than 1 millisecond; No business code needs to be modified. All logic is completed within the database access proxy gateway and asynchronous task framework, supporting high-concurrency online transactions and real-time analysis scenarios in e-commerce, finance, and logistics.

[0032] Experimental procedure: I. Flash Sale Scenarios During E-commerce Mega-Promotions: Scenario parameters: MySQL 8.0, order table has 800 million rows, daily average QPS is 25,000, peak QPS during major promotions is 120,000; Variable settings: α = 500, β = 30 ms, γ = 1 MB, number of slices N = 128, cron = “0 / 2 ?” (every 2 min); Deployment method: Proxy gateway: 8 cores, 16 GB RAM, 20 Pods, unified entry point via Kubernetes Service; L1: Caffeine maximumSize = 256 MB, expireAfterWrite = 60 s; L2: Redis Cluster 10 masters 10 slaves, 32 GB per master node, HashTag = {fp}; L3: RocksDB local disk 2 TB NVMe, compression ratio 4:1; Implementation process: 1. Gateway captures SQL: `SELECT order_id, price FROM orders WHERE seller_id = ? AND status =? AND gmt_create >= ?`; Template after parameter abstraction: `SELECT order_id, price FROM orders WHERE seller_id = ? AND status =? AND gmt_create >= ?`; MD5 fingerprint = 7d8a3b2c.

[0033] 2. A 10-second window yielded QPS = 3200, RT = 28 ms, and Bytes = 0.8 MB, meeting the hotspot criteria, and data was written to ZooKeeper.

[0034] 3. Time-driven: 600 parameter combinations are generated every 2 minutes and delivered to Kafka; Event-driven: binlog line changes average 6000 tps, token bucket rate limiting is 500 tps, actual triggering is 480 tps.

[0035] 4. The preloaded thread pool has 128 concurrent connections, and the data is sharded by seller_id % 128; each shard averages 6,000 rows, which is 180 kB after Protobuf serialization; the write order is DB→L3→L2→L1, with an average time of 42 ms.

[0036] 5. Query path: User request → Gateway assembles Key= `hot:7d8a3b2c:shard-99:20251116120000`, L1 hit 0.3 ms, L2 hit 1.2 ms, L3 hit 4 ms; overall P99 7.8 ms, compared to 118 ms for direct database query, a decrease of 93%.

[0037] 6. Updated case: Seller shipment triggers binlog, average time from event to cache refresh is 1.9 seconds; version number comparison failed 3 times / 100,000 requests, signature conflict 0.

[0038] 7. Resource benefits: MySQL CPU usage dropped from 68% to 35%, connection count decreased by 42%, and there were no spikes during the promotion.

[0039] II. Real-time risk control for financial transactions: Scenario parameters: Oracle 19c, transaction log of 300 million rows, peak QPS of 80,000, consistency requirement ≤ 2 seconds.

[0040] Variable settings: α = 1000, β = 20 ms, γ = 512 kB, number of slices N = 64, cron = “0 / 1 ?”.

[0041] Key differences: Event-driven delivery uses Oracle GoldenGate for real-time delivery to Kafka with a latency of < 500 ms; Version numbers are taken from Oracle SCN to ensure global order; The query must include min_version = front-end transaction SCN. The gateway will only return a result if version ≥ min_version.

[0042] Results: P99 latency was 9 ms, consistency latency was 1.2 s, risk control rejection rate increased by 4.3%, and database CPU usage decreased by 40%.

[0043] III. Logistics Tracking Screen: Scenario parameters: PolarDB-X, trajectory table with 1.2 billion rows, range query `WHERE create_time BETWEEN ?AND ?`, large screen refresh every 5 seconds.

[0044] Variable settings: α = 150, β = 100 ms, γ = 2 MB, number of fragments N = 256, cron = “0 / 10 ?” (every 10 s).

[0045] Innovation points: The parameter template normalizes the time range to 5-minute alignment, such as 14:00:00 and 14:05:00, reducing the number of parameter combinations. Using a stepped TTL = 300 s + rand(600)s, 900 time slices are scattered to expire; The preloaded results are written directly to L2, skipping L1, to avoid local memory exhaustion; Large screen requests only access L2, P99 11 ms, with no front-end jitter.

[0046] IV. SaaS Multi-tenant Reports: Scenario parameters: MySQL 8.0, 5000 tenants, 6000 SQL reports, 12,000 concurrent users at night.

[0047] Variable settings: α = 30, β = 200 ms, γ = 3 MB, number of fragments N = 32, cron = “0 0 23 ?” (every day at 11 PM).

[0048] Innovation points: In the parameter template, tenant_id is used as the first partition key, and tenant_id % 32 is used for sharding to avoid cross-tenant pollution; Using L3 RocksDB cold storage, 30 days of historical reports are saved, and after compression, 78% of disk space is saved; If the query hits L3, the asynchronous thread backfills the result to L2, achieving a "warm-up" effect. The first access the next day will already be in L2, reducing the latency from 900 ms to 25 ms.

[0049] V. Multi-cloud disaster recovery switchover: Scenario parameters: The primary database is located in Hangzhou, the disaster recovery database is located in Shenzhen, RPO = 1 min, RTO = 5 min.

[0050] Variable settings: α = 200, β = 50 ms, γ = 1 MB, number of slices N = 64.

[0051] Innovation points: The same gateway and preloaded components were deployed in the Shenzhen cluster, and the Hangzhou binlog was subscribed to via DTS with a latency of 800 ms. The version number uses a global TSO, and the cache keys in both locations are exactly the same; After switching traffic, Shenzhen L2 immediately hit the target, P99 10 ms, meeting the RTO target; During the rollback, L3 RocksDB is used to reverse synchronize the new data from Shenzhen to Hangzhou, achieving bidirectional drift with zero data loss.

[0052] Overall effect: 1. Delay: All five examples P99 were ≤ 11 ms, with an average reduction of over 90%.

[0053] 2. Load: Database CPU usage decreased by 35%–45%, and connection count decreased by 30%–50%.

[0054] 3. Consistency: binlog to cache refresh delay of 1–2 s, signature conflict <0.02%.

[0055] 4. Cost: Redis saves 30%–40% on memory, has a disk cold storage compression ratio of 4:1, and reduces overall TCO by 25%.

[0056] 5. Scalability: The number of shards N and thresholds α / β / γ are all adjustable online, and K8s HPA automatically expands the capacity without affecting the business.

[0057] Experiments in different scenarios fully verify the advantages of the technical method of the present invention, proving that the present invention can run stably in high concurrency, large data volume, and strong consistency scenarios, and has significant industrial application value.

[0058] Based on the above-described preferred embodiments of the present invention, and through the foregoing description, those skilled in the art can make various changes and modifications without departing from the inventive concept. The technical scope of this invention is not limited to the contents of the specification, but must be determined according to the scope of the claims.

Claims

1. A method for quickly returning SQL query results based on distributed cache preloading, characterized in that, Includes the following steps: Step 1: Abstract the SQL parameters and generate key prefixes; Step 2: Determine SQL fingerprint hotspots, mark preloaded objects, and write them to the hotspot list; Step 3: Generate preloaded tasks using the hotspot list; Step 4: Design caching and storage; Step 5: Load the result set in chunks, splitting the result set into several chunk tasks and using threads to perform pre-computation concurrently; Step 6: Attach data consistency metadata when writing to the cache; Step 7: Assemble the cache key and check the cache and storage hit status; Step 8: Provide a cache invalidation and avalanche prevention mechanism with tiered random TTL; Step 9: Expose the cache system design and performance monitoring metrics at the gateway layer, and trigger alarms using the cache system design or performance monitoring.

2. The method for fast return of SQL query results based on distributed cache preloading according to claim 1, characterized in that, The database access proxy gateway layer abstracts parameters for incoming and outgoing SQL queries, generating a key prefix composed of SQL fingerprints and parameter templates. The SQL is preprocessed by literal substitution, expression normalization, and filtering of non-cacheable features.

3. The method for fast return of SQL query results based on distributed cache preloading according to claim 1, characterized in that, When the query frequency (QPS), average response time (RT), and traffic (Bytes) are all higher than the threshold, the SQL fingerprint is marked as a preloaded object.

4. The method for fast return of SQL query results based on distributed cache preloading according to claim 1, characterized in that, The preloading task uses both time-driven and event-driven approaches.

5. The method for fast return of SQL query results based on distributed cache preloading according to claim 4, characterized in that, Time-driven computation is performed periodically by a timer according to a cron expression, either as a full or incremental pre-computation.

6. The method for fast return of SQL query results based on distributed cache preloading according to claim 4, characterized in that, Event-driven approaches capture data change events related to SQL fingerprints by subscribing to the database binary log (binlog) in real time, triggering asynchronous reruns of SQL queries and updating the cache.

7. The method for fast return of SQL query results based on distributed cache preloading according to claim 1, characterized in that, The result set is split into shards based on the SQL partition key. The sharding strategy is determined based on whether the hot SQL queries have a partition key, and the sharded results are cached independently.

8. The method for fast return of SQL query results based on distributed cache preloading according to claim 1, characterized in that, Metadata includes: Version number (version), data signature (sign); Expiration time: expire_at.

9. The method for fast return of SQL query results based on distributed cache preloading according to claim 1, characterized in that, After the query request reaches the gateway, a cache key is assembled. If any one of the L1, L2, or L3 layers hits the cache key, the result is returned. If none of the three layers hit the cache key, a distributed lock is used to ensure that the same key is retrieved from the source database only once. Other threads are woken up after the result is written to the cache.

10. The method for fast return of SQL query results based on distributed cache preloading according to claim 1, characterized in that, Alarms can be triggered when the hit rate is below a threshold or the preloading delay is above a threshold.

Images