专栏首页膨胀的面包MySQL 数据库中随机获取一条或多条记录的三种方法

MySQL 数据库中随机获取一条或多条记录的三种方法

工作中会遇到从数据库中随机获取一条或多条记录的场景,下面介绍几种随机获取的方法供参考。

首先创建个 users 表演示:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表中存放30万记录。

1、使用 order by rand()

SELECT * FROM users ORDER BY RAND() LIMIT 1;

LIMIT 1 的执行时间:0.559s, LIMIT 100 耗时 0.660s。

此种方法在数据量小的情况下可以使用,但在生产环境不建议使用。

MYSQL 手册里面针对 RAND() 的提示大概意思就是,在 ORDER BY 从句里面不能使用 RAND() 函数,因为这样会导致数据列被多次扫描,导致效率相当相当的低,效率不行,切忌使用。

2、使用 join 及 rand() 函数

SELECT * FROM users AS t1 JOIN (SELECT ROUND(RAND()*(SELECT MAX(id) 
FROM users)) AS id) AS t2 WHERE t1.id>=t2.id ORDER BY t1.id LIMIT 1;

此 sql 随机获取一条的时间是 0.014s,LIMIT 100 时耗时 0.020s,性能上没差多少。

获取多条的话有时会达不到要求(获取的记录数可能达不到多条)

3、子查询及 rand() 函数

SELECT * FROM users as t1 WHERE t1.id>=(RAND()*(SELECT MAX(id) FROM users)) LIMIT 1;

随机取一条的耗时:0.015,LIMIT 100 时耗时 0.026s。

随机获取一条记录推荐使用 第 2 种方法,在 30 万条记录时也只需 0.014s。

2021.06.02 更新:

上边第二种方法更优写法:

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

第三种方法的更优写法:

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

via: MySQL数据库中随机获取一条或多条记录_River106的博客-CSDN博客_mysql随机取一条记录 https://blog.csdn.net/angellee1988/article/details/103845533

MYSQL随机读取一条数据_shenzhou_yh的博客-CSDN博客_mysql 随机查询一条数据 https://blog.csdn.net/shenzhou_yh/article/details/90550090

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 一种获取SAP HANA数据库表条目数的另类方法

    Still used the old way SELECT COUNT(*) to get total number of records stored in ...

    Jerry Wang
  • PHP查询数据库中满足条件的记录条数(二种实现方法)

    在需要输出网站用户注册数或者插入数据之前判断是否有重复记录时,就需要获取满足条件的MySQL查询的记录数目,接下来介绍两种查询统计方法,感兴趣的朋友可以了解下啊...

    shirayner
  • 使用Django从数据库中随机取N条记录的不同方法及其性能实测

    【声明】:本文中的实验仅限于特定数据库和特定框架。不同数据库,数据库服务器的性能,甚至同一个数据库的不同配置都会影响到同一段代码的性能。具体情况请在自己的生产环...

    小贝壳
  • 【面经】面试官:如何以最高的效率从MySQL中随机查询一条记录?

    从这个题目来看,其实包含了两个要求,第一个要求就是:从MySQL数据表中查询一条随机的记录。第二个要求就是要保证效率最高。

    冰河
  • MySQL索引设计概要

    在关系型数据库中设计索引其实并不是复杂的事情,很多开发者都觉得设计索引能够提升数据库的性能,相关的知识一定非常复杂。 ? 然而这种想法是不正确的,索引其实并不是...

    用户1263954
  • 教你几招,快速创建 MySQL 五百万级数据,愉快的学习各种优化技巧

    如果你打算好好学习一下 MySQL,性能优化肯定是绕不过去一个问题。当你撸起袖子准备开始的时候,突然发现一个问题摆在眼前,本地数据库中没那么大的数据量啊,几条数...

    古时的风筝
  • 通过cursor游标讲解,带你初步搞懂python操作mysql数据库

    有时候,我们执行一条查询语句的时候,往往会得到N条返回结果,执行sql语句取出这些返回结果的接口(起始点),就是游标。沿着这个游标,我们可以一次取出一行记录。

    朱小五
  • MySql数据库Update批量更新与批量更新多条记录的不同值实现方法

    这里注意 ‘other_values' 是一个逗号(,)分隔的字符串,如:1,2,3

    Lanson
  • sql必会基础4

    多数情况下,可以认为如果一个资源被锁定,它总会在以后某个时间被释放。而死锁发生在当多个进程访问同一数据库时,其中每个进程拥有的锁都是其他进程所需的,由此造成每个...

    Java架构师历程
  • 面试:集合:redis:kafka

    主要是解决读数据从Redis缓存,一旦涉及到数据更新:数据库和缓存更新,就容易出现缓存(Redis)和数据库(MySQL)间的数据一致性问题。

    黑白格
  • Mysql调优你不知道这几点,就太可惜了

    ​ 1、客户端端与Mysql服务端的连接层建立连接,根据请求类型去选择相应的服务层的请求接口。

    TrueDei
  • MySQL 技术非懂不可

    在MySQL数据库中,实例与数据库的关系通常是一一对应的,即一个实例对应一个数据库,一个数据库对应一个实例。但是,在集群情况下可能存在一个数据库被多个数据实例使...

    java乐园
  • MySQL锁详解

    转载自http://www.cnblogs.com/luyucheng/p/6297752.html

    allsmallpig
  • 我通过六个 MySQL 死锁案例,终于理解了死锁的原因

    生活中,最常见的案例之一,十字路口没有红绿灯,到了十字路口相互不让,最后,整个马路瘫痪,在我们技术层面称之为死锁。

    田维常
  • MySQL死锁产生原因和解决方法

    所谓死锁:是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁...

    用户1516716
  • Mysql之锁、事务绝版详解---干货!

    数据库锁定机制简单来说,就是数据库为了保证数据的一致性,而使各种共享资源在被并发访问变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制,所以...

    码农编程进阶笔记
  • 谈谈MySQL优化方面的常用方法(最详细)

    1.选取最适用的字段属性,可以的情况下,应该尽量把字段设置为NOT NULL 2.使用连接(JOIN)来代替子查询 3.使用联合来代替手动创建的临时表 4...

    葆宁
  • 【转】Innodb中的事务隔离级别和锁的关系一次封锁or两段锁?事务中的加锁方式参考资料

    因为有大量的并发访问,为了预防死锁,一般应用中推荐使用一次封锁法,就是在方法的开始阶段,已经预先知道会用到哪些数据,然后全部锁住,在方法运行之后,再全部解锁。这...

    阿杜
  • 再谈mysql锁机制及原理—锁的诠释

    加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前,先向系统发出请求,对其加锁。加锁后事务就对该数据对象有了一定的控制,在该事务释放...

    周陆军

扫码关注云+社区

领取腾讯云代金券