mysql order by rand() 效率最佳化方法

從一次查詢中隨機傳回一條數據，一般使用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-1)
3.使用limit N,1 取得記錄
程式碼如下：

<?php // 获取总记录数$sqlstr = &#39;select count(*) as recount from user&#39;;$query = mysql_query($sqlstr) or die(mysql_error());$stat = mysql_fetch_assoc($query);$total = $stat[&#39;recount&#39;];// 随机偏移$offset = mt_rand(0, $total-1);// 偏移后查询$sqlstr = &#39;select * from user limit &#39;.$offset.&#39;,1&#39;;$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中文網其他相關文章！