排⾏榜数据库设计与分析——为什么实时排⾏不可⾏？
很多⽹游中都有排⾏榜，这⾥就专门讨论⼀下这个排⾏榜背后的数据库设计。⼀开始我觉得这是⼀个基本的数据库设计问题。只需要有⼀个实体，没有实体间的关系，没有复杂的逻辑。⽹络上也搜索不到太多关于这类设计的问题，好像根本不值得为其写个⽂章。但是在公司专门做了⼀个⽉的排⾏榜数据库设计。才发现问题根本没有看上去那么简单。甚⾄⼀篇⽂章都难以讲明⽩。不知⾃⼰误⼊歧途了，还是这个问题的确就是很复杂的。所以写个⽂章讲给⼤家，或许能有⼈⼀语道破。
⼀开始听到要设计⼀个排⾏榜，觉得很简单，⼀个外键加⼀个分数列，排名不保存在数据库中，每次查询都实时计算。不就得了？
陈思成与佟丽娅接下来，就来讨论⼀下这种⽅案的可⾏性。先来描述⼀下经过最简化的基本要求：
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
怎么下载歌曲到mp3COMMIT 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：还是别实时了~~~~~，详见下回分解。