MySQL 随机获取N条数据

发表于 | 分类于
建立测试表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 用户表
CREATE TABLE `user` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` TINYINT(1) NOT NULL,
  `name` VARCHAR(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

-- 用户映射表,保存type=1的用户ID
CREATE TABLE `user_map` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) UNSIGNED NOT NULL,
    PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
生成测试数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE `generateUser` (IN num INT)
BEGIN   
	DECLARE chars varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
	DECLARE nickname VARCHAR(24);
	DECLARE count int DEFAULT 1;
	DECLARE len int;	
	WHILE count <= num DO
		SET len = 24;
		SET nickname = '';		
		WHILE len > 0 DO
			SET nickname = CONCAT(nickname, SUBSTRING(chars, FLOOR(1 + RAND() * 62), 1));
			SET len = len - 1;
		END WHILE;
		INSERT INTO `user`(type, nickname) VALUES(FLOOR(RAND() * 2), nickname);
		SET count = count + 1;
	END WHILE;
END
$$
DELIMITER ;

-- 生成500万测试数据
CALL generateUser(5000000);

5OO万数据中获取随机7条记录

1
2
3
4
5
6
7
8
9
10
11
12
-- 第一种：查询时间 6.654s, 数据随机, 大数据量时效率太差(不推荐)
SELECT * FROM `user` ORDER BY RAND() LIMIT 7;

-- 第二种 查询时间 0.005s, 生成随机数 rnd, 并获取 id >= rnd 的数据, 所以获取的数据是连续的, 若要获取的数据在表内分布不均匀, 不建议采用此方法
SELECT u.* FROM user u 
INNER JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) - MIN(id) FROM user) + (SELECT MIN(id) FROM user)) AS rnd) uu ON u.id >= uu.rnd 
LIMIT 7;

-- 第三种 查询时间 0.005s, 数据随机
SELECT u.* FROM user u 
INNER JOIN (SELECT ROUND(RAND ) * (SELECT MAX(id) - MIN(id) FROM user) + (SELECT MIN(id) FROM user)) AS rnd FROM user LIMIT 7) uu ON u.id = uu.rnd 
LIMIT 7;

ID 不连续或分配不均匀
场景：总共有用户100个, type = 1 的用户ID为 20-30 和 70-90, 随机获取7条 type = 1 的用户数据

错误的解决方案

采用第二种方式, 取得的随机数范围为 1-100, 即使在获取随机数时加上条件, 随机数能命中 type = 1 的用户ID的概率并不高, 有可能出现获取数据为空的情况, 所以这种情况下, 不建议采用这种方式获取随机记录

1
SELECT MAX(id) - MIN(id) FROM user WHERE type = 1

建议解决方案

创建 user_map 表, 保存所有 type = 1 的用户ID, 这样一来, 只要随机获取 user_map 表内的 id, 根据 id 找到对应的用户即可

1
2
3
4
5
6
7
8
SELECT * FROM user u 
INNER JOIN (
    SELECT um.* FROM user_map um
	INNER JOIN (
	    SELECT ROUND(RAND() * (SELECT MAX(id) - MIN(id) FROM user_map) + (SELECT MIN(id) FROM user_map)) AS rnd FROM user_map LIMIT 7
	) umm ON um.id = umm.rnd
	LIMIT 7
) um ON u.id = um.user_id

0%