SELECT userid FROM user WHERE userename =0 ORDER BY rand( ) LIMIT 30
从user表里面随机的取出30个ID 这个sql怎么优化 数据库是mysql.
这个表数据量很大 执行要9秒...
- foolbirdflyfirst
- (龙腾虎跃)
- 的答案:
正好,之前有看到个order by rand()的优化,老外说即使innodb引擎都有10倍提升,我测试了下,数据越多,确实有更多提升,但是10倍提升还没测出来。你数据量大可以再帮我测试下
SELECT i.userid FROM ( SELECT @cnt := COUNT(*) + 1, @lim := 30 FROM users ) vars STRAIGHT_JOIN( SELECT r.*, @lim := @lim - 1 FROM users r WHERE (@cnt := @cnt - 1) AND RAND() < @lim / @cnt ) i
只取userid,可以再改改
SELECT i.userid FROM ( SELECT @cnt := COUNT(*) + 1, @lim := 30 FROM users ) vars STRAIGHT_JOIN( SELECT r.userid, @lim := @lim - 1 FROM users r WHERE (@cnt := @cnt - 1) AND RAND() < @lim / @cnt ) i