MySQL随机挑选资源--优化

2022-07-31 18:43:31

字体：大中小

来源：转载

供稿：网友

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登陆告错mysql unknown variable basedir

下一篇：MySQL系统表故障研究