Ankündigung

Einklappen
Keine Ankündigung bisher.

Highscore-Ranking: Optimierung möglich?

Einklappen

Neue Werbung 2019

Einklappen
X
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • Highscore-Ranking: Optimierung möglich?

    ´Guten Tag,

    ich arbeite als Software-Entwickler für eine Firma die Spiele herstellt. Nebenbei kümmere ich mich auch nach bestem Wissen und Gewissen um unsere Datenbank (bin leider kein SQL-Profi, aber bisher hats gereicht). In der DB werden u.A. die Highscores unserer Spieler (mehrere Millionen) gespeichert. Und hier wird seit Wochen ein Problem immer deutlicher: Je mehr Spieler wir haben, desto langsamer wird der Zugriff auf die DB d.H. unsere Spieler müssen, wenn sie ihr Highscore-Ranking abfragen wollen, immer länger warten bis sie ihr Ergebnis bekommen.
    Mir ist aufgefallen, dass die CPU-Auslastung des Servers durch mySQL teilweise 99% beträgt.
    Ich würde hier gerne erfahren, ob das an dem SQL-Query liegt den ich zur Berechnung verwende und ob man diesen noch optimieren kann, oder ob sich unsere Firma einen neuen Server zulegen sollte.
    Hier der Query (sortiert wird nach den erreichten Scores - wenn mehrere User den selben Score haben, wird nach Upload-Datum sortiert):

    SELECT COUNT(id) rank
    FROM 'highscores'
    WHERE (score > (
    ..SELECT score
    ..FROM 'highscores'
    ..WHERE name = '$username')
    || (score = (
    ....SELECT score
    ....FROM 'highscores'
    ....WHERE name = '$username')
    ....&& upload_date < (
    ....SELECT upload_date
    ....FROM 'highscores'
    ....WHERE name = '$username')
    ..)
    );

    Hinweis:
    Indizes für score & upload_date sind gesetzt!
    Tabellenformat ist MyISAM.
    Zugriff auf die DB erfolgt über ein PHP-Skript.

    Ich hoffe ein versierter SQL-User kann mir einen Rat geben, was ich tun kann, damit unser Server nich ganz in die Knie geht.

    Eine Idee wäre, eine weitere Tabelle anzulegen, in der die User bereits ihrem Ranking nach gespeichert sind.
    Vorteil: schnelle Abfrage des Rankings eines Users.
    Nachteil: wenn ein User sich in seinem Ranking verändert, muss im Worst-Case der Inhalt der komplette Tabelle umsortiert werden.

    Vielleicht kann mir auch jemand seine eigene Erfahrung mitteilen, der dieses Idee bereits umgesetzt hat. Ich habe nämlich die Befürchtung, auch wenn deutlich mehr User eine Rankingabfrage machen als ihre Highscores hochzuladen, dass die Zeit, die ein updaten der Tabelle benötigt, mit steigender Userzahl ebenfalls deutlich steigen wird und wir somit nichts von der Umstellung haben.

    Bin für jeden Rat dankbar!


  • #2
    Zitat von oplagon Beitrag anzeigen
    SELECT COUNT(id) rank
    FROM 'highscores'
    WHERE (score > (
    ..SELECT score
    ..FROM 'highscores'
    ..WHERE name = '$username')
    || (score = (
    ....SELECT score
    ....FROM 'highscores'
    ....WHERE name = '$username')
    ....&& upload_date < (
    ....SELECT upload_date
    ....FROM 'highscores'
    ....WHERE name = '$username')
    ..)
    );
    Zwei Dinge wären noch wichtig:

    a) den SQL-Befehl ob nochmal mit "CODE" Tags ausgeben, dann ist er besser zu lesen.

    b) Einen EXPLAIN mit dem SQL ausführen und das Ergebnis als formatieren Text oder als Bild liefern. Sonst läßt sich der EXPLAIN schwer entziffern.
    Mit dem EXPLAIN läßt sich gut erkennen, wie MySQL "einfach" die Daten findet und ob Indices genutzt werden.

    Code:
    EXPLAIN SELECT ...
    Grüße
    Thomas

    Kommentar


    • #3
      Diese Mehrfachverschachtelung von SELECT's drückt auf die Performance - für jedes äussere Ergebniss werden die inneren SELECT's erneut durchgeführt...

      Je nach Verteilung der Daten läuft die Abfrage entweder schneller oder langsamer (je mehr 'äußere' Datensätze geliefert werden, desto schlechter wird das Laufzeitverhalten)

      Bsp.:
      5 äussere, 100 innere Ergebnisse (5 mal wird eine Query ausgeführt, die 100 Datensätze als Ergebniss liefert)
      100 äussere, 5 innere Ergebnisse (100 mal wird eine Query ausgeführt, die 5 Datensätze als Ergebniss liefert)
      Über 90% aller Gewaltverbrechen passieren innerhalb von 24 Stunden nach dem Konsum von Brot.

      Kommentar


      • #4
        Ich habe mal schnell den SQL formatiert, da sieht man erst die ganze Komplexität.

        Code:
        SELECT COUNT(id) rank
        FROM   highscores
        WHERE  ( score > (SELECT score
                          FROM   highscores
                          WHERE  name = '$username')
                          || ( score = (SELECT score
                                        FROM   highscores
                                        WHERE  name = '$username') && upload_date < (SELECT upload_date
                                                                                     FROM   highscores
                                                                                     WHERE  name = '$username') ) );
        Als zusätzlichen Index würde ich mal folgendes probieren:

        Code:
        CREATE INDEX sx_highscores_01 on highscores ( name, score, upload_date);
        Grüße
        Thomas

        Kommentar


        • #5
          Die nested Subselects sehen ja richtig böse aus Vor allem ist er immer identisch.

          Ich würde zwei Abfragen draus machen. Zuerst score und upload_date des Benutzers ermitteln und diese Werte dann in die zweite Abfrage einsetzen. Das sollte bedeutend sportlicher laufen.

          PS: Soll eigentlich nur von einem User das Ranking ermittelt werden? Nicht, dass du o.g. Query noch in einer Schleife aufrufst

          Kommentar


          • #6
            Zitat von hpf Beitrag anzeigen
            Die nested Subselects sehen ja richtig böse aus Vor allem ist er immer identisch.

            Ich würde zwei Abfragen draus machen. Zuerst score und upload_date des Benutzers ermitteln und diese Werte dann in die zweite Abfrage einsetzen. Das sollte bedeutend sportlicher laufen.

            PS: Soll eigentlich nur von einem User das Ranking ermittelt werden? Nicht, dass du o.g. Query noch in einer Schleife aufrufst
            Danke für die Antwort.
            2 Querys draus zu machen, hat ein paar Millisekunden gebracht. Schon ma besser als nüscht

            Und nein, es wird nur der eigene Rang des Spielers so ermittelt. Nix mit Schleife und so. Um die TOP 100 rauszusuchen, wird von mir folgendes verwendet:
            Code:
            SELECT username, score
            FROM `highscores`
            ORDER BY score DESC, upload_Date ASC 
            LIMIT 100

            Kommentar


            • #7
              Wie schaut denn die zweite Query jetzt aus?

              Code:
              select count(*) from highscores where score < ?  or (score = ? and upload_date < ?)
              Sollte bei richtigen Indizes doch gar nicht so langsam sein, eigentlich. Wobei das natürlich drauf ankommt wie score ausschaut. Also wie groß der Wertebereich ist. Je kleiner der Bereich ist desto weniger nützt der Index. Es gibt sogar Situationen, in denen ein Index die Query langsamer macht.

              Prinzipiell wird es allerdings immer recht zäh sobald man mehrere Millionen Datensätze hat.

              Kommentar


              • #8
                Erster Query:
                Code:
                SELECT score, upload_date
                FROM highscores
                WHERE name = '$username'
                Die Ergenbisse trage ich dann in den zweiten Query ein:

                Code:
                SELECT COUNT(id) rank
                FROM highscores
                WHERE (score > $score) ||
                          (score = $score && upload_date < '$upload_date')
                Außerdem hats noch einiges gebracht, dass ich in meinen Sub-Querys nicht erneut den Usernamen vergleiche. String-Vergleiche drücken auch auf die Performance habe ich mir sagen lassen.
                Im großen und ganzen steht unser Server nun nicht mehr ganz so unter Strom wie vorher (teilweise konnten Useranfragen garnicht mehr beantwortet werden, weil der Server ausgelastet war). Hat also einiges gebracht.

                Vielen Dank an alle die sich die Zeit genommen haben hier etwas zu posten.

                Kommentar

                Lädt...
                X