mysql使用rand随机查询记录效率测试

一直以为mysql随机查询几条数据,就用
SELECT * FROM `table` ORDER BY RAND() LIMIT 5

就可以了。
但是真正测试一下才发现这样效率非常低。一个15万余条的库,查询5条数据,居然要8秒以上

查看官方手册,也说rand()放在ORDER BY 子句中会被执行多次,自然效率及很低。
You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.


搜索Google,网上基本上都是查询max(id) * rand()来随机获取数据。
SELECT *
FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id ASC LIMIT 5;


但是这样会产生连续的5条记录。解决办法只能是每次查询一条,查询5次。即便如此也值得,因为15万条的表,查询只需要0.01秒不到。

上面的语句采用的是JOIN,mysql的论坛上有人使用
SELECT *
FROM `table`
WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` )
ORDER BY id LIMIT 1;


我测试了一下,需要0.5秒,速度也不错,但是跟上面的语句还是有很大差距。总觉有什么地方不正常。

于是我把语句改写了一下。
SELECT * FROM `table`
WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `table`)))
ORDER BY id LIMIT 1;

这下,效率又提高了,查询时间只有0.01秒

最后,再把语句完善一下,加上MIN(id)的判断。我在最开始测试的时候,就是因为没有加上MIN(id)的判断,结果有一半的时间总是查询到表中的前面几行。
完整查询语句是:
SELECT * FROM `table`
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`))) ORDER BY id LIMIT 1;

SELECT *
FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;


最后在php中对这两个语句进行分别查询10次,
前者花费时间 0.147433 秒
后者花费时间 0.015130 秒
看来采用JOIN的语法比直接在WHERE中使用函数效率还要高很多。

本文章非原创mc
### 关于 MySQL 测试数据生成 #### 使用 Datagenerator 创建测试数据 Datagenerator 是一款基于图形用户界面的工具,能够依据设定好的规则来批量生产用于不同数据库系统的测试资料集。对于希望在多个平台间移植应用程序或者需要跨多种环境进行开发工作的团队来说尤其有用。该软件兼容众多关系型数据库管理系统,其中包括 MySQL,在这些平台上均可实现高效的数据填充操作[^1]。 #### 利用专门脚本快速构建大规模数据集合 针对特定需求场景下的大数据量模拟任务,则可以通过编写定制化的 SQL 脚本来完成。例如,在一次实践中展示了一个高效的方案用来向 MySQL 数据库中插入一百万条记录仅需耗费约二十余秒钟时间。此过程涉及到了对目标表格结构的理解以及合理设计插入语句以确保性能最优[^2]。 ```sql -- 假设有一个名为 game_characters 的表存储游戏角色信息 INSERT INTO game_characters (name, level, experience) SELECT CONCAT('Character_', FLOOR(RAND() * 90000 + 10000)), -- 随机名称 FLOOR(RAND() * 80), -- 随机等级范围0到79 ROUND((RAND() * POW(10, 6))) -- 经验值取整数部分 FROM dual CROSS JOIN ( SELECT a.N + b.N * 10 AS num FROM( SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 )AS a, ( SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 )AS b ) numbers LIMIT 1000000; ``` 这段SQL代码展示了如何通过交叉连接两个数值序列并限制最终结果的数量来一次性插入大量随机生成的游戏角色数据。 #### 应用 Navicat 工具简化工作流程 除了上述两种方式外,还有像 Navicat 这样的集成化解决方案可供选择。这类应用不仅提供了直观易懂的操作界面帮助开发者轻松定义各种复杂条件下的字段属性及其分布规律;而且支持同时处理多张关联表之间的同步更新动作,从而大大减少了手动编码所带来的错误风险,并提高了整体工作效率[^3]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值