60 Millionen Einträge, wählen Sie Einträge aus einem bestimmten Monat. Wie optimiert man die Datenbank?

Nutzen Sie die innodb-Cluster-Primärschlüsselindizes.

Ссылка

Dies wird extrem performant sein:

BEARBEITEN 2

Ich habe vergessen, dass ich das erste Testskript mit 3 Monaten Daten ausgeführt habe. Hier sind die Ergebnisse für einen einzelnen Monat: 0,34 und 0,69 Sekunden.

BEARBEITEN 1

Entschied sich, das obige Schema mit ca. 60 Millionen Zeilen verteilt über 3 Jahre. Jede Abfrage wird kalt ausgeführt, d. H. Jeder Lauf wird separat ausgeführt, nach dem mysql neu gestartet wird und alle Puffer gelöscht werden, ohne dass die Abfrage zwischengespeichert wird.

Das vollständige Testskript finden Sie hier: Ссылка oder darunter ...

Wie Sie sehen, ist es sogar auf meinem bescheidenen Desktop ein ziemlich performantes Schema:)

Hoffe das hilft:)

Um Einträge in einem bestimmten Monat für ein bestimmtes Jahr schneller zu erhalten, müssen die Spalte time indizieren :

Zusätzlich verwenden Sie:

... weil BETWEEN inklusiv ist, so dass Sie mit der von Ihnen geposteten Suchanfrage alles erhalten, was mit "2010-05-01 00:00:00" datiert ist.

Ich möchte auch Daten aus einem bestimmten Monat aus einer bestimmten DataSourceID

Sie können entweder einen separaten Index für die Spalte datasourceid hinzufügen:

... oder richten Sie einen Deckungsindex ein, um beide Spalten einzubeziehen:

Ein überdeckender Index erfordert, dass die Spalten ganz links in der Abfrage für den zu verwendenden Index verwendet werden müssen. In diesem Beispiel funktioniert die Verwendung von time first für beide genannten Situationen - datasourceid muss nicht verwendet werden, damit der Index verwendet werden kann. Sie müssen jedoch Ihre Abfragen testen, indem Sie die EXPLAIN-Ausgabe anzeigen, um zu wissen, was für Ihre Daten am besten geeignet ist. die Abfragen, die an diesen Daten ausgeführt werden.

Das heißt, Indizes verlangsamen INSERT-, UPDATE- und DELETE-Anweisungen. Und ein Index liefert keinen großen Wert, wenn die Spaltendaten nur wenige unterschiedliche Werte haben - IE: Eine boolesche Spalte ist eine schlechte Wahl für die Indexierung, da die Kardinalität niedrig ist.

Sie können einen Index verwenden, um die Plattenauslastung mit der Abfragegeschwindigkeit zu vergleichen. Ein Index, der die Spalte time startet, kann Abfragen beschleunigen, die nach einem bestimmten Monat fragen:

Da der Index mit dem Feld time beginnt, kann MySQL eine Schlüsselbereichsuche für den Index durchführen. Das sollte so schnell wie möglich sein. Der Index sollte alle Spalten in der Abfrage enthalten, oder MySQL müsste für jede Zeile vom Index zu den Tabellendaten schauen. Da Sie nach 2 Millionen Zeilen fragen, wird MySQL wahrscheinlich einen Index ignorieren, der nicht abdeckt. (Deckungsindex = Index, der alle Zeilen der Abfrage enthält.)

Wenn Sie keine ID abfragen, können Sie die Tabelle neu definieren, um (time, DataSourceID, ID) als Primärschlüssel zu verwenden:

Dies beschleunigt die Suche nach time ohne Speicherplatzkosten, aber die Suche nach ID wird sehr langsam sein.

Ich würde versuchen, einen Index zu setzen, wenn Sie nicht bereits auf dem Zeitfeld sind.

Bei DataSourceID könnten Sie versuchen, Enum anstelle von varchar / int zu verwenden.