『MySQL 实战 45 讲』17 - 如何正确地显示随机消息？（随机抽取 3 个词）

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

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

# 建表
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();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

内存临时表

1. 实现的逻辑可以这样

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

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

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

3. 对于内存临时表（使用 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

4. 使用 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;
1
2
3
4
5
6
7
8

5. 小结：order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法

磁盘临时表

1. tmp_table_size 限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表
2. 磁盘临时表默认为 InnoDB 存储引擎，由 internal_tmp_disk_storage_engine 控制
3. 复现场景，使用下面的参数

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
2
3
4
5
6
7
8
9
10
11

4. 分析

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

5. 原因

• 使用了优先队列排序算法，其实只要取 R 的最小的 3 个 rowid
  

• 对于这 10000 个准备排序的 (R,rowid)，先取前三行，构造成一个堆

• 取下一个行 (R’,rowid’)，跟当前堆里面最大的 R 比较，如果 R’小于 R，把这个 (R,rowid) 从堆中去掉，换成 (R’,rowid’)

• 重复第 2 步，直到第 10000 个 (R’,rowid’) 完成比较

随机排序选择方法

1. 方案一

• 假设随机选择 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;
1
2
3

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

2. 方案二

• 取得整个表的行数，并记为 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;
1
2
3
4
5
6

• 缺点
  • 虽然解决了方案一的问题，但是 MySQL 处理 limit Y,1 的做法就是按顺序一个一个地读出来，丢掉前 Y 个，然后把下一个记录作为返回结果，因此这一步需要扫描 Y+1 行。再加上，第一步扫描的 C 行，总共需要扫描 C+Y+1 行，执行代价比随机算法一的代价要高