order by rand() 实现随机获取消息

业务场景

比如，要做一个学习单词的App，实现用户在打开首页的时候，随机显示三个单词：

// 创建单词表
mysql> create table words(
	`id` int NOT NULL AUTO_INCREMENT,
	`word` varchar(64) DEFAULT NULL,
	PRIMARY KEY(`id`)
)ENGINE=InnoDB;

// 插入10000行模拟数据
mysql> insert into words values(1, "abandon") (...) (...);

/********************************************************
*  @ order by rand() 
*  实现随机排序,随机选取三个单词
********************************************************/
mysql> select word from words order by rand() limit 3;

// 使用explain命令查看该语句的执行情况
mysql> explain select word from words order by rand() limit 3;

通过学习前面 Mysql数据库 order by 实现原理，我们知道，对于使用InnoDB引擎的表来说，全字段排序，可以不用回表，所以，会被优先采用。但是，对于内存表来说，数据已经读到内存了，无非是根据数据行的位置，直接访问内存的得到，不会再访问磁盘。所以，对于使用内存临时表进行的排序来说，优化器没有了回表的顾虑，rowid 排序会被优先采用。

内存临时表

explain结果，Extra字段 Using temporary表示使用临时表，Using filesort表示需要排序。

SQL语句执行流程

1. 创建临时表，使用memory引擎。该临时表有两个字段，一个 double 类型字段，我们记为R，一个varchar（64）类型字段，我们记为W，表上没有索引。
2. 根据主键id的顺序，从 words 表中读取 word 值。对于每个 word 值，调用rand()生成一个0~1之间的随机小数，放到R字段，并将对应的 word 放入W字段。
3. 此时，临时表中有10000行数据，我们需要根据 R 的值进行排序；
4. 初始化 sort_buffer，确定放入 doule 和 int 两个字段；（其中：double字段保存临时表的R值，int字段保存“位置信息”，后面我们解释为什么保存的是位置信息）
5. 从内存临时表中一行一行读取R和 “位置信息”放入sort_buffer；
6. 根据R字段进行排序，排序完，取前三行结果，依次到内存临时表根据位置信息取出word值，返回。

Mysql表是如何定位一条记录的

答案是：rowid，每个引擎用来唯一标记行信息的标识。

• 对于创建了主键的 InnoDB 表来说，rowid 就是主键ID；
• 没有创建主键的 InnoDB 表，系统会默认生成一个6字节的 rowid 作为主键；
• 对于Memory引擎的表，“位置信息”可以理解为数组的下标。

磁盘临时表

临时表一定是内存临时表吗？不一定。

参数 tmp_table_size 限制了内存临时表的大小，默认16 M，如果超过最大限制，就会使用磁盘临时表，磁盘临时表使用的引擎默认是 InnoDB，当使用磁盘临时表的时候，对应的就是一个没有显示索引的InnoDB表的排序过程。

排序优化

经过上面的排序完成后，所有的数据都有序了，但是我们只需要前三个，有点浪费。所以Mysql在5.6版本之后引入新的排序算法：优先队列排序算法（堆排序）。

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

如何得到严格的随机结果

1. 扫描全表，得到总行数C
2. 取得 Y=floor(C * rand())。floor取整。
3. select * from words limit Y,1 取得一行

limit Y, 1; 和 id >= Y limit 1; 一样吗？
不一样，MySQL 处理 limit Y,1 的做法就是按顺序一个一个地读出来，丢掉前 Y 个，然后把下一个记录作为返回结果。

笔记参考于极客时间《MySQL实战45讲》