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;
