第十七讲笔记——如何显示随机消息

从单词表中随机选出三个单词。

CREATE TABLE `words` (
`id` int(11) NOTNULLAUTO_INCREMENT,
`word` varchar(64) DEFAULTNULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

随机插入 10000 行数据，之后来分析。

内存临时表

select word fromo words order by rand() limit 3

这个 SQL 就是随机排序取前三个，但执行过程有点复杂。

我们使用 explain 这个命令来看看这个语句的执行情况：

在 Extra 这个字段上的 Using temporary，表示使用了临时表；Using filesort，表示需要执行排序操作。

我们上节课得出的结论：对于 InnoDB 表来说，执行全字段排序会减少磁盘访问，会被优先选择。

这里强调的是 InnoDB 表，但是优化器（server 层）就没有了这个顾虑，那么它会优先考虑，用于排列的行越少越好，所以，MySQL 这时会选择 rowid 排序。那这条语句的执行过程是这样：

1. 创建一个临时表，这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且这个表没有主键。
2. 从 word 表中，按主键顺序取出所有 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并和 word 存入临时表的 R 和 W 字段中，到此，扫描行数是 10000；
3. 现在在没有索引的内存临时表中，按照字段 R 进行排序；
4. 初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型。
5. 从内存临时表中一行一行的取出 R 值和位置信息，分别存入 sort_buffer 中的两个字段里。这个过程要对临时表进行全表扫描，扫描行数变成了 20000；
6. 在 sort_buffer 中根据 R 的值进行排序。这里没有涉及到表操作，扫描行数不变；
7. 排序完成后，取出前三个结果的位置信息，依次从内存临时表中取出 word 值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003。

在这里 pos 就是位置信息。这里就涉及到：MySQL 表是用什么方法来定位“一行数据”的。

如果我们把表的主键删掉，那么 InnoDB 会自己生成一个长度为 6 字节的 rowid 来作为主键。

这就是排序模式中 rowid 的来源，rowid 实际上表示：每个引擎用来唯一表示数据行的信息。

• 对于有主键的 InnoDB 表，这个 rowid 就是主键 ID；
• 对于没有主键的表，这个 rowid 就是由系统生成；
• MEMORY 引擎不是索引组织表。这个例子中约等于数组，rowid 就是下标。

总结：order by rand() 使用了内存临时表，内存临时表排序时使用了 rowid 排序方法。

磁盘临时表

tmp_table_size 这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小大于这个值，那么就会转成磁盘临时表。

基本上就是使用外部排序了。

不过 MySQL 5.6 版本引入了一个叫：优先队列排序算法。

上面的 SQL 语句就可以使用这个算法，原理是使用小根堆进行排序，因为只要取 3 个数据。

随机排序方法

先简化一下问题，如果只随机选择 1 个 word 值，怎么做呢？

1. 取这个表的主键 id 的最大值 M 和最小值 N；
2. 用随机函数生成一个最大值到最小值之间的数 $X=(M-N)\ast rand()+N$。
3. 取不小于 X 的第一个 ID 的行。

把这个算法，暂时称为算法 1。

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

在 MySQL 中 @.. 是用户变量，@@.. 是系统变量。

这个算法效率很高，但是不严格满足题目随机要求，因为 ID 中间可能有空洞，导致不同行的概率是不一样的。

现在来看算法 2：

1. 取得整张表的行数，记为 C；
2. 取得 $Y=floor(C\ast rand())$。floor 函数在这里作用是取整数部分。
3. 再用 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;

由于 limit 后面的参数不能直接跟变量，所以使用了 prepare+execute 方法，相当于 SQL 语句的拼接。

这个算法就是按顺序一个个读，丢到前 Y 个，取 Y + 1 行，这要扫描 Y + 1 行。执行代价比算法 1 高。当然，和 order by rand() 比起来开销还是小很多。

按照算法 2 的思路，随机取三个呢？那就是取整张表行数 C ，得出三个随机数，再执行三个 limit Y,1 语句。

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；