MySQL技术问询：从含30K+用户的表中随机选取100个用户的全部记录

Hey there! Let's tackle this problem efficiently—since you've got a large dataset (30k+ users, each with 1k+ records), we need a method that doesn't strain your database performance. Here are two reliable approaches:

Approach 1: Directly from Your Record Table #

If you don't have a separate user table, you can first fetch 100 unique random user IDs, then join back to your main table to get all their records. This avoids sorting your entire massive dataset:

SELECT t.*
FROM your_table t
INNER JOIN (
    -- Get 100 unique random user IDs
    SELECT DISTINCT ANONID
    FROM your_table
    ORDER BY RAND()
    LIMIT 100
) AS random_users 
    ON t.ANONID = random_users.ANONID;

Key Notes: #

• The subquery only processes the distinct ANONID values (around 30k rows) instead of the full 30M+ records, making the random sort way faster.
• Make sure ANONID has an index—this will drastically speed up the join operation between the subquery results and your main table.

Approach 2: Use a Separate User Table (If Available) #

If you have a dedicated user table (e.g., users with ANONID as the primary key), this is even more efficient since we're only working with the compact user list:

SELECT t.*
FROM your_table t
INNER JOIN (
    -- Pick 100 random users directly from the user table
    SELECT ANONID
    FROM users
    ORDER BY RAND()
    LIMIT 100
) AS random_users 
    ON t.ANONID = random_users.ANONID;

Critical Avoidance Tip: #

Steer clear of SELECT * FROM your_table ORDER BY RAND() LIMIT 100000 or similar queries. This would force MySQL to sort your entire 30M+ row table, which is extremely slow and resource-heavy. The join-based methods above are far more scalable for large datasets.

Hope this helps you get the data you need quickly! If you run into any performance hiccups or syntax issues, feel free to follow up.

内容的提问来源于stack exchange，提问作者Shafi ullah