`
xbyang18
  • 浏览: 16342 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

mysql 随机选择一条记录

阅读更多
mysql 随机选择一条记录

    博客分类:
    数据库

MySQLSQLBlog

转自:

http://hi.baidu.com/why_question_how/blog/item/d3e20a44e8b032046b63e58c.html






MySQL:快速随机选取一条记录的思考
2010年07月14日 星期三 10:43
MySQL:快速随机选取一条记录的思考

标签: MySQL  随机  分类: mysql 2010-03-04 12:23



本文讨论的是如何从MySQL一个数据表中提取一条随机的效率,同时要保证效率最高。

方法一

这是最原始最直观的语法,如下:

SELECT * FROM foo ORDER BY RAND() LIMIT 1

当数据表中数据量较小时,此方法可行。但当数据量到达一定程度,比如100万数据或以上,就有很大的性能问题。如果你通过EXPLAIN来分析这个 语句,会发现虽然MySQL通过建立一张临时表来排序,但由于ORDER BY和LIMIT本身的特性,在排序未完成之前,我们还是无法通过LIMIT来获取需要的记录。亦即,你的记录有多少条,就必须首先对这些数据进行排序。

方法二

看来对于大数据量的随机数据抽取,性能的症结出在ORDER BY上,那么如何避免?方法二提供了一个方案。

首先,获取数据表的所有记录数:

SELECT count(*) AS num_rows FROM foo

然后,通过对应的后台程序记录下此记录总数(假定为num_rows)。

然后执行:

SELECT * FROM foo LIMIT [0到num_rows之间的一个随机数],1

上面这个随机数的获得可以通过后台程序来完成。此方法的前提是表的ID是连续的或者自增长的。

这个方法已经成功避免了ORDER BY的产生。

方法三

有没有可能不用ORDER BY,用一个SQL语句实现方法二?可以,那就是用JOIN。

SELECT * FROM Bar B JOIN (SELECT CEIL(MAX(ID)*RAND()) AS ID FROM Bar) AS m ON B.ID >= m.ID LIMIT 1;

此方法实现了我们的目的,同时,在数据量大的情况下,也避免了ORDER BY所造成的所有记录的排序过程,因为通过JOIN里面的SELECT语句实际上只执行了一次,而不是N次(N等于方法二中的num_rows)。而且, 我们可以在筛选语句上加上“大于”符号,还可以避免因为ID好不连续所产生的记录为空的现象。

还有更好的方案吗?有待总结。







第二篇文章

转自:

http://blog.csdn.net/zxl315/archive/2008/05/12/2435368.aspx



在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中使用函数效率还要高很多
分享到:
评论
1 楼 xbyang18 2012-03-30  
<input type="text" value="xb aaa">asdfsdf

相关推荐

    PHP实现在数据库百万条数据中随机获取20条记录的方法

    今天偶然想起来一个坑爹数据,如:PHP取百万条数据中随机20条记录,当时就用的算法。 1.先统计统计数据库多少条记录(这个做个数据缓存,如1小时重新统计一次), 2.根据总条数,随机1次,1次性取出20条记录(当然这...

    php随机取mysql记录方法小结

    本文实例总结了php随机取mysql记录方法。...把 limit 后面的数值改为你想随机抽取的条数,这里只取一条. 方法二,代码如下: 复制代码 代码如下:$query= “SELECT count(*) as count FROM recommends”; …

    MySql分组后随机获取每组一条数据的操作

    思路:先随机排序然后再分组就好了。 1、创建表: CREATE TABLE `xdx_test` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `class` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=...

    mysql优化取随机数据慢的方法

    MySQL很多时候需要获取随机数据,举个例子,要从tablename表中随机提取一条记录,大家一般的写法就是: 代码如下:SELECT * FROM tablename ORDER BY RAND() LIMIT 1 但是,后来我查了一下MYSQL的官方手册,里面针对...

    MYSQL 随机 抽取实现方法及效率分析

    MYSQL的随机抽取实现方法。举个例子,要从tablename表中随机提取一条记录,大家一般的写法就是:SELECT * FROM tablename ORDER BY RAND() LIMIT 1。

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

    要从tablename表中随机提取一条记录,大家一般的写法就是:SELECT * FROM tablename ORDER BY RAND() LIMIT 1。 但是,后来我查了一下MYSQL的官方手册,里面针对RAND()的提示大概意思就是,在ORDER BY从句里面不能...

    数据库查询排序使用随机排序结果示例(Oracle/MySQL/MS SQL Server)

    MySQL随机查询出一条记录: 代码如下: — 下面的查询语句效率高,不要使用 SELECT * FROM table1 ORDER BY rand() LIMIT 1 来查询 SELECT * FROM table1 WHERE id=(SELECT id FROM table1 ORDER BY rand() LIMIT 1)...

    MySQL 5权威指南(第3版) 中文版 下载地址

     10.11 对前n条或后n条记录进行处理  10.12 以随机方式选择数据记录  10.13 全文索引  10.14 锁定  10.15 事务  第11章 访问权限与信息安全  11.1 简介  11.2 急救  11.3 访问控制机制的内部工作...

    mysql数据库my.cnf配置文件

    适用于在一次性插入100-1000+条记录时, 提高效率。默认值是8M。可以针对数据量的大小,翻倍增加。 myisam_sort_buffer_size = 1024M # MyISAM设置恢复表之时使用的缓冲区的尺寸,当在REPAIR TABLE或用CREATE INDEX...

    mysql中RAND()随便查询记录效率问题和解决办法分享

    举个例子,要从tablename表中随机提取一条记录,大家一般的写法就是:SELECT * FROM tablename ORDER BY RAND() LIMIT 1。 有两个方法可以达成以上效果. 1.新建一个表,里面存着 -5 至 5 之间的数.再利用order by ...

    MySQL5 权威指南第3版中文版_part1

     10.11 对前n条或后n条记录进行处理  10.12 以随机方式选择数据记录  10.13 全文索引  10.14 锁定  10.15 事务  第11章 访问权限与信息安全  11.1 简介  11.2 急救  11.3 访问控制机制的内部工作原理  ...

    MySQL Order By Rand()效率分析

    举个例子,要从tablename表中随机提取一条记录,大家一般的写法就是:SELECT * FROM tablename ORDER BY RAND() LIMIT 1。 但是,后来我查了一下MYSQL的官方手册,里面针对RAND()的提示大概意思就是,在ORDER BY从句...

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

    一个15万余条的库,查询5条数据,居然要8秒以上 查看官方手册,也说rand()放在ORDER BY 子句中会被执行多次,自然效率及很低。 You cannot use a column with RAND() values in an ORDER BY clause, because ORDER ...

    MySQL中建立索引的集中方式

    索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是... 我们随机向里面插入了10000条记录,其中有一条:5555, admin。  在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时

    MySQL索引类型总结和使用技巧以及注意事项

    我们随机向里面插入了10000条记录,其中有一条:5555, admin。 在查找username=”admin”的记录 SELECT * FROM mytable WHERE username=’admin’;时,如果在username上已经建立了索引,MySQL无须任何扫描,即准确可...

    mysql储存相关的面试题目精选

    存储引擎 1、有哪些常见的存储引擎? 2、MyISAM 和 InnoDB 的区别?3、InnoDB 的四大特性? 插入缓冲insert buffer) ...用来描述数据页中的具体信息,比如存在多少条纪录,第一条纪录的位置等。 6.页中插入记录的过程?

    MySql基本查询、连接查询、子查询、正则表达查询讲解

    不加条件,那么就只取每个分组的第一条。 如果想看分组的内容,可以加groub_concat [sql] view plain copy select STU_SEX,group_concat(STU_NAME) from STUDENT group by STU_SEX; 3.2、一般情况下group需与...

    批量替换 MySQL 指定字段中的字符串

    如果你想把 article 表中 ID 小于5000的记录,content 字段中“解决”替换成“解放”,那么语法就是: 代码如下: UPDATE article SET content = replace(content, ‘解决’, ‘解放’) WHERE ID&lt;5000; 是不是很...

Global site tag (gtag.js) - Google Analytics