MySql 100万级别数据中随机获取一条或多条记录之RAND()优化

最新推荐文章于 2024-08-08 22:07:11 发布
最新推荐文章于 2024-08-08 22:07:11 发布
阅读量1.6w 收藏 15
点赞数 5
分类专栏： MySql 文章标签： mysql 数据 优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yeshencat/article/details/73504036
版权

处理业务中，有这样的需求，例如：有100W甚至更多的用户，此时我们要随机一条男性或者女性用户出来做数据操作。基于这个需求，我们做一下实验。

基础准备

  1. 准备一张用户表，结构如下
CREATE TABLE `user` (
  `uid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `name` varchar(255) DEFAULT NULL COMMENT '用户姓名',
  `age` tinyint(3) unsigned DEFAULT 0 COMMENT '年龄',
  `gender` tinyint(3) unsigned DEFAULT 2 COMMENT '性别 2 人妖 1 男 0 女',
  `create_time` int(10) unsigned  DEFAULT 0 COMMENT '创建时间',
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  1. 简单写个MySQL储存
DELIMITER ;;
USE `test`;;

DROP PROCEDURE IF EXISTS `autoFull`;;

CREATE DEFINER=`root`@`localhost` PROCEDURE`autoFull`(num INT)
BEGIN
    #Routine body goes here...
    DECLARE count INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    set @exesql = concat("insert into user(name,age,gender,create_time) values ");
    set @exedata = "";
    while count<num do 
            set @exedata = concat(@exedata, ",('",MD5(i), "','", floor(rand()*37+18), "','", ROUND(RAND() * 1), "','",current_timestamp(), "')");
            set count=count+1;
            set i=i+1;
            if i%1000=0
            then 
                    set @exedata = SUBSTRING(@exedata, 2);
                    set @exesql = concat("insert into user(name,age,gender,create_time) values ", @exedata);
                    prepare stmt from @exesql;
                    execute stmt;
                    DEALLOCATE prepare stmt;
                    set @exedata = "";
            end if;
    end while;

    if length(@exedata)>0 
    then 
            set @exedata = SUBSTRING(@exedata, 2);
            set @exesql = concat("insert into user(name,age,gender,create_time) values ", @exedata);
            prepare stmt from @exesql;
            execute stmt;
            DEALLOCATE prepare stmt;
    end if;
END;;
DELIMITER ;

具体步骤：

mysql> use test
Database changed
mysql> 

mysql> CREATE TABLE `user` (
    ->   `uid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',
    ->   `name` varchar(255) DEFAULT NULL COMMENT '用户姓名',
    ->   `age` tinyint(3) unsigned DEFAULT 0 COMMENT '年龄',
    ->   `gender` tinyint(3) unsigned DEFAULT 2 COMMENT '性别 2 人妖 1 男 0 女',
    ->   `create_time` int(10) unsigned  DEFAULT 0 COMMENT '创建时间',
    ->   PRIMARY KEY (`uid`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

mysql> DELIMITER ;;
mysql> 
mysql> DROP PROCEDURE IF EXISTS `autoFull`;;
Query OK, 0 rows affected (0.00 sec)

mysql>  
mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE`autoFull`(num INT)
    -> BEGIN
    -> #Routine body goes here...
    -> DECLARE count INT DEFAULT 0;
    -> DECLARE i INT DEFAULT 0;
    -> set @exesql = concat("insert into user(name,age,gender,create_time) values ");
    -> set @exedata = "";
    -> while count<num do 
    -> set @exedata = concat(@exedata, ",('",MD5(i), "','", floor(rand()*37+18), "','", ROUND(RAND() * 1), "','",current_timestamp(), "')");
    -> set count=count+1;
    -> set i=i+1;
    -> if i%1000=0
    -> then 
    -> set @exedata = SUBSTRING(@exedata, 2);
    -> set @exesql = concat("insert into user(name,age,gender,create_time) values ", @exedata);
    -> prepare stmt from @exesql;
    -> execute stmt;
    -> DEALLOCATE prepare stmt;
    -> set @exedata = "";
    -> end if;
    -> end while;
    -> 
    -> if length(@exedata)>0 
    -> then 
    -> set @exedata = SUBSTRING(@exedata, 2);
    -> set @exesql = concat("insert into user(name,age,gender,create_time) values ", @exedata);
    -> prepare stmt from @exesql;
    -> execute stmt;
    -> DEALLOCATE prepare stmt;
    -> end if;
    -> END;;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;//还原界定符

mysql> call autoFull(1000000);
Query OK, 0 rows affected, 64 warnings (1 min 3.81 sec)
//调用下储存

查看下自己的储存

mysql> select count(uid) from user;
+------------+
| count(uid) |
+------------+
|    1000001 |
+------------+
1 row in set (0.20 sec)

mysql>

原始简单粗暴的 SQL 语句 select * from user order by RAND() LIMIT 1; (切勿使用)

mysql> select * from user order by RAND() LIMIT 1;
+--------+----------------------------------+------+--------+-------------+
| uid    | name                             | age  | gender | create_time |
+--------+----------------------------------+------+--------+-------------+
| 318393 | 48f8b305de34c87af8143fe1f24732ad |   24 |      0 |        2017 |
+--------+----------------------------------+------+--------+-------------+
1 row in set (17.19 sec)

mysql>

简单分析下：

mysql> explain select * from user order by RAND() LIMIT 1;
+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 1000340 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
1 row in set (0.00 sec)

type => all 呵呵，全表扫描 1000340 条数据
key => null 且没有索引，我们是随机查询
MySql 手册专门有提醒在 Order by 后面不能使用 RAND() 函数，会导致全表扫描

简单优化 SQL 语句, 使用join

 SELECT * FROM user  AS u1  JOIN (SELECT ROUND(RAND() * ((SELECT MAX(uid) FROM `user`)-(SELECT MIN(uid) FROM user))+(SELECT MIN(uid) FROM user)) AS uid) AS u2 WHERE u1.uid >= u2.uid ORDER BY u1.uid LIMIT 1
mysql> SELECT * FROM user  AS u1  JOIN (SELECT ROUND(RAND() * ((SELECT MAX(uid) FROM `user`)-(SELECT MIN(uid) FROM user))+(SELECT MIN(uid) FROM user)) AS uid) AS u2 WHERE u1.uid >= u2.uid ORDER BY u1.uid LIMIT 1
    -> ;
+--------+----------------------------------+------+--------+-------------+--------+
| uid    | name                             | age  | gender | create_time | uid    |
+--------+----------------------------------+------+--------+-------------+--------+
| 798024 | c1a781e16b45f2ddc18b42365b8b0903 |   48 |      0 |        2017 | 798024 |
+--------+----------------------------------+------+--------+-------------+--------+
1 row in set (0.14 sec)

以上测试发现快了不少，来分析下这个SQL语句，该SQL 语句的核心 Join和随机，随机的基本公式: RAND()*(max-min)+mix,随机出一个 uid as u2 然后 条件查询，uid 自建索引，效率蛮高的。

mysql> explain SELECT * FROM user  AS u1  JOIN (SELECT ROUND(RAND() * ((SELECT MAX(uid) FROM `user`)-(SELECT MIN(uid) FROM user))+(SELECT MIN(uid) FROM user)) AS uid) AS u2 WHERE u1.uid >= u2.uid ORDER BY u1.uid LIMIT 1;
+----+-------------+------------+--------+---------------+---------+---------+------+--------+------------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows   | Extra                        |
+----+-------------+------------+--------+---------------+---------+---------+------+--------+------------------------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL |      1 |                              |
|  1 | PRIMARY     | u1         | range  | PRIMARY       | PRIMARY | 4       | NULL | 500170 | Using where                  |
|  2 | DERIVED     | NULL       | NULL   | NULL          | NULL    | NULL    | NULL |   NULL | No tables used               |
|  5 | SUBQUERY    | NULL       | NULL   | NULL          | NULL    | NULL    | NULL |   NULL | Select tables optimized away |
|  4 | SUBQUERY    | NULL       | NULL   | NULL          | NULL    | NULL    | NULL |   NULL | Select tables optimized away |
|  3 | SUBQUERY    | NULL       | NULL   | NULL          | NULL    | NULL    | NULL |   NULL | Select tables optimized away |
+----+-------------+------------+--------+---------------+---------+---------+------+--------+------------------------------+
6 rows in set (0.01 sec)

如果想随机多条呢？修改LIMIT？来看看

mysql> SELECT * FROM user  AS u1  JOIN (SELECT ROUND(RAND() * ((SELECT MAX(uid) FROM `user`)-(SELECT MIN(uid) FROM user))+(SELECT MIN(uid) FROM user)) AS uid) AS u2 WHERE u1.uid >= u2.uid ORDER BY u1.uid LIMIT 5;

mysql> SELECT * FROM user  AS u1  JOIN (SELECT ROUND(RAND() * ((SELECT MAX(uid) FROM `user`)-(SELECT MIN(uid) FROM user))+(SELECT MIN(uid) FROM user)) AS uid) AS u2 WHERE u1.uid >= u2.uid ORDER BY u1.uid LIMIT 5;
+--------+----------------------------------+------+--------+-------------+--------+
| uid    | name                             | age  | gender | create_time | uid    |
+--------+----------------------------------+------+--------+-------------+--------+
| 948535 | da2c5dbe42945a0cc5b46a1e6acf746b |   46 |      0 |        2017 | 948535 |
| 948536 | 8b6f00e7098d215d4a85b160fcbbce4f |   22 |      1 |        2017 | 948535 |
| 948537 | d3caa715182997a67fdf3ab245ea53f3 |   24 |      1 |        2017 | 948535 |
| 948538 | 727659b109bfe2a21f8be7a5c1d1b301 |   27 |      0 |        2017 | 948535 |
| 948539 | 4b5038af305fb4629d38d067f806c7ab |   27 |      0 |        2017 | 948535 |
+--------+----------------------------------+------+--------+-------------+--------+
5 rows in set (0.00 sec)

如果使用以上的SQL语句，发现查询到的数据是连续的，我们要的是随机的，不难理解 LIMIT 5 得到当前查询条件的前五条，所以是相对连续的，uid 是自增的，因为用的是储存插入的，实际项目也是相对连续的。这条SQL 一次性查询无法达到我们的需求，则可分别一条条查询，如果要求的随机条数较多，那就不建议使用该条SQL语句了。

再来一条SQL语句

mysql>  SELECT * FROM user WHERE uid >= ((SELECT MAX(uid) FROM user)-(SELECT MIN(uid) FROM user)) * RAND() + (SELECT MIN(uid) FROM user) LIMIT 1
    -> ;
+------+----------------------------------+------+--------+-------------+
| uid  | name                             | age  | gender | create_time |
+------+----------------------------------+------+--------+-------------+
| 2343 | c8dfece5cc68249206e4690fc4737a8d |   34 |      0 |        2017 |
+------+----------------------------------+------+--------+-------------+
1 row in set (0.01 sec)

explain 下

mysql> explain SELECT * FROM user WHERE uid >= ((SELECT MAX(uid) FROM user)-(SELECT MIN(uid) FROM user)) * RAND() + (SELECT MIN(uid) FROM user) LIMIT 1;
+----+-------------+-------+------+---------------+------+---------+------+---------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+---------+------------------------------+
|  1 | PRIMARY     | user  | ALL  | NULL          | NULL | NULL    | NULL | 1000340 | Using where                  |
|  4 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL |    NULL | Select tables optimized away |
|  3 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL |    NULL | Select tables optimized away |
|  2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL |    NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+---------+------------------------------+
4 rows in set (0.00 sec)

看看随机多条

mysql> SELECT * FROM user WHERE uid >= ((SELECT MAX(uid) FROM user)-(SELECT MIN(uid) FROM user)) * RAND() + (SELECT MIN(uid) FROM user) limit 20;
+------+----------------------------------+------+--------+-------------+
| uid  | name                             | age  | gender | create_time |
+------+----------------------------------+------+--------+-------------+
|  786 | fc8001f834f6a5f0561080d134d53d29 |   24 |      0 |        2017 |
| 1961 | e4dd5528f7596dcdf871aa55cfccc53c |   54 |      1 |        2017 |
| 2958 | db5cea26ca37aa09e5365f3e7f5dd9eb |   27 |      0 |        2017 |
| 3122 | f231f2107df69eab0a3862d50018a9b2 |   43 |      1 |        2017 |
| 3445 | 12092a75caa75e4644fd2869f0b6c45a |   31 |      0 |        2017 |
| 4121 | 1b69ebedb522700034547abc5652ffac |   48 |      0 |        2017 |
| 4682 | 4f5c422f4d49a5a807eda27434231040 |   38 |      1 |        2017 |
| 4815 | 187acf7982f3c169b3075132380986e4 |   26 |      1 |        2017 |
| 5028 | f02208a057804ee16ac72ff4d3cec53b |   19 |      0 |        2017 |
| 5182 | fa3dade3a49305f27f64203452ac954c |   32 |      1 |        2017 |
| 5245 | b49d4455d64520060ac01fb5a3c757e4 |   34 |      1 |        2017 |
| 5405 | bb1443cc31d7396bf73e7858cea114e1 |   40 |      1 |        2017 |
| 5486 | 03b2ceb73723f8b53cd533e4fba898ee |   21 |      0 |        2017 |
| 5700 | 7f848746fe2599dc199a75f0d02fc3d6 |   36 |      0 |        2017 |
| 5835 | f5f3b8d720f34ebebceb7765e447268b |   36 |      0 |        2017 |
| 5991 | 1ae6464c6b5d51b363d7d96f97132c75 |   49 |      1 |        2017 |
| 6064 | 09ccf3183d9e90e5ae1f425d5f9b2c00 |   36 |      0 |        2017 |
| 6160 | 08ad21c6f9da6bdf51ae0b971f43d96d |   31 |      1 |        2017 |
| 6306 | ccf0304d099baecfbe7ff6844e1f6d91 |   48 |      1 |        2017 |
| 6810 | 6194a1ee187acd6606989f03769e8f7f |   41 |      0 |        2017 |
+------+----------------------------------+------+--------+-------------+
20 rows in set (0.01 sec)

随机多条也是完美的，随机核心就是用 RAND() 随机出一个用户uid，或则随机区间，然后再进行limit 即可，此处基本阐述完了，欢迎大家批评指正。

深夜的猫
关注
专栏目录
教你如何6秒钟往MySQL插入100万条数据的实现
09-08
本文将详细解析如何在6秒钟内向MySQL数据库中插入100万条数据的实现方法。以下将围绕标题和描述中的关键点展开讨论。 首先，要实现快速插入，我们需要使用`PreparedStatement`对象。`PreparedStatement`是SQL语句的...
mysql 生成指定范围的数值 保留两位（可以是整数，可以是小数）
霍城延的随笔
09-24 2001
大家都知道mysql rand 用起来在某些场景下不是很好用 ，今天咱们就来实现一个函数。来提升他的功能 1. 随机生成 0.0.1~0.05之间的数据 DROP FUNCTION IF EXISTS rand_num ; CREATE FUNCTION rand_num ( start_num DOUBLE, end_num DOUBLE ) RETURNS DOUBLE BEGIN RETURN ROUND(start_num + RAND() * (end_num
mysql随机查询三条不重复数据【最佳实践】
最新发布
m0_53626105的博客
08-08 725
mysql获取3条不充分随机数据，三种最优解决方案。
MySQL取随机数
qq_35298337的博客
10-12 424
MySQL取随机数： 若要在i ≤ R ≤ j 这个范围得到一个随机整数R ，需要用到表达式 FLOOR(i + RAND() * (j – i + 1))。 例如， 若要在7 到 12 的范围（包括7和12）内得到一个随机整数, 可使用以下语句： SELECT FLOOR(7 + (RAND() * 6)); 也可以通过创建function取随机值,如下： CREATE FUNCTION rand_num ( start_num INTEGER, end_num INTEGER ) RE
mysql从百万数据中随机查询多条数据，主键id不自增
u012150602的博客
05-12 777
mysql从百万数据中随机查询多条数据，主键id不自增
Oracle 百万数据随机取一条性能优化
Qensq的博客
08-22 521
select * from ( select * from t where rownum < trunc(dbms_random.value(2,1000)) order by dbms_random.value ) where rownum=1 ...
MySQL 多表关联一对多查询实现取最新一条数据的方法示例
09-08
在MySQL数据库中，多表关联查询常常用于处理复杂的业务场景，特别是当数据分布在多个相关表中时...通过合理的设计和优化，我们可以有效地处理一对多关系中的数据获取问题，提高查询效率，并确保数据的准确性和一致性。
PHP实现在数据库百万条数据中随机获取20条记录的方法
01-20
本文实例讲述了PHP实现在数据库百万条数据中随机获取20条记录的方法。分享给大家供大家参考，具体如下： 额，为什么要写这个？ 在去某个公司面试时，让写个算法出来，当时就蒙了，我开发过程中用到算法的吗？又不是...
Mysql查询最近一条记录的sql语句(优化篇)
09-09
在MySQL数据库中，查询最近一条记录通常涉及到时间戳或者某个排序字段。本文主要探讨了三种不同的SQL语句来实现这一目标，从效率低下的方法到更优化的解决方案，特别是针对大量数据的情况。 首先，最简单但效率最低...
MySql分组后随机获取每组一条数据的操作
12-14
在MySQL数据库中，有时我们需要对数据进行分组处理，并从每个分组中随机选取一条记录。这在统计分析或者抽样调查等场景中非常常见。本文将详细介绍如何在MySQL中实现这一操作，以及一些关于随机选取数据的优化方法。...
【MySQL高级】数据生成与插入脚本编写与使用
记录一名在读研究生的学习笔记、感悟、开发产物
06-27 2340
编写脚本实现数据的随机生成，并将数据插入到数据库中
mysql 获取一条数据_MySQL数据库中随机获取一条或多条记录
weixin_36478445的博客
01-18 9813
在开发过程中遇到了一个问题，使用MySQL数据库，用SQL语句在表中随机获取一条或多条数据，看似简单，但是往深层研究的话还是很有深度的，查了好多资料，接下来给大家分享一下：1. 随机获取单条数据SELECT * FROM table_name ORDER BY RAND() LIMIT 1；MySQL中的RAND()函数调用可以在0和1之间产生一个随机数。这条SQL语句可以随机取出一条数据，而且将...
MySQL 随机取选取一条记录
TauCrus的博客
03-06 2344
最近项目中用到随机取数据表中任意一条数据，既然用到了就记录下来，以便下次查阅。 1、查询效率低的 SELECT * FROM table_name ORDER BY RAND() LIMIT 1 2、查询效率高的 SELECT * FROM table_name AS t1 JOIN ( SELECT ROUND(RAND() * ( (SELECT MAX(id) FROM ...
如何以最高的效率从MySQL中随机查询一条记录？（文末附视频教程）
Java___interview的博客
11-05 333
写在前面 MySQL数据库在互联网行业使用的比较多，有些小伙伴可能会认为MySQL数据库比较小，存储不了很多的数据。其实，这些小伙伴是真的不了解MySQL。MySQL的小不是说使用MySQL存储的数据少，而是说其体积小，比较轻量。使用MySQL完全可以存储千亿级别的数据，好了，说了这么多，今天给大家分享一篇有关MySQL的经典面试题：如何以最高的效率从MySQL中随机查询一条记录？ 面试题目 如何从MySQL一个数据表中查询一条随机的记录，同时要保证效率最高。 从这个题目来看，其实包含了两个要求
MySQL中，使用RAND()的查询优化
Lubin的编程专栏
11-28 3351
随机找出2万条纪录， 方案一： 最直接的语句是： SELECT * FROM moni_role ORDER BY RAND() LIMIT 20000; mysql> EXPLAIN SELECT * FROM moni_role ORDER BY RAND() LIMIT 20000; +----+-------------+--------...
MySQL数据库从表中随机获取一条或者多条记录
热门推荐
PHP网站建设，PHP技术分享
03-28 1万+
MySQL数据库从表中随机获取一条或者多条记录
mysql 高性能随机一条数据
weixin_41340064的博客
05-26 2724
但是，这种方法通常会导致性能问题，因为MySQL需要扫描整个表并计算ORDER BY RAND()函数。这个方法比前两种方法更高效，但它仍然需要扫描整个表并计算COUNT(*)函数。总的来说，使用LIMIT和OFFSET是获取高性能随机一条数据的最佳方法。这个方法通过子查询生成一个随机的id，并返回id大于或等于该值的第一行。这个方法比ORDER BY RAND()更高效，但在大表上可能会变慢。可以使用LIMIT和OFFSET来获取一个随机偏移量并返回一行数据。1.使用ORDER BY RAND()
mysql数据库查询随机100条_Mysql取随机数据效率测试（200W条中读取100条）
weixin_30466329的博客
01-20 4371
第一种方案：SELECT *FROM `follow_record` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(record_id) FROM `follow_record`)-(SELECT MIN(record_id) FROM `follow_record`))+(SELECT MIN(record_id) FROM `follow_rec...
使用C#从MySQL数据库获取单条数据
"该资源提供了一段C#代码示例，用于从MySQL数据库中提取特定数据，特别是查询表中的第一条num数据。" 在标题和描述中提到的知识点主要涉及MySQL数据库的操作，具体包括以下几个方面： 1. **MySQL数据库连接**: ...
评论 6
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值