MySQL 17 如何正确地显示随机消息?

假设有一个场景,一个英语学习APP首页有一个随机显示单词的功能,用户每次访问首页的时候,都会随机滚动显示三个单词。

已知表里有10000条记录,来看看随机选择3个单词有什么方法,又存在什么问题。

建表语句:

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

 

粉丝福利!

需要全套2025最新Java面试笔记【点击此处即可】即可免费获取!

内存临时表

首先,可以使用order by rand()来实现:

 
select word from words order by rand() limit 3;

该语句的执行情况:

Extra字段显示Using temporary,表示需要使用临时表;Using filesort,表示需要执行排序操作。

为了更好地分析,这里引用上一篇文章中全字段排序和rowid排序的流程图:

那么对于临时内存表的排序来说,它会选择哪一种算法呢?

对于内存表,回表过程只是简单根据数据行的位置,直接访问内存得到数据,不会导致多访问磁盘。这种情况下,优化器会考虑用于排序的行的大小,所以MySQL会选择rowid排序方法。

因此该语句的执行流程为:

  • 创建一个临时表,临时表用的是MEMORY引擎,表里有两个字段,第一个是double类型,记为字段R,第二个是varchar(64)类型,记为字段W,临时表没有建索引

  • 从words表按主键顺序取出所有的word值,对于每一个word,调用rand()生成一个大于0小于1的随机数,并把这个随机数和word分别存入临时表的R和W字段中,该步骤扫描行数10000行;

  • 初始化sort_buffer,里面有两个字段,一个是double类型,另一个是整型;

  • 从内存临时表中逐行取出R值和“位置信息”(后面解释),分别存入sort_buffer中的两个字段里,这个过程要对内存临时表做全表扫描,该步骤扫描行数10000行;

  • 在sort_buffer中根据R值进行排序;

  • 排序完成后,取出前三个结果的位置信息,依次到内存临时表取出word值,返回给客户端。该步骤访问三行,因此总扫描行数变为20003。

完整的排序执行流程图:

位置信息本质是数据库引擎用来快速定位“一行数据”的唯一标识,一般称为rowid,在不同引擎中其具体形式不同:

  • 对于有主键的InnoDB表,rowid就是主键ID;

  • 对于没有主键的InnoDB表,rowid是由系统生成的6字节的主键;

  • 对于MEMORY引擎,由于其不是索引组织表,可以认为是一个数组,因此rowid其实是数组的下标。

因此,可以总结:order by rand()使用了内存临时表,内存临时表排序时候使用了rowid排序方法。

磁盘临时表

并不是所有的临时表都是内存表,参数tmp_table_size配置限制了内存临时表的大小,默认是16M,如果临时表大小超过了配置值,内存临时表会转成磁盘临时表。

磁盘临时表使用的引擎默认是InnoDB,是由参数internal_tmp_disk_storage_engine控制。

当使用磁盘临时表,对应是一个没有显式索引的InnoDB表的排序过程。这里把tmp_table_size设为1024,sort_buffer_size设为32768,max_length_for_sort_data设为16,查看OPTIMIZER_TRACE,得到部分结果如下:

对于结果:

  • sort_mode里是rowid,这个符合预期,因为max_length_for_sort_data设为16,小于word字段的长度定义,因此使用rowid算法,参与排序是随机值R和6字节的主键;

  • number_of_tmp_files=0,没有用到临时文件是因为这个语句的排序用的是MySQL 5.6版本引入的优先队列排序算法。

对于取R值最小的3个rowid的目标,如果使用归并排序,在算法结束后已经将10000行数据都排好序了,其实浪费了比较多的计算量,而使用优先队列算法就可以精确只得到三个最小值,其执行流程为:

  • 对于10000个准备排序的(R,rowid),取前三行构造大根堆

  • 取下一行(R',rowid'),与堆顶R比较,如果R'小于R,将堆顶弹出,放入(R',rowid');

  • 重复上一步,直到第10000个数据完成比较;

  • 取出堆中的rowid,去临时表里拿到word字段。

在OPTIMIZER_TRACE结果中,filesort_priority_queue_optimization里的chosen=true,就表示使用了优先队列排序算法。

那为什么上篇文章的查询语句(见下)没有使用优先队列呢?

 
select city,name,age from t where city='杭州' order by name limit 1000 ;

是因为如果使用优先队列,需要维护的堆的大小是1000行的(name,rowid),超过了设置的sort_buffer_size大小,所以只能使用归并排序算法。

不过,不论是使用内存临时表还是磁盘临时表,order by rand()这种方法都会让计算过程非常复杂,需要大量的扫描行数。

随机排序方法

把问题简化一下,如果只随机选择一个word值,那么思路为:

  • 取表的主键id的最大值M和最小值N;

  • 用随机函数生成一个最大值与最小值之间的数;

  • 取不小于X的第一个ID的行。

我们把这个算法称为算法1,其执行语句为:

 
mysql> 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;

算法1的效率很高,因为取最值都不需要扫描索引,而第三步的查询也能利用索引快速定位,可以认为总共就扫描了3行。但这个算法并不严格满足要求,因为ID中间可能有空洞,那么选择不同行的概率不一样,并不是真正的随机。

比如4个id,分别是1、2、4、5,如果按照上面的方法,那么取到id=4的概率是取得其他行概率的两倍。

为了做到严格随机,可以用下面流程:

  • 取得整个表的行数C;

  • 取得;

  • limit Y,1取一行。

我们把这个算法称为算法2,其执行语句为:

 
mysql> select count(*) into @C from t;
set @Y = floor(@C * rand());
set @sql = concat("select * from t limit ", @Y, ",1");
prepare stmt from @sql;
execute stmt;
DEALLOCATE prepare stmt;

MySQL处理limit Y,1时会按顺序一个一个读出来,丢掉前Y个后将下一个记录返回,因此需要扫描Y+1行,算上计算行数扫描的C行,总共扫描C+Y+1行,执行代价高于算法1,但小于order by rand()

那么按照算法2的思路,随机取3个word值的做法为:

  • 取得整个表的行数C;

  • 根据相同的随机方法取得Y1,Y2,Y3;

  • 执行三个limit Y,1取得三行数据。

其执行语句为:

 
mysql> 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;

最后总结:对于随机排序,尽量避免使用order by rand()

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。
如若转载,请注明出处:http://www.pswp.cn/pingmian/89876.shtml
繁体地址,请注明出处:http://hk.pswp.cn/pingmian/89876.shtml

如若内容造成侵权/违法违规/事实不符,请联系多彩编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

7-Zip 曝出两个可导致拒绝服务的中危漏洞

研究人员在全球使用最广泛的开源文件压缩软件7-Zip中新发现两个漏洞(CVE-2025-53816和CVE-2025-53817)。这两个漏洞影响7-Zip 25.0.0之前的所有版本,虽然不能实现远程代码执行,但可能引发内存损坏和拒绝服务(Denial of…

史上最简单Conda+Ollama+Open-Webui安装方法!

史上最简单CondaOllamaOpen-Webui安装方法 一、安装Anaconda 1、到Anaconda官网下载conda_24.10.1 链接:https://repo.anaconda.com/archive/Anaconda3-2024.10-1-Windows-x86_64.exe 2.双击安装包,开始安装 选择All Users 切记安装路径不要选C盘&am…

Python-数据库概念-pymysql-元编程-SQLAlchemy-学习笔记

序 欠4前年的一份笔记 ,献给今后的自己。 数据库 概念 数据库:按照数据结构来组织、存储、管理数据的仓库。 诞生 计算机的发明是为了做科学计算的,而科学计算需要大量的数据输入和输出。 早期,可以使用打孔卡片的孔、灯泡的亮灭来…

Linux入门篇学习——借助 U 盘或 TF 卡拷贝程序到开发板上

借助 U 盘或 TF 卡拷贝程序到开发板上我们已经学习了怎么在 ubuntu 和 windows 上互传文件,那么怎么把 ubuntu 或 win 上的程序拷贝到开发板呢,这里给大家介绍第一种方法,使用 U 盘或者 TF 卡来完成,如果大家使用的是 U 盘&#x…

【亲测有效】防检测插件playwright_stealth 2.X版本快速使用

这里写自定义目录标题核心方法apply_stealth_syncuse_sync和use_async一. playwright_stealth 2.0以上版本1.同步方法2.异步方法3.实例二.playwright_stealth 2.0以下版本playwright-stealth 是一个用于 Playwright 的库,旨在帮助自动化脚本避开一些检测机制&#x…

docker安装与简单项目上手

1.docker安装 系统版本为almalinux9.6 首先添加一下docker的软件安装源(源选择的阿里云,只要是rhel的系统都适用,无论是rockylinux还是almalinux还是红帽企业版) dnf config-manager --add-repo https://mirrors.aliyun.com/doc…

计算机网络基础:从协议到通信全解析(大致框架)

本节重点:1.了解网络发展背景,对局域网/广域网的概念有基本认识2.了解网络协议的意义,重点理解TCP/IP五层结构模型3.学习网络传输的基本流程,理解封装和解包分用一、计算机网络发展背景:人与人之间是需要协同工作的&am…

PDF 编辑器:多文件合并 拆分 旋转 顺序随便调 加水印 密码锁 页码背景

各位打工人、学生党们,你们是不是也遇到过这种情况,领导甩来一个PDF让你改,结果你捣鼓半天,发现这玩意儿根本动不了,简直想原地爆炸!别急别急,今天就给你们安利一个办公软件——PDF编辑器&#…

【软件基础学习配置那些事 4-3】3ds Max2026 菜单栏常用命令-----文件、视图、编辑、工具、组

3ds Max学习的笔记小知识!!!!!!!!后续都会补充添加!!!!(个人的一些学习笔记,如有不对,欢迎订正&am…

网络爬虫的介绍

网络爬虫库网络爬虫通俗来讲就是使用代码将HTML网页的内容下载到本地的过程。爬取网页主要是为了获取网中的关键信息,例如网页中的数据、图片、视频等。Python语言中提供了多个具有爬虫功能的库,下面将具的介绍。urlib库:是Python自带的标准库&#xff0…

C# 编程实战进阶:字符串与字符串数组 (3)

目录 1、给定一个整数数组 nums,将数组中的元素向右轮转 k 个位置,其中 k 是非负数。 2、无重复字符的最长字符串 ,给定一个字符串 s 请你找出其中不含有重复字符的最长字符串的长度。 3、给定两个字符串 s 和 t ,它们只包含小…

Python趣味算法:百钱百鸡问题——双重循环优化与算法效率分析

如何用Python解决中国古代数学难题?本文从暴力枚举到高效优化,带你领略算法之美,效率提升100倍! 看在每天坚持分享有趣知识的份上,点个关注吧(づ ̄ 3 ̄)づ 关注是我更新的动力 ̄︶ ̄∗ ̄︶ ̄∗) 作者会分享更多涉及到各种编程语言的有趣知识!(^∀^●)ノシ 目录 …

JAVA_TWO-初识Java2

1.IDEA管理Java程序的结构2.idea编译后的class文件在哪在工程out文件夹下。3.idea一些快捷键4.导入模块File→New→Module from Existing Sources → 添加后缀.iml文件5.注释单行注释 //多行注释 /* 注释内容1注释内容2 */文档注释 /** 注释内容 */ (文档注释内容可…

二、Dify 版本升级教程(LInux-openeuler)

首先,你需要先按照好dify,然后才能升级,本文教程是基与Docker Compose 如果你还没有安装,可以看看这个教程。 一、Dify 私有部署、本地安装教程(LInux-openeuler)_dify1.5版本部署-CSDN博客 安装完成后&a…

Java 大视界 -- Java 大数据在智能安防门禁系统中的多生物特征融合识别与权限管理(280)

💖亲爱的朋友们,热烈欢迎来到 青云交的博客!能与诸位在此相逢,我倍感荣幸。在这飞速更迭的时代,我们都渴望一方心灵净土,而 我的博客 正是这样温暖的所在。这里为你呈上趣味与实用兼具的知识,也期待你毫无保留地分享独特见解,愿我们于此携手成长,共赴新程!💖 本博…

【Tools】Ubuntu24.04安装详细教程

00. 目录 文章目录00. 目录01. Ubuntu 24.04简介02. Ubuntu 24.04下载03. Ubuntu 24.04虚拟机创建04. Ubuntu 24.04安装步骤05. Ubuntu 24.04常用软件06. 附录01. Ubuntu 24.04简介 Ubuntu 24.04 LTS(代号“Noble Numbat”)是Canonical于2024年4月25日发…

linux基础入门Ubuntu 22.04 系统中添加、删除和授予用户 sudo权限

在 Ubuntu 中,sudo 允许授权用户以 root 级别权限执行任务,即使他们不知道 root 用户密码。这对于执行管理任务非常重要,因为它可以避免直接使用 root 用户,从而减少系统被误操作的风险,同时在企业生产中由于ubuntu系统…

npm : 无法加载文件 C:\Program Files\nodejs\npm.ps1

问题描述使用git bash, cmd运行npm都可以,但是用Power Shell运行npm,却报错:npm : 无法加载文件 C:\Program Files\nodejs\npm.ps1,因为在此系统上禁止运行脚本。有关详细信息,请参阅 https:/go.microsoft.com/fwlink/…

【面经】实习经历

文章目录一、求职准备篇1.1提升技术水平1.1.1学什么?1.1.2怎么学?1.2做项目1.3做简历1.4找实习二、求职难度篇找实习难不难?笔试面试三、实习内容篇新人入职 -- 学会看代码参与小需求实习日常实习到底难不难?四、总结 一、求职准备…

The Missing Semester of Your CS Education 学习笔记以及一些拓展知识(二)

文章目录The Missing Semester of Your CS Education 学习笔记以及一些拓展知识Bash脚本笔记部分一些在Bash脚本中的常用命令补充常用标准输入输出命令常用环境变量(普通变量)控制命令常用系统时间信息获取命令常用函数执行状态控制命令常用脚本执行控制命令Bash脚本的创建和运…