如何正确地显示随机消息？（随机抽取 3 个词）

需求：从用户的英语单词表中，随机选择三个单词，创表和插入数据如下：

# 建表
CREATE TABLE `words` (`id` INT(11) NOT NULL AUTO_INCREMENT,`word` VARCHAR(64) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=INNODB;
# 插入数据
DELIMITER ;;
CREATE PROCEDURE idata()
BEGINDECLARE i INT;SET i=0;WHILE i<10000 DOINSERT 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();

内存临时表

实现的逻辑可以这样

# 每行随机一个数字之后，排序
select word from words order by rand() limit 3;

通过 explain 查看语句的执行情况

Using temporary：表示用了临时表，临时表分内存临时表和磁盘临时表，看实际数据量才知道有没有变为磁盘临时表
Using filesort：表示的是需要执行排序操作

对于内存临时表（使用 Memory 引擎，这时候的数据量能够存在内存临时表下，tmp_table_size 默认 16M，超过了才会转为磁盘临时表），回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘，MySQL 就会选择 rowid 排序

创建一个临时表。这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引
从 words 表中，按主键顺序取出所有的 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中，到此，扫描行数是 10000
现在临时表有 10000 行数据了，接下来在这个没有索引的内存临时表上，按照字段 R 排序
初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型
从内存临时表中一行一行地取出 R 值和 pos 位置信息（MEMORY 引擎不是索引组织表，这个 pos 即 rowid，其实是数组下标）
分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成了 20000
在 sort_buffer 中根据 R 的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数
排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003

使用 slow.log 验证扫描到的行数

# 查看慢日志存储位置
show variables like '%slow%'; 
# 开启慢查询
set global slow_query_log=ON;
# 记录了包含所有执行时间超过参数 long_query_time 的 sql
set long_query_time=0;select word from words order by rand() limit 3;

在这里插入图片描述
5. 小结：order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法

磁盘临时表

tmp_table_size 限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表
磁盘临时表默认为 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

在这里插入图片描述
4. 分析

这里的 sort_mode 现实 rowid 排序
num_examined_rows 总行数 1w，R 字段 8 字节，rowid 字段 6 字节，合计 14w 字节，应该超过 sort_buffer_size 的 32768 字节，但是 num_initial_chunks_spilled_to_disk 却为 0？

原因

使用了优先队列排序算法，其实只要取 R 的最小的 3 个 rowid
对于这 10000 个准备排序的 (R,rowid)，先取前三行，构造成一个堆
取下一个行 (R’,rowid’)，跟当前堆里面最大的 R 比较，如果 R’小于 R，把这个 (R,rowid) 从堆中去掉，换成 (R’,rowid’)
重复第 2 步，直到第 10000 个 (R’,rowid’) 完成比较

随机排序选择方法

方案一

假设随机选择 1 个值，后面重复 3 次
取得这个表的主键 id 的最大值 M 和最小值 N
用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N
取不小于 X 的第一个 ID 的行

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;

在这里插入图片描述

缺点
- 对于 id 数据不均匀，例如 1、2、40000、40001，那么取到 40000 的几率就很大

方案二

取得整个表的行数，并记为 C
取得 Y = floor(C * rand())。 floor 函数在这里的作用，就是取整数部分
再用 limit Y,1 取得一行

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;