mysql随机查询效率优化
最近由于需要研究了一下MYSQL的随机抽取实现方法。举个例子，要从tablename表中随机提取一条记录，大家一般的写法就是： SELECT * FROM content ORDER BY RAND () LIMIT 1 【3万条记录查询花费 0.3745 秒（下同）；从mysql slow query log看出“ORDER BY RAN
最近由于需要研究了一下MYSQL的随机抽取实现方法。举个例子，要从tablename表中随机提取一条记录，大家一般的写法就是：
<code><span>SELECT </span><span>*</span><span> FROM content ORDER BY RAND</span><span>()</span><span> LIMIT </span><span>1</span></code>
【3万条记录查询花费 0.3745 秒（下同）；从mysql slow query log看出“ORDER BY RAND() ”全表扫描了2次！】
后来我查了一下MYSQL的官方手册，里面针对RAND()的提示大概意思就是，在ORDER BY从句里面不能使用RAND()函数，因为这样会导致数据列被多次扫描。但是在MYSQL 3.23版本中，仍然可以通过ORDER BY RAND()来实现随机。
但是真正测试一下才发现这样效率非常低。一个15万余条的库，查询5条数据，居然要8秒以上。查看官方手册，也说rand()放在ORDER BY 子句中会被执行多次，自然效率及很低。
搜索Google，采用JOIN，查询max(id) * rand()来随机获取数据。
<code><span>SELECT </span><span>*</span><span> FROM </span><span>`content`</span><span> AS t1 JOIN </span><span>(</span><span>SELECT ROUND</span><span>(</span><span>RAND</span><span>()</span><span>*</span><span>(</span><span>SELECT MAX</span><span>(</span><span>id</span><span>)</span><span> FROM </span><span>`content`</span><span>))</span><span> AS id</span><span>)</span><span> AS t2 WHERE t1</span><span>.</span><span>id </span><span>>=</span><span> t2</span><span>.</span><span>id ORDER BY t1</span><span>.</span><span>id ASC LIMIT </span><span>1</span><span>;</span></code>
【查询花费 0.0008 秒，飘易认为可以推荐使用这个语句！！】
但是这样会产生连续的5条记录。解决办法只能是每次查询一条，查询5次。即便如此也值得，因为15万条的表，查询只需要0.01秒不到。
有一个方法：
<code><span>SELECT </span><span>*</span><span> FROM </span><span>`content`</span><span> AS a JOIN </span><span>(</span><span> SELECT MAX</span><span>(</span><span> ID </span><span>)</span><span> AS ID FROM </span><span>`content`</span><span>)</span><span> AS b ON </span><span>(</span><span> a</span><span>.</span><span>ID </span><span>>=</span><span> FLOOR</span><span>(</span><span> b</span><span>.</span><span>ID </span><span>*</span><span> RAND</span><span>(</span><span>)</span><span>)</span><span>)</span><span> LIMIT </span><span>5</span><span>;</span></code>
上面这种方式保证了一定范围内的随机,查询花费 0.4265 秒，也不推荐。
下面的语句，mysql的论坛上有人使用
<code><span>SELECT </span><span>*</span><span> FROM </span><span>`content`</span><span> WHERE id </span><span>>=</span><span>(</span><span>SELECT FLOOR</span><span>(</span><span> MAX</span><span>(</span><span>id</span><span>)</span><span>*</span><span> RAND</span><span>())</span><span> FROM </span><span>`content`</span><span>)</span><span> ORDER BY id LIMIT </span><span>1</span><span>;</span></code>
【查询花费 1.2254 秒，飘易强烈不推荐！因为实测后，3万行的表，这个语句竟然会扫描500万行！！】
跟上面的语句还是有很大差距。总觉有什么地方不正常。于是我把语句改写了一下。
<code><strong><span><span>SELECT </span><span>*</span><span> FROM </span><span>`content`</span><span> WHERE id </span><span>>=</span><span>(</span><span>SELECT floor</span><span>(</span><span>RAND</span><span>()</span><span>*</span><span>(</span><span>SELECT MAX</span><span>(</span><span>id</span><span>)</span><span> FROM </span><span>`content`</span><span>)))</span><span> ORDER BY id LIMIT </span><span>1</span><span>;</span></span></strong></code>
【查询花费 0.0012 秒】
这下，效率又提高了，查询时间只有0.01秒
最后，再把语句完善一下，加上MIN(id)的判断。我在最开始测试的时候，就是因为没有加上MIN(id)的判断，结果有一半的时间总是查询到表中的前面几行。
完整查询语句是：
<code><span>SELECT </span><span>*</span><span> FROM </span><span>`content`</span><span> WHERE id </span><span>>=</span><span>(</span><span>SELECT floor</span><span>(</span><span> RAND</span><span>()</span><span>*</span><span>((</span><span>SELECT MAX</span><span>(</span><span>id</span><span>)</span><span> FROM </span><span>`content`</span><span>)-(</span><span>SELECT MIN</span><span>(</span><span>id</span><span>)</span><span> FROM </span><span>`content`</span><span>))</span><span>+</span><span>(</span><span>SELECT MIN</span><span>(</span><span>id</span><span>)</span><span> FROM </span><span>`content`</span><span>)))</span><span> ORDER BY id LIMIT </span><span>1</span><span>;</span></code>
【查询花费 0.0012 秒】
<code><span>SELECT </span><span>*</span><span> FROM </span><span>`content`</span><span> AS t1 JOIN </span><span>(</span><span>SELECT ROUND</span><span>(</span><span>RAND</span><span>()</span><span>*</span><span>((</span><span>SELECT MAX</span><span>(</span><span>id</span><span>)</span><span> FROM </span><span>`content`</span><span>)-(</span><span>SELECT MIN</span><span>(</span><span>id</span><span>)</span><span> FROM </span><span>`content`</span><span>))+(</span><span>SELECT MIN</span><span>(</span><span>id</span><span>)</span><span> FROM </span><span>`content`</span><span>))</span><span> AS id</span><span>)</span><span> AS t2 WHERE t1</span><span>.</span><span>id </span><span>>=</span><span> t2</span><span>.</span><span>id ORDER BY t1</span><span>.</span><span>id LIMIT </span><span>1</span><span>;</span></code>
【查询花费 0.0008 秒】
最后在php中对这两个语句进行分别查询10次，
前者花费时间 0.147433 秒
后者花费时间 0.015130 秒
看来采用JOIN的语法比直接在WHERE中使用函数效率还要高很多。（via）
======================================
【好了，最后飘易来总结下】：
第一种方案，即原始的 Order By Rand() 方法：
<code><span>$sql</span><span>=</span><span>"SELECT * FROM content ORDER BY rand() LIMIT 12"</span><span>;</span><span>
$result</span><span>=</span><span>mysql_query</span><span>(</span><span>$sql</span><span>,</span><span>$conn</span><span>);</span><span>
$n</span><span>=</span><span>1</span><span>;</span><span>
$rnds</span><span>=</span><span>''</span><span>;</span><span>while</span><span>(</span><span>$row</span><span>=</span><span>mysql_fetch_array</span><span>(</span><span>$result</span><span>)){</span><span>
$rnds</span><span>=</span><span>$rnds</span><span>.</span><span>$n</span><span>.</span><span>". <a href="show%22</span><span>.</span><span>%24row</span><span>%5B</span><span>" id><span>].</span><span>"-"</span><span>.</span><span>strtolower</span><span>(</span><span>trim</span><span>(</span><span>$row</span><span>[</span><span>'title'</span><span>])).</span><span>"'>"</span><span>.</span><span>$row</span><span>[</span><span>'title'</span><span>].</span><span>"</span></a><br>\n"</span><span>;</span><span>
$n</span><span>++;</span><span>}</span></code>3万条数据查12条随机记录，需要0.125秒，随着数据量的增大，效率越来越低。
第二种方案，改进后的 JOIN 方法：
<code><span>for</span><span>(</span><span>$n</span><span>=</span><span>1</span><span>;</span><span>$n</span><span><span>12</span><span>;</span><span>$n</span><span>++){</span><span>
$sql</span><span>=</span><span>"SELECT * FROM `content` AS t1
JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `content`)) AS id) AS t2
WHERE t1.id >= t2.id ORDER BY t1.id ASC LIMIT 1"</span><span>;</span><span>
$result</span><span>=</span><span>mysql_query</span><span>(</span><span>$sql</span><span>,</span><span>$conn</span><span>);</span><span>
$yi</span><span>=</span><span>mysql_fetch_array</span><span>(</span><span>$result</span><span>);</span><span>
$rnds </span><span>=</span><span> $rnds</span><span>.</span><span>$n</span><span>.</span><span>". <a href="show%22</span><span>.</span><span>%24yi</span><span>%5B</span><span>" id><span>].</span><span>"-"</span><span>.</span><span>strtolower</span><span>(</span><span>trim</span><span>(</span><span>$yi</span><span>[</span><span>'title'</span><span>])).</span><span>"'>"</span><span>.</span><span>$yi</span><span>[</span><span>'title'</span><span>].</span><span>"</span></a><br>\n"</span><span>;</span><span>}</span></span></code>3万条数据查12条随机记录，需要0.004秒，效率大幅提升，比第一种方案提升了约30倍。缺点：多次select查询，IO开销大。
第三种方案，SQL语句先随机好ID序列，用 IN 查询（飘易推荐这个用法，IO开销小，速度最快）：
<code><span>$sql</span><span>=</span><span>"SELECT MAX(id),MIN(id) FROM content"</span><span>;</span><span>
$result</span><span>=</span><span>mysql_query</span><span>(</span><span>$sql</span><span>,</span><span>$conn</span><span>);</span><span>
$yi</span><span>=</span><span>mysql_fetch_array</span><span>(</span><span>$result</span><span>);</span><span>
$idmax</span><span>=</span><span>$yi</span><span>[</span><span>0</span><span>];</span><span>
$idmin</span><span>=</span><span>$yi</span><span>[</span><span>1</span><span>];</span><span>
$idlist</span><span>=</span><span>''</span><span>;</span><span>for</span><span>(</span><span>$i</span><span>=</span><span>1</span><span>;</span><span>$i</span><span><span>20</span><span>;</span><span>$i</span><span>++){</span><span>if</span><span>(</span><span>$i</span><span>==</span><span>1</span><span>){</span><span> $idlist</span><span>=</span><span>mt_rand</span><span>(</span><span>$idmin</span><span>,</span><span>$idmax</span><span>);</span><span>}</span><span>else</span><span>{</span><span> $idlist</span><span>=</span><span>$idlist</span><span>.</span><span>','</span><span>.</span><span>mt_rand</span><span>(</span><span>$idmin</span><span>,</span><span>$idmax</span><span>);</span><span>}</span><span>}</span><span>
$idlist2</span><span>=</span><span>"id,"</span><span>.</span><span>$idlist</span><span>;</span><span>
$sql</span><span>=</span><span>"select * from content where id in ($idlist) order by field($idlist2) LIMIT 0,12"</span><span>;</span><span>
$result</span><span>=</span><span>mysql_query</span><span>(</span><span>$sql</span><span>,</span><span>$conn</span><span>);</span><span>
$n</span><span>=</span><span>1</span><span>;</span><span>
$rnds</span><span>=</span><span>''</span><span>;</span><span>while</span><span>(</span><span>$row</span><span>=</span><span>mysql_fetch_array</span><span>(</span><span>$result</span><span>)){</span><span>
$rnds</span><span>=</span><span>$rnds</span><span>.</span><span>$n</span><span>.</span><span>". <a href="show%22</span><span>.</span><span>%24row</span><span>%5B</span><span>" id><span>].</span><span>"-"</span><span>.</span><span>strtolower</span><span>(</span><span>trim</span><span>(</span><span>$row</span><span>[</span><span>'title'</span><span>])).</span><span>"'>"</span><span>.</span><span>$row</span><span>[</span><span>'title'</span><span>].</span><span>"</span></a><br>\n"</span><span>;</span><span>
$n</span><span>++;</span><span>}</span></span></code>3万条数据查12条随机记录，需要0.001秒，效率比第二种方法又提升了4倍左右，比第一种方法提升120倍。注，这里使用了 order by
field($idlist2) 是为了不排序，否则 IN 是自动会排序的。缺点：有可能遇到ID被删除的情况，所以需要多选几个ID。
测试方法：
<code><span>$t </span><span>=</span><span> microtime</span><span>(</span><span>true</span><span>);</span><span>//执行语句</span><span> echo microtime</span><span>(</span><span>true</span><span>)</span><span>-</span><span> $t</span><span>;</span></code>
参考：
http://blog.csdn.net/zxl315/article/details/2435368
http://jan.kneschke.de/projects/mysql/order-by-rand/
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
Video Face Swap
使用我們完全免費的人工智慧換臉工具，輕鬆在任何影片中換臉！
熱門文章
熱工具
記事本++7.3.1
好用且免費的程式碼編輯器
SublimeText3漢化版
中文版，非常好用
禪工作室 13.0.1
強大的PHP整合開發環境
Dreamweaver CS6
視覺化網頁開發工具
SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)
MySQL：世界上最受歡迎的數據庫的簡介
Apr 12, 2025 am 12:18 AM
MySQL是一種開源的關係型數據庫管理系統，主要用於快速、可靠地存儲和檢索數據。其工作原理包括客戶端請求、查詢解析、執行查詢和返回結果。使用示例包括創建表、插入和查詢數據，以及高級功能如JOIN操作。常見錯誤涉及SQL語法、數據類型和權限問題，優化建議包括使用索引、優化查詢和分錶分區。
apache怎麼連接數據庫
Apr 13, 2025 pm 01:03 PM
Apache 連接數據庫需要以下步驟：安裝數據庫驅動程序。配置 web.xml 文件以創建連接池。創建 JDBC 數據源，指定連接設置。從 Java 代碼中使用 JDBC API 訪問數據庫，包括獲取連接、創建語句、綁定參數、執行查詢或更新以及處理結果。
MySQL的位置：數據庫和編程
Apr 13, 2025 am 12:18 AM
MySQL在數據庫和編程中的地位非常重要，它是一個開源的關係型數據庫管理系統，廣泛應用於各種應用場景。 1）MySQL提供高效的數據存儲、組織和檢索功能，支持Web、移動和企業級系統。 2）它使用客戶端-服務器架構，支持多種存儲引擎和索引優化。 3）基本用法包括創建表和插入數據，高級用法涉及多表JOIN和復雜查詢。 4）常見問題如SQL語法錯誤和性能問題可以通過EXPLAIN命令和慢查詢日誌調試。 5）性能優化方法包括合理使用索引、優化查詢和使用緩存，最佳實踐包括使用事務和PreparedStatemen
為什麼要使用mysql？利益和優勢
Apr 12, 2025 am 12:17 AM
選擇MySQL的原因是其性能、可靠性、易用性和社區支持。 1.MySQL提供高效的數據存儲和檢索功能，支持多種數據類型和高級查詢操作。 2.採用客戶端-服務器架構和多種存儲引擎，支持事務和查詢優化。 3.易於使用，支持多種操作系統和編程語言。 4.擁有強大的社區支持，提供豐富的資源和解決方案。
MySQL的角色：Web應用程序中的數據庫
Apr 17, 2025 am 12:23 AM
MySQL在Web應用中的主要作用是存儲和管理數據。 1.MySQL高效處理用戶信息、產品目錄和交易記錄等數據。 2.通過SQL查詢，開發者能從數據庫提取信息生成動態內容。 3.MySQL基於客戶端-服務器模型工作，確保查詢速度可接受。
docker怎麼啟動mysql
Apr 15, 2025 pm 12:09 PM
在 Docker 中啟動 MySQL 的過程包含以下步驟：拉取 MySQL 鏡像創建並啟動容器，設置根用戶密碼並映射端口驗證連接創建數據庫和用戶授予對數據庫的所有權限
laravel入門實例
Apr 18, 2025 pm 12:45 PM
Laravel 是一款 PHP 框架，用於輕鬆構建 Web 應用程序。它提供一系列強大的功能，包括：安裝： 使用 Composer 全局安裝 Laravel CLI，並在項目目錄中創建應用程序。路由： 在 routes/web.php 中定義 URL 和處理函數之間的關係。視圖： 在 resources/views 中創建視圖以呈現應用程序的界面。數據庫集成： 提供與 MySQL 等數據庫的開箱即用集成，並使用遷移來創建和修改表。模型和控制器： 模型表示數據庫實體，控制器處理 HTTP 請求。
解決數據庫連接問題：使用minii/db庫的實際案例
Apr 18, 2025 am 07:09 AM
在開發一個小型應用時，我遇到了一個棘手的問題：需要快速集成一個輕量級的數據庫操作庫。嘗試了多個庫後，我發現它們要么功能過多，要么兼容性不佳。最終，我找到了minii/db，這是一個基於Yii2的簡化版本，完美地解決了我的問題。
