本文介绍 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行。
[En]
Sort by priority queue, with only 10003 lines scanned.
磁盘临时表
当临时表的数据量大小大于 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；
Original: https://www.cnblogs.com/flowers-bloom/p/mysql45-random-query-and-tmp-file.html
Author: flowers-bloom
Title: MySQL45讲之随机查询和临时表
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/508124/
转载文章受原作者版权保护。转载请注明原作者出处！