Optimierung von MySQL-Abfragen mit GROUP BY auf Zeitfunktionen

Question

Ich habe die folgende Frage:

SELECT location, step, COUNT(*), AVG(foo), YEAR(start), MONTH(start), DAY(start)
FROM table WHERE jobid = 'xxx' AND start BETWEEEN '2010-01-01' AND '2010-01-08'
GROUP BY location, step, YEAR(start), MONTH(start), DAY(start)

Ursprünglich hatte ich Indizes für einzelne Spalten, wie z. B. Jobid y Start aber schnell festgestellt, dass MySQL nur einen Index pro Tabelle in einem Select anerkennt. Als solches würde es den Jobid Index und führen dann einen ziemlich großen Scan durch, um nach dem Start Bereich.

Hinzufügen eines Indexes auf ( Jobid , Start ) hat ziemlich geholfen, aber die GROUP BY verursacht immer noch Leistungsprobleme. Ich habe gelesen, dass die Dokumente über Optimierungen von GROUP BY und verstehe, dass ich, um von diesen Optimierungen profitieren zu können, einen Index benötige, der ( Standort , Schritt , Start ), aber ich habe noch zwei offene Fragen:

1. Werden die Optimierungen für die Gruppierung nach Zeitfunktionen (JAHR, MONAT, TAG usw.) überhaupt funktionieren? Oder muss ich diese Werte als separate Spalten speichern? Der Grund, warum ich die Funktionen gerne verwende, ist, dass ich damit die Zeitzone pro Verbindung steuern kann und Ergebnisse erhalte, die auf die Zeitzone des Endbenutzers zugeschnitten sind. Wenn ich das Jahr, den Monat und den Tag im Voraus speichern muss, werde ich es über UTC tun und dann werden alle meine Benutzer nur Berichte in UTC erhalten.

2. Selbst wenn ich Problem Nr. 1 lösen kann, kann ich das überhaupt tun? Der Index ( Jobid , Start ) half bei der WHERE-Klausel, aber die GROUP BY benötigt einen anderen Index, um optimiert zu werden ( Standort , Schritt , Start ) oder, abhängig von der Antwort auf Frage 1, ( Standort , Schritt , Jahr , Monat , Tag ). Aber das Problem ist, dass diese beiden Indizes nicht eine gemeinsame linke Reihe von Spalten teilen, so glaube ich nicht, dass meine WHERE und GROUP by kompatibel sein können, so dass der gleiche Index verwendet wird. Meine Frage lautet also: Bin ich hier einfach aufgeschmissen?

Jede andere Idee, wie man dies erreichen kann, wäre hilfreich. Und, um ein paar Fragen/Kommentare, die auftauchen könnten, vorwegzunehmen:

1. Ja, es handelt sich um einen Zeitreihendatensatz.
2. Ja, es würde von etwas profitieren wie RRDtool aber das würde dazu führen, dass ich die zeitzonenspezifischen Ergebnisse verliere.
3. Ja, die Vorausberechnung von Rollups wäre wahrscheinlich eine gute Idee, aber ich brauche keine Fantastisch Leistung, und deshalb ist es für mich in Ordnung, wenn お利口さん Leistung, wenn ich die Ergebnisse für die Zeitzonen der einzelnen Benutzer anpassen kann.

Mit dem oben Gesagten, wenn jemand hat alle Design-Vorschläge, wie man so etwas wie Rollups oder Round-Robin-Datenbanken zu tun und immer noch Zeitzone-spezifische Ergebnisse, ich bin ganz Ohr!

---

Update Wie gewünscht, hier einige weitere Informationen:

Indizes aus der Ausgabe anzeigen:

step    0   PRIMARY 1   step\_id A   16  NULL    NULL        BTREE   
step    1   start   1   start   A   16  NULL    NULL        BTREE   
step    1   step    1   step    A   2   NULL    NULL        BTREE   
step    1   foo 1   foo A   16  NULL    NULL    YES BTREE   
step    1   location    1   location    A   2   NULL    NULL    YES BTREE   
step    1   jobid   1   jobid   A   2   NULL    NULL    YES BTREE   

show create table output:

CREATE TABLE \`step\` (
  \`start\` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  \`step\` smallint(2) unsigned NOT NULL,
  \`step\_id\` int(8) unsigned NOT NULL AUTO\_INCREMENT,
  \`location\` varchar(12) DEFAULT NULL,
  \`jobid\` varchar(37) DEFAULT NULL,
  PRIMARY KEY (\`step\_id\`),
  KEY \`start\_time\` (\`start\`),
  KEY \`step\` (\`step\`),
  KEY \`location\` (\`location\`),
  KEY \`job\_id\` (\`jobid\`)
) ENGINE=InnoDB AUTO\_INCREMENT=240 DEFAULT CHARSET=utf8

Answer 1

Stattdessen wird dies getan

GROUP BY location, step, YEAR(start), MONTH(start), DAY(start)
ORDER BY location, step, YEAR(start), MONTH(start), DAY(start)

Versuchen Sie

GROUP BY location, step, date_format(start, '%Y%m%d')
ORDER BY location, step, date_format(start, '%Y%m%d')

Answer 2

und verstehe, dass ich, um von diesen Optimierungen zu profitieren, einen Index benötige, der (Ort, Schritt, Start) enthält

Nö. Sie könnten einen zusammengesetzten Index erstellen jobid + start + location + step und es würde Hilfe, wenn es keine BETWEEN . Da Sie die Bereichsbedingung in WHERE - werden keine Indizes verwendet für GROUP BY und das einzige und beste, was Sie für diese Anfrage tun können, ist einfach jobid + start Index.

Die beste Lösung ist imho, diese Tabelle in eine vorberechnete Form zu zerlegen. Zum Beispiel: die Daten stündlich nach Planer zu aggregieren.

Answer 3

Einen einzelnen zusammengesetzten Index für jobid, start, location, step

gruppieren Sie dann zuerst nach dieser Reihenfolge und sortieren Sie sie:

SELECT location, step, COUNT(*), AVG(foo), YEAR(start), MONTH(start), DAY(start)
FROM table WHERE jobid = 'xxx' AND start BETWEEEN '2010-01-01' AND '2010-01-08'
GROUP BY YEAR(start), MONTH(start), DAY(start), location, step
ORDER BY location, step, YEAR(start), MONTH(start), DAY(start)

アップデイト

Es sieht so aus, als ob MySql den Index nicht verwenden kann, wenn die Funktionen YEAR, MONTH und DAY verwendet werden. da

1. Nachdem der Anfang aus der WHERE-Klausel entfernt wurde, zeigt die Erklärung immer noch using filesort
2. Hinzufügen von 3 Spalten: y = YEAR(start), m = MONTH(start), d=DAY(start) und erstellt einen Index für jobid, y, m, d, location, step und die Aktualisierung der WHERE ... AND y = 2010 AND m = 12 AND d BETWEEN 1 AND 08 entfernt die using temporary using filesort .

Die Beibehaltung von 3 zusätzlichen Spalten scheint eine schlechte Idee zu sein, da der Leistungsunterschied zwischen dem GROUP BY nicht so groß sein sollte, wenn es temporär verwendet wird oder nicht.

Answer 4

Es besteht die Möglichkeit, dass die Auswahl schneller erfolgt, wenn Ort und Schritt ganzzahlig sind Schlüssel in andere Tabellen sind, die nur Name und Integer-ID haben.

Erstens würde die Abfrage auf Integer-Daten gruppiert werden, was einen viel schnelleren Vergleich ermöglicht. Zweitens besteht die Möglichkeit, dass die DB-Engine diese Zahlen automatisch indiziert.

Ich würde auch in Erwägung ziehen, jobid in eine separate Tabelle auszulagern, falls sich der Wert wiederholt.