Löschen Sie alle Einträge, die älter als 15 Minuten sind

Question

Ich habe eine Tabelle, die pro Minute etwa 10-15 Tausend Einträge erhält. Jeder Eintrag wird beim Eingang mit dem aktuellen Zeitstempel markiert. Die Tabelle ist eine MEMORY Tabelle, da der Datenverlust keine Sorge ist.

Jede Minute habe ich ein Skript, das die folgende Abfrage ausführt:

DELETE FROM tracker WHERE post_time < DATE_SUB(NOW(), INTERVAL 15 MINUTE)

Diese Abfrage dauert ungefähr 1-2 Sekunden, was nicht schlecht ist, aber es scheint, dass diese Art von Abfrage (Löschen von allem älteren als X) viel schneller ausgeführt werden sollte, wenn sie gegen eine MEMORY Tabelle ausgeführt wird. Es gibt auch einen entsprechenden Spike auf der CPU, der sich jede Minute wie ein Dorn im Auge abhebt.

Gibt es Optimierungen, die ich an meiner Abfrage vornehmen kann, um sie effizienter auszuführen?

Answer 1

Wie immer sollten Sie den Abfrageplan anzeigen und hier posten. Sie tun dies, indem Sie EXPLAIN DELETE FROM tracker WHERE post_time < DATE_SUB(NOW(), INTERVAL 15 MINUTE) ausführen.

Jetzt ist das Problem wahrscheinlich, dass die DELETE-Abfrage keinen Index verwenden kann und alle Ihre Zeilen durchlaufen muss.

Auch wenn Sie bereits einen Index auf post_time haben, wird er wahrscheinlich nicht verwendet, da Standardindizes auf MEMORY-Tabellen Hash-Indizes sind. Hash-Indizes können nur für Gleichheitsprüfungen verwendet werden, nicht für Bereiche wie post_time < DATE_SUB(NOW(), INTERVAL 15 MINUTE)

Erstellen Sie einen BTREE-Index auf Ihrer post_time-Spalte,

CREATE INDEX post_time_idx ON tracker (post_time) USING BTREE;

Answer 2

Erstellen Sie einen Index auf post_time. Wenn die Zeilen, die mit post_time < DATE_SUB(NOW(), INTERVAL 15 MINUTE) übereinstimmen, nur einen kleinen Teil der gesamten Tabelle ausmachen, sollte dies die Geschwindigkeit erheblich erhöhen.

Answer 3

Wenn Ihre Tabelle nie mehr als 15 Minuten Daten enthält, könnten Sie einen kleineren Datentyp als DATETIME verwenden, um Ihre Zeitstempel zu speichern. Je nach der Granularität, die Ihnen wichtig ist, könnten Sie potenziell einen sehr kleinen Datentyp verwenden... Mit einem SMALLINT können Sie 'Minuten seit Mitternacht' speichern. Wenn Sie bereit wären, noch mehr Granularität zu verlieren, könnten Sie für eine 15-Minuten-Granularität ein TINYINT verwenden. Natürlich erfordert dies etwas komplexere Logik, um Fälle wie "kurz nach Mitternacht" zu behandeln...

DELETE FROM tracker
WHERE (
    EXTRACT(DAY_MINUTE FROM NOW()) > 15
    AND post_time < EXTRACT(DAY_MINUTE FROM NOW())
) OR (
    post_time < EXTRACT(DAY_MINUTE FROM NOW()) < 15
    AND post_time < EXTRACT(DAY_MINUTE FROM NOW()+60)
)

Der Vorteil besteht darin, dass die Daten, die Sie lesen und vergleichen müssen, viel kleiner sind, sodass sie schneller verarbeitet werden können. Dies würde einen größeren Unterschied machen, wenn Sie Ihre Daten auf der Festplatte speichern würden, da die Festplatten-E/A im Verhältnis viel wichtiger ist als Ihre Speicherbandbreite.

Außerdem bezweifle ich, dass dies einen spürbaren Unterschied machen würde, ob auf der Festplatte oder im Speicher, für eine Tabelle mit nur 10-15k Zeilen und einem geeigneten Index.