表数据
tm_timeline
40多w数据
原始查询
SELECT * FROM tm_timeline
ORDER BY RAND()
LIMIT 5
运行sql
简单优化
SELECT * FROM tm_timeline t1 join (SELECT id FROM tm_timeline ORDER BY rand() LIMIT 5) t2 on t1.id=t2.id
运行sql
谷歌给的方案
id 必须是连续自增了，雪花id无效
SELECT * FROM tm_timeline AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM tm_timeline)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id ASC LIMIT 5;
运行sql
另外一种写法sql
和谷歌的效率差不多
id 必须是连续自增了，雪花id无效
SELECT * FROM tm_timeline AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM tm_timeline)-(SELECT MIN(id) FROM tm_timeline))+(SELECT MIN(id) FROM tm_timeline)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 5;
运行sql
cs
