函数rand();
在有数百万行时工作缓慢,
我有表"扫描器">
id image type
1 name 1
2 name 4
3 name 76
19999999 name 3
需要选择
select * from banners where type = 1 order by rand() limit 1
如果没有结果,则
select * from banners where type = 3 order by rand() limit 1
如果没有结果,则
select * from banners order by rand() limit 1
我尝试
select * from (
(select * from banners where type = 1 order by rand() limit 1) union
(select * from banners where type = 3 order by rand() limit 1) union
(select * from banners order by rand() limit 1)
) as r limit 1
但是非常慢!
您可以组合查询:
select *
from banners
order by type=1 desc, type=3 desc, rand()
limit 1;
但这仍然可能需要阅读整个表格。
您有INDEX(type)
吗?如果是这样,可能会帮助您的前2个SELECTs
。
提供SHOW INDEXES
。它可以表明;基数;类型的是这样的,即它将"始终"或"从不"执行表扫描。此外,为每种情况提供EXPLAIN SELECT ...
。
如果索引是有用的并且具有type=1
的行不多,则该查询将相对较快。等等
本文讨论了几种有效地选择随机行的方法。看看哪一个适用于你的3个案例中的每一个。然后编写一个类似的存储例程
BEGIN
... type=1
IF a row found
RETURN...
... type=1
IF a row found
RETURN...
Return a random row from the whole table
END
select * from banners
order by Case
when Type=1 then 1
when Type=3 then 2
else 3
end asc, rand()
limit 1;