国内最全IT社区平台 联系我们 | 收藏本站
华晨云阿里云优惠2
您当前位置:首页 > 数据库 > 数据库应用 > mysql order by rand() 效率优化方法

mysql order by rand() 效率优化方法

来源:程序员人生   发布时间:2016-06-27 16:01:50 阅读次数:2929次

从1次查询中随机返回1条数据,1般使用mysql的order by rand() 方法来实现

例如: 从20万用户中随机抽取1个用户

mysql> select * from user order by rand() limit 1; +-------+------------+----------------------------------+----------+--------------+-----------+ | id | phone | password | salt | country_code | ip | +-------+------------+----------------------------------+----------+--------------+-----------+ | 15160 | 6549721306 | e4f302120c006880a247b652ad0e42f2 | 40343586 | 86 | 127.0.0.1 | +-------+------------+----------------------------------+----------+--------------+-----------+ 1 row in set (0.25 sec) mysql> explain select * from user order by rand() limit 1; +----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+ | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 200303 | Using temporary; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+ 1 row in set (0.00 sec)

根据分析结果,运行需要0.25秒,order by rand() 需要使用临时表(Using temporary),需要使用文件排序(Using filesort),效力低下。


改进方法

1.首先获得查询的总记录条数total
2.在总记录条数中随机偏移N条(N=0~total⑴)
3.使用limit N,1 获得记录


代码以下:

<?php // 获得总记录数 $sqlstr = 'select count(*) as recount from user'; $query = mysql_query($sqlstr) or die(mysql_error()); $stat = mysql_fetch_assoc($query); $total = $stat['recount']; // 随机偏移 $offset = mt_rand(0, $total-1); // 偏移后查询 $sqlstr = 'select * from user limit '.$offset.',1'; $query = mysql_query($sqlstr) or die(mysql_error()); $result = mysql_fetch_assoc($query); print_r($result); ?>

分析:

mysql> select * from user limit 23541,1; +-------+------------+----------------------------------+----------+--------------+-----------+ | id | phone | password | salt | country_code | ip | +-------+------------+----------------------------------+----------+--------------+-----------+ | 23542 | 3740507464 | c8bc1890de179538d8a49cc211859a46 | 93863419 | 86 | 127.0.0.1 | +-------+------------+----------------------------------+----------+--------------+-----------+ 1 row in set (0.01 sec) mysql> explain select * from user limit 23541,1; +----+-------------+-------+------+---------------+------+---------+------+--------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+--------+-------+ | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 200303 | NULL | +----+-------------+-------+------+---------------+------+---------+------+--------+-------+ 1 row in set (0.00 sec)
生活不易,码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
程序员人生
------分隔线----------------------------
分享到:
------分隔线----------------------------
关闭
程序员人生