MySQL 随机排序 ORDER BY RAND() 性能优化

表记录1876302,totalScore创建了索引。

第一种:MySQL 随机排序常规写法:

1
SELECT * FROM users WHERE totalScore BETWEEN 5 AND 100 ORDER BY RAND() LIMIT 100;

执行耗时1.18s

1
SELECT * FROM users WHERE totalScore BETWEEN 5 AND 100 ORDER BY RAND() LIMIT 100;

执行耗时1.25s

这样的耗时不能接受。

第二种:stackoverflow上找了一个黑科技写法:

1
SELECT * FROM users WHERE totalScore BETWEEN 5 AND 100 ORDER BY 37*(UNIX_TIMESTAMP() ^ id) & 0xffff LIMIT 100;

执行耗时150ms

1
SELECT * FROM users WHERE totalScore BETWEEN 5 AND 100 ORDER BY 37*(UNIX_TIMESTAMP() ^ id) & 0xffff LIMIT 100;

执行耗时153ms

执行耗时直接缩短至150ms,已经比上一个写法快很多了,而且LIMIT 1000时耗时也是150ms左右。

第三种方式:

1
2
3
4
SELECT *
FROM users AS u
INNER JOIN (SELECT id FROM users WHERE totalScore BETWEEN 5 AND 100 ORDER BY RAND() LIMIT 100) AS t ON t.id=u.id
WHERE 1

执行耗时110ms

LIMIT 1000时耗时也稳定在110ms左右。

耗时最少,推荐使用第三种。