因为业务需要，要求在一个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进行全表扫描。
网友评论