MySQL中的随机抽取的实现
作者:XHHP 发布时间:2024-01-17 21:07:42
1. 引言
现在有一个需求是从一个单词表中每次随机选取三个单词。
这个表的建表语句和如下所示:
mysql> Create table 'words'(
'id' int(11) not null auto_increment;
'word' varchar(64) default null;
primary key ('id')
) ENGINE=InnoDB;
然后我们向其中插入10000行数据。接下来我们看看如何从中随机选择3个单词。
2. 内存临时表
首先,我们通常会想到用order by rand()来实现这个逻辑:
mysql> select word from words order by rand() limit 3;
虽然这句话很简单,但是执行流程则比较复杂。我们使用explain来看看语句的执行情况:
Extra字段中Using temporary表示需要使用临时表,Using filesort表示需要进行排序。也就是需要进行排序操作。
对于InnoDB表来说,执行全字段排序能够减少对于磁盘的访问,所以会被优先选择。
而对于内存表来说,回表过程只是简单地根据数据行的位置,直接访问内存得到数据,根本不会导致多访问磁盘。所以这时MySQL会优选选择rowid排序。
我们接下来再来梳理下这条语句的执行流程:
创建一个临时表,这个表使用memory引擎,表里有两个字段,第一个字段是double类型,记为R,第二个字段是varchar(64)类型,记为W。并且这个表没有索引。
从words表中,按主键顺序取出所有的word。对于每个word,调用rand()函数随机生成一个大于0小于1的随机小数,并把这个随机小数和word分别存入临时表的R和W字段中。
接下来就是按照字段R进行排序
初始化sort_buffer。sort_buffer有两个字段,一个是double类型,另一个是整型。
从内存临时表中一行行取出R值和位置信息,分别存入sort_buffer的两个字段里。
sort_buffer按照R值进行排序
排序完成后,取出前三个结果的位置信息,到内存临时表中取出相应的word,返回给客户端。
流程示意图如下所示:
上面讲的位置信息,其实就是行所在的位置,也就是我们之前说的rowid。
对于InnoDB引擎来说,对于有没有主键表来说有两种处理方式:
对于有主键的InnoDB表来说,这个rowid就是主键id
对于没有主键的InnoDB表来说,这个rowid是由系统生成的,用来标识不同行。
因此,order by randn()使用了内存临时表,内存临时表的排序方法用的是rowid排序方法。
3. 磁盘临时表
不是所有的临时表都是内存临时表。tmp_table_size这个配置限制了内存临时表的大小,如果超过了这个大小,就会使用磁盘临时表。InnoDB引擎就是默认使用磁盘临时表。
4. 优先队列排序算法
在MySQL5.6之后,引入了优先队列排序算法,这种算法是不需要使用临时文件的。而原本的归并排序算法则是需要使用临时文件。
因为当你使用归并算法的时候,其实你只需要得到前3,但是你是用完归并排序,那已经整体有序了,造成了资源的浪费。
而优先队列排序算法则可以只取到前三,执行流程如下:
对于这10000个准备排序的(R,rowid),先取前三行,构造成一个堆,并且将最大的值放在堆顶;
取下一行(R’,rowid’),跟当前堆里面最大的R比较,如果R’小于R,则把(R,rowid)从堆中去掉,换成(R’,rowid’)。
不断重复上面的过程。
流程如下图所示:
但是当limit的数比较大时,维护堆比较困难,所以又会使用归并排序算法。
来源:自己整理的MySQL实战45讲笔记
来源:https://blog.csdn.net/weixin_41799019/article/details/127704560
猜你喜欢
- ubuntu18.04版本,python版本python2.7,python3.5,python3.6因为安装一些库会安装到python3.
- 创建表时创建外键创建两个表格,一个名为class,create table classes(id int not null primary
- 查询语句的优化是SQL效率优化的一个方式,可以通过优化sql语句来尽量使用已有的索引,避免全表扫描,从而提高查询效率。最近在对项目中的一些s
- 用过一段时间的caffe后,对caffe有两点感受:1、速度确实快; 2、 太不灵活了。深度学习技术一直在发展,但是caffe的更新跟不上进
- 我就废话不多说啦,还是直接看代码吧!import osimport sysimport djangosys.path.append(r
- 目录一、前言二、Json.loads与eval 性能对比1. eval2. json.loads一、前言最近发现一些小伙伴使用eval来处理
- Socket有一个缓冲区,缓冲区是一个流,先进先出,发送和取出的可自定义大小的,如果取出的数据未取完缓冲区,则可能存在数据怠慢。其中【rec
- 写的dht协议搜索的程序,这几天优化了一下发现速度确实快了好多。但是出现了一个新的问题,内存直接飙升,我开了十个爬虫占用内存800m。开始我
- 设置MySQL数据同步(单向&双向)由于公司的业务需求,需要网通和电信的数据同步,就做了个MySQL的双向同步,记下过程,以后用得到
- 匹配中文字符的正则表达式: [\u4e00-\u9fa5]匹配双字节字符(包括汉字在内):[^\x00-\xff]应用:计算字符串的长度(一
- Django View官方文档一个视图函数(类),简称视图,是一个简单的 Python 函数(类),它接受Web请求并且返回Web响应。响应
- 任务1、我的咖啡馆你做主元组coffee_name=('蓝山','卡布奇诺','拿铁',
- 概述TensorFlow2 的基本操作和 Numpy 的操作很像. 今天带大家来看一看 TensorFlow 的基本数据操作.创建数据详细讲
- 本文实例分析了php字符串截取函数用法。分享给大家供大家参考。具体分析如下:php自带的截取字符串的函数只能处理英文,数字的不能截取中文混排
- 今天第一次玩SQL Server 2005,需要配置远程连接。我废话少说,直入主题,总结下我从新手立场在win2003 server环境下去
- 本文实例讲述了python实现的爬取电影下载链接功能。分享给大家供大家参考,具体如下:#!/usr/bin/python#coding=UT
- 数据库开启慢查询日志修改配置文件在配置文件my.ini中加上下面两句话log-slow-queries = C:\xampp\mysql_s
- 定义:在Django框架中,模板是可以帮助开发者快速生成呈现给用户页面的工具模板的设计方式实现了我们MVT中VT的解耦,VT有着N:M的关系
- 本文实例为大家分享了python实现自动下载sftp文件的具体代码,供大家参考,具体内容如下实现功能:利用python自动连接sftp,并下
- 一、介绍正则表达式是一个特殊的字符序列,计算机科学的一个概念。通常被用来检索、替换那些符合某个模式(规则)的文本。许多程序设计语言都支持利用