MySQL45讲之随机查询和临时表

时间:2021-09-24 10:42:38   收藏:0   阅读:42

前言

本文介绍 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 时,会使用内存临时表。

工作流程:
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

位置信息含义:

磁盘临时表

当临时表的数据量大小大于 tmp_table_sizesort_buffer 足够时,会先采用 MySQL5.6 版本引入的内存优先队列的排序方式。

大致工作流程:
1、对于要排序的 10000 行数据,先取前 3 行,构成一个堆
2、取一行剩余的数据添加到堆中,再调整
3、重复第二步,直至所有剩余的行插入完成

如果临时表的数据量大小大于 tmp_table_size 且优先队列的大小大于 sort_buffer,那么还是会采用磁盘临时表,和归并排序的方式排序。

优化

对于 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;

参考

原文:https://www.cnblogs.com/flowers-bloom/p/mysql45-random-query-and-tmp-file.html

评论(0
© 2014 bubuko.com 版权所有 - 联系我们:wmxa8@hotmail.com
打开技术之扣,分享程序人生!