MySQL 如何正确实现“随机采样”

在开发英语学习或社交应用时，随机展示单词或消息是一个高频需求。然而，看似简单的“随机”逻辑，如果实现方式不当，会随着数据量的增长演变为系统瓶颈 。

1. 性能陷阱：order by rand()

最直观的写法是select word from words order by rand() limit 3;。但在 MySQL 内部，这条语句的代价极其沉重。

执行逻辑解析

1. 创建临时表：MySQL 会在内存中创建一个使用Memory引擎的临时表，包含一个double类型的字段RRR（存储随机数）和一个word字段 。
2. 全表扫描生成随机数：逐行读取words表，调用rand()函数生成随机数并写入临时表。扫描行数：10,000。
3. 临时表排序：在sort_buffer中对随机数RRR进行排序。此时采用的是RowID 排序，即只在内存中操作“随机值”和“行位置信息（pos）” 。对于Memory引擎，pos 相当于数组下标 。
4. 取出结果：排序完成后，取出前 3 个位置信息，回到临时表中取出word返回。
   结论：总扫描行数为20,003次（10,000 + 10,000 + 3） 。如果数据量达到百万级，这种“先生成随机数再排序”的方式会产生巨大的 CPU 和内存消耗。

2. 排序优化：优先队列算法

如果limit的值较小，MySQL 5.6+ 会采用优先队列算法来优化排序 。

• 逻辑：不需要对全表 10,000 条记录进行完整归并排序。MySQL 会在sort_buffer中维护一个最大堆（堆顶始终是当前最大的随机值） 。
• 过程：读取一行 (R, pos)，如果 R 比堆顶小，则替换堆顶并重新调整堆。
• 优势：只需遍历一遍数据即可得到最小的 3 个随机数，避免了生成复杂的磁盘临时文件 。

3. 两种高效的替代方案

方案 A：基于 ID 范围（随机算法 1）

1. 查询主键的最大值MMM和最小值NNN。
2. 在应用层计算随机 ID：X=floor((M−N+1)×rand()+N)X = \text{floor}((M-N+1) \times \text{rand}() + N)X=floor((M−N+1)×rand()+N)。
3. 执行select * from t where id >= X limit 1;。

注意：如果 ID 存在大量“空洞”（如删除过数据），此方案会导致 ID 分布不均，某些行被选中的概率更高 。

方案 B：基于偏移量（随机算法 2）

1. 查询总行数C=count(*)C = \text{count(*)}C=count(*)。
2. 在应用层计算偏移量Y=floor(C×rand())Y = \text{floor}(C \times \text{rand}())Y=floor(C×rand())。
3. 执行select * from t limit Y, 1;。

结论：此方案解决了概率不均问题 。虽然扫描行数为C+Y+1C + Y + 1C+Y+1次，但由于不涉及临时表和排序，性能远优于order by rand()。

4. 终极优化：方案 C（排序偏移量法）

如果要随机取NNN条（如 3 条）记录，且要求满足概率独立和极高性能，方案 C 是最佳实践。

优化思路

如果直接执行 3 次独立查询，总扫描行数高达C+∑(Yn+1)C + \sum(Y_n + 1)C+∑(Yn​+1)。为了减少扫描开销，我们可以：

1. 预计算：在应用层生成 3 个随机偏移量Y1,Y2,Y3Y1, Y2, Y3Y1,Y2,Y3，并按从小到大排序。

2. 单次扫描定位：

   • SQL 改写：

     -- 伪代码逻辑-- 1. 获取第 Y1 行select*fromtlimitY1,1;-- 假设返回 ID_1-- 2. 基于 ID_1 获取第 Y2 行（利用索引锚点）select*fromtwhereid>ID_1limitY2-Y1-1,1;-- 3. 基于 ID_2 获取第 Y3 行select*fromtwhereid>ID_2limitY3-Y2-1,1;

3. 性能优势：

   • 总扫描行数：约等于C+max⁡(Yn)+1C + \max(Y_n) + 1C+max(Yn​)+1。
   • 原理：利用了“延迟关联”的思想，在获取第一个随机点后，后续查找可以基于索引有序性进行增量扫描，避免了从表头反复开始读取。