MySQL随机选取资源--优化

接前文:
http://blog.itpub.net/29254281/viewspace-2120294/

前文中,Order by rand()在数据量大的时候,会有一些性能问题.

优化的方式就是从最大的ID,随机选取一个值。
这样避免了排序.
但是应用程序还是需要判断,Update的影响行数是否为0.如果为0,则需要再次调用.

大招版本:

set autocommit=false;
set @roomid:=-1;
select max(roomid) into @roomid from room_info;
set @roomid:=floor(rand()*@roomid+1);
update room_info
set
state = 2
where
roomid =
coalesce
(
(select roomid from (select min(roomid) roomid from room_info where state=1 and roomid > @roomid) a),
(select roomid from (select max(roomid) roomid from room_info where state=1 and roomid < @roomid) b)
)
and state = 1 and @roomid:=roomid;
select @roomid;
commit;

网站栏目：MySQL随机选取资源--优化
链接分享：http://kswjz.com/article/ggicgo.html

我们在微信上24小时期待你的声音

解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流

行业动态