Scan Query Performance Optimization: From 200ms to 50ms

The consumer experience of scanning a code to verify authenticity directly impacts brand perception. For every 100ms increase in scan response latency, the probability of consumer abandonment rises by approximately 7%. This article documents the complete process of how we reduced scan query response time from 200ms to 50ms.

Bottleneck analysis is the first step in optimization. Full-chain tracing with Arthas revealed: database queries took 120ms (60% of total time), network transmission 30ms, serialization/deserialization 15ms, and business logic 35ms. Database queries were the biggest bottleneck, so the optimization strategy focused on multi-level caching.

The caching architecture uses a two-tier approach: Caffeine (local cache) + Redis (distributed cache). Caffeine is configured with a 5-minute expiration and a maximum of 10,000 entries, achieving an 85%+ hit rate. Redis caches product information and anti-counterfeit status corresponding to code data, with a 30-minute expiration. Cache penetration protection uses a Bloom Filter, pre-loading all valid code hashes to intercept invalid queries.

On the database optimization side, we added covering indexes to the code table, ensuring scan queries need only a single index lookup to retrieve all required fields. The connection pool uses HikariCP, with maximumPoolSize tuned through stress testing to CPU cores × 2 + 1. The final results: P50 response time dropped from 200ms to 42ms, P99 from 500ms to 98ms, and system throughput increased 3x.