Scaling a Student Grade Portal from 5,000 to 500,000 Users Without Buying More Servers

Student Grade Query System: From 5,000 to 500,000 People, How Would You Design It?

Introduction

The moment final exams end each semester, 20,000 students across all grades flood the academic affairs system to check their grades—and then the system crashes. A few hundred visits on a normal day, hundreds of thousands on exam day. Buying a bunch of servers for a peak that only lasts 3 days a year won't get approved by leadership. But if the system goes down on grade release day, student complaint calls will blow up the phone lines.

This isn't a technical challenge; it's a cost vs. experience balancing act: 5 QPS normally, 5000 QPS at peak—a 1000x difference. What do you do with your system?

This article centers on implementing a real "Student Grade Query System," evolving the architecture step-by-step from a 5,000-student independent college to a 500,000-student provincial examination authority.

Environment Info: Spring Boot 3.2.7 | JDK 21 | Redis 7.x | MySQL 8.0 | JMeter 5.6

1. First, Understand the Battlefield: Business Characteristics of Grade Queries

Before choosing an architecture, you must understand the traffic characteristics of the business. Grade queries and e-commerce flash sales both look like "high concurrency," but they are fundamentally different:

Dimension	E-commerce Flash Sale	Grade Query
Traffic Pattern	Instant spike + sustained high concurrency	Rapid ramp-up + subsides within 30 minutes
Read/Write Ratio	Heavy writes (orders) + reads	99% reads
Data Consistency	Strong consistency (inventory cannot be oversold)	Eventual consistency (a grade value cached 10 seconds ago is still correct)
User Experience	User leaves if no response in 2 seconds	Can wait in a queue, even accept "we'll notify you in 30 seconds"
Hotspot Data	A few products are hotspots	Every student's grades are a hotspot (everyone checks their own)

Key Insight (must read first):

The "read-heavy, write-light + eventual consistency tolerance" of grade queries dictates that caching is the primary weapon, not an auxiliary tool. Once published, grades are almost immutable, making them a natural fit for caching. The traffic spike combined with users' willingness to queue also gives us room to use asynchronous peak-shaving.

But here's a key point that's easy to misjudge: An e-commerce flash sale is "1 iPhone page refreshed by 10,000 people simultaneously" (hotspot key), whereas a grade query is "10,000 students each checking their own student ID" (distributed keys). This means many strategies targeting "hotspot keys" (like distributed locks to prevent cache breakdown) have limited benefit in the grade query scenario—this article will mark which solutions are "the right medicine" and which are "using a sledgehammer to crack a nut."

Data Model (Used Across All Versions)

-- Student table: 50,000 rows
CREATE TABLE t_student (
    id BIGINT PRIMARY KEY,
    student_no VARCHAR(20) NOT NULL UNIQUE,  -- Student ID
    name VARCHAR(50) NOT NULL,
    class_id BIGINT NOT NULL,
    INDEX idx_class (class_id)
);

-- Course table: 500 rows (mostly static)
CREATE TABLE t_course (
    id BIGINT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    credit DECIMAL(3,1) NOT NULL
);

-- Score table: 500,000 rows per semester (50,000 students × 10 courses)
CREATE TABLE t_score (
    id BIGINT PRIMARY KEY,
    student_id BIGINT NOT NULL,
    course_id BIGINT NOT NULL,
    score DECIMAL(5,1),            -- Score
    semester VARCHAR(20) NOT NULL,  -- Semester, e.g., 2025-2026-1
    publish_time DATETIME,          -- Grade publish time
    INDEX idx_student_semester (student_id, semester),
    INDEX idx_publish (publish_time)
);

Core Query: A student checks all their grades for a specific semester—this is the highest QPS endpoint:

SELECT c.course_name, s.score, s.semester
FROM t_score s
JOIN t_course c ON s.course_id = c.id
WHERE s.student_id = ? AND s.semester = ?;

Single Query Time: MySQL 8.0, 500,000 rows, idx_student_semester index → ~3-5ms.

💡 Optimization Tip: If the query only needs course_id and score, add these two fields to the index (covering index idx_student_semester_cover(student_id, semester, course_id, score)) to avoid a table lookup on the primary key index, shaving another 1-2ms under high concurrency.

💡 Data Volume Estimation: This article assumes ~10 courses per student per semester. If the system supports many electives (50+ per student), consider splitting cache keys by course category (e.g., grade:{sid}:{semester}:required and grade:{sid}:{semester}:elective) to prevent a single key's JSON from becoming too large and causing Redis bandwidth bottlenecks.

2. V1.0: Monolithic Architecture — "A 5,000-student independent college, one machine is enough"

Applicable Scale

Students: < 5,000
Peak QPS: < 300
Deployment: Single-instance Spring Boot + single-machine MySQL

Architecture

Browser → Nginx → Spring Boot → MySQL

Implementation

@RestController
@RequestMapping("/api/grade")
public class GradeController {

    private final ScoreMapper scoreMapper;

    @GetMapping("/{studentId}")
    public List<ScoreVO> query(@PathVariable Long studentId,
                                @RequestParam String semester) {
        return scoreMapper.selectByStudentAndSemester(studentId, semester);
    }
}

Why It's Enough

Tomcat 200 threads, each query 5ms (raw SQL time) → theoretical QPS under ideal conditions = 200/0.005 = 40,000.

But this is not production-viable QPS. Factoring in JSON serialization, Spring framework overhead, GC pauses, network I/O, TCP connection establishment, and MySQL buffer pool fluctuations, the actual stable QPS for a single instance is between 2,000 and 4,000. 300 QPS is indeed stress-free for this architecture.

# V1.0 Single database connection pool configuration
# Formula: connections = ((core_count * 2) + effective_spindle_count)
# 4-core server + SSD → about 10 connections. Read replicas need more after read/write split (see V4.0).
spring:
  datasource:
    hikari:
      maximum-pool-size: 20       # Moderate over-provisioning, finalize after load testing
      minimum-idle: 5
      connection-timeout: 3000    # 3-second timeout, don't wait 30 seconds
      idle-timeout: 600000
      max-lifetime: 1800000

When It's No Longer Enough

When the grade-level director shares the grade-check link in a group chat, students flood in instantly, and QPS jumps from 10 to 2,000. Tomcat's 200-thread pool and HikariCP connection pool are fully saturated, requests start queuing → response time spikes → avalanche.

Bottleneck Location: MySQL connection pool. 20 connections, each query 5ms, single-machine limit ~4,000 QPS (ideal). In practice, due to network, lock contention, etc., instability begins above 2,000 QPS.

3. V2.0: Introducing Redis Cache — "A 20,000-student undergraduate university, surviving the grade-check peak"

Applicable Scale

Students: 5,000 ~ 20,000
Peak QPS: 300 ~ 2,000
New Weapon: Redis centralized cache + cache preheating

First, Correct a Misconception: Should You Even Use Caching for Grade Queries?

Many people's first reaction is "add a Redis cache"—but for the grade query business, a purely "lazy-loading" cache is almost useless. Here's why:

Lazy-loading pattern (problematic):
  Grades published → Students flood in to check
  → Student A checks their grades → Redis miss → Query DB → Write back to cache
  → Student B checks their grades → Redis miss → Query DB → Write back to cache
  → ...
  → 5,000 students each check once → 5,000 Redis misses → 5,000 DB queries

Result: Redis cache is effectively useless, DB still gets hammered.

Each student checks their own student ID = 5,000 different cache keys = all first-round queries are misses.

Conclusion: Caching for grade query scenarios cannot rely on "student's first query" for passive population; it must be actively preheated when grades are published.

Core Strategy: Cache Preheating

/**
 * Cache preheater triggered after grade publication.
 *
 * Invocation timing: After teachers upload grades and click the "Publish" button,
 * triggered by an admin endpoint. Executes asynchronously, does not block the publish process.
 */
@Service
public class GradeCachePreheater {

    private final StringRedisTemplate redis;
    private final ScoreMapper scoreMapper;
    private final ObjectMapper objectMapper;
    private static final String CACHE_PREFIX = "grade:";
    private static final Duration TTL = Duration.ofMinutes(30);

    /**
     * Preheats the cache for all students' grades in a given semester.
     *
     * @param semester  Semester, e.g., "2025-2026-1"
     * @param batchSize Number of students per batch to avoid loading too much data at once
     */
    @Async  // Spring async, doesn't block the publish endpoint
    public void preheat(String semester, int batchSize) {
        long lastId = 0;
        int totalPreheated = 0;

        while (true) {
            // Batch query: fetch batchSize students' grades at a time
            List<StudentScoreBatch> batch = scoreMapper
                    .selectBatchBySemester(semester, lastId, batchSize);

            if (batch.isEmpty()) break;

            // Group by studentId, batch write to Redis
            Map<String, String> cacheMap = new LinkedHashMap<>();
            for (StudentScoreBatch row : batch) {
                String key = CACHE_PREFIX + row.getStudentId() + ":" + semester;
                // Add random offset to each key to prevent avalanche
                cacheMap.put(key, objectMapper.writeValueAsString(row.getScores()));
            }

            // Pipeline batch write, significantly reduces network round-trips
            // Note: executePipelined lifecycle is managed by Spring; connection is auto-returned after execution
            // For very large batches (>100k entries), consider increasing Redis timeout to avoid pipeline execution timeout
            redis.executePipelined((RedisCallback<Object>) connection -> {
                cacheMap.forEach((key, value) -> {
                    byte[] rawKey = key.getBytes();
                    byte[] rawValue = value.getBytes();
                    connection.setEx(rawKey,
                            TTL.plusSeconds(ThreadLocalRandom.current().nextInt(300))
                                .getSeconds(),
                            rawValue);
                });
                return null;
            });

            totalPreheated += batch.size();
            lastId = batch.get(batch.size() - 1).getStudentId();
        }

        log.info("Cache preheating complete, semester={}, total {} students' grades preheated", semester, totalPreheated);
    }
}

💡 Performance Tip: ObjectMapper is thread-safe and should be reused as a singleton (already a private final field above). If the preheated data volume is very large (500,000 students), GC pressure from JSON serialization could become a bottleneck—consider switching to MessagePack or Protobuf instead of JSON serialization.

Preheating Effect: After grades are published, students receive a notification on their phones → open the grade-check page → 99% hit Redis cache → DB pressure near zero.

⚠️ Preheating Time Estimate: 50,000 students × 10 courses, Pipeline batch write, takes about 2-5 minutes. For larger data volumes (500,000+), it's recommended to trigger preheating 10 minutes in advance.

Key Point: Traffic Must Be Blocked Before Preheating Completes

Preheating requires a "query switch"—grade publication ≠ ready to query. The correct sequence:

1. Teacher clicks "Publish Grades" → Data written to MySQL → Trigger cache preheating (async)
2. Preheating in progress → Query switch = CLOSED → Student requests return "Grades being prepared, please wait" (no DB query)
3. Preheating complete → Query switch = OPEN → Students query normally (99% cache hit)

// Query switch in Redis
// Set to CLOSED when grades are published, set to OPEN after preheating completes
public class GradePublishService {

    private final StringRedisTemplate redis;
    private final GradeCachePreheater preheater;

    public void publish(String semester) {
        // 1. Close query switch (all grade-check requests return "preparing" page)
        redis.opsForValue().set("grade:switch:" + semester, "CLOSED");

        // 2. Async preheat
        preheater.preheat(semester, 1000)
            .thenRun(() -> {
                // 3. Preheating complete → Open switch
                redis.opsForValue().set("grade:switch:" + semester, "OPEN");
                log.info("Grade publication complete, query switch opened: semester={}", semester);
            });
    }
}

// Controller layer checks the switch
public Result query(Long studentId, String semester) {
    String status = redis.opsForValue().get("grade:switch:" + semester);
    if (!"OPEN".equals(status)) {
        return Result.accepted("Grades being prepared, please wait...");
    }
    // ... normal grade query
}

Without this switch, all requests flooding in during those 2-5 minutes of preheating will miss → DB gets hammered. This switch is essential supporting infrastructure for the cache preheating solution; neither can be omitted.

Architecture

Browser → Nginx → Spring Boot → Redis (99%+ hit rate after preheating) → MySQL (fallback)

Implementation: Cache-Aside + Preheating Fallback

Cache query logic after preheating:

@Service
public class GradeService {

    private final ScoreMapper scoreMapper;
    private final StringRedisTemplate redis;
    private final ObjectMapper objectMapper;  // ← Must be declared, otherwise won't compile

    private static final String CACHE_PREFIX = "grade:";
    private static final Duration TTL = Duration.ofMinutes(30);

    public List<ScoreVO> query(Long studentId, String semester) {
        String cacheKey = CACHE_PREFIX + studentId + ":" + semester;

        // 1. Check Redis (hit rate > 99% after preheating)
        String cached = redis.opsForValue().get(cacheKey);
        if (cached != null) {
            return objectMapper.readValue(cached,
                    new TypeReference<List<ScoreVO>>() {});
        }

        // 2. Miss (abnormal: preheating missed / TTL expired / unpreheated semester) → Query DB
        List<ScoreVO> scores = scoreMapper.selectByStudentAndSemester(studentId, semester);

        // 3. Write back to cache
        if (!scores.isEmpty()) {
            String json = objectMapper.writeValueAsString(scores);
            redis.opsForValue().set(cacheKey, json,
                    TTL.plusSeconds(ThreadLocalRandom.current().nextInt(300)));
        }

        return scores;
    }
}

Results

Metric	V1.0 (No Cache)	V2.0 (Redis Cache + Preheating)
First-round Redis hit rate	—	99%+ (after preheating)
Database QPS	2000	~10 (only misses penetrate)
Average RT	45ms	3ms
Supported Students	~5000	~20000

Four Problems That Must Be Understood

Below we discuss the four classic caching problems. But note: their severity varies in the grade query scenario. For each problem, I'll mark "Applicability to Grade Query Scenario."

Problem 1: Cache Penetration—"Querying a non-existent student ID" (⭐⭐⭐ Applicable)

Attacker/Crawler: GET /api/grade/99999?semester=2025-2026-1
          → Redis doesn't have it (non-existent IDs aren't preheated)
          → MySQL queried, returns empty
          → 1000 non-existent IDs → 1000 useless DB queries

This problem does exist in grade query scenarios—students might enter wrong IDs, crawlers might enumerate IDs. But first, assess: How many invalid IDs actually reach the service layer? Most invalid IDs are intercepted at the frontend form validation (student ID regex), gateway JWT validation, or Controller identity comparison. Invalid IDs that can penetrate to the cache layer are mainly: crawlers bypassing the gateway, internal endpoints lacking authentication, etc.

Recommended Solution: Cache Null Values (Null Object Pattern)—Simplest and Most Reliable

public List<ScoreVO> query(Long studentId, String semester) {
    String cacheKey = CACHE_PREFIX + studentId + ":" + semester;

    String cached = redis.opsForValue().get(cacheKey);
    if (cached != null) {
        // If it's a null marker, return empty directly (no further DB penetration)
        if ("NULL".equals(cached)) {
            return Collections.emptyList();
        }
        return parseFromJson(cached);
    }

    // Query DB
    List<ScoreVO> scores = scoreMapper.selectByStudentAndSemester(studentId, semester);
    if (scores.isEmpty()) {
        // Cache null marker, short TTL 5 minutes—blocks this batch of invalid requests
        redis.opsForValue().set(cacheKey, "NULL", Duration.ofMinutes(5));
    } else {
        setCache(cacheKey, scores);
    }
    return scores;
}

Why not use a Bloom filter? Bloom filters are technically feasible, but they introduce an independently maintained data structure—student withdrawals, ID recycling, new student enrollment all require synchronous updates, and deletion isn't supported. Under multiple live instances, you must use RedisBloom (introducing another Redis Module dependency). Null value caching, on the other hand:

No extra dependencies, just 3 more lines of code
Auto-expires, no maintenance needed
Attacker switches to a new invalid ID → penetrates at most once, that ID won't penetrate again for 5 minutes

Alternative: Bloom Filter (Consider when student count > 500,000 + targeted enumeration attacks exist)

⚠️ If you really must use Bloom, use Redisson's RBloomFilter (Redis-based), not the Guava local version (inconsistent across instances).

@Configuration
public class BloomFilterConfig {

    @Bean
    public RBloomFilter<Long> studentBloomFilter(RedissonClient redisson, StudentMapper mapper) {
        RBloomFilter<Long> filter = redisson.getBloomFilter("student:bloom");
        // Initialize: expected 1 million students, 0.1% false positive rate
        filter.tryInit(1_000_000L, 0.001);
        // Batch load (add in batches for large data to avoid blocking startup)
        List<Long> allIds = mapper.selectAllIds();
        allIds.forEach(filter::add);
        log.info("Bloom filter initialized, {} student IDs loaded", allIds.size());
        return filter;
    }
}

Bloom filter maintenance requires extra work: filter.add() when new students are added, and periodic (e.g., after new student enrollment each year) full rebuilds to clean up withdrawn/graduated IDs (Bloom filters don't support deletion, only rebuild).

Problem 2: Cache Breakdown—"Mass requests hit DB when a hotspot key expires" (⭐ Low Applicability)

Classic scenario: When grade:10001:2025-2026-1 expires, 100 requests simultaneously find the cache expired and all query MySQL.

But this scenario almost never happens in grade query business. Reasons:

Grade query scenario is N different keys each accessed by 1 student, not 1 key accessed by N students.
Distributed lock granularity is key-level—requests for different keys don't block each other, all penetrate to DB.
The real problem isn't "the same key hit by many people" (this doesn't exist), but "5,000 different keys miss simultaneously" (this is a DB capacity problem, solved by MQ peak-shaving, see V4.0).

When does the grade query scenario need distributed locks? Academic affairs admin scenarios—counselors querying a whole class's grades, academic affairs checking university-wide stats—these are true "hotspot keys." If your system is primarily student-facing, this part's priority can be lowered.

Below is the distributed lock implementation (only for admin hotspot queries, not needed for student-facing):

// Use Redisson RLock, not hand-written SETNX + Lua
// Reason: Redisson has built-in lock holder validation, preventing deadlocks and accidental deletion
public List<ScoreVO> queryWithLock(Long studentId, String semester) {
    String cacheKey = CACHE_PREFIX + studentId + ":" + semester;

    // 1. Check cache
    String cached = redis.opsForValue().get(cacheKey);
    if (cached != null) return parseFromJson(cached);

    // 2. Acquire lock (Redisson RLock)
    RLock lock = redisson.getLock(cacheKey + ":lock");
    try {
        // tryLock(waitTime, leaseTime, timeUnit)
        // waitTime 0 + leaseTime 3s: return immediately if can't acquire, must complete within 3s after acquiring
        // Explicit leaseTime disables watch-dog auto-renewal: prevents infinite lock renewal after Full GC starving other threads
        if (lock.tryLock(0, 3, TimeUnit.SECONDS)) {
            // Double-check
            cached = redis.opsForValue().get(cacheKey);
            if (cached != null) return parseFromJson(cached);

            // Query DB → Write cache
            List<ScoreVO> scores = scoreMapper.selectByStudentAndSemester(studentId, semester);
            if (!scores.isEmpty()) {
                setCache(cacheKey, scores);
            }
            return scores;
        } else {
            // Didn't get lock → Return 202, let frontend retry later (non-blocking thread)
            throw new RetryableException("System busy, please try again later");
        }
    } catch (InterruptedException e) {
        Thread.currentThread().interrupt();
        throw new RetryableException("System busy");
    } finally {
        if (lock.isHeldByCurrentThread()) {
            lock.unlock();
        }
    }
}

Problem 3: Cache Avalanche—"Mass keys expire simultaneously" (⭐⭐⭐ Applicable)

Scenario: Grade system publishes grades at 6 PM, all keys preheated with TTL 30 minutes.
      6:30 PM preheated keys expire en masse → Subsequent requests all pass through to MySQL → MySQL crashes

This problem is severe in grade query scenarios. The solution is simple, already used in the code above:

// TTL with random offset (avalanche prevention)
Duration actualTTL = baseTTL.plusSeconds(ThreadLocalRandom.current().nextInt(300)); // 0~300s

50,000 students × random offset 0~~300 seconds → Cache expiration times evenly distributed over 30~~35 minutes, naturally staggered.

Problem 4: Data Security—"Can Student A see Student B's grades?" (⭐⭐⭐⭐⭐ Highest Priority)

The cache key design above is grade:{studentId}:{semester}—anyone can query any student's grades by iterating studentId. Grades are personal private data; this is a compliance red line.

Solution: Authentication upfront + cache key bound to user

@GetMapping("/{studentId}")
public Result query(@PathVariable Long studentId,
                    @RequestParam String semester,
                    @RequestHeader("Authorization") String token) {
    // Step 1: Verify identity—must be done before cache query
    Long loginUserId = jwtService.parseUserId(token);
    if (!loginUserId.equals(studentId)) {
        throw new ForbiddenException("Not authorized to query others' grades");
    }

    // Step 2: Normal cache query
    return Result.success(gradeService.query(studentId, semester));
}

Why must validation be placed before cache query? If you query cache first then validate identity, the cache already holds a memory reference to sensitive data, and the cache key doesn't differentiate requesters—there's a side-channel leak risk. The correct order is always: Authentication → Authorization → Cache Query → DB Fallback.

Integration: Stringing the Above Solutions Together

In actual code, they work together. Below is a production-grade query method for student-facing grade checks:

@Service
public class GradeService {

    private final ScoreMapper scoreMapper;
    private final StringRedisTemplate redis;
    private final RBloomFilter<Long> studentBloomFilter;
    private final ObjectMapper objectMapper;

    private static final String CACHE_PREFIX = "grade:";
    private static final Duration TTL = Duration.ofMinutes(30);

    /**
     * Student-facing grade query: no distributed lock (each student checks their own grades, keys don't conflict).
     * For admin queries (whole class/university), use queryWithLock().
     */
    public List<ScoreVO> query(Long studentId, String semester) {
        // ====== 0. Bloom Filter (Optional): Quickly reject invalid IDs ======
        if (studentBloomFilter != null && !studentBloomFilter.contains(studentId)) {
            return Collections.emptyList();
        }

        String cacheKey = CACHE_PREFIX + studentId + ":" + semester;

        // ====== 1. L1 Local Cache (Caffeine, nanosecond-level) ====== (Details in V3.0)
        List<ScoreVO> scores = localCache.getIfPresent(cacheKey);
        if (scores != null) return scores;

        // ====== 2. L2 Redis ======
        String cached = redis.opsForValue().get(cacheKey);
        if (cached != null) {
            // Null marker check (Null Object Pattern)
            if ("NULL".equals(cached)) return Collections.emptyList();
            scores = parseFromJson(cached);
            localCache.put(cacheKey, scores);  // Backfill L1
            return scores;
        }

        // ====== 3. L3 MySQL (Fallback, normally rarely reached after preheating) ======
        scores = scoreMapper.selectByStudentAndSemester(studentId, semester);
        if (scores.isEmpty()) {
            // Cache null marker, short TTL to prevent penetration
            redis.opsForValue().set(cacheKey, "NULL", Duration.ofMinutes(5));
        } else {
            String json = objectMapper.writeValueAsString(scores);
            Duration actualTTL = TTL.plusSeconds(ThreadLocalRandom.current().nextInt(300));
            redis.opsForValue().set(cacheKey, json, actualTTL);
            localCache.put(cacheKey, scores);
        }
        return scores;
    }

    private List<ScoreVO> parseFromJson(String cached) {
        try {
            return objectMapper.readValue(cached, new TypeReference<List<ScoreVO>>() {});
        } catch (JsonProcessingException e) {
            throw new RuntimeException("Failed to parse cached data", e);
        }
    }
}

This single method simultaneously solves: cache penetration (null value caching + optional BloomFilter), cache avalanche (TTL randomization), and multi-level caching (L1/L2/L3). Note—no distributed lock. Because students only check their own IDs, keys naturally don't conflict; locks would only add latency and various edge-case bugs.

4. V3.0: Multi-Level Caching + Rate Limiting — "A 50,000-student provincial key university, unafraid of any load"

Applicable Scale

Students: 20,000 ~ 50,000
Peak QPS: 2,000 ~ 5,000
New Weapons: Caffeine local cache + rate limiting

Why Redis Alone Isn't Enough

Redis is fast (100k QPS per machine), but every request still requires a network round-trip (~0.5ms in the same data center). At 5,000 QPS, this network overhead becomes noticeable. Taking it further—can we avoid the network entirely?

Architecture

Browser → Nginx (rate limiting) → Spring Boot
                          ├── Caffeine local cache (L1, no network overhead)
                          ├── Redis distributed cache (L2)
                          └── MySQL (L3)

Implementation 1: Caffeine Local Cache

Caffeine's benefit depends on cache hit rate. In the student-facing scenario, a student typically checks their grades multiple times in a short period (e.g., repeatedly confirming failed subjects); Caffeine eliminates duplicate network requests within the same session. But if each student only checks once, Caffeine offers almost no benefit.

Caffeine truly shines for admin-facing use: A counselor queries 40 students' grades in a class → 1st request goes to Redis → backfills Caffeine → subsequent 39 requests all hit local, 0ms.

@Configuration
public class CacheConfig {

    @Bean
    public Cache<String, List<ScoreVO>> localGradeCache() {
        return Caffeine.newBuilder()
                .maximumSize(10_000)
                .expireAfterWrite(5, TimeUnit.MINUTES)   // Short TTL, reduces impact of multi-instance inconsistency
                .recordStats()
                .build();
    }
}

Candid Note on Multi-Instance Consistency: Caffeine is a local cache; instances are inherently inconsistent. Using Redis Pub/Sub for cache invalidation broadcasts carries message loss risk (Pub/Sub is fire-and-forget). Production-grade solutions include:

Short TTL as fallback (Recommended): Set Caffeine TTL to 1-5 minutes; even if Pub/Sub messages are lost, at most a few minutes of stale reads. Grade scenarios tolerate this.
Reliable broadcasting: Use Redis Stream or RocketMQ for message broadcasting, ensuring no loss.

💡 Why not Canal + Binlog? Canal listens to MySQL Binlog → pushes to Kafka → consumer clears cache—this pipeline has high operational cost and many failure points. Valuable for flash sales, finance scenarios requiring strong consistency, but for grade queries allowing "minute-level inconsistency, 30-minute TTL natural expiry," it's severe over-engineering. Engineering principle: Choose the solution matching your consistency requirements, not the flashiest one.

Cache Tier	Hit Latency	Capacity	Consistency
L1 Caffeine	< 1μs	JVM Heap	Weak (eventual)
L2 Redis	0.5~2ms	Independently scalable	Strong (single key read/write atomic)
L3 MySQL	3~5ms	Disk	Strong

Implementation 2: Rate Limiting

V2.0 already solved most DB pressure with cache preheating, but one more threshold is needed: use rate limiting to handle extreme cases (e.g., massive requests flooding in before cache preheating completes).

Recommended Solution: Redisson RRateLimiter (true token bucket)

@Configuration
public class RateLimiterConfig {

    @Bean
    public RRateLimiter gradeQueryRateLimiter(RedissonClient redisson) {
        RRateLimiter limiter = redisson.getRateLimiter("rate:grade:query");
        // Initialize: generate 5000 tokens per second (adjust based on actual load test results)
        limiter.trySetRate(RateType.OVERALL, 5000, 1, RateIntervalUnit.SECONDS);
        return limiter;
    }
}

// Use in Interceptor or Filter
@Component
public class RateLimitInterceptor implements HandlerInterceptor {

    private final RRateLimiter rateLimiter;

    @Override
    public boolean preHandle(HttpServletRequest request,
                             HttpServletResponse response, Object handler) throws Exception {
        if (!rateLimiter.tryAcquire(1, 100, TimeUnit.MILLISECONDS)) {
            response.setStatus(429);
            response.getWriter().write("{\"code\":429,\"msg\":\"System busy, please try again later\"}");
            return false;
        }
        return true;
    }
}

**** Redisson's RRateLimiter has a built-in token bucket algorithm (not a simple counter), automatically handling edge bursts, distributed consistency, expiration cleanup, and other details. ****

Alternative: Nginx-level Rate Limiting (Lighter weight, recommended as the first line of defense)

# Nginx rate limiting: max 10 requests per second per IP
limit_req_zone $binary_remote_addr zone=grade_api:10m rate=10r/s;

location /api/grade/ {
    limit_req zone=grade_api burst=20 nodelay;
    proxy_pass http://backend;
}

⚠️ Campus Network Note: $binary_remote_addr rate-limits by source IP. University dormitory/classroom buildings often share a single egress IP (NAT) for the entire building; one student frantically refreshing could rate-limit everyone in the building. Teams with the capability should rate-limit by userId from the JWT at the gateway layer.

After Rate Limiting Rejects: Queue Page

Requests that are rate-limited don't just get a blunt 429—give users a "waiting in queue" experience:

<!-- Grade check queue page (static HTML + JS, deployable to CDN) -->
<div id="queue">
    <h2>Current queue count: <span id="queueCount">--</span></h2>
    <p>Estimated wait time: <span id="waitTime">--</span> seconds</p>
    <p>Page will auto-refresh, please don't close</p>
</div>
<script>
const timer = setInterval(async () => {
    const res = await fetch('/api/grade/queue/status?token=' + queueToken);
    const data = await res.json();
    if (data.ready) {
        clearInterval(timer);
        window.location.href = '/grade-result';
    }
    document.getElementById('queueCount').innerText = data.ahead;
    document.getElementById('waitTime').innerText = data.estimatedWait;
}, 3000);
</script>

5. V4.0: Asynchronous Peak-Shaving + Read/Write Splitting — "A 100,000-candidate provincial exam authority, trading space for stability"

Applicable Scale

Candidates: 50,000 ~ 200,000
Peak QPS: 5,000 ~ 20,000
New Weapons: Message queue peak-shaving + MySQL master-slave read/write splitting + asynchronous notification

Core Mindset Shift

When the scale reaches 100,000+, caching alone is no longer enough—even with 99% preheated, the remaining 1% (1,000 people × 10 courses) of first-round misses will crush the DB.

V4.0 changes the approach: Don't require instant results; accept the request first, process it slowly.

This is viable for grade queries—students can accept "click query, wait 30 seconds, grades pushed to WeChat" instead of "must see the page within 100ms."

Architecture

Browser → Nginx → Gateway (rate limiting)
                  ├── Synchronous query (cache hit) → Caffeine → Redis → MySQL Slave
                  └── Asynchronous query (cache miss) → MQ → Worker → MySQL Master
                                                                 ↓
                                                            WeChat/SMS Notification

Implementation: MQ Peak-Shaving

@RestController
public class AsyncGradeController {

    private final RabbitTemplate rabbitTemplate;
    private final GradeService gradeService;

    @PostMapping("/api/grade/query-async")
    public Result asyncQuery(@RequestBody QueryRequest req) {
        // 1. Try cache first (99% of requests after preheating hit here, instant return)
        List<ScoreVO> cached = gradeService.query(req.getStudentId(), req.getSemester());
        if (cached != null && !cached.isEmpty()) {
            return Result.success(cached);  // Synchronous return
        }

        // 2. Cache miss → Put into MQ queue, async processing
        GradeQueryMessage msg = new GradeQueryMessage(
                req.getStudentId(), req.getSemester(),
                req.getNotifyType(), req.getNotifyTarget(),
                System.currentTimeMillis()  // ← Record request time, Worker uses it to check expiry
        );
        rabbitTemplate.convertAndSend("grade.query.exchange", "grade.query", msg);

        // 3. Return "queued"
        return Result.accepted("Query submitted, we'll notify you when results are ready");
    }
}

@Component
@Slf4j
public class GradeQueryWorker {

    private final ScoreMapper scoreMapper;
    private final GradeService cacheService;
    private final NotifyService notifyService;
    private final StringRedisTemplate redis;

    // ⚠️ Must configure concurrent consumers, otherwise single-threaded processing can't keep up
    @RabbitListener(
        queues = "grade.query.queue",
        concurrency = "5-10"       // Min 5, max 10 consumer threads
    )
    public void handleQuery(GradeQueryMessage msg) {
        // ====== 0. Message expiry check: Discard messages queued for more than 5 minutes ======
        if (msg.getRequestTime() != null
                && System.currentTimeMillis() - msg.getRequestTime() > 300_000) {
            log.info("Message expired, discarding: studentId={}, delay={}ms",
                    msg.getStudentId(), System.currentTimeMillis() - msg.getRequestTime());
            return;  // Student is long gone, don't process or notify
        }

        // ====== 1. Idempotency deduplication (State Machine Pattern) ======
        String dedupKey = "msg:dedup:" + msg.getStudentId() + ":" + msg.getSemester();
        // Use SETNX for lightweight state machine: INIT → PROCESSING → DONE
        Boolean acquired = redis.opsForValue()
                .setIfAbsent(dedupKey, "PROCESSING", Duration.ofMinutes(5));
        if (!Boolean.TRUE.equals(acquired)) {
            String status = redis.opsForValue().get(dedupKey);
            if ("DONE".equals(status)) {
                return;  // Already processed
            }
            // PROCESSING status → Previous consumer is processing, current message is duplicate delivery → Skip
            return;
        }

        try {
            // Query MySQL → Write cache → Push notification
            List<ScoreVO> scores = scoreMapper.selectByStudentAndSemester(
                    msg.getStudentId(), msg.getSemester());
            cacheService.setCache(msg.getStudentId(), msg.getSemester(), scores);
            notifyService.send(msg.getNotifyType(), msg.getNotifyTarget(),
                    "Your grade query results are ready, click to view");

            // Mark as done
            redis.opsForValue().set(dedupKey, "DONE", Duration.ofHours(1));
        } catch (Exception e) {
            // Processing failed → Clear status, allow retry
            redis.delete(dedupKey);
            throw e;  // RabbitMQ will redeliver
        }
    }
}

⚠️ Idempotency State Machine Note: Use (studentId, semester) as the idempotency key, not msgId. Because the same student's same query might be delivered multiple times (RabbitMQ retries), deduplication by business dimension is more sensible. If message processing succeeds but push notification fails, the current implementation clears the status to allow retry—if your business requires retryable push, consider extracting the push logic into a separate message queue.

⚠️ Production Monitoring Must-Dos:

MQ Lag Monitoring: Consumption speed can't keep up with production speed → backlog exceeds threshold (e.g., 10,000 messages) → Auto-scale Worker Pods or trigger ingress rate limiting

Dead Letter Queue (DLQ): Failed messages go to DLQ, manual intervention for investigation, don't silently lose them

Frontend Progress Display: Show "Currently queued at position X, estimated wait Y seconds" (backlog ÷ consumption rate), managing user expectations

Read/Write Splitting

In async mode, the Worker's cache writing + notification part only depends on the MySQL master; synchronous queries go to the slave:

spring:
  datasource:
    master:
      url: jdbc:mysql://master-db:3306/grade
      hikari:
        maximum-pool-size: 20
    slave:
      url: jdbc:mysql://slave-db:3306/grade
      hikari:
        maximum-pool-size: 30   # Slave connection pool should be > master, as 99% of requests go to slave

⚠️ Connection pool size isn't guesswork: HikariCP's official formula connections = ((core_count * 2) + effective_spindle_count) gives a baseline, then finalize via load testing. Slave connections should be at least 1.5x master (reads far outnumber writes). Async Worker thread count × single query time ÷ CPU utilization = required connections; verify in JMeter load tests.

// Grade query goes to slave
@DS("slave")   // baomidou dynamic-datasource
public List<ScoreVO> queryFromSlave(Long studentId, String semester) { ... }

// Cache write/update goes to master
@DS("master")
public void updateScore(Long studentId, ScoreVO score) { ... }

Dependency:

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>4.2.0</version>
</dependency>

⚠️ Master-Slave Lag Handling: MySQL master-slave replication typically has 0.1-2 seconds of lag. For grade query scenarios, if grades were just published (publish_time within the last 30 seconds), it's recommended to force query the master to avoid the slave not having synced the latest data yet, causing queries to return empty.

public List<ScoreVO> queryWithDelayAware(Long studentId, String semester) {
    // If grades were published within the last 30 seconds, go to master (avoid master-slave lag causing "not found")
    if (isRecentlyPublished(semester)) {
        return scoreMapper.selectByStudentAndSemester(studentId, semester);  // @DS defaults to master
    }
    return queryFromSlave(studentId, semester);  // @DS("slave")
}

6. V5.0: Cloud-Native Elastic Scaling — "Million-level candidates, Gaokao-grade query challenge"

Applicable Scale

Candidates: 200,000 ~ 1,000,000+
Peak QPS: 20,000 ~ 100,000+
New Weapons: K8s HPA auto-scaling + Sentinel circuit-breaking/degradation + CDN staticization

Core Idea

When the scale reaches the provincial exam authority level, no single point is reliable. The core of the architecture is no longer "a specific technical point" but—every layer can auto-scale, every layer has a degradation plan.

Architecture Panorama

CDN (Static pages / Queue page)

WAF / Anti-DDoS IP

SLB (Alibaba Cloud / Tencent Cloud Load Balancer)

Nginx / Kong Gateway Layer (HPA × N)
  ├── Rate Limiting (Token Bucket)
  └── Routing

Spring Boot Application Layer (K8s HPA × N)
  ├── Sentinel Circuit-Breaking/Degradation
  ├── Caffeine Local Cache
  └── Sync/Async Traffic Split

Redis Sentinel (Master-Slave + Auto Failover + Multiple Read Replicas, Independently Scalable)

MySQL Master-Slave + ShardingSphere (Shard by semester or student_id)

MQ (RocketMQ Cluster, recommended over RabbitMQ for high-throughput scenarios)

Notification Service (WeChat/SMS/Email)

Key Capability 1: HPA Auto-Scaling + Pre-Warming

⚠️ Important Engineering Reality: Prometheus Adapter's collection interval is typically 30 seconds, but grade-check traffic spikes in seconds. Waiting for HPA to see QPS rise before scaling—the system is already crushed. Common production practice: 1 hour before grade release, manually raise minReplicas to 50% of the estimated value, and pre-warm Pods in advance (warm up JIT, fill connection pools, load caches).

# k8s-hpa.yaml
apiVersion: autoscaling/v2
kind: HorizontalPodAutoscaler
metadata:
  name: grade-api-hpa
spec:
  scaleTargetRef:
    apiVersion: apps/v1
    kind: Deployment
    name: grade-api
  minReplicas: 10             # Adjusted to 10 on grade release day (usually 3)
  maxReplicas: 50             # Max 50 Pods on grade release day
  metrics:
  - type: Resource
    resource:
      name: cpu
      target:
        type: Utilization
        averageUtilization: 60
  behavior:
    scaleUp:
      stabilizationWindowSeconds: 0   # Scale up immediately upon detecting load, don't wait
      policies:
      - type: Percent
        value: 100
        periodSeconds: 60
    scaleDown:
      stabilizationWindowSeconds: 300  # Wait 5 minutes before scaling down, avoid flapping

Using custom metrics (e.g., http_requests_per_second) for HPA requires: Prometheus Adapter or KEDA deployed in the cluster. Without these components, HPA can only scale based on CPU/Memory. For read-heavy scenarios like grade queries, CPU metrics are usually sufficient—cache hits consume very little CPU.

Key Capability 2: Sentinel Multi-Level Degradation

⚠️ Compliance Warning: Returning "grades from 5 minutes ago" during degradation improves availability but may violate data accuracy compliance requirements in the education sector. Grades are sensitive personal data; displaying data of uncertain correctness carries policy risk. Recommendation: Return "System busy" and guide to queue during degradation, do not return old data.

@RestController
public class GradeControllerV5 {

    @GetMapping("/api/grade/{studentId}")
    @SentinelResource(
        value = "grade-query",
        blockHandler = "gradeQueryBlocked"    // Only keep rate-limit/circuit-break degradation
    )
    public Result query(@PathVariable Long studentId,
                        @RequestParam String semester) {
        return Result.success(gradeService.query(studentId, semester));
    }

    // Degradation method when rate-limited—guides to queue, does not return old data
    public Result gradeQueryBlocked(Long studentId, String semester,
                                     BlockException e) {
        String token = queueService.joinQueue(studentId, semester);
        return Result.accepted("High query volume, you've been queued")
                .withData("queueToken", token)
                .withData("ahead", queueService.getPosition(token));
    }
}

Degradation Tiers:

Priority	Strategy	Trigger Condition	User Perception
Normal	Query L1→L2→L3	System normal	Instant return
Level 1 Degradation	Skip L3 MySQL	DB RT > 1s	"System busy, please try later"
Level 2 Degradation	Skip Redis, local only	Redis connection timeout	"Partial functionality unavailable"
Level 3 Degradation	Return queue page	App QPS > threshold	"You've been queued"
Fallback	CDN static "System busy" page	Everything unavailable	Friendly message

Key Capability 3: CDN + Staticization

The queue page is pure HTML + JS, deployed on CDN. Even if the entire application layer goes down, students at least see a "System busy" page—better than a white screen.

💡 Key Design: Rate-limit token generated by gateway. Don't rely on the application layer to generate queue tokens—if the application layer goes down, the /api/grade/queue/status request in the queue page also fails, and users only see a perpetually loading "Queued..." The correct approach: In the Kong/Nginx + OpenResty rate-limiting plugin, when rate limiting is triggered, directly generate a token and write it to Redis; the frontend polls the gateway endpoint (not going through the application layer).

7. V5+: When Data Volume Reaches Tens of Millions—Sharding

As scale continues to grow, a single table's grade data may reach tens of millions or even hundreds of millions of rows. At this point, even with indexes, the increased B+Tree depth causes single query time to go from 3ms to 10ms+.

Sharding Strategy

The most commonly used shard key for grade query business is student_id:

# ShardingSphere Configuration Example
spring:
  shardingsphere:
    datasource:
      names: ds0, ds1
    rules:
      sharding:
        tables:
          t_score:
            actual-data-nodes: ds$->{0..1}.t_score_$->{0..7}
            database-strategy:
              standard:
                sharding-column: student_id
                sharding-algorithm-name: db-inline
            table-strategy:
              standard:
                sharding-column: student_id
                sharding-algorithm-name: tbl-inline
        sharding-algorithms:
          db-inline:
            type: INLINE
            props:
              algorithm-expression: ds${student_id % 2}
          tbl-inline:
            type: INLINE
            props:
              algorithm-expression: t_score_${student_id % 8}

Why choose student_id over semester?

The core SQL for grade queries is WHERE student_id = ? AND semester = ?; student_id is the most frequent query condition
After sharding by student_id, all of a student's semester grades are in the same shard, minimizing cross-shard queries
Sharding by semester is a classic anti-pattern: The shard containing the latest semester (e.g., 2025-2026-1) carries 100% of the traffic, while other old semester shards have zero traffic—effectively no sharding at all. Sharding by student_id % N ensures each shard bears an average 1/N of the traffic, achieving true load balancing.

Sharding by semester (Anti-pattern):
  Shard0: 2025-2026-1 ← 100% traffic
  Shard1: 2024-2025-2 ← 0%
  Shard2: 2024-2025-1 ← 0%

Sharding by student_id % 4:
  Shard0 ← 25%  Shard1 ← 25%  Shard2 ← 25%  Shard3 ← 25%

Hot/Cold Data Isolation (Simpler Approach)

If sharding feels too heavy, a more pragmatic approach:

-- Current semester table (hot data, 50,000 students × 10 courses = 500,000 rows)
CREATE TABLE t_score_current LIKE t_score;

-- Historical semester table (cold data, potentially tens of millions of rows)
CREATE TABLE t_score_archive LIKE t_score;

-- Query the current table first, fallback to historical table on miss

99% of queries are for the current semester. After hot/cold separation, the current table's 500,000 rows are perfectly manageable with MySQL indexes.

8. Security Hardening Checklist

The following four items are basic production environment requirements; don't wait until you're reported to patch them:

Item	Description	Implementation
SQL Injection Prevention	Using `${}` to concatenate semester parameters in MyBatis poses injection risk	All dynamic SQL must use `#{}`. If dynamic sorting/grouping is needed, use whitelist validation
Prevent Student ID Enumeration	Attackers iterate studentId to guess valid IDs	Bloom filter can filter invalid IDs, but valid IDs can still be discovered. Recommend adding endpoint-level frequency control (max 20 different IDs queried per IP per minute)
Log Desensitization	Printing grade data to logs may leak information	Configure desensitization rules in logging framework, or `ScoreVO.toString()` should not print `score` field value
Endpoint Authentication	JWT validation should be in Filter/Interceptor layer, not hand-written in every method	Unified interceptor + annotation (`@RequireStudent`); missing one is an incident

ScoreVO override toString() to not print grades:

@Override
public String toString() {
    return "ScoreVO{course='" + courseName + "', semester='" + semester + "', score=***}";
}

9. Solution Evolution Panorama

┌─────────────┬──────────────┬─────────────┬──────────────┬──────────────┐
│   V1.0      │   V2.0       │   V3.0      │   V4.0       │   V5.0       │
│   Monolith   │   Redis Cache │   Multi-Level│   Async Peak │   Elastic   │
│              │              │   Cache     │   Shaving    │   Scaling   │
├─────────────┼──────────────┼─────────────┼──────────────┼──────────────┤
│ Spring Boot │ + Redis      │ + Caffeine  │ + MQ Shaving │ + K8s HPA   │
│ + MySQL     │ + Preheating │ + Token Bucket│ + R/W Split │ + Sentinel  │
│             │ + Bloom Filter│   Rate Limit│ + Async Notify│ + Multi-Level│
│             │              │             │              │   Degradation│
├─────────────┼──────────────┼─────────────┼──────────────┼──────────────┤
│ ~5,000 ppl  │ ~20,000 ppl  │ ~50,000 ppl │ ~200k ppl    │ ~1M ppl     │
│ QPS < 300   │ QPS < 2000   │ QPS < 5000  │ QPS < 20000  │ QPS < 100k  │
├─────────────┼──────────────┼─────────────┼──────────────┼──────────────┤
│ Cost: ★☆    │ Cost: ★★☆   │ Cost: ★★★☆  │ Cost: ★★★★☆  │ Cost: ★★★★★ │
│ Complexity:★☆│ Complexity:★★☆│ Complexity:★★★☆│ Complexity:★★★★☆│ Complexity:★★★★★│
└─────────────┴──────────────┴─────────────┴──────────────┴──────────────┘

10. Summary

Four Core Ideas (Sorted by Priority)

Cache Preheating > Lazy Loading. In the grade query scenario, "each student checks their own ID," there are no shared hotspot keys. Relying on students' first visits to populate the cache—the first wave punches through the DB. Must actively preheat the cache when grades are published; this is the core design principle of a grade query system.
The right medicine, not the full三板斧 (three-board axe). Cache penetration (Bloom filter) is applicable, cache avalanche (random TTL) is applicable. The distributed lock for cache breakdown is basically inapplicable in the "everyone checks their own" student-facing scenario—don't blindly copy e-commerce flash sale tricks into the grade query scenario.
Use off-the-shelf, don't hand-write. Redisson RLock instead of hand-written SETNX+Lua, RRateLimiter instead of hand-written token bucket—your code volume reduces by 50%, bugs by 80%. Hand-write only to understand principles; prefer mature tools in production.
Authentication before caching, security before performance. Data exposed by cache keys must have identity verification at the Controller layer—don't let "performance optimization" become a channel for data leaks. This is non-negotiable.
Graceful degradation > Stubborn resistance. Rather than letting users see a 500 error, give them a queue page, an async notification, a "WeChat will notify you." Sacrifice a bit of real-timeness in exchange for the system not dying. But mind the compliance boundary: better to say nothing about old grade data than to say it wrong.

The Most Important Sentence

A grade query system is fundamentally a cache preheating + asynchronous peak-shaving design problem, not a high-concurrency programming problem. Knowing how to write multi-threaded code is useless—preheating strategy, rate-limiting placement, degradation plans—these are what determine whether your phone gets blown up by students on grade release day.