mysql随机查询效率优化-mysql教程-PHP中文网

首页

数据库

mysql教程

mysql随机查询效率优化

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:34 PM

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/

声明

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

您可以使用哪些工具来监视MySQL性能？Apr 23, 2025 am 12:21 AM

如何有效监控MySQL性能？使用mysqladmin、SHOWGLOBALSTATUS、PerconaMonitoringandManagement(PMM)和MySQLEnterpriseMonitor等工具。1.使用mysqladmin查看连接数。2.用SHOWGLOBALSTATUS查看查询数。3.PMM提供详细性能数据和图形化界面。4.MySQLEnterpriseMonitor提供丰富的监控功能和报警机制。

MySQL与SQL Server有何不同？Apr 23, 2025 am 12:20 AM

MySQL和SQLServer的区别在于：1)MySQL是开源的，适用于Web和嵌入式系统，2)SQLServer是微软的商业产品，适用于企业级应用。两者在存储引擎、性能优化和应用场景上有显着差异，选择时需考虑项目规模和未来扩展性。

在哪些情况下，您可以选择SQL Server而不是MySQL？Apr 23, 2025 am 12:20 AM

在需要高可用性、高级安全性和良好集成性的企业级应用场景下，应选择SQLServer而不是MySQL。1)SQLServer提供企业级功能，如高可用性和高级安全性。2)它与微软生态系统如VisualStudio和PowerBI紧密集成。3)SQLServer在性能优化方面表现出色，支持内存优化表和列存储索引。

MySQL如何处理角色集和碰撞？Apr 23, 2025 am 12:19 AM

mySqlManagesCharacterSetsetSandCollationsyutusututf-8asthEdeFault，允许ConfigurationAtdataBase，table和columnlevels，AndrequiringCarefullageLignmentToavoidMismatches.1）setDefeaultCharactersetTercharactersetEtCollacterSeteTandColletationForAdataBase.2）conformentcollecharactersettersetertersetcollatertersetcollationcollation

MySQL中有什么触发器？Apr 23, 2025 am 12:11 AM

MySQL触发器是与表相关联的自动执行的存储过程，用于在特定数据操作时执行一系列操作。1）触发器定义与作用：用于数据校验、日志记录等。2）工作原理：分为BEFORE和AFTER，支持行级触发。3）使用示例：可用于记录薪资变更或更新库存。4）调试技巧：使用SHOWTRIGGERS和SHOWCREATETRIGGER命令。5）性能优化：避免复杂操作，使用索引，管理事务。

您如何在MySQL中创建和管理用户帐户？Apr 22, 2025 pm 06:05 PM

在MySQL中创建和管理用户账户的步骤如下：1.创建用户：使用CREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password';2.分配权限：使用GRANTSELECT,INSERT,UPDATEONmydatabase.TO'newuser'@'localhost';3.修正权限错误：使用REVOKEALLPRIVILEGESONmydatabase.FROM'newuser'@'localhost';然后重新分配权限；4.优化权限：使用SHOWGRA

MySQL与Oracle有何不同？Apr 22, 2025 pm 05:57 PM

MySQL适合快速开发和中小型应用，Oracle适合大型企业和高可用性需求。1）MySQL开源、易用，适用于Web应用和中小型企业。2）Oracle功能强大，适合大型企业和政府机构。3）MySQL支持多种存储引擎，Oracle提供丰富的企业级功能。

与其他关系数据库相比，使用MySQL的缺点是什么？Apr 22, 2025 pm 05:49 PM

MySQL相比其他关系型数据库的劣势包括：1.性能问题：在处理大规模数据时可能遇到瓶颈，PostgreSQL在复杂查询和大数据处理上表现更优。2.扩展性：水平扩展能力不如GoogleSpanner和AmazonAurora。3.功能限制：在高级功能上不如PostgreSQL和Oracle，某些功能需要更多自定义代码和维护。

See all articles