|
|
很多網游中都有排行榜，這里就專門討論一下這個排行榜背后的數據庫設計。一開始我覺得這是一個基本的數據庫設計問題。只需要有一個實體，沒有實體間的關系，沒有復雜的邏輯。網絡上也搜索不到太多關于這類設計的問題，好像根本不值得為其寫個文章。但是在公司專門做了一個月的排行榜數據庫設計。才發現問題根本沒有看上去那么簡單。甚至一篇文章都難以講明白。不知自己誤入歧途了，還是這個問題的確就是很復雜的。所以寫個文章講給大家，或許能有人一語道破。
一開始聽到要設計一個排行榜，覺得很簡單，一個外鍵加一個分數列，排名不保存在數據庫中，每次查詢都實時計算。不就得了？
接下來，就來討論一下這種方案的可行性。先來描述一下經過最簡化的基本要求：
1. 參與排行的設計用戶量為1000萬左右。
2. 并不要求實時，一小時更新一次。（我一開始的想法很天真，實時不是更好？所以才試了這個實時的排行榜）
3. 排行榜的結果要正確。（最廢話的一條，其實很關鍵，直接導致實時方案作廢）
生產環境，數據庫服務器：
CPU：雙路4核，至強。
內存：32G。
開發、測試的環境：（以下運行時間數據基于此環境）
CPU：賽揚D 2.66G
內存：1G。
建表：
Create Table RealTimeCLB
(
UserId INT NOT NULL PRIMARY KEY,
Rating INT NOT NULL
)
放數據：一定要用Tran。
BEGIN TRAN
DECLARE @I as int
SET @I = 0
INSERTDATA:
INSERT RealTimeCLB VALUES(@I, RAND()*10000000)
SET @I = @I + 1
IF @I < 5000000
GOTO INSERTDATA
COMMIT TRAN
插入500萬數據就用了16分鐘，心里有點怵了。實時計算排名會不會慢呢？不管了，試試再說，反正真正的服務器很強大的說。注意Rating值是用隨機數生成的。
為Rating列加索引：
CREATE INDEX IX_RealTimeCLB_Rating ON RealTimeCLB (Rating);
加索引又用了30秒。
查詢：
SELECT TOP 100 *, RANK() OVER (ORDER BY Rating DESC) AS [rank] FROM RealTimeCLB
用時0秒。很快啊。會不會影響并發的數據更新呢？
UPDATE RealTimeCLB SET Rating = Rating + RAND() * 1000 where UserId = 2
運行沒有影響。
這里要解釋一個問題。如果查詢時，有更新操作，那查詢出來的不就是臟的了嗎？這個是可以接受了。更新晚于查詢，再正常不過了。所以這個不是個問題。
但是如果世界就這么和諧了，也就不用研究一個月了。本文只是這一個月的第一天而已。
因為查詢的方式多種多樣。上面只查了前100名，很快。但是如果隨便一個想查一下自己的名次呢？這也是必須要實現的基本功能。
查詢指定用戶的名次：
SELECT *, RANK() OVER (ORDER BY Rating DESC) AS [rank]
FROM RealTimeCLB WHERE UserId = 1
如果你看到這里沒有大叫，就說明你沒有仔細看，或者至少對SQL不熟悉。因為上面的語句永遠返回1。無論查誰，都是第1。
正確的SQL有很多寫法，下面是其中一種：
SELECT * from
(SELECT *, RANK() OVER (ORDER BY Rating DESC) AS [rank] FROM RealTimeCLB) AS d
WHERE d.UserId = 1
很不幸，這條語句用了4.5秒。如果用1000萬用戶的數據量，豈不是要10秒？如果你不知道為什么查詢自己很慢，就找本書看看索引是如何運作的吧。這里我就不解釋了。
也許我的SQL比較低效（你有快的嗎？要實時計算。）。但是QQ和MSN之類用戶已經有2億了，如果那天也要做個迅雷樣的排行榜。實時？那還了得？數據庫服務器天天別干別的了，光排個名就排不過來了。
把Rank做為一列放進表里，查詢不就快了？那更新不就慢了？更新一個人的分數，就要給一群人重新計算排名。你SQL寫得好，在500萬數據量上，也要5秒運行時間。
所以結論就是，排行榜，在大用戶量和當前硬件環境下，是不可能實時的。
如果有人說，我們數據量很小，就10萬用戶，那總可以了吧？一次查詢也就0.05秒，還可以了。聽上去是可以了。SQL Server 2005提供的Rank函數，讓按列計算排名快了很多。但是還是不行！因為上面的方法，無法保證最基本的一個需求，正確性！
可以不管查詢出來的數據是舊的，但是一定要正確啊。但是上面的方案，不能保證查詢結果的正確性！
而下面的解釋，才是本文的重點部分。
回到查詢語句
SELECT * from (SELECT *, RANK() OVER (ORDER BY Rating DESC) AS [rank] FROM RealTimeCLB) AS d WHERE d.UserId = 1
UserId是外鍵，而且用來查詢的UserId一定存在，但是就是這個語句會出問題，有看出什么問題嗎？
問題就在于，這個語句返回的行數不確定！邏輯上，一個User一個Rank，但是這個語句，可能會返回兩個或兩個以上的結果行，甚至可能沒有返回（即使UserId存在）。
出現的必要條件：
1. 在這個查詢語句正確運行時，同時有數據更新。
2. 表上的Rating列建有索引。
表上有索引，就可能有這個問題，經過測試，如果把表上的索引刪除，這個語句一定有一個返回行。
大家應該已經猜到問題的所在。在有索引的表上更新索引列，索引樹為了保持平衡，就要同時改變索引數據的位置。如果同時有基于此索引的查詢，就有可能因為索引節點在索引樹上跳來跳去而遺漏或是重復讀取一些節點。從而導致上面的問題。
解決方案1：查詢時加表鎖。既保證了正確性，又保證了時效性。但是查詢的時候，就不能更新數據了。放棄。
解決方案2：不加索引。先把索引刪除。
DROP INDEX IX_RealTimeCLB_Rating ON RealTimeCLB
那么在500萬數據量下的查詢速度如何呢？
SELECT TOP 100 *, RANK() OVER (ORDER BY Rating DESC) AS [rank] FROM RealTimeCLB
要21秒。100萬數據要4秒?；旧铣烧?。其實時間就是花在了排序上。所以運行時間基本上只和排序算法的效率相關。因為沒有了索引，所以查詢一個用戶的時間也和這個差不多。如果你說你們只有幾千用戶量，還可以試下這個方法。
解決方案3：還是別實時了~~~~~，詳見下回分解。
NET技術：排行榜數據庫設計與分析——為什么實時排行不可行?，轉載需保留來源！
鄭重聲明：本文版權歸原作者所有，轉載文章僅為傳播更多信息之目的，如作者信息標記有誤，請第一時間聯系我們修改或刪除，多謝。
