|
|
很多网游中都有排行榜，这里就专门讨论一下这个排行榜背后的数据库设计。一开始我觉得这是一个基本的数据库设计问题。只需要有一个实体，没有实体间的关系，没有复杂的逻辑。网络上也搜索不到太多关于这类设计的问题，好像根本不值得为其写个文章。但是在公司专门做了一个月的排行榜数据库设计。才发现问题根本没有看上去那么简单。甚至一篇文章都难以讲明白。不知自己误入歧途了，还是这个问题的确就是很复杂的。所以写个文章讲给大家，或许能有人一语道破。
一开始听到要设计一个排行榜，觉得很简单，一个外键加一个分数列，排名不保存在数据库中，每次查询都实时计算。不就得了？
接下来，就来讨论一下这种方案的可行性。先来描述一下经过最简化的基本要求：
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技术：排行榜数据库设计与分析——为什么实时排行不可行?，转载需保留来源！
郑重声明：本文版权归原作者所有，转载文章仅为传播更多信息之目的，如作者信息标记有误，请第一时间联系我们修改或删除，多谢。
