Mysql 系列 | order by rand()
上一篇 Mysql 系列 | order by 中介绍了全字段排序、rowid 排序以及借助索引优化排序。
在更复杂的 SQL 中，order by 还会有其他排序方式。
场景
在英语学习 APP 首页，每次打开会随机显示三个英语单词。也就需要每次从表中随机查出三条数据。
表数据越多，随机查询会变得很慢，影响首页打开的速度。
有什么办法可以快速取到随机的三条数据呢！
内存临时表
select word from words order by rand() limit 3;
explain select word from words order by rand() limit 3;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: words
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 9980
filtered: 100.00
Extra: Using temporary; Using filesort
-
explain 结果中，Extra 显示 Using temporary 表示要使用临时表。Using filesort 表示需要执行排序。则这条语句需要在临时表上排序。
-
对于内存临时表，回表也是在内存上查找数据，不会导致多访问磁盘。所以对于临时表排序，会选择排序内容较少的 rowid 排序。
-
语句的执行过程如下，
-
创建一个临时表，临时表使用 memory 引擎。表中有两个字段，一个是 double（简称为 R），一个是 varchar(64)（简称为 W），并且表中没有索引。
-
从 words 表中，按主键顺序取出所有 word。对于每一个 word，调用函数 rand() 生成 0 和 1 之间的小数。把取到的 word 和随机小数分别放在 W 和 R 字段中。
-
在临时表上，按照 R 进行排序
-
初始化 sort buffer，里面有两个字段，一个存放 R，一个是 rowid。memory 引擎中用的不是索引组织表，可以理解为是一个数组，相应的 rowid 就是数组的下标。
-
从临时表中一行一行取出 R 和位置信息（rowid），放入 sort buffer 的两个字段中。这个过程会扫描临时表全表。
-
在 sort buffer 中，根据 R 值进行排序。这个过程不涉及数据扫描。
-
排序结束后，取出前三个结果的位置信息（rowid），依次到临时表取出 word，返回给客户端。
-
磁盘临时表
- 当临时表内存不够用时，会转移到磁盘上。默认为 16M
mysql> show variables like "tmp_table_size";
+----------------+----------+
| Variable_name | Value |
+----------------+----------+
| tmp_table_size | 33554432 |
+----------------+----------+
1 row in set (0.00 sec)
- 磁盘临时表默认使用的引擎为 InnoDB
mysql> show variables like "internal_tmp_disk_storage_engine";
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
1 row in set (0.00 sec)
-
因为只取三条数据，这里使用优先队列排序算法，可以精确得到前三个数据，而后面的数据不需要排序。执行顺序如下，
-
对于 10,000 个准备排序的（R,rowid），取出前三行构成一个堆
-
取下一个（R,rowid），和堆中最大值比较，比最大值小则替换掉原来的最大值。这个过程中，为了更快地拿到堆中的最大值，最大值总是放在最上面。
-
以此类推，找到 R 最小的三行数据。这个过程不需要借助临时文件。
-
得到取出的三行的 rowid，然后去临时表取得 word。
-
-
不管使用哪种类型的临时表，order by rand() 计算过程都比较复杂，会耗费比较多的资源。
随机排序方法
方案一
-
取随机一行数据
-
取出表中 ID 的最大值 M 和最小值 N
-
用随机函数生成 M、N 之间的一个数，X = (M - N) * rand() + N
-
去表中取 ID 不小于 X 的第一条数据
-
-
这个方法效率很高，扫描行数很少。但是 id 中间可能有空洞，不能做到真正的随机。
方案二
-
取随机一行数据
-
取出表的行数 C
-
得到 Y = floor(C * rand())
-
去表中取数据 limit Y,1
-
-
limit Y,1 的执行顺序是，从表中依次读数据，然后丢掉前面 Y 个，返回第 Y+1 条数据。
-
方案二的扫描行数为 C + Y + 1，比方案一多很多，不过可以方案一的问题。
-
按照方案二取三条随机数，则需要算出三个 Y，分别用 limit 找到第 Y+1 条数据。这样扫描行数是 C + Y1 + Y2 + Y3 + 3。
-
对于方案二取三条随机数，可以进一步优化，减少扫描行数。
-
从扫描行数考虑，方案二和 order by rand() 查不了多少。但是 limit 取数据是根据 ID 索引，远远比前面的构成临时表，再根据 R 字段无索引排序效率要高。
--- 在数据库的使用中，尽量把逻辑写在业务代码中，让**数据库只用来读写数据**。
