Ankündigung

Einklappen
Keine Ankündigung bisher.

Multi Column Index - Abfrage beschleunigen?

Einklappen

Neue Werbung 2019

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

  • Multi Column Index - Abfrage beschleunigen?

    Ich muss seit langem auch mal eine Frage zum Thema MySQL stellen.

    Ich habe eine Tabelle mit ~50.000.000 Einträge und jede Sekunde kommen so 10-20 Einträge hinzu. In dieser Tabelle gibt es nun 2 Spalten welche sehr häufig verwendet werden für Abfragen, auf denen aktuell leider kein Index ist.
    Die Spalten heißen incoming_date und outgoing_date.

    Aktuell braucht die folgende Abfrage ~10-30 Minuten je nach Server Auslastung.

    Code:
    SELECT m.id, m.incoming_date, m.outgoing_date FROM message m WHERE m.outgoing_date IS NULL OR m.incoming_date > m.outgoing_date
    Ich suche aktuell eine Möglichkeit diese Abfrage zu optimieren und frage mich, ob ein Multi-Index auf beide Spalten hier eine Verbesserung bring, und ob ein Index beim Vergleich von 2 Spalten überhaupt benutzt wird.
    Hat da jemand Erfahrung mit?

    Bevor die Frage aufkommt: Die Anwendung wird gerade Stück für Stück neu geschrieben und Ziel ist es in Zukunft auf PostgreSQL umzusteigen, aktuell soll die Abfrage aber erstmal für MySQL optimiert werden.
    Wenn es in Postgres noch weitere Möglichkeiten gibt dies zu optimieren, würde ich mich auch hier über Erfahrungen freuen.

  • #2
    Moin,

    scheinbar funktioniert sowas in MySQL nicht. https://dba.stackexchange.com/questi...umn-comparison
    Habe es gerade auch lokal ausprobiert (350k Einträge), der Index wird für den Vergleich nicht genutzt.

    Vielleicht hilft sowas: https://dev.mysql.com/doc/refman/5.7...y-indexes.html um die Differenz virtuell zu speichern. Alternativ evtl. Trigger auf Insert und Update um die Differenz direkt zu setzen - ich weiß aber nicht wie sich das bei solchen Datenmengen auswirkt.
    [COLOR=#A9A9A9]Relax, you're doing fine.[/COLOR]
    [URL="http://php.net/"]RTFM[/URL] | [URL="http://php-de.github.io/"]php.de Wissenssammlung[/URL] | [URL="http://use-the-index-luke.com/de"]Datenbankindizes[/URL] | [URL="https://www.php.de/forum/webentwicklung/datenbanken/111631-bild-aus-datenbank-auslesen?p=1209079#post1209079"]Dateien in der DB?[/URL]

    Kommentar


    • #3
      Mit MySQL mag ich Dir nicht helfen, aber PostgreSQL.

      Code:
      test=# create table zeichen32 (id serial primary key, in_date date, out_date date, data text);
      CREATE TABLE
      test=*# insert into zeichen32 (in_date, out_date, data) select '1990-01-01'::date + (random()*1000)::int * '1day'::interval, '1993-01-01'::date + (random()*1000)::int * '1day'::interval, repeat(random()::text,5) from generate_series(1,10000) s;
      test=*# insert into zeichen32 (in_date, out_date, data) select '1990-01-01'::date + (random()*1000)::int * '1day'::interval, '1993-01-01'::date + (random()*1000)::int * '1day'::interval, repeat(random()::text,5) from generate_series(1,100000) s;
      INSERT 0 100000
      test=*# insert into zeichen32 (in_date, out_date, data) select '1990-01-01'::date + (random()*1000)::int * '1day'::interval, '1992-06-01'::date + (random()*1000)::int * '1day'::interval, repeat(random()::text,5) from generate_series(1,100000) s;
      INSERT 0 100000
      Tabelle hat nun einige Datensätze, einige davon treffen auf die Where-Condition zu.

      Abfrage ohne Index:

      Code:
      test=*# explain analyse select id, in_date, out_date from zeichen32 where out_date is null or in_date > out_date;
                                                         QUERY PLAN                                                  
      ----------------------------------------------------------------------------------------------------------------
       Seq Scan on zeichen32  (cost=0.00..8434.10 rows=132999 width=12) (actual time=18.145..43.357 rows=700 loops=1)
         Filter: ((out_date IS NULL) OR (in_date > out_date))
         Rows Removed by Filter: 209300
       Planning Time: 0.057 ms
       Execution Time: 43.469 ms
      (5 rows)
      An der Stelle hatte ich vorher vergessen, die Statistiken zu aktualisieren, daher noch eine grobe Fehlschätzung der Ergebnissmenge. Aber auch egal, hier geht eh nur ein seq-Scan.

      Nun ein Index. Dieser besitzt, voila, die Where-Condition, die wie später benötigen:

      Code:
      test=*# create index idx_test on zeichen32 (id, in_date, out_date) where out_date is null or in_date > out_date;
      CREATE INDEX
      test=*#
      test=*#
      test=*# explain analyse select id, in_date, out_date from zeichen32 where out_date is null or in_date > out_date;
                                                             QUERY PLAN                                                      
      ------------------------------------------------------------------------------------------------------------------------
       Bitmap Heap Scan on zeichen32  (cost=41.45..4421.20 rows=70700 width=12) (actual time=0.192..0.889 rows=700 loops=1)
         Recheck Cond: ((out_date IS NULL) OR (in_date > out_date))
         Heap Blocks: exact=567
         ->  Bitmap Index Scan on idx_test  (cost=0.00..23.77 rows=70700 width=0) (actual time=0.124..0.124 rows=700 loops=1)
       Planning Time: 0.169 ms
       Execution Time: 0.997 ms
      (6 rows)
      Schon besser, oder? Aber da geht noch was...

      Code:
      test=# vacuum zeichen32 ;
      VACUUM
      
      test=# explain analyse select id, in_date, out_date from zeichen32 where out_date is null or in_date > out_date;
                                                                  QUERY PLAN                                                            
      ----------------------------------------------------------------------------------------------------------------------------------
       Index Only Scan using idx_test on zeichen32  (cost=0.28..730.77 rows=70700 width=12) (actual time=0.026..0.117 rows=700 loops=1)
         Heap Fetches: 0
       Planning Time: 0.170 ms
       Execution Time: 0.208 ms
      (4 rows)
      Durch das VACUUM habe ich die Visibility-Map (VM) erstellt bzw. aktualisiert. Die Erklärung, was das ist und warum und so würde zu weit führen, aber da er nun a) die VM hat UND alle Spalten im Index sind, wechselt er von einen 'normalen' Index-Scan bzw. Bitmap Index Scan nun auf einen 'Index Only' - Scan, was die schnellste Methode hier ist.

      Sehr sicher wirst Du auch bei 50 Millionen Zeilen damit im Bereich von Millisekunden bleiben, wenn die Ergebnissmenge relativ gering ist, was ich vermute. Vermutlich willst Du ja nur Probleme rausfiltern mit dieser Where-Condition, oder?

      Andreas


      PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

      Kommentar


      • #4
        Zitat von VPh Beitrag anzeigen
        Moin,

        scheinbar funktioniert sowas in MySQL nicht. https://dba.stackexchange.com/questi...umn-comparison
        Habe es gerade auch lokal ausprobiert (350k Einträge), der Index wird für den Vergleich nicht genutzt.

        Vielleicht hilft sowas: https://dev.mysql.com/doc/refman/5.7...y-indexes.html um die Differenz virtuell zu speichern. Alternativ evtl. Trigger auf Insert und Update um die Differenz direkt zu setzen - ich weiß aber nicht wie sich das bei solchen Datenmengen auswirkt.
        Danke für den Link, da werde ich mal etwas mit testen. Auf die Idee mit einer eigenen virtuellen Spalte bin ich noch nicht gekommen.

        Zitat von akretschmer Beitrag anzeigen
        Durch das VACUUM habe ich die Visibility-Map (VM) erstellt bzw. aktualisiert. Die Erklärung, was das ist und warum und so würde zu weit führen, aber da er nun a) die VM hat UND alle Spalten im Index sind, wechselt er von einen 'normalen' Index-Scan bzw. Bitmap Index Scan nun auf einen 'Index Only' - Scan, was die schnellste Methode hier ist.
        Das klingt auf jeden Fall nach der optimalen Lösung, wenn wir auf Postgres umgestiegen sind. Vielen Dank hierfür schonmal.

        Zitat von akretschmer Beitrag anzeigen
        Sehr sicher wirst Du auch bei 50 Millionen Zeilen damit im Bereich von Millisekunden bleiben, wenn die Ergebnissmenge relativ gering ist, was ich vermute. Vermutlich willst Du ja nur Probleme rausfiltern mit dieser Where-Condition, oder?
        Das ist korrekt. Die Daten kommen aus einem RabbitMQ Stream und werden 1) in die Datenbank eingetragen und 2) an eine Memory Datenbank weitergeleitet. Die Datenbank ist für das Archivieren der Langzeitdaten zuständig und in der Memory Datenbank wird dann mit den "heißen" Daten gearbeitet. Diese verbleiben ungefähr 5-10 Minuten in der Memory Datenbank und das Ergebnis wrd dann in der Archive Datenbank aktualisiert. Mit der Abfrage oben, wird in regelmäßigen Abständen überprüft, ob Einträge vergessen oder erneut gestartet werden müssen z.B. weil ein Worker abgestürzt ist. Ob die Abfrage hier jetzt 100ms oder 2 Sekunden braucht ist dabei nicht so wichtig. Nur die aktuell 10-30 Minuten sind deutlich zu lange.

        Kommentar


        • #5
          wenn Du es langsamer haben willst, baue halt ein pg_sleep() mit ein

          Code:
          test=*# explain analyse select id, in_date, out_date, pg_sleep(1) from zeichen32 where out_date is null or in_date > out_date;
                                                                          QUERY PLAN                                                                
          ------------------------------------------------------------------------------------------------------------------------------------------
           Index Only Scan using idx_test on zeichen32  (cost=0.27..897.69 rows=70000 width=16) (actual time=1000.452..700831.840 rows=700 loops=1)
             Heap Fetches: 0
           Planning Time: 0.093 ms
           Execution Time: 700832.517 ms
          (4 rows)
          SCNR
          PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

          Kommentar


          • #6
            Zitat von VPh Beitrag anzeigen
            scheinbar funktioniert sowas in MySQL nicht.
            Oh, interessant, was ist "sowas" genau?
            Ein Index über 2 Spalten?
            Ein Index über 2 Spalten, der sogar berücksichtigt wird?

            Mir fällt als "Abkürzung" noch Partitionierung ein oder geht das auch nicht mysql?

            Ansonsten grundsätzlich:
            Eine Abfrage Bedingung mir OR bedeutet, dass man gern pro Operand einen (Full)Scan kalkulieren muss. Hier also 2 Fullscans. Die Reduktion auf ein Kriterium = 1 Scan würde also die Ausführungszeit der Abfrage auch ohne Index halbieren. 115 minuten statt 30 Minuten.
            Es bietet sich daher an, auf Kriterien mit OR zu verzichten. Das kann man oft dadurch erreichen, dass man NULL schlicht nicht erlaubt. Denn erst das NULL erfordert die "Sonderbehandlung" mit separater Prüfung.
            Wie es konkret in dem Fall oben umsetzbar wäre, müsste man prüfen. Also im Wesentlichen die Frage, wie behandelt man den derzeitigen NULL Fall in der Spalte incoming_date, was wird statt dessen eingetragen ...
            Wenn die Archivdaten(bank) kein Hindernis ist, könnte man das Handling vielleicht umstellen und wäre doppelt so schnell. Schnell verdientes Geld.

            Kommentar


            • #7
              Zitat von Perry Staltic Beitrag anzeigen

              Ansonsten grundsätzlich:
              Eine Abfrage Bedingung mir OR bedeutet, dass man gern pro Operand einen (Full)Scan kalkulieren muss. Hier also 2 Fullscans. Die Reduktion auf ein Kriterium = 1 Scan würde also die Ausführungszeit der Abfrage auch ohne Index halbieren.
              Sorry, aber das ist schlicht falsch.
              PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

              Kommentar


              • #8
                Zitat von Perry Staltic Beitrag anzeigen

                Oh, interessant, was ist "sowas" genau?
                Ein Index über 2 Spalten?
                Ein Index über 2 Spalten, der sogar berücksichtigt wird?

                Mir fällt als "Abkürzung" noch Partitionierung ein oder geht das auch nicht mysql?

                Ansonsten grundsätzlich:
                Eine Abfrage Bedingung mir OR bedeutet, dass man gern pro Operand einen (Full)Scan kalkulieren muss. Hier also 2 Fullscans. Die Reduktion auf ein Kriterium = 1 Scan würde also die Ausführungszeit der Abfrage auch ohne Index halbieren. 115 minuten statt 30 Minuten.
                Es bietet sich daher an, auf Kriterien mit OR zu verzichten. Das kann man oft dadurch erreichen, dass man NULL schlicht nicht erlaubt. Denn erst das NULL erfordert die "Sonderbehandlung" mit separater Prüfung.
                Wie es konkret in dem Fall oben umsetzbar wäre, müsste man prüfen. Also im Wesentlichen die Frage, wie behandelt man den derzeitigen NULL Fall in der Spalte incoming_date, was wird statt dessen eingetragen ...
                Wenn die Archivdaten(bank) kein Hindernis ist, könnte man das Handling vielleicht umstellen und wäre doppelt so schnell. Schnell verdientes Geld.
                Ich wäre jetzt davon ausgegangen, da es ja eh ein Full Scan machen muss und es sich um die selben Columns handelt, die Abfrage von Links nach Rechts abarbeitet. Also alle schon mal mit rein nimmt wo die Spalte NULL ist und nur wenn die nicht NULL ist dann den zweiten Teil der Abfrage abarbeitet. Wieso muss MySQL dort 2 mal die ganze Tabelle durchgehen?

                Kommentar


                • #9
                  Zitat von Zeichen32 Beitrag anzeigen
                  Diese verbleiben ungefähr 5-10 Minuten in der Memory Datenbank und das Ergebnis wrd dann in der Archive Datenbank aktualisiert. Mit der Abfrage oben, wird in regelmäßigen Abständen überprüft, ob Einträge vergessen oder erneut gestartet werden müssen z.B. weil ein Worker abgestürzt ist. Ob die Abfrage hier jetzt 100ms oder 2 Sekunden braucht ist dabei nicht so wichtig. Nur die aktuell 10-30 Minuten sind deutlich zu lange.
                  Das heißt du kannst auch die Abfrage auf ein Zeitfenster begrenzen? Wenn es reicht z.B. die letzten 24h zu betrachten, kannst du das über ein Index auf incoming_date umsetzen.

                  PS: 10-30 Minuten Ausführungszeit für ein Query dieser Art sind extrem langsam.

                  Kommentar


                  • #10
                    Zitat von Perry Staltic Beitrag anzeigen

                    Oh, interessant, was ist "sowas" genau?
                    Ein Index über 2 Spalten?
                    Ein Index über 2 Spalten, der sogar berücksichtigt wird?
                    Die beiden Spalten aus dem Index miteinander zu vergleichen.
                    Aber vielleicht wird auch einfach nicht der Index genutzt weil sowieso alle Einträge angesehen werden müssen. Im Explain wurde der Index nicht mal als "possible key" aufgeführt.
                    [COLOR=#A9A9A9]Relax, you're doing fine.[/COLOR]
                    [URL="http://php.net/"]RTFM[/URL] | [URL="http://php-de.github.io/"]php.de Wissenssammlung[/URL] | [URL="http://use-the-index-luke.com/de"]Datenbankindizes[/URL] | [URL="https://www.php.de/forum/webentwicklung/datenbanken/111631-bild-aus-datenbank-auslesen?p=1209079#post1209079"]Dateien in der DB?[/URL]

                    Kommentar


                    • #11
                      Zitat von erc Beitrag anzeigen
                      Das heißt du kannst auch die Abfrage auf ein Zeitfenster begrenzen? Wenn es reicht z.B. die letzten 24h zu betrachten, kannst du das über ein Index auf incoming_date umsetzen.
                      Da müsste ich nochmal genau prüfen ob dies an anderen Stellen Seiteneffekte hat. Von der Idee her hatte ich die Idee ebenfalls bereits, aber wollte mich erst umhören, ob es auch Möglichkeiten gibt ohne die "Logik" der Abfrage verändern zu müssen.

                      Zitat von erc Beitrag anzeigen
                      PS: 10-30 Minuten Ausführungszeit für ein Query dieser Art sind extrem langsam.
                      Wenn man sie alleine betrachtet bestimmt, allerdings schrieb ich oben ja bereits, dass gleichzeitig auch noch 10-20 Rows jede Sekunde hinzukommen. Dazu kommen noch die normalen Abfragen. Laut Statistik bedient das MySQL Cluster so im Schnitt 800 gleichzeitige Verbindungen.

                      Zitat von VPh
                      Aber vielleicht wird auch einfach nicht der Index genutzt weil sowieso alle Einträge angesehen werden müssen. Im Explain wurde der Index nicht mal als "possible key" aufgeführt.
                      Das kann ich bestätigen, wenn ich das Explain auf meine Tabelle ausführe steht dort auch nur "Using where".

                      Mir fällt als "Abkürzung" noch Partitionierung ein oder geht das auch nicht mysql?
                      Doch das gibt es. Wenn ich es allerdings richtig verstehe, kann man dies nicht ohne Downtime einrichten. Bzw. hätte man dies bereits am Anfang einrichten müssen.
                      https://dev.mysql.com/doc/refman/5.7...titioning.html

                      Kommentar

                      Lädt...
                      X