我們?cè)撊绾卧O(shè)計(jì)數(shù)據(jù)庫(五)
最近真是忙翻天了，該是有三個(gè)月沒寫博客了。
這次的需求是在Mongo的使用中碰到的，但是我覺得把這個(gè)需求放進(jìn)傳統(tǒng)的RDBMS中更易于理解。需求是這樣的：假設(shè)你數(shù)據(jù)庫使用的是Sqlserver，有一張表，500W條數(shù)據(jù)，你要做一個(gè)隨機(jī)在表中選擇一條數(shù)據(jù)的功能。
假設(shè)本文所探討的數(shù)據(jù)結(jié)構(gòu)如圖（聚集索引在Pk上，UserName上加了非聚集索引）：
你的***反應(yīng)大概是：哎呀媽呀忒巧了，正好主鍵使用的是Int自增的，我只用生成一個(gè)隨機(jī)數(shù)，然后找這個(gè)隨機(jī)數(shù)對(duì)應(yīng)的主鍵就好了
實(shí)現(xiàn)的步驟大概是：
①返回?cái)?shù)據(jù)庫中ID的***值IdMax
②生成1到IdMax中間一個(gè)的隨機(jī)數(shù) int random = new Random().Next(1,IdMax);
③使用UserID = random作為條件查詢
④如果沒有查詢到數(shù)據(jù)，則重新生成一個(gè)隨機(jī)數(shù)，再次查找（因?yàn)槟硞€(gè)UserID的數(shù)據(jù)可能被刪除了）
這種方法簡單，暴力，但是有一個(gè)致命的問題：我這里在建表的時(shí)候?yàn)榱苏f明這種方法，所以主鍵使用的是Int，但是在大多數(shù)我所知道的生產(chǎn)環(huán)境中，其實(shí)是用Guid的。這個(gè)致命的問題會(huì)直接導(dǎo)致上面的那個(gè)方法不可用。
至于為什么大多數(shù)我所知道的生產(chǎn)環(huán)境中用Guid而不用Int，我下一篇會(huì)做出對(duì)比。
既然Int在使用Guid作為主鍵的時(shí)候不能用，那么我們就用Row_Number吧。Sqlserver必然是支持Row_Number的，貌似Oracle和MySql中也有類似概念（不確定，問同事得到了肯定答復(fù)，沒有深究）。
實(shí)現(xiàn)的步驟大概是：
①返回?cái)?shù)據(jù)庫中數(shù)據(jù)的總條數(shù)count
②生成1到count中間一個(gè)的隨機(jī)數(shù) int random = new Random().Next(1,count);
③查找Row_Number = random的那條數(shù)據(jù)
但是Row_Number有個(gè)極其不好的地方，就是查詢?cè)胶竺娴臄?shù)據(jù)越慢，越吃資源。但凡是將數(shù)據(jù)有序儲(chǔ)存的數(shù)據(jù)庫基本都有這個(gè)問題，比如說下面兩條語句：
- select * from
- (SELECT UserID,UserName,Password,Sex,City,ROW_NUMBER()OVER(ORDER BY CURRENT_TIMESTAMP) as Number
- FROM [User_db].[dbo].[Users] ) as query
- where query.Number = 20
- select * from
- (SELECT UserID,UserName,Password,Sex,City,ROW_NUMBER()OVER(ORDER BY CURRENT_TIMESTAMP) as Number
- FROM [User_db].[dbo].[Users] ) as query
- where query.Number = 5000000
***條查Row_Number=20的數(shù)據(jù)，logical reads 5.elapsed time = 58 ms.
第二條查Row_Number=5000000的數(shù)據(jù)，logical reads 90208.elapsed time = 900 ms.
可以明顯的看出，后者的邏輯讀次數(shù)多了太多，而運(yùn)行速度也慢了不少。如果這個(gè)功能比較頻繁使用，比如說這是向用戶隨機(jī)推薦好用的功能，那么這個(gè)將會(huì)成為一個(gè)性能瓶頸
有的網(wǎng)友說使用這句：
- SELECT TOP 1 * FROM Users ORDER BY NEWID()
這個(gè)運(yùn)行出來結(jié)果是正確的，但是效率卻大打折扣。比如說我查到了第1336793條數(shù)據(jù)，logical reads 90208,elapsed time = 3026 ms
查看執(zhí)行計(jì)劃，發(fā)現(xiàn)Sort占用了98%：
有沒有比Row_Number更好一點(diǎn)的方法？
答案是在表中再加一列Random列，使得數(shù)據(jù)結(jié)構(gòu)變更成這樣：
在添加數(shù)據(jù)的時(shí)候，就生成一個(gè)隨機(jī)數(shù)插入進(jìn)來。按照本篇的例子來說，一開始可以生成0到一億之間的隨機(jī)數(shù)插入。注意，要在Random上加索引。
實(shí)現(xiàn)的步驟大概是：
①插入數(shù)據(jù)的時(shí)候添加一個(gè)隨機(jī)
②生成一個(gè)隨機(jī)數(shù)，查詢 select top(1) * from Users where Random > 隨機(jī)數(shù)
③這個(gè)查詢的結(jié)果可能會(huì)有多條（但不會(huì)很多），再在這個(gè)多條數(shù)據(jù)中隨機(jī)篩選其一（使用Linq可以很方便的實(shí)現(xiàn)，不贅述）
好了，基本說完了，請(qǐng)?jiān)试S我在結(jié)尾賣個(gè)萌：聰明的讀者，開動(dòng)腦筋，您還有更好的方法么？如果有，請(qǐng)留言。
原文鏈接：http://www.cnblogs.com/CrazyJinn/archive/2013/03/19/2968769.html
