Bonjour,
je viens à vous pour un besoin simple (au moins dans sa définition):
- j'ai un jeu (amateur) dans lequel à chaque partie jouée le joueur gagne ou perd des points, ce qui fait évoluer son score global. A partir du score on peut établir un classement de l'ensemble des joueurs que le joueur peut parcourir à sa guise dans le jeu (le plus haut score est le premier, ...).
- on a divers classements pour diverses périodes (annuel, mensuel, hebdomadaire, ...) ; chaque classement ne prend en compte que les parties jouées pendant la période concernée.
- question volumétrie, le jeu rassemble quelques 150 000 joueurs inscrits et une partie est jouée en moyenne toutes les 1.5 secondes (donc les scores des joueurs et donc les classements évoluent à cette fréquence aussi).
Voilà pour la présentation. Mon principal souci est qu'en l'état actuel des choses, faire des requêtes sur la base mange pas mal de ressources au serveur (calculer le rang d'un joueur, récupérer la liste des joueurs du rang (xxx) à (xxx + 10) pour les afficher, etc...) ; j'aimerais trouver une solution qui soit la plus économe possible.
Concernant la structure (simplifiée) de ma base, on a:
- une table 'profil' qui rassemble les caractéristiques du joueur (id, pseudo, avatar, ...).
ID_PROFILE bigint(20)
PSEUDO varchar(25)
AVATAR smallint(6)
PRIMARY: (ID_PROFILE)
INDEXES: aucun (en dehors du primary)
- plusieurs tables, une pour chaque type de période de classement: charts_yearly, charts_monthly, charts_weekly, ...
PERIOD int(11)
ID_PROFILE bigint(20)
SCORE int(11)
PRIMARY: (PERIOD, ID_PROFILE)
INDEXES: (PERIOD, ID_PROFILE, SCORE), (SCORE)
Le champ 'PERIOD' est un identifiant pour la période concernée. Par exemple dans charts_monthly, PERIOD vaut (année * 100 + mois), donc un enregistrement avec PERIOD = 201012 représente le classement pour le joueur PROFILE pour ses parties jouées en décembre 2010.
On a donc 0 ou 1 enregistrement pour chaque couple (PERIOD, ID_PROFILE) ; zéro si le joueur n'a joué aucune partie pendant la période donnée.
Le champ 'SCORE' est le cumul des points gagnés/perdus à chaque partie.
Les requêtes qui sont faites sur la base:
(Les valeurs entre [ ] sont les valeurs injectées dans la requête en fonction du contexte)
A la fin d'une partie, pour créer/mettre à jour un classement (exemple ici, le classement annuel ; il y a autant de requêtes faites que de types de classement (annuel, mensuel, hebdomadaire, ...):
Récupérer le rang d'un joueur:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5 INSERT INTO charts_yearly (PERIOD, ID_PROFILE, SCORE) VALUES ( (YEAR(NOW())), [profile_id], [scorePartie] ) ON DUPLICATE KEY UPDATE SCORE=SCORE + [scorePartie];
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7 SELECT count(*) AS RANK FROM charts_yearly WHERE PERIOD = [period] AND (SCORE < [playerScore] OR (SCORE = [playerScore] AND ID_PROFILE < [playerProfileId] )
Récupérer les joueurs classés entre le rang (begin) et le rang (begin+length):
Enfin pour récupérer le classement autour d'un joueur donné, je fais d'abord la requête n°2 pour récupérer son rang puis la requête n°3 pour récupérer les joueurs aux rangs [rang - 5, rang + 5]
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8 SET @RANK:=[begin]-1; SELECT @RANK:=@RANK+1 AS RANK, c.*, p.* FROM charts_yearly c, profiles p WHERE c.PERIOD = [period] AND c.ID_PROFILE = p.ID_PROFILE ORDER BY c.SCORE DESC, c.ID_PROFILE ASC LIMIT [begin], [length]
Voilà.
Après un premier test sur mon laptop une base avec 150 000 profils et 300 000 entrées dans charts_yearly (deux entrées par profil pour 2010 et 2011), je mets près de 600ms lorsque je veux récupérer les 10 joueurs classés du 100 000 ème au 100 010 ème (cf. la dernière requête).
Cette dernière requête semble peu affectée par le nombre d'enregistrements dans la table (j'obtiens les même temps avec charts_weekly qui a 7 millions d'enregistrements), mais beaucoup plus par les valeurs de LIMIT: il est beaucoup plus lent de récupérer les rangs [100 000, 100 010] que les rangs [100, 110].
EDIT: avec PhpMyAdmin, un profiling de la requête donne ceci:
Et encore, c'est ce que j'obtiens sur mon laptop (un core-i5 + SSD) ; le serveur de prod actuel étant un modeste Pentium Dual Core E5200 AVEC 2Go de RAM.
Sachant que les joueurs consultent très régulièrement le classement mais que ce n'est pas la seule chose que le serveur ait à gérer (loin de là), vous comprenez aisément mon souci.
Si vous aviez des conseils avisés, je serais évidemment preneur
PS: Pour info le principal soft qui utilise le serveur MySQL est le serveur de jeu (en Java, via JDBC) ; il y a une connexion distincte pour chaque 'fonctionnalité' (gestion des utilisateurs + authentification, gestion des classements, ...) ; à terme, un site web devra pouvoir également fournir ces infos.
Partager