mysql随机调用高效率语句汇总
以下所有测试结果,为300w条真实数据,所占内存: 6.2 GB 。
常规的语句,速度最慢,数据量大的情况下,不建议使用:
SELECT * FROM `ask` order BY RAND() limit 0,500
查询花费40s +
2. (共 500 行, 查询花费 0.0367 秒。) 速度最快
SELECT * FROM ask WHERE Id >= ((SELECT MAX(Id) FROM ask)-(SELECT MIN(Id) FROM ask)) * RAND() + (SELECT MIN(Id) FROM ask) LIMIT 500
3. (共 500 行, 查询花费 0.0060 秒。) 速度快,但是ID 连贯,非随机
SELECT * FROM ask AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(Id) FROM `ask`)-(SELECT MIN(Id) FROM ask))+(SELECT MIN(Id) FROM ask)) AS Id) AS t2 WHERE t1.Id >= t2.Id ORDER BY t1.Id LIMIT 500
4. (共 500 行, 查询花费 0.2487 秒。)
SELECT * FROM `ask` WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `ask`)-(SELECT MIN(id) FROM `ask`)) + (SELECT MIN(id) FROM `ask`))) ORDER BY id LIMIT 500;
5.(共 500 行, 查询花费 0.2437 秒。)
SELECT * FROM `ask` WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `ask`))) ORDER BY id LIMIT 500
6. (共 500 行, 查询花费 0.0381 秒。)
SELECT * FROM ask WHERE id >= ((SELECT MAX(id) FROM ask)-(SELECT MIN(id) FROM ask)) * RAND() + (SELECT MIN(id) FROM ask) limit 500;