Ankündigung

Einklappen
Keine Ankündigung bisher.

[Erledigt] SQL mit Subselect und Join optimieren

Einklappen

Neue Werbung 2019

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

  • [Erledigt] SQL mit Subselect und Join optimieren

    Hi,
    ich habe eine simple MySQL-Tabelle, die etwas vereinfacht wie folgt aufgebaut ist:
    Code:
    id	datumzeit		messwert
    ----------------------------------------
    0	2014-01-01 00:00:00	65
    1	2014-01-01 00:00:00	34
    2	2014-01-01 00:00:00	16
    0	2014-01-01 00:10:00	66
    1	2014-01-01 00:10:00	30
    0	2014-01-01 00:20:00	60
    1	2014-01-01 00:20:00	31
    2	2014-01-01 00:20:00	17
    0	2014-01-01 00:30:00	66
    1	2014-01-01 00:30:00	38
    Als Ergebis möchte ich eine Tabelle haben, in der zu jedem Messpunkt id die letzte Zeit und der dazugehörige Messwert enthalten ist, also für das obige Minimalbeispiel
    Code:
    0	2014-01-01 00:30:00	66
    1	2014-01-01 00:30:00	38
    2	2014-01-01 00:20:00	17
    Bisher nehme ich folgende SQL:
    Code:
    SELECT 
      t1.id, 
      t1.datumzeit, 
      t1.messwert  
    FROM
      messdat AS t1
    JOIN (
      SELECT 
        id, 
        MAX(datumzeit) AS mdz  
      FROM 
        messdat 
      GROUP BY 
        id)
      AS t2 
    ON t1.id = t2.id AND t1.datumzeit = t2.mdz
    Mein Problem ist das Zeitverhalten ab eines bestimmten Datenumfanges.
    Bei ca 500.000 Datensätzen liegt das schon im zweistelligen Sekundenbereich.
    Die Ursache liegt wohl im inneren SELECT/GROUP BY begründet.

    Bin dankbar für jeden Tipp.
    (Für Andreas: Muss gerätebedingt bei MySQL bleiben.)

    LG jspit
    PHP-Klassen auf github


  • #2
    Zitat von jspit Beitrag anzeigen
    (Für Andreas: Muss gerätebedingt bei MySQL bleiben.)

    LG jspit
    LOL. Hätte sonst DISTINCT ON() gesagt, aber so, ...
    PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

    Kommentar


    • #3
      Zeig mal CREATE TABLE
      Standards - Best Practices - AwesomePHP - Guideline für WebApps

      Kommentar


      • #4
        Und den EXPLAIN...
        Über 90% aller Gewaltverbrechen passieren innerhalb von 24 Stunden nach dem Konsum von Brot.

        Kommentar


        • #5
          A. beim schreiben direkt den/die letzten Datensatz markieren (Flag mit entsprechenden Index -> 3 stelliger Millionenbereich kein Problem, Schreiblast beachten) Damit sind auch die letzten n Datensätze möglich...

          B. wenn du eine Möglichkeit hast die Menge einzugrenzen. Z.B. dich interessieren nur ids die innerhalb der letzten n Messungen oder innerhalb des letzen Monats/Woche/Tag aufgretten sind (wobei LIMIT n schneller ist). Dann kannst du dir die Eingenheiten von GROUP BY zu nutze machen.

          PHP-Code:
          SELECT 
            t1
          .id
            
          t1.datumzeit
            
          t1.messwert  
          FROM
            
          (
            
          SELECT 
              id

              
          messwert
              
          datumzeit
            FROM 
              messdat
            
          /*WHERE  (entweder)
                 datumzeit > DATE_SUB(NOW(), INTERVAL 7 DAY)
            */
            
          ORDER BY
              datumzeit DESC
            
          /*LIMIT  (oder)
              10000*/
            
          ) AS t1 
          GROUP BY
            id 
          Dazu ist aber ein Index auf datumzeit erforderlich...

          Kommentar


          • #6
            Hi,
            bedanke mich schon mal für die Hinweise. Kann erst morgen die Informationen liefern und testen.

            LG jspit
            PHP-Klassen auf github

            Kommentar


            • #7
              Moin,
              mein Problem hat sich etwas entschärft. Mein CREATE TABLE war wie folgt:
              Code:
              CREATE TABLE `messdat` (
                `id` int(11) NOT NULL,
                `datumzeit` int(11) NOT NULL,
                `messwert` double DEFAULT NULL,
                 KEY `id` (`id`),
                 KEY `datumzeit` (`datumzeit`)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8
              datumzeit ist historisch bedingt ein UNIX-Timestamp.

              Laufzeit mit der SQL-Abfrage auf der Entwicklungsmaschine 36 Sekunden.
              Um nochmal nachzuprüfen, das ein Index was bringt hab ich diese testweise entfernt.

              Code:
              CREATE TABLE `messdat` (
                `id` int(11) NOT NULL,
                `datumzeit` int(11) NOT NULL,
                `messwert` double DEFAULT NULL
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8
              Laufzeit: 7 Sekunden

              Aktuelle Tabelle:
              Code:
              CREATE TABLE `messdat` (
                `id` int(11) NOT NULL,
                `datumzeit` int(11) NOT NULL,
                `messwert` double DEFAULT NULL,
                KEY `datumzeit` (`datumzeit`)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8
              Laufzeit: 2 Sekunden.

              Auf dem Zielsystem ist die Laufzeit noch um einiges höher.
              Da werde ich mal die Vorschläge von erc weiter verfolgen.

              LG jspit
              PHP-Klassen auf github

              Kommentar


              • #8
                Zitat von jspit Beitrag anzeigen
                Laufzeit: 7 Sekunden
                Das einmal nutzt er den Index von Id zum gruppieren und das andere mal macht er gleich ein full table scan. Eigentlich sollte der Optimizer das erkennen. Es macht dabei ein gewaltigen Unterscheid ob du wenige ids mit vielen Messwerten hast (was nach dem obrigen Verhalten wohl der Fall ist) oder viele Ids mit wenigen Messwerten.
                Theoretisch wäre bei deinem Query ein kombinierte Index auf id und datumzeit das sinnigste, leider sind Theorie (jedenfalls meine Theorie) und Praxis teils anderer Meinung. Ich hab das mit "viele Ids mit wenigen Messwerten" versucht, das war ab 1 Mio. Datensätze inakzeptabel. Mit Variante A läuft das auch mit über 200Mio. Datensätze im ms Bereich. Wobei Variante A auch anderes aussehen kann. Wenn du nur den letzten Datensatz brauchst, kommst du mit einer zweiten Tabelle, die immer den aktuellsten Datensatz je Id enthält, besser.

                Kommentar


                • #9
                  Zitat von erc Beitrag anzeigen
                  wenige ids mit vielen Messwerten hast (was nach dem obrigen Verhalten wohl der Fall ist) oder viele Ids mit wenigen Messwerten.
                  Genau! ca. 50 id's in der Testdatenbank.

                  Zitat von erc Beitrag anzeigen
                  Theoretisch wäre bei deinem Query ein kombinierte Index auf id und datumzeit das sinnigste, leider sind Theorie (jedenfalls meine Theorie) und Praxis teils anderer Meinung.
                  Hab ich gleich probiert. Der Gewinn liegt nur bei 20%, bei einer grösseren DB

                  Die Variante A bringt mir nicht viel, denn die obige Abfrage ist nur die einfachste von mehreren ähnlichen Varianten, wie z.B. diese Stichtagsvariante:
                  Code:
                  SELECT 
                    t1.id, 
                    t1.datumzeit, 
                    t1.messwert  
                  FROM
                    messdat AS t1
                  JOIN (
                    SELECT 
                      id, 
                      MIN(datumzeit) AS mdz  
                    FROM 
                      messdat 
                    WHERE datumzeit > :datefrom
                    GROUP BY id
                  ) AS t2 
                  ON t1.id = t2.id AND t1.datumzeit = t2.mdz
                  PHP-Klassen auf github

                  Kommentar


                  • #10
                    Zitat von lstegelitz Beitrag anzeigen
                    Und den EXPLAIN...
                    hab ich jetzt mal gemacht:

                    Code:
                    id	select_type	table	        type	possible_keys	key	        key_len	ref	rows	Extra
                    1	PRIMARY	        <derived2>	ALL					                51	
                    1	PRIMARY	        t1	        ref	datumzeit	datumzeit	4	t2.mdz	3	Using where
                    2	DERIVED	        messdat	        ALL					                550851	Using temporary; Using filesort
                    Kann hieraus noch etwas Neues entnommen werden?

                    LG jspit
                    PHP-Klassen auf github

                    Kommentar


                    • #11
                      Kann hieraus noch etwas Neues entnommen werden?
                      Jetzt vermutlich nicht mehr, erc hat die wichtigsten Sachen schon erkannt & genannt...
                      (im EXPLAIN sieht man aber sehr schön, das an zwei Stellen kein Index genutzt wurde, und das die Datenmenge so groß ist, das eine temp. Tabelle auf Platte erzeugt wird, beides Performancekiller)
                      Über 90% aller Gewaltverbrechen passieren innerhalb von 24 Stunden nach dem Konsum von Brot.

                      Kommentar


                      • #12
                        Zitat von lstegelitz Beitrag anzeigen
                        und das die Datenmenge so groß ist, das eine temp. Tabelle auf Platte erzeugt wird, beides Performancekiller
                        Das erklärt, warum ab einer bestimmten Größe der Tabelle die Abfragezeiten sprichwörtlich explodieren. Danke.
                        PHP-Klassen auf github

                        Kommentar


                        • #13
                          Die Optimierungen richten sich immer nach den Anforderungen, was für Informationen willst du aus den Daten gewinnen, wie schnell muss das sein, wie oft, wie groß ist das Wachstum usw...
                          Bis zu einen gewissen Punkt funktioniert das auch noch mit full table scans. Mit aktuellen Maschinen hast du da auch ein relativ großen Spielraum, aber irgendwann ist Zick. Du musst die Menge die du verarbeitest eingrenzen, ansonsten wird es mit jedem Datensatz langsamer. Pi mal Daumen bist du bis 100.000 Datensätze im grünen Bereich, drüber wirds dann langsam (>0,5 Sekunden).
                          Bei deinem Query mit dem Stichtag hast du auch das Problem. Wenn der Stichtag recht aktuell ist sind es nur wenig Datensätze, je weiter er in der Vergangenheit ist desto mehr Datensätze müssen verarbeitet werden. Sinnvollerweise müsstest du dort mit WHERE datumzeit BETWEEN :datefrom AND (soweit wie nötig, so klein wie möglich) eingrenzen.

                          Was du noch versuchen könntest ist die GROUP BY Funktionaliät von Mysql auszunutzen.

                          PHP-Code:
                          SELECT 
                            t1
                          .id
                            
                          t1.datumzeit
                            
                          t1.messwert  
                          FROM
                            
                          (
                                
                          SELECT 
                                  id

                                  
                          mdz  
                                FROM 
                                  messdat 
                                WHERE datumzeit 
                          > :datefrom
                                ORDER BY
                                   datumzeit DESC
                            
                          ) AS t1
                          GROUP BY
                            t1
                          .id 
                          Beim schreiben fällt mir aber grade die Sinnlosigkeit dabei auf. Bei 51 Ids sparst du damit 51 Join-Operationen die gut über den datumzeit Index abgewickelt werden und verbrätst dabei bei weitem mehr Speicher für die temporäre Tabelle. Brauchst du also gar nicht probieren, macht nur im kleineren Rahmen ohne Index sinn.

                          Zitat von lstegelitz Beitrag anzeigen
                          (im EXPLAIN sieht man aber sehr schön [...] und das die Datenmenge so groß ist, das eine temp. Tabelle auf Platte erzeugt wird
                          Das sagt es nicht aus, beides wird im Speicher abgewickelt bis die jeweiligen Limits erreicht sind. Die Temporäre Tabelle enthält nur die 51 Datensätze, das Filesort hat die 500.000 Datensätze verarbeitet und je nach eingestellten sort buffer wahrscheinlich auf die Festplatte dabei ausgelagert.

                          Kommentar


                          • #14
                            Zitat von erc Beitrag anzeigen
                            Mit aktuellen Maschinen hast du da auch ein relativ großen Spielraum, aber irgendwann ist Zick. Du musst die Menge die du verarbeitest eingrenzen, ansonsten wird es mit jedem Datensatz langsamer. Pi mal Daumen bist du bis 100.000 Datensätze im grünen Bereich, drüber wirds dann langsam (>0,5 Sekunden).
                            Da liege ich ja mit meiner 'Maschine' in der Größe einer Zigarettenschachtel noch richtig gut.

                            Zitat von erc Beitrag anzeigen
                            Bei deinem Query mit dem Stichtag hast du auch das Problem. Wenn der Stichtag recht aktuell ist sind es nur wenig Datensätze, je weiter er in der Vergangenheit ist desto mehr Datensätze müssen verarbeitet werden. Sinnvollerweise müsstest du dort mit WHERE datumzeit BETWEEN :datefrom AND (soweit wie nötig, so klein wie möglich) eingrenzen.
                            Werde ich so machen.

                            Zitat von erc Beitrag anzeigen
                            Was du noch versuchen könntest ist die GROUP BY Funktionaliät von Mysql auszunutzen.

                            PHP-Code:
                            SELECT 
                              t1
                            .id
                              
                            t1.datumzeit
                              
                            t1.messwert  
                            FROM
                              
                            (
                                  
                            SELECT 
                                    id

                                    
                            mdz  
                                  FROM 
                                    messdat 
                                  WHERE datumzeit 
                            > :datefrom
                                  ORDER BY
                                     datumzeit DESC
                              
                            ) AS t1
                            GROUP BY
                              t1
                            .id 
                            Beim schreiben fällt mir aber grade die Sinnlosigkeit dabei auf. Bei 51 Ids sparst du damit 51 Join-Operationen die gut über den datumzeit Index abgewickelt werden und verbrätst dabei bei weitem mehr Speicher für die temporäre Tabelle. Brauchst du also gar nicht probieren, macht nur im kleineren Rahmen ohne Index sinn.
                            Hab ich schon durch. So ähnlich war meine erste Variante, ab einer relativen kleinen Anzahl Datensätze ging dann gar nichts mehr.

                            LG jspit
                            PHP-Klassen auf github

                            Kommentar


                            • #15
                              Zitat von jspit Beitrag anzeigen
                              Da liege ich ja mit meiner 'Maschine' in der Größe einer Zigarettenschachtel noch richtig gut.
                              Nicht zu genau nehmen... ich hab nicht unbedingt an ein System gedacht was idlet und einmal am Tag ein Query beantwortet.

                              Kommentar

                              Lädt...
                              X