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

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

setautocommit=false; set@roomid:=-1; select min(roomid)into@roomid from room_info where roomid> ( select floor(max(roomid)*rand()+1) from room_info ) andstate=1;updateroom_info set state=2 where roomid=@roomid andstate=1;select@roomid;commit;

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

大招版本:

setautocommit=false; set@roomid:=-1; selectmax(roomid)into@roomidfromroom_info; set@roomid:=floor(rand()*@roomid+1); updateroom_info set state=2 where roomid= coalesce ( (selectroomidfrom(selectmin(roomid)roomidfromroom_infowherestate=1androomid>@roomid)a), (selectroomidfrom(selectmax(roomid)roomidfromroom_infowherestate=1androomid<@roomid)b) ) andstate=1and@roomid:=roomid;select@roomid;commit;