使用MySQL从20万条数据中通过筛选随机取出1条数据的方法

因为业务需要，要求在一个where筛选完的数据集中随机取出1条数据。

经典的方法

SELECT * FROM table where 条件 ORDER BY RAND() limit 1;

适用入门级应用，怎么这样说呢?

因为数据量小时，倒没多大问题，由于会进行全表扫描，当数据量渐渐巨型时，查询时间会相当变态。

本地环境运行需时在0.276s左右。

百度出来别人验证过，性能高效的方法是

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;

加上我们需要的条件

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 and 条件1 and 条件2 and 条件3
ORDER BY t1.id LIMIT 1;

我们下面就来测试一下
生产虚拟数据
先生成20万条虚拟数据，要随机生成，保证数据的均匀分布。

跑一下
平均只用0.006s左右

统计符合条件的数目

SELECT COUNT(*) FROM `table` WHERE 条件1 and 条件2 and 条件3;

符合条件的数据有8340条，占比0.0417。

数据校验
运行8000次
校验通过：0.999875, 检验不通过：0.000125
计算了一下，是1条。

运行了两次程序，结果依旧，原因暂未查明。

数据重复性测试

运行	出现1次	2次	3次	4次	5次	6次	7次	8次	9次	10次	11次	12次	13次	14次
1000	0.7970	0.1760	0.0270	0	0	0	0	0	0	0	0	0	0	0
2000	0.6355	0.265	0.0825	0.014	0.0025	0	0	0	0	0	0	0	0	0
3000	0.5367	0.2853	0.1290	0.0307	0.0100	0.0080	0	0	0	0	0	0	0	0
4000	0.4603	0.2900	0.1388	0.0590	0.0313	0.0150	0.0035	0.0020	0	0	0	0	0	0
5000	0.3844	0.3000	0.1632	0.0872	0.042	0.0132	0.0084	0.0016	0	0	0	0	0	0
6000	0.3405	0.2833	0.1965	0.0967	0.0500	0.0160	0.0070	0.0067	0.0030	0	0	0	0	0
7000	0.2990	0.2700	0.1701	0.1000	0.0729	0.0497	0.0230	0.0069	0.0039	0.0043	0	0	0	0
8000	0.266	0.2593	0.1935	0.1095	0.0719	0.0465	0.0263	0.014	0.0034	0.0025	0.0028	0.0045	0	0

由此来看，随机性还是挺不错的，多数数据是出现在前段，这个方法可以使用。

注意：在max,min里面的语句不能再加入where，加入后发现查询明显减慢，经EXPLAIN分析是会导致一条或多条select_type为SUBQUERY进行全表扫描。

美化SQL：

SELECT * FROM `T_UserDefaultProfile` AS t1 
  JOIN (
    SELECT ROUND(
      RAND() * (
                (SELECT MAX(F_UdpID) FROM `T_UserDefaultProfile`)-(SELECT MIN(F_UdpID) FROM `T_UserDefaultProfile`)
               ) + (SELECT MIN(F_UdpID) FROM `T_UserDefaultProfile`)
    ) AS F_UdpID
  ) AS t2
WHERE t1.F_UdpID >= t2.F_UdpID and 1=1 and 2=2 and 3=3
ORDER BY t1.F_UdpID LIMIT 1;

SELECT ROUND(1,10)

————————————————
原文链接：https://blog.csdn.net/Wai_Leung/article/details/88318905