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

在开发英语学习或社交应用时随机展示单词或消息是一个高频需求。然而看似简单的“随机”逻辑如果实现方式不当会随着数据量的增长演变为系统瓶颈 。1. 性能陷阱order by rand()最直观的写法是select word from words order by rand() limit 3;。但在 MySQL 内部这条语句的代价极其沉重。执行逻辑解析创建临时表MySQL 会在内存中创建一个使用Memory引擎的临时表包含一个double类型的字段RRR存储随机数和一个word字段 。全表扫描生成随机数逐行读取words表调用rand()函数生成随机数并写入临时表。扫描行数10,000。临时表排序在sort_buffer中对随机数RRR进行排序。此时采用的是RowID 排序即只在内存中操作“随机值”和“行位置信息pos” 。对于Memory引擎pos 相当于数组下标 。取出结果排序完成后取出前 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查询主键的最大值MMM和最小值NNN。在应用层计算随机 IDXfloor((M−N1)×rand()N)X \text{floor}((M-N1) \times \text{rand}() N)Xfloor((M−N1)×rand()N)。执行select * from t where id X limit 1;。注意如果 ID 存在大量“空洞”如删除过数据此方案会导致 ID 分布不均某些行被选中的概率更高 。方案 B基于偏移量随机算法 2查询总行数Ccount(*)C \text{count(*)}Ccount(*)。在应用层计算偏移量Yfloor(C×rand())Y \text{floor}(C \times \text{rand}())Yfloor(C×rand())。执行select * from t limit Y, 1;。结论此方案解决了概率不均问题 。虽然扫描行数为CY1C Y 1CY1次但由于不涉及临时表和排序性能远优于order by rand()。4. 终极优化方案 C排序偏移量法如果要随机取NNN条如 3 条记录且要求满足概率独立和极高性能方案 C 是最佳实践。优化思路如果直接执行 3 次独立查询总扫描行数高达C∑(Yn1)C \sum(Y_n 1)C∑(Yn​1)。为了减少扫描开销我们可以预计算在应用层生成 3 个随机偏移量Y1,Y2,Y3Y1, Y2, Y3Y1,Y2,Y3并按从小到大排序。单次扫描定位SQL 改写-- 伪代码逻辑-- 1. 获取第 Y1 行select*fromtlimitY1,1;-- 假设返回 ID_1-- 2. 基于 ID_1 获取第 Y2 行利用索引锚点select*fromtwhereidID_1limitY2-Y1-1,1;-- 3. 基于 ID_2 获取第 Y3 行select*fromtwhereidID_2limitY3-Y2-1,1;性能优势总扫描行数约等于Cmax⁡(Yn)1C \max(Y_n) 1Cmax(Yn​)1。原理利用了“延迟关联”的思想在获取第一个随机点后后续查找可以基于索引有序性进行增量扫描避免了从表头反复开始读取。