MySQL-Lösung zur Datenpaging-Abfrageoptimierung auf Millionenebene

Wenn die Tabelle Zehntausende Datensätze enthält, die aus der Datenbank abgefragt werden müssen, wird das gleichzeitige Abfragen aller Ergebnisse sehr langsam, insbesondere wenn die Datenmenge zunimmt. Zu diesem Zeitpunkt ist eine Paging-Abfrage erforderlich. Auch für Datenbank-Paging-Abfragen gibt es zahlreiche Methoden und Optimierungspunkte. Hier sind einige Methoden, die ich kenne.

Vorbereitung

Um einige der unten aufgeführten Optimierungen zu testen, wird nachfolgend eine vorhandene Tabelle beschrieben.

Tabellenname: order_history
Beschreibung: Die Bestellverlaufstabelle eines bestimmten Unternehmens. Hauptfelder: unsigned int id, tinyint(4) int type
Feldsituation: Die Tabelle hat insgesamt 37 Felder, große Arrays wie Text ausgenommen. Das Maximum ist varchar(500). Das Feld id ist ein Index und wird hochgezählt.
Datenvolumen: 5709294
MySQL-Version: 5.7.16
Es ist nicht einfach, offline eine Testtabelle mit Millionen von Daten zu finden. Wenn Sie sie selbst testen müssen, können Sie ein Shell-Skript schreiben, um Daten zum Testen einzufügen.
Die Ausführungsumgebung aller folgenden SQL-Anweisungen hat sich nicht geändert. Hier sind die grundlegenden Testergebnisse:

select count(*) from orders_history;

Ergebnis zurückgeben: 5709294

Die drei Abfragezeitpunkte sind:

8903 ms
8323 ms
8401 ms

Allgemeine Paging-Abfrage

Allgemeine Paging-Abfragen können mit einer einfachen Limit-Klausel implementiert werden. Die Limit-Klausel wird wie folgt deklariert:

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

Mit der LIMIT-Klausel können Sie die Anzahl der Datensätze angeben, die von der SELECT-Anweisung zurückgegeben werden sollen. Folgende Punkte sind zu beachten:

Der erste Parameter gibt den Offset der ersten zurückgegebenen Datensatzzeile an. Der zweite Parameter gibt die maximale Anzahl zurückgegebener Datensatzzeilen an.

Wenn nur ein Parameter angegeben wird, gibt er die maximale Anzahl der zurückzugebenden Zeilen an. Der zweite Parameter ist -1, was bedeutet, dass alle Zeilen ab einem bestimmten Offset bis zum Ende des Datensatzes abgerufen werden. Der anfängliche Zeilenoffset ist 0 (nicht 1).

Hier ein Anwendungsbeispiel:

select * from orders_history where type=8 limit 1000,10;

Diese Anweisung fragt die 10 Datensätze nach dem 1000. Datensatz aus der Tabelle „orders_history“ ab, also den 1001. bis 10010. Datensatz.

Standardmäßig werden Datensätze in der Datentabelle nach Primärschlüssel (normalerweise ID) sortiert. Das obige Ergebnis entspricht:

select * from orders_history where type=8 order by id limit 10000,10;

Die drei Abfragezeitpunkte sind:

3040 ms
3063 ms
3018 ms

Für diese Abfragemethode wird im Folgenden der Einfluss des Abfragedatensatzvolumens auf die Zeit getestet:

Wählen Sie * aus der Auftragshistorie, wobei Typ = 8, Limit 10000,1 ist;
Wählen Sie * aus der Auftragshistorie, wobei Typ = 8, Limit 10000,10 ist;
Wählen Sie * aus der Auftragshistorie, wobei Typ = 8, Limit 10000,100 ist;
Wählen Sie * aus der Auftragshistorie, wobei Typ = 8, Limit 10000,1000 ist;
Wählen Sie * aus der Auftragshistorie, wobei Typ = 8, Limit 10000,10000 ist;

Die drei Abfragezeitpunkte sind wie folgt:

Abfrage 1 Datensatz: 3072 ms 3092 ms 3002 ms
Abfrage 10 Datensätze: 3081 ms 3077 ms 3032 ms
Abfrage von 100 Datensätzen: 3118 ms 3200 ms 3128 ms
Abfrage 1000 Datensätze: 3412ms 3468ms 3394ms
Abfrage 10.000 Datensätze: 3749ms 3802ms 3696ms

Darüber hinaus habe ich auch mehr als zehn Abfragen durchgeführt. Gemessen an der Abfragezeit kann grundsätzlich bestätigt werden, dass es grundsätzlich keinen Unterschied in der Abfragezeit gibt, wenn die Anzahl der Abfragedatensätze weniger als 100 beträgt. Mit zunehmender Anzahl der Abfragedatensätze erhöht sich auch der Zeitaufwand.

Test auf Abfrage-Offset:

Wählen Sie * aus der Auftragshistorie, wobei Typ = 8, Limit 100.100 ist;
Wählen Sie * aus der Auftragshistorie, wobei Typ = 8, Limit 1000,100 ist;
Wählen Sie * aus der Auftragshistorie, wobei Typ = 8, Limit 10000,100 ist;
Wählen Sie * aus der Auftragshistorie, wobei Typ = 8, Limit 100000,100 ist;
Wählen Sie * aus der Auftragshistorie, wobei Typ = 8, Limit 1000000,100 ist;

Die drei Abfragezeitpunkte sind wie folgt:

Abfrage 100 Offset: 25 ms 24 ms 24 ms
Abfrage 1000 Offset: 78 ms 76 ms 77 ms
Abfrage 10000 Offset: 3092 ms 3212 ms 3128 ms
Abfrage 100000 Offset: 3878 ms 3812 ms 3798 ms
Abfrage 1000000 Offset: 14608 ms 14062 ms 14700 ms

Mit zunehmendem Abfrage-Offset, insbesondere wenn der Abfrage-Offset größer als 100.000 ist, erhöht sich die Abfragezeit dramatisch.

Diese Paging-Abfragemethode beginnt mit dem Scannen beim ersten Datensatz in der Datenbank. Die Abfragegeschwindigkeit wird also langsamer, je weiter Sie in der Zeit zurückgehen. Darüber hinaus wird die Gesamtabfragegeschwindigkeit langsamer, je mehr Daten Sie abfragen.

Unterabfrageoptimierung verwenden

Diese Methode ermittelt zuerst die ID der Offsetposition und führt dann eine Rückwärtsabfrage durch. Diese Methode eignet sich für Situationen, in denen die ID inkrementell zunimmt.

Wählen Sie * aus der Auftragshistorie, wobei Typ = 8, Limit 100000,1 ist;

Wählen Sie die ID aus der Auftragshistorie, wobei Typ = 8, Limit 100000,1 ist;

Wählen Sie * aus der Auftragshistorie, wobei Typ = 8 und 
id>=(Wählen Sie die ID aus dem Auftragsverlauf, wobei Typ=8, Limit 100000,1 ist) 
Grenze 100;

Wählen Sie * aus der Auftragshistorie, wobei Typ = 8, Limit 100000,100 ist;

Die Abfragezeiten für die vier Anweisungen betragen:

Anweisung 1: 3674 ms
Anweisung 2: 1315 ms
Anweisung 3: 1327 ms
Anweisung 4: 3710 ms

Hinweis zur obigen Abfrage:

Vergleichen Sie die erste Anweisung mit der zweiten Anweisung: Die Verwendung von „select id“ anstelle von „select *“ erhöht die Geschwindigkeit um das Dreifache. Vergleichen Sie die zweite Anweisung mit der dritten Anweisung: Der Geschwindigkeitsunterschied beträgt einige zehn Millisekunden. Vergleichen Sie die dritte Anweisung mit der vierten Anweisung: Dank der Geschwindigkeitssteigerung von „select id“ erhöht sich die Abfragegeschwindigkeit der dritten Anweisung um das Dreifache. Diese Methode ist um ein Vielfaches schneller als die ursprüngliche allgemeine Abfragemethode.

Verwenden Sie die ID, um die Optimierung einzuschränken

Diese Methode geht davon aus, dass die ID der Datentabelle kontinuierlich zunimmt. Dann können wir den Bereich der abzufragenden ID basierend auf der Anzahl der Seiten und der Anzahl der abgefragten Datensätze berechnen. Wir können die ID zwischen und verwenden, um abzufragen:

Wählen Sie * aus der Auftragshistorie, wobei Typ = 2 und ID zwischen 1000000 und 1000100, Limit 100, ist.

Abfragezeit: 15ms 12ms 9ms

Diese Abfragemethode kann die Abfragegeschwindigkeit erheblich optimieren und kann grundsätzlich innerhalb von zehn Millisekunden abgeschlossen werden. Die Einschränkung besteht darin, dass es nur verwendet werden kann, wenn die ID eindeutig bekannt ist. Beim Erstellen einer Tabelle wird jedoch normalerweise ein grundlegendes ID-Feld hinzugefügt, was bei Paging-Abfragen viel Traversierung mit sich bringt.

Es gibt eine andere Möglichkeit, es zu schreiben:

select * from orders_history where id >= 1000001 limit 100;


Natürlich können Sie auch die in-Methode zum Abfragen verwenden, die häufig zum Abfragen verwendet wird, wenn mehrere Tabellen verknüpft sind, und den ID-Satz anderer Tabellenabfragen zum Abfragen verwenden:

Wählen Sie * aus der Auftragshistorie, wobei die ID in (Wählen Sie die Auftrags-ID aus Handel_2, wobei Waren = „Stift“) ist. Limit 100;

Bitte beachten Sie, dass einige MySQL-Versionen die Verwendung von Limits in der IN-Klausel nicht unterstützen.

Optimieren mithilfe temporärer Tabellen

Diese Methode gehört nicht mehr zur Abfrageoptimierung, sei hier aber am Rande erwähnt.

Um das Problem der Verwendung der ID zur Begrenzung der Optimierung zu lösen, muss die ID kontinuierlich erhöht werden. In einigen Szenarien, z. B. bei der Verwendung historischer Tabellen oder wenn Probleme mit fehlenden Daten auftreten, können Sie jedoch die Verwendung einer temporären Speichertabelle zum Aufzeichnen der Paging-ID und die Verwendung der Paging-ID zum Ausführen von Abfragen in Erwägung ziehen. Dadurch kann die Geschwindigkeit herkömmlicher Paging-Abfragen erheblich verbessert werden, insbesondere bei Datenmengen im zweistelligen Millionenbereich.

Beschreibung der Datentabellen-ID

Wenn Sie in der Datenbank eine Tabelle erstellen, ist es im Allgemeinen zwingend erforderlich, jeder Tabelle ein ID-inkrementierendes Feld hinzuzufügen, um die Abfrage zu erleichtern.

Wenn die Datenmenge einer Datenbank, beispielsweise einer Auftragsdatenbank, sehr groß ist, werden diese üblicherweise in einzelne Datenbanken und Tabellen aufgeteilt. Derzeit wird nicht empfohlen, die Datenbank-ID als eindeutige Kennung zu verwenden. Stattdessen sollten Sie einen verteilten Generator für eindeutige IDs mit hoher Parallelität verwenden, um sie zu generieren, und ein anderes Feld in der Datentabelle verwenden, um diese eindeutige Kennung zu speichern.

Die Abfragegeschwindigkeit kann um ein Vielfaches erhöht werden, wenn Sie zuerst die Bereichsabfrage verwenden, um die ID (oder den Index) zu lokalisieren, und dann den Index verwenden, um die Daten zu lokalisieren. Das heißt, wählen Sie zuerst die ID und dann * aus.