order by rand() 的代替

最新推荐文章于 2022-08-07 15:55:35 发布

原创最新推荐文章于 2022-08-07 15:55:35 发布 · 442 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#MySQL

本文介绍了一种改进的方法来替代使用 ORDER BY RAND() 进行随机查询，通过先获取表中记录总数再进行随机数生成及 LIMIT 查询，显著提高了查询效率。

使用 ORDER BY RAND()：$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");

替换为：

$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);

$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

// what NOT to do:
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");

// much better:

$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);

$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");