具有玩家排名(及周围玩家)的快速mySQL排行榜 [英] Fast mySQL leaderboard with player rank (& surrounding players)

问题描述

我正在制作一款具有排行榜功能(mySQL/PHP)的简单游戏.

I'm making a simple game, with leaderboard functionality (mySQL/PHP).

在游戏结束时:

1. 到服务器:玩家的得分
2. 从服务器:服务器，玩家等级加5 &上方的玩家在他们下面 在排名中

1. To Server: Player's score
2. From Server: Server, player's rank PLUS 5 players directly above & below them in rankings

我不确定服务器将处理预期的玩家流量-所以我想做正确的事.

I'm not particularky confident the sever will handle the expected player traffic - so I want to do this right.

以下哪种方法可行(最快)?

Which of the following approaches would work (and be fastest)?

• 更改表? (当表经常被修改时会变慢吗?如何获得排名?)
• 带有ORDER BY的简单行(等级)计数器? 示例
• 使用WHERE子句的简单行(秩)计数器更快? 示例

• Alter Table? (slow when table is often modified? how to get rank?)
• Simple row (rank) counter with ORDER BY? Example
• Simple row (rank) counter using WHERE clause faster? Example

还是我错过了更好的解决方案?

or have I missed a better solution?

推荐答案

ALTER TABLE
ALTER TABLE是用于更改表的结构.
当您犯了一个错误或改变了做事的方式时，可以使用它.
如果您不知道它有什么用，请不要使用它.

ALTER TABLE
ALTER TABLE is to change the structure of the table.
You use it when you've made a mistake or you've changed your mind on how to do things.
If you dunno what it does, don't use it.

INDEX
INDEX可以使排序更快，总是分配索引，以便为您在WHERE子句或ORDER BY子句中经常使用的列分配索引.
您可以排序并选择合适的没有索引，只是速度较慢.

INDEX
An INDEX is to make sorting faster, always assign an index to columns that you use regularly in a WHERE clause or in an ORDER BY clause.
You can sort and select just fine without indexes, just slower.

排名
如果您想将排名最高的球员列在首位，那么:

RANKING
If you want to list highest ranking players on top then:

SELECT field1, field2, ... FROM players ORDER BY score DESC LIMIT 25  

将给您排名前25位的高分玩家，得分最高的是(排名从高到低按相反的顺序 DESC

Will give you the top 25 high score players, highest score first (it sorts in reverse order DESC from high to low)

幻想排名

SELECT @rank:= 0; -- <<-- First run this query!

SELECT @rank:= @rank + 1 as rank, s.* FROM (
  SELECT field1, field2, ... FROM players ORDER BY score DESC LIMIT 25  
) s; --<<-- than this one.

哦，继续阅读有关SQL的基本知识.
Google for SQL tutorial

Oh and read up on basic SQL stuff.
Google for SQL tutorial

祝你好运.

这篇关于具有玩家排名(及周围玩家)的快速mySQL排行榜的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！