开发者社区> 杰克.陈> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

从SQLSERVER/MYSQL数据库中随机取一条或者N条记录

简介: 原文:从SQLSERVER/MYSQL数据库中随机取一条或者N条记录 从SQLSERVER/MYSQL数据库中随机取一条或者N条记录 很多人都知道使用rand()函数但是怎麽使用可能不是每个人都知道 建立测试表 USE [sss] GO CREATE TABLE RANDTEST(...
+关注继续查看
原文:从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 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 JOIN (SELECT RAND()*10 AS nid) t2 ON t1.id > t2.nid LIMIT 5;

 

 ---------------------------------------------------------------------------------------------

 

如有不对的地方,欢迎大家拍砖o(∩_∩)o 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Centos7修改默认网卡名
 en命名方式修改 enp8s0=`ip a |grep ether | awk 'NR==1{print $2}'` enp9s0=`ip a |grep ether | awk 'NR==2{print $2}'` sed -i 's/rhgb/rhgb net.
1068 0
Centos 更改MySQL5.7数据库目录位置
Centos7.3 安装Mysql5.7并修改初始密码 基于 CentOS Mysql 安装与主从同步配置详解 Centos 通过yum安装(RPM分发进行安装)MySQL的几个人默认目录如...
1899 0
通过Xshell连接CentOS虚拟机
1.通过"setup"配置网络ip(CentOS7不支持setup配置)2.通过“service network restart"来重启网络服务 3.“ifconfig"查看配置是否生效            刚刚完成的配置再重启后未生效,那么原因是为什么呢? 1.
1191 0
mssql数据库游标批量修改符合条件的记录
//需求:由于项目刚上传,没有票数,为了表现出一定的人气,需要在一开始把各项目的票数赋一个值 , 但每个项目不能一样,否则容易看出问题,呵呵 。 DECLARE @Id varchar(50) DECLARE My_Cursor CURSOR --定义游标 FOR (SELECT Id FROM dbo.
849 0
+关注
杰克.陈
一个安静的程序猿~
10424
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载