mySQL: Index auf Spalten, die schon im Primary Index enthalten sind?

    Hallo,


    die Frage in Kurzform: macht es Sinn, einen zusätzlichen Index auf Spalten zu erzeugen, die schon im Primary Index enthalten sind?


    Lange Version:

    Ich hab eine Datenbank im iobroker, in der ich u.a. die Daten meiner Wetterstation sammle, und in die ich auch die Daten aus der Cloud des Anbieters der Wetterstation importiert habe. Die Datenbank hat die Spalten:

    id - der Messwert (z.B. Außentemperatur)

    ts - ein Timestamp

    value - der eigentliche Messwert zum jeweiligen Zeitpunkt


    Im Primary Index sind id und ts, das hat iobroker bei der Einrichtung so angelegt. Die SELECT Statements für die Visualisierung sehen z.B. so aus:

    SQL
    SELECT ts, val FROM `iobroker`.ts_number WHERE  `iobroker`.ts_number.id=7 AND `iobroker`.ts_number.ts < 1659175200000 AND `iobroker`.ts_number.ts >= 1656583200000 UNION ( SELECT ts, val FROM `iobroker`.ts_number WHERE  `iobroker`.ts_number.id=7 AND `iobroker`.ts_number.ts < 1656583200000 ORDER BY `iobroker`.ts_number.ts DESC LIMIT 1) UNION ( SELECT ts, val FROM `iobroker`.ts_number WHERE  `iobroker`.ts_number.id=7 AND `iobroker`.ts_number.ts >= 1659175200000 ORDER BY `iobroker`.ts_number.ts ASC LIMIT 1) ORDER BY ts ASC;


    An sich funktioniert das. Abfragen über einen Zeitraum von 2 Jahren dauern ca. 0,4 Sekunden. Allerdings tauchen die Statements im Log für "queries-not-using-indexes" auf, und ich weiß nicht, wieso. Denn wenn ich mir so ein SELECT Statement mit EXPLAIN ansehe, dann steht dort sogar, dass der PRIMARY Key benutzt wird - bis auf das UNION Result am Ende. Ist das der Grund?


    Frage ist nun, ob die Abfragen beschleunigt werden können, indem man zusätzliches Indizes einführt, z.B. über die Spalte id oder ts. Ich hab das mal probiert, und die Anzeige in phpmyadmin wird spürbar beschleunigt (vermutlich, weil die ausschließlich nach ts sortiert wird). Die Anzeige im iobroker profitiert allerdings nicht.

    Hay,

    Zitat von frank_m

    macht es Sinn, einen zusätzlichen Index auf Spalten zu erzeugen, die schon im Primary Index enthalten sind?

    JA!


    Ein zusammengesetzter primary key ist nur effizient, wenn nach dem zusammengesetzten primary key gesucht wird.


    Wir haben eine Tabelle, die über eine Mio Datensätze enhält und eine Abfrage, die diese Tabelle mit anderen sieben per JOIN verknüpft. Hat wunderbar funktioniert. Alle Verknüpfungen passieren über Indizes. Es wurde ein weiterer JOIN dazu gefügt, mit einem JOIN über einen Wert, der Bestandteil eines zusammengesetzten primary keys war -> 13 Sekunden dauerte das Query plötzlich. Ich vermutete als spontanen Auslöser den kombinierten Index - als der Parameter des JOINs in einen zusätzlichen Index überführt wurde -> 0.1 Sekunden.


    Ich habe dann noch einen Abfrage optimiert, damit es noch schneller läuft. Es wurden auch jedesmal unnötige Daten generiert über einen zweifachen count, die nur einmal sonst im ganzen Projekt benötigt wurde - und sie werden jetzt nur noch dort mit generiert und nicht im Mega-JOIN.


    CU, Peter

    Peter Kleemann // https://www.pkleemann.de // +49 621 1806222-0 // Kann Programme, Internet, Netzwerke und Telefon.

    2 Mal editiert, zuletzt von CmdrXay ()

    Gefällt mir 2
    Zitat von CmdrXay

    Ein zusammengesetzter primary key ist nur effizient, wenn nach dem zusammengesetzten primary key gesucht wird.

    Das ist der Normalfall. Eine SELECT der iobroker Visualisierung ist immer eine Kombination aus id und ts - siehe oben. Ausnahme ist eigentlich nur der Aufruf der Tabelle in phpmyadmin.

    Von wie vielen Datensätzen sprechen wir hier eigentlich? Und wie groß sind Daten bzw. Index?


    Da Du sowieso immer (nur) id und ts im WHERE hast, sehe ich keinen Grund für einen weiteren Index. Das ist sowieso schon im primären Index enthalten. Anders würde es natürlich aussehen, wenn Du nur nach ts suchen willst, da der Index mit id anfängt und dadurch nicht verwendet werden kann. Wenn das aber nur bei phpMyAdmin der Fall ist und Du dort nicht jeden Tag ran musst, würde ich es bleiben lassen. Ob der Query in phpMyAdmin nun eine halbe oder drei Sekunden braucht, ist meiner Meinung nach kaum der Rede wert.

    "Wer nur noch Enten sieht, hat die Kontrolle über seine Server verloren." (Netzentenfund)

    … jep, auf exakte Abfrage nach dem Index (in der richtigen Reihenfolge) und auf Index covering hatte ich schon geguckt.


    Wobei man halt trotzdem nicht weiß ob Mysql vielleicht da doch noch ne Optimierung hat wenn es den extra Index findet (Index covering ist z.B. so ein Fall). Deshalb mal messen und Execution Plans dafür rauslassen. Und dann gucken wir weiter :)

    Hay,


    also ich wette, dass das ORDER BY hier die show versaut. Dort wird immer nur auf ts referenziert.


    Ein Hinweis, falls das Mißverstanden wurde: Wenn ich schreibe "gesucht wird", dann umfasst das alle elementaren Operationen, bei denen in Datebanken gesucht wird, das umfasst nicht nur WHERE, JOIN, sondern auch HAVING, ORDER und in gewissem Umfang auch GROUP (letzteres for Oracle auf jeden Fall, mysql/Mariadb weiß ich jetzt nicht).


    CU. Peter

    Peter Kleemann // https://www.pkleemann.de // +49 621 1806222-0 // Kann Programme, Internet, Netzwerke und Telefon.

    Einmal editiert, zuletzt von CmdrXay ()

    Gefällt mir 1

    Die Anleitung von Mariadb ist da zum Thema Query Plan eigentlich ganz gut: https://mariadb.com/kb/en/explain/


    Und ja, ein zusätzlicher Index auf ts -könnte- das sortieren unterstützen, da hat CmdrXay natürlich recht!


    Prinzipiell machst den Query mal mit explain im query und mal mit und ohne den extra Index und schaust ob sich was am Plan ändert.


    Bzw: Explain Analyze ist natürlich noch nen ticken detailreicher. Explain führt den Select nicht aus, explain analyze schon.

    Ich hab den Aufruf einmal mit einem Index auf ts und einmal ohne gemacht. Das Ergebnis war allerdings bis auf wenige ms identisch.




    Auf der einen Seite scheint das Einsammeln der Daten aus den drei Select Statements nicht allzulange zu dauern. Die beiden Limits sind sehr kurz, und die Filter Zeile mit 156 ms auch ok. Aber eine Ebene höher dauert das Union materialize plötzlich fast 700 ms. Wenn ich die Beschreibung richtig verstanden hab, dann hätte ich dort in etwa die Summe der drei Selects erwartet.

    Die zusätzlichen 170 ms für den Table Scan und das Sortieren sind meines Erachtens wieder ok.


    Was dauert an dem Union materialize so lange und kann man das optimieren?

    Hay,


    könnte ev. an der deduplication liegen. Versuch mal in den Statements UNION ALL nur zum Spaß...


    CU, Peter

    Peter Kleemann // https://www.pkleemann.de // +49 621 1806222-0 // Kann Programme, Internet, Netzwerke und Telefon.

    Gefällt mir 1

    Wenn ich das richtig im Kopf habe, kann der Cache einem solche Tests ebenfalls vermiesen. Verwende beim SELECT unbedingt mal ein SQL_NO_CACHE. Und teste es keinesfalls mit einer Dummy-Tabelle, die nur ein paar Testeinträge enthält, da werden Indizes Dank dem Optimizer oft gar nicht verwendet.


    (Dass auch das Dateisystem theoretisch einen Cache hat, lasse ich mal außen vor. Das spielt bei SSDs eher eine untergeordnete Rolle.)

    "Wer nur noch Enten sieht, hat die Kontrolle über seine Server verloren." (Netzentenfund)

    Zitat von CmdrXay

    Versuch mal in den Statements UNION ALL nur zum Spaß...

    Das macht aus den knapp 700 ms bei "Union materialize" 180 ms ... In der Gesamtabfrage sinkt die Zeit von gut 870 ms auf 305.


    Zitat von KB19

    Verwende beim SELECT unbedingt mal ein SQL_NO_CACHE.

    Das hingegen hatte keinen Einfluss. Die gesamte Abfrage dauerte 30 ms länger.


    Zitat von KB19

    Und teste es keinesfalls mit einer Dummy-Tabelle, die nur ein paar Testeinträge enthält, da werden Indizes Dank dem Optimizer oft gar nicht verwendet.

    Ich teste das auf der produktiven Tabelle mit den 13 Mio Einträgen. Die Daten da drin sind nur für mich privat zum Spaß, und ggf. auch noch als Backup in der Herstellercloud verfügbar.



    Bleibt die Frage: Das Ganze ist ja Bestandteil des eChart Plugins vom ibroker. Vermutlich muss ich da jetzt in den Sourcecode und schauen, wo die SQL Statements zusammengebaut werden?

    Hay,

    Zitat von frank_m

    Das macht aus den knapp 700 ms bei "Union materialize" 180 ms ... In der Gesamtabfrage sinkt die Zeit von gut 870 ms auf 305

    Fein.


    Allerdings ein wichtiger Hinweis: Deduplizieren heißt ja, dass doppelte Datensätze, die ggf. durch UNION entstehen, im nachhinein entfernt werden. D.h. Du solltest zählen, ob vor und nach dem ALL die Anzahl der ausgeworfenen Datensätze gleich ist bzw. entscheiden, ob Dir das überhaupt wichtig, falls sie sich unterscheiden.


    CU, Peter

    Peter Kleemann // https://www.pkleemann.de // +49 621 1806222-0 // Kann Programme, Internet, Netzwerke und Telefon.

    frank_m Was willst du denn noch erreichen wenn du meinst den Source zu durchsuchen?


    Wenn du die tatsächlich gesendeten Statements sehen willst dann kannst du das Slow Query Log mit einer sehr geringen Zeit einstellen um erstmal alle Querys einzusammeln. Alternativ gibts bei Mysql noch sehr viele Protokolltabellen die du aktivieren könntest, je nachdem was du vorhast :)


    Es gibt fürs Query Log sogar einen Parameter 'log_queries_not_using_indexes': https://stackoverflow.com/ques…ies-not-using-index-mysql

    (Achtung: Thread is sehr alt!)

    Zitat von ThomasChr

    Es gibt fürs Query Log sogar einen Parameter 'log_queries_not_using_indexes':

    Genau damit habe ich die Aufrufe ja gefunden, um sie dann mit "explain" bzw. nun mit "explain analyze" zu untersuchen.


    Und genau da stehe ich jetzt. Anhand von "explain analyze" weiß ich, dass ein "UNION ALL" deutlich fixer ist, als ein "UNION". Nun muss ich ja die aufrufende Software modifizieren, da wo das SQL Statement zusammengebaut wird. Denn ich werde ja kaum dem SQL Server sagen können, dass er UNION ALL anstatt UNION ausführen soll.

    Wenn du dir sicher bist dass der UNION ALL keine Probleme verursacht solltest du den Programmcode direkt beim Hersteller anpassen. Für dein Plugin hätte ich vermutet das es hier ist: https://github.com/ioBroker/ioBroker.echarts

    Das ist aber wohl nur der Vordergrund, weil nur Javascript. Da werden die SQL Statements definitiv nicht sein.

    Evtl. also im Hauptrepo: https://github.com/ioBroker/ioBroker


    Wobei ich auch da nix mit SQL finde...

    So, ich habe noch ein bisschen experimentiert.


    Die Ergebnisse bei UNION und UNION ALL sind bei mir identisch. Das ist nicht verwunderlich, denn die Tabelle sollte eigentlich immer nur einen Messwert für die Kombination aus id und ts haben. Die Abfragen mit UNION ALL sind aber ca. doppelt so schnell, wie mit UNION.


    Ich bin mir aber nicht sicher, ob ich daraus einen Request an das SQL Plugin vom iobroker mache. Denn es ist denkbar, dass andere Datenquellen die Datenbank anders befüllen, und da könnte das UNION ALL möglicherweise problematisch sein. Grundsätzlich funktioniert es ja.