MySQL45讲之随机查询和临时表 - flowers

前言

本文介绍 MySQL 随机查询的工作流程、优化随机查询的方式、和临时表。

工作流程

根据下表结构建立 words 表，并通过过程插入 10000 条模拟数据。

CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

随机查询场景，“从上表中随机查询三个 wrod”。简单直接的查询方式就是，SELECT * FROM words ORDER BY rand() LIMIT 3;，下面分析下这条语句的工作流程。

内存临时表

这个排序需要使用临时表，当临时表数据量小于 tmp_table_size 时，会使用内存临时表。tmp_table_size 默认是 16M。

一般情况下，会优先采用全字段排序方式，因为可以减少一次回表查询。不过，因为是使用的内存临时表，所以 rowid 方式回表也是直接查询内存，并且 rowid 方式因为只存储 rowid 和排序字段，在相同大小的 sort_buffer 下，可以存储更多的行，避免或减少外存文件的使用。所以，MySQL 会选择 rowid 排序方式。

如果不存在 LIMIT 关键字，工作流程：
1、创建一个临时表，采用内存 memory 引擎，包含一个 double 字段，表示 rand() 函数生成的随机数，另一个是 word 字段
2、从原表中根据主键取出 word 值，并执行 rand 函数生成一个 0 ~ 1 的数字写入到临时表，直至写完 10000 行，扫描行数 10000
3、初始化 sort_buffer，确定只有一个 double 和一个整形字段
4、从临时表中按行读取 double 字段，和位置信息，写入到 sort_buffer，扫描行数变为 20000
5、根据 double 字段排序
6、排序完后，根据前 3 行的位置信息到临时表中取对应的 word，返回客户端，扫描行数变为 20003

位置信息含义：

• 对于有主键的InnoDB表来说，这个rowid就是主键ID；
• 对于没有主键的InnoDB表来说，这个rowid就是由系统生成的；
• MEMORY引擎不是索引组织表。在这个例子里面，你可以认为它就是一个数组。因此，这个rowid其实就是数组的下标。

如果语句中存在 LIMIT 关键字，所以还会使用 MySQL5.6 提供的优先队列的排序方式进行优化。

大致工作流程：
1、根据主键从原数据表中，先取前 3 行，构成一个堆
2、取一行剩余的数据添加到堆中，再调整
3、重复第二步，直至所有剩余的行插入完成

按优先队列的方式排序，只需要扫描 10003 行。

磁盘临时表

当临时表的数据量大小大于 tmp_table_size 时，则会采用磁盘临时表。并且，这时候回表意味着随机读文件，所以会搭配全字段排序方式。

优化

对于 SELECT * FROM words ORDER BY rand() LIMIT 3; 需要扫描 20003 行，随着数据量增大，它执行速度会越来越慢，有什么优化方式么？

方案1：

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 的范围，然后在 id 范围内进行随机，再取不小于随机值的行（注意不能用 id = @X 的判断逻辑，否则可能 id 不存在）。

虽然这个方案可以随机，但是随机是不公平的。因为表可能会删除数据，id 不连续，存在空洞。

方案2：

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；

为了保证随机是公平的，那么可以随机 [1, 总行数] 中的一个数，然后通过分页操作拿到对应的行。此时需要扫描 C + Y1 + Y2 + Y3 + 3 行。

进一步优化，可以采用下面的查询方式，只需要扫描 C + max(Y1, Y2, Y3) + 3 行。

# 前提，对 Y1、Y2、Y3 提前排序好
id1 = select * from t limit @Y1，1；
id2 = select * from t where id > id1 limit @Y2 - @Y1，1；
id3 = select * from t where id > id2 limit @Y3 - @Y2，1；

参考

• [1] 如何正确地显示随机消息