文档章节

mysql随机取数据,如何实现高效率?

豆花饭烧土豆
 豆花饭烧土豆
发布于 2016/04/23 01:21
字数 704
阅读 85
收藏 18

最近由于需要大概研究了一下MYSQL的随机抽取实现方法。举个例子,要从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;

但是这样会产生连续的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;

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

参考文献:
MySQL Order By索引优化:http://www.phpq.net/mysql/mysql-order-by.html
MySQL Order By语法:http://www.phpq.net/mysql/mysql-order-by-syntax.html
MySQL Order By Rand()效率:http://www.phpq.net/mysql/mysql-order-by-rand.html
MySQL Order By用法:http://www.phpq.net/mysql/mysql-order-by-use.html

本文转载自:http://my.oschina.net/u/1024573/blog/472030

豆花饭烧土豆

豆花饭烧土豆

粉丝 15
博文 358
码字总数 93985
作品 0
深圳
私信 提问

暂无文章

5分钟了解阿里时序时空数据库

简介 时序时空数据库(Time Series & Spatial Temporal Database,简称 TSDB)是一种高性能、低成本、稳定可靠的在线时序时空数据库服务,提供高效读写、高压缩比存储、时序数据插值及聚合计...

阿里云云栖社区
31分钟前
4
0
分享一个vue项目“脚手架”项目的实现步骤

搭建缘由 源于公司每次新启动一个由多人协同开发的项目都由负责人初始化项目之后,每个人再去从私服pull一下项目才开始开发。但是每次初始化工程都是一步步的造轮子,一个个依赖去安装,新建...

开元中国2015
44分钟前
3
0
Odoo 自定义Widgets 基础教程(章节2)

大家好, 本文将进一步解释odoo widget 的相关原理,其中首先会讲述:1. 什么是widgets 中的继承;2. 为什么有很多 init : function 语法;3. widgets 系统结构; 首先,让我们来看看结构:(...

openerp
今天
2
0
我的Linux常用软件

我的Linux常用软件 本帖记录我在Linux下常用的软件,系统是Deepin Linux, 以备查询用, 不定期更新。 Sougou Pinyin 这个不用多说, 装系统第一个要找输入法。 TeamViewer Linux有时候需要远...

Frank1126lin
今天
6
0
TypeScript + eslint 格式化代码及其它

缘起 之前使用 tslint 校验代码格式,后来发现 ts 官方已经放弃这个转而使用 eslint,研究了一下把相应配置调整了一下。 目标: 使用 eslint、prettier 格式化 TypeScript 代码 使用 husky、...

郁也风
今天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部