我們該如何設計資料庫(五)

最近真是忙翻天了，該是有三個月沒寫部落格了。

這次的需求是在Mongo的使用中碰到的，但是我覺得把這個需求放進傳統的RDBMS中更易於理解。需求是這樣的：假設你資料庫使用的是Sqlserver，有一張表，500W條資料，你要做一個隨機在表中選擇一條資料的功能。

假設本文所探討的資料結構叢集索引在Pk上，UserName上加了非叢集索引）：

你的第一反應大概是：哎呀媽呀忒巧了，正好主鍵使用的是Int自增的，我只用產生一個隨機數，然後找這個隨機數對應的主鍵就好了

實現的步驟大概是：

①返回資料庫中ID的最大值IdMax 

②產生1到IdMax中間一個的隨機數 int random = new Random().Next(1,IdMax);

③使用UserID = random作為條件查詢

④如果沒有查詢到資料，則重建一個隨機數，再次尋找因為某個UserID的資料可能被刪除了）

這種方法簡單，暴力，但是有一個致命的問題：我這裡在建表的時候為了說明這種方法，所以主鍵使用的是Int，但是在大多數我所知道的生產環境中，其實是用Guid的。這個致命的問題會直接導致上面的那個方法不可用。

至於為什麼大多數我所知道的生產環境中用Guid而不用Int，我下一篇會做出對比。

既然Int在使用Guid作為主鍵的時候不能用，那麼我們就用Row_Number吧。Sqlserver必然是支援Row_Number的，貌似Oracle和MySql中也有類似概念不確定，問同事得到了肯定回覆，沒有深究）。

實現的步驟大概是：

①返回資料庫中資料的總條數count

②產生1到count中間一個的隨機數 int random = new Random().Next(1,count);

③尋找Row_Number = random的那條資料

但是Row_Number有個極其不好的地方，就是查詢越後面的資料越慢，越吃資源。但凡是將資料有序儲存的資料庫基本都有這個問題，比如說下面兩條語句：

 

  
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的資料，logical reads 5.elapsed time = 58 ms.

第二條查Row_Number=5000000的資料，logical reads 90208.elapsed time = 900 ms.

可以明顯的看出，後者的邏輯讀次數多了太多，而運行速度也慢了不少。如果這個功能比較頻繁使用，比如說這是向使用者隨機推薦好用的功能，那麼這個將會成為一個效能瓶頸

有的網友說使用這句：

 

  
SELECT TOP 1 * FROM Users ORDER BY NEWID()  
 

這個運行出來結果是正確的，但是效率卻大打折扣。比如說我查到了第1336793條資料，logical reads 90208,elapsed time = 3026 ms

查看執行計畫，發現Sort佔用了98%：

有沒有比Row_Number更好一點的方法？

答案是在表中再加一列Random列，使得資料結構變更成這樣：

在添加資料的時候，就產生一個隨機數插入進來。按照本篇的例子來說，一開始可以產生0到一億之間的隨機數插入。注意，要在Random上加索引。

實現的步驟大概是：

①插入資料的時候添加一個隨機

②產生一個隨機數，查詢 select top(1) * from Users where Random > 隨機數

③這個查詢的結果可能會有多條但不會很多），再在這個多條資料中隨機篩選其一使用Linq可以很方便的實現，不贅述）

好了，基本說完了，請允許我在結尾賣個萌：聰明的讀者，開動腦筋，您還有更好的方法嗎？如果有，請留言。