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

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

第一种：MySQL 随机排序常规写法：展开目录

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

执行耗时 1.18s

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

执行耗时 1.25s

这样的耗时不能接受。

第二种：stackoverflow 上找了一个黑科技写法：展开目录

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

执行耗时 150ms

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

执行耗时 153ms

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

第三种方式：展开目录

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 左右。

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