MySQL 取随机行

本文将介绍 MySQL 的取随机行的正确做法。

一、取随机行的需求

在实际开发中，可能会有这样的需求：

从一堆数据中随机取出某几条数据。

例如：

  • 从题库中随机取几道题目放入试卷
  • 从课程库中随机取几个课程，向用户推荐

对应到数据库层面，便需要从某个数据表中取随机行。

二、通过 order by rand() 实现

1
select word from words order by rand() limit 3;

对 words 表进行随机排序，取前三个。

这个语句的具体执行流程是：

  • 假如内存中放得下：

    • 创建一个位于内存中的临时表

    • 从 words 表中，按顺序取出 word 值，rand() 生成随机数，将 word 值与随机数作为临时表的一行放入

    • 初始化 sort_buffer

    • 从临时表中逐行取出随机数和 “位置信息”，放入 sort_buffer 中

      • 临时表的存储引擎是 MEMORY，并不使用索引，而是使用数组下标定位，这里称为 “位置信息”
      • 不直接取 word 的原因是为了避免额外的内存占用，并且对于内存中的临时表额外，回表并不会照成额外的性能损耗

    • 对 sort_buffer 中的记录做排序

    • 取前三个结果，根据 “位置信息” 到临时表中取出 word 值，返回给客户端

  • 假如内存中放不下：

    • 需要创建位于磁盘中的临时表
    • ···

无论哪种情况，order by rand() 的资源消耗都会很大。

三、通过代码逻辑实现

1. 根据主键大小

  • 做法：

    • 获取主键最大值、最小值
    • 取主键大于等于 (MAX - MIN) * rand() + MIN 的行

  • 好处：

    • 取主键最大最小值都不需要扫描索引
    • 取目标行时可以通过聚簇索引快速定位

  • 缺点：

    • 仅适用于主键为数字且递增 的情况

    • 如果表在增删改查过程中产生了主键空洞，则不同行的随机概率将不同

      1
      2
      3
      insert 1, 2, 3, 4, 5
      delete 2, 3, 4
      -- 此时这个算法选中 1 的概率为 1/5, 选中 5 的概率为 4/5

2. 根据行数

  • 做法：
    • 取表的行数
    • 通过 limit floor(行数 * rand()) 1 取行
  • 好处：
    • 可以解决 “根据主键大小” 算法中因主键 ID 空洞而导致的概率不均问题
    • 可以适用于任意主键类型的表
  • 缺点：
    • 首先需要扫描所有行获得行数，然后需要依次扫描随机个行再丢弃，最后需要扫描目标行，因此扫描行数为 所有行数 + 随机数 + 1，执行代价更高

参考

  • MySQL 技术内幕
  • MySQL 实战 45 讲
  • MySQL 是怎样运行的：从根儿上理解 MySQL