mysql order by rand()性能问题和解决方案 [英] mysql order by rand() performance issue and solution

问题描述

我正在使用rand()命令从数据库中生成随机行，没有任何问题，但是我得知随着数据库大小的增加，此rand()会导致服务器负载沉重，所以我一直在寻找替代方法，并尝试通过生成一个使用php rand()函数的随机数，并将其作为id放入mysql查询中，因为mysql知道行ID，所以速度非常快 但是问题是我的桌子上所有数字都不可用.例如1,2,5,9,12这样.

i was using order by rand() to generate random rows from database without any issue but i reaalised that as the database size increase this rand() causes heavy load on server so i was looking for an alternative and i tried by generating one random number using php rand() function and put that as id in mysql query and it was very very fast since mysql was knowing the row id but the issue is in my table all numbers are not availbale.for example 1,2,5,9,12 like that.

如果php rand()生成数字3,4等，则查询将为空，因为没有编号为3、4等的id.

if php rand() generate number 3,4 etc the query will be blank as there is no id with number 3 , 4 etc.

从php生成最佳随机数的最佳方法是什么，但是它应该在该表中生成可用的no，因此必须检查该表.请告知.

what is the best way to generate random numbers preferable from php but it should generate the available no in that table so it must check that table.please advise.

$id23=rand(1,100000000);
    SELECT items FROM tablea where status='0' and id='$id23' LIMIT 1

以上查询速度很快，但有时不会生成，数据库中没有可用的标签.

the above query is fast but generate sometimes no which is not availabel in database.

    SELECT items FROM tablea where status=0 order by rand() LIMIT 1

上面的查询太慢，导致服务器上的负载很重

the above query is too slow and causes heavy load on server

推荐答案

首先，全部生成一个从1到MAX(id)的随机值，而不是100000000.

First of, all generate a random value from 1 to MAX(id), not 100000000.

那么至少有两个好的解决方案:

Then there are at least a couple of good solutions:

1. 使用>而不是=

SELECT items FROM tablea where status='0' and id>'$id23' LIMIT 1

在(status,id,items)上创建索引，以使其成为仅索引的查询.

Create an index on (status,id,items) to make this an index-only query.

使用=，但是如果找不到匹配项，请使用其他随机值重试.有时需要几次尝试，但通常只需要尝试一次. =应该更快，因为它可以使用主键.如果速度更快，并且90％的时间可以一次尝试，那么一次以上的尝试就可以弥补另外10％的时间.取决于您的ID值中有多少空白.

Use =, but just try again with a different random value if you don't find a hit. Sometimes it will take several tries, but often it will take only one try. The = should be faster since it can use the primary key. And if it's faster and gets it in one try 90% of the time, that could make up for the other 10% of the time when it takes more than one try. Depends on how many gaps you have in your id values.

这篇关于mysql order by rand()性能问题和解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！