从SQLSERVER/MYSQL数据库中随机取一条或者N条记录
从SQLSERVER/MYSQL数据库中随机取一条或者N条记录
很多人都知道使用rand()函数但是怎麽使用可能不是每个人都知道
建立测试表
USE [sss] GO CREATE TABLE RANDTEST(ID INT DEFAULT RAND()*100,NAME NVARCHAR(200) DEFAULT 'nihao') GO CREATE INDEX IX_RANDTEST_ID ON RANDTEST(ID) GO INSERT INTO RANDTEST DEFAULT VALUES GO 2000 SELECT * FROM RANDTEST
第一种写法:大家会想到ORDER BY NEWID()
SET STATISTICS TIME ON SET STATISTICS IO ON SELECT TOP 50 [id] FROM [dbo].[RANDTEST] GROUP BY ID ORDER BY NEWID() SET STATISTICS TIME OFF SET STATISTICS IO OFF
这种写法使用到索引扫描,而且每次select出来的结果都是一样的,都是50条记录
第二种写法:
SET STATISTICS TIME ON SET STATISTICS IO ON SELECT TOP 50 [t1].[ID] FROM [dbo].[RANDTEST] t1 INNER JOIN (SELECT RAND()*100 AS nid) t2 ON [t1].[ID]>[t2].[nid] GROUP BY [t1].[ID] SET STATISTICS TIME OFF SET STATISTICS IO OFF
跟t2这个表做比较,而且每次能够达到随机取一条或者N条记录的效果
每次select出来的行数都是不一样的
比较一下IO和时间
当两种写法select出来的结果条数都是50条的时候,时间和IO都是一样的,如果第二种写法select出来的记录条数不是50条
那么IO肯定比第一种写法要少
--第一种写法 select出来50条记录 SQL Server 执行时间: CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。 (50 行受影响) 表 'RANDTEST'。扫描计数 1,逻辑读取 5 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。 SQL Server 执行时间: CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。 ------------------------------------------------------------------------------ --第二种写法 select出来37条记录 SQL Server 执行时间: CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。 (27 行受影响) 表 'RANDTEST'。扫描计数 1,逻辑读取 4 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。 SQL Server 执行时间: CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
总结
如果第一种写法写成下面的样子,那么每次select出来的结果都是一样的,而且不会进行排序,在执行计划里面你看不到排序这个运算符
因为非聚集索引是排好序的,扫描非聚集索引只会得到排好序的结果
SELECT TOP 50 [id] FROM [dbo].[RANDTEST] GROUP BY ID ORDER BY RAND()*100
综上,想从SQLSERVER数据库中随机取一条或者N条记录时,最好把RAND()生成随机数放在JOIN子查询中以提高效率。
SELECT TOP n [id] FROM table GROUP BY ID ORDER BY NEWID()
改造成下面这个:
SELECT TOP n [t1].[ID] FROM table t1 JOIN (SELECT RAND()*100 AS nid) t2 ON [t1].[ID]>[t2].[nid] GROUP BY [t1].[ID]
就可以享受在SQL中直接取得随机数了,不用再在程序中构造一串随机数去检索了。
MYSQL也是同样的原理
CREATE TABLE `t_innodb_random` ( `id` INT(10) UNSIGNED NOT NULL, `user` VARCHAR(64) NOT NULL DEFAULT '', KEY `idx_id` (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO `t_innodb_random` (`id`, `user`) VALUES('1','lily'); INSERT INTO `t_innodb_random` (`id`, `user`) VALUES('3','tom'); INSERT INTO `t_innodb_random` (`id`, `user`) VALUES('5','fancy'); INSERT INTO `t_innodb_random` (`id`, `user`) VALUES('6','cici'); INSERT INTO `t_innodb_random` (`id`, `user`) VALUES('9','syan'); SELECT * FROM t_innodb_random; SELECT id FROM t_innodb_random ORDER BY RAND() LIMIT 5; -- 改造成下面这个: SELECT id FROM t_innodb_random t1 INNER JOIN (SELECT RAND()*10 AS nid) t2 ON t1.id > t2.nid LIMIT 5;
---------------------------------------------------------------------------------------------
如有不对的地方,欢迎大家拍砖o(∩_∩)o
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)