-- 方式一 :数据量119,544行, 多次测试平均每次查询耗时约0.042s (45次循环查询45条约1.89s)
SELECT t1.*
FROM A AS t1 JOIN
(
SELECT ROUND(RAND() * ((SELECT MAX(KeyId) FROM A)-(SELECT MIN(KeyId) FROM A))+(SELECT MIN(KeyId) FROM A)) AS KeyId2
) AS t2
WHERE t1.KeyId >= t2.KeyId2
ORDER BY t1.KeyId LIMIT 1;
-- 方式一 :数据量598,791行, 多次测试平均每次查询耗时约0.053s (45次循环查询45条约2.385s)
SELECT t1.*
FROM B AS t1 JOIN
(
SELECT ROUND(RAND() * ((SELECT MAX(KeyId) FROM B)-(SELECT MIN(KeyId) FROM B))+(SELECT MIN(KeyId) FROM B)) AS KeyId2
) AS t2
WHERE t1.KeyId >= t2.KeyId2
ORDER BY t1.KeyId LIMIT 1;
-- 方式二 :数据量119,544行, 多次测试平均每次查询耗时约0.926s(3次循环查询45条约2.778s)
select * FROM A order by rand() limit 0,15;
-- 方式二 :数据量598,791行, 多次测试平均每次查询耗时约5.556s (3次循环查询45条约16.668s)
select * FROM B order by rand() limit 0,15;
-- 总结:随机查询很多条数据,20万条数据以内建议方式二;否则建议方式一宁愿多次查询。实际中两种方式可以一起使用,以表的MAX(ID)作为分别两种方式的判断条件。
mysql order by rand()很慢,怎么办?提供一种解决实现思路。
最新推荐文章于 2024-06-22 10:25:57 发布