MySQL函数rand()随机获取数据的正确方法(亲测100万,支持大数据量)

在mysql中带了随机取数据的函数,在mysql中我们会有rand()随机函数,很多朋友都会直接使用,如果几百条数据肯定没事,如果几万或上百万等等时你会发现,直接使用是错误的。下面我来介绍:

 

mysql rand()随机函数取数据一些优化方法:

SELECT * FROM table_name ORDER BY rand() LIMIT 5;

 

rand在手册里是这么说的:

RAND()

RAND(N)

返回在范围0到1.0内的随机浮点值。如果一个整数参数N被指定,它被用作种子值。

mysql> select RAND();
        -> 0.5925
mysql> select RAND(20);
        -> 0.1811
mysql> select RAND(20);
        -> 0.1811
mysql> select RAND();
        -> 0.2079
mysql> select RAND();
        -> 0.7888

 

你不能在一个ORDER BY子句用RAND()值使用列,因为ORDER BY将重复计算列多次。

 

然而在MySQL3.23中,你可以做:

SELECT * FROM table_name ORDER BY RAND();

 

这是有利于得到一个来自:

SELECT * FROM table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000

 

注意:在一个WHERE子句里的一个RAND()将在每次WHERE被执行时重新评估。

 

网上基本上都是查询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秒,速度也不错,但是跟上面的语句还是有很大差距,后来请教了baidu,得到如下语句:

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;

 

最后在php中对这两个语句进行分别查询10次;

前者花费时间 0.147433 秒,后者花费时间 0.015130 秒。

 

执行效率需要0.02 sec,可惜的是只有mysql 4.1.*以上才支持这样的子查询。

 

总结:有一个很大的注意事项,查看了官方手册,也说rand()放在ORDER BY 子句中会被执行多次,自然效率及很低。以上的sql语句最后一条,本人实际测试通过,100W数据,瞬间出结果。

    A+
发布日期:2019年03月26日 21:19:40  所属分类:mysql函数
最后更新时间:2019-03-26 21:20:14
评分: (1 票;平均数5.00 ;最高评分 5 ;用户总数1;总得分 5;百分比100.00)
头像
wp discux 帝国 dedecms phpcms等快速建站
wp discux 帝国 dedecms phpcms等快速建站
  • ¥ 99.9元
  • 市场价:499.9元
免费SSL证书服务 HTTPS申请 安装 配置 支持通配符*
免费SSL证书服务 HTTPS申请 安装 配置 支持通配符*
  • ¥ 199.9元
  • 市场价:20000元
PHP运行环境 wamp lamp lnmp 安装 配置 搭建服务
PHP运行环境 wamp lamp lnmp 安装 配置 搭建服务
  • ¥ 9.9元
  • 市场价:49.9元
服务器管理面板/主机控制面板“安装”服务
服务器管理面板/主机控制面板“安装”服务
  • ¥ 9.9元
  • 市场价:49.9元

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: