(转)mysql随机查询的优化 mysql随机函数RAND()的使用方法
mysql随机查询最常见的写法如下:
- SELECT * FROM tablename ORDER BY RAND() LIMIT 1
SELECT * FROM tablename ORDER BY RAND() LIMIT 1
php手册上如此解释:
AboutselectingrandomrowsfromaMySQLtable:
SELECT*FROMtablenameORDERBYRAND()LIMIT1
worksforsmalltables,butoncethetablesgrowlargerthan300,000recordsorsothiswillbeveryslowbecauseMySQLwillhavetoprocessALLtheentriesfromthetable,orderthemrandomlyandthenreturnthefirstrowoftheorderedresult,andthissortingtakeslongtime.Insteadyoucandoitlikethis(atleastifyouhaveanauto_incrementPK):
SELECTMIN(id),MAX(id)FROMtablename;
Fetchtheresultinto$a
$id=rand($a[0],$a[1]);
SELECT*FROMtablenameWHEREid>='$id'LIMIT1
大意是说,如果你用ORDERBYRAND()来随机读取记录的话,当数据表记录达到30万或者更多的时候,mysql将非常吃力.
所以php手册里给了一种方法,结合php来实现:
首先SELECTMIN(id),MAX(id)FROMtablename;取数据库里最大最小值;
然后$id=rand($a[0],$a[1]);产生一个随机数;
最后SELECT*FROMtablenameWHEREid>='$id'LIMIT1将上面产生的随机数带入查询;