香港aa三级久久三级,久久人人爽人爽人人爽av ,亚洲AV日韩精品久久久久久久

從一次查詢中隨機返回一條數據，一般使用mysql的order by rand() 方法來實現

例如: 從20萬用戶中隨機抽取1個用戶

mysql&gt;?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&gt;?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-1)
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&gt;?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&gt;?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() 效率優化方法，更多相關內容請關注php中文網。