Erstellen eines Index für einen Zeitstempel zur Optimierung der Abfrage

Question

Ich habe eine Anfrage der folgenden Form:

SELECT * FROM MyTable WHERE Timestamp > [SomeTime] AND Timestamp < [SomeOtherTime]

Ich möchte diese Abfrage zu optimieren, und ich bin über das Setzen eines Index auf Zeitstempel denken, aber bin nicht sicher, ob dies helfen würde. Idealerweise würde ich den Zeitstempel gerne zu einem geclusterten Index machen, aber MySQL unterstützt keine geclusterten Indizes, außer für Primärschlüssel.

• MyTable hat mehr als 4 Millionen Zeilen.
• Timestamp ist eigentlich vom Typ INT .
• Sobald eine Zeile eingefügt wurde, wird sie nicht mehr geändert.
• Die Anzahl der Zeilen mit einer bestimmten Timestamp liegt im Durchschnitt bei 20, kann aber auch bis zu 200 betragen.
• Neu eingefügte Zeilen haben eine Timestamp die größer ist als die meisten der vorhandenen Zeilen, aber kleiner als einige der neueren Zeilen sein kann.

Würde ein Index auf Timestamp mir helfen, diese Abfrage zu optimieren?

Answer 1

Das ist keine Frage. Ohne den Index muss Ihre Abfrage jede Zeile der Tabelle durchsuchen. Mit dem Index wird die Abfrage so gut wie augenblicklich die richtigen Zeilen finden. Der Preis, den Sie dafür zahlen, ist ein leicht Leistungsabfall bei den Beilagen, der aber wirklich gering sein wird.

Answer 2

Sie sollten unbedingt einen Index verwenden. MySQL hat keine Ahnung, in welcher Reihenfolge diese Zeitstempel stehen, und um einen Datensatz für einen bestimmten Zeitstempel (oder Zeitstempelbereich) zu finden, muss es jeden einzelnen Datensatz durchsuchen. Und bei 4 Millionen Datensätzen ist das eine ganze Menge Zeit! Indizes sind Ihre Art, MySQL Ihre Daten mitzuteilen - "Ich werde dieses Feld ziemlich oft ansehen, also führe eine Liste, wo ich die Datensätze für jeden Wert finden kann."

Indizes sind im Allgemeinen eine gute Idee für regelmäßig abgefragte Felder. Der einzige Nachteil bei der Definition von Indizes ist, dass sie zusätzlichen Speicherplatz verbrauchen, also sollten Sie versuchen, sie zu benutzen, wenn Sie nicht wirklich Platzprobleme haben. Wenn sie nicht zutreffen, wird MySQL sie einfach ignorieren.

Answer 3

Ich widerspreche nicht der Bedeutung der Indizierung zur Verbesserung der Select-Abfragezeiten, aber wenn Sie auf andere Schlüssel indizieren können (und Ihre Abfragen mit diesen Indizes bilden), ist eine Indizierung auf Zeitstempel möglicherweise nicht erforderlich.

Wenn Sie zum Beispiel eine Tabelle mit timestamp , category y userId ist es vielleicht besser, einen Index auf userId stattdessen. In einer Tabelle mit vielen verschiedenen Benutzern wird dadurch die verbleibende Menge, in der der Zeitstempel gesucht werden kann, erheblich reduziert.

...und wenn ich mich nicht irre, wäre der Vorteil davon, den Overhead der Erstellung des Zeitstempelindex bei jeder Einfügung zu vermeiden -- in einer Tabelle mit hohen Einfügeraten und sehr eindeutigen Zeitstempeln könnte dies eine wichtige Überlegung sein.

Ich kämpfe mit den gleichen Problemen der Indizierung auf der Grundlage von Zeitstempeln und anderen Schlüsseln. Ich muss noch Tests durchführen, damit ich meine Aussagen hier belegen kann. Ich werde versuchen, auf der Grundlage meiner Ergebnisse zu posten.

Ein Szenario zur besseren Erklärung:

1. Zeitstempel 99% eindeutig
2. userId 80% eindeutig

3. Kategorie 25% einzigartig

   • Die Indizierung auf Zeitstempel reduziert die Abfrageergebnisse schnell auf 1% der Tabellengröße
   • Die Indizierung auf userId wird die Abfrageergebnisse schnell auf 20% der Tabellengröße reduzieren
   • Die Indizierung nach Kategorie reduziert die Abfrageergebnisse schnell auf 75 % der Tabellengröße.
   • Das Einfügen mit Indizes auf Zeitstempel hat einen hohen Overhead **
   • Obwohl wir wissen, dass unsere Einfügungen die Tatsache berücksichtigen, dass wir inkrementelle Zeitstempel haben, sehe ich keine Diskussion über die Optimierung von MySQL auf der Grundlage inkrementeller Schlüssel.
   • Das Einfügen mit Indizes auf userId führt zu einem relativ hohen Overhead.
   • Das Einfügen mit Indizes für die Kategorie hat einen relativ geringen Overhead.

** Es tut mir leid, ich kenne den berechneten Overhead oder die Einfügung mit Indexierung nicht.

Answer 4

Wenn Ihre Abfragen hauptsächlich diesen Zeitstempel verwenden, können Sie diesen Entwurf testen (Erweiterung des Primärschlüssels mit dem Zeitstempel als erstem Teil):

CREATE TABLE perf (
  , ts INT NOT NULL
  , oldPK 
  , ... other columns 
, PRIMARY KEY(ts, oldPK)
, UNIQUE (oldPK)
) ENGINE=InnoDB ;

Dadurch wird sichergestellt, dass die Abfragen wie die von Ihnen gepostete den geclusterten (Primär-)Schlüssel verwenden.

Der Nachteil ist, dass Ihre Einsätze etwas langsamer sind. Wenn Sie andere Indizes in der Tabelle haben, benötigen diese auch etwas mehr Platz (da sie den 4 Byte breiteren Primärschlüssel enthalten).

Der größte Vorteil eines solchen geclusterten Index ist, dass Abfragen mit großen Bereichsscans, z. B. Abfragen, die große Teile der Tabelle oder die gesamte Tabelle lesen müssen, die zugehörigen Zeilen sequentiell und in der gewünschten Reihenfolge finden ( BY timestamp ), was auch nützlich ist, wenn Sie nach Tag, Woche, Monat oder Jahr gruppieren möchten.

Die alte PK kann immer noch verwendet werden, um Zeilen zu identifizieren, indem man eine UNIQUE Einschränkung zu machen.

---

Sie können auch einen Blick werfen auf TokuDB eine (quelloffene) Variante von MySQL, die es ermöglicht mehrere geclusterte Indizes .