因为业务需要,要求在一个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