AllYouNeedIsBackend

Ich bin ein großer Fan von Datenbanken. Als ich an der Universität war, wollte ich sogar mein eigenes DBMS entwickeln. Jetzt arbeite ich sowohl mit RDBMS als auch mit NoSQL-Lösungen, und ich bin sehr begeistert davon. Wissen Sie, es gibt keinen goldenen Hammer, jedes Problem hat seine eigene Lösung. Oder eine Teilmenge von Lösungen.

In der Serie von Blogbeiträgen The SQL I Love <3 gehe ich einige mit SQL gelöste Probleme durch, die ich besonders interessant fand. Die Lösungen werden anhand einer Tabelle mit mehr als 100 Millionen Datensätzen getestet. Alle Beispiele verwenden MySQL, aber die Ideen gelten auch für andere relationale Datenspeicher wie PostgreSQL, Oracle und SQL Server.

In diesem Kapitel geht es um das effiziente Scannen einer großen Tabelle unter Verwendung von Paginierung mit offset auf dem Primärschlüssel. Dies ist auch als Keyset-Paginierung bekannt.

Hintergrund

In diesem Kapitel verwenden wir die folgende Datenbankstruktur als Beispiel. Das kanonische Beispiel über die Benutzer sollte zu jeder Domäne passen.

CREATE TABLE `users` ( `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `external_id` varchar(32) NOT NULL, `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `metadata` text COLLATE utf8_unicode_ci, `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`user_id`), UNIQUE KEY `uf_uniq_external_id` (`external_id`), UNIQUE KEY `uf_uniq_name` (`name`), KEY `date_created` (`date_created`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Ein paar Anmerkungen zur Struktur:

• external_id Spalte speichert Verweise auf denselben Benutzer in anderen Systemen im UUID-Format
• name stellt Firstname Lastname
• metadata Spalte enthält JSON-Blob mit allen Arten von unstrukturierten Daten

Die Tabelle ist relativ groß und enthält etwa 100 000 000 Datensätze. Beginnen wir unsere Lernreise.

Scannen einer großen Tabelle

Problem: Sie müssen durch die Tabelle gehen, jeden Datensatz extrahieren, ihn im Code Ihrer Anwendung umwandeln und an anderer Stelle einfügen. Wir konzentrieren uns in diesem Beitrag auf die erste Phase – das Scannen der Tabelle.

Offensichtliche und falsche Lösung

SELECT user_id, external_id, name, metadata, date_createdFROM users;

In meinem Fall mit 100 000 000 Datensätzen wird die Abfrage nie beendet. Das DBMS bricht sie einfach ab. Und warum? Wahrscheinlich, weil sie zu dem Versuch führte, die gesamte Tabelle in den RAM zu laden. Bevor die Daten an den Client zurückgegeben werden. Eine andere Vermutung ist, dass das Vorladen der Daten vor dem Senden zu viel Zeit in Anspruch genommen hat und die Abfrage eine Zeitüberschreitung verursacht hat. Wie auch immer, unser Versuch, alle Datensätze rechtzeitig zu erhalten, schlug fehl. Wir müssen eine andere Lösung finden.

Lösung #2

Wir können versuchen, die Daten seitenweise zu erhalten. Da die Datensätze in einer Tabelle auf physischer oder logischer Ebene nicht garantiert geordnet sind, müssen wir sie auf der DBMS-Seite mit der ORDER BY-Klausel sortieren.

SELECT user_id, external_id, name, metadata, date_createdFROM usersORDER BY user_id ASCLIMIT 0, 10 000;10 000 rows in set (0.03 sec)

Süß. Es hat funktioniert. Wir haben die erste Seite mit 10 000 Datensätzen abgefragt, und es hat nur 0.03 Sekunden gedauert, sie zurückzugeben. Aber wie würde es bei der 5000. Seite funktionieren?

SELECT user_id, external_id, name, metadata, date_createdFROM usersORDER BY user_id ASCLIMIT 50 000 000, 10 000; --- 5 000th page * 10 000 page size10 000 rows in set (40.81 sec)

Das ist in der Tat sehr langsam. Schauen wir mal, wie viel Zeit benötigt wird, um die Daten für die letzte Seite zu erhalten.

SELECT user_id, external_id, name, metadata, date_createdFROM usersORDER BY user_id ASCLIMIT 99 990 000, 10 000; --- 9999th page * 10 000 page size10 000 rows in set (1 min 20.61 sec)

Das ist Wahnsinn. Kann aber für Lösungen, die im Hintergrund laufen, OK sein. Ein weiteres verstecktes Problem mit diesem Ansatz tritt zutage, wenn Sie versuchen, einen Datensatz aus der Tabelle zu löschen, während Sie sie gerade durchsuchen. Angenommen, Sie haben die 10. Seite beendet (100 000 Datensätze sind bereits besucht) und wollen nun die Datensätze zwischen 100 001 und 110 000 durchsuchen. Die Datensätze 99 998 und 99 999 werden jedoch vor der nächsten SELECT Ausführung gelöscht. In diesem Fall liefert die folgende Abfrage ein unerwartetes Ergebnis:

 SELECT user_id, external_id, name, metadata, date_created FROM users ORDER BY user_id ASC LIMIT 100 000, 10 000; N, id, ... 1, 100 003, ... 2, 100 004, ...

Wie Sie sehen können, überspringt die Abfrage die Datensätze mit den IDs 100 001 und 100 002. Sie werden vom Anwendungscode bei diesem Ansatz nicht verarbeitet, da sie nach den beiden Löschvorgängen unter den ersten 100 000 Datensätzen erscheinen. Daher ist die Methode unzuverlässig, wenn der Datensatz veränderlich ist.

Lösung Nr. 3 – die letzte für heute

Der Ansatz ist dem vorherigen sehr ähnlich, da er immer noch Paging verwendet, aber jetzt verwenden wir statt der Anzahl der gescannten Datensätze den user_id des zuletzt besuchten Datensatzes als offset.

Vereinfachter Algorithmus:

1. Wir erhalten PAGE_SIZE Anzahl von Datensätzen aus der Tabelle. Der Startversatz ist 0.
2. Verwenden Sie den maximalen zurückgegebenen Wert für user_id im Stapel als Versatz für die nächste Seite.
3. Gewinnen Sie den nächsten Stapel aus den Datensätzen, deren user_id Wert höher ist als der aktuelle offset.

Die Abfrage in Aktion für die 5.000ste Seite, wobei jede Seite Daten über 10.000 Benutzer enthält:

SELECT user_id, external_id, name, metadata, date_createdFROM usersWHERE user_id > 51 234 123 --- value of user_id for 50 000 000th recordORDER BY user_id ASCLIMIT 10 000;10 000 rows in set (0.03 sec)

Beachten Sie, dass die Werte von user_id nicht sequentiell sind und Lücken haben können, wie z.B. 25 348 ist direkt nach 25 345. Die Lösung funktioniert auch, wenn Datensätze aus zukünftigen Seiten gelöscht werden – selbst in diesem Fall überspringt die Abfrage keine Datensätze. Super, oder?

Erklärung der Leistung

Für weiteres Lernen empfehle ich, die Ergebnisse von EXPLAIN EXTENDED für jede Version der Abfrage zu untersuchen, um die nächsten 10 000 Datensätze nach 50 000 000 zu erhalten.

Lassen Sie uns auf den Hauptunterschied zwischen den Ausführungsplänen für die 2. und 3. Lösung konzentrieren, da die 1. Lösung für große Tabellen praktisch nicht sinnvoll ist.

• Join type: index vs. range. Die erste Lösung bedeutet, dass der gesamte Indexbaum durchsucht wird, um die Datensätze zu finden. Der Typ range besagt, dass der Index nur verwendet wird, um übereinstimmende Zeilen innerhalb eines bestimmten Bereichs zu finden. Der Typ range ist also schneller als index.
• Mögliche Schlüssel: NULL vs. PRIMARY. Die Spalte zeigt die Schlüssel an, die von MySQL verwendet werden können. Wenn wir uns die Spalte Schlüssel ansehen, können wir übrigens feststellen, dass der Schlüssel PRIMARY für beide Abfragen verwendet wird.
• Zeilen: 50 010 000 vs. 50 000 000. Der Wert zeigt die Anzahl der analysierten Datensätze an, bevor das Ergebnis zurückgegeben wird. Bei der zweiten Abfrage hängt der Wert davon ab, wie tief wir blättern. Wenn wir zum Beispiel versuchen, die nächsten 10 000 Datensätze nach der 9999. Seite zu erhalten, werden 99 990 000 Datensätze untersucht. Im Gegensatz dazu hat die 3. Abfrage einen konstanten Wert; es spielt keine Rolle, ob wir Daten für die 1. oder die allerletzte Seite laden. Sie ist immer halb so groß wie die Tabelle.
• Gefiltert: 200.00 vs 100.00. Die Spalte gibt den geschätzten Prozentsatz der Tabelle an, der vor der Verarbeitung gefiltert werden soll. Je höher der Wert, desto besser. Der Wert von 100.00 bedeutet, dass die Abfrage die gesamte Tabelle durchsucht. Bei der zweiten Abfrage ist der Wert nicht konstant und hängt von der Seitenzahl ab: Wenn wir die erste Seite abfragen, würde der Wert der gefilterten Spalte 1000000.00 sein. Für die allerletzte Seite wäre es 100.00.
• Extra: NULL vs Using where. Liefert zusätzliche Informationen darüber, wie MySQL die Abfrage auflöst. Die Verwendung von WHERE auf dem PRIMARY-Schlüssel beschleunigt die Ausführung der Abfrage.

Ich vermute, dass der Join-Typ der Parameter der Abfrage ist, der den größten Beitrag zur Leistung leistet, um die dritte Abfrage schneller zu machen. Ein weiterer wichtiger Punkt ist, dass die 2. Abfrage extrem abhängig von der Anzahl der zu blätternden Seiten ist. Eine tiefere Paginierung ist in diesem Fall langsamer.

Weitere Hinweise zum Verständnis der Ausgabe des EXPLAIN-Befehls finden Sie in der offiziellen Dokumentation für Ihr RDBMS.

Zusammenfassung

Das Hauptthema des Blogbeitrags war das Scannen einer großen Tabelle mit 100 000 000 Datensätzen unter Verwendung von offset mit einem Primärschlüssel (Paginierung mit Schlüssel). Insgesamt wurden 3 verschiedene Ansätze untersucht und mit dem entsprechenden Datensatz getestet. Ich empfehle nur einen von ihnen, wenn Sie eine große Tabelle mit veränderlichen Daten scannen müssen.

Auch die Verwendung des Befehls EXPLAIN EXTENDED zur Analyse des Ausführungsplans von MySQL-Abfragen wurde überarbeitet. Ich bin sicher, dass andere RDBMS analoge Funktionen haben.

Im nächsten Kapitel werden wir uns mit der Datenaggregation und der Speicheroptimierung beschäftigen. Bleiben Sie dran!

Welche Methode verwenden Sie, um große Tabellen zu scannen?

Erinnern Sie sich an irgendeinen anderen Zweck der Verwendung von Keyset-Paginierung wie in Lösung #3?