MySQL45讲 第十七讲 如何正确地显示随机消息？

MySQL45讲 第十七讲 随机排序功能的实现与优化

一、引言

本文从一个英语学习 App 首页随机显示单词的功能出发，如果数据库有1万个单词，我要从中选择3个单词作为App首页的单词，如果处理不当，进入App首页会出现卡顿。所以在开发应用程序时，经常会遇到随机排序数据的需求。探讨 MySQL 中实现随机排序的多种方法，包括使用order by rand()的方式以及自行设计的随机算法，并分析它们的执行流程、性能问题及优化策略。

---

二、内存临时表方式（order by rand ()）

（一）实现原理与执行流程

使用select word from words order by rand() limit 3;来实现随机选择 3 个单词的功能。

1. SQL 语句及初步分析：通过explain命令查看执行情况，发现Extra字段显示Using temporary（需要使用临时表）和Using filesort（需要执行排序操作）。

   

2. 算法选择逻辑：由于涉及临时内存表排序，对于内存表，回表过程简单且不涉及磁盘访问，优化器会优先考虑用于排序的行越少越好，因此选择rowid排序。

3. 详细执行步骤

   • 创建一个使用memory引擎的临时表，包含double类型字段R（用于存储随机值）和varchar(64)类型字段W（用于存储单词），且无索引。
   • 从words表按主键顺序取出所有word值，为每个值调用rand()函数生成随机小数存入临时表的R字段，同时存入word值到W字段，此过程扫描行数为 10000。
   • 在无索引的内存临时表上，按照R字段排序。
   • 初始化sort_buffer，包含double类型和整型字段，从内存临时表取出R值和位置信息存入sort_buffer，此过程对临时表全表扫描，扫描行数增加 10000 变为 20000。
   • 在sort_buffer中根据R值排序，不涉及表操作，扫描行数不变。
   • 排序完成后，取出前三个结果的位置信息，到内存临时表中取出word值返回给客户端，此过程访问三行数据，总扫描行数变为 20003。通过慢查询日志验证了扫描行数的分析结果。

   

（二）存在问题

1. 这种方式使用了内存临时表和rowid排序方法，计算过程复杂，扫描行数多，资源消耗大，对性能影响较大，尤其在数据量较大时会严重影响查询效率。

---

三、磁盘临时表方式（order by rand () 相关情况）

是不是所有临时表都存放在内存呢？有没有存在于磁盘中的临时表？

（一）磁盘临时表的触发条件

1. tmp_table_size配置限制内存临时表大小，默认值 16M。若临时表大小超过该值，内存临时表会转成磁盘临时表。磁盘临时表默认使用InnoDB引擎，由internal_tmp_disk_storage_engine参数控制。

（二）排序算法选择

set tmp_table_size=1024;
set sort_buffer_size=32768;
set max_length_for_sort_data=16;
/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on';
/* 执行语句 */
select word from words order by rand() limit 3;
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

1. 以特定参数设置（tmp_table_size = 1024，sort_buffer_size = 32768，max_length_for_sort_data = 16）执行select word from words order by rand() limit 3;语句，分析OPTIMIZER_TRACE结果。

• 由于max_length_for_sort_data=16设置小于word字段长度，采用rowid排序。虽数据总行数计算所需空间超过sort_buffer_size，但未使用临时文件，而是采用了 MySQL 5.6 版本引入的优先队列排序算法。优先队列算法如下图所示：

  

• 因为对于该查询只需取最小的 3 个rowid，优先队列算法可精确得到三个最小值，避免了归并排序算法对全部数据排序的浪费。若查询语句为limit较大值（如limit 1000）且超过sort_buffer_size，则可能使用归并排序算法。

---

四、随机排序方法（替代方案）

不论是使用哪种类型的临时表，order by rand()这种写法都会让计算过程非常复杂，需要大量的扫描行数，因此排序过程的资源消耗也会很大。有没有什么办法解决呢？

（一）随机算法 1

mysql> select max(id),min(id) into @M,@N from t ;
set @X= floor((@M-@N+1)*rand() + @N);
select * from t where id >= @X limit 1;

1. 算法步骤
   • 取得表主键id的最大值M和最小值N。
   • 用随机函数生成一个在最大值到最小值之间的数X = (M - N)*rand() + N。
   • 取不小于X的第一个ID的行。
2. 执行效率与问题：取max(id)和min(id)无需扫描索引，第三步select可利用索引快速定位，扫描行数少（可认为仅 3 行）。但该算法不严格满足随机要求，当ID中间存在空洞时，选择不同行的概率不同，例如有id为 1、2、4、5 时，取到id = 4的概率是其他行的两倍，若空洞较大（如 1、2、40000、40001），算法基本视为有问题。

（二）随机算法 2

mysql> select count(*) into @C from t;
set @Y = floor(@C * rand());
set @sql = concat("select * from t limit ", @Y, ",1");
prepare stmt from @sql;
execute stmt;
DEALLOCATE prepare stmt;

1. 算法步骤
   • 取得整个表的行数C。
   • 计算Y = floor(C * rand())（floor函数取整数部分）。
   • 用limit Y,1取得一行。
2. 执行代价分析：解决了算法 1 的概率不均匀问题。MySQL处理limit Y,1是按顺序读取并丢弃前Y个，然后取接下来的一个记录作为返回结果，此步骤扫描Y + 1行，加上第一步扫描的C行，总共扫描C + Y + 1行，执行代价比随机算法 1 高，但比直接order by rand()小很多。

（三）随机算法 3（随机取多个值）

mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y1，1； //在应用代码里面取Y1、Y2、Y3值，拼出SQL后执行
select * from t limit @Y2，1；
select * from t limit @Y3，1；

1. 算法步骤
   • 取得整个表的行数C。
   • 根据相同随机方法得到Y1、Y2、Y3。
   • 执行三个limit Y, 1语句得到三行数据。
2. 可优化方向：该算法总扫描行数为C+(Y1 + 1)+(Y2 + 1)+(Y3 + 1)，存在优化空间以减少扫描行数，例如可考虑一次性获取多个limit值范围内的数据，减少重复扫描表的次数等（具体优化方案可根据实际情况进一步探讨）。

---

五、总结与思考

1. 直接使用order by rand()在 MySQL 中实现随机排序虽简单，但执行代价大，应尽量避免。
2. 在实际应用中，可结合业务逻辑在业务代码中配合数据库操作，如采用随机算法 2 或 3 等方式实现更高效的随机排序功能。同时，对于随机算法 3，开发者可思考如何进一步优化以减少扫描行数，例如通过更精准的随机数生成策略或改进数据获取方式等，以提升查询性能。