您好,登錄后才能下訂單哦!
下文內(nèi)容主要給大家?guī)?a title="mysql" target="_blank" href="http://www.kemok4.com/mysql/">mysql的order by rand方法實(shí)際運(yùn)用,這里所講到的知識,與書籍略有不同,都是億速云專業(yè)技術(shù)人員在與用戶接觸過程中,總結(jié)出來的,具有一定的經(jīng)驗(yàn)分享價(jià)值,希望給廣大讀者帶來幫助。
從一次查詢中隨機(jī)返回一條數(shù)據(jù),一般使用mysql的order by rand() 方法來實(shí)現(xiàn)
例如: 從20萬用戶中隨機(jī)抽取1個(gè)用戶
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)
根據(jù)分析結(jié)果,運(yùn)行需要0.25秒,order by rand() 需要使用臨時(shí)表(Using temporary),需要使用文件排序(Using filesort),效率低下。
1.首先獲取查詢的總記錄條數(shù)total
2.在總記錄條數(shù)中隨機(jī)偏移N條(N=0~total-1)
3.使用limit N,1 獲取記錄
代碼如下:
<?php// 獲取總記錄數(shù)$sqlstr = 'select count(*) as recount from user';$query = mysql_query($sqlstr) or die(mysql_error());$stat = mysql_fetch_assoc($query);$total = $stat['recount'];// 隨機(jī)偏移$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)
本篇介紹了mysql order by rand() 效率優(yōu)化方法 ,更多相關(guān)內(nèi)容請關(guān)注億速云。
相關(guān)推薦:
解讀php的PDO連接數(shù)據(jù)庫的相關(guān)內(nèi)容
講解PHP面向?qū)ο?,PHP繼承相關(guān)代碼
在PHP中使用魔術(shù)方法__CLASS__來獲取類名的相關(guān)操作
以上就是mysql的order by rand方法實(shí)際運(yùn)用詳細(xì)內(nèi)容,更多請關(guān)注億速云其它相關(guān)文章!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。