当前位置:首页 > Mysql技术文章 > MySQL查询随机数据的4种方法和性能对比

MySQL查询随机数据的放在总结和性能分析

  • 发布时间:
  • 作者:码农之家原创
  • 点击:76

这篇文章主要知识点是关于MySQL、MySQL随机数、mysql随机查询若干条数据的方法 的内容,如果大家想对相关知识点有系统深入的学习,可以参阅以下相关资源

PHP和MySQL Web开发

在《PHP和MySQL Web开发》书中,我们将把使用PHP与MySQL的经验和心得体会毫无保留地分享给你,PHP和MySQL是目前热门的两个Web开发工具。

查看详情

MySQL查询随机数据的4种方法和性能对比

下面从以下四种方案分析各自的优缺点。
方案一:
 
SELECT * FROM `table` ORDER BY RAND() LIMIT 0,1;

这种方法的问题就是非常慢。原因是因为MySQL会创建一张零时表来保存所有的结果集,然后给每个结果一个随机索引,然后再排序并返回。
有几个方法可以让它快起来。
基本思想就是先获取一个随机数,然后使用这个随机数来获取指定的行。
由于所有的行都有一个唯一的id,我们将只取最小和最大id之间的随机数,然后获取id为这个数行。为了让这个方法当id不连续时也能有效,我们在最终的查询里使用”>=”代替”=”。
为了获取整张表的最小和最大id,我们使用MAX()和MIN()两个聚合函数。这两个方法会返回指定组里的最大和最小值。在这里这个组就是我们表里的所有id字段值。
方案二:
 
<?php
$range_result = mysql_query( " SELECT MAX(`id`) AS max_id , MIN(`id`) AS min_id FROM `table` ");
$range_row = mysql_fetch_object( $range_result );
$random = mt_rand( $range_row->min_id , $range_row->max_id );
$result = mysql_query( " SELECT * FROM `table` WHERE `id` >= $random LIMIT 0,1 ");

就像我们刚才提到的,这个方法会用唯一的id值限制表的每一行。那么,如果不是这样情况怎么办?
下面这个方案是使用了MySQL的LIMIT子句。LIMIT接收两个参数值。第一个参数指定了返回结果第一行的偏移量,第二个参数指定了返回结果的最大行数。偏移量指定第一行是0而不是1。
为了计算第一行的偏移量,我们使用MySQL的RAND()方法从0到1之间生成一个随机数。然后我们把这个数字跟我们用COUNT()方法获取倒的表记录数相乘。由于LIMIT的参数必须是int型而不能是float,我们使用FLOOR()来处理结果。FLOOR()会计算小于表达式的最大值。最终的代码就是这样:
方案三:
 
<?php
$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `table` ");
$offset_row = mysql_fetch_object( $offset_result );
$offset = $offset_row->offset;
$result = mysql_query( " SELECT * FROM `table` LIMIT $offset, 1 " );

在MySQL 4.1以后我们可以使用子子查询合并上面两个方法:
方案四:
 
SELECT * FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 1;

这个方案跟方案二有同样的弱点,只对有唯一id值的表有效。
记住我们最初寻找选择随机行的替代方法的原因,速度!所以,这些方案的在执行时间上的比较会怎么样?我不会指出硬件和软件配置或者给出具体的数字。大概的结果是这样的:
最慢的是解决方案一(我们假定它用了100%的时间)。
方案二用了79%
方案三 – 13%
方案四 – 16%
so, 方案三胜出!

mysql随机查询若干条数据的方法

在mysql中查询5条不重复的数据,使用以下:


SELECT * FROM `table` ORDER BY RAND() LIMIT 5

就可以了。但是真正测试一下才发现这样效率非常低。一个15万余条的库,查询5条数据,居然要8秒以上
搜索Google,网上基本上都是查询max(id) * rand()来随机获取数据。


SELECT *
FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id ASC LIMIT 5;

但是这样会产生连续的5条记录。解决办法只能是每次查询一条,查询5次。即便如此也值得,因为15万条的表,查询只需要0.01秒不到。
上面的语句采用的是JOIN,mysql的论坛上有人使用


SELECT *
FROM `table`
WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` )
ORDER BY id LIMIT 1;

我测试了一下,需要0.5秒,速度也不错,但是跟上面的语句还是有很大差距。总觉有什么地方不正常。
于是我把语句改写了一下。


SELECT * FROM `table`
WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `table`))) 
ORDER BY id LIMIT 1;

这下,效率又提高了,查询时间只有0.01秒
最后,再把语句完善一下,加上MIN(id)的判断。我在最开始测试的时候,就是因为没有加上MIN(id)的判断,结果有一半的时间总是查询到表中的前面几行。
完整查询语句是:


SELECT * FROM `table`
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`))) 
ORDER BY id LIMIT 1;
SELECT *
FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;

最后对这两个语句进行分别查询10次,
前者花费时间 0.147433 秒
后者花费时间 0.015130 秒
看来采用JOIN的语法比直接在WHERE中使用函数效率还要高很多。

以上就是本次给大家分享的关于Mysql的全部知识点内容总结,大家还可以在下方相关文章里找到MySQL存储引擎InnoDB的配置及、 mysql中插入emoji表情失败的、 Linux/UNIX和Window平台上安装、 等mysql文章进一步学习,感谢大家的阅读和支持。

上一篇:MySQL性能优化总结

下一篇:整理MySQL基本命令、常用命令

展开 +

收起 -

MySQL查询随机数 相关内容
深入理解MariaDB与MySQL

深入理解MariaDB与MySQL 讲解了MariaDB的诞生背景及设计目的,并通过与MySQL的比较使读者轻松理解MariaDB。本书内容涉及MariaDB 5.1~10.0的各个方面,讲解重点在于MariaDB 10.0的各种实用功能。此外,M

查看详情
高性能MySQL 查看详情
PHP&MySQL跨设备网站开发实例精粹

这本书从易学实用的角度详细讲解了PHP、HTML5语法,MySQL数据库存取,网页之间的信息传递、表单的后端处理等,提供了丰富的网站开发范例,欢迎下载

查看详情
零点起飞学MySQL

本书是一本MySQL的入门教程,是手把手教会读者使用该数据库的入门教程。本书概念清楚,用穿插类比和图解的方式进行讲解,可以让读者更加直观地理解和掌握MySQL的各个知识点。本书附带1张光盘,收录了本书配套多媒体教学视频及涉及的源文件,便于读者高效、直观地学习。

查看详情
MySQL 8 Cookbook

MySQL 8的更新幅度在MySQL历史上可谓空前,增添很多重磅新特性——“数据字典”取消了已存在几十年的frm文件,将插件式数据库的重心偏向InnoDB,极大提高meta data的访问性能,并支持了DDL原子性

查看详情
MySQL DBA修炼之道

MySQL DBA修炼之道 以实战讲解为主,摒弃了生产实践中不常用的知识和技术。书中内容主要分为5部分。第壹部分介绍MySQL的应用领域和适用场景、基础架构、常见生产环境部署方式,以及MySQL安

查看详情
PHP与MySQL高性能应用开发

PHP与MySQL高性能应用开发书中不仅讲解了OOP、设计模式,也讲解了如何进行性能优化和调试。甚至,还对于语言之外的代码重构进行了讲解,本书面向的对象是有一定PHP基础,但是又想深入学习的PHP开发者,欢迎免费下载

查看详情
MySQL查询随机数 学习笔记
网友NO.677010

MYSQL随机抽取查询 MySQL Order By Rand()效率问题

要从tablename表中随机提取一条记录,大家一般的写法就是:SELECT * FROM tablename ORDER BY RAND() LIMIT 1。 但是,后来我查了一下MYSQL的官方手册,里面针对RAND()的提示大概意思就是,在ORDER BY从句里面不能使用RAND()函数,因为这样会导致数据列被多次扫描。但是在MYSQL 3.23版本中,仍然可以通过ORDER BY RAND()来实现随机。 但是真正测试一下才发现这样效率非常低。一个15万余条的库,查询5条数据,居然要8秒以上。查看官方手册,也说rand()放在ORDER BY 子句中会被执行多次,自然效率及很低。 You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. 搜索Google,网上基本上都是查询max(id) * rand()来随机获取数据。 SELECT * FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2 WHERE t1.id = t2.id ORDER BY t1.id ASC LIMIT 5; 但是这……

网友NO.864051

MySQL随机查询记录的效率测试分析

以下就是文章的主要内容。 1.SELECT * FROM `table` ORDER BY RAND() LIMIT 5 就可以了。 但是真正测试一下才发现这样效率非常低。一个15万余条的库,查询5条数据,居然要8秒以上 查看官方手册,也说rand()放在ORDER BY 子句中会被执行多次,自然效率及很低。 You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. 搜索Google,网上基本上都是查询max(id) * rand()来随机获取数据。 1.SELECT * 2.FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2 3.WHERE t1.id = t2.id 4.ORDER BY t1.id ASC LIMIT 5; 但是这样会产生连续的5条记录。解决办法只能是每次查询一条,查询5次。即便如此也值得,因为15万条的表,查询只需要0.01秒不到。 上面的语句采用的是JOIN,MySQL的论坛上有人MySQL使用 1.SELECT * 2.FROM `table` 3.WHERE id = (SELECT FLOOR( MAX(id) * R……

网友NO.302073

mysql实现随机查询经验谈

一、随机查询一条数据 方法一:SELECT * FROM `table` ORDER BY RAND() limit 1 评价:不建议使用,效率非常低,官方文档中进行说明:Order By和RAND()连用,会多次扫描表,导致速度变慢。 方法二:SELECT * FROM `table` WHERE id = (SELECT floor(RAND() * (SELECT MAX(id) FROM `table`))) ORDER BY id LIMIT 1; 解释:SELECT MAX(id) FROM `table` 这句话查询出最大的id值 SELECT floor(RAND() * (SELECT MAX(id) FROM `table`)) 这句获取一个小于MAX(id)的随机数 WHERE id = (SELECT floor(RAND() * (SELECT MAX(id) FROM `table`))) 这句话筛选出所有的大于生成随机数的id的行 然后最后就把大于这个随机id的行查询出来,然后按照id排序,选择第一个,就相当与获取了所有行中随机的一行。 评价:有问题,如果id不是从0开始的话,比如从10000开始自增,那么 SELECT floor(RAND() * (SELECT MAX(id) FROM `table`)) 得到的将是会哟很大概率得到小于10000的值,经……

网友NO.921360

MySQL 随机查询数据与随机更新数据实现代码

MySQL随机查询数据 以前在群里讨论过这个问题,比较的有意思.MySQLl的语法真好玩.他们原来都想用PHP的实现随机,但取出多条好像要进行两次以上查询.翻了手册,找到了下面这个语句,可以完成任务了。 SELECT * FROM table_name ORDER BY rand() LIMIT 5; MySQL的rand()函数在手册里是这么说的: RAND() RAND(N) 返回在范围0到1.0内的随机浮点值。如果一个整数参数N被指定,它被用作种子值。 关于MySQL的rand()函数的效率问题,大家可以参考《MySQLL Order By Rand()效率》:http://www.phpq.net/MySQLl/MySQLl-order-by-rand.html 实际效果 MySQLl select RAND(); - 0.5925 MySQLl select RAND(20); - 0.1811 MySQLl select RAND(20); - 0.1811 MySQLl select RAND(); - 0.2079 MySQLl select RAND(); - 0.7888 MySQL随机更新数据 如何写一个语句能一下更新几百条MySQLL数据! 需要测试MySQL数据库,里面有一个上万条数据的数据库,如何写一个PHP文件一下每次更……

<
1
>

Copyright 2018-2020 xz577.com 码农之家

本站所有电子书资源不再提供下载地址,只分享来路

免责声明:网站所有作品均由会员网上搜集共同更新,仅供读者预览及学习交流使用,下载后请24小时内删除

版权投诉 / 书籍推广 / 赞助:QQ:520161757