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

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