從MSSQL(SQLSERVER)/MYSQL數(shù)據(jù)庫中隨機(jī)取一條或者多條記錄

tmtony

從MSSQL(SQLSERVER)/MYSQL數(shù)據(jù)庫中隨機(jī)取一條或者多條記錄

很多人都知道使用rand()函數(shù)但是怎麼使用可能不是每個(gè)人都知道

摘自樺仔 博文

建立測試表

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

第一種寫法：大家會(huì)想到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出來的結(jié)果都是一樣的，都是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這個(gè)表做比較，而且每次能夠達(dá)到隨機(jī)取一條或者N條記錄的效果

每次select出來的行數(shù)都是不一樣的

tmtony

比較一下IO和時(shí)間

當(dāng)兩種寫法select出來的結(jié)果條數(shù)都是50條的時(shí)候，時(shí)間和IO都是一樣的，如果第二種寫法select出來的記錄條數(shù)不是50條

那么IO肯定比第一種寫法要少

--第一種寫法  select出來50條記錄SQL Server 執(zhí)行時(shí)間:   CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 0 毫秒。(50 行受影響)表 'RANDTEST'。掃描計(jì)數(shù) 1，邏輯讀取 5 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。SQL Server 執(zhí)行時(shí)間:   CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 1 毫秒。--------------------------------------------------------------------------------第二種寫法  select出來37條記錄SQL Server 執(zhí)行時(shí)間:   CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 0 毫秒。(27 行受影響)表 'RANDTEST'。掃描計(jì)數(shù) 1，邏輯讀取 4 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。SQL Server 執(zhí)行時(shí)間:   CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 1 毫秒。

---

總結(jié)

如果第一種寫法寫成下面的樣子，那么每次select出來的結(jié)果都是一樣的，而且不會(huì)進(jìn)行排序，在執(zhí)行計(jì)劃里面你看不到排序這個(gè)運(yùn)算符

因?yàn)榉蔷奂�索引是排好序的，掃描非聚集索引只�?huì)得到排好序的結(jié)果

SELECT TOP 50 [id  FROM    [dbo.[RANDTESTGROUP BY IDORDER BY RAND()*100

tmtony

綜上，想從SQLSERVER數(shù)據(jù)庫中隨機(jī)取一條或者N條記錄時(shí)，最好把RAND()生成隨機(jī)數(shù)放在JOIN子查詢中以提高效率。

SELECT TOP n [id  FROM    tableGROUP BY IDORDER BY NEWID()

改造成下面這個(gè)：

SELECT TOP n   [t1.[ID  FROM table  t1 JOIN (SELECT RAND()*100 AS nid) t2 ON [t1.[ID>[t2.[nidGROUP BY [t1.[ID

就可以享受在SQL中直接取得隨機(jī)數(shù)了，不用再在程序中構(gòu)造一串隨機(jī)數(shù)去檢索了。

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;
-- 改造成下面這個(gè)：


SELECT id FROM t_innodb_random t1 JOIN (SELECT RAND()*10 AS nid) t2 ON t1.id > t2.nid LIMIT 5;

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