接前文:
http://blog.itpub.net/29254281/viewspace-2120294/
前文中,Order by rand()在数据量大的时候,会有一些性能问题.
-
set autocommit=false;
-
set @roomid:=-1;
-
select
-
min(roomid) into @roomid
-
from
-
room_info
-
where
-
roomid >
-
(
-
select
-
floor(max(roomid) * rand() + 1)
-
from
-
room_info
-
)
-
and state = 1;
-
-
update room_info
-
set
-
state = 2
-
where
-
roomid =@roomid
-
and state = 1;
-
-
select @roomid;
-
-
commit;
优化的方式就是从最大的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://www.cdkjz.cn/article/ggicgo.html