搜尋
首頁資料庫mysql教程MySQL中的隨機抽取如何實現
MySQL中的隨機抽取如何實現Jun 03, 2023 am 08:25 AM
mysql

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來看看語句的執行情況:

MySQL中的隨機抽取如何實現

Extra欄位中Using temporary表示需要使用臨時表,Using filesort表示需要進行排序。也就是需要進行排序操作。

對於InnoDB表來說,執行全字段排序能夠減少對於磁碟的訪問,所以會被優先選擇。

MySQL中的隨機抽取如何實現

而對於記憶體表來說,回表過程只是簡單地根據資料行的位置,直接存取記憶體得到數據,根本不會導致多存取磁碟。所以這時MySQL會優選選擇rowid排序。

MySQL中的隨機抽取如何實現

我們接下來再來梳理下這條語句的執行流程:

  • 建立一個臨時表,這個表使用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,回傳給客戶端。

流程示意圖如下所示:

MySQL中的隨機抽取如何實現

#上面講的位置信息,其實就是行所在的位置,也就是我們之前說的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’)。

  • 不斷重複上面的過程。

流程如下圖所示:

MySQL中的隨機抽取如何實現

#但是當limit的數比較大時,維護堆比較困難,所以又會使用歸併排序演算法。

以上是MySQL中的隨機抽取如何實現的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述
本文轉載於:亿速云。如有侵權,請聯絡admin@php.cn刪除
图文详解mysql架构原理图文详解mysql架构原理May 17, 2022 pm 05:54 PM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于架构原理的相关内容,MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层,下面一起来看一下,希望对大家有帮助。

mysql的msi与zip版本有什么区别mysql的msi与zip版本有什么区别May 16, 2022 pm 04:33 PM

mysql的msi与zip版本的区别:1、zip包含的安装程序是一种主动安装,而msi包含的是被installer所用的安装文件以提交请求的方式安装;2、zip是一种数据压缩和文档存储的文件格式,msi是微软格式的安装包。

mysql怎么去掉第一个字符mysql怎么去掉第一个字符May 19, 2022 am 10:21 AM

方法:1、利用right函数,语法为“update 表名 set 指定字段 = right(指定字段, length(指定字段)-1)...”;2、利用substring函数,语法为“select substring(指定字段,2)..”。

mysql怎么替换换行符mysql怎么替换换行符Apr 18, 2022 pm 03:14 PM

在mysql中,可以利用char()和REPLACE()函数来替换换行符;REPLACE()函数可以用新字符串替换列中的换行符,而换行符可使用“char(13)”来表示,语法为“replace(字段名,char(13),'新字符串') ”。

mysql怎么将varchar转换为int类型mysql怎么将varchar转换为int类型May 12, 2022 pm 04:51 PM

转换方法:1、利用cast函数,语法“select * from 表名 order by cast(字段名 as SIGNED)”;2、利用“select * from 表名 order by CONVERT(字段名,SIGNED)”语句。

MySQL复制技术之异步复制和半同步复制MySQL复制技术之异步复制和半同步复制Apr 25, 2022 pm 07:21 PM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于MySQL复制技术的相关问题,包括了异步复制、半同步复制等等内容,下面一起来看一下,希望对大家有帮助。

mysql怎么判断是否是数字类型mysql怎么判断是否是数字类型May 16, 2022 am 10:09 AM

在mysql中,可以利用REGEXP运算符判断数据是否是数字类型,语法为“String REGEXP '[^0-9.]'”;该运算符是正则表达式的缩写,若数据字符中含有数字时,返回的结果是true,反之返回的结果是false。

带你把MySQL索引吃透了带你把MySQL索引吃透了Apr 22, 2022 am 11:48 AM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了mysql高级篇的一些问题,包括了索引是什么、索引底层实现等等问题,下面一起来看一下,希望对大家有帮助。

See all articles

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

AI Hentai Generator

AI Hentai Generator

免費產生 AI 無盡。

熱門文章

R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
2 週前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳圖形設置
2 週前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.如果您聽不到任何人,如何修復音頻
2 週前By尊渡假赌尊渡假赌尊渡假赌

熱工具

EditPlus 中文破解版

EditPlus 中文破解版

體積小,語法高亮,不支援程式碼提示功能

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

強大的PHP整合開發環境

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser是一個安全的瀏覽器環境,安全地進行線上考試。該軟體將任何電腦變成一個安全的工作站。它控制對任何實用工具的訪問,並防止學生使用未經授權的資源。

Dreamweaver Mac版

Dreamweaver Mac版

視覺化網頁開發工具

VSCode Windows 64位元 下載

VSCode Windows 64位元 下載

微軟推出的免費、功能強大的一款IDE編輯器