MySQL - 选择随机记录

MySQL - 高效地选择随机记录

在许多应用程序中，从在线测验到显示“特色产品”的电子商务网站，都需要从数据库表中随机选择一条或多条记录。虽然 MySQL 提供了简单的实现方式，但了解其性能影响并掌握适用于现代、可扩展应用程序的更高效替代方案至关重要。

方法 1：简单（但效率低下）的 ORDER BY RAND()

获取随机行的最直接方法是在 ORDER BY 子句中使用 RAND() 函数。此函数为每一行生成一个介于 0 和 1 之间的随机小数，然后 ORDER BY 根据这些随机值对整个表进行排序。

语法

SELECT column_name(s)
FROM table_name
ORDER BY RAND()
LIMIT N;

N 是你希望检索的随机记录的数量。

示例

让我们使用 CUSTOMERS 表进行演示：

CREATE TABLE CUSTOMERS(
    ID INT AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(100) NOT NULL,
    AGE INT
);

INSERT INTO CUSTOMERS(NAME, AGE) VALUES
('John', 23), ('Larry', 21), ('David', 21), ('Carol', 24),
('Bob', 27), ('Mike', 29), ('Sam', 26);

选择两个随机客户：

SELECT * FROM CUSTOMERS ORDER BY RAND() LIMIT 2;

输出（结果会不同）

每次执行都可能产生不同的结果。

ID	姓名	年龄
5	Bob	27
2	Larry	21

性能警告：为什么 ORDER BY RAND() 慢

这是本教程最重要的收获。 对于行数超过几百的表，使用 ORDER BY RAND() 效率极低。为了执行此查询，MySQL 必须：

• 执行全表扫描以读取每一行。
• 为这些行中的每一行分配一个随机数。
• 将这些行及其随机数存储在一个临时表中。
• 对整个临时表进行排序。
• 最后，返回前 N 行。

这个过程非常缓慢，会消耗大量内存和 CPU，尤其是在大型表上。不建议在生产系统中使用此方法。 让我们探讨更好的方法。

方法 2：使用 JOIN 的更具性能的方法

一种更快的方法是首先选择随机主键，然后将该表与自身连接以获取完整的行数据。这避免了对整个表数据进行排序。

SELECT t1.*
FROM CUSTOMERS AS t1
JOIN (
    SELECT ID FROM CUSTOMERS ORDER BY RAND() LIMIT 2
) AS t2 ON t1.ID = t2.ID;

为什么更好： ORDER BY RAND() 只应用于 ID 列，而该列是索引的。对小型索引列进行排序比对多个大型列进行排序快得多。

方法 3：用于数字键的偏移量方法

如果你的主键是连续的且没有间隙，你可以在应用程序代码中计算一个随机偏移量。这种方法非常快，但有显著的局限性。

-- 步骤 1：获取总数
SELECT COUNT(*) AS total FROM CUSTOMERS;

-- 步骤 2：在你的应用程序中，生成一个介于 0 和 (total - 1) 之间的随机数。
-- 假设你生成了 '3'。

-- 步骤 3：获取该偏移量处的记录。
SELECT * FROM CUSTOMERS LIMIT 1 OFFSET 3;

优点： 极快，因为它避免了排序。 缺点： 如果 ID 序列中存在间隙（例如，由于删除的行），则会失败，因为偏移量将无法映射到有效的 ID。

方法 4：应用程序端洗牌

对于中等大小的表，一种健壮的方法是将所有主键获取到你的应用程序中，打乱列表，然后查询随机选择的键的完整数据。

-- 步骤 1：在你的应用程序中，获取所有 ID。
SELECT ID FROM CUSTOMERS;

-- 步骤 2：在你的代码中（例如 Python、JavaScript），打乱此 ID 数组并选择其中的 N 个。
-- 假设你选择了 [5, 2]。

-- 步骤 3：获取这些特定 ID 的完整数据。
SELECT * FROM CUSTOMERS WHERE ID IN (5, 2);

优点： 保证随机性，适用于有间隙的 ID，利用快速主键查找。 缺点： 占用应用程序内存来保存所有 ID，不适用于特大型表（数百万行）。

现代方法：使用 TABLESAMPLE（MySQL 8+）

从 MySQL 8.0.29 开始，你可以使用 TABLESAMPLE 子句进行高效、近似的随机采样。它并非完全随机，但由于不扫描整个表，所以速度极快。

-- 大约获取 50% 的行
SELECT * FROM CUSTOMERS TABLESAMPLE SYSTEM(50);

-- 获取特定数量的行（近似）
-- 注意：它返回大约 10 行，而非精确 10 行。
SELECT * FROM CUSTOMERS TABLESAMPLE SYSTEM (10 ROWS);

用例： 最适合对大型数据集进行统计分析，在这些场景中，代表性的、接近随机的样本就足够，并且性能至关重要。

总结：你应该使用哪种方法？

方法	最佳适用场景	优点	缺点
ORDER BY RAND()	非常小、非关键的表。	语法简单。	在大型表上性能极差。
JOIN 与 RAND()	中等大小的表。	比基本的 RAND() 性能好得多。	仍然需要对主键进行排序。
应用程序端洗牌	小到中等大小的表。	真正的随机性，可靠。	需要应用程序内存来保存所有 ID，不适用于特大型表（数百万行）。
TABLESAMPLE	用于统计分析的大型表。	速度极快。	近似，非完全随机。

在客户端程序中实现随机选择

这是一个实现“应用程序端洗牌”方法的现代 Node.js 示例，该方法通常在性能和真正随机性之间取得良好平衡。

Node.js（应用程序端洗牌）



此示例获取所有 ID，在 JavaScript 中打乱它们，选择两个，然后获取完整的客户数据。它使用连接池和 async/await。

```javascript
// randomCustomer.js
const mysql = require('mysql2/promise');

// 一个简单的洗牌函数（Fisher-Yates 洗牌算法）
function shuffleArray(array) {
  for (let i = array.length - 1; i > 0; i--) {
    const j = Math.floor(Math.random() * (i + 1));
    [array[i], array[j]] = [array[j], array[i]];
  }
  return array;
}

async function getRandomCustomers(count) {
  let connection;
  try {
    const pool = mysql.createPool({ host: 'localhost', user: 'root', password: 'password', database: 'TUTORIALS' });
    connection = await pool.getConnection();

    // 1. 获取所有 ID
    const [idRows] = await connection.execute('SELECT ID FROM CUSTOMERS');
    let allIds = idRows.map(row => row.ID);

    // 2. 在应用程序中洗牌 ID
    const shuffledIds = shuffleArray(allIds);
    const selectedIds = shuffledIds.slice(0, count);

    if (selectedIds.length === 0) {
      console.log('没有客户可供选择。');
      return [];
    }

    // 3. 获取所选 ID 的完整数据
    // 为 IN 子句动态创建占位符 (?, ?, ...)
    const placeholders = selectedIds.map(() => '?').join(',');
    const sql = `SELECT * FROM CUSTOMERS WHERE ID IN (${placeholders})`;

    const [customers] = await connection.execute(sql, selectedIds);

    console.log(`随机选择了 ${count} 位客户：`);
    console.log(customers);
    return customers;

  } catch (error) {
    console.error('获取随机客户失败：', error);
  } finally {
    if (connection) connection.release();
  }
}

getRandomCustomers(2);