前言
本文介绍 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] 如何正确地显示随机消息