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