Designing a Million‑User, Millisecond‑Level Lottery System with MySQL and Redis

Requirement

During a flash‑sale event at midnight, the system must randomly select N users from more than ten million followers for prize distribution, ensuring that each user can participate only once and cannot win multiple times.

Design Options

1. Simple RAND() ordering

Generate a random number for each row and order by it.

select * from 关注用户表 order by rand() desc limit 0,100

This approach quickly becomes infeasible with tens of millions of rows.

2. Two‑step offset selection

First compute a random offset, then fetch a single row.

offset = SELECT FLOOR(RAND()*COUNT(*)) AS offset FROM 关注用户表;
select * from 关注用户表 limit offset,1;

Works but requires two statements and still cannot guarantee uniqueness under high concurrency.

3. Redis Set with SPOP

When a user follows the live stream, insert its ID into a Redis Set userlist. The set guarantees global uniqueness and stores IDs in hash‑shuffled order. sadd userlist xxxid During the draw, call spop userlist to atomically pop a random ID. Repeat until the required number of winners is obtained. Then retrieve full user data with a single SELECT ... WHERE id IN (...) query (max 1000 IDs per IN clause).

4. Pure Redis solution

If memory is not a constraint, keep all user IDs in a Redis Set. For ten million users the set occupies roughly 3 GB. spop can return 100 IDs in about 2.5 ms, and the operation is fully atomic.

sadd userlist '123456:ikun'
sadd userlist '123456:akun'
sadd userlist '123456:bkun'

Performance tests show 1000 spop calls take 2565 ms (≈2.5 ms per call).

Conclusion

The Redis‑based approaches provide millisecond‑level latency and guarantee non‑duplicate winners at the scale of tens of millions of users. Do you have a better design?