Designing a Cache for 10 M MySQL Rows with Only 2 M Redis Slots – Meituan Interview Answer

Problem Essence

MySQL stores 10 M rows (~1 KB each, ~10 GB). Redis can hold only 200 k rows due to a 20 GB memory limit. The challenge is to design a cache architecture that keeps hot data in memory while discarding cold data.

Challenges

Cost: Redis memory is expensive, MySQL disk storage is cheap.

Traffic skew: ~20 % of data often handles ~80 % of traffic; flash‑sale items can dominate.

Hotspot volatility: Hot items change quickly.

Solution – Five Techniques

1. Cold‑Hot Separation

Store cold data in MySQL and hot data in Redis. Add a hot_score column to the product table and update it daily. Pre‑warm the top‑N hot keys into Redis and use the Cache‑Aside pattern.

ALTER TABLE products
ADD COLUMN hot_score INT DEFAULT 0 COMMENT 'hot score (last 7 days accesses)',
ADD INDEX idx_hot_score (hot_score DESC);
-- daily update
UPDATE products p
JOIN (
  SELECT item_id, COUNT(*) AS cnt
  FROM access_log
  WHERE access_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
  GROUP BY item_id
) l ON p.id = l.item_id
SET p.hot_score = l.cnt;

2. Hit‑Rate Governance

Select an eviction policy that matches the workload. For flash‑sale scenarios allkeys‑lfu is preferred because it evicts by access frequency rather than recency. noeviction – never evicts (default). allkeys‑lru – approximate LRU. allkeys‑lfu – approximate LFU.

Other policies: volatile‑ttl, volatile‑random, etc.

Configuration example:

maxmemory 20gb
maxmemory-policy allkeys-lfu
lfu-log-factor 10
lfu-decay-time 1

3. Hotspot Detection

Use JD’s open‑source HotKey system to actively identify hot keys. Clients report local hot keys, the server aggregates a global top‑200 k list and pushes it to clients.

// HotKey client (Spring Boot)
HotKeyClient client = new HotKeyClient("my_app");
client.setZkAddr("zk1:2181,zk2:2181");
client.setCheckInterval(60);
client.start();

4. Multi‑Level Defense

Build a three‑tier cache: Nginx shared_dict (layer 3), JVM local cache such as Caffeine (layer 2), Redis cluster (layer 1), with MySQL as the persistent fallback. Data flow: Nginx → Caffeine → Redis → MySQL, with back‑fill at each miss.

// Caffeine + Redis + MySQL cascade
LoadingCache<String, Object> localCache = Caffeine.newBuilder()
    .maximumSize(1000)
    .expireAfterWrite(30, TimeUnit.SECONDS)
    .build(key -> {
        Object v = redis.get(key);
        if (v != null) return v;
        Object db = mysql.query("SELECT * FROM products WHERE id = ?", key);
        if (db != null) redis.setex(key, 3600, db.toString());
        return db;
    });

5. Multi‑Dimensional Pre‑Heating

Combine a daily scheduled pre‑heat (top‑200 k keys from yesterday’s logs) with real‑time pre‑heat triggered by HotKey events.

// Daily pre‑heat task
@Scheduled(cron = "0 0 2 * * ?")
public void preheatCache() {
    List<String> hotIds = jdbcTemplate.queryForList(
        "SELECT item_id FROM access_log WHERE DATE(access_time)=CURDATE()-1 GROUP BY item_id ORDER BY COUNT(*) DESC LIMIT 200000",
        String.class);
    hotIds.forEach(id -> {
        String data = jdbcTemplate.queryForObject("SELECT * FROM products WHERE id = ?", String.class, id);
        redisTemplate.opsForValue().set(id, data, 86400, TimeUnit.SECONDS);
    });
}

// Real‑time pre‑heat (HotKey listener)
@Component
public class HotKeyListener implements HotKeyChangeEvent {
    @Autowired
    private RedisTemplate<String, String> redisTemplate;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public void onHotKeyAdd(String key) {
        String data = jdbcTemplate.queryForObject("SELECT * FROM products WHERE id = ?", String.class, key);
        if (data != null) {
            redisTemplate.opsForValue().set(key, data, 24, TimeUnit.HOURS);
            System.out.println("Real‑time pre‑heat Key=" + key + " success");
        }
    }
}

Performance Evaluation

No Cache : Hit Rate 0 %, Avg Latency 900 ms, QPS 1 000, Redis Memory –.

Basic LRU : Hit Rate 50 %, Avg Latency 300 ms, QPS 5 000, Redis Memory Utilization 60 % (≈17 GB).

LFU + HotKey + Multi‑Level : Hit Rate 85 % , Avg Latency 100 ms, QPS 20 000, Redis Memory Utilization 92 % (≈18.4 GB).

Interview Takeaways

Explain the three‑tier cache, justify the eviction policy choice, describe how HotKey provides proactive hotspot detection, and show the quantitative improvement in hit rate and latency. This demonstrates deep understanding of high‑concurrency cache design.