ホームページ データベース mysql チュートリアル mysql 随机获取记录order by rand 优化

mysql 随机获取记录order by rand 优化

Jun 07, 2016 pm 05:52 PM
ランダム

如果要随机获取记录数,在mysql里最简单的方法肯定是order by rand()了,但是这种方法只能在表记录极少的情况下才能使用。主要是因为order by rand()导致了using filesort.这个时候查询类型会变成all,索引会失效。只需简单的变通下,完成可以做到同样的效果

下面我就以users(userId,userName,password……)表(有一百多万条记录)为例,对比讲解下几个方法效率问题:

 代码如下 复制代码

1.select * from users order by rand() LIMIT 1

执 行该sql语句,老半天没有反应,最后被迫手动停止执行,怎个伤人了得啊!后来我查了一下MYSQL手册,里面针对RAND()的提示大概意思就是,在 ORDER BY从句里面不能使用RAND()函数,因为这样会导致数据列被多次扫描,导致效率相当相当的低!效率不行,切忌使用!

 代码如下 复制代码

2.SELECT * FROM users  AS t1  JOIN (SELECT ROUND(RAND() * ((SELECT MAX(userId) FROM `users`)-(SELECT MIN(userId) FROM users))+(SELECT MIN(userId) FROM users)) AS userId) AS t2 WHERE t1.userId >= t2.userId ORDER BY t1.userId LIMIT 1

执行该sql语句,用时0.031s,效率没说的,相当的给力!心里那个爽啊,紧接着,我把”LIMIT 1“改为了”LIMIT 100“ 随机取一百条记录,用时0.048,给力吧。可是就在此时问题出现了,发现结果好像不是随机的?为了验证结果,又执行了N次,真不是随机的, 问题出现在”ORDER BY t1.userId“这里,按userId排序了。随机取一条记录还是不错的选择,多条就不行了啊!

 代码如下 复制代码

3.SELECT * FROM users WHERE userId >= ((SELECT MAX(userId) FROM users)-(SELECT MIN(userId) FROM users)) * RAND() + (SELECT MIN(userId) FROM users)  LIMIT 1

执行该sql语句,用时0.039s,效率太给力了!接着我就把”LIMIT 1“改为了”LIMIT 10000“,用时0.063s。经过多次验证,哥对灯发誓,结果肯定是随机的!
结论:随机取一条或多条记录,方法都不错!

4.通过sql获得最大值和最小值,然后通过php的rand生成一个随机数randnum,再通过

 代码如下 复制代码
SELECT * FROM users WHERE userId >= randnum LIMIT 1

,获得一条记录效率应该还可以,多条应该就不行了。
 

根据记录的类型,分类连续和非连续两种。
连续指记录是连续存放的,并且有字段可以证明记录是连续的,例如自增id。
非连续是指记录是随机存放的,例如有条件的查询,结果肯定不是连续的。

一、连续记录优化
先得到表的最大id和最小id。select max(id),min(id) from table

1.在程序里随机一个在最大id和最小id的中间数,查询的时候大于这个随机数的就是随机记录了。

Sql代码 

 代码如下 复制代码

1.select * from table where id > 中间数 limit length; 
select * from table where id > 中间数 limit length;缺点:如果中间数很大的话,获取不了需要的记录数,随机性不强

 代码如下 复制代码

2.在程序里随机n个最大id和最小id的中间数,查询的时候用in获得这几个中间数的记录
Sql代码 
1.select * from table where id in (中间数1, 中间数2,中间数3) 
select * from table where id in (中间数1, 中间数2,中间数3)需要注意的是,如果你要获取5条记录,那建议随机10个数。
缺点:性能不如第1种方法,但是随机性更强

二、非连续记录优化

其实非连续记录的方法一样可以应用在连续记录中。
首先获得记录的总数,例如:select count(*) from table where groupid = 1;
然后在程序里随机n个小于记录总数的中间数,之后通过循环

Sql代码 

 代码如下 复制代码
1.select * from table where groupid = 1 limit 中间数,1 
select * from table where groupid = 1 limit 中间数,1来获得记录。

关于优化循环sql可以采用prepare或者union all来优化循环执行


结论:方法1效率不行,切忌使用;随机获得一条记录,方法2是相当不错的选择,采用JOIN的语法比直接在WHERE中使用函数效率还是要高一些的,不过方法3也不错;随机获得多条记录

このウェブサイトの声明
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。

ホットな記事タグ

メモ帳++7.3.1

メモ帳++7.3.1

使いやすく無料のコードエディター

SublimeText3 中国語版

SublimeText3 中国語版

中国語版、とても使いやすい

ゼンドスタジオ 13.0.1

ゼンドスタジオ 13.0.1

強力な PHP 統合開発環境

ドリームウィーバー CS6

ドリームウィーバー CS6

ビジュアル Web 開発ツール

SublimeText3 Mac版

SublimeText3 Mac版

神レベルのコード編集ソフト(SublimeText3)

DockerでのMySQLメモリの使用を減らします DockerでのMySQLメモリの使用を減らします Mar 04, 2025 pm 03:52 PM

DockerでのMySQLメモリの使用を減らします

Alter Tableステートメントを使用してMySQLのテーブルをどのように変更しますか? Alter Tableステートメントを使用してMySQLのテーブルをどのように変更しますか? Mar 19, 2025 pm 03:51 PM

Alter Tableステートメントを使用してMySQLのテーブルをどのように変更しますか?

mysqlの問題を解決する方法共有ライブラリを開くことができません mysqlの問題を解決する方法共有ライブラリを開くことができません Mar 04, 2025 pm 04:01 PM

mysqlの問題を解決する方法共有ライブラリを開くことができません

sqliteとは何ですか?包括的な概要 sqliteとは何ですか?包括的な概要 Mar 04, 2025 pm 03:55 PM

sqliteとは何ですか?包括的な概要

Linuxでmysqlを実行します(phpmyAdminを使用してポッドマンコンテナを使用して/なし) Linuxでmysqlを実行します(phpmyAdminを使用してポッドマンコンテナを使用して/なし) Mar 04, 2025 pm 03:54 PM

Linuxでmysqlを実行します(phpmyAdminを使用してポッドマンコンテナを使用して/なし)

MACOSで複数のMySQLバージョンを実行する:ステップバイステップガイド MACOSで複数のMySQLバージョンを実行する:ステップバイステップガイド Mar 04, 2025 pm 03:49 PM

MACOSで複数のMySQLバージョンを実行する:ステップバイステップガイド

人気のあるMySQL GUIツール(MySQL Workbench、PhpMyAdminなど)は何ですか? 人気のあるMySQL GUIツール(MySQL Workbench、PhpMyAdminなど)は何ですか? Mar 21, 2025 pm 06:28 PM

人気のあるMySQL GUIツール(MySQL Workbench、PhpMyAdminなど)は何ですか?

共通の脆弱性(SQLインジェクション、ブルートフォース攻撃)に対してMySQLを保護するにはどうすればよいですか? 共通の脆弱性(SQLインジェクション、ブルートフォース攻撃)に対してMySQLを保護するにはどうすればよいですか? Mar 18, 2025 pm 12:00 PM

共通の脆弱性(SQLインジェクション、ブルートフォース攻撃)に対してMySQLを保護するにはどうすればよいですか?

See all articles