17 | 如何正确地显示随机消息？

林晓斌



该思维导图由 AI 生成，仅供参考

我在上一篇文章，为你讲解完 order by 语句的几种执行模式后，就想到了之前一个做英语学习 App 的朋友碰到过的一个性能问题。今天这篇文章，我就从这个性能问题说起，和你说说 MySQL 中的另外一种排序需求，希望能够加深你对 MySQL 排序逻辑的理解。
这个英语学习 App 首页有一个随机显示单词的功能，也就是根据每个用户的级别有一个单词表，然后这个用户每次访问首页的时候，都会随机滚动显示三个单词。他们发现随着单词表变大，选单词这个逻辑变得越来越慢，甚至影响到了首页的打开速度。
现在，如果让你来设计这个 SQL 语句，你会怎么写呢？
为了便于理解，我对这个例子进行了简化：去掉每个级别的用户都有一个对应的单词表这个逻辑，直接就是从一个单词表中随机选出三个单词。这个表的建表语句和初始数据的命令如下：
mysql> CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=0;
  while i<10000 do
    insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();
为了便于量化说明，我在这个表里面插入了 10000 行记录。接下来，我们就一起看看要随机选择 3 个单词，有什么方法实现，存在什么问题以及如何改进。
内存临时表首先，你会想到用 order by rand() 来实现这个逻辑。
mysql> select word from words order by rand() limit 3;
这个语句的意思很直白，随机排序取前 3 个。虽然这个 SQL 语句写法很简单，但执行流程却有点复杂的。

公开

同步至部落

取消

完成

0/2000

荧光笔

直线

曲线

笔记

复制

AI

深入了解
翻译
英语
中文简体
中文繁体
法语
德语
日语
韩语
俄语
西班牙语
阿拉伯语
解释
总结

本文深入介绍了在MySQL中实现随机消息显示的技术特点和优化方法。作者以一个英语学习App的性能问题为例，详细讲解了随机选择单词的SQL语句设计、执行流程和优化方法。文章首先介绍了内存临时表排序方法，并分析了其执行流程和扫描行数。作者还解释了内存临时表排序使用的rowid排序方法和rowid的概念。此外，文章强调了对于InnoDB表，执行全字段排序会减少磁盘访问，因此会被优先选择。随后，文章详细讲解了磁盘临时表的排序过程，并介绍了MySQL 5.6版本引入的优先队列排序算法。最后，作者提出了随机排序的三种方法，并对比了它们的执行代价。通过深入的技术分析和实例讲解，为读者提供了在MySQL中实现随机消息显示的全面指南。文章内容涵盖了MySQL中随机消息显示的技术特点、优化方法以及实际应用中的注意事项，对于需要在MySQL中实现随机消息显示的开发人员和数据库管理员具有重要的参考价值。

仅可试看部分内容，如需阅读全部内容，请付费购买文章所属专栏
《MySQL 实战 45 讲》，新⼈⾸单¥68

立即购买

登录后留言

全部留言(185)

最新
精选

老杨同志
置顶
对应单词这种总量不是很多的数据，第一感觉应该装jdk缓存或者redis缓存。由于需要随机访问，数组比较好。假如一个单词平均10个字节，10*10000，不到1M就装下了。如果一定要用数据库来做，老师的方案1比较好，空洞的问题，如果单词库不变，可以在上线前整理数据，把空洞处理调。比如：原来单词存在A表，新建B表，执行 insert into B(word) select word from A. B的id是自增的，就会生成连续的主键。当然如果A表写比较频繁，且数据量较大，业务上禁用这种写法，RR的隔离级别会锁A表
作者回复: 重新整理表这个思路很赞👍🏿 看得出你是业务经验很丰富啊，这几次问题，对底层实现和业务功能的平衡，考虑点很不错
2018-12-21
7
291
雪中鼠
置顶
如果按照业务需求，随机取三个，数据库还在设计阶段,可以增加一个主键字段,用来记录每行记录的rowid，这样一万行，那就是连续的一万，然后随机，用该随机rowid回表查询该行记录
作者回复: 这个也是个好方法，就是确保连续，可以快速的得到C和几个偏移量
2018-12-21
5
30
HuaMax
假设Y1，Y2，Y3是由小到大的三个数，则可以优化成这样，这样扫描行数为Y3 id1 = select * from t limit @Y1，1； id2= select * from t where id > id1 limit @Y2-@Y1，1； select * from t where id > id2 limit @Y3 - @Y2，1；
作者回复: 👍🏿
2018-12-21
8
156
大白给小白讲故事
为什么随机算法2比order by rand()的代价小很多？因为随机算法2进行limit获取数据的时候是根据主键排序获取的，主键天然索引排序。获取到第9999条的数据也远比order by rand()方法的组成临时表R字段排序再获取rowid代价小的多。
作者回复: 对的，你是第一个回答正文中间问题的😄👍🏿
2018-12-21
5
99
吴宇晨
我觉得可以按Y排个序，第一条取完，拿到对应id，然后有一条语句就是where id大于xxx，limit y2-y1，1
作者回复: 抓住了关键点👍🏿
2018-12-21
4
94
倪大人
课后题可以在随机出Y1、Y2、Y3后，算出Ymax、Ymin 再用 select id from t limit Ymin，(Ymax - Ymin)；得到id集后算出Y1、Y2、Y3对应的三个id 最后 select * from t where id in (id1, id2, id3) 这样扫描的行数应该是C+Ymax+3
作者回复: 漂亮
2018-12-21
18
46
大神仙
老师，limit n order by 非索引字段进行分页查询。数据库符合条件的count=147000条，分页查询count也正确，但是分页查询出的147000条数据中存在重复数据。 1，这个我看网上解释是因为堆排序算法不稳定导致的。这个说法是否正确。 2，我查了很多资料，没找到，或者您能给我个指导，我去查查
作者回复: 我的理解是说，你碰到了这种情况： limit n, a; 显示a条记录；然后 limit n+a, a显示第二组a条件记录；这两组a个记录出现了重复数据对吧，是的，是因为limit 有可能出现两种算法，比如直接排序和优先队列排序，就是不同的结果。而limit 后面的参数，是会影响算法的
2019-02-20
13
30
王飞洋
归并排序，优先队列，算法无处不在。
作者回复: 要说算法还是隔壁王老师讲的专业，这里咱们就只追求MySQL 里面用到的，能给大家讲明白就行了😄
2018-12-21
3
23
梦康
翻了下评论，没人问优先队列排序里的 row_size 和 rows_estimate 是如何计算的。想了半天没想明白。
作者回复: 帮你贴下你自己的答案哈 https://mengkang.net/1338.html
2019-02-13
4
22
Mr.Strive.Z.H.L
老师你好，回顾这篇的时候突然有个疑惑。执行器只是调引擎接口获取结果，但是我认为order by的排序过程应该是在执行器执行的吧？内存临时表使用的memory引擎，应该也是在server端，而磁盘临时表应该是innodb内部。我这么理解对吗？还是说整个排序过程全部都在innodb内部执行？对此突然有点疑惑………
作者回复: mysql的执行过程都是由执行器来调度的不论创建memory临时表还是innodb临时表，都是执行器调用引擎的创建表接口实现的写数据和读数据也是排序这个操作，是在server层做的
2018-12-29

19

收起评论