So paginieren Sie schnell MySQL-Datenmengen im zweistelligen Millionenbereich

Vorwort

Um bei der Backend-Entwicklung zu verhindern, dass zu viele Daten gleichzeitig geladen werden, was zu übermäßigem Speicher- und Festplatten-E/A-Overhead führt, ist häufig eine Paging-Anzeige erforderlich. Zu diesem Zeitpunkt wird das Schlüsselwort LIMIT von MySQL benötigt. Aber glauben Sie, dass mit LIMIT-Paging alles gut gehen wird? Es ist zu neu und zu einfach. Bei großen Datenmengen ist Deep Paging wahrscheinlich ein Problem, das LIMIT verursachen kann.

Fall

Hier nehme ich die Anzeige der E-Commerce-Bestelldetails als Beispiel und die neue Tabelle sieht wie folgt aus:

Tabelle „cps_user_order_detail“ erstellen (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel',
 `user_id` varchar(32) NOT NULL DEFAULT '' KOMMENTAR 'Benutzer-ID',
 `order_id` bigint(20) DEFAULT NULL COMMENT 'Bestell-ID',
 `sku_id` bigint(20) unsigned NOT NULL COMMENT 'Produkt-ID',
 `order_time` datetime DEFAULT NULL COMMENT 'Bestellzeit, Format jjjj-MM-tt HH:mm:ss',
 Primärschlüssel (`id`),
 SCHLÜSSEL `idx_time_user` (`order_time`,`user_id`) MIT BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='Benutzerbestelldetails';

Fügen Sie dann manuell 1,2 Millionen Datensätze in die Tabelle ein.

Jetzt gibt es eine Anforderung: Zeigen Sie die Bestelldetails des Benutzers seitenweise an, und zwar in umgekehrter Reihenfolge der Bestellzeit.

Die Tabellenstruktur ist schlank und die Anforderungen sind einfach. Also habe ich den Code schnell fertig geschrieben und zum Testen online gestellt. Anfangs lief alles normal, doch mit zunehmendem Auftragsvolumen wurde das System zunehmend langsamer und es wurden hin und wieder mehrere langsame Abfragen gemeldet.

An diesem Punkt sollten Sie denken, dass es sich um ein LIMIT-Offset-Problem handelt. Ja, es liegt nicht daran, dass Ihr SQL nicht schön genug ist, sondern am Mechanismus von MySQL selbst.

Hier nehme ich einfach zwei SQL-Anweisungen als Beispiel, wie in der folgenden Abbildung gezeigt, die von dem Positionsoffset 100 bzw. 1 Million aus paginieren. Sie können sehen, dass der Zeitunterschied sehr groß ist. Darin ist die Zeit für andere Datenberechnungen und -verarbeitungen nicht enthalten. Eine einzelne SQL-Abfrage dauert mehr als eine Sekunde, was bei den den Benutzern zur Verfügung gestellten Funktionen nicht tolerierbar ist (im E-Commerce ist es oft erforderlich, dass die Reaktionszeit einer Schnittstelle 200 ms nicht überschreitet).

Hier sehen wir uns den Ausführungsplan an, wie unten dargestellt:

Hier stellen wir zunächst die möglichen Werte und Bedeutungen der Spalte Extra im Ausführungsplan vor:

1. Die Verwendung von „where:“ gibt an, dass der Optimierer Daten über den Index zurück zur Tabelle abfragen muss.
2. Index verwenden: Überdeckender Index bedeutet, dass der direkte Zugriff auf den Index ausreicht, um die gewünschten Daten zu erhalten, ohne über den Index wieder zur Tabelle zurückkehren zu müssen. Dies wird in der Regel dadurch erreicht, dass für die abzufragenden Felder ein gemeinsamer Index erstellt wird.
3. Verwenden der Indexbedingung: Eine neue Funktion, die nach Version 5.6 hinzugefügt wurde, der bekannte Index-Pushdown, ist eine wesentliche Optimierung von MySQL zur Reduzierung der Anzahl der Tabellenrückgaben.
4. Verwenden von Filesort: Dateisortierung. Dies wird normalerweise während ORDER BY durchgeführt. Wenn die Datenmenge zu groß ist, ruft MySQL alle Daten zum Sortieren in den Speicher ab, was mehr Ressourcen verbraucht.

Wenn man das Bild oben betrachtet, führt dieselbe Anweisung allein aufgrund der unterschiedlichen Offsets zu einem großen Unterschied im Ausführungsplan (erlauben Sie mir, ein wenig zu übertreiben). In der ersten Anweisung ist der Wert der Spalte LIMIT 100,6type range, was auf einen Bereichsscan hinweist. Seine Leistung ist eine Stufe niedriger als die von ref, es wird jedoch auch berücksichtigt, dass der Index verwendet wird, und es wird auch Index-Pushdown angewendet: Das heißt, der Index wird zum Bestellzeitpunkt nach WHERE gelöscht und ausgewählt, und das nachfolgende ORDER BY wird ebenfalls basierend auf Index-Pushdown optimiert, was synchron ausgeführt wird, wenn die WHERE-Bedingung gefiltert wird (ohne zur Tabelle zurückzukehren).
Die zweite Anweisung LIMIT 1000000,6 verwendet den Index überhaupt nicht und der Wert der Typspalte ist ALL, was offensichtlich ein vollständiger Tabellenscan ist. In der Spalte „Extra“ gibt „Using where“ an, dass eine Tabellenrückgabe erfolgt, und „Using filesort“ gibt an, dass während ORDER BY eine Dateisortierung erfolgt. Die Langsamkeit hat hier also zwei Gründe: Erstens nimmt das Sortieren der Dateien zu viel Zeit in Anspruch, und zweitens muss nach dem Filtern der relevanten Daten entsprechend den Bedingungen basierend auf dem Offset zur Tabelle zurückgekehrt werden, um alle Werte zu erhalten. Unabhängig von dem oben genannten Punkt liegt die Ursache darin, dass der LIMIT-Offset zu groß ist. Daher besteht in der tatsächlichen Entwicklungsumgebung häufig die Anforderung, dass der nicht statistische Tabellenpegel eine Million nicht überschreiten darf.

Optimierung

Nachdem die Ursache analysiert wurde, wie können wir LIMIT Deep Paging in der tatsächlichen Entwicklung optimieren? Hier gebe ich Ihnen zwei Lösungen.
Eine Möglichkeit ist die Optimierung des Primärschlüsselindex. Was bedeutet das? Ändern Sie einfach die obige Anweisung wie folgt:

AUSWÄHLEN * VON cps_user_order_detail d WO d.id > #{maxId} UND d.order_time>'2020-8-5 00:00:00' BESTELLEN NACH d.order_time LIMIT 6;

Wie im obigen Code gezeigt, ist es auch paginiert, aber es gibt eine maxId-Einschränkung. Was bedeutet das? maxId ist die maximale Primärschlüssel-ID auf der vorherigen Seite. Daher lautet die Voraussetzung für die Verwendung dieser Methode: 1) Der Primärschlüssel muss automatisch inkrementiert werden und darf keine UUID sein. Zusätzlich zur Übergabe der grundlegenden Paging-Parameter pageNo und pageSize muss das Front-End auch die maximale ID jeder vorherigen Seite bereitstellen. 2) Diese Methode unterstützt keine zufälligen Seitensprünge, d. h. sie kann nur nach oben und unten blättern. Die folgende Abbildung zeigt eine tatsächliche Seite eines bekannten E-Commerce-Unternehmens.

Die zweite Möglichkeit ist die Suchmaschine Elastic Search (basierend auf invertiertem Index). Tatsächlich stellen E-Commerce-Unternehmen wie Taobao grundsätzlich alle ihre Produkte in die ES-Suchmaschine ein (es ist unmöglich, so große Datenmengen in MySQL einzugeben, und es ist nicht realistisch, sie in Redis einzugeben). Aber auch wenn Sie die Suchmaschine ES verwenden, können dennoch Deep-Paging-Probleme auftreten. Was sollten Sie dann tun? Die Antwort erfolgt über das Scrollen des Cursors. Wir werden hier nicht näher auf diesen Punkt eingehen, aber wer interessiert ist, kann selbst recherchieren.

Zusammenfassung

Ich habe diesen Blog geschrieben, weil ich es vor einiger Zeit während der Entwicklung tatsächlich erlebt habe und es während des Byte-Interviews mit dem Interviewer besprochen habe. Wenn Sie die Einschränkungen und Optimierungen von LIMIT kennen, ist das ein Plus, wenn Sie dies im Vorstellungsgespräch erwähnen können. Sagen Sie nicht, dass es bei der MySQL-Optimierung nur um das Erstellen von Indizes und Anpassen von SQL geht (tatsächlich sind die Auswirkungen dieser beiden Optimierungslösungen in der realen Entwicklung minimal). Wenn die MySQL-Optimierung so großartig wäre, gäbe es nicht so viele Middlewares.
Ich bin Shaoxia Lu Fei, ich liebe Technologie und ich teile gerne.

Oben finden Sie Einzelheiten dazu, wie Sie in MySQL schnell Millionen von Daten paginieren können. Weitere Informationen zum schnellen Paginieren in MySQL finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!