表记录 1876302,totalScore 创建了索引。
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
这样的耗时不能接受。
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 左右。
耗时最少,推荐使用第三种。
7 天前