例如: 從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-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> 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() 效率優化方法 ，更多相關內容請關注php中文網。

相關推薦：

解讀php的PDO連接數據庫的相關內容

講解PHP面向對象，PHP繼承相關代碼

在PHP中使用魔術方法__CLASS__來獲取類名的相關操作

以上就是mysql order by rand() 效率優化方法的詳細內容，更多請關注php中文網其它相關文章！