很多网游中都有排行榜这里就专门讨论一下这个排行榜背后的数据库设计。一开始我觉得这是一个基本的数据库设计问题。只需要有一个实体没有实体间的关系没有复杂的逻辑。网络上也搜索不到太多关于这类设计的问题好像根本不值得为其写个文章。但是在公司专门做了一个月的排行榜数据库设计。才发现问题根本没有看上去那么简单。甚至一篇文章都难以讲明白。不知自己误入歧途了还是这个问题的确就是很复杂的。所以写个文章讲给大家或许能有人一语道破。一开始听到要设计一个排行榜觉得很简单一个外键加一个分数列排名不保存在数据库中每次查询都实时计算。不就得了接下来就来讨论一下这种方案的可行性。先来描述一下经过最简化的基本要求1. 参与排行的设计用户量为1000万左右。2. 并不要求实时一小时更新一次。我一开始的想法很天真实时不是更好所以才试了这个实时的排行榜3. 排行榜的结果要正确。最废话的一条其实很关键直接导致实时方案作废。生产环境数据库服务器CPU双路4核至强。内存32G。开发、测试的环境以下运行时间数据基于此环境CPU赛扬D 2.66G内存1G。建表CreateTableRealTimeCLB(UserIdINTNOTNULLPRIMARYKEY,RatingINTNOTNULL)放数据一定要用Tran。BEGINTRANDECLAREIasintSETI0INSERTDATA:INSERTRealTimeCLBVALUES(I,RAND()*10000000)SETII1IFI5000000GOTOINSERTDATACOMMITTRAN插入500万数据就用了16分钟心里有点怵了。实时计算排名会不会慢呢不管了试试再说反正真正的服务器很强大的说。注意Rating值是用随机数生成的。为Rating列加索引CREATEINDEXIX_RealTimeCLB_RatingONRealTimeCLB(Rating);加索引又用了30秒。查询SELECTTOP100*,RANK()OVER(ORDERBYRatingDESC)AS[rank]FROMRealTimeCLB用时0秒。很快啊。会不会影响并发的数据更新呢UPDATERealTimeCLBSETRatingRatingRAND()*1000whereUserId2运行没有影响。这里要解释一个问题。如果查询时有更新操作那查询出来的不就是脏的了吗这个是可以接受了。更新晚于查询再正常不过了。所以这个不是个问题。但是如果世界就这么和谐了也就不用研究一个月了。本文只是这一个月的第一天而已。因为查询的方式多种多样。上面只查了前100名很快。但是如果随便一个想查一下自己的名次呢这也是必须要实现的基本功能。查询指定用户的名次SELECT*,RANK()OVER(ORDERBYRatingDESC)AS[rank]FROMRealTimeCLBWHEREUserId1如果你看到这里没有大叫就说明你没有仔细看或者至少对SQL不熟悉。因为上面的语句永远返回1。无论查谁都是第1。正确的SQL有很多写法下面是其中一种SELECT*from(SELECT*,RANK()OVER(ORDERBYRatingDESC)AS[rank]FROMRealTimeCLB)ASdWHEREd.UserId1很不幸这条语句用了4.5秒。如果用1000万用户的数据量岂不是要10秒如果你不知道为什么查询自己很慢就找本书看看索引是如何运作的吧。这里我就不解释了。也许我的SQL比较低效你有快的吗要实时计算。。但是QQ和MSN之类用户已经有2亿了如果那天也要做个迅雷样的排行榜。实时那还了得数据库服务器天天别干别的了光排个名就排不过来了。把Rank做为一列放进表里查询不就快了那更新不就慢了更新一个人的分数就要给一群人重新计算排名。你SQL写得好在500万数据量上也要5秒运行时间。所以结论就是排行榜在大用户量和当前硬件环境下是不可能实时的。如果有人说我们数据量很小就10万用户那总可以了吧一次查询也就0.05秒还可以了。听上去是可以了。SQL Server 2005提供的Rank函数让按列计算排名快了很多。但是还是不行因为上面的方法无法保证最基本的一个需求正确性可以不管查询出来的数据是旧的但是一定要正确啊。但是上面的方案不能保证查询结果的正确性而下面的解释才是本文的重点部分。回到查询语句SELECT*from(SELECT*,RANK()OVER(ORDERBYRatingDESC)AS[rank]FROMRealTimeCLB)ASdWHEREd.UserId1UserId是外键而且用来查询的UserId一定存在但是就是这个语句会出问题有看出什么问题吗问题就在于这个语句返回的行数不确定逻辑上一个User一个Rank但是这个语句可能会返回两个或两个以上的结果行甚至可能没有返回即使UserId存在。出现的必要条件1. 在这个查询语句正确运行时同时有数据更新。2. 表上的Rating列建有索引。表上有索引就可能有这个问题经过测试如果把表上的索引删除这个语句一定有一个返回行。大家应该已经猜到问题的所在。在有索引的表上更新索引列索引树为了保持平衡就要同时改变索引数据的位置。如果同时有基于此索引的查询就有可能因为索引节点在索引树上跳来跳去而遗漏或是重复读取一些节点。从而导致上面的问题。解决方案1查询时加表锁。既保证了正确性又保证了时效性。但是查询的时候就不能更新数据了。放弃。解决方案2不加索引。先把索引删除。DROPINDEXIX_RealTimeCLB_RatingONRealTimeCLB那么在500万数据量下的查询速度如何呢